---
status: accepted
---

# 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.