Leaderboards

Architecture

Cron Worker computes rankings via D1 aggregation on a schedule, writes result JSON to KV. Clients read KV. No D1 hit per user request. No real-time ranking.

Cron Worker (scheduled)
  → D1 GROUP BY aggregation query
  → write JSON to KV: leaderboard:{window}:{category}

Client request: GET /api/leaderboard?window=weekly&category=expeditions_completed
  → Worker reads single KV key
  → returns pre-computed list

---

Stat Ledger

Append-only event log. Only server-side settlement code writes here — never client. Authoritative by construction.

D1 Schema

CREATE TABLE character_stat_event (
  id           TEXT PRIMARY KEY,
  character_id TEXT NOT NULL REFERENCES character(id),
  stat_type    TEXT NOT NULL,
  delta        INTEGER NOT NULL,
  occurred_at  INTEGER NOT NULL
);

CREATE INDEX idx_stat_event_type_time ON character_stat_event(stat_type, occurred_at DESC);
CREATE INDEX idx_stat_event_char      ON character_stat_event(character_id, stat_type);

Stat Types

stat_type	Written on
expeditions_completed	Expedition settlement — success outcome
expeditions_failed	Expedition settlement — failure outcome
coin_earned	Market fill (seller side), expedition reward
coin_spent	Market order post fee, buy fill
items_crafted	Craft action settlement
items_gathered	Gather expedition settlement
town_influence	Town contribution ledger write (TownSettlementDO)
board_orders_filled	Board acceptance + successful settlement
elite_kills	Elite encounter resolved in expedition
boss_kills	Boss encounter resolved in expedition

Each write appends one row. No in-place update. Aggregation happens only during cron.

---

Cron Schedule

Cloudflare Cron Triggers (max 5 on free tier — these use 3):

Trigger	Cron	Computes
Hourly	0 * * * *	weekly — all categories
6-hourly	0 */6 * * *	monthly — all categories
Daily	0 3 * * *	all_time — all categories

Weekly and monthly use rolling windows (not calendar-aligned). All-time has no time filter.

---

Aggregation Query

Per category per window, run inside the cron Worker:

-- example: weekly expeditions_completed
SELECT
  c.id            AS character_id,
  c.display_name,
  SUM(e.delta)    AS total
FROM character_stat_event e
JOIN character c ON c.id = e.character_id
WHERE e.stat_type = 'expeditions_completed'
  AND e.occurred_at > unixepoch() - 604800   -- 7 days
GROUP BY e.character_id
ORDER BY total DESC
LIMIT 100;

Window offsets:

Window	occurred_at filter
weekly	> unixepoch() - 604800
monthly	> unixepoch() - 2592000
all_time	(no filter)

Result trimmed to top 100. Cron runs all categories in sequence per window. Total D1 reads per cron run: ~10 queries (5 categories × 2 windows per cron; all_time daily cron = 5 queries).

---

KV Keys

leaderboard:{window}:{category}

Examples:

leaderboard:weekly:expeditions_completed
leaderboard:weekly:coin_earned
leaderboard:weekly:items_crafted
leaderboard:weekly:town_influence
leaderboard:weekly:elite_kills
leaderboard:weekly:boss_kills
leaderboard:monthly:expeditions_completed
leaderboard:monthly:coin_earned
...
leaderboard:all_time:expeditions_completed
leaderboard:all_time:boss_kills

Value Shape

{
  "computed_at": 1747468800,
  "window": "weekly",
  "category": "expeditions_completed",
  "entries": [
    { "rank": 1, "character_id": "char_abc", "display_name": "Maerath", "value": 47 },
    { "rank": 2, "character_id": "char_def", "display_name": "Corren",  "value": 39 }
  ]
}

No TTL set on KV keys — overwritten each cron run. If cron fails, stale data is served until next successful run. Staleness is acceptable; leaderboards are not real-time.

---

API Routes

GET /api/leaderboard?window={window}&category={category}

Query params:

Param	Values
window	weekly | monthly | all_time
category	expeditions_completed | coin_earned | items_crafted | town_influence | elite_kills | boss_kills

Worker: 1. Build KV key from params 2. Return KV value directly — no D1 read 3. If KV key missing (first boot before first cron): return { "entries": [], "computed_at": null }

Optional query param ?highlight={character_id}: client uses this to highlight caller's row. No server change needed — field matched client-side.

Caller's Own Rank

If caller is not in top 100, show their position at the bottom of the list. Fetch via:

GET /api/leaderboard/me?window=weekly&category=expeditions_completed

Worker: one D1 count query to find rank:

SELECT COUNT(*) + 1 AS rank
FROM (
  SELECT character_id, SUM(delta) AS total
  FROM character_stat_event
  WHERE stat_type = 'expeditions_completed'
    AND occurred_at > unixepoch() - 604800
  GROUP BY character_id
  HAVING total > (
    SELECT SUM(delta) FROM character_stat_event
    WHERE stat_type = 'expeditions_completed'
      AND character_id = ?
      AND occurred_at > unixepoch() - 604800
  )
) ranked;

Result cached in leaderboard_me:{character_id}:{window}:{category} KV for 1 hour. Avoids D1 hit on repeated checks.

---

Client Routes

/leaderboard

UI:

• Tab bar: Weekly | Monthly | All-Time
• Category selector: Expeditions | Coin Earned | Crafted | Influence | Elites | Boss Kills
• Table: rank, display name (links to /character/{id}), value
• Caller's row highlighted; pinned to bottom if outside top 100
• computed_at shown as "Last updated X hours ago"

No server-sent updates. Player refreshes or navigates away and back.

---

Cost Profile

Event	Cost
Cron run (hourly, weekly)	~10 D1 reads, ~10 KV writes
Cron run (6-hourly, monthly)	~10 D1 reads, ~10 KV writes
Cron run (daily, all_time)	~10 D1 reads, ~10 KV writes
User views leaderboard	1 KV read
User fetches own rank	1 D1 read → cached 1h in KV
Stat write per expedition settle	1–3 D1 writes (one per stat type earned)

D1 aggregation queries run only during cron — never per user request. All user-facing reads are KV. Fits Cloudflare free tier for Phase 1.

---

Phase 2 Extensions (deferred)

• Per-region leaderboards (filter by character home location)
• Guild leaderboards (aggregate by guild membership at time of event)
• Seasonal leaderboards (calendar-aligned windows, reset on season boundary)
• Leaderboard history archival (snapshot top 10 to D1 at season end for hall of fame)
• Anti-gaming review: flag characters with anomalous delta spikes for admin review