Prompt file imported from ZACK770/studytoy (.windsurf/workflows/db-migration.md). Copyright stays with the author.

Database Migration Workflow (Alembic)

Alembic is the SINGLE source of truth for DB schema. No raw SQL anywhere else.

Before You Start

1. Check current head: // turbo

cd backend && python -m alembic heads

2. List existing migration files: // turbo

ls backend/alembic/versions/*.py

Creating a New Migration

1. Determine the next number: look at existing files (001, 002, ...) and increment.

2. Create the migration file at backend/alembic/versions/NNN_short_description.py:

"""short description of what this migration does

Revision ID: NNN_short_description
Revises: PREVIOUS_HEAD_REVISION
Create Date: YYYY-MM-DD
"""
from alembic import op
import sqlalchemy as sa

revision = 'NNN_short_description'
down_revision = 'PREVIOUS_HEAD_REVISION'  # ← must match current head exactly
branch_labels = None
depends_on = None


def upgrade():
    conn = op.get_bind()
    # ALL operations MUST be idempotent:
    conn.execute(sa.text("ALTER TABLE x ADD COLUMN IF NOT EXISTS y VARCHAR(100)"))
    conn.execute(sa.text("CREATE TABLE IF NOT EXISTS z (id SERIAL PRIMARY KEY, ...)"))
    conn.execute(sa.text("CREATE INDEX IF NOT EXISTS ix_z_col ON z(col)"))


def downgrade():
    pass  # or explicit reverse if needed

3. Update the SQLAlchemy model in backend/app/models/ to match the new columns/tables.

4. Update __init__.py if you added a new model class: backend/app/models/__init__.py

5. Update Pydantic schemas if needed: backend/app/schemas/

Rules — MUST FOLLOW

• NEVER write raw SQL in main.py, startup events, or API endpoints for schema changes
• NEVER modify an existing committed migration file — create a new one instead
• NEVER use bare op.add_column() or op.create_table() — they fail if object already exists
• ALWAYS use IF NOT EXISTS / ADD COLUMN IF NOT EXISTS for idempotency
• ALWAYS set down_revision to the exact current head revision string
• ALWAYS update the corresponding SQLAlchemy model to match
• ONE migration per logical change (don't bundle unrelated changes)

Naming Convention

NNN_short_description.py

• NNN = zero-padded sequence number (001, 002, 003, ...)
• short_description = snake_case description of what changed
• Revision ID = same as filename without .py

Deploy Flow (automatic via render.yaml)

stamp_alembic.py  →  alembic upgrade head  →  seed_games.py

No manual steps needed — Render runs this as preDeployCommand.

• stamp_alembic.py — reads all migration files, finds the HEAD dynamically, and force-stamps alembic_version to HEAD. This means alembic upgrade head always sees "nothing to do" and never re-runs old migrations. You never need to edit this file.
• alembic upgrade head — verifies all migrations are applied. Since stamp already set HEAD, this is a no-op (safe).
• All migrations use IF NOT EXISTS / ADD COLUMN IF NOT EXISTS — they are idempotent and safe to re-run.