Allowing user to rollback from db audit trail with SQLAlchemy

audit, python, rollback, sqlalchemy

Solution

Although I haven't used SQLAlchemy specifically, I can give you some general tips that can be easily implemented in any ORM:

- Separate out the versioned item into two tables, say `Document` and `DocumentVersion`. `Document` stores information that will never change between versions, and `DocumentVersion` stores information that does change.

- Give each `DocumentVersion` a "parent" reference. Make a foreign key to the same table, pointing to the previous version of the document.

- Roll back to previous versions by updating a reference from `Document` to the "current" version. Don't delete versions from the bottom of the chain.

- When they make newer versions after rolling back, it will create another branch of versions.

Example, create A, B, C, rollback to B, create D, E:

(A)
 |
(B)
 | \
(C) (D)
     |
    (E)

Problem

I'm starting to use SQLAlchemy for a new project where I was planning to implement an audit trail similar to the one proposed on these questions: - Implementing Audit Trail for Objects in C#? - Audit trails and implementing SOX/HIPAA/etc, best practices for sensitive data - Ideas on database design for capturing audit trails - What is the best implementation for DB Audit Trail? - Is this the best approach to creating an audit trail? - Effective strategy for leaving an audit trail/change history for DB applications? - Data Auditing in NHibernate and SqlServer. As I will have the full history of the "interesting" objects, I was thinking in allowing users to rollback to a given version, giving them the possibility to have unlimited `undo`. Would this be possible to be done in a clean way with SQLAlchemy? What would be the correct way to expose this feature in the internal API (business logic and ORM)? I was something along the ways of `user.rollback(ver=42)`.

Original source

Related problems