docs/knowledge/knowledge_tables.sql

-- 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:<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);


-- ============================================================================
-- §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 <person>.
ALTER TABLE knowledge_reviews ADD COLUMN IF NOT EXISTS authorised_by text;