Rifqi Hafizuddin
Claude Opus 5
[NOTICKET] feat(knowledge-domain): the declared half — domain TBox + review (V4-9, V4-10)
601937c | -- 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:<user_id>'. 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); | |