7eabbc34b9
The Prisma migration chain began with an ALTER on the operations table, so a fresh database could not be initialized from migrations alone (the first migration failed with no table to ALTER). Separately, the login_token / login_token_expires_at columns and index existed in schema.prisma and were used at runtime by src/auth.ts (magic-link login) but were never created by any migration, so a migrate-only database crashed with "column users.login_token does not exist". - Add a 0_init baseline migration that creates the base tables (the pre-2025-12-12 shape), sorting before the first incremental migration so the existing ALTER migrations apply cleanly on top. - Add 20260601000000_add_login_token to create the missing magic-link columns and index (IF NOT EXISTS, safe on installs that already have them from a prior db push). - Add migration_lock.toml (provider = postgresql), the standard companion to a real migration baseline. - Document baselining for existing databases: `migrate resolve --applied 0_init` for installs with migration history, and resolving the whole chain for db-push installs with none (covers the Helm/Docker unattended paths). - Add regression tests asserting the baseline exists and that every @map column in schema.prisma stays backed by a migration (guards the drift class that caused this bug, not just login_token). Verified by replaying all migrations on a fresh Postgres: the chain applies cleanly and the resulting schema matches schema.prisma exactly. Closes #8187 |
||
|---|---|---|
| .. | ||
| 0_init | ||
| 20251212000000_add_is_payload_encrypted | ||
| 20251218000000_add_storage_quota | ||
| 20251228000000_remove_parent_op_id | ||
| 20251228000001_remove_tombstones | ||
| 20251231000000_add_password_reset_token | ||
| 20260102000000_add_passkey_support | ||
| 20260103000000_add_optype_index | ||
| 20260329000000_add_sync_import_reason | ||
| 20260511000000_add_entity_sequence_index | ||
| 20260512000000_add_full_state_sequence_index_drop_redundant_indexes | ||
| 20260513000000_add_full_state_vector_clock | ||
| 20260514000000_add_encrypted_ops_partial_index | ||
| 20260514000001_add_operation_payload_bytes | ||
| 20260514000002_add_payload_bytes_unbackfilled_index | ||
| 20260601000000_add_login_token | ||
| migrate-passkey-credentials.ts | ||
| migration_lock.toml | ||
| README.md | ||
Migration authoring rules
Prisma 5.x wraps every migration in a transaction. PostgreSQL forbids
CREATE INDEX CONCURRENTLY / DROP INDEX CONCURRENTLY inside a transaction
block, so a CONCURRENTLY migration always fails the normal
prisma migrate deploy path (P3018 / SQLSTATE 25001).
scripts/migrate-deploy.sh recovers from this generically: on that specific
failure it reads the failing migration's name from Prisma's output, runs that
migration's own migration.sql out-of-band (no transaction,
statement-by-statement), marks it applied, and retries. It hardcodes no
migration names. Recovery is exercised by tests/migrate-deploy-script.spec.ts
(behavioral, end-to-end) and tests/migration-sql.spec.ts (the migration SQL
shapes the recovery relies on).
Prefer migrations that don't need recovery
Recovery is a safety net, not the happy path. Prefer, in order:
- No CONCURRENTLY if the table is small enough that a brief lock is fine.
- A single CONCURRENTLY statement per migration file. Prisma issues a
single-statement migration as one query, which Postgres does not wrap in
an implicit transaction, so
prisma migrate deployapplies it natively with no recovery needed. (Do not retro-split already-applied migrations — that changes their checksum and breaksmigrate deploy.)
Out-of-band recovery cost scales with the number of consecutive pending CONCURRENTLY migrations and the number of statements in each (one Prisma process per statement), so a large backlog deploy is intentionally slower.
Rules for a recoverable CONCURRENTLY index migration
A migration is auto-recovered only if its SQL contains BOTH a
DROP INDEX CONCURRENTLY and a CREATE INDEX CONCURRENTLY (the idempotent
drop-then-create shape). Anything else falls through to a loud failure with
copy-pasteable manual steps and is never auto-marked-applied.
-
Drop-then-create, idempotent. A re-run after a partial/interrupted concurrent build (which leaves an
INVALIDindex) must succeed:DROP INDEX CONCURRENTLY IF EXISTS "my_idx"; CREATE INDEX CONCURRENTLY "my_idx" ON "operations"(...) WHERE ...;Do not use
CREATE INDEX CONCURRENTLY IF NOT EXISTSinstead — a leftoverINVALIDindex has the right name but is unusable, soIF NOT EXISTSwould skip rebuilding it. -
One statement per logical block, terminated by
;at end of line. The out-of-band splitter ends a statement when a line ends with;. Multi-line statements are fine (collapsed to one line before execution — safe for index DDL). -
Full-line
--comments only. Trailing/inline comments after SQL on the same line are not stripped. -
No
;inside string literals. The splitter treats any end-of-line;as a statement terminator. (True for all index DDL; theWHERE op_type IN ('A', 'B')form is fine — no semicolons.) -
No
BEGIN/COMMIT/DROP TABLE.
Intentional exception: bare CREATE INDEX CONCURRENTLY
20260511000000_add_entity_sequence_index is a deliberately bare
CREATE INDEX CONCURRENTLY with no DROP. Its own comment is explicit: an
interrupted build leaving an INVALID index "should fail loudly instead of
being marked as an applied migration." The recovery guard requires the
drop-then-create shape precisely so this (and any future bare-CREATE) is not
auto-recovered — it fails loudly by gate, deterministically, which is the
intended behavior. This is enforced by tests/migration-sql.spec.ts (the
migration has no DROP) and tests/migrate-deploy-script.spec.ts (a bare
CREATE is refused, never marked applied).