Advanced Schema PatternsLesson 4.4

Audit tables: tracking who changed what and when

audit trail pattern, separate audit table, created_by, updated_by, changelog table structure, trigger-based audit, application-layer audit, GDPR considerations

Why Audit Trails

An audit trail records who made each change and when. Required for financial systems, healthcare, compliance, and any system where accountability matters.

Approach 1: Audit Columns on the Main Table

ALTER TABLE orders
  ADD COLUMN created_by  INT REFERENCES users(user_id),
  ADD COLUMN updated_by  INT REFERENCES users(user_id),
  ADD COLUMN created_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  ADD COLUMN updated_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
              ON UPDATE CURRENT_TIMESTAMP;

Tracks last modifier only. No history.

Approach 2: Separate Audit Table

CREATE TABLE orders_audit (
  audit_id    INT PRIMARY KEY AUTO_INCREMENT,
  order_id    INT NOT NULL,
  action      ENUM('INSERT','UPDATE','DELETE') NOT NULL,
  changed_by  INT REFERENCES users(user_id),
  changed_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  old_data    JSON,
  new_data    JSON
);

Full history of every change. Trigger or application layer writes to this table on every mutation. The JSON columns capture the before and after state of the row.

GDPR Note

Audit logs containing personal data are subject to right-to-erasure requests. Design the audit table so PII can be anonymized (replace with a hash or null) without destroying the audit chain integrity.