Database Migrations in Production and Rollback Strategy

The Feature

Database migrations run automatically before each deployment. If a migration fails, the deployment is aborted and the old application continues running. The developer can roll back a migration manually if a deployed migration causes issues.

The Decision

Migrations run before the new application code deploys. This is the only safe order. If the new code deploys first, it may reference columns or tables that do not exist yet. If the migration runs first and the code deployment fails, the old code runs against a schema with additional columns (which is safe if all new columns are nullable or have defaults).

The Implementation

Pre-deployment Migration Script

#!/bin/bash
# scripts/deploy.sh - Called by Coolify before starting new containers

set -euo pipefail

echo "Running database migrations..."
uv run alembic upgrade head

if [ $? -ne 0 ]; then
    echo "Migration failed. Aborting deployment."
    exit 1
fi

echo "Migrations complete. Starting application..."
exec uv run uvicorn app.main:app --host 0.0.0.0 --port 8000 --workers 2

Backup Before Migration

# Supabase provides automatic daily backups on the Pro plan.
# For the free tier, create a manual backup before running migrations:

# Option 1: Supabase dashboard > Database > Backups > Create backup
# Option 2: pg_dump from the Coolify container
docker compose exec backend pg_dump "$DATABASE_URL" > backup_$(date +%Y%m%d_%H%M%S).sql

Rollback Procedure

# 1. Identify the current migration
docker compose exec backend uv run alembic current

# 2. View migration history
docker compose exec backend uv run alembic history --verbose

# 3. Downgrade to previous migration
docker compose exec backend uv run alembic downgrade -1

# 4. If the downgrade itself fails, restore from backup
psql "$DATABASE_URL" < backup_20260515_120000.sql

Migration Safety Rules

1. Never drop a column in the same release that stops using it. Release 1: stop reading the column. Release 2: drop the column. This ensures that if Release 1 is rolled back, the column is still there.

2. Never rename a column. Add the new column, migrate data, update code to use the new column, then drop the old column in a later release.

3. Always add columns as nullable or with a default. Non-nullable columns without defaults fail on tables with existing rows.

4. Test every migration locally before running in production. Run alembic upgrade head on a fresh database and on a database with existing data.

# TRAP: Migration that works on empty table but fails on existing data
def upgrade() -> None:
    op.add_column("markets", sa.Column("slug", sa.String(200), nullable=False))
    # Fails if markets table has rows: column 'slug' cannot be null

# SAFE: Add as nullable, backfill, then alter
def upgrade() -> None:
    op.add_column("markets", sa.Column("slug", sa.String(200), nullable=True))
    # Backfill existing rows
    op.execute("""
        UPDATE markets SET slug = lower(replace(name, ' ', '-'))
        WHERE slug IS NULL
    """)
    op.alter_column("markets", "slug", nullable=False)

The Trap

The worst migration failure is not a crash. It is a migration that succeeds but corrupts data silently. A migration that changes a column type from TEXT to INTEGER will truncate or error on rows that contain non-numeric text. Always test migrations against a copy of production data, not an empty database.

The Cost

Item	Cost
Alembic migrations	$0
Database backups (Supabase free tier)	Manual only
Database backups (Supabase Pro)	Automatic daily, $25/month

For the first months, manual backups before migrations are sufficient. When the product has paying customers, the Supabase Pro plan’s automatic daily backups justify the $25/month cost as insurance against data loss.