Implementation of Change History Tracking for Critical Schemas

[totopoloco]

Related Ticket: #1844

Core Requirements

Implement immutable change tracking for critical data schemas to:

• Ensure data provenance compliance
• Support FAIR principles (Reusable metadata)
• Enable forensic analysis of modifications
• Maintain dataset integrity through lifecycle

A MongoDB-based history tracking system using:

• Change Capture: Mongoose middleware hooks pre('save'), pre('remove')
• Automatic recording of history data, see the following example:

+--------+-----------+------------+-----------+---------------------+--------------------+---------------------+-----------+
| ID     | ModelName | OriginalId | Operation | OldValues           | NewValues          | HistoryDate         | ChangedBy |
+--------+-----------+------------+-----------+---------------------+--------------------+---------------------+-----------+
| addf34 | User      | 6488b5     | UPDATE    | {"name":"XXXXXXXX"} | {"name":"YYYYYYY"} | 2025-04-29 13:03:04 | system    |
+--------+-----------+------------+-----------+---------------------+--------------------+---------------------+-----------+

Field Descriptions:
OriginalId: Reference to source document
Operation: UPDATE/DELETE
OldValues/NewValues: Delta changes

Open Questions for Community Input

1. Schema Selection: Which entities require history tracking?

2. Access Control Model

| **Access Model**      | **Advantages**         | **Challenges**            |
|-----------------------|------------------------|---------------------------|
| Inherit Permissions   | Simple implementation  | Potential security risk   |
| Admin-Only Access     | Enhanced security      | Reduced transparency      |
| Tiered Access         | Granular control       | Complex implementation    |

4. Query Requirements: What filtering capabilities are needed for history data?
   • Time windows: "Show changes last 30 days"
   • User tracking: "Changes by user X"
   • Operation types: "All DELETE operations"
   • Cross-reference: "History for document ABC"

Next Action Plan

1. Community review of POC in Sample History
2. Agree on list of critical schemas for Phase 1
3. Define API contract for history access
4. Performance testing plan (load testing with history enabled and index optimization)
5. Documentation framework:
   • Retention policy guidelines
   • Storage cost estimations
   • Access pattern examples

Community Engagement Request
Share your expertise on:

• Audit system implementation patterns
• MongoDB performance optimization
• GDPR-compliant retention policies
• Hidden edge cases (e.g., concurrent updates)

Let's build a solution that balances transparency, performance, and security!

Note: I have started to create an architecture for it:
Yellow: Core framework (non-modifiable)
Green: Schema-specific implementations

Let's collaborate to build a robust, maintainable solution that serves our community's needs!