0
Challenge · 39Functions & Triggers in the Database
Audit logging with triggers
Dataused in ~55% of apps
"Who changed this, and what did it look like before?" is the question every production incident eventually asks, and an application that only stores the current row can never answer it — the previous value is already gone. One generic AFTER trigger, attached to a table's INSERT, UPDATE and DELETE, gives DevLog an honest history of every change, written in the same transaction as the change itself, immune to a client that forgets to log anything because the client is never the one logging it.
Core idea. An audit trigger is a single trigger function, attached to INSERT, UPDATE and DELETE alike, that inserts exactly one new row into an append-only audit_log table per change — branching on TG_OP to decide which of OLD/NEW to snapshot as jsonb, and stamping who (auth.uid()) and when (now(), by default). Because the insert into audit_log happens inside the very same trigger invocation as the write it's watching, the two are transactionally inseparable: a rolled-back write leaves no trace in the log, and a committed write always leaves exactly one.
poke it, drag it,
try to break it
try to break it
no slides here.
you drive.
you drive.
Playground, this isn't a screenshot
public.posts
mutable — this row's past values are gone once written
{"title":"Why I switched to Postgres (v2)","like_count":0}
AFTER trigger
tg_op = 'UPDATE'
public.audit_log
append-only — a new row every time, never updated
action: UPDATE
old_data: {"title":"Why I switched to Postgres","like_count":0}
new_data: {"title":"Why I switched to Postgres (v2)","like_count":0}
changed_by: auth.uid()
UPDATE has both: old_data is the row before, new_data is the row after. Diff them to see exactly which columns changed.
The rule: one audit trigger, attached to INSERT, UPDATE and DELETE, writes exactly one new
audit_log row per change. The source table can be edited or purged; the log beside it cannot — by design, the trigger function never issues an UPDATE or DELETE against its own table.↑ play a little first, then dig in