Audit Log — logAdminAction pattern

In a backend with admin capabilities, capturing who-when-what-why is more than mere tradition. It is how you satisfy privacy law (PIPA · GDPR), investigate incidents, and deter privilege misuse.

1. Four questions an audit log answers

There is no single standard. Covering all four axes is usually enough for first-pass incident response.

2. Minimal PostgreSQL schema

CREATE TABLE IF NOT EXISTS audit_logs (
  id          BIGSERIAL PRIMARY KEY,
  user_id     UUID,                                    -- NULL ok: system / cron
  user_email  TEXT,
  action      VARCHAR(40) NOT NULL,
  resource    VARCHAR(80) NOT NULL,                    -- 'catalog.user' (dot-namespaced)
  resource_id TEXT,
  details     JSONB NOT NULL DEFAULT '{}'::jsonb,
  ip_address  INET,
  created_at  TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_audit_logs_resource_created
  ON audit_logs (resource, created_at DESC);
CREATE INDEX IF NOT EXISTS idx_audit_logs_user_created
  ON audit_logs (user_id, created_at DESC);

Prefixing resource with a domain (catalog.user · content.post · profile.announcement) makes ILIKE 'catalog.%' views convenient per-domain.

3. Helper — failure policy by risk

Do not apply one policy to every action. Reads and reversible ordinary changes may isolate audit latency with asynchronous writes. Deletion, permission changes, and initialization must first durably record intent; if that write fails, the mutation does not start.

export function logAdminAction(input: {
  action: string;
  resource: string;
  resourceId?: string;
  details?: Record<string, unknown>;
  request?: Request;
}): void {
  const run = async () => {
    const session = await resolveSession(input.request);
    const ip = await resolveClientIp(input.request);
    await pool.query(
      `INSERT INTO audit_logs
         (user_id, user_email, action, resource, resource_id, details, ip_address)
       VALUES ($1,$2,$3,$4,$5,$6,$7)`,
      [session.userId, session.email, input.action, input.resource,
       input.resourceId ?? null, input.details ?? {}, ip]
    );
  };
  run().catch((e) => logger.error('audit_log_failed', e));
}

When request is absent, falling back to Next.js App Router cookies() lets Server Actions still satisfy NOT NULL user_id.

const audited = await logAdminActionDurable({
  action: 'DB_INIT_INTENT', resource: 'database', details: { mode }, request,
});
if (!audited) return { status: 503, mutationApplied: false };

If result audit fails, work may already have applied. Return neither generic success nor an ambiguous 500. Expose mutationApplied: true, auditFailed: true, and a warning against blind retries.

4. Why enforce reason

Deletions of personal data, permission changes, manual point adjustments — actions that matter for later forensics — should require a 30–100 char reason. Enforce at the API layer (reject), store under details.reason JSONB key.

if (destructive && (!reason || reason.length < 30)) {
  return NextResponse.json({ error: 'reason 30+ chars' }, { status: 400 });
}
logAdminAction({
  action: 'DELETE',
  resource: 'catalog.user',
  resourceId: String(userId),
  details: { reason, targetEmail },
});

Empty or two-character reasons make post-hoc analysis impossible; rejecting at the API layer is cheaper than cleaning up later.

5. Viewer UI

• Filters: resource · action · user_email · date range
• Sort: created_at DESC
• Pagination: offset or keyset (keyset wins at scale)
• Per-domain views: fix resource ILIKE 'catalog.%' under /admin/catalog/audit-log

6. Gotchas

System actions with NULL user_id — cron · backups · webhooks have no human actor. Allow NULL + sentinel user_email = 'system@internal'.

Audit table growth — millions of rows per year. Partition (created_at monthly) or archive.

Schema-less JSONB details — flexibility is a plus, but required keys (reason · previous_value · new_value) are easier to search if enforced in code-level helpers.

Deleting audit records — do not build a "delete audit" feature. For privacy requests, anonymize (user_email = NULL).

Missing actor fallback — Server Actions and scheduled jobs may not have a request object. Cookie-based fallback prevents NOT NULL violations — freeze it as a regression test.

7. Operational checklist

• Every mutation API · Server Action calls logAdminAction
• Destructive / PII actions enforce 30+ char reason
• resource follows dot-namespacing
• Ordinary changes isolate audit outages; high-risk actions do not start when intent audit fails
• Result-audit failure exposes possible application and forbids blind retry
• Viewer is behind admin auth
• Archive / partition plan exists

Closing

An audit log is not a tool used only during incidents; it is the first answer to everyday questions like "why did this user receive these points yesterday?". Without a reason field, audits answer when-what but never why. Treat reason as a required column from day one.

Next

• api-handler-pattern
• security/01-jwt-rotation

References: OWASP Logging Cheat Sheet · PostgreSQL JSONB.