-- Knowledge pipeline tables — dedorch -- -- Source of record: docs/knowledge/KNOWLEDGE_PERSISTENCE_CONTRACT.md §2. -- This file is the exact SQL that was executed, kept so that what ran is -- reproducible and reviewable rather than retyped from prose. -- -- ⚠️ OWNERSHIP. Go owns every dedorch migration (CLAUDE.md §2.2). Python -- executes NO DDL, ever: `SKIP_INIT_DB` stays true, there is no `create_all` -- and no Alembic against this database. These statements were run ONCE by an -- operator to unblock the build, following the 2026-07-13 `message_charts` -- precedent — and that precedent's mistake must not be repeated: the schema was -- run by hand and never handed over, so a fresh environment built from Go's -- migrations comes up WITHOUT those tables and both write paths fail silently. -- -- Run by hand, then hand over. Never run by hand only. -- -- Harry: these five tables now exist in dedorch. Nothing here needs designing — -- the ask is that the migration set match what is already running, so a fresh -- environment reproduces it. See §5 Q5 for the two older tables in the same -- position (message_traceability, message_charts). -- -- Executed: 2026-09-07 · Idempotent (IF NOT EXISTS throughout) · Additive only: -- no DROP, no ALTER, nothing touching an existing table. -- 1. Parsed document artifact. Chunks live INSIDE `artifact` (jsonb), deliberately -- not a child table: documents are small (9 pages = 31 chunks) and the artifact -- is versioned as a unit. CREATE TABLE IF NOT EXISTS knowledge_documents ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), doc_id text NOT NULL, -- pipeline key, e.g. 'STD_2026_006_MNO' (see 5 Q1) document_id text REFERENCES documents(id), -- Q1 CLOSED 2026-09-07: documents.id is text PK. -- Null for local CLI parses; set when ingested from an uploaded doc. scope_id text NOT NULL, -- TENANT KEY: the company. Provisional value - -- normalised users.company, else 'user:'. See revision note 3. user_id text NOT NULL, -- the uploader, not the owner of the knowledge source_path text NOT NULL, content_hash text NOT NULL, -- sha256 of the SOURCE FILE n_pages integer NOT NULL, version integer NOT NULL DEFAULT 1, schema_version text NOT NULL, -- ParsedDocument contract version, e.g. '0.3.1' parser_name text NOT NULL DEFAULT 'mistral', -- Mistral OCR is the primary parser since 2026-09-02 parser_version text, -- '3.4.4' parser_backend text, -- 'mistral' | 'pipeline' | 'hybrid' | 'vlm' (see section 3) parser_config text, -- fingerprint of output-affecting settings normaliser_version text, -- fingerprint of our post-processing code raw_output_dir text, -- blob key for the untouched parser output artifact jsonb NOT NULL, -- full ParsedDocument, chunks[] included created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (doc_id, content_hash, version) ); CREATE INDEX IF NOT EXISTS idx_knowledge_documents_scope ON knowledge_documents (scope_id, created_at DESC); CREATE INDEX IF NOT EXISTS idx_knowledge_documents_user ON knowledge_documents (user_id); -- 2. One row per extraction run. CREATE TABLE IF NOT EXISTS knowledge_extraction_runs ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), knowledge_document_id uuid NOT NULL REFERENCES knowledge_documents(id) ON DELETE CASCADE, doc_id text NOT NULL, scope_id text NOT NULL, -- tenant key, see revision note 3 user_id text NOT NULL, status text NOT NULL DEFAULT 'running', -- running | succeeded | failed branches jsonb NOT NULL DEFAULT '[]', -- ['glossary','rule','formula','summary'] model_deployment text, -- OUR name for it; stable model_version text, -- returned by the API; CAN move under a stable deployment system_fingerprint text, n_calls integer NOT NULL DEFAULT 0, prompt_tokens integer NOT NULL DEFAULT 0, cached_tokens integer NOT NULL DEFAULT 0, completion_tokens integer NOT NULL DEFAULT 0, usage jsonb NOT NULL DEFAULT '[]', -- CallUsage[] rejected jsonb NOT NULL DEFAULT '[]', -- RejectedField[] - what the span check refused counts jsonb NOT NULL DEFAULT '{}', -- cluster/entry counts, link dangle counts error_message text, created_at timestamptz NOT NULL DEFAULT now(), completed_at timestamptz ); CREATE INDEX IF NOT EXISTS idx_knowledge_runs_doc ON knowledge_extraction_runs (knowledge_document_id); -- 3. Candidate entries - all four kinds in one table. CREATE TABLE IF NOT EXISTS knowledge_entries ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), -- Nullable since 2026-09-08, and only for kind='domain'. Every other entry -- is produced BY a run FROM a document, but the scope-level domain -- declaration is composed across all of them and belongs to neither. The -- CHECK below is what keeps that from becoming a loophole for the rest. run_id uuid REFERENCES knowledge_extraction_runs(id) ON DELETE CASCADE, knowledge_document_id uuid REFERENCES knowledge_documents(id) ON DELETE CASCADE, doc_id text NOT NULL, scope_id text NOT NULL, -- tenant key; every read filters on it user_id text NOT NULL, -- 'document' added 2026-09-08, BEFORE this file was adopted, so it costs an -- edit rather than a migration. Under the v4 plan (KNOWLEDGE_DOMAIN_CONTEXT_V4.md) -- 'domain' becomes the SCOPE-level semantic model - measures, entities, -- conventions, boundary - and 'document' takes over the per-document brief -- that 'domain' holds today. Both values are legal now so the split can land -- without a second schema change. kind text NOT NULL CHECK (kind IN ('glossary','rule','formula','domain','document')), entity_id text NOT NULL, -- term_id | rule_id | formula_id | domain_id label text, -- term / formula name / rule head, for listing without opening jsonb mention_count integer NOT NULL DEFAULT 0, extraction_status text, -- ok | no_definition_found | escalated diff_status text, -- new | duplicate | conflicting definition_conflict boolean NOT NULL DEFAULT false, payload jsonb NOT NULL, -- full GlossaryEntry | RuleEntry | FormulaEntry | DomainContext created_at timestamptz NOT NULL DEFAULT now(), UNIQUE (run_id, kind, entity_id), -- Only a domain declaration may be parentless; anything else without a run -- and a document is a bug, not a scope-level object. CONSTRAINT knowledge_entries_parent_check CHECK ( kind = 'domain' OR (run_id IS NOT NULL AND knowledge_document_id IS NOT NULL) ) ); CREATE INDEX IF NOT EXISTS idx_knowledge_entries_scope ON knowledge_entries (scope_id, kind); CREATE INDEX IF NOT EXISTS idx_knowledge_entries_doc_kind ON knowledge_entries (doc_id, kind); CREATE INDEX IF NOT EXISTS idx_knowledge_entries_entity ON knowledge_entries (entity_id); CREATE INDEX IF NOT EXISTS idx_knowledge_entries_queue ON knowledge_entries (run_id, definition_conflict DESC, mention_count DESC); -- 4. Expert decisions. APPEND-ONLY - no UPDATE, no DELETE. -- A changed mind is a NEW row; current state = latest row per entity_id. CREATE TABLE IF NOT EXISTS knowledge_reviews ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), entry_id uuid NOT NULL REFERENCES knowledge_entries(id) ON DELETE RESTRICT, -- RESTRICT, not CASCADE: a cascade would let entry pruning silently delete an -- expert's rulings, destroying the audit trail the entity_id keying protects. See 3.5. entity_id text NOT NULL, -- the DURABLE key: survives re-runs, unlike entry_id doc_id text NOT NULL, scope_id text NOT NULL, -- tenant key, see revision note 3 content_hash text NOT NULL, -- WHICH artifact version this decision was made against decision text NOT NULL CHECK (decision IN ('approved','edited','rejected')), edited_payload jsonb, -- populated only when decision = 'edited' note text, reviewer_id text NOT NULL, created_at timestamptz NOT NULL DEFAULT now() ); CREATE INDEX IF NOT EXISTS idx_knowledge_reviews_entity ON knowledge_reviews (entity_id, content_hash); CREATE INDEX IF NOT EXISTS idx_knowledge_reviews_entry ON knowledge_reviews (entry_id); -- 5. Ingestion jobs. ASYNC ENVELOPE, and the batch unit: Data Eyond's flow is -- one click over MANY documents, so one job spans N of them. Whether the -- batch is the clicker's documents or the whole company's is open (surface plan). -- A run row exists only once extraction starts, so a PARSE failure has no -- other home; without this table the caller polls for a row never created. CREATE TABLE IF NOT EXISTS knowledge_jobs ( id uuid PRIMARY KEY DEFAULT gen_random_uuid(), scope_id text NOT NULL, -- tenant key, see revision note 3 user_id text NOT NULL, -- who clicked the button status text NOT NULL DEFAULT 'queued' CHECK (status IN ('queued','running','succeeded','partial','failed','blocked')), -- 'blocked' = the pre-flight estimate exceeded the spend ceiling and the job -- declined to continue. NOT a failure: it is the one outcome an operator can -- act on (raise the ceiling, or ingest fewer documents at a time). stage text CHECK (stage IN ('parsing','filtering','estimating','extracting','persisting')), -- Ordered by cost. Everything up to and including 'estimating' is free or -- near-free; 'extracting' is where the LLM spend happens, which is why the -- ceiling is enforced between them. document_ids jsonb NOT NULL DEFAULT '[]', -- documents(id)[] requested in this batch n_requested integer NOT NULL DEFAULT 0, n_succeeded integer NOT NULL DEFAULT 0, n_failed integer NOT NULL DEFAULT 0, results jsonb NOT NULL DEFAULT '[]', -- per-document {document_id, knowledge_document_id, -- run_id, status, error} - what the poll route renders estimated_cost jsonb, -- pre-flight estimate_cost() output; the spend gate error_message text, -- set only when the JOB failed, not a member document created_at timestamptz NOT NULL DEFAULT now(), started_at timestamptz, completed_at timestamptz ); CREATE INDEX IF NOT EXISTS idx_knowledge_jobs_user ON knowledge_jobs (user_id, created_at DESC);