← back to PoppyPetitions
PoppyPetitions P1: dedicated audit_events table + lib/audit.ts best-effort logger
77c4ca89fd4c59a0ec4f0d3a20176f9bdbc9c2d1 · 2026-05-31 13:44:42 -0700 · Steve Abrams
Fills the gap from P1-E (b444995), which stored the session<->agent binding
in compute_usage.metadata - that only has rows when Gemini bills compute, so
non-billing audit-worthy actions (denied agent lookups, rejected oversized
writes, logins) left no trail. New poppy.audit_events table + logAudit() cover
all AI/write routes, best-effort (never throws, never blocks the request).
- migrations/0001_audit_events.sql: idempotent, additive-only, no down-migration.
Apply manually to the poppy DB. NOT to be applied to the compromised prod
Kamatera host until it is rebuilt/cleaned.
- lib/audit.ts: logAudit / requestProvenance / auditFromRequest.
- Full-project tsc --noEmit: zero errors.
- Wiring logAudit into the petitions/vote/comment POST routes is a separate
follow-up, beyond this board item's scope.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
A lib/audit.tsA migrations/0001_audit_events.sql
Diff
commit 77c4ca89fd4c59a0ec4f0d3a20176f9bdbc9c2d1
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Sun May 31 13:44:42 2026 -0700
PoppyPetitions P1: dedicated audit_events table + lib/audit.ts best-effort logger
Fills the gap from P1-E (b444995), which stored the session<->agent binding
in compute_usage.metadata - that only has rows when Gemini bills compute, so
non-billing audit-worthy actions (denied agent lookups, rejected oversized
writes, logins) left no trail. New poppy.audit_events table + logAudit() cover
all AI/write routes, best-effort (never throws, never blocks the request).
- migrations/0001_audit_events.sql: idempotent, additive-only, no down-migration.
Apply manually to the poppy DB. NOT to be applied to the compromised prod
Kamatera host until it is rebuilt/cleaned.
- lib/audit.ts: logAudit / requestProvenance / auditFromRequest.
- Full-project tsc --noEmit: zero errors.
- Wiring logAudit into the petitions/vote/comment POST routes is a separate
follow-up, beyond this board item's scope.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
lib/audit.ts | 121 +++++++++++++++++++++++++++++++++++++++
migrations/0001_audit_events.sql | 66 +++++++++++++++++++++
2 files changed, 187 insertions(+)
diff --git a/lib/audit.ts b/lib/audit.ts
new file mode 100644
index 0000000..f5d93d7
--- /dev/null
+++ b/lib/audit.ts
@@ -0,0 +1,121 @@
+// 2026-05-31 (P1-deferred): first-class audit logging for PoppyPetitions.
+//
+// Companion to migrations/0001_audit_events.sql. P1-E (commit b444995) recorded
+// the session ↔ agent binding inside poppy.compute_usage.metadata, but that row
+// only exists when a Gemini call bills compute. This module writes to the
+// dedicated poppy.audit_events table so audit-worthy actions that DON'T bill
+// compute — a denied agent lookup, a rejected oversized write, a login — still
+// leave a forensic trail.
+//
+// Design rules:
+// - BEST-EFFORT, NEVER THROWS. An audit-write failure must never turn a
+// successful request into a 500. Every path is wrapped; failures are
+// console.error'd and swallowed. Callers do NOT await for correctness —
+// they may `void logAudit(...)` fire-and-forget, or await it when they
+// want ordering, but either way it cannot reject.
+// - Additive: depends only on the existing query() helper and the new table.
+// - Request provenance (ip / user-agent) is extracted defensively from the
+// NextRequest headers — both are spoofable and only useful as correlation
+// hints, never as a trust signal.
+
+import type { NextRequest } from 'next/server';
+import { query } from '@/lib/db';
+
+/** Outcome of the audited action. */
+export type AuditStatus = 'ok' | 'error' | 'denied';
+
+export interface AuditEvent {
+ /** Dotted action verb, e.g. 'petition.create', 'vote.cast', 'agent.resolve.denied'. */
+ action: string;
+ /** Authenticated admin session (verifyAuth() return value), if any. */
+ actorSession?: string | null;
+ /** Agent id the request claimed to act as, if any. */
+ agentId?: string | null;
+ /** What kind of thing was acted on, e.g. 'petition', 'comment', 'agent'. */
+ resourceType?: string | null;
+ /** Id of the acted-on resource, if known. */
+ resourceId?: string | null;
+ /** Outcome — defaults to 'ok'. */
+ status?: AuditStatus;
+ /** HTTP status code mirrored onto the row, if applicable. */
+ statusCode?: number | null;
+ /** Best-effort request provenance. */
+ ip?: string | null;
+ userAgent?: string | null;
+ /** Forward-compatible extra fields (token counts, error message, etc.). */
+ metadata?: Record<string, unknown>;
+}
+
+/**
+ * Pull best-effort client provenance out of a NextRequest. Both fields are
+ * spoofable; treat them as correlation hints only. x-forwarded-for may be a
+ * comma-separated list (proxy chain) — we keep the first hop.
+ */
+export function requestProvenance(
+ request: NextRequest,
+): { ip: string | null; userAgent: string | null } {
+ try {
+ const xff = request.headers.get('x-forwarded-for');
+ const ip = xff
+ ? xff.split(',')[0]!.trim()
+ : request.headers.get('x-real-ip');
+ const userAgent = request.headers.get('user-agent');
+ return { ip: ip || null, userAgent: userAgent || null };
+ } catch {
+ return { ip: null, userAgent: null };
+ }
+}
+
+/**
+ * Append a row to poppy.audit_events. Best-effort: returns the new row id on
+ * success, or null on any failure (never throws). Safe to fire-and-forget:
+ *
+ * void logAudit({ action: 'petition.create', actorSession: user,
+ * agentId: author_id, resourceType: 'petition',
+ * resourceId: petition.id, status: 'ok', statusCode: 201 });
+ */
+export async function logAudit(event: AuditEvent): Promise<string | null> {
+ try {
+ const r = await query<{ id: string }>(
+ `INSERT INTO poppy.audit_events
+ (actor_session, agent_id, action, resource_type, resource_id,
+ status, status_code, ip, user_agent, metadata)
+ VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10)
+ RETURNING id`,
+ [
+ event.actorSession ?? null,
+ event.agentId ?? null,
+ event.action,
+ event.resourceType ?? null,
+ event.resourceId ?? null,
+ event.status ?? 'ok',
+ event.statusCode ?? null,
+ event.ip ?? null,
+ event.userAgent ?? null,
+ JSON.stringify(event.metadata ?? {}),
+ ],
+ );
+ return r.rows[0]?.id ?? null;
+ } catch (e) {
+ // Swallow — an audit failure must never break the request it describes.
+ console.error('[audit] logAudit failed:', (e as Error).message, 'action:', event.action);
+ return null;
+ }
+}
+
+/**
+ * Convenience wrapper combining requestProvenance + logAudit so route code can
+ * audit in one line with the request in hand:
+ *
+ * await auditFromRequest(request, {
+ * action: 'vote.cast', actorSession: user, agentId,
+ * resourceType: 'petition', resourceId: id, statusCode: 200,
+ * });
+ */
+export async function auditFromRequest(
+ request: NextRequest,
+ event: Omit<AuditEvent, 'ip' | 'userAgent'>,
+): Promise<string | null> {
+ const { ip, userAgent } = requestProvenance(request);
+ return logAudit({ ...event, ip, userAgent });
+}
diff --git a/migrations/0001_audit_events.sql b/migrations/0001_audit_events.sql
new file mode 100644
index 0000000..5b9fb23
--- /dev/null
+++ b/migrations/0001_audit_events.sql
@@ -0,0 +1,66 @@
+-- 2026-05-31 (P1-deferred): dedicated audit-events table for PoppyPetitions.
+--
+-- Background: P1-E (commit b444995) deliberately AVOIDED a schema migration and
+-- stuffed the session ↔ agent ↔ action triple into the existing
+-- poppy.compute_usage.metadata JSONB column. That was the right call for a
+-- hotfix, but compute_usage only has a row when a Gemini call happens — so any
+-- audit-worthy action that does NOT bill compute (a rejected write, a 404'd
+-- agent lookup, a login, a settings change) leaves no trail. This migration
+-- adds a first-class append-only audit log that every AI/write route can call,
+-- independent of whether the action billed compute.
+--
+-- Idempotent: safe to run repeatedly (CREATE ... IF NOT EXISTS throughout).
+-- Additive only: no existing object is altered or dropped. No down-migration is
+-- provided on purpose — an audit table is not something we want a deploy script
+-- to be able to drop.
+--
+-- Apply (local poppy DB):
+-- psql "$DATABASE_URL" -f migrations/0001_audit_events.sql
+-- Apply (prod) is BLOCKED until the Kamatera host is rebuilt/cleaned — do not
+-- ship this against the compromised production host.
+
+SET search_path TO poppy, public;
+
+CREATE TABLE IF NOT EXISTS poppy.audit_events (
+ id BIGSERIAL PRIMARY KEY,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
+
+ -- WHO: the authenticated admin session (verifyAuth() return value) that
+ -- initiated the request, and the agent identity it claimed to act as.
+ actor_session TEXT,
+ agent_id TEXT REFERENCES poppy.agents(id) ON DELETE SET NULL,
+
+ -- WHAT: a dotted action verb ('petition.create', 'vote.cast',
+ -- 'comment.create', 'gemini.call', 'agent.resolve.denied', ...) plus the
+ -- resource it acted on.
+ action TEXT NOT NULL,
+ resource_type TEXT,
+ resource_id TEXT,
+
+ -- OUTCOME: 'ok' | 'error' | 'denied'. status_code mirrors the HTTP response.
+ status TEXT NOT NULL DEFAULT 'ok',
+ status_code INTEGER,
+
+ -- WHERE FROM: best-effort request provenance for forensic correlation.
+ ip TEXT,
+ user_agent TEXT,
+
+ -- EXTRA: forward-compatible JSONB blob (token counts, error messages,
+ -- rejected-field name, etc.). Defaults to '{}' so callers can always merge.
+ metadata JSONB NOT NULL DEFAULT '{}'::jsonb
+);
+
+-- Most common query shape is "recent events, newest first", often filtered by
+-- action or agent. These three indexes cover the forensic/admin read paths.
+CREATE INDEX IF NOT EXISTS audit_events_created_at_idx
+ ON poppy.audit_events (created_at DESC);
+
+CREATE INDEX IF NOT EXISTS audit_events_action_idx
+ ON poppy.audit_events (action, created_at DESC);
+
+CREATE INDEX IF NOT EXISTS audit_events_agent_idx
+ ON poppy.audit_events (agent_id, created_at DESC);
+
+COMMENT ON TABLE poppy.audit_events IS
+ 'Append-only audit log for PoppyPetitions AI/write routes (P1, 2026-05-31). '
+ 'Written best-effort by lib/audit.ts logAudit(); never blocks the request.';
← 438eef6 P1-F: compute_usage NULL race — safeNumber + petition_id bac
·
back to PoppyPetitions
·
(newest)