D1: auto-migrate referenced-table rebuilds via FK cascade (follow-up to #226)

[dmealing]

Follow-up to #226. That issue is being fixed by detect-and-refuse: meta migrate --dialect d1 now throws a clear generation-time error instead of silently emitting a migration that fails against production when it would rebuild a foreign-key-referenced table (design: docs/superpowers/specs/2026-07-27-d1-referenced-table-rebuild-refuse-design.md).

This issue tracks the deferred auto-fix: instead of refusing, generate a cascade that actually applies on remote D1.

What was prototyped (Appendix A of the #226 design)

A cascade works, validated against libSQL modelling remote D1 (batch in one implicit transaction, foreign_keys=ON):

• rebuild each referencing child without its FK -> rebuild the parent -> restore the children's FKs, all in one pass;
• defer_foreign_keys=ON as a belt; temp tables reference each other's temp names (SQLite resolves FK targets lazily) and RENAME rewrites them to final names;
• two load-bearing ordering rules: referrers-first DROP, parents-first RENAME.

Validated for single-parent, transitive (g→c→p), multi-child, and self-referential graphs — data intact, FKs re-enforced.

Why it was deferred

Disproportionate for a niche dialect's edge case: needs a reverse FK-graph + topological sort + self-ref special-casing, and multi-table cycles (A↔B) have no reliable atomic sequence and would still need refusing. Detect-and-refuse removes the dangerous (silent, production) behavior at ~10% of the cost.

If picked up

• Build the reverse FK-graph + topological order over the affected set (recreate set ∪ transitive referrers).
• Emit the validated cascade; keep refusing genuine multi-table cycles.
• Gate the migrate-doctrine way: emit → apply to a real engine (libSQL, batch-in-one-transaction to model remote D1) → re-diff EMPTY, per topology.

[dmealing]

Residual under-refuse gap in the #226 detect-and-refuse fix (to fold into this cascade work)

The shipped #226 refuse detects referenced-table rebuilds from the expected (target) schema's foreign keys only. A final-review pass found one narrow, order-dependent case this misses:

A single migration that both rebuilds a parent (e.g. an add-check / column-type change) and drops the child's FK to that parent. In the target schema the FK is gone, so the parent isn't refused — but both tables land in the recreate set, and if the parent's DROP TABLE is emitted before the child's rebuild, on remote D1 the old child still holds the live FK → the exact silent FOREIGN KEY constraint failed returns.

It can't be closed cheaply within detect-and-refuse: renderD1 never receives the actual schema, and a drop-fk change carries only the FK name (its refTable lives only in the optional restore?: FkDescriptor), so the actual-side FK graph can't be reliably reconstructed from changes alone.

When this cascade work is picked up, the actual-schema FK graph gets plumbed into the D1 emitter anyway (the cascade needs it) — which also closes this gap. Until then it is documented as a known limitation in docs/features/migrations-and-drift.md. Decision (2026-07-27): document + track here rather than build the actual-schema plumbing as part of the #226 patch.

[dmealing]

Shipped in npm 0.20.8 (14-package lockstep; PyPI/NuGet/Maven Central unchanged — D1 is a TS-only dialect). Tag v0.20.8; release commit f0a3fd9c, docs dcc66948, site bdd23e5.

meta migrate --dialect d1 now auto-generates an appliable foreign-key cascade to rebuild an FK-referenced table on remote D1 (drop referrers-first, rename parents-first, under PRAGMA defer_foreign_keys = ON), replacing #226's refusal. The affected set is keyed on the union of the actual and expected FK graphs, which closes #226's residual under-refuse gap (a single migration that both rebuilds a referenced table and drops the referencing FK). Multi-table FK cycles are still refused; self-references are handled. Byte-identical for any migration that does not rebuild an FK-referenced table.

Verified by a libSQL real-engine gate (one-transaction = remote-D1 model) across all topologies + the #226 gap + a mixed-migration splice + cycle refusal — each asserting applies-cleanly, row data intact, FK re-enforced, and a re-diff EMPTY. Follow-up #243 tracks a pre-existing dependent-view / Pass-2c interaction (narrow; docs qualified accordingly).