Canonical pipeline write-path → prod schema gap audit (S295)

Canonical pipeline write-path → prod schema gap audit (S295)

Purpose: precise, read-only diff of every cocoindex declare_row write in scripts/cocoindex_pipeline/flow.py against the actual prod content_items / source_documents / content_chunks / q_a_extractions / entity_mentions columns. This is the input artefact for ID-64 DDL schema reconciliation — the pre-ingest gate that blocks the {66.16} content-write half.

Provenance: S295, 2026-06-01. Prod project rovrymhhffssilaftdwd, information_schema.columns (read-only). Source tree at 15d564e6 (scripts/cocoindex_pipeline/flow.py). Audit performed after the S295 extraction-layer fixes (cfb2535a validation defaults + 2558365a code-fence strip + 15d564e6 stamp-wiring) proved the extraction half works live — the first latent write-path bug (content_items.content_text does not exist) surfaced only because extraction now reaches the DB write.

Why this exists: the pipeline was coded against an assumed schema (the ColumnDef comments trust a database.types.ts shape that does not match prod). The canonical pipeline has never completed a content-write to prod, so these mismatches were never exercised. The full reconciliation is ID-64’s job, gated on Liam’s OQ-64 retention decisions — this doc decides nothing, it just enumerates the gaps precisely.

NOT-NULL-violation rule applied: a prod column counts as “required-not-written” when is_nullable = NO AND column_default IS NULL AND is_generated = NEVER, and the pipeline never writes it.

---

Summary

Write-blocked today (2 of 5):

Table	Blocked by	Class
content_items	(1) writes content_text — column absent in prod (the live UndefinedColumnError); (2) prod title NOT NULL never written; (3) prod content_type NOT NULL never written	(1) pipeline-code rename → content; (2) DDL/design question; (3) mostly pipeline-code (value already computed at flow.py:1484)
source_documents	writes content_fingerprint — column absent in prod; and prod NOT-NULL cols never written: filename, original_filename, mime_type, file_size, (content_hash)	rename content_fingerprint→content_hash is plausible pipeline-code; the 4 file-metadata NOT-NULL cols are DDL/design questions

Not write-blocked (3 of 5): content_chunks, q_a_extractions, entity_mentions — every written key exists in prod and every NOT-NULL-no-default column is satisfied. Three low-risk CHECK/type verifies remain (below).

Pipeline-code fixes (no DDL — just align the code):

• content_items: rename declare_row/ColumnDef key content_text → content (flow.py:816, 1403). Value already correct (the Stage-2 extracted body).
• content_items: add content_type to ColumnDef + declare_row using the already-available _field(classification, "content_type") (computed at flow.py:1484 but never written to content_items). Verify the classifier value satisfies prod’s content_type domain.
• source_documents: likely rename content_fingerprint → content_hash (flow.py:859, 1385). Confirm the fingerprint-hex is the intended content_hash.

Genuine DDL / design questions for ID-64 (do NOT decide here):

• content_items.title (NOT NULL, no default): canonical source undecided — classifier suggested_title (prod has it, nullable), filename-derived, or first heading? Either populate it or relax the column.
• source_documents.{filename, original_filename, mime_type, file_size} (all NOT NULL, no default): the on-prem flow currently surfaces only rel_path (→ storage_path) + content_fingerprint; deriving these from the file (basename + os.stat size + mimetype guess) vs threading them through the flow vs relaxing the columns is a design decision.
• Whether content_text→content and content_fingerprint→content_hash are true renames vs the pipeline targeting a different intended schema shape (i.e. reconcile by changing the pipeline vs adding columns) — ID-64/S296 framing.

Low-risk verify-before-trust items (3 clean tables):

• content_chunks.position: ColumnDef integer vs prod smallint (int2) — flow.py:920-923 asserts PG casts int4→int2 on INSERT; confirm under cocoindex’s parameterised INSERT.
• q_a_extractions.extractor_kind: written constant "llm_extraction" (flow.py:1491) — confirm no prod CHECK constraint rejects it.
• entity_mentions.entity_type: free text in prod — confirm no CHECK constraint on permitted entity types.

---

S296 — RATIFIED DECISIONS (Liam) — these resolve the OQs

Authored S296 against this S295 audit + the S292 re-baseline (top of canonical-pipeline-sequencing.md) + decision-graph Q1.9 Option α + 02-data-flow.md §9.3 + the ID-53 / ID-57 / ID-58 cross-task synthesis. The write-path is the live {66.16} content-half blocker; everything below is now decided.

Write-path resolutions

Gap	Ratified resolution	Class
content_items.content_text	RENAME declare_row/ColumnDef key → content (canonical body col, 02-data-flow §9.3)	pipeline-code (flow.py)
content_items.content_type	WRITE classifier value (already computed flow.py:1484); guard to the 14-value CHECK domain (capability retired S237), fallback 'other'	pipeline-code
content_items.title	classification.suggested_title ?? filename_stem(rel_path). NO migration (title stays NOT NULL)	pipeline-code
source_documents.content_fingerprint	RENAME → content_hash (Q1.9 α; value = file content-hash hex)	pipeline-code
source_documents.{filename, mime_type, file_size}	DERIVE + write: basename / suffix-mime / os.stat size (Q1.9 α keeps these)	pipeline-code
source_documents.original_filename	RELAX to NULLABLE (Q1.9 α retires it) — ONE migration	DDL (1 ALTER)
content_chunks.position int4→int2 · q_a_extractions.extractor_kind CHECK · entity_mentions.entity_type CHECK	verify-only (not blockers)	verify

OQ-64 dispositions (consolidated — post-S292 + S296)

• OQ-64-1 (RLS membership) — CLOSED S292. Role-based USING(true) is intended. {64.5} = RESEARCH-framing correction only, NOT a re-ingest gate.
• OQ-64-2 (RSS/URL → content_items vs feed_articles) — resolve in {64.7}; feed_articles is already the RSS home.
• OQ-64-3 (bid_responses table rename) — DEFER out of pre-re-ingest scope (touches live procurement UI + the unbuilt ID-58 citing_entity='bid_response' value).
• OQ-64-4 (entity_aliases) — RETAIN. No canonical entities table exists → no FK target; it is admin-curated read-only input to Stage-5 (ID-53) → do NOT truncate, do NOT add FK. {64.6} = doc-only. entity_pair_resolutions determinism cache also retained (truncating breaks idempotency). NOTE: ontology 18-entity-aliases.md claims a canonical_id FK that does NOT exist in prod (live col is free-text canonical) — correct the ontology doc; the real entity-spine FK is T12/ID-60 future work.
• OQ-64-5 (T10/T11/T12 land order) — AFTER re-ingest. ID-57/58 capture nothing at ingestion (downstream of q_a_pairs, never pipeline-written) → zero re-ingest risk. NOT added as ID-64 deps.
• OQ-64-6 (ingest_source values) — empirical; prod = qa_import/manual/url_import/rss_feed/python_markdown. Confirm keep-set in {64.7}.
• OQ-64-7 (workspace ontology + workspaces retention) — resolve in {64.7} w/ ID-63 cross-ref (now decoupled from OQ-64-1).
• OQ-64-8 (re-ingest target) — FRESH Supabase preview branch (ephemeral), wired into staging Coolify {66.21}. NOT the persistent staging branch.
• OQ-64-9 (content_history) — resolve in {64.7}.
• OQ-63-9 ({64.9}) — RATIFIED S289: primary_domain/primary_subtopic NOT-NULL + 'unclassified' sentinel.

New subtasks to add to ID-64 (write-path — not in the existing {64.2}-{64.9} set)

• {64.10} content_items write-path reconciliation — flow.py: content_text→content rename; wire content_type (CHECK-domain-guarded, fallback 'other'); title = suggested_title ?? filename_stem. No DDL. testStrategy: a live content_items row lands with non-null title/content and content_type inside the CHECK domain.
• {64.11} source_documents write-path reconciliation — flow.py: content_fingerprint→content_hash rename; derive+write filename/mime_type/file_size; + ONE migration relaxing original_filename NOT-NULL. testStrategy: a live source_documents row lands with every NOT-NULL satisfied; original_filename is nullable.

---

Per-table detail

1. source_documents — sd_target

ColumnDef: flow.py:855-869. declare_row: flow.py:1381-1392. Pipeline writes: id, storage_path, content_fingerprint, op_id, extraction_method, pullmd_share_id.

• MISSING-IN-PROD: content_fingerprint (flow.py:859, 1385) — no such prod column → UndefinedColumnError. Plausible rename target content_hash (prod text, NOT NULL); written value is (await file.content_fingerprint()).hex().
• REQUIRED-NOT-WRITTEN: filename, original_filename, mime_type, file_size, (content_hash unless the rename above lands there). All NOT NULL, no default. storage_path IS written → satisfied; version, status have defaults.
• TYPE/NOTE: extraction_method, pullmd_share_id, op_id match prod. No GENERATED-ALWAYS columns. The 4 file-metadata columns are ID-64 design questions.

2. content_items — ci_target ← the failing table today

ColumnDef: flow.py:813-833. declare_row: flow.py:1400-1415. Pipeline writes: id, content_text, embedding, source_document_id, op_id, primary_domain, primary_subtopic.

• MISSING-IN-PROD: content_text (flow.py:816, 1403) — the live failure. Rename → content (prod text, NOT NULL). Pipeline-code fix.
• REQUIRED-NOT-WRITTEN: title (NOT NULL — design question), content_type (NOT NULL — value available at flow.py:1484, code fix). primary_domain / primary_subtopic ARE written and also carry prod default 'unclassified'.
• TYPE/NOTE: content_text_hash is GENERATED ALWAYS — correctly OMITTED (flow.py:809-811, 830). embedding vector(1024) matches prod. id, embedding, source_document_id, op_id, primary_domain, primary_subtopic line up.

3. content_chunks — cc_target

ColumnDef: flow.py:915-935. declare_row: flow.py:1456-1476. Pipeline writes: id, content_item_id, content, position, char_count, word_count, embedding, op_id.

• MISSING-IN-PROD: none. REQUIRED-NOT-WRITTEN: none (content_item_id, content, position all written; heading_path NOT NULL has default '{}', correctly omitted).
• TYPE/NOTE: position int4 vs prod smallint — verify cast. Not blocked.

4. q_a_extractions — qa_target

ColumnDef: flow.py:835-853. declare_row: flow.py:1487-1508. Pipeline writes: id, source_content_item_id, extractor_kind, extracted_question_text, extracted_answer_text, expected_response_kind, evaluation_criteria, evidence_requirements, scope_tags, extraction_metadata, op_id.

• MISSING-IN-PROD: none. REQUIRED-NOT-WRITTEN: none (extractor_kind + extracted_question_text both written).
• TYPE/NOTE: confirm no CHECK on extractor_kind="llm_extraction". Not blocked.

5. entity_mentions — em_target

ColumnDef: flow.py:879-892. declare_row: flow.py:1535-1550. Pipeline writes: id, content_item_id, entity_type, entity_name, canonical_name, confidence, context_snippet, metadata, op_id.

• MISSING-IN-PROD: none. REQUIRED-NOT-WRITTEN: none (content_item_id, entity_type, entity_name, canonical_name all written).
• TYPE/NOTE: confirm no CHECK on entity_type. Not blocked.

---

Operational state left at S295 close

• On-prem app kh-onprem-pipeline-git (ybiczck7f7e1xbdev3bk89cr) is STOPPED.
• COCOINDEX_IMAGE_TAG = sha-15d564e606b37f54413ef9d8112fd61ad89884f2 (the fully-extraction-fixed image).
• COCOINDEX_SOURCE_PATH env var DELETED (idle-safe): if Coolify git-auto-deploy restarts the app on a future push to main, it boots idle (compose ${COCOINDEX_SOURCE_PATH:-} → empty) instead of crash-looping the broken content_items write and burning Anthropic tokens. ID-64/S296 must re-set COCOINDEX_SOURCE_PATH=/cocoindex-state/corpus before the next ingest.
• Burn footgun: a push to main touching scripts/**/*.py fires onprem-deploy.yml AND Coolify git-auto-deploy, which restarts the app on the current COCOINDEX_IMAGE_TAG. Keep COCOINDEX_SOURCE_PATH unset until the write-path schema is reconciled.