Audit trail

[mahalakshme]

Need:

• Help in troubleshooting data problems
  • Many times when user report faulty data, since there is no audit trail it is almost impossible or takes signifcantly longer time to troubleshoot.
  • A simple audit trail like what Metabase and Freshdesk give would go a long way in supporting and using the product better.

Solution suggestions:

• Have a audit record for each table where we just copy the row to the new audit table, before updating the main table

Inputs:

• metadata(no need the QoS that we are providing may be - specific table)/transactional data(helps for losing data)
• separate table - so current behavior not changed
• triggers - before update - parallel table
• single table - table_name, column_name, old_value, new_value, date_time
• 3 approaches: single table, parallel table, json blob
• Maintainenance: structure of table when source changing, features built on it
• other tools: check out - easy to query, restore, SQL domain(spike)

Analysis:

Metadata tables: address_level, catchment_address_mapping, subject_type, encounter_type, program

### Tasks
- [ ] https://github.com/avniproject/avni-server/issues/732