Migrations

Banks ship database changes the same way they ship code: a reviewable SQL diff, recorded in source control, applied once, never edited after the fact. CrateStack’s migration runner enforces that contract.

Shape

A migration is a struct, not a file convention — banks integrate it into whatever build tooling they already use:

use cratestack::Migration;

let migrations = vec![
    Migration {
        id: "20260101000000_create_accounts".to_owned(),
        description: "create accounts table".to_owned(),
        up: r#"
            CREATE TABLE accounts (
                id BIGINT PRIMARY KEY,
                balance NUMERIC NOT NULL DEFAULT 0,
                version BIGINT NOT NULL DEFAULT 0
            );
            CREATE INDEX accounts_balance_idx ON accounts (balance);
        "#.to_owned(),
        down: None,
    },
    Migration {
        id: "20260201000000_add_currency".to_owned(),
        description: "add currency to accounts".to_owned(),
        up: "ALTER TABLE accounts ADD COLUMN currency TEXT NOT NULL DEFAULT 'USD';".to_owned(),
        down: None,
    },
];

Conventions banks adopt:

id is sortable — YYYYMMDDHHMMSS_<slug> is canonical
description is short and human-readable
up is the SQL applied forward; multiple statements are split on ; and run in one transaction
down is recorded but never executed by the runner — irreversible-by-default is the safe banking posture

Running

use cratestack::{apply_pending, status, ensure_migrations_table};

ensure_migrations_table(&pool).await?;
let applied: Vec<String> = apply_pending(&pool, &migrations).await?;

The runner:

compares each input migration against cratestack_migrations
skips already-applied rows whose checksum matches
aborts with CoolError::Internal if an applied row’s checksum has drifted
for each pending row: opens a transaction, executes every statement in up, inserts the record into cratestack_migrations, commits

A failure in any statement rolls the whole migration back. A multi- statement script with a broken second statement leaves zero artefacts — the first CREATE TABLE rolls back with the failed CREATE INDEX, and cratestack_migrations does not record the partial attempt.

Checksum drift

Each migration’s checksum is SHA-256(id || \0 || description || \0 || up). Editing an already-applied migration in source control changes the checksum:

migration `20260101000000_create_accounts` is recorded as applied but its
SQL has changed; resolve drift before continuing

The runner refuses to apply anything until the drift is resolved. Banks treat this as a release-process failure to escalate to humans — there is no --force flag. Restoring the original SQL or rolling forward with a new migration are the two acceptable resolutions.

Inspecting state

status(&pool, &migrations) returns one MigrationState per input:

pub struct MigrationState {
    pub id: String,
    pub status: MigrationStatus,
}

pub enum MigrationStatus {
    Pending,
    Applied,
    ChecksumMismatch,
}

Banks plug this into a deployment dashboard so operators see drift before the next deploy attempt.

Multi-statement scripts

Postgres prepared statements accept exactly one command per round-trip, so the runner splits up on ; and executes each non-empty statement sequentially inside the same transaction. Common patterns this enables:

CREATE TABLE transfers (
    id BIGINT PRIMARY KEY,
    amount BIGINT NOT NULL,
    status TEXT NOT NULL
);
CREATE INDEX transfers_status_idx ON transfers (status);
INSERT INTO transfers_status_lookup (key, label)
    VALUES ('pending', 'Pending'), ('settled', 'Settled');

All three statements land atomically. A failure in the INSERT rolls the CREATE TABLE and CREATE INDEX back together.

What the runner is not

not a down/rollback engine — down is recorded for audit but never run
not a parallel-applier — migrations are sequential and serialized through the tracking table
not a long-running-migration coordinator — banks executing a 6-hour ALTER TABLE use their own backfill tooling and record the migration as a no-op when the backfill is done

Generating migrations from `.cstack`

The runner consumes SQL migrations identically whether they are hand-written or generated. CrateStack ships a separate schema diff generator that produces those migrations from .cstack against a committed schema snapshot — see ADR 0004 for the full design. Three commands cover the lifecycle:

cratestack migrate diff — offline. Diffs the current .cstack against migrations/<backend>/schema.snapshot.json and writes a new migration directory.
cratestack migrate verify — CI gate. Replays the full migration history against an ephemeral DB and checks the result matches the snapshot.
cratestack migrate drift — ops tool. Reports differences between the snapshot and a live database. Read-only.

Generated migrations remain reviewable SQL diffs — that property is preserved. The generator just removes the hand-translation step from .cstack to SQL. Destructive operations (column drop, lossy type change) still require explicit opt-in, and renames still require an explicit @rename annotation. Hand-written migration steps coexist with generated ones via optional up.pre.sql / up.post.sql files inside the migration directory; the generator never overwrites them. Use these for backfills, lookup-table seeds, materialized-view refreshes, and any transform the diff engine cannot infer.

Schema

CREATE TABLE cratestack_migrations (
    id TEXT PRIMARY KEY,
    description TEXT NOT NULL,
    checksum BYTEA NOT NULL,
    applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

The DDL is exposed as cratestack::MIGRATIONS_TABLE_DDL and applied idempotently by ensure_migrations_table.

Overview

Get Started

Guides

Banking Readiness

Architecture

Tooling

Studio

Reference

Internals

Migrations

Migrations

Shape

Running

Checksum drift

Inspecting state

Multi-statement scripts

What the runner is not

Generating migrations from `.cstack`

Schema

Read Next

​Migrations

​Shape

​Running

​Checksum drift

​Inspecting state

​Multi-statement scripts

​What the runner is not

​Generating migrations from .cstack

​Schema

​Read Next

Migrations

Shape

Running

Checksum drift

Inspecting state

Multi-statement scripts

What the runner is not

Generating migrations from `.cstack`

Schema

Read Next