bike-app/apps/api

apps/api

Python 3.12 / FastAPI / SQLAlchemy 2.0 async / Alembic / SQLite. See docs/PLAN.md for the overall design and docs/DECISIONS.md (D15 especially) for why the database is SQLite and not the Postgres+PostGIS setup Phase 0 originally shipped with.

Layout

velodrome/
  app.py              FastAPI app factory
  config.py            Settings (env-driven, see below)
  db.py                Single engine + the repository-layer scope — read this first, it's the
                        load-bearing module for user isolation now that there's no RLS
  ids.py                UUIDv7 generation
  models/               SQLAlchemy models
  auth/                 Password hashing, session service, FastAPI auth dependencies
  api/v1/                Route handlers
  schemas/               Pydantic request/response models
alembic/                 Migrations. 0001_baseline.py creates the identity tables — no roles, no
                          RLS, no GRANTs, none of those concepts exist in SQLite.
tests/                    pytest, against a real SQLite file, never mocked

Why isolation is enforced in Python now, not the database

Phase 0 originally ran two Postgres roles (velodrome_app/velodrome_auth) with row-level security as a database-enforced isolation layer. SQLite has no roles, no session variables, and no policy engine — there is no database-level backstop anymore. This is the single most important thing to understand before touching auth/ or db.py:

• scoped_session(user_id) — for every query against a user-owned table once identity is known. Yields a Scope, whose select() is the only way to build a query through it, and every query it builds is pre-filtered to that user_id on any model that declares one. There is no method on Scope that returns an unfiltered query — see tests/test_auth.py's test_scope_select_rejects_models_without_user_id for what happens if you try it on a model that isn't user-owned (Invite, scoped by created_by rather than user_id, is the real example used there).
• auth_session() — used only by auth/service.py, for the narrow set of lookups that must happen before identity is known: login by email, a session by its token hash, an invite by its code hash, plus the inserts that create those rows. Every one of those queries is an exact match on a unique key, never an unfiltered scan — that discipline is what makes it safe to use a plain session here instead of Scope.
• unscoped_session() — a plain session with no scoping applied at all, for health checks and anything that never touches a user-owned table. tests/test_auth.py's test_unscoped_session_can_see_every_user_when_misused demonstrates, deliberately, what happens if this gets used on a user-owned table instead of scoped_session — it sees everyone's rows. That test exists to make the point vivid: reaching for unscoped_session() (or a raw auth_session() query) against a user-owned table is a review-blocking mistake now, not a style preference, because there's nothing else standing behind it.

If you're adding a new table with per-user data: give it a user_id column and query it only through scoped_session. See CLAUDE.md's invariant #4.

Two SQLite behaviours that don't match the defaults you'd expect

Both confirmed empirically against real aiosqlite, not assumed from docs — see db.py's _configure_sqlite_for_concurrent_writers for the fixes:

1. Foreign keys, ON DELETE CASCADE included, are OFF by default per connection. Without PRAGMA foreign_keys=ON, deleting a user silently leaves its sessions/api_tokens behind instead of cascading — no error either way.
2. Transactions default to DEFERRED, which only takes a write lock on the first actual write — leaving a real check-then-act race window (e.g. two concurrent redemptions of the same invite code both reading used_count < max_uses as true before either commits). Every transaction on this engine issues BEGIN IMMEDIATE instead, which takes the write lock up front. One consequence worth knowing if you're writing tests: a session that autobegins a transaction via a bare read and never explicitly commits/rolls back holds that write lock until the session closes — see the comment above await db_auth.commit() in test_scoped_session_blocks_cross_user_reads for a real example of this biting a long-lived test fixture.

Also worth knowing: Uuid(as_uuid=True) stores as 32-char hex with no hyphens on SQLite, not str(uuid)'s hyphenated form. This only matters if you ever write a UUID into this schema via raw SQL instead of the ORM (as the test fixtures do, to set up state without going through the API) — use .hex, not str(), or the ORM's own later queries against that row won't match it. See the docstring on _seed_invite in tests/test_auth.py for the failure this caused when it was gotten wrong.

Environment variables

Variable	Used by	Notes
VELODROME_DATABASE_URL	The app and Alembic, both	e.g. sqlite+aiosqlite:////data/velodrome.db. One DSN — there's no separate migration role anymore since SQLite has no roles to separate.
VELODROME_SECRET_KEY	The app	Not yet used (arrives with the Bryton credential encryption in a later phase); declared now so the settings shape is stable.
VELODROME_ENVIRONMENT	The app	development / test / production. Gates the session cookie's Secure flag — see the comment in api/v1/auth.py before changing this condition.
VELODROME_PUBLIC_URL	The app	Used for the CSRF Origin check on cookie-authenticated mutations.

Running locally

uv sync --all-extras

export VELODROME_DATABASE_URL=sqlite+aiosqlite:///./velodrome-dev.db

uv run alembic upgrade head
uv run uvicorn velodrome.app:app --reload

GET /api/v1/healthz should return {"status": "ok"}; GET /api/v1/docs has interactive Swagger UI outside production.

Testing

uv run ruff check . && uv run ruff format --check .
uv run mypy --strict velodrome
uv run pytest

Tests run against a real SQLite file in a temp directory (never :memory:, which gives each separate connection its own isolated database rather than one shared one, and never mocks for DB behaviour, per CLAUDE.md). The suite runs the real Alembic migration at session start, not a parallel schema-creation shortcut, so it's exercising the exact same path a real deploy uses.

The test worth reading first if you're new to this codebase is tests/test_auth.py::test_scoped_session_blocks_cross_user_reads — it doesn't trust that Scope.select() filters correctly because the code reads correctly; it proves it by registering two real users and confirming a scoped read for one never returns the other's row, even though both exist in the same table.

A note on alembic check

CI's migrations job runs alembic check to catch drift between the ORM models and the actual migrations. There's no include_object filter in alembic/env.py anymore — Phase 0's version needed one to exclude PostGIS/TIGER's own pre-installed tables from the comparison, but SQLite starts with nothing but what this app's own migrations create, so there's no foreign-table noise to filter out in the first place.