ID-45 {45.3} TECH — Full-corpus re-ingest + four schema-correctness cutover items

ID-45 {45.3} TECH — Full-corpus re-ingest + cutover

Status: TECH artefact (the {45.3} step of the ID-45 spec chain). Input: the ratified {45.1} RESEARCH.md + {45.2} PRODUCT.md (both read in full). This TECH was DEFERRED at PRODUCT-authoring time (RESEARCH §1.3) and is now authored as the gate to the re-ingest run, scoped by Liam’s S380 ratification of four schema-correctness cutover items timed to the INV-6 zero-row truncate window.

Governing priority (Liam, S379): “Schema correctness > re-ingest speed.” ID-45 is a ONE-TIME truncate-and-re-walk that becomes the client’s STARTING dataset; the ongoing pipeline does incremental UPSERTs, never truncate. Files are the source of truth; DB rows are derived projections (platform-direction.md principle 1 + v1-shape line 41). Every item below is sequenced so the schema lands in its correct long-term shape during the one window where it is cheap (every pipeline write target is empty).

De-identification: the V1 client is referred to only generically. No client codename appears in this doc.

---

Context

What is being built

This TECH covers (a) the run mechanics that satisfy PRODUCT groups A–D (already grounded in RESEARCH §3–§6 — reproduced here only where an implementation decision is needed), and (b) four NEW schema-correctness DDL cutover items ratified by Liam at S380, each landed during the INV-6 zero-row truncate window (PRODUCT INV-6) because that is the only moment a NOT-NULL / constraint addition is free of a backfill:

1. Source-backing constraint — content_items.source_document_id → NOT NULL + re-added FK to source_documents(id) (bl-266, OQ-59-3).
2. Citations durable stale marker — citations.stale_at timestamptz NULL (bl-325).
3. content_type='q_a_pair' legacy discriminator retire.
4. Explicit truncate child-delete ordering for the now-FK-less pipeline children.

PRODUCT defines the run/cutover invariants (INV-1..33); this TECH adds the four cutover-DDL items as proposed changes and maps them to the invariants they touch or extend.

Code-intelligence orientation (cited verbatim — Checker-verifiable)

The write surface is split TypeScript (app routes, lib/mcp) + Python (flow.py). The cocoindex pipeline write surface is Python, so ast-dataflow (TS-only) does not cover it; orientation was run via gitnexus_query / gitnexus_context for the TS surface and grep

• Read against flow.py and the migration corpus (SQL outside both symbol indexes).

• gitnexus_query({repo:'canonical', query:'content_items source_document_id ingest insert'}) resolved the manual create entry to Function:app/api/items/route.ts:POST (lines 29–452) and the pipeline body to Function:scripts/cocoindex_pipeline/flow.py:_ingest_file_body (lines 1832–1974); it did not enumerate every mint site, so the four+ write-paths below were confirmed by direct grep/Read.
• gitnexus_query({repo:'canonical', query:'citations re-anchor stale UC4 text loss'}) surfaced the citation-rendering cluster (lib/citations.ts, components/item-detail/content-effectiveness-panel.tsx, lib/mcp/formatters/procurements.ts:formatCitation) but not the UC4 re-anchor write site, which was pinned by direct read to app/api/items/[id]/route.ts:685–698 (the log-only stale branch) and :700–727 (the survives / re-anchor-update branch).
• gitnexus_context({repo:'canonical', name:'reference_ingest'}) returned Symbol 'reference_ingest' not found (it is a PL/pgSQL RPC, not a TS/Python symbol — outside the graph). The RPC body was read directly at supabase/migrations/20260617130000_squash_baseline.sql:4555–4616. ccc search was not separately reachable in this environment; the migration-corpus reads stand as the orientation evidence for the SQL surface. Not a greenfield surface — every cited symbol resolved.

Current state — the proven SD-first pattern (the template for item 1)

The migration corpus was squashed at S-recent into 20260617130000_squash_baseline.sql; all brief line-cites resolve against it. public.reference_ingest (squash:4555–4616) is the proven, in-engine precedent that source-first minting is satisfiable under cocoindex:

• It mints the source_documents PK server-side as uuid5(_KH_PIPELINE_DOC_NS, 'sd:'||url) (squash:4562), inserts the source_documents row first (squash:4585), then the reference_items row carrying source_document_id = v_sd_id (squash:4598–4607) — both in one transaction (SECURITY DEFINER PL/pgSQL body runs in the caller’s txn; squash:4581–4584 comment).
• reference_items.source_document_id is NOT NULL (squash:7230) with an ON DELETE RESTRICT FK (squash:9919). REFERENCE_ITEMS_SCHEMA in flow.py declares the same column nullable=False (flow.py:1270). This is the exact target shape item 1 brings content_items to — and it already works in production in the same engine.

Current state — content_items.source_document_id and its mint sites

• content_items.source_document_id is currently nullable uuid with no FK (squash:315; the FK was deliberately dropped in 20260602073942 / BUG-E for cocoindex cross-target reasons — confirmed absent: no content_items_source_document_id_fkey in the squash). A non-partial index exists only as a partial WHERE source_document_id IS NOT NULL (squash:8796).

• The pipeline write-path is ALREADY source-backed. flow.py:_ingest_file_body mints the source_documents row (source_document_id = uuid5('sd:'||rel_path), flow.py:2039 + the declare_row at 2068) and writes that same id onto the content_items row (flow.py:2114). The URL/reference content path does likewise (flow.py:2775, :2817). The CONTENT_ITEMS_SCHEMA column is declared nullable=True only because the constraint is not yet landed (flow.py:1170) — flipping it to nullable=False is a declaration tighten, not a logic change (the value is already always present on the pipeline path).

• FIVE app-side write-paths mint content_items rows that may NOT carry source_document_id and would 500 under NOT NULL (each uses the conditional-spread ...(source_document_id && { source_document_id }) idiom, omitting the column when absent):

  #	Path	Mint site	Source-backing today
  P1	POST /api/items (manual create)	route.ts:202, insert :215	optional (schemas.ts:346); omitted when caller passes none
  P2	POST /api/items/batch	batch/route.ts:267, insert :278	optional; omitted when absent
  P3	lib/mcp/tools/content.ts (mcp_create)	content.ts:590, insert :609	optional; omitted when absent
  P4	app/api/upload/route.ts	content_items insert :315 before source_documents :421, links back via UPDATE :444; sd-insert is wrapped try{} and tolerated as non-fatal	wrong order — ci minted source-less, sd linked after
  P5	POST /api/procurement/[id]/outcome/integrate (bid_outcome_integration)	route.ts:209–246	writes NO source_document_id at all (the insert has no such key)

  Brief correction (load-bearing). The dispatch brief described P5 as “writes none” meaning it mints no rows; in fact P5 does insert content_items rows (route.ts:210) and sets no source_document_id — so it is a genuine fifth source-less mint path, not an exempt one. The Executor must treat P5 as in-scope for the source-first fix.

Current state — citations UC4 re-anchor (item 2)

The citations table (squash:5447–5467) has no stale column today. On a content edit, PATCH /api/items/[id] re-anchors char-kind citations: it loads citations for the item (route.ts:659–668), and for each whose cited_text is no longer present in the new content it logs only — route.ts:686–698, with the literal comment “Non-destructive — no row mutation (no stale column to set)”. Survivors get their char offsets re-anchored via UPDATE (route.ts:703–727). The api.citations view (squash:5477–5493) is the runtime read surface (clients route to the api schema). There is no badge for staleness; the only existing citation-state badge is the “Source removed” orphan badge (distinct concept — orphan = the cited target row is gone; stale = the cited text moved/vanished but the target survives).

Current state — q_a_pair discriminator (item 3) + truncate children (item 4)

• content_type='q_a_pair' is live in the content_items_valid_content_type CHECK (squash:340) and backed by the active partial index idx_content_items_qa_type (squash:8784, WHERE content_type = 'q_a_pair'). Q&A’s canonical home is now q_a_extractions → q_a_pairs (the UC5 promotion), so content_type='q_a_pair' on content_items is a legacy discriminator.
• The 5 dropped cross-target FKs were the pipeline FKs only (RESEARCH R1: ci→sd, chunk→ci, em→ci, qa→source_content_item, ftf→ft). content_items still has surviving app-side FK children in the squash: citations (CASCADE, :9494), classification_disputes (CASCADE, :9514), content_history (SET NULL, :9544), content_item_workspaces (CASCADE, :9554), entity_relationships.source_item_id (SET NULL, :9619), feed_articles (SET NULL, :9639), ingestion_quality_log (CASCADE, :9809), read_marks (CASCADE, :9909), source_document_diffs.affected_content_item_id (SET NULL, :9949), verification_history (CASCADE, :10049). So item 4’s truncate ordering must handle BOTH the FK-less pipeline children (no cascade — explicit delete) AND the surviving app-side children (cascade/set-null fire automatically, or are RETAIN-class and must be excluded from the wipe). This is a sharper requirement than “auto-cascade is gone”.

Reference for the run/cutover mechanics (not restated): RESEARCH §3 (the on-prem walk into a Preview branch), §4 (per-stage verification), §6 (in-place cutover). PRODUCT INV-1..33 are the acceptance conditions. CLAUDE.md / supabase/CLAUDE.md gotchas apply throughout (DDL via CLI only, cat supabase/.temp/project-ref before any push, SET search_path = public, extensions + REVOKE EXECUTE ... FROM anon on every new function, types regen --schema public,api).

---

Proposed changes

The four cutover items are strictly sequenced: the app-path fixes (item 1’s P1–P5) land and merge before the NOT-NULL constraint migration, so no live write-path can 500 the moment the constraint exists. Items 2–4 are constraint/DDL changes that ride the same cutover-batch window but have no app-fix-before-DDL ordering hazard of their own (item 2 has an app-fix-alongside-DDL pairing; see below). The whole batch lands during INV-6 (every pipeline target empty), so every NOT-NULL addition is backfill-free.

Item 1 — Source-backing constraint (maps to / extends INV-8, INV-9, INV-22)

Goal shape: content_items.source_document_id NOT NULL + FK REFERENCES source_documents(id), matching the proven reference_items pattern. This makes PRODUCT INV-22 (“source-document linkage by derivation”) enforceable by constraint rather than only by uuid5 derivation, and turns INV-8/INV-9 source-backing into a DB invariant.

Phase A — app-path fixes (land + merge FIRST, before any constraint). Each of P1–P5 must mint a synthetic source_documents evidence row in-transaction (the reference_ingest pattern) and set content_items.source_document_id to it, BEFORE the constraint lands:

• Shared seam (recommended). Introduce ONE owner-gated SQL write seam mirroring reference_ingest — a SECURITY DEFINER PL/pgSQL RPC (working name public.content_item_ingest_with_source(...), in a new migration) that, given the content payload + provenance hints (source_url / source_file / synthesised filename), mints source_documents first (uuid5 'sd:'||<provenance-key>, or gen_random_uuid() for manual rows with no natural URL/file key) then inserts content_items with the fk set — one transaction, idempotent on the deterministic PK where a natural key exists. This keeps all five paths consistent and avoids five separate hand-rolled sd-first blocks. Must carry SET search_path = public, extensions and a REVOKE EXECUTE ... FROM anon grant.

  • Rejected alternative: fix each of P1–P5 inline with a per-route sd insert. Rejected because (a) it duplicates the sd-first logic five times (drift risk), and (b) P4 already demonstrates the hazard of app-level ordering (ci-first, sd-linked-after, sd-insert tolerated as non-fatal — exactly the shape NOT NULL breaks). A single RPC is the reference_ingest precedent and the lower-drift path.

• P4 reorder is mandatory regardless of seam choice. upload/route.ts currently inserts content_items at :315 then source_documents at :421 and links via UPDATE at :444, with the sd insert wrapped in a try{} tolerated as non-fatal (:411 comment). Under NOT NULL the ci insert at :315 fails. Reorder to source-first (mint sd, then ci with the fk), and the sd-insert failure must become fatal (it can no longer be “non-fatal” once the fk requires it). Note P4 still needs the Storage upload + the itemId/filename storage_path, so the sd row can be minted with a placeholder storage_path updated post-upload, OR the upload sequenced before the ci/sd pair — the Executor picks the minimal reorder that keeps ci source-backed at insert time.

• P5 (outcome/integrate) mints a synthetic sd evidence row for the integrated bid outcome (provenance = the source bid; the route already has source_bid_id / source_question_id in metadata, route.ts:238–241 — a natural uuid5 key 'sd:bid_outcome:'||question_id is available).

• Flip the cocoindex schema declaration. CONTENT_ITEMS_SCHEMA["source_document_id"] nullable=True → False (flow.py:1170), matching REFERENCE_ITEMS_SCHEMA (flow.py:1270). No pipeline logic change — the value is already always present (flow.py:2114, :2817).

• Verify mount/commit ordering (the central risk — see Risks). Mount declaration order is ci_target (flow.py:3259) before sd_target (flow.py:3271), but cocoindex is per-target autocommit with Rust-core (not parent-before-child) cross-target ordering (RESEARCH §4.1, R1). Mount order does NOT guarantee sd commits before ci. The constraint must not resurrect the BUG-E hazard — see Risks §R1 and OQ-45-9.

Phase B — the constraint migration (lands AFTER Phase A merges + during INV-6 zero-row). A new migration:

-- (i) belt-and-braces: no source-less rows can exist (INV-6 guarantees the table is empty,
--     but a guard makes the migration safe even if run off-window)
ALTER TABLE public.content_items
  ALTER COLUMN source_document_id SET NOT NULL;
ALTER TABLE public.content_items
  ADD CONSTRAINT content_items_source_document_id_fkey
  FOREIGN KEY (source_document_id) REFERENCES public.source_documents(id)
  ON DELETE <RESTRICT|SET NULL — see OQ-45-8>;

The partial index (squash:8796) can be promoted to a full index (the column is now NOT NULL, so WHERE source_document_id IS NOT NULL is redundant) — optional, Executor’s call. Regen database.types.ts (--schema public,api) so the column types as non-nullable.

OPEN — ON DELETE semantics (OQ-45-8, resolve in this TECH per brief; flagged for Liam ratification). reference_items uses RESTRICT. But content_items is downstream of the incremental-UPSERT file-deletion → source_documents archival flow, and a hard RESTRICT would block deleting a source_documents row while any content_items still references it.

TECH recommendation: ON DELETE RESTRICT for v1, with archival-first handled at the application layer — NOT SET NULL. Rationale: (1) the column is becoming NOT NULL, so SET NULL is self-contradictory (a delete-cascade that sets a NOT-NULL column to NULL raises immediately — SET NULL is only valid on a nullable column, so choosing it would force the column to stay nullable and defeat item 1). (2) Files-are-truth means a content_items row is a derived projection of a source file; when the file is deleted the incremental pipeline should delete/supersede the derived content_items row FIRST (the existing source_documents_parent_id_fkey already uses SET NULL for versioning lineage, squash:9979, and source_document_diffs uses CASCADE, squash:9959/9964 — so source_documents deletion is already a managed flow, not a raw DELETE). (3) RESTRICT matches the proven reference_items shape, keeping the two source-backed tables symmetric. The file-deletion → archival flow (incremental pipeline, future work) deletes children before the parent; RESTRICT makes an out-of-order deletion fail loudly rather than silently orphan provenance — consistent with “schema correctness > speed”.

If this blocks a known deletion flow, escalate. I found no current code path that does a raw DELETE FROM source_documents where a content_items child exists (the upload re-version path uses parent_id lineage, not delete). If the Executor or a later incremental-pipeline Task surfaces a deletion flow that RESTRICT blocks, that is an escalation to add an explicit archival-first delete-children step, not a reason to weaken the FK to SET NULL. This OQ is recorded for Liam to ratify alongside the constraint.

PRODUCT-amendment check (escalation, NOT specced here). Bringing manual/MCP create under mandatory source-backing changes the ingest UX: P1 (POST /api/items) and P3 (mcp_create) callers who today create a content item with no provenance will, post-change, always have a synthetic source_documents row minted on their behalf. This is invisible to the caller (no new required field — the seam synthesises provenance), so it is a behaviour-preserving internal change and likely does NOT need a PRODUCT amendment. However, if Liam wants manual/MCP create to require an explicit source document (a real UX change — a new required field), that is a PRODUCT-level decision. Escalation to the Orchestrator: confirm whether synthetic-provenance-minting (transparent) is acceptable, or whether mandatory-explicit-source (UX-visible) is wanted — the latter needs a {45.2} PRODUCT amendment before the Executor builds it. This TECH assumes transparent synthetic minting (no PRODUCT amendment).

Item 2 — Citations durable stale marker (extends INV-23; new render-surface invariant)

Goal shape: record when a citation went stale, durably, and surface it on text-rendering surfaces only.

• Migration: ALTER TABLE public.citations ADD COLUMN stale_at timestamptz NULL; (RATIFIED timestamptz NOT boolean — a superset: records when, and IS NOT NULL answers the boolean question). Optional partial index CREATE INDEX idx_citations_stale_at ON public.citations (stale_at) WHERE stale_at IS NOT NULL; (cheap; only stale rows indexed). No FK, no CHECK needed.
• Regenerate the api.citations view (squash:5477–5493) to add the stale_at column to the SELECT list — the view is security_invoker=true, so no grant change; just re-CREATE OR REPLACE with the column added. Regen database.types.ts (--schema public,api) so both public.citations and api.citations carry stale_at.
• Flip the UC4 write site (app-fix, lands ALONGSIDE the migration — paired, not sequenced). app/api/items/[id]/route.ts:
  • Stale branch (:685–698): replace the log-only block with an UPDATE setting stale_at = now() on the citation (via tryQuery(supabase.from('citations').update({ stale_at: new Date().toISOString() }).eq('id', citation.id), 'items.patch.citation_reanchor.stale')). Keep the existing logBestEffortWarn. This is the durable marker.
  • Survives branch (:700–727, self-healing clear): when a previously-stale citation’s text is found again (the survives path), reset stale_at = null in the same UPDATE that re-anchors the char offsets (so a single round-trip both re-anchors and clears). This is the RATIFIED self-healing behaviour — a re-edit that restores the text un-stales the citation.
• Citation-panel badge (UI). Add a “may be outdated” badge to the citation panel, distinct from the existing “Source removed” orphan badge. The render surfaces are the citation-rendering cluster found in orientation: components/item-detail/ citation panel
  • lib/mcp/formatters/procurements.ts:formatCitation (squash-area lib/mcp formatter) where citation text is rendered to an MCP consumer. Badge shows when stale_at IS NOT NULL.
• Stale-AGNOSTIC consumers (RATIFIED — do NOT change). Win-rate / effectiveness COUNT consumers (get_content_win_rate, get_aggregate_win_rate_stats — squash:511/456 api, :2246/:2018 public; formatContentEffectiveness lib/mcp) stay stale-agnostic: a stale anchor does not invalidate won/lost provenance. ONLY text-rendering surfaces show the badge. The Executor must NOT add a stale_at IS NULL filter to any COUNT consumer.

Truncate-coordination note (RATIFIED framing). citations is NOT a pipeline truncate target — its empty state pre-launch is INCIDENTAL, not pipeline-driven. Describe this migration as cutover-batch-COORDINATED (one api-view + types regen alongside the other cutover DDL), NOT truncate-dependent. The column add is online-safe (nullable add, no rewrite) and could in principle land any time; it rides the cutover batch only to share the single types-regen + api-view-regen pass. bl-326 (block/page re-indexing) stays DEFERRED — this item handles char-kind staleness only, matching the existing re-anchor scope (route.ts:703, char-kind only).

Item 3 — content_type='q_a_pair' legacy discriminator retire

Goal shape: retire the legacy discriminator from the live CHECK + index, riding the zero-row window (the re-walk does not write content_type='q_a_pair' — Q&A lands in q_a_extractions).

• Legacy-row soft-archive (PRE-step, before the CHECK change). Any surviving content_items rows with content_type='q_a_pair' that are in the keep-set must be soft-archived or re-typed before the CHECK drops the value (a CHECK change is rejected if any row violates it). Because INV-6 truncates the pipeline targets and the keep-set export ({64.7}) is the corpus input, the Executor must confirm the keep-set carries zero content_type='q_a_pair' rows (or soft-archive them: set publication_status='archived' + re-type to 'document'/'other', preserving the row for history). This is a data step gated on the {64.7} keep-set — flag as a pre-DDL check.
• Migration: re-create the content_items_valid_content_type CHECK (squash:340) without 'q_a_pair', and drop the partial index idx_content_items_qa_type (squash:8784). A CHECK constraint cannot be edited in place — DROP CONSTRAINT content_items_valid_content_type then ADD CONSTRAINT ... CHECK (...) with the reduced value set, in one migration.
• Regen database.types.ts so the content_type enum-like type drops q_a_pair.

Sequencing within item 3: legacy-row soft-archive (data) → DROP/ADD CHECK + DROP index (DDL). All inside the zero-row window. If the keep-set is confirmed q_a_pair-free this is pure DDL; the soft-archive step is a guard.

Item 4 — Explicit truncate child-delete ordering (implements INV-6 safely)

Goal shape: the INV-6 truncate of the 9 pipeline write targets must delete children before parents explicitly, because the 5 cross-target pipeline FKs were dropped (RESEARCH R1) so auto-cascade is GONE for the pipeline parent→child edges — while the surviving app-side FK children (CASCADE/SET NULL) still fire and the RETAIN-class children must be excluded.

• Author an explicit ordered truncate/delete script (a runbook SQL block, NOT a migration — it is a one-time cutover operation, owned by {64.8}/the cutover runbook, this TECH specifies the ordering). Order, child-before-parent, for the FK-less pipeline edges:
  1. Leaf derived children first: content_chunks (was chunk→ci, FK dropped), entity_mentions (was em→ci), entity_relationships (source_item_id SET NULL to content_items survives as an app FK — delete er rows in the pipeline wipe regardless), q_a_extractions (was qa→source_content_item), form_template_fields (was ftf→ft).
  2. Then the parents: content_items, form_templates, source_documents, reference_items.
  3. Plus the downstream non-pipeline derived children that ride the wipe: q_a_pairs + q_a_pair_history (promotion targets), and any citations whose cited target is being truncated — but note citations cited→content_item FK is CASCADE (squash:9494), so truncating content_items auto-removes those citations; since citations is empty pre-launch (incidental, item 2) this is moot in practice but must be in the documented ordering for correctness.
• RETAIN-class exclusion (cross-ref PRODUCT INV-5). The script must NOT touch content_item_workspaces (the ID-69 junction — see OQ-45-10 below), feed_articles (re-link target, RETAIN structure), entity_aliases / entity_pair_resolutions (Stage-5 idempotency input), user_roles / user_profiles / company_profiles / feed_sources / feed_prompts. Cross-ref the RESEARCH §2.1 disposition table — that table is the authoritative keep/wipe list; this script implements its wipe column in FK-safe order.
• Mechanics: prefer explicit DELETE FROM <child> in order (TRUNCATE … CASCADE would cascade through the surviving app FKs and could hit RETAIN tables — RISKY; explicit ordered DELETE is safer and auditable). Wrap in a single transaction so a partial wipe cannot pollute the verification baseline (cross-ref INV-6’s blocking zero-row re-check).

ID-69 junction survival (OQ-45-10, from S379 §3 — surfaced for Liam). The content_item_workspaces M2M junction (squash:5725) is populated by user action, not the pipeline, with ON DELETE CASCADE to content_items (squash:9554). When INV-6 truncates content_items, those existing workspace assignments cascade-delete. The S379 finding asks: does ID-45 need a carry/repopulation step for existing assignments, or is assignment purely forward (fresh user action post-cutover)? Because content PKs are deterministic uuid5 (ci:{rel_path}), a content item re-ingested from the same file gets the same id — so a carry step (snapshot content_item_workspaces before the wipe, restore after the re-walk re-mints the same-id content rows) is feasible and would preserve assignments. TECH recommendation: snapshot-and-restore the junction (it is cheap, and losing the 3 intelligence workspaces’ content assignments would be a silent regression at handover — violates INV-32 “no silent data loss of RETAIN-class tables”). Flag for Liam: confirm whether existing assignments are carried (recommended) or forward-only. If carried, this is a new implementation Subtask (snapshot pre-wipe, restore post-walk).

---

Testing and validation

The four cutover items map onto PRODUCT invariants; each gets a concrete check. The live-Preview-branch smoke remains the oracle (RESEARCH §4) — mocked tests prove shape, only the live smoke proves DB-enforced behaviour (FK, NOT NULL, CHECK).

Item	Invariant(s)	Validation
1 — source-backing	INV-8, INV-9, INV-22	After app-fixes merge: each of P1–P5 exercised (create with NO provenance) lands a content_items row WITH a non-null source_document_id resolving to a real source_documents row — no 500. After constraint lands: INSERT INTO content_items with NULL source_document_id raises NOT NULL; INSERT with a non-existent fk raises FK violation. Pipeline live smoke: full walk lands 0 source-less content_items (INV-22 now constraint-enforced, not just uuid5-derived).
1 — ON DELETE	OQ-45-8	DELETE FROM source_documents where a content_items child exists raises (RESTRICT) — proves no silent orphan.
1 — schema flip	INV-9	CONTENT_ITEMS_SCHEMA nullable=False; a pipeline content write with a missing sd id fails in test (it never should, since :2114 always sets it).
2 — stale_at	INV-23 (extends)	Edit a content item removing cited text → the citation’s stale_at is set (UPDATE, not log-only). Re-edit restoring the text → stale_at resets to NULL (self-heal). api.citations view exposes stale_at. Badge renders on the panel when stale_at IS NOT NULL, distinct from “Source removed”. Win-rate COUNT unchanged by a stale anchor (stale-agnostic).
3 — q_a_pair retire	INV-28-adjacent	Post-migration: INSERT content_items (content_type='q_a_pair') raises (CHECK). idx_content_items_qa_type absent. Keep-set confirmed q_a_pair-free (or soft-archived).
4 — truncate ordering	INV-6, INV-32	Ordered DELETE script runs in one txn with zero FK violations; post-run all 9 pipeline targets count 0 (INV-6); RETAIN tables (incl. content_item_workspaces if carried) untouched / restored (INV-32).

Behaviour-first tests per test-philosophy.md; bun run test (never bun test) for the TS suite, python3 -m pytest scripts/tests/ for the pipeline schema-flip. Each app-path fix (P1–P5) gets a behaviour test asserting the source-backed row is written. The full constraint/run/cutover gating is PRODUCT INV-29 (groups A–C green before cutover).

---

Risks and mitigations

• R1 — re-adding the content_items→source_documents FK can resurrect the BUG-E cross-target-commit hazard (CRITICAL — the central risk of this TECH). Cocoindex writes per-target in independent AUTOCOMMIT transactions with Rust-core cross-target ordering (RESEARCH §4.1, R1); mount declaration order (ci at flow.py:3259 before sd at :3271) does not guarantee the source_documents row commits before the content_items row that references it. The FK was originally DROPPED (BUG-E, 20260602073942) for exactly this reason. With NOT NULL + FK, a content_items commit that lands before its sd row commits raises an FK violation and fails the file.
  • Mitigation / OQ-45-9 (must resolve before the constraint lands). Three options, in preference order: (a) verify empirically on the Preview-branch smoke that, with the constraint present, the pipeline’s per-target ordering in practice commits sd before ci for every file (the reference path already does this successfully for reference_items→source_documents under an identical RESTRICT FK — strong precedent that the engine’s ordering is sd-first for the URL path; confirm it holds for the file path). (b) If ordering is not guaranteed, make the FK DEFERRABLE INITIALLY DEFERRED so the constraint is checked at transaction commit rather than per-row — but note per-target autocommit means each target is its own transaction, so DEFERRABLE only helps if sd+ci ever share a txn (they do not today), so this likely does NOT help. (c) Route the pipeline content write through the same in-transaction sd-first RPC pattern as reference_ingest (a larger pipeline change). The empirical Preview-branch smoke (a) is the gate — the constraint MUST NOT be merged to prod until the live smoke proves the pipeline lands zero FK violations across a full walk. This is why item 1’s constraint rides the cutover window after a verified Preview run, not before.
• R2 — app-fix-before-constraint ordering inverted. If the constraint migration merges before P1–P5 are fixed, every source-less create 500s in production. Mitigation: the constraint migration is a SEPARATE, LATER Subtask with an explicit dependency on all five app-fix Subtasks (sibling-only deps within ID-45 — enforced in the PLAN decomposition).
• R3 — SET NULL chosen for ON DELETE would silently defeat NOT NULL. A SET NULL cascade on a NOT-NULL column raises at delete time; choosing it forces the column to stay nullable. Mitigation: OQ-45-8 recommends RESTRICT; the Executor must not “fix” a delete-flow failure by switching to SET NULL — escalate instead.
• R4 — TRUNCATE CASCADE in item 4 cascading into RETAIN tables. TRUNCATE content_items CASCADE would cascade through surviving app FKs (e.g. content_item_workspaces, feed_articles SET NULL) — potentially destroying RETAIN-class data. Mitigation: explicit ordered DELETE (not TRUNCATE CASCADE), RETAIN tables excluded, single transaction (item 4 mechanics).
• R5 — types-regen drift. All four items touch the schema; a missed --schema public,api regen leaves database.types.ts stale and breaks supabase-types-parity CI (PRODUCT INV-2). Mitigation: one consolidated types regen at the end of the cutover batch; verify supabase-types-parity green (INV-2) before cutover.
• R6 — Q&A sidecar walk-skip is a NAMED PRE-WALK GATE owned by the parallel sidecar spec (do NOT implement here). The ID-45 walk MUST skip Q&A sidecar .md files or it mints junk content_items (the S297 BUG-B hazard). The sidecar file-layout convention (owned by the parallel sidecar spec — PRODUCT-qa-sidecar-canonical.md, an ID-59 amendment) MUST be frozen before the walk; the walk’s skip-rule implements that convention. This TECH does NOT specify the convention. Mitigation: a named pre-walk gate — the walk does not start until the sidecar convention is frozen and the skip-rule is wired. Flag as a hard cross-Task dependency (Task-level, not a sibling Subtask dep — see Escalations).

---

Dependencies and sequencing (additions to RESEARCH §7)

• Q&A sidecar file-layout convention (parallel ID-59 amendment, PRODUCT-qa-sidecar-canonical.md) — cross-Task dependency. The walk’s skip-rule implements the frozen convention; the walk cannot start until it is frozen. This is a Task-level dep (not a sibling Subtask dep) — see Escalations.
• bl-106 ingestion_source ratification (OQ-45-1) — still the outstanding G5 tail; gates the keep-set filter exactness (PRODUCT INV-7). Unchanged by this TECH.
• {64.7} keep-set export — gates item 3’s legacy-row soft-archive (the keep-set must be confirmed q_a_pair-free or the rows soft-archived) and item 4’s RETAIN/wipe split.
• ID-57 question_matches (OQ-45-6) — confirmed: the pipeline does not write question_matches; reconciliation stays deferred. No ingest-time capture added by this TECH.

---

Open questions for Liam (new at TECH; RESEARCH/PRODUCT OQs carry forward unchanged)

#	Question	Blocks
OQ-45-8	content_items.source_document_id ON DELETE semantics. TECH recommends RESTRICT (matches reference_items; SET NULL is invalid on a NOT-NULL column; files-are-truth → archival-first delete-children handled at app layer). Ratify RESTRICT, or name a deletion flow that needs different handling.	Item 1 constraint shape.
OQ-45-9	Pipeline cross-target commit ordering under the re-added FK (R1). The Preview-branch live smoke MUST prove the pipeline lands zero FK violations (sd commits before ci for every file) before the constraint merges to prod. Confirm the empirical-smoke gate is acceptable as the proof, or mandate routing pipeline content writes through an in-txn sd-first RPC.	Item 1 constraint merge-to-prod.
OQ-45-10	ID-69 content_item_workspaces junction survival across the truncate (S379 §3). TECH recommends snapshot-and-restore (deterministic uuid5 ids make it feasible; losing assignments violates INV-32). Confirm carry (recommended) vs forward-only.	Item 4 scope (adds a Subtask if carried).
OQ-45-11	Manual/MCP source-backing UX (P1/P3). TECH assumes transparent synthetic-provenance minting (no required field, no PRODUCT amendment). Confirm — or if mandatory-explicit-source is wanted, a {45.2} PRODUCT amendment is needed first.	Item 1 P1/P3 build (PRODUCT amendment if explicit).

---

Follow-ups

• bl-326 (block/page citation re-indexing) stays DEFERRED — item 2 handles char-kind staleness only (matching the existing re-anchor scope). Named here so it is not lost.
• The shared content_item_ingest_with_source RPC (item 1) is reusable beyond the five current paths — any future content-mint path should route through it for source-backing parity. Record in the seam’s migration comment.