The database will have a “roll-back” table which will allow for “undo” operations. It's envisaged that each transaction (which is a collection of stored procedure calls) will have a unique transaction identifier. When a user wants to undo a previous action they will be able to as the previous transaction identifiers will be stored in their session history. There will be an arbitrary limit to how far they can roll-back transactions.
The roll-back table will also capture the previous/current value for each change. This will be useful for reporting and also invaluable for auditing activity in the database.
This will result in a lot of disk space being used to support this functionality. These days storage is cheap so it is not considered a bad choice.
Another method is to never update a row, but mark it as old and copy a new row details. This will then allow you to easily roll back by just copying the row again to a previous one. This means you can also snapshot the whole application at a point in time. The downside of this is that it's going to store a lot of data. To try and mitigate this the tables should not have too many columns. This means more tables, and more normalisation. This is also known as a "Point in Time Architecture".
This idea came from: stackoverflow.com/questions/2640610/best-practices-for-managing-updating-a-database-with-a-complex-set-of-changes and www.simple-talk.com/sql/database-administration/database-design-a-point-in-time-architecture/.
There will be a few meta-data columns in each table:
The table_uuid and table_id will be generated the first time a new item is added to a table. These will then be re-used for each time the new version of the row is added. There will be a “Sequence” table or something like it, which will store the next id for every table in the system. This will have a mutex lock on it so that the next generated id will not be clashing with something else.
There will also be an audit table to capture field level changes, and a human readable audit message which describes what has been changed. See the “Detailed Auditing” section below.
The clustered index (main index) for every table will be the following columns: