Avni Server - Modifying the Backend Database
Ensure access for newly created DB tables for all Organisation DB_Users
In Avni Server, whenever we create a new DB Table using migrations, ensure that you include a grant_all_on_table
command to make it accessible for all organisation db_users.
SELECT grant_all_on_table(a.rolname, '<specify table name>') FROM pg_roles a WHERE pg_has_role('openchs', a.oid, 'member');
SELECT grant_all_on_table(a.rolname, 'organisation_status') FROM pg_roles a WHERE pg_has_role('openchs', a.oid, 'member');
Whenever a new DB table is created, include it for cleanup during Deletion of Organisation Data
For a newly created DB table, figure out the bucket it should fall under and include the table in corresponding OrganisationService.delete***() method. Also recreate the content, if its a basic admin config data for an organisation.
Definition for classification of Avni DB tables into types
- Transactional Data: All DB tables that are used to store Individual(Subject) related details. Ex: Individual, Encounter, ProgramEnrolment, ProgEncounter, Checklist, UserToSubjectAssignment, etc..
- Admin Config Data: All data which is accessible from the "Admin" section of the Avni-webapp
- Metadata: Rest of the DB tables specific to an organisation, that is not part of either "Transactional" or "Admin Config" Data
- System config data: Tables corresponding to
- CRUD of SuperAdmins
- CRUD of Organisation or OrganisationGroup
- Generic System data which is shared across Organisations. Ex: Privilege, ApprovalStatus, etc.
Use Repeatable migrations for Creation of Views, Functions and Triggers
Always include psql Create/replace views, functions and triggers within respective Repeatable migrations file and not in any version migrations. This is due to the fact that
- We would need to change the view based on any modifications to the tables its based on
- And in test DB, we'll recreate the DB and apply creation of views at the end after all the versioned migrations.
Updated about 2 months ago