Most audit logs get written by application code. A service function calls the database, then calls a logging function right after, two separate steps, two separate places to remember. Forget the second call in one code path, a new API route, an admin script, a one-off fix run straight from a database console, and that mutation happens with no record of it. Nothing breaks. No error. The gap just sits there undiscovered until the day you need history that isn't there.
The fix isn't a better logging library or a stricter code review checklist. It's moving the write itself out of application code entirely. If the database writes the log row as a direct consequence of the mutation, in the same transaction, there's no second step left for anyone to forget. The log becomes a property of the schema, not a habit every contributor has to maintain.
Here's how to build that, with Postgres triggers, using a real activity_log table and real trigger functions from Atlas, a project-management app I built.
The table the whole pattern hangs off of
create table public.activity_log (
id uuid primary key default gen_random_uuid(),
project_id uuid not null references public.projects(id) on delete cascade,
actor_id uuid references public.profiles(id) on delete set null,
actor_name text not null,
verb text not null check (verb in (
'project_created', 'project_updated',
'task_created', 'task_status_changed', 'task_updated', 'task_deleted',
'member_added', 'member_removed'
)),
entity_type text not null check (entity_type in ('project', 'task', 'project_member')),
entity_id uuid,
entity_name text not null,
metadata jsonb not null default '{}'::jsonb,
created_at timestamptz not null default now()
);
create index activity_log_project_id_created_at_idx
on public.activity_log (project_id, created_at desc);A few of these columns are doing more work than they look like.
actor_id is nullable and set to null on delete, but actor_name is a plain, required text column. That pairing is deliberate. A log entry has to outlive the user it's about. Delete a profile later, offboarding, a right-to-erasure request, whatever, and the log keeps the "who" even after that foreign key is gone.
verb is a closed enum, enforced with a CHECK constraint, not a generic insert or update or delete flag. That's what lets this read like an actual activity feed, task_status_changed, not a database changelog nobody wants to read.
entity_type, entity_id, and entity_name together are a polymorphic pointer. One table logs history for three unrelated tables, projects, tasks, and project members, instead of three separate log tables.
metadata jsonb is where the real diff lives. Its shape depends on which verb produced it, more on that shortly.
The index matches the one query this table serves: a project's own recent activity, newest first.
The permission layer, not just the trigger
None of that matters if the row that just wrote a log entry can also edit its own history afterward. This is the part that audit-log write-ups often skip, and it's the part that makes "cannot bypass" true instead of aspirational. Check what the application's own database role is allowed to do to this table:
alter table public.activity_log enable row level security;
create policy "activity_log: members can view"
on public.activity_log for select
to authenticated
using (is_project_member(auth.uid(), project_id));
grant select on public.activity_log to authenticated;Worth naming up front: this is Supabase's Postgres, authenticated is the role every logged-in request connects as, and auth.uid() reads the user's id from the JWT claims PostgREST attaches to that connection, roughly a request-scoped role plus decoded session token on most other stacks.
There's one SELECT policy, scoped to project members, and no INSERT, UPDATE, or DELETE grant to authenticated anywhere in the migrations, the role the app itself connects as. Postgres denies every privilege by default unless it's explicitly granted. No grant for writing or altering a row means no path to do either. Not a weak path, no path at all.
Which raises the obvious question: if the app's own role can't INSERT into this table, how does a new row ever get written?
The trigger function
security definer. Each function that writes to activity_log is declared with it. Here's the one attached to tasks, the most complete of the three:
create or replace function public.handle_task_activity()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
declare
_actor uuid := auth.uid();
_changes jsonb := '[]'::jsonb;
begin
if tg_op = 'INSERT' then
insert into public.activity_log
(project_id, actor_id, actor_name, verb, entity_type, entity_id, entity_name, metadata)
values
(new.project_id, _actor, activity_actor_name(_actor), 'task_created', 'task', new.id, new.title, '{}'::jsonb);
return new;
end if;
if tg_op = 'DELETE' then
if exists (select 1 from public.projects where id = old.project_id) then
insert into public.activity_log
(project_id, actor_id, actor_name, verb, entity_type, entity_id, entity_name, metadata)
values
(old.project_id, _actor, activity_actor_name(_actor), 'task_deleted', 'task', old.id, old.title, '{}'::jsonb);
end if;
return old;
end if;
if new.status is distinct from old.status then
insert into public.activity_log
(project_id, actor_id, actor_name, verb, entity_type, entity_id, entity_name, metadata)
values
(new.project_id, _actor, activity_actor_name(_actor), 'task_status_changed', 'task', new.id, new.title,
jsonb_build_object('from', old.status, 'to', new.status));
end if;
if new.title is distinct from old.title then
_changes := _changes || jsonb_build_object('field', 'title', 'from', old.title, 'to', new.title);
end if;
if new.description is distinct from old.description then
_changes := _changes || jsonb_build_object('field', 'description', 'from', old.description, 'to', new.description);
end if;
if new.due_date is distinct from old.due_date then
_changes := _changes || jsonb_build_object('field', 'due_date', 'from', old.due_date, 'to', new.due_date);
end if;
if jsonb_array_length(_changes) > 0 then
insert into public.activity_log
(project_id, actor_id, actor_name, verb, entity_type, entity_id, entity_name, metadata)
values
(new.project_id, _actor, activity_actor_name(_actor), 'task_updated', 'task', new.id, new.title, jsonb_build_object('changes', _changes));
end if;
return new;
end;
$$;Take it piece by piece.
security definer is the actual mechanism answering the question above. A function marked this way runs with its owner's privileges, typically a privileged migration role, not the caller's. The application's authenticated role triggers it with an ordinary UPDATE on tasks, a table it genuinely can write to. The function itself, running as its owner, is what reaches into activity_log. The app never touches that table directly, and doesn't need to.
_actor uuid := auth.uid() captures the acting user server-side, from the session's JWT claim, when the trigger fires. It isn't passed in as an argument, or set by a request body. Whatever the database itself believes the caller's identity is, that's the actor. Nothing upstream of the database gets a vote in who's blamed for a change.
The tg_op branches come first and return early. INSERT logs a task_created row and stops. DELETE logs task_deleted and stops, guarded by an exists check, since a cascade delete can reach this trigger after the project itself is already gone. Everything below those two early returns only runs for an update.
Status changes get their own verb, task_status_changed, with its own two-field metadata shape, separate from the generic diff below. That's a deliberate modeling choice: status is the one field this schema treats as worth naming on its own, so the log captures how a task moves through its lifecycle, not just that it changed.
Everything else, title, description, due date, falls into the generic path: an is distinct from check per column, each appending a {field, from, to} object to a growing array, only inserted if non-empty. Worth being honest about the shape: it's four hand-written comparisons, not a loop over every column. Add a fifth trackable field to tasks, and it needs a fifth comparison here too, or it changes silently. The pattern guarantees no mutation skips logging, since every branch either returns early or falls through to this check. It doesn't guarantee every column change reaches the diff. That still depends on the function knowing the column exists.
Wiring it up
The function above does nothing until something calls it:
create trigger on_task_created_activity
after insert on public.tasks
for each row execute function public.handle_task_activity();
create trigger on_task_updated_activity
after update on public.tasks
for each row execute function public.handle_task_activity();
create trigger on_task_deleted_activity
after delete on public.tasks
for each row execute function public.handle_task_activity();Three separate statements, one per event, all pointing at the same function. The tg_op branching inside tells them apart at runtime. AFTER, not BEFORE: the row already exists in the transaction, recording something that already happened, not something still pending. FOR EACH ROW, not FOR EACH STATEMENT: an update touching ten tasks fires this ten times, since the diff is per row, not per statement.
Proving it, against something that isn't the app
Everything above is a claim until it's tested against something other than the application. Here's a real psql session, connected directly to Atlas's database. No app code in this transcript.
A task that actually exists, currently todo:
select status from public.tasks where id = '7be1a49e-4002-4070-adb1-5053d4ea2645';
status
--------
todo
Set the session to look like the app's own role and a real logged-in user. One gotcha worth knowing first: set_config's third argument has to be false, not true, or the JWT claim only lasts one statement. Every query after it silently loses the session, auth.uid() goes back to null, and the write that follows just matches zero rows, no error, nothing to notice.
SET ROLE authenticated;
select set_config('request.jwt.claims',
'{"sub":"6e9c97b7-6219-4905-bc66-9e9901261de9","role":"authenticated"}', false);
A direct update, no application involved:
update public.tasks
set status = 'in_progress'
where id = '7be1a49e-4002-4070-adb1-5053d4ea2645';
UPDATE 1
Check activity_log for that project:
select verb, entity_name, metadata, created_at
from public.activity_log
where project_id = 'a0e8d23e-fc6a-45aa-928c-c69df965a479'
order by created_at desc limit 1;
verb | entity_name | metadata | created_at
------------------------+-------------+----------------------------------------+-------------------------------
task_status_changed | E2E task | {"to": "in_progress", "from": "todo"} | 2026-09-12 11:36:10.428584+00
A row appeared. Nobody called a logging function. Nobody imported an audit module. The UPDATE itself produced it, because the trigger fires on the operation, not on whichever code path performed it. A raw DELETE against the same task produces the same result, a task_deleted row, same mechanism, nowhere to have skipped it.
That's one half of the claim: the mutation can't happen without the log row appearing. The other half is sharper. Can the log itself be edited afterward, to cover up what happened?
update public.activity_log
set metadata = '{"tampered": true}'::jsonb
where id = '41ad0934-3e0a-4ea6-9e9a-318957332fb4';
ERROR: permission denied for table activity_log
HINT: Grant the required privileges to the current role with: GRANT UPDATE ON public.activity_log TO authenticated;
delete from public.activity_log
where id = '41ad0934-3e0a-4ea6-9e9a-318957332fb4';
ERROR: permission denied for table activity_log
HINT: Grant the required privileges to the current role with: GRANT DELETE ON public.activity_log TO authenticated;
Same role, same active session, same table, and Postgres refuses outright. Not a silently filtered zero rows, a hard permission error, because the grant that would make either statement legal doesn't exist. The claim, demonstrated twice: the application's own role can produce the log, and cannot touch it once it exists.
What this costs, and what it doesn't guarantee
Every mutation to a tracked table now runs an extra function, on every insert, update, and delete, in the same transaction. For a handful of tables in a project-management app, that overhead doesn't register. For a table taking write load in the thousands per second, measure it first, not something to wave off as "just a trigger."
One more scoping note: authenticated also holds TRUNCATE by Supabase default, but PostgREST has no REST verb mapping to it, so nothing in the app's real surface can reach it. Revoking it protects against nothing, since reaching it needs the database password directly, and anyone holding that can just re-grant it or drop the table outright. The claim here is about the application's write path, not surviving a compromised credential, which no schema defends against.
security definer earns the trust it's given by staying boring. It runs with elevated privilege, so the discipline that matters is keeping its logic fixed and hardcoded, no dynamic SQL a caller could influence. Before trusting this pattern in your own schema, audit every security definer function in it, not just the trigger you just wrote:
select p.proname, p.prosecdef
from pg_proc p
join pg_namespace n on n.oid = p.pronamespace
where n.nspname = 'public' and p.prosecdef = true;Read every result's body for EXECUTE, format(), or any string-built query. The functions this pattern adds, the three handle_*_activity triggers and one small actor-name helper, are each a handful of lines of fixed logic, no dynamic SQL, no string concatenation, nothing built from caller input. That's what makes the elevated privilege safe here. Don't assume it holds elsewhere. Check each function on its own.
The per-column comparisons are an ongoing maintenance cost, not a one-time setup step: a new trackable column needs its own comparison added, or its changes go uncaptured.
project_id also uses on delete cascade, deliberate here since a deleted project leaves no feed to show. To make the log outlive the project instead, switch that FK to on delete set null, like actor_id, or drop it, rather than just removing the cascade, which would only fail the project delete on a foreign-key violation.
Skip this if the data doesn't need it. A trusted internal tool with no compliance requirement, and no real cost to a missed entry, doesn't need a function running extra on every write. Reach for this when the log has to survive the application being wrong, buggy, or bypassed, not just a developer remembering to call a function.