What are Postgres audit log triggers and how do they work?

A Postgres audit log trigger is a row-level AFTER INSERT OR UPDATE OR DELETE trigger that fires once for every row a statement changes. A single generic trigger function, attached to each audited table, reads the OLD and NEW row records, converts them to jsonb, computes a diff of the changed columns, and inserts one row into a shared, append-only audit table. Because the table name, operation, and row data all come from the trigger's context, one function serves every table.

The trigger runs inside the same transaction as the write that fired it. If the transaction commits, the audit row commits with it. If it rolls back, both disappear. You never get a change without its history, or history for a change that never happened.

Consider the write paths in a typical monolith:

  • The application's ORM saving a model
  • A schema migration that backfills a column
  • A nightly cron job issuing a bulk UPDATE
  • An engineer fixing data by hand in psql

Model callbacks and service-layer hooks only see the first path. The trigger sees all four, because every one of them ends up modifying rows in the same table. One known gap is TRUNCATE, which does not fire row-level triggers.

This pattern fits monoliths where many code paths share one Postgres database and a complete change history is worth more than raw write throughput. Every audited write performs a second insert, and you accept that cost on purpose.

+-------------+ +-------------+ +-------------+ +-------------+
|  App / ORM  | |  Migration  | |  Cron job   | |    psql     |
+------+------+ +------+------+ +------+------+ +------+------+
       |               |               |               |
       +---------------+-------+-------+---------------+
                               |
                               | INSERT / UPDATE / DELETE
                               v
+----------------------------------------------------------------------+
| Audited table (e.g. orders, users, invoices)                         |
| trigger: AFTER INSERT OR UPDATE OR DELETE FOR EACH ROW               |
+------------------------------+---------------------------------------+
                               | fires once per changed row
                               v
+----------------------------------------------------------------------+
| Generic audit trigger function (one function, every table)           |
|  - table name + operation from trigger context (TG_TABLE_NAME, TG_OP)|
|  - before = to_jsonb(OLD)     after = to_jsonb(NEW)                  |
|  - diff  = only the columns whose values changed                     |
+------------------------------+---------------------------------------+
                               | INSERT, same transaction as the write
                               v
+----------------------------------------------------------------------+
| audit_log  (single shared table, append-only)                        |
|  table_name | op | before jsonb | after jsonb | diff jsonb | when    |
+----------------------------------------------------------------------+
  COMMIT   -> the write and its audit row persist together
  ROLLBACK -> neither persists  |  TRUNCATE: no row trigger, not logged

Every write path, whether ORM, migration, cron job or psql, modifies rows in the audited table, so one generic AFTER row trigger records before, after and diff snapshots into a single append-only audit table within the same transaction.

How should you design the audit table?

Keep one audit table for the whole database rather than one per audited table. A single table lets one trigger function serve every table and lets you query history across entities. The CREATE TABLE statement in the accompanying example defines these columns:

Column Type Source Why it exists
id bigint identity sequence Stable row identity and insert order
schema_name, table_name text TG_TABLE_SCHEMA, TG_TABLE_NAME Distinguishes same-named tables in different schemas
operation text TG_OP INSERT, UPDATE or DELETE
record_pk text row's primary key Text accepts integer, UUID and composite keys alike
old_data, new_data jsonb OLD, NEW Full row snapshots. Null on insert and delete respectively
changed_fields jsonb computed diff Only the keys that changed, so readers skip scanning snapshots
txid bigint txid_current() Groups rows written by one transaction
changed_at timestamptz clock_timestamp() Wall-clock time of the write
actor, request_id text, nullable session settings Who or what caused the change

The timestamp default matters. now() returns the transaction's start time, so every change in a long-running transaction gets the same value. A batch job that runs for ten minutes would record all of its writes at the same instant. clock_timestamp() returns the actual time of each write, which preserves the order of changes within a transaction. If you also need the transaction start time, read it from now() when you query.

The main index is on (table_name, record_pk, changed_at). It serves the most common query: the history of one entity, in time order. Add a separate index on txid only if you regularly reconstruct whole transactions.

CREATE SCHEMA IF NOT EXISTS audit;

-- One audit table for the whole database; a single trigger function writes here.
CREATE TABLE audit.log (
    id             bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    schema_name    text        NOT NULL,  -- TG_TABLE_SCHEMA
    table_name     text        NOT NULL,  -- TG_TABLE_NAME
    operation      text        NOT NULL   -- TG_OP
                   CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    record_pk      text        NOT NULL,  -- text fits integer, UUID and composite keys
    old_data       jsonb,                 -- NULL on INSERT
    new_data       jsonb,                 -- NULL on DELETE
    changed_fields jsonb,                 -- only the keys whose values changed
    txid           bigint      NOT NULL DEFAULT txid_current(),
    -- clock_timestamp() is the time of this write; now() would be the
    -- transaction start time, identical for every row in a long batch.
    changed_at     timestamptz NOT NULL DEFAULT clock_timestamp(),
    -- Set per request by the application, e.g. SET LOCAL app.actor = 'user:42'.
    -- The second argument (missing_ok = true) yields NULL when the setting is absent.
    actor          text        DEFAULT current_setting('app.actor', true),
    request_id     text        DEFAULT current_setting('app.request_id', true)
);

-- Main access path: the history of one entity, in time order.
CREATE INDEX log_entity_history_idx
    ON audit.log (table_name, record_pk, changed_at);

-- Optional: only if you look up everything a single transaction changed.
CREATE INDEX log_txid_idx ON audit.log (txid);

-- Example read: one row's history, with transaction start time recovered at query time.
-- SELECT operation, changed_fields, actor, changed_at
-- FROM audit.log
-- WHERE table_name = 'orders' AND record_pk = '1001'
-- ORDER BY changed_at;

The audit table DDL: one shared table, a clock_timestamp() default so writes inside a long transaction keep their real order, actor and request-id defaults taken from session settings, and the (table_name, record_pk, changed_at) index for per-entity history.

How do you write a generic audit trigger function with a jsonb diff?

The function is generic because it never names a column. to_jsonb(OLD) and to_jsonb(NEW) turn whichever row fired the trigger into a JSON object. The function then branches on TG_OP:

  • INSERT stores to_jsonb(NEW) as the new state. Do not reference OLD in this branch.
  • DELETE stores to_jsonb(OLD). This is the final snapshot of the row, so a deleted entity can still be reconstructed.
  • UPDATE computes a diff. Iterate over jsonb_each(new_row) and keep each key whose value IS DISTINCT FROM the same key in the old row. Use IS DISTINCT FROM rather than <> because it treats a change to or from NULL as a change. Collect the matching keys with jsonb_object_agg, recording old and new values for each.

No-op updates. If the diff is empty, return before inserting anything. ORMs often save whole objects even when nothing changed. Skipping these updates keeps such writes out of the change history.

Excluded columns. Strip excluded columns before diffing, using the jsonb - text[] operator. Otherwise an update that only bumps updated_at still produces an audit row.

Trigger arguments. Pass the excluded columns and the redacted columns as trigger arguments. They arrive in TG_ARGV as text, for example 'updated_at' and 'password_hash,api_token'. If many tables need the same settings, read them from a small config table keyed by table name instead.

Redaction. Replace redacted values with a fixed marker such as "[redacted]" instead of dropping them. The audit table then shows that a credential changed without storing its value.

Attaching the trigger. Attach the function with AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW. Use AFTER so the trigger sees the final row, after any BEFORE triggers have modified it, and so it audits only writes that actually happened. Use FOR EACH ROW so each changed row gets its own record. The return value of an AFTER trigger is ignored, so return NULL. Use TG_TABLE_SCHEMA and TG_TABLE_NAME to fill the table-name column.

CREATE TABLE audit_log (id bigserial PRIMARY KEY, table_name text NOT NULL, op text NOT NULL,
  changes jsonb NOT NULL, changed_at timestamptz NOT NULL DEFAULT now());

CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE
  excluded text[]; redacted text[]; old_row jsonb; new_row jsonb; payload jsonb; k text;
BEGIN
  -- TG_ARGV[0] = columns to ignore, TG_ARGV[1] = columns to redact (missing args are NULL)
  excluded := coalesce(string_to_array(TG_ARGV[0], ','), '{}');
  redacted := coalesce(string_to_array(TG_ARGV[1], ','), '{}');
  -- Only touch OLD/NEW when they exist for this operation; strip noisy columns first
  IF TG_OP IN ('UPDATE', 'DELETE') THEN old_row := to_jsonb(OLD) - excluded; END IF;
  IF TG_OP IN ('INSERT', 'UPDATE') THEN new_row := to_jsonb(NEW) - excluded; END IF;

  IF TG_OP = 'UPDATE' THEN
    -- Diff on real values so a changed secret is still detected, then mask it in the output
    SELECT jsonb_object_agg(n.key, jsonb_build_object(
             'old', CASE WHEN n.key = ANY (redacted) THEN '"[redacted]"'::jsonb ELSE old_row -> n.key END,
             'new', CASE WHEN n.key = ANY (redacted) THEN '"[redacted]"'::jsonb ELSE n.value END))
      INTO payload
      FROM jsonb_each(new_row) AS n
     WHERE n.value IS DISTINCT FROM old_row -> n.key;
    IF payload IS NULL THEN RETURN NULL; END IF;  -- no-op update: write nothing
  ELSE
    -- INSERT stores the new row, DELETE stores the final snapshot
    payload := coalesce(new_row, old_row);
    FOREACH k IN ARRAY redacted LOOP
      IF payload ? k THEN payload := jsonb_set(payload, ARRAY[k], '"[redacted]"'); END IF;
    END LOOP;
  END IF;

  INSERT INTO audit_log (table_name, op, changes) VALUES (TG_TABLE_NAME, TG_OP, payload);
  RETURN NULL;  -- return value is ignored for AFTER row triggers
END;
$$;

CREATE TRIGGER users_audit AFTER INSERT OR UPDATE OR DELETE ON users
  FOR EACH ROW EXECUTE FUNCTION audit_row_change('updated_at', 'password_hash,api_token');

A generic PL/pgSQL audit trigger that snapshots inserts and deletes, stores a per-column jsonb diff for updates, skips no-op updates, ignores columns listed in the first trigger argument and redacts columns listed in the second.

How do you record which user or request caused a change?

The database has no idea which application user is behind a connection. Every request in a monolith typically connects as the same role, so current_user in the trigger tells you "the app did it" and nothing more. The application has to pass that context in explicitly.

At the start of each request transaction, call set_config('app.user_id', '42', true). The third argument makes the value transaction-local, so it is discarded at COMMIT or ROLLBACK. SET LOCAL app.user_id = '42' does the same thing, but set_config is an ordinary function, so you can pass the value as a bind parameter instead of building SQL strings.

In the trigger, read the value with current_setting('app.user_id', true). The second argument stops an error being raised when the setting was never defined. Wrap the call in NULLIF(..., ''), because once a session has used the setting, it can come back as an empty string instead of NULL after the transaction ends. Cron jobs, migrations, and engineers in psql won't set it at all. Store NULL for the user, and also record session_user so those writes can still be attributed to a database role. Don't make the trigger fail on missing context, or you will block legitimate maintenance work.

Avoid plain session-level SET. Under transaction-mode connection pooling, each transaction may run on a different server connection. A session setting stays on that server connection after your transaction finishes, and the next client's transaction inherits your user id.

Set app.request_id and an optional app.reason alongside the user id, and store txid_current() in each audit row. Together these let you group every row a single request changed across multiple tables, and explain why the change happened.

-- Trigger function: read request context, tolerate its absence
CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
  INSERT INTO audit_log
    (table_name, op, row_data, app_user_id, db_role, request_id, reason, txid)
  VALUES (
    TG_TABLE_NAME,
    TG_OP,
    CASE WHEN TG_OP = 'DELETE' THEN to_jsonb(OLD) ELSE to_jsonb(NEW) END,
    -- missing_ok = true: NULL instead of an error when never set;
    -- NULLIF: a previously used setting can read back as ''
    NULLIF(current_setting('app.user_id', true), '')::bigint,
    session_user,  -- still attributes cron/psql writes to a DB role
    NULLIF(current_setting('app.request_id', true), ''),
    NULLIF(current_setting('app.reason', true), ''),
    txid_current() -- groups rows across tables changed in one transaction
  );
  RETURN NULL;  -- AFTER trigger: return value is ignored
END;
$$;

CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_row_change();

-- What the app runs per request (literals stand in for bind parameters)
BEGIN;
SELECT set_config('app.user_id',    '42',           true),  -- true = transaction-local
       set_config('app.request_id', 'req-7f3c9a1e', true),
       set_config('app.reason',     'checkout',     true);
UPDATE orders SET status = 'paid' WHERE id = 1001;
COMMIT;  -- settings are discarded here, so a pooled connection carries nothing over

An audit trigger that reads transaction-local user, request and reason settings, falls back to NULL plus session_user when they are missing, and records txid_current() so changes made by one request can be correlated across tables.

How do you protect the audit table and query change history?

Start by taking write access away from the application. The table should be owned by a dedicated role that the app never logs in as. The application role gets SELECT at most (often not even that), and you explicitly REVOKE INSERT, UPDATE, DELETE, TRUNCATE from it. If an attacker or a buggy migration runs as the app role, it can no longer rewrite history.

That raises a question: how does the trigger insert rows if the app role can't? Declare the trigger function SECURITY DEFINER and have the audit owner role own it. The function then runs with the owner's privileges, while the app role still has no direct INSERT path.

A SECURITY DEFINER function needs a fixed search_path, set with SET search_path = pg_catalog, pg_temp on the function itself. Also schema-qualify the audit table inside the function body. Without this, unqualified names resolve against the caller's search path. A caller who can create objects in any schema that appears earlier in that path can define their own audit_log table, or a function or operator with a matching name. The privileged function would then use the attacker's object while running with the owner's privileges. Putting pg_temp last closes the temporary-schema variant of the same attack.

For reading, the most common query is one entity's full history. Filter on table name and primary key, then order by the audit row's id or timestamp. A B-tree index on (table_name, entity_id, changed_at) covers it.

The second common query is "who changed email, and when." Filter with diff ? 'email' and project diff -> 'email' along with the user id and timestamp. Add a GIN index on diff only if that query is slow in practice. It makes every insert more expensive. Use the default jsonb_ops operator class, because jsonb_path_ops doesn't support the ? operator.

How do you roll out audit triggers to an existing monolith?

Treat audit triggers like any other schema object: create them in versioned migrations, never by hand in a console. A trigger added manually on one environment and forgotten on another produces gaps in change history that nobody notices until an investigation needs the missing rows.

Attach triggers with a helper

Write a small SQL function, for example audit.enable_for(regclass[]), that loops over the tables it is given and uses dynamic SQL to drop and recreate the audit trigger on each. Taking regclass makes Postgres reject misspelled table names when the migration runs. Dropping before creating makes the call idempotent, so re-running a migration or adding tables later is a one-line change. Roll out in batches, starting with low-write tables, so you can watch insert latency and audit table growth before covering hot paths.

Test inside a transaction

For each audited table, open a transaction, perform an insert, update and delete, then assert that the audit table contains exactly the expected rows, operations and diff keys. Roll back at the end so the test leaves nothing behind. Run these in CI against the migrated schema.

Plan for schema changes

Because rows store jsonb snapshots, added and dropped columns cause no errors: old rows simply lack or keep the key. Renames and type changes are different. History will show email before a date and email_address after it, or a value that changes from a string to a number. Consumers have to map these explicitly, so record such migrations in a changelog that lives next to the audit schema.

Weigh the tradeoff

You gain a lot of engineering time. There are no per-model hooks to write or keep in sync, and new write paths are covered automatically, including raw SQL, background jobs and admin scripts. The cost is that triggers are invisible from application code. Document which tables are audited and why, or the next engineer will be surprised by side effects they cannot find in the codebase.

When should you avoid audit triggers, and what are the alternatives?

Triggers are not free. Every audited write adds an insert to the same transaction, so hot tables pay extra latency and generate extra WAL. A bulk statement makes this obvious. As an illustrative estimate, a 1-million-row UPDATE produces 1 million audit rows, all committed or rolled back together. Storage grows just as fast. Plan a retention policy, partition the audit table by time range on changed_at so old partitions can be dropped instead of deleted, or store only the jsonb diff rather than full row images.

Coverage has gaps too. Row-level triggers do not fire on TRUNCATE, and a session that sets session_replication_role = replica skips ordinary triggers entirely. Triggers also record what changed in a row, not why. "Price updated" is not the same as "discount campaign applied."

Approach Completeness Actor context Write-path cost Infra / dev cost
Audit triggers All row-level DML, any client Only via session settings Synchronous, in-transaction Low infra, moderate SQL
Application event logging Only code paths that emit events Full business intent Low to moderate High dev discipline
Logical decoding / CDC All committed changes, read from WAL Weak unless written alongside data Near zero on the write path Needs wal_level = logical, slots, consumers

Use triggers when you need tamper-resistant row history across every client and write volume is moderate. Use application events when auditors care about intent. Use CDC when write latency matters most, or when changes already have to flow to other systems.

How OneEnv can help

Disclosure: OneEnv is the product behind this blog.

OneEnv (oneenv.io) is a workspace-based governance platform for the things services share — environment variables, database structure, API routes and validation rules. A change to a shared value is staged on a branch, reviewed once, and then every affected service explicitly approves or rejects it before it goes live, so you can see who a change touches before it ships instead of finding out in production.

Applied to postgres audit log triggers, this is where it helps:

Saves time

  • Every service that inherits a shared value is listed in one place, so nobody searches repos and chats to find out who is affected.
  • One review per branch replaces coordinating each team separately; each service still keeps its own approve/reject.
  • Changing a shared variable is one edit and one review instead of editing every service's .env by hand.

Saves cost

  • Fewer production incidents and rollbacks caused by a shared value, route or column changing under a consumer unannounced.
  • Less duplicated configuration to maintain: one shared definition inherited by many services.
  • A wrong or stale value is caught at review time by the services it affects, not by a failed deploy.
  • OneEnv has a free plan (1 workspace, unlimited services and members, full governance), so trying it costs nothing.

These are qualitative benefits, not measured results — how much you save depends on how many services share the value.

Want to try it? Start at oneenv.io.