Data shape

Everything observable lives in one SQLite file: /var/lib/fraud-data/corpus.db on node-eighteen. There is no Postgres, no Timescale, no parquet.

corpus.db tables

cases                doc_chunk_meta              documents_fts        sources
documents            doc_chunks                  documents_fts_*      tickers
documents_fts        doc_chunks_chunks           entities             triples
case_facts (new)     doc_chunks_info             mentions             urls
workflow_runs (new)  doc_chunks_metadatachunks00 meta                 schema_version
matters              doc_chunks_rowids           doc_page_summaries
defendant_dispositions (new)  doc_chunks_vector_chunks00

documents — the main table

CREATE TABLE documents (
  id INTEGER PRIMARY KEY,
  source_id INTEGER NOT NULL REFERENCES sources(id),
  url TEXT UNIQUE NOT NULL,
  kind TEXT NOT NULL,            -- press_release, litigation_release, complaint, judgment, pdf, ...
  title TEXT,
  published_at TEXT,
  case_number TEXT,
  caption TEXT,
  release_number TEXT,
  fetched_at TEXT NOT NULL DEFAULT (datetime('now')),
  http_status INTEGER,
  sha256 TEXT,
  content_type TEXT,
  byte_size INTEGER,
  body_path TEXT,
  pdf_path TEXT,
  parent_document_id INTEGER REFERENCES documents(id),
  body TEXT,
  -- OCR-side columns
  ocr_text TEXT,
  ocr_method TEXT,
  ocr_confidence REAL,
  -- Summarization-side columns
  summary_llm1_gemma TEXT,
  summary_llm2_qwen TEXT,
  summary_combined TEXT,
  summary_one_sentence TEXT,
  summary_paragraph TEXT,
  summary_llm3_qwen14b TEXT,
  llm_artifacts TEXT,
  processed_at TEXT
);

Columns added by enrichment workflows (via migrations, see below):

GAP: the case-facts.ts workflow comment says it writes columns via /apply/case-facts, not by ALTERing documents — facts land in the dedicated case_facts table below.

GAP: migrations 2026-05-18-title-format.sql and 2026-05-18-scheme.sql have an allowlist mismatch in the broker (_overnight-questions.md Q7) — the filename doesn't match the name= value, so POST /admin/migrate?name=title-format returns "file not found". Those columns landed via a different path (direct sqlite ALTER) on prod.

case_facts — sibling table for the case-facts workflow

-- broker/src/migrations/case_facts.sql
CREATE TABLE IF NOT EXISTS case_facts (
  document_id     INTEGER PRIMARY KEY REFERENCES documents(id),
  defendants_json TEXT,             -- JSON array of strings
  first_act_date  TEXT,             -- YYYY-MM-DD
  plea_date       TEXT,
  sentence_date   TEXT,
  monetary_amount INTEGER,          -- USD, integer cents (sic: comment says cents but workflow writes whole dollars — verify)
  monetary_currency TEXT DEFAULT 'USD',
  notes           TEXT,
  extracted_at    TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX idx_cf_first_act ON case_facts(first_act_date);
CREATE INDEX idx_cf_amount    ON case_facts(monetary_amount);

TODO: confirm monetary_amount units. SQL comment says "integer cents", case-facts.ts extracts "largest dollar amount (integer USD)".

workflow_runs — the new gating table (2026-05-19)

-- broker/src/migrations/2026-05-19-workflow-runs.sql
CREATE TABLE IF NOT EXISTS workflow_runs (
  workflow         TEXT    NOT NULL,
  document_id      INTEGER NOT NULL,
  status           TEXT    NOT NULL CHECK(status IN ('pending','running','done','failed','skipped')),
  attempts         INTEGER NOT NULL DEFAULT 0,
  last_error       TEXT,
  last_attempt_at  TEXT,
  result_summary   TEXT,
  PRIMARY KEY (workflow, document_id)
);
CREATE INDEX idx_workflow_runs_status ON workflow_runs(workflow, status, last_attempt_at);

defendant_dispositions — per-defendant outcomes (2026-05-27)

One row per (document_id, defendant_name). Populated by the disposition-extract, sentence-extract, and sanctions-extract workflows (see enrichment.md), each writing a disjoint set of columns via POST /apply/defendant-disposition (UPSERT + COALESCE). Created on broker startup by migrate-015.

-- broker/src/migrations/2026-05-27-defendant-dispositions.sql  (mirrored in migrate.ts migrate-015)
CREATE TABLE IF NOT EXISTS defendant_dispositions (
  document_id               INTEGER NOT NULL,        -- REFERENCES documents(id)
  defendant_name            TEXT    NOT NULL,        -- raw, as printed
  entity_id                 INTEGER,                 -- best-effort resolve via nameSignature(canon(name)) → entities.norm
  role                      TEXT,                    -- defendant | relief_defendant | nominal | unknown
  defendant_class           TEXT,                    -- individual | corporate | entity
  disposition_type          TEXT,                    -- guilty_plea|jury_verdict|bench_verdict|dpa|npa|
                                                     --   default_judgment|consent_judgment|dismissed|cooperation|unknown
  charge_date               TEXT,
  disposition_date          TEXT,
  charge_count              INTEGER,
  top_charge                TEXT,                    -- e.g. "securities fraud", "wire fraud"
  prison_months             INTEGER,
  probation_months          INTEGER,
  supervised_release_months INTEGER,
  home_confinement_months   INTEGER,
  cooperation               INTEGER,                 -- 0/1
  acceptance_of_responsibility INTEGER,              -- 0/1
  sanctions_json            TEXT,                    -- JSON array of sanction enums
  monetary_json             TEXT,                    -- {disgorgement,civil_penalty,restitution,forfeiture,criminal_fine}
  confidence                REAL,
  source_method             TEXT,                    -- regex | llm
  extracted_at              TEXT NOT NULL DEFAULT (datetime('now')),
  PRIMARY KEY (document_id, defendant_name)
);
CREATE INDEX idx_defdisp_entity ON defendant_dispositions(entity_id) WHERE entity_id IS NOT NULL;

NOTE: roll up by entity_id, not defendant_name — the same actor appears under casing variants ("Paul Sexton" vs "PAUL SEXTON") across docs, both resolving to one entity_id. Two derived stats-miner materialisers consume this table: actor-recidivism (entity in ≥2 matters) and parallel-proceedings (entity in both an SEC and a DOJ doc on one scheme). These are computed views, not corpus tables.

Other tables (read-only summary)

Workflow-runs state machine

                     fraud-publisher                      fraud-enricher-v2
                          publishes                       consumes
documents.queued_at = NULL ──────▶ queued_at = NOW       ──────▶ workflow_runs row
                                                                  status='running'
                                                                       │
                                                            ┌──────────┴─────────────┐
                                                            ▼                        ▼
                                                  process()→{patch}        process()→{skip}
                                                            │                        │
                                                            ▼                        ▼
                                                 broker /update OR             status='skipped'
                                                 /apply/<endpoint>             attempts++
                                                            │
                                                            ▼
                                                  status='done'
                                                  queued_at cleared by enricher

Terminal statuses (skipped from re-publish): done, failed, skipped. Active gating is by workflow_runs left-join NULL — see fraud-publisher/index.ts:fetchCandidateIds() which wraps the workflow's selectSql() in:

SELECT inner_q.id FROM (<workflow.selectSql>) inner_q
WHERE NOT EXISTS (
  SELECT 1 FROM workflow_runs wr
  WHERE wr.workflow = ?
    AND wr.document_id = inner_q.id
    AND wr.status IN ('done','failed','skipped')
)

The queue envelope

interface PublishEnvelope {
  doc_id: number;
  workflow: string;            // one of the 10 known workflows
  attempt?: number;
  idempotency_key?: string;
  envelope_id?: string;
  attribution: {
    project: string;           // "fraud-heuristics"
    service: string;           // "fraud-publisher" | "fraud-enricher-v2"
    workflow_stage?: string;
    client_id?: string;
  };
}

Source: shared/federation-queue-client.ts:PublishEnvelope.

GAP: master spec at *********/gpu-federation-monitor/docs/specs/federation-queue-architecture.md referenced from comments — for cross-project changes update both.

Ingest-side filings.db

This repo does not own EDGAR filings. The fingerprint NDJSONs at node-eighteen:/mnt/oink/docker/edgar-fraud-scan/data/flagged/ come from a sibling project (edgar-fraud-scan). For us they are read-only inputs cross-referenced when starting an investigation. See heuristics.md for the per-rule schemas.

Migration list (as of 2026-05-19)

broker/src/migrations/
├── 2026-05-18-federation-queue.sql   # queued_at, queued_workflow, envelope_id
├── 2026-05-18-scheme.sql             # scheme, scheme_confidence, scheme_classified_at, scheme_slug
├── 2026-05-18-title-format.sql       # title_formatted, title_format_source, reference_filing_json
├── 2026-05-19-workflow-runs.sql      # workflow_runs gating table
├── 2026-05-27-defendant-dispositions.sql  # defendant_dispositions table
└── case_facts.sql                    # case_facts table

Apply each via POST http://fraud-db-broker:3100/admin/migrate?name=<name> from inside the swarm overlay. Idempotent.

NOTE: the date-prefixed .sql files back the /admin/migrate?name= route, but the auto-run-on-startup migrations live in code (broker/src/migrate.ts, migrate-005…migrate-015). defendant_dispositions is created by migrate-015 on every broker boot, so no manual /admin/migrate call is needed for it. New tables should add a migrate-NNN block there too, not just a .sql file.