sys-metadata's declared overlay index shape diverges from the migration-owned partial form — every composed migrate plan proposes a recreate_index that would break draft/active coexistence

[hotlong]

From cloud#1736's audit (control-plane drift): the framework's overlay-index migration owns idx_sys_metadata_overlay_active and installs a PARTIAL, NULL-safe form (WHERE state='active', COALESCE(package_id,'')), but sys-metadata.object.ts DECLARES a plain index under the same name. Consequences: (1) os migrate plan proposes recreate_index [destructive] on every run against a DB carrying the migration's form; (2) if anyone ever applies it, the plain form collides draft with active and leaves package-less overlays unconstrained — and because syncDeclaredIndexes skips by NAME, the framework migration would never repair it. Deliverable: make the DECLARED shape carry the partial/NULL-safe form (or teach the declaration vocabulary to express it), so the plan stops proposing the wrong index. Consumer evidence: cloud#1736 table, findings on sys_metadata.

[os-zhuang]

PM triage — seat session_01LZbWd2jNV1FErXTPSS4Dry (PM seat #6367, domain:engine lane). ⚠️ Not dispatching this, because the stated mechanism does not reproduce at current main (e22158fff), and the deliverable as written is measured impossible. Both readings below, so a later seat does not repeat the work.

1. The divergence is deliberate and documented on both sides — not an oversight

packages/metadata-core/src/objects/sys-metadata.object.ts:221-230 already carries a comment explaining the plain declaration, in the card's own terms — it calls itself "the coarser fallback that a driver without that runtime migration gets: it still prevents duplicate ACTIVE overlays, at the cost of also colliding with archived/reset rows."

The reason it cannot be otherwise is recorded at packages/metadata-protocol/src/migrations/overlay-index.ts:15-19: syncDeclaredIndexes materializes declarations through knex's table.unique(), which "can express neither a WHERE nor a COALESCE."

So the card's first branch — "make the DECLARED shape carry the partial/NULL-safe form" — cannot be done at the materializer as it stands. Only the parenthetical second branch ("or teach the declaration vocabulary to express it") is open, and that is a contract widening of the index-declaration vocabulary, not a p1 bugfix. It would change this card's tier and its review path.

2. Consequence (1) is already carved out — including on a cold process

packages/drivers/driver-sql/src/schema-drift.ts:1769-1777 skips this exact index before it can emit a recreate_index, with a comment that quotes the declaration's own "fallback shape" sentence and concludes "Not ours to reconcile (#4884)."

The carve-out is isRuntimeManagedIndex (:1262-1268), and its two limbs matter separately here:

1. runtimeCreated?.has(index.name) — process-local. runtimeCreatedIndexes is an in-memory Map on the SqlDriver instance (sql-driver.ts:7951), populated only when this very process executed the CREATE INDEX. A cold os migrate plan has it empty, so this limb does not fire for the card's scenario.
2. !isSyncReproducibleIndex(index, tenantField) — and :1242 is if (index.partial === true) return false;. The migration-installed index is partial, so this returns false, the negation is true, and the index is carved out anyway.

Limb 2's docblock (:1258-1260) says it is there for precisely this case: "durable across restarts, and the only witness available on the second boot, when the runtime ledger starts empty again." A cold planning process is that second boot.

I also found no drift differ on the CLI side at all: detectSchemaDrift / diffManagedIndexes / schema-drift have zero non-test importers under packages/cli/src and packages/metadata/src, and recreate_index is produced nowhere in packages/cli/src, packages/metadata/src, or packages/runtime/src. The op can only come from the driver differ above — the one that skips.

What would make the card true, and what I need

The reading that survives is a DB where the physical index is not the partial form — i.e. the CREATE degraded or failed, which overlay-index.ts's own header documents as a real historical outcome (#6418: an ordering defect that could leave sys_metadata with no unique index and no log line). On such a DB limb 2 does not fire, the physical index is sync-reproducible, and recreate_index would be proposed — matching the audit.

If that is what cloud#1736 observed, the card is real but its title is aimed at the wrong object: the defect would be the degraded physical state, not the declared shape, and the fix is repair/detection of that state rather than teaching the declaration a partial form.

@hotlong — to move this, could you say from cloud#1736 which of these the audited DB actually had:

• the physical idx_sys_metadata_overlay_active as the partial WHERE state='active' / COALESCE(package_id,'') form (⇒ my reading says no recreate_index, and I would want the plan output that shows one), or
• some other physical shape — plain, missing, or partially built (⇒ card is real, and I will re-scope it to the degradation and re-dispatch).

The os migrate plan output for sys_metadata from that audit would settle it in one paste. Leaving this on pm:queue, unassigned, until then — ⛔ dispatching it as written would ask a dev to either do the impossible branch or silently widen a spec vocabulary.

---

Generated by Claude Code