Database Migrations

The platform uses a safe-only migration strategy with automatic schema management on startup, plus Alembic for controlled production migrations.

Auto-Migration (Development)

On startup, the backend applies safe-only changes automatically:

  • Adding new tables

  • Adding new columns (with defaults)

  • Adding new indexes

  • Adding new constraints

This is handled in the lifespan function of the FastAPI application.

Warning

Setting RESET_DB_ON_STARTUP=true drops ALL tables and recreates them. Never use this in production.

Alembic (Production)

For production deployments, use Alembic for controlled migrations:

cd backend

# Generate a new migration
alembic revision --autogenerate -m "Add new column to projects"

# Review the generated migration
cat migrations/versions/xxx_add_new_column.py

# Apply migrations
alembic upgrade head

# Rollback one step
alembic downgrade -1

# Check current version
alembic current

Migration Best Practices

  1. Always review auto-generated migrations before applying

  2. Backfill data for new NOT NULL columns before adding the constraint

  3. Use savepoints for constraint violations (the platform handles this)

  4. Test migrations on a copy of production data first

  5. Never drop columns without deprecation period

Schema Management Rules

Safe operations (auto-applied):

  • CREATE TABLE

  • ALTER TABLE ADD COLUMN (with default or nullable)

  • CREATE INDEX

  • ALTER TABLE ADD CONSTRAINT

Unsafe operations (require manual Alembic migration):

  • DROP TABLE

  • DROP COLUMN

  • ALTER COLUMN TYPE

  • ALTER COLUMN SET NOT NULL (on populated tables)

  • Data migrations

LangGraph Checkpoint Tables

The following tables are managed by LangGraph and excluded from auto-migration:

  • checkpoint_writes

  • checkpoint_blobs

  • checkpoints

  • checkpoint_migrations

These are initialized by the LangGraph PostgreSQL checkpointer on first use.

UUID Generation

All models use uuid4() as callable defaults, ensuring unique IDs are generated per-row at insert time (not at class definition time).

Connection Configuration

# Async engine (for application)
engine = create_async_engine(
    POSTGRES_URL,  # postgresql+asyncpg://user:pass@host:5432/db
    echo=DEBUG,
)

# Session factory
async_session = sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)