Leaderboard Synchronization

1. Overview

Leaderboard Synchronization is the automated recalculation layer: when race results change, it recomputes the affected leaderboards' standings without manual intervention. It is an event-driven replacement for the current manual SQL aggregation process.

This layer builds on the Leaderboard Model & Result Classification. The schema (Leaderboard, LeaderboardCategory), the scoring scales (PointsCalculator), and the aggregation strategies (LeaderboardType / LeaderboardCalculator) are all defined there and are in production use today via manual aggregation. This page describes only what is added to make that recalculation automatic: the change event, debouncing, the ResultSet state machine, manual-recalc API, and failure handling.

Status: built. Standings are persisted since #1379–#1381, and the trigger runs on the Signal & Sweep framework as LeaderboardReactor since US #409. Where the built design differs from the original, the sections below say so; the authoritative statement of the aggregation and the write is Leaderboard Materialisation.

2. Problem Statement

Leaderboards (Top Schools, All Stars, Individual Rider, and the WP Cycling boards) are currently populated by manual aggregation that reads RaceResult.points. When results change there is no automatic recalculation — an operator must re-run the aggregation. This layer makes the recalculation fire automatically and safely on any result change.

3. Source Code

These are the components this layer adds on top of the model. (The model’s own files — Leaderboard, LeaderboardCategory, LeaderboardType, the PointsCalculator / LeaderboardCalculator families — are listed in the model doc.)

File Purpose

LeaderboardReactor.java

Signal & Sweep reactor: recalculates on RESULT_SET_CHANGED, and its sweep retries FAILED drafts (US #409, #1466).

LeaderboardSynchronizationService.java

Recalculates standings, one transaction per leaderboard; records a failed attempt’s FAILED drafts after it rolls back (#1438, #1466).

StandingMaterialisationService.java

Writes a standing (#1380, #1381); creates or refreshes the FAILED drafts, and promotes one when a retry succeeds (#1466).

LeaderboardResource.java

REST API for manual recalculation.

Modified to participate in sync:

File Change

ResultSet.java

errorMessage and leaderboard relationship (#1379).

ResultSetState.java

FAILED('F') state.

RaceResultServiceEx.java

Emits RESULT_SET_CHANGED through ResultSetChangeNotifier on every save and delete (ADO #1127).

ResultImportXLS.java

The bulk import saves each row through RaceResultServiceEx, so it emits too.

4. Architecture

4.1. Event Flow

   Result Change (API / Import)
            │
            ▼
   RESULT_SET_CHANGED signal, key event:{id}
            │
            ▼
   Debounce (60s squash window)
            │
            ▼
   LeaderboardReactor → active leaderboards of the event's series
            │
   ┌────────┼────────┐
   ▼        ▼        ▼
 School  TopSchools Individual   ← LeaderboardCalculator strategies (see model doc)
   └────────┼────────┘
            ▼
   Upsert Results (state-aware)

4.2. Rate Limiting

A 60-second debounce absorbs bursts:

  • Each result change emits RESULT_SET_CHANGED, keyed on its event. Changes to the same event within the window squash into one signal, and each squash pushes the deadline out again.

  • The signal is processed once the event has been quiet for 60 seconds, and only that event’s standings are recalculated, followed by each leaderboard’s cumulative standing.

  • The signal is stored, so a restart loses nothing.

  • Manual recalculation via the API bypasses the debounce.

4.3. ResultSet State Machine

Recalculation writes standings into a ResultSet, whose state governs whether it may be modified in place.

State Code Behaviour

DRAFT

-

Upsert freely. Whether a standing is published is active, not state.

PROVISIONAL

P

Upsert freely; displayed publicly, marked provisional.

APPROVED

A

Immutable — clone to a new DRAFT to change.

REPLACED

R

Superseded version; replaces links the new version.

DELETED

D

Immutable — clone to a new DRAFT to change.

FAILED

F

A failed attempt’s draft: inactive, empty, beside the standing it could not replace. The retry target. See leaderboards/leaderboard-materialisation.adoc#failed-draft.

When upserting standings:

  • DRAFT or PROVISIONAL → update directly.

  • APPROVED or DELETED → clone the ResultSet, set the original to REPLACED, update the clone.

  • On failure → create or refresh the FAILED draft beside the published standing, which is untouched. A retry writes into the draft and promotes it on success.

5. Manual Recalculation API

Endpoint Purpose

POST /api/leaderboards/{id}/recalculate

Recalculate one leaderboard immediately.

POST /api/leaderboards/series/{seriesId}/recalculate

Recalculate all active leaderboards for a series.

GET /api/leaderboards/pending-count

Count the RESULT_SET_CHANGED signals still pending.

Manual triggers bypass the 60-second debounce, run synchronously and report completed or failed. The series trigger reports failed when any leaderboard failed, naming them; the others still committed.

6. Upsert Strategy

Standings are upserted into the ResultSet keyed by the grouping CustomListValue (school/club/ team id) so that re-runs update existing rows rather than duplicating them, and rows that drop out of the standings are removed:

public void upsertLeaderboardResults(ResultSet resultSet, List<LeaderboardEntry> entries) {
    Map<Long, RaceResult> existingByCustom = resultSet.getResults().stream()
        .filter(r -> r.getCustom1() != null)
        .collect(Collectors.toMap(r -> r.getCustom1().getId(), r -> r));

    Set<Long> processedIds = new HashSet<>();
    for (LeaderboardEntry entry : entries) {
        RaceResult existing = existingByCustom.get(entry.getCustomListValueId());
        if (existing != null) {
            existing.setPosition(entry.getPosition());
            existing.setPoints(entry.getPoints());
            existing.setSeq(entry.getSeq());
            raceResultRepository.save(existing);
            processedIds.add(existing.getId());
        } else {
            raceResultRepository.save(new RaceResult()
                .resultSet(resultSet)
                .custom1(customListValueRepository.getReferenceById(entry.getCustomListValueId()))
                .position(entry.getPosition()).points(entry.getPoints()).seq(entry.getSeq()));
        }
    }
    resultSet.getResults().stream()
        .filter(r -> !processedIds.contains(r.getId()))
        .forEach(raceResultRepository::delete);
}

The seq field (see the model) gives deterministic ordering when positions tie, and a stable secondary key for this upsert.

7. Failure Handling

A recalculation that fails has written nothing. Each leaderboard is recalculated in its own transaction, so the series' other leaderboards still commit. After the failed one’s transaction rolls back:

  1. One inactive FAILED draft is created, or refreshed, per standing the attempt would have rewritten, with the cause in result_set.error_message. The published standing is untouched.

  2. While a FAILED draft exists for any event standing of the leaderboard, the cumulative roll-up refuses, so the whole board stays at its last consistent state. The season’s own draft is the roll-up’s target, never a block, and drafts the retry has already promoted in its own transaction do not count.

  3. The leaderboard reactor’s sweep retries the drafts every ten minutes; the signal that caused the attempt retries with backoff; the manual recalculation API retries on demand. All three write into the draft and promote it on success.

  4. Telling someone is a separate concern: an email on the first failure, and later an Admin Portal notification. Both fire when the leaderboard goes from having no draft to having one.

A failure on the manual recalculation endpoints takes the same path, so it leaves drafts and is emailed in the same way (#1456).

7.1. The operator email

The operator named in ems.leaderboard.failure-notification.to is emailed (US #1463) once the drafts have committed, and only when the leaderboard goes from having no FAILED draft to having one. A leaderboard that fails again on every ten-minute retry produces one email, not one per retry; a leaderboard that recovers and later fails again produces another.

A plain-text message, subject Leaderboard recalculation failed: <leaderboard> (<series>), carrying:

  • the leaderboard, its series and the series' organisation, by id and name;

  • the standings that could not be recalculated, those the failure left FAILED drafts beside: each event by name and date, then season total for the cumulative standing;

  • the cause, exactly as recorded on error_message;

  • when the attempt started, and which deployment sent it: the application name, the pod’s host name and the base URL where one is set, so that a stage email is not read as production;

  • what happens next: the published standings stay active and unchanged and the season total is not recalculated until a retry succeeds, and no further email comes while the drafts remain;

  • the two manual recalculation endpoints for retrying by hand.

It is operational mail, not a customer communication. It goes straight to the email transport: no email_template row, no communication_log row and no idempotency guard.

The address is per deployment. application.yml ships [email protected], the same in every profile — on dev the mail host is localhost:25, so nothing leaves the machine — and an environment overrides it through config.extraProperties in its ArgoCD manifest. Blank turns the email off.

A mail failure changes nothing else. It is logged; the FAILED drafts keep the recalculation’s cause, never the mail error, and the series' other leaderboards still recalculate.

Not built: a notification in the Admin Portal, which is the eventual channel (#1464), and an email when a leaderboard recovers.

8. Schema additions

This layer adds only the sync-specific columns; the leaderboard tables themselves are in the model.

Both columns have now shipped. The table states each one’s status because they were once listed together as though both had shipped when only one had.
Change Status Description

result_set.error_message

Shipped

FAILED-state details. Present in the entity and in the live schema.

result_set.leaderboard_id FK

Shipped (#1379)

Links a ResultSet to the leaderboard it materialises. A standing produced before it existed is linked only by ResultSet.name matching Leaderboard.name, and is adopted on its first recalculation. Specified in Leaderboard Materialisation.

9. Deferred / open design

  • A general GROUP / CUSTOM_LIST LeaderboardType (or a second-CustomList override on SCHOOL) to model Club/Team grouping and the COALESCE(team, club) overlay without reusing SCHOOL — see WP Cycling procedure.

  • A first-class per-leaderboard event set (flag on event / join table) to replace the hardcoded event-ID lists used by the current manual "all events vs starred events only" aggregation.

10. Related Documentation