import { Database } from "bun:sqlite";
import { createHash } from "node:crypto";
import { mkdirSync } from "node:fs";
import { dirname } from "node:path";
import { selectRequestDeliveryLineage } from "@overdeck/report-contract";
import type {
  RequestEvidenceAcknowledgementV1,
  RequestEvidenceAttachmentV1,
  RequestEvidenceEnvelopeV1,
  RequestEvidenceSourceHealthV1,
  RequestStoryAttachment,
  RequestStoryEvidenceLink,
  RequestEventKind,
  RequestStoryActor,
  RequestStoryEvent,
  RequestStoryV1,
} from "@overdeck/report-contract";
import { isPendingDecisionMarker } from "../../../modules/workstation/claude/hooks/lib/decision-marker.mjs";

export type RequestState = "asked" | "in_flight" | "blocked_needs_owner" | "orphaned" | "shipped" | "canceled";
export type RequestOrigin = "owner" | "agent-incident" | "agent-judgement";

export interface RequestRow {
  id: string;
  title: string;
  project: string;
  state: RequestState;
  priority: string;
  origin: RequestOrigin;
  asked_at: string;
  /** Exact immutable request body captured at intake. Null for pre-ledger rows. */
  original_body: string | null;
  original_body_format: "plain_text" | "markdown" | null;
  intake_source: string | null;
  intake_source_event_id: string | null;
  updated_at: string;
  worker: string | null;
  detail: string | null;
  proof_url: string | null;
  plan_ref: string | null;
  /** Explicit factory run identity. Legacy and unlinked requests remain null. */
  factory_run_id?: string | null;
  /** Friendly Claude session name when the writer can resolve one. */
  session_name: string | null;
  /** Raw CLI session identity. Null for work not owned by a CLI session. */
  session_id: string | null;
  announced_at: number | null;
  /** Always present from the live store; optional for legacy in-process callers. */
  receipt_trail?: RequestReceipt[];
  /** Server-recorded lifecycle transitions, oldest first. */
  transition_trail?: RequestTransition[];
}

export interface RequestTransition {
  id: string;
  request_id: string;
  at: string;
  from_state: RequestState;
  to_state: RequestState;
  actor: string;
  reason: string | null;
}

export interface GithubCheckRow {
  repo: string;
  run_id: number;
  name: string;
  status: string;
  conclusion: string | null;
  sha: string;
  branch: string | null;
  started_at: string | null;
  completed_at: string | null;
  work_key: string | null;
  observed_at: string;
}

/** An owner-visible lifecycle fact recorded by the request registry. */
export interface RequestReceipt {
  id: string;
  request_id: string;
  at: string;
  kind: "claimed" | "progress" | "landed" | "deployed" | "failed" | "worker-lost" | "queue-event";
  line: string;
  meta: { worker?: string; host?: string; session_id?: string };
}

interface RequestEventRow {
  id: string;
  request_id: string;
  kind: RequestEventKind;
  occurred_at: string;
  recorded_at: string;
  summary: string;
  actor_type: RequestStoryActor["type"];
  actor_id: string | null;
  actor_display_name: string | null;
  session_id: string | null;
  host_id: string | null;
  account_id: string | null;
  source_id: string;
  source_event_id: string;
  producer_id: string;
  source_record_id: string | null;
  payload: string;
}

interface RequestEvidenceLinkRow {
  kind: RequestStoryEvidenceLink["kind"];
  target_source: string;
  target_id: string;
  occurred_at: string;
  source_id: string;
  source_event_id: string;
  owner_url: string | null;
  metadata: string;
}

interface RequestEvidenceAttachmentRow {
  digest: string;
  byte_count: number;
  media_type: RequestStoryAttachment["mediaType"];
  redaction_status: RequestStoryAttachment["redactionStatus"];
  truncated: number;
  original_byte_count: number | null;
  content: Uint8Array | null;
}

interface RequestEvidenceSourceHealthRow {
  request_id: string;
  source_id: string;
  producer_id: string;
  reported_at: string;
  queue_count: number;
  oldest_queued_at: string | null;
  quarantine_count: number;
  last_acknowledged_at: string | null;
}

export type CreateRequestInput = Omit<RequestRow, "worker" | "detail" | "proof_url" | "plan_ref" | "factory_run_id" | "session_name" | "session_id" | "origin" | "original_body" | "original_body_format" | "intake_source" | "intake_source_event_id" | "announced_at" | "receipt_trail" | "transition_trail"> & {
  origin?: RequestOrigin;
  original_body?: string | null;
  original_body_format?: "plain_text" | "markdown" | null;
  intake_source?: string | null;
  intake_source_event_id?: string | null;
  worker?: string | null;
  worker_host?: string | null;
  detail?: string | null;
  proof_url?: string | null;
  plan_ref?: string | null;
  factory_run_id?: string | null;
  session_name?: string | null;
  session_id?: string | null;
};
export type UpdateRequestPatch = Partial<Pick<RequestRow, "state" | "worker" | "detail" | "proof_url" | "priority" | "updated_at" | "factory_run_id" | "session_name" | "session_id">> & { worker_host?: string | null };

export interface FireClaimInput {
  id: string;
  title: string;
  project: string;
  worker: string | null;
  worker_host?: string | null;
  detail: string | null;
  now: string;
}
export interface FireClaimResult {
  row: RequestRow;
  /** true: this call created or reopened the incident and owns it. false: an open incident with the same id already exists. */
  claimed: boolean;
}

export class RequestNotFoundError extends Error {
  constructor(id: string) {
    super(`request not found: ${id}`);
    this.name = "RequestNotFoundError";
  }
}

export class InvalidRequestTransitionError extends Error {
  constructor(readonly current: RequestState, readonly requested: RequestState) {
    super(`invalid request transition: ${current} -> ${requested}`);
    this.name = "InvalidRequestTransitionError";
  }
}

export class RequestEvidenceConflictError extends Error {
  constructor(readonly requestId: string) {
    super(`request evidence conflicts with existing request: ${requestId}`);
    this.name = "RequestEvidenceConflictError";
  }
}

export class DuplicateRequestPlanRefError extends Error {
  constructor(readonly row: RequestRow) {
    super(`duplicate request plan_ref: ${row.plan_ref}`);
    this.name = "DuplicateRequestPlanRefError";
  }
}

const STATES: readonly RequestState[] = ["asked", "in_flight", "blocked_needs_owner", "orphaned", "shipped", "canceled"];
const ORIGINS: readonly RequestOrigin[] = ["owner", "agent-incident", "agent-judgement"];
const ALLOWED_TRANSITIONS: Record<RequestState, readonly RequestState[]> = {
  // A fresh request can genuinely need an owner decision before any worker
  // can claim it. Do not force a fake claim just to make that visible.
  asked: ["in_flight", "blocked_needs_owner", "canceled"],
  in_flight: ["blocked_needs_owner", "shipped", "canceled"],
  blocked_needs_owner: ["in_flight", "canceled"],
  orphaned: ["in_flight", "canceled"],
  shipped: [],
  canceled: [],
};
const REASON_REQUIRED_STATES: readonly RequestState[] = ["blocked_needs_owner", "canceled"];

function hasPendingOwnerDecision(...values: Array<string | null | undefined>): boolean {
  return values.some((value) => isPendingDecisionMarker(value));
}

function requireBlockedOwnerDecision(state: RequestState, ...values: Array<string | null | undefined>): void {
  if (state !== "blocked_needs_owner" && hasPendingOwnerDecision(...values)) {
    throw new Error("pending owner decision must be blocked_needs_owner");
  }
}

const MAX_EVIDENCE_ATTACHMENT_BYTES = 256 * 1024;

function parseEventPayload(value: string): Record<string, unknown> {
  try {
    const parsed: unknown = JSON.parse(value);
    return parsed !== null && typeof parsed === "object" && !Array.isArray(parsed)
      ? parsed as Record<string, unknown>
      : { recordUnreadable: true };
  } catch {
    return { recordUnreadable: true };
  }
}

export class RequestsStore {
  private readonly db: Database;
  private readonly insert;
  private readonly select;
  private readonly listRows;
  private readonly claimFireQuery;
  private readonly markAnnouncedQuery;
  private readonly listReceipts;
  private readonly upsertGithubCheck;
  private readonly listGithubChecksQuery;
  private readonly listTransitions;

  constructor(path: string) {
    mkdirSync(dirname(path), { recursive: true, mode: 0o700 });
    this.db = new Database(path, { create: true });
    this.db.exec("PRAGMA journal_mode=WAL; PRAGMA synchronous=FULL; PRAGMA busy_timeout=5000;");
    this.db.exec(`CREATE TABLE IF NOT EXISTS requests (
      id TEXT PRIMARY KEY,
      title TEXT NOT NULL,
      project TEXT NOT NULL,
      state TEXT NOT NULL CHECK (state IN ('asked', 'in_flight', 'blocked_needs_owner', 'orphaned', 'shipped', 'canceled')),
      priority TEXT NOT NULL,
      origin TEXT NOT NULL DEFAULT 'owner' CHECK (origin IN ('owner', 'agent-incident', 'agent-judgement')),
      asked_at TEXT NOT NULL,
      original_body TEXT,
      original_body_format TEXT CHECK (original_body_format IS NULL OR original_body_format IN ('plain_text', 'markdown')),
      intake_source TEXT,
      intake_source_event_id TEXT,
      updated_at TEXT NOT NULL,
      worker TEXT,
      detail TEXT,
      proof_url TEXT,
      plan_ref TEXT,
      factory_run_id TEXT,
      session_name TEXT,
      session_id TEXT,
      announced_at INTEGER
    )`);
    this.db.exec(`CREATE TABLE IF NOT EXISTS receipt_trail (
      id TEXT PRIMARY KEY,
      request_id TEXT NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
      at TEXT NOT NULL,
      kind TEXT NOT NULL CHECK (kind IN ('claimed', 'progress', 'landed', 'deployed', 'failed', 'worker-lost', 'queue-event')),
      line TEXT NOT NULL CHECK (length(line) <= 120),
      meta TEXT NOT NULL
    )`);
    this.db.exec("CREATE INDEX IF NOT EXISTS receipt_trail_request_at ON receipt_trail(request_id, at, id)");
    this.db.exec(`CREATE TABLE IF NOT EXISTS github_checks (
      repo TEXT NOT NULL,
      run_id INTEGER NOT NULL,
      name TEXT NOT NULL,
      status TEXT NOT NULL,
      conclusion TEXT,
      sha TEXT NOT NULL,
      branch TEXT,
      started_at TEXT,
      completed_at TEXT,
      work_key TEXT,
      observed_at TEXT NOT NULL,
      PRIMARY KEY (repo, run_id)
    )`);
    this.db.exec("CREATE INDEX IF NOT EXISTS github_checks_sha ON github_checks(repo, sha)");
    this.db.exec(`CREATE TABLE IF NOT EXISTS request_transitions (
      id TEXT PRIMARY KEY,
      request_id TEXT NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
      at TEXT NOT NULL,
      from_state TEXT NOT NULL,
      to_state TEXT NOT NULL,
      actor TEXT NOT NULL,
      reason TEXT
    )`);
    this.db.exec("CREATE INDEX IF NOT EXISTS request_transitions_request_at ON request_transitions(request_id, at, id)");
    this.db.exec(`CREATE TABLE IF NOT EXISTS request_events (
      id TEXT PRIMARY KEY,
      request_id TEXT NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
      kind TEXT NOT NULL,
      occurred_at TEXT NOT NULL,
      recorded_at TEXT NOT NULL,
      summary TEXT NOT NULL,
      actor_type TEXT NOT NULL CHECK (actor_type IN ('owner', 'agent', 'machinery', 'unknown')),
      actor_id TEXT,
      actor_display_name TEXT,
      session_id TEXT,
      host_id TEXT,
      account_id TEXT,
      source_id TEXT NOT NULL,
      source_event_id TEXT NOT NULL,
      producer_id TEXT NOT NULL,
      source_record_id TEXT,
      payload TEXT NOT NULL,
      UNIQUE(source_id, source_event_id)
    )`);
    const eventColumns = this.db.query<{ name: string }, []>("PRAGMA table_info(request_events)").all();
    if (!eventColumns.some((c) => c.name === "source_event_id")) {
      this.db.exec("ALTER TABLE request_events ADD COLUMN source_event_id TEXT");
      this.db.exec("UPDATE request_events SET source_event_id = COALESCE(source_record_id, id) WHERE source_event_id IS NULL");
    }
    if (!eventColumns.some((c) => c.name === "producer_id")) {
      this.db.exec("ALTER TABLE request_events ADD COLUMN producer_id TEXT");
      this.db.exec("UPDATE request_events SET producer_id = source_id WHERE producer_id IS NULL");
    }
    this.db.exec("CREATE UNIQUE INDEX IF NOT EXISTS request_events_source_event_unique ON request_events(source_id, source_event_id)");
    this.db.exec("CREATE INDEX IF NOT EXISTS request_events_request_at ON request_events(request_id, occurred_at, id)");
    this.db.exec(`CREATE TABLE IF NOT EXISTS request_evidence_links (
      request_id TEXT NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
      source_id TEXT NOT NULL,
      source_event_id TEXT NOT NULL,
      kind TEXT NOT NULL CHECK (kind IN ('factory_run', 'phase', 'gate', 'tool', 'change', 'log', 'branch', 'land_submission', 'commit', 'deployment', 'proof')),
      target_source TEXT NOT NULL,
      target_id TEXT NOT NULL,
      occurred_at TEXT NOT NULL,
      owner_url TEXT,
      metadata TEXT NOT NULL,
      PRIMARY KEY (source_id, source_event_id, kind, target_source, target_id),
      FOREIGN KEY (source_id, source_event_id) REFERENCES request_events(source_id, source_event_id) ON DELETE CASCADE
    )`);
    const evidenceLinksSchema = this.db.query<{ sql: string | null }, []>(
      "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'request_evidence_links'",
    ).get()?.sql ?? "";
    if (!evidenceLinksSchema.includes("'branch'")) {
      this.db.transaction(() => {
        this.db.exec("ALTER TABLE request_evidence_links RENAME TO request_evidence_links_legacy");
        this.db.exec(`CREATE TABLE request_evidence_links (
          request_id TEXT NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
          source_id TEXT NOT NULL,
          source_event_id TEXT NOT NULL,
          kind TEXT NOT NULL CHECK (kind IN ('factory_run', 'phase', 'gate', 'tool', 'change', 'log', 'branch', 'land_submission', 'commit', 'deployment', 'proof')),
          target_source TEXT NOT NULL,
          target_id TEXT NOT NULL,
          occurred_at TEXT NOT NULL,
          owner_url TEXT,
          metadata TEXT NOT NULL,
          PRIMARY KEY (source_id, source_event_id, kind, target_source, target_id),
          FOREIGN KEY (source_id, source_event_id) REFERENCES request_events(source_id, source_event_id) ON DELETE CASCADE
        )`);
        this.db.exec(`INSERT INTO request_evidence_links
          (request_id, source_id, source_event_id, kind, target_source, target_id, occurred_at, owner_url, metadata)
          SELECT request_id, source_id, source_event_id, kind, target_source, target_id, occurred_at, owner_url, metadata
          FROM request_evidence_links_legacy`);
        this.db.exec("DROP TABLE request_evidence_links_legacy");
      })();
    }
    this.db.exec("CREATE INDEX IF NOT EXISTS request_evidence_links_request_at ON request_evidence_links(request_id, occurred_at, source_id, source_event_id)");
    this.db.exec(`CREATE TABLE IF NOT EXISTS request_evidence_attachments (
      digest TEXT PRIMARY KEY,
      byte_count INTEGER NOT NULL CHECK (byte_count >= 0),
      media_type TEXT NOT NULL CHECK (media_type IN ('text/plain', 'text/x-diff', 'application/json')),
      redaction_status TEXT NOT NULL CHECK (redaction_status IN ('redacted', 'not_required')),
      truncated INTEGER NOT NULL CHECK (truncated IN (0, 1)),
      original_byte_count INTEGER CHECK (original_byte_count IS NULL OR original_byte_count >= byte_count),
      content BLOB NOT NULL,
      created_at TEXT NOT NULL
    )`);
    this.db.exec(`CREATE TABLE IF NOT EXISTS request_evidence_event_attachments (
      request_id TEXT NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
      source_id TEXT NOT NULL,
      source_event_id TEXT NOT NULL,
      digest TEXT NOT NULL REFERENCES request_evidence_attachments(digest),
      PRIMARY KEY (source_id, source_event_id, digest),
      FOREIGN KEY (source_id, source_event_id) REFERENCES request_events(source_id, source_event_id) ON DELETE CASCADE
    )`);
    this.db.exec("CREATE INDEX IF NOT EXISTS request_evidence_event_attachments_request ON request_evidence_event_attachments(request_id, digest)");
    this.db.exec(`CREATE TABLE IF NOT EXISTS request_evidence_source_health (
      request_id TEXT NOT NULL REFERENCES requests(id) ON DELETE CASCADE,
      source_id TEXT NOT NULL,
      producer_id TEXT NOT NULL,
      reported_at TEXT NOT NULL,
      queue_count INTEGER NOT NULL CHECK (queue_count >= 0),
      oldest_queued_at TEXT,
      quarantine_count INTEGER NOT NULL CHECK (quarantine_count >= 0),
      last_acknowledged_at TEXT,
      PRIMARY KEY (request_id, source_id, producer_id)
    )`);
    const columns = this.db.query<{ name: string }, []>("PRAGMA table_info(requests)").all();
    if (!columns.some((c) => c.name === "origin")) {
      this.db.exec("ALTER TABLE requests ADD COLUMN origin TEXT NOT NULL DEFAULT 'owner'");
    }
    if (!columns.some((c) => c.name === "announced_at")) {
      this.db.exec("ALTER TABLE requests ADD COLUMN announced_at INTEGER");
    }
    if (!columns.some((c) => c.name === "session_name")) this.db.exec("ALTER TABLE requests ADD COLUMN session_name TEXT");
    if (!columns.some((c) => c.name === "session_id")) this.db.exec("ALTER TABLE requests ADD COLUMN session_id TEXT");
    if (!columns.some((c) => c.name === "factory_run_id")) this.db.exec("ALTER TABLE requests ADD COLUMN factory_run_id TEXT");
    if (!columns.some((c) => c.name === "original_body")) this.db.exec("ALTER TABLE requests ADD COLUMN original_body TEXT");
    if (!columns.some((c) => c.name === "original_body_format")) this.db.exec("ALTER TABLE requests ADD COLUMN original_body_format TEXT");
    if (!columns.some((c) => c.name === "intake_source")) this.db.exec("ALTER TABLE requests ADD COLUMN intake_source TEXT");
    if (!columns.some((c) => c.name === "intake_source_event_id")) this.db.exec("ALTER TABLE requests ADD COLUMN intake_source_event_id TEXT");

    // SQLite cannot widen a CHECK constraint in place. Rebuild the parent table once,
    // copying every row and leaving child-table declarations pointed at `requests`.
    // The partial plan_ref index is recreated below after the old table is dropped.
    const requestsTable = this.db.query<{ sql: string }, []>("SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'requests'").get();
    if (requestsTable && (!requestsTable.sql.includes("'orphaned'") || !requestsTable.sql.includes("'canceled'"))) {
      this.db.transaction(() => {
        this.db.exec(`CREATE TABLE requests_s4 (
          id TEXT PRIMARY KEY,
          title TEXT NOT NULL,
          project TEXT NOT NULL,
          state TEXT NOT NULL CHECK (state IN ('asked', 'in_flight', 'blocked_needs_owner', 'orphaned', 'shipped', 'canceled')),
          priority TEXT NOT NULL,
          origin TEXT NOT NULL DEFAULT 'owner' CHECK (origin IN ('owner', 'agent-incident', 'agent-judgement')),
          asked_at TEXT NOT NULL,
          original_body TEXT,
          original_body_format TEXT,
          intake_source TEXT,
          intake_source_event_id TEXT,
          updated_at TEXT NOT NULL,
          worker TEXT,
          detail TEXT,
          proof_url TEXT,
          plan_ref TEXT,
          factory_run_id TEXT,
          session_name TEXT,
          session_id TEXT,
          announced_at INTEGER
        )`);
        this.db.exec(`INSERT INTO requests_s4
          (id, title, project, state, priority, origin, asked_at, original_body, original_body_format, intake_source, intake_source_event_id, updated_at, worker, detail, proof_url, plan_ref, factory_run_id, session_name, session_id, announced_at)
          SELECT id, title, project, state, priority, origin, asked_at, original_body, original_body_format, intake_source, intake_source_event_id, updated_at, worker, detail, proof_url, plan_ref, factory_run_id, session_name, session_id, announced_at
          FROM requests`);
        this.db.exec("DROP TABLE requests");
        this.db.exec("ALTER TABLE requests_s4 RENAME TO requests");
      })();
    }
    const duplicatePlanRefs = this.db.query<{ plan_ref: string; count: number }, []>(`
      SELECT plan_ref, COUNT(*) AS count FROM requests
      WHERE plan_ref IS NOT NULL
      GROUP BY plan_ref
      HAVING COUNT(*) > 1
      ORDER BY plan_ref
    `).all();
    if (duplicatePlanRefs.length > 0) {
      const details = duplicatePlanRefs.map(({ plan_ref, count }) => `${plan_ref} (${count})`).join(", ");
      throw new Error(`cannot enforce unique requests.plan_ref: duplicate non-null plan_ref values: ${details}`);
    }
    this.db.exec("CREATE UNIQUE INDEX IF NOT EXISTS requests_plan_ref_unique ON requests(plan_ref) WHERE plan_ref IS NOT NULL");
    this.insert = this.db.query(`INSERT INTO requests (id, title, project, state, priority, origin, asked_at, original_body, original_body_format, intake_source, intake_source_event_id, updated_at, worker, detail, proof_url, plan_ref, factory_run_id, session_name, session_id)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`);
    this.select = this.db.query<RequestRow, [string]>("SELECT * FROM requests WHERE id = ?");
    this.listRows = this.db.query<RequestRow, []>("SELECT * FROM requests ORDER BY updated_at DESC");
    this.markAnnouncedQuery = this.db.query("UPDATE requests SET announced_at = ? WHERE id = ?");
    // Order by `at` then `rowid` (SQLite's implicit, strictly-insertion-order rowid), never
    // `id`: two transitions/receipts recorded within the same millisecond share an identical
    // `at`, and `id` is a random `transition-<uuid>`/`receipt-<uuid>` whose lexical order has no
    // relationship to insertion order — sorting by it flips same-millisecond rows unpredictably.
    this.listReceipts = this.db.query<{ id: string; request_id: string; at: string; kind: RequestReceipt["kind"]; line: string; meta: string }, [string]>("SELECT id, request_id, at, kind, line, meta FROM receipt_trail WHERE request_id = ? ORDER BY at, rowid");
    this.listTransitions = this.db.query<RequestTransition, [string]>("SELECT id, request_id, at, from_state, to_state, actor, reason FROM request_transitions WHERE request_id = ? ORDER BY at, rowid");
    this.claimFireQuery = this.db.query<RequestRow, [string, string, string, RequestState, string, string | null, string, string, string | null, string | null]>(`
      INSERT INTO requests (id, title, project, state, priority, origin, asked_at, original_body, original_body_format, intake_source, intake_source_event_id, updated_at, worker, detail, proof_url, plan_ref)
      VALUES (?, ?, ?, ?, 'NORMAL', 'agent-incident', ?, ?, 'plain_text', 'request-fire', ?, ?, ?, ?, NULL, NULL)
      ON CONFLICT(id) DO UPDATE SET
        state = excluded.state, asked_at = excluded.asked_at, original_body = excluded.original_body,
        original_body_format = excluded.original_body_format, intake_source = excluded.intake_source,
        intake_source_event_id = excluded.intake_source_event_id, updated_at = excluded.updated_at,
        worker = excluded.worker, detail = excluded.detail
      WHERE requests.state IN ('shipped', 'canceled')
      RETURNING *
    `);
    this.upsertGithubCheck = this.db.query(`INSERT INTO github_checks
      (repo, run_id, name, status, conclusion, sha, branch, started_at, completed_at, work_key, observed_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(repo, run_id) DO UPDATE SET
        name = excluded.name, status = excluded.status, conclusion = excluded.conclusion,
        sha = excluded.sha, branch = excluded.branch, started_at = excluded.started_at,
        completed_at = excluded.completed_at, work_key = excluded.work_key,
        observed_at = excluded.observed_at`);
    this.listGithubChecksQuery = this.db.query<GithubCheckRow, []>("SELECT * FROM github_checks ORDER BY observed_at DESC, run_id DESC");
  }

  private appendEvent(input: {
    requestId: string;
    kind: RequestEventKind;
    occurredAt: string;
    summary: string;
    actor: RequestStoryActor;
    sourceId: string;
    sourceEventId: string;
    producerId?: string;
    sourceRecordId?: string | null;
    sessionId?: string | null;
    hostId?: string | null;
    accountId?: string | null;
    payload?: Record<string, unknown>;
  }): void {
    this.db.query(`INSERT INTO request_events
      (id, request_id, kind, occurred_at, recorded_at, summary, actor_type, actor_id, actor_display_name,
       session_id, host_id, account_id, source_id, source_event_id, producer_id, source_record_id, payload)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`)
      .run(`event-${crypto.randomUUID()}`, input.requestId, input.kind, input.occurredAt, new Date().toISOString(), input.summary,
        input.actor.type, input.actor.id ?? null, input.actor.displayName ?? null, input.sessionId ?? null,
        input.hostId ?? null, input.accountId ?? null, input.sourceId, input.sourceEventId, input.producerId ?? input.sourceId,
        input.sourceRecordId ?? null, JSON.stringify(input.payload ?? {}));
  }

  private appendEvidenceDetails(envelope: RequestEvidenceEnvelopeV1): void {
    for (const link of envelope.links ?? []) {
      this.db.query(`INSERT INTO request_evidence_links
        (request_id, source_id, source_event_id, kind, target_source, target_id, occurred_at, owner_url, metadata)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)`).run(
        envelope.requestId,
        envelope.sourceId,
        envelope.sourceEventId,
        link.kind,
        link.targetSource,
        link.targetId,
        envelope.occurredAt,
        link.ownerUrl ?? null,
        JSON.stringify(link.metadata ?? {}),
      );
    }
    for (const attachment of envelope.attachments ?? []) {
      const content = this.validateAttachment(attachment);
      const existing = this.db.query<RequestEvidenceAttachmentRow, [string]>(
        "SELECT * FROM request_evidence_attachments WHERE digest = ?",
      ).get(attachment.digest);
      if (existing) {
        if (existing.byte_count !== attachment.byteCount
          || existing.media_type !== attachment.mediaType
          || existing.redaction_status !== attachment.redactionStatus
          || Boolean(existing.truncated) !== attachment.truncated
          || existing.original_byte_count !== (attachment.originalByteCount ?? null)
          || !Buffer.from(existing.content ?? []).equals(content)) {
          throw new RequestEvidenceConflictError(envelope.requestId);
        }
      } else {
        this.db.query(`INSERT INTO request_evidence_attachments
          (digest, byte_count, media_type, redaction_status, truncated, original_byte_count, content, created_at)
          VALUES (?, ?, ?, ?, ?, ?, ?, ?)`).run(
          attachment.digest,
          attachment.byteCount,
          attachment.mediaType,
          attachment.redactionStatus,
          attachment.truncated ? 1 : 0,
          attachment.originalByteCount ?? null,
          content,
          new Date().toISOString(),
        );
      }
      this.db.query(`INSERT INTO request_evidence_event_attachments
        (request_id, source_id, source_event_id, digest) VALUES (?, ?, ?, ?)`).run(
        envelope.requestId,
        envelope.sourceId,
        envelope.sourceEventId,
        attachment.digest,
      );
    }
  }

  private validateAttachment(attachment: RequestEvidenceAttachmentV1): Buffer {
    const content = Buffer.from(attachment.contentBase64, "base64");
    if (content.byteLength !== attachment.byteCount || content.byteLength > MAX_EVIDENCE_ATTACHMENT_BYTES) {
      throw new Error("invalid request evidence attachment size");
    }
    const digest = `sha256:${createHash("sha256").update(content).digest("hex")}`;
    if (digest !== attachment.digest) throw new Error("invalid request evidence attachment digest");
    return content;
  }

  private listEvidenceLinks(requestId: string): RequestStoryEvidenceLink[] {
    return this.db.query<RequestEvidenceLinkRow, [string]>(`SELECT kind, target_source, target_id,
      occurred_at, source_id, source_event_id, owner_url, metadata FROM request_evidence_links
      WHERE request_id = ? ORDER BY occurred_at, rowid`).all(requestId).map((link) => ({
      kind: link.kind,
      targetSource: link.target_source,
      targetId: link.target_id,
      occurredAt: link.occurred_at,
      sourceId: link.source_id,
      sourceEventId: link.source_event_id,
      ...(link.owner_url ? { ownerUrl: link.owner_url } : {}),
      metadata: parseEventPayload(link.metadata),
    }));
  }

  private listEvidenceAttachments(requestId: string): RequestStoryAttachment[] {
    return this.db.query<RequestEvidenceAttachmentRow, [string]>(`SELECT DISTINCT a.digest, a.byte_count,
      a.media_type, a.redaction_status, a.truncated, a.original_byte_count, NULL AS content
      FROM request_evidence_attachments a
      JOIN request_evidence_event_attachments ea ON ea.digest = a.digest
      WHERE ea.request_id = ? ORDER BY a.digest`).all(requestId).map((attachment) => ({
      digest: attachment.digest,
      byteCount: attachment.byte_count,
      mediaType: attachment.media_type,
      redactionStatus: attachment.redaction_status,
      truncated: Boolean(attachment.truncated),
      ...(attachment.original_byte_count === null ? {} : { originalByteCount: attachment.original_byte_count }),
      status: "available",
      url: `/requests/${encodeURIComponent(requestId)}/attachments/${encodeURIComponent(attachment.digest)}`,
    }));
  }

  getEvidenceAttachment(requestId: string, digest: string): { attachment: RequestStoryAttachment; content: Uint8Array } | null {
    const row = this.db.query<RequestEvidenceAttachmentRow, [string, string]>(`SELECT a.*
      FROM request_evidence_attachments a
      JOIN request_evidence_event_attachments ea ON ea.digest = a.digest
      WHERE ea.request_id = ? AND a.digest = ? LIMIT 1`).get(requestId, digest);
    if (!row?.content) return null;
    return {
      attachment: {
        digest: row.digest,
        byteCount: row.byte_count,
        mediaType: row.media_type,
        redactionStatus: row.redaction_status,
        truncated: Boolean(row.truncated),
        ...(row.original_byte_count === null ? {} : { originalByteCount: row.original_byte_count }),
        status: "available",
      },
      content: row.content,
    };
  }

  private listTypedEvents(requestId: string): RequestStoryEvent[] {
    return this.db.query<RequestEventRow, [string]>(`SELECT * FROM request_events
      WHERE request_id = ? ORDER BY occurred_at, rowid`).all(requestId).map((event) => ({
        id: event.id,
        requestId: event.request_id,
        kind: event.kind,
        occurredAt: event.occurred_at,
        recordedAt: event.recorded_at,
        summary: event.summary,
        actor: {
          type: event.actor_type,
          ...(event.actor_id ? { id: event.actor_id } : {}),
          ...(event.actor_display_name ? { displayName: event.actor_display_name } : {}),
        },
        ...(event.session_id ? { sessionId: event.session_id } : {}),
        ...(event.host_id ? { hostId: event.host_id } : {}),
        ...(event.account_id ? { accountId: event.account_id } : {}),
        sourceId: event.source_id,
        sourceEventId: event.source_event_id,
        producerId: event.producer_id,
        ...(event.source_record_id ? { sourceRecordId: event.source_record_id } : {}),
        payload: parseEventPayload(event.payload),
        legacy: false,
      }));
  }

  appendEvidence(envelope: RequestEvidenceEnvelopeV1): RequestEvidenceAcknowledgementV1 {
    const acknowledgement = (replayed: boolean): RequestEvidenceAcknowledgementV1 => ({
      requestId: envelope.requestId,
      sourceId: envelope.sourceId,
      sourceEventId: envelope.sourceEventId,
      replayed,
    });
    try {
      return this.db.transaction(() => {
        const seen = this.db.query<{ request_id: string }, [string, string]>(
          "SELECT request_id FROM request_events WHERE source_id = ? AND source_event_id = ?",
        ).get(envelope.sourceId, envelope.sourceEventId);
        if (seen) {
          if (seen.request_id !== envelope.requestId) throw new RequestEvidenceConflictError(envelope.requestId);
          return acknowledgement(true);
        }

        const existing = this.get(envelope.requestId);
        if (!existing) {
          if (envelope.event.kind !== "intake" || !envelope.createRequest) {
            throw new RequestNotFoundError(envelope.requestId);
          }
          const create = envelope.createRequest;
          this.create({
            id: envelope.requestId,
            title: create.title,
            project: create.project,
            state: "asked",
            priority: create.priority,
            origin: create.origin,
            asked_at: envelope.occurredAt,
            updated_at: envelope.occurredAt,
            original_body: create.originalBody,
            original_body_format: create.originalBodyFormat,
            intake_source: envelope.sourceId,
            intake_source_event_id: envelope.sourceEventId,
            plan_ref: create.workKey ?? null,
            detail: create.detail ?? null,
            session_name: create.sessionName ?? null,
            session_id: create.sessionId ?? envelope.event.sessionId ?? null,
          });
          this.db.query(`UPDATE request_events SET
            kind = ?, occurred_at = ?, summary = ?, actor_type = ?, actor_id = ?, actor_display_name = ?,
            session_id = ?, host_id = ?, account_id = ?, producer_id = ?, payload = ?
            WHERE request_id = ? AND source_id = ? AND source_event_id = ?`).run(
            envelope.event.kind,
            envelope.occurredAt,
            envelope.event.summary,
            envelope.event.actor.type,
            envelope.event.actor.id ?? null,
            envelope.event.actor.displayName ?? null,
            envelope.event.sessionId ?? null,
            envelope.event.hostId ?? null,
            envelope.event.accountId ?? null,
            envelope.producerId,
            JSON.stringify(envelope.event.payload),
            envelope.requestId,
            envelope.sourceId,
            envelope.sourceEventId,
          );
          this.appendEvidenceDetails(envelope);
          return acknowledgement(false);
        }

        // An intake is an object-creation assertion. A different intake event may never
        // attach itself to an existing request id, even when its prose happens to match.
        if (envelope.event.kind === "intake" || envelope.createRequest) {
          throw new RequestEvidenceConflictError(envelope.requestId);
        }
        if (envelope.projection) {
          const projection = envelope.projection;
          if (!STATES.includes(projection.state as RequestState)) throw new Error(`invalid request state: ${projection.state}`);
          if (existing.state !== projection.expectedState
            || !ALLOWED_TRANSITIONS[existing.state].includes(projection.state as RequestState)) {
            throw new InvalidRequestTransitionError(existing.state, projection.state as RequestState);
          }
          const changed = this.db.query(`UPDATE requests SET state = ?, updated_at = ?, worker = ?, detail = ?,
            session_name = ?, session_id = ? WHERE id = ? AND state = ?`).run(
            projection.state,
            envelope.occurredAt,
            projection.worker === undefined ? existing.worker : projection.worker,
            projection.detail === undefined ? existing.detail : projection.detail,
            projection.sessionName === undefined ? existing.session_name : projection.sessionName,
            projection.sessionId === undefined ? existing.session_id : projection.sessionId,
            existing.id,
            projection.expectedState,
          );
          if (changed.changes !== 1) throw new InvalidRequestTransitionError(existing.state, projection.state as RequestState);
        }
        this.appendEvent({
          requestId: envelope.requestId,
          kind: envelope.event.kind,
          occurredAt: envelope.occurredAt,
          summary: envelope.event.summary,
          actor: envelope.event.actor,
          sourceId: envelope.sourceId,
          sourceEventId: envelope.sourceEventId,
          producerId: envelope.producerId,
          sessionId: envelope.event.sessionId ?? null,
          hostId: envelope.event.hostId ?? null,
          accountId: envelope.event.accountId ?? null,
          payload: envelope.event.payload,
        });
        this.appendEvidenceDetails(envelope);
        return acknowledgement(false);
      })();
    } catch (error) {
      if (error instanceof Error && /UNIQUE constraint failed: request_events\.source_id, request_events\.source_event_id/.test(error.message)) {
        const seen = this.db.query<{ request_id: string }, [string, string]>(
          "SELECT request_id FROM request_events WHERE source_id = ? AND source_event_id = ?",
        ).get(envelope.sourceId, envelope.sourceEventId);
        if (seen?.request_id === envelope.requestId) return acknowledgement(true);
        throw new RequestEvidenceConflictError(envelope.requestId);
      }
      throw error;
    }
  }

  recordEvidenceSourceHealth(health: RequestEvidenceSourceHealthV1): void {
    if (!this.get(health.requestId)) throw new RequestNotFoundError(health.requestId);
    this.db.query(`INSERT INTO request_evidence_source_health
      (request_id, source_id, producer_id, reported_at, queue_count, oldest_queued_at, quarantine_count, last_acknowledged_at)
      VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      ON CONFLICT(request_id, source_id, producer_id) DO UPDATE SET
        reported_at = excluded.reported_at, queue_count = excluded.queue_count,
        oldest_queued_at = excluded.oldest_queued_at, quarantine_count = excluded.quarantine_count,
        last_acknowledged_at = excluded.last_acknowledged_at`).run(
      health.requestId,
      health.sourceId,
      health.producerId,
      new Date().toISOString(),
      health.queueCount,
      health.oldestQueuedAt,
      health.quarantineCount,
      health.lastAcknowledgedAt,
    );
  }

  private deliveryCoverage(requestId: string): RequestStoryV1["coverage"] {
    const rows = this.db.query<RequestEvidenceSourceHealthRow, [string]>(
      "SELECT * FROM request_evidence_source_health WHERE request_id = ? ORDER BY source_id, producer_id",
    ).all(requestId);
    const grouped = new Map<string, RequestEvidenceSourceHealthRow[]>();
    for (const row of rows) grouped.set(row.source_id, [...(grouped.get(row.source_id) ?? []), row]);
    return [...grouped.entries()].map(([sourceId, sourceRows]) => {
      const queueCount = sourceRows.reduce((sum, row) => sum + row.queue_count, 0);
      const quarantineCount = sourceRows.reduce((sum, row) => sum + row.quarantine_count, 0);
      const oldestReport = sourceRows.reduce((oldest, row) => !oldest || row.reported_at < oldest ? row.reported_at : oldest, "");
      if (quarantineCount > 0) return {
        fact: "activity_delivery" as const,
        status: "partial" as const,
        sourceId,
        reason: `${quarantineCount} recorded update${quarantineCount === 1 ? " is" : "s are"} quarantined after validation failed.`,
      };
      if (queueCount > 0) return {
        fact: "activity_delivery" as const,
        status: "pending_delivery" as const,
        sourceId,
        reason: `${queueCount} recorded update${queueCount === 1 ? " is" : "s are"} waiting for delivery.`,
      };
      if (!oldestReport || Date.now() - Date.parse(oldestReport) > 5 * 60_000) return {
        fact: "activity_delivery" as const,
        status: "stale" as const,
        sourceId,
        reason: "This evidence source has not reported recently.",
      };
      return { fact: "activity_delivery" as const, status: "complete" as const, sourceId };
    });
  }

  getStory(id: string): RequestStoryV1 | null {
    const row = this.get(id);
    if (!row) return null;
    const typed = this.listTypedEvents(id);
    const represented = new Set(typed.map((event) => event.sourceRecordId).filter((value): value is string => Boolean(value)));
    const legacyTransitions: RequestStoryEvent[] = (row.transition_trail ?? []).filter((event) => !represented.has(event.id)).map((event) => ({
      id: `legacy-${event.id}`,
      requestId: id,
      kind: "legacy_transition",
      occurredAt: event.at,
      recordedAt: event.at,
      summary: event.reason || `State changed from ${event.from_state} to ${event.to_state}.`,
      actor: { type: "unknown", displayName: event.actor },
      sourceId: "legacy-request-transitions",
      sourceEventId: event.id,
      producerId: "legacy-request-transitions",
      sourceRecordId: event.id,
      payload: { fromState: event.from_state, toState: event.to_state },
      legacy: true,
    }));
    const legacyReceipts: RequestStoryEvent[] = (row.receipt_trail ?? []).filter((event) => !represented.has(event.id)).map((event) => ({
      id: `legacy-${event.id}`,
      requestId: id,
      kind: "legacy_receipt",
      occurredAt: event.at,
      recordedAt: event.at,
      summary: event.line,
      actor: event.meta.worker ? { type: "unknown", displayName: event.meta.worker } : { type: "unknown" },
      ...(event.meta.session_id ? { sessionId: event.meta.session_id } : {}),
      ...(event.meta.host ? { hostId: event.meta.host } : {}),
      sourceId: "legacy-receipt-trail",
      sourceEventId: event.id,
      producerId: "legacy-receipt-trail",
      sourceRecordId: event.id,
      payload: { receiptKind: event.kind },
      legacy: true,
    }));
    const events = [...typed, ...legacyTransitions, ...legacyReceipts]
      .sort((a, b) => a.occurredAt.localeCompare(b.occurredAt));
    const hasLegacy = events.some((event) => event.legacy);
    const hasUnreadablePayload = typed.some((event) => event.payload.recordUnreadable === true);
    const links = this.listEvidenceLinks(id);
    const attachments = this.listEvidenceAttachments(id);
    const workLinkKinds = new Set(["factory_run", "phase", "gate", "tool", "change", "log"]);
    const deliveryLinkKinds = new Set(["branch", "land_submission", "commit", "deployment", "proof"]);
    const hasWorkEvidence = links.some((link) => workLinkKinds.has(link.kind)) || attachments.length > 0;
    const deliveryLinks = links.filter((link) => deliveryLinkKinds.has(link.kind));
    const deliveryComplete = selectRequestDeliveryLineage(deliveryLinks).complete;
    return {
      schemaVersion: 1,
      requestId: row.id,
      title: row.title,
      project: row.project,
      state: row.state,
      priority: row.priority,
      askedAt: row.asked_at,
      updatedAt: row.updated_at,
      originalRequest: { body: row.original_body, format: row.original_body_format, source: row.intake_source },
      events,
      links,
      attachments,
      coverage: [
        row.original_body
          ? { fact: "original_request", status: "complete", sourceId: row.intake_source ?? "request-registry" }
          : { fact: "original_request", status: "unavailable", sourceId: row.intake_source ?? "legacy-request-registry", reason: "The complete original ask was not recorded for this request." },
        hasLegacy || hasUnreadablePayload
          ? { fact: "activity", status: "partial", sourceId: hasLegacy ? "legacy-request-registry" : "request-events", reason: hasUnreadablePayload ? "At least one recorded activity detail could not be read." : "Some activity predates typed evidence recording." }
          : typed.length === 0
            ? { fact: "activity", status: "unavailable", sourceId: "legacy-request-registry", reason: "No typed activity was recorded for this request." }
            : { fact: "activity", status: "complete", sourceId: "request-events" },
        hasWorkEvidence
          ? { fact: "work_evidence", status: "complete", sourceId: "request-evidence-links" }
          : { fact: "work_evidence", status: "unavailable", sourceId: "request-evidence-links", reason: "No exact work run, gate, tool, or change link was recorded for this request." },
        deliveryComplete
          ? { fact: "delivery_evidence", status: "complete", sourceId: "request-evidence-links" }
          : deliveryLinks.length > 0
            ? { fact: "delivery_evidence", status: "partial", sourceId: "request-evidence-links", reason: "Some delivery stages are recorded; unrecorded stages remain unknown." }
            : { fact: "delivery_evidence", status: "unavailable", sourceId: "request-evidence-links", reason: "No exact branch, landing, commit, deployment, or installed-proof link was recorded for this request." },
        ...this.deliveryCoverage(id),
      ],
    };
  }

  create(input: CreateRequestInput): RequestRow {
    if (!STATES.includes(input.state)) throw new Error(`invalid request state: ${input.state}`);
    if (input.state === "orphaned") throw new Error("orphaned requests require canonical session evidence");
    if (input.state === "canceled") throw new Error("canceled requests require a reasoned transition");
    requireBlockedOwnerDecision(input.state, input.title, input.detail);
    const origin = input.origin ?? "owner";
    if (!ORIGINS.includes(origin)) throw new Error(`invalid request origin: ${origin}`);
    const bodyCandidate = input.original_body ?? null;
    const originalBody = bodyCandidate && bodyCandidate.trim() ? bodyCandidate : null;
    const originalBodyFormat = originalBody ? (input.original_body_format ?? "plain_text") : null;
    const intakeSource = input.intake_source?.trim() || origin;
    const intakeSourceEventId = input.intake_source_event_id?.trim() || input.id;
    try {
      this.db.transaction(() => {
        this.insert.run(input.id, input.title, input.project, input.state, input.priority, origin, input.asked_at,
          originalBody, originalBodyFormat, intakeSource, intakeSourceEventId, input.updated_at,
          input.worker ?? null, input.detail ?? null, input.proof_url ?? null, input.plan_ref ?? null,
          input.factory_run_id ?? null, input.session_name ?? null, input.session_id ?? null);
        this.appendEvent({
          requestId: input.id,
          kind: "intake",
          occurredAt: input.asked_at,
          summary: "Request recorded.",
          actor: origin === "owner"
            ? { type: "owner", displayName: "Owner" }
            : origin === "agent-judgement"
              ? { type: "agent", displayName: "Agent" }
              : { type: "machinery", displayName: "Overdeck" },
          sourceId: intakeSource,
          sourceEventId: intakeSourceEventId,
          sourceRecordId: intakeSourceEventId,
          sessionId: input.session_id ?? null,
          payload: { originalBodyRecorded: originalBody !== null },
        });
        this.recordClaim(input.id, input.updated_at, input.worker ?? null, input.worker_host ?? null, input.session_id ?? null);
      })();
    } catch (error) {
      if (error instanceof Error && /UNIQUE constraint failed: requests.id/.test(error.message)) throw new Error(`duplicate request id: ${input.id}`);
      if (input.plan_ref && error instanceof Error && /UNIQUE constraint failed: requests.plan_ref/.test(error.message)) {
        const existing = this.getByPlanRef(input.plan_ref);
        if (existing) throw new DuplicateRequestPlanRefError(existing);
      }
      throw error;
    }
    return this.get(input.id)!;
  }

  update(id: string, patch: UpdateRequestPatch): RequestRow {
    if (patch.state !== undefined && !STATES.includes(patch.state)) throw new Error(`invalid request state: ${patch.state}`);
    if (patch.state === "orphaned") throw new Error("orphaned requests require canonical session evidence");
    if (patch.state === "canceled") throw new Error("canceled requests require a reasoned transition");
    const existing = this.get(id);
    if (!existing) throw new RequestNotFoundError(id);
    // `update` does not write a lifecycle fact. A pending decision may only be
    // edited after a real transition has already placed its row on the board.
    const nextDetail = Object.prototype.hasOwnProperty.call(patch, "detail") ? patch.detail : existing.detail;
    if (hasPendingOwnerDecision(existing.title, nextDetail)
      && (existing.state !== "blocked_needs_owner" || (patch.state !== undefined && patch.state !== "blocked_needs_owner"))) {
      throw new Error("pending owner decision must be blocked_needs_owner");
    }
    const now = new Date().toISOString();
    const next = { ...existing, ...patch, id, updated_at: now };
    this.db.transaction(() => {
      this.db.query(`UPDATE requests SET state = ?, priority = ?, updated_at = ?, worker = ?, detail = ?, proof_url = ?, factory_run_id = ?, session_name = ?, session_id = ? WHERE id = ?`)
        .run(next.state, next.priority, next.updated_at, next.worker, next.detail, next.proof_url, next.factory_run_id ?? null, next.session_name, next.session_id, id);
      if (patch.worker !== undefined && patch.worker !== existing.worker) this.recordClaim(id, next.updated_at, patch.worker, patch.worker_host ?? null, next.session_id);
    })();
    return this.get(id)!;
  }

  resolve(idOrWorkKey: string): RequestRow | null {
    const byId = this.get(idOrWorkKey);
    if (byId) return byId;
    return this.getByPlanRef(idOrWorkKey);
  }

  private getByPlanRef(planRef: string): RequestRow | null {
    const row = this.db.query<RequestRow, [string]>("SELECT * FROM requests WHERE plan_ref = ? LIMIT 1").get(planRef);
    return row ? this.withReceipts(row) : null;
  }

  transition(idOrWorkKey: string, input: { state: RequestState; actor: string; reason?: string | null; worker?: string | null; session_name?: string | null; session_id?: string | null }): RequestRow {
    const existing = this.resolve(idOrWorkKey);
    if (!existing) throw new RequestNotFoundError(idOrWorkKey);
    if (!ALLOWED_TRANSITIONS[existing.state].includes(input.state)) throw new InvalidRequestTransitionError(existing.state, input.state);
    const actor = input.actor.trim();
    if (!actor) throw new Error("transition actor is required");
    const reason = input.reason?.trim() || null;
    if (REASON_REQUIRED_STATES.includes(input.state) && !reason) {
      const label = input.state === "blocked_needs_owner" ? "blocked" : input.state;
      throw new Error(`${label} reason is required`);
    }
    const now = new Date().toISOString();
    this.db.transaction(() => {
      const changed = this.db.query("UPDATE requests SET state = ?, updated_at = ?, worker = ?, detail = ?, session_name = ?, session_id = ? WHERE id = ? AND state = ?")
        .run(input.state, now, input.worker === undefined ? existing.worker : input.worker, REASON_REQUIRED_STATES.includes(input.state) ? reason : null, input.session_name === undefined ? existing.session_name : input.session_name, input.session_id === undefined ? existing.session_id : input.session_id, existing.id, existing.state);
      if (changed.changes !== 1) {
        const current = this.db.query<{ state: RequestState }, [string]>("SELECT state FROM requests WHERE id = ?").get(existing.id)?.state ?? existing.state;
        throw new InvalidRequestTransitionError(current, input.state);
      }
      const transitionId = `transition-${crypto.randomUUID()}`;
      this.db.query("INSERT INTO request_transitions (id, request_id, at, from_state, to_state, actor, reason) VALUES (?, ?, ?, ?, ?, ?, ?)")
        .run(transitionId, existing.id, now, existing.state, input.state, actor, reason);
      this.appendEvent({
        requestId: existing.id,
        kind: "state_transition",
        occurredAt: now,
        summary: reason || `State changed from ${existing.state} to ${input.state}.`,
        actor: actor === "owner" ? { type: "owner", displayName: actor } : { type: "unknown", displayName: actor },
        sourceId: "request-registry",
        sourceEventId: transitionId,
        sourceRecordId: transitionId,
        sessionId: input.session_id ?? existing.session_id,
        payload: { fromState: existing.state, toState: input.state },
      });
    })();
    return this.get(existing.id)!;
  }

  /** Records the owner's words and resumes the request as one lifecycle fact. */
  answer(idOrWorkKey: string, answer: string, nextState: RequestState = "in_flight"): RequestRow {
    const existing = this.resolve(idOrWorkKey);
    if (!existing) throw new RequestNotFoundError(idOrWorkKey);
    const text = answer.trim();
    if (!text) throw new Error("answer is required");
    if (!ALLOWED_TRANSITIONS[existing.state].includes(nextState)) throw new InvalidRequestTransitionError(existing.state, nextState);
    const now = new Date().toISOString();
    this.db.transaction(() => {
      const changed = this.db.query("UPDATE requests SET state = ?, updated_at = ?, detail = ? WHERE id = ? AND state = ?")
        .run(nextState, now, REASON_REQUIRED_STATES.includes(nextState) ? text : null, existing.id, existing.state);
      if (changed.changes !== 1) {
        const current = this.db.query<{ state: RequestState }, [string]>("SELECT state FROM requests WHERE id = ?").get(existing.id)?.state ?? existing.state;
        throw new InvalidRequestTransitionError(current, nextState);
      }
      const transitionId = `transition-${crypto.randomUUID()}`;
      this.db.query("INSERT INTO request_transitions (id, request_id, at, from_state, to_state, actor, reason) VALUES (?, ?, ?, ?, ?, 'owner', ?)")
        .run(transitionId, existing.id, now, existing.state, nextState, text);
      this.appendEvent({
        requestId: existing.id,
        kind: "state_transition",
        occurredAt: now,
        summary: `Request resumed after the owner's answer.`,
        actor: { type: "owner", displayName: "Owner" },
        sourceId: "request-registry",
        sourceEventId: transitionId,
        sourceRecordId: transitionId,
        payload: { fromState: existing.state, toState: nextState },
      });
      const receiptId = `receipt-${crypto.randomUUID()}`;
      this.db.query("INSERT INTO receipt_trail (id, request_id, at, kind, line, meta) VALUES (?, ?, ?, 'progress', ?, ?)")
        .run(receiptId, existing.id, now, "Owner answered the request.", JSON.stringify({ worker: "owner" }));
      this.appendEvent({
        requestId: existing.id,
        kind: "owner_answer",
        occurredAt: now,
        summary: `Owner answered: ${text}`,
        actor: { type: "owner", displayName: "Owner" },
        sourceId: "request-registry",
        sourceEventId: receiptId,
        sourceRecordId: receiptId,
        payload: { answer: text },
      });
    })();
    return this.get(existing.id)!;
  }

  /**
   * S4 ledger writer: orphan only requests already associated to this exact session.
   * No row is created, and only actively in-flight work can move to recovery.
   */
  orphanSession(sessionId: string, dirtyCount: number, actor = "session ledger"): RequestRow[] {
    const exactSessionId = sessionId.trim();
    if (!exactSessionId || !Number.isInteger(dirtyCount) || dirtyCount <= 0) return [];
    const exactActor = actor.trim();
    if (!exactActor) throw new Error("transition actor is required");
    const reason = `Worker session stopped with ${dirtyCount} uncommitted file${dirtyCount === 1 ? "" : "s"}.`;
    const candidates = this.db.query<{ id: string }, [string]>("SELECT id FROM requests WHERE session_id = ? AND state = 'in_flight' ORDER BY id").all(exactSessionId);
    const changedIds: string[] = [];
    this.db.transaction(() => {
      for (const candidate of candidates) {
        const now = new Date().toISOString();
        const changed = this.db.query("UPDATE requests SET state = 'orphaned', updated_at = ?, detail = ? WHERE id = ? AND session_id = ? AND state = 'in_flight'")
          .run(now, reason, candidate.id, exactSessionId);
        if (changed.changes !== 1) continue;
        const transitionId = `transition-${crypto.randomUUID()}`;
        this.db.query("INSERT INTO request_transitions (id, request_id, at, from_state, to_state, actor, reason) VALUES (?, ?, ?, 'in_flight', 'orphaned', ?, ?)")
          .run(transitionId, candidate.id, now, exactActor, reason);
        this.appendEvent({
          requestId: candidate.id,
          kind: "state_transition",
          occurredAt: now,
          summary: reason,
          actor: { type: "machinery", displayName: exactActor },
          sourceId: "session-ledger",
          sourceEventId: transitionId,
          sourceRecordId: transitionId,
          sessionId: exactSessionId,
          payload: { fromState: "in_flight", toState: "orphaned", rescuedPaths: dirtyCount },
        });
        const receiptId = `receipt-${crypto.randomUUID()}`;
        this.db.query("INSERT INTO receipt_trail (id, request_id, at, kind, line, meta) VALUES (?, ?, ?, 'worker-lost', ?, ?)")
          .run(receiptId, candidate.id, now, reason, JSON.stringify({ worker: exactActor, session_id: exactSessionId }));
        this.appendEvent({
          requestId: candidate.id,
          kind: "recovery_required",
          occurredAt: now,
          summary: reason,
          actor: { type: "machinery", displayName: exactActor },
          sourceId: "session-ledger",
          sourceEventId: receiptId,
          sourceRecordId: receiptId,
          sessionId: exactSessionId,
          payload: { rescuedPaths: dirtyCount },
        });
        changedIds.push(candidate.id);
      }
    })();
    return changedIds.map((id) => this.get(id)!);
  }

  recordReceipt(idOrWorkKey: string, kind: RequestReceipt["kind"], line: string): RequestRow {
    const existing = this.resolve(idOrWorkKey);
    if (!existing) throw new RequestNotFoundError(idOrWorkKey);
    const now = new Date().toISOString();
    const receiptId = `receipt-${crypto.randomUUID()}`;
    this.db.transaction(() => {
      this.db.query("INSERT INTO receipt_trail (id, request_id, at, kind, line, meta) VALUES (?, ?, ?, ?, ?, ?)")
        .run(receiptId, existing.id, now, kind, line, JSON.stringify({}));
      this.appendEvent({
        requestId: existing.id,
        kind: "progress",
        occurredAt: now,
        summary: line,
        actor: { type: "unknown" },
        sourceId: "legacy-receipt-api",
        sourceEventId: receiptId,
        sourceRecordId: receiptId,
        payload: { receiptKind: kind },
      });
    })();
    return this.get(existing.id)!;
  }

  /**
   * Atomic S3 claim: `id` is derived from the incident SIGNATURE (see requests-fire.ts), so a
   * unique-key conflict IS the claim check — no read-then-write race. An open incident (any state
   * but `shipped`/`canceled`) with the same id blocks the caller (claimed: false). A terminal
   * incident with the same signature is reopened in the same statement (claimed: true) rather
   * than permanently blocking a recurrence of a fixed or dismissed failure.
   */
  claimFire(input: FireClaimInput): FireClaimResult {
    const state: RequestState = hasPendingOwnerDecision(input.title, input.detail) ? "blocked_needs_owner" : "asked";
    const sourceRecordId = `request-fire:${input.id}:${input.now}`;
    let claimedRow: RequestRow | null = null;
    this.db.transaction(() => {
      claimedRow = this.claimFireQuery.get(input.id, input.title, input.project, state, input.now,
        input.detail, sourceRecordId, input.now, input.worker, input.detail);
      if (!claimedRow) return;
      this.appendEvent({
        requestId: input.id,
        kind: "intake",
        occurredAt: input.now,
        summary: "Request recorded from an automated incident.",
        actor: { type: "machinery", displayName: "Overdeck" },
        sourceId: "request-fire",
        sourceEventId: sourceRecordId,
        sourceRecordId,
        payload: { originalBodyRecorded: Boolean(input.detail?.trim()) },
      });
      this.recordClaim(input.id, input.now, input.worker, input.worker_host ?? null, null);
    })();
    if (claimedRow) return { row: this.get(input.id)!, claimed: true };
    const existing = this.get(input.id);
    if (!existing) throw new Error(`fire claim conflict without a resolvable existing row: ${input.id}`);
    return { row: existing, claimed: false };
  }

  private recordClaim(requestId: string, at: string, worker: string | null, host: string | null, sessionId: string | null): void {
    if (!worker) return;
    const meta = { worker, ...(host ? { host } : {}), ...(sessionId ? { session_id: sessionId } : {}) };
    const receiptId = `receipt-${crypto.randomUUID()}`;
    this.db.query("INSERT INTO receipt_trail (id, request_id, at, kind, line, meta) VALUES (?, ?, ?, 'claimed', ?, ?)")
      .run(receiptId, requestId, at, `Work started on this request.`, JSON.stringify(meta));
    this.appendEvent({
      requestId,
      kind: "claimed",
      occurredAt: at,
      summary: "Work started on this request.",
      actor: { type: "agent", displayName: worker },
      sourceId: "request-registry",
      sourceEventId: receiptId,
      sourceRecordId: receiptId,
      sessionId,
      hostId: host,
      payload: {},
    });
  }

  private withReceipts(row: RequestRow): RequestRow {
    const receipt_trail = this.listReceipts.all(row.id).map((receipt) => ({ ...receipt, meta: JSON.parse(receipt.meta) as RequestReceipt["meta"] }));
    const transition_trail = this.listTransitions.all(row.id);
    return { ...row, receipt_trail, transition_trail };
  }

  list(): RequestRow[] { return this.listRows.all().map((row) => this.withReceipts(row)); }
  listGithubChecks(): GithubCheckRow[] { return this.listGithubChecksQuery.all(); }
  recordGithubCheck(check: Omit<GithubCheckRow, "work_key">): GithubCheckRow {
    const workKey = this.findWorkKey(check.sha, check.branch);
    this.upsertGithubCheck.run(check.repo, check.run_id, check.name, check.status,
      check.conclusion, check.sha, check.branch, check.started_at, check.completed_at,
      workKey, check.observed_at);
    return this.listGithubChecks().find((row) => row.repo === check.repo && row.run_id === check.run_id)!;
  }
  private findWorkKey(sha: string, branch: string | null): string | null {
    const needles = [sha, branch].filter((value): value is string => Boolean(value));
    for (const request of this.list()) {
      const haystack = [request.id, request.detail, request.plan_ref, request.proof_url,
        ...(request.receipt_trail ?? []).map((receipt) => receipt.line)].filter(Boolean).join("\n");
      if (needles.some((needle) => haystack.includes(needle))) return request.id;
    }
    return null;
  }
  get(id: string): RequestRow | null {
    const row = this.select.get(id);
    return row ? this.withReceipts(row) : null;
  }
  markAnnounced(id: string, at: number): void { this.markAnnouncedQuery.run(at, id); }
  close(): void { this.db.close(); }
}