Keeping history: soft deletes, audit rows and who changed what

An UPDATE destroys the old value

A customer’s address changes. UPDATE customers SET address = ... WHERE id = 812 writes the new one, and the old one is gone. Next month someone asks where the March invoice was sent, and the table cannot say. A DELETE is the same one step further: the row is gone, and with it the fact that it ever existed.

Most businesses need some of the past. This lesson is the three ways to keep it, and what each costs.

Soft delete: the row stays, marked

ALTER TABLE customers ADD COLUMN deleted_at timestamptz;
UPDATE customers SET deleted_at = now() WHERE id = 812;   -- instead of DELETE

The row remains, with a timestamp saying when it was “deleted”. Every query that should not see deleted customers must now say WHERE deleted_at IS NULL, and every query that forgets will count them. This is the containment problem in miniature: the filter has to be everywhere, and the safe way to make it everywhere is a view, customers_live, that has the filter built in, with the raw table read only by the code that needs the deleted rows.

Two things break quietly. A UNIQUE (email) constraint now prevents a deleted customer’s email from ever being reused; the fix is a partial unique index, CREATE UNIQUE INDEX ON customers (email) WHERE deleted_at IS NULL, which enforces uniqueness only among the living. And a foreign key from orders still points at the soft-deleted customer, which is what you want for an order that happened and is confusing for a report that joins to it without the filter.

An audit table: what changed, when, by whom

Soft delete keeps the row but not its earlier versions. An audit table keeps every version:

CREATE TABLE customers_audit (
  audit_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL,
  op          text NOT NULL CHECK (op IN ('I', 'U', 'D')),
  changed_at  timestamptz NOT NULL DEFAULT now(),
  changed_by  text NOT NULL DEFAULT current_user,
  old_row     jsonb,
  new_row     jsonb
);

CREATE OR REPLACE FUNCTION customers_audit_fn() RETURNS trigger AS $$
BEGIN
  INSERT INTO customers_audit (customer_id, op, old_row, new_row)
  VALUES (coalesce(NEW.id, OLD.id), left(TG_OP, 1), to_jsonb(OLD), to_jsonb(NEW));
  RETURN NULL;
END $$ LANGUAGE plpgsql;

CREATE TRIGGER customers_audit AFTER INSERT OR UPDATE OR DELETE ON customers
FOR EACH ROW EXECUTE FUNCTION customers_audit_fn();

A trigger is code the database runs on every write to the table. This one copies the row before and after each change into the audit table as JSON, with the operation, the time and the database user. Nothing in the application has to remember to do it, which is the point: an audit that depends on every code path calling it is an audit with gaps. SQLite and MySQL have triggers with their own syntax; the previous lesson’s JSON column is what makes storing “the whole row, whatever its columns” straightforward.

The audit table answers “who changed this and what did it say before”. It does not answer “what was true on 14 March” conveniently, because that means finding, for each customer, the latest audit row before that date, which is the as-of pattern from the time module. If as-of questions are the common ones, the effective-dated table from that module is the better shape, with valid_from and valid_to on each version. Many systems keep both: the effective-dated table for queries, the audit log for accountability.

The minimum, on every table

created_at and updated_at, both timestamptz NOT NULL DEFAULT now(), with a trigger or application rule that sets updated_at on every change. They cost nothing and they answer half of all questions about history: how old is this, when did it last change, what changed today. A table without them is a table where “when” cannot be asked.

What history costs

An audit table grows faster than the table it watches, because every update adds a row. On a table updated a hundred times a second it becomes the largest table in the database within months, and needs its own retention rule, usually “keep two years, then archive to files”. Retention is also where the law enters: some records must be kept for a fixed period, some must be deleted on request, and the two requirements can apply to the same row. The moderation-and-legal material elsewhere on this site covers the legal-hold case; the database design point is that a soft delete and a legal hold are different flags, and merging them makes the audit log lie about which one applied.

The habit

Before the first UPDATE or DELETE on a table, decide which of the three it needs: nothing, a deleted_at, or a full audit trail. The decision is much cheaper to make before the history you wanted has already been overwritten.