What is a trigger-based audit log, and why does it catch more than Hibernate Envers?
A trigger-based audit log records changes inside the database itself. You attach an AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW trigger to each audited table. The trigger function converts the OLD and NEW row images to JSONB and inserts them into a single change history table, along with the table name, the operation, and a timestamp. Because the trigger fires as part of the same transaction as the write, the audit row commits or rolls back with the change it describes.
Hibernate Envers works one layer higher. It listens to Hibernate's entity lifecycle events, so it only sees changes that pass through a Hibernate session as managed entities. It misses everything else:
- Native SQL queries
- Bulk JPQL or HQL
UPDATEandDELETEstatements, which skip entity events - Schema and data migrations
- Manual fixes someone runs in
psqlduring an incident - Writes from scripts, cron jobs or other services that share the database
These are often the changes you most need to explain later.
This approach fits a monolith backed by one Postgres database that needs a complete audit trail for compliance reviews, customer support questions or debugging bad data. Every write path ends at the same tables, so a trigger on each table covers all of those paths at once. You do not have to find each repository method, batch job and admin endpoint and add auditing to it. A new code path that writes to an audited table is recorded automatically, without any review checklist.
WRITE PATHS INTO THE DATABASE [Envers sees it?]
+------------------------------------+
| Hibernate entity changes [yes] |--+
| Native SQL queries [no] |--+
| Bulk HQL/JPQL UPDATE/DELETE [no] |--+
| Schema / data migrations [no] |--+
| Manual psql incident fixes [no] |--+
| Scripts, cron, other services [no] |--+
+------------------------------------+ |
v
+-------------------------------------------------------+
| Postgres (single database) |
| +---------------------------+ |
| | Audited table (row write) | |
| +-------------+-------------+ |
| v fires |
| +---------------------------+ |
| | AFTER INSERT OR UPDATE OR | |
| | DELETE FOR EACH ROW | |
| | trigger function | |
| +-------------+-------------+ |
| OLD/NEW JSONB + table, op, time v |
| +---------------------------+ |
| | Change history table | |
| | (one table, all audits) | |
| +---------------------------+ |
| Same transaction as the write: the audit row |
| commits or rolls back with the change. |
+-------------------------------------------------------+
Every write path reaches the same audited tables, so a row-level AFTER trigger records OLD and NEW images as JSONB in one change history table. Hibernate Envers sees only changes made through Hibernate-managed entities.
How do you design a generic change history table?
One table holds changes for every audited entity. A generic table means one trigger function, one retention policy and one set of grants. Adding auditing to a new table becomes a single CREATE TRIGGER statement, with no new schema to design. JSONB makes this possible because the row images do not need to match the source table's columns.
Columns
id: abigintidentity. It orders events within the log.table_name: the schema-qualified source table.entity_key: the primary key stored astext. Composite keys are serialized into a single value, so lookups work the same way for every table.operation:INSERT,UPDATEorDELETE.old_data/new_data: full row images as JSONB. Inserts have no old image and deletes have no new one. Storing both images means you never have to reconstruct state from earlier events.changed_columns: atext[]of the columns that differ. It lets you ask "who changedstatus?" without diffing JSON.txid: the value oftxid_current(). It groups every row written in one business operation.changed_at: atimestamptz.actor_user_id,request_id,reason: application context. These are nullable because migrations and manual fixes may not have it.
Indexes and partitioning
Most queries ask for one entity's history, so create an index on (table_name, entity_key, changed_at). Add a second index on txid so you can expand a single change into its full transaction.
Declare the table PARTITION BY RANGE (changed_at) from the start. Postgres cannot convert an existing table into a partitioned one in place, so adding partitioning later means a migration. With partitions in place, retention is a matter of dropping old partitions. Because Postgres requires unique constraints on a partitioned table to include the partition key, the primary key must be (id, changed_at).
-- One generic audit table for every audited entity, partitioned by time from day one.
CREATE TABLE audit.change_log (
id bigint GENERATED ALWAYS AS IDENTITY,
table_name text NOT NULL, -- schema-qualified, e.g. 'public.orders'
entity_key text NOT NULL, -- primary key as text; composite keys serialized
operation text NOT NULL
CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
old_data jsonb, -- NULL for INSERT
new_data jsonb, -- NULL for DELETE
changed_columns text[], -- columns that differ on UPDATE
txid bigint NOT NULL DEFAULT txid_current(),
changed_at timestamptz NOT NULL DEFAULT now(),
-- Application context: nullable because migrations and manual fixes may not set it
actor_user_id text,
request_id text,
reason text,
-- Unique constraints on a partitioned table must include the partition key
PRIMARY KEY (id, changed_at)
) PARTITION BY RANGE (changed_at);
-- One entity's history, in time order
CREATE INDEX change_log_entity_idx
ON audit.change_log (table_name, entity_key, changed_at);
-- Expand a single change into everything written in the same transaction
CREATE INDEX change_log_txid_idx
ON audit.change_log (txid);
-- Monthly partitions; indexes defined on the parent are created on each partition
CREATE TABLE audit.change_log_2026_11 PARTITION OF audit.change_log
FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');
CREATE TABLE audit.change_log_2026_12 PARTITION OF audit.change_log
FOR VALUES FROM ('2026-12-01') TO ('2027-01-01');
-- Retention: drop a whole month instead of running a large DELETE
-- DROP TABLE audit.change_log_2025_11;
This DDL creates the generic audit table, partitioned by changed_at. It adds the entity-history and transaction indexes, and it includes changed_at in the primary key because Postgres requires unique constraints on a partitioned table to contain the partition key.
How does the trigger function work?
One PL/pgSQL function serves every audited table. It branches on TG_OP, records TG_TABLE_SCHEMA and TG_TABLE_NAME so the history table knows where the row came from, and converts the row images with to_jsonb(OLD) and to_jsonb(NEW). Working in JSONB lets a single function handle any column layout, so a schema change never requires editing the function.
Sensitive columns are removed before anything else happens. Each table passes its exclusion list as trigger arguments, which the function reads from TG_ARGV. The jsonb - text[] operator then deletes those keys from both images. If you need evidence that a password changed without storing its value, replace the value with a fixed marker instead of deleting the key.
For updates, the function compares the cleaned images. If they are equal, it returns without writing anything. This skips ORM saves that rewrite unchanged rows, and it also skips updates that touched only excluded columns. Otherwise it expands both images with jsonb_each and keeps the keys whose values differ using IS DISTINCT FROM, which treats NULL-to-value changes as changes. The result is stored as the changed-columns list.
Actor context comes from current_setting('app.user_id', true). The second argument makes a missing setting return NULL instead of raising an error, so migrations and psql sessions still succeed.
Attach the function AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW. Because AFTER triggers ignore their return value, the function returns NULL, and the audit entry is written only once other BEFORE triggers have finished changing the row. Declare the function SECURITY DEFINER so application roles can write audit entries without holding INSERT privileges on the history table. Pin search_path to pg_catalog plus the audit schema so no caller can shadow a function or table the trigger relies on.
-- One reusable audit function for every table. SECURITY DEFINER lets app roles
-- write history rows without INSERT on audit.history; the pinned search_path
-- prevents a caller from shadowing objects the function relies on.
CREATE OR REPLACE FUNCTION audit.log_change() RETURNS trigger
LANGUAGE plpgsql SECURITY DEFINER
SET search_path = pg_catalog, audit AS $$
DECLARE
excluded text[] := COALESCE(TG_ARGV, '{}'); -- per-table list of columns to strip
old_row jsonb;
new_row jsonb;
changed text[];
BEGIN
-- Strip sensitive keys before any comparison or storage.
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
-- No-op save, or only excluded columns changed: write nothing.
IF old_row = new_row THEN RETURN NULL; END IF;
-- IS DISTINCT FROM counts NULL <-> value transitions as changes.
SELECT array_agg(n.key ORDER BY n.key) INTO changed
FROM jsonb_each(new_row) AS n
WHERE n.value IS DISTINCT FROM old_row -> n.key;
END IF;
INSERT INTO audit.history
(schema_name, table_name, operation, actor_id, old_row, new_row, changed_cols)
VALUES
(TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_OP,
current_setting('app.user_id', true), -- NULL when unset (migrations, psql)
old_row, new_row, changed);
RETURN NULL; -- ignored for AFTER triggers
END
$$;
-- Attach per table; trigger arguments become TG_ARGV (the exclusion list).
CREATE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON public.users
FOR EACH ROW EXECUTE FUNCTION audit.log_change('password_hash', 'api_token');
A single SECURITY DEFINER PL/pgSQL trigger function that strips per-table excluded columns, skips no-op updates, computes the changed-column list from JSONB images, records the actor from current_setting, and is attached as an AFTER row trigger.
How do you pass the user and request ID from the application into Postgres?
The trigger reads the actor from a custom configuration parameter, so the application's only job is to set that parameter inside every write transaction. Call set_config('app.user_id', '42', true) and set_config('app.request_id', '...', true) as the first statement after BEGIN. The third argument, true, makes the value transaction-local, the same as SET LOCAL. Prefer set_config because it accepts bind parameters and SET does not, so you never build SQL from a user ID string.
In Spring, place an aspect inside the @Transactional boundary by ordering it after the transaction interceptor. It runs the call on the connection the transaction is already holding. In other stacks, run the same call from middleware or from a hook on the ORM's transaction-begin event.
The setting must be transaction-scoped. A plain session-level SET survives COMMIT. In a transaction-pooling connection pooler, the next transaction on that server connection may belong to a different request, and it inherits the previous user's ID. You then get confident, wrong attribution, which is worse than no attribution.
In the trigger, read the values with nullif(current_setting('app.user_id', true), ''). The true argument returns NULL instead of raising an error when the parameter is undefined. The nullif handles the case where an earlier local setting has reset the parameter to an empty string.
Background jobs, migrations and ad hoc psql sessions will arrive with no actor. You have two options:
- Tolerate it. Record NULL and have jobs set a descriptive value such as
job:invoice-sweeper. - Reject it. Raise an exception in the trigger so every writer must identify itself. Migration tooling and operators then set the parameter explicitly before they write.
-- 1. Application side: the first statements after BEGIN in every write transaction.
-- $1/$2 are bind parameters sent by the driver. Never concatenate them into SQL.
BEGIN;
SELECT set_config('app.user_id', $1, true), -- true = transaction-local, like SET LOCAL
set_config('app.request_id', $2, true);
-- ... application writes; audit triggers fire here ...
COMMIT; -- both settings are discarded, so the pooled connection carries nothing forward
-- 2. Trigger side: one helper that every audit trigger calls to resolve the actor.
CREATE OR REPLACE FUNCTION audit_current_actor() RETURNS text
LANGUAGE plpgsql AS $$
DECLARE
-- missing_ok = true returns NULL instead of raising when the parameter was never set.
-- nullif covers a parameter that was set earlier and has since reset to ''.
actor text := nullif(current_setting('app.user_id', true), '');
BEGIN
IF actor IS NULL THEN
-- Reject policy: every writer must identify itself.
-- For the tolerate policy, delete this block and let the audit row store NULL.
RAISE EXCEPTION 'audit: app.user_id is not set for this transaction'
USING HINT = 'Run set_config(''app.user_id'', <actor>, true) after BEGIN.';
END IF;
RETURN actor;
END;
$$;
-- 3. Non-request writers set a descriptive actor explicitly.
-- A literal value is fine here because no user input is involved.
BEGIN;
SET LOCAL app.user_id = 'migration:add_invoice_status';
UPDATE invoices SET status = 'open' WHERE status IS NULL;
COMMIT;
Shows how to set the actor as a transaction-local value from the application with bind parameters, how to resolve it safely in a trigger helper that rejects unidentified writes (or tolerates them if you remove one block), and how a migration or psql session identifies itself with SET LOCAL.
Rolling out triggers, securing the log and keeping coverage current
Attaching triggers from a migration
Attach the trigger from a migration rather than by hand. A DO block that loops over an array of table names and runs EXECUTE format('CREATE TRIGGER ... ON %I ...', t) for each one keeps the audited set in a single reviewable list. Using %I quotes each identifier safely. If you write DROP TRIGGER IF EXISTS before each CREATE, the migration can be re-run without errors.
Checking coverage in CI
A new table is unaudited by default, so check coverage in CI. Join information_schema.tables (filtered to your schemas, excluding the audit table itself) against pg_trigger on tgrelid and tgfoid pointing at your audit function. Fail the build if any table is returned. Tables you intentionally leave unaudited go in an explicit exclusion list.
Locking down the log
Revoke UPDATE, DELETE and TRUNCATE on the audit table from the application role, so the application can only insert. The trigger runs with the privileges of the role performing the write unless the function is SECURITY DEFINER. Grant INSERT accordingly, or use SECURITY DEFINER with a pinned search_path.
Limits of tamper evidence
This setup is tamper-resistant, not tamper-proof:
TRUNCATEon an audited table does not fire row-level triggers.- Table owners can run
DISABLE TRIGGER. - Superusers can set
session_replication_role = replica. - Restores can load data with triggers disabled.
Shipping rows to a separate system that the database's administrators cannot write to raises the bar.
Managing growth and retention
Partition the audit table by month on the change timestamp. To enforce retention, DETACH PARTITION, export the detached table to cold storage, then drop it. This is much cheaper than a bulk DELETE. Agree on the retention period with compliance before you automate it. Archived data must remain retrievable, and some regimes require deleting personal data that the log captured.
How do you query the audit trail?
The most common question is "what happened to this row?" Filter on table_name and the primary key, then order by changed_at. The composite index on (table_name, row_id, changed_at) covers both the filter and the sort. Postgres reads one contiguous slice of the index and skips the sort step, so the query stays fast as the log grows into hundreds of millions of rows.
Reconstructing state at a point in time
To see what an entity looked like at a given moment, use the same filter with changed_at <= :ts, order descending, take LIMIT 1 and read new_data. This uses the same index, scanned backwards. If the latest qualifying row is a DELETE, its new_data is null, which correctly means the entity did not exist at that time.
One request, one actor
Filter on txid to see everything a single request touched. Because every trigger firing in one transaction records the same txid, this returns the whole unit of work across all tables. Queries by actor or request_id use their own indexes. Combine them with a changed_at range so the planner can limit the scan to the period under review instead of an actor's entire history.
Handling schema drift
Old JSONB keeps the shape the table had when the row was written. When a column has been renamed, read both keys: coalesce(new_data->>'full_name', new_data->>'name'). A dropped column stays in historical payloads, so new_data ? 'legacy_flag' tells you whether a record predates the drop. Put these mappings in a view per audited table, so analysts query a stable shape and nobody has to remember each migration.
When should you avoid trigger-based auditing, and what are the alternatives?
Triggers make every write cost more. Each audited row change adds a heap insert, its index entries and the WAL for both, inside the same transaction.
Illustrative estimate, assumed inputs: a backfill UPDATE touches 2,000,000 rows, and each audit row stores old and new JSONB totalling about 1 KB. That single statement produces 2,000,000 audit inserts and roughly 2 GB of extra table data before index and WAL overhead. Replicas and backups have to absorb all of it.
Triggers also record what changed, not why. A row diff cannot tell a refund from a manual correction. Trigger logic sits outside the application codebase, so it is easy to overlook in review and needs database-backed tests. Every column rename or type change also leaves JSONB history in an older shape.
| Criterion | JSONB triggers | Hibernate Envers | Domain events / audit service | Logical decoding CDC |
|---|---|---|---|---|
| Completeness | All SQL writes | ORM writes only | Only what code emits | All committed writes |
| Business intent | None | Minimal | Rich | None |
| Typing | Untyped JSONB | Typed _AUD tables |
Typed schemas | Depends on consumer |
| Write-path cost | Synchronous | Synchronous | Varies | Asynchronous; slot retains WAL |
| Visibility | Hidden in DB | In code | In code | Separate pipeline |
| Dev cost | Low | Low (JPA apps) | High | High (infrastructure) |
When to avoid triggers and when to combine approaches
Avoid triggers when write throughput is very high or bulk jobs are routine. Avoid them when data spans several databases, because no single trigger sees every write. Avoid them when consumers need intent-rich events.
A common combination works well. Keep triggers as a tamper-resistant catch-all for completeness. Emit domain events for actions where the business reason matters. Use CDC when downstream systems need the change stream.
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
.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.