SPEC-091 migrate/guard RED: w3-chunk-embedding-backfill verify mismatch + iw2 ON CONFLICT 21000 #396

Problem

An external production deployment running the SPEC-091 mid-cutover path (edgequake migrate → edgequake guard RED → start API so the migration engine can copy leftovers) cannot finish vector copy. Guard stays RED, DROP OLD (125 / 126 / 131) correctly fail-closed, and starting EdgeQuake to process residuals still errors.

Tried GHCR / product cuts: 0.26.0 → 0.26.1 → 0.26.5. The crash log below is the 0.26.1 engine signature. Current default branch (edgequake-main @ v0.26.5, commit 86aa8a67) already contains the SPEC-139 engine patch, but 0.26.5 can still leave guard RED on prod-like residue, and a mixed CLI/API pin can still emit the old crash.

This is a product defect in the SPEC-091 copy engine, not a consent-token / --confirm-drop misspelling (that class was SPEC-137, shipped in 0.26.1). Do not weaken DROP SQL.

Repro (prod-like, multi-workspace fleet)

1. Start from a mid-cutover database that already has SAFE SCHEMA through 149, leftover DROP OLD 125 / 126 / 131, and a large public.eq_%_vectors fleet (shared + per-workspace tables).
2. edgequake migrate (expandable / SAFE SCHEMA only).
3. edgequake guard → RED (expected while copy is unfinished).
4. Start the API with EDGEQUAKE_MIGRATION_MODE=automatic so the engine can copy leftovers.
5. Observe engine logs, then edgequake guard still RED.
6. Optional: edgequake migrate --confirm-drop (or --drop-confirm) → Wave D / W4 / IW2 ABORT on uncovered rows.

On 0.26.0, edgequake migrate --drop-confirm also failed on the same class of residue (CLI only documented --confirm-drop; even with consent, DROP stays fail-closed while uncovered rows exist).

Exact error evidence

ERROR edgequake_storage::migration_engine::runner: migration verification FAILED
step="w3-chunk-embedding-backfill" report=VerifyReport { metric:
"w3-chunk-embedding-fleet", expected: 44580, actual: 18503, sampled: 2416,
mismatches: 1370 }
INFO edgequake_storage::migration_engine::runner: migration lease claimed —
starting batches step="iw2-fleet-embedding-backfill"
ERROR edgequake_storage::migration_engine::runner: SPEC-091 migration engine
terminated with error error=database error: iw2 entity insert failed: error
returned from database: ON CONFLICT DO UPDATE command cannot affect row a
second time

Fingerprint:

Postgres contract (verified)

PostgreSQL documents INSERT … ON CONFLICT DO UPDATE as deterministic: one statement must not affect any existing row more than once. Duplicate constrained values in the proposed set raise 21000 (not “last write wins”).

• Docs: PostgreSQL INSERT — ON CONFLICT
• SQLSTATE: 21000 cardinality_violation
• Same class as prior product bugs: SPEC-110 (migration 118 wsdoc), SPEC-098 (AGE edge upsert), serving vector QW2 in storage_impl.rs

This is not 23505 unique-violation on (workspace_id, legacy_vector_id) (closed #374 / #377). Those are a different index. The field string is the 21000 cardinality error.

sqlx does surface DatabaseError::code() == "21000" (proven by e2e_spec139_01_iw2_within_batch_dedupe_avoids_21000).

Root cause (code-grounded)

Track A — iw2 21000 (primary crash on 0.26.0 / 0.26.1)

write_entity_batch builds parallel arrays and upserts:

INSERT INTO entity_embeddings
  (model_id, entity_id, workspace_id, embedding, dimensions, legacy_vector_id)
SELECT $1, e, w, v::halfvec, d, lid
FROM unnest($2::uuid[], $3::uuid[], $4::text[], $5::int[], $6::text[])
  AS t(e, w, v, d, lid)
ON CONFLICT (model_id, entity_id) DO UPDATE
  SET legacy_vector_id = COALESCE(entity_embeddings.legacy_vector_id, EXCLUDED.legacy_vector_id)

Join is EntityNameIndex::resolve → normalize_entity_name. Two legacy ids in one ORDER BY id LIMIT n batch that fold to the same spine (entity:Acme Corp Ltd and entity:ACME_CORP_LTD → ACME_CORP_LTD) propose the same (model_id, entity_id) twice. Postgres refuses.

On v0.26.1 there was no within-batch arbiter dedupe. The file header even claimed ON CONFLICT DO NOTHING while the SQL was DO UPDATE (required for provenance COALESCE). Serving upsert already last-write-wins (QW2); iw2 did not.

Relationship batches have the same DO UPDATE on (model_id, relationship_id).

First claimed batch dies (~100ms). TX rolls back. uncovered_fleet never moves. Every retry hits the same 21000 until the binary changes.

Current HEAD (fleet_embedding_backfill.rs): collapse_writes → conflict_dedupe::dedupe_last_write_wins before UNNEST, plus a 21000/23505 swallow. That is the SPEC-139 fix shipped in 0.26.3.

Pointers (current tree):

• edgequake/crates/edgequake-storage/src/migration_engine/fleet_embedding_backfill.rs — write_entity_batch, collapse_writes, UNNEST upsert (~99–118, ~563–656)
• edgequake/crates/edgequake-storage/src/migration_engine/conflict_dedupe.rs — dedupe_last_write_wins
• edgequake/crates/edgequake-storage/src/migration_engine/coverage.rs — EntityNameIndex::resolve + normalize_entity_name (~61–74, ~788)
• edgequake/crates/edgequake-storage/src/entity_id.rs — normalize_entity_name
• Pre-fix: v0.26.1 write_entity_batch (push entity_ids with no collapse; header still said DO NOTHING)

Track B — W3 verify FAIL + terminal failed

On v0.26.1, ChunkEmbeddingBackfillJob::verify aggregated:

agg.expected += r.expected          -- SUM of per-table legacy `-chunk-` counts
agg.actual = agg.actual.max(r.actual)  -- MAX of the same global typed COUNT(*)

verify_chunk_embedding_backfill used COUNT(*) FROM chunk_embeddings (global) as actual. With many eq_*_vectors tables, SUM(legacy) cannot equal max(global typed) even after a complete copy. Field 44580 vs 18503 is that accounting bug.

Verify FAIL → finish_job(..., "failed") with last_error.verify_failed. claim_lease only claims pending|preflight|running|paused. Next boot skipped W3. Remaining uncovered chunks stayed uncovered even after W1 added spine.

VerifyReport::passes() on 0.26.1 also required mismatches == 0. Sampled 1370/2416 mismatches (halfvec rounding, missing spine, model-name join) failed the job even when coverage was the only DROP-126 gate.

Current HEAD:

• verify_chunk_embedding_backfill actual is per-table coverage (chunks ⋈ chunk_embeddings for that table’s UUID-shaped -chunk- ids), then fleet SUM — same predicate as migration 126.
• passes() is coverage-only (actual >= expected); equality only if EDGEQUAKE_MIGRATION_VERIFY_EQUALITY=1.
• reclaim_verify_failed_jobs resets failed + verify_failed jobs to pending.

Pointers:

• chunk_embedding_backfill.rs verify() (~325–345)
• verify.rs verify_chunk_embedding_backfill (~116–241)
• runner.rs VerifyReport::passes (~46–60)
• lease.rs claim_lease / reclaim_verify_failed_jobs (~58–80, ~411–432)
• Pre-fix MAX bug: v0.26.1 chunk verify lines 339–340
• DROP gate (must stay fail-closed): 126_spec091_vector_drop.sql

Track C — why expected / actual / mismatches can still be “wrong” after 0.26.3

Three different numbers, three meanings:

W3 write path still silently continues when spine/workspace_id is missing and reports failed: 0:

            let Some((chunk_id, ws)) = spine_map.get(&(doc_uuid, index)) else {
                continue; // spine missing (W1 backfill lag) — skipped, reconciled on rerun
            };
            let Some(ws_uuid) = ws else {
                continue; // typed schema requires workspace_id NOT NULL
            };

Cursor still advances. After exhaustion, verify fails, job goes failed, reclaim retries. If the spine never appears (orphan legacy keys), guard stays RED forever. That is fail-closed (126 would abort anyway) but looks like “residuals still error.”

mismatches on 0.26.3+ do not fail verify unless EDGEQUAKE_MIGRATION_VERIFY_EQUALITY=1.

Track D — engine isolation

v0.26.1 run_engine:

for job in &jobs {
    run_job(&pool, job.as_ref(), &config).await?;
}

iw2 Err skipped iw2-fleet-provenance-stamp and any remainder jobs. Log text is exactly SPEC-091 migration engine terminated with error.

HEAD continues after one job Err (SPEC-139: migration job failed — continuing remaining jobs). Seeing the old terminate line on a host that “is 0.26.5” almost always means the serving API image is still 0.26.1 (CLI updated, engine binary not).

Track E — 0.26.0 --drop-confirm

0.26.0 CLI documented --confirm-drop only. Alias + unknown-flag fail-closed shipped in 0.26.1 (SPEC-137). Independently, DROP 125/126/131 must abort while uncovered KV/vector rows remain. A failed --drop-confirm on prod-like data is expected until the engine finishes copy — not a reason to skip guards.

Version matrix

Internal write-up (not a public issue): specs/139-issue-migration/. Upgrade: docs/operations/upgrade-to-0.26.3.md.

Residual gaps on 0.26.5 / HEAD (why an integrator can still be stuck)

1. Image skew. edgequake migrate 0.26.5 + API ghcr.io/raphaelmansuy/edgequake:0.26.1 still 21000-terminates. Confirm /health.version on the running API.
2. W3 silent skip of missing spine keeps actual < expected; reclaim loops; guard RED. Orphan {uuid}-chunk-{n} keys need an honest failed_count / advisor line, not a silent skip.
3. 21000/23505 swallow is all-or-nothing (written = 0, failed += batch.keys.len()) and the cursor still advances. If anything bypasses Rust dedupe, those keys are skipped until reclaim resets the whole job.
4. Partial UNIQUE (workspace_id, legacy_vector_id) (migrations 143/144) is not the iw2 arbiter. Residual 23505 is swallowed the same way (related: Race condition on legacy_vector_id unique index #374, #374 fix confirmed occurring in normal unattended processing — persistent (non-transient) entity collision, retry does not clear it #377, iw2-fleet-embedding-backfill silently drops ~99.7% of relationship rows while self-reporting failed_count: 0v #363).
5. ensure_job_row is ON CONFLICT DO NOTHING. claim_lease requires matching step_sha384. iw2/W3 DESCRIPTOR_DEF strings were not changed in 139 (correct — field jobs stay claimable). Future descriptor edits without a schema_generation bump will lock out in-flight jobs.

Proposed fix options (ranked)

1. P0 — Keep within-batch last-write-wins on the arbiter (already on HEAD). Required. Do not switch provenance upsert to DO NOTHING (loses legacy_vector_id stamp).
2. P0 — SQL belt-and-suspenders: wrap UNNEST in SELECT DISTINCT ON (e) … ORDER BY e (and the rel/report arbiters). Survives a missed Rust collapse. Same pattern as SPEC-110 DISTINCT ON (doc_id).
3. P0 — Do not treat 21000 as “skip this batch and advance.” If 21000 still fires: log colliding keys, do not advance last_id, retry after forced dedupe or fall back to per-row upsert. Swallow-and-advance silently drops residue (the iw2-fleet-embedding-backfill silently drops ~99.7% of relationship rows while self-reporting failed_count: 0v #363 failure mode).
4. P1 — W3 expected vs copyable: either count missing-spine skips in failed_count or make expected = copyable rows (legacy id and chunks spine). DROP 126 must keep the stricter uncovered predicate (fail-closed). Advisor should show uncovered_chunk split: missing_spine vs missing_embedding.
5. P1 — Smaller batches are not a fix. They only reduce alias collision probability. Normalize-join still collapses names inside any LIMIT.
6. P2 — Unique constraints already exist ((model_id, entity_id) plus partial legacy_vector_id). Adding another unique index does not fix 21000; the proposed set must be unique on the named arbiter.
7. P2 — Ops pin: API + CLI + image tag ≥ 0.26.3, EDGEQUAKE_MIGRATION_MODE=automatic, wait migrate guard GREEN, then --confirm-drop. Backup before DROP.

Acceptance / how to test

Existing (must stay green):

# requires DATABASE_URL / postgres feature
cargo test -p edgequake-storage --features postgres --test e2e_spec139_engine -- --nocapture --test-threads=1
make spec139-migrate-engine-proof

In particular:

• e2e_spec139_01_iw2_within_batch_dedupe_avoids_21000 — raw UNNEST of (eid, eid) must raise 21000; patched job writes one typed row + stamps legacy_vector_id
• e2e_spec139_02_iw2_relationship_normalize_dedupe
• W3 coverage-sum + reclaim_verify_failed_jobs tests in the same file
• contract_spec139_dedupe_last_write_wins_and_order

Add / require for this issue:

• Prod-like fixture: ≥2 eq_*_vectors tables + alias pair entity:Acme Corp Ltd / entity:ACME_CORP_LTD in one keyset batch → no 21000, typed row exists, engine does not terminate
• W3 verify actual is SUM(per-table coverage), never max(COUNT(*) FROM chunk_embeddings)
• passes() default ignores sampled mismatches; EDGEQUAKE_MIGRATION_VERIFY_EQUALITY=1 still enforces 0
• Missing-spine leftover is visible (failed_count or advisor), not a silent failed: 0 + eternal RED
• 21000 after swallow does not advance the keyset cursor
• edgequake guard can reach GREEN after copy on colliding aliases; --confirm-drop then applies 126/131 without W4/IW2 ABORT
• DROP bodies 125 / 126 / 131 unchanged
• Confirm /health.version on the serving API matches the CLI cut (≥ 0.26.3)

Related

• Internal: specs/139-issue-migration/, specs/091-simplify-data-layer/07-migration-engine.md
• Prior same SQLSTATE class: SPEC-110 (118/121), SPEC-098, iw2-fleet-embedding-backfill silently drops ~99.7% of relationship rows while self-reporting failed_count: 0v #363 (iw2 silent relationship drop), Race condition on legacy_vector_id unique index #374 / #374 fix confirmed occurring in normal unattended processing — persistent (non-transient) entity collision, retry does not clear it #377 (legacy_vector_id 23505)
• CHANGELOG: 0.26.3 “SPEC-139 — mid-cutover engine stall”

Operator workaround (until a follow-up lands)

1. Confirm the API image is ≥ 0.26.3 (curl -s $API/health | jq -r .version). 0.26.5 CLI + 0.26.1 API will still 21000-terminate.
2. EDGEQUAKE_MIGRATION_MODE=automatic and let the engine run (W3 reclaim + iw2 dedupe).
3. Do not --confirm-drop until edgequake migrate guard is GREEN.
4. If W3 still fails with actual < expected after 0.26.3+, inspect leftover {uuid}-chunk-{n} keys that have no public.chunks row (missing spine / W1), not the old MAX-accounting bug.