# Database Migrations with Alembic

CHAP uses [Alembic](https://alembic.sqlalchemy.org/) to manage database schema changes. Migrations run automatically when you start the application with `docker compose up`.

## Prerequisites

Make sure the database is running:

```bash
docker compose -f compose.yml -f compose.dev.yml up -d postgres
```

The `compose.dev.yml` file exposes the postgres port on localhost for development, allowing you to run Alembic commands locally and connect with database tools like pgAdmin or DBeaver.

Alembic will use the default database URL (`postgresql://root:thisisnotgoingtobeexposed@localhost:5432/chap_core`) when running locally. If you need to connect to a different database, set the `CHAP_DATABASE_URL` environment variable:

```bash
export CHAP_DATABASE_URL="postgresql://user:password@host:port/database"
```

## Quick Start: Making Schema Changes

### 1. Modify the Database Model

Edit the SQLModel class in `chap_core/database/`:

```python
# chap_core/database/dataset_tables.py
class DataSet(DataSetBase, table=True):
    # Add a new field
    new_field: Optional[str] = None
```

### 2. Generate a Migration

```bash
uv run alembic revision --autogenerate -m "add_new_field_to_dataset"
```

This creates a new file in `alembic/versions/` with the detected schema changes.

### 3. Review the Migration

Open the generated file and verify the `upgrade()` and `downgrade()` functions are correct:

```python
def upgrade() -> None:
    op.add_column('dataset', sa.Column('new_field', sa.String(), nullable=True))

def downgrade() -> None:
    op.drop_column('dataset', 'new_field')
```

### 4. Test Locally

```bash
# Apply the migration
uv run alembic upgrade head

# Check current version
uv run alembic current

# If needed, rollback
uv run alembic downgrade -1
```

### 5. Commit and Deploy

```bash
git add alembic/versions/*.py
git commit -m "Add new_field to dataset table"
```

When deployed, the migration runs automatically on startup.

## Common Commands

```bash
# Show current migration version
uv run alembic current

# Show migration history
uv run alembic history

# Apply all pending migrations
uv run alembic upgrade head

# Rollback one migration
uv run alembic downgrade -1

# Create empty migration (for data changes)
uv run alembic revision -m "migrate_old_data"
```

## How Migrations Work

When you run `docker compose up`, the system runs migrations in this order:

1. **Custom migrations** - For backward compatibility with older database versions (v1.0.17 and earlier)
2. **Table creation** - Creates any new tables
3. **Alembic migrations** - Applies schema changes from `alembic/versions/`

This hybrid approach ensures smooth upgrades from older versions while using a standard migration tool for future changes.

> **Note**: The custom migration system in `chap_core/database/database.py` (steps 1 and 2) is a temporary solution for backward compatibility. Once all production instances have upgraded past v1.0.17, we can remove `_run_generic_migration()` and `_run_v1_0_17_migrations()` in a future release, leaving only Alembic for all schema management.

## Best Practices

- **Always review** autogenerated migrations before committing
- **Test migrations** locally before pushing
- **Write descriptive messages** using imperative mood (e.g., "add_column" not "added_column")
- **Don't modify committed migrations** - create a new one instead
- **Test rollback** to ensure `downgrade()` works

## Data Migrations

For changes that require updating existing data (not just schema):

```bash
# Create empty migration
uv run alembic revision -m "migrate_user_data"
```

Edit the file to add custom SQL or Python logic:

```python
def upgrade() -> None:
    # Update existing data
    op.execute("UPDATE users SET status = 'active' WHERE created > '2024-01-01'")

def downgrade() -> None:
    # Reverse if possible
    op.execute("UPDATE users SET status = NULL WHERE created > '2024-01-01'")
```

## Troubleshooting

### Autogenerate detects unwanted changes

This usually means the database schema doesn't match your models. Either:
- The database has old columns that should be removed
- Your model types don't match the database types

Review the migration and adjust as needed.

### Migration fails with "revision not found"

Your local `alembic/versions/` is out of sync with the database:

```bash
# Pull latest migrations
git pull

# Try again
uv run alembic upgrade head
```

## Learn More

- Full documentation: `alembic/README.md`
- [Alembic documentation](https://alembic.sqlalchemy.org/)
- [SQLModel documentation](https://sqlmodel.tiangolo.com/)