Skip to main content

Database and migrations

PostgreSQL is the primary database. SQLite is acceptable for CLI, local, or lightweight applications. Add Redis only when caching or ephemeral state is really needed. Do not introduce another database without a use case that justifies it.

SQLAlchemy​

  • Use SQLAlchemy 2.x.
  • AsyncSession for FastAPI services; sync Session for batch jobs.
  • Provide the session through a get_db dependency in the composition root.
  • ORM models live in infrastructure/; domain entities live in domain/ and are different classes.
  • Own the transaction boundary in the application layer (use case), not in repositories or routes.

Table conventions​

  • Column order is id → data → audit (created_at, updated_at).
  • Define id, created_at, and updated_at explicitly on each concrete model. An abstract base that declares them forces them to the front of the column order.
  • Use timezone-aware timestamps.
  • Name constraints and indexes explicitly so migrations are stable across environments.
  • Declare foreign keys, NOT NULL, and uniqueness in the schema, not only in application code.

Migrations​

Alembic owns the schema. See ADR-003.

alembic revision --autogenerate -m "add project status"
alembic upgrade head
  • Review every autogenerated migration by hand. Autogenerate misses renames and data changes.
  • Every model change ships with its migration in the same pull request.
  • Never auto-migrate inside application code at import time. Run migrations as an explicit step (deploy job or container start script) that must succeed before the app starts serving.
  • Migrations must be reversible where practical. Document irreversible ones in the migration docstring.
  • Never edit a migration that has been merged; add a new one.

Local development​

compose.yaml runs PostgreSQL. make dev applies migrations before starting the app.

Test isolation​

Prefer a real PostgreSQL in integration tests (container or CI service) when behavior depends on Postgres features. SQLite in-memory is acceptable only for tests whose behavior does not depend on dialect. See backend testing.