CocoIndex write-model — constraints the pipeline MUST obey

CocoIndex write-model — constraints the pipeline MUST obey

⚠️ SUPERSEDED (S436, 2026-07-02): the target tables here centre on content_items, which id-131 eliminates — embeddings → record_embeddings (EMB-STORE), freshness/governance → record_lifecycle, retrieval grain → source_documents/q_a_pairs. The cocoindex 1.0.x write-model mechanics (autocommit per target, uuid5 identity, no cross-target FK) still hold; the table names do not. Read as pre-OKF history. See reference/deployment-architecture.md §3 + the id-131 spec.

Provenance: S297 (2026-06-02). Authored from the first live prod runs that ever reached the DB-write stage of the canonical pipeline. The live smoke peeled seven latent write-path bugs (A–G) that the mocked unit suite was structurally blind to. This doc is the canonical reference for how cocoindex 1.0.3 writes rows and what that forces on our schema + pipeline code. Read it before adding any target table, FK, or jsonb column to the pipeline.

Verified against installed cocoindex==1.0.3 (connectors/postgres/_target.py, resources/file.py) + asyncpg 0.31, with an independent read-only subagent audit.

---

1. The write model (the root fact)

The pipeline mounts N row-level targets (mount_table_target(..., managed_by=ManagedBy.USER)) and calls target.declare_row(row=...) per item. cocoindex’s USER-managed row-upsert path (_target.py _RowHandler._apply_actions → _schedule_upserts → _execute_upsert_chunk) then:

• writes each target on its OWN pooled connection in AUTOCOMMIT — there is no shared transaction (async with self._pool.acquire() as conn: await conn.execute(sql, *params), no conn.transaction());
• parallelises upsert chunks via asyncio.TaskGroup;
• orders cross-target applies in the Rust core (core.abi3.so) — there is no Python-visible ordering / dependency / single-transaction option on mount_table_target or anywhere else. Empirically the order is NOT parent-before-child (a content_items row committed before its source_documents parent).
• passes raw declare_row values to asyncpg with no per-column encoding (the ColumnDef.encoder hook exists but is unset for most columns; the upsert path does params.extend(action.value.get(col) ...)).
• the upsert conflict clause is ON CONFLICT (<primary key>) only — it does NOT cover any other UNIQUE constraint on the table.

Consequence: related rows that live in different targets are written in different, independent, possibly-concurrent transactions. The pipeline cannot make cross-target writes atomic or ordered.

---

2. Hard constraints (the rules — do not violate)

R1 — NO cross-target FK constraints on pipeline-written tables

A FK from one pipeline target to another cannot be satisfied (the referenced parent row may be uncommitted on its own connection when the child commits), and DEFERRABLE INITIALLY DEFERRED does not help (deferral only defers to commit within one transaction; these are separate autocommit transactions).

Integrity is instead guaranteed by construction: every child’s FK value is a deterministic uuid5(_KH_PIPELINE_DOC_NS, "<kind>:" + rel_path[...]) equal to the parent’s PK on every (idempotent UPSERT) run. The FK never established the relationship — only the uuid5 derivation does.

The 5 cross-target FKs dropped in S297 (migration 20260602073942): content_items_source_document_id_fkey, content_chunks_content_item_id_fkey, entity_mentions_content_item_id_fkey, q_a_extractions_source_content_item_id_fkey, form_template_fields_template_id_fkey. (The earlier deferrable migration 20260601211302 was a wrong-hypothesis dead end — superseded.)

FKs to PRE-EXISTING tables are fine (the parent already exists), e.g. form_templates.workspace_id → workspaces, form_templates.created_by → user_profiles.

Trade-off accepted: ON DELETE CASCADE / SET NULL across the dropped pairs no longer fire. Any wholesale corpus wipe MUST delete children explicitly (no auto-cascade), and app-side deletes of canonical rows no longer auto-clean dependents. The canonical layer is pipeline-written + re-ingested wholesale, so this is acceptable — but it is a real behavioural change.

R2 — jsonb columns need a connection codec, not a per-column encoder

asyncpg cannot encode a Python dict→jsonb without a registered codec (it calls as_pg_string_and_size on the raw value → DataError: expected str, got dict). We register one once on the KH-owned pool: kh_pipeline_lifespan → asyncpg.create_pool(..., init=_register_pg_codecs) → set_type_codec('jsonb', json.dumps/json.loads, schema='pg_catalog'). Any new jsonb column the pipeline writes is automatically handled — do NOT add per-call-site json.dumps.

R3 — natural-key UNIQUE constraints must be deduped in the pipeline

Because ON CONFLICT targets the PK only, a second UNIQUE constraint will raise UniqueViolationError if the pipeline declares two rows that collide on it. Dedup in Python before declare_row, and seed the PK on the natural key so re-ingest UPSERTs. Example: entity_mentions UNIQUE (canonical_name, entity_type, content_item_id) → dedup per (canonical, type), PK em:{rel_path}:{canonical}:{entity_type} (S297 BUG-F).

R4 — the real cocoindex FileLike API (the fakes lied)

• file.size is an async def size(self) -> int — call await file.size(), NOT file.size (which is the bound method). (resources/file.py.)
• file.content_fingerprint() is async → (await file.content_fingerprint()).hex().
• file.file_path.path is ABSOLUTE in production (/cocoindex-state/corpus/test/x.md) despite the 1.0.3 “relative to source base dir” docstring. Normalise to source-relative via flow_source_path before using it as storage_path / the uuid5 seed (S297 BUG-A). mount_each does NOT pass the walk key to fn.

R5 — the workspace manifest + the canonical/workspace boundary

• The manifest (.kh-workspace-map.json) is loaded once at flow start and is a mandatory flow-start requirement today (the flow aborts without it). It is a Path-B (form-write) concern only.
• It lives inside COCOINDEX_SOURCE_PATH, so walk_dir also enumerates it — it MUST be skipped in ingest_file before conversion (a .json would raise Unsupported file extension; S297 BUG-B).
• Content (Path-A) is workspace-AGNOSTIC (ID-69 BI-1): content_items has no workspace_id; the content_item_workspaces M2M junction is ID-69 scope and is NOT populated by the pipeline. Only Path-B form-write resolves a workspace_id. A workspace-resolution failure must never affect the content writes.

---

3. Faithful-test discipline (why the smoke is the oracle)

All seven S297 bugs (A–G) passed the mocked unit suite because the fakes (_FakeTarget appends to a list; _FakeFile used relative paths + a @property size; no asyncpg, no FK, no UNIQUE, no jsonb codec). The mocked suite can prove the declare_row shape; it cannot prove anything the DB enforces.

Faithful unit tests MUST model the prod reality: absolute file.file_path.path, async file.size(), the real resolve_workspace, duplicate entity mentions, real convert_binary_to_markdown on a .json. But the following are DB-level and only the live smoke can prove them: cross-target FK behaviour, UNIQUE constraints, the per-target transaction model, jsonb encoding. A real prod (or preview-branch) smoke is a required gate, not a formality — it has caught a fresh latent layer on every run.

---

4. The S297 bug ledger (A–G)

Bug	Layer	Symptom	Fix
A	walk/resolve	absolute rel_path ≠ relative manifest prefix → resolve fail + dirty uuid5 seeds	thread flow_source_path, normalise rel_path source-relative (_to_source_relative)
B	walk	.kh-workspace-map.json walked as content → Unsupported .json	skip the manifest filename at the top of ingest_file
C	(reverted)	hypothesised intra-flow FK flush order	deferrable FKs — WRONG model; superseded by E
D	DB write	dict→jsonb DataError: expected str, got dict	pool jsonb codec (create_pool(init=))
E	DB write	cross-target FK violation; DEFERRABLE useless	DROP the 5 cross-target FKs; integrity via uuid5
F	DB write	entity_mentions UNIQUE (canonical,type,content_item) dup	dedup per (canonical,type) + natural-key PK
G	DB write	file.size bound-method passed as int4	await file.size() ×3; fakes → async size

Result: content_items landed on prod for the first time after D/E (S297). F/G unblock source_documents + entity_mentions; the re-smoke after F/G is the content-half close gate.

---

5. Open risks / future watch

• First Path-B FORM smoke is untested. The 2-file content smoke exercises no form rows. When a form file ingests, watch: form_templates.form_type → form_types.key (a value/CHECK risk if the extracted form_type is not one of the valid keys — the fail path writes form_type=None, which is safe); and the now-dropped form_template_fields → form_templates FK (integrity via the ft:/ftf: uuid5 seeds).
• Mandatory-manifest relax is an ID-69-PRODUCT-sanctioned TECH design choice (make a missing manifest non-fatal → content-only ingest, skip Path-B). Deferred S297 (Liam chose keep-and-seed for the smoke). Track for the content-only ingest path.
• Re-ingest target is a fresh Supabase preview branch (OQ-64-8), but the on-prem app is wired to PROD today; {66.21} staging Coolify is the non-prod path (not yet wired). The smoke writes to prod (test data, wiped at cutover).
• The wipe procedure must delete children explicitly now that the cascade FKs are gone (R1 trade-off).

---

6. Operational notes (live-ops)

• On-prem app kh-onprem-pipeline-git (Coolify uuid ybiczck7f7e1xbdev3bk89cr), image ghcr.io/ai-solution-hub/kh-cocoindex-pipeline:sha-<commit>. IMAGE_TAG env uuid l92p743mjory7qaopxqjx58e; SOURCE_PATH env uuid rqjlptckuuqvt4q8nz0wd98t. SSH root@77.68.122.71 key ~/.ssh/kh_ionos_ed25519.
• Burn-safe deploy ({66.11}): the sole deploy trigger is the deploy-cocoindex job in .github/workflows/onprem-deploy.yml — it builds, bumps COCOINDEX_IMAGE_TAG to the freshly-built sha-<commit>, then deploys that pinned tag (never a stale tag). Coolify git-auto-deploy must stay disabled so a push cannot redeploy on the stale current tag before the build finishes. The full burn-safe procedure + operator activation checklist lives in docs/runbooks/onprem-b1-deploy.md “{66.11}”.
• Boot is burn-safe regardless of COCOINDEX_SOURCE_PATH (ID-83 / bl-221): boot is lifespan-only — server.py enters the cocoindex environment’s lifespan via coco.start_blocking() and runs no app_main, so walk_dir never runs at boot. A deploy/restart can therefore never auto-walk the corpus or burn Anthropic, even with COCOINDEX_SOURCE_PATH persistently set. The corpus walk fires only on an explicit bearer-gated POST /walk (server.py::_walk_handler, a one-shot update_blocking(live=False) pass). This retires the old SOURCE_PATH-blanking burn-valve: SOURCE_PATH is now persistently set to /cocoindex-state/corpus on both Coolify envs (S312) and must not be emptied — “boot never walks” is the architectural burn guard, not a manual operator step.
• Smoke sequence: push → wait for the ghcr image build → bump IMAGE_TAG to the new sha → confirm SOURCE_PATH=/cocoindex-state/corpus (persistently set per S312; deploy is burn-safe regardless) → stop app → clear LMDB (/var/lib/docker/volumes/ybiczck7f7e1xbdev3bk89cr_cocoindex-state/_data/lmdb/mdb, the engine memo, so processing is fresh) → deploy → watch docker logs → issue a bearer-gated POST /walk (boot no longer walks — ID-83; ingestion fires only on this explicit trigger) → verify rows (source_documents going 0→N is the cleanest oracle: legacy app-side rows never wrote it).
• SOURCE_PATH duplication footgun: Coolify can create a duplicate COCOINDEX_SOURCE_PATH env on deploy — keep it to a single key.
• Migrations auto-apply to PROD on main push via the Supabase GitHub integration (~1 min, async — not a GH-Actions step). Verify with a pg_constraint introspection before the smoke.
• Coolify env_vars reveal=true dumps ALL secrets — never use it; the update responses already echo the value you just set.