The AI decision log

-- ai_decision_log.sql (PostgreSQL)
-- From the talk "Your AI feature is an architecture problem" (netninjas.eu/events).
-- One row per AI proposal and the decision on it. The approval service writes
-- the row; the model never does.

CREATE TABLE ai_decision_log (
  request_id     uuid        PRIMARY KEY,
  created_at     timestamptz NOT NULL DEFAULT now(),
  tenant_id      text        NOT NULL,
  actor_id       text        NOT NULL,              -- the signed-in user or service that asked
  use_case       text        NOT NULL,              -- a fixed name, such as 'onboarding-document-check'
  input_ref      text        NOT NULL,              -- a pointer to the input in the system of record
  input_sha256   text        NOT NULL,              -- proves which version of the input the model saw
  context_refs   jsonb       NOT NULL DEFAULT '[]', -- IDs and versions of the retrieved documents
  model          text        NOT NULL,
  prompt_version text        NOT NULL,
  output         jsonb       NOT NULL,              -- the proposal, as structured data
  checks         jsonb       NOT NULL DEFAULT '[]', -- each deterministic rule and its result
  decision       text        NOT NULL CHECK (decision IN ('approved', 'rejected', 'edited', 'expired')),
  decided_by     text        NOT NULL,              -- a person, or the ID of the rule that decided
  decided_at     timestamptz NOT NULL,
  action_ref     text,                              -- the resulting call in the system of record, if any
  corrects       uuid        REFERENCES ai_decision_log (request_id), -- a correction points at the old row
  input_tokens   integer,
  output_tokens  integer,
  latency_ms     integer
);

CREATE INDEX ai_decision_log_tenant_time ON ai_decision_log (tenant_id, created_at);

-- Append-only: the application role may insert and read, never change or delete.
CREATE ROLE ai_log_writer NOLOGIN;
REVOKE ALL ON ai_decision_log FROM PUBLIC;
GRANT INSERT, SELECT ON ai_decision_log TO ai_log_writer;