Skip to main content

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:

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-<runtime|admin>@<ACA revision>, 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):

az containerapp exec -g rg-qody-prod -n qody-api-prod \
  --revision <serving-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.

No comments to display

Back to top