Database Migrations Skill

Overview

This skill guides you through creating safe, tested Alembic migrations for the brickston-ai PostgreSQL database.

File Locations

•Migrations: apps/api/alembic/versions/
•Alembic config: apps/api/alembic.ini
•env.py: apps/api/alembic/env.py

Creating a New Migration

Step 1: Generate Migration File

bash

cd apps/api
alembic revision -m "descriptive_name_here"

Step 2: Edit the Migration

The generated file will be in alembic/versions/. Edit both:

•upgrade() — forward migration
•downgrade() — rollback migration

Step 3: Follow These Patterns

For Adding Tables

python

def upgrade():
    op.create_table(
        'table_name',
        sa.Column('id', sa.Integer(), primary_key=True),
        sa.Column('created_at', sa.DateTime(), server_default=sa.func.now()),
        # ... other columns
    )

def downgrade():
    op.drop_table('table_name')

For Foreign Keys in Production

Use NOT VALID to avoid locking large tables during constraint creation:

python

op.create_foreign_key(
    'fk_name', 'child_table', 'parent_table',
    ['parent_id'], ['id'],
    postgresql_not_valid=True  # Critical for large tables
)

For Adding Indexes

Use concurrently=True for production safety:

python

op.create_index(
    'ix_table_column',
    'table_name',
    ['column_name'],
    postgresql_concurrently=True
)

Testing Migrations

Local Testing

bash

cd apps/api
alembic upgrade head
alembic downgrade -1
alembic upgrade head

Check Current State

bash

alembic current
alembic history --verbose

Common Issues & Solutions

Issue	Solution
`ForeignKeyViolation` during tests	Use `NOT VALID` constraint or truncate dependent tables first
`UndefinedColumn`	Check column exists before referencing in foreign keys
Slow migration on large tables	Use `postgresql_concurrently=True` for indexes

Deployment

Migrations run automatically via the /deploy workflow:

bash

alembic upgrade head

Safety Checklist

• Both upgrade() and downgrade() implemented
• Foreign keys use NOT VALID for large tables
• Indexes use concurrently=True in production
• Tested locally: up → down → up
• No data loss in downgrade