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. There are 28 migrations in total:

File What it adds
001_initial_postgres.sql Core tables: projects, repositories, snapshots, scans, findings, events
002_scan_lifecycle_columns.sql Scan status transition columns and timestamps
003_runner_jobs_idempotency.sql runner_jobs table and idempotency keys
004_findings_columns.sql Additional finding fields
005_operational_state_columns.sql Worker heartbeat columns, stale scan timestamps
006_special_threat_modeling.sql Threat modeling strategy fields
007_remove_threat_profiles.sql Remove legacy threat_profiles table
008_scan_artifacts.sql Scan artifact reference columns
009_retire_severity_threshold.sql Remove deprecated severity threshold field
010_llm_usage_analytics.sql LLM token and cost usage tables
011_planning_artifacts.sql planning_artifacts table (cached plan bundles)
012_github_connections.sql github_connections table (JSONB) for GitHub App installation records
013_user_sub2api_keys.sql user_sub2api_keys table (JSONB) for per-user Sub2API key records
014_sub2api_scan_keys.sql sub2api_users table (JSONB) for Sub2API user and scan key lifecycle
015_scan_scope_metrics.sql Add scope_file_count and scope_total_bytes typed columns to scans
016_scan_llm_usage_synced_at.sql Add llm_usage_synced_at timestamp column to scans
017_billing_wallet.sql billing_customers table (Stripe customer ID) and related billing tables
018_billing_promotions.sql billing_promotion_campaigns table for promotional credit campaigns
019_billing_promotions_sub2api.sql Add wallet_credit_cents column to billing_promotion_campaigns
020_billing_spend_limits.sql Per-user monthly spend limit storage
021_repository_ingest_jobs.sql repository_ingest_jobs table (JSONB) for ingest job lifecycle tracking
022_domain_events.sql domain_events table with (aggregate_id, sequence) ordering for the event store
023_workspace_scan_query_indexes.sql Indexes on repositories and scans for workspace-level scan queries
024_project_summary_query_indexes.sql Indexes on findings and scans for project summary rollup queries
025_findings_scan_scoped_identity.sql Rebuild finding identity index for scan-scoped deduplication
026_scan_live_query_indexes.sql Indexes on findings for scan-live and live-detail queries
027_finding_triage_feedback.sql Add triage_status and agentic_note typed columns to findings
028_scan_log_read_models.sql scan_log_segments and worker component read model tables

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 is not recorded in 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 non-zero.

Run migrations before deploying new code

If new application code expects a column or table 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/029_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 after validation)

Adding new fields to JSONB documents

Because domain records are stored as JSONB, adding a new field to a DomainModel with a default value requires no migration — new instances will have the field; existing records will get the default when deserialized. Only create a migration if you need a Postgres-level index or constraint on the new field.

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 TABLE or DROP COLUMN)
  2. Apply the new migration
  3. Deploy application code that works with the reverted schema

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