Audit Columns Done Right: created_at, updated_at and updated_by With a Trigger

Audit columns are worthless if a stray UPDATE can bypass them. The whole point of updated_at is that it is true every single time a row changes — and the moment it depends on the application remembering to set it, it stops being evidence and becomes a hopeful convention.

I have lost count of the tables where updated_at was set faithfully by the ORM in the main write path, and never once by the migration script, the manual data fix, the backfill job or the analyst with write access and a deadline. By the time anyone asks “when did this row last change, and who changed it,” the column is a polite fiction. This post fixes that in PostgreSQL 17: four audit columns, a session-level identity, and a BEFORE UPDATE trigger that makes the database — not the client — the thing that guarantees the timestamp.

The four columns, and why defaults only get you halfway

Start with the columns themselves. Use timestamptz, never timestamp — an audit time without a timezone is an audit time you cannot reconcile across regions, and now() returns timestamptz anyway. Store the actor in created_by and updated_by.

CREATE TABLE invoice (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    amount       numeric(12,2) NOT NULL,
    status       text NOT NULL DEFAULT 'draft',
    -- audit columns
    created_at   timestamptz NOT NULL DEFAULT now(),
    updated_at   timestamptz NOT NULL DEFAULT now(),
    created_by   text NOT NULL DEFAULT current_setting('app.user', true),
    updated_by   text NOT NULL DEFAULT current_setting('app.user', true)
);

The DEFAULT now() handles inserts correctly and cheaply: created_at is stamped once and never touched again. That is the easy half. The hard half is updated_at, because a column default in PostgreSQL fires on insert, not on update. Nothing about DEFAULT now() re-evaluates when the row changes. So if you rely on defaults alone, updated_at is frozen at creation time and quietly lies for the rest of the row’s life.

The usual reflex is to make the application set updated_at = now() on every write. That works right up until the code path you forgot about — and there is always a code path you forgot about. This is the same discipline problem I described in threat modelling for teams that have never done it: a control that depends on everyone remembering it, forever, is not a control. Push the guarantee down into the database.

Getting the application user into the session

The database connection is usually a single service role — app, api, whatever your pool authenticates as. That is not who changed the row. The human or service identity lives up in the application, typically decoded from a token; if you are doing that decoding properly, see validating JWTs correctly before you trust the subject claim you are about to write down.

Pass that identity into the session with set_config. Custom parameters need a namespace — a dot in the name, here app.user — or PostgreSQL rejects them. Set it once per request, immediately after checking the connection out of the pool:

-- third argument is is_local: true = reset at end of transaction
SELECT set_config('app.user', 'alice@example.com', true);

-- read it back anywhere in the same transaction
SELECT current_setting('app.user', true);   -- 'alice@example.com'

Two details matter. Pass true as the third argument so the setting is transaction-local and resets when the transaction ends — you do not want one request’s identity leaking to the next connection that borrows the same pooled backend. And read it with current_setting('app.user', true): the second argument, missing_ok, returns NULL instead of raising an error when the parameter was never set. Without it, any write on a connection that forgot to set the user would blow up.

The trigger that makes updated_at non-negotiable

Here is the enforcement. A BEFORE UPDATE trigger rewrites updated_at and updated_by on the NEW row before it is written, overriding whatever the client sent — or forgot to send. It also pins created_at and created_by back to their old values, so no update can rewrite the creation record.

CREATE OR REPLACE FUNCTION set_audit_columns()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.updated_at := now();
    NEW.updated_by := current_setting('app.user', true);
    -- creation record is immutable
    NEW.created_at := OLD.created_at;
    NEW.created_by := OLD.created_by;
    RETURN NEW;
END;
$$;

CREATE TRIGGER invoice_set_audit
    BEFORE UPDATE ON invoice
    FOR EACH ROW
    EXECUTE FUNCTION set_audit_columns();

Now every route into the table obeys the same rule. The ORM, the psql session, the migration, the one-off backfill — all of them hit the trigger, because the trigger belongs to the table, not to any application. Run UPDATE invoice SET status = 'paid' WHERE id = 1; from a bare psql prompt and updated_at still moves. That is the difference between a convention and a guarantee.

One refinement worth adding on hot tables: skip the timestamp bump when nothing actually changed. Wrapping the body in IF NEW IS DISTINCT FROM OLD THEN ... END IF; means a no-op update — SET status = status — does not churn updated_at. Whether you want that depends on whether “someone ran an update” or “a value changed” is the event you are recording. Decide deliberately.

What this is, and what it is not

Be clear about the boundary. These columns tell you when a row last changed and who the session claimed to be. They do not tell you what changed, what the previous values were, or how many times the row was touched between two reads. That is a history table or a change-data-capture stream, and it is a separate build. Audit columns are the cheap, always-on first layer; they are not a full audit trail, and selling them internally as one is how you end up with a compliance gap wearing a green tick.

Two more honest caveats. The trigger trusts app.user — it records the identity the session asserted, so the assurance is only as good as the code that set it. And superusers or roles with SET SESSION_REPLICATION_ROLE can disable triggers, so this defends against forgetful code, not a determined operator with elevated rights. For regulated data where lineage is itself a controlled artefact — the kind of provenance I wrote about in data governance under AI Act Article 10 — pair these columns with an append-only history and locked-down role grants.

But get this layer right and you close the most common failure by far: the updated_at that was accurate everywhere except the one place it mattered. Put the guarantee in the database, and no forgotten code path can quietly make your audit columns lie.