Skip to content
Tutorials Center
My learning

Topics

Lesson 85 of 90 ยท Projects

Project: Audit Log with Triggers

Build a generic audit trail in PostgreSQL: one PL/pgSQL trigger function, who/when/what columns, JSONB diffs, history queries and a retention plan.

"Who changed this customer's email address, and what was it before?" Sooner or later someone asks that question, and the answer is either in your audit log or nowhere. An audit trail records every change to the tables you care about: which row, what operation, who did it, when, and the values before and after. Done in the database with triggers, it also catches changes made by scripts, admin tools and hand-typed SQL, not just the changes your application code remembers to log.

In this project you design the log table, write one generic trigger function that works for any table, compute readable diffs, query the history, and plan retention so the log does not eat your disk. It builds on Triggers, PL/pgSQL Basics and JSON and JSONB.

What runs live. SQLite supports triggers, so the audit idea is demonstrated live with one trigger per table and json_object for the before and after values. SQLite has no PL/pgSQL, no TG_TABLE_NAME and no generic trigger functions, so the PostgreSQL version is shown read-only. The history queries, diffs and retention steps run live with SQLite JSON functions.

What to record

The five questions

Which row? Table name and primary key.
What action? INSERT, UPDATE or DELETE.
Who? The app user, not just the DB role.
When? A timestamp set by the database.
1 / 2

Storing whole rows as jsonb has one big advantage: the log keeps working when you add or drop columns on the audited table. A "column per audited column" design would need a migration for every schema change.

The PostgreSQL design

SQL
-- PostgreSQL (read-only)
CREATE TABLE audit_log (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  table_name text        NOT NULL,
  row_pk     text        NOT NULL,
  action     text        NOT NULL CHECK (action IN ('INSERT', 'UPDATE', 'DELETE')),
  changed_at timestamptz NOT NULL DEFAULT now(),
  changed_by text        NOT NULL DEFAULT coalesce(current_setting('app.user', true), session_user),
  old_data   jsonb,
  new_data   jsonb
);

CREATE INDEX audit_log_row_idx  ON audit_log (table_name, row_pk, id);
CREATE INDEX audit_log_time_idx ON audit_log USING brin (changed_at);

Two details deserve attention. First, changed_by defaults to a custom setting, app.user, and falls back to the database role. A web application usually connects as one shared role, so session_user would say "app" for every change. Your application sets the real user at the start of each transaction with SET LOCAL app.user = 'alice' (or SELECT set_config('app.user', 'alice', true)), and the trigger picks it up. The second argument true of current_setting means "return NULL instead of an error if the setting does not exist". Second, the BRIN index on changed_at is tiny and suits an append-only table whose timestamps grow in step with physical order (see BRIN, Hash and SP-GiST Indexes).

One trigger function for every table

Because PL/pgSQL triggers know the table they fired on (TG_TABLE_NAME) and the operation (TG_OP), and to_jsonb(OLD) converts any row to JSON, a single function can audit any table. The primary key column name is passed as a trigger argument:

SQL
-- PostgreSQL (read-only)
CREATE FUNCTION audit_row() RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = pg_catalog, public
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    INSERT INTO audit_log (table_name, row_pk, action, new_data)
    VALUES (TG_TABLE_NAME, to_jsonb(NEW) ->> TG_ARGV[0], 'INSERT', to_jsonb(NEW));
    RETURN NEW;

  ELSIF TG_OP = 'UPDATE' THEN
    IF to_jsonb(OLD) = to_jsonb(NEW) THEN
      RETURN NEW;                       -- nothing really changed
    END IF;
    INSERT INTO audit_log (table_name, row_pk, action, old_data, new_data)
    VALUES (TG_TABLE_NAME, to_jsonb(NEW) ->> TG_ARGV[0], 'UPDATE', to_jsonb(OLD), to_jsonb(NEW));
    RETURN NEW;

  ELSE  -- DELETE
    INSERT INTO audit_log (table_name, row_pk, action, old_data)
    VALUES (TG_TABLE_NAME, to_jsonb(OLD) ->> TG_ARGV[0], 'DELETE', to_jsonb(OLD));
    RETURN OLD;
  END IF;
END;
$$;

CREATE TRIGGER accounts_audit
AFTER INSERT OR UPDATE OR DELETE ON accounts
FOR EACH ROW EXECUTE FUNCTION audit_row('id');

CREATE TRIGGER invoices_audit
AFTER INSERT OR UPDATE OR DELETE ON invoices
FOR EACH ROW EXECUTE FUNCTION audit_row('invoice_no');

Why AFTER and not BEFORE? An AFTER trigger runs once the row has passed every constraint and every BEFORE trigger, so the log records what really happened, and a failed insert never leaves a phantom log entry. Because both the change and the log row are written in the same transaction, they commit or roll back together. SECURITY DEFINER lets the function write to audit_log even if the application role has no privileges on it, and the pinned search_path guards against the hijacking attack described in SQL Injection and Safe Queries in PostgreSQL.

TRUNCATE does not fire row-level triggers. If it matters, add a statement-level BEFORE TRUNCATE trigger that logs a single entry, or simply revoke TRUNCATE from the application role.

The same idea, live

SQLite triggers cannot be generic, so the live version writes one set of triggers per table and builds the JSON by hand with json_object. The "current user" lives in a one-row table that the application would update at the start of each request. The WHEN clause on the update trigger skips updates that change nothing, the same job as the IF to_jsonb(OLD) = to_jsonb(NEW) check above:

SQL Audit triggers that record who, what and the before/after values

Press Run (or Ctrl + Enter) to see the result here.

The log has five entries: two inserts and one update by alice, then one update and one delete by bob. The no-op update of Ann's email created no entry, and the update of Bo's row recorded both changed columns in a single entry. Each entry came from the same transaction as the change itself, so the log can never disagree with the data.

Reading the history

Whole-row JSON is complete but tiring to read. The useful view for a human is the diff: only the keys whose value changed. In PostgreSQL you compute it with a small SQL function that compares the two objects key by key:

SQL
-- PostgreSQL (read-only)
CREATE FUNCTION jsonb_diff(a jsonb, b jsonb) RETURNS jsonb
LANGUAGE sql IMMUTABLE AS $$
  SELECT coalesce(jsonb_object_agg(k, jsonb_build_object('old', a -> k, 'new', b -> k)), '{}'::jsonb)
  FROM (SELECT jsonb_object_keys(coalesce(a, '{}')) AS k
        UNION
        SELECT jsonb_object_keys(coalesce(b, '{}'))) keys
  WHERE a -> k IS DISTINCT FROM b -> k
$$;

-- the full history of one account, newest first
SELECT id, changed_at, changed_by, action, jsonb_diff(old_data, new_data) AS changes
FROM audit_log
WHERE table_name = 'accounts' AND row_pk = '2'
ORDER BY id DESC;

The live version below uses json_each to do the same job. It starts from a prepared log (with fixed timestamps so the results are repeatable) and runs three typical queries: the diff for one row, the number of changes per user, and the state of every row as of a point in time, which works by taking the newest new_data before that moment for each row:

SQL History queries: diffs, activity per user, state as of a date

Press Run (or Ctrl + Enter) to see the result here.

The diff query lists exactly three changes: row 1's balance from 100 to 120, and row 2's email and balance. The "as of" query returns account 1 with balance 120 and account 2 with its original values, because the later changes happened after the cutoff. Row 1 would disappear from an "as of March 5" query: its newest entry is a DELETE, which the action <> 'DELETE' filter excludes. This "latest entry per key" pattern is the heart of every temporal query over a change log.

Hardening the log

An audit log is only worth trusting if nobody can quietly edit it:

SQL
-- PostgreSQL (read-only)
REVOKE ALL ON audit_log FROM app_role;            -- the app can neither read nor write it directly
GRANT SELECT ON audit_log TO auditor_role;        -- read-only for people who review it

CREATE FUNCTION audit_log_immutable() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  RAISE EXCEPTION 'audit_log is append-only';
END;
$$;

CREATE TRIGGER audit_log_no_change
BEFORE UPDATE OR DELETE ON audit_log
FOR EACH ROW EXECUTE FUNCTION audit_log_immutable();

Because the audit function is SECURITY DEFINER, the application can cause log entries without being able to touch them. A determined superuser can still bypass any of this, which is why high-assurance systems also ship the log to separate storage; the pgaudit extension, mentioned in Authentication, TLS and Encryption, covers statement-level auditing for compliance.

Other practical points:

  • Mask sensitive columns. to_jsonb(NEW) copies everything, including password hashes and card tokens. Remove keys with to_jsonb(NEW) - 'password_hash', or audit only selected columns.
  • Mind the cost. Every audited write becomes two writes. Bulk updates of a million rows produce a million log rows. For such jobs consider a statement-level trigger with transition tables, which can log a summary.
  • Index for your questions. The (table_name, row_pk, id) index answers "history of this row". Add (changed_by, changed_at) only if you really query by user.

Retention

An audit log grows forever unless you plan for it. Keep recent entries in the main table, move older ones to cheaper storage, and delete them when the law or your policy allows. In PostgreSQL the cleanest tool is Declarative Partitioning: partition audit_log by month on changed_at, and retiring a month becomes ALTER TABLE ... DETACH PARTITION followed by a dump to object storage and DROP TABLE, which is instant and creates no dead rows (a DELETE of millions of rows would). Without partitioning you archive and delete in batches. The live example does it in one transaction, so a failure cannot lose entries:

SQL Archive old audit entries, then delete them

Press Run (or Ctrl + Enter) to see the result here.

Three entries from 2025 move to the archive and two remain live. In production the archive would be a separate table or an object-storage file rather than a sibling table in the same database, and you would run the move in batches of a few thousand rows to keep transactions short.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why does the audit trigger use AFTER instead of BEFORE?

    An AFTER row trigger runs once the row change is final, so the log records what actually happened. A failed insert never leaves a phantom entry, and the log row shares the transaction of the change.
  2. 2. A web app connects with one shared database role. How can the audit log still record the real end user?

    A transaction-scoped custom setting carries the application user into the trigger. The second argument true makes current_setting return NULL instead of an error when the setting is missing.
  3. 3. What does the ROW_NUMBER() ... ORDER BY id DESC query with rn = 1 produce?

    Numbering entries per row from newest to oldest and keeping number 1 selects the latest state at or before the cutoff. Rows whose latest entry is a DELETE are filtered out.
  4. 4. What is the main advantage of dropping an old monthly partition over running DELETE on the same rows?

    Dropping or detaching a partition is a metadata operation. A DELETE of millions of rows writes a lot of WAL and leaves dead tuples that VACUUM must clean up.

Summary

  • An audit table records table, row key, action, time, user, and the before and after rows as JSONB, so schema changes do not break it.
  • One generic PL/pgSQL AFTER trigger function using TG_TABLE_NAME, TG_OP, TG_ARGV and to_jsonb(OLD/NEW) can audit any table.
  • Carry the application user into the trigger with a custom setting such as app.user.
  • Diffs come from comparing the two JSON objects key by key; "state as of" queries take the latest entry per row before a cutoff.
  • Protect the log: revoke direct access, use SECURITY DEFINER, block updates and deletes, and mask sensitive columns.
  • Plan retention up front; monthly partitions make dropping old data instant.

Next: Project: Job Queue with SKIP LOCKED.