How do I stop an admin from editing or deleting audit log rows?

Enable row level security on the audit table and write only a SELECT policy. With RLS on and no INSERT, UPDATE, or DELETE policy, those operations are denied for every API role even if someone later grants the privilege. Rows get written by a SECURITY DEFINER trigger function that runs as the table owner, which is not subject to policies on its own table. The honest limit is that anything holding the service role key or a superuser connection can still bypass RLS, so if you need tamper evidence on top of tamper resistance, add a hash chain over each row or ship a copy to write-once storage.

Is a Postgres trigger fast enough to audit every write?

For normal application traffic, yes. Each audited row change costs one extra insert into an append-only table with no foreign keys, and the JSONB conversion of a single row is cheap. The costs that actually bite are index bloat on the audit table and bulk operations, so keep the indexes to the ones your viewer filters on, redact wide columns out of the payload, and partition by month once the table gets large. For a one-off backfill or a large migration, disable the trigger inside that transaction instead of writing a million audit rows nobody will ever read.

How long should audit logs be kept for SOC 2?

SOC 2 does not name a specific number of days. Auditors expect you to define a retention period, document it, and demonstrate that the system enforces it, which is why a scheduled purge with a legal hold flag is worth more than an unbounded table nobody trims. Decide the window with whoever owns compliance, write it into the policy document, and implement it as a pg_cron job so the evidence is the schedule itself rather than a promise. Keeping everything forever is not a stronger answer, it is an undocumented one.

Audit Logs

Problem: A B2B buyer asks who changed that invoice, and the honest answer is that nobody knows. The row shows the current value and updated_at, and that is it.

Fix: Capture every write in Postgres itself, with the actor and the before/after payload, in a table that cannot be edited after the fact. Triggers do the capture, row level security makes it insert-only, and a scheduled job enforces the retention window. Claude Code writes the SQL, you check the parts that matter.

What Belongs in an Audit Row

An audit row answers five questions: who, what, when, where, and what changed. Skip any of them and the log stops being usable in the moment somebody actually needs it.

Store the actor as an id plus a snapshot of their email and role at the time. Emails change and roles get revoked, so a join to profiles six months later gives you the wrong answer. The old_data and new_data columns hold the whole row as jsonb, and a changed_fields array makes the common query (show me every change to the status column) fast without digging into the payload.

One thing to decide up front: this design logs writes, not reads. Read logging is a different problem with a much worse cost profile, and most buyer questionnaires are asking about changes to their data plus a handful of sensitive access events like exports and impersonation. Those two get logged explicitly later in this guide, which covers the question without turning every SELECT into an insert.

The Audit Table

Put the table in its own audit schema. It keeps it out of the default PostgREST surface, out of your generated TypeScript types, and clearly outside the application data model. The identity column is bigint because this table grows faster than anything else you own.

Ask Claude Code to write the migration, then read it before applying anything.

-- supabase/migrations/20260722000000_audit_log.sql
create schema if not exists audit;

create table audit.log_entries (
  id             bigint generated always as identity primary key,
  table_schema   text not null,
  table_name     text not null,
  record_id      text,
  operation      text not null check (operation in ('INSERT', 'UPDATE', 'DELETE', 'EVENT')),
  actor_id       uuid,
  actor_email    text,
  actor_role     text,
  workspace_id   uuid,
  old_data       jsonb,
  new_data       jsonb,
  changed_fields text[] not null default '{}',
  legal_hold     boolean not null default false,
  occurred_at    timestamptz not null default now()
);

create index log_entries_table_time_idx  on audit.log_entries (table_name, occurred_at desc);
create index log_entries_actor_time_idx  on audit.log_entries (actor_id, occurred_at desc);
create index log_entries_record_idx      on audit.log_entries (table_name, record_id);
create index log_entries_workspace_idx   on audit.log_entries (workspace_id, occurred_at desc);

Four indexes, and every one of them maps to a filter in the viewer we build later. Resist adding a GIN index over new_data until you have a query that actually needs it, because it is the most expensive index type to maintain on a table whose entire job is absorbing writes.

Knowing Who Did It

Supabase puts the signed-in user's JWT claims into a Postgres setting on every request, which is how auth.uid() works. The trigger can read the same claims directly and get the actor for free on any write that came through the API.

Writes that do not carry a user JWT need a fallback. A background job running with the service role, or a query you run yourself in the SQL editor, has no sub claim, so the function also reads a custom audit.actor_id setting that server code can set explicitly.

This helper resolves the actor once and returns it as a small jsonb object. The set search_path = '' line is not optional on a SECURITY DEFINER function, and it is the reason every reference below is schema-qualified.

create or replace function audit.current_actor()
returns jsonb
language plpgsql
stable
security definer
set search_path = ''
as $$
declare
  claims jsonb;
begin
  begin
    claims := nullif(current_setting('request.jwt.claims', true), '')::jsonb;
  exception when others then
    claims := null;
  end;

  return jsonb_build_object(
    'id',    nullif(coalesce(claims ->> 'sub',   current_setting('audit.actor_id', true)), ''),
    'email', nullif(coalesce(claims ->> 'email', current_setting('audit.actor_email', true)), ''),
    'role',  coalesce(claims ->> 'role', current_setting('audit.actor_role', true), 'unknown')
  );
end;
$$;

The second argument to current_setting is missing_ok, so an unset key returns NULL instead of raising. Without it, every insert from a context that never set the value would fail.

The Trigger That Captures Everything

One trigger function serves every table. It converts the old and new rows to jsonb, removes any columns you passed as trigger arguments (password hashes, tokens, anything you do not want duplicated into a long-lived table), computes the changed keys, and inserts one row.

The changed_fields calculation compares each key in the new payload against the old one using is distinct from, which handles NULLs correctly where <> would not.

create or replace function audit.log_change()
returns trigger
language plpgsql
security definer
set search_path = ''
as $$
declare
  actor    jsonb := audit.current_actor();
  old_row  jsonb := case when tg_op in ('UPDATE', 'DELETE') then to_jsonb(old) end;
  new_row  jsonb := case when tg_op in ('INSERT', 'UPDATE') then to_jsonb(new) end;
  changed  text[] := '{}';
begin
  if tg_nargs > 0 then
    if old_row is not null then old_row := old_row - tg_argv; end if;
    if new_row is not null then new_row := new_row - tg_argv; end if;
  end if;

  if tg_op = 'UPDATE' then
    select coalesce(array_agg(n.key order by n.key), '{}')
      into changed
      from jsonb_each(new_row) as n(key, value)
     where old_row -> n.key is distinct from n.value;

    if changed = '{}' then
      return null;
    end if;
  end if;

  insert into audit.log_entries (
    table_schema, table_name, record_id, operation,
    actor_id, actor_email, actor_role, workspace_id,
    old_data, new_data, changed_fields
  ) values (
    tg_table_schema,
    tg_table_name,
    coalesce(new_row ->> 'id', old_row ->> 'id'),
    tg_op,
    (actor ->> 'id')::uuid,
    actor ->> 'email',
    actor ->> 'role',
    coalesce(new_row ->> 'workspace_id', old_row ->> 'workspace_id')::uuid,
    old_row,
    new_row,
    changed
  );

  return null;
end;
$$;

The early return null on an empty diff matters more than it looks. Plenty of ORMs and form handlers write every column back on save, and without that check you get an audit row every time somebody opens and closes an edit dialog. The return value of an AFTER trigger is ignored, so returning NULL is correct here.

Attaching the Trigger to Real Tables

Each audited table gets one AFTER trigger covering all three operations. Pass column names as arguments and they get stripped from both payloads before the insert.

create trigger audit_invoices
after insert or update or delete on public.invoices
for each row execute function audit.log_change();

create trigger audit_profiles
after insert or update or delete on public.profiles
for each row execute function audit.log_change('password_hash', 'stripe_customer_id');

Doing that by hand across twenty tables is where drift starts. This block attaches the trigger to a list of tables and is safe to re-run, so you can add a table name and apply the migration again.

do $$
declare
  t text;
begin
  foreach t in array array['invoices', 'memberships', 'workspaces', 'api_keys']
  loop
    execute format(
      'drop trigger if exists audit_%1$s on public.%1$I;
       create trigger audit_%1$s
       after insert or update or delete on public.%1$I
       for each row execute function audit.log_change();',
      t
    );
  end loop;
end;
$$;

Insert-Only by Construction

This is the part buyers ask about. Turn RLS on, grant authenticated nothing but SELECT, and write exactly one policy. With RLS enabled and no INSERT, UPDATE, or DELETE policy present, those operations are refused for every API role, and a future grant cannot quietly re-open them.

The trigger still writes, because a SECURITY DEFINER function runs as the role that owns the function, which here is the role that owns the table, and a table owner is not subject to policies on its own table unless you add force row level security. That is the whole trick: the only path that can write is the one you control.

alter table audit.log_entries enable row level security;

revoke all on audit.log_entries from anon, authenticated;
grant usage on schema audit to authenticated;
grant select on audit.log_entries to authenticated;

create policy "workspace admins read their own audit log"
on audit.log_entries
for select
to authenticated
using (public.is_workspace_admin(auth.uid(), workspace_id));

The policy leans on one helper, which you define once and reuse in every workspace-scoped policy you write. Wrapping the membership lookup in a stable SECURITY DEFINER function keeps the policy readable and lets Postgres cache the result per statement instead of re-running the subquery for every row it checks.

create or replace function public.is_workspace_admin(
  p_user_id uuid,
  p_workspace_id uuid
)
returns boolean
language sql
stable
security definer
set search_path = ''
as $$
  select exists (
    select 1
    from public.memberships m
    where m.user_id = p_user_id
      and m.workspace_id = p_workspace_id
      and m.role in ('owner', 'admin')
  );
$$;

revoke execute on function public.is_workspace_admin(uuid, uuid) from public;
grant execute on function public.is_workspace_admin(uuid, uuid) to authenticated;

It is SECURITY DEFINER on purpose. The policy has to check membership even when the caller cannot read memberships directly, and a plain subquery there would either leak the membership table or recurse into its own RLS policy.

The audit schema is not exposed through the API, so reads go through a view in public. Marking the view security_invoker = true means it runs with the caller's permissions, so the policy above still applies instead of being bypassed by the view owner.

create view public.audit_log_entries
with (security_invoker = true)
as
select id, table_name, record_id, operation,
       actor_id, actor_email, actor_role, workspace_id,
       old_data, new_data, changed_fields, occurred_at
from audit.log_entries;

grant select on public.audit_log_entries to authenticated;

Note what is missing from the view: legal_hold and table_schema stay internal. Do not assume the view is read-only just because it is a view, though. A plain projection off a single table like this one is automatically updatable in Postgres, so a write against it is a real attempt against audit.log_entries. What stops it is the same two things as before: security_invoker means the attempt runs as the caller, and the caller holds SELECT and nothing else on the underlying table.

Events That Are Not Row Changes

Plenty of what an auditor asks about never touches a table. Logins, failed logins, data exports, permission grants, support impersonation. Those get written explicitly with operation = 'EVENT' through a SECURITY DEFINER function, which is the only INSERT path exposed to your application.

create or replace function public.record_audit_event(
  p_action       text,
  p_workspace_id uuid default null,
  p_target       text default null,
  p_metadata     jsonb default '{}'::jsonb
)
returns void
language plpgsql
security definer
set search_path = ''
as $$
declare
  actor jsonb := audit.current_actor();
begin
  insert into audit.log_entries (
    table_schema, table_name, record_id, operation,
    actor_id, actor_email, actor_role, workspace_id, new_data
  ) values (
    'app',
    p_action,
    p_target,
    'EVENT',
    (actor ->> 'id')::uuid,
    actor ->> 'email',
    actor ->> 'role',
    p_workspace_id,
    p_metadata
  );
end;
$$;

grant execute on function public.record_audit_event(text, uuid, text, jsonb) to authenticated;

Background jobs are the other gap. A job running with the service role has no user JWT, so the actor would land as NULL. Set the actor inside the same transaction as the write, which is what set_config with a local scope does, and wrap the mutation in a function so the two never come apart.

create or replace function public.set_invoice_status(
  p_invoice_id uuid,
  p_status     text,
  p_actor_id   uuid
)
returns void
language plpgsql
security definer
set search_path = ''
as $$
begin
  perform set_config('audit.actor_id', p_actor_id::text, true);
  perform set_config('audit.actor_role', 'service', true);

  update public.invoices
     set status = p_status,
         updated_at = now()
   where id = p_invoice_id;
end;
$$;

A separate rpc call to set the actor first would not work, because each PostgREST request runs in its own transaction and a local setting dies with it. Same transaction or no actor.

Reading the Log From Next.js

Reads go through oRPC so the filter shape is validated once and the component compiles against the same contract. The admin middleware is defense in depth: the RLS policy is already doing the real work, and this just fails faster with a better error.

// server/orpc/audit.ts
import { os, ORPCError } from "@orpc/server";
import { z } from "zod";
import { createClient } from "@/lib/supabase/server";

export interface Context {
  userId: string;
  role: "user" | "admin";
  workspaceId: string;
}

const base = os.$context<Context>();

const requireAdmin = base.middleware(async ({ context, next }) => {
  if (context.role !== "admin") {
    throw new ORPCError("FORBIDDEN", { message: "Admins only" });
  }
  return next();
});

export const listAuditEntries = base
  .use(requireAdmin)
  .input(
    z.object({
      tableName: z.string().trim().optional(),
      operation: z.enum(["INSERT", "UPDATE", "DELETE", "EVENT"]).optional(),
      actorId: z.string().uuid().optional(),
      recordId: z.string().trim().optional(),
      from: z.string().datetime().optional(),
      to: z.string().datetime().optional(),
      page: z.number().int().min(1).default(1),
      pageSize: z.number().int().min(1).max(100).default(50),
    })
  )
  .handler(async ({ input }) => {
    const supabase = await createClient();
    const start = (input.page - 1) * input.pageSize;

    let query = supabase
      .from("audit_log_entries")
      .select("*", { count: "exact" })
      .order("occurred_at", { ascending: false })
      .range(start, start + input.pageSize - 1);

    if (input.tableName) query = query.eq("table_name", input.tableName);
    if (input.operation) query = query.eq("operation", input.operation);
    if (input.actorId) query = query.eq("actor_id", input.actorId);
    if (input.recordId) query = query.eq("record_id", input.recordId);
    if (input.from) query = query.gte("occurred_at", input.from);
    if (input.to) query = query.lte("occurred_at", input.to);

    const { data, count, error } = await query;
    if (error) throw new ORPCError("INTERNAL_SERVER_ERROR", { message: error.message });

    return {
      entries: data ?? [],
      total: count ?? 0,
      page: input.page,
      pageSize: input.pageSize,
    };
  });

export const router = { listAuditEntries };

The filter dropdown needs the list of audited tables. Deriving it from the log itself is the obvious move and it is wrong twice: a table nobody has written to yet would never appear in the dropdown, and any query that scans the log to build a menu gets slower every week. The list changes when you ship a migration, so keep it next to the migration.

// lib/audit/tables.ts

// Mirrors the trigger list in the audit migration. Add a table here in the
// same commit that attaches its trigger.
export const AUDITED_TABLES: string[] = [
  "invoices",
  "memberships",
  "workspaces",
  "api_keys",
  "profiles",
];

Worth knowing why this is not a cached database call. A "use cache" function in Next.js 16 cannot touch request-time APIs, and the Supabase server client reads cookies to find the session, so wrapping that query in "use cache" fails rather than caching. When you do need cached data from Supabase, it has to come from a client built without cookies.

The Admin Viewer

The page keeps every filter in the URL, which makes a specific slice of the log linkable in a support thread. Search params arrive as a promise in Next.js 16, so they get awaited before use.

// app/(admin)/admin/audit/page.tsx
import { createRouterClient } from "@orpc/server";
import { router } from "@/server/orpc/audit";
import { createContext } from "@/server/orpc/context";
import { AUDITED_TABLES } from "@/lib/audit/tables";
import { AuditTable } from "@/components/admin/audit-table";

interface PageProps {
  searchParams: Promise<{
    table?: string;
    op?: "INSERT" | "UPDATE" | "DELETE" | "EVENT";
    actor?: string;
    record?: string;
    page?: string;
  }>;
}

export default async function AuditPage({ searchParams }: PageProps) {
  const params = await searchParams;
  const client = createRouterClient(router, { context: await createContext() });

  const result = await client.listAuditEntries({
    tableName: params.table,
    operation: params.op,
    actorId: params.actor,
    recordId: params.record,
    page: params.page ? Number(params.page) : 1,
    pageSize: 50,
  });

  return (
    <main className="mx-auto max-w-6xl px-6 py-10">
      <h1 className="mb-6 text-2xl font-semibold">Audit log</h1>
      <AuditTable {...result} tables={AUDITED_TABLES} filters={params} />
    </main>
  );
}

The row detail is where the before/after payload earns its keep. This Client Component renders only the changed keys, side by side, so a reviewer sees "status: draft to paid" instead of two forty-key JSON blobs.

// components/admin/audit-diff.tsx
"use client";

interface AuditDiffProps {
  oldData: Record<string, unknown> | null;
  newData: Record<string, unknown> | null;
  changedFields: string[];
}

function render(value: unknown): string {
  if (value === null || value === undefined) return "(empty)";
  if (typeof value === "object") return JSON.stringify(value);
  return String(value);
}

export function AuditDiff({ oldData, newData, changedFields }: AuditDiffProps) {
  const keys =
    changedFields.length > 0
      ? changedFields
      : Object.keys(newData ?? oldData ?? {});

  return (
    <dl className="grid grid-cols-3 gap-x-4 gap-y-2 text-sm">
      {keys.map((key) => (
        <div key={key} className="contents">
          <dt className="font-medium text-muted-foreground">{key}</dt>
          <dd className="truncate text-red-600 line-through">
            {render(oldData?.[key])}
          </dd>
          <dd className="truncate text-emerald-600">{render(newData?.[key])}</dd>
        </div>
      ))}
    </dl>
  );
}

Retention That Actually Runs

A retention policy nobody enforces is worse than none, because it is a written claim you fail on. Supabase ships pg_cron, so the schedule lives next to the data instead of on a server you forget to redeploy.

Deleting a year of rows in one statement takes a long lock. This function deletes in batches, skips anything under legal hold, and returns how much it removed so the job leaves a trace in cron.job_run_details.

create extension if not exists pg_cron;

create or replace function audit.purge_expired(
  p_keep  interval default '400 days',
  p_batch integer  default 5000
)
returns bigint
language plpgsql
security definer
set search_path = ''
as $$
declare
  cutoff  timestamptz := now() - p_keep;
  removed bigint := 0;
  chunk   bigint;
begin
  loop
    delete from audit.log_entries
     where id in (
       select id
         from audit.log_entries
        where occurred_at < cutoff
          and legal_hold = false
        limit p_batch
     );

    get diagnostics chunk = row_count;
    removed := removed + chunk;
    exit when chunk = 0;
  end loop;

  return removed;
end;
$$;

select cron.schedule(
  'purge-audit-log',
  '15 3 * * *',
  $$ select audit.purge_expired() $$
);

Two rules make this defensible. Whatever window you pick, write it down in the same document the auditor reads, because the number matters less than the fact that code enforces it. And when an incident or a dispute opens, flip legal_hold on the affected rows before the next purge runs rather than after.

Four Ways This Goes Wrong

Every one of these has a cheap fix if you know about it before the table has ten million rows in it.

Trap	What happens	Fix
Actor is always NULL	Writes come from the service role or a migration, so there is no JWT to read	Set `audit.actor_id` inside the same transaction as the write
Audit rows for nothing	An ORM writes every column on save, so opening a dialog logs a change	Return early when the computed diff is empty
Secrets duplicated forever	`to_jsonb(new)` copies token and hash columns into a long-retention table	Pass those column names as trigger arguments so they get stripped
Backfill floods the log	A data migration writes a million rows and a million audit rows	`alter table ... disable trigger` inside the migration transaction, then re-enable

The last one deserves a note on scale. Once the table passes tens of millions of rows, convert it to a monthly range partition on occurred_at. Retention then becomes dropping last year's partition, which is instant, instead of deleting rows in batches and waiting on autovacuum to reclaim the space.

Teaching Claude Code the Rules

Claude will happily add an UPDATE policy to the audit table if a later prompt sounds like it needs one. A short block in CLAUDE.md stops that, and it is cheaper than catching it in review.

## Audit Log

- Audit rows live in audit.log_entries. Never add INSERT, UPDATE, or DELETE policies to it.
- Writes happen only through audit.log_change() triggers or public.record_audit_event().
- New tables holding customer data get an audit trigger in the same migration.
- Redact secret columns by passing them as trigger arguments, never by filtering in the app.
- Reads go through the public.audit_log_entries view, never the audit schema directly.

Proving It Holds

Type checks catch the TypeScript half. The database half needs its own test, and it is three statements: become an authenticated user, try to rewrite history, confirm Postgres refuses.

begin;
set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-000000000001", "role": "authenticated"}';

-- both of these must fail
update audit.log_entries set actor_email = 'nobody@example.com' where id = 1;
delete from audit.log_entries where id = 1;
rollback;

Then the usual gates before anything ships:

npx tsc --noEmit
npm run lint
npm run build

What an Orchestrated Pipeline Looks Like

A feature like this is really four features stapled together (schema, trigger, policy, viewer), and the failure mode is that one of them silently drifts from the others three sprints later. That is the gap the $29 Code Kit fills: it is a harness on top of Claude Code, $29 one-time with no subscription, that runs a feature through plan, build, evaluate, and test before it lands, with quality gates that mean zero type errors, zero lint errors, and a clean build every time. It does not replace Claude Code, which still needs a paid Anthropic plan, and it does not write your retention policy for you. It just makes the pipeline the default instead of something you remember to run.

Posted by @speedy_devv

Audit Logs

On this page