docs/knowledge/KNOWLEDGE_PERSISTENCE_CONTRACT.md

Knowledge Pipeline — Persistence Contract (DDL handoff)

✅ LIVE — the five tables exist in dedorch as of 2026-09-07. Agreed by Rifqi and created by a one-time manual DDL run, exactly as knowledge_tables.sql. Still owed: Harry folds that SQL into Go's migration set — until he does, the tables exist only because they were run by hand, and a fresh environment comes up without them. That is the same gap that still leaves message_traceability and message_charts out of the migrations (§7).

💡 Was: PROPOSAL — v2, revised 2026-09-07, sent to Harry 2026-09-01. This is the D2 deliverable: the tables the knowledge pipeline needs, in Harry-ready form. Nothing here exists in dedorch yet and no ORM model has been written against it — do not implement until Harry confirms. Go owns the dedorch schema (CLAUDE.md §2.2); Python executes no DDL, ever.

Revision 2026-09-07 — read this before the tables below. Three changes: the first two driven by a read-only introspection of the live dedorch database, the third a decision by Rifqi.

1. Q1 is CLOSED. documents.id is text NOT NULL PRIMARY KEY — verified in the live schema and confirmed by Rifqi. knowledge_documents.document_id is text and may carry a real FK.

2. A fifth table, knowledge_jobs. Ingestion is asynchronous (parse ~80 s + extract ~170 s is far past any HTTP timeout) and Data Eyond's flow is one click over all of a user's documents, so the job is a batch that outlives any single document. Parsing has no run row, so a parse-stage failure had nowhere to live.

3. scope_id — company scope, decided by Rifqi 2026-09-07. Knowledge is a company's, not a user's: Data Eyond is sold to a company whose employees are its users, so the production standard one employee uploads and the maintenance SOP another uploads are one body of knowledge and must share a glossary. Under a user_id key they would never diff against each other and dedup would never fire. Every table here therefore carries scope_id text NOT NULL beside user_id (which stays, as the uploader).

   The value is provisional, and that is safe only because of one invariant. dedorch has no companies table (verified — 15 tables, none a tenant entity) and users.company is nullable free text with no id. So scope_id is populated today from a normalised users.company (casefold + trim), falling back to user:<user_id> when it is null — which degrades cleanly to single-user scope rather than to a shared bucket. Swapping to a real companies.id later is then an UPDATE … SET scope_id, a value migration, provided:

   ⚠️ scope_id MUST NEVER enter the entity_id derivation. ids.py hashes doc_id + content and nothing else (verified 2026-09-07). Keep it that way and scope_id stays a pure row-level partition key that can be re-pointed freely. Fold it into the hash and re-pointing it becomes a re-key that destroys every expert approval — the exact failure the whole id scheme exists to prevent.

   scope_id is also a tenant boundary, not just a label. Every read filters on it. This is the F-1 lesson (DEV_PLAN #38), where a store filtered on one key while Go filtered on two and the ownership check ended up comparing a value against itself.

Known weaknesses of the interim value, accepted knowingly: a typo in users.company forks a company's knowledge base, and renaming the company orphans it. Both are fixed by Q6.

Still deferred: a canonical knowledge_concepts layer unifying the same term across documents. Company scope makes it more likely to be wanted — two employees' documents genuinely collide, where one user's rarely would — but resolving a collision needs an expert to rule, and Data Eyond's flow has none. Until then knowledge_entries.diff_status carries the cross-document relationship, classified and surfaced rather than collapsed. See context doc §1.6.

Sent: 2026-09-01 · Owner: Rifqi → Harry · Tracker: DEV_PLAN §0.8 D2 · checklist X17 Producers: src/knowledge_parsing/ (artifact) · src/knowledge_extraction/ (runs, entries) Consumers: Go review CRUD (read + reviews) · Python analytics path (approved entries, later)

Companion to the two output contracts, which define the payload shapes this schema stores: KNOWLEDGE_PARSING_OUTPUT_CONTRACT.md (parsing) and KNOWLEDGE_OUTPUT_CONTRACT.md (extraction). This document adds only where those payloads live, never their shape.

---

0 · Ownership

Concern	Owner	Note
The DDL + migration	Harry (Go)	Go owns every dedorch migration. Python runs no create_all
Writing rows	Python	Parse + extract insert; transitional, same posture as report_inputs / message_charts
Review CRUD (read entries, approve/edit/reject)	Harry (Go)	Records CRUD is Go's shape. Python is generation-only (CLAUDE.md §2.4)
Ingestion endpoint (POST …/knowledge/ingest)	Python	The pipeline lives here. Async batch job. (Was: "only the Python image carries MinerU" — MinerU dropped 2026-09-07; auth posture settled separately, surface plan §6)

---

1 · Why five tables

The set was cut from five (chunks folded into the artifact JSONB, four entity kinds folded into one knowledge_entries), then knowledge_jobs was added back for a different reason. Three of the five look droppable and are not:

knowledge_extraction_runs. One document is re-extracted many times as prompts are tuned. Without a run row, entries from two runs are indistinguishable and a quality change cannot be attributed. This is not hypothetical: identical input scored 6/7 one day and 0/12 the next, and CallUsage.model_version / system_fingerprint exist precisely because the deployment name stayed stable while the model behind it moved. A run without those recorded is a measurement of an unknown.

knowledge_reviews. The candidate entry is what the model claimed; the review is what the expert ruled. They are different facts with different authors and must not share a row. Collapsing them into a status column on the entry destroys the audit trail that the entire span-verification apparatus exists to produce — and makes an edited value overwrite the claim it was correcting.

knowledge_jobs. A run row is created only once parsing has succeeded and extraction begins, so without a job row a parse failure is unrepresentable: the caller polls for something that will never exist. It is also the only row that spans the whole batch — Data Eyond's flow is one click over all of a user's documents, so the unit the user is waiting on is N documents, not one. knowledge_reviews is the one table Data Eyond's flow never writes; it is here because the expert loop is what the experiment exists to learn about (context doc §1.5).

---

2 · The tables

-- 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 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 idx_knowledge_documents_scope ON knowledge_documents (scope_id, created_at DESC);
CREATE INDEX idx_knowledge_documents_user  ON knowledge_documents (user_id);

-- 2. One row per extraction run.
CREATE TABLE 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 idx_knowledge_runs_doc ON knowledge_extraction_runs (knowledge_document_id);

-- 3. Candidate entries - all four kinds in one table.
CREATE TABLE knowledge_entries (
    id                    uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    run_id                uuid NOT NULL REFERENCES knowledge_extraction_runs(id) ON DELETE CASCADE,
    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; every read filters on it
    user_id               text NOT NULL,
    kind                  text NOT NULL CHECK (kind IN ('glossary','rule','formula','domain')),
    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)
);
CREATE INDEX idx_knowledge_entries_scope    ON knowledge_entries (scope_id, kind);
CREATE INDEX idx_knowledge_entries_doc_kind ON knowledge_entries (doc_id, kind);
CREATE INDEX idx_knowledge_entries_entity   ON knowledge_entries (entity_id);
CREATE INDEX 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 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 idx_knowledge_reviews_entity ON knowledge_reviews (entity_id, content_hash);
CREATE INDEX 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 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 idx_knowledge_jobs_user ON knowledge_jobs (user_id, created_at DESC);

---

3 · Column semantics that are not obvious

entity_id is deterministic and content-derived — t_/f_/r_/b_ plus a sha256 prefix over document-scoped content, never positional and never model-written (src/knowledge_extraction/ids.py; scheme in KNOWLEDGE_OUTPUT_CONTRACT.md §1). This is what lets an expert's approvals survive a prompt retune, which is why knowledge_reviews keys on entity_id + content_hash rather than on entry_id: re-extracting mints new entry rows but keeps the same entity ids, so prior decisions still resolve. These two tables together are the persisted registry that the "known limit" note in ids.py and the output contract both point at.

parser_backend is not cosmetic. MinerU's CPU-only pipeline backend copies the multiplication sign as a literal letter x, fusing MOHH x Qty x PA into a single token and taking five glossary terms with it; hybrid/vlm write \times. It also never analyses images, so generated_description is always null. A pipeline artifact is valid but lower fidelity and must never be used as a reference measurement (calibration §13). Recording the backend per row is what keeps a CPU-parsed artifact distinguishable from a GPU-parsed one after the fact.

normaliser_version and parser_config fingerprint different things. parser_config covers MinerU's settings; normaliser_version covers our own post-processing code; schema_version versions the artifact's shape. None of the three moves when a normalisation bug is fixed without a shape change — which is exactly how a stale artifact went unnoticed for a day on 2026-08-26.

The review queue is not stored. It is a pure sort over entries — conflicts first, then descending mention count (queue/review_queue.py). Persisting a derived ordering creates a second truth that drifts from the first. idx_knowledge_entries_queue exists so Go can produce it with an ORDER BY; review_reason is likewise derived at read time from diff_status / definition_conflict / extraction_status / the presence of a definition.

Promoted columns are a read optimisation, not a second copy. mention_count, extraction_status, diff_status and definition_conflict also live inside payload. They are lifted out so the queue can be ordered and filtered in SQL without opening jsonb. payload is the source of truth; a writer must set both from the same object in the same insert.

Files stay files. The source PDF and the untouched MinerU output belong in blob storage, not Postgres. raw_output_dir holds a blob key, not a local path — today it is a local directory, which is a Python-side change tracked in KNOWLEDGE_SURFACE_PLAN.md.

---

3.5 · Conformance with dedorch's live conventions (checked 2026-09-07)

Checked by read-only introspection so Harry gets nothing to bounce back. Matching already: uuid primary keys with DEFAULT gen_random_uuid() (pgcrypto is installed), text for user_id and document ids, timestamptz … DEFAULT now(), jsonb NOT NULL DEFAULT '[]'::jsonb / '{}'::jsonb, plural snake_case table names, idx_<table>_<purpose> index names, Postgres-default <table>_<col>_fkey constraint names.

One deliberate divergence, flagged rather than hidden. dedorch's existing foreign keys carry no ON DELETE action (message_charts_analysis_id_fkey is a bare REFERENCES analyses(id)), while the knowledge tables use ON DELETE CASCADE down the document → run → entry chain. That is intended: those three are one artifact at three grains, and an entry outliving the run that produced it is meaningless. Say the word if the house rule is no cascade anywhere and we will make deletion explicit instead.

⚠️ One cascade is wrong and should not ship as drafted: knowledge_reviews.entry_id … ON DELETE CASCADE. Reviews are the append-only record of what a human ruled, and they key on entity_id + content_hash precisely so a decision survives re-extraction — but a cascade means pruning old entry rows silently deletes the approvals attached to them. The keying protects the audit trail and the cascade destroys it. Recommend ON DELETE RESTRICT: an entry that has been ruled on cannot be deleted until its reviews are dealt with deliberately. This is the one change to the DDL between v2 as drafted and what should actually be migrated.

documents FK — keep it (A4, decided 2026-09-07). knowledge_documents.document_id text REFERENCES documents(id). documents.id is a stable text primary key, ingestion now reads documents rows rather than file paths, and it is the natural stable key for the doc_id problem on the parsing side. Null stays legal for local CLI parses that never went through an upload.

---

4 · Write rules for Python

1. No DDL, ever. Python only INSERTs and SELECTs. If a column is missing, the read is getattr-tolerant of both shapes and a new handoff goes to Harry.
2. knowledge_reviews is append-only. Python never UPDATEs or DELETEs it. Neither should Go.
3. knowledge_documents is content-addressed. Re-parsing the same file with the same settings should find the existing (doc_id, content_hash, version) row, not duplicate it. A changed document or changed parser settings is a new version.
4. Entries are written per run, never upserted across runs. Two runs of one document produce two full sets of entry rows sharing entity_ids. History is the point.
5. Ingest is never on the request path. Nothing in the chat pipeline imports the parsing or extraction modules; the span model is imported lazily so import main pulls neither torch nor gliner. That stays true.

---

5 · Open questions for Harry

Verified against the live dedorch database 2026-09-07 (read-only introspection: 15 tables). The questions the schema itself could answer have been answered; what is left needs Harry.

Q1 — document_id type. CLOSED 2026-09-07. documents.id is text NOT NULL PRIMARY KEY (live schema; pgcrypto is installed so gen_random_uuid() is available). knowledge_documents.document_id is declared text REFERENCES documents(id). Confirm the FK is welcome, or say so and we drop the constraint and keep the bare column.

Q2 — Review CRUD: yours later, ours minimally now. The four routes proposed on 2026-09-01 are the production review surface and belong to the separate B2B knowledge product (context doc §1.5) — no need to build them yet. But knowledge_reviews is written from day one, not left empty: we are running the loop end to end in Data Eyond specifically to learn where it is wrong, and a pipeline that stops at "here are 63 candidates" never surfaces the problems that appear only when a human works the queue. Python will therefore expose a minimal, explicitly transitional review surface (list the queue, record a decision) to be handed to Go when Product B is real. Flagging it so the eventual ownership is not a surprise. Two shape amendments to carry whenever the production version is built: queue rows carry row_kind (term | key_parameter, where a key_parameter row has null term_id, no definition and no mention count), and provenance carries a 1-based page_no beside the 0-based page — a review UI must render page_no.

Q3 — Provenance lookup cost. With chunks inside artifact, resolving one chunk_id to highlight a span is a jsonb path query rather than an indexed row read. Fine at current sizes (9 pages = 31 chunks); if a review UI ever hammers it, promoting chunks to their own table is the fix and is backward compatible, so deferring costs nothing.

Q4 — Migration ordering. These five tables depend on no Go-owned table except the optional documents FK in Q1, so the migration can land independently of anything else in flight.

Q5 — Are message_traceability and message_charts in your migration set? Both tables exist in dedorch (verified: 80 and 6 rows) and idx_message_charts_message is present — but they were created by hand from this side (DEV_PLAN #32 / V5 / V8) and were never handed over as a migration. A fresh environment built from Go's migrations alone would therefore come up without them, and both write paths are never-throw seams, so the failure is silent: charts and traceability simply stop being recorded. Please fold the existing shapes into the migration set. No change to either table is being requested — only that the schema of record matches what is live.

Q6 — We need a real company / organisation entity. Is one planned? ⬆️ Raised from FYI to a request 2026-09-07. Knowledge in Data Eyond is scoped to the company, not the uploader: one employee's production standard and another's maintenance SOP are one body of knowledge and must share a glossary. Every table here now carries scope_id text NOT NULL.

There is nothing to key it on today — dedorch has no companies table, and users.company is nullable free text with no id (all 9 live users carry the same string). So the interim value is a normalised users.company, falling back to user:<user_id>. That works, with two known weaknesses: a typo forks a company's knowledge base, and renaming the company orphans it.

The ask: a companies table with a stable id, and users.company_id referencing it. Not blocking — we can ship on the interim value — but the sooner it exists, the smaller the backfill. It stays a cheap UPDATE … SET scope_id (a value migration, never a re-key) because scope_id is deliberately excluded from the entity_id derivation; that exclusion is an invariant on our side, recorded in the revision note above.

---

6 · Status

Item	State
Tables created in dedorch	✅ 2026-09-07 — 5 tables + 10 indexes, verified by introspection (column counts 18/20/15/11/15, all FKs and CHECKs present)
Go migration	⬜ The one thing still owed. Harry adopts knowledge_tables.sql verbatim
ORM models · write path · endpoints	✅ 2026-09-07 — src/db/postgres/models.py, src/knowledge_ingest/, src/api/v1/knowledge.py
Verified end to end	✅ 88 entry rows persisted from the BUMA standard; idempotency and tenant scoping both checked
DDL drafted and sent (v1, four tables)	✅ 2026-09-01
Revised to v2 — Q1 closed, knowledge_jobs added, scope_id (company) added	✅ 2026-09-07
Conformance pass · documents FK kept · reviews FK CASCADE → RESTRICT · Q5 appendix	✅ 2026-09-07
status/stage CHECK constraints widened before creation (blocked, filtering, estimating)	✅ 2026-09-07 — as drafted, the table would have rejected rows this service produces
Ready to send — one self-contained document, six questions	✅ 2026-09-07
Verified against the live dedorch schema (read-only)	✅ 2026-09-07
Harry's response	⬜ blocks everything below
ORM models in src/db/postgres/models.py	⬜ Deliberately not written — waiting on Harry
Python write path	⬜ Blocked on the above
Minimal review surface (Python, transitional)	⬜ Blocked on the write path — the loop must close end to end
Go review CRUD (production)	⏸️ Deferred — Product B (Q2)

---

7 · Appendix — two tables already live but never handed over (Q5)

Not part of the knowledge pipeline. They are here because this is the one handoff going to Harry and both have been outstanding since July (DEV_PLAN #32 / V5 / V8). No change is requested — the SQL below is the live shape, transcribed from dedorch on 2026-09-07 (pg_get_constraintdef + pg_indexes, read-only). The ask is only that Go's migration set matches what is already running.

Why it matters. Both were created by hand from the Python side to unblock a live test, and the hand-over never happened. A fresh environment built from Go's migrations alone comes up without them — and because both write paths are never-throw seams, nothing errors: charts and traceability simply stop being recorded, and the first symptom is an empty GET /charts that nobody can explain. This is exactly the failure mode the appendix exists to close.

-- LIVE in dedorch (80 rows on 2026-09-07). Python-owned; one JSONB row per chat message.
-- Note: the PK is message_id itself - there is no surrogate id, and no FK to analyses.
CREATE TABLE message_traceability (
    message_id  text        NOT NULL,
    analysis_id uuid        NOT NULL,
    user_id     text        NOT NULL,
    intent      text        NOT NULL,
    data        jsonb       NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT message_traceability_pkey PRIMARY KEY (message_id)
);
CREATE INDEX idx_message_traceability_analysis ON message_traceability (analysis_id);

-- LIVE in dedorch (6 rows on 2026-09-07). Python-owned; one row per rendered chart.
CREATE TABLE message_charts (
    id          uuid        NOT NULL DEFAULT gen_random_uuid(),
    message_id  text        NOT NULL,
    analysis_id uuid        NOT NULL,
    user_id     text        NOT NULL,
    record_id   text,
    chart_type  text        NOT NULL,
    title       text,
    spec        jsonb       NOT NULL,
    created_at  timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT message_charts_pkey PRIMARY KEY (id),
    CONSTRAINT message_charts_analysis_id_fkey FOREIGN KEY (analysis_id) REFERENCES analyses(id)
);
CREATE INDEX idx_message_charts_lookup  ON message_charts (analysis_id, message_id);
CREATE INDEX idx_message_charts_message ON message_charts (message_id);

idx_message_charts_message backs the 2026-07-14 lead decision that GET /charts looks up by message_id alone; idx_message_charts_lookup predates it and is kept because GET /traceability still takes both parameters. report_inputs is not listed here — its schema was handed over on 2026-07-22 (DEV_PLAN #22) and needs nothing.