| # Knowledge Pipeline — Persistence Contract (DDL handoff) |
|
|
| <!-- doc-status --> |
| > ✅ **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](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](KNOWLEDGE_PARSING_OUTPUT_CONTRACT.md) (parsing) and |
| [KNOWLEDGE_OUTPUT_CONTRACT.md](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 |
|
|
| ```sql |
| -- 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](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](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_id`s. 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. |
|
|
| ```sql |
| -- 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. |
| |
