# Database Migrations Guide

This document covers the proper workflow for database migrations and how to fix common issues.

## Migration Workflow Checklist

When making model changes, **always follow this order**:

### 1. Make model changes
Edit `backend/models.py` with your changes.

### 2. Create the migration
```bash
# Local development
uv run flask --app backend.app:create_app db migrate -m "Description of changes"

# Docker development
docker compose exec backend uv run flask --app backend.app:create_app db migrate -m "Description of changes"
```

### 3. Review the generated migration
Check `migrations/versions/` for the new file. Verify it does what you expect.

### 4. Apply locally and test
```bash
uv run flask --app backend.app:create_app db upgrade
```

### 5. Commit BOTH the model AND migration together
```bash
git add backend/models.py migrations/versions/
git commit -m "Add field_name to Model"
```

**CRITICAL: Never commit model changes without the corresponding migration file!**

### 6. Deploy and run migrations on production
```bash
# Rebuild to include new migration file
docker compose -f docker-compose.production.yml up --build -d

# Apply the migration
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db upgrade
```

## Common Issues and Fixes

### Error: "Can't locate revision identified by 'xxxxx'"

**Cause:** The database references a migration that doesn't exist in the codebase (migration was applied but never committed).

**Fix:**
```bash
# 1. Check what revision the DB thinks it's at
docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
print(conn.execute('SELECT * FROM alembic_version').fetchall())
"

# 2. Find the latest valid migration in your codebase
ls migrations/versions/

# 3. Force the DB to point to a valid migration
docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
conn.execute(\"UPDATE alembic_version SET version_num = 'YOUR_VALID_REVISION'\")
conn.commit()
print('Done')
"

# 4. Manually add any missing columns
docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
conn.execute('ALTER TABLE tablename ADD COLUMN columnname TYPE')
conn.commit()
print('Column added')
"
```

### Error: "table has no column named X"

**Cause:** Model has columns that don't exist in the database.

**Fix:** Add the columns manually:
```bash
docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
cursor = conn.execute('PRAGMA table_info(tablename)')
print('Current columns:', [col[1] for col in cursor.fetchall()])
"

# Then add missing columns:
docker compose -f docker-compose.production.yml exec backend python -c "
import sqlite3
conn = sqlite3.connect('backend/instance/trivia.db')
conn.execute('ALTER TABLE games ADD COLUMN completed_at DATETIME')
conn.execute('ALTER TABLE games ADD COLUMN winners JSON')
conn.commit()
print('Done')
"
```

### Checking current migration state

```bash
# What migration does the DB think it's at?
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db current

# What's the latest migration in the codebase?
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db heads

# Show migration history
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db history
```

### Nuclear option: Reset migrations (destroys all data!)

Only use if you can recreate all data:
```bash
# Delete database
docker compose -f docker-compose.production.yml exec backend rm -f backend/instance/trivia.db

# Recreate from scratch
docker compose -f docker-compose.production.yml exec backend uv run flask --app backend.app:create_app db upgrade
```

## SQLite Column Type Reference

When adding columns manually, use these SQLite types:
- `INTEGER` - for integers, booleans
- `TEXT` or `VARCHAR(N)` - for strings
- `DATETIME` - for dates/times
- `JSON` - for JSON data
- `FLOAT` or `REAL` - for decimals

## Pre-deployment Checklist

Before deploying model changes:

- [ ] Migration file exists in `migrations/versions/`
- [ ] Migration file is committed to git
- [ ] Migration tested locally with `db upgrade` and `db downgrade`
- [ ] Model changes and migration are in the same commit