Drizzle: no standalone repair for a broken encrypted ALTER COLUMN migration

[tobyhede]

Follow-up to #693 (fixed in the fix/drizzle-v3-alter-column work). That change made rewriteEncryptedAlterColumns match the EQL v3 domain family and wired the sweep into stash eql migration --drizzle, which is the only v3 command that had a natural place for it. That is a bounded improvement, not a complete fix — this issue records the gap.

The sweep runs at the wrong moment

In the intended v3 flow the sweep sees an empty directory:

1. stash eql migration --drizzle   -> writes 0000_install-eql.sql, sweeps (nothing there yet)
2. edit the schema to use v3 columns
3. drizzle-kit generate            -> writes 0001 with the broken ALTER COLUMN
4. drizzle-kit migrate             -> fails

The broken migration is created at step 3, after the sweep has already run. Today the only way to get it repaired is to run stash eql migration --drizzle a second time at step 4 — which works, and is how people actually find this, but it means generating a redundant install migration to trigger an unrelated repair. The same shape applies to the v2 path (stash eql install --drizzle, install.ts step 5); this is not new to v3.

Proposal

A standalone repair that a user can run at step 4:

• stash eql repair --drizzle (or similar), sweeping the migrations directory and reporting what it rewrote; or
• fold the check into stash encrypt plan, which already inspects intent vs. observed state and is the command a user is likely to reach for next.

Either removes the need to regenerate an install migration to get the repair.

Known hazard to handle in the design

The sweep is unfiltered — it rewrites any matching file regardless of whether it has already been applied. That is safe for essentially every match, because a matching statement is un-runnable by construction (Postgres rejects both the mangled "undefined"."eql_v3_*" form and the valid-looking bare form, since there is no cast from text/numeric to an EQL domain), so the migration failed on apply and is unapplied.

The exception: a jsonb column changed to a v3 domain coerces successfully, so that migration can be applied. Rewriting it afterwards changes the file's hash, and drizzle-kit migrate would then treat it as unapplied and re-run an ADD/DROP/RENAME sequence against a table already in the target shape.

Narrow, and pre-existing in the v2 sweep, but a standalone repair command is the right place to fix it — it can consult the drizzle journal (and, given --database-url, __drizzle_migrations) and refuse to touch an applied migration, which the offline migration-first path cannot.

Notes

An ordering filter (only sweep entries after the install migration) was considered and rejected: whether the broken migration sorts before or after the install migration depends on when each was generated, so the filter would suppress the repair in exactly the recovery path above.

[tobyhede]

Two further review observations on the shared rewriter (rewriteEncryptedAlterColumns) belong to this follow-up rather than the #693 bugfix, because they are limitations of the long-standing v2 behaviour that the v3 change inherits — not defects introduced by it. A standalone repair command is the right place to address both:

1. Column metadata / dependent constraints are not preserved. The ADD COLUMN + DROP + RENAME sequence is equivalent to DROP+ADD: even on an empty table it does not carry over NOT NULL, DEFAULT, indexes, FKs, or comments on the original column. (In practice a plaintext→encrypted change rarely keeps those, since the encrypted column holds an opaque EQL envelope — but the tool shouldn't imply they survive.) A repair command that can read the schema could preserve or re-emit them, or refuse and require an explicit staged migration.

2. Matching is not SQL-context-aware. ALTER_COLUMN_TO_ENCRYPTED_RE matches anywhere in the file, so an ALTER COLUMN … SET DATA TYPE eql_v3_* sitting inside a -- line comment, a /* */ block comment, or a quoted string literal would be rewritten. The risk is near-zero on drizzle-kit-generated files (it never emits such statements in comments/strings), which is why the inline regex is acceptable for the generate-time sweep — but a robust repair command should tokenize and skip non-executable contexts, with regression coverage for all three.

Both were raised by CodeRabbit on the #693 PR and deliberately deferred there to keep that change scoped to "give v3 the rewriter v2 already has."

[tobyhede]

Follow-up scope surfaced in PR #728 review: the in-place ALTER COLUMN rewrite (renderSafeAlter) drops the column's NOT NULL, DEFAULT, UNIQUE, and indexes. The PR only warns about this in the emitted comment; actual preservation belongs here, not in the text-pass rewriter, because it needs a source of truth the rewriter doesn't have.

Why it can't live in renderSafeAlter. That function sees only the single ALTER COLUMN ... SET DATA TYPE statement, which carries none of the column's constraints. The tractable source is the drizzle snapshot already on disk — drizzle/meta/*_snapshot.json holds each table's columns (notNull, default) plus indexes, uniqueConstraints, checkConstraints, foreignKeys. A repair command that consults the journal/snapshot (which this issue is already about) is the right home. Catalog introspection is the alternative but the rewriter runs offline before the migration is applied.

Even with the data, only a subset is recoverable — this is the encryption model, not effort:

• UNIQUE / indexes — recoverable. Indexes on EQL domains work; re-emit CREATE UNIQUE INDEX / ADD CONSTRAINT after the RENAME.
• DEFAULT — impossible. A plaintext default ('') can't apply to an encrypted domain; the value would have to be a ZeroKMS envelope, which isn't a SQL literal (same reason the commented UPDATE placeholder is dead).
• NOT NULL — impossible in the same migration. The new column starts NULL on cutover, so NOT NULL fails on apply.
• value-dependent CHECK / foreign keys — impossible / nonsensical on ciphertext.

Scoped task: in the repair command, read the target column's prior definition from the drizzle snapshot and (a) re-emit recoverable indexes and unique constraints on the encrypted column, and (b) name the unrecoverable ones explicitly ("re-added your indexes; you must manually handle NOT NULL/DEFAULT because they can't exist on an encrypted column") instead of the current blanket "not carried over" warning.

Note this is a compromise for the empty-table in-place path only. For populated tables the staged stash encrypt lifecycle (add -> backfill -> cutover -> drop) already preserves every constraint correctly by keeping the original column alive until cutover — so this constraint-reconstruction work is strictly for users who take the in-place shortcut.