# Designing an audit log for an admin panel

> One append-only table, namespaced past-tense actions, emails copied in, and a clear rule for when the audit row can share a transaction with the change and when it cannot.

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

```sql
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:

```ts
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.

---

Agentic Boilerplate: A Next.js repo your agent already knows. Free during launch, then $99 once.

- Site map for agents: https://agenticboilerplate.com/llms.txt
- Public API: https://agenticboilerplate.com/openapi.json
- Contact: agenticstudio@gmail.com