S536: what repeatedly UPDATEs q_a_pairs — the churn is the eval seeder, not the walk

What repeatedly UPDATEs q_a_pairs — S536 analysis

Read-only, Platform staging rbwqewalexrzgxtvcqrh, 2026-08-05 (S536, dispatched sub-agent; queries + code sweeps enumerated at the end). This is the analysis owed before the deferred q_a_pairs mint (S536 continuation prompt: “Settle this before minting: it may widen (a)‘s scope or add a third item.” — it adds a third item).

Headline

The walk is NOT the churn source — the hypothesis “something touches every pair on every walk” is REFUTED in both halves. The churn is scripts/mcp-eval/seed-fixtures.ts re-upserting 10 eval fixtures with byte-identical values per CI job, amplified by an unguarded history trigger that writes a full snapshot row on no-op UPDATEs. 455 of 553 surviving history rows (82%) record zero change.

The two writers — the only two that have ever fired

Writer 1 — the MCP eval seeder. THE CHURN. A DEFECT. scripts/mcp-eval/seed-fixtures.ts:203 does .from('q_a_pairs').upsert({...}, { onConflict: 'id' }) over the 10 MCP_EVAL_SEED_ITEMS fixtures. The rows always already exist, so every run takes the DO UPDATE limb writing values identical to what is stored. Cadence: per CI job, NOT per walk (.github/workflows/ci.yml:801 mcp-eval-seed; also local bun run seed:mcp-eval). Volume: 455 rows / 10 pairs = 45.5 per pair; surviving versions span 193–239, so pre-cleanup ≈230 versions per seed pair — 10 × ~230 ≈ 2,300 of the deleted ~3,000. Requirement-first: the live requirement is idempotent presence of the fixtures, not rewriting — the seeder’s own sibling record_embeddings limb already guards (needsEmbedding(...)); the q_a_pairs limb skipped that discipline.

Writer 2 — corpus promotion. BY DESIGN. lib/q-a-pairs/promote-corpus.ts issues exactly two UPDATEs per newly promoted pair: :715 SET source_document_id (INV-11 link-then-publish lineage, ID-131 {131.8} BI-16), then :1192 SET publication_status='published'. Measured: 98 rows / 49 pairs = exactly 2.0 per pair; no corpus pair has ever reached version 3, and consecutive walks’ pair-UUID sets are disjoint — a later walk never re-touches an existing pair.

Nothing else has ever fired: zero DB functions/rules update the table, cron.job is empty, and all 553 history rows have changed_by IS NULL — the human/edit routes (app/api/q-a-pairs/[id], dedup-merge, governance, archive, repromote) have never run on staging.

The amplifier — the trigger has no distinctness guard

CREATE TRIGGER q_a_pairs_history_on_update AFTER UPDATE ON public.q_a_pairs
  FOR EACH ROW EXECUTE FUNCTION q_a_pairs_history_trigger()

No WHEN (OLD.* IS DISTINCT FROM NEW.*). A no-op UPDATE writes a full history row. Verified empirically twice over: sampled seed pairs carry one distinct payload across 45–46 snapshots, and their updated_at still equals created_at (2026-07-07). Second-order cost: the trigger body runs SELECT COALESCE(MAX(version),0)+1 FROM q_a_pair_history WHERE q_a_pair_id = OLD.id on every update — cost per no-op grows with the churn already stored (quadratic in updates-per-pair).

Corrections to prior readings

• “≈20 updates per pair” was an averaging artefact. The distribution is bimodal: 10 pairs at ~230 versions, 49 pairs at exactly 2, and 87 of 146 pairs at ZERO.
• The 08-05 07:38Z burst (the baseline walk) touched 9 pairs — all newly created, disjoint from the previous day’s. Eval-seed bursts land at all hours, uncorrelated with walks.

Fidelity gap (adjacent, not churn)

source_document_id is NOT among the trigger’s snapshot columns, so history v1 (post-link) and v2 (post-publish) are indistinguishable — the INV-11 lineage write is invisible in the history it generates.

Scope consequence for the deferred mint (owner decision pending)

The eval-seed churn and the teardown gap are independent axes; neither fix covers the other. “Stop q_a_pair_history growing” needs BOTH:

1. the parent-level teardown delete (q_a_pairs WHERE source_document_id IN (…) in dropFixture + the nightly sweep) — kills the linear growth from accumulating MOCK pairs (79 and counting, each legitimately contributing 2 rows), and cuts dedup proposals by the square;
2. a trigger WHEN (OLD.* IS DISTINCT FROM NEW.*) guard AND/OR a presence-check in the eval seeder’s q_a_pairs limb — kills the no-op churn, which is per-CI-job and survives any teardown fix untouched.

Plus riders now on the table: the version-scan quadratic cost, and the source_document_id snapshot omission.

Concept origins

Trigger snapshot columns cite bl-74 (superseded_by) + ID-59 (edit_intent); the only in-repo migration touching the function is 20260715182216_id145_w2_... (ID-145 W2 dropped-column repair — the unconditional shape was visible then and not changed). Seeder is post-M6; promotion’s two-UPDATE structure is ID-131 {131.8} BI-16.

NOT covered

• Whether the eval-seed upsert was ALWAYS a no-op — the ~1,900 deleted rows are unrecoverable; verdict unaffected, historical characterisation only.
• CI-triggered vs local seeder runs not correlated against Actions history (fix blast radius differs: CI job vs developer habit).
• Payload-identity sampled 3 of 10 seed pairs; walk-burst column inspection 24 of 98 rows (per-pair counts and version ranges are full-population).
• Platform staging only — prod and client DBs unqueried.
• RLS policies unread (no such mechanism originates UPDATEs on its own).
• q_a_pair_dedup_proposals contents untouched; the 4 KEPT 27/07 debugging rows intact.

Executed (summary)

13 read-only SQL projections (trigger/function defs via pg_get_triggerdef/ pg_get_functiondef; per-minute history bucketing; per-pair version ranges; payload distinctness; origin_kind/publication_status roll-ups; pg_proc/pg_rules/cron.job sweeps) + full-repo code sweeps for q_a_pairs UPDATE paths in Python (scripts/cocoindex_pipeline/), TS (app/, lib/, components/, hooks/, scripts/), migrations, and workflows.