ENG-001
A leaderboard with a scheduled reset
Rank from a summary table rather than the raw ledger, snapshot the winners before you clear, and pay from the snapshot.
Ranking users by summing the ledger works until the ledger is large, at which point every page load runs an aggregate over millions of rows. A small summary table updated as events happen keeps the leaderboard instant regardless of history.
The reset is where the mistakes live. Clearing the table before recording who won means the winners are gone, and paying prizes directly from a live leaderboard means a claim arriving mid-payout changes the standings underneath you.
Snapshot first, pay from the snapshot, then clear. In that order, and inside one transaction.
/*
CREATE TABLE leaderboard (
season INT UNSIGNED NOT NULL,
user_id INT UNSIGNED NOT NULL,
points BIGINT UNSIGNED NOT NULL DEFAULT 0,
updated_at DATETIME NOT NULL,
PRIMARY KEY (season, user_id),
KEY ix_rank (season, points DESC)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
*/
function add_points(PDO $pdo, int $userId, int $points): void
{
$season = setting_int('season', 1);
$pdo->prepare(
'INSERT INTO leaderboard (season, user_id, points, updated_at)
VALUES (?, ?, ?, NOW())
ON DUPLICATE KEY UPDATE points = points + VALUES(points), updated_at = NOW()'
)->execute([$season, $userId, $points]);
}
function close_season(PDO $pdo, array $prizeUnits): int
{
$season = setting_int('season', 1);
$pdo->beginTransaction();
try {
// 1. Freeze the standings BEFORE anything else touches them.
$pdo->prepare(
'INSERT INTO leaderboard_history (season, user_id, points, place, closed_at)
SELECT season, user_id, points,
ROW_NUMBER() OVER (ORDER BY points DESC, user_id ASC), NOW()
FROM leaderboard WHERE season = ?'
)->execute([$season]);
// 2. Pay from the frozen snapshot, not from the live table.
$winners = $pdo->prepare(
'SELECT user_id, place FROM leaderboard_history
WHERE season = ? AND place <= ? ORDER BY place'
);
$winners->execute([$season, count($prizeUnits)]);
foreach ($winners->fetchAll(PDO::FETCH_ASSOC) as $w) {
$units = (int) ($prizeUnits[(int) $w['place'] - 1] ?? 0);
if ($units > 0) {
$pdo->prepare('UPDATE users SET balance = balance + ? WHERE id = ?')
->execute([$units, (int) $w['user_id']]);
$pdo->prepare('INSERT INTO ledger (user_id, kind, units, memo, created_at) VALUES (?, "prize", ?, ?, NOW())')
->execute([(int) $w['user_id'], $units, 'season ' . $season . ' place ' . $w['place']]);
}
}
// 3. Now move on. The old season's rows stay for the history page.
setting_set('season', (string) ($season + 1));
$pdo->commit();
return $season + 1;
} catch (Throwable $e) {
$pdo->rollBack();
throw $e;
}
}Using it
Keep the season number in settings and in the primary key. Starting a new season is then one increment, with no deletion at all and the history intact.
Break ties on the earlier user id or the earlier update time, so the order is deterministic and two people on equal points do not swap places on refresh.
Show the user their own rank even when they are not in the top ten. It is the number they actually came to see.
What bites people
ROW_NUMBER needs MySQL 8 or MariaDB 10.2 and up. On anything older, rank in PHP after ordering, or use a counter variable.
Do not delete the previous season. A history page costs nothing and answers every dispute about who won.
Award prizes inside the transaction that snapshots. A crash between the two leaves winners recorded but unpaid, which is the worst of both.
This one touches real money. Point it at a throwaway wallet and watch a full cycle before you trust it with a live balance.