Skip to content

Run database migrations

We currently run two separate database stacks, and each has its own migration workflow:

  • Supabase holds the Gateway's data (user profiles, executions, billing, API keys, …). Migrations are plain SQL applied with the scripts in supabase/scripts/.
  • GCP Cloud SQL holds per-service databases (croesus, hydra, iris). Migrations live in db/migrations/<db>/ and are applied through the DB - Migrate GitHub Actions workflow.

This guide covers how to author and apply a migration in each. Pick the section for the database you are changing.

Note

New services use the GCP Cloud SQL databases. The Supabase database is the older store that still backs the Gateway. If you are unsure which one your change targets, then follow the data. A change to a Gateway table is Supabase. A change to a croesus/hydra/iris table is GCP.

Planned direction: Supabase is being retired, and migrations should be collapsed

Supabase SHOULD be removed and the Gateway's data moved to Cloud SQL, so the Supabase workflow below is legacy. Separately, the numbered Cloud SQL migrations pile up. They SHOULD be collapsed into a single baseline periodically. See /supabase and /db.

Supabase

The Supabase schema is managed with timestamped SQL files and a small set of helper scripts. The same migration is applied to every schema we run: public for production, integration for staging, devel for devel. SQL is therefore written against a %SCHEMA% placeholder instead of a hard-coded schema name.

Everything lives under supabase/ ⧉:

  • migrations/initialization/: the base schema, applied once when a database is first created. Read-only. Never edit these.
  • migrations/incremental/: every change since. This is where your migration goes.
  • seeds/: seed data, split into common.sql and one file per schema.
  • scripts/: init_db.sh (initialise and seed), apply_changes.sh (apply incremental migrations) and create_migration.sh (scaffold a new one).

Setting up locally

init_db.sh applies the base schema and seeds. apply_changes.sh then replays everything incremental on top. Both take a schema name, and both are needed for each schema you want locally:

cd supabase
supabase start

for schema in public integration devel; do
    ./scripts/init_db.sh "$schema"
    ./scripts/apply_changes.sh "$schema"
done

To start over, supabase db reset and repeat the loop.

1. Create the migration

cd supabase
./scripts/create_migration.sh "add_timeout_to_execution"

This writes migrations/incremental/YYYYMMDD_HHMMSS_add_timeout_to_execution.sql from a template and opens it in your editor.

Warning

Never edit files in migrations/initialization/. That is the read-only base schema, applied only once when a database is first initialised. All changes go into migrations/incremental/.

2. Write the SQL

  • Always reference schemas via the %SCHEMA% placeholder, never public or integration directly. The scripts substitute it at apply time.
  • Make operations idempotent (IF NOT EXISTS, CREATE OR REPLACE, DO blocks guarding constraints) so a re-run is safe.
ALTER TABLE %SCHEMA%.execution
    ADD COLUMN IF NOT EXISTS timeout_seconds INTEGER DEFAULT 300;

3. Test and apply

ALWAYS test your migrations in devel first!

# Devel
./scripts/apply_changes.sh devel "$DEVEL_DB_URL"

After merging your changes, you also have to apply them to the other envs.

# Staging
./scripts/apply_changes.sh integration "$STAGING_DB_URL"

# Production
./scripts/apply_changes.sh public "$PROD_DB_URL"

Note

The DB URL is currently actually the same in all envs: "postgresql://postgres:<SUPABASE_DB_PASSWORD>@<SUPABASE_DB_URL>:5432/postgres". The needed values can be found in our common config.

Initialising a new database

A brand-new remote database needs the base schema applied first, once per schema, before any incremental migration will run:

./scripts/init_db.sh public "postgresql://..."
./scripts/init_db.sh integration "postgresql://..."

GCP Cloud SQL

The GCP databases each live as a separate database (croesus, hydra, iris) on a single Cloud SQL Postgres instance. Migrations use the golang-migrate ⧉ convention: one numbered, paired *.up.sql / *.down.sql per change, kept per database under db/migrations/<db>/.

The instance is only reachable from inside the VPC, so migrations are not run from a laptop against the remote databases directly. Instead, use the DB - Migrate GitHub Actions workflow ( .github/workflows/ops-db-migrate.yml) each database with the dedicated migration user.

1. Create the migration files

Add a new numbered pair under the relevant database directory, incrementing from the highest existing number, e.g. for hydra:

db/migrations/hydra/000005_add_my_change.up.sql
db/migrations/hydra/000005_add_my_change.down.sql

The .up.sql applies the change. The .down.sql reverses it. If you have the migrate CLI installed, you can scaffold the pair:

migrate create -ext sql -dir db/migrations/hydra -seq add_my_change

Note

Unlike Supabase, these migrations target a single database each and use real schema names (typically public). There is no %SCHEMA% placeholder here.

2. Write and lint the SQL

Migrations are linted with squawk ⧉ via the pre-commit hook, which catches unsafe operations. Lock and statement timeouts are set on the connection when the workflow runs. Do not set them in the file. require-timeout-settings is excluded in .squawk.toml ⧉.

Run the hook before pushing:

pre-commit run squawk --all-files

Note

Especially the down migrations might sometimes surface linting errors. For them, it can be acceptable to sometimes ignore certain errors with a comment. See other down migrations to see examples.

Strive to keep migrations idempotent. If one fails halfway, it must be safe to re-run (see Recovering from a failed migration).

Tip

Integration tests for the Go services apply these same *.up.sql files against an ephemeral database. Write the migration, then run the service's make test-integration as a local check before relying on the workflow.

3. Apply via the workflow

Migrations are applied by manually running the DB - Migrate workflow from GitHub Actions:

  • environment. devel, integration, or production. Any environment other than devel can only be run from the main branch.
  • db. A single database name (croesus, hydra, iris) to migrate just one, or leave empty to migrate all of them.
  • force_version. Leave empty for normal runs. See below.

The workflow connects through Tailscale and cloud-sql-proxy to the main instance, then runs migrate up for each selected database. Promote changes the same way as everywhere else: devel → integration → production.

Note

You can also run migrations locally via Neph.

Recovering from a failed migration

If a migration fails partway through, golang-migrate marks the database version "dirty" and refuses further runs until it is forced back. Re-run the workflow with force_version set to dirty_version - 1, which calls migrate force to that version before retrying up. Forcing to 0 instead clears the schema_migrations table entirely (use only to reset before any migration has run). This is why migrations should be idempotent. A forced re-run replays the failed migration's statements.