What is trigger-based audit logging in Postgres, and why use it in a monolith?
Trigger-based audit logging moves change capture out of application code and into the database. You attach one generic AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW trigger to each table you want tracked. For every affected row, the trigger function writes a record to a single change history table. That record holds the table name, the operation, a timestamp, and JSONB snapshots of the row before (OLD) and after (NEW) the change.
In a monolith, this beats calling an audit helper from application code. Application-level audit calls only cover the paths that remember to make them. They miss ORM bulk updates that skip model hooks, raw SQL in reporting code, background jobs, migrations that backfill data, and the fix someone typed into a production console at 2 a.m. A row-level trigger fires for all of these because it sits below them. The audit row is also written inside the same transaction as the change. If the business write rolls back, the audit entry rolls back with it, so the history never records changes that didn't happen.
┌──────────────────── one transaction ─────────────────────┐
│ app request │
│ -> middleware: SET LOCAL app.user_id = '42' │
│ -> business write: UPDATE orders SET status = ... │
│ -> AFTER ROW trigger reads app.user_id │
│ -> INSERT INTO audit.change_history (...) │
└──────────────────────── COMMIT ──────────────────────────┘
This approach suits teams running a single Postgres-backed monolith that need a complete audit trail for compliance, customer support, or debugging. It also saves engineering time. Nobody has to write per-feature audit code, and nobody can forget to. Reviewers check one trigger function instead of hunting for audit calls across the codebase.
┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐ ┌──────────┐
│ web req +│ │ ORM bulk │ │ raw SQL │ │ bg job / │ │ console │
│ SET LOCAL│ │ update │ │ report │ │ migration│ │ hotfix │
└────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘ └────┬─────┘
│ │ │ │ │
└────────────┴────────────┼────────────┴────────────┘
│ INSERT / UPDATE / DELETE
┌──────────────────────────────┼─ one transaction: BEGIN ... COMMIT ┐
│ ▼ │
│ ┌───────────────────────────┐ │
│ │ tracked table: orders │ │
│ └─────────────┬─────────────┘ │
│ │ fires once per row │
│ ▼ │
│ ┌──────────────────────────────────────────────┐ │
│ │ AFTER INSERT OR UPDATE OR DELETE │ │
│ │ FOR EACH ROW EXECUTE FUNCTION audit_row() │ │
│ │ reads current_setting('app.user_id', true) │ │
│ └──────────────────────┬───────────────────────┘ │
│ ▼ INSERT one row │
│ ┌──────────────────────────────────────────────┐ │
│ │ audit.change_history │ │
│ │ table_name, op, changed_at, user_id, │ │
│ │ old_row JSONB, new_row JSONB │ │
│ └──────────────────────────────────────────────┘ │
│ │
│ ROLLBACK undoes the business write and its audit row together │
└────────────────────────────────────────────────────────────────────┘
Every write path, from web requests to console hotfixes, reaches the tracked table. A row-level AFTER trigger then writes a JSONB before/after snapshot to audit.change_history within the same transaction, so the audit row commits or rolls back with the change.
How should you design the change history table?
Every audit row answers the same questions: what changed, where, when, in which transaction, and who did it. The columns map directly to those questions:
schema_name,table_name: where the change happened. Store both, because the same table name can exist in more than one schema.operation:INSERT,UPDATEorDELETE, enforced with aCHECKconstraint.row_pk: the changed row's primary key as text. Text handles integer, UUID and composite keys alike.old_data,new_data:JSONBsnapshots.old_datais null on insert, andnew_datais null on delete.JSONBalso lets you diff and index the snapshots later.changed_at:timestamptz, defaulting tonow(). Every row from one transaction then gets the same timestamp.txid: fromtxid_current(). Use it to group every row a single request touched.app_user_id,db_user: the application actor, plussession_useras a fallback when no application context is set.
Use one generic table rather than per-table shadow tables:
| Concern | Generic table | Per-table shadow tables |
|---|---|---|
| Trigger code | One function | One per table, or generated |
| Source schema changes | Absorbed by JSONB | Shadow table must be migrated too |
| Cross-entity queries | Single WHERE |
UNION across many tables |
| Column-level typing | Lost, lives inside JSON | Preserved |
The statement below declares the table PARTITION BY RANGE (changed_at). Audit data only grows, and partitioning lets you enforce retention by detaching or dropping old partitions. Deleting millions of rows from a single table is far more expensive. The primary key must include the partition column, so it is (id, changed_at).
-- Generic change history table, partitioned by month on changed_at.
CREATE TABLE audit_log (
id bigint GENERATED ALWAYS AS IDENTITY,
schema_name text NOT NULL,
table_name text NOT NULL,
operation text NOT NULL
CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
row_pk text NOT NULL, -- text covers int, uuid and composite keys
old_data jsonb, -- null on INSERT
new_data jsonb, -- null on DELETE
changed_at timestamptz NOT NULL DEFAULT now(), -- same value for the whole transaction
txid bigint NOT NULL DEFAULT txid_current(),
app_user_id text, -- set by the application, may be null
db_user text NOT NULL DEFAULT session_user, -- fallback actor
-- On a partitioned table the primary key must include the partition column.
PRIMARY KEY (id, changed_at)
) PARTITION BY RANGE (changed_at);
-- One partition per month. Create these ahead of time, for example from a scheduled job.
CREATE TABLE audit_log_2026_11 PARTITION OF audit_log
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE audit_log_2026_12 PARTITION OF audit_log
FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
-- Indexes declared on the parent are created on every partition.
CREATE INDEX ON audit_log (schema_name, table_name, row_pk);
CREATE INDEX ON audit_log (txid);
-- Retention: detach the old month, then archive or drop it.
-- ALTER TABLE audit_log DETACH PARTITION audit_log_2026_11;
-- DROP TABLE audit_log_2026_11;
Creates the generic audit_log table partitioned by range on changed_at, adds monthly partitions and lookup indexes, and shows how to enforce retention by detaching and dropping a partition.
How does the generic audit trigger function work?
One PL/pgSQL function serves every audited table because it reads its context from trigger variables. TG_OP gives the operation, and TG_TABLE_SCHEMA and TG_TABLE_NAME identify the source table. OLD and NEW hold the row images. Converting each row with to_jsonb() removes any dependency on a particular table's columns, so adding a column never requires changing the function.
On UPDATE, the function expands both images with jsonb_each and keeps only the keys whose values differ. This keeps change history rows small and makes them readable. Trigger arguments arrive in TG_ARGV as text. Pass the column names to exclude there, such as password hashes, API tokens and updated_at. The function removes those keys from both images with the jsonb - text[] operator before it computes the diff, so secrets never reach the audit table.
For attribution, the function reads current_setting('app.user_id', true). The second argument returns NULL instead of raising an error when the setting is absent. It also records session_user so that migrations and manual psql sessions still leave a trace. The function is SECURITY DEFINER, so application roles can write to an audit table they cannot otherwise touch. Because of that, pin search_path to pg_catalog and the audit schema. Inside the function, current_user reports the function owner, while session_user still reports the connected role.
Attach two AFTER ... FOR EACH ROW triggers to each table:
- One for INSERT OR DELETE.
- One for UPDATE with
WHEN (OLD.* IS DISTINCT FROM NEW.*), which skips no-op updates before the function runs.
Both triggers are needed because a single trigger covering all three operations would log every update twice alongside the conditional one. When only an excluded column changed, WHEN still fires, so the function should also skip the write if the diff is empty.
The trigger runs inside the business transaction. Any error it raises, including a full disk or a missing partition, rolls back the user's write.
CREATE OR REPLACE FUNCTION audit.log_change() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, audit -- pinned: callers cannot shadow objects
AS $$
DECLARE
excluded text[] := TG_ARGV; -- column names passed in CREATE TRIGGER
old_row jsonb;
new_row jsonb;
BEGIN
-- Strip secrets and noise before anything else touches the row images
IF TG_OP <> 'INSERT' THEN old_row := to_jsonb(OLD) - excluded; END IF;
IF TG_OP <> 'DELETE' THEN new_row := to_jsonb(NEW) - excluded; END IF;
IF TG_OP = 'UPDATE' THEN
-- Keep only keys whose values changed, with before and after values
SELECT jsonb_object_agg(n.key, o.value), jsonb_object_agg(n.key, n.value)
INTO old_row, new_row
FROM jsonb_each(new_row) n
JOIN jsonb_each(old_row) o ON o.key = n.key
WHERE o.value IS DISTINCT FROM n.value;
IF new_row IS NULL THEN RETURN NULL; END IF; -- only excluded columns changed
END IF;
INSERT INTO audit.change_history
(table_schema, table_name, op, app_user_id, db_role, old_values, new_values)
VALUES
(TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP,
NULLIF(current_setting('app.user_id', true), ''), -- NULL if unset
session_user, -- connected role; current_user would be the owner here
old_row, new_row);
RETURN NULL; -- return value is ignored for AFTER row triggers
END $$;
CREATE TRIGGER accounts_audit_ins_del AFTER INSERT OR DELETE ON public.accounts
FOR EACH ROW EXECUTE FUNCTION audit.log_change('password_hash', 'api_token', 'updated_at');
CREATE TRIGGER accounts_audit_upd AFTER UPDATE ON public.accounts
FOR EACH ROW WHEN (OLD.* IS DISTINCT FROM NEW.*) -- skip no-op updates
EXECUTE FUNCTION audit.log_change('password_hash', 'api_token', 'updated_at');
A reusable SECURITY DEFINER audit function that strips columns named in TG_ARGV, stores only changed keys on UPDATE, and is attached to one table through separate INSERT/DELETE and conditional UPDATE triggers.
How do you attribute changes to the real application user?
The database has no idea who clicked the button. It sees only the pooled role the application connects as. To record the real actor, the request middleware opens a transaction and, before any business query, calls set_config('app.user_id', $1, true). The third argument makes the setting transaction-local, which is the same as SET LOCAL. The trigger reads it with current_setting('app.user_id', true), which returns NULL instead of raising an error when the setting is absent.
Scope matters because of pooling. A plain session-level SET sticks to the server connection. Under a transaction-mode pooler, that connection is handed to a different client after COMMIT, so the next request inherits the previous user's identity. A transaction-local value disappears at commit or rollback, so it cannot leak.
Paths that skip the middleware
Cron jobs, queue workers, migrations and ad-hoc psql sessions never pass through request middleware. Give each one an explicit, non-human tag at the start of its transaction, such as job:nightly-reconcile or migration:0042. After that, a NULL actor should mean something you did not anticipate. Alert on it or count it, rather than treating it as normal.
Protecting the trail
Have a dedicated role, not the application role, own the audit table and the trigger function. Mark the function SECURITY DEFINER with a pinned search_path so it can insert on the application's behalf. Revoke INSERT, UPDATE, DELETE and TRUNCATE on the audit table from the application role and grant only SELECT where reads are required. A compromised app can then add history only through the trigger and cannot rewrite it.
-- Dedicated owner: the app role never owns the audit objects.
CREATE ROLE audit_owner NOLOGIN;
CREATE SCHEMA audit AUTHORIZATION audit_owner;
CREATE TABLE audit.change_log (
id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
table_name text NOT NULL,
op text NOT NULL,
actor text, -- NULL = untagged code path; alert on it
row_data jsonb,
changed_at timestamptz NOT NULL DEFAULT now()
);
ALTER TABLE audit.change_log OWNER TO audit_owner;
-- Runs as audit_owner. The pinned search_path blocks object hijacking.
CREATE FUNCTION audit.log_change() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER SET search_path = pg_catalog, pg_temp AS $$
BEGIN
INSERT INTO audit.change_log (table_name, op, actor, row_data)
VALUES (TG_TABLE_NAME, TG_OP,
-- nullif: after a local setting ends, the session can still see ''
nullif(current_setting('app.user_id', true), ''),
to_jsonb(CASE WHEN TG_OP = 'DELETE' THEN OLD ELSE NEW END));
RETURN NULL;
END $$;
ALTER FUNCTION audit.log_change() OWNER TO audit_owner;
-- App role can read history but cannot write, rewrite or truncate it.
REVOKE ALL ON audit.change_log FROM PUBLIC, app_user;
GRANT USAGE ON SCHEMA audit TO app_user;
GRANT SELECT ON audit.change_log TO app_user;
CREATE TRIGGER orders_audit AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit.log_change();
-- Request middleware: the setting is transaction-local and gone at COMMIT.
BEGIN;
SELECT set_config('app.user_id', 'user:8f3a2c', true);
UPDATE orders SET status = 'shipped' WHERE id = 1042;
COMMIT;
-- Cron job, migration or psql session: tag the transaction explicitly.
BEGIN; SET LOCAL app.user_id = 'job:nightly-reconcile'; /* ... */ COMMIT;
A separate role owns the audit table and its SECURITY DEFINER trigger, the application role is left with SELECT only, and each transaction sets a transaction-local actor that the trigger records, from either request middleware or an explicitly tagged job.
How do you query the audit trail in Postgres?
Most audit queries start from one record. Filter on table name and record key, then order by changed_at. That gives you a timeline of inserts, updates and deletes for the row. Each entry carries the old and new JSONB images, so you can see the full state at every step without replaying diffs.
To find who last changed a specific field, compare the field across the two images. old_data -> 'email' IS DISTINCT FROM new_data -> 'email' keeps only the rows where that value actually moved. Use IS DISTINCT FROM rather than <> so that changes to or from null still match. Order by changed_at descending, take the first row, and read changed_by.
To see everything one request did, group by the transaction ID the trigger captured. A single business action often touches several tables. Grouping by transaction ID shows the whole unit of work together, which is usually what an incident review needs.
Indexes
- Record history: add a composite btree on
(table_name, record_id, changed_at). It serves the record-history and last-changed queries directly, and the trailing timestamp lets Postgres return rows already in order. - Time-range scans: a BRIN index on
changed_atworks well because the audit table is append-only. Rows arrive roughly in timestamp order, so each block range covers a narrow time window. A BRIN index stores one summary per block range, so it stays tiny compared to a btree. If you bulk-load or backfill out of order, that correlation breaks and BRIN loses its benefit. In that case use a btree instead.
Renamed and dropped columns
Old JSONB keeps the column names that existed when the row was written. After a rename, query both keys, for example COALESCE(new_data -> 'email_address', new_data -> 'email'). Record schema changes with their dates so readers know which key applies to which period.
Rolling out audit triggers in an existing monolith
Treat the audit objects like any other schema change. Ship the change history table, the trigger function and each CREATE TRIGGER statement through your normal migration tool. This way they are versioned, reviewed and applied the same way in every environment. A trigger created by hand in production will be missing in staging, and nobody will notice until an incident.
Order of rollout
- Start with the highest-value tables. Begin with tables where "who changed this?" comes up in support tickets or compliance reviews, such as accounts, permissions, billing and orders. Attach triggers to more tables only once you have seen the write overhead on these.
- Optionally backfill a baseline. Insert one synthetic row per existing record, using a distinct operation value such as
SNAPSHOT. Without it, the first realUPDATEhas no earlier state to compare against. - Test it in the suite. Write tests that insert, update and delete through the application's normal code paths. Then assert that the expected audit rows exist and that the actor column holds the expected user. These tests catch connection-pool code that stops setting the session variable.
- Review schema changes.
to_jsonb()picks up new columns automatically, so a newly added secret starts appearing in history unless someone adds it to the excluded-column list. Add that check to the migration review checklist.
Retention
Partition the history table by time. Create future partitions ahead of time with a scheduled job, so inserts never fail for lack of a matching partition. Detach or drop old partitions instead of running large DELETEs, which cause table bloat and heavy vacuum work.
Development cost
The upfront work is modest, usually a few migrations and tests. The ongoing cost is extra migration review, slower test runs, and teaching every new developer that some behaviour lives in the database rather than in the application code.
When should you avoid Postgres audit triggers, and what are the alternatives?
Triggers are a poor fit in several situations, and most of them only show up under load or during an incident.
- Hot tables and bulk updates. Every audited row write adds a second insert, its index maintenance, and its WAL inside the same transaction. A backfill that updates ten million rows also writes ten million audit rows. Benchmark your own workload before auditing high-churn tables.
- Growth. On frequently updated tables, the audit table can grow faster than the source data. Without partitioning and a retention policy, it eventually dominates storage and vacuum time.
- Attribution and intent. Triggers record what changed. They record who changed it only if the application set the session context correctly. They never record why, and they don't see reads.
- Bypasses.
TRUNCATEdoes not fire row-level triggers. Settingsession_replication_role = replicaskips ordinary triggers. Superusers and table owners can disable triggers. Row triggers don't capture DDL either. - Hidden behaviour and coupling. Developers get surprised by side effects that live outside the codebase. The design also assumes that all audited data lives in one database.
| Approach | Captures | Fits better when |
|---|---|---|
| Application-level audit events | Business intent, actor, and request context. Can include reads. | You need the "why", or the monolith is splitting into services |
| Log-based CDC (logical decoding) | Every committed row change from the WAL, with no write-path trigger | You need low write overhead or need to stream changes to other systems. Actor must be carried in the data |
| Per-table shadow/history tables | Full row versions for selected tables, queryable as typed columns | A few tables need point-in-time reconstruction |
| pgAudit (statement logging) | SQL statements, including DDL and reads, written to the server log | Compliance requires a record of who ran what, rather than row diffs |
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.
- Reviewers see the exact diff and the list of affected services up front, so approval is a decision rather than an investigation.
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.
- Per-service approve/reject means a bad shared value can be refused where it would break something, before it is live.
- 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.