ID-57 TECH — question_matches + retrieval + scoring

ID-57 {57.3} TECH — question_matches + retrieval + scoring (T10)

Authored: 13/06/2026 (Planner, fresh context — {57.3} TECH dispatch; per Q-PLANNER-2/B4 NOT the {57.2} PRODUCT instance and NOT the {57.1} RESEARCH instance). Task: ID-57/T10 — question_matches table + retrieval RPC + scoring. Derives one-to-one from: ratified {57.2} PRODUCT (id-57-question-matches-retrieval/PRODUCT.md, 35 numbered invariants A1–A9 / B1–B8 / C1–C6 / D1–D5 / E1–E3 / F1–F3) and ratified {57.1} RESEARCH. Canonical architecture: themes/canonical-pipeline/intended-architecture/05-qa-flow.md §7 (+ §11 anti-patterns). De-identification: the v1 pilot tenant is “the pilot tenant” throughout. No counterparty name appears.

Scope split (load-bearing). This TECH spec separates a pre-cutover schema-slice migration (the question_matches table DDL only — the {64.8} gate G6 artefact, §A) from post-cutover migration(s) (the retrieval RPC, population function, and bl-76 calibration — §C–§F feature behaviour). The schema slice is structurally complete per the ratified spec and independently ratifiable/landable ahead of the ID-45 re-ingest cutover.

---

Context

question_matches is the corpus-match substrate recording ranked candidate q_a_pairs for a form-question — the missing edge between a workspace-scoped form-question instance and the shared Q&A corpus (PRODUCT §Summary; 05-qa-flow.md §7.2). T10 makes the SQL-comment-pre-declared scoring columns real and gives the existing-but-uncalled scoring maths a caller. PRODUCT carries the user-visible behaviour; this section is grounded in the codebase precedents the DDL and RPC must mirror.

Code-intelligence orientation (verbatim — fresh {57.3} run)

Per the binding pre-spec-write rule, orientation ran first; raw outputs cited verbatim so the Checker can verify the step. Tool catalogue: .gitnexus/CLAUDE.md, .ast-dataflow/CLAUDE.md (cited, not reproduced).

• gitnexus_query({query: 'q_a_search question_matches form_questions scope_tag scoring', repo: '…/subo-id-57'}) returned execution flows but no question_matches-bearing symbol. Top process symbols: Function:app/api/procurement/[id]/questions/match/route.ts:POST (startLine 32, endLine 252, module [id], process proc_44_post “POST → CreateClient”), Function:app/api/procurement/[id]/questions/route.ts:POST (startLine 184, endLine 290), and definition Function:app/api/procurement/[id]/questions/match/route.ts: matchQuestion (startLine 110, endLine 180, module Ai). All edge form-questions → content_items; none touch question_matches or q_a_pairs retrieval.
• gitnexus_context({name: 'q_a_search', repo: '…/subo-id-57'}) returned { "error": "Symbol 'q_a_search' not found" } — expected, GitNexus is TS-only and q_a_search is a PL/pgSQL RPC; not evidence of non-existence (the precedent migration is read verbatim below).
• grep fallback (greenfield-disclaimer discipline, run before any “does not exist” claim): grep -rln 'question_matches' over supabase/ lib/ app/ scripts/ types/ → hits ONLY in supabase/migrations/20260520231524_t6_q_a_search_rpcs.sql (header comment naming question_matches.embedding_score/fulltext_score as the column-shape source of truth) and a comment in scripts/cocoindex_pipeline/extraction.py — zero DDL, zero row writes anywhere (TS, Python, SQL). grep -rln 'q_a_search' → defined in four migrations, zero TS call sites.
• ast-dataflow (column-reads/column-writes) is TS-only (.ast-dataflow/CLAUDE.md: “ts-morph operates on the TypeScript corpus only”) — N/A for a new SQL table with no TS read/write sites yet; the grep sweep above is the SQL-corpus substitute mandated by that same CLAUDE.md.

Orientation conclusion: question_matches is genuinely unbuilt (confirmed unbuilt-table gap, not a greenfield disclaimer — the precise SQL-layer precedent it builds on was found).

Precedent 1 — the retrieval/scoring substrate q_a_search + q_a_get_verbatim (BUILT, uncalled)

supabase/migrations/20260520231524_t6_q_a_search_rpcs.sql (read verbatim — the canonical precedent T10 reuses). Load-bearing facts:

• q_a_search(p_query text, p_query_embedding vector(1024), p_limit integer DEFAULT 20) RETURNS TABLE (pair_id uuid, question_text_preview text, answer_standard_preview text, embedding_score numeric(5,4), fulltext_score numeric(5,4), scope_tag text[], publication_status text).
• embedding_score = (1.0 - (qap.question_embedding <=> p_query_embedding))::numeric(5,4) (cosine similarity, line ~140).
• fulltext_score = ts_rank(to_tsvector('english', question_text || ' ' || COALESCE(answer_standard,'') || ' ' || array_to_string(alternate_question_phrasings,' ')), plainto_tsquery('english', p_query), 2)::numeric(5,4) — the 2 is the ts_rank normalisation flag (linear doc-length divisor). The migration’s inline comment uses the looser wording “divide by 1 + log(ndoc)” — imprecise; flag 2 is the doc-length divisor (flag 1 is 1+log(ndoc)). This spec cites the correct flag-2 semantics.
• Filter is WHERE question_embedding IS NOT NULL AND publication_status = 'published'; scope_tag/publication_status are pass-through columns and scope filtering is CALLER-SIDE (WHERE scope_tag && caller_scope_tags).
• Internal ORDER BY (embedding_score * 0.6 + fulltext_score * 0.4) DESC — the blend ranks but is NOT returned; callers receive raw per-method scores (N9 RESOLVED-S236).
• LANGUAGE plpgsql STABLE SECURITY DEFINER SET search_path = public, extensions; ALTER FUNCTION … OWNER TO postgres; then explicit REVOKE EXECUTE … FROM anon + GRANT EXECUTE … TO authenticated, service_role. GRANT/REVOKE uses bare vector (no size) — Postgres stores vector(1024) as catalog type vector. Hardened by 20260521095209_t6_followup_revoke_public_execute_anon_inherit_fix.sql.
• The header already binds question_matches.embedding_score + …fulltext_score as the column-shape source of truth — the scoring columns are pre-specified at the SQL-comment layer; ID-57 makes them real.

Precedent 2 — the form-question instance + catalogue + form-type vocabulary (BUILT)

• form_questions (live name; renamed from bid_questions at 20260609145550_id64_14_bid_to_form_rename.sql:46; FK form_questions_template_requirement_id_fkey renamed line 56). Baseline shape (20260416102457_pre_squash_reconciliation.sql:3317): id uuid PK, workspace_id uuid NOT NULL (renamed from project_id at T2 20260520120828_t2_…:157), question_text text NOT NULL, matched_content_ids uuid[] (existing KB-match output — not q_a_pairs), template_requirement_id uuid (FK → form_template_requirements(id) — the transitive Path C reach, PRODUCT A2).
• form_types CV table (20260520120828_t2_…:628): PK key text, seeded 8 values (…:639): bid, rfp, pqq, itt, tender, checklist, questionnaire, sales_proposal_template. RLS-enabled + grant_standard_public_table_access + FOR SELECT USING (true) CV policy.
• FK-to-form_types(key) precedent (resolves OQ-D): form_template_requirements.template_type was migrated from an inline CHECK to ADD CONSTRAINT form_template_requirements_template_type_fkey FOREIGN KEY (template_type) REFERENCES public.form_types(key) ON DELETE RESTRICT (20260520120828_t2_…:695) — the exact pattern question_kind mirrors.

Precedent 3 — the corpus RHS q_a_pairs (BUILT, corpus-level, no workspace FK)

20260520225456_t6_q_a_pairs_full_schema.sql: question_embedding vector(1024) NULL, scope_tag text[] NOT NULL (line 163), anti_scope_tag text[] NOT NULL (line 164), GIN-indexed idx_q_a_pairs_scope_tag (line 285) + idx_q_a_pairs_anti_scope_tag (288). No workspace_id FK — q_a_pairs.workspace_id, the q_a_pair_workspaces junction, and idx_q_a_pairs_workspace are all [RATIFIED-DO-NOT-BUILD] (05-qa-flow.md §11).

Precedent 4 — the grant/RLS helper + role function + a recent new-table RLS precedent

• grant_standard_public_table_access(target_table regclass) and the rls_auto_enable() event trigger (20260514150238_enable_rls_auto_event_trigger_and_grants_pattern.sql:104 / :39) — read verbatim. The helper grants anon SELECT + authenticated/service_role full CRUD; ensure_rls auto-enables RLS on every new public.* table at ddl_command_end. Both are SECURITY DEFINER SET search_path = pg_catalog, REVOKE EXECUTE … FROM anon.
• get_user_role() (20260416102457_…:2425, read verbatim): RETURNS text LANGUAGE plpgsql SECURITY DEFINER SET search_path = public, extensions; SELECT role FROM user_roles WHERE user_id = auth.uid(), RETURN COALESCE(user_role, 'viewer').
• Recent new-table RLS precedent — citations (ID-58, 20260609192337_id58_citations_polymorphic_replace.sql:112): ENABLE ROW LEVEL SECURITY; SELECT TO authenticated USING (true); INSERT/UPDATE gated on get_user_role() = ANY (ARRAY['admin','editor']); DELETE gated on get_user_role() = 'admin'. This is the role matrix question_matches mirrors.

Crux finding — the workspace scope_tag is NOT stored on workspaces

The workspaces table (20260416102457_…:1951) carries no scope_tag column — the workspace’s scope_tag/anti_scope_tag is a caller-resolved value, exactly as q_a_search treats it (scope filtering caller-side). This is load-bearing for OQ-C: the retrieval RPC cannot resolve workspace scope internally from form_questions.workspace_id alone; it must accept scope_tag/anti_scope_tag as parameters (the caller resolves them the same way q_a_search callers do). PRODUCT B5’s workspace.scope_tag is therefore a caller-supplied input, not a column read.

Empirical-verification note (Q-EX2 scope)

vector(1024), the <=> cosine operator, ts_rank(…, 2), to_tsvector/plainto_tsquery are internal KH SQL constructs already in live use in four migrations (the precedent itself) — the vector extension is installed (20260416102457_…:76, CREATE EXTENSION IF NOT EXISTS "vector"). These are not external-library symbols requiring an import-and-call check (the Q-EX2 forcing function scopes to external-library APIs, not internal SQL constructs or installed-extension operators). No external dependency is introduced by this spec.

---

Open-question resolutions

The four TECH-owned open questions are resolved here; each feeds a specific section.

• OQ-D — question_kind storage → RESOLVED: FK to form_types(key) ON DELETE RESTRICT. Mirrors the form_template_requirements.template_type precedent exactly (20260520120828_t2_…:695): typed cardinality, extension via INSERT INTO form_types not ALTER, and ON DELETE RESTRICT so a form-type cannot be deleted while matches reference it. (DDL in §A, item A4.)
• OQ-A5-null — score-column nullability → RESOLVED: both embedding_score and fulltext_score are NULL-able; a CHECK requires at least one present. The population path (OQ-B below) materialises embeddable-eligible candidates only (it reuses the q_a_search filter question_embedding IS NOT NULL), so in practice both scores are present for every row — but making embedding_score NOT NULL would hard-block the future fulltext-only-eligible candidate (PRODUCT B6/D3: a pair with no embedding is still fulltext-scorable) and force an ALTER post-cutover, violating A7. The resolution keeps the schema future-proof without weakening v1: CHECK (embedding_score IS NOT NULL OR fulltext_score IS NOT NULL) guarantees no all-null row, and fulltext_score is effectively always present (PRODUCT D3: ts_rank returns 0, never null). Each column also carries CHECK (… BETWEEN 0 AND 1). (DDL in §A, item A5.)
• OQ-C — new RPC vs reuse/generalise q_a_search → RESOLVED: NEW sibling RPCs that reuse the q_a_search scoring expression verbatim, split into a VOLATILE scoring writer (question_match_recompute) and a STABLE materialised reader (question_match_search); do NOT extend q_a_search. Extending q_a_search would couple the MCP search tool’s contract to the match path and force a signature change on a shipped RPC (RESEARCH §2.3 / OQ-C caveat). The writer (§E) wraps the identical cosine + ts_rank(…, 2) expression with the form-question inputs (p_query, p_query_embedding, p_question_kind) and the workspace scope-overlap parameters (p_scope_tag, p_anti_scope_tag) — moving the scope filter inside the writer (PRODUCT B5) while keeping it parameter-driven (caller resolves scope, RPC applies it) — and materialises the top-N scored rows. The reader (§C, STABLE) reads those materialised rows keyed by form_question_id and returns the stored scores; it does no re-scoring. This is the architecture-faithful model: the table records the candidates (05-qa-flow.md §7.2) and the reader retrieves them for a form-question (PRODUCT C1) — never a write-only log. (Topology in §C; writer in §E.)
• OQ-B — population timing/trigger → RESOLVED: on-demand materialise-and-cache (the VOLATILE writer RPC question_match_recompute), NOT an on-create trigger, NOT a batch sweep. On-create triggers would fire before the corpus is populated (the whole feature is post-cutover) and re-fire on every form-question edit; a batch sweep needs a scheduler not yet in scope. On-demand keeps population lazy and idempotent: the writer scores the scope-eligible corpus, selects the top-N candidates for one form-question, and INSERT … ON CONFLICT (form_question_id, q_a_pair_id) DO UPDATE — recompute-in-place, honouring A6 uniqueness and refreshing matched_at (PRODUCT E2/E3). The reader question_match_search (the read path, §C) returns those materialised rows; v1 ships the writer (scoring + population) + the reader (materialised retrieval). (Population in §E.)

---

Proposed changes

§A — Pre-cutover schema-slice migration (the G6 gate — table DDL ONLY)

Migration filename: supabase/migrations/<ts>_id57_question_matches_table.sql (no client/counterparty name — IP-leak guard). This migration contains the CREATE TABLE, indexes, RLS policies, and grants ONLY — no RPC, no population function, no calibration. It is the structurally-complete G6 artefact that must land in the handover DB before the ID-45 cutover.

Copy-pasteable DDL (resolves A1–A9, OQ-D, OQ-A5-null):

-- =============================================================================
-- ID-57 T10 WP1 — question_matches table (pre-cutover schema slice / {64.8} G6)
-- =============================================================================
-- Source of truth: docs/specs/id-57-question-matches-retrieval/{PRODUCT,TECH}.md §A.
-- Records ranked candidate q_a_pairs for a form-question (NOT the selected answer).
-- Separate per-method scores (N9 RESOLVED-S236); never a blended match_score column
-- (05-qa-flow.md §11 anti-pattern). RPC + population + bl-76 calibration ship POST-cutover.
-- The `vector`/scope columns live on q_a_pairs (RHS, corpus-level); this table stores
-- the scored edge only. Workspace relevance is a read-time scope-overlap filter, never a
-- stored FK on the RHS (A3/A8; 05-qa-flow.md §11).

CREATE TABLE public.question_matches (
  id                uuid        PRIMARY KEY DEFAULT gen_random_uuid(),

  -- A2: LHS = form-question instance (workspace-scoped; carries workspace_id).
  form_question_id  uuid        NOT NULL
                                REFERENCES public.form_questions(id) ON DELETE CASCADE,

  -- A3: RHS = corpus q_a_pair (corpus-level; no workspace FK).
  q_a_pair_id       uuid        NOT NULL
                                REFERENCES public.q_a_pairs(id) ON DELETE CASCADE,

  -- A4 / OQ-D: form-type discriminator as FK to form_types(key) (mirrors
  -- form_template_requirements.template_type precedent; extension via INSERT not ALTER).
  question_kind     text        NOT NULL
                                REFERENCES public.form_types(key) ON DELETE RESTRICT,

  -- A5 / OQ-A5-null: separate per-method scores, both nullable, >=1 present, each in [0,1].
  embedding_score   numeric(5,4),
  fulltext_score    numeric(5,4),

  -- A7: population-anticipating audit columns (no post-cutover ALTER required).
  matched_at        timestamptz NOT NULL DEFAULT now(),
  created_at        timestamptz NOT NULL DEFAULT now(),
  updated_at        timestamptz NOT NULL DEFAULT now(),

  -- A6: candidate-edge uniqueness — one corpus pair is never a duplicate candidate
  -- for the same form-question (question_kind is derivable from the form-question, so
  -- the two-column key suffices per PRODUCT A6 default assumption).
  CONSTRAINT question_matches_candidate_unique UNIQUE (form_question_id, q_a_pair_id),

  -- A5 / OQ-A5-null: no all-null-score row; each score bounded to cosine/ts_rank range.
  CONSTRAINT question_matches_score_present_chk
    CHECK (embedding_score IS NOT NULL OR fulltext_score IS NOT NULL),
  CONSTRAINT question_matches_embedding_score_range_chk
    CHECK (embedding_score IS NULL OR (embedding_score >= 0 AND embedding_score <= 1)),
  CONSTRAINT question_matches_fulltext_score_range_chk
    CHECK (fulltext_score IS NULL OR (fulltext_score >= 0 AND fulltext_score <= 1))
);

ALTER TABLE public.question_matches OWNER TO postgres;

-- A8: indexes for the two load-bearing access paths.
--  (i) retrieve all candidates for a form-question, ranked — FK col + score cols.
--      (The UNIQUE constraint A6 already provides a (form_question_id, q_a_pair_id) index;
--       this adds the ranking-friendly ordering on the per-method scores.)
CREATE INDEX idx_question_matches_form_question_ranked
  ON public.question_matches (form_question_id, embedding_score DESC, fulltext_score DESC);
--  (ii) reverse lookup: which form-questions cite a given corpus pair (recompute on
--       q_a_pair supersession — PRODUCT E3).
CREATE INDEX idx_question_matches_q_a_pair
  ON public.question_matches (q_a_pair_id);
-- NOTE A8: deliberately NO workspace-partition index on the RHS — q_a_pairs scope filtering
-- rides the existing idx_q_a_pairs_scope_tag GIN index (05-qa-flow.md §11 [DO-NOT-BUILD]).

-- A9: RLS + grants. The ensure_rls event trigger auto-enables RLS on CREATE TABLE; the
-- explicit ENABLE here is idempotent and self-documenting. grant_standard_public_table_access
-- applies the standard 3-role grants — including anon SELECT. We then explicitly REVOKE that
-- anon SELECT (belt-and-braces, matching the citations precedent's REVOKE-ALL-FROM-anon
-- posture): RLS already gates anon to zero rows via the policies below, but the explicit
-- REVOKE removes the table-level privilege entirely so anon cannot read question_matches at
-- all (B7/A9 — anon never reads). authenticated/service_role retain the helper's CRUD grants.
ALTER TABLE public.question_matches ENABLE ROW LEVEL SECURITY;
SELECT public.grant_standard_public_table_access('public.question_matches'::regclass);
REVOKE SELECT ON TABLE public.question_matches FROM anon;

-- Role matrix mirrors citations (ID-58): authenticated read; admin/editor write; admin delete.
-- (Workspace-membership scoping of candidate reads is enforced by the SECURITY DEFINER read
--  RPC in WP2 via form_questions.workspace_id — B7; direct-table SELECT is the coarse gate.)
CREATE POLICY question_matches_select_authenticated ON public.question_matches
  FOR SELECT TO authenticated USING (true);
CREATE POLICY question_matches_insert_editor_admin ON public.question_matches
  FOR INSERT TO authenticated
  WITH CHECK (public.get_user_role() = ANY (ARRAY['admin','editor']));
CREATE POLICY question_matches_update_editor_admin ON public.question_matches
  FOR UPDATE TO authenticated
  USING (public.get_user_role() = ANY (ARRAY['admin','editor']));
CREATE POLICY question_matches_delete_admin ON public.question_matches
  FOR DELETE TO authenticated
  USING (public.get_user_role() = 'admin');

-- updated_at maintenance: reuse the standard touch trigger if one exists in the squash
-- baseline (set_updated_at / moddatetime); otherwise matched_at is the authoritative
-- last-scored stamp and updated_at tracks row mutation. (WP1 wires whichever the baseline
-- provides; no new trigger function is introduced by the schema slice.)

COMMENT ON TABLE public.question_matches IS
  'ID-57 T10 — ranked candidate edge between a form-question instance and a corpus q_a_pair. '
  'Separate per-method scores (N9 RESOLVED-S236); never a blended match_score. Candidacy, '
  'not selection (05-qa-flow.md §7.2). Distinct from citations (ID-58 provenance).';

Design notes for §A.

• ON DELETE CASCADE on both FKs: a deleted form-question or corpus pair should not leave orphan candidate edges (matches are derived data, cheaply recomputable). question_kind is ON DELETE RESTRICT (a CV value in active use must not vanish).
• question_kind is derivable from the form-question’s form-type, but is stored on the row (PRODUCT B4) so a bid-kind vs sales_proposal_template-kind match is distinguishable without joining back to the form-question (05-qa-flow.md §7.2). WP2’s writer sets it from the form-question’s resolved form-type.
• The UNIQUE constraint is the two-column (form_question_id, q_a_pair_id) per PRODUCT A6’s default assumption (one kind per form-question). If a later spec needs multi-kind matching per form-question, that is a 3-column-key change in a separate post-cutover migration — out of T10 scope.

§C — Retrieval RPC topology (post-cutover, WP2)

Migration filename: supabase/migrations/<ts>_id57_question_match_rpcs.sql.

Resolving OQ-C: a new writer RPC question_match_recompute owns the q_a_search scoring expression (cosine + ts_rank(…, 2)) and materialises top-N candidate rows into question_matches (OQ-B, §E), plus a new reader RPC question_match_search that reads the materialised rows keyed by form_question_id and returns their stored per-method scores. This is the architecture-faithful split: the table records the ranked candidates (05-qa-flow.md §7.2) and the reader retrieves them for a form-question (PRODUCT C1) — the table is the read source of truth, never a write-only log. The live scoring lives only in the writer (§E); the reader does no re-scoring. Both follow the P-4 security posture.

Reader RPC — question_match_search (Step 1 list/preview over materialised rows; PRODUCT C1–C6, D4): reads question_matches filtered by p_form_question_id (and optionally p_question_kind), ranks by the stored per-method scores, and joins q_a_pairs only to surface the preview text + pass-through scope_tag/publication_status. It re-applies the publication gate at read time so a candidate whose pair was archived/unpublished after materialisation does not surface stale (B6); scope eligibility was already enforced at materialisation time (the writer’s scope filter), so the reader does not re-take scope parameters — keeping the reader workspace-agnostic and dependent only on the stored edge.

CREATE OR REPLACE FUNCTION public.question_match_search(
  p_form_question_id uuid,
  p_question_kind    text    DEFAULT NULL,   -- optional kind filter (PRODUCT A6/B4)
  p_limit            integer DEFAULT 20      -- C4, mirrors q_a_search p_limit DEFAULT 20
)
RETURNS TABLE (
  q_a_pair_id               uuid,
  question_text_preview     text,
  answer_standard_preview   text,
  embedding_score           numeric(5,4),
  fulltext_score            numeric(5,4),
  scope_tag                 text[],
  publication_status        text
)
LANGUAGE plpgsql
STABLE
SECURITY DEFINER
SET search_path = public, extensions
AS $$
BEGIN
  RETURN QUERY
  -- Reads the MATERIALISED candidate edges for this form-question (05-qa-flow.md §7.2:
  -- question_matches RECORDS the ranked candidates). Returns the STORED per-method scores;
  -- no live re-scoring. The join to q_a_pairs supplies preview + pass-through columns only.
  SELECT
    qm.q_a_pair_id,
    LEFT(qap.question_text, 200)                 AS question_text_preview,
    LEFT(COALESCE(qap.answer_standard, ''), 200) AS answer_standard_preview,
    qm.embedding_score,                          -- STORED score (set by the writer, §E)
    qm.fulltext_score,                           -- STORED score (set by the writer, §E)
    qap.scope_tag,
    qap.publication_status
  FROM public.question_matches qm
  JOIN public.q_a_pairs qap ON qap.id = qm.q_a_pair_id
  WHERE qm.form_question_id = p_form_question_id                 -- C1: candidates FOR this fq
    AND (p_question_kind IS NULL OR qm.question_kind = p_question_kind)
    AND qap.publication_status = 'published'      -- B6 re-checked at read (no stale surfacing)
  -- D4 default ranking/blend over the STORED scores; C3 deterministic tie-break.
  ORDER BY (COALESCE(qm.embedding_score, 0) * 0.6 + COALESCE(qm.fulltext_score, 0) * 0.4) DESC,
           qm.q_a_pair_id
  LIMIT p_limit;
END;
$$;

ALTER FUNCTION public.question_match_search(uuid, text, integer) OWNER TO postgres;
REVOKE EXECUTE ON FUNCTION public.question_match_search(uuid, text, integer) FROM anon;
GRANT  EXECUTE ON FUNCTION public.question_match_search(uuid, text, integer)
  TO authenticated, service_role;

Step 2 (verbatim) reuses the existing q_a_get_verbatim(p_pair_id uuid) unchanged (PRODUCT C2) — no publication_status filter at Step 2, question_embedding excluded. No new Step-2 RPC is built.

Blend tunability (PRODUCT D5 / D4 hand-off — TECH owns). The default blend is the precedent’s embedding * 0.6 + fulltext * 0.4, used for ORDER BY only (in both the writer’s top-N selection and the reader’s ranking), never stored as a column, never the sole returned figure (the reader returns both stored raw scores, C6). It is tunable without a schema change by either: (a) the caller re-sorting client-side on the stored raw scores the reader already returns (the recommended v1 path — zero DB change, matches the q_a_search “callers apply own blend” rationale); or (b) parameterising the weights as two optional reader args p_embedding_weight numeric DEFAULT 0.6, p_fulltext_weight numeric DEFAULT 0.4 in a later CREATE OR REPLACE (a function-body change, never an ALTER TABLE — satisfying D5). v1 ships path (a) as the default and reserves (b) as the documented tuning lever. Because the table stores the two raw per-method scores, any blend is recomputable at read time over the materialised rows — no re-scoring of the corpus is needed to re-rank.

§E — Population path (post-cutover, WP2; resolves OQ-B)

Writer RPC — question_match_recompute (VOLATILE; owns the live scoring; PRODUCT E1–E3, D1–D3). This RPC holds the q_a_search scoring expression verbatim — cosine + ts_rank(…, 2) over the scope-eligible, published, embedding-bearing corpus — and UPSERTs the top-N candidate rows (with their per-method scores stored) into question_matches. It takes the form-question inputs (p_query, p_query_embedding, p_question_kind) and the caller-resolved scope params (p_scope_tag/p_anti_scope_tag; the workspaces table carries no scope_tag — Context crux). The reader (§C) then reads what this writes.

CREATE OR REPLACE FUNCTION public.question_match_recompute(
  p_form_question_id uuid,
  p_query            text,
  p_query_embedding  vector(1024),
  p_question_kind    text,
  p_scope_tag        text[],          -- caller-resolved workspace scope (B5)
  p_anti_scope_tag   text[],
  p_limit            integer DEFAULT 20
)
RETURNS integer          -- count of candidate rows materialised
LANGUAGE plpgsql
VOLATILE
SECURITY DEFINER
SET search_path = public, extensions
AS $$
DECLARE
  v_count integer;
BEGIN
  WITH scored AS (
    -- The live scoring expression (mirrors q_a_search verbatim). This is the ONLY place
    -- re-scoring happens; the reader (question_match_search) consumes the stored result.
    SELECT
      qap.id AS q_a_pair_id,
      (1.0 - (qap.question_embedding <=> p_query_embedding))::numeric(5,4) AS embedding_score,
      ts_rank(
        to_tsvector('english',
          qap.question_text || ' ' || COALESCE(qap.answer_standard, '') || ' ' ||
          array_to_string(qap.alternate_question_phrasings, ' ')),
        plainto_tsquery('english', p_query),
        2  -- bl-76 calibration anchor (F1/D3); changing the flag never alters the table (F3)
      )::numeric(5,4) AS fulltext_score
    FROM public.q_a_pairs qap
    WHERE qap.question_embedding IS NOT NULL        -- B6 embedding-eligibility
      AND qap.publication_status = 'published'      -- B6 publication gate
      AND qap.scope_tag && p_scope_tag                       -- B5 scope overlap
      AND NOT (qap.anti_scope_tag && p_scope_tag)            -- B5 anti-scope exclusion
  ),
  ranked AS (
    SELECT s.q_a_pair_id, s.embedding_score, s.fulltext_score
    FROM scored s
    -- D4 default blend selects the top-N to materialise; C3 deterministic tie-break.
    ORDER BY (s.embedding_score * 0.6 + s.fulltext_score * 0.4) DESC, s.q_a_pair_id
    LIMIT p_limit
  ),
  upserted AS (
    INSERT INTO public.question_matches
      (form_question_id, q_a_pair_id, question_kind, embedding_score, fulltext_score, matched_at)
    SELECT p_form_question_id, r.q_a_pair_id, p_question_kind,
           r.embedding_score, r.fulltext_score, now()
    FROM ranked r
    ON CONFLICT (form_question_id, q_a_pair_id) DO UPDATE
      SET embedding_score = EXCLUDED.embedding_score,
          fulltext_score  = EXCLUDED.fulltext_score,
          matched_at      = now(),
          updated_at      = now()
    RETURNING 1
  )
  SELECT count(*) INTO v_count FROM upserted;
  RETURN v_count;
END;
$$;

ALTER FUNCTION public.question_match_recompute(uuid, text, vector, text, text[], text[], integer)
  OWNER TO postgres;
REVOKE EXECUTE ON FUNCTION
  public.question_match_recompute(uuid, text, vector, text, text[], text[], integer) FROM anon;
GRANT  EXECUTE ON FUNCTION
  public.question_match_recompute(uuid, text, vector, text, text[], text[], integer)
  TO authenticated, service_role;

Population semantics (OQ-B / E1–E3).

• On-demand, caller-triggered (an authenticated API route or MCP tool calls question_match_recompute when a form-question’s candidates are first needed or when its text / the eligible corpus changes). Not an on-create trigger (the corpus is empty pre-population; triggers would mis-fire), not a batch sweep (no scheduler in T10 scope).
• Recompute-in-place via ON CONFLICT … DO UPDATE — A6 uniqueness holds across recompute (no duplicate edges, E3), matched_at reflects the last scoring time. Stale candidates that drop out of top-N are not auto-deleted by the upsert; a DELETE … WHERE form_question_id = $1 AND q_a_pair_id <> ALL(<new set>) reconciliation is an optional WP2 refinement (left as a one-line follow-up, not load-bearing for v1).
• q_a_pair supersession recompute (E3): when a q_a_pair is superseded, the idx_question_matches_q_a_pair index (A8-ii) supports finding affected form-questions to re-trigger recompute. The trigger wiring is a WP2 detail; the schema slice already carries the index.
• The pipeline never writes question_matches (RESEARCH §5; grep-confirmed) — no pipeline change, no re-ingest re-run (PRODUCT Non-goals d).

§F — bl-76 ts_rank-normalisation calibration plan (post-cutover, WP3; PRODUCT F1–F3)

bl-76 folds into ID-57 as a post-cutover calibration deliverable, gated only on a post-T7 real Q&A corpus (the re-ingested pilot-tenant corpus). It is not a schema or RPC blocker.

• Default until calibrated (F1): ts_rank(…, 2) (linear doc-length divisor) — the shipped default baked into question_match_recompute (the scoring writer) and q_a_search alike. The reader question_match_search stores no flag — it returns the already-computed fulltext_score, so a flag change never touches the reader (F3).
• Measurement procedure (F2): against the re-ingested corpus, build A/B queries with known relevance ground truth; run the three normalisation flags — 0 (none), 1 (1 + log(doc-length)), 2 (current) — compare fulltext_score distributions + NDCG@10; re-ratify the best flag. The “Cloud Run sidecar” framing is stale (S298 teardown; pipeline runs on IONOS/Coolify) and MUST NOT appear in any deliverable.
• Adoption is a runtime-parameter change, never a schema change (F3): adopting a different flag is a CREATE OR REPLACE FUNCTION editing the ts_rank(…, flag) literal inside question_match_recompute (the scoring writer — and optionally q_a_search) — it does not touch question_matches (the table stores the resulting fulltext_score, not the flag) and does not touch the reader question_match_search. Re-materialising the affected form-questions (a re-call of the writer) refreshes the stored scores. bl-76 therefore never reopens the §A schema gate.
• Benign adjacent nit (RESEARCH §4): the trailing space from array_to_string on an empty alternate_question_phrasings — fix only if q_a_search gets a new CREATE OR REPLACE for another reason; do not open a migration solely for it.

---

1:1 process-contract map (every PRODUCT invariant → mechanism)

The Checker verifies this mapping is complete. Pre = pre-cutover schema slice (WP1); Post = post-cutover feature (WP2/WP3).

PRODUCT invariant	Mechanism in this TECH	Phase
A1 table named question_matches, never bid_question_matches	§A CREATE TABLE public.question_matches	Pre
A2 LHS NOT-NULL FK form_question_id → form_questions(id); Path C transitive	§A form_question_id uuid NOT NULL REFERENCES form_questions(id)	Pre
A3 RHS NOT-NULL FK q_a_pair_id → q_a_pairs(id); no workspace FK on RHS	§A q_a_pair_id uuid NOT NULL REFERENCES q_a_pairs(id); no RHS workspace col/index	Pre
A4 / OQ-D question_kind aligned to form_types; recommend FK	§A question_kind text NOT NULL REFERENCES form_types(key) ON DELETE RESTRICT	Pre
A5 / OQ-A5-null separate embedding_score+fulltext_score NUMERIC(5,4), nullability stated	§A two numeric(5,4) cols, both nullable, …_score_present_chk + range CHECKs	Pre
A6 candidate-edge uniqueness (form_question_id, q_a_pair_id)	§A question_matches_candidate_unique UNIQUE (…)	Pre
A7 population-anticipating cols (matched_at, id, created_at/updated_at)	§A audit columns; no post-cutover ALTER needed	Pre
A8 index on form_question_id (+ranking); no workspace-partition index	§A idx_question_matches_form_question_ranked + idx_question_matches_q_a_pair; RHS scope rides idx_q_a_pairs_scope_tag	Pre
A9 RLS-enabled, grant_standard_public_table_access, REVOKE-from-anon posture	§A ENABLE ROW LEVEL SECURITY + helper + explicit REVOKE SELECT … FROM anon + role policies; RPCs REVOKE anon EXECUTE	Pre (table) / Post (RPC)
B1 row = ranked candidate, not selected answer	§A no selected/accepted column; COMMENT states candidacy	Pre
B2 cardinality 1 form-question ↔ many pairs; pair unique per question	§A UNIQUE key; FK cardinality	Pre
B3 each row carries both per-method scores	§A two score cols; §E writer sets both	Pre/Post
B4 question_kind reflects form-type; live form_types.key	§A FK to form_types(key); §E writer sets from form-question form-type	Pre/Post
B5 scope-overlap filter at match time (&& scope, NOT && anti-scope)	§E question_match_recompute (writer) WHERE scope_tag && p_scope_tag AND NOT (anti_scope_tag && p_scope_tag) — enforced at materialisation	Post
B6 publication gate + embedding-eligibility	§E writer WHERE question_embedding IS NOT NULL AND publication_status='published'; §C reader re-checks publication_status='published' at read (no stale surfacing)	Post
B7 role/workspace visibility; anon never reads	§A authenticated-read policy + explicit anon SELECT REVOKE; §C SECURITY DEFINER reader scopes via form_questions.workspace_id	Pre/Post
B8 distinct from citations; no shared FK	§A no citations FK; COMMENT states distinction	Pre
C1 retrieve ranked candidates for a form-question	§C reader question_match_search(p_form_question_id, …) reads materialised question_matches rows keyed by form_question_id	Post
C2 two-step list/preview → verbatim	§C Step 1 = question_match_search (materialised rows); Step 2 = existing q_a_get_verbatim	Post
C3 ordering by default ranking policy; deterministic tie-break	§C reader ORDER BY (blend over stored scores) DESC, q_a_pair_id	Post
C4 bounded result size (p_limit DEFAULT 20)	§C reader p_limit integer DEFAULT 20	Post
C5 empty result is valid, distinguishable	§C reader returns empty TABLE (not error) when no materialised rows; “not found”/“not yet scored” distinguished by caller	Post
C6 caller receives raw per-method scores	§C reader returns both stored embedding_score+fulltext_score columns	Post
D1 two methods, two scores, always separate	§A/§E separate stored cols; blend never stored/returned	Pre/Post
D2 embedding_score = 1.0 - (emb <=> q) cosine	§E writer cosine expression (verbatim from precedent); stored on the row	Post
D3 fulltext_score = ts_rank(…, 2); returns 0 not null	§E writer ts_rank(…, 2) stored; §A fulltext_score nullable but effectively always present	Post
D4 both present + meaningful; default ranking policy exists	§E writer materialises both via 0.6/0.4 top-N selection; §C reader ranks stored scores by same blend	Post
D5 blend tunable without schema change	§C/§D blend tunability note (client re-sort on stored scores or reader CREATE OR REPLACE weight params; never ALTER TABLE)	Post
E1 rows materialised post-cutover, never by pipeline	§E question_match_recompute writer; no pipeline change	Post
E2 population timing TECH decision; schema anticipates (OQ-B)	§E on-demand materialise; §A matched_at+UNIQUE anticipate	Pre/Post
E3 recompute bounded: no duplicates, last-scored visible	§E ON CONFLICT … DO UPDATE; A6 holds; matched_at refreshed	Post
F1 default normalisation flag 2 until calibrated	§F default ts_rank(…, 2) in question_match_recompute (scoring writer); reader stores no flag	Post
F2 calibration measured vs real re-ingested corpus	§F measurement procedure (flags 0/1/2, NDCG@10)	Post
F3 re-calibration changes runtime param, not schema	§F CREATE OR REPLACE flag change; never touches table	Post

Every A1–A9, B1–B8, C1–C6, D1–D5, E1–E3, F1–F3 invariant is mapped.

---

Migration plan (split honouring G6)

Two (optionally three) migrations, applied staging-first → prod, foreground.

WP	Migration file	Contents	Gate
WP1 (pre-cutover)	<ts>_id57_question_matches_table.sql	CREATE TABLE + indexes + RLS policies + grants (§A only)	{64.8} G6 — must land in the structurally-complete handover DB before the ID-45 re-ingest cutover. Independently ratifiable/landable.
WP2 (post-cutover)	<ts>_id57_question_match_rpcs.sql	question_match_search (read) + question_match_recompute (writer) + REVOKE/GRANT (§C/§E)	Post-cutover; needs the re-ingested corpus to be useful but does not gate cutover.
WP3 (post-cutover)	<ts>_id57_qa_ts_rank_calibration.sql (only if a flag change is ratified)	CREATE OR REPLACE FUNCTION question_match_recompute (the scoring writer) with the calibrated ts_rank flag (§F)	Post-calibration; optional — only if bl-76 ratifies a flag other than 2.

Apply order + staging-first→prod discipline.

1. WP1 first, independently — this is the G6 gate. Apply to staging, verify, then prod, ahead of the ID-45 cutover. It carries no dependency on the RPC or population work.
2. WP2 after the corpus is re-ingested (post-cutover). Depends on WP1 (the table) only.
3. WP3 only if bl-76 ratifies a non-default flag.

Per-apply discipline (every WP, from supabase/CLAUDE.md):

• cat supabase/.temp/project-ref first to confirm the target project ref before any push (staging turayklvaunphgbgscat for .env.local; prod opt-in via --env=prod).
• supabase migration new <name> to scaffold; never MCP execute_sql for DDL.
• supabase db push foreground (interactive CLIs hang background shells — CLAUDE.md).
• Migration filenames carry no client/counterparty name (IP-leak guard).
• After WP1 applies, regenerate types (supabase/CLAUDE.md type-regen flow) so Tables<'question_matches'> is available to any TS caller; consume via Tables<'question_matches'> / Enums<>, never hand-edit database.types.ts.

---

Testing and validation

Each PRODUCT invariant maps to a concrete check (see the 1:1 map for the full set). Tests run via bun run test (Vitest; never bun test) for TS surfaces and python3 -m pytest scripts/tests/ for any pipeline-adjacency assertion; behaviour-first per reference/test-philosophy.md.

WP1 schema-slice validation (pre-cutover G6 — the ratifiable gate):

• A1/A2/A3/A4 — apply WP1 to staging; assert via information_schema / a pgTAP-style SQL test that the table exists with the four FKs and the question_kind → form_types(key) FK. Verify form_question_id/q_a_pair_id are NOT NULL; question_kind FK is ON DELETE RESTRICT.
• A5/OQ-A5-null — insert a row with embedding_score = NULL, fulltext_score = 0.5000 (passes), a row with both NULL (rejected by …_score_present_chk), a row with embedding_score = 1.5 (rejected by range CHECK). Both score columns numeric(5,4).
• A6 — insert two rows with identical (form_question_id, q_a_pair_id); second is rejected by question_matches_candidate_unique.
• A7 — assert matched_at/created_at/updated_at default to now(); id defaults to gen_random_uuid().
• A8 — \d question_matches (or pg_indexes) shows idx_question_matches_form_question_ranked + idx_question_matches_q_a_pair and no workspace-partition index.
• A9/B7 — confirm RLS enabled; an anon-role SELECT returns zero rows / is denied; an authenticated non-admin INSERT is rejected by the editor/admin policy; admin/editor INSERT succeeds; non-admin DELETE rejected.

WP2 retrieval/scoring/population validation (post-cutover). The writer (question_match_recompute) scores + filters + materialises; the reader (question_match_search) reads the materialised rows. So every read assertion first calls the writer to populate, then the reader to verify — exercising the coherent two-RPC contract.

• E1/E2/E3 (writer — run first) — seed a form-question + a handful of published corpus pairs (mixed scope_tag); call question_match_recompute; assert rows materialise into question_matches with both stored scores + matched_at + correct question_kind; call it again (recompute) and assert no duplicate edges (A6) and refreshed matched_at; assert the pipeline test suite still writes zero question_matches rows.
• B5/B6 (writer-side eligibility) — a pair whose scope_tag does not overlap, or whose anti_scope_tag overlaps, or that is draft/in_review, or that has question_embedding IS NULL — assert the writer does not materialise it; additionally assert the reader re-suppresses a candidate whose pair is unpublished after materialisation (B6 read-time re-check).
• D2/D3 (writer scoring) — after recompute, assert the stored embedding_score equals 1.0 - cosine_distance to 4 dp; the stored fulltext_score equals ts_rank(…, 2); a no-match query yields a stored fulltext_score = 0 (not null).
• C1–C6 (reader) — call question_match_search(p_form_question_id, …) against the materialised rows; assert: only candidates for that form-question are returned (C1), ranked order over stored scores (C3, deterministic on repeat), p_limit bound (C4), both stored raw scores returned (C6), preview truncation + pass-through scope_tag/publication_status (C2), empty list for a form-question with no materialised rows (C5, not an error — distinguishable from “not found”).
• D4/D5 (reader ranking) — assert the reader’s default ORDER reflects 0.6/0.4 over the stored scores; assert re-sorting on the returned raw scores reproduces an alternate ordering with no schema change and no re-scoring of the corpus.
• B8 — assert question_matches shares no FK with citations (structural).

WP3 calibration validation (post-cutover, only on flag change):

• F1/F3 — assert the shipped default is flag 2; after a CREATE OR REPLACE flag change, assert question_matches rows are unaffected structurally (only new fulltext_score values on next recompute).
• F2 — the NDCG@10 / distribution comparison is the bl-76 measurement deliverable (eval-style script against the re-ingested corpus), not a unit test.

---

Risks and mitigations

• G6 gate slip. WP1 is the load-bearing pre-cutover artefact; if it lands after cutover the handover DB is not structurally complete. Mitigation: WP1 carries zero dependency on the RPC/population work and is dispatchable + ratifiable first (see Decomposition).
• Anon EXECUTE auto-grant on the new RPCs. pg_default_acl auto-grants anon EXECUTE on every new public.*() function; REVOKE … FROM PUBLIC alone is a no-op. Mitigation: both RPCs carry explicit REVOKE EXECUTE … FROM anon (P-4), mirroring the q_a_search follow-up fix migration.
• vector GRANT/REVOKE signature. Postgres stores vector(1024) as catalog type vector (no size). Mitigation: all ALTER/REVOKE/GRANT … FUNCTION signatures use bare vector (as the DDL above does), matching the q_a_search precedent.
• Scope filter mis-resolution. workspaces carries no scope_tag column (Context crux); a future caller might assume the RPC resolves scope internally. Mitigation: the RPC takes p_scope_tag/p_anti_scope_tag as explicit params and the spec documents that the caller resolves them, exactly as q_a_search callers do.
• Schema-parity prod ↔ staging. WP1 must apply to both. Mitigation: the staging-first → prod discipline + migration-revoke-guard/schema-parity side-workflows (CLAUDE.md Deployment & CI) catch drift.
• Stale-candidate accumulation. The upsert does not delete candidates that drop out of top-N. Mitigation: v1 accepts a bounded superset (the p_limit read still returns top-N); the optional reconciliation DELETE is a named follow-up, not a v1 blocker.

---

Follow-ups

• Optional WP2 refinement: reconcile-delete stale candidates that drop out of top-N on recompute (one DELETE statement; non-load-bearing for v1).
• bl-76 WP3 calibration script (NDCG@10 vs the re-ingested corpus) — gated on the post-T7 real corpus.
• The benign array_to_string trailing-space nit in q_a_search (RESEARCH §4) — fold into any future q_a_search 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) — a separate spec if ever built.

---

Decomposition recommendation (→ Orchestrator)

Recommend a {57.4} PLAN decomposition — YES (still holds after the reader/writer reconciliation). The ratified PRODUCT+TECH pair has the exact compound shape that warrants PLAN: a clean pre-cutover-schema-slice vs post-cutover-feature split (the G6 gate), multiple migrations (WP1/WP2/optional WP3), chain-dependent slices (WP2 depends on WP1’s table; the reader depends on the writer that materialises the rows it reads), and >2h aggregate effort. Decomposing also lets the schema-slice Subtask be isolated and independently ratifiable/landable as the G6 gate ahead of the post-cutover feature work — which the RESEARCH §5 / §7 routing explicitly asks for. The reconciliation sharpened the build order (writer-before-reader, since the writer owns the scoring the reader consumes) but does not change the PLAN-warranting shape.

Suggested {57.5+} Subtask sketch (sibling-only deps; the schema slice is isolated and first; writer precedes reader):

• {57.5} — question_matches table migration (WP1 / G6 schema slice). Author + apply <ts>_id57_question_matches_table.sql per §A (table + indexes + RLS + grants, incl. the explicit anon SELECT REVOKE). Regenerate types. deps: [] — independently landable; this is the G6 pre-cutover gate. (This Subtask alone satisfies the {64.8} G6 tail; it must be ratifiable on its own.)
• {57.6} — scoring + population writer RPC question_match_recompute (WP2). Author + apply the VOLATILE writer per §E: the live cosine + ts_rank(…,2) scoring, the scope/publication/embedding eligibility filter, and the top-N INSERT … ON CONFLICT … DO UPDATE upsert into question_matches; P-4 grants. deps: [57.5] — the writer owns the scoring expression the reader will consume. (Build order: writer before reader.)
• {57.7} — retrieval reader RPC question_match_search (WP2). Author + apply the STABLE reader per §C: reads materialised question_matches rows keyed by form_question_id, returns stored per-method scores, ranks by the 0.6/0.4 blend, re-checks publication at read; P-4 grants. deps: [57.6] — reads what {57.6} materialises.
• {57.8} — bl-76 ts_rank calibration deliverable (WP3, post-cutover). Measurement script (flags 0/1/2, NDCG@10) against the re-ingested corpus; ratify + optionally CREATE OR REPLACE the ts_rank flag inside the writer question_match_recompute per §F. deps: [57.6] (the flag lives in the writer; gated externally on the post-T7 corpus — noted in details, not as a Subtask dep since the corpus is cross-Task).

All deps are sibling-only (within Task 57). The bl-76 corpus precondition is a cross-Task readiness gate (post-T7), not a Subtask dependency — it belongs in the {57.8} details/status_note, not as an edge, so no sibling-only-constraint escalation is needed.

No residual blocking OQ. OQ-A (resolved into PRODUCT A2), OQ-B/OQ-C/OQ-D/OQ-A5-null (all resolved above), OQ-E (resolved into PRODUCT B8). The schema slice (§A) is unambiguous and ratifiable as the G6 gate.