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, follow the data: a change to a Gateway table is Supabase, a change to a croesus/hydra/iris table is GCP.

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), so SQL is written against a %SCHEMA% placeholder instead of a hard-coded schema name.

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.

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/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, so you 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 comments; 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, so writing a migration and running the service's make test-integration is a good 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 will replay the failed migration's statements.