Skip to main content
Version: 2.0.0

Reset a leaderboard each season

Prerequisites

You need a module and a client connected to it. If you don't have them yet, follow the quickstart for your language first.

In this guide, you'll split a top 100 leaderboard into seasons that last 4 weeks each. When a season ends, its 3 best players go into a hall of fame, and the leaderboard starts over. You'll use a schedule table to end each season on time, and give each score the season it was set in, so that ending a season doesn't delete anything.

How it works​

  • A private player_score table stores each player's best score, and the season it was set in.
  • A private season table holds the current season's number and when it ends.
  • When the season ends, a schedule table calls the end_season reducer. It records the season's 3 best players in a season_winner table, then starts the next season.
  • The top_100 view only reads the current season's scores, so the leaderboard starts empty each season.
  • Clients subscribe to three anonymous views: top_100, current_season and hall_of_fame.

Ending a season never deletes a score. Deleting every score at the end of a season would touch every player's row in a single transaction, which takes longer the more players you have. Moving to the next season only changes one row, however many players you have. A player's score from an earlier season is replaced the next time they submit a score.

Define the tables​

Add four tables, all private:

  • player_score has one row per player: their best score, and the season they set it in. It has an index on season: views can't scan a whole table with iter(), so they read the current season's scores through this index.
  • season has a single row, with id 0, that holds the current season's number and when it ends.
  • season_timer is the schedule table that ends the season.
  • season_winner holds the 3 best players of each finished season.
import { ScheduleAt, Timestamp } from 'spacetimedb';
import { schema, table, t, SenderError } from 'spacetimedb/server';

// How long each season lasts: 4 weeks, in microseconds.
const SEASON_LENGTH_MICROS = 28n * 24n * 60n * 60n * 1_000_000n;
// How many players of each season go into the hall of fame.
const WINNERS_PER_SEASON = 3;

const playerScore = table(
  { name: 'player_score' },
  {
    identity: t.identity().primaryKey(),
    name: t.string(),
    // The season this score was set in.
    season: t.u32().index('btree'),
    score: t.u64(),
  }
);

// The current season, in a single row with id 0.
const season = table(
  { name: 'season' },
  {
    id: t.u8().primaryKey(),
    number: t.u32(),
    endsAt: t.timestamp(),
  }
);

const seasonTimer = table(
  { name: 'season_timer' },
  {
    scheduledId: t.u64().primaryKey().autoInc(),
    scheduledAt: t.scheduleAt(),
  }
);

// The best players of each finished season.
const seasonWinner = table(
  { name: 'season_winner' },
  {
    id: t.u64().primaryKey().autoInc(),
    season: t.u32(),
    rank: t.u8(),
    name: t.string(),
    score: t.u64(),
  }
);

const spacetimedb = schema({ playerScore, season, seasonTimer, seasonWinner });
export default spacetimedb;

// Sorts scores from highest to lowest.
function byScoreDescending(a: { score: bigint }, b: { score: bigint }): number {
  return a.score < b.score ? 1 : a.score > b.score ? -1 : 0;
}

Start the first season​

Insert season 1 and its timer from the init reducer. The timer fires once, when the season ends.

export const init = spacetimedb.init(ctx => {
  const endsAt = new Timestamp(ctx.timestamp.microsSinceUnixEpoch + SEASON_LENGTH_MICROS);
  ctx.db.season.insert({ id: 0, number: 1, endsAt });
  ctx.db.seasonTimer.insert({ scheduledId: 0n, scheduledAt: ScheduleAt.time(endsAt.microsSinceUnixEpoch) });
});
note

init only runs when the database is created. Publishing new code to an existing database doesn't run it again. To add seasons to a database that already exists, insert the season row and its timer from another reducer, once.

Submit a score​

Add a submit_score reducer that players call with their name and score. It keeps each player's best score of the current season, and ignores lower ones. If the player's stored score is from an earlier season, the new score replaces it, even if it's lower: a player's first score of a season always counts.

export const submitScore = spacetimedb.reducer(
  { name: t.string(), score: t.u64() },
  (ctx, { name, score }) => {
    const current = ctx.db.season.id.find(0);
    if (!current) {
      throw new SenderError('no season in progress');
    }
    const existing = ctx.db.playerScore.identity.find(ctx.sender);
    if (!existing) {
      ctx.db.playerScore.insert({ identity: ctx.sender, name, season: current.number, score });
    } else if (existing.season !== current.number || score > existing.score) {
      // Keep the player's best score of the season.
      // A score from an earlier season is replaced, even by a lower one.
      ctx.db.playerScore.identity.update({ ...existing, name, season: current.number, score });
    }
  }
);

End a season​

Add the end_season reducer, which SpacetimeDB calls when the season's timer fires. It does two things:

  1. It records the season's best players. It reads the season's scores through the season index, sorts them from highest to lowest, and copies the first 3 into season_winner. Always sort the rows yourself rather than relying on the order of the index: the rows written by the current transaction can come out of the index before all the others.
  2. It starts the next season. It increments the season number, and schedules the next end one season after the previous end, rather than one season after now. A timer can fire a few milliseconds late, and counting from now would make each season start a little later than the last.

end_season reads all of the season's scores, but only once per season. Clients can't call it: scheduled reducers are private, so only the scheduler, the database owner and team collaborators can run them.

export const endSeason = spacetimedb.reducer(
  { onSchedule: seasonTimer },
  { arg: seasonTimer.rowType },
  ctx => {
    const current = ctx.db.season.id.find(0);
    if (!current) {
      throw new Error('no season in progress');
    }

    // Record the season's best players.
    const best = Array.from(ctx.db.playerScore.season.filter(current.number))
      .sort(byScoreDescending)
      .slice(0, WINNERS_PER_SEASON);
    best.forEach((row, i) => {
      ctx.db.seasonWinner.insert({
        id: 0n,
        season: current.number,
        rank: i + 1,
        name: row.name,
        score: row.score,
      });
    });

    // Start the next season. This season's scores stay in the table, but no longer count.
    const endsAt = new Timestamp(current.endsAt.microsSinceUnixEpoch + SEASON_LENGTH_MICROS);
    ctx.db.seasonTimer.insert({ scheduledId: 0n, scheduledAt: ScheduleAt.time(endsAt.microsSinceUnixEpoch) });
    ctx.db.season.id.update({ ...current, number: current.number + 1, endsAt });
  }
);

Define the views​

Add three public anonymous views. They return the same rows to every player, so SpacetimeDB computes each one once and shares it with every subscribed client.

  • top_100 reads the current season's scores through the season index, sorts them from highest to lowest, and returns the first 100. Like end_season, it sorts the rows itself rather than relying on the order of the index.
  • current_season returns the season row, so clients can show when the season ends.
  • hall_of_fame returns every row of season_winner.

current_season and hall_of_fame return their whole table, so they return a query instead of rows: SpacetimeDB keeps them up to date the same way as a subscription query. Returning a whole table through a view keeps the table private, so you can still change what clients see later, for example to only show the last few seasons.

const LEADERBOARD_SIZE = 100;

export const top100 = spacetimedb.anonymousView(
  { name: 'top_100', public: true },
  t.array(playerScore.rowType),
  ctx => {
    const current = ctx.db.season.id.find(0);
    if (!current) {
      return [];
    }
    return Array.from(ctx.db.playerScore.season.filter(current.number))
      .sort(byScoreDescending)
      .slice(0, LEADERBOARD_SIZE);
  }
);

export const currentSeason = spacetimedb.anonymousView(
  { name: 'current_season', public: true },
  t.array(season.rowType),
  ctx => ctx.from.season
);

export const hallOfFame = spacetimedb.anonymousView(
  { name: 'hall_of_fame', public: true },
  t.array(seasonWinner.rowType),
  ctx => ctx.from.seasonWinner
);

Show the season on the client​

On the client, subscribe to the three views, and draw the season, the leaderboard and the hall of fame from the client cache each frame. The client cache doesn't keep the order the views returned their rows in, so sort the rows before you display them.

When a season ends, the client receives the whole change at once: the new season number, the hall of fame entries, and the removal of every score from the leaderboard.

import { DbConnection, tables } from './module_bindings';

const conn = DbConnection.builder()
  .withUri('ws://localhost:3000')
  .withDatabaseName('my-game')
  .onConnect(ctx => {
    ctx.subscriptionBuilder().subscribe([tables.top100, tables.currentSeason, tables.hallOfFame]);
  })
  .build();

// Call this each frame to draw the season from the client cache.
function drawSeason() {
  for (const season of conn.db.currentSeason.iter()) {
    const daysLeft = Math.ceil((season.endsAt.toDate().getTime() - Date.now()) / 86_400_000);
    console.log(`Season ${season.number} ends in ${Math.max(daysLeft, 0)} days`);
  }

  // The client cache doesn't keep the view's order, so sort the rows here.
  const leaderboard = Array.from(conn.db.top100.iter()).sort((a, b) =>
    a.score < b.score ? 1 : a.score > b.score ? -1 : 0
  );
  leaderboard.forEach((row, i) => console.log(`${i + 1}. ${row.name} - ${row.score}`));

  // The hall of fame, most recent season first.
  const winners = Array.from(conn.db.hallOfFame.iter()).sort((a, b) => b.season - a.season || a.rank - b.rank);
  for (const winner of winners) {
    console.log(`Season ${winner.season}, #${winner.rank}: ${winner.name} - ${winner.score}`);
  }
}

// Call this when a game ends to submit the player's score.
function onGameOver(name: string, score: bigint) {
  conn.reducers.submitScore({ name, score }).catch(console.error);
}

What's next?​

You now have a leaderboard that starts over every 4 weeks, and remembers each season's best players:

  • Each score records the season it was set in, and the views only read the current season's scores.
  • A one-shot timer ends each season, records its 3 best players, and schedules the end of the next season.
  • Ending a season never deletes a score, so it takes the same time however many players you have.

To adapt it to your game:

  • Give out rewards. end_season already knows the season's best players: grant their rewards in the same reducer, for example by inserting rows into a table of badges.
  • End seasons on calendar dates. To end each season on the first day of the month, compute the next end date in end_season instead of adding a fixed length. In a TypeScript module, the Cron submodule, a package you add to your module, can run end_season on a calendar schedule instead, in the time zone you choose.
  • Clean up old scores. Rows from players who stopped playing stay in player_score. If that matters, delete the rows of old seasons from another scheduled reducer, a few at a time.
  • Reset ratings too. If you use skill-based matchmaking, you can also reset players' ratings in end_season, so that everyone starts the new season on equal terms.

To learn more about the features used here, see Schedule Tables, Indexes and Views.