Audit log — logAdminAction

"Why did that user get blocked yesterday?" — the audit log is the first answer. Every mutation in the hub flows through logAdminAction.

1. Schema

CREATE TABLE IF NOT EXISTS audit_logs (
  id          BIGSERIAL PRIMARY KEY,
  user_id     UUID,
  user_email  TEXT,
  action      VARCHAR(40) NOT NULL,
  resource    VARCHAR(80) NOT NULL,
  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);

Dot-namespaced resource (blog.post, market.user) enables ILIKE 'blog.%' per-domain views.

2. Two policies by risk

export function logAdminAction(input: LogInput): void {
  const run = async () => {
    const session = input.request
      ? await verifySessionFromRequest(input.request)
      : await verifySession();
    const ip = resolveIp(input.request);
    await centralPool.query(
      `INSERT INTO audit_logs (user_email, action, resource, resource_id, details, ip_address)
       VALUES ($1,$2,$3,$4,$5,$6)`,
      [
        session?.email ?? 'system@internal',
        input.action, input.resource,
        input.resourceId ?? null, input.details ?? {}, ip,
      ]
    );
  };
  run().catch((e) => logger.error('audit_log_failed', e));
}

This asynchronous path fits reads and reversible ordinary changes. Deletion, permission changes, and initialization first await a durable intent record. If intent audit fails, the mutation must not start.

const audited = await logAdminActionDurable({
  action: 'DELETE_INTENT', resource: 'blog.post', resourceId: id,
  details: { reason }, request: req,
});
if (!audited) return NextResponse.json(
  { ok: false, mutationApplied: false, error: 'audit_unavailable' },
  { status: 503 },
);

If result audit fails after work may have applied, expose mutationApplied: true and warn against a blind retry.

3. API route call

export async function DELETE(req: NextRequest, { params }) {
  const { id } = await params;
  const { reason } = await req.json();
  if (!reason || reason.trim().length < 30)
    return NextResponse.json({ error: 'reason 30+ chars' }, { status: 400 });

  const audited = await logAdminActionDurable({
    action: 'DELETE_INTENT',
    resource: 'blog.post',
    resourceId: id,
    details: { reason },
    request: req,
  });
  if (!audited) return NextResponse.json({ error: 'audit unavailable' }, { status: 503 });

  await queryBlog(`DELETE FROM posts WHERE id = $1`, [id]);
  logAdminAction({ action: 'DELETE', resource: 'blog.post', resourceId: id, details: { reason }, request: req });
  return NextResponse.json({ ok: true });
}

API-layer rejection is the only reliable place. DB triggers or client checks can be bypassed.

4. Server Action call

'use server';
export async function deletePostAction(id: string, reason: string) {
  if (reason.trim().length < 30) throw new Error('reason 30+ chars');
  const audited = await logAdminActionDurable({
    action: 'DELETE_INTENT', resource: 'blog.post', resourceId: id, details: { reason },
  });
  if (!audited) throw new Error('Audit unavailable; deletion did not start.');
  await queryBlog(`DELETE FROM posts WHERE id = $1`, [id]);
  logAdminAction({ action: 'DELETE', resource: 'blog.post', resourceId: id, details: { reason } });
  revalidatePath('/admin/blog/posts');
}

No request → fall back to cookies() for actor.

5. Viewer

export default async function Page({ searchParams }) {
  const sp = await searchParams;
  const logs = await queryCentral<AuditLog>(
    `SELECT ... FROM audit_logs
      WHERE ($1 = '' OR resource LIKE $1 || '%')
      ORDER BY created_at DESC
      LIMIT 50 OFFSET $2`,
    [sp.resource ?? '', ((Number(sp.page ?? 1)) - 1) * 50]
  );
  return <AuditLogView initialRows={logs} />;
}

6. Regression test

const { mockQuery } = vi.hoisted(() => ({ mockQuery: vi.fn() }));
vi.mock('./db', () => ({ centralPool: { query: mockQuery } }));

it('fills user_email in Server Action path via cookies fallback', async () => {
  mockQuery.mockResolvedValueOnce({ rows: [] });
  logAdminAction({ action: 'DELETE', resource: 'blog.post', resourceId: '1' });
  await new Promise((r) => setImmediate(r));
  expect(mockQuery).toHaveBeenCalled();
});

7. Gotchas

user_id NOT NULL vs system actions → allow NULL or use a sentinel email
Storing raw passwords / tokens in details → audit table becomes a leak vector
Ignoring every audit failure → high-risk work succeeds without durable evidence
Returning only a generic 500 after result-audit failure → operators may repeat an already-applied mutation
Millions of rows per year → plan monthly partitioning

Closing

Audit logs are not incident-only tools; they are the everyday "why did that happen" answer. Treat reason as a required column from day one.

07-backup-automation