Explorer 30d dashboard frozen at stale values: ledger window aggregates exceed max_statement_time

Status reconciliation — 2026-10-22

GitHub confirms PR #54 merged on 2026-10-18, despite the stale open development link. Implementation and production-snapshot rehearsal are complete (exact volume reconciliation; comparison query 27.9s to 4.1s). Remaining: coordinated migration/release and post-deployment browser verification. Do not restart implementation or treat the merge as production acceptance.

Symptom

On economy.dev.paradaux.io (prod data, refreshed 2026-10-17) the 30-day dashboard shows $0 supply, 0 players and 0 transactions. The 7-day view is correct.

Cause

• getActivityComparison(30) (lib/sql/health.ts) joins ledger_txns to ledger_postings over 60 days, about 6M rows. It takes 13–20 s on staging, which exceeds the explorer's 15 s max_statement_time. getDailyVolume(30) takes about 7 s.
• The PAR-156 SWR cache (lib/cache.ts) ignores background recompute errors, so the last good value (computed when the DB was empty) is served indefinitely.
• Prod has the same ledger and a 128 MB buffer pool, so it is exposed to the same failure.

Fix

• New migration with an append-only ledger_txn_volume table: one row per transaction holding its settlement time and positive-posting volume. It is maintained by triggers keyed on the transaction's own id, so it adds no shared or hot row locks to the ledger write path. A cross-transaction hourly rollup row was rejected because it would deadlock against the ascending FOR UPDATE ordering in LedgerServiceImpl. The migration backfills from existing ledger history, so refreshed prod dumps and the prod release are covered.
• Dashboard volume, comparison and heatmap queries read it via a covering index, with no join.
• Cache logs failed recomputes instead of swallowing them.
• Release runbook timing updated with the measured backfill time.