How Do You Design an Audit Trail Table in a Relational Database?
Learn how to design an audit trail table using triggers, capture old and new values, and keep the history tamper-evident.
Expected Interview Answer
An audit trail table records who changed what, when, and how — typically storing the table name, row ID, changed columns, old and new values, the actor, and a timestamp for every insert, update, or delete on the tables being audited, usually populated via triggers or an application-level logging layer.
The two common shapes are a wide history table that mirrors the source table's columns plus metadata (operation, changed_by, changed_at) for a full row snapshot per change, or a narrow event-log table storing one row per changed field with old/new value pairs, which is more flexible across many tables but harder to reconstruct a full row from. Triggers guarantee the audit entry is written in the same transaction as the change, so it can never be bypassed by application code, while application-level logging is easier to enrich with business context (like the reason for a change) but can be skipped if a direct database write happens outside the application. Either way, the audit table should be append-only, indexed by row ID and timestamp for lookups, and never updated or deleted once written.
- Provides a tamper-evident record of who changed what and when
- Supports compliance, dispute resolution, and debugging of unexpected data changes
- Enables reconstructing a row's state at any point in history
- Trigger-based capture cannot be bypassed by application bugs
AI Mentor Explanation
A cricket board's scorer keeps a separate logbook alongside the official scoreboard, writing down every correction ever made — who changed a run count, from what value to what, and at what minute — even after the scoreboard itself is updated to the corrected number. The logbook is never edited or torn out; it only grows. An audit trail table works the same way: every change to the main data gets a permanent, append-only entry describing exactly what changed and who made the change, separate from the current live data.
Step-by-Step Explanation
Step 1
Design the audit table shape
Choose a wide row-snapshot table or a narrow field-level event log, storing table name, row ID, operation, old/new values, actor, and timestamp.
Step 2
Capture changes via triggers
Attach AFTER INSERT/UPDATE/DELETE triggers on the source table so every change is logged in the same transaction, with no way to bypass it.
Step 3
Make the audit table append-only
Grant only INSERT privileges on the audit table to the application role; never allow UPDATE or DELETE on it.
Step 4
Index for lookups
Index by source row ID and by changed_at so you can efficiently pull a row's history or query changes within a time range.
What Interviewer Expects
- A concrete audit table schema with actor, timestamp, old/new values, and operation type
- Discussion of trigger-based vs application-level capture and their trade-offs
- Awareness that the audit table itself must be append-only and tamper-evident
- Recognition of the storage growth trade-off for high-write tables
Common Mistakes
- Allowing UPDATE or DELETE privileges on the audit table, defeating its purpose
- Relying only on application-level logging, which can be bypassed by direct database access
- Not capturing the actor (who made the change), only the what and when
- Storing only the new value with no old value, making it impossible to see what changed
Best Answer (HR Friendly)
“An audit trail table records every meaningful change made to important data — what changed, who changed it, the old and new values, and when it happened — kept as a permanent, append-only history separate from the live table. I would usually implement it with database triggers so the audit entry is written in the same transaction as the actual change and can never be skipped, and I would lock down the audit table so it only accepts inserts, never updates or deletes, to keep it trustworthy.”
Code Example
CREATE TABLE customers_audit (
audit_id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL,
operation TEXT NOT NULL,
old_email TEXT,
new_email TEXT,
changed_by TEXT NOT NULL,
changed_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE OR REPLACE FUNCTION log_customer_email_change()
RETURNS TRIGGER AS $$
BEGIN
IF OLD.email IS DISTINCT FROM NEW.email THEN
INSERT INTO customers_audit (customer_id, operation, old_email, new_email, changed_by)
VALUES (NEW.customer_id, 'UPDATE', OLD.email, NEW.email, current_user);
END IF;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_customers_email_audit
AFTER UPDATE ON customers
FOR EACH ROW EXECUTE FUNCTION log_customer_email_change();Follow-up Questions
- How would you reconstruct a row's full state as of a specific past date from an audit table?
- What are the storage implications of auditing a very high-write table, and how would you mitigate them?
- Would you prefer trigger-based auditing or application-level auditing, and why?
- How would you prevent even a database administrator from tampering with audit records?
MCQ Practice
1. Why are database triggers often preferred for capturing audit trail entries?
Because triggers fire as part of the same transaction, any write to the source table guarantees a corresponding audit entry, even for direct database access.
2. What privilege should the application role have on an audit trail table?
Restricting the audit table to INSERT-only preserves its integrity as a tamper-evident, append-only history.
3. What core fields does a well-designed audit entry need at minimum?
Without old value, actor, and timestamp, the audit entry cannot answer who changed what, from what, to what, and when.
Flash Cards
What is an audit trail table? — An append-only table recording who changed what data, the old and new values, and when the change happened.
Why use triggers for audit logging? — Triggers run in the same transaction as the change, so the audit entry cannot be skipped or bypassed.
What privilege should be granted on the audit table? — INSERT only — never UPDATE or DELETE — to keep the history tamper-evident.
Wide snapshot vs narrow event log? — A wide table stores a full row snapshot per change; a narrow log stores one row per changed field with old/new values.