File size: 11,505 Bytes
c2164ae 601937c c2164ae 1b8660e c2164ae 601937c c2164ae | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 | -- 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);
|