Database Migrations

The project uses Alembic for database schema migrations and data seeding.

Applying Migrations

# Apply all pending migrations
uv run alembic upgrade head

# Check current migration version
uv run alembic current

# View migration history
uv run alembic history

Creating a Schema Migration

When you modify tables in src/db/tables.py, auto-generate a migration:

uv run alembic revision --autogenerate -m "add column_name to table_name"

Warning

Always review the generated migration file in src/db/migrations/versions/. Auto-generation can miss some changes (e.g., column renames, data type changes).

Creating a Data Migration

Agent prompts and seed data are loaded via data migrations, not application code. To add or update a prompt:

1. Create a new migration file:

   uv run alembic revision -m "seed agent_name prompt v2"
   

2. Write the upgrade() and downgrade() functions:

   from alembic import op
   import sqlalchemy as sa
   
   def upgrade() -> None:
       # Deactivate previous version
       op.execute(
           sa.text(
               "UPDATE agent_prompts SET active = false "
               "WHERE agent_name = 'agent_name' AND active = true"
           )
       )
       # Insert new version
       op.execute(
           sa.text(
               "INSERT INTO agent_prompts "
               "(agent_name, version, model, system_prompt, active) "
               "VALUES (:name, :version, :model, :prompt, true)"
           ).bindparams(
               name="agent_name",
               version=2,
               model="claude-sonnet-4-6",
               prompt="Your prompt text here...",
           )
       )
   
   def downgrade() -> None:
       op.execute(
           sa.text(
               "DELETE FROM agent_prompts "
               "WHERE agent_name = 'agent_name' AND version = 2"
           )
       )
       op.execute(
           sa.text(
               "UPDATE agent_prompts SET active = true "
               "WHERE agent_name = 'agent_name' AND version = 1"
           )
       )
   

3. Apply the migration:

   uv run alembic upgrade head
   

Migrations User

In production, migrations run under a dedicated migrations database user with DDL privileges, separate from the runtime pipeline user. Set DATABASE_URL_MIGRATIONS to use a different connection string for Alembic.

Testing Against Staging

Before applying to production, test against the Neon staging branch:

DATABASE_URL_MIGRATIONS="postgresql+asyncpg://migrations:PASSWORD@ep-staging.neon.tech/db?sslmode=require" \
  uv run alembic upgrade head

See Deployment for staging branch setup.