Step 6
Audit log — logAdminAction
30 min
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. Helper
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));
}
Errors go to logger.error; the main request continues.
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 });
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');
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 NULLvs system actions → allow NULL or use a sentinel email- Storing raw passwords / tokens in
details→ audit table becomes a leak vector - Audit failure bubbling into 500 → violates fire-and-forget
- 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.
Next
- 07-backup-automation