# Database Migrations

This guide covers managing database schema changes in OpenSail using Alembic.

## Overview

OpenSail uses Alembic for database migrations with SQLAlchemy models. The configuration supports:
- Async database connections (asyncpg)
- Autogeneration from SQLAlchemy models
- Type and default value change detection

### Key Files

| File | Purpose |
|------|---------|
| `orchestrator/alembic.ini` | Alembic configuration |
| `orchestrator/alembic/env.py` | Migration environment setup |
| `orchestrator/alembic/versions/` | Migration files |
| `orchestrator/app/models.py` | Main SQLAlchemy models |
| `orchestrator/app/models_auth.py` | Authentication models |
| `orchestrator/app/models_kanban.py` | Kanban models |

## Prerequisites

```bash
cd orchestrator

# Activate virtual environment
source .venv/bin/activate  # macOS/Linux
.\.venv\Scripts\Activate.ps1  # Windows PowerShell

# Ensure DATABASE_URL is set
export DATABASE_URL=postgresql+asyncpg://postgres:postgres@localhost:5432/tesslate
```

## Creating Migrations

### Auto-generate from Model Changes

When you add or modify models in `models.py`:

```bash
cd orchestrator

# Generate migration based on model changes
alembic revision --autogenerate -m "description_of_changes"

# Example:
alembic revision --autogenerate -m "add_notifications_table"
```

This creates a new file in `alembic/versions/` like:
```
alembic/versions/abc123def456_add_notifications_table.py
```

### Manual Migration

For complex changes that autogenerate cannot detect:

```bash
alembic revision -m "manual_data_migration"
```

### Review Generated Migration

Always review the generated migration file before running:

```python
"""add_notifications_table

Revision ID: abc123def456
Revises: previous_revision
Create Date: 2026-01-10 12:00:00.000000

"""
from alembic import op
import sqlalchemy as sa

# revision identifiers, used by Alembic.
revision = 'abc123def456'
down_revision = 'previous_revision'
branch_labels = None
depends_on = None


def upgrade() -> None:
    # ### commands auto generated by Alembic ###
    op.create_table('notifications',
        sa.Column('id', sa.UUID(), nullable=False),
        sa.Column('user_id', sa.UUID(), nullable=False),
        sa.Column('title', sa.String(length=255), nullable=False),
        sa.Column('message', sa.Text(), nullable=False),
        sa.Column('type', sa.String(length=50), nullable=True),
        sa.Column('read_at', sa.DateTime(), nullable=True),
        sa.Column('created_at', sa.DateTime(), nullable=True),
        sa.ForeignKeyConstraint(['user_id'], ['users.id'], ),
        sa.PrimaryKeyConstraint('id')
    )
    op.create_index(op.f('ix_notifications_user_id'), 'notifications', ['user_id'], unique=False)
    # ### end Alembic commands ###


def downgrade() -> None:
    # ### commands auto generated by Alembic ###
    op.drop_index(op.f('ix_notifications_user_id'), table_name='notifications')
    op.drop_table('notifications')
    # ### end Alembic commands ###
```

## Running Migrations

### Apply All Pending Migrations

```bash
alembic upgrade head
```

### Apply Specific Migration

```bash
# Upgrade to specific revision
alembic upgrade abc123def456

# Upgrade one step
alembic upgrade +1
```

### Check Current Version

```bash
alembic current
```

### View Migration History

```bash
alembic history
```

## Rollback

### Rollback One Migration

```bash
alembic downgrade -1
```

### Rollback to Specific Version

```bash
alembic downgrade abc123def456
```

### Rollback All Migrations

```bash
alembic downgrade base
```

## Common Scenarios

### Adding a New Table

1. Create the model in `models.py`:

```python
class Notification(Base):
    """User notifications."""
    __tablename__ = "notifications"

    id = Column(UUID(as_uuid=True), primary_key=True, default=uuid.uuid4)
    user_id = Column(UUID(as_uuid=True), ForeignKey("users.id"), nullable=False)
    title = Column(String(255), nullable=False)
    message = Column(Text, nullable=False)
    type = Column(String(50), default="info")
    read_at = Column(DateTime, nullable=True)
    created_at = Column(DateTime, default=datetime.utcnow)

    # Relationships
    user = relationship("User", back_populates="notifications")
```

2. Add back-reference to User model:

```python
class User(Base):
    # ... existing fields ...
    notifications = relationship("Notification", back_populates="user")
```

3. Generate and run migration:

```bash
alembic revision --autogenerate -m "add_notifications_table"
alembic upgrade head
```

### Adding a Column

1. Add column to existing model:

```python
class Project(Base):
    # ... existing fields ...
    archived_at = Column(DateTime, nullable=True)  # New column
```

2. Generate and run migration:

```bash
alembic revision --autogenerate -m "add_archived_at_to_projects"
alembic upgrade head
```

### Renaming a Column

Autogenerate cannot detect renames (it sees delete + add). Create a manual migration:

```bash
alembic revision -m "rename_column_old_to_new"
```

Edit the migration:

```python
def upgrade() -> None:
    op.alter_column('table_name', 'old_column', new_column_name='new_column')


def downgrade() -> None:
    op.alter_column('table_name', 'new_column', new_column_name='old_column')
```

### Adding an Index

1. Add index to model:

```python
class Project(Base):
    __tablename__ = "projects"
    __table_args__ = (
        Index('ix_projects_user_id_created_at', 'user_id', 'created_at'),
    )
```

2. Generate migration:

```bash
alembic revision --autogenerate -m "add_project_user_created_index"
alembic upgrade head
```

### Data Migration

For migrating data (not just schema):

```bash
alembic revision -m "migrate_user_preferences"
```

Edit migration:

```python
from sqlalchemy.sql import table, column
from sqlalchemy import String

def upgrade() -> None:
    # Define lightweight table reference
    users = table('users',
        column('id', String),
        column('preferences', String),
        column('theme', String),
    )

    # Execute data migration
    connection = op.get_bind()
    connection.execute(
        users.update().where(users.c.preferences.is_(None)).values(preferences='{}')
    )


def downgrade() -> None:
    # Data migrations often cannot be reversed
    pass
```

## Handling Multiple Heads

If two developers create migrations from the same revision:

```
alembic: ERROR: Multiple heads detected
```

### Solution: Merge Heads

```bash
# See current heads
alembic heads

# Create merge migration
alembic merge heads -m "merge_heads"

# Run merge migration
alembic upgrade head
```

## Running Migrations in Production

### Local Testing First

Always test migrations locally before production:

```bash
# Test upgrade
alembic upgrade head

# Verify data integrity
# ...

# Test downgrade
alembic downgrade -1

# Re-upgrade
alembic upgrade head
```

### Kubernetes Deployment

Migrations run automatically on backend startup via `Base.metadata.create_all()`. For explicit migrations:

```bash
# Run migration in backend pod
kubectl exec -n tesslate deployment/tesslate-backend -- alembic upgrade head

# Or use a job
kubectl create job migrate --from=cronjob/tesslate-migration -n tesslate
```

### AWS EKS

```bash
# Execute in backend pod
MSYS_NO_PATHCONV=1 kubectl exec -n tesslate deployment/tesslate-backend -- alembic upgrade head
```

## Best Practices

### 1. Always Review Generated Migrations

Autogenerate is not perfect. Check for:
- Correct column types
- Proper nullable settings
- Index and constraint names
- Data loss risks

### 2. Make Migrations Reversible

Always implement `downgrade()` so you can rollback:

```python
def upgrade() -> None:
    op.add_column('users', sa.Column('avatar_url', sa.String(500)))

def downgrade() -> None:
    op.drop_column('users', 'avatar_url')
```

### 3. Keep Migrations Small

One logical change per migration:
- Good: "add_user_avatar_column"
- Bad: "add_user_fields_and_refactor_projects_and_fix_indexes"

### 4. Test Both Directions

```bash
# Test upgrade
alembic upgrade head

# Test downgrade
alembic downgrade -1

# Re-upgrade
alembic upgrade head
```

### 5. Use Transactions

Alembic runs migrations in transactions by default. For operations that cannot run in a transaction (like CREATE INDEX CONCURRENTLY in PostgreSQL):

```python
def upgrade() -> None:
    # Run outside transaction
    op.execute("CREATE INDEX CONCURRENTLY ix_users_email ON users (email)")
```

### 6. Document Breaking Changes

If a migration requires application changes:

```python
"""
Breaking change: user.preferences now stored as JSONB instead of Text.

Application changes required:
- Update UserSchema to use dict instead of str for preferences
- Update frontend to handle JSON preferences object

Revision ID: abc123
"""
```

### 7. Backup Before Production Migrations

```bash
# PostgreSQL backup
pg_dump -h localhost -U postgres tesslate > backup_$(date +%Y%m%d).sql
```

## Troubleshooting

### Migration Fails Midway

If a migration partially applies:

```bash
# Check current state
alembic current

# Manually fix database if needed
psql -h localhost -U postgres -d tesslate

# Mark migration as complete (if already applied manually)
alembic stamp abc123def456
```

### Autogenerate Misses Changes

Check that models are imported in `env.py`:

```python
# alembic/env.py
from app.database import Base
from app import models  # Must import all model files
from app import models_kanban
from app import models_auth
```

### Type Changes Not Detected

Enable type comparison in `env.py`:

```python
context.configure(
    connection=connection,
    target_metadata=target_metadata,
    compare_type=True,  # Detect column type changes
    compare_server_default=True,  # Detect default value changes
)
```

### Foreign Key Constraint Errors

Order migrations correctly:
1. Create parent table first
2. Then create child table with foreign key

For circular dependencies, create tables first, add foreign keys later:

```python
def upgrade() -> None:
    # Create tables without foreign keys
    op.create_table('table_a', ...)
    op.create_table('table_b', ...)

    # Add foreign keys
    op.create_foreign_key('fk_a_b', 'table_a', 'table_b', ['b_id'], ['id'])
```

## Example Migration Files

See existing migrations in `orchestrator/alembic/versions/` for reference:

| Migration | Purpose |
|-----------|---------|
| `b1f239268ccd_initial_fastapi_users_migration.py` | Initial user tables |
| `9f5ed32f3379_add_feedback_system_tables.py` | Feedback feature |
| `a1b2c3d4e5f6_add_deployment_models.py` | Deployment feature |
| `20251220_add_avatar_url_to_users.py` | Adding column example |

### Recent Migrations (0023-0027)

| Migration | Purpose | Key Changes |
|-----------|---------|-------------|
| `0023_add_container_startup_command.py` | Container startup customization | Adds `startup_command` column to `containers` table |
| `0024_add_skills_system.py` | Skills marketplace | Adds `skill_body` column to `marketplace_agents`, creates `agent_skill_assignments` table |
| `0025_add_channel_and_mcp_system.py` | Channels and MCP servers | Creates `channel_configs`, `channel_messages`, `user_mcp_configs` tables |
| `0026_add_agent_mcp_assignments.py` | Agent-MCP linking | Creates `agent_mcp_assignments` table for connecting agents to MCP servers |
| `0027_add_git_repo_url_to_marketplace_agents.py` | Git repo tracking | Adds `git_repo_url` column to `marketplace_agents` for open-source skill/agent source repos |

## Database Seeding

After running migrations on a fresh database, seed marketplace data:

```bash
# Docker Compose
docker cp scripts/seed/seed_marketplace_bases.py tesslate-orchestrator:/tmp/
docker cp scripts/seed/seed_marketplace_agents.py tesslate-orchestrator:/tmp/
docker cp scripts/seed/seed_opensource_agents.py tesslate-orchestrator:/tmp/
docker cp scripts/seed/seed_skills.py tesslate-orchestrator:/tmp/
docker cp scripts/seed/seed_mcp_servers.py tesslate-orchestrator:/tmp/
docker exec -e PYTHONPATH=/app tesslate-orchestrator python /tmp/seed_marketplace_bases.py
docker exec -e PYTHONPATH=/app tesslate-orchestrator python /tmp/seed_marketplace_agents.py
docker exec -e PYTHONPATH=/app tesslate-orchestrator python /tmp/seed_opensource_agents.py
docker exec -e PYTHONPATH=/app tesslate-orchestrator python /tmp/seed_skills.py
docker exec -e PYTHONPATH=/app tesslate-orchestrator python /tmp/seed_mcp_servers.py
```

For themes, also copy the JSON files:
```bash
docker cp scripts/seed/seed_themes.py tesslate-orchestrator:/tmp/
docker exec tesslate-orchestrator mkdir -p /tmp/themes
docker cp scripts/themes/. tesslate-orchestrator:/tmp/themes/
docker exec -e PYTHONPATH=/app tesslate-orchestrator python -c "
import asyncio, sys; sys.path.insert(0, '/app')
from pathlib import Path
exec(open('/tmp/seed_themes.py').read().split('if __name__')[0])
asyncio.run(seed_themes(themes_dir=Path('/tmp/themes')))
"
```

See the **Database Seeding** section in the root [CLAUDE.md](../../CLAUDE.md) for the full reference including Kubernetes instructions.

### Seed Scripts Reference

| Script | Location | Seeds |
|--------|----------|-------|
| `seed_marketplace_bases.py` | `scripts/seed/` | 4 project templates |
| `seed_marketplace_agents.py` | `scripts/seed/` | 6 official agents + Tesslate account (includes Librarian) |
| `seed_opensource_agents.py` | `scripts/seed/` | 6 open-source agents |
| `seed_skills.py` | `scripts/seed/` | 11 marketplace skills (open-source from GitHub) |
| `seed_mcp_servers.py` | `scripts/seed/` | 6 MCP server configurations |
| `seed_themes.py` | `scripts/seed/` | 7 UI themes (reads from `scripts/themes/*.json`) |
| `seed_community_bases.py` | `scripts/seed/` | 63 community open-source bases |

All scripts are **idempotent** — safe to re-run without duplicating data.

### Automatic Seeding on Startup

The backend runs all seeds automatically on startup via `run_all_seeds()` in `orchestrator/app/seeds/__init__.py`. This includes:
1. Marketplace bases and community bases
2. Official agents (6 agents including the Librarian)
3. Open-source agents
4. Auto-adding the Tesslate Agent and Librarian agent to all users
5. Skills (item_type='skill')
6. Themes
7. Workflow templates

The Librarian agent is automatically added to all user libraries on startup via `auto_add_librarian_agent_to_users()`.

## Next Steps

- [Adding Routers](adding-routers.md) - Create API endpoints for new models
- [Local Development](local-development.md) - Test migrations locally
- [Troubleshooting](troubleshooting.md) - Debug migration issues
