Team assignment query runs endlessly if the location hierarchy is invalid

[bennsimon]

When a user logs in on opensrp web or app, team assignment query is executed to get the organizations and locations for assigned to a user. That query will run endlessly (if the location hierarchy is invalid) unless explicitly stopped by the application or database.

select lm.geojson_id identifier ,lm."name" ,lm.parent_id, 'false' voided, lm.location_id,       string_agg(lt."name",',') tags,       l.json->'properties'->>'geographicLevel' geographic_level,   record.level   from core.location_metadata lm       left join core.location_tag_map ltm on ltm.location_id =lm.location_id    left join core.location_tag lt on lt.id =ltm.location_tag_id       INNER join core.location l on l.id = lm.location_id   JOIN   (    WITH RECURSIVE locations    AS    ( SELECT lm1.geojson_id, lm1.parent_id, lm1.location_id, 0 as level     FROM core.location_metadata lm1 WHERE geojson_id  IN       (              ?         )            AND lm1.status IN ('ACTIVE', 'PENDING_REVIEW')     UNION     SELECT lm2.geojson_id, lm2.parent_id, lm2.location_id,level+1 as level     FROM locations lm1     INNER JOIN core.location_metadata lm2 on lm2.geojson_id = lm1.parent_id  AND lm2.status IN ('ACTIVE', 'PENDING_REVIEW')    )    SELECT location_id, level FROM locations   ) record   on lm.location_id =record.location_id       group by lm.geojson_id ,lm."name" ,lm.parent_id, voided, lm.location_id, geographic_level, record.level       order by record.level

Validation needs to be added to ensure the location hierarchy is always correct when locations are being added/updated.