migration

Creating database migrations

Migrations in this project are generated via the Makefile, which wraps Alembic's autogenerate. You never write migration files by hand, and you never edit an existing migration file.

The three Makefile targets

upgrade:    alembic upgrade head                # apply all pending migrations
downgrade:  alembic downgrade -1                # roll back the latest migration
generate:   alembic revision --autogenerate -m "$(NAME)"   # create a new migration

Run them from the repo root. The generate target requires a NAME variable — Alembic uses it as the migration message (and as part of the filename slug).

The standard workflow

When the user asks for a schema change, follow this exact order:

1. Edit the SQLAlchemy model under src/database/psql/models/<entity>.py. Add/remove columns, indices, constraints, relationships — whatever the request needs.
2. Update companion types under src/database/psql/types/<entity>.py:
   • If a new writable column: add it to Create<Entity>Type (required) and/or Update<Entity>Type (optional, total=False).
   • If a new relationship: add its name to <Entity>Loads = Literal[...].
3. Generate the migration via the Makefile — do not call alembic directly:

   make generate NAME="add_is_verified_to_user"
   

   Alembic autogenerate will diff the model metadata against the current DB schema and write a new file to migrations/versions/NN_<hash>_add_is_verified_to_user.py. The NN_ numeric prefix is added by the project's naming convention — Alembic appends the hash + slug.
4. Inspect the generated file. Autogenerate is imperfect — it often misses:
   • Column type changes (e.g. String(32) → String(64) may require manual alter_column with existing_type)
   • Index renames
   • Enum value additions (Postgres ALTER TYPE ... ADD VALUE)
   • server_default changes
   • Data migrations (filling a new NOT NULL column for existing rows)