Fix duplicate privileges in mobile app

[mahalakshme]

As is:

Currently voided field in groupPrivileges table is not synced. But it needs to be synced to fix the privilege conflicts in mobile app.

AC:

• Sync voided field(independent of the version) also for group privileges to mobile app - which means will get fixed only after mobile upgrade which is fine.
• Whenever checking(say to display 'Edit' button on an encounter) for privileges in mobile app, check for only unvoided.

[himeshr]

Analysis of Privileges sync and usage in client

• All group privileges for an organisation, across all userGroups are always synced to the client, irrespective of the user loggedIn
• During privilege check, we filter and use only applicable userGroup groupPrivileges which are set to allow "True"
• We do not save voided field information in avni-client therefore, logic to use it also doesn't exist
• On Avni webapp, we do make use of groupPrivilege.isVoided flag to control privilege config on App Designer and Avni Webapp in general
• On Avni server, we do make use of only non voided groupPrivilege to controll CRUD access to Avni Generic entities via External APIs and web apis

Discussion info

After further discussion, we found that introducing the additional "voided" flag, does not seem to have an effective change in privilege handling, even after server and client app upgrades for a user.
Instead, during data fix, marking duplicate entries with allow = "false" is a more effective means to ensure that the expected change in User access is effected using groupPrivileges sync.

Conclusion

We'll not make any code changes with respect to groupPrivileges sync.
Instead, we'll set the allow field to "FALSE" for all duplicate entries and also mark them as voided in avniproject/avni-server#746

[himeshr]

Card is a Tech QA Card, which would be validated by myself, as i already have debugging and analysis context.

[himeshr]

Executed the GroupPrivileges correction queries on production.
ProdSqlsLogs.txt
ProdSqlsExecuted.txt

[kushboojain-iplit]

@himeshr The above file with the sql statements to update group_priviledge needs to be run before pulling the latest version of avni-server so that the migration to check uniqueness doesn't fail?

[mahalakshme]

@kushboojain-iplit you can run the below query alone, no need to run all the statements:

WITH group_privileges_duplicates AS
         (SELECT id,
                 ROW_NUMBER() OVER
                     (PARTITION BY group_id,privilege_id,subject_type_id,program_id,program_encounter_type_id,encounter_type_id,checklist_detail_id
                     ORDER BY last_modified_date_time desc)
                     AS rn
          FROM group_privilege
          where is_voided = false)
update group_privilege
SET last_modified_date_time = current_timestamp + (random() * 1000 * (interval '1 millisecond')),
    last_modified_by_id     = ${valid_user_id},
    is_voided= true,
    allow                   = false
FROM group_privileges_duplicates
where group_privilege.id = group_privileges_duplicates.id
  and group_privileges_duplicates.rn > 1;

[kushboojain-iplit]

@mahalakshme Thank you so much
I'll try this out