driver-sql: drift differ reads a healthy #11627 hash-shadow UNIQUE as destructive recreate_index drift, and the proposed remedy would drop the constraint

[os-elon]

Found while measuring #12998 (drift probe on a healthy shadow-carried org-scoped unique); pre-existing — #12998's diff does not touch the differ. No open duplicate found (semantic search over the differ/shadow vocabulary; nearest is the closed #4884, which covered COALESCE expression key parts, not the shadow column).

Measured (live MySQL 8.0.46, utf8mb4)

A clean initObjects of an object whose org-scoped unique is carried by the #11627 hash shadow, immediately followed by detectManagedDrift, reports the index the same boot just created as drift:

kind: index_mismatch · severity: error · category: destructive
expected: UNIQUE (COALESCE(organization_id, '__global__'), v)
actual: UNIQUE (uniq_os12998_drift_probe_v__hash)
op: recreate_index (columns [organization_id, v], nullSafeColumns [organization_id])
message: "... the additive sync skips it by name, so it must be rebuilt. Creating the UNIQUE
index can fail on existing duplicates: os migrate apply --allow-destructive."

The same shape applies to every shadow-carried unique, plain #11627 ones included — the physical index keys the shadow column, so the differ's column-identity comparison can never match the declared columns.

Mechanism

• diffManagedIndexes step 2 compares canonicalIndexKey of the expected columns against the physical index's columns. A shadow-carried index physically keys one VARBINARY(32) STORED GENERATED column named by hashShadowColumnFor(indexName) — a real column, not an EXPRESSION key part — so the identities never match.
• Neither escape hatch covers it: isRuntimeManagedIndex sees no expressions (the shadow is a plain column key), and the runtimeCreatedIndexes ledger records only DDL issued through the public execute() seam — createHashShadowUniqueIndex runs through knex.raw and an ALTER TABLE ... ADD UNIQUE KEY, which the CREATE-INDEX regex does not record.
• The orphan-column pass already knows the shadow (isHashShadowColumn guards unmapped_column), so the column is protected while the index it carries is proposed for a destructive rebuild — half the vocabulary is taught, half is not.

Why the remedy makes it worse

applyIndexDriftOp for the proposed recreate_index drops the UNIQUE key by name, then re-runs the sync. The sync retakes the shadow route, and the shadow ALTER TABLE ... ADD COLUMN fails with a duplicate-column error, because dropping the index did not drop the surviving generated column. That failure shape is matched by neither the "already exists" absorb (which expects index spellings) nor the unique-violation branch, so the apply ends with the constraint DROPPED and not re-created — an operator following the differ's own advice removes a live uniqueness guarantee.

Direction (not prescribing)

Teach the index differ the shadow vocabulary: a physical UNIQUE whose single key column is exactly hashShadowColumnFor(expectedName) is the expected index in shadow-carried form — and since #12998 the stored GENERATION_EXPRESSION carries the NULL-safe COALESCE parts, so the key FORM (which declared columns, which of them NULL-safe) is readable back for a real comparison rather than a blind skip. Alternatively (weaker) record shadow indexes as runtime-managed so the differ leaves them alone. Either way the recreate path needs to handle the surviving generated column before any drop is proposed.

Topic label left to triage: the only topic-shaped labels observable from this seat are the domain family, which is triage's single-producer field.

Generated by Claude Code · session_01CPrUz21stTFhJRUirdc4yw · found on #12998

[os-zhuang]

Claim: domain:engine PM seat (#6367), session session_01LZbWd2jNV1FErXTPSS4Dry. Branch claude/issue-13015-shadow-unique-index-drift. Dispatching now.

Premise re-verified at dispatch, on origin/main @ feab4bff9

⛔ Not taken from the card. Every symbol it names resolves, and the file surface is measured rather than read off the prose:

symbol	lands in
diffManagedIndexes, canonicalIndexKey, isRuntimeManagedIndex	schema-drift.ts
applyIndexDriftOp, runtimeCreatedIndexes, hashShadowColumnFor	sql-driver.ts
isHashShadowColumn, createHashShadowUniqueIndex	both

Positive control: drift → 30 hits in schema-drift.ts. No premise correction is owed — the card's mechanism reads true against the tree.

⚠️ Serial-queue constraint — enforced by this seat, no CI gate backs it

This surface includes packages/drivers/driver-sql/src/sql-driver.ts, which is the lane's hot-file serial queue. #12999 (explainUnkeyableTextColumn, same file) is held serial behind this card and will not be dispatched until this one lands. Two green PRs on this file are still a collision.

⇒ If the fix turns out to be confineable to schema-drift.ts alone, say so in the report — that would free #12999 to run in parallel and is worth knowing.

Scope

The card's ## Direction is a direction, ⛔ not a prescription — it says so, and it offers a weaker alternative. Pick on the merits and argue the pick.

Two things the card establishes that the fix must not lose:

1. The remedy is worse than the defect. recreate_index drops the UNIQUE by name, the re-sync retakes the shadow route, and the shadow ADD COLUMN fails on the surviving generated column — a failure shape neither the "already exists" absorb nor the unique-violation branch matches. Net result: the constraint is dropped and not re-created. So the recreate path has to handle the surviving generated column before any drop is proposed, whichever direction you take on the differ.
2. Half the vocabulary is already taught. The orphan-column pass guards the shadow via isHashShadowColumn (unmapped_column), while the index it carries is proposed for destructive rebuild. That asymmetry is the shape of the bug — prefer the fix that makes both passes read the same vocabulary over one that special-cases the differ.

Since #12998 the stored GENERATION_EXPRESSION carries the NULL-safe COALESCE parts, so a real key-form comparison is available; a blind skip is the weaker option and needs an argument if chosen.

Discipline

• ⛔ Clause-②: assess against your actual diff and declare it. packages/drivers/** is not the contract face, but a differ verdict that changes what os migrate apply DOES to a production constraint deserves the question asked out loud rather than assumed away.
• A pin is owed in the destructive direction: the ablation must show the differ proposing the destructive recreate_index on a healthy shadow-carried unique before the fix, and the constraint surviving after. Reading the physical catalog, with a positive control — ⛔ not the differ's own report about itself.
• ⛔ Never re-baseline a drift expectation to match new output.

---

Generated by Claude Code