> ## Documentation Index
> Fetch the complete documentation index at: https://docs.encoreos.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Migration version-collision resolution (canonical runbook)

> Preventing and resolving Supabase migration version-prefix collisions when parallel agents author migrations against the same branch.

**Version:** 1.0.0
**Last Updated:** 2026-06-30
**Related:** [`MIGRATION_LANES.md`](/development/supabase/MIGRATION_LANES) · [ADR-021](https://github.com/Encore-OS/encoreos/blob/development/docs/architecture/decisions/ADR-021-migration-edit-exception-rls-helper-collision.md) · design `docs/superpowers/specs/2026-06-09-migration-collision-proofing-and-declarative-pilot-design.md`

> **Read this before researching a collision.** The collision-proofing machinery is
> already built and layered. When you hit a version collision, you do **not** need to
> review Supabase docs or design a bespoke fix — run the one blessed command below.
> The recurring pain was N parallel agents each independently re-researching and
> proposing different resolutions; this runbook is the one resolution.

***

## The problem in one paragraph

Migrations are named `YYYYMMDDHHMMSS_*.sql`. That 14-digit prefix is the PRIMARY KEY of
`supabase_migrations.schema_migrations`, so it is a **global ordering key**, not a real
date (the tree even holds invalid hour-24/25 prefixes — treat versions as **opaque
integers**, never date math). `supabase db diff -f` and `supabase migration new` both
stamp the *current second*, and each worktree only sees its own migrations — so N parallel
agents branched off `development` reliably mint colliding or out-of-order prefixes. A
**fresh** collision hard-fails `db push`; a **shadowed** collision (one version already
recorded on a remote) silently skips the second file → real schema drift.

## The layered defense (where each catch happens)

| Stage                           | Tool                                               | Scope                                                                    | Action on a collision                     |
| ------------------------------- | -------------------------------------------------- | ------------------------------------------------------------------------ | ----------------------------------------- |
| **Author (prevent)**            | `npm run db:migration:next-version`                | mints above `max(local ∪ origin/development ∪ now)`                      | hands you a collision-resistant version   |
| **Author (prevent)**            | `npm run db:migration:reversion`                   | reversions CLI-generated **branch-new** files above `origin/development` | renames in place, preserves order         |
| **Pre-commit**                  | `check-migration-version-collisions.mjs`           | repo-wide (this branch's tree)                                           | blocks the commit                         |
| **Pre-push**                    | `db:migration:reversion:check` (`.husky/pre-push`) | branch-new vs `origin/development`                                       | blocks the push, prints the fix           |
| **PR CI**                       | `migration-guard` (`supabase-ci.yml`)              | within-PR / base-visible                                                 | reds the check                            |
| **Deploy boundary (guarantee)** | `ensure-unique-migration-versions.ts`              | the serialized remote-ledger write                                       | dev: auto-PR the rename · prod: fail-loud |

Author/pre-commit/pre-push/PR stages are **best-effort** — two branches in flight at the
same time are invisible to each other. The **deploy boundary** is the actual guarantee:
*no version collision ever reaches a remote as silent drift.*

***

## Prevent (do this every time you author a migration)

**Hand-authored / caveat (DML, policy, grant) migration** — mint the version, don't let
the CLI pick wall-clock:

```bash theme={null}
F=$(npm run -s db:migration:next-version -- ce_add_widget)   # → <version>_ce_add_widget.sql
: > "supabase/migrations/$F"
```

**Declarative DDL (`supabase db diff -f`) or `supabase migration new`** — the CLI controls
the timestamp, so reversion it immediately after generating:

```bash theme={null}
npx supabase db diff -f ce_add_widget        # CLI stamps a wall-clock prefix
npm run db:migration:reversion               # pulls the new file above origin/development
git add supabase/migrations
```

`db:migration:reversion` is idempotent — a no-op when your migration already sorts after
`origin/development`. It does a best-effort `git fetch origin development` first; pass
`--no-fetch` offline, or a filename to target one file explicitly.

***

## Resolve (you already have a collision)

You will land here from a red pre-push, `migration-guard`, or a deploy auto-PR.

### 1. The blessed command (handles the safe majority)

```bash theme={null}
npm run db:migration-versions:check     # report (dry-run): what collides + the git mv
npm run db:migration-versions:fix       # apply: rename the dependency-later file(s)
git add supabase/migrations && git commit -m "fix(migrations): resolve version collision"
```

This keeps the lexicographically-first file at each colliding version and bumps the rest to
the next free opaque version. For a **fresh** pair both files then apply once, in order.

### 2. Shadowed / REFUSED (human action — rare)

If `db:migration-versions:fix` prints **`REFUSED`**, the later file's version is already
recorded on the remote *and* the file is not re-apply-safe (raw `CREATE TABLE` / `ADD
COLUMN` / `CREATE POLICY` with no `IF NOT EXISTS` / `DROP … IF EXISTS`). Do **not** force it.
Per **ADR-021 Addendum (2026-06-09)**:

1. Make the file idempotent (add the `IF NOT EXISTS` / `DROP POLICY IF EXISTS` guards), **or**
2. Rename it to the suggested next free version by hand and confirm it applies cleanly fresh.

The hard boundary: **never rename a file whose current version is already recorded on the
target remote** — reversion targets the *unrecorded* twin only.

### 3. Local recovery after a bot/auto rename

A version you already applied locally that gets renamed: recover the local ledger with
`supabase migration repair --status reverted <old> --status applied <new>`. **Never**
`supabase db reset` on the shared local stack (`.claude/rules/database.md`).

***

## Do NOT

* ❌ Hand-pick a "probably free" timestamp, or copy another migration's prefix and `+1` by eye.
* ❌ Edit an **applied** migration's SQL to dodge a collision (ADR-021 — the *only* blessed
  edit is the automated version-prefix rename of an **unapplied** file).
* ❌ Research Supabase docs to design a new resolution. The resolution is the command above.

## References

* Scripts: `scripts/database/next-migration-version.ts`, `reversion-migration.ts`,
  `ensure-unique-migration-versions.ts`, `check-migration-version-collisions.mjs`,
  `migration-version-lib.ts` (pure helpers).
* npm: `db:migration:next-version`, `db:migration:reversion[:check]`,
  `db:migration-versions:check|fix`, `db:check:migration-collisions`.
* [`MIGRATION_LANES.md`](/development/supabase/MIGRATION_LANES) §5.8 (author-time allocator),
  ADR-021 (collision-edit exception), the 2026-06-09 collision-proofing design.