Address crossover issues and group subject assignments in APF Odisha

[mahalakshme]

A potential theory of why there could be a lot of association errors in the case of group subjects. This is not related to fast syncs, and will need to be analysed further.

1. There are a lot of intersecting catchments that are not pure subsets (say catchment A has address levels a, b and c while catchment B has address levels a, b and d). We use group subjects in apfodisha, and if in this scenario a household is registered in a and the individual in c, then user with catchment B will face association error during sync)

with first as (select distinct vcamt.catchment_id, array_agg(vcamt.addresslevel_id) addresses from virtual_catchment_address_mapping_table vcamt where vcamt.catchment_id in (select catchment_id from users where is_voided is false) group by vcamt.catchment_id), second as (select distinct vcamt.catchment_id, array_agg(vcamt.addresslevel_id) addresses from virtual_catchment_address_mapping_table vcamt where vcamt.catchment_id in (select catchment_id from users where is_voided is false) group by vcamt.catchment_id) select distinct first.catchment_id, second.catchment_id from first inner join second on first.addresses @> second.addresses is false and first.addresses <@ second.addresses is false and first.addresses && second.addresses is true;

2. Households and individuals are registered at different address levels. There are about 2400 such instances in the system today. If this matches the above condition, then we have a potential conflict. Catchments also change over time, causing association errors un-analysable.

select h.first_name household_name, i.first_name individual_name, a1."GP" household_gp, a1."Village/Hamlet" household_village, a2."GP" individual_gp, a2."Village/Hamlet" individual_village from apfodisha.household_individual hi inner join apfodisha.individual i on i.id = hi.member_subject_id inner join apfodisha.address a1 on i.address_id = a1.id inner join apfodisha.household h on h.id = hi.group_subject_id and i.address_id <> h.address_id inner join apfodisha.address a2 on h.address_id = a2.id order by h.id;

Potential solutions (if we conclude that the above mentioned issues are really an issue, and that they cause sync problems)

1. Provide method to ensure via rules that groups subjects and member subjects belong to the same address
2. Add warnings when there are potential cross-catchments when creating them on the UI

[mahalakshme]

@vinayvenu we would have known by now if the prod data has above issue since poshan sathis have small catchments and they are using the app. But we need to definitly do something to identify and provide a warning from a product perspective to avoid cross cathcment issue. Moving this to 6.0.