Who changed this number? An audit log in one Postgres trigger
Customers came back confused by figures they did not remember entering, and we could not show who had; one generic Postgres trigger fixed that, and a connection pool taught me where the user id belongs.
8 min read
A customer opens their report and a figure looks wrong. They do not remember entering it. Maybe someone else on their team changed it, maybe not. So they come to us. In 2023 we could not tell them who changed it, when, or what it was before.
Context
Customers entered sensitive figures, financial KPIs among them, and we generated their reports from that data. The stack was FastAPI, SQLModel on SQLAlchemy, and Postgres. Several people on a customer’s team could edit the same figures, and they did not always know what the others had changed.
Problem
A row held its current value and nothing else. When a customer questioned a number, the honest answer was “we don’t know”. That is a bad answer about data that ends up in a customer’s report.
What I tried
I first considered doing it in the application, with the ORM’s hooks. It fell short of what I needed:
- Every write path has to remember to log. Miss one and the history has a hole nobody sees.
- The ORM does not see every write. SQLAlchemy’s bulk UPDATE and DELETE skip the unit-of-work hooks; only a statement-level hook sees them, not the individual rows.
- Changes made by hand count too. When someone runs an UPDATE in a SQL console to fix a figure, I want that in the history as well. No application hook sees it.
- The audit should not care about the schema. Adding or dropping a column on an audited table should need no change to the audit code.
- It ties the history to one language and one set of libraries. I did not want to rebuild the audit log if we ever moved off them.
I wanted the database to be the one place that cannot forget.
I looked at pgAudit first. Cloud SQL supported it, and an extension built for auditing sounded like the right tool. It answers a different question. It writes which database role ran which statement to the server log, the kind of record ISO 27001 and similar certifications ask for. Our app used one role for every request, so that log would say the app changed a figure. It would not say which person did, or what the value was before. So I wrote the trigger myself and attached it only to the tables where a change matters to a customer, not to every table.
Large platforms often go the other way. GitLab writes named events from application code, and GitHub’s audit log is also a list of named events like repo.create, not row diffs. That is worth it at their size. For a small team, one trigger covered every insert and update for the price of one function.
What worked
One trigger function for every table. It fires after each INSERT or UPDATE, once per row, and writes a row into an audit table: the table, the operation, the row’s id, the data, the time and the user. On INSERT it stores the whole new row as jsonb. On UPDATE it stores only the columns that changed, old and new. Adding a table is one line in a migration. The change data is recorded as jsonb, not as text and not as a column per field. Text would hold the row as a string Postgres cannot search or compare, and a column per field would tie the audit table to every audited table’s schema. With jsonb, one save is one row, the trigger computes the diff in SQL, and adding or dropping a column needs no change to the audit code. The price: values lose their column types, and the diff stops at the column, so changing one key inside a JSON column stores the whole old and new value. It covered three tables when it shipped, two and a half weeks after the first commit: accounts, users (we also wanted to know who invited whom) and KPI definitions. The figures followed in March 2024, and 19 tables within seven months.
create table audit_log ( id bigint generated always as identity primary key, table_name text not null, operation text not null, row_id text not null, old_data jsonb, new_data jsonb, changed_by uuid, changed_at timestamptz not null default now());
create function audit_row() returns triggerlanguage plpgsql as $$declare old_part jsonb; new_part jsonb := to_jsonb(new);begin if tg_op = 'UPDATE' then -- keep only the columns that changed select jsonb_object_agg(n.key, o.value), jsonb_object_agg(n.key, n.value) into old_part, new_part from jsonb_each(to_jsonb(new)) n join jsonb_each(to_jsonb(old)) o on o.key = n.key where n.value is distinct from o.value; if new_part is null then return null; -- nothing changed end if; end if;
insert into audit_log (table_name, operation, row_id, old_data, new_data, changed_by) values (tg_table_name, tg_op, to_jsonb(new) ->> 'id', old_part, new_part, nullif(current_setting('app.user_id', true), '')::uuid); return null;end $$;
create trigger audit after insert or update on report_figurefor each row execute function audit_row();AFTER, not BEFORE. My first version was a BEFORE trigger. In review, a teammate edited one KPI and got two audit rows, an INSERT and an UPDATE. The KPI and figure saves were upserts (INSERT … ON CONFLICT DO UPDATE), and a BEFORE INSERT trigger fires for the insert attempt even when the conflict turns it into an update. With an AFTER trigger, only the action that actually happened fires: one UPDATE row. An AFTER trigger costs a little more. One correct row is worth it.
Telling Postgres who. The trigger cannot see the HTTP request, so the app has to tell Postgres who is acting. Postgres has a place for that: custom settings. The app sets app.user_id, and the trigger reads it with current_setting.
Where the user id lives. I had learned this a month earlier with another setting, the user’s language. I set it once per request with a plain SET. My tests showed it reverting, or leaking into another request, because SQLAlchemy returns the connection to the pool on every commit and the next transaction may get a different one. So the app already started every statement with a SET, and the user id went in the same way. It is right on whichever pooled connection a statement lands.
The pull request that added the trigger also shipped a back-office view: who changed which value, from what to what, and when. “Who changed this number?” became a lookup instead of an argument.
What it cost
- Latency: I tested it briefly and it was never an issue at our write volume, which was small.
- Storage: the table grew steadily, not wildly. Whole rows on insert add up. I planned to keep only the last N months; it never got prioritised.
- Deletes: the log cannot say who deleted a figure. I left DELETE out on purpose: logging a delete copies the row into the audit table, and then a deletion is not really a deletion. Even that only went halfway. Deleting an account took its figures’ history with it, but everywhere else the earlier inserts and updates of a deleted row stayed in the log.
What I would do differently
- Scope the user to the transaction. A plain
SEToutlives the request on a pooled connection, so anything that shares the pool but skips the app’s path, a background job say, can inherit the last user.set_config(..., true), the same asSET LOCAL, at the start of each transaction ends that. I did not get there. How SQLAlchemy sessions, scoped sessions and pooled connections share state was not obvious, and the tests I wrote to pin it down ended up skipped. Next time I would start here, tests first:
from sqlalchemy import event, textfrom sqlalchemy.orm import Session
@event.listens_for(Session, "after_begin")def set_audit_user(session, transaction, connection): user_id = session.info.get("user_id") if user_id is not None: connection.execute( text("select set_config('app.user_id', :uid, true)"), {"uid": str(user_id)}, )- Let Postgres check who is acting. Today I would pass the user’s login token instead of a user id. An extension like pg_session_jwt verifies the JWT against the auth provider’s key, and the trigger reads
auth.user_id(). The app forwards a token it already has instead of extracting and injecting an id, and no script can claim to be someone else. The token still has to reach each pooled connection, so the point above still applies, and it means running Postgres somewhere that allows the extension. - Treat an empty setting as no user. Once a custom setting has been set on a connection, even transaction-locally, reading it back returns an empty string, not NULL, and casting that to
uuidfails the write being audited. Hence thenullifabove. - Decide retention on day one. Partition the audit table by month so old history drops cheaply, and make sure deleting personal data reaches the audit table too.
From my time as CTO of ImpactNexus. Drafted with AI from my notes and the project’s history; I proofread it and checked every claim. The code is written for this post.