# Migrations (`db/migrate` → `migration/` crate)

> **TL;DR**
> - `sea-orm-cli migrate generate <verb_noun>`; write the migration and the entity together; `cargo run -- db migrate`.
> - Committed migrations are append-only (CI enforces it): change the schema with a new migration; a repair migration's `down` restores the predecessor's exact schema.
> - **Ids and foreign keys are 64-bit:** `big_pk_auto`, `big_integer(_null)`, `i64` in the entity. Never `integer`/`pk_auto` for an `id` or `*_id` (`offrails-check` `[ids]`).
> - Every migration has a working `down` (prove rollback, then migrate, on both backends); every FK has an index and an explicit `on_delete`; NOT NULL by default.
> - Stick to SQLite-safe schema changes (rebuild tables for what SQLite's `ALTER` can't do); data backfills go in `src/tasks/`.
>
> Read the rest when writing or repairing a migration, or changing a framework table.

Migrations are Rust, written with `sea-orm-migration`'s schema DSL, which emits correct DDL for SQLite and Postgres. They live in the `migration/` workspace crate (the standard SeaORM layout) and are embedded in the app binary, so **deploying the binary deploys the migrations**.

## Commands

| Task | Command |
|------|---------|
| New migration | `sea-orm-cli migrate generate create_posts` creates `migration/src/m<timestamp>_create_posts.rs` (its body is `todo!()`; replace it) and registers it in `migration/src/lib.rs`. Registration needs the multi-line `vec![` … `]` shape in `lib.rs`: after a generate, check that a `Box::new(m…::Migration),` line was added ([new.md step 3](../generators/new.md#workspace--dependencies)) |
| Apply (any env) | `cargo run -- db migrate` (dev) / `app db migrate` (prod; also runs in the Docker entrypoint) |
| Status | `app db status` |
| Roll back last | `app db rollback` (`--steps N`) |
| Prove reversibility | `app db rollback && app db migrate` |
| Against a remote DB | `DATABASE_URL=postgres://… cargo run -- db migrate` (see `deployment.md` for each platform's one-liner) |

## Template

Verified with sea-orm-migration 2.0.4 on SQLite and Postgres 18. The generated file names the migration with `impl MigrationName for Migration { fn name(&self) -> &str { "m…" } }`; `#[derive(DeriveMigrationName)]` is equivalent. Keep whichever the CLI wrote.

```rust
use sea_orm_migration::{prelude::*, schema::*};

#[derive(DeriveMigrationName)]
pub struct Migration;

#[async_trait::async_trait]          // `async_trait` comes from sea_orm_migration's prelude; don't add the crate
impl MigrationTrait for Migration {
    async fn up(&self, m: &SchemaManager) -> Result<(), DbErr> {
        m.create_table(
            Table::create()
                .table(Posts::Table)
                .if_not_exists()
                .col(big_pk_auto(Posts::Id))
                .col(big_integer(Posts::AuthorId))
                .col(string(Posts::Title))
                .col(text(Posts::Body))
                .col(string(Posts::Status).default("draft")
                    .check(Expr::col(Posts::Status).is_in(["draft", "published"])))
                .col(timestamp_with_time_zone_null(Posts::PublishedAt))
                .col(timestamp_with_time_zone(Posts::CreatedAt))
                .col(timestamp_with_time_zone(Posts::UpdatedAt))
                .foreign_key(
                    ForeignKey::create()
                        .from(Posts::Table, Posts::AuthorId)
                        .to(Users::Table, Users::Id)
                        .on_delete(ForeignKeyAction::Cascade),
                )
                .to_owned(),
        ).await?;
        m.create_index(
            Index::create().name("posts_author_id_idx").table(Posts::Table).col(Posts::AuthorId).to_owned(),
        ).await
    }

    async fn down(&self, m: &SchemaManager) -> Result<(), DbErr> {
        m.drop_table(Table::drop().table(Posts::Table).to_owned()).await
    }
}

#[derive(DeriveIden)]
enum Posts { Table, Id, AuthorId, Title, Body, Status, PublishedAt, CreatedAt, UpdatedAt }

#[derive(DeriveIden)]
enum Users { Table, Id }
```

Helper names come from `sea_orm_migration::schema`:

- `big_pk_auto`, `string`, `text`, `big_integer`, `integer`, `small_integer`, `boolean`, `json`, `uuid`, `date`, `timestamp_with_time_zone`, …;
- `*_null` for nullable and `*_uniq` for unique. Helpers are `NOT NULL` unless suffixed `_null`.

Chain modifiers onto them: `string(X).default("draft").check(Expr::col(X).is_in([...]))`, `json(X).default("{}")`, `string(X).primary_key()` for natural keys.

**Primary keys: `big_pk_auto(T::Id)`.** Verified DDL:

| Backend | `id` column |
|---------|-------------|
| SQLite | `"id" integer NOT NULL PRIMARY KEY AUTOINCREMENT` |
| Postgres | `bigint generated by default as identity` |

Entities use `i64` for it.

### Ids are 64-bit

Every `id` is `big_pk_auto`, and every `*_id` is `big_integer` / `big_integer_null`, with `i64` (or `Option<i64>`) in the entity. **Never `integer`, `small_integer`, `pk_auto` or `.integer()` for an id.** SQLite's `INTEGER` is 64-bit whatever you write, so a SQLite-only suite passes; on Postgres the column is `INT4`, and every insert or query that returns an id fails to decode into `i64`: a 500 on every write. A peer's Kanban shipped exactly that, "green", because its tests never ran on Postgres. `offrails-check` fails the build on it (`[ids]`: a 32-bit `id`/`*_id` column in `migration/src`, or an `i32` id field in `src/`), and on a `tests/app/support.rs` that never reads `TEST_DATABASE_URL` (`[postgres]`).

**Repairing an applied migration** (rule 1 below: never edit it): a new migration `widen_ids` alters each column to `BIGINT` on Postgres (`SERIAL`'s sequence too) and does nothing on SQLite, whose `INTEGER` already holds an `i64`. Its `down` restores `INTEGER`. Then add `// offrails-check: ids widened in m<timestamp>_widen_ids` to each repaired migration file (a comment, so the file's behavior doesn't change); the check then skips that file and fails if the named migration doesn't exist.

```rust
// migration/src/m<timestamp>_widen_ids.rs: one (table, column) per 32-bit id; foreign keys first, then ids.
const COLUMNS: &[(&str, &str)] = &[("cards", "column_id"), ("cards", "board_id"), ("cards", "id"), ("boards", "id")];

async fn up(&self, m: &SchemaManager) -> Result<(), DbErr> {
    if m.get_database_backend() != DbBackend::Postgres {
        return Ok(()); // SQLite: INTEGER is already 64-bit
    }
    for (table, column) in COLUMNS {
        m.alter_table(Table::alter().table(Alias::new(*table))
            .modify_column(ColumnDef::new(Alias::new(*column)).big_integer()).to_owned()).await?;
    }
    // pk_auto made SERIAL columns: their sequences are INT4 too (portable SQL, Postgres branch only).
    for table in ["cards", "boards"] {
        m.get_connection().execute_unprepared(&format!("ALTER SEQUENCE IF EXISTS {table}_id_seq AS bigint")).await?;
    }
    Ok(())
}
// down: the same loops with .integer() and `AS integer`, also Postgres only.
```

`modify_column` changes only the type (sea-query 1.0.2 emits `ALTER COLUMN … TYPE bigint` and nothing else); `NOT NULL`, defaults and foreign keys stay. Verified on Postgres 18 (the SQL, on `serial` tables with a foreign key and rows): types and constraints kept, and the next id after `setval(…, 3000000000)` inserts. The `down` fails once any id passes 2,147,483,647, which is correct. Prove it on Postgres: `db rollback --steps 1 && db migrate`, then the whole suite with `TEST_DATABASE_URL`. Entities change to `i64` in the same slice, and the compiler lists every caller.

**Verified portable:**

- `json` columns are `json_text` on SQLite and `json` on Postgres.
- `timestamp_with_time_zone` is text on SQLite and `timestamptz` on Postgres. Both round-trip `DateTimeUtc`.
- A unique index on a nullable column allows many `NULL`s on both backends.

## Rules

1. **Committed migrations are append-only.** Never edit a migration that is on the main branch, has been deployed, or was run against any database you don't throw away (including other agents' and the human's dev databases): those databases never re-run it, so the edit silently never applies and the code then queries columns that don't exist ("no such column"). Change the schema with a **new** migration (`alter_table`, per the table below). Only a migration that exists solely in your own uncommitted work may be edited. CI enforces it: `git diff --name-only --diff-filter=M origin/main... -- 'migration/src/m*.rs'` must print nothing.
2. **Every migration has a working `down`.** Run rollback then migrate before you're done.
   - **Repairing an earlier migration** (rule 1 forbids editing it): write a new migration whose `up` fixes the schema, and whose **`down` restores the predecessor's exact schema**, not "drop everything". Example: an early `create_mailbox_messages` was a stub, and a later `fix_mailbox_schema` drops and recreates the table with the right columns. Its `down` must recreate the **stub** table (same columns, same indexes), so the next `down` (the stub's own) still finds a table to drop. If both `down`s drop the same table, a full rollback aborts midway ("table does not exist"), which breaks the DoD's rollback gate.
   - Test it: `db rollback --steps all`, then `db migrate`, on both backends (CI does).
3. **Write the migration and the entity in the same step.** They must match.
4. **Every FK gets an index** and an explicit `on_delete` (`Cascade` for owned children, `Restrict` for references, `SetNull` for optional).
5. **`NOT NULL` by default.** A column is nullable only if the spec says the value can be absent.
6. **Name indexes** `<table>_<cols>_idx`, and unique ones `<table>_<cols>_key`.
7. **Case-insensitive uniqueness** (emails): store lowercased in Rust and put a plain unique index on the column. It's portable, unlike `lower()` expression indexes.
8. **Data backfills** go in `src/tasks/`, not in migrations, unless trivial.

## SQLite-safe schema changes (portability)

SQLite's `ALTER TABLE` is limited. To keep every migration portable:

| Change | Do this |
|--------|---------|
| Add column | `alter_table` with **one** `add_column` per statement. Nullable, or `NOT NULL` with a default |
| Drop column | Allowed (SQLite ≥ 3.35). One per statement. Drop its indexes first |
| Rename column | `rename_column`. One per statement |
| Change type / nullability / add FK to an existing table | **Rebuild:** create `<table>_new`, copy rows with `INSERT … SELECT` (via `m.get_connection().execute_unprepared`, the only sanctioned raw SQL in migrations, which must be portable SQL), drop the old table, rename the new one, recreate indexes. Do it in one migration |
| Concurrent index on a big Postgres table | Backend branch: `if m.get_database_backend() == DbBackend::Postgres { … CONCURRENTLY … } else { normal create_index }`. Log it in decisions |

## Framework tables

Generators create these. Never hand-roll alternatives: `users`, `sessions`, `password_resets` (auth), `jobs`, `job_queues`, `job_nodes`, `recurring_executions` (jobs), `mailbox_messages` (dev mailbox), `cache_entries` (cache), `broadcasts` (realtime), `blobs` (storage).