02 — Data flow

02 — Data flow

⚠️ SUPERSEDED (S436, 2026-07-02): this sub-doc describes the pre-OKF world — content_items as the embeddings/freshness/UPSERT grain, and the Cloud Run sidecar. Under id-131 content_items is eliminated: embeddings → record_embeddings (EMB-STORE), freshness/governance → record_lifecycle, and the retrieval grain re-homes to source_documents/q_a_pairs. Not rewritten; read as history. See reference/deployment-architecture.md §3 + the id-131 spec.

Last verified: 15/05/2026 (S240 Wave 2 split — WP4 architecture sub-doc) Scope: Source-binding model, cocoindex flow stages, Cloud Run sidecar topology, audit_log + op_id propagation, RLS pattern forward-reference, ingest write paths, edit re-classification trigger policy, freshness substrate separation, and anti-patterns + retired patterns. Status: [CURRENT-CANONICAL] Layer: 2 — references 01-vision.md for the AI-consumer-first framing; references 04-workspace-types.md for all schema details (source_documents, workspaces, application_types, satellites); downstream 05-qa-flow.md and 09-diagrams.md anchor flow details here. Companion sub-docs: 01-vision.md (ratified S238 pilot), 03-tech-stack.md, 04-workspace-types.md, 05-qa-flow.md, 06-mcp-tooling.md (gated on Theme F), 07-collapse-list.md, 08-new-features.md, 09-diagrams.md.

---

1. Mission

This sub-doc is the canonical record of how content moves through Knowledge Hub — from an external source folder to a structured, embedded, RLS-protected corpus row, and back again when an editor makes a change that warrants re-classification. It covers the source-binding contract (what makes a source canonical), the cocoindex pipeline topology (what happens to bytes once they enter the ingest path), the Cloud Run sidecar architecture that makes heavy parsing feasible within Vercel’s function-bundle limits, the op_id and audit_log propagation pattern that gives the platform a coherent observability story, and the write-path discipline that prevents silent failures from leaking bad data into the corpus.

Two scope boundaries apply throughout:

1. Schema detail lives in 04-workspace-types.md. This sub-doc describes data flow at the pipeline level — what tables are written, in what order, via what adapters. Column-level shapes, FK definitions, and the combined-PR migration scope are 04-workspace-types.md territory; this sub-doc references them by section number, never duplicates.
2. RLS policy SQL lives in docs/specs/rls-pattern/{PRODUCT.md,TECH.md}. Per Liam’s S239 Wave 2 ratification, the RLS pattern is a separate spec, not inline here. §6 below is a short forward-reference to that spec; SQL and role-grants narrative live in the destination doc.

The ten content sections that follow mirror the 10-section structure from the readiness audit: source-binding (§2) → cocoindex flow stages (§3) → Cloud Run sidecar (§4) → audit_log + op_id (§5) → RLS-PATTERN forward-ref (§6) → ingest write paths (§7) → edit re-classification trigger policy (§8) → freshness substrates (§9) → anti-patterns (§10) → cross-doc references (§11). Source-doc supersession carries its own section at §12 per the pilot pattern.

Application-type coupling in this sub-doc is mention-only: the data-flow substrate (cocoindex → Cloud Run → Postgres) is application-type-agnostic at the ingest level. Per-application-type differences (e.g. the procurement flow capturing form_type in the extraction step) emerge from the edit_intent and ExtractByLlm output shape, not from separate flow branches. Per-application-type schema details live in 04-workspace-types.md; the ingest flow described here is the single substrate all application types share.

---

2. Source-binding model

2.1 External-folder canonical

The source of truth for every content item is the client’s own file system — LocalFS, SharePoint, Notion, or Google Drive. Knowledge Hub does not own the content storage layer; it maintains a materialised index plus curated derivations. Per 01-vision.md §2.1, “The client’s source files are the canonical content store. KH is a materialised index plus curated derivations — not the storage layer.”

Cocoindex binds to these sources using localfs.walk_dir(live=True) (LocalFS + Cloud Run mounted share) or the equivalent connector for cloud sources. Per phase-b-prerequisite-2-cocoindex-deep-dive.md §1.1, localfs.walk_dir(live=True) is Layer-2 admin-metadata binding: it yields a stream of file-change events (create, modify, delete) which cocoindex translates into pipeline runs.

2.2 Cocoindex source-key + content-hash idempotency

Each file entering the cocoindex pipeline carries a stable source-key — typically <connector>://<path-or-url> — and a content-hash derived from the file’s bytes. The content-hash is the idempotency guard: if the hash matches the stored hash for that source-key, the pipeline short-circuits via @coco.fn(memo=True) per COCO.9 (S9 spike RESOLVED-PARTIAL-S235; layered fn-shape required — inner-tier functions consume content_text: str, not FileLike). The source-key maps to source_documents.storage_path in KH’s schema; the content-hash is stored in source_documents as the ingest state signal.

Per Q-OQR1-09, source_documents.workspace_id is NULLABLE. An admin-shared binary (e.g. a Phew bid library DOCX used across multiple procurement workspaces) binds without a workspace assignment. RLS for the nullable case routes through the content-item-to-workspace edge: a workspace may read a source_document if any content_items row referencing it is assigned to that workspace via content_item_workspaces. Schema detail and RLS adjustment narrative: 04-workspace-types.md §9.1. Reserved workspace seats interact with this pattern per docs/specs/reserved-workspace-seats/{PRODUCT.md,TECH.md} — a source document serving a sales_proposal_workspaces row inherits the same nullable-workspace-id RLS path at v1 apply time.

2.3 P7 file-upload route — RESOLVED per N5

app/api/upload/route.ts is the HITL manual-upload entry point. Per N5 RESOLVED (00-synthesis-v2.md §5.1), the upload route is retained under Option α (slim-and-keep), fixing the silent-fail regression at lines 444-447. The fix binds under the cocoindex source-binding architecture: after an upload, the route writes a source_documents row and cocoindex’s source binding observes the new file on next scan (or on a triggered run). The upload route does not duplicate ingest logic; it is the HITL surface that creates the source-document record and places the file where cocoindex can observe it.

This closes the [CONDITIONAL-RETIRE] flag on P7 from 07-collapse-list.md §12.2 — the condition (source-binding architecture resolution) is satisfied by Option α per the above. P7 binds here at §1; 07-collapse-list.md §12.2 defers to this section for the resolution.

RATIFIED per B2 RESOLVED-α (phase-b-prerequisite-2-cocoindex-deep-dive.md §1.2) + N5 RESOLVED-S236 (00-synthesis-v2.md §5.1).

---

3. Cocoindex flow stages

3.1 Stage overview

The cocoindex pipeline runs in the Cloud Run sidecar (§4 below). Stages, in order:

Stage	Cocoindex primitive	KH purpose	Ratification
Source walk	localfs.walk_dir(live=True)	Emit file-change events	COCO.1; phase-b-prerequisite-2-cocoindex-deep-dive.md §1.1
Binary conversion	files_transform + per-MIME @coco.fn wrapper	Produce content_text: str from PDF / DOCX / XLSX via Docling; from HTML via pullmd	COCO.2–COCO.6; 00-synthesis-v2.md §3.1–§3.3
LLM extraction	ExtractByLlm with typed Python output_type	Classification, Q&A extraction, entity extraction	COCO.1; phase-b-prerequisite-2-cocoindex-deep-dive.md §4 Recommendation 1
Embedding	LiteLLMEmbedder("openai/text-embedding-3-large")	vector(1024) embeddings per content_items row	Already in plan per S1 spike; CLAUDE.md “Supabase & Schema”
Entity resolution	entity_resolution	Named-entity dedup (clients / competitors / employees)	COCO.1 + phase-b-prerequisite-2-cocoindex-deep-dive.md §1.1 (selective adoption)
Postgres UPSERT	postgres.mount_table_target(managed_by="user")	Write to content_items, source_documents, q_a_extractions targets	COCO.1; phase-b-prerequisite-2-cocoindex-deep-dive.md §1.1

3.2 Adapter affordances

• @coco.fn decorator with memo=True — memoisation per component-path, not global content-hash dedup. Memo scoping means each pipeline stage short-circuits independently when its input hash matches. Inner-tier functions must accept content_text: str (not FileLike) per S9 spike layered-fn-shape requirement (COCO.9 + COCO.10 CLOSED-CONDITIONAL).
• Retry / back-off / DLQ — cocoindex provides these natively. KH does not build a pipeline_failures table (COCO.7 DO-NOT-BUILD per 00-synthesis-v2.md §3.10).
• Crash recovery — inherited free from cocoindex per the persistent-pipeline substrate (phase-b-prerequisite-2-cocoindex-deep-dive.md §1.3).
• Per-flow op_id — cocoindex emits a stable op_id per pipeline run, propagated into Postgres targets. The hybrid op_id pattern (§5 below) bridges cocoindex’s per-flow op_id to KH’s trigger-driven audit_log cohesion.

3.3 Roadmap LIKELY-ABSORB per-item verification

Three roadmap items from 07-collapse-list.md §12.3 carry [CONDITIONAL-RETIRE] flags gated on this sub-doc’s source-binding architecture resolution:

• Roadmap §3 classification telemetry — direction: absorbed by cocoindex ExtractByLlm flow + per-stage metrics (COCO.8 + 00-synthesis-v2.md §3.11). The custom classification-logging pattern retires in favour of cocoindex’s native memoisation + per-stage metric capture. [RATIFIED-RETIRE] per CX.21 + COCO.8.
• Roadmap §3.7 AI telemetry pipeline — direction: absorbed by cocoindex’s pipeline catalog + version tracking (phase-b-prerequisite-2-cocoindex-deep-dive.md §2). Q4.12 cost-tracking dashboards retire per 00-synthesis-v2.md §3.11. [RATIFIED-RETIRE] per COCO.8.
• Roadmap §11 context graph phase 5 — direction: cocoindex meeting_notes_graph_neo4j-style person-dedup pattern adopted for named-entity dedup per COCO.1; Knowledge Map graph substrate deferred to S7 spike per 00-synthesis-v2.md §5.2. This item is [CONDITIONAL-RETIRE] pending the S7 Knowledge Map substrate spike; it is not fully resolved here and carries that carry-forward flag.

Gap flag (item 3): The Knowledge Map graph substrate decision is STILL-OPEN per 00-synthesis-v2.md §5.2 row 4. Checked 00-synthesis-v2.md §3 + §5, 0.9-decision-graph.md §11.4, and INV-architecture-split-readiness.md §5 — no ratification on the graph substrate target (Neo4j vs PG entity_relationships vs mempalace) found. Category: investigation needed. Suggested prerequisite work: S7 spike per the canonical register row. 08-new-features.md carries Knowledge Map as a mention-only cocoindex-substrate reference until the spike resolves.

---

4. Binary path + Cloud Run sidecar

4.1 Topology

Docling’s 1.8 GB on-disk footprint (phase-b-prerequisite-2d-docling-bakeoff.md §6) exceeds Vercel’s 250 MB function-bundle limit. The Cloud Run sidecar is therefore mandatory for any deployment that includes Docling. KH already operates Cloud Run for the Python pipeline (kh-prod-494815 main branch + kh-staging-494815 staging) — extension to host cocoindex + Docling is operational only, not a new infrastructure decision (COCO.6 RESOLVED-S234 per 00-synthesis-v2.md §3.1).

Sidecar components:

Component	Format coverage	Footprint driver
Docling (primary binary parser)	PDF, DOCX, XLSX	1.8 GB model download (layout-heron + docling-models); MIT license
pullmd (HTML adapter — retained)	HTML, URL-based sources	~3.7 GB Playwright sidecar when enabled; AGPL v3 (network-service clause does not propagate to KH code per phase-b-prerequisite-2d-docling-bakeoff.md §3.3)
cocoindex engine (Rust)	Orchestration	LMDB ops-DB; requires dangerouslyDisableSandbox: true for dev per CLAUDE.md gotcha

4.2 Per-MIME viewer composition

id-131 note (S436): the per-record __qa__ markdown sidecar referenced here is retired — under the OKF model a Q&A pair is a record (q_a_pairs), never a markdown concept file. See reference/deployment-architecture.md §3 + the id-131 spec.

Per 03-tech-stack.md §9.5, for binary content the viewer composition is:

• Docling produces GFM markdown from PDF / DOCX / XLSX via files_transform + the per-MIME @coco.fn wrapper.
• Tiptap ContentEditor renders the markdown as the primary read surface. The markdown sidecar v1 pattern (COCO.10 CLOSED-CONDITIONAL; full detail in 05-qa-flow.md §4) drives this composition.
• pullmd produces markdown from HTML sources via the Playwright sidecar; output enters the same Tiptap ContentEditor render path.
• Binary artefacts (the original PDF / DOCX) are read-only via a separate viewer component; they are not the edit-path entry point.

4.3 pullmd retention rationale

pullmd is not consolidated into Docling for HTML per COCO.5 and 00-synthesis-v2.md §3.3. The five replaceability gaps that make consolidation impossible: (1) JS-rendered SPAs via Playwright sidecar coverage; (2) Cloudflare-protected URL short-circuit; (3) Reddit comment-tree first-class extractor; (4) share-id stable identity contract (pullmd://${share_id} as source_documents.storage_path); (5) URL-input handling without a separate binary blob. Architecture reference: 03-tech-stack.md §7.2.

---

5. audit_log + op_id propagation pattern

5.1 The hybrid pattern (N7 ratification)

The op_id propagation pattern is ratified as a hybrid per N7 RESOLVED-S236 (00-synthesis-v2.md §5.1; 0.9-decision-graph.md §11.4.1):

• Cocoindex per-flow op_id — cocoindex emits a stable op_id per pipeline run and propagates it into the Postgres target columns it writes. This gives the pipeline side a correlation handle: every content_items row, every q_a_extractions row, every source_documents state update written by a given cocoindex run carries that run’s op_id. Pipeline-side correlation (“which rows did run X write?”) is answerable directly from the target tables.
• Trigger-driven audit_log — KH’s audit_log table captures platform-wide audit events via Postgres AFTER INSERT / UPDATE / DELETE triggers on governed tables. The trigger fires independently of whether the write came from cocoindex, the upload route, a governance cron, or a user edit. The audit_log row carries the table name, the row id, the operation type, and the invoking role — providing audit cohesion across sources that cocoindex’s per-flow op_id alone cannot supply (e.g. a user editing a content_items row directly via the KH web UI has no cocoindex op_id).

The hybrid means both signals are present for cocoindex-originated writes: the row-level op_id column gives pipeline correlation; the audit_log entry gives governance cohesion. For non-cocoindex writes (direct UI edits, governance cron updates), only the audit_log entry is present — which is the correct and intended state.

RATIFIED per N7 RESOLVED-S236 (00-synthesis-v2.md §5.1). Citations: 0.9-decision-graph.md §11.4.1 (N7 hybrid op_id pattern); phase-b-prerequisite-2-cocoindex-deep-dive.md §1.3 (confirms “OQ-Q24-C / N7 (op_id propagation pattern): trigger-driven vs app-stamped is a Postgres-side implementation choice. Cocoindex doesn’t pick. Working default per Finding 01: hybrid.”).

5.2 Why trigger-driven, not app-stamped

The trigger-driven approach is preferred over app-stamped (writing to audit_log explicitly from application code on every write) for two reasons:

1. Coverage completeness. A trigger fires on every governed-table write regardless of call site — it cannot be accidentally omitted by a new write path. App-stamped audit requires every caller to know to insert the audit row; any new ingest route, cron job, or admin-panel bulk update that forgets the audit_log.insert() call creates a silent audit gap. With a trigger, no such gap is possible.
2. Cocoindex write-path independence. Cocoindex’s postgres.mount_table_target() UPSERT is an external write that the KH application code does not intermediate. App-stamping would require KH to intercept every cocoindex UPSERT and insert an audit row — coupling the audit system to the cocoindex target-table registration. The trigger approach decouples audit from the writer identity.

5.3 audit_log and op_id columns

The op_id column is added to content_items (and potentially other cocoindex target tables) as part of the combined-PR scope per 0.9-decision-graph.md §11.4.1. RATIFIED-S236, column-level schema STILL-OPEN pending the Q-OQR1-16 combined-PR migration (00-synthesis-v2.md §5.1 N7 disposition). The audit_log table schema is governed separately — it is a platform-level governance table, not part of the Q-OQR1-16 combined PR scope. Column-level detail for audit_log is not in scope for this sub-doc; it is referenced from docs/plans/phase-0-investigation/supabase-db-action-items.md Item 2 context and from the rls_auto_enable() trigger function which uses RAISE LOG (not audit_log insert) at v1 per RLS-PATTERN P-5 [DEFERRED-v1.1].

5.4 CocoInsight — a separate surface, not a replacement for audit_log

CocoInsight is the developer-facing pipeline transformation-lineage observability tool. Per phase-b-prerequisite-2-cocoindex-deep-dive.md §3.3, CocoInsight is not a replacement for audit_log:

• CocoInsight audience: engineering — pipeline lineage, transformation step inputs/outputs, cocoindex-internal metrics.
• audit_log audience: compliance + governance — who changed what row, when, and from which role.

Different audiences, different retention requirements, different query patterns. Both are retained. CocoInsight on-prem deployment posture is STILL-OPEN per 00-synthesis-v2.md §5.2 row 6 (only relevant if KH self-hosts cocoindex infrastructure). The TS-facing API for the cocoindex per-flow-run ledger is DEFERRED-v1.1 per RATIFIED-S243 (Item 11) [CORRECTION-S267: was framed STILL-OPEN at draft; superseded — v1 ships only the pipeline_runs rollup; per-stage detail surface deferred. See PLAN.md §7 item 9 + docs/specs/id-36-cocoindex-ledger-api/TECH.md (v1.1 substrate).] — currently pipeline_runs insulates the UI from this gap (§6.2).

---

6. RLS pattern — forward-reference to RLS-PATTERN spec pair

Per Liam’s S239 Wave 2 ratification, the RLS pattern for v1 lives in a dedicated spec, not inline in this sub-doc. Forward-reference:

• docs/specs/rls-pattern/PRODUCT.md — user-perspective invariants: P-1 (RLS auto-enabled on new public tables), P-2 (per-role grants required), P-3 (fail-loud Data API on missing grants), P-4 (per-function anon REVOKE-EXECUTE), P-5 (observability hook [DEFERRED-v1.1] per Liam S240 ratification — option (c) structured-logging-only).
• docs/specs/rls-pattern/TECH.md — implementation references: T-1 (rls_auto_enable() event trigger pattern on ddl_command_end), T-2 (grant_standard_public_table_access(regclass) helper), T-3 (combined migration 20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql — APPLY GATED ON LIAM REVIEW), T-4 (per-function anon REVOKE-EXECUTE), T-5 (observability — [DEFERRED-v1.1]).

The auto-RLS event trigger fires on every CREATE TABLE in the public schema — including all tables created by the combined-PR migration (the new application_types table, the procurement_workspaces rename target, the 5 reserved satellite seats per docs/specs/reserved-workspace-seats/{PRODUCT.md,TECH.md}). RLS-first, then grants, in the combined migration per 00-synthesis-v2.md §3.16+§3.17.

P-5 observability: the rls_auto_enable() function emits RAISE LOG on each successful RLS enable (migration draft lines 62, 65, 68-69). No audit_log row is written at v1. Structured-log shipping to the KH observability stack via Cloud Run sidecar log ingest satisfies the v1 observability requirement. Options (a) audit_log insert and (b) fan-out helper remain v1.1 candidates per docs/specs/rls-pattern/PRODUCT.md §P-5.

---

7. Ingest write paths

7.1 Upload-route HITL fix — N5 RESOLVED

app/api/upload/route.ts contains a silent-fail regression at lines 444-447 per N5 (00-synthesis-v2.md §5.1). The fix proceeds under Option α (slim-and-keep): the upload route is retained as the HITL manual-upload surface; silent-fail prevention applies via sb() (fail-fast) or tryQuery() (Result-returning) from @/lib/supabase/safe. The broader silent-failure prevention pattern per CLAUDE.md “Silent failures in Supabase calls” gotcha applies here: warningsEnvelope() for composite responses; logBestEffortWarn() for best-effort swallows. Full spec: docs/specs/silent-failure-prevention-spec.md.

7.2 pipeline_runs retained as KH-side rollup — N6 RESOLVED

pipeline_runs is retained as the KH-side pipeline rollup per N6 RESOLVED (00-synthesis-v2.md §5.1 + phase-b-prerequisite-2-cocoindex-deep-dive.md §1.2). Cocoindex does not expose a TS-facing API for its per-flow-run ledger at the page level (DEFERRED-v1.1 per RATIFIED-S243 Item 11 — was STILL-OPEN at draft; v1 ships only the pipeline_runs rollup, per-stage surface deferred); pipeline_runs insulates the KH UI from cocoindex schema changes and provides the rollup surface for run history. Inserts via recordPipelineRun() from @/lib/pipeline/record-run per CLAUDE.md “Cron pipeline_runs inserts” gotcha.

7.3 pipeline_failures DO-NOT-BUILD — COCO.7

pipeline_failures is not built. Cocoindex’s retry / back-off / DLQ subsumes the planned table per COCO.7 RESOLVED-S234 (00-synthesis-v2.md §3.10). recordPipelineRun() handles the KH-side rollup for run tracking; individual failure details live in cocoindex’s internal ops-DB and Cloud Run logs, not in a KH-owned failure table.

7.4 Silent-fail prevention — platform standard

Every Supabase write in the ingest path routes through the silent-failure prevention pattern:

• sb() — fail-fast, throws on error; use for writes where silent failure would corrupt corpus state.
• tryQuery() — Result-returning ({ data, error }); use where the caller can handle partial failure gracefully.
• warningsEnvelope() — composite responses with per-item warnings; use for bulk ingest writes where some rows may fail without invalidating the batch.
• logBestEffortWarn() — best-effort swallows for non-critical side-effects (e.g. freshness-score update on ingest that doesn’t block the primary write).

ESLint rules local/no-unchecked-supabase-error and local/no-silent-promise-catch enforce. Full spec: docs/specs/silent-failure-prevention-spec.md.

---

8. Edit re-classification trigger policy (Theme C)

8.1 The edit_intent Layer-1 CV

edit_intent is a Layer-1 controlled vocabulary ratified at S234 per ONT.14 (0.9-decision-graph.md §11.1). It is the signal that governs whether an edit to a content_items row triggers cocoindex re-classification or short-circuits.

8.2 Intent-to-action mapping

edit_intent value	Cocoindex re-classification	Rationale
cosmetic	Skips — no re-run	Typo fix, formatting change; content-hash changes but classification inference is stable. Cocoindex @coco.fn(memo=True) short-circuits at the classification stage for cosmetic edits after the content-hash check.
data	Triggers re-run	Factual update changes the answer to an extractable question; LLM re-inference required.
structural	Triggers re-run	Heading hierarchy, table restructure, section reorder; layout-aware extraction (Docling path) may yield different output.

This is RESOLVED-DIRECTIONAL per Theme C (phase-b-prerequisite-2-cocoindex-deep-dive.md §1.2 “Re-extract on edit-back (OQ-CX33-A): Cocoindex re-extracts naturally via source-binding + content-hash idempotency. Gates only on KH-side: which edit_intent values trigger reprocessing”). The trigger is the edit_intent value written by the editor at save time.

8.3 Concurrent-edit handling

Concurrent-edit handling defers to operational design. The Tiptap + Yjs layer handles concurrent CRDT at the editor level; post-CRDT-merge last-write-wins at the file layer (03-tech-stack.md §9.2). The cocoindex source binding observes the final file state on next scan — it does not observe mid-CRDT-merge partial states. Per 0.9-edit-flow-investigation.md §6.1 (UC1 Candidate A ratified), the KH server writes the file after the CRDT merge completes; cocoindex observes the merged result.

The question of which edit_intent to assign to a merged concurrent edit (where two users independently applied data and cosmetic intents) is an operational design decision. Gap flag: No ratification on concurrent-edit edit_intent arbitration found in checked sources (00-synthesis-v2.md §5.1, 0.9-edit-flow-investigation.md §6, 0.9-decision-graph.md §11). Category: tech spec needed. Suggested prerequisite work: docs/specs/id-59-concurrent-edit-intent-arbitration/TECH.md. The v1 safe default is to treat any concurrent edit that includes a data or structural intent as a data intent (conservative re-run triggers rather than silent skip). This default must be codified at the point where the KH server resolves the CRDT merge and writes the final file — the edit_intent assigned in that write determines whether cocoindex re-classifies on next scan.

---

9. Cocoindex freshness vs governance freshness

9.1 Two separate substrates — not a merge

The cocoindex pipeline tracks its own freshness concept: ingest-latency — how recently the pipeline last processed a given source file. This is an operational metric: “has this file been processed in the last N hours?” It lives in cocoindex’s internal LMDB ops-DB.

Knowledge Hub’s governance freshness lives in content_items.freshness and carries four states: fresh / aging / stale / expired. These states reflect content quality and lifecycle, not ingest timing — a record can be freshly ingested and still be stale if its underlying facts are out of date. Governance freshness is computed by KH’s cron-driven freshness-scoring logic, not by cocoindex.

Per Theme D resolution (phase-b-prerequisite-2-cocoindex-deep-dive.md §1.2): “Separate substrates kept: cocoindex’s freshness = ingest-latency metric (operational); KH’s content_items.freshness enum = governance freshness (fresh/aging/stale/expired). Both retained, no merge.”

RESOLVED per COCO.1 + 00-synthesis-v2.md §3.11.

9.2 Why the substrates must not merge

Merging would create a false equivalence: a file ingested yesterday by cocoindex would appear fresh even if the underlying facts it describes are two years old. Governance freshness is a corpus-quality signal driven by content age, expiry dates, and owner-review cadence — not by pipeline timing. The Wikipedia Principle (“one record, many views” per 01-vision.md §1.2) applies here: the governance freshness state is the canonical view of record quality; ingest-latency is an operational-health metric.

9.3 Implications for the data flow

Cocoindex UPSERT writes to content_items with the content and embedding; it does not update content_items.freshness. The freshness field is updated by the governance cron (or by an explicit governance review action). This separation ensures the ingest pipeline cannot accidentally reset a record’s governance-freshness state by re-ingesting it.

The write-path separation has two concrete consequences:

1. Re-ingesting a stale record does not make it fresh. If an intelligence workspace document is re-indexed because its markdown sidecar was regenerated (e.g. a Docling model update caused a content-hash change), the content_items.freshness value does not change. A record that was stale before re-ingestion remains stale until the governance cron or a human review action updates it.
2. Freshness scoring sees the latest ingest output. The governance cron reads content_items.content (the latest cocoindex-produced content_text) when computing freshness scores — so it always evaluates current content, not cached content from a prior ingest cycle. This is the correct dependency direction: freshness is downstream of ingest, not upstream.

These constraints are not separately ratified — they follow directly from the Theme D separation decision. They are recorded here because they are the most common source of “why is this record still stale after re-indexing?” confusion in operations.

---

10. Anti-patterns + retired patterns

10.1 Pattern A/B Q&A parser retire (B1 RESOLVED)

The Pattern A / Pattern B Q&A parsers (extractQaPairs and related functions) were the planned recurring extraction primitives before cocoindex adoption. Post-S234, these retire as one-shot Phew-migration helpers — they run once to populate q_a_pairs from existing bid data, then are removed. The recurring Q&A extraction path is cocoindex ExtractByLlm with typed Python output_type per COCO.1 + B1 RESOLVED.

Pattern A/B detail lives in 05-qa-flow.md §10 (the Pattern B retire section). GitNexus confirmed zero callers for extractQaPairs outside the Phew-migration script per Q3.5 RESOLVED-MIGRATION-HELPER-ONLY. This sub-doc notes the retire direction; 05-qa-flow.md §10 carries the full retire narrative.

[RATIFIED-RETIRE] — retire post-Phew-migration per B1 RESOLVED-S234 + Q3.5 RESOLVED-MIGRATION-HELPER-ONLY (00-synthesis-v2.md §2.1 B1).

10.2 Diff-UI — [RETAINED-S243] for v1 (markdown-first, re-pointed to sidecar)

The diff-UI surface comprises:

• app/api/source-documents/[id]/diff/* (diff API routes)
• app/documents/[id]/diff/page.tsx (diff page route)
• Diff-viewer components under components/source-documents/
• lib/source-documents/document-diff.ts
• lib/source-documents/source-document-impact.ts

Per RATIFIED-S243 (Item 5), these are RETAINED for v1 — superseding the earlier S240 [DEFERRED-v1.1] framing. The diff-UI surface is not retired and not deferred; it is converted to markdown-first and re-pointed from the source_document_diffs substrate to markdown sidecar revision comparison. Implementation lands as the Diff-UI sidecar re-point sub-task in PLAN.md T9.

The data-flow context for this retention: the original diff-UI surface worked against the pre-Docling source_document_diffs shape. Post-Docling, source documents produce GFM markdown sidecars via cocoindex; the meaningful diff surface compares markdown sidecar versions, not binary diffs. Re-pointing the diff surface onto the markdown sidecar is the correct architecture and is the chosen v1 direction. The retention is therefore data-flow-motivated, not arbitrary.

Ratification source: RATIFIED-S243 (Item 5) — diff-UI retained, markdown-first; see PLAN.md §7 item 2 + T9. Binding location in the architecture: 07-collapse-list.md §12.1.

This closes the [ACTUALLY-STILL-OPEN] item 2 from docs/specs/core-docs-pathway-assessment/S239-still-open-consolidation.md for the purposes of this sub-doc’s §10. The 07-collapse-list.md §12.1 entry carries the tier marker; this sub-doc records the data-flow-relevant framing.

10.3 Custom URL-freshness cron retire — [RATIFIED-RETIRE]

The custom URL-freshness cron (a planned background job that would ping source URLs to detect staleness) retires in favour of cocoindex source-binding freshness. Cocoindex’s live=True source walk detects file changes without a separate cron job; URL-based sources route through pullmd’s share-id pattern which handles its own 90-day TTL. The KH governance freshness cron (§8 above) handles the governance dimension; no separate URL-freshness cron is needed.

[RATIFIED-RETIRE] per 07-collapse-list.md §12.3 LIKELY-ABSORB direction + COCO.1. Binding location: 07-collapse-list.md §12.3.

10.4 pipeline_failures table — [RATIFIED-DO-NOT-BUILD]

See §7.3. [RATIFIED-DO-NOT-BUILD] per COCO.7 (00-synthesis-v2.md §3.10).

10.5 Summary anti-patterns table

The following framings are explicitly rejected per Phase 0.9 ratifications and the cocoindex adoption cascade. Subsequent sub-docs (09-diagrams.md flow diagrams, 05-qa-flow.md extraction patterns) build on these closures.

Anti-pattern	Why rejected	Citation
Building pipeline_failures table	Cocoindex retry / back-off / DLQ subsumes; recordPipelineRun() handles rollup	00-synthesis-v2.md §3.10; COCO.7
Treating cocoindex freshness as governance freshness	Two distinct substrates — ingest-latency is not corpus quality; merging would make re-indexed stale records appear fresh	phase-b-prerequisite-2-cocoindex-deep-dive.md §1.2 Theme D; 00-synthesis-v2.md §3.11
Treating CocoInsight as a replacement for audit_log	Different audiences, retention, and query patterns — both are retained	phase-b-prerequisite-2-cocoindex-deep-dive.md §3.3
Merging pullmd into Docling for HTML	Five replaceability gaps make consolidation impossible (JS-SPA, Cloudflare, Reddit, share-id contract, URL-input)	00-synthesis-v2.md §3.3; COCO.5; phase-b-prerequisite-2d-docling-bakeoff.md §5
App-stamped audit_log writes (without trigger)	A trigger fires on every governed-table write regardless of call site; app-stamped audit creates silent gaps in new write paths	N7 RESOLVED-S236; 0.9-decision-graph.md §11.4.1
Direct binary ingestion in Vercel function	1.8 GB Docling footprint > Vercel 250 MB function limit; Cloud Run sidecar is mandatory	phase-b-prerequisite-2d-docling-bakeoff.md §6; COCO.6

---

11. Cross-doc references

11.1 Schemas anchored in 04-workspace-types.md

This sub-doc references the following schema artefacts by pointer only — column-level detail lives in the cited 04-workspace-types.md sections:

Schema artefact	This sub-doc’s role	Canonical home
source_documents.workspace_id NULLABLE	Source-binding §1 — the nullable FK is the source-binding consequence of Q-OQR1-09	04-workspace-types.md §9.1
application_types instance table	§5 — auto-RLS event trigger applies to new application_types table in the combined PR	04-workspace-types.md §3
procurement_workspaces + 5 reserved satellite seats	§5 — auto-RLS fires on seat tables created by the combined PR migration	04-workspace-types.md §4; docs/specs/reserved-workspace-seats/{PRODUCT.md,TECH.md}
q_a_pairs corpus-level shape (no workspace FK)	§3.1 stage overview — cocoindex UPSERT targets this table via ExtractByLlm	04-workspace-types.md §5
content_items.freshness governance enum	§8.3 — cocoindex does not update this field	docs/reference/SCHEMA-QUICK-REFERENCE.md (column-level); 04-workspace-types.md §1 for the broader workspace-binding context

11.2 RLS pattern

The full RLS pattern forward-reference is in §5. Canonical destination: docs/specs/rls-pattern/PRODUCT.md + docs/specs/rls-pattern/TECH.md.

11.3 Reserved workspace seats

New satellite tables created by the combined-PR migration interact with the data-flow source-binding and RLS patterns per docs/specs/reserved-workspace-seats/PRODUCT.md S-4 + S-6 and docs/specs/reserved-workspace-seats/TECH.md T-4 + T-6. Each reserved seat inherits the auto-RLS event trigger (P-1) and grants helper (P-2) from the RLS-PATTERN spec.

11.4 ERDs deferred to 09-diagrams.md

The rendered ERDs — source_documents ↔ content_items ↔ workspaces ↔ application_types ↔ satellite tables; cocoindex ingest flow topology; Cloud Run sidecar sequence — are deferred to 09-diagrams.md. That sub-doc is Wave 3 (sequencing-gated on Wave 1+2 sub-docs landing first per docs/specs/core-docs-pathway-assessment/INV-architecture-split-readiness.md §4).

11.5 Q&A flow

Full Q&A round-trip — markdown sidecar v1, citations polymorphic, question_matches.question_kind discriminator, separate embedding_score + fulltext_score columns — lives in 05-qa-flow.md. This sub-doc names the cocoindex ExtractByLlm stage as the extractor (§3.1) and the q_a_pairs table as a Postgres UPSERT target (§3.1); the table-cardinality rationale lives in 04-workspace-types.md §5.

11.6 MCP tooling

06-mcp-tooling.md is gated on Theme F MCP-action review (STILL-OPEN per 00-synthesis-v2.md §5.2 row 1). The data-flow sub-doc’s connection to MCP tooling is one-directional: the cocoindex ingest pipeline writes to Postgres targets that the KH MCP server then reads and surfaces to Claude clients via tools and resources. The ingest pipeline does not call MCP tools — it writes corpus data that MCP tools consume. This sub-doc does not forward-reference 06-mcp-tooling.md beyond naming it as the destination for MCP tool inventory; ingest and MCP surface are cleanly separated.

---

12. Source-doc supersession

This sub-doc is one of nine that supersede docs/plans/phase-0-investigation/0.9-intended-architecture.md (2015 lines, S229). The source doc’s §3 data-flow framing predates the cocoindex adoption (S234), the Cloud Run sidecar mandate (COCO.6 S234), and the op_id hybrid pattern (N7 S236). It carries a pipeline_failures-table framing that is superseded by COCO.7 and a freshness framing that does not distinguish cocoindex ingest-latency from governance freshness. Full audit trail of the 10 superseded items: docs/specs/core-docs-pathway-assessment/INV-architecture-split-readiness.md §2. Canonical-state sources: 00-synthesis-v2.md §3.1–§3.3 + §3.10–§3.11 + §3.16–§3.17 + §5.1; 0.9-decision-graph.md §11.2 (twelve COCO closures) + §11.4.1 (N7 hybrid op_id).

Source doc preserved as pre-split snapshot during Waves 1-4; archived to .planning/.archive/.specs/ after 09-diagrams.md closes per CLAUDE.md “Historical planning”.

12.1 Heritage docs feeding this sub-doc

Per construction guide §4.1 — three-tier status taxonomy ([CURRENT-CANONICAL] / [PARTIALLY-SUPERSEDED] / [FULLY-SUPERSEDED]); table shape mirrored from 01-vision.md §7.1.

Doc	Date	Status	Useful for
docs/plans/phase-0-investigation/phase-b-prerequisite-2-cocoindex-deep-dive.md	13/05/2026 (S234; §7 S234 end-of-session closures)	[CURRENT-CANONICAL] — cocoindex affordance map (§1.1), substrate-decision register (§1.2 / §1.3), and per-theme resolutions all reflect ratified state. The §2 decision-resolution table preserves pre-spike state for audit; §7 is authoritative for the data-flow topology post-Docling.	Cocoindex stage-by-stage affordance map (§1.1); substrate decisions cocoindex resolves (§1.2); substrate decisions cocoindex does NOT resolve (§1.3); Recommendations 1-5 (§4); CocoInsight vs audit_log separation (§3.3); freshness separate substrates (§1.2 Theme D).
docs/plans/phase-0-investigation/phase-b-prerequisite-2d-docling-bakeoff.md	13/05/2026 (S234; §9 S235 WP-DOCLING-XLSX append)	[CURRENT-CANONICAL] — bake-off evidence + per-format verdicts hold; §9 XLSX URL-recall re-verification supersedes the S234 sample-time caveat.	PDF decisive win evidence (§2.1 — 75 headings, 299 table rows vs markitdown 0); Cloud Run sidecar mandate rationale (§6 footprint: 1.8 GB); pullmd retention rationale (§5 JS-coverage gap); license verdicts (§3.3 MIT / AGPL v3).
docs/reference/ai-integration-strategy.md	11/03/2026 (updated 07/04/2026 + verified 28/04/2026)	[PARTIALLY-SUPERSEDED] — §4-§7 layer descriptions hold; Cloud Run sidecar and cocoindex adoption are post-doc additions; “bid management” framing not yet updated to procurement umbrella per Q-OQR1-02.	Layer 2 admin-metadata framing (§4.1); Layer 5 ontology extraction framing (§5); the AI-consumer-first lens on ingest architecture (§1).

Heritage note: 02-data-flow.md has no direct predecessor sub-doc. 01-vision.md provides the positioning context (AI-consumer-first, external-folder canonical, application-types framing) that this sub-doc’s pipeline architecture instantiates; it is the Layer-1 upstream that 02-data-flow.md depends on as a reader entry point.

---

End of sub-doc. Wave 2 parallel: 05-qa-flow.md (dispatched concurrently). Both anchor schemas in 04-workspace-types.md — 02-data-flow.md §2 + §6 cite source_documents.workspace_id nullability + auto-RLS event trigger interaction; 05-qa-flow.md §2 cites the q_a_pairs corpus-level shape from 04-workspace-types.md §5.