How To: Create a Database Migration

Steps

make migrate-create MSG="add_feature_name"  # Generate migration
make test-migrations                        # REQUIRED: Test from scratch (needs Docker!)
make migrate                                # Apply locally

Requirements

1. Docker MUST be running -- make test-migrations uses real PostgreSQL. Start OrbStack if needed: open -a OrbStack
2. Use IF NOT EXISTS -- Migrations must be idempotent (CI re-runs them)
3. Test locally before pushing -- CI will fail if migrations aren't idempotent
4. Guard table references -- CI runs from empty DB. Use IF EXISTS checks for tables outside the migration chain.

View Dependencies

The plates_with_current_valuation view depends on plates columns. When altering plates columns:

1. Drop the view first
2. Alter the column
3. Recreate the view

See VIEW_RECREATE_SQL in existing migrations for the pattern.

SQL Escaping

Avoid PostgreSQL's format() with %s/%I specifiers in op.execute() -- psycopg2's % escaping conflicts with SQLAlchemy.

# Preferred -- use quote_ident() and string concatenation
op.execute("DO $$ ... 'p.' || quote_ident(col) ... $$")
op.execute("DO $$ ... EXECUTE 'SELECT ' || cols || ' FROM t' ... $$")

Testing Matrix

What you changed	Run this	Requires Docker?
backend/app/models/*.py	make test-backend + make test-backend-pg	Yes
backend/alembic/**	make test-migrations	Yes

What make test-migrations Does

1. Resets database (drops all data)
2. Runs all migrations from scratch (alembic upgrade head)
3. Re-runs upgrade (idempotency check)
4. Rewinds 2 revisions and re-upgrades (resilience check)
5. Validates schema matches SQLAlchemy models (schema_check.py)