AceTrader Docs

Appearance

Leaderboard ​

Single global UserPaperTradeLeaderboard Durable Object with queue-based updates.

Purpose ​

Provides ranked leaderboard views of challenge participants. Receives batched updates from a Cloudflare Queue (USER_PAPER_TRADE_LEADERBOARD_QUEUE), stores denormalized challenge stats in SQLite, and serves paginated ranking queries.

  • Single global instance (id: "global")
  • Queue-based ingestion for write scalability
  • Offset-based pagination for ranking queries
  • Deterministic tie-breaking via high water mark versioning

High-Level Design ​

Data Flow ​

UserPaperTradePortfolio (ChallengeManager)
    │
    │  sendLeaderboardUpdate() on:
    │  challenge start, pass, fail, deactivation, position close
    ▼
Cloudflare Queue (USER_PAPER_TRADE_LEADERBOARD_QUEUE)
    │
    │  Batched delivery (retries on consumer error)
    ▼
Queue Consumer (filters for "CHALLENGE_UPDATE" messages)
    │
    ▼
UserPaperTradeLeaderboard.updateChallengeRankings(updates[])
    │
    │  Atomic SQLite transaction with UPSERT
    ▼
challenge_rankings table (denormalized, queryable)
    │
    ▼
API endpoints → Singleflight dedup → Optional KV cache (10s TTL) → Client

Queue-Based Write Path ​

The leaderboard never receives direct RPC writes from portfolios. All mutations flow through a Cloudflare Queue, which decouples the write path from the portfolio DO. This means:

  • Non-blocking: Portfolio DOs fire-and-forget into the queue, never waiting on leaderboard writes
  • Batched: The consumer receives multiple messages per invocation, wrapped in a single SQLite transaction
  • Eventually consistent: Rankings reflect updates with queue delivery latency (typically sub-second)
  • Retriable: Consumer rethrows errors to trigger Cloudflare Queue retry delivery

Optimistic Versioning ​

Queue messages may arrive out-of-order. The system handles this with high water mark versioning:

getNextVersion() → max(Date.now(), lastVersion + 1)

Each UPSERT includes WHERE excluded.last_updated > challenge_rankings.last_updated, so a stale message arriving after a newer one is silently skipped. This guarantees monotonically increasing state without coordination.

Denormalization Strategy ​

All ranking data is stored redundantly in a single challenge_rankings table — no joins required. Each update carries the full snapshot of a user's challenge state (ROI, PnL, account value, trading days, trade count, social profile). This trades write amplification for read performance, which suits the read-heavy leaderboard workload.

Two fields are immutable after insert: capital and started_at are set once when the challenge ranking is created and excluded from the UPSERT's UPDATE clause.

Social profiles are point-in-time snapshots embedded in each row. Staleness is acceptable since social data changes infrequently.

Filtering & Ranking ​

Rankings are queryable across four dimensions:

  • Group: Maps to challenge types. Currently free (FREE) and paid (STARTER + STANDARD + PRO). Groups are config-driven (constants/leaderboard.ts) — splitting or merging requires only a constant change, no migration.
  • Status: Single status filter per query — ACTIVE, PASSED, or FAILED.
  • Time Window (Seasons): Filters on started_at range. A challenge belongs to the season it started in, even if it spans windows. Currently only "all-time"; seasons are addable via config with immediate effect.
  • Trading Volume: Optional minimum threshold (trading_volume >= ?) to filter out inactive accounts.

Sort options: roi_pct DESC or realized_pnl DESC, with last_updated ASC as deterministic tie-breaker (earlier updater ranks higher).

Singleflight Request Coalescing ​

On KV cache miss, concurrent requests for the same leaderboard page are deduplicated via singleflight() (workers/endpoints/helpers/singleflight.ts). The first request executes the DO RPC; subsequent requests with the same cache key await the same in-flight promise. This prevents thundering herd on the singleton leaderboard DO. The KV read remains outside singleflight since it's cheap and independent per request.

User Rank Calculation ​

getUserRank() uses a three-step approach rather than window functions:

  1. Find the user's best entry across requested challenge types
  2. Count entries with strictly better scores (or same score with earlier last_updated)
  3. Rank = count + 1

Returns rank: 0 with entry: null when the user is not in the filtered set (not found, outside time window, or below volume minimum).

Edge Cases & Error Handling ​

  • Empty types array — returns empty result immediately (no SQL executed).
  • SQL injection prevention — sortBy is validated against a whitelist (roi_pct, realized_pnl). Invalid values throw.
  • NaN/Infinity guard — numeric fields are validated with Number.isFinite() before insert. Invalid values throw.

See Also ​