Database Migrations¶

Schema changes are managed with Alembic. This guide covers the day-to-day workflow plus the production safety net the boilerplate adds on top.

Two Modes: Auto-Create vs Migrations¶

The boilerplate supports both. They are alternatives, not complements:

`CREATE_TABLES_ON_STARTUP=true` — auto-create¶

The app calls Base.metadata.create_all on startup, creating any missing tables from the current models.

Use when	Don't use when
Local dev with a throwaway database	You need version-controlled schema changes
Tests with an ephemeral testcontainer	Multiple developers share a database
Quick prototyping	You're deploying to staging/production

Driven by the CREATE_TABLES_ON_STARTUP env var, which defaults to on when ENVIRONMENT is local or development and off everywhere else. Setting it to true with ENVIRONMENT=production is refused at startup by the production security validator. The factory honors it via create_application(create_tables_on_startup=...).

Alembic migrations¶

Tracked, reviewable, reversible schema changes — what you want for anything beyond a local sandbox. Leave CREATE_TABLES_ON_STARTUP off — its default outside local and development, and what production insists on — and run migrations explicitly.

Generate your own baseline, once you've chosen your features

backend/migrations/versions/ ships empty on purpose: the schema a project needs depends on the features it keeps, so there is no revision that fits every project. Remove the features you don't want (Composable Features), then generate the baseline for what's left.

Generate it against a database with nothing in it. Autogenerate writes the difference between the models and the database it connects to, so pointing it at a database that already has the schema — a dev stack, which CREATE_TABLES_ON_STARTUP built with create_all — writes a revision whose upgrade() is pass. Make a throwaway database and name it on the command line:

# from the repository root, with the local stack up
docker compose exec postgres createdb -U postgres baseline_tmp

cd backend
POSTGRES_SERVER=localhost POSTGRES_PORT=5432 POSTGRES_DB=baseline_tmp \
    CREATE_TABLES_ON_STARTUP=false uv run --no-sync alembic revision --autogenerate -m baseline

cd .. && docker compose exec postgres dropdb -U postgres baseline_tmp

POSTGRES_USER and POSTGRES_PASSWORD come from your .env; only the server, the port and the database name are overridden here. Use the port the stack publishes if it isn't 5432 (docker compose port postgres 5432).

POSTGRES_SERVER matters as much as the database name: .env.example ships it as postgres, the compose service, which doesn't resolve from your shell — the command dies with socket.gaierror. Either pass localhost, as above, or run the command in a container on the compose network with your backend/migrations mounted into it, so the revision lands in your working tree rather than inside a container that is about to be removed.

env.py imports the models the project still carries, so the baseline holds exactly those tables — a project without tiers gets no tiers table. Review the file, check that upgrade() really creates them, and commit it: from then on it's the first revision every environment runs. Then apply it with alembic upgrade head against the database you serve, or stamp it where that database already has the tables.

Once you rely on migrations, keep CREATE_TABLES_ON_STARTUP off (its default outside local and development) so the app doesn't create tables behind Alembic's back. The models use a naming convention for constraints and indexes (Base.metadata in src/infrastructure/database/session.py), so autogenerated names stay stable across regenerations.

Addresses stored before they were canonical

User declares ix_user_email_lower, a unique index over lower(email), so the database keeps one account per address whatever case it was written in. A database written by an older version can hold Foo@x.com and foo@x.com at once, and creating the index on it fails. Run uv run --no-sync python scripts/canonicalize_emails.py from backend/ first, or docker compose exec api python -m scripts.canonicalize_emails in the container: it rewrites the stored addresses into the canonical form and adds the index, or names the colliding addresses and changes nothing.

Avatar URLs stored before the column had a length

User.profile_image_url is String(2048), so the panel and a seeding script are held to the length a caller is. The column had no length before, and the revision that adds one fails on a database holding a longer value. Find them first:

SELECT id FROM "user" WHERE length(profile_image_url) > 2048;

Those rows hold a URL nothing can serve; shorten them, or put them back to the default (https://profileimageurl.com), before applying the revision.

Two rate-limit rules for one path

RateLimit declares uq_rate_limits_tier_path, a unique index over (tier_id, path) for the rows a soft delete has not taken out, so a tier limits a path with one rule. A database written by an older version can hold two, and creating the index on it fails. Find them first:

SELECT tier_id, path, count(*) FROM rate_limits
WHERE is_deleted = false GROUP BY tier_id, path HAVING count(*) > 1;

Keep the rule each tier should apply to the path and delete the rest. Until then the limit a caller gets is whichever row the lookup reads first.

Existing databases

Databases created before the naming convention was added keep PostgreSQL's default constraint names (user_pkey, user_tier_id_fkey, tiers_name_key). The first revision autogenerated against such a database drops and recreates the unique constraints under the new names and drops the duplicate ix_api_keys_key_prefix, ix_key_usage_endpoint and ix_key_usage_status_code indexes — review it before applying. Alembic doesn't compare primary and foreign key names, so rename those yourself before any migration refers to them by name:

ALTER TABLE "user" RENAME CONSTRAINT user_pkey TO pk_user;
ALTER TABLE "user" RENAME CONSTRAINT user_tier_id_fkey TO fk_user_tier_id_tiers;

Applying a baseline to a database that already has tables¶

A database an older release built with CREATE_TABLES_ON_STARTUP, or a dev database you want to keep, already holds the tables the baseline creates. Applying the baseline to it fails on the first one:

asyncpg.exceptions.DuplicateTableError: relation "roles" already exists

The baseline describes a schema that database already has, so tell alembic that one revision is already there rather than running it. Stamp the baseline's own revision id, not head: head marks every revision applied, including the ones after the baseline, so the changes they carry are skipped and never run.

cd backend
uv run --no-sync alembic history | tail -1      # <base> -> 866640ae8b30, baseline
uv run --no-sync alembic stamp 866640ae8b30     # records the baseline as applied, runs none of it
uv run --no-sync alembic upgrade head           # runs every revision after the baseline
uv run --no-sync alembic check                  # does the database match the models?

alembic history lists revisions newest first, so the last line is the baseline; the id is also the first part of the file name in migrations/versions/. stamp writes that id into alembic_version and executes nothing, so the rows are untouched.

alembic check is what tells you whether stamping was honest, and it runs after the upgrade: it works by autogenerating, so a database with a revision still pending makes it refuse with Target database is not up to date. rather than report anything. On the day a project first adopts migrations the baseline is the only revision, so there is nothing to upgrade and stamp then check is the same sequence.

It diffs the models against the live database and exits non-zero listing what differs — it compares the models, not the revision history, so it answers "does this database hold today's schema" rather than "was the stamp right". A database an older release built with create_all usually does differ from a baseline generated from today's models, and check names each difference as an operation:

ERROR [alembic.util.messaging] New upgrade operations detected:
    [('add_column', None, 'user', Column('token_version', Integer(), nullable=False))]

Fix those on that database by hand, with the ALTER TABLEs they describe. A revision that added them would be wrong: every database built from the baseline already has them, so that revision would fail there. The alternative, if there are many, is to generate the baseline from the release that built the database and carry later revisions for what changed since — then the history describes the path those databases actually took.

The order, for a deployment moving off auto-created tables:

The two repairs, which rewrite rows the newer code reads differently. From backend/ on the host:
```
uv run --no-sync python scripts/canonicalize_emails.py
uv run --no-sync python scripts/cleanup_api_key_json.py
```
Or in the container, where the working directory is /app/src and the path form finds nothing:
```
docker compose exec api python -m scripts.canonicalize_emails
docker compose exec api python -m scripts.cleanup_api_key_json
```
2. Generate the baseline against an empty database, review it, commit it (above). 3. alembic stamp <baseline revision> on each database that already has the tables. 4. Apply every later revision the normal way, alembic upgrade head, then alembic check to see what that database is still missing against today's models, and fix those by hand. 5. Turn CREATE_TABLES_ON_STARTUP off, which is its default outside local and development, so nothing creates tables beside alembic again. python -m scripts.setup_initial_data honours it: with the setting off it seeds rows and creates no tables, so run it after step 4.

Configuration¶

`backend/alembic.ini`¶

The shipped config sets:

script_location = %(here)s/migrations — migration files live in backend/migrations/
prepend_sys_path = . — so src.* resolves when running from backend/
sqlalchemy.url = driver://user:pass@localhost/dbname — a placeholder; the real URL is overridden in env.py

`backend/migrations/env.py`¶

The boilerplate's env.py does three things you'll want to know about:

from src.infrastructure.config.settings import settings
from src.infrastructure.database.session import Base

# 1. The real DATABASE_URL is taken from app settings (which build it from POSTGRES_*)
config.set_main_option("sqlalchemy.url", settings.DATABASE_URL)


# 2. Production safety check — refuses to run unless explicitly opted in
def validate_production_migration():
    if os.getenv("ENVIRONMENT") == "production":
        if os.getenv("CONFIRM_PRODUCTION_MIGRATION") != "yes":
            raise Exception(
                "Production migration requires CONFIRM_PRODUCTION_MIGRATION=yes"
            )


# 3. Auto-import every module under src.modules so Alembic sees all models
import_models("src.modules")
target_metadata = Base.metadata

The auto-import walks src.modules recursively — you don't need to maintain a list of model imports for --autogenerate to find new tables. Just create the model file under modules/<feature>/models.py and it's discovered. Import errors fail fast rather than being skipped, so a broken model module can't silently produce a migration full of drop_table statements.

Workflow¶

All commands run from backend/:

cd backend

1. Generate a Migration¶

After you change a model:

uv run --no-sync alembic revision --autogenerate -m "Add user profile fields"

Alembic compares the current models with the database schema and writes a new file in backend/migrations/versions/.

2. Review the Generated Migration¶

Always read it before applying. Autogenerate isn't perfect — it can miss enum changes, server-side defaults, computed columns, and complex constraint renames. A typical generated file:

"""Add user profile fields

Revision ID: abc123def456
Revises: prev_revision_id
"""

from alembic import op
import sqlalchemy as sa

revision = "abc123def456"
down_revision = "prev_revision_id"
branch_labels = None
depends_on = None


def upgrade() -> None:
    op.add_column("user", sa.Column("bio", sa.String(500), nullable=True))
    op.add_column("user", sa.Column("website", sa.String(255), nullable=True))
    op.create_index("ix_user_website", "user", ["website"])


def downgrade() -> None:
    op.drop_index("ix_user_website", "user")
    op.drop_column("user", "website")
    op.drop_column("user", "bio")

3. Apply¶

# All pending migrations
uv run --no-sync alembic upgrade head

# Step forward N revisions
uv run --no-sync alembic upgrade +2

# Up to a specific revision
uv run --no-sync alembic upgrade abc123def456

4. Inspect¶

uv run --no-sync alembic current      # current revision
uv run --no-sync alembic history      # full history
uv run --no-sync alembic heads        # any branched heads
uv run --no-sync alembic show <rev>   # details about a specific revision

5. Roll Back¶

uv run --no-sync alembic downgrade -1                # one step back
uv run --no-sync alembic downgrade <revision>        # to a specific point
uv run --no-sync alembic downgrade base              # all the way back

Test your downgrade in dev — it's the cheapest way to spot a missing op.drop_index or similar.

Common Scenarios¶

Adding a New Model¶

Create the module folder with the model:

# backend/src/modules/widgets/models.py
from sqlalchemy import String
from sqlalchemy.orm import Mapped, mapped_column

from ...infrastructure.database.models import SoftDeleteMixin, TimestampMixin
from ...infrastructure.database.session import Base


class Widget(Base, TimestampMixin, SoftDeleteMixin):
    __tablename__ = "widgets"

    id: Mapped[int] = mapped_column(
        "id", autoincrement=True, nullable=False,
        primary_key=True, init=False,
    )
    name: Mapped[str] = mapped_column(String(100), unique=True, index=True)

Register it in modules/__init__.py so other code can import it:
```
from .widgets.models import Widget
```
(The migrations env.py auto-imports src.modules, so the new model is picked up regardless — but adding it to modules/__init__.py keeps the public API explicit.)

Generate and apply:

uv run --no-sync alembic revision --autogenerate -m "Add widgets table"
# review backend/migrations/versions/<timestamp>_add_widgets_table.py
uv run --no-sync alembic upgrade head

Adding a Foreign Key¶

# Add to the model
class Widget(Base, ...):
    owner_id: Mapped[int] = mapped_column(ForeignKey("user.id"), index=True)

uv run --no-sync alembic revision --autogenerate -m "Add widget.owner_id"

The generated migration will include the column, the FK constraint, and the index.

Data Migrations¶

Sometimes a schema change needs a data backfill. Edit the autogenerated upgrade to include it:

def upgrade() -> None:
    # 1. Add nullable column
    op.add_column("post", sa.Column("category_id", sa.Integer(), nullable=True))

    # 2. Backfill
    bind = op.get_bind()
    bind.execute(sa.text(
        "INSERT INTO category (name, slug) VALUES ('General', 'general')"
    ))
    default_id = bind.execute(
        sa.text("SELECT id FROM category WHERE slug = 'general'")
    ).scalar_one()
    bind.execute(sa.text(
        "UPDATE post SET category_id = :cid WHERE category_id IS NULL"
    ), {"cid": default_id})

    # 3. Tighten the constraint
    op.alter_column("post", "category_id", nullable=False)
    op.create_foreign_key(
        "fk_post_category_id", "post", "category", ["category_id"], ["id"]
    )

Renaming a Column¶

def upgrade() -> None:
    op.alter_column("user", "full_name", new_column_name="name")


def downgrade() -> None:
    op.alter_column("user", "name", new_column_name="full_name")

For columns with foreign keys or indexes, autogenerate may produce drop-and-recreate instead of a rename. Edit it to use alter_column if you want to preserve data.

Dropping a Table¶

def upgrade() -> None:
    op.drop_table("old_table")


def downgrade() -> None:
    op.create_table(
        "old_table",
        sa.Column("id", sa.Integer(), nullable=False),
        sa.Column("name", sa.String(50)),
        sa.PrimaryKeyConstraint("id"),
    )

Production Migration Strategy¶

The boilerplate adds a hard guard against accidental production migrations: with ENVIRONMENT=production, Alembic refuses to run unless CONFIRM_PRODUCTION_MIGRATION=yes is set.

# Production migration — explicit confirmation required
ENVIRONMENT=production CONFIRM_PRODUCTION_MIGRATION=yes uv run --no-sync alembic upgrade head

Recommended Flow¶

Develop and test the migration locally against a copy of production data
Deploy code with the new migration files to staging

Back up the staging database:

pg_dump -h staging-db -U user dbname > staging_backup_$(date +%Y%m%d_%H%M%S).sql

Apply against staging and run a smoke test
Schedule a maintenance window if the migration is destructive

Back up production:

pg_dump -h prod-db -U user dbname > prod_backup_$(date +%Y%m%d_%H%M%S).sql

Apply against production with the explicit confirmation env var
Watch logs and metrics post-migration

Docker Migration Job¶

bp deploy generate prod (and nginx) writes a migrate service for this, built from the migrate stage in backend/Dockerfile, which runs alembic upgrade head and exits. It sits behind a compose profile, and nothing waits on it:

docker compose --profile migrate run --rm migrate   # then, only if it succeeded:
docker compose up -d

Running it first and restarting afterwards is the order that matters: a migration that fails leaves the old code serving on the old schema, instead of restarting the API into a schema it can't use. Deploying a schema change to a running stack is the same two steps — migrate, then docker compose up -d to replace the containers that changed.

Best Practices¶

Always review autogenerated migrations¶

Autogenerate misses enum changes, server-side defaults, certain constraint renames, and computed columns.

Use descriptive messages¶

# Good
uv run --no-sync alembic revision --autogenerate -m "Add user.email_verified"

# Less useful three months later
uv run --no-sync alembic revision --autogenerate -m "Update user model"

Adding a non-nullable column to a populated table¶

Do it in three steps in the same migration:

def upgrade() -> None:
    op.add_column("user", sa.Column("phone", sa.String(20), nullable=True))
    op.execute("UPDATE \"user\" SET phone = ''")
    op.alter_column("user", "phone", nullable=False)

Test downgrades¶

uv run --no-sync alembic downgrade -1
uv run --no-sync alembic upgrade head

If the down step blows up, fix it before merging the migration.

Don't commit auto-generated `.pyc` files¶

The migrations/versions/ directory should only contain hand-written .py files. Alembic does not track .pyc.

Troubleshooting¶

"Target database is not up to date"¶

You created a new revision but the database is one or more revisions behind. Run uv run --no-sync alembic upgrade head first, then generate the new revision.

"Multiple heads detected"¶

Two branches both added migrations from the same parent. Merge them:

uv run --no-sync alembic merge -m "merge heads" <head1> <head2>

Autogenerate produces an empty migration¶

You haven't actually changed the schema — or target_metadata doesn't see your model. Check that the new module is importable from src.modules (the auto-importer in env.py walks that package).

Migration applies in dev but fails in prod¶

Common causes: data the dev DB doesn't have (e.g. an unindexed column with NULLs you tried to make NOT NULL), different Postgres versions, or extensions installed only in one environment. Always test against production-like data.

Next Steps¶

CRUD Operations — Use the migrated schema
API Endpoints — Build endpoints on the new model
Production — Production deployment guide