(Nice-to-have) Transaction Log

[Jimmy-Lin]

For tracing user misconduct, most applications should have a log of every state-change to their data.

The most basic design would be a table containing columns identifying:

• The datetime of the transaction
• The user making the transaction
• The table being modified
• The column being modified
• The old value before transaction
• The new value after transaction
• A transaction ID (grouping multiple column changes into a single action)
• A brief comment specifying the reason/purpose behind the transaction

Admins and moderators should be able to list and perform queries on this table. This table will become massive so be careful what kind of queries you expose through the API.

Creating records on this table should be "implicit" as in it should be triggered by other user activity, checking before and after the activity so that you can observe and record any change resulted.