Allowing user to rollback from db audit trail with SQLAlchemy

Question

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).

Answer 1

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)