Designing an audit log for an admin panel

The question arrives in one of three ways. A customer asks who changed their account. An investor asks whether you can prove internal access is controlled. Something breaks in production and nobody remembers who ran what. All three want the same record, and it is far cheaper to write it from day one than to rebuild it from application logs later.

An audit log for an admin panel is one table, one write helper and one page. Resist anything bigger.

The table

create table audit_log (
  id         uuid primary key default gen_random_uuid(),
  actor_id   text,                              -- who acted; null for a script or job
  action     text not null,                     -- 'admin.user.banned'
  entity     text not null,                     -- 'user'
  entity_id  text,                              -- the row acted on
  metadata   jsonb not null default '{}',       -- emails, reason, from/to, ip, user agent
  created_at timestamptz not null default now()
);

create index audit_log_created_at_idx          on audit_log (created_at);
create index audit_log_entity_idx               on audit_log (entity, entity_id);
create index audit_log_actor_id_created_at_idx on audit_log (actor_id, created_at);

Four decisions worth defending.

No foreign key to the users table. The row must outlive the account. A cascade would erase exactly the history you want when a user is deleted, and a restrict would make deleting a user impossible.

Ids are text. Your auth provider owns user ids, and they are not always uuids (Clerk's are user_2abc...). Text holds all of them.

Emails copied into metadata. Record actorEmail and targetEmail at the time of the action. Joining to the live users table shows today's email, which is wrong for the same reason an old invoice does not re-price itself.

One metadata column, not ten. Each action needs different context: a ban has a reason and an expiry, a role change has from and to, an impersonation has a duration. JSONB keeps the table stable while the vocabulary grows. Keep the keys consistent per action and document them next to the code that writes them.

Name actions like events

Past tense, namespaced, dot-separated:

admin.user.banned            admin.user.unbanned
admin.user.role_changed      admin.user.sessions_revoked
admin.impersonation.started  admin.impersonation.stopped
billing.purchase.refunded

The namespace is what makes the log filterable: action like 'admin.%' is every admin action, admin.impersonation.% is every impersonation. Escape the prefix before it goes into LIKE (_ is a wildcard) and accept only names that match a strict pattern, so a stray % in a URL cannot widen the filter.

When the audit row can share a transaction, and when it cannot

The textbook advice is to write the audit row in the same transaction as the change, so the log never records a change that rolled back or misses one that committed. Do that whenever both writes are in your database:

await db.transaction(async (tx) => {
  await tx.update(orders).set({ status: "refunded" }).where(eq(orders.id, id));
  await tx.insert(auditLog).values({ actorId, action: "billing.order.refunded", entity: "order", entityId: id, metadata });
});

An admin panel on a hosted auth provider cannot. The ban happens at Clerk, in Supabase's auth schema through its admin API, or through Better Auth's endpoints, which run their own queries. There is no transaction that spans both. So pick the order that fails safely:

1. Make the change at the provider.
2. Only if it succeeded, write the audit row.
3. If the audit write fails, still report the change as done, say that the audit entry failed, and log the full entry as one JSON line so it is not lost.

Auditing first is the wrong way round: when the provider then refuses, the log describes a ban that never happened, and a log that lies once is useless as evidence.

What to record

Record what an operator did to someone else's account: bans, unbans, role changes, sign-outs, impersonation start and stop, refunds, data exports, deletions. Add the request context once, in the helper: the IP (x-vercel-forwarded-for first on Vercel, else the first hop of x-forwarded-for) and the user agent.

Do not record page views. A log that grows by a row per click is a log nobody reads.

Never record secrets. Not a password, not a session token, not an API key. Record that something changed and between what, not the value.

Append-only

No code path updates or deletes an audit row. If the database role your app uses can be restricted, revoke update and delete on the table from it. Retention, when you need it, is a deliberate scheduled job that deletes by age, run by a different role and itself logged.

The page

Newest first, keyset-paginated on (created_at, id), because the table only grows and people read the top. Filter by action (exact or namespace) and by person (actor or target, by id or email). Every user's detail page shows the entries where they are the actor or the target, which is what people use most when investigating one account.

Without a database (a hosted auth provider and nothing else), write each entry as one JSON line with "type":"audit" to the server logs, and have the audit page say that is where they are. An empty table that looks like nothing ever happened is worse than an honest notice.

Checking your work

• Ban someone, then make the provider refuse (ban an account that no longer exists): one entry for the first, none for the second.
• Delete a user: their entries survive, with the email from the time.
• Filter by admin. and by one email: only matching rows, newest first.
• Page past the first 50: no duplicates, no gaps, even while new entries arrive.
• Search the table for anything that looks like a token: nothing.