Database Migration Workflow

This skill helps with the complete database migration workflow in this project.

When to use this skill

Modifying SQLAlchemy models in backend/app/models/
Creating new database tables or columns
Changing relationships between models
Running database migrations
Checking migration status

Workflow Steps

Before modifying models: Check current migration status

make db-upgrade  # Ensure all migrations are applied

After modifying models: Create migration

make db-migrate  # Auto-generate migration from model changes

Review migration: Always read the generated migration file in backend/alembic/versions/
- Verify upgrade() and downgrade() operations
- Check for data migrations if needed
- Ensure no data loss operations

Apply migration:

make db-upgrade  # Apply to local database

Test migration: Run tests to verify schema changes
```
make test
```

Critical Rules

NEVER delete the Docker volume pgdata - local database must persist
Always review auto-generated migrations before applying
Test both upgrade AND downgrade paths
For production: coordinate with team before running migrations
Row-level security (RLS) policies may need manual updates in migrations

Common Tasks

Add new model field

Add field to SQLAlchemy model
Run make db-migrate
Review generated migration
Run make db-upgrade
Run make test

Create new table

Create new model class in appropriate module
Import model in backend/app/models/__init__.py
Run make db-migrate
Review generated migration
Run make db-upgrade
Run make test

Check migration status

cd backend
uv run alembic current     # Show current revision
uv run alembic heads       # Show latest revision
uv run alembic history     # Show all migrations

Troubleshooting

Migration conflict: Multiple heads exist, merge with alembic merge
Auto-generation missed changes: Check model imports and table metadata
Test database issues: Use DATABASE_URL="postgresql://postgres:postgres@localhost:5433/manageros_test" make db-upgrade