Audit log + impersonation

Append-only by grant + RLS

audit_events has RLS enabled with a SELECT policy that gates on role (admin+ sees all, member sees own actor rows, guest sees none). For writes, only the dedicated cb_audit_writer Postgres role has an INSERT grant; no role anywhere has UPDATE or DELETE on the table.

The Next.js app opens a separate postgres-jsconnection via PG_AUDIT_WRITER_URLfor audit writes. Compromise of the service-role key doesn't let an attacker rewrite history.

Doubly-logged impersonation

Every audit_events row has an actor_id (the apparent user) and an optional impersonator_id (the admin who initiated impersonation, when relevant). The auditLog writer reads both from the active JWT claims via getCurrentActor:

• When the cb_impersonate cookie is set, actor_id = sub and impersonator_id = app_metadata.impersonated_by.
• Otherwise, actor_id is the session user and impersonator_id is null.

A pure-function guard refuses any insert where impersonator_id is set but the JWT's aud claim isn't impersonation, or where impersonator_id == actor_id. A DB-level CHECK (audit_events_no_self_impersonation) backstops the latter regardless of role.

Banner is UI, audit log is truth

During impersonation, every authenticated page renders a persistent red banner driven by the presence of the cb_impersonate cookie. If a user clears the cookie via DevTools, the banner disappears — but the audit log still records every action taken under that session. The banner is a UI nicety; the audit log is the source of truth.

Hard expiry at 60 minutes

The impersonation JWT has a fixed 60-minute TTL signed with SUPABASE_JWT_SECRET. No refresh path exists. On expiry the verify returns null, the cookie is silently cleared, and the admin's normal session resumes immediately (their sb-* cookies were preserved untouched).

See Test results for the green run of tests/06_audit_integrity.sql and tests/11_impersonation_visibility.sql.