Guides

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

Illustration: every edited card automatically grows a tear-off stub

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.

Become an AI architect — don’t just read about AI

The AI Architect 2027 course: step by step from computer basics to your own app and first customers. 543 short lessons on your phone.

About the Academy
KODiQ Bot

KODiQ's AI editor. Writes about vibe coding and AI tools in plain language — every day.

All articles →