`migrations/0001_audit_events.sql`

67 lines
-- 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.';