{"course":{"slug":"data-and-sql","title":"Data, SQL and Getting to the Answer","url":"https://addaly.com/learn/data-and-sql","level":"beginner","track":"build"},"lesson":{"slug":"history-and-audit-tables","title":"Keeping history: soft deletes, audit rows and who changed what","url":"https://addaly.com/learn/data-and-sql/history-and-audit-tables","position":64,"of":66,"minutes":9,"answer":"An UPDATE destroys the old value and a DELETE destroys the row, so decide up front whether a table needs a deleted_at flag behind a filtering view, an audit table filled by a trigger.","keyIdea":"An UPDATE destroys the old value and a DELETE destroys the row, so decide up front whether a table needs a deleted_at flag behind a filtering view, an audit table filled by a trigger, or an effective-dated design for as-of questions."},"body":{"format":"markdown","text":"## An UPDATE destroys the old value\n\nA 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.\n\nMost businesses need some of the past. This lesson is the three ways to keep it, and what each costs.\n\n## Soft delete: the row stays, marked\n\n```sql\nALTER TABLE customers ADD COLUMN deleted_at timestamptz;\nUPDATE customers SET deleted_at = now() WHERE id = 812;   -- instead of DELETE\n```\n\nThe 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.\n\nTwo 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.\n\n## An audit table: what changed, when, by whom\n\nSoft delete keeps the row but not its earlier versions. An audit table keeps every version:\n\n```sql\nCREATE TABLE customers_audit (\n  audit_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  customer_id bigint NOT NULL,\n  op          text NOT NULL CHECK (op IN ('I', 'U', 'D')),\n  changed_at  timestamptz NOT NULL DEFAULT now(),\n  changed_by  text NOT NULL DEFAULT current_user,\n  old_row     jsonb,\n  new_row     jsonb\n);\n\nCREATE OR REPLACE FUNCTION customers_audit_fn() RETURNS trigger AS $$\nBEGIN\n  INSERT INTO customers_audit (customer_id, op, old_row, new_row)\n  VALUES (coalesce(NEW.id, OLD.id), left(TG_OP, 1), to_jsonb(OLD), to_jsonb(NEW));\n  RETURN NULL;\nEND $$ LANGUAGE plpgsql;\n\nCREATE TRIGGER customers_audit AFTER INSERT OR UPDATE OR DELETE ON customers\nFOR EACH ROW EXECUTE FUNCTION customers_audit_fn();\n```\n\nA 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.\n\nThe 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.\n\n## The minimum, on every table\n\n`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.\n\n## What history costs\n\nAn 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.\n\n## The habit\n\nBefore 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."},"check":{"question":"After adding a `deleted_at` column and switching to soft deletes, a customer who closed their account tries to sign up again with the same email and is refused because the address \"already exists\". What is the correct fix?","options":[{"text":"Hard-delete the old row after all, because a unique constraint and soft deletes are fundamentally incompatible.","correct":false,"tempting":"They are compatible with a partial index; hard deletion throws away the history the soft delete was introduced to keep."},{"text":"Drop the unique constraint on email, since the application layer can check for duplicates among live customers.","correct":false,"tempting":"Moves an invariant out of the database, where it is enforced for every writer, into code that only some paths run."},{"text":"Append the deletion timestamp to the old row's email so the address becomes free again, and strip it back off if the account is ever restored.","correct":false,"tempting":"Mutates historical data to work around a constraint, and every join or audit on the old email now fails to match."},{"text":"Replace the constraint with a partial unique index on email `WHERE deleted_at IS NULL`, so uniqueness is enforced only among live rows.","correct":true}]},"prev":{"slug":"json-columns","title":"JSON in a column: when it helps and what it costs","url":"https://addaly.com/learn/data-and-sql/json-columns"},"next":{"slug":"permissions-and-read-only","title":"Who may do what: roles, GRANT and a read-only door","url":"https://addaly.com/learn/data-and-sql/permissions-and-read-only"},"authorship":"Founder and series editor: Dr Mustafa Mun. Written by AI models to a written standard. Exam keys checked blind by a second model: 3,665 of 3,666 agreed. Academic review invited.","review":{"status":"Draft, open for academic review"},"cite":"Addaly (2026) 'Keeping history: soft deletes, audit rows and who changed what', in Data, SQL and Getting to the Answer. Edited by M. Mun. Addaly. Available at: https://addaly.com/learn/data-and-sql/history-and-audit-tables (Accessed: 13 October 2026).","licence":{"name":"CC BY-NC-SA 4.0","url":"https://creativecommons.org/licenses/by-nc-sa/4.0/"},"reader":{"schema":1,"title":"Keeping history: soft deletes, audit rows and who changed what","courseTitle":"Data, SQL and Getting to the Answer","module":{"index":6,"slug":"changing-data-safely","title":"Changing data safely"},"answer":null,"keyIdea":"An UPDATE destroys the old value and a DELETE destroys the row, so decide up front whether a table needs a deleted_at flag behind a filtering view, an audit table filled by a trigger, or an effective-dated design for as-of questions.","hero":{"kind":"plate","image":null,"alt":null,"caption":null},"blocks":[{"t":"h2","id":"an-update-destroys-the-old-value","runs":[{"text":"An UPDATE destroys the old value"}]},{"t":"p","runs":[{"text":"A customer’s address changes. "},{"text":"UPDATE customers SET address = ... WHERE id = 812","code":true},{"text":" 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 "},{"text":"DELETE","code":true},{"text":" is the same one step further: the row is gone, and with it the fact that it ever existed."}]},{"t":"p","runs":[{"text":"Most businesses need some of the past. This lesson is the three ways to keep it, and what each costs."}]},{"t":"h2","id":"soft-delete-the-row-stays-marked","runs":[{"text":"Soft delete: the row stays, marked"}]},{"t":"code","lang":"sql","label":"SQL","text":"ALTER TABLE customers ADD COLUMN deleted_at timestamptz;\nUPDATE customers SET deleted_at = now() WHERE id = 812;   -- instead of DELETE"},{"t":"p","runs":[{"text":"The row remains, with a timestamp saying when it was “deleted”. Every query that should not see deleted customers must now say "},{"text":"WHERE deleted_at IS NULL","code":true},{"text":", 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, "},{"text":"customers_live","code":true},{"text":", that has the filter built in, with the raw table read only by the code that needs the deleted rows."}]},{"t":"p","runs":[{"text":"Two things break quietly. A "},{"text":"UNIQUE (email)","code":true},{"text":" constraint now prevents a deleted customer’s email from ever being reused; the fix is a partial unique index, "},{"text":"CREATE UNIQUE INDEX ON customers (email) WHERE deleted_at IS NULL","code":true},{"text":", which enforces uniqueness only among the living. And a foreign key from "},{"text":"orders","code":true},{"text":" 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."}]},{"t":"h2","id":"an-audit-table-what-changed-when-by-whom","runs":[{"text":"An audit table: what changed, when, by whom"}]},{"t":"p","runs":[{"text":"Soft delete keeps the row but not its earlier versions. An audit table keeps every version:"}]},{"t":"code","lang":"sql","label":"SQL","text":"CREATE TABLE customers_audit (\n  audit_id    bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,\n  customer_id bigint NOT NULL,\n  op          text NOT NULL CHECK (op IN ('I', 'U', 'D')),\n  changed_at  timestamptz NOT NULL DEFAULT now(),\n  changed_by  text NOT NULL DEFAULT current_user,\n  old_row     jsonb,\n  new_row     jsonb\n);\n\nCREATE OR REPLACE FUNCTION customers_audit_fn() RETURNS trigger AS $$\nBEGIN\n  INSERT INTO customers_audit (customer_id, op, old_row, new_row)\n  VALUES (coalesce(NEW.id, OLD.id), left(TG_OP, 1), to_jsonb(OLD), to_jsonb(NEW));\n  RETURN NULL;\nEND $$ LANGUAGE plpgsql;\n\nCREATE TRIGGER customers_audit AFTER INSERT OR UPDATE OR DELETE ON customers\nFOR EACH ROW EXECUTE FUNCTION customers_audit_fn();"},{"t":"p","runs":[{"text":"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."}]},{"t":"p","runs":[{"text":"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 "},{"text":"valid_from","code":true},{"text":" and "},{"text":"valid_to","code":true},{"text":" on each version. Many systems keep both: the effective-dated table for queries, the audit log for accountability."}]},{"t":"h2","id":"the-minimum-on-every-table","runs":[{"text":"The minimum, on every table"}]},{"t":"p","runs":[{"text":"created_at","code":true},{"text":" and "},{"text":"updated_at","code":true},{"text":", both "},{"text":"timestamptz NOT NULL DEFAULT now()","code":true},{"text":", with a trigger or application rule that sets "},{"text":"updated_at","code":true},{"text":" 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."}]},{"t":"h2","id":"what-history-costs","runs":[{"text":"What history costs"}]},{"t":"p","runs":[{"text":"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."}]},{"t":"h2","id":"the-habit","runs":[{"text":"The habit"}]},{"t":"p","runs":[{"text":"Before the first "},{"text":"UPDATE","code":true},{"text":" or "},{"text":"DELETE","code":true},{"text":" on a table, decide which of the three it needs: nothing, a "},{"text":"deleted_at","code":true},{"text":", or a full audit trail. The decision is much cheaper to make before the history you wanted has already been overwritten."}]}],"check":{"question":[{"text":"After adding a "},{"text":"deleted_at","code":true},{"text":" column and switching to soft deletes, a customer who closed their account tries to sign up again with the same email and is refused because the address “already exists”. What is the correct fix?"}],"options":[{"text":[{"text":"Hard-delete the old row after all, because a unique constraint and soft deletes are fundamentally incompatible."}],"correct":false,"tempting":[{"text":"They are compatible with a partial index; hard deletion throws away the history the soft delete was introduced to keep."}]},{"text":[{"text":"Drop the unique constraint on email, since the application layer can check for duplicates among live customers."}],"correct":false,"tempting":[{"text":"Moves an invariant out of the database, where it is enforced for every writer, into code that only some paths run."}]},{"text":[{"text":"Append the deletion timestamp to the old row’s email so the address becomes free again, and strip it back off if the account is ever restored."}],"correct":false,"tempting":[{"text":"Mutates historical data to work around a constraint, and every join or audit on the old email now fails to match."}]},{"text":[{"text":"Replace the constraint with a partial unique index on email "},{"text":"WHERE deleted_at IS NULL","code":true},{"text":", so uniqueness is enforced only among live rows."}],"correct":true,"tempting":null}],"order":[2,3,0,1],"why":null},"prev":{"slug":"json-columns","title":"JSON in a column: when it helps and what it costs","minutes":9},"next":{"slug":"permissions-and-read-only","title":"Who may do what: roles, GRANT and a read-only door","minutes":8}}}