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.

Progress

• Add a connected-only fallback refresh tick
• Compute per-pool traffic histograms lazily
• Back the 7d window with daily rollups
• Move /admin/upstreams account reloads off the LiveView process
• Aggregate /admin/upstreams/:id request metrics asynchronously in SQL
• Build /admin/stats dashboard data asynchronously
• Load /admin/request-logs filtered data asynchronously

1. Connected-only fallback refresh tick

Status: completed in sub-issue 253 by 46bd71cd

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

The implementation mirrors maybe_start_connected_refresh from lib/codex_pooler_web/live/admin/pages/jobs_live.ex: a connected-only 60-second Process.send_after loop calls the existing coalesced Pool traffic loader and routes timer refreshes through the global live-update pause gate.

2. Compute per-pool traffic histograms lazily

Status: open in sub-issue 254

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

Status: open in sub-issue 255

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 remaining item: 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) applied to each, with page-specific constraints.

Page	Verdict	Notes
/admin/upstreams	complete	Tracked in sub-issue 256 and implemented in 5c25567c. Upload/dialog assigns and account snapshots are preserved across async merges.
/admin/upstreams/:id (cockpit)	complete	Tracked in sub-issue 257. Request health and Pool contribution run asynchronously with bounded SQL aggregation in cd174f43, 6634a600, and fc128cfd.
/admin/stats	complete	Tracked in sub-issue 258. Dashboard builds are generation-tagged, stale-safe, and coalesced in 6e99f0ad and fc128cfd.
/admin/request-logs	complete	Tracked in sub-issue 259. COUNT, page rows, attempts, turns, and details load asynchronously with filter generations in 203a3a05 and fc128cfd.
/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.

Remaining implementation order: rollup-backed 7d window → lazy per-Pool histograms. The watch items remain opportunistic hardening.

[masterkain]

New constraint on item 1 (connected-only fallback refresh tick), since 26c49264 landed after this was written.

Admin auto-refresh now has a global pause: a play/pause in the top bar, held in sessionStorage, gating the source rather than the socket. CodexPoolerWeb.Admin.LiveUpdatesHooks sits in front of every admin LiveView and holds Pool events while paused, replaying them on resume — so a refresh driven by a Pool event needs no page code.

A fallback timer is exactly the case the gate cannot see. Whoever implements the pools tick must route it through LiveUpdatesHooks.unless_paused/2 or it will redraw the traffic panel under an operator who paused to read it. lib/codex_pooler_web/live/admin/pages/jobs_live.ex is the worked example: the tick keeps running so the page is current the moment it resumes, but the reload itself is gated, and hold/1 records that a refresh was skipped so the resume runs it.

Two details that cost us a round of review each, worth having in advance:

• A page that holds a reload whose debounce timer has already fired must also clear the timer assign. Leaving the fired reference in place makes every later schedule_* coalesce onto a timer that will never send, and the page goes permanently deaf. See 12723c6c.
• The resumed handler should cancel the debounce a replayed event just armed, rather than only forgetting it, or a resume runs the aggregate twice.

Nothing here changes the three follow-ups themselves; item 3 (rollup-backed 7d window) is still the one with operator-visible impact.