Why Keep History
A live table holds only the current state. Compliance, debugging, and analytics often need the past: what a record looked like before, who changed it, and when. An audit table captures each change as an immutable record alongside the main table.
How It Is Built
- For a table like accounts, add an accounts history table.
- On each insert, update, or delete, write a row recording the new values, the operation type, a timestamp, and the acting user.
- A trigger or application code performs the dual write.
What an Audit Row Holds
- The primary key of the affected record.
- A snapshot of the changed columns, often old and new values.
- Metadata: operation, actor, and time.
Design Cautions
- History tables grow without bound, so plan partitioning or archival.
- Triggers are easy to forget if writes bypass them, so prefer one consistent path.
- Audit rows should be append only and never updated.
Key idea
Audit tables record every change as immutable append only rows, answering who changed what and when at the cost of unbounded growth.