Your application must be able to answer "who changed this customer record, when, and what did it look like before?". What table design gives you that, and what belongs in each column?
answer
- shadow table: entity_id, op, changed_at, changed_by, payload
- append-only, no FK back to the live table
- full snapshot > column deltas
- index (entity_id, changed_at DESC)
- same transaction; revoke UPDATE/DELETE
basics
~20 sAdd a history table that mirrors the live table's columns plus its own id, the changed row's id, changed_at, changed_by and the operation (insert/update/delete). Write one append-only row per change, inside the same transaction as the change.
solid answer
~60 sThe standard shape is a **shadow history table** per audited table: ``` customers_history ( history_id BIGSERIAL PRIMARY KEY, customer_id BIGINT NOT NULL, -- no FK to customers operation CHAR(1) NOT NULL, -- I / U / D changed_at TIMESTAMPTZ NOT NULL, changed_by BIGINT, -- user or service identity ... -- snapshot of every audited column ) ``` Design points worth stating: - **Append-only.** No updates, no deletes, no unique constraints beyond the surrogate key. It is a log. - **No foreign key back to the live table** — the parent may be hard-deleted later, and the history must survive it. - **Snapshot the whole row** rather than storing only changed columns; reconstructing a row from column deltas is fiddly and error-prone. Store the *after* image on insert/update and the *before* image on delete. - **Index `(customer_id, changed_at DESC)`** — the near-universal read is "history of this entity, newest first". - **Capture identity**, not just time: application user, and ideally request/correlation id. Write it in the **same transaction** as the change so history and data commit or roll back together.
code
sql · 14 linesCREATE TABLE customers_history (
history_id BIGSERIAL PRIMARY KEY,
customer_id BIGINT NOT NULL,
operation CHAR(1) NOT NULL CHECK (operation IN ('I','U','D')),
changed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
changed_by BIGINT,
request_id UUID,
name TEXT,
email TEXT,
credit_limit NUMERIC(12,2)
);
CREATE INDEX customers_history_entity_ix
ON customers_history (customer_id, changed_at DESC);go deeper
Describe the shadow table and its core columns — entity id, operation, changed_at, changed_by, and a copy of the values — and say it is append-only.
Add the design choices: snapshot vs delta vs JSON payload, no FK back to the live table, the (entity_id, changed_at) index, and writing inside the same transaction.
Discuss who fills changed_by and how (session context for triggers), tamper resistance via revoked privileges, growth control through partitioning and retention, and the limits of system-time-only auditing.
Decide which tables genuinely need history and to what standard, where history is stored relative to the OLTP path, how it is verified, and how retention interacts with erasure obligations.
## What is being asked for An *audit trail* or *history table* answers three questions about a row: what values it held at some past moment, when each change happened, and who made it. It is different from a backup (which recovers state, not narrative) and different from application logs (unstructured, usually unqueryable, often on a different retention schedule). ## The shadow-table shape For each audited table `customers`, create `customers_history` containing: - **`history_id`** — its own surrogate primary key. The history table has its own identity; the business key of the audited row is just data here. - **`customer_id`** — which entity this record describes. Deliberately *not* a foreign key: if the customer is later hard-deleted, the history must remain, and a foreign key would either block the delete or cascade the history away. - **`operation`** — `I`, `U` or `D` (or an enum). Without it you cannot distinguish "created with these values" from "updated to these values", and a delete has no after-image to infer from. - **`changed_at`** — the commit-relevant timestamp. Use a timestamp with time zone; store UTC. Note that in engines where `now()` is the transaction start time, all rows in a transaction share one value, which is usually what you want because it groups a logical change. - **`changed_by`** — the acting identity. This is the column that is hardest to fill and most often missing, because the database only knows the connection user, not the application user. Application-level auditing has it naturally; trigger-based auditing must be handed it through a session variable set at the start of the transaction. - **The payload** — a copy of the audited columns. Optional but frequently valuable: `transaction_id` (groups changes made together across tables), `request_id`/`correlation_id` (ties the change to an API call), and `reason`/`comment` for human-driven edits. ## Snapshot vs delta vs JSON Three payload styles: **Full-row snapshot with mirrored columns.** Every audited column is duplicated in the history table. Fast to query with plain SQL (`WHERE credit_limit > 1000`), self-documenting, and typed. The cost is schema coupling: every DDL change on the live table needs a matching change on the history table, and the history now contains rows from several schema generations. **Column-level delta** — one row per changed column (`column_name`, `old_value`, `new_value` as text). Compact and easy to display as "field X changed from A to B", but reconstructing the full row at a past instant requires replaying every delta from the beginning, values lose their types, and querying by business predicate becomes painful. **JSON snapshot** — a single `JSONB`/`JSON` column holding the whole row. No DDL coupling (the live table can evolve freely), and modern engines index and query JSON adequately. This is increasingly the default for generic auditing frameworks. The trade is weaker typing and slower, clumsier analytical queries. A common hybrid: JSON payload plus a few promoted, indexed columns (entity id, changed_at, changed_by, operation). ## Before-image, after-image, or both Storing the **after** image for inserts and updates, and the **before** image for deletes, is sufficient: the previous state of an update is the previous history row for that entity. Storing both images per row doubles the size but makes each history row self-contained and diffing trivial — worth it for low-volume, high-scrutiny tables (permissions, prices, limits) and wasteful for high-volume ones. ## Constraints, indexes and hygiene - **No unique constraints** on business columns; the whole point is repeated values over time. - **No updates or deletes** in normal operation. Enforce it if you can — revoke `UPDATE`/`DELETE` on the history table from the application role, so an accidental (or malicious) rewrite of the audit trail is impossible. Auditors care about this specifically. - **Index `(entity_id, changed_at DESC)`** for the entity-timeline read and, if you need cross-entity forensics, `(changed_by, changed_at)`. - **Plan for growth from day one.** History outgrows the live table quickly on hot tables. Partition by month, define a retention policy, and consider compression or moving old partitions to cheaper storage. ## Transactionality The history row must be written in the same transaction as the change. If history is written by a separate service call after the commit, any failure between the two produces a change with no record — the one thing an audit trail may not do. That constraint is why database triggers, or an in-process interceptor sharing the transaction, are the usual implementations, and why asynchronous approaches are built on the database's own log rather than on a second write. ## What this design does not give you It records *when the database was told*, not *when the fact was true in the world* — a salary change entered on the 10th but effective from the 1st looks like a change on the 10th. Expressing effective dates needs valid-time columns on the live row instead. It also records nothing about reads, so "who looked at this record" needs a different mechanism entirely.
- Why should the history table not have a foreign key to the live table?Because the audited row may legitimately be hard-deleted, and the history of a deleted entity is often the most valuable part of the trail. A foreign key would either block the delete (RESTRICT) or destroy the history (CASCADE). The history keeps the entity id as plain data, accepting that it can point at a row that no longer exists.
- Would you store the full row on every change, or only the columns that changed?Full-row snapshots by default: reconstructing the state at a past moment is a single row read rather than a replay of deltas, and the values keep their types. Column deltas are attractive for rendering "field X changed from A to B" and for very wide tables where changes touch one column, but they make point-in-time reconstruction and business-predicate queries much harder. A JSON snapshot is a good middle ground when the live schema evolves often.
saying these in an interview costs you the question
- Writing the audit record in a separate transaction or after the commit, so failures lose changes
- Putting a foreign key from history to the live table, which breaks when the entity is deleted
- Adding unique constraints or updating history rows in place
- Recording only a timestamp with no acting user, or only the database connection user
- Ignoring growth: no partitioning, no retention, indexes that only support full scans