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

Axis	Columns	Meaning
Who	user_id · user_email · ip_address	Actor. Internal user + (if any) external IP
When	created_at TIMESTAMPTZ	UTC, at least 1 s resolution
What	action · resource · resource_id	DELETE + pryzeet.user + 12345
Why	details JSONB.reason	Free text. Destructive or PII-related actions require 30–100 chars

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,                    -- 'pryzeet.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 (pryzeet.user · codingstairs.post · da2ari.announcement) makes ILIKE 'pryzeet.%' views convenient per-domain.

3. Helper — fire-and-forget

Blocking the request path on an audit INSERT ties operational traffic to audit write latency. Fire-and-forget, log errors.

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.

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: 'pryzeet.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 'pryzeet.%' under /admin/pryzeet/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
• INSERT failure does not bubble into a 500 (fire-and-forget)
• 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.