How to add an audit log — step by step, so it doesn't lie to you

You need an audit log the moment someone asks: "who changed the price?" With no journal, the honest answer is "no idea, and there's no way to find out."
The tempting approach is to do it in code: before every update, append a row to a history table. That works — until the first time data changes outside your app. A migration script, a button in your hosting panel, you at 2am in a SQL console. That audit log notices none of it and looks perfectly clean. Which is worse than having none: you trust it.
The right place for auditing is the database itself. Let's do it in PostgreSQL — it's what sits under Supabase, Neon, Railway and most managed databases.
What you'll end up with
A separate table where the database itself writes a row on every change: which operation, when, by whom, and what the record looked like. Nothing gets past it — not your app, not a script, not a person with console access.
1. Create the journal table
The official PostgreSQL docs example looks like this:
CREATE TABLE emp_audit(
operation char(1) NOT NULL,
stamp timestamp NOT NULL,
userid text NOT NULL,
empname text NOT NULL,
salary integer
);
The shape: operation, time, who, and the record's own columns.
For your own project a generic version is handier — one journal for all tables, with the row itself stored as JSON:
CREATE TABLE audit_log (
id bigserial PRIMARY KEY,
table_name text NOT NULL,
operation text NOT NULL,
changed_at timestamptz NOT NULL DEFAULT now(),
db_user text NOT NULL,
app_user text,
row_before jsonb,
row_after jsonb
);
2. Write the function that fills the journal
The database calls this function on every change. Three things are available inside: TG_OP — the operation type, OLD — the row before, NEW — the row after.
CREATE OR REPLACE FUNCTION write_audit() RETURNS TRIGGER AS $$
BEGIN
INSERT INTO audit_log (table_name, operation, db_user, app_user, row_before, row_after)
VALUES (
TG_TABLE_NAME,
TG_OP,
current_user,
current_setting('app.user_id', true),
CASE WHEN TG_OP = 'INSERT' THEN NULL ELSE to_jsonb(OLD) END,
CASE WHEN TG_OP = 'DELETE' THEN NULL ELSE to_jsonb(NEW) END
);
RETURN NULL;
END;
$$ LANGUAGE plpgsql;
RETURN NULL isn't a mistake here: the result of an AFTER trigger is ignored anyway — the docs say exactly that in the comment on their own example.
3. Attach the trigger to a table
CREATE TRIGGER products_audit
AFTER INSERT OR UPDATE OR DELETE ON products
FOR EACH ROW EXECUTE FUNCTION write_audit();
Two words matter. AFTER — write once the change has actually happened, so you don't log something that got rolled back. FOR EACH ROW — per row: if one UPDATE touched 50 rows, you get 50 journal entries, not one.
The same function can be attached to as many tables as you like — it learns the table name itself via TG_TABLE_NAME.
4. Verify it right now
UPDATE products SET price = 990 WHERE id = 1;
SELECT operation, db_user, row_before -> 'price', row_after -> 'price' FROM audit_log;
An UPDATE row with the old and new price should appear. If it doesn't, the trigger is almost always on a different table — or a different schema.
5. Record who it really was
Here's the step people skip, and shouldn't.
The journal already has current_user, but that's the database connection role. Behind a connection pool and inside Supabase, every request arrives under one role. You'll get 40,000 entries by authenticated — that is, by nobody.
You have to pass the real user in yourself. PostgreSQL lets you define your own setting: the docs state plainly that it "will accept a setting for any two-part parameter name". Let's use that.
In your code, before touching data, in the same transaction:
SELECT set_config('app.user_id', '8f21c0a4-...', true);
That third argument true means "current transaction only" — the value won't leak into the next request through the pool. The trigger reads it with current_setting('app.user_id', true), and the true there matters too: without it the database throws an error when the setting was never set, and your update fails. With it, you simply get an empty value.
What this buys you: a change from the app is signed by a human, a change from the console isn't. Which is exactly what you need to know.
6. Lock the journal against edits
A journal anyone can quietly tidy up proves little. Take everything but insert away from the normal role:
REVOKE ALL ON audit_log FROM PUBLIC;
GRANT INSERT ON audit_log TO your_app_role;
Let a separate role — or you, via the console — do the reading. There's no perfect defence against a superuser, but a casual "let's clean this up" no longer goes through.
7. Decide what happens as it grows
The journal grows faster than your data: a row lives once, but its change records pile up by the dozen. Six months in, you'll notice.
Two calm options: delete entries older than a year with a scheduled task, or simply don't audit tables where changes don't matter. Application logs won't do this job — they're a different thing and usually live for weeks, not years.
Does an audit log replace backups?
No. The journal answers "who changed what"; a backup answers "how do I get it back". You can restore a single row by hand from the journal, but not the whole database.
Won't the trigger slow writes down?
Every change now performs one extra insert — roughly twice the write work. For an admin panel and ordinary user actions that's invisible. For a bulk load of a million rows, drop the trigger for the duration.
How do I do this outside PostgreSQL?
Same mechanics: a trigger plus a journal table. The syntax differs, so do the variable names. Document databases often have no triggers at all — there you'd build auditing on a change stream, or write it from the app with all the caveats from the top of this article. And add auditing via a migration, not by hand in a console — otherwise it won't exist in production.
The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.





