Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Address crossover issues and group subject assignments in APF Odisha #1141

Open
mahalakshme opened this issue Oct 16, 2023 · 1 comment
Open

Comments

@mahalakshme
Copy link
Contributor

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;

  1. 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 mahalakshme converted this from a draft issue Oct 16, 2023
@mahalakshme
Copy link
Contributor Author

mahalakshme commented Oct 27, 2023

@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.

@mahalakshme mahalakshme moved this from In Analysis to Focus Items in Avni Product Oct 30, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Labels
None yet
Projects
Status: Focus Items
Development

No branches or pull requests

1 participant