@ultimat3/db - npm Package Compare versions

Comparing version 15.0.0 to 16.0.0

src/dependent-view.ts

+// Single responsibility: refuse a migration whose `alter column … type` a VIEW is compiled against,
+// before the statement is sent — and name the view, the column and the statement that recreates it.
+//
+// **This is the honest ceiling for views, and the reason it is not in the generator.** `x db gen`
+// runs with no database open; `SchemaDescription` has no field for a view; `introspect()` reads
+// none by construction (`app-relation.ts` excludes every non-table relation); and no `entity()` can
+// declare one. So nothing the generator reads knows a view exists, and a `GenerateOptions.views`
+// with no caller to fill it is the declared-and-never-wired defect this release exists to
+// eliminate. What DOES have a connection is `migrate()`, one statement before the abort — and the
+// catalog answers the question exactly, including for a view no migration in this repo wrote.
+//
+// Measured on 18.4: `alter table "dv_docs" alter column "rank" type text using "rank"::text` under
+// a view selecting that column answers `0A000 cannot alter type of a column used by a view or
+// rule`, with `rule _RETURN on view dv_docs_published depends on column "rank"` in a DETAIL field
+// nothing printed — surfaced as `X_DB_UNAVAILABLE: cannot reach the database`, whose registered
+// `fix:` says to set `DATABASE_URL`.
+//
+// It does not repair anything and does not claim to: the deploy still stops. What it replaces is
+// wrong advice about a healthy database with the two statements that unblock it.
+
+import type { DbClient } from './client';
+import { migrationViewDepends } from './migration-errors';
+import { identifier, join, sql } from './sql';
+import { IDENTIFIER_PART, noiseAt } from './sql-scan';
+import { statementsOf } from './statement-split';
+
+/** One `alter table <table> alter column <column> type …`, as the catalog spells both names. */
+export interface RetypeTarget {
+  readonly table: string;
+  readonly column: string;
+}
+
+/**
+ * One name in a statement. Called a WORD and not the obvious lexer noun deliberately:
+ * `scripts/secret-compare.ts` reads a comparison whose operand is NAMED like a credential, and
+ * that noun is one of the names it reads — a `.text === spelling` under it is indistinguishable
+ * from an auth check to a static rule that has only the name to go on.
+ */
+interface SqlWord {
+  readonly text: string;
+  /** A quoted name is never a keyword — `"type"` is a column called type, not the clause. */
+  readonly quoted: boolean;
+}
+
+/**
+ * The names in one statement, in order, folded the way Postgres folds them: an unquoted identifier
+ * to lower case, a quoted one verbatim. Comments, string literals and dollar-quoted bodies
+ * contribute nothing, through this package's one lexer — `-- alter column` is prose and
+ * `'alter column'` is data.
+ */
+function wordsOf(statement: string): readonly SqlWord[] {
+  const words: SqlWord[] = [];
+  let at = 0;
+  while (at < statement.length) {
+    const noise = noiseAt(statement, at);
+    if (noise !== null) {
+      if (noise.kind === 'identifier') {
+        words.push({ text: statement.slice(at + 1, noise.end - 1), quoted: true });
+      }
+      at = noise.end;
+      continue;
+    }
+    if (!IDENTIFIER_PART.test(statement[at] ?? '')) {
+      at += 1;
+      continue;
+    }
+    let end = at;
+    while (end < statement.length && IDENTIFIER_PART.test(statement[end] ?? '')) end += 1;
+    words.push({ text: statement.slice(at, end).toLowerCase(), quoted: false });
+    at = end;
+  }
+  return words;
+}
+
+const keyword = (word: SqlWord | undefined, spelling: string): boolean =>
+  word !== undefined && !word.quoted && word.text === spelling;
+
+/**
+ * Every column this script retypes. Narrow ON PURPOSE — `alter table <t> … alter [column] <c> type`
+ * and nothing else — because a miss costs exactly what happens today (the server's own `0A000`,
+ * one statement later) while a false positive costs a catalog read and a refusal on a migration
+ * that would have applied. Every retype `generateMigration` emits is this shape; a hand-written
+ * `ALTER TABLE ONLY t …` is not, and is deliberately left to the server.
+ */
+export function retypeTargets(script: string): readonly RetypeTarget[] {
+  const targets: RetypeTarget[] = [];
+  for (const statement of statementsOf(script)) {
+    const words = wordsOf(statement);
+    const table = words[2];
+    if (!keyword(words[0], 'alter') || !keyword(words[1], 'table') || table === undefined) {
+      continue;
+    }
+    for (let index = 3; index < words.length; index += 1) {
+      if (!keyword(words[index], 'alter')) continue;
+      const at = keyword(words[index + 1], 'column') ? index + 2 : index + 1;
+      const column = words[at];
+      if (column === undefined || !keyword(words[at + 1], 'type')) continue;
+      targets.push({ table: table.text, column: column.text });
+    }
+  }
+  return targets;
+}
+
+interface ViewRow {
+  readonly view_name: string;
+  readonly table_name: string;
+  readonly column_name: string;
+  readonly definition: string;
+  /** `v` or `m`. A MATERIALISED view needs different DDL to drop and to recreate. */
+  readonly relkind: string;
+}
+
+/**
+ * `pg_depend` -> `pg_rewrite` is the only edge that records this: a view depends on a column
+ * through its `_RETURN` rule, never through a row in `pg_class` alone. Materialised views are
+ * included (`relkind = 'm'`) because they carry the same rule and fail the same way.
+ *
+ * One round trip for every target, `in` over both name lists, and the exact pairing filtered in
+ * the caller — a per-target query would be a loop of statements inside the migration's own
+ * transaction, and a cross-product read is cheap where a false pair is not.
+ */
+async function dependentViews(
+  client: DbClient,
+  targets: readonly RetypeTarget[],
+): Promise<readonly ViewRow[]> {
+  const tables = join(
+    [...new Set(targets.map((target) => target.table))].map((name) => sql`${name}`),
+  );
+  const columns = join(
+    [...new Set(targets.map((target) => target.column))].map((name) => sql`${name}`),
+  );
+  return client.query<ViewRow>(sql`
+    select distinct v.relname as view_name, c.relname as table_name, a.attname as column_name,
+           pg_get_viewdef(v.oid, true) as definition, v.relkind as relkind
+      from pg_depend d
+      join pg_rewrite r on r.oid = d.objid and d.classid = 'pg_rewrite'::regclass
+      join pg_class v on v.oid = r.ev_class
+      join pg_class c on c.oid = d.refobjid and d.refclassid = 'pg_class'::regclass
+      join pg_attribute a on a.attrelid = c.oid and a.attnum = d.refobjsubid
+     where v.relkind in ('v', 'm') and v.oid <> c.oid
+       and c.relname in (${tables}) and a.attname in (${columns})
+     order by v.relname
+  `);
+}
+
+/**
+ * One SQL statement as a single argv word for `psql -c`.
+ *
+ * SINGLE quotes, unlike `migrationConflict`'s `-c "…"`: `identifier()` writes the view's name in
+ * DOUBLE quotes, so a double-quoted shell word would end at the name. The definition is the
+ * server's own text and may hold a `'` of its own — `where status = 'published'` — so the one
+ * escape a POSIX shell has for it is spelled out here. This is not the SQL literal escape
+ * (`sql.ts`'s `literal()`, the tree's one copy of that); nothing below is sent to a server.
+ */
+const shellArg = (statement: string): string => `'${statement.replaceAll("'", `'\\''`)}'`;
+
+/** The invocation `migrationConflict` already writes, with the statement as its own argv word. */
+const psql = (statement: string): string => `psql "$DATABASE_URL" -c ${shellArg(statement)}`;
+
+/**
+ * The two statements that unblock the deploy, as one line an operator pastes.
+ *
+ * It leads with the command to RUN and carries the follow-up in a `#` comment, the shape
+ * `migrateConcurrent` and `migrationSnapshotMissing` already write. It used to lead with bare DDL
+ * and a `#`: `#` is not a comment in Postgres, so psql read the whole line and failed on it, while
+ * a shell read `drop` as a program that does not exist. Neither reader could run it (axiom 4).
+ *
+ * `identifier()` REFUSES a name holding a quote, a space or a backslash — all three legal inside a
+ * quoted Postgres name — and a `fix:` may not throw: the rule `rebuildForeignKey` already states,
+ * with the same shape. A refusal that raised `X_SQL_UNSAFE` in place of the finding would hand the
+ * operator an exception where a verdict was asked for, over a view name that is perfectly legal.
+ * The fallback still leads with a command that runs — a psql session — because quoting that name
+ * is the one step this package will not do twice: `identifier()` is its only identifier writer.
+ *
+ * The definition is collapsed to one line because `pg_get_viewdef(oid, true)` pretty-prints across
+ * several and a `fix:` is read as a command.
+ *
+ * `relkind` decides the DDL and is not cosmetic: `dependentViews` deliberately selects `'m'` as
+ * well as `'v'`, and Postgres refuses `drop view` on a materialised one — `WRONG_OBJECT_TYPE`,
+ * "use DROP MATERIALIZED VIEW". So the one case the query went out of its way to include was the
+ * one whose `fix:` could not run. `pg_get_viewdef` answers the SELECT for both kinds, so only the
+ * two keywords differ; a matview's indexes and its `WITH DATA` population are NOT carried, and
+ * the fix says so rather than implying the recreate is complete.
+ */
+function restoreView(view: string, definition: string, relkind: string): string {
+  const body = definition.replace(/\s+/g, ' ').replace(/;\s*$/, '').trim();
+  const materialised = relkind === 'm';
+  const kind = materialised ? 'materialized view' : 'view';
+  const note = materialised
+    ? '   # then re-create its indexes: a matview keeps none of them across a drop'
+    : '';
+  try {
+    const name = identifier(view).text;
+    return (
+      `${psql(`drop ${kind} ${name}`)}   # then x db migrate, then: ` +
+      `${psql(`create ${kind} ${name} as ${body}`)}${note}`
+    );
+  } catch {
+    return (
+      `psql "$DATABASE_URL"   # quote the ${kind} name ${JSON.stringify(view)} yourself, then: ` +
+      `drop ${kind} <name>; \\q; x db migrate; and create it again as: create ${kind} <name> as ${body}${note}`
+    );
+  }
+}
+
+/**
+ * Refuse before the ALTER, or return having sent nothing at all. A script that retypes no column
+ * costs one text scan and no round trip, which is every migration an app writes that is not a
+ * retype.
+ */
+export async function refuseDependentViews(client: DbClient, script: string): Promise<void> {
+  const targets = retypeTargets(script);
+  if (targets.length === 0) return;
+  const wanted = new Set(targets.map((target) => `${target.table}.${target.column}`));
+  for (const row of await dependentViews(client, targets)) {
+    if (!wanted.has(`${row.table_name}.${row.column_name}`)) continue;
+    throw migrationViewDepends(
+      row.view_name,
+      row.table_name,
+      row.column_name,
+      restoreView(row.view_name, row.definition, row.relkind),
+    );
+  }
+}

src/index-plan.ts

+// Single responsibility: which indexes an existing table gains, has rebuilt, or LOSES. Split out
+// of `generate.ts` along the seam `check-ddl.ts` and `index-ddl.ts` already drew — `generate.ts`
+// assembles a plan, `index-ddl.ts` writes the statements, and this file decides which of them go
+// in. `checkPlan` is its shape, deliberately: "what does the record hold that the declaration does
+// not" is one question, and a fourth spelling of it is the split axiom 1 refuses.
+//
+// The removal arm is why this file exists. `diffTable` walked `declaredIndexes(entity)` and matched
+// by name, with no reverse pass, so an index the entities stopped declaring stayed on the database
+// forever while the sidecar beside it stopped recording it — `examples/dummy` carried
+// `member_unique_per_org`, `members_tz_idx` and `post_slug_unique_per_org` through every
+// regeneration, and `x verify`'s drift step was green over all three because drift judges the
+// declared side. The same defect `foreignKeyPlan` closed on 2026-08-19, one arm over.
+//
+// KNOWN LIMIT, named rather than half-built: a UNIQUE index that a foreign key on ANOTHER table
+// still references cannot be dropped (2BP01), and this arm sees one table at a time. That
+// declaration is already broken — the key has nothing to point at — and the failure arrives with
+// the server's own words naming both ends.
+
+import type { EntityDescriptionLike } from './entity-shape';
+import type { Plan } from './foreign-key-plan';
+import {
+  asDeclared,
+  createIndex,
+  dropIndex,
+  dropRecordedIndex,
+  impliedByColumnClause,
+  redefineIndex,
+} from './index-ddl';
+import type { TableDescription } from './introspect';
+import { declaredIndexes } from './invariant-ddl';
+
+/**
+ * What the rest of this migration has already done to the columns underneath the indexes — every
+ * field is a set of names some other arm produced, and each answers "this index is already gone".
+ */
+export interface IndexPlanContext {
+  /** Columns this migration ADDS, whose own `unique` clause brings an index Postgres names. */
+  readonly added: ReadonlySet<string>;
+  /** Columns `regenerate` dropped and re-added outright — every index over one went with it. */
+  readonly rebuilt: ReadonlySet<string>;
+  /** Indexes a retype already dropped ahead of its ALTER (`moveDependentsAside`). */
+  readonly moved: ReadonlySet<string>;
+}
+
+/**
+ * Which indexes an existing table gains, has rebuilt, or loses.
+ *
+ * Declared first and removed last, the order `checkPlan` uses. Both orders are safe — two indexes
+ * over the same columns may coexist for the length of one migration — so the tie goes to the file
+ * this one is a copy of.
+ *
+ * `down` is pushed FORWARDS and read backwards, because assembly reverses it: the restore of a
+ * removed index therefore lands after the drop of everything created beside it.
+ */
+export function indexPlan(
+  entity: EntityDescriptionLike,
+  live: TableDescription,
+  plan: Plan,
+  context: IndexPlanContext,
+): void {
+  const indexed = new Map(live.indexes.map((index) => [index.name, index]));
+  const declared = new Set<string>();
+  for (const index of declaredIndexes(entity)) {
+    declared.add(index.name);
+    const recorded = indexed.get(index.name);
+    // A rebuilt column took its indexes down with it, and a retype dropped the ones whose
+    // predicate it could not survive — either way this one is CREATED rather than compared:
+    // `redefineIndex` sees a definition that never moved and would emit nothing at all.
+    const gone =
+      context.moved.has(index.name) || index.columns.some((each) => context.rebuilt.has(each));
+    if (recorded !== undefined && !gone) {
+      redefineIndex(entity.table, index, recorded, plan);
+      continue;
+    }
+    // `added` only: an index over a column that was already there is implied by no clause this
+    // migration emits, so it still needs a statement of its own.
+    if (impliedByColumnClause(entity, index, context.added)) continue;
+    plan.up.push(createIndex(entity.table, index));
+    // The plain drop, always: this migration CREATED it, with `create index`, so it is an index
+    // and never a constraint's — `dropRecordedIndex`'s ambiguity is about the recorded side only.
+    plan.down.push(dropIndex(index.name));
+  }
+  removeUndeclared(entity, live, plan, context, declared);
+}
+
+/**
+ * Every recorded index this entity no longer declares, dropped — and restored in `down` from what
+ * the SNAPSHOT recorded, never from what the entity declares, since the entity is precisely what
+ * stopped describing it. The rule the retype path already states.
+ *
+ * Four names are skipped, and each one is a statement Postgres would refuse or repeat:
+ *
+ * | skipped                                  | because |
+ * |------------------------------------------|---------|
+ * | `primary`                                | `drop index` on a primary key's index is 2BP01; the key is `TableDescription.primaryKey`, a different question |
+ * | already in `context.moved`               | a retype dropped it ahead of the ALTER — a second drop is 42704 |
+ * | over a column in `context.rebuilt`       | it went with the `drop column` half of `regenerate` — 42704 |
+ * | over a column this migration DROPS       | `alter table … drop column` takes it, so a drop beside it says nothing new. The rule `foreignKeyPlan` applies to a constraint on a dropped column |
+ *
+ * A doomed TABLE needs no arm: `generate.ts` only reaches a diff for a table an entity still
+ * declares, so `drop table` and this function never meet.
+ */
+function removeUndeclared(
+  entity: EntityDescriptionLike,
+  live: TableDescription,
+  plan: Plan,
+  context: IndexPlanContext,
+  declared: ReadonlySet<string>,
+): void {
+  const columns = new Set(entity.columns.map((column) => column.column));
+  for (const recorded of live.indexes) {
+    if (recorded.primary || declared.has(recorded.name)) continue;
+    if (context.moved.has(recorded.name)) continue;
+    if (recorded.columns.some((column) => context.rebuilt.has(column))) continue;
+    if (!recorded.columns.every((column) => columns.has(column))) continue;
+    plan.up.push(...dropRecordedIndex(live.name, recorded));
+    plan.down.push(createIndex(live.name, asDeclared(recorded)));
+  }
+}

src/migration-errors.ts

+// Single responsibility: the refusals a MIGRATION earns — the lock it could not take, a ledger that
+// disagrees with this build, a plan that cannot be reversed, a plan that destroys rows without
+// saying so, a snapshot that was never written, and a view standing in a retype's way. Split out of
+// `errors.ts` only because that file reached the 500-line ceiling, exactly as `invariant-errors.ts`
+// was: every code below is still declared, titled and registered there, and `DbError` is still the
+// one class. One direction only — nothing here is imported back.
+
+import { DESTRUCTIVE_CAUSE, DESTRUCTIVE_MARKER, type DestructiveStatement } from './destructive';
+import { DbError } from './errors';
+
+/**
+ * The migration advisory lock was still held when the wait ran out. `pg_advisory_lock` blocks with
+ * no timeout, so a migrator wedged on a partition — or OOM-killed with its backend still alive —
+ * left `helm upgrade --wait` sitting inside one statement, printing nothing, with the job never
+ * failing so `backoffLimit` never fired. A bounded `pg_try_advisory_lock` poll turns that into an
+ * exit code.
+ */
+export const migrateConcurrent = (lockKey: number, waitedMs: number): DbError =>
+  new DbError({
+    code: 'X_MIGRATE_CONCURRENT',
+    cause:
+      `another session still holds pg_advisory_lock(${lockKey}) after waiting ${waitedMs}ms, ` +
+      'so this migrator refused rather than block a deploy forever',
+    fix:
+      'psql "$DATABASE_URL" -c "select pid, application_name, state from pg_stat_activity ' +
+      "join pg_locks using (pid) where locktype = 'advisory'\"" +
+      '   # pg_terminate_backend(pid) the wedged migrator, then: x db migrate',
+    meta: { lockKey, waitedMs },
+  });
+
+export const migrationConflict = (cause: string, fix: string): DbError =>
+  new DbError({ code: 'X_MIGRATION_CONFLICT', cause, fix });
+
+export const migrationIrreversible = (cause: string, fix: string): DbError =>
+  new DbError({ code: 'X_MIGRATION_IRREVERSIBLE', cause, fix });
+
+/**
+ * A view standing in the way of a retype, refused one statement before Postgres would have.
+ *
+ * `restore` is the caller's, the way `migrationIrreversible`'s `fix:` is: it is a pair of
+ * `psql "$DATABASE_URL" -c '…'` invocations — the shape `migrationConflict` above already writes —
+ * built out of live catalog values through `identifier()`. This file may not import `sql.ts`: that
+ * module imports `identifierUnsafe` from here, and an import cycle around the module whose
+ * evaluation REGISTERS every code is not a cycle worth having for one quoted name.
+ */
+export const migrationViewDepends = (
+  view: string,
+  table: string,
+  column: string,
+  restore: string,
+): DbError =>
+  new DbError({
+    code: 'X_MIGRATION_VIEW_DEPENDS',
+    cause:
+      `view "${view}" is compiled against "${table}"."${column}", which this migration retypes; ` +
+      'Postgres answers 0A000 and rolls the whole migration back',
+    fix: restore,
+    meta: { view, table, column },
+  });
+
+/**
+ * A rollback step count this build cannot honour. `steps` reaches `Array.prototype.slice`, where a
+ * negative count counts from the END: `steps: -1` selected every applied migration except the
+ * newest and reversed four of five, which is the one class of mistake a rollback cannot undo.
+ * Refused rather than coerced, exactly as `DATABASE_POOL_MAX` is — a number silently reinterpreted
+ * as a different one is the failure a validated argument exists to prevent.
+ */
+export const rollbackStepsInvalid = (received: number): DbError =>
+  new DbError({
+    code: 'X_INVARIANT',
+    cause: `rollback was asked to reverse ${String(received)} migrations, which is not a positive integer`,
+    fix: 'rollback({ migrations, steps: 1 })   # a whole number of migrations, newest first',
+    meta: { steps: received },
+  });
+
+/**
+ * `packages/db/migrations/0000_initial.snapshot.json` → `packages/db/migrations/0000_initial.*` —
+ * every file that one migration owns, as one `rm` argument. Derived from the path the caller passed
+ * rather than rebuilt from a directory this package does not know: `db` is tier 1 and where an app
+ * keeps its migrations is `@ultimat3/cli`'s answer, not this one's.
+ */
+const snapshotSiblings = (file: string): string => file.replace(/\.snapshot\.json$/, '.*');
+
+/**
+ * `20260817120000_add_posts` → `add_posts`, the argument `x db gen` takes. The name is free text
+ * and only ever labels a *new* id, so an id carrying no stamp answers with itself rather than with
+ * the empty string — a `fix:` ending in `x db gen ""` is a command that cannot be run.
+ */
+const migrationNameOf = (id: string): string => id.replace(/^\d+_/, '') || id;
+
+/**
+ * The sidecar every generated migration writes is what the *next* generation diffs against, so a
+ * newest migration without one leaves nothing to diff. Refused rather than defaulted to the empty
+ * schema, which would generate `create table` for every table the database already holds.
+ */
+export const migrationSnapshotMissing = (id: string, file: string): DbError =>
+  new DbError({
+    code: 'X_MIGRATION_SNAPSHOT_MISSING',
+    cause: `migration "${id}" records no schema snapshot (${file}), so there is nothing to diff against`,
+    // Two remedies, both commands, in the order they are safe to try. "restore from version
+    // control" alone was neither: on a scaffolded app the sidecar was never written, so there is
+    // nothing to restore — and the drift this refusal answers named `x db gen` as *its* fix, so
+    // the two errors pointed at each other and an app's first migration had no way out.
+    // `x db gen` is named only *after* the files it would trip over are gone.
+    fix:
+      `git checkout -- ${file}   # or, if it was never written: ` +
+      `rm ${snapshotSiblings(file)} && x db gen "${migrationNameOf(id)}"`,
+    meta: { id, file },
+  });
+
+/**
+ * One error per file, never one per statement: the marker declares the whole migration, so a
+ * second finding would repeat an instruction the first already gave. `file` is app-relative and
+ * arrives from the caller — `db` is tier 1 and does not know where an app keeps its migrations.
+ *
+ * Irreversible and destructive are two questions. `X_MIGRATION_IRREVERSIBLE` refuses to *generate*
+ * a plan whose `down` cannot restore the rows; this one refuses to *ship* a plan whose `up`
+ * destroys them without saying so — a retype is reversible in DDL and still rewrites every row.
+ */
+export const migrationDestructive = (
+  file: string,
+  first: DestructiveStatement,
+  more = 0,
+): DbError =>
+  new DbError({
+    code: 'X_MIGRATION_DESTRUCTIVE',
+    cause:
+      `${file} ${DESTRUCTIVE_CAUSE[first.kind]} and does not declare it` +
+      `${more === 0 ? '' : ` (and ${more} more destructive)`}: ${first.statement}`,
+    fix: `add the line "${DESTRUCTIVE_MARKER}" to ${file}, or regenerate it: x db gen "<name>" --allow-destructive`,
+    meta: { file, kind: first.kind, statements: more + 1 },
+  });

src/retype-keys.ts

+// Single responsibility: which columns this migration RETYPES, and which recorded FOREIGN KEYS
+// that breaks — the one answer to both, computed over the whole schema and above `diffTable`.
+//
+// **Why above `diffTable`, and not inside it like every other dependent.** Postgres re-checks a
+// key's two ends against each other on every `alter column … type` and cannot rebuild one whose
+// sides stopped matching: measured on 18.4, `42804 foreign key constraint "rk_posts_org_code_fkey"
+// cannot be implemented — Key columns "org_code" … and "code" … are of incompatible types: integer
+// and text`, thrown by the ALTER itself, inside `ROLE=migrate`, with the ledger recording nothing.
+// The constraint that breaks is recorded on the table that OWNS it, which for a retype of the
+// key's TARGET is a different entity's record — `diffTable(orgs)` is handed `orgs`'s row and can
+// never see `posts.foreignKeys`. So the retype set is derived once, from `entities` and `current`
+// together, and `retypeColumn` READS it rather than deciding again: two answers to "is this column
+// being retyped" is the axiom-1 split this package has spent the week closing.
+//
+// **Over-approximated on purpose, the rule `retype-dependents.ts` states.** Whether two types keep
+// an equality operator between them is operator resolution, which is exactly the knowledge a
+// generator with no database cannot have — `varchar(80)` and `text` share one, `integer` and
+// `text` do not. A key moved aside that did not need to be is one `add constraint` re-validating a
+// table the ALTER beside it is already rewriting under ACCESS EXCLUSIVE; a key missed is the
+// release phase failing with the server's words and none of the entity's.
+//
+// **What it cannot see.** A key the recorded schema does not hold — a hand-written migration's,
+// or one from a sidecar written before `foreignKeys` was recorded — is invisible here and still
+// `42804`, the same construction limit `x db gen` has against a hand-added expression index. And
+// re-adding the key is still the SERVER's judgement: an entity that retypes one end and not the
+// other declares a pairing Postgres has no operator for, and the `add constraint` at the end of
+// `up` is where that is said. Refusing it here would need the type knowledge two paragraphs up.
+
+import type { EntityDescriptionLike } from './entity-shape';
+import { addForeignKey, dropForeignKey, keyId, unrestorableNote } from './foreign-key';
+import type { Plan } from './foreign-key-plan';
+import { isGenerated } from './generated-column';
+import type { ForeignKeyDescription, SchemaDescription, TableDescription } from './introspect';
+import { findTable } from './introspect';
+import { sqlType } from './sql-type';
+
+/** Table name to the columns whose physical type this migration moves. Empty entries are omitted. */
+export type RetypedColumns = ReadonlyMap<string, ReadonlySet<string>>;
+
+/** The columns of one table this migration retypes — `retypeColumn`'s own read of the set above. */
+export function retypedIn(retyped: RetypedColumns, table: string): ReadonlySet<string> {
+  return retyped.get(table) ?? new Set<string>();
+}
+
+/**
+ * Every plain `alter column … type` this migration will emit, before any of them is written.
+ *
+ * A GENERATED column is deliberately absent: `generated-column.ts` owns every statement one of
+ * them produces, and its plain -> generated path is a `drop column` that takes the key with it
+ * rather than an ALTER that trips over it. That gap is real and is named in `generated-column.ts`.
+ */
+export function retypedColumns(
+  entities: readonly EntityDescriptionLike[],
+  current: SchemaDescription,
+): RetypedColumns {
+  const moved = new Map<string, ReadonlySet<string>>();
+  for (const entity of entities) {
+    const live = findTable(current, entity.table);
+    if (live === undefined) continue;
+    const recorded = new Map(live.columns.map((column) => [column.name, column]));
+    const columns = new Set<string>();
+    for (const column of entity.columns) {
+      const held = recorded.get(column.column);
+      if (held === undefined) continue;
+      if (isGenerated(column) || held.generated !== undefined) continue;
+      if (held.dataType !== sqlType(column.kind)) columns.add(column.column);
+    }
+    if (columns.size > 0) moved.set(entity.table, columns);
+  }
+  return moved;
+}
+
+/** Whether either end of `key` sits on a column this migration retypes. `owner` owns the key. */
+function breaksOn(key: ForeignKeyDescription, owner: string, retyped: RetypedColumns): boolean {
+  const own = retyped.get(owner);
+  if (own !== undefined && key.columns.some((column) => own.has(column))) return true;
+  const target = retyped.get(key.referencedTable);
+  return target !== undefined && key.referencedColumns.some((column) => target.has(column));
+}
+
+/**
+ * Drop every recorded key a retype breaks, restore it in `down`, and answer which names were moved.
+ *
+ * The two statements go in the plan's OWN buckets and not beside the ALTER, because the drop has
+ * to precede every alter in the migration and the restore has to follow every one of them — both
+ * ends of a key can move, in two different entities' diffs. `preAlters` is merged at the very top
+ * of `up` and at the very FRONT of `down`, which reversal turns into the very end: so the reversed
+ * script reads drop-the-new-key, retype both ends back, add the recorded key. Restoring it any
+ * earlier is `42804` in the other direction.
+ *
+ * What comes back in `up` is not written here at all: `foreignKeyPlan` reads the returned set,
+ * treats a moved key as one the schema does not record, and adds the DECLARED key in the
+ * `constraints` bucket that already runs after every table statement. That is what makes the three
+ * outcomes fall out of code that already exists — still declared (added back), no longer declared
+ * (gone, exactly as the removal arm would have left it), and declared with a new `on delete` rule
+ * (added back carrying it) — instead of three branches restating them here.
+ */
+export function moveKeysAside(
+  current: SchemaDescription,
+  retyped: RetypedColumns,
+  doomed: ReadonlySet<string>,
+  preAlters: Plan,
+): ReadonlySet<string> {
+  const moved = new Set<string>();
+  for (const table of current.tables) {
+    for (const key of table.foreignKeys) {
+      if (!breaksOn(key, table.name, retyped)) continue;
+      moved.add(keyId(table.name, key.name));
+      preAlters.up.push(dropForeignKey(table.name, key.name));
+      preAlters.down.push(restore(table, key, doomed));
+    }
+  }
+  return moved;
+}
+
+/**
+ * The `down` half. A key whose own table or whose target is being dropped has no `add constraint`
+ * that could run at all, so it gets the SAME note `unrestorableDrop` gives one — the text is
+ * `unrestorableNote`'s, in `foreign-key.ts`, and not a second spelling here. One failed rollback
+ * has one wording whichever module emitted it (axiom 2); these two had already drifted, this one
+ * naming no table at all while `foreign-key-plan.ts` named the target.
+ *
+ * The table it names is the key's OWN when that is the one going: a constraint whose table is
+ * gone is the more proximate reason there is nothing to add it back onto.
+ */
+function restore(
+  table: TableDescription,
+  key: ForeignKeyDescription,
+  doomed: ReadonlySet<string>,
+): string {
+  if (!doomed.has(table.name) && !doomed.has(key.referencedTable)) {
+    return addForeignKey(table.name, key);
+  }
+  return unrestorableNote(
+    table.name,
+    key.name,
+    doomed.has(table.name) ? table.name : key.referencedTable,
+  );
+}

src/sql-type.ts

+// Single responsibility: the physical Postgres type a declared column KIND becomes. One table, so
+// the statement that writes a column, the snapshot that records it and the pass that decides
+// whether a retype is happening at all cannot disagree about what `char` means.
+//
+// Split out of `generate.ts` for `retype-keys.ts`, which has to answer "does this column's type
+// move" ABOVE `diffTable` — a foreign key over a retyped column lives in another table's record.
+
+const SQL_TYPES: Readonly<Record<string, string>> = {
+  uuid: 'uuid',
+  text: 'text',
+  // Bare `char` is `char(1)` in Postgres, and the only column carrying this kind is money's
+  // currency — a three-letter ISO 4217 code whose CHECK the entity emits on the same line.
+  // Without the length no currency ever fits the constraint the same statement demands.
+  char: 'char(3)',
+  boolean: 'boolean',
+  integer: 'integer',
+  bigint: 'bigint',
+  numeric: 'numeric',
+  timestamptz: 'timestamptz',
+  date: 'date',
+  jsonb: 'jsonb',
+};
+
+/**
+ * A kind this table does not name passes through verbatim — an app's own domain, an enum type a
+ * hand-written migration created.
+ *
+ * `Object.hasOwn` and not a bare index: `kind` is DATA, so `SQL_TYPES['constructor']` answered the
+ * `Object` function and `type ${wanted}` spliced its source into a statement, while `'__proto__'`
+ * answered `[object Object]`. Guarded, both behave like every other unknown kind and pass through
+ * as themselves. Measured across the package's 766 tests: no other input's answer moves.
+ */
+export function sqlType(kind: string): string {
+  return Object.hasOwn(SQL_TYPES, kind) ? (SQL_TYPES[kind] ?? kind) : kind;
+}

package.json

 {
   "name": "@ultimat3/db",
-  "version": "15.0.0",
+  "version": "16.0.0",
   "description": "Postgres access, transactions, migrations and drift detection",
@@ -34,3 +34,3 @@ "license": "MIT",
   "dependencies": {
-    "@ultimat3/core": "15.0.0"
+    "@ultimat3/core": "16.0.0"
   },
@@ -37,0 +37,0 @@ "peerDependencies": {

src/errors.ts

@@ -14,3 +14,2 @@ // The database layer's stable error codes. Every factory produces the exact command that
 } from '@ultimat3/core';
-import { DESTRUCTIVE_CAUSE, DESTRUCTIVE_MARKER, type DestructiveStatement } from './destructive';
 import { type DbSqlStateCode, sqlState, sqlStateCode } from './sqlstate';
@@ -35,2 +34,3 @@
   'X_MIGRATION_SNAPSHOT_MISSING',
+  'X_MIGRATION_VIEW_DEPENDS',
   'X_MIGRATE_CONCURRENT',
@@ -77,2 +77,3 @@ 'X_SQL_UNSAFE',
   X_MIGRATION_SNAPSHOT_MISSING: 'the newest migration records no schema snapshot',
+  X_MIGRATION_VIEW_DEPENDS: 'a view is compiled against a column this migration retypes',
   X_SQL_UNSAFE: 'SQL was built by string interpolation',
@@ -290,22 +291,2 @@ X_BRANCH_EXISTS: 'that branch database already exists',
 
-/**
- * The migration advisory lock was still held when the wait ran out. `pg_advisory_lock` blocks with
- * no timeout, so a migrator wedged on a partition — or OOM-killed with its backend still alive —
- * left `helm upgrade --wait` sitting inside one statement, printing nothing, with the job never
- * failing so `backoffLimit` never fired. A bounded `pg_try_advisory_lock` poll turns that into an
- * exit code.
- */
-export const migrateConcurrent = (lockKey: number, waitedMs: number): DbError =>
-  new DbError({
-    code: 'X_MIGRATE_CONCURRENT',
-    cause:
-      `another session still holds pg_advisory_lock(${lockKey}) after waiting ${waitedMs}ms, ` +
-      'so this migrator refused rather than block a deploy forever',
-    fix:
-      'psql "$DATABASE_URL" -c "select pid, application_name, state from pg_stat_activity ' +
-      "join pg_locks using (pid) where locktype = 'advisory'\"" +
-      '   # pg_terminate_backend(pid) the wedged migrator, then: x db migrate',
-    meta: { lockKey, waitedMs },
-  });
-
 /** The contract's pinned wording. Mirror of `@ultimat3/entity`'s `dbDrift()` — keep in sync. */
@@ -320,81 +301,2 @@ export const dbDrift = (tableName: string, columnName: string): DbError =>
 
-export const migrationConflict = (cause: string, fix: string): DbError =>
-  new DbError({ code: 'X_MIGRATION_CONFLICT', cause, fix });
-
-export const migrationIrreversible = (cause: string, fix: string): DbError =>
-  new DbError({ code: 'X_MIGRATION_IRREVERSIBLE', cause, fix });
-
-/**
- * A rollback step count this build cannot honour. `steps` reaches `Array.prototype.slice`, where a
- * negative count counts from the END: `steps: -1` selected every applied migration except the
- * newest and reversed four of five, which is the one class of mistake a rollback cannot undo.
- * Refused rather than coerced, exactly as `DATABASE_POOL_MAX` is — a number silently reinterpreted
- * as a different one is the failure a validated argument exists to prevent.
- */
-export const rollbackStepsInvalid = (received: number): DbError =>
-  new DbError({
-    code: 'X_INVARIANT',
-    cause: `rollback was asked to reverse ${String(received)} migrations, which is not a positive integer`,
-    fix: 'rollback({ migrations, steps: 1 })   # a whole number of migrations, newest first',
-    meta: { steps: received },
-  });
-
-/**
- * `packages/db/migrations/0000_initial.snapshot.json` → `packages/db/migrations/0000_initial.*` —
- * every file that one migration owns, as one `rm` argument. Derived from the path the caller passed
- * rather than rebuilt from a directory this package does not know: `db` is tier 1 and where an app
- * keeps its migrations is `@ultimat3/cli`'s answer, not this one's.
- */
-const snapshotSiblings = (file: string): string => file.replace(/\.snapshot\.json$/, '.*');
-
-/**
- * `20260817120000_add_posts` → `add_posts`, the argument `x db gen` takes. The name is free text
- * and only ever labels a *new* id, so an id carrying no stamp answers with itself rather than with
- * the empty string — a `fix:` ending in `x db gen ""` is a command that cannot be run.
- */
-const migrationNameOf = (id: string): string => id.replace(/^\d+_/, '') || id;
-
-/**
- * The sidecar every generated migration writes is what the *next* generation diffs against, so a
- * newest migration without one leaves nothing to diff. Refused rather than defaulted to the empty
- * schema, which would generate `create table` for every table the database already holds.
- */
-export const migrationSnapshotMissing = (id: string, file: string): DbError =>
-  new DbError({
-    code: 'X_MIGRATION_SNAPSHOT_MISSING',
-    cause: `migration "${id}" records no schema snapshot (${file}), so there is nothing to diff against`,
-    // Two remedies, both commands, in the order they are safe to try. "restore from version
-    // control" alone was neither: on a scaffolded app the sidecar was never written, so there is
-    // nothing to restore — and the drift this refusal answers named `x db gen` as *its* fix, so
-    // the two errors pointed at each other and an app's first migration had no way out.
-    // `x db gen` is named only *after* the files it would trip over are gone.
-    fix:
-      `git checkout -- ${file}   # or, if it was never written: ` +
-      `rm ${snapshotSiblings(file)} && x db gen "${migrationNameOf(id)}"`,
-    meta: { id, file },
-  });
-
-/**
- * One error per file, never one per statement: the marker declares the whole migration, so a
- * second finding would repeat an instruction the first already gave. `file` is app-relative and
- * arrives from the caller — `db` is tier 1 and does not know where an app keeps its migrations.
- *
- * Irreversible and destructive are two questions. `X_MIGRATION_IRREVERSIBLE` refuses to *generate*
- * a plan whose `down` cannot restore the rows; this one refuses to *ship* a plan whose `up`
- * destroys them without saying so — a retype is reversible in DDL and still rewrites every row.
- */
-export const migrationDestructive = (
-  file: string,
-  first: DestructiveStatement,
-  more = 0,
-): DbError =>
-  new DbError({
-    code: 'X_MIGRATION_DESTRUCTIVE',
-    cause:
-      `${file} ${DESTRUCTIVE_CAUSE[first.kind]} and does not declare it` +
-      `${more === 0 ? '' : ` (and ${more} more destructive)`}: ${first.statement}`,
-    fix: `add the line "${DESTRUCTIVE_MARKER}" to ${file}, or regenerate it: x db gen "<name>" --allow-destructive`,
-    meta: { file, kind: first.kind, statements: more + 1 },
-  });
-
 export const sqlUnsafe = (received: string, position: number): DbError =>
@@ -401,0 +303,0 @@ new DbError({

src/foreign-key-plan.ts

@@ -6,5 +6,11 @@ // Single responsibility: which foreign keys a migration must add or drop, and into which of the
 import type { EntityDescriptionLike } from './entity-shape';
-import { addForeignKey, dropForeignKey, foreignKeyTarget, onDeleteRule } from './foreign-key';
+import {
+  addForeignKey,
+  dropForeignKey,
+  foreignKeyTarget,
+  keyId,
+  onDeleteRule,
+  unrestorableNote,
+} from './foreign-key';
 import type { ForeignKeyDescription, TableDescription } from './introspect';
-import { identifier } from './sql';
 
@@ -77,6 +83,15 @@ /** The two directions of one migration, pushed in `up` order; `down` is reversed at assembly. */
  * database *and* the record denied one the catalog holds — and `compareForeignKeys` judges the
- * declared side, so no drift check could ever see it. Not parity with a removed index either: that
- * leaves the snapshot correct by omission. The drop names the constraint the previous snapshot
+ * declared side, so no drift check could ever see it. This comment used to add "not parity with a
+ * removed index either: that leaves the snapshot correct by omission", which was **wrong** — a
+ * removed index lied in the identical way, and `index-plan.ts` is the arm that closed it. The drop names the constraint the previous snapshot
  * recorded, never the name this generator would have chosen — a hand-written `fk_legacy` is
  * `42704` under the generated spelling.
+ *
+ * `plans.predropped` names the constraints a RETYPE already took out of the way (`retype-keys.ts`),
+ * and it is read as "the schema does not record this key" — the same reading `checkPlan` gives its
+ * own `predropped` set. That is what makes this function the one writer of an `add constraint`
+ * after a retype: a key still declared is added back here, in the bucket that already runs after
+ * every table statement; one the entity dropped is left where the retype left it, because dropping
+ * it a second time is `42704`; and one whose `on delete` moved comes back carrying the new rule.
+ * Its `down` is the retype's, pushed where reversal puts it after both ends are back.
  */
@@ -88,5 +103,8 @@ export function foreignKeyPlan(
 ): void {
-  const { constraints, preDrops, doomed } = plans;
+  const { constraints, preDrops, doomed, predropped } = plans;
   const wanted = foreignKeysOf(entity);
-  const held = new Map((live?.foreignKeys ?? []).map((key) => [foreignKeyTarget(key), key]));
+  const recordedKeys = (live?.foreignKeys ?? []).filter(
+    (key) => !predropped.has(keyId(entity.table, key.name)),
+  );
+  const held = new Map(recordedKeys.map((key) => [foreignKeyTarget(key), key]));
   for (const key of wanted) {
@@ -121,3 +139,3 @@ const recorded = held.get(foreignKeyTarget(key));
   const columns = new Set(entity.columns.map((column) => column.column));
-  for (const key of live?.foreignKeys ?? []) {
+  for (const key of recordedKeys) {
     if (declared.has(foreignKeyTarget(key))) continue;
@@ -143,2 +161,8 @@ // `drop column` takes the constraint with it, so a `drop constraint` after that statement is
   readonly doomed: ReadonlySet<string>;
+  /**
+   * Recorded keys a retype already dropped ahead of the ALTERs, by `keyId` (`retype-keys.ts`).
+   * Read as "not recorded", never as "leave it alone": the declared side still needs its
+   * `add constraint`, and it is this function that writes it.
+   */
+  readonly predropped: ReadonlySet<string>;
 }
@@ -149,12 +173,9 @@
  *
- * The note goes through `identifier` too. A `--` comment ends at the first newline, so a name
- * holding one is a second command on the line after it — the same escape `columnClause` closed,
- * one quoting rule short of the statement above it.
+ * The note itself is `unrestorableNote` (`foreign-key.ts`) and not a string built here —
+ * `retype-keys.ts` says the same thing about the same failed rollback, and two spellings of one
+ * fact is whichever module emitted last deciding what an operator reads.
  */
 function unrestorableDrop(table: string, constraint: string, target: string, preDrops: Plan): void {
   preDrops.up.push(dropForeignKey(table, constraint));
-  preDrops.down.push(
-    `-- constraint ${identifier(constraint).text} on ${identifier(table).text} ` +
-      `cannot be restored; ${identifier(target).text} is gone`,
-  );
+  preDrops.down.push(unrestorableNote(table, constraint, target));
 }

src/foreign-key.ts

@@ -56,2 +56,15 @@ // Single responsibility: what a foreign key *is* — where it points — and the two statements that
 /**
+ * Which constraint, on which table — a key's NAME, where `foreignKeyTarget` is its meaning.
+ *
+ * The two exist for opposite questions and neither substitutes for the other. Drift asks whether
+ * two keys point the same way and must ignore the name; a plan that has already DROPPED a
+ * constraint asks whether this is that exact constraint, which is the name and nothing else. The
+ * table is in it because two tables may each hold a `..._org_id_fkey`, and `checkPlan`'s
+ * `predropped` set is the same shape one file over.
+ */
+export function keyId(table: string, constraint: string): string {
+  return JSON.stringify([table, constraint]);
+}
+
+/**
  * Through `identifier`, never `"${…}"` — the package's one rule, which every name this file writes
@@ -103,2 +116,22 @@ * now goes through. A name that closes its own quote produced a real `drop table` through
 /**
+ * What a `down` says in place of an `add constraint` it cannot run: the key's table or its target
+ * is dropped by this migration, so there is nothing to add the constraint back onto.
+ *
+ * ONE text, two writers — `foreign-key-plan.ts`'s `unrestorableDrop` (a key pointing at a doomed
+ * table) and `retype-keys.ts`'s `restore` (a key a retype moved aside whose ends are doomed).
+ * They spelled the same fact two ways and had already drifted, so an operator reading a failed
+ * rollback saw whichever module emitted last. It lives here because both import this module and
+ * neither imports the other.
+ *
+ * Every name goes through `identifier`, including `gone`: a `--` comment ends at the first
+ * newline, so a name holding one puts a second command on the line after it.
+ */
+export function unrestorableNote(table: string, constraint: string, gone: string): string {
+  return (
+    `-- constraint ${identifier(constraint).text} on ${identifier(table).text} ` +
+    `cannot be restored; ${identifier(gone).text} is gone`
+  );
+}
+
+/**
  * The drop/add pair that moves a key's `on delete` rule — a rebuild, because Postgres has no
@@ -105,0 +138,0 @@ * `alter constraint` for it — for a `fix:` line an author pastes into a new migration.

src/generate.ts

@@ -12,7 +12,7 @@ // Single responsibility: turn an entity snapshot into a timestamped, reversible migration.
 import type { ColumnDescriptionLike, EntityDescriptionLike } from './entity-shape';
-import { migrationIrreversible } from './errors';
 import { type ConstraintPlans, foreignKeyPlan, foreignKeysOf, type Plan } from './foreign-key-plan';
 import type { Regeneration } from './generated-column';
 import { generatedClause, isGenerated, regenerate } from './generated-column';
-import { createIndex, dropIndex, impliedByColumnClause, redefineIndex } from './index-ddl';
+import { createIndex, impliedByColumnClause } from './index-ddl';
+import { indexPlan } from './index-plan';
 import {
@@ -25,27 +25,10 @@ type ColumnDescription,
 import { declaredIndexes } from './invariant-ddl';
+import { migrationIrreversible } from './migration-errors';
 import type { MovedAside } from './retype-dependents';
 import { moveDependentsAside } from './retype-dependents';
+import { moveKeysAside, retypedColumns, retypedIn } from './retype-keys';
 import { identifier } from './sql';
+import { sqlType } from './sql-type';
 import { type UnrenderedDeclaration, unrenderedComment, unrenderedOf } from './unrendered';
 
-const SQL_TYPES: Readonly<Record<string, string>> = {
-  uuid: 'uuid',
-  text: 'text',
-  // Bare `char` is `char(1)` in Postgres, and the only column carrying this kind is money's
-  // currency — a three-letter ISO 4217 code whose CHECK the entity emits on the same line.
-  // Without the length no currency ever fits the constraint the same statement demands.
-  char: 'char(3)',
-  boolean: 'boolean',
-  integer: 'integer',
-  bigint: 'bigint',
-  numeric: 'numeric',
-  timestamptz: 'timestamptz',
-  date: 'date',
-  jsonb: 'jsonb',
-};
-
-function sqlType(kind: string): string {
-  return SQL_TYPES[kind] ?? kind;
-}
-
 function columnClause(column: ColumnDescriptionLike): string {
@@ -165,2 +148,3 @@ // The generation clause sits directly after the type, and `generatedClause` refuses the pairs
   moved: MovedAside,
+  retyped: ReadonlySet<string>,
 ): Regeneration {
@@ -172,5 +156,8 @@ const wanted = sqlType(column.kind);
   if (isGenerated(column) || recorded.generated !== undefined) {
-    return regenerate(table, column, wanted, recorded, plan);
+    return regenerate(live, column, wanted, recorded, plan, moved);
   }
-  if (recorded.dataType === wanted) return 'unchanged';
+  // The set, never `recorded.dataType === wanted` a second time: `retypedColumns` decided this for
+  // the whole schema before any statement was written, because the foreign keys a retype breaks
+  // are recorded on tables this diff is not looking at (`retype-keys.ts`).
+  if (!retyped.has(column.column)) return 'unchanged';
   moveDependentsAside(live, column.column, plan, moved);
@@ -185,3 +172,8 @@ const alter = (type: string): string =>
 
-function diffTable(entity: EntityDescriptionLike, live: TableDescription, plan: Plan): void {
+function diffTable(
+  entity: EntityDescriptionLike,
+  live: TableDescription,
+  plan: Plan,
+  retyped: ReadonlySet<string>,
+): void {
   const existing = new Map(live.columns.map((column) => [column.name, column]));
@@ -197,3 +189,3 @@ const added = new Set<string>();
     if (recorded !== undefined) {
-      if (retypeColumn(live, column, recorded, plan, moved) === 'rebuilt') {
+      if (retypeColumn(live, column, recorded, plan, moved, retyped) === 'rebuilt') {
         rebuilt.add(column.column);
@@ -225,19 +217,5 @@ }
 
-  const indexed = new Map(live.indexes.map((index) => [index.name, index]));
-  for (const index of declaredIndexes(entity)) {
-    const recorded = indexed.get(index.name);
-    // A rebuilt column took its indexes down with it, and a retype dropped the ones whose
-    // predicate it could not survive — either way this one is CREATED rather than compared:
-    // `redefineIndex` sees a definition that never moved and would emit nothing at all.
-    const gone = moved.indexes.has(index.name) || index.columns.some((each) => rebuilt.has(each));
-    if (recorded !== undefined && !gone) {
-      redefineIndex(entity.table, index, recorded, plan);
-      continue;
-    }
-    // `added` only: an index over a column that was already there is implied by no clause this
-    // migration emits, so it still needs a statement of its own.
-    if (impliedByColumnClause(entity, index, added)) continue;
-    plan.up.push(createIndex(entity.table, index));
-    plan.down.push(dropIndex(index.name));
-  }
+  // Both directions, in `index-plan.ts`: a recorded index the entity no longer declares is DROPPED
+  // there, which is the arm this loop did not have for as long as it lived here.
+  indexPlan(entity, live, plan, { added, rebuilt, moved: moved.indexes });
 
@@ -302,2 +280,6 @@ // Last: a CHECK may read a column this migration just added, and `add constraint` on a column
   const preDrops: Plan = { up: [], down: [] };
+  // Ahead of EVERYTHING, and at the far end of `down`: a foreign key compiled against a column
+  // being retyped has to be gone before the first ALTER and back after the last one, and both ends
+  // of one key can move in two different entities' diffs (`retype-keys.ts`).
+  const preAlters: Plan = { up: [], down: [] };
   const wanted = new Set(options.entities.map((entity) => entity.table));
@@ -308,3 +290,7 @@
   );
-  const plans: ConstraintPlans = { constraints, preDrops, doomed };
+  // Before the loop, because the answer spans it: `diffTable` is handed one entity's recorded row
+  // and the key that a retype of its column breaks is recorded on whichever table OWNS the key.
+  const retyped = retypedColumns(options.entities, current);
+  const predropped = moveKeysAside(current, retyped, doomed, preAlters);
+  const plans: ConstraintPlans = { constraints, preDrops, doomed, predropped };
 
@@ -319,3 +305,3 @@ for (const entity of options.entities) {
     }
-    diffTable(entity, live, plan);
+    diffTable(entity, live, plan, retypedIn(retyped, entity.table));
     const kept = new Set(entity.columns.map((column) => column.column));
@@ -378,3 +364,3 @@ for (const column of live.columns) {
   const unrendered = unrenderedOf(options.entities, current);
-  const body = plan.up.join('\n');
+  const body = [...preAlters.up, ...plan.up].join('\n');
   const up = body.length === 0 ? body : unrenderedComment(unrendered) + body;
@@ -386,4 +372,6 @@ return {
     up,
-    // Reverse order: the last thing created is the first thing dropped.
-    down: [...plan.down].reverse().join('\n'),
+    // Reverse order: the last thing created is the first thing dropped. `preAlters` goes in at the
+    // FRONT here precisely so reversal puts it last — a key is added back only once both of its
+    // ends have been retyped back, which is every other statement in the script.
+    down: [...preAlters.down, ...plan.down].reverse().join('\n'),
     snapshot: snapshotOf(options.entities),
@@ -390,0 +378,0 @@ destructive: isDestructive(up),

src/generated-column.ts

@@ -9,3 +9,5 @@ // Single responsibility: what a column the DATABASE computes contributes to DDL, and what changes
 import type { Plan } from './foreign-key-plan';
-import type { ColumnDescription } from './introspect';
+import type { ColumnDescription, TableDescription } from './introspect';
+import type { MovedAside } from './retype-dependents';
+import { moveDependentsAside } from './retype-dependents';
 import { identifier } from './sql';
@@ -72,5 +74,15 @@
  * generated → plain direction, which keeps the values it computed.
+ *
+ * **The rebuild moves the column's dependents aside first, and it is `retype-dependents.ts` that
+ * says which — never a second answer written here.** The `rebuilt`
+ * set `diffTable` carries into its index loop is keyed on an index's COLUMNS, so a partial index
+ * whose `where` names this column and whose key columns do not was dropped with the column by
+ * `drop column` and re-created by nothing: measured, the table came back with the index gone, the
+ * snapshot still recording it, and `down` unable to restore it. An invariant's CHECK reading the
+ * column is the same loss one arm over. `moveDependentsAside` drops each of them explicitly,
+ * restores them in `down`, and puts the name in `moved` — which is what makes the ordinary diff
+ * CREATE the declared one instead of comparing a definition that never moved.
  */
 export function regenerate(
-  table: string,
+  live: TableDescription,
   column: ColumnDescriptionLike,
@@ -80,3 +92,5 @@ wantedType: string,
   plan: Plan,
+  moved: MovedAside,
 ): Regeneration {
+  const table = live.name;
   const wanted = column.generated ?? null;
@@ -95,2 +109,3 @@ const held = recorded.generated ?? null;
   if (held === null) {
+    moveDependentsAside(live, column.column, plan, moved);
     const dropColumn = `alter table ${identifier(table).text} drop column ${identifier(column.column).text};`;
@@ -111,14 +126,24 @@ plan.up.push(
   }
-  let moved = false;
+  let changed = false;
+  // NOT `moveDependentsAside`, and the reason is measured rather than assumed. This ALTER trips the
+  // same `42883` (`operator does not exist: text > integer`, on a generated `integer` column under
+  // `where (doubled > 0)`) — but moving the index aside only relocates the failure to the
+  // `create index` that puts it back, because a predicate whose operator the NEW type has no
+  // resolution for cannot be written either. The plain path's dependents survive precisely because
+  // an untyped literal re-resolves (`status = 'published'` under an enum and under `text`), and a
+  // generated column reaching that shape needs its EXPRESSION changed in the same migration, which
+  // `regenerate` emits AFTER this statement. Left open deliberately, with the failure named.
   if (recorded.dataType !== wantedType) {
     plan.up.push(`${alterColumn(table, column.column)} type ${wantedType};`);
     plan.down.push(`${alterColumn(table, column.column)} type ${recorded.dataType};`);
-    moved = true;
+    changed = true;
   }
+  // Nothing moves for this one either, and here it is free: `set expression` recomputes every value
+  // and leaves the type alone, so nothing compiled against the type has anything to recompile.
   if (held !== wanted) {
     plan.up.push(`${alterColumn(table, column.column)} set expression as (${wanted});`);
     plan.down.push(`${alterColumn(table, column.column)} set expression as (${held});`);
-    moved = true;
+    changed = true;
   }
-  return moved ? 'altered' : 'unchanged';
+  return changed ? 'altered' : 'unchanged';
 }

src/index-ddl.ts

@@ -88,2 +88,53 @@ // Single responsibility: the DDL an entity's INDEX declaration becomes — the `create index` a
 /**
+ * Whether Postgres could be backing this RECORDED index with a UNIQUE constraint rather than
+ * holding it as an index of its own — which decides the only question `dropIndex` cannot answer.
+ *
+ * A UNIQUE constraint's index is unique, total, unordered and btree; `add constraint … unique`
+ * and a `unique` column clause can produce nothing else. So an index missing any one of those is
+ * provably an index, and `drop index` on it is right. Everything else is genuinely ambiguous —
+ * see `dropRecordedIndex`.
+ */
+export function mayBeConstraintBacked(index: IndexDescription): boolean {
+  return (
+    index.unique &&
+    !index.primary &&
+    index.where === null &&
+    index.order === null &&
+    indexMethodOf(index) === 'btree'
+  );
+}
+
+/**
+ * Remove a RECORDED index whose kind this generator cannot know, in statements that are correct
+ * on both databases it cannot tell apart.
+ *
+ * `TableDescription` carries no discriminator, and it cannot be given one that would help: the
+ * SAME declaration reaches the server as a CONSTRAINT or as an INDEX depending on which migration
+ * created it. A `unique` column on a table `createTable` writes emits `create table … slug text
+ * unique`, and Postgres backs that with a constraint named `posts_slug_key`; the same column
+ * gaining `unique` later takes `diffTable`'s `create unique index "posts_slug_key"` and is a plain
+ * index. `snapshotOf` records both as `{ unique: true, primary: false }`, and every sidecar already
+ * on disk was written that way — a new field could not classify one of them retroactively.
+ *
+ * Measured on 18.4 (`index-removal.live.test.ts`), which is why the pair and not a guess:
+ *
+ * | statement                                  | on a constraint's index | on a plain index |
+ * |--------------------------------------------|-------------------------|------------------|
+ * | `drop index "n"`                           | **2BP01**               | ok               |
+ * | `drop index if exists "n"`                 | **2BP01** — not suppressed | ok            |
+ * | `alter table … drop constraint if exists`  | drops it, index and all | notice, no-op    |
+ *
+ * Constraint first, then the index: reversed, the `drop index` reaches a constraint's index and is
+ * the 2BP01 this exists to avoid. Both halves carry `if exists`, so whichever one did nothing says
+ * so with a notice rather than 42704.
+ */
+export function dropRecordedIndex(table: string, index: IndexDescription): readonly string[] {
+  if (!mayBeConstraintBacked(index)) return [dropIndex(index.name)];
+  return [
+    `alter table ${identifier(table).text} drop constraint if exists ${identifier(index.name).text};`,
+    `drop index if exists ${identifier(index.name).text};`,
+  ];
+}
+
+/**
  * A RECORDED index as a declaration this generator can emit again — `declaredMethod`, never a
@@ -90,0 +141,0 @@ * cast: the recorded side is typed open because the catalog shares the shape, and a method this

src/index.ts

@@ -84,11 +84,5 @@ // Single responsibility: the public API of @ultimat3/db. Explicit named exports only —
   identifierUnsafe,
-  migrateConcurrent,
-  migrationConflict,
-  migrationDestructive,
-  migrationIrreversible,
-  migrationSnapshotMissing,
   multipleStatements,
   poolAcquireTimeout,
   poolMaxInvalid,
-  rollbackStepsInvalid,
   serializationExhausted,
@@ -151,2 +145,11 @@ sqlUnsafe,
 } from './migrate';
+export {
+  migrateConcurrent,
+  migrationConflict,
+  migrationDestructive,
+  migrationIrreversible,
+  migrationSnapshotMissing,
+  migrationViewDepends,
+  rollbackStepsInvalid,
+} from './migration-errors';
 export type { StatementAttribution, StatementEvent, StatementObserver } from './observe';
@@ -153,0 +156,0 @@ export { setStatementObserver, statementObserver } from './observe';

src/migrate.ts

@@ -14,5 +14,6 @@ // Single responsibility: apply pending migrations and keep the `x_migrations` ledger honest.
 } from './client';
-import { migrateConcurrent, migrationConflict, rollbackStepsInvalid } from './errors';
+import { refuseDependentViews } from './dependent-view';
 import { expectedQueryLoop } from './expected-loop';
 import type { SchemaDescription } from './introspect';
+import { migrateConcurrent, migrationConflict, rollbackStepsInvalid } from './migration-errors';
 import { raw, sql } from './sql';
@@ -352,2 +353,8 @@ import { SQLSTATE, sqlState } from './sqlstate';
                 await setLockTimeout(tx, lockTimeoutMs);
+                // Before the first statement, never after the failure: a view compiled against a
+                // column this script retypes is `0A000` from the server with the view named only in
+                // a DETAIL field nothing prints, surfaced as "cannot reach the database". Costs one
+                // text scan and no round trip on a migration that retypes nothing, which is nearly
+                // all of them (`dependent-view.ts`).
+                await refuseDependentViews(tx, migration.up);
                 await applyScript(tx, migration.up);
@@ -443,2 +450,5 @@ const durationMs = Math.round(performance.now() - at);
                 await setLockTimeout(tx, lockTimeoutMs);
+                // Both directions: a reversal retypes the same column back, and a view created
+                // since the migration applied blocks it exactly as one created before would.
+                await refuseDependentViews(tx, migration.down);
                 await applyScript(tx, migration.down);
@@ -445,0 +455,0 @@ await tx.execute(sql`delete from ${raw(LEDGER_TABLE)} where id = ${row.id}`);

src/sql.ts

@@ -135,15 +135,33 @@ // Single responsibility: build parameterised SQL. String interpolation is how every SQL
 /**
- * A quoted string literal. Postgres utility statements (`CREATE DATABASE`, `COMMENT ON`) reject
- * bound parameters, so this is the only place a value may be inlined — and it escapes quotes.
- * Never reach for it in a query: `sql` binds parameters there.
+ * A quoted string literal Postgres reads IDENTICALLY under both settings of
+ * `standard_conforming_strings`. Utility and DDL statements (`CREATE DATABASE`, `COMMENT ON`,
+ * `create table … default …`) reject bound parameters, so this is the only place a value may be
+ * inlined. Never reach for it in a query: `sql` binds parameters there.
  *
- * The doubling is only an escape while `standard_conforming_strings` is `on`, which has been the
- * server default since 9.1: with it OFF, a backslash escapes the quote that follows and a value
- * ending in one closes the literal early. So this is safe for framework-supplied names — a
- * database, a schema, a comment this repo writes — and is NOT an escape for untrusted text under
- * an arbitrary server configuration. Nothing passes it caller input today; if something must,
- * bind a parameter instead, or send `E''`-style quoting from a statement that can take one.
+ * **It DOES receive caller input, and this comment said otherwise until 2026-08-25.**
+ * `column-default.ts:43` renders `ColumnDefaultLike` here, which is an app's own
+ * `.default('C:\\logs')` crossing the tier seam from `@ultimat3/entity` — nothing validates it and
+ * no `identifier()` guards it. (The package's two other callers are safe by CONSTRUCTION, not by
+ * input: `readonly-role.ts:71` sits in the same `sql` template as an `identifier(role)` that throws
+ * first, and `branch.ts:85` runs after an already-awaited `identifier(base)`.)
+ *
+ * Doubling the quote is not the whole rule. That GUC is settable per session, per database and per
+ * role and `SET` needs no privilege, and with it `off` a backslash escapes the character after it
+ * inside an ordinary `'…'`. Measured on 18.4 through `generateMigration`: `.default('C:\\logs')`
+ * emits `default 'C:\logs'`, which stores `C:\logs` with the GUC on and **`C:logs`** with it off —
+ * a column defaulting to a value nobody wrote, with no error anywhere. A value ENDING in a
+ * backslash is worse than wrong: the escaped quote leaves the literal unterminated and the text
+ * after it is string data until the next `'` puts the remainder back into code position.
+ *
+ * `E'…'` fixes the dialect in the text itself rather than trusting a setting, so both readings
+ * agree — **only** when the value actually carries a backslash. Without one there is no escape
+ * mechanism for the two settings to disagree about, so every migration already generated stays byte
+ * for byte what it was and nothing regenerates spuriously; both tracked apps have applied
+ * migrations on disk with hashes over this text. Same rule, same measurement, as
+ * `packages/entity/src/sql-literal.ts`, which is where it was first written and which adopts this
+ * one — tier 1 holds it, tier 2 imports down.
  */
 export function literal(value: string): SqlFragment {
-  return raw(`'${value.replaceAll("'", "''")}'`);
+  const quoted = value.replaceAll("'", "''");
+  return raw(value.includes('\\') ? `E'${quoted.replaceAll('\\', '\\\\')}'` : `'${quoted}'`);
 }
@@ -150,0 +168,0 @@

No alert changes

Improved metrics

Total package byte prevSize

552823

10.73%

Number of package files

63

8.62%

Lines of code

7807

9.4%

Dependency changes

• + Added

  @electric-sql/pglite@0.5.8
  (transitive)

• + Added

  @ultimat3/core@16.0.0
  (transitive)

• - Removed

  @electric-sql/pglite@0.5.7
  (transitive)

• - Removed

  @ultimat3/core@15.0.0
  (transitive)

• Updated

  @ultimat3/core@16.0.0