How to build a tamper-evident audit log in Postgres with one trigger

Sooner or later somebody emails support saying “I never made that change” or “where did my hours go?” If you have an audit log, the answer takes thirty seconds. If you do not, the answer takes a day, and the trust-with-the-customer cost is real.

This tutorial builds a tamper-evident audit log in Postgres using one trigger per table you want to track. No external service, no APM (Application Performance Monitoring tool, the category that includes Datadog and Sentry), no log shipper. Just SQL.

When you need an audit log (and when you do not)

You need one for any table where:

• Money is involved (orders, payments, refunds, invoices).
• Time is involved (hours logged, leave requests, billing windows).
• PII is involved (user profiles, addresses, ID numbers).
• A user can dispute a change and the answer matters legally or operationally.

You probably do not need one for:

• Append-only tables (the created_at row IS the audit; new inserts are the only operation).
• Caching tables (the source of truth lives elsewhere; replay is the audit).
• Tables under 100 rows where a manual inspection covers it.

The cost of an audit log is a small write amplification on the parent table (every UPDATE and DELETE now triggers a second insert) and disk space proportional to your change volume. For most apps both are negligible.

The audit-log table schema

One table. Catches every change on every parent table you attach the trigger to:

create schema if not exists audit;

create table audit.activity_log (
  id bigserial primary key,
  table_name text not null,
  record_id text not null,
  action text not null check (action in ('UPDATE', 'DELETE')),
  old_data jsonb,
  new_data jsonb,
  changed_by uuid,
  changed_at timestamptz default now()
);

create index activity_log_table_record_idx
  on audit.activity_log (table_name, record_id, changed_at desc);

Six things worth calling out:

old_data and new_data are JSONB. Postgres serializes the row as JSON, which means you do not need a separate audit column per parent column. One trigger function covers every table.

record_id is text to support any primary-key type (uuid, bigint, text). The trade-off is that you cast back at query time.

changed_by is a uuid because most modern apps key identity by Supabase Auth or similar; if your app uses a different identity type, change the column.

The index is (table_name, record_id, changed_at desc) because the query you actually run is “show me the recent changes to this specific row,” not “show me everything that happened on Tuesday.”

There is no INSERT action. The original row’s created_at is the insert audit. Adding INSERT to the audit doubles every write and gives no new information.

The table lives in a separate audit schema, which keeps it out of your application’s autogenerated TypeScript types and out of the default RLS scope. The application code never reads from audit.activity_log directly.

The trigger function

One function. Covers every table that opts in:

create or replace function audit.log_changes()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  v_old jsonb;
  v_new jsonb;
  v_user uuid;
begin
  -- Strip sensitive columns from the audit payload
  if tg_op = 'UPDATE' then
    v_old := to_jsonb(old) - 'encrypted_password' - 'reset_token';
    v_new := to_jsonb(new) - 'encrypted_password' - 'reset_token';
  elsif tg_op = 'DELETE' then
    v_old := to_jsonb(old) - 'encrypted_password' - 'reset_token';
  end if;

  -- Try to capture the user making the change
  begin
    v_user := auth.uid();
  exception when others then
    v_user := null;
  end;

  insert into audit.activity_log (
    table_name, record_id, action, old_data, new_data, changed_by
  ) values (
    tg_table_name,
    coalesce((new).id::text, (old).id::text),
    tg_op,
    v_old,
    v_new,
    v_user
  );

  return coalesce(new, old);
end;
$$;

Three clauses earn their place.

security definer lets the function run with elevated privileges so it can write to audit.activity_log even when the calling session has no direct permissions on the audit schema. This is the point: the application user can change the parent row but cannot reach into the audit log to cover their tracks.

set search_path = '' closes the search-path injection vulnerability that SECURITY DEFINER opens. Every table reference inside the function is fully qualified (audit.activity_log), so the empty search path is safe.

The to_jsonb(new) - 'encrypted_password' syntax uses the - operator on JSONB to strip a key. Strip any column that should not appear in the audit (password hashes, reset tokens, anything you would not want in a database export sitting on someone’s laptop).

The auth.uid() call is the Supabase pattern for “get the current user.” If you are not on Supabase, replace it with whatever your stack exposes (e.g. a session variable set at the start of each request via set_config). The exception when others block handles the case where the change happens outside any user session (a migration, a scheduled job, a manual fix from psql) by setting changed_by to NULL.

return coalesce(new, old) returns new on UPDATE and old on DELETE, satisfying Postgres’s trigger contract without branching.

Attaching the trigger to a parent table

One trigger per table you want to audit. Each one points at the same function:

create trigger time_entries_audit
  after update or delete on public.time_entries
  for each row execute function audit.log_changes();

Repeat for each table that needs auditing. The trigger fires AFTER UPDATE OR DELETE so the audit row reflects the change that actually committed, not the change that was attempted. for each row means one audit row per affected parent row, which is what you want.

If you have many tables to audit, the SQL is repetitive. A do block generates them in bulk:

do $$
declare
  t text;
begin
  for t in select unnest(array[
    'time_entries', 'invoices', 'user_profiles', 'leave_requests'
  ]) loop
    execute format(
      'create trigger %I_audit
         after update or delete on public.%I
         for each row execute function audit.log_changes()',
      t, t
    );
  end loop;
end $$;

Lock the audit table down with RLS

The whole point of an audit log is that the application user cannot reach into it. Two things make this true:

-- 1. RLS on, with no default policy. Nobody reads or writes by default.
alter table audit.activity_log enable row level security;

-- 2. Admins (or whatever role is allowed) get explicit read access.
create policy "admins read audit log"
  on audit.activity_log
  for select
  using (
    exists (
      select 1 from public.user_roles
      where user_id = auth.uid() and role = 'admin'
    )
  );

-- 3. Nobody (including admins) can write. The trigger function bypasses RLS
--    because of SECURITY DEFINER; the application path is closed.
-- (No insert/update/delete policies. The absence is the lock.)

This gives you a table that the application can write to (via the trigger, which runs as the function owner) but nobody can read except admins. Even an admin cannot insert, update, or delete rows in it, because no policy grants those actions.

If your compliance regime requires the audit log to be append-only at the database level (not just by policy), you can revoke UPDATE and DELETE from every role on audit.activity_log. The trigger’s SECURITY DEFINER insert path still works because the function owner retains the insert grant.

The query you run during an incident

When a user comes to you with “I never made that change,” this is the query:

select
  changed_at,
  action,
  changed_by,
  old_data->>'hours' as old_hours,
  new_data->>'hours' as new_hours,
  jsonb_pretty(old_data) as old_state,
  jsonb_pretty(new_data) as new_state
from audit.activity_log
where table_name = 'time_entries'
  and record_id = 'the-disputed-record-id'
order by changed_at desc
limit 10;

You get back the last ten changes to that row, who made each, when, what the value used to be, and what it changed to. The user dispute resolves in the time it takes to read the result.

The jsonb_pretty formatting and the ->> operator (extract a JSON value as text) make the result readable in psql. The composite index on (table_name, record_id, changed_at desc) keeps the query fast even on a multi-million-row audit table.

What this does not catch (and what to add later)

This trigger pattern captures changes that go through Postgres. It does not capture:

• Reads. If you need to know who looked at what, you need a different mechanism (logging at the application layer, or pgaudit for built-in read logging). The trigger pattern only fires on write operations.
• Schema changes. A DROP TABLE does not fire row-level triggers. Use Postgres event triggers (ddl_command_end) if you need to audit DDL.
• Changes via direct database access. A developer running UPDATE from psql still triggers the row-level audit, but changed_by will be NULL because auth.uid() is not set outside a request context. The pattern catches the change; you cannot tell who made it. For Supabase, the Postgres Roles docs explain which role connects via which path.
• Bulk updates. A single UPDATE statement that touches 10,000 rows generates 10,000 audit rows. That is correct behavior but expensive. If you do regular bulk updates, partition audit.activity_log by month so the writes spread across smaller tables.

For more on the partial-failure class of bug that audit logs help diagnose, the silent failures in AI-built data pipelines post covers what shows up when nobody is watching the audit trail.

Putting it all together

The full migration is forty lines of SQL. One trigger function, one audit-log table, RLS policies, and triggers on the parent tables you care about. Ship it as one migration so the trigger is there before any user makes any change you might later need to explain.

When you set the auth layer up correctly, this kind of trigger is exactly the thing that makes a Supabase project auditable for SOC 2 or any other compliance regime. For the auth-layer pattern itself, see two-layer identity models in Supabase.

If you are running a Supabase-backed product and want a second pair of eyes on the audit setup before the next compliance review, let’s talk. The audit takes about three hours and covers everything in this post plus the related security patterns in the same series.