ID-57 PLAN — question_matches decomposition

ID-57 {57.4} PLAN — `question_matches` + retrieval + scoring (T10)

Authored: 13/06/2026 (Planner, fresh context — {57.4} PLAN dispatch; per Q-PLANNER-2/B4 NOT the {57.3} TECH, {57.2} PRODUCT, or {57.1} RESEARCH instance). Task: ID-57/T10 — question_matches table + retrieval RPC + scoring. Decomposes (one-to-one) from: ratified {57.3} TECH (id-57-question-matches-retrieval/TECH.md — DDL §A, RPC topology §C, population §E, calibration §F, the WP1/WP2/WP3 migration plan, and the §Decomposition recommendation sketch) over ratified {57.2} PRODUCT (35 invariants A1–A9 / B1–B8 / C1–C6 / D1–D5 / E1–E3 / F1–F3) and {57.1} RESEARCH. De-identification: the v1 pilot tenant is “the pilot tenant” throughout. No counterparty name appears.

Overview

T10 delivers three surfaces — a table (question_matches, storing per-candidate, per-method scores), a scoring + population writer RPC (question_match_recompute, VOLATILE), and a retrieval reader RPC (question_match_search, STABLE) — plus a post-cutover calibration deliverable (bl-76 ts_rank normalisation). The load-bearing plan decision is the pre-cutover / post-cutover split: the table DDL is isolated as its own Subtask so it is independently ratifiable and landable as the {64.8} gate G6 artefact ahead of the ID-45 re-ingest cutover, while the RPCs and calibration are post-cutover feature behaviour that does not gate cutover.

This PLAN confirms the TECH §Decomposition sketch verbatim (four Subtasks {57.5}–{57.8}) and fixes the build order, the sibling-only dependency edges, the WP/PRODUCT-invariant coverage each Subtask owns, and the migration/apply discipline.

Architecture decisions (carried from ratified TECH — not re-litigated here)

Schema slice isolated as the G6 gate. The CREATE TABLE + indexes + RLS + grants (TECH §A / WP1) is a standalone migration and a standalone Subtask {57.5} so it can ratify and land before cutover. The RPCs and calibration ride post-cutover.
Two RPCs, not one (OQ-C RESOLVED). A VOLATILE writer question_match_recompute owns the live scoring (cosine + ts_rank(…, 2)) + scope/publication/embedding eligibility + top-N + ON CONFLICT upsert; a STABLE reader question_match_search reads the materialised rows keyed by form_question_id, returns the stored per-method scores, ranks by the 0.6/0.4 blend (ORDER BY only), and re-checks publication at read. q_a_search is not extended (avoids coupling the MCP search tool to the match path).
Build order: writer before reader. The reader consumes the rows the writer materialises, so {57.6} (writer) precedes {57.7} (reader). This is the one ordering refinement the TECH reader/writer reconciliation introduced over the RESEARCH sketch.
bl-76 is a runtime-parameter calibration, never a schema change (F3). The ts_rank flag lives inside the writer; adopting a different flag is a CREATE OR REPLACE, never an ALTER TABLE. So {57.8} depends on {57.6} (the writer that owns the flag), and is gated externally on the post-T7 re-ingested corpus — a details precondition, not a Subtask dependency edge (sibling-only constraint; the corpus readiness is cross-Task).

Task list (4 Subtasks; sibling-only deps within Task 57)

Phase 1 — Pre-cutover schema slice (the G6 gate)

{57.5} — `question_matches` table migration (WP1 / G6 schema slice)

Description. Author + apply <ts>_id57_question_matches_table.sql per TECH §A: the CREATE TABLE public.question_matches, the two ranking indexes, the RLS enable + grant_standard_public_table_access + explicit REVOKE SELECT … FROM anon, the four role policies (authenticated read; admin/editor insert/update; admin delete), and the table COMMENT. Regenerate types so Tables<'question_matches'> is available. No RPC, no population, no calibration — table DDL only. This is the structurally-complete G6 artefact that must land in the handover DB before the ID-45 cutover.

PRODUCT invariants owned: A1–A9 (the full schema-slice contract), B1/B2/B8 (structural: candidacy-not-selection COMMENT, cardinality via the UNIQUE key + FK shape, no shared FK with citations), E2-anticipation (the matched_at + UNIQUE columns that anticipate the populate path).

TECH WP: WP1 (§A). deps: [] — independently landable; the G6 pre-cutover gate.

Acceptance: table exists with the four FKs (form_question_id/q_a_pair_id NOT-NULL CASCADE; question_kind → form_types(key) RESTRICT); the score CHECKs reject both-null + out-of-range; the (form_question_id, q_a_pair_id) UNIQUE rejects duplicate edges; RLS denies anon SELECT and non-admin/editor writes; the two ranking indexes and no workspace-partition index are present; types regenerated.

Scope: S (one migration file + a type regen + a schema-assertion test).

Phase 2 — Post-cutover feature (writer → reader chain)

{57.6} — scoring + population writer RPC `question_match_recompute` (WP2)

Description. Author + apply the VOLATILE writer per TECH §E in <ts>_id57_question_match_rpcs.sql (the writer half): the live cosine 1.0 - (question_embedding <=> p_query_embedding) + ts_rank(…, 2) scoring over the scope-eligible / published / embedding-bearing corpus, the scope_tag && overlap + anti_scope_tag exclusion, the top-N selection by the 0.6/0.4 blend, and the INSERT … ON CONFLICT (form_question_id, q_a_pair_id) DO UPDATE upsert refreshing matched_at/updated_at. P-4 grants (ALTER … OWNER TO postgres, explicit REVOKE … FROM anon, GRANT … TO authenticated, service_role; bare vector in the signature).

PRODUCT invariants owned: B3/B4 (writes both per-method scores + question_kind), B5 (scope/anti-scope overlap at materialisation), B6 (publication + embedding eligibility), D2/D3 (cosine + ts_rank(…, 2) semantics; ts_rank returns 0 not null), D4 (both scores materialised; default blend selects top-N), E1/E2/E3 (post-cutover materialise; recompute bounded — no duplicate edges, last-scored matched_at visible), F1 (default flag 2 baked into the writer).

TECH WP: WP2 (§E). deps: [57.5] — needs the table; owns the scoring expression the reader will consume.

Acceptance: seeding a form-question + mixed-scope published corpus pairs and calling question_match_recompute materialises top-N rows with both stored scores + matched_at + correct question_kind; a non-overlapping-scope / anti-scope-overlapping / draft/in_review / embeddingless pair is not materialised; a re-call leaves no duplicate edge (A6) and refreshes matched_at; the pipeline suite still writes zero question_matches rows; anon EXECUTE is revoked.

Scope: M (the writer RPC in the shared migration + behaviour tests covering scoring + eligibility + upsert idempotency).

{57.7} — retrieval reader RPC `question_match_search` (WP2)

Description. Author + apply the STABLE reader per TECH §C (the reader half of <ts>_id57_question_match_rpcs.sql): reads the materialised question_matches rows by p_form_question_id (optional p_question_kind), joins q_a_pairs for preview text + pass-through scope_tag/publication_status, returns the stored per-method scores (no re-scoring), re-checks publication_status = 'published' at read time, orders by the 0.6/0.4 blend over stored scores with a q_a_pair_id tie-break, and bounds by p_limit DEFAULT 20. P-4 grants.

PRODUCT invariants owned: C1 (candidates for a form-question), C2 (Step-1 list/preview; Step-2 reuses existing q_a_get_verbatim unchanged), C3 (default-ranking order + deterministic tie-break), C4 (p_limit bound), C5 (empty list, not error — distinguishable from not-found), C6 (raw per-method scores returned), B6 read-time re-check (no stale surfacing), B7 (SECURITY DEFINER scoping; anon never reads), D4/D5 (default ranking policy; blend re-rankable over stored scores with no schema change).

TECH WP: WP2 (§C). deps: [57.6] — reads what {57.6} materialises.

Acceptance: after a {57.6} recompute, question_match_search returns only candidates for that form-question, in stored-score 0.6/0.4 order (deterministic on repeat), both raw scores present, preview truncated, p_limit honoured; a candidate whose pair is unpublished after materialisation is suppressed at read; a form-question with no materialised rows returns an empty list (not an error); re-sorting the returned raw scores reproduces an alternate ordering with no schema change; anon EXECUTE is revoked.

Scope: M (the reader RPC in the shared migration + reader behaviour tests run after a writer populate).

{57.8} — bl-76 ts_rank normalisation calibration (WP3, post-cutover)

Description. Build the bl-76 measurement deliverable per TECH §F: against the post-T7 re-ingested corpus, construct A/B queries with known relevance ground truth, run the three ts_rank normalisation flags — 0 (none), 1 (1 + log(doc-length)), 2 (current default) — compare fulltext_score distributions + NDCG@10, and ratify the best flag. If the ratified flag differs from 2, ship an optional WP3 migration that CREATE OR REPLACEs the ts_rank(…, flag) literal inside the writer question_match_recompute only — never a schema change, never a touch of the reader or the table. Re-materialise affected form-questions to refresh stored scores.

PRODUCT invariants owned: F1 (the shipped default is flag 2 until calibrated), F2 (measurement vs the real re-ingested corpus; NDCG@10), F3 (re-calibration is a runtime CREATE OR REPLACE, never reopens the §A schema gate).

TECH WP: WP3 (§F). deps: [57.6] — the calibrated flag lives inside the writer. The post-T7-corpus readiness is a cross-Task precondition recorded in details/status_note, NOT a Subtask dependency edge (sibling-only constraint — no dep on ID-45). The “Cloud Run sidecar” framing is stale (S298 teardown; pipeline runs on IONOS/Coolify) and MUST NOT appear in the deliverable.

Scope: M (an eval-style measurement script + ratification note; optionally one CREATE OR REPLACE migration if a non-default flag wins — not a unit-test surface).

Wave / ordering plan

Wave	Subtask(s)	Parallelisable?	Gate
W1 (pre-cutover)	`{57.5}`	Runs first and alone	{64.8} G6 — must ratify + land staging→prod before the ID-45 cutover.
W2 (post-cutover)	`{57.6}`	Serial (after `{57.5}` lands + corpus re-ingested)	Owns the scoring the reader consumes.
W3 (post-cutover)	`{57.7}`	Serial (after `{57.6}`)	Reads materialised rows.
W3′ (post-cutover)	`{57.8}`	Parallel with `{57.7}` (both depend only on `{57.6}`)	Externally gated on the post-T7 corpus.

Serialisation rationale. {57.6} and {57.7} share the same migration file (<ts>_id57_question_match_rpcs.sql) and the reader depends on the writer’s stored output, so they are a serial chain, not a parallel pair — dispatch them in build order (writer → reader) to avoid mid-file conflict on a shared migration. {57.5} is strictly first and alone (the G6 gate, zero deps). {57.8} depends only on {57.6} and may run in parallel with {57.7}, but is in practice blocked externally until the post-T7 corpus exists — so in calendar terms it lands last regardless of its lean dependency edge.

Pre-cutover vs post-cutover callout (load-bearing). Only {57.5} is the pre-cutover G6 deliverable — it is the structurally-complete schema slice that must be in the handover DB before the ID-45 re-ingest cutover, and it is independently ratifiable on its own. {57.6}, {57.7}, and {57.8} are all post-cutover — they need the re-ingested real corpus to be useful and do not gate cutover. Do not block cutover on the RPC or calibration work; do not land {57.5} after cutover.

Migration / apply discipline (every WP — from `supabase/CLAUDE.md`)

Confirm target project ref first: cat supabase/.temp/project-ref before any push (staging turayklvaunphgbgscat for .env.local; prod opt-in via --env=prod).
Scaffold via the CLI: supabase migration new <name> — never MCP execute_sql for DDL. Filenames carry no client/counterparty name (IP-leak guard).
Apply staging-first → prod, foreground: supabase db push foreground (interactive CLIs hang background shells). Verify on staging, then prod.
Apply order: {57.5}/WP1 first and independently (the G6 gate) staging→prod ahead of cutover; {57.6}+{57.7}/WP2 after the corpus re-ingest (depend on WP1 only); {57.8}/WP3 only if bl-76 ratifies a non-default flag.
Regenerate types after WP1 applies (supabase/CLAUDE.md type-regen flow) so Tables<'question_matches'> / Enums<> are available; never hand-edit database.types.ts.
Schema-parity: WP1 must apply to both staging and prod; the migration-revoke-guard / schema-parity side-workflows catch drift.

Code-intelligence discipline (every code-touching Subtask — `{57.5}`–`{57.7}`)

The eventual Executor for each code-touching Subtask MUST follow the project code-intel discipline (per .ast-dataflow/CLAUDE.md Inv 3): pre-edit gitnexus_impact on any touched symbol (HIGH/CRITICAL → escalate to the Orchestrator, do not silently proceed); pre-commit gitnexus_detect_changes + a git diff --name-only file-boundary check. SQL migrations and PL/pgSQL RPCs are grep-covered, not ast-dataflow-covered (ts-morph is TS-only) — run a grep sweep over supabase/migrations/*.sql for any symbol-name change. {57.8} is measurement-primary (eval script) but any CREATE OR REPLACE it ships is grep-covered SQL on the same footing.

Risks and mitigations

Risk	Impact	Mitigation
G6 gate slip (`{57.5}` lands after cutover)	High	`{57.5}` carries zero deps; dispatch + ratify it first, independently of all RPC work.
Anon EXECUTE auto-grant on new RPCs (`pg_default_acl`; `REVOKE FROM PUBLIC` is a no-op)	High	Both RPCs carry explicit `REVOKE EXECUTE … FROM anon` (P-4), mirroring the `q_a_search` follow-up migration.
`vector` GRANT/REVOKE signature mismatch	Med	All `ALTER/REVOKE/GRANT … FUNCTION` signatures use bare `vector` (catalog type), per the precedent.
Writer/reader mid-file conflict (shared migration)	Med	`{57.6}`→`{57.7}` is a serial chain on the shared `<ts>_id57_question_match_rpcs.sql`, not a parallel pair.
Stale-candidate accumulation (upsert does not delete dropped-out top-N)	Low	v1 accepts a bounded superset; the reconcile-DELETE is a named non-load-bearing WP2 follow-up.
bl-76 blocked on a corpus that does not yet exist	Low	Recorded as a `details`/`status_note` precondition (post-T7 corpus), NOT a dependency edge — `{57.8}` is dispatch-ready against `{57.6}` and waits externally.

Open questions

No residual blocking OQ. OQ-A (resolved into PRODUCT A2 — instance-LHS), OQ-B / OQ-C / OQ-D / OQ-A5-null (all resolved in TECH §Open-question resolutions), OQ-E (resolved into PRODUCT B8). The sibling-only dependency constraint holds across all four Subtasks with no escalation: every dep is a {57.x} sibling, and the only cross-Task readiness gate (the post-T7 corpus for {57.8}) is a details precondition, not a dependency edge.

Residual (non-blocking, post-cutover) follow-ups (carried from TECH §Follow-ups): optional WP2 reconcile-DELETE of dropped-out top-N candidates; the benign array_to_string trailing-space nit in q_a_search (fold into any future CREATE OR REPLACE, never a standalone migration); an admin/reviewer matching UI consuming question_match_search is explicitly out of T10 scope (PRODUCT Non-goal a).