Feature: DBI Table Versioning #293

New issue

Open

opened 2024-08-13 00:31:35 +00:00 by jamie · 1 comment

jamie commented

2024-08-13 00:31:35 +00:00

(Migrated from git.hazaar.io)

Problem Statement

The current ORM in Hazaar MVC does not support table versioning, which is essential for tracking changes to database records over time. This feature would allow users to maintain a history of changes, making it possible to audit, rollback, or view previous states of the data. Given that all queries are already abstracted via a DBI Adapter and a schema manager, it should be feasible to implement a versioning system that stores table history in another schema or database.

Who will benefit?

Developers and administrators who require robust data tracking and auditing capabilities will benefit from the ability to track changes to database records over time. This feature is particularly useful in applications where data integrity and history are critical, such as financial systems, content management, and legal compliance.

Benefits and risks

Benefits

Provides a detailed history of changes to database records, enabling auditing and rollback capabilities.
Enhances data integrity and compliance with regulations that require data versioning.
Can be implemented using the existing DBI Adapter and schema manager, leveraging existing abstractions.

Risks

Potential performance impact due to the additional overhead of versioning each change.
Increased complexity in the database schema and ORM logic to manage and retrieve historical data.
Need to ensure that the versioning system does not introduce significant delays or storage inefficiencies.

Proposed solution

Wrapper Function or Trigger-Based Versioning:
- Implement a wrapper function around the existing DBI Adapter to intercept all CRUD operations and store versions of records before they are modified or deleted.
- Alternatively, implement database triggers that automatically capture changes and store the previous state of records in a versioning table.
Storage of Versioned Data:
- Store versioned records in a separate schema or database dedicated to historical data.
- Implement mechanisms to efficiently retrieve historical data when needed, without impacting the performance of regular queries.
Schema Manager Integration:
- Extend the existing schema manager to support versioned tables and handle the creation and management of versioning schemas.
Configuration Options:
- Provide configuration options to enable or disable versioning on a per-table basis.
- Allow customization of versioning retention policies (e.g., how long to keep historical data).

Examples

N/A

Priority/Severity

High (This will bring a huge increase in performance/productivity/usability/legislative cover)
Medium (This will bring a good increase in performance/productivity/usability)
Low (anything else e.g., trivial, minor improvements)

## Problem Statement The current ORM in Hazaar MVC does not support table versioning, which is essential for tracking changes to database records over time. This feature would allow users to maintain a history of changes, making it possible to audit, rollback, or view previous states of the data. Given that all queries are already abstracted via a DBI Adapter and a schema manager, it should be feasible to implement a versioning system that stores table history in another schema or database. ## Who will benefit? Developers and administrators who require robust data tracking and auditing capabilities will benefit from the ability to track changes to database records over time. This feature is particularly useful in applications where data integrity and history are critical, such as financial systems, content management, and legal compliance. ## Benefits and risks ### Benefits - Provides a detailed history of changes to database records, enabling auditing and rollback capabilities. - Enhances data integrity and compliance with regulations that require data versioning. - Can be implemented using the existing DBI Adapter and schema manager, leveraging existing abstractions. ### Risks - Potential performance impact due to the additional overhead of versioning each change. - Increased complexity in the database schema and ORM logic to manage and retrieve historical data. - Need to ensure that the versioning system does not introduce significant delays or storage inefficiencies. ## Proposed solution 1. **Wrapper Function or Trigger-Based Versioning:** - Implement a wrapper function around the existing DBI Adapter to intercept all CRUD operations and store versions of records before they are modified or deleted. - Alternatively, implement database triggers that automatically capture changes and store the previous state of records in a versioning table. 2. **Storage of Versioned Data:** - Store versioned records in a separate schema or database dedicated to historical data. - Implement mechanisms to efficiently retrieve historical data when needed, without impacting the performance of regular queries. 3. **Schema Manager Integration:** - Extend the existing schema manager to support versioned tables and handle the creation and management of versioning schemas. 4. **Configuration Options:** - Provide configuration options to enable or disable versioning on a per-table basis. - Allow customization of versioning retention policies (e.g., how long to keep historical data). ## Examples N/A ## Priority/Severity - [x] High (This will bring a huge increase in performance/productivity/usability/legislative cover) - [ ] Medium (This will bring a good increase in performance/productivity/usability) - [ ] Low (anything else e.g., trivial, minor improvements)

jamie commented

2024-08-13 00:31:36 +00:00

(Migrated from git.hazaar.io)

assigned to @jamie