Database Migrations

Open Astra manages its schema through numbered SQL migration files in src/db/migrations/. Migrations run automatically at gateway startup in alphabetical order and are tracked in the schema_migrations table. Each migration runs exactly once.

Automatic execution

At startup, the gateway calls runMigrations() which:

1. Creates the schema_migrations table if it does not exist
2. Lists all SQL files in src/db/migrations/ in alphabetical order
3. For each file not yet recorded in schema_migrations, executes it in a transaction
4. Records the filename and execution timestamp in schema_migrations

Migrations are idempotent by design — re-running them is safe because each is only applied once. All migrations use existence guards (IF NOT EXISTS, IF EXISTS checks via pg_class) for PG17 compatibility.

All 35 migrations

Adding a new migration

Create a new SQL file with the next sequential number:

# Next migration is 031
touch src/db/migrations/031-my-feature.sql

-- src/db/migrations/017-my-feature.sql
CREATE TABLE IF NOT EXISTS my_table (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  workspace_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  data JSONB NOT NULL DEFAULT '{}',
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE INDEX my_table_workspace_idx ON my_table(workspace_id);

The migration runs automatically the next time the gateway starts.