Async request-scoped DB session, secondary-site read-only guard, selectinload convention#

First ADR touching DB session / pooling / async in this repo. Foundation slice of epic #187 (async DB session migration, PRD 0002); formally retires the descoped design slice #166.

Context#

Every authenticated request runs sync DB work (Depends(get_db)) on the event loop, blocking it. Epic #187 migrates the request path to a single canonical request-scoped AsyncSession. This slice establishes the shared infrastructure and conventions every later slice builds on, without changing any endpoint behaviour yet.

Decisions#

1. get_async_db is the canonical async session dependency#

get_async_db (previously wired into zero routers) becomes the one async session dependency. Contract:

session = session_factory()
try:
    yield session
except Exception:
    await session.rollback()
    raise
finally:
    await session.close()

It never commits on the endpoint’s behalf — write endpoints call await session.commit() themselves. The lifespan-owned factory keeps expire_on_commit=False, so instances stay usable after commit (response serialization).

2. Secondary-site local session is read-only + autoflush=False (new hardening)#

The secondary-site local DB is a streaming replica. The retired get_db() only gated DDL at init and set autoflush=False; neither stops an explicit runtime write. This slice adds a real guard on the async local engine:

  • Read-only via asyncpg connect_args={"server_settings": {"default_transaction_read_only": "on"}}. asyncpg applies server settings at connection startup and they persist for the connection’s lifetime. A connect-event SET was tried first and rejected: SQLAlchemy’s connection-reset rolls the SET back, silently defeating the guard (the GUC reads on yet writes still succeed). A write now raises asyncpg.exceptions.ReadOnlySQLTransactionError.

  • autoflush=False on the local session factory, so ORM autoflush can’t emit writes.

This is a deliberate behaviour change, not restored parity: it can surface previously-silent direct writes at the secondary site. Rollout note: watch secondary-site logs for ReadOnlySQLTransactionError after deploy — each one is a real write that was silently hitting the replica before.

Postgres/asyncpg-only; ignored for other URLs (the sqlite test harness relies on this being a no-op). On the main site the local and main engines are the same object and is_secondary is False, so neither the read-only guard nor autoflush=False applies there.

3. selectinload, not joinedload, for relationship loading#

The migration eager-loads relationships with selectinload. User.roles and the other migrated relationships are collections (lazy="select"); joinedload on a collection forces .unique() handling on the Result for no benefit, whereas selectinload issues a clean second SELECT. Later slices auditing existing joinedload collection use must either switch to selectinload or add .unique().

4. raiseload("*") on the test session factory only#

The async test harness (tests/async_harness.py) attaches a do_orm_execute event applying raiseload("*") to a test-only Session subclass. Any endpoint whose response serialization would lazy-load in production then fails deterministically in CI (InvalidRequestError) instead of crashing with MissingGreenlet in production. This is not applied to production model lazy= defaults: the sync consumers data-transfer / workflow-manager legitimately rely on lazy loading.

5. Permission decorators dispatch sync vs async endpoints explicitly#

The auth slice converts get_current_user, get_current_user_optional, get_service_user, verify_jwt_token, verify_api_token and get_token_type onto the async session (await session.execute(select(...))) with selectinload(User.roles) at every auth query site, so the ~16 downstream .roles reads never lazy-load off the async session.

That conversion exposed a latent decorator bug worth recording. FastAPI/Starlette decide whether to offload an endpoint to a threadpool by inspecting the function it is handed — but for a @require_permissions / @require_roles endpoint that is the async wrapper, never the wrapped endpoint. A plain def endpoint under these decorators therefore had its blocking body executed directly on the event loop, defeating the threadpool protection for ~142 endpoints; and require_roles additionally did an unconditional await func(...), which raises TypeError on a sync endpoint the moment one is added.

The wrappers now dispatch on the wrapped function themselves (_dispatch_endpoint): await func(...) when inspect.iscoroutinefunction(func), else await run_in_threadpool(func, ...) (from starlette.concurrency). This restores the offload for sync endpoints and awaits async ones directly, at both call sites of require_permissions (the API-token-scope branch and the role-permission branch) and in require_roles.

Scope note: the low-frequency token-management CRUD (routers/api_tokens.py) was deferred while the auth hot path converted; the final slice (#186) moves its endpoints onto get_async_db too. Rather than rewrite the sync auth/token_manager.py helpers — which lean on lazy relationship access (token.user, user.api_tokens, admin_user.roles) and are used nowhere else — each manager call is bridged onto the async session’s underlying sync Session via await db.run_sync(fn, ...), the same greenlet-offload the pipeline/diagnostics routers use for their external sync-only dependencies. github_auth.py’s login/role-mutation path is on the request path and is converted to async: it eager-loads roles before .append()/.remove() and commits explicitly, and its post-commit user.username read stays valid under expire_on_commit=False.

Consequences#

  • Later slices convert endpoints onto get_async_db + selectinload, exercised by the sqlite+aiosqlite harness; the read-only guard is exercised by a Postgres testcontainer (locally) / CI service container.

  • Final slice (#186): get_db(), get_db_session() and the sync init_ccat_ops_db engine machinery are deleted — the async pool is now the only DB pool an API process holds. The async engine gains a fail-fast pool_timeout (Dynaconf ASYNC_POOL_TIMEOUT, default 10s, via _async_pool_timeout()): once all pool_size + max_overflow (5 + 10 = 15) connections are checked out, a further request raises TimeoutError after 10s instead of hanging on the default 30s and stacking requests behind an exhausted pool. A saturated pool now fails loudly (fast 500, retryable) rather than degrading into silent pile-up.