What should an audit trail answer, and which approach captures each part?
An audit trail exists to answer five questions about any change: which row changed, what its values were before and after, when it happened, who caused it, and through which request or for what reason. Teams build one because those questions keep coming up. An engineer debugging corrupted state needs the sequence of writes. A compliance reviewer needs proof of who touched regulated data. A support agent needs to tell a customer why their plan changed. During a billing dispute, someone has to establish what the record said on a given date.
The two common approaches see different parts of that picture. A trigger fires inside the database on every write, whatever the source: ORM, raw SQL, migration, or someone in psql. That makes it reliable for what and when. It has no knowledge of the HTTP request, the authenticated user, or the business operation behind the write. Application code has that context but only sees writes that pass through it.
| Question | Triggers capture naturally | App code captures naturally |
|---|---|---|
| Which row changed | Yes, every write path | Only writes routed through the app |
| Old and new values | Yes, via OLD/NEW |
Only if the app loads the row first |
| When | Yes, transaction timestamp | Yes, but clocks and ordering can drift |
| Who | Only the database role | Yes, the authenticated user |
| Which request or why | No | Yes, request ID and business action |
The thesis of this article: triggers own the record of data changes, the application supplies identity and intent, and the strongest design combines the two.
Who should use trigger-based audit logging?
This design fits teams running a monolith, or a small number of services, against a single Postgres database that holds the data they need to audit. It is most useful when that data changes through more than one path. Typical examples are the web app, background jobs, data migrations, one-off backfill scripts, and engineers running manual fixes in psql. Each extra path is a place where app-only logging can silently miss changes. A trigger covers all of them.
It suits teams that regularly face these questions:
- Product and platform engineers who debug state corruption and need the exact sequence of writes to a record.
- Teams with compliance or contractual obligations to show who changed regulated data, such as billing, permissions, or personal data, and when.
- Support and operations teams who have to explain to a customer why an account, order, or plan changed.
- Small teams without a dedicated data platform. For them, a history table in the existing database is simpler to run than a separate change-capture pipeline.
It is a weaker fit for the cases covered in the section on when to avoid triggers: write-heavy hot tables, data spread across many databases or shards, and requirements for tamper evidence that an in-database table cannot meet alone. If the application really is the only writer and only a few business actions need recording, explicit app-side events alone may be enough.
How does it work end to end?
The combined design has four moving parts: a transaction wrapper in the application, row-level triggers on each audited table, a change history table, and an optional business event table. A single write flows through them like this:
- The request opens a transaction and sets context. Middleware or the shared transaction wrapper calls
set_config('app.user_id', ..., true)andset_config('app.request_id', ..., true). Those settings exist only for the current transaction. - Business code writes as usual. It uses the ORM, a query builder, or raw SQL. The code does not need to know that auditing exists.
- Triggers capture each row change. An
AFTER ... FOR EACH ROWtrigger serializesOLDandNEWtojsonb, reads the user and request ID withcurrent_setting(..., true), and inserts a row into the history table along withtxid_current()and a timestamp. - For important actions, the app records intent. Code that handles a refund, a permission change, or a manual override inserts one row into an event table that says why, tagged with the same transaction ID and request ID.
- Everything commits or rolls back together. The business rows, history rows, and event row share one transaction, so the audit trail never describes changes that didn't happen and never misses changes that did.
- Reviewers query by record or by intent. "Show me the history of order 1001" is an indexed range scan on the history table. "What did ticket 4821 change?" starts at the event table and joins on transaction ID or request ID.
The sections below cover each part in detail: the trigger function, the history table design, passing context from the app, and linking business events to row changes.
How does audit logging with Postgres triggers work?
A trigger-based audit log uses one generic PL/pgSQL function attached to every audited table. You declare it once and bind it with an AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW trigger per table. Inside the function, TG_TABLE_NAME names the affected table and TG_OP names the operation. to_jsonb(OLD) and to_jsonb(NEW) serialize the row before and after the change. Branch on TG_OP so an INSERT stores only the new image, a DELETE stores only the old one, and an UPDATE stores both. All of this goes into a single change history table. Because the row is captured as jsonb, the same function works for any table shape and survives most column additions without edits.
The important property is that the trigger runs inside the transaction that made the change. If the transaction rolls back, the history row disappears with it. If it commits, the history row commits too. Application code that writes audit records separately can't make that guarantee without careful transaction handling. The trade-off is that a failure in the audit insert aborts the business write.
app sets user/request context →
UPDATE orders ...→ row trigger fires → history row inserted → singleCOMMIT
Choose the timestamp function deliberately. now() returns the transaction's start time, so every history row written by a multi-statement transaction gets the same timestamp, and you can't order them by time. clock_timestamp() returns the actual time of each call, which preserves the order of changes within a transaction.
-- One history table for every audited table
CREATE TABLE change_history (
id bigserial PRIMARY KEY,
table_name text NOT NULL,
op text NOT NULL, -- INSERT / UPDATE / DELETE
old_row jsonb, -- NULL for INSERT
new_row jsonb, -- NULL for DELETE
changed_by text, -- set by the app per transaction
changed_at timestamptz NOT NULL
);
-- Generic function: works for any table because rows become jsonb
CREATE OR REPLACE FUNCTION audit_row_change() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO change_history (table_name, op, old_row, new_row, changed_by, changed_at)
VALUES (
TG_TABLE_NAME,
TG_OP,
CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) END,
CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) END,
current_setting('app.user_id', true), -- true: NULL instead of error if unset
clock_timestamp() -- real call time, not transaction start like now()
);
RETURN NULL; -- return value is ignored for AFTER row triggers
END;
$$;
-- Bind it once per audited table
CREATE TRIGGER orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW EXECUTE FUNCTION audit_row_change();
-- Request path: context, change, history row, one commit (ROLLBACK discards both)
BEGIN;
SET LOCAL app.user_id = 'user-4821';
UPDATE orders SET status = 'shipped' WHERE id = 1001;
COMMIT;
A generic PL/pgSQL AFTER row trigger that writes TG_TABLE_NAME, TG_OP, to_jsonb(OLD/NEW), the app-supplied user, and clock_timestamp() into one change_history table within the same transaction as the business write.
How do you design the change history table?
Each audit row needs enough context to answer "what changed, on which record, by whom, and as part of what" without joining back to live tables, which may have changed or been deleted since. A workable column set:
table_nameandrecord_pk: store the key as text or jsonb so one table can hold history for every audited table, including those with composite keys.operation:INSERT,UPDATEorDELETE.old_dataandnew_dataasjsonb, or a single computed diff holding only the changed keys.actorandrequest_id, read from session settings as described in the next section.transaction_id, so that rows changed together can be grouped.txid_current()gives you this.changed_at, filled fromclock_timestamp().
Index (table_name, record_pk, changed_at). The most common query is "show me the history of this record," and this index serves it as a single range scan.
Make the table append-only. Grant the application role INSERT only, or let the trigger function insert as a separate owner, and revoke UPDATE and DELETE. If the app can rewrite the log, the log proves nothing.
To control noise, skip no-op updates by comparing OLD IS DISTINCT FROM NEW, either in the function or in the trigger's WHEN clause. Strip columns such as updated_at, and leave out large blobs, using jsonb key removal (to_jsonb(NEW) - 'updated_at'). For retention, range-partition by changed_at so that expiring a month of history is a DETACH PARTITION or a DROP TABLE rather than a slow DELETE.
Full OLD/NEW snapshots grow fast, and they copy PII and secrets into a table that often has broader read access and longer retention than the source. Exclude or hash sensitive columns at capture time.
CREATE SCHEMA IF NOT EXISTS audit;
-- One table for every audited table; range-partitioned by time for cheap retention.
CREATE TABLE audit.change_log (
id bigserial,
table_name text NOT NULL,
record_pk text NOT NULL, -- text form of the key, works for composite keys too
operation text NOT NULL CHECK (operation IN ('INSERT','UPDATE','DELETE')),
old_data jsonb, -- NULL on INSERT
new_data jsonb, -- NULL on DELETE
actor text, -- from session settings set by the app
request_id text,
transaction_id bigint NOT NULL DEFAULT txid_current(),
changed_at timestamptz NOT NULL DEFAULT clock_timestamp(),
PRIMARY KEY (id, changed_at) -- must include the partition key
) PARTITION BY RANGE (changed_at);
-- Serves "show me the history of this record" as one range scan per partition.
CREATE INDEX ON audit.change_log (table_name, record_pk, changed_at);
CREATE TABLE audit.change_log_2026_11 PARTITION OF audit.change_log
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
-- Append-only: the app role can neither rewrite nor read the log directly.
-- Rows arrive via a SECURITY DEFINER trigger function owned by audit_owner.
ALTER TABLE audit.change_log OWNER TO audit_owner;
REVOKE ALL ON audit.change_log FROM PUBLIC, app_rw;
GRANT SELECT ON audit.change_log TO audit_reader;
-- Skip no-op updates before the trigger function is even called.
-- audit.capture() is defined separately.
CREATE TRIGGER orders_audit_upd
AFTER UPDATE ON public.orders
FOR EACH ROW WHEN (OLD IS DISTINCT FROM NEW)
EXECUTE FUNCTION audit.capture();
-- Retention: drop a whole month instead of running a slow DELETE.
-- ALTER TABLE audit.change_log DETACH PARTITION audit.change_log_2025_11;
-- DROP TABLE audit.change_log_2025_11;
An append-only, month-partitioned change history table with a per-record history index, revoked write access for the app role, a WHEN clause that skips no-op updates, and partition-based retention.
How do you pass the user and request ID from app code into triggers?
The trigger runs inside the database and has no idea which HTTP request caused the write. The application has to hand that context over, and the cleanest channel is a custom configuration parameter scoped to the current transaction. At the start of each transaction, call set_config('app.user_id', $1, true) and set_config('app.request_id', $2, true). The third argument makes the setting transaction-local, the same as SET LOCAL. Prefer set_config because it accepts bind parameters, while SET LOCAL would need the value interpolated into the SQL string. The trigger reads the value back with current_setting('app.user_id', true). The true there means "missing is fine": it returns NULL instead of raising an error. Wrap it in NULLIF(..., ''). Once a custom setting has been used on a connection, it can read back as an empty string after the transaction ends rather than as NULL.
Transaction scope matters because of connection pools. A session-level SET persists on the physical connection after it returns to the pool, so the next request can inherit the previous user's identity and write audit rows under the wrong name. Transaction-pooling proxies make session state unreliable in any case, because consecutive transactions from one client may run on different server connections. A transaction-local setting lives exactly as long as the writes it describes.
Set the context in one place, such as request middleware or the transaction wrapper every unit of work goes through, so no code path can forget it. When the context is missing anyway, the trigger should record NULL or an explicit sentinel such as 'unknown'. Never fall back to a default user. A wrong but plausible actor is worse than an obviously missing one.
-- Trigger function: reads the transaction-local context the app set.
-- missing_ok = true returns NULL instead of erroring; NULLIF turns the
-- empty string a previously used setting can leave behind into NULL.
CREATE OR REPLACE FUNCTION audit_capture() RETURNS trigger
LANGUAGE plpgsql AS $$
BEGIN
INSERT INTO audit_log (table_name, op, actor_id, request_id, changed_at)
VALUES (
TG_TABLE_NAME,
TG_OP,
NULLIF(current_setting('app.user_id', true), ''), -- NULL if unknown, never a default user
NULLIF(current_setting('app.request_id', true), ''),
now()
);
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_capture();
-- What the transaction wrapper sends for every unit of work.
-- In app code, pass the values as bind parameters ($1, $2) rather than
-- interpolating them into the SQL string.
BEGIN;
SELECT set_config('app.user_id', '4821', true), -- true = transaction-local, like SET LOCAL
set_config('app.request_id', 'req_7f3a9c', true);
UPDATE orders SET status = 'shipped' WHERE id = 1001; -- audit row gets actor 4821
COMMIT;
-- After COMMIT the settings are gone, so the next transaction on this
-- pooled connection cannot inherit user 4821. Without new context:
UPDATE orders SET status = 'cancelled' WHERE id = 1002; -- audit row gets actor NULL
A trigger that reads app.user_id and app.request_id with current_setting(..., true), and a transaction that sets them with transaction-local set_config so the identity cannot leak to the next request on a pooled connection.
What are the alternatives to triggers for application audit logging?
Three app-side patterns are common. Explicit audit calls in service methods write a record next to each mutation. They carry the richest context but depend on every developer remembering them. ORM hooks or callbacks fire on save or delete for mapped entities, so they are harder to forget, but they only see changes the ORM tracks per object. Domain events such as InvoiceVoided express business intent well. They describe what the code meant to do, and that is not always what the rows ended up as.
All three share one blind spot: they only see writes that pass through that code path. Bulk ORM updates often skip per-entity hooks. Raw SQL, data migrations, manual fixes in psql, and other services or scripts sharing the database bypass the application entirely. App-code logging therefore cannot be a complete record of data changes.
Where the event is written matters as much. If you publish to an async queue, open a separate connection, or ship to an external log, the audit write is not atomic with the business change. A crash after commit loses the event. A rollback after publishing records a change that never happened. Write audit rows in the same transaction, or use an outbox table committed alongside the change.
| Approach | Coverage | Context richness | Bypass risk | Maintenance cost |
|---|---|---|---|---|
| Postgres triggers | All writes to the table | Low unless context is passed in | Low (needs superuser or trigger disable) | Schema changes need trigger review |
| Explicit service calls | Only instrumented paths | High | High | High, per call site |
| ORM hooks | ORM entity saves only | Medium | Medium (bulk ops, raw SQL) | Medium |
| Domain events | Modelled actions only | High, includes intent | High | Medium to high |
How do you record business intent alongside row-level changes?
The hybrid that works well in a monolith is to keep the triggers as the complete record of what changed and let application code add a small number of events that say why. A refund, for example, touches the order, the payment, and a ledger row. The triggers capture all three updates, but none of those rows says "refund issued by support agent for ticket 4821 because the item arrived damaged." Only the code handling the refund knows that.
Write that business event to a separate table, such as audit_event, with an event type, actor, reason, any ticket reference, and a small structured payload. Insert it inside the same database transaction as the data change. That way it commits or rolls back with the rows it describes, and you never get an "override approved" event pointing at changes that didn't happen.
To link the two, store txid_current() on both the event row and every trigger-written history row, along with the request ID from the session setting described earlier. A reviewer can start from "admin override, ticket OPS-112" and query the change history table for that transaction ID to see exactly which columns moved, from what values to what values. The request ID matters when one request spans several transactions, because the transaction ID alone won't connect them.
This split also limits the instrumentation work. When someone adds a column or a table, the triggers record its changes without anyone touching the application. Developers write explicit events only at the few call sites where intent matters, such as money movement, permission changes, manual overrides, and data deletion requests. They don't have to wrap every write path to get a complete audit trail.
-- Statements the refund handler issues, all in ONE transaction.
BEGIN;
-- Request ID from the HTTP layer; 'true' scopes it to this transaction.
-- The triggers read it with current_setting('app.request_id', true).
SELECT set_config('app.request_id', 'req_7f3a9c21', true);
-- Ordinary data changes. Triggers write each one to audit_change
-- along with txid_current() and the request ID.
UPDATE orders SET status = 'refunded' WHERE id = 90412;
UPDATE payments SET refunded_cents = 4999 WHERE order_id = 90412;
INSERT INTO ledger_entries (order_id, amount_cents, kind)
VALUES (90412, -4999, 'refund');
-- The business event: why this happened and who did it.
-- It shares the same txid, so it commits or rolls back with the rows above.
INSERT INTO audit_event (event_type, actor, reason, ticket_ref, payload,
txid, request_id)
VALUES ('refund_issued', 'support:agent_118', 'item arrived damaged',
'TICKET-4821', '{"order_id": 90412, "amount_cents": 4999}',
txid_current(), current_setting('app.request_id', true));
COMMIT;
-- Reviewer: start from the intent and list the exact row changes.
SELECT e.event_type, e.actor, e.ticket_ref,
c.table_name, c.row_pk, c.old_values, c.new_values
FROM audit_event e
JOIN audit_change c
ON c.txid = e.txid
OR c.request_id = e.request_id -- catches other transactions in the same request
WHERE e.ticket_ref = 'TICKET-4821'
ORDER BY c.changed_at;
The refund handler writes a business-level audit_event in the same transaction as the trigger-captured row changes, tagged with txid_current() and the request ID so a reviewer can join from the stated intent to the exact before/after values.
How can it save engineering time?
The time savings come from doing the instrumentation once per table instead of once per write path, and from answering audit questions with a query instead of an investigation.
- Instrumentation scales with tables, not call sites. One generic function plus one
CREATE TRIGGERper table covers every write path to that table. That includes paths added later, such as a new background job, a migration, or a script. With app-only logging, each new mutation site needs its own audit call and its own review to confirm the call is there. - Schema changes mostly need no audit work. Because rows are captured as
jsonb, a new column shows up in the history automatically. The remaining work is a review step: deciding whether the new column is sensitive and should be stripped. - Debugging starts from facts. When a record ends up in an unexpected state, the history table gives the ordered sequence of writes, the before and after values, and the request ID behind each one. Engineers don't have to correlate application logs or reproduce the bug to learn what happened.
- Compliance and support requests become queries. "Who changed this customer's plan, and when?" is a lookup on
(table_name, record_pk). Without a reliable history, the same question can mean digging through logs and backups, and the answer may still be incomplete. - Fewer audit gaps found late. Missing audit calls are usually discovered when someone needs a record that doesn't exist. Database-level capture removes that whole class of omission for audited tables, so less time goes into after-the-fact fixes and explanations.
An illustrative estimate, with assumed inputs only: assume a monolith with 20 audited tables and 60 code paths that write to them. App-only logging means writing and reviewing 60 audit calls, plus one more for every new write path. Triggers mean one shared function and 20 trigger definitions. You would also add explicit intent events at the handful of call sites where the reason matters, assumed here to be 5 to 10. The actual numbers depend on the codebase. The point is that the trigger approach's cost grows with the number of tables, while the app-only approach's cost grows with the number of write paths, which usually grows faster.
The savings have a price, covered in the next section. Someone has to maintain PL/pgSQL in migrations, attach triggers to new tables, and keep integration tests that check the actor and request context are recorded.
When should you avoid audit log Postgres triggers, and what do they cost?
Every audited statement does extra work inside the same transaction: the trigger function runs, serializes the row (often to JSONB), and inserts into the history table, which carries its own indexes and WAL. That cost lands on the caller's latency. A bulk UPDATE touching many rows fires the trigger once per row and writes one history row each, so a backfill can roughly double its write volume and hold locks longer. FOR EACH STATEMENT triggers with transition tables let you insert all history rows in one set-based statement, which cuts per-row function overhead. The cost is a more complex function that must diff the old and new transition tables itself.
There is also a development cost. PL/pgSQL lives in migrations, where application developers rarely look. It is awkward to unit test, and people forget to attach it when they add a table or a column.
Triggers know the actor only if the app sets app.user_id on every connection path, including jobs and scripts. They also don't stop privileged users. A superuser or the table owner can disable triggers or rewrite history rows. Tamper resistance requires restricted roles that can only insert into the history table, and shipping history off the database.
Triggers fit poorly in three cases:
- Write-heavy hot tables, where the extra writes and latency hurt most.
- Multi-database or heavily sharded setups, where history ends up fragmented across instances.
- Strict tamper-evidence requirements, which an in-database table can't satisfy alone.
If you adopt triggers, write integration tests against a real Postgres. Each test should assert that a write produces a history row with the expected actor. Another test should assert that a write without app.user_id records null or a sentinel value, not a guessed or stale user.
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 audit log postgres 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
.envby 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.