Indexer: live podium predictions from Postgres per category/epoch (GET /v1/arena/podiums)

Summary

Complete the #254 (closed) gap: while the arena is live, GET /v1/arena/podiums must return top-3 winner slots and scores derived from indexed Postgres (per category and current epoch), not from head TimeArena.podium(category) RPC alone. Consumers (Simple /arena, Arena views, play skills) use this route when VITE_INDEXER_URL is set.

Related: #254 (closed) (schema + HTTP baseline), #238 (closed) (epic), #247 (closed) (independent podium epochs), #252 (closed) (WarBow), frontend PODIUM_CONTRACT_CATEGORY_INDEX (#256 (closed)).

Current codebase

HTTP (GET /v1/arena/podiums)

  • Handler: indexer/src/api_arena.rs (arena_podiums).
  • Winners / values come from the in-memory chain-timer snapshot (podium_contract[0..3]), which is populated by head RPC podium(cat) in indexer/src/chain_timer.rs.
  • Indexed data used today: only MAX(epoch) per category from idx_arena_podium_epoch (rolled epochs). Response always sets podium_prediction: true without building leaders from the DB.
  • Row category labels follow onchain index order (last_buy, time_booster, defended_streak, warbow), not the UX order the frontend expects when mapping rows by array index.

Frontend contract

Indexed inputs (partial)

Onchain category Index Leaderboard metric (live) Indexed today
Last Buy 0 Last 3 distinct buyers in current lastBuyEpoch (buyCount) idx_arena_buy (no epoch column; no order index)
Time Booster 1 totalEffectiveTimerSecAdded[buyer] not projected
Defended Streak 2 bestDefendedStreak[buyer] not projected
WarBow 3 battlePoints[buyer] in current podiumEpoch[3] partial via idx_warbow_epoch_score (post-log eth_call snapshots)

Settlement history: idx_arena_podium_epoch / view idx_arena_podium_snapshot — rolled epochs only, not live boards.

Product / onchain authority

Stale design note

docs/indexer/design.md (TimeCurve podiums section) still describes live DB predictions during !sale_ended; Arena v2 implementation was never completed — this issue tracks that work.

Why this is needed

  1. #254 (closed) acceptance explicitly requires GET /v1/arena/podiums: live predictions per category per epoch. #254 (closed) remains open on this slice.
  2. WarBow UX: head podium(3) is often empty mid-epoch while BP exists onchain; live sale UI must show indexed leaders (docs/indexer/design.md).
  3. Agent / API consumers (skills/play-active-time-arena) need a single HTTP source aligned with event history, not an extra RPC fan-out per page load.
  4. Row ordering: indexer response order must align with frontend UX slots or expose explicit category + stable sort so Simple/Arena do not swap WarBow and Time Booster.

Constraints and guardrails

  • AGPL repo; keep logic modular (docs/licensing.md).
  • No new authoritative business rules offchain — replicate onchain leaderboard inputs at ingest or read with block-tagged eth_call snapshots (same pattern as indexer/src/warbow_score.rs). Prefer event-sourced projections where events carry enough data; use bounded RPC only where logs omit scores.
  • One SQL transaction per block for ingest (#140 (closed)); reorg rollback must include new tables (indexer/src/reorg.rs).
  • Epoch scope:
    • Last Buy leaders scoped to head lastBuyEpoch (from chain timer / LastBuyEpochStarted).
    • Categories 1–3 scoped to head podiumEpoch[cat] (RPC or idx_arena_podium_epoch + 1 for live window).
  • Arena v2 only — no TimeCurve tables or sale_ended gating (arena is live until paused).
  • After rollPodiumEpoch: live board for that category clears onchain; API may return empty slots or fall back to settled idx_arena_podium_epoch row for the just-finished epoch — document chosen behavior in API + invariants.
  • Performance: GET /v1/arena/podiums should stay O(1) or indexed top-3 queries, not full-table scans; target p95 acceptable on Anvil-rich dataset (see #255 (closed) wallet-stats note).
  • Schema version: bump SCHEMA_VERSION in indexer/src/api.rs and extend frontend/src/lib/indexerApi.ts types.

Relevant files

Area Paths
API indexer/src/api_arena.rs, indexer/src/chain_timer.rs
Ingest / decode indexer/src/ingestion.rs, indexer/src/decoder.rs, indexer/src/persist.rs, indexer/src/warbow_score.rs
Migrations indexer/migrations/
Tests indexer/tests/integration_stage2.rs
Frontend frontend/src/lib/indexerApi.ts, frontend/src/pages/arena/usePodiumReads.ts, frontend/src/pages/arena/podiumCopy.tsx
Docs / invariants docs/indexer/design.md, docs/testing/invariants-and-business-logic.md, docs/frontend/arena-views.md, skills/play-active-time-arena/SKILL.md
Onchain reference contracts/src/arena/TimeArena.sol, contracts/test/TimeArena.t.sol

Recommended direction

  1. New projection table(s) (name TBD, e.g. idx_arena_podium_live or per-cat tables) storing, per (category, epoch, player): score, optional tie-break fields, block_number, updated_at — updated on ingest when Buy, WarBow PvP logs, or LastBuyEpochStarted affect leaders.
  2. Ingest enrichment module (e.g. indexer/src/arena_podium_live.rs):
    • On each qualifying log at block B, eth_call at B the same views TimeArena uses: buyCount, totalEffectiveTimerSecAdded, bestDefendedStreak, battlePoints (WarBow may reuse/extend warbow_score.rs).
    • Alternatively, port _updateTopThree math in Rust only if every input is recoverable from indexed events without drift — prefer snapshot-at-block when in doubt.
  3. arena_podiums read path:
    • Read head last_buy_epoch + podium_epoch[cat] from chain timer (extend snapshot if missing).
    • For each UX-ordered row (PODIUM_CONTRACT_CATEGORY_INDEX), query top-3 from Postgres for (cat, current_epoch).
    • Set podium_prediction: true when rows are DB-derived; set false when intentionally mirroring RPC (e.g. post-roll settled-only mode, if implemented).
    • Return rows in UX order (or add category_index + sort; update usePodiumReads if needed).
  4. Optional: GET /v1/arena/podiums?category=warbow later — out of scope unless trivial; single route is enough for v1.
  5. Close #254 (closed) only after this issue’s verification criteria pass.

Acceptance criteria

  • New migration(s) for live podium projection(s); included in ARENA_INDEX_TABLES / rollback_after.
  • Ingest persists live leaderboard state for all four categories on relevant events (at minimum every Buy + WarBow BP mutations).
  • GET /v1/arena/podiums returns top-3 winners + values from Postgres for the current epoch per category while the arena is live.
  • Response includes correct epoch (string decimal) per row matching onchain podiumEpoch / lastBuyEpoch semantics.
  • Rows are ordered for frontend UX (Last Buy, WarBow, Defended Streak, Time Booster) or frontend remaps by category without slot swap.
  • podium_prediction: true only when values are DB-derived; document any RPC fallback.
  • WarBow mid-epoch: non-zero leaders when idx_warbow_epoch_score / live table has data, even if head podium(3) is empty.
  • SCHEMA_VERSION bump + indexerApi.ts types updated.
  • INV-INDEXER-PODIUM-PREDICT-LIVE (or successor) added/updated in docs/testing/invariants-and-business-logic.md; cross-link docs/indexer/design.md.
  • integration_stage2.rs covers HTTP body shape + persist counts for at least two categories (e.g. Last Buy order + WarBow BP).

Test plan — functional paths

# Path Expected
1 Empty DB, chain timer ready GET /v1/arena/podiums → 200, four rows, zero/empty winners, epoch from head RPC
2 Single buyer, one DOUB buy Last Buy row shows buyer in 1st; buyCount score matches onchain at buy block
3 Two buyers, same lastBuyEpoch Last Buy top-3 order = last-three distinct buyers (3rd → 2nd → 1st buy order)
4 Buys adding timer seconds Time Booster top entrant matches totalEffectiveTimerSecAdded leader at head block
5 Streak buys under 15m Defended Streak leader matches bestDefendedStreak at head block
6 WarBow buys + steal WarBow top-3 matches battlePoints for current podiumEpoch[3]
7 LastBuyEpochStarted / hard reset Last Buy board resets for new epoch; prior-epoch buys not in live top-3
8 PodiumEpochRolled for cat 1 Live Time Booster board clears or switches to new epoch per documented API rule
9 Reorg rollback Live rows for reorged blocks removed; API consistent with surviving chain
10 chain_timer unavailable 503 chain_timer_unavailable (unchanged)
11 Frontend with VITE_INDEXER_URL Simple podium cards show same addresses as cast call podium(cat) at same block (manual or scripted)

Commands: YIELDOMEGA_PG_TEST_URL=… cargo test --test integration_stage2; local stack bash scripts/start-local-anvil-stack.sh + indexer; optional Playwright arena podium specs if present.

Test plan — attack, hack, and abuse vectors

Vector Risk Mitigation / test
SQL injection via query params Low (read-only route today) Keep parameterized SQL; no raw address concatenation
Oversized / malicious addresses in stored rows Display confusion Store lowercase 0x + 40 hex; API validates format; ignore malformed rows in top-3
Reorg double-spend of leaderboard Stale winners shown rollback_after deletes live rows; integration reorg test
Indexer drift vs chain Wrong payout preview Block-tagged snapshots at ingest; golden test: compare API vs cast call at read_block_number
RPC manipulation (indexer operator) False leaders Document trust model; compare against user RPC on dispute; do not accept client-supplied scores
DoS via heavy GET /v1/arena/podiums DB CPU Index (category, epoch, score DESC); limit work to top-3 per cat; rate limit at reverse proxy (ops)
WarBow BP generation bump Stale BP from prior epoch Tie snapshots to warbowBpGeneration / epoch; test epoch roll clears live WarBow table
Self-referral / sybil buys Game design, not indexer No special casing; ensure buyCount not inflated by indexer bugs (one row per log)
Flash-buy ordering Last Buy slot gaming Order by buy_index / block+log order from chain, not wall clock
Empty pool / zero address winners UI crash Match onchain address(0) handling; frontend already pads zeros

Verification criteria (close checklist)

  • All acceptance criteria checked.
  • cargo clippy --all-targets -- -D warnings and cargo test green in indexer/.
  • integration_stage2 passes with Postgres.
  • Manual: Anvil deploy → 3+ wallets buy → curl $INDEXER/v1/arena/podiums matches cast call podium for each PODIUM_CONTRACT_CATEGORY_INDEX at same block.
  • QA comment on #254 (closed) linking this issue; close #254 (closed) when both pass.
  • No new TimeCurve-only routes or tables introduced.