Skip to content

Migrations

Database migrations evolve the Postgres schema over time. Each migration is a plain SQL file that runs once and is never re-run.

Migration files

Migrations live in app/storage/migrations/ and are numbered sequentially:

File What it adds
001_initial_postgres.sql Core tables: users, projects, repositories, snapshots, scans, findings, events, API keys
002_scan_lifecycle_columns.sql Scan status transition columns and timestamps
003_runner_jobs_idempotency.sql runner_jobs table for ECS task tracking; idempotency keys
004_findings_columns.sql Additional finding fields: file path, evidence, confidence
005_operational_state_columns.sql Worker heartbeat columns, stale scan recovery timestamps

How the migration runner works

scripts/run-db-migrations.py connects to Postgres and:

  1. Creates a schema_migrations table if it doesn't exist
  2. Reads all *.sql files from app/storage/migrations/ in order
  3. Skips any migration that already appears in schema_migrations
  4. Runs each new migration in a transaction
  5. Records the migration name in schema_migrations on success

If a migration fails, the transaction is rolled back and the script exits with a non-zero code. The failed migration name is not added to schema_migrations, so you can fix the SQL and run again.

Running migrations locally

python scripts/run-db-migrations.py

Requires VEGA_DATABASE_URL (or DATABASE_URL) to be set.

Running migrations in AWS

scripts/aws/run-migrations.sh dev
scripts/aws/run-migrations.sh prod

This launches the vega-maintenance ECS task with the migration command, waits for it to complete, and fails if the container exits with a non-zero code.

Run migrations before deploying new code

If new application code expects a column that doesn't exist yet, it will crash at runtime. Always run migrations first, then deploy the application code.

Adding a new migration

  1. Create app/storage/migrations/006_your_change_name.sql
  2. Write the SQL. Use transactions where possible.
  3. Test locally: python scripts/run-db-migrations.py
  4. Confirm the new migration appears in schema_migrations
  5. Deploy to AWS: scripts/aws/run-migrations.sh dev (then prod)

Checking migration status

Connect to Postgres and query:

SELECT * FROM schema_migrations ORDER BY applied_at;

This shows which migrations have been applied and when.

Reverting a migration

There's no automated rollback. If a migration needs to be reverted:

  1. Write a new migration that undoes the change (e.g., DROP COLUMN for an ADD COLUMN)
  2. Apply the new migration
  3. Deploy application code that works with the reverted schema

For production, always plan your rollback before applying a risky migration.