Async DB foundation and seams

## Original requirement (verbatim) > "Make the control-plane API highly concurrent and distributed: many processes, no per-task connection caps." Epic 12693. Settled upstream: PRD (task 12694) and Architecture (task 12695). The operator overrode the PRD's multi-worker reading (D3): ONE Granian process per task on free-threaded CPython, async DB I/O on one shared pool. The full design (decisions D1–D9, risks, oracle tooling) is the `architecture` artifact of task 12695; read it (`artifact_read` / `task_read` on 12695) for the pinned seams S1–S5 (§4), the measured cause (§1), material risks (§6) and the burst-probe tooling and baseline (§8) before you start. This body carries this child's scope, acceptance and fences verbatim. ## Scope of this child: C2 — Async DB foundation (Architecture §5, verbatim) ### C2 — Async DB foundation - Build S1, S2, S3 and S4. - **Holder tracking:** `app/db.py`'s `_held`, `_checkout_depth` and `_lane` are thread-keyed, which is wrong for coroutines (all requests share one loop thread). For the async pool, key on the asyncio task through a contextvar. - `pool_watch` samples the async pool: metrics `DbPoolCheckedOutMax`/`DbPoolCheckoutTimeouts` with `Lane=async`. The EMF names are a contract with the monitoring terraform (`app/metrics_emf.py:25`). - **Thread limiter:** stop sizing the anyio thread limiter to the request pool. Sync routes still in the allowlist keep working, but a thread no longer stands for a connection. The limiter becomes `API_THREAD_LIMIT`, default 200. - **Convert the request path's shared plumbing to async:** - principal and identity reads (`IdentitySessionLocal` callers in auth dependencies) → `async_session(identity=True)`; - the idempotency layer; - the tenancy `bind` dependency. These run on every request, so the whole app benefits before any batch lands. - **Prove the seam:** - a test where a cross-org row is filtered through an `AsyncSession`, and a write under the wrong scope is refused; - a test that two concurrent requests each hold their own connection only during their statements. - **ADR:** add one register entry, "Control-plane DB access is async: one pool per task, connections held per statement, never across a non-DB await". Its home is `app/db_async.py`. It sits in `docs/adrs/README.md` beside ADR-0142. ## Approach (planner) Build S1/S2 first with their tests, then the tenancy proof (it gates every batch), then the ratchet and S4 test, then convert the shared per-request plumbing and shift its connections. ## Applies to every child (verbatim) Each child is one implementer's PR. **Dependencies are data/API only.** Children that edit the same file are not serialized; they rebase at merge. **Every child owns AC15:** its merge's Jaah Deploy must go green, and dev `/api/health` must answer 200. Every child that changes request-path behaviour also runs the S4 regression probe. ## Constraints (PRD §7, verbatim) ## 7. Constraints - Merges go through the merge lane on `./dc check full`. Each merge's Jaah Deploy must be watched to green (project instruction). - Migrations, if any, must be additive while two releases overlap (`docs/standards/coding-standards.md`). - The deploy overlap doubles tasks, so every connection-budget formula carries ×2. - Live state is read through the console UI only (CLAUDE.md hard rule). `tools/cp_psql.sh` was refused by the credential broker for this BA run (`org/aws/cp-psql-reader`), so current dev latency was not measured here. The Architect must get it (AS6, AC12). Credentials & reach: | Oracle | Credential / identity / environment | Status | |---|---|---| | Burst probe, AC3, AC5, AC8, AC10 (console as operator) | `me/jaah/debug/main/operator-login` on `app.dev.jaah.ai` | verified: `credential_get` via broker OK. Login 200 and timed GETs from this node (ts node of task 12695). | | AC12, C8 metrics (dev + main CloudWatch) | `me/jaah/system/bootstrap-admin` → `OrganizationAccountAccessRole` in `jaah-dev` / `jaah-main`, read-only | verified: ECS `describe-services` and CloudWatch `get-metric-statistics` succeeded on both from this node. | | AC13, AC14 (gate) | Worktree only (`./dc check full`, `tools/check_context.py`) | verified: nothing beyond the worktree. | Out of scope: - Do NOT migrate route handlers beyond the shared plumbing named in scope (C3a–C3e own the routes); do NOT convert background crons/pools (C4). - Do NOT remove `idempotency.MAX_WAITERS` (C6 removes the cap; C2 only changes how waiters wait). - Do NOT delete the sync engines (C8). Epic-wide (PRD §6, verbatim): - **Do not** promote to `master` or change prod's live config as part of done (Q8). Prod tfvars may be edited only so that the R7 budget test covers them. Do not apply them to prod. - **Do not** remove or loosen anonymous/public abuse controls (AS2): the WAF, `SharedRateLimiter` public and trigger-intake limits, read-relay `MAX_SOCKETS_PER_IP` / `ANON_*`, site-chat per-IP limits, headscale edge nginx `limit_conn` / `limit_req` (`infra/jaah/headscale/nginx.conf`). - **Do not** remove resource-safety bounds (AS1): body, frame and response size limits, read timeouts, `pool_timeout`. - **Do not** remove per-ECS-task DB pool sizing as a concept, or exceed RDS `max_connections` (AS3). - **Do not** add a numeric load-test gate or a benchmark harness. None was asked for (Q4, Q7). - **Do not** change node-side caps that are not on the CP-call path: vnode conntrack limits (`apps/jaahd/vnodehelper/`), MicroVM egress `MAX_CONNECTIONS_PER_TAP`, reconcile probe parallelism, `pr_stats_capture` semaphore. - **Do not** change unrelated product limits: plan and org limits (`orgs.limit_*`), `max_accounts_per_node`, node session caps, worktrees-per-node. - **Do not** introduce a leader-elected singleton CP or pin `desired_count` to 1 (ADR-0142 anti-pattern still holds). - **Do not** add a process-local registry consulted by a request path (ADR-0142 anti-pattern still holds). - Do NOT change task CPU/memory, RDS class or autoscaling max (operator D2: sizing out of scope). - Do NOT reintroduce multi-worker / multiple processes per container (operator D3), and do NOT keep a uvicorn fallback (D8). Done means: - C2-a — `apps/api/app/db_async.py` exposes the S1 API exactly (`async_engine`, `get_db`, `DbDep`, `async_session(identity=...)`, `release`, `run_on_loop`) and `apps/api/app/offload.py` exposes `run_cpu` (S2); `async_engine()` raises off the loop thread, `run_on_loop` raises on it — oracle: unit tests for each contract clause. - C2-b — Tenancy seam proven on `AsyncSession`: a cross-org row is filtered, and a write under the wrong scope is refused; contextvars reach `do_orm_execute` under the greenlet — oracle: the new negative tests in the gate. - C2-c — Two concurrent requests each hold their own connection only during their statements; a nested `async_session()` while holding a connection raises under test; a test helper fails a route test when a connection is checked out across a non-DB await — oracle: the new tests. - C2-d — S3 ratchet: `tools/check_async_routes.py` runs in the quick gate, scans every module under `apps/api/app/` declaring an APIRouter/WebSocket route, and fails on a sync handler or `get_session`/`SessionLocal` import outside `apps/api/sync_routes_allowlist.txt` (seeded with every current file) — oracle: `./dc check quick` green; a scratch sync handler added to a non-allowlisted module makes the check fail (shown in PR body, then reverted). - C2-e — Principal/identity reads, the idempotency layer (waiters wait asynchronously, no thread poll) and the tenancy `bind` dependency run async on every request; the anyio thread limiter is `API_THREAD_LIMIT` (default 200), no longer the request pool size — oracle: tests + code citations file:line. - C2-f — `pool_watch` emits `DbPoolCheckedOutMax`/`DbPoolCheckoutTimeouts` with `Lane=async` (EMF names unchanged) — oracle: unit test on the EMF payload; after deploy, the metric appears in CloudWatch `Jaah/dev`. - AC13 — `./dc check full` passes, including `infra/jaah/test_cp_autoscaling.py` updated for the one async pool (S4 formula, C2 shift main 6 / dev 4, total per task unchanged: dev 24, main 31), over dev and main tfvars with max tasks × 2 overlap — oracle: the merge lane gate. - C2-g — ADR register entry "Control-plane DB access is async: one pool per task, connections held per statement, never across a non-DB await", home `app/db_async.py`, beside ADR-0142 in `docs/adrs/README.md`; `python3 tools/check_context.py` passes — oracle: the file and the command. - C2-h (critic MAJOR, budget correctness) — `DB_POOL_SIZE`/`DB_MAX_OVERFLOW` size the async pool (tfvars `cp_db_async_pool_size`/`cp_db_async_max_overflow`). The sync request lane moves to its own env `DB_SYNC_POOL_SIZE`/`DB_SYNC_MAX_OVERFLOW` (tfvars `cp_db_sync_pool_size`/`cp_db_sync_max_overflow`, renamed from `cp_db_pool_size`/`cp_db_max_overflow`). `modules/ecs/main.tf` renders every lane explicitly, 0 included. Today a lane of 0 is omitted (main.tf:1483-1486) and the process silently boots at the code default (`config.py:162-166`). A lane sized 0 is disabled: it opens no engine and any use raises. It never falls back to a default. A new test renders the module's DB env for dev and main tfvars and asserts each lane's effective size in the process equals the figure `test_cp_autoscaling.py` budgets — oracle: that test in the gate. - C2-i (critic MAJOR, drained identity lane) — Every live `IdentitySessionLocal` caller is converted to `async_session(identity=True)`, not only the auth dependencies. That includes `node_bus.py:144`, `login_funnel.py:632`, `stale_tab_guard.py:47,53`, `api_keys.py:246,305,393,525` and `ip_allowlist.py:202`. A gate test boots the app with the identity lane at 0 and exercises those paths — oracle: `git grep -n IdentitySessionLocal -- apps/api/app` shows no live caller outside `db.py`; the test passes. - S4 regression check — after this child deploys, the §8 burst probe on dev is no worse than the baseline at N=24 (`/api/tasks` N=24 max 4.6 s) — oracle: run the burst probe, record wall/p50/max in the PR body and report. Evidence, not a numeric gate; a child that makes it worse is not done. - AC15 — Jaah Deploy to dev succeeds for this child's merge commit on `develop`, and dev `/api/health` answers 200 after it — oracle: GitHub Actions "Jaah Deploy" run for the merge commit is green; `curl https://dev.jaah.ai/api/health` → 200. (If the deploy is cancelled/superseded by another deploy, leave it and complete.)

Async DB foundation merged (#7755, 502122f4, full gate green). The pool, identity reads, idempotency, guard, ratchet and budget (dev 24, main 31) all landed. Jaah Deploy was superseded, so the post-deploy burst probe and the Lane=async CloudWatch check were not run.