Database Migrations¶

Migrations are numbered SQL files in packages/db/migrations/. They're applied in lexicographic order by pnpm db:migrate. Already-applied migrations are tracked in a _migrations table.

Running migrations¶

Bare-metal¶

pnpm db:migrate

This runs from services/api against the URL in DATABASE_URL.

Docker¶

docker compose -f docker/docker-compose.yml run --rm migrate

The migrate service is a small, dedicated container that runs once and exits. It's auto-run on first up; rerun manually after pulling new code.

Idempotency¶

The runner is idempotent. Migrations already in _migrations are skipped. Safe to run on every deploy.

Adding a new migration¶

Pick the next number higher than the current highest in the directory.

ls packages/db/migrations | sort -V | tail -1
# After 050_mode_tool_config.sql, next is 051_*

Create the file:

touch packages/db/migrations/051_my_change.sql

Write SQL that's idempotent when possible:

CREATE TABLE IF NOT EXISTS my_new_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE INDEX IF NOT EXISTS idx_my_new_table_created
  ON my_new_table (created_at);

Add corresponding query helpers in packages/db/src/queries/ and any types in types-core.ts.
Run locally:

pnpm db:migrate

Commit the migration and the helpers in the same PR.

Rolling back¶

Doable doesn't ship automatic down migrations; they're error-prone in production. Roll back manually:

Write a follow-up migration (e.g. 052_rollback_my_change.sql) that undoes the change.
Or, restore from your most recent backup if the change was destructive.

For destructive changes (DROP TABLE / column), always:

Make sure no in-flight code reads the dropped object.
Take a backup immediately before.
Roll out in two steps: stop reading, then run migration.

Naming conventions¶

Lower-case, underscore-separated descriptive name.
Number padded to 3 digits.
Group related changes into a single file when possible.

Examples from the existing tree:

004_analytics.sql
017_teams.sql
024_git_versioning.sql
050_mode_tool_config.sql

Multi-step migrations (zero-downtime)¶

For changes that would lock heavily on a large table:

Migration A: add the new column / table, dual-write in app code.
Migration B (next deploy): backfill existing rows.
Migration C (later): switch reads to the new column, drop the old one.

This is the standard expand, migrate, contract pattern. Doable's tables are small enough that most of the time you can do it in a single migration without locking issues, but for chat_messages, activity_events, or analytics tables in busy instances, plan ahead.

Testing migrations¶

Spin up a throwaway database:

docker run --rm -d -p 5433:5432 -e POSTGRES_PASSWORD=test --name pgtest pgvector/pgvector:pg16
DATABASE_URL=postgres://postgres:test@localhost:5433/postgres pnpm db:migrate
docker rm -f pgtest

For more involved checks, snapshot a production backup (anonymized) to staging and run the full migration set.

Where the runner lives¶

services/api/src/db/migrate.ts: small, ~100 lines. Reads files from packages/db/migrations, compares to _migrations, runs missing ones in a transaction, records each.

Using a different schema name¶

By default everything is in public. If you want a dedicated doable schema:

-- Run once manually
CREATE SCHEMA doable;
ALTER ROLE doable SET search_path = doable, public;

Then re-run pnpm db:migrate. Migrations don't qualify table names, so the role's search_path controls placement.