@molecule/api-leaderboard

Auto-generated, AI-first package reference for the molecule.dev ecosystem. It is written to be read by coding agents as much as by people, and is generated from this package's source — edit src/index.ts JSDoc, not this file.

Generic leaderboard engine for molecule.dev.

Ranked-aggregate over (user_id, metric, window) with built-in daily / weekly / monthly / all-time windows, custom [start, end) ranges, optional scopeKey partitioning (per-cohort, per-classroom, per-friend-group), competition ranking with explicit tie-break strategies, and pagination.

The pure engine ({@link computeLeaderboard}) is fully testable without a database. The {@link recordMetric}, {@link getLeaderboard}, {@link rollupLeaderboard}, and {@link deleteEvents} service helpers persist via the abstract @molecule/api-database DataStore — no raw SQL in handler-callable code. Schema lives in __setup__/leaderboard_events.sql.

Apps that need cron-style rollups can wire {@link rollupLeaderboard} into a @molecule/api-cron job.

Quick Start

import { recordMetric, getLeaderboard, rollupLeaderboard } from '@molecule/api-leaderboard'

await recordMetric('user-1', 'xp', 50)
await recordMetric('user-2', 'xp', 70)

const top = await getLeaderboard({
  metric: 'xp',
  window: 'weekly',
  limit: 10,
  tieBreak: 'earliest',
})

// Cron rollup (e.g. every hour)
await rollupLeaderboard({ metric: 'xp', window: 'weekly' })

Type

utility

Installation

npm install @molecule/api-leaderboard @molecule/api-database

API

Interfaces

ComputeInput

Inputs for the pure {@link computeLeaderboard} engine.

interface ComputeInput {
  /** Events to aggregate. Pre-filtering by metric / scope is the caller's job. */
  events: LeaderboardEvent[]
  /** Engine options (window resolution, ranking, paging). */
  options: LeaderboardOptions
  /** Aggregation strategy. Defaults to `sum`. */
  aggregation?: Aggregation
}

CustomWindow

A custom half-open [start, end) window expressed as Dates.

interface CustomWindow {
  /** Inclusive lower bound. */
  start: Date
  /** Exclusive upper bound. */
  end: Date
}

LeaderboardEntry

A single entry in a computed leaderboard.

rank is 1-based and uses competition ranking (a.k.a. "1224"): tied entries share the same rank, and the next distinct score skips by the number of ties (1, 2, 2, 4 — no rank 3).

interface LeaderboardEntry {
  /** The user identifier this entry represents. */
  user_id: string
  /**
   * 1-based competition rank. Tied entries share the same `rank`; the
   * next distinct score skips ahead by the number of preceding ties.
   */
  rank: number
  /** Aggregated score for the entry within the requested window. */
  score: number
  /** `true` when at least one other entry in the result shares this rank. */
  tied?: boolean
}

LeaderboardEvent

A single recorded metric event used by the pure engine.

interface LeaderboardEvent {
  /** The user identifier. */
  user_id: string
  /** Score contribution for this event (any finite number). */
  value: number
  /** Event timestamp. */
  when: Date
  /**
   * Optional scope partition matching the {@link LeaderboardOptions.scopeKey}
   * supplied at query time. Events with a non-matching `scopeKey` are
   * filtered out.
   */
  scopeKey?: string
}

LeaderboardOptions

Options accepted by {@link getLeaderboard} / {@link computeLeaderboard}.

interface LeaderboardOptions {
  /** Metric identifier, e.g. `'xp'`, `'lessons-completed'`, `'goals'`. */
  metric: string
  /** Window over which to aggregate. Named or custom. */
  window: LeaderboardWindow
  /**
   * Maximum entries returned (top-N). When omitted, all matching
   * entries are returned (still ranked).
   */
  limit?: number
  /**
   * Number of leading entries to skip (paginated top-N). Defaults to
   * `0`. Combined with `limit` this enables stable paging.
   */
  offset?: number
  /**
   * Optional scope partition. Two boards with different `scopeKey`
   * never see each other's events — useful for per-classroom,
   * per-cohort, or per-friend-group boards.
   */
  scopeKey?: string
  /**
   * Tie-break strategy. Defaults to `'none'`.
   */
  tieBreak?: TieBreak
  /**
   * Reference instant for resolving named windows (`daily`, `weekly`,
   * `monthly`). Defaults to `new Date()` at call time.
   */
  now?: Date
}

ResolvedWindow

Resolved window — either edge may be null for an unbounded side.

interface ResolvedWindow {
  /** Inclusive lower bound, or `null` for an unbounded start. */
  start: Date | null
  /** Exclusive upper bound, or `null` for an unbounded end. */
  end: Date | null
}

Types

Aggregation

Aggregation strategy for combining multiple events for the same user within a window.

• sum (default) — total of values.
• max — highest single value.
• count — number of events (ignores value).
• latest — value of the most recent event.

type Aggregation = 'sum' | 'max' | 'count' | 'latest'

LeaderboardWindow

The full set of supported window descriptors.

type LeaderboardWindow = NamedWindow | CustomWindow

NamedWindow

Named time windows supported out of the box.

daily, weekly, monthly are computed against the calendar boundaries in UTC of the reference instant (defaulting to "now"). all-time aggregates every recorded event regardless of timestamp.

type NamedWindow = 'daily' | 'weekly' | 'monthly' | 'all-time'

TieBreak

Tie-break strategy for entries with equal aggregated scores.

• none (default) — tied entries keep the same rank (competition ranking). Order among ties is implementation-defined.
• earliest — among ties, the user whose earliest contributing event has the lower timestamp ranks higher (still shares the rank, but appears first in the result array).
• user_id — stable lexicographic tiebreaker by user_id ascending.

type TieBreak = 'none' | 'earliest' | 'user_id'

Functions

computeLeaderboard(input)

Pure aggregator. Computes a ranked leaderboard from the supplied events using competition ranking (1, 2, 2, 4) with optional deterministic tie-break ordering for the result array.

Filters events against the resolved window and (when supplied) the scopeKey. Events with a scopeKey mismatch — including events that have a scopeKey when none was requested — are excluded.

function computeLeaderboard(input: ComputeInput): LeaderboardEntry[]

• input — Events plus options.

Returns: Ranked leaderboard, paginated by offset + limit.

deleteEvents(metric, scopeKey)

Bulk delete events for a metric (and optional scope). Useful for tests, GDPR erasure, or pruning historical data.

function deleteEvents(metric: string, scopeKey?: string): Promise<void>

• metric — Metric identifier.
• scopeKey — Optional scope partition. When omitted, all events for the metric are deleted regardless of scope. When supplied, only matching scope events are deleted (use the literal null-equivalent by passing the empty string only if you persisted that explicitly — the column is null by default).

Returns: Resolves once the deletion is dispatched.

getLeaderboard(options, aggregation)

Reads all events relevant to the requested leaderboard, then folds them through the pure engine.

For very large boards, prefer {@link rollupLeaderboard} and read pre-computed rollups instead.

function getLeaderboard(
  options: LeaderboardOptions,
  aggregation?: Aggregation,
): Promise<LeaderboardEntry[]>

• options — Leaderboard query options.
• aggregation — Aggregation strategy. Defaults to sum.

Returns: Ranked + paginated entries.

isInWindow(when, resolved)

true when when falls inside [resolved.start, resolved.end). null edges are treated as unbounded.

function isInWindow(when: Date, resolved: ResolvedWindow): boolean

• when — Event timestamp.
• resolved — Resolved window.

Returns: true if when is inside the window.

recordMetric(userId, metric, value, when, scopeKey)

Records a single metric event for a user.

Idempotency: not enforced. Apps that need de-duplication should pass a stable surrogate metric id and clean up via {@link deleteEvents} before re-recording, or use a dedicated upsert path.

function recordMetric(
  userId: string,
  metric: string,
  value: number,
  when?: Date,
  scopeKey?: string,
): Promise<void>

• userId — The user identifier.
• metric — Metric identifier, e.g. 'xp', 'lessons-completed'.
• value — Score contribution. Any finite number.
• when — Event timestamp. Defaults to new Date().
• scopeKey — Optional scope partition for friend / cohort boards.

Returns: Resolves once the row is persisted.

resolveWindow(window, now)

Resolve a {@link LeaderboardWindow} to a concrete [start, end) pair.

Named windows are computed against now:

• daily — [start of UTC day, +24h).
• weekly — [start of ISO week (Monday 00:00 UTC), +7d).
• monthly — [first of UTC month, first of next UTC month).
• all-time — { start: null, end: null }.

Custom windows are returned as-is.

function resolveWindow(window: LeaderboardWindow, now?: Date): ResolvedWindow

• window — The window descriptor.
• now — Reference instant for named windows. Defaults to new Date().

Returns: The resolved window.

rollupLeaderboard(options, aggregation)

Computes a leaderboard once and persists each ranked entry to the leaderboard_rollups table for the supplied window. Useful from a @molecule/api-cron hourly/daily job.

Existing rollup rows for the exact same (metric, window_kind, window_start, scope_key) tuple are deleted first so the rollup reflects the latest aggregate state.

function rollupLeaderboard(
  options: LeaderboardOptions,
  aggregation?: Aggregation,
): Promise<LeaderboardEntry[]>

• options — Leaderboard query options. limit / offset are ignored — the full board is rolled up.
• aggregation — Aggregation strategy. Defaults to sum.

Returns: The ranked entries that were written.

Injection Notes

Requirements

Peer dependencies:

• @molecule/api-database ^1.0.1

Runtime Dependencies

• @molecule/api-database

Table prerequisites: the service helpers read/write leaderboard_events and leaderboard_rollups. The DDL ships as a .sql file under the package's __setup__ directory. An mlcl-scaffolded API replays it automatically on migrate; anywhere else run the file once against your database — nothing at runtime creates the tables.

The DDL is portable, standard SQL and runs UNCHANGED on PostgreSQL and SQLite — it uses no dialect-only functions: row ids come from the @molecule/api-database DataStore's create() (which generates a UUID when the caller omits one, so the id columns need no DB-side gen_random_uuid()), and timestamp defaults use the standard CURRENT_TIMESTAMP rather than Postgres's now(). The UUID/TIMESTAMPTZ type names and the partial index are PostgreSQL-native and accepted by SQLite's type affinity; MySQL's stricter parser has them normalised automatically by the @molecule/api-database-mysql bond at migrate time (UUID→CHAR(36), TIMESTAMPTZ→TIMESTAMP, the partial-index predicate dropped). So the service is genuinely dialect-agnostic across all three official database bonds — no manual DDL porting required.

A @molecule/api-database bond must be wired at startup before calling the service helpers; the pure engine (computeLeaderboard) needs no database at all.

Scope semantics: omitting scopeKey in a query targets the GLOBAL board (rows whose scope_key is null) — it does not aggregate across scopes. Ranking is competition style: tied scores share a rank, and tieBreak ('none' | 'earliest' | 'user_id') controls ordering within a tie.