admin/pools async traffic follow-ups: fallback tick, lazy histograms, rollup-backed 7d window

[masterkain]

Context

eb2a84d (perf(admin): move the pools traffic read off the LiveView process) fixed the /admin/pools responsiveness problem: the multi-aggregate traffic read (~400 ms cold at the 24h window, ~13 s cold at 7d in a loaded deployment) used to run synchronously inside the LiveView process and was re-triggered by gateway usage events, so every click queued behind it. The read now runs through start_async with coalescing (one in-flight task, re-run flag, stale-window discard), structural data stays synchronous, and the dead token_usage_weekly scan was deleted.

Three follow-ups were deliberately deferred from that change. They are polish, not correctness: none of them is needed to keep the page responsive.

1. Connected-only fallback refresh tick

Traffic metrics currently refresh only on triggers: gateway usage events (1 s debounce → async task), pool/upstream lifecycle events, or operator actions. On a quiet instance nothing fires, so rolling-window numbers ("Requests 24h") slowly go stale — requests age out of the window but the display keeps the last computed totals. A crashed traffic task on a quiet instance also stays at the "…" loading affordance until the next trigger.

Proposal: mirror maybe_start_connected_refresh from lib/codex_pooler_web/live/admin/pages/jobs_live.ex (connected-only Process.send_after loop) calling start_pool_traffic_load/1, which already coalesces. Interval should be conservative (30–60 s, not the 5 s the jobs page uses): the aggregate costs ~400 ms of DB work per admin session per tick.

Open question: pick the interval based on how many concurrent admin sessions we realistically serve.

2. Compute per-pool traffic histograms lazily

Every pool card eagerly builds and ships a bucketed traffic histogram (lib/codex_pooler_web/live/admin/components/pages/pools/list_components.ex, chart hook payload) on every merge, for every pool, whether or not the card is in view. Cheap at 3 pools; wasteful render CPU + WebSocket payload at tens of pools.

Proposal: compute/send histogram data only when a card is expanded or enters the viewport. Touches the stable chart selectors (ApexTimeSeriesChart, phx-update="ignore") that tests target, so it needs care. No urgency until pool counts per instance grow.

3. Back the 7d window with daily rollups

The 7d window still aggregates raw requests/attempts/ledger_entries rows over 7 days (~13 s cold). Since the read is async this no longer freezes the page — but the operator watches the "…" placeholder for up to ~13 s after selecting 7d.

Proposal: rebuild the 7d path on the existing daily rollups (lib/codex_pooler/accounting/schemas/daily_rollup.ex, lib/codex_pooler/accounting/usage/rollups.ex) the way the Observatory reads already do. Non-trivial parts:

• reconcile per-UTC-day rollups with the still-hot current day (rollups + today's raw tail) under the same exclusion rules (usage_unknown, settled-only cost);
• degrade gracefully when rollups are late or partial (fresh deploys, backfill in progress) — the raw path should remain the fallback;
• dedicated tests for window boundaries at day edges.

This is the highest-value item of the three: it is the only one with operator-visible impact today.

Related: other admin pages sharing the sync-read pattern

A code audit of every admin page (mount cost + event triggers + handle_info behavior) found the same disease — expensive read on the LiveView process re-triggered by hot events — on four more pages. The eb2a84d1 split (cheap structural read synchronous, expensive read via start_async with one-in-flight/re-run/stale-discard coalescing) applies to each, with page-specific constraints.

Page	Verdict	Notes
/admin/upstreams	needs async	UpstreamAccountsReadModel.list_visible_accounts runs token-burn ledger_entries aggregates (no index serves that access path) plus per-identity N+1 (quota windows, unindexed oban_jobs scans) synchronously; the upstreams topic is hot because quota-window updates ride it per gateway response. Constraints: upload/dialog assigns must never be touched by the async merge; account snapshots (renaming_account, delete, saved-reset editing) re-resolve on completion.
/admin/upstreams/:id (cockpit)	needs async	request_health materializes a row per request and pool_contribution aggregates in Elixir; move both off-process and push the aggregation into SQL (count by day×status, bounded latency percentile).
/admin/stats	needs async	Stats.build_dashboard (~a dozen aggregate reads) runs synchronously in handle_params and on debounced event reloads. Keep list_reporting_pools + filter normalize sync; async task must be keyed on the URL params generation (push_patch races).
/admin/request-logs	needs async	COUNT + page rows + attempts + turns run sync per reload, re-triggered by per-request request_logs events (debounced). Tag results with a filter generation; maybe_clear_missing_selected_request_log must only act on fresh results.
/admin/jobs	watch	Reloads are debounced and oban_jobs-bounded (24h retention), traffic-independent; residual risk is the 5s timer polling a moderately heavy sync read. Cheap hardening: scope the subscription to job_status.
/admin/alerts	watch	No hot trigger; risk is unbounded incident history making first paint slow. Fix is query-side (SQL LIMIT/COUNT instead of load-all + Enum.take), not async.
/admin/invites	watch	Hot reason (upstream_quota_windows_updated) rides the upstreams topic it legitimately needs; reason-filter the reload + scoped subscription + short coalescing timer. Read itself is cheap.
/admin/api-keys	fine	Already the template: topic-scoped subscription, accounting reads removed. Latent N+1 per pool if instances reach 100+ visible pools (batch with IN queries).
/admin/audit-logs	fine	No event-triggered reloads. Minor: unbounded total COUNT lacks a plain occurred_at index if audit volume grows.

Suggested order if/when picked up: stats → upstreams → request-logs → cockpit (cockpit needs SQL rework anyway), with the watch items as opportunistic hardening.