ID-59 {59.20} TECH — Corpus q_a_extractions → q_a_pairs promotion (route i, extracted_from_corpus)

ID-59 {59.20} TECH — Corpus Q&A promotion (route i, `origin_kind='extracted_from_corpus'`)

Status: TECH draft — authored 14/06/2026 by a FRESH planner instance (Q-PLANNER-2; NOT the {59.19} PRODUCT author). Companion to the ratified PRODUCT-qa-corpus-promotion.md in this dir (read in full). This is a SEPARATE feature from the sibling TECH.md (per-UC write-back + edit_intent arbitration); it does NOT overwrite it.

Inputs read in full: PRODUCT-qa-corpus-promotion.md (INV-1..INV-16); the S352 adversarial review (specs/_reviews/s352-spec-oq-adversarial-review.md, ID-59 section + silent-default #3); the route-iii promotion endpoint (app/api/q-a-pairs/promote/route.ts); the q_a_pairs / q_a_extractions / q_a_pair_history schema; the q_a_search RPC; the {64.15} history-column migration; lib/ai/embed.ts.

S352 disposition encoded by this TECH (all four ratified decisions): OQ-1 = auto-batch (DEFAULT-SOUND); OQ-2 = ACTIVE RETIREMENT on invalidation (Liam product call, supersedes the PRODUCT’s leave-published recommendation); OQ-3 = DECOUPLE lineage from embedding success (CHANGE, highest severity); SILENT-DEFAULT #3 = idempotency anchor under concurrency (CHANGE — atomic CAS + UNIQUE partial index). Where this TECH departs from a PRODUCT recommendation, the departure is the ratified decision; the PRODUCT invariants are unchanged and each is mapped 1:1 below.

Context

Route i promotes live corpus q_a_extractions into q_a_pairs tagged origin_kind='extracted_from_corpus', writes the promoted_to_pair_id lineage back, embeds the question, and drives the pair to published so it is visible to q_a_search — satisfying ID-45 INV-23. It is greenfield against a ratified S16 design: promoted_to_pair_id and origin_kind='extracted_from_corpus' exist in schema with zero live writers (PRODUCT Context, verified). The route-iii endpoint is the shape to mirror but diverges in three load-bearing ways (batch not per-response; extraction-side lineage not response FKs; reaches search-visibility not draft).

Code-intelligence orientation (cited verbatim)

gitnexus_query({query: 'q_a pairs promotion extraction embedding publish', repo: 'knowledge-hub'}) (14/06/2026) top process proc_97_post = app/api/q-a-pairs/promote/route.ts:POST (lines 70–192); also surfaced components/review/review-tabs.tsx:ReviewTabs (the review-tab pattern an optional curated queue would mirror) and the cocoindex extraction producers (flow.py). No existing process performs extraction→pair promotion — route i is a new flow.

gitnexus_context({name: 'POST', file_path: 'app/api/q-a-pairs/promote/route.ts', repo: 'knowledge-hub'}) — outgoing calls: getAuthorisedClient, authFailureResponse, parseBody, tryQuery, coerceIntent, safeErrorMessage. No arbitrate/arbitrateMany (single-actor). Route i mirrors this call set MINUS coerceIntent (no intent capture in batch promotion) PLUS generateEmbedding.

ast-dataflow/gitnexus are TS-only; the q_a_extractions/q_a_search/SQL claims are grounded by a grep sweep over supabase/migrations/ + direct migration reads.

Relevant files (grounded)

app/api/q-a-pairs/promote/route.ts:71-192 — route-iii promotion (the auth-guard + tryQuery + insert-with-lineage pattern route i mirrors). Note its 422 guards (questionText missing → 422; empty answer → 422) become batch skips in route i (INV-7).
supabase/migrations/20260520225456_t6_q_a_pairs_full_schema.sql:54 — question_embedding vector(1024) NULL added to q_a_pairs (route i is its first writer); :68-73 — q_a_pairs_origin_kind_check admits extracted_from_corpus; :103-127 — q_a_extractions shape (id uuid PRIMARY KEY DEFAULT gen_random_uuid() at :104 — no deterministic backstop; promoted_to_pair_id uuid NULL at :120; invalidated_at timestamptz NULL at :124).
supabase/migrations/20260520120828_t2_combined_pr_intel_shape_b_form_type_split.sql:353-365 — q_a_pairs base: source_workspace_id (:360), superseded_by uuid NULL REFERENCES q_a_pairs(id) ON DELETE SET NULL (:365) — the OQ-2 retirement lineage column already exists.
supabase/migrations/20260609143055_add_superseded_by_source_workspace_to_q_a_pair_history.sql — {64.15}: history trigger now snapshots OLD.superseded_by + OLD.source_workspace_id (:101-102) into q_a_pair_history. So any superseded_by/publication_status UPDATE route i makes is automatically versioned by the existing history trigger — no new trigger needed for the audit trail.
supabase/migrations/20260520231524_t6_q_a_search_rpcs.sql:117-118 — q_a_search visibility predicate verbatim: WHERE qap.question_embedding IS NOT NULL AND qap.publication_status = 'published'.
supabase/migrations/20260521203414_t8_op_id_propagation.sql:16 — the only existing index touching q_a_extractions is idx_q_a_extractions_op_id (partial on op_id). No UNIQUE on promoted_to_pair_id or source_content_item_id exists — confirming S352 silent-default #3.
lib/ai/embed.ts:97 — generateEmbedding(text: string): Promise<number[]> (OpenAI text-embedding-3-large, 1024-dim, in-process 1h TTL cache). Vector → DB serialised via JSON.stringify(embedding) (canonical pattern, e.g. lib/ingest/markdown-orchestrator.ts:675 .update({ embedding: JSON.stringify(embedding) })).

Empirical verification (Q-EX2 / OQ-3 — external-library symbols only)

Date	Pinned	Symbol	Result
14/06/2026	`openai@^6.37.0` (installed `6.37.0`)	`OpenAI.embeddings.create`	PRESENT (`typeof === 'function'`, import-and-call check)

generateEmbedding, getAuthorisedClient, tryQuery, sb() are internal KH symbols (not externally verified per scope). No ABSENT/SIGNATURE_DRIFT — TECH is clear for ratification.

Proposed changes

Two artefacts: (M1) a migration adding the idempotency anchor + a publish-readiness CHECK, and (R1) a batch-promotion entry point. No new RPC is required — route i writes the two Q&A tables directly through the safe Supabase wrappers, mirroring route-iii.

M1 — Migration `idN_route_i_promotion_idempotency.sql` (maps INV-5, INV-8, SILENT-DEFAULT #3)

supabase migration new (CLI, never MCP execute_sql per supabase/CLAUDE.md). Contents:

UNIQUE partial index — the idempotency backstop (SILENT-DEFAULT #3, the single most important change per S352). The extraction→pair link is the SOLE idempotency anchor (PK is gen_random_uuid(), no deterministic key). Add:
```
1
CREATE UNIQUE INDEX CONCURRENTLY IF NOT EXISTS uq_q_a_extractions_promoted_to_pair_id
2
  ON public.q_a_extractions (promoted_to_pair_id)
3
  WHERE promoted_to_pair_id IS NOT NULL;
```
This makes “one extraction → at most one pair link” a DB invariant (1:1, INV-5), and combined with the atomic CAS (R1 step 3) closes the double-promote race that two concurrent/retried batches could otherwise hit. CONCURRENTLY cannot run inside a txn block — the migration file must NOT be wrapped in BEGIN/COMMIT (Supabase db push runs each statement; verify no implicit txn wrapper). (Note: a UNIQUE on promoted_to_pair_id prevents two extractions pointing at the SAME pair — the correct guard for 1:1; it does NOT prevent two pairs from one extraction by itself — that is the CAS’s job in R1.3. Both layers are required.)
No new column. question_embedding, promoted_to_pair_id, superseded_by, source_workspace_id, invalidated_at all already exist (Context). OQ-2 retirement reuses the existing superseded_by + publication_status='archived'; OQ-3 retry reuses the existing nullable question_embedding + the absence of a link.
No new EXECUTE grant / search_path — route i adds no PL/pgSQL function (the CAS is a parameterised UPDATE issued from TS). If implementation later prefers a SQL function for the atomic pair-insert+link (R1 alternative), THAT function MUST carry SET search_path = public, extensions and an explicit REVOKE EXECUTE … FROM anon; (per supabase/CLAUDE.md). The TS-only path needs neither.
Type regen after push: supabase gen types … (no hand-edit) — the new index does not change the row type, so regen is a no-op for database.types.ts; run it to keep parity discipline.

Migration safety (CLAUDE.md gotchas): cat supabase/.temp/project-ref before push; worktree agents must supabase link --project-ref turayklvaunphgbgscat (staging) first; run db push foreground (it prompts). Index build on a near-empty q_a_extractions (pre-cutover) is cheap.

R1 — Batch-promotion entry point (maps INV-1..INV-12)

Location. A library function promoteCorpusExtractions() in lib/q-a-pairs/promote-corpus.ts (new file; direct import, no barrel per CLAUDE.md), invoked by (a) an authenticated operator HTTP route app/api/q-a-pairs/promote-corpus/route.ts (admin/editor guard; NOT added to proxy.ts publicRoutes), and (b) the ID-45 re-ingest pipeline as a batch step (it imports the same library function — single implementation, two callers, INV-3).

Auth + Supabase safety (INV-14, INV-15). getAuthorisedClient(['admin','editor']); failures via authFailureResponse(auth). ALL DB access via sb()/tryQuery() from @/lib/supabase/safe — RLS-scoped authorised client, no service-role escalation, no cross-workspace write (mirrors route-iii PC-20). The pipeline caller passes an operator-context client of the same shape.

Algorithm (per-extraction loop over the eligible set):

Select eligible extractions (INV-1, OQ-3 eligibility — CHANGE). Eligibility is NOT just promoted_to_pair_id IS NULL. To make embedding failure self-healing (OQ-3), an extraction is eligible when it is live AND (unpromoted OR linked-but-its-pair-is-unembedded):
```
1
SELECT e.* FROM public.q_a_extractions e
2
LEFT JOIN public.q_a_pairs p ON p.id = e.promoted_to_pair_id
3
WHERE e.invalidated_at IS NULL
4
  AND (e.promoted_to_pair_id IS NULL
5
       OR (p.id IS NOT NULL AND p.question_embedding IS NULL))
```
This is the S352 “alternative” retry mechanism made concrete: a linked-but-unembedded pair stays eligible, so a transient embedding outage is retried on the next run rather than stranded forever. Order by created_at for deterministic batching. (Implement as a tryQuery against a small RPC or a PostgREST embedded-resource select; a thin RPC q_a_extractions_promotion_candidates() is acceptable and keeps the LEFT JOIN server-side — if added, apply SET search_path + REVOKE … FROM anon.)
Skip unpromotable rows (INV-7). If extracted_answer_text is NULL/empty → record a skip with reason no_answer_text, continue (do NOT fail the batch; mirrors route-iii’s 422 as a skip).
Atomic compare-and-set link + pair insert (INV-5, INV-8, SILENT-DEFAULT #3 — CHANGE). Per extraction, in this exact order so no pair is orphaned and no double-promote window exists:
- (unpromoted case) Insert the q_a_pairs row FIRST (status draft, embedding NULL — see step 4 for why insert-before-embed), capture newPairId. Then CAS the link:
```
1
UPDATE public.q_a_extractions
2
   SET promoted_to_pair_id = :newPairId, updated_at = now()
3
 WHERE id = :extractionId AND promoted_to_pair_id IS NULL;
```
  If the UPDATE affects 0 rows, a concurrent run already linked this extraction → DELETE the just-inserted orphan pair (it has no link, no embedding, never published — safe to delete) and treat as already_promoted. The tryQuery result’s row count is the CAS signal; the UNIQUE index (M1.1) is the backstop if two runs race the insert→CAS interleave. (REST PATCH can silently no-op — assert affected-row count, do not assume success: CLAUDE.md gotcha.)
- (linked-but-unembedded retry case) Skip the insert + CAS; the pair already exists, jump to step 4 to re-attempt embedding on the existing newPairId = e.promoted_to_pair_id.
Embed independently of the link (OQ-3 — CHANGE, highest severity). Call generateEmbedding(pair.question_text). The link from step 3 is ALREADY written and is NOT conditional on embedding success — this is the OQ-3 decouple. On success: UPDATE the pair SET question_embedding = JSON.stringify(embedding)::vector, publication_status = 'published' (auto-batch drives straight to published, INV-3/INV-12). On embedding failure: leave the pair draft with question_embedding NULL (so q_a_search correctly excludes it — never silently published-but-invisible, INV-11), record a embed_failed failure in the summary, and continue. Because eligibility (step 1) re-selects linked-but-unembedded pairs, the next run retries the embed — self-healing (the precise S352 fix; the pair is created + linked but stays retryable; the lineage link does NOT block retry).
Field mapping (INV-6). Pair carries: question_text ← extracted_question_text; answer_standard ← extracted_answer_text; alternate_question_phrasings ← extraction phrasings when present else ’{}‘; origin_kind = ‘extracted_from_corpus’(INV-4 — route i writes no otherorigin_kind); provenance preserved via the extraction's source_content_item_idlineage (the extraction row retains it; the pair'ssuperseded_by/form FKs stay NULL — route-i pairs have NO source_form_response_id/source_question_id). 5a. **Corpus provenance link — q_a_pairs.source_document_id(as-built; ID-127 {127.38} / DR-086 re-point, 25/07/2026).** After the CAS win and BEFORE the publish (**link-then-publish**), the promoter sets the promoted pair'ssource_document_id`. As of {127.38} the linked value is:
```
1
q_a_pairs.source_document_id  ←  extraction.source_document_id
```
i.e. the real corpus source_documents row the Q&A was extracted from, read off the extraction, not derived. This REPLACES the {59.29} derivation sdUuid5(qaSidecarRelPath(pairId)), which — once the sidecar file emit was retired ({131.8} BI-16) — minted a pointer to a source_documents row that is never created. That dangling id silently broke every consumer of the column: derived_pairs on /api/source-documents/[id], the MCP resource’s source-document resolution, the procurement citation overlay’s resolved_source_document_id, and the l_records LEFT JOIN q_a_pairs qa ON qa.source_document_id = sd.id. All four resolve under the new value. The companion sidecar specs in this directory (PRODUCT-/TECH-qa-sidecar-canonical.md) are RETIRED in full — see their banners.
- A NULL extraction.source_document_id is a FAILURE, not a NULL write (OQ-4, ruled strict): the link returns 'failed' → sidecar_failed++ → the publish is ABORTED, the pair stays draft, and the eligibility RPC re-selects it next run (INV-9/INV-11 link-then-publish and its self-heal loop are unchanged — only the value’s provenance changed). The column is nullable in schema but the walk mints it NOT NULL on every extraction, asserted by G5 in scripts/verify-platform-promotion-gate.ts.
- INV-4 is UNCHANGED by this re-point. INV-4 here is origin_kind = 'extracted_from_corpus' (“route i writes no other origin_kind”). It says nothing about source_document_id, and route i still writes exactly one origin_kind. (Note the INV-N numbering COLLIDES across this directory’s specs — INV-4 means the walk skip/route gate in TECH-qa-sidecar-canonical.md and UC6 KH-DB-only in TECH.md. Do not cross-edit on the number.)
- INV-8’s uuid5 file-identity anchor is MOOT for this leg: the id is now read from a row rather than derived, so there is no cross-language derivation left to keep in parity.
OQ-2 active retirement on invalidation (CHANGE vs PRODUCT recommendation — Liam product call). A SEPARATE pass within the same batch (run before or after the promote loop) handles invalidated sources: for each q_a_extractions row with invalidated_at IS NOT NULL AND promoted_to_pair_id IS NOT NULL whose linked pair is still published:
- If a NEW live extraction for the same source_content_item_id has been promoted to a NEW pair this run (or already has one), set the OLD pair superseded_by = newPairId and publication_status = 'archived' (active retirement — the stale answer leaves q_a_search). The existing {64.15} history trigger snapshots the transition automatically.
- If NO replacement pair exists (the re-extraction produced no usable answer, INV-7 skipped it), STILL archive the old pair (publication_status='archived', superseded_by = NULL) — active retirement means a known-stale answer is removed even without a replacement (the ratified OQ-2 posture: correct-but-missing over wrong-but-present). Count these as retired_no_replacement in the summary so the operator sees the coverage delta (no silent drop — the count IS the signal). This retirement is idempotent: an already-archived pair is skipped.
Structured summary (INV-3, INV-7, INV-11, ID-45 INV-23 reconciliation). Return { considered, promoted, skipped: [{extractionId, reason}], already_promoted, embed_failed, retired, retired_no_replacement }. The ID-45 cutover Checker reads promoted + embed_failed: INV-23 “consistent with promoted extractions” holds when embed_failed === 0 (all promoted pairs are search-visible) OR is explicitly reported as a shortfall.

Data flow

1
sequenceDiagram
2
  participant Op as Operator / ID-45 pipeline
3
  participant R as promoteCorpusExtractions()
4
  participant DB as Supabase (RLS-scoped)
5
  participant E as generateEmbedding (OpenAI)
6
  Op->>R: invoke (auth: admin/editor)
7
  R->>DB: SELECT eligible (live AND (unlinked OR linked-but-unembedded))
8
  loop per extraction
9
    alt no answer text
10
      R->>R: skip(reason=no_answer_text)
11
    else promotable
12
      R->>DB: INSERT q_a_pairs(origin_kind=extracted_from_corpus, draft, embed NULL)
13
      R->>DB: UPDATE extraction SET promoted_to_pair_id=pair WHERE link IS NULL  (CAS)
14
      alt CAS 0 rows (race)
15
        R->>DB: DELETE orphan pair  -> already_promoted
16
      else CAS 1 row
17
        R->>E: generateEmbedding(question_text)
18
        alt embed ok
19
          R->>DB: UPDATE pair SET embedding, publication_status=published
20
        else embed fail
21
          R->>R: count embed_failed (pair stays draft+unembedded, retried next run)
22
        end
23
      end
24
    end
25
  end
26
  R->>DB: retirement pass: archive superseded published pairs (OQ-2)
27
  R-->>Op: structured summary

Testing and validation

Vitest (bun run test, never bun test), behaviour-first per test-philosophy.md. Each PRODUCT invariant maps to a concrete check:

INV-1 (live-only) / INV-7 (skip-not-fail): seed an invalidated extraction + an empty-answer extraction + a live one → assert only the live promotes, the invalidated is never linked, the empty is in skipped with reason no_answer_text, batch does not throw.
INV-4 (origin_kind) / INV-6 (field mapping): assert every created pair has origin_kind='extracted_from_corpus', correct question_text/answer_standard, NULL form FKs.
INV-5 + INV-8 (idempotency, 1:1, no duplicates): run the batch twice over identical corpus state → second run promotes 0, extracted_from_corpus count == live-ever-promoted count; the UNIQUE index (M1.1) rejects a hand-crafted second link to the same pair.
SILENT-DEFAULT #3 (concurrency CAS): simulate two concurrent runs over one extraction (or assert the CAS WHERE … IS NULL affected-row contract + UNIQUE backstop) → exactly one pair, one link, the loser deletes its orphan. This is the load-bearing race test.
OQ-3 (decouple + self-heal) — CHANGE: stub generateEmbedding to throw on run 1 → pair created+linked, draft, question_embedding NULL, in embed_failed; un-stub, run 2 → SAME pair (no new pair, link unchanged) gets embedded and published, leaves embed_failed. Asserts the link does NOT block retry. (Maps INV-10/INV-11.)
INV-10/INV-11/INV-12 (search visibility): after a successful promote, q_a_search returns the pair (predicate published AND embedding NOT NULL); a pair that reached published with NULL embedding is asserted IMPOSSIBLE by the code path (publish only on embed success). ID-45 INV-23 integration assertion: post-batch extracted_from_corpus published count > 0 and == promoted minus embed_failed.
OQ-2 (active retirement) — CHANGE: invalidate a promoted source, re-extract to a new pair → old pair archived + superseded_by=newPair, gone from q_a_search; invalidate with NO usable replacement → old pair archived, counted retired_no_replacement. Assert the {64.15} history row was written for the transition.
INV-14 (auth): viewer → 403 via authFailureResponse; route absent from proxy.ts publicRoutes (assert allowlist).

Risks and mitigations

CREATE INDEX CONCURRENTLY in a txn block fails. The migration must not be wrapped in BEGIN/COMMIT; verify Supabase db push does not implicitly wrap. Mitigation: keep M1.1 as the sole statement or split the file.
CAS-then-embed orphan on crash mid-loop. If the process dies after the CAS but before the embed UPDATE, the pair is linked + draft + unembedded — which the eligibility predicate (R1.1) re-selects next run. No orphan, no manual cleanup. This is the intended self-healing, not a leak.
REST PATCH/UPDATE silent no-op (CLAUDE.md): the CAS relies on the affected-row count being the truth signal. tryQuery must surface the count; never assume the UPDATE succeeded.
Embedding cost on a large first batch. generateEmbedding has an in-process cache only (no cross-run persistence); a full-corpus first promotion embeds every question once. Acceptable (one-shot per re-ingest); the cache helps within a run. Surface promoted count so cost is visible.
Schema parity prod↔staging: push M1 to staging, run schema-parity before any prod cutover.

Open questions surfaced by this TECH

NEW-OQ-1 (candidate-select shape): the eligibility LEFT JOIN (R1.1) is cleanest as a thin RPC (q_a_extractions_promotion_candidates()) vs a PostgREST embedded-resource select from TS. RPC keeps the JOIN server-side and avoids over-fetching pairs, but adds a function (needs search_path
- REVOKE anon). Recommend the RPC for clarity; flag for the Executor/Checker to confirm at impl. (Implementation detail, not a product gate — does not block ratification.)
NEW-OQ-2 (retirement-pass ordering): run the OQ-2 retirement pass BEFORE or AFTER the promote loop within one batch? After is safer (a new pair exists to point superseded_by at before the old is archived), but a same-run re-extraction means the new pair is created in THIS run’s promote loop — so retirement must run AFTER. Encoded as “after” above; flag for Checker confirmation.
No cross-Task dependency surfaced — the work is self-contained within ID-59’s q_a-promotion slice against already-landed {64.15} columns. Sibling-only Subtask-dependency constraint holds; no escalation needed.

End of TECH. Proposed changes M1 (migration: UNIQUE partial index idempotency anchor) + R1 (promoteCorpusExtractions() batch entry point) map 1:1 to PRODUCT invariants INV-1..INV-16. Next chain step: decompose implementation into Subtasks {59.21+} (suggested split below).