# QODY API revision retention and DB connection budget — MC #107292 # QODY API revision retention and DB connection budget — MC #107292 **Status:** Published to QODY BookStack on 2026-08-17 **BookStack location:** `https://docs.alai.no/books/qody` **Source commit:** `b6062f2500fd4074023a9a0aa120af0fb54c3a9d` **Incident:** 2026-08-17, `qody-prod-db` active connections reached 46/50 **Evidence:** `/Users/makinja/system/evidence/107291/` ## What happened `qody-api-prod` used Azure Container Apps Multiple revision mode with `minReplicas=1`. Eight active revisions therefore meant eight running API replicas even though seven revisions carried 0% traffic. Each replica opened runtime/admin Hikari pools and a dedicated PostgreSQL LISTEN connection. The stale revisions held 32 idle application sessions and pushed the normal Azure metric baseline to roughly 40–41 connections. The Sev-1 alert triggered at 46 connections and self-resolved, but the unsafe baseline remained. Emergency mitigation MC #107291 deactivated six old zero-traffic revisions and retained the serving revision plus one rollback. Idle application sessions dropped from 32 to 8; Azure active connections settled near average 16 / maximum 18 while API health and RLS checks remained PASS. ## Important metric correction `ResiliencyRequestsPendingConnectionPool` belongs to the `Microsoft.App/containerApps` proxy/resiliency namespace. It is not a Hikari/JDBC/PostgreSQL pool metric. Its 09:20–09:25 spike did not correlate with the DB connection spike at 10:50 UTC and must not be used as proof of a DB leak. If monitored, use Average/Maximum (never Total), label it diagnostic-only, and correlate it with request retries, timeouts, and latency. ## Permanent retention control After successful API canary promotion in both demo and production, the pipeline: 1. captures `az containerapp revision list -o json`; 2. validates exactly one healthy/provisioned 100%-traffic revision and confirms it is the revision just promoted; 3. protects the revision that served 100% immediately before promotion as the last-known-good rollback; 4. retains any newer 0%-traffic revision as possible functional-smoke evidence, even if its ACA probe state says Healthy; 5. deactivates only healthy 0%-traffic revisions older than the protected rollback. The pure planner is `infrastructure/scripts/retain-api-revisions.mjs`. It makes no Azure calls and is fixture-tested, including a sanitized real incident revision list. On ambiguous state it exits non-zero before any mutation. Unhealthy/unprovisioned canaries are also retained as incident evidence. Partial deactivation is safe to retry because planning is idempotent. Before a new canary is created, the pipeline also aborts if more than two API revisions are already active. Failed-canary evidence must then be manually triaged and explicitly deactivated before another deploy; it cannot accumulate outside the validated four-replica-slot budget. This control is API-only: static guest/admin/kitchen MFEs do not own database pools. Their traffic-cutover behavior remains under MC #106744. ## Connection-budget invariant Revision retention and pool sizing are one joint invariant: ```text active revisions × replicas per revision × max connections per replica + platform/reserved connections <= PostgreSQL max_connections ``` At the incident configuration, `2 × 2 × (12 runtime + 5 admin) = 68`, already above the B1ms ceiling of 50 before Azure/system reserve. The shipped remediation uses runtime max 4 and admin max 5 with an eight-connection reserve: `(4 + 5) × 2 × 2 + 8 = 44/50`, leaving six connections of hard-cap headroom. This is not a normal operating target; existing Sev-1 RULE-04b intentionally pages earlier at 43. The four modeled replica slots also cover the canary peak: the traffic-serving revision may scale to two replicas while the prior rollback and new 0%-traffic canary remain at one minimum replica each (`2 + 1 + 1 = 4`). `ConnectionBudget` parses bounded environment overrides and fails startup before creating either pool if the joint invariant is unsafe. The admin pool now has explicit 60-second idle, 10-second acquisition, and 3-second validation timeouts. Its permanently borrowed LISTEN connection is not idle in Hikari and is therefore not reaped by idleTimeout. ## Pool telemetry and attribution Both Hikari pools use the official Micrometer tracker. A one-minute internal reporter reads pool MXBeans without borrowing a connection and exports active, idle, pending, total, maximum, timeout count, and mean/max acquisition latency to structured ACA console logs. Public `/health` does not expose pool capacity. PostgreSQL `application_name` is `qody-@`, sanitized and collision-safe within the 63-byte server limit. This identifies a pool/revision for a connection's lifetime; it does not identify individual requests. Canonical author-only alert definitions are: - RULE-04c — Hikari pending connection waiters; - RULE-04d — Hikari acquisition-timeout counter increase; - RULE-04e — configured joint-budget headroom below four. They extend the DB-pool portion of MC #104302 and leave existing PostgreSQL RULE-04/RULE-04b unchanged. After an approved deployment, verify the platform-provided revision identity and DB attribution (commands documented here, not executed during implementation): ```bash az containerapp exec -g rg-qody-prod -n qody-api-prod \ --revision \ --command "printenv CONTAINER_APP_REVISION" # Through an approved DB session; expected qody-runtime@... / qody-admin@... SELECT application_name, state, count(*) FROM pg_stat_activity WHERE datname = 'qody' GROUP BY application_name, state; ``` ## Runbook correction The former QODY pointer `~/system/runbooks/azure-aca-incident.md` did not exist. The versioned incident playbook is now `docs/security/incident-response.md`, with QODY-specific rollback and automatic-retention behavior in `RUNBOOK.md`. ## Rollback The newest healthy 0%-traffic revision stays active specifically for fast rollback. Operators may route 100% traffic to it using the existing `az containerapp ingress traffic set` procedure in `RUNBOOK.md`. Automatic retention never changes traffic weights and never deactivates failed canaries. ## Scope boundaries Not introduced by this task without separate CEO approval: - PostgreSQL SKU or `max_connections` change; - API replica-count increase; - PgBouncer/pgcat; - `ALTER ROLE ... CONNECTION LIMIT`; - standalone/cron cleanup or failed-canary cleanup; - production deployment. ## Production rollout completion — 2026-08-18 - Final source: `71fd83c778c90ae1ed320b095b6387fbc96c7a49`; production build **1126 succeeded** after the configured CEO approval gate. - `qody-api-prod--71fd83c7-1126` is Healthy/Provisioned at 100% traffic; `qody-api-prod--956f9f35-939` remains Healthy at 0% as rollback. Exactly two API revisions are active. - Live Hikari telemetry: runtime maximum 4, admin maximum 5, configured app maximum 36, available-to-app 42, headroom 6, pending 0, timeout count 0. - PostgreSQL: latest maximum 17 connections, 30-minute maximum 20 during rollout, failed connections 0. Standard_B1ms was retained because verified headroom made a SKU mutation unnecessary. - RULE-04c/d/e are enabled, routed to `qody-ops-oncall`, and their exact production queries returned no firing condition after rollout. - API, Guest, Admin, and Kitchen public endpoints returned HTTP 200; production browser deploy-gate passed 2/2. - Request-path analysis disproved a route-driven leak: traffic remained 2–3 requests per five-minute bucket; stale active revisions held the static pool baseline. Evidence: `/Users/makinja/system/evidence/107292/prod-postdeploy/final-summary.json` and `/Users/makinja/system/evidence/107292/prod-postdeploy/request-path-verdict.json`.