-- Knowledge pipeline tables — dedorch -- -- HANDOFF TO HARRY. Updated 2026-09-14. Source of record: -- docs/knowledge/KNOWLEDGE_PERSISTENCE_CONTRACT.md §2. -- -- ⚠️ 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. Everything in this file was run ONCE by -- an operator to unblock the build, following the 2026-07-13 `message_charts` -- precedent — and that precedent's mistake is the one this file exists to avoid: -- 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 because both write -- paths are never-throw seams, nothing errors. They just stop recording. -- -- Run by hand, then hand over. Never run by hand only. -- -- ───────────────────────────────────────────────────────────────────────────── -- WHAT IS LIVE IN DEDORCH RIGHT NOW, AND WHAT YOU ALREADY HAVE -- ───────────────────────────────────────────────────────────────────────────── -- -- §1-§5 five tables run 2026-09-07 sent ✅ -- §6 4 columns + 2 indexes run 2026-09-14 sent ✅ -- §7 3 columns (review modes) run 2026-09-14 NOT SENT ⬅ new -- -- **If you adopted the copy sent before 2026-09-14, it is now incomplete in two -- ways, and one of them is silent:** -- -- 1. §7 is missing entirely. Without it, `POST /knowledge/ingest` with -- `review_mode=llm` fails on insert and no entry is ever auto-approved. -- 2. §1-§5 DRIFTED AFTER the 2026-09-07 run, and `CREATE TABLE IF NOT EXISTS` -- will not repair an existing table. Two edits landed: -- * `knowledge_entries.kind` CHECK gained 'document' -- * `knowledge_entries.run_id` / `.knowledge_document_id` became NULLABLE -- for kind='domain' only, guarded by knowledge_entries_parent_check -- Both are reflected in §3 below. **Take the current file, not the old one.** -- -- Nothing here needs designing. The ask is only that the migration set -- reproduce what is already running, so a fresh environment matches. -- -- ───────────────────────────────────────────────────────────────────────────── -- STILL OUTSTANDING FROM AN EARLIER ROUND — not in this file -- ───────────────────────────────────────────────────────────────────────────── -- -- `message_traceability` (80 rows) and `message_charts` (6 rows) have been live -- since July, created the same way, and are still absent from the migration set. -- Their DDL is in KNOWLEDGE_PERSISTENCE_CONTRACT.md §appendix rather than here. -- Say the word and it gets folded into this file so one artifact covers every -- Python-written table you are missing. -- -- ───────────────────────────────────────────────────────────────────────────── -- VERIFIED AGAINST LIVE — 2026-09-14, read-only -- ───────────────────────────────────────────────────────────────────────────── -- -- Not 'what we believe ran'. Read back out of dedorch after the fact: -- -- 5 tables knowledge_documents 19 cols · knowledge_entries 18 · -- knowledge_extraction_runs 20 · knowledge_jobs 16 · -- knowledge_reviews 13 -- §6+§7 columns 7 of 7 present (doc_ids, content, provenance, -- withdrawn_at, review_mode, decided_by, authorised_by) -- indexes 19 on knowledge_* (12 named here + 7 PK/unique) -- -- The ORM also reads every one of them against live, so the Python models -- and this file agree with the database rather than only with each other. -- -- Idempotent throughout (IF NOT EXISTS) · Additive only: no DROP, no type -- change, nothing that rewrites an existing column. -- 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); -- ============================================================================ -- §6. EXECUTED 2026-09-14 — from the 2026-09-08 discussion (m3, m4, m5, m8, i7). -- -- Verified after the run: 4 new columns and 2 new indexes present, and the -- ORM reads all of them against live. Harry still needs this in the -- migration set so a fresh environment reproduces it. -- -- Additive only: three columns and one index on `knowledge_entries`. Nothing -- is dropped and no column changes type, so a pre-migration reader keeps -- working and Python's reads stay tolerant of both shapes while this lands. -- ============================================================================ -- m3. "doc_id jadi list". One entry fuses meaning from several documents, but -- every source stays recorded. `doc_id` REMAINS the primary source — every -- existing filter and index uses it, and a single extracted assertion -- always has exactly one — while `doc_ids` carries the full list. Additive -- rather than a type change: rewriting `doc_id text` into jsonb in place -- would break every read at once, for a value that is still needed. ALTER TABLE knowledge_entries ADD COLUMN IF NOT EXISTS doc_ids jsonb NOT NULL DEFAULT '[]'; -- m4. "kolom content". The definition or rule body, lifted out of `payload` so -- a consumer does not have to open jsonb to use it — the objection raised -- on 2026-09-08 was that too much JSON makes the data contract hard and -- easy to break. `payload` stays the source of truth; this is a promoted -- copy written from the same object in the same insert, exactly the -- convention `label`, `mention_count` and `diff_status` already follow. ALTER TABLE knowledge_entries ADD COLUMN IF NOT EXISTS content text; -- m5. "kolom provenance". Which document, which page — a list of objects: -- [{"doc_id": "STD_2026_006_MNO", "page": 3, "page_no": 4, -- "section_no": "2.1.3", "chunk_id": "STD_2026_006_MNO::0007"}] -- Both page forms travel together on purpose: `page` is 0-based as the -- parser reports it, `page_no` is the 1-based number a human reads, and -- they are derived from one value so they cannot disagree. -- `section_no` is included because the review queue already renders it — -- whether it earns a place was left open in the meeting, so drop it from -- this shape if the answer is no. ALTER TABLE knowledge_entries ADD COLUMN IF NOT EXISTS provenance jsonb NOT NULL DEFAULT '[]'; -- m8. Makes the readable-key lookup a hit instead of a scan within the tenant. -- `label` now carries the TYPE_SUBJECT key (`GLOSSARY_PA`), and -- `KnowledgeStore.get_by_key` filters on exactly this pair. Without the -- index the lookup is still correct, just not the O(1) the design intends. CREATE INDEX IF NOT EXISTS idx_knowledge_entries_key ON knowledge_entries (scope_id, label); -- i7. "kasus dokumen dihapus" — ruling 2026-09-14: mark withdrawn, keep the -- rows, flag what is left. A withdrawn document stops counting towards the -- knowledge without anything being destroyed: an approval is an act someone -- took, and the record of what it referred to must survive the source going -- away. Null = live. Set = withdrawn. -- Entries whose last remaining source is withdrawn are surfaced to the -- expert to rule on rather than deleted — whether the knowledge leaves with -- the document is their call, not the pipeline's. ALTER TABLE knowledge_documents ADD COLUMN IF NOT EXISTS withdrawn_at timestamptz; CREATE INDEX IF NOT EXISTS idx_knowledge_documents_live ON knowledge_documents (scope_id, doc_id) WHERE withdrawn_at IS NULL; -- ============================================================================ -- §7. EXECUTED 2026-09-14 — the review system (r1, r2, r3) from the -- 2026-09-08 discussion. Verified after the run: the ORM reads -- review_mode, decided_by and authorised_by against live. -- -- Additive only: three columns, no drops, no type changes. -- -- The point of these, in Mas's words: an entry approved without a human -- looking at it must still say WHO allowed that to happen. *"Kalau ada apa-apa -- jangan sampai kita yang disalahin… ya udah itu by mereka aja."* It is a -- risk-mitigation requirement, not bookkeeping — which is why it belongs in -- columns rather than in a free-text note. -- ============================================================================ -- r1. Which review mode a batch was ingested under. Chosen by the caller at -- ingest time: 'expert' leaves entries waiting for a human; 'llm' takes -- them straight to approved with no reviewer step at all. -- Nullable so every job written before this column existed stays legal. ALTER TABLE knowledge_jobs ADD COLUMN IF NOT EXISTS review_mode text CHECK (review_mode IS NULL OR review_mode IN ('expert','llm')); -- r2. WHO decided. 'human' is the existing behaviour and the default, so every -- review already recorded keeps its meaning without a backfill. ALTER TABLE knowledge_reviews ADD COLUMN IF NOT EXISTS decided_by text NOT NULL DEFAULT 'human' CHECK (decided_by IN ('human','llm')); -- r3. On WHOSE AUTHORITY. The admin who clicked ingest and the expert who -- reviews can be different people, and in 'llm' mode there is no reviewer -- at all — so the clicker's id has to reach this row or the decision has -- no owner. `reviewer_id` stays "who performed the review"; -- `authorised_by` is "who permitted it to happen this way". -- Together they render: approved by LLM with permission of . ALTER TABLE knowledge_reviews ADD COLUMN IF NOT EXISTS authorised_by text;