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

[tobyhede]

Status — re-verified 2026-07-30 against main (2d7ef563)

Reopened 2026-07-30. This was closed 2026-07-22 as "Fixed in #728", but #728 did not implement it: its closingIssuesReferences is [693], not this issue, and its body carries a section headed "Not in scope (filed as #710**)"** which reads: "The complete fix is a standalone repair command (stash eql repair --drizzle, or a check in stash encrypt plan)… Filed as #710 rather than expanding this PR's surface."

The ask remains unmet on main (2d7ef563). packages/cli/src/cli/registry.ts declares only eql install (:307), eql migration (:325), eql upgrade (:364) and eql status (:369) — no repair. The sweep is not folded into stash encrypt plan: packages/cli/src/commands/encrypt/plan.ts is 61 lines that read .cipherstash/migrations.json and never touch a drizzle output directory. rewriteEncryptedAlterColumns has exactly two production call sites repo-wide — packages/cli/src/commands/eql/migration.ts:270 and packages/wizard/src/lib/rewrite-migrations.ts:1312 (reached from post-agent.ts's sweepMigrationDirs).

Everything below has been re-verified against 2d7ef563; the earlier revision of this issue was written against c8b1325a, since superseded by #838/#839/#840. Sections corrected in that pass are marked.

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. The only way to get it repaired today 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.

Corrected 2026-07-30 — the v2 comparison is dead. An earlier revision noted "the same shape applies to the v2 path (stash eql install --drizzle, install.ts step 5); this is not new to v3." That flag no longer exists: packages/cli/src/commands/db/install.ts:41 returns "eql install --drizzle has been removed. Generate an EQL v3 Drizzle migration with stash eql migration --drizzle". This strengthens the case rather than weakening it — there is now exactly one command in the CLI that can run the sweep, and reaching it still requires generating a migration the user does not want.

Proposal

A standalone repair 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. The standalone command is the stronger option: it is the only one of the two that has a natural place to accept --database-url and consult applied state (see below).

Known hazard to handle in the design

Corrected 2026-07-30 (twice). The original text described a hash-based re-run mechanism that does not exist; that correction stands and has now been extended to the actual command in the repro. A second correction narrows the jsonb exception.

The sweep is unfiltered — it rewrites any matching file regardless of whether it has already been applied. It reads neither meta/_journal.json nor __drizzle_migrations; its only exclusion is options.skip, the single file eql migration just wrote. That is safe for essentially every match, because a matching statement is un-runnable by construction: EQL v3 columns are Postgres domains over jsonb, so there is no assignment cast from text/numeric to one, and the mangled "undefined"."eql_v3_*" form names a schema that does not exist. Either way the migration failed on apply and is unapplied.

The jsonb exception, narrowed. A jsonb column changed to a v3 domain is base-type-compatible, so the ALTER can succeed — but only when the column is empty, or already holds valid EQL envelopes. The domains carry a CHECK on the envelope shape (skills/stash-postgres/SKILL.md:8,43,50 — the failure reads value for domain eql_v3_… violates check constraint), so on a populated plaintext-jsonb table the ALTER fails that CHECK and the migration is unapplied like every other case. The applied-migration hazard is therefore real but confined to empty tables — which is exactly the case the in-place rewrite targets, so it is not a rare corner.

Why the original "it will re-run" claim was wrong. Drizzle's applied-check is timestamp-based, not hash-based — drizzle-orm@0.45.2 pg-core/dialect.js:

62:  if (!lastDbMigration || Number(lastDbMigration.created_at) < migration.folderMillis) {
67:      sql`insert into … ("hash", "created_at") values(${migration.hash}, ${migration.folderMillis})`

The hash is written on insert at :67 and never read for the decision; folderMillis comes from the journal's when (migrator.js:22). The sweep does not touch meta/_journal.json, so nothing re-runs on the original database. Extended 2026-07-30: this was originally verified only against the ORM, while the repro flow is drizzle-kit migrate. drizzle-kit 0.31.10's bin.cjs delegates to drizzle-orm/<driver>/migrator for every Postgres driver (:78880 node-postgres, :78951 postgres-js, :79001 vercel-postgres, :79080 neon-serverless, :78829 pglite, :78767 aws-data-api), so the same timestamp logic governs the command users actually run.

The real residual hazard is cross-environment drift. Rewriting an already-applied migration leaves the .sql describing a shape the database never got from it. Dev, which applied the original, has payload eql_v3_json. A fresh staging or CI database replaying the rewritten file gets payload jsonb + payload_encrypted. The two environments silently diverge, and nothing in the migration history records that they should.

Partly mitigated on main, guidance-only. #839 added describeStagedReconciliation (packages/cli/src/commands/db/rewrite-migrations.ts:916-955), which names the post-sweep divergence precisely — database has both columns, schema.ts and meta/*_snapshot.json know neither — and walks the user through reconciling. That addresses the schema-vs-SQL divergence a sweep leaves behind. It does not address this one: nothing refuses to rewrite an already-applied migration, so the cross-environment drift is unmitigated. A standalone repair command is still the right place — 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.

What is no longer in scope

Both follow-ups recorded in the comments below have been overtaken by #823, #836 and #839, and are not part of this issue's remaining work:

1. Column metadata / dependent constraints not preserved — moot. The rewrite is add-only since Make the Drizzle EQL migration rewriter non-destructive and fail closed #823: renderSafeAlter (rewrite-migrations.ts:1213-1240) emits a single ALTER TABLE … ADD COLUMN "<col>_encrypted" and preserves the source column. Nothing is dropped or renamed, so there is nothing to carry over. The scoped task of re-emitting recoverable indexes/uniques and naming the unrecoverable NOT NULL/DEFAULT falls away with it, and the guidance half is now covered by describeStagedReconciliation.
2. Matching is not SQL-context-aware — done. isInsideCommentOrString gates the rewrite pass and five of the six index scans; Drizzle migration rewriter: correctness follow-ups to #823 #836 closed the remaining dollar-quoted-DDL hole.

Remaining scope

A standalone repair entry point, plus applied-migration awareness: read meta/_journal.json offline, and __drizzle_migrations when --database-url is supplied, and refuse to rewrite a migration that has already been applied rather than silently creating cross-environment drift.

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]

SUPERSEDED 2026-07-30. Both items below have since been implemented and are no longer part of this issue. Item 1 (column metadata / dependent constraints) is moot — #823 made the rewrite add-only, so nothing is dropped and there is nothing to preserve. Item 2 (SQL-context-aware matching) is done — isInsideCommentOrString gates the rewrite pass and five of six index scans, and #836 closed the dollar-quoted-DDL hole. Original text preserved below.

---

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]

SUPERSEDED 2026-07-30. The scoped task below no longer applies. It was written against the ADD/DROP/RENAME emission, which #823 replaced with the add-only renderSafeAlter — the source column is preserved, so no constraint is lost and none needs reconstructing. The guidance half is now covered by describeStagedReconciliation (#839). Original text preserved below.

---

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.

[auxesis]

Fixed in #728.

[tobyhede]

Verification pass, 2026-07-30 — main @ 2d7ef563

Re-verified every claim in the body against current main rather than the c8b1325a the previous revision was written against (19 commits stale — #838/#839/#840 have landed since). The body has been updated; this comment records the evidence.

Confirmed — the reopen is correct.

Claim	Evidence
#728 does not close this	closingIssuesReferences is [693]; body carries the verbatim "Not in scope (filed as #710)" section
No repair command	registry.ts declares eql install (:307), eql migration (:325), eql upgrade (:364), eql status (:369) — no repair
Not in encrypt plan	commands/encrypt/plan.ts is 61 lines reading .cipherstash/migrations.json; never touches a drizzle out dir
Two production call sites	commands/eql/migration.ts:270, wizard/src/lib/rewrite-migrations.ts:1312
Sweep cannot filter on applied state	no _journal.json / __drizzle_migrations reference in the rewriter; only exclusion is options.skip
Rewrite is add-only	renderSafeAlter (:1213-1240) emits one ADD COLUMN, no DROP/RENAME

Drizzle's applied-check — correction verified, and extended. drizzle-orm@0.45.2 pg-core/dialect.js:62 is exactly if (!lastDbMigration || Number(lastDbMigration.created_at) < migration.folderMillis), with the hash written only at :67 and never read; folderMillis comes from the journal's when (migrator.js:22). That was verified against the ORM API, but the repro flow is drizzle-kit migrate — so I checked drizzle-kit too. 0.31.10's bin.cjs delegates to drizzle-orm/<driver>/migrator for every Postgres driver (:78880 node-postgres, :78951 postgres-js, :79001 vercel-postgres, :79080 neon-serverless, :78829 pglite, :78767 aws-data-api). The timestamp logic governs the actual command. Hash drift causes no re-run.

Three corrections folded into the body.

1. The v2 comparison is dead. stash eql install --drizzle was removed — commands/db/install.ts:41 returns a removal message pointing at eql migration --drizzle. There is now exactly one command that can run the sweep, which strengthens the case for a standalone repair.
2. The jsonb exception is narrower than stated. EQL v3 columns are domains over jsonb with a CHECK on the envelope shape (skills/stash-postgres/SKILL.md:8,43,50), so jsonb → eql_v3_* only applies successfully on an empty table or one already holding valid envelopes. On a populated plaintext-jsonb table the ALTER fails the CHECK and the migration is unapplied like every other case. Still not a rare corner — empty tables are exactly what the in-place rewrite targets. (Inferred from the skill, not read from the DDL: @cipherstash/eql@3.0.2 ships only dist JS; the SQL is fetched at install time.)
3. The drift hazard is half-addressed. fix(cli,wizard): correctness follow-ups to the Drizzle EQL migration rewriter #839's describeStagedReconciliation (:916-955) names the post-sweep schema-vs-SQL divergence and walks the user through reconciling it. It does not touch the cross-environment case — nothing refuses to rewrite an already-applied migration.

Both earlier comments are superseded and have been annotated as such. #823 (add-only renderSafeAlter) makes the constraint-preservation work moot, and isInsideCommentOrString plus #836 close the SQL-context-awareness gap.

Remaining scope is now just: a standalone repair entry point, plus applied-migration awareness — read meta/_journal.json offline and __drizzle_migrations when --database-url is given, and refuse to rewrite an applied migration rather than silently creating cross-environment drift.