Plan: Caseload-trend daily rollup + cache_ttl_seconds enforcement (#1218, epic &73)
On this page
Scale-audit finding H11 (HIGH) + the L1 cache_ttl_seconds framework half deferred here by #1233. Adjacent, untouched: #728 (cross-program depth in canopy-reporting), the general validate-effective-composition-before-commit gap (filed at delivery). Governing ADRs: ADR-021 (manifest schema; amended by this plan’s docs step), ADR-024 (user layer stays closed — TTL authority excluded), ADR-007 (CLI parity for the new endpoint).
Status
| Step | Description | Status |
|---|---|---|
0 |
Preflight (claim #1218, scoped labels) + commit this plan, nav-linked. |
Done (2026-08-09) — 7678231c |
1 |
|
Done (2026-08-09) — 567481b3 |
2 |
Rollup-fed serving SQL (TZ-explicit, one-clock) + |
Done (2026-08-09) — 77b874bd |
3 |
Applications twin: half-open sargable bucket predicate + 4-way EXPLAIN pins + boundary membership tests. |
Done (2026-08-09) — c5f56109 |
4 |
|
Done (2026-08-09) — 1a18cdbd |
5 |
Manifest call-site audit — fix declaration drift (audit_events limit, overpayment path, …) in its own commit; identify no-I/O items. |
Done (2026-08-09) — bbeba6dd |
6 |
Manifest TTL author-defaults sweep (0 on read-your-own-writes + no-I/O; 30 on org-wide aggregates) — lands BEFORE enforcement. Deviation: the call-site audit showed system_messages performs NO fetch, so it took the R7 stub shape (0), not the planned 60. |
Done (2026-08-09) — ba6db132 |
7 |
|
Done (2026-08-09) — a254554d |
8 |
CLI parity: |
Done (2026-08-09) — 8b0473e3 |
9 |
Doc ripple (ADR-021 note, ADR-024 amendment, data-models/api/services/shared-crates/canopy-web/worker-portal/CLI-reference/testing pages, CHANGELOG) + this plan → Done/Archive (in-MR flip). |
Done (2026-08-09) — this MR’s docs commit |
10 |
Battery + GA-scale probe (Appendix A) + devstack/e2e verification + Draft MR → ready → force-merge → close #1218 with evidence. |
Done (2026-08-09) — !1110; battery green (3243 unit + 1949 integration); probe: serve 0.15–1.0ms vs legacy 4.98s @1M, sweep 3.94s |
Epic: &73
Issue: #1218 (priority::high)
Branch: fix/1218-caseload-trend-rollup
Context
The supervisor caseload-trend panel re-runs a 12-bucket × ~1M-cert aggregate (COUNT(DISTINCT household_id) over an interval-stabbing join) on every dashboard render and every htmx retry. At GA scale the query exceeds the panel’s 5s timeout; each timeout’s Retry stacks another full aggregate; clustered morning supervisor logins exhaust the renewals pool by construction. The ratified cache_ttl_seconds manifest field (ADR-021 [data] schema) is parsed and consumed by nothing.
A sargable rewrite cannot fix the renewals query: nearly every active certification covers every bucket in the serving window, so the aggregate is intrinsically O(caseload × buckets). Only precomputation reaches AC-1 (render cost O(buckets), independent of caseload size). The applications twin counts inflow (received_at bucketing) and IS genuinely sargable.
Retroactivity forces full-window recomputation: reinstate_certification (store.rs:498-512) clears terminated_at — up to ~30 days of history flips; create_certification (store.rs:43-68) accepts arbitrarily backdated start dates — unbounded history rewrites. The rollup is therefore a materialized cache of the existing lossless reconstruction (semantics codified at store.rs:486-496, not relitigated), recomputed over the full serving window each run; divergence self-heals within the freshness contract.
Ratified decisions (frozen at plan approval, 2026-08-09)
| # | Decision |
|---|---|
R1 |
Freshness contract: trend serves only rollup data ≤ 48h old (else 503 + |
R2 |
One clock: |
R3 |
First-deploy cold start: bounded trend-only outage (immediate first probe at boot; duration measured by the GA-scale probe). No readiness gating — blocking every endpoint on one panel’s materialization is worse. Restarts are warm (table persists). |
R4 |
Manual refresh: service-auth only, no cooldown (advisory lock serializes; repeated calls are an operator choice, matching the existing scheduler-trigger policy). On success it stamps today’s fence window so the next probe doesn’t duplicate the sweep. |
R5 |
TTL override authority: baseline TOML + jurisdiction-live + role layers. The user layer is excluded — |
R6 |
No semantic TTL ceiling (the deployment owns the trade); memory is bounded in bytes instead (per-entry cap, aggregate budget, entry cap — typed |
R7 |
No-I/O panels (per the call-site audit) get |
Design
W1 — renewals rollup (steps 1–2)
Table (migrations/20261103000000_snap_caseload_daily.sql):
CREATE TABLE snap_caseload_daily (
rollup_date DATE PRIMARY KEY,
household_count BIGINT NOT NULL CHECK (household_count >= 0),
refreshed_at TIMESTAMPTZ NOT NULL
);No backfill: the endpoint 503s honestly until the first refresh (R3); fabricated zero-series ("caseload collapsed") are exactly the lie the panel’s failure-honesty states exist to prevent.
Refresh — refresh_caseload_rollup(pool, anchor: NaiveDate, stamped_at: DateTime<Utc>), one TX (prune outside [anchor−735, anchor+8], then upsert), SET LOCAL statement_timeout runaway bound. Coverage bounds computed in Rust from the consts and bound as parameters — never duplicated as SQL literals. The SQL is sweep-line, O(certs·log certs) regardless of window length:
-- $1 cov_start DATE, $2 cov_end DATE, $3 stamped_at TIMESTAMPTZ
WITH intervals AS (
-- per-cert effective day-interval clipped to [cov_start, cov_end].
-- terminated_at boundary matches the legacy strict '>' at UTC midnight:
-- exactly-midnight termination EXCLUDES that day; any later instant keeps it.
SELECT c.household_id,
GREATEST(c.certification_start_date, $1) AS d_start,
LEAST(c.certification_end_date, $2,
CASE WHEN c.terminated_at IS NULL THEN c.certification_end_date
WHEN (c.terminated_at AT TIME ZONE 'UTC')
= date_trunc('day', c.terminated_at AT TIME ZONE 'UTC')
THEN (c.terminated_at AT TIME ZONE 'UTC')::date - 1
ELSE (c.terminated_at AT TIME ZONE 'UTC')::date END) AS d_end
FROM snap_certifications c
WHERE c.active = true
AND c.certification_start_date <= $2 AND c.certification_end_date >= $1
), valid AS (SELECT * FROM intervals WHERE d_start <= d_end),
-- gaps-and-islands per household (new island when d_start > running-max(d_end)+1);
-- merged islands reproduce COUNT(DISTINCT household_id) per day exactly
merged AS (/* MIN(d_start) m_start, MAX(d_end) m_end per island */),
events AS (SELECT m_start AS day, 1 AS delta FROM merged
UNION ALL SELECT m_end + 1, -1 FROM merged),
daily_delta AS (SELECT day, SUM(delta) AS delta FROM events GROUP BY day),
series AS (SELECT generate_series($1, $2, interval '1 day')::date AS day),
counts AS (SELECT s.day, SUM(COALESCE(dd.delta,0))
OVER (ORDER BY s.day ROWS UNBOUNDED PRECEDING) AS cnt
FROM series s LEFT JOIN daily_delta dd USING (day))
INSERT INTO snap_caseload_daily (rollup_date, household_count, refreshed_at)
SELECT day, cnt, $3 FROM counts ORDER BY day
ON CONFLICT (rollup_date) DO UPDATE
SET household_count = EXCLUDED.household_count, refreshed_at = EXCLUDED.refreshed_at;Job: run_caseload_rollup_fenced (job canopy-renewals.caseload-rollup, own hourly probe task, first tick immediate) + unfenced twin refresh_caseload_rollup_with_lock (same advisory-lock name; stamps the window on success per R4) behind POST /v1/renewals/caseload-rollup/refresh (200/Ran + rows_refreshed, 202/Skipped on lock-busy, 403 non-service). Telemetry: duration, outcome, rows, generation-age gauge.
Serving: TZ-explicit spine with $2 = clock-now end (R2), LEFT JOIN snap_caseload_daily at the eval date (day: bucket; week: bucket+6d), household_count fetched as Option<i64> (no COALESCE) + MAX(refreshed_at). New thiserror enum CaseloadTrendError { Db, RollupNotMaterialized, RollupStale { age_hours } } → 503 + Retry-After. Old consts become #[cfg(test)] ORACLE_CASELOAD_DEPTH_{DAY,WEEK}_SQL (drift-pin). Wire DTO unchanged; the GET’s utoipa responses gain 503; the OpenAPI path-count const (api/mod.rs:1795) increments; bless once for the MR.
W2 — applications sargable rewrite (step 3)
date_trunc(unit, a.received_at) = s.bucket_start → half-open a.received_at >= s.bucket_start AND a.received_at < s.bucket_start + interval '1 day'|'1 week'. Equivalence: spine values are unit-aligned by construction; for aligned b, date_trunc(u,x) = b ⟺ b ≤ x < b+1u; series step == bucket width ⇒ exact tiling. Rides partial applications_received_at_idx. Forced-plan tests prove sargability (not GA planner choice — the probe records the natural plan).
W3 — cache_ttl_seconds enforcement (steps 4–7)
Composition: ComposedItem gains #[serde(default, skip_serializing_if = "Option::is_none")] cache_ttl_seconds: Option<u32> (canonical-JSON hygiene; absent ⇒ hash-identical, Some ⇒ hash changes — both tested). ~24 struct-literal sites updated. Authority per R5; TTL-typed RFC 6902 validation at the live/role write APIs (add for absent field, replace/remove accepted; reject otherwise); user-layer writes touching the path rejected; resolution treats user-layer/malformed TTL as absent + WARN (a bad patch can never 500 a render).
Cache (services/canopy-web/src/panel_cache.rs): parking_lot::RwLock<HashMap<CacheKey, CacheEntry>> + per-key single-flight (Weak<tokio::Mutex<()>>, double-check after acquire). CacheKey { service, url /* canonical FULL url incl. origin /, auth_sha256 }; CacheEntry { inserted_at, body: Bytes }; *hit rule: now − inserted_at < caller’s effective TTL (lowering a TTL takes effect immediately). Bounds per R6; expired-on-read + prune-on-insert; injectable clock for deterministic tests.
Client seam (clients.rs::get_uncounted): auth_unavailable + deadline guards FIRST, cache check second, single-flight + permit only on miss; insert only after bounded body read + successful typed decode of a 2xx GET. upstream.call_outcome recorded on real calls only. Never get_raw_streaming/writes.
Dispatch seams: dashboard/panels/mod.rs:123-130, case_detail/sections.rs:250-263,294-306, and the explicit get_tab arms (api/case_detail.rs:2595) reworked so full-page and tab rendering resolve the same item TTL (parity test). TTL = item.cache_ttl_seconds.unwrap_or(manifest default); 0 ⇒ bypass.
Safety: the cache stores upstream response bytes pre-personalization; the step-5 call-site audit confirms per-user shaping is URL-borne or applied post-decode per render (the audit_events pattern); enforced by a two-session isolation test.
Verification
-
Full pre-push battery (sole functional gate; push
-o ci.skip). -
Equivalence + freshness + Sunday→Monday rollover + session-TZ-independence suites; EXPLAIN pins (renewals:
snap_certificationsabsent; applications: 4-way partial-index pin). -
GA-scale probe (Appendix A) — AC-2 evidence into the closing comment: refresh
EXPLAIN (ANALYZE, BUFFERS)/duration/temp at ~1M rows; serving cold/warm ×20 + 16-way concurrent (target p95 ≪ 250ms; hard AC < 5s); pool counts under load; first-deploy outage duration (R3). -
Devstack probe: seed → CLI refresh → 104w trend instant non-zero tail; fresh-schema GET → 503 + Retry-After; double dashboard load ⇒ cache hit; case-detail edit ⇒ immediate freshness; two-session isolation spot-check.
-
e2e
dashboard-supervisor.spec.tswith refresh-after-seed + non-zero-tail assertion (kills the flat-zero false-green). -
OpenAPI snapshot diff = POST endpoint + GET 503 exactly.
Risks
-
≤24h tail staleness by design; the 48h gate bounds the worst case honestly (503, never stale-200 beyond contract, never fabricated zeros).
-
Refresh shares the service pool/DB — scheduling isolation only; statement_timeout + probe measurements decide whether a bounded dedicated pool is warranted (escalation path, not built speculatively).
-
Fence-crash semantics: a crash mid-refresh consumes the UTC window ⇒ up to next-window delay; 48h freshness absorbs one such day; the generation-age gauge is the alert signal.
-
Composition write-validation is TTL-scoped; the general effective-composition validation gap is filed at delivery, not silently absorbed.
Appendix A: Appendix A — reproducible GA-scale probe
Run against a scratch schema on the devstack renewals PG (never a live schema). Records: refresh plan + duration + temp usage, serving latency distribution, natural (unforced) plans, first-refresh duration (R3 evidence).
# 1. scratch schema + synthetic caseload (~1M certs, ~800K households,
# realistic mix: ~70% active-now 12/24-month intervals, ~20% expired,
# ~8% terminated (terminated_at inside interval), ~2% reopened lookalikes)
psql "$RENEWALS_URL" <<'SQL'
CREATE SCHEMA ga_probe_1218;
SET search_path = ga_probe_1218;
-- snap_certifications DDL copied from migrations (table + CHECK only, no FKs)
-- \i or inline; then:
INSERT INTO snap_certifications
(id, household_id, certification_start_date, certification_end_date,
status, active, terminated_at)
SELECT gen_id, hh, start_d, start_d + dur,
CASE WHEN term IS NULL THEN 'active' ELSE 'terminated' END,
true, term
FROM (
SELECT gen_random_uuid() AS gen_id,
('00000000-0000-7000-8000-' || lpad(to_hex((g*13) % 800000), 12, '0'))::uuid AS hh,
(current_date - (random()*900)::int) AS start_d,
(CASE WHEN random() < 0.5 THEN 365 ELSE 730 END) AS dur,
CASE WHEN random() < 0.08
THEN now() - (random()*300 || ' days')::interval END AS term
FROM generate_series(1, 1000000) g
) s;
ANALYZE snap_certifications;
SQL
# 2. refresh: EXPLAIN (ANALYZE, BUFFERS) the sweep with cov bounds bound as
# literals for psql; capture wall time + temp blocks. Repeat 3x.
# 3. serving: the repointed day+week SQL, cold (restart backend) then warm x20;
# then 16 concurrent via pgbench -f serve.sql -c 16 -T 30; capture p50/p95.
# 4. natural plans: EXPLAIN (ANALYZE) both applications trend variants
# (no enable_seqscan forcing) on a 1M-row applications clone.
# 5. teardown: DROP SCHEMA ga_probe_1218 CASCADE;(Note: gen_random_uuid() here is a probe script, not a migration — the #1173 gate applies to migrations only.)