bug: Area elements do not get recalculated on change of area bounds.

[dadofsambonzuki]

Matrix discussion.

Problem Summary

When a community boundary (geo_json) is redefined, the "Maintain" tab continues to show linting issues for elements that are now outside the new bounds.

Root Cause

The linting issues are fetched via an RPC call to get_element_issues which queries the element_issue table joined with the area_element table. However, the SQL queries don't filter out soft-deleted area_element records.

Flow of the Bug

1. Initial State: Element A is inside Community X's bounds → area_element record exists linking Element A to Community X

2. Boundary Change: You update Community X's geo_json, and Element A is now outside the new bounds

3. Correct Behavior: patch_tags() in area.rs calls generate_mapping() which correctly marks the area_element record as deleted by setting deleted_at = NOW()

4. Bug: The get_element_issues RPC still returns Element A's issues because the SQL join doesn't filter for deleted_at IS NULL

Location of Bug

File: btcmap-api/src/db/element_issue/blocking_queries.rs

Affected Functions:

• select_ordered_by_severity() (lines 80-125)
• select_count() (lines 142-189)

The Problematic Code

In select_ordered_by_severity() (line 87-93):

let area_join = match area_id {
    662 => "".into(),
    _ => format!(
        "INNER JOIN {area_element_table} ae ON ae.element_id = ei.element_id AND ae.area_id = {area_id}",
        area_element_table = db::area_element::schema::TABLE_NAME
    )
};

Missing: AND ae.deleted_at IS NULL

In select_count() (line 148-153):

let area_join = match area_id {
    662 => "".into(),
    _ => format!(
        "INNER JOIN {area_element_table} ae ON ae.element_id = ei.element_id AND ae.area_id = {area_id}",
        area_element_table = db::area_element::schema::TABLE_NAME
    )
};

Missing: Same issue - no deleted_at filter

The Fix

Add AND ae.deleted_at IS NULL to both area_join clauses:

let area_join = match area_id {
    662 => "".into(),
    _ => format!(
        "INNER JOIN {area_element_table} ae ON ae.element_id = ei.element_id AND ae.area_id = {area_id} AND ae.deleted_at IS NULL",
        area_element_table = db::area_element::schema::TABLE_NAME
    )
};

Files to Modify

1. btcmap-api/src/db/element_issue/blocking_queries.rs
   • Line 89-93: Update area_join in select_ordered_by_severity()
   • Line 150-153: Update area_join in select_count()

Impact

After this fix, when a community boundary is updated:

• Elements outside the new bounds will have their area_element records soft-deleted
• The maintain tab queries will correctly exclude these elements' issues
• Users will only see issues for elements actually within the community's current bounds

[dadofsambonzuki]

Could the below be related, or is this a separate issue @bubelov?

Hello Nathan, a Bitcoin enthusiast from Jamaica let me know that the
last two businesses were not displaying correctly on the Jamaican map:
https://btcmap.org/country/jm/merchants

These are the businesses:

https://btcmap.org/merchant/34931

https://btcmap.org/merchant/34933

Have a nice day. Thank you

Rockedf

🌎 BTCmap.org Volunteer
https://btcmap.org/tagger/7522075