Document sync-Session + async-route pattern; add optional AsyncSession dependency

[chriscarrollsmith]

Context

PR #236 fixed event-loop blocking by:

1. Offloading get_user_from_request DB work via run_in_threadpool
2. Converting falsely-async routes (async def with no await) to sync def so FastAPI runs them in the threadpool

That leaves a design gap for template users who occasionally need a true async route (uploads, outbound HTTP, streaming) while the app’s default DB stack remains sync Session + Depends(get_session).

Goals

1. Document pattern: prefer sync routes; structure rare async routes carefully

Document in contributor/template docs (e.g. README and/or .cursor/rules/routers.mdc):

• Default: use sync def route handlers for anything that does SQLModel/Session work. FastAPI runs these in a threadpool; session create/use/close stay on one worker.
• Reserve async def for real awaits only (UploadFile / request.form(), httpx, streaming, etc.).
• When an async route also needs the DB:
  • Do awaits first, then use the Depends-provided sync Session on that phase without shipping the Session (or ORM instances) into another run_in_threadpool call; or
  • Offload only CPU-bound work (e.g. image processing) via run_in_threadpool, keeping session.commit() where the session already lives; or
  • Open a fresh Session inside the worker and re-load by primary key (pass only plain data across the boundary).
• Do not pass a Session or attached ORM objects into run_in_threadpool — SQLAlchemy sessions are one-thread-at-a-time.

2. Optionally implement AsyncSession dependency

Add a parallel dependency for async-first routes, e.g. get_async_session, backed by create_async_engine / AsyncSession, so template users who need async DB I/O have a first-class path without abusing sync Session from the event loop.

Scope sketch:

• Async engine lifecycle alongside the existing sync engine (or documented migration notes)
• async def get_async_session() yield/async with cleanup
• One or two example routes or a short doc snippet showing when to use which dependency
• Tests for the dependency (open/close, override in TestClient if applicable)
• Keep sync get_session as the default for the template’s existing routers

Non-goals

• Rewriting all routers to AsyncSession
• Changing auth/session cookie flows beyond what’s needed for the async dependency example

Related

• PR Fix get user from request blocks loop #236 — event loop blocking / falsely-async route cleanup

[chriscarrollsmith]

Opened as follow-up from review of #236.