Duplicate identifiers assigned to beneficiaries.

[Gojo-Taqi]

Describe the bug
In "Ctrith IPH" org, there are 6 individuals who duplicate identifiers assigned.

Run the below script to verify.

set role ctrith_iph;

select i.first_name , created_date_time , last_modified_date_time, i.observations ->> 'ba0d18c5-3da1-4d39-8b33-0ea92a95e26d'
from individual i 
where created_by_id = 7313
and i.observations ->> 'ba0d18c5-3da1-4d39-8b33-0ea92a95e26d' in (2812::text, 2813::text, 2814::text)
;

The folks from IPH claim that the users only use mobile application.
And on checking the sync telemetry for the user who created these beneficiaries, turns out that he has been using only one device so far.
And there is no way right now to verify whether the data was entered using DEA.

[vinayvenu]

@Gojo-Taqi why does the user have identifiers that are overlapping?

select *
from identifier_user_assignment
where assigned_to_user_id = 7313;

Important to remember that you don't have overlapping ids in rows of identifier_user_assignment. Use below query to figure out problems

select u1.username, u2.username,
       substr(iua1.identifier_start, 2)::numeric,
       substr(iua1.identifier_end, 2)::numeric,
       substr(iua2.identifier_start, 2)::numeric,
       substr(iua2.identifier_end, 2)::numeric
from identifier_user_assignment iua1
         inner join identifier_user_assignment iua2 on
            iua1.id <> iua2.id and
            iua1.identifier_source_id = iua1.identifier_source_id and
            substr(iua1.identifier_end, 2)::numeric > substr(iua2.identifier_start, 2)::numeric
        and substr(iua1.identifier_start, 2)::numeric < substr(iua2.identifier_end, 2)::numeric
        and iua1.is_voided is false and iua2.is_voided is false and iua1.assigned_to_user_id < iua2.assigned_to_user_id
         inner join users u1 on iua1.assigned_to_user_id = u1.id
         inner join users u2 on iua2.assigned_to_user_id = u2.id;

[vinayvenu]

Primary issue to be fixed through avniproject/avni-webapp#1022 . Closing this card.

[Gojo-Taqi]

If you check, you can see the type IDs are different for both.
There are 2 separate identifiers assigned to each user.
One for individual, and one for household.
Hence the overlapping in user assignment IDs.

[mahalakshme]

@Gojo-Taqi can you check if catchment was changed for any of the subject that has duplicate identifier from subject_migration table? Asking since this results in duplicate identifier.

[vinayvenu]

There are instances of the same id given to consecutive individuals. Looking at code, it looks like this if condition is not required - https://github.com/avniproject/avni-client/blob/master/packages/openchs-android/src/service/IdentifierAssignmentService.js#L69

If we remove it, any errors will be flagged right away instead of getting buried in. This can potentially cause some failures on the Android app, but will ensure that id assignments are not duplicated.

[mahalakshme]

@vinayvenu duplicate of avniproject/avni-client#1092. Have added the details in the ticket.