Allowing user to rollback from db audit trail with SQLAlchemy

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)