# database-migrations

> Create and test Alembic database migrations safely

- Author: Justinsato
- Repository: Justinsato/brickston-ai
- Version: 20260203201127
- Stars: 0
- Forks: 0
- Last Updated: 2026-02-06
- Source: https://github.com/Justinsato/brickston-ai
- Web: https://mule.run/skillshub/@@Justinsato/brickston-ai~database-migrations:20260203201127

---

---
name: database-migrations
description: Create and test Alembic database migrations safely
---

# 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