Supabase + Better Auth Database Audit Logging Setup

This guide explains how to implement full database-level audit logging in Supabase using PostgreSQL triggers, capturing:

Before and after row values
Diffs on UPDATE
Primary key snapshots
Executed SQL query
Better Auth session token
Better Auth user ID
Better Auth email
Better Auth user’s full name
Fallback to session_user when no Better Auth session exists

1. Create the Audit Schema


CREATE SCHEMA IF NOT EXISTS audit;
 
GRANT USAGE ON SCHEMA audit TO postgres, anon, authenticated, service_role;

2. Create the `logged_actions` Table


CREATE TABLE IF NOT EXISTS audit.logged_actions (
  id bigserial PRIMARY KEY,
  action_tstamp_tx TIMESTAMPTZ NOT NULL DEFAULT now(),
 
  schema_name TEXT,
  table_name TEXT,
 
  -- Better Auth context
  user_name TEXT,        -- session token
  user_id TEXT,          -- Better Auth userId
  user_email TEXT,       -- email
  user_full_name TEXT,   -- user's name
 
  action TEXT,           -- 'I', 'U', 'D'
 
  original_data JSONB,
  new_data JSONB,
  diff_data JSONB,
  pk_data JSONB,
 
  query TEXT
);

3. Grant Permissions to Insert Into Audit Table


GRANT INSERT ON audit.logged_actions TO anon, authenticated, service_role;

4. Updated Audit Trigger Function (Better Auth + Full Diff)


CREATE OR REPLACE FUNCTION audit.if_modified_func()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public, audit, pg_catalog
AS $body$
DECLARE
  v_headers_txt     text;
  v_headers         json;
  v_session_token   text;
  v_user_id         text;
  v_user_email      text;
  v_user_full_name  text;
 
  v_old   jsonb;
  v_new   jsonb;
  v_diff  jsonb;
  v_pk    jsonb;
BEGIN
  v_headers_txt := current_setting('request.headers', true);
 
  IF v_headers_txt IS NOT NULL AND v_headers_txt <> '' THEN
    v_headers := v_headers_txt::json;
 
    v_session_token := nullif(v_headers ->> 'x-better-auth-session', '');
  END IF;
 
  IF v_session_token IS NOT NULL THEN
    v_session_token := split_part(v_session_token, '.', 1);
 
    BEGIN
      SELECT s."userId"
      INTO v_user_id
      FROM public.session AS s
      WHERE s.token = v_session_token
      ORDER BY s."createdAt" DESC
      LIMIT 1;
    EXCEPTION WHEN others THEN
      v_user_id := NULL;
    END;
  END IF;
 
  IF v_user_id IS NOT NULL THEN
    BEGIN
      SELECT u."email", u."name"
      INTO v_user_email, v_user_full_name
      FROM public."user" AS u
      WHERE u."id" = v_user_id
      LIMIT 1;
    EXCEPTION WHEN others THEN
      v_user_email := NULL;
      v_user_full_name := NULL;
    END;
  END IF;
 
  IF v_user_email IS NULL THEN
    v_user_email := session_user::text;
  END IF;
 
  IF TG_OP = 'UPDATE' THEN
    v_old := to_jsonb(OLD);
    v_new := to_jsonb(NEW);
 
    SELECT COALESCE(
      jsonb_object_agg(
        key,
        jsonb_build_object(
          'old', v_old->key,
          'new', v_new->key
        )
      ),
      '{}'::jsonb
    )
    INTO v_diff
    FROM jsonb_object_keys(v_new) AS key
    WHERE v_old->key IS DISTINCT FROM v_new->key;
 
    SELECT COALESCE(
      jsonb_object_agg(a.attname, v_new->a.attname),
      '{}'::jsonb
    )
    INTO v_pk
    FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey)
    WHERE i.indrelid = TG_RELID AND i.indisprimary;
 
    INSERT INTO audit.logged_actions (
      schema_name, table_name,
      user_name, user_id, user_email, user_full_name,
      action, original_data, new_data, diff_data, pk_data, query
    )
    VALUES (
      TG_TABLE_SCHEMA, TG_TABLE_NAME,
      v_session_token, v_user_id, v_user_email, v_user_full_name,
      'U', v_old, v_new, v_diff, v_pk, current_query()
    );
 
    RETURN NEW;
 
  ELSIF TG_OP = 'DELETE' THEN
    v_old := to_jsonb(OLD);
 
    SELECT COALESCE(
      jsonb_object_agg(a.attname, v_old->a.attname),
      '{}'::jsonb
    )
    INTO v_pk
    FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey)
    WHERE i.indrelid = TG_RELID AND i.indisprimary;
 
    INSERT INTO audit.logged_actions (
      schema_name, table_name,
      user_name, user_id, user_email, user_full_name,
      action, original_data, pk_data, query
    )
    VALUES (
      TG_TABLE_SCHEMA, TG_TABLE_NAME,
      v_session_token, v_user_id, v_user_email, v_user_full_name,
      'D', v_old, v_pk, current_query()
    );
 
    RETURN OLD;
 
  ELSIF TG_OP = 'INSERT' THEN
    v_new := to_jsonb(NEW);
 
    SELECT COALESCE(
      jsonb_object_agg(a.attname, v_new->a.attname),
      '{}'::jsonb
    )
    INTO v_pk
    FROM pg_index i
    JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY (i.indkey)
    WHERE i.indrelid = TG_RELID AND i.indisprimary;
 
    INSERT INTO audit.logged_actions (
      schema_name, table_name,
      user_name, user_id, user_email, user_full_name,
      action, new_data, pk_data, query
    )
    VALUES (
      TG_TABLE_SCHEMA, TG_TABLE_NAME,
      v_session_token, v_user_id, v_user_email, v_user_full_name,
      'I', v_new, v_pk, current_query()
    );
 
    RETURN NEW;
  END IF;
 
  RETURN NULL;
END;
$body$;
 
ALTER FUNCTION audit.if_modified_func() OWNER TO postgres;

5. Add a Trigger to Any Table

Example for the public.clients table:


DROP TRIGGER IF EXISTS clients_if_modified_trigger ON public.clients;
 
CREATE TRIGGER clients_if_modified_trigger
AFTER INSERT OR UPDATE OR DELETE ON public.clients
FOR EACH ROW
EXECUTE FUNCTION audit.if_modified_func();

Or add trigger to all tables in public schema in one go:


DO $$
DECLARE
  r record;
BEGIN
  FOR r IN
    SELECT tablename
    FROM pg_tables
    WHERE schemaname = 'public'
      AND tablename NOT IN ('excluded_table')  -- add more exclusions if you want
  LOOP
    -- Only create the trigger if it doesn't already exist on this table
    IF NOT EXISTS (
      SELECT 1
      FROM pg_trigger t
      JOIN pg_class c ON t.tgrelid = c.oid
      JOIN pg_namespace n ON c.relnamespace = n.oid
      WHERE t.tgname = 'audit_if_modified_trg'
        AND n.nspname = 'public'
        AND c.relname = r.tablename
    ) THEN
      EXECUTE format($f$
        CREATE TRIGGER audit_if_modified_trg
        AFTER INSERT OR UPDATE OR DELETE ON public.%I
        FOR EACH ROW
        EXECUTE FUNCTION audit.if_modified_func();
      $f$, r.tablename);
    END IF;
  END LOOP;
END;
$$;

6. Update Your Supabase Server Client (Next.js + Better Auth)

Supabase client initiatior function located in server.ts needs to be tweaked to pass better-auth session token in headers:


import { cookies } from "next/headers";
 
import { createServerClient } from "@supabase/ssr";
import type { Database } from "./database.types";
 
export async function createClient() {
	const cookieStore = await cookies();
 
	// Get Better Auth session cookie to pass in headers
	const sessionCookie = cookieStore.get("better-auth.session_token");
	const sessionToken = sessionCookie?.value
		? sessionCookie.value.split(".")[0]
		: undefined;
	console.log("sessionToken", sessionToken);
 
	return createServerClient<Database>(
		process.env.NEXT_PUBLIC_SUPABASE_URL!,
		process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
		{
			global: {
				headers: sessionToken ? { "x-better-auth-session": sessionToken } : {}
			},
			cookies: {
				getAll() {
					return cookieStore.getAll();
				},
				setAll(cookiesToSet) {
					try {
						cookiesToSet.forEach(({ name, value, options }) =>
							cookieStore.set(name, value, options),
						);
					} catch {
						// The `setAll` method was called from a Server Component.
						// This can be ignored if you have middleware refreshing
						// user sessions.
					}
				},
			},
		},
	);
}

7. What Happens When There Is No Session Header?

The audit row still logs correctly, but:

user_name = NULL
user_id = NULL
user_email = authenticator (DB role)
user_full_name = NULL

No errors — just no Better Auth user context.

8. Querying Your Logs

All logs for a given user:


SELECT *
FROM audit.logged_actions
WHERE user_id = 'USER_ID_HERE'
ORDER BY action_tstamp_tx DESC;

All changes to a specific table:


SELECT *
FROM audit.logged_actions
WHERE table_name = 'clients'
ORDER BY action_tstamp_tx DESC;

Only rows where Better Auth session was used:


SELECT *
FROM audit.logged_actions
WHERE user_id IS NOT NULL;

9. Example Logged Entry


{
  "id": 204,
  "schema_name": "public",
  "table_name": "clients",
  "action": "U",
  "user_name": "GJiRWIaiQpdFVA1FESvCKevO76MlFtvR",
  "user_id": "HroWCM59EW0LxVvxeuKhKO1KKARV46SH",
  "user_email": "coach@example.com",
  "user_full_name": "Coach Name",
  "original_data": { ... },
  "new_data": { ... },
  "diff_data": { "name": { "old": "A", "new": "B" } },
  "pk_data": { "id": "uuid" },
  "query": "UPDATE ... "
}