Leaderboard Synchronization

1. Overview

The Leaderboard Synchronization system automatically recalculates leaderboard standings when race results change. It replaces the current manual SQL-based process with an event-driven architecture that supports multiple leaderboard types.

2. Problem Statement

Currently, leaderboards (Top Schools, All Stars, Individual Rider) are created via manual SQL scripts with hardcoded series category IDs. When race results change, there’s no automatic recalculation of affected leaderboards.

3. Leaderboard Types

Type Description

SCHOOL

Groups points by School (EventParticipant.custom_3_id)

TOP_SCHOOLS

Top N schools by aggregate points (per gender, per PS/HS level)

ALL_STARS

Cross-gender aggregate of top schools

INDIVIDUAL_RIDER

Aggregates Person points across ALL events in a series

4. Source Code

4.1. New Files

File Purpose

Leaderboard.java

Leaderboard configuration entity

LeaderboardCategory.java

Category mapping entity (replaces hardcoded series_category_id lists)

LeaderboardType.java

Enum for leaderboard types

ResultSetChangedEvent.java

Single event for all result changes

LeaderboardSynchronizationService.java

Main sync service with debouncing

LeaderboardCalculator.java

Calculator interface

SchoolLeaderboardCalculator.java

School points aggregation

TopSchoolsLeaderboardCalculator.java

Top N schools calculation

AllStarsLeaderboardCalculator.java

Cross-gender aggregate

IndividualRiderLeaderboardCalculator.java

Person points across series

LeaderboardResource.java

REST API for manual recalculation

LeaderboardFailureNotificationService.java

Failure notification handling

LeaderboardFailureEmailTask.java

Scheduled task to email admins on failures

Location: admin-service/src/main/java/za/co/idealogic/event/admin/service/

4.2. Modified Files

File Purpose

RaceResult.java

Add seq field for deterministic ordering

ResultSet.java

Add leaderboard relationship, errorMessage field

ResultSetState.java

Add FAILED('F') state

RaceResultServiceEx.java

Publish ResultSetChangedEvent on save/delete

ResultImportXLS.java

Publish event after import

ResultSetImportSheets.java

Publish event after import

SASchoolCyclingSeriesPointsCalculator.java

Implement PointsCalculator interface

5. Architecture

5.1. Event Flow

                    ┌─────────────────────┐
                    │   Result Change     │
                    │  (API/Import)       │
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │ ResultSetChangedEvent│
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │  Debounce (60s)     │
                    └──────────┬──────────┘
                               │
                               ▼
                    ┌─────────────────────┐
                    │ Find Affected       │
                    │ Leaderboards        │
                    └──────────┬──────────┘
                               │
              ┌────────────────┼────────────────┐
              ▼                ▼                ▼
       ┌──────────┐     ┌──────────┐     ┌──────────┐
       │  School  │     │Top Schools│    │Individual│
       │Calculator│     │Calculator │    │ Rider    │
       └────┬─────┘     └─────┬─────┘    └────┬─────┘
            │                 │               │
            └────────────────┬┴───────────────┘
                             │
                             ▼
                    ┌─────────────────────┐
                    │  Upsert Results     │
                    │  (state-aware)      │
                    └─────────────────────┘

5.2. Rate Limiting

The system uses a 60-second debounce mechanism:

  • When a ResultSetChangedEvent is received, it’s queued

  • A scheduled task checks for pending changes older than 60 seconds

  • This prevents rapid recalculations during bulk imports or rapid edits

  • Manual recalculation via API bypasses the debounce

5.3. ResultSet State Machine

State Code Behavior

DRAFT

-

Can upsert freely, not displayed publicly

PROVISIONAL

P

Can upsert freely, displayed publicly but marked as provisional

APPROVED

A

Cannot modify - must clone to new DRAFT ResultSet

REPLACED

R

Previous version, replaces field links new version

DELETED

D

Cannot modify - must clone to new DRAFT ResultSet

FAILED

F

Recalculation failed - triggers notifications, can retry

5.4. State Transitions

States: DRAFT → PROVISIONAL → APPROVED
             ↓         ↓           ↓
           (upsert)  (upsert)   (clone to DRAFT)
             ↓         ↓              ↓
           FAILED    FAILED    REPLACED (original)

When upserting leaderboard results:

  • If state is DRAFT or PROVISIONAL: update directly

  • If state is APPROVED or DELETED: clone ResultSet, set original to REPLACED, update clone

  • On failure: set state to FAILED, store error message

6. Domain Entities

6.1. Leaderboard Entity

@Entity
public class Leaderboard {
    Long id;
    String name;
    LeaderboardType type;       // TOP_SCHOOLS, SCHOOL, ALL_STARS, INDIVIDUAL_RIDER
    Gender gender;              // NULL for gender-neutral
    Integer topCount;           // Number of top results to aggregate (e.g., 5 for boys, 3 for girls)
    Integer resultLimit;        // Max entries in leaderboard
    Boolean active;
    Series series;
    CustomListValue levelFilter;  // PS or HS CustomListValue
    CustomList groupByList;       // List to group by (e.g., Schools)
    String pointsCalculatorClass; // Pluggable points calculator
    Set<LeaderboardCategory> categories;
}

6.2. LeaderboardCategory Entity

Maps leaderboards to source series categories, replacing hardcoded ID lists.

@Entity
public class LeaderboardCategory {
    Long id;
    Leaderboard leaderboard;
    EventCategory seriesCategory;  // Series-level category
    BigDecimal pointsMultiplier;   // Optional points multiplier
}

6.3. RaceResult Modification

Add seq field for deterministic ordering:

@Column(name = "seq")
private Integer seq;

The seq field provides:

  • Unique ordering within a ResultSet (position can have ties)

  • Secondary key for upsert identification

  • Deterministic display order

7. Calculator Framework

7.1. Interface

public interface LeaderboardCalculator {
    LeaderboardType getType();
    List<LeaderboardEntry> calculate(Leaderboard leaderboard, Long eventId);
}

7.2. Points Calculator Interface

public interface PointsCalculator {
    int getPoints(Integer position);
}

The existing SASchoolCyclingSeriesPointsCalculator will implement this interface, allowing pluggable points calculators per leaderboard.

7.3. School Leaderboard Calculator

Groups points by school using EventParticipant.custom_3_id:

// 1. Get series category IDs from LeaderboardCategory
Set<Long> seriesCategoryIds = leaderboard.getCategories().stream()
    .map(lc -> lc.getSeriesCategory().getId())
    .collect(Collectors.toSet());

// 2. Query results matching criteria
List<RaceResult> results = raceResultRepository.findForLeaderboard(
    eventId, gender, seriesCategoryIds, levelFilterId);

// 3. Group by school (custom_3)
Map<Long, List<RaceResult>> bySchool = results.stream()
    .filter(r -> r.getEventParticipant().getCustom3() != null)
    .collect(Collectors.groupingBy(
        r -> r.getEventParticipant().getCustom3().getId()));

// 4. Sum points per school
List<LeaderboardEntry> entries = bySchool.entrySet().stream()
    .map(e -> new LeaderboardEntry(e.getKey(),
        e.getValue().stream().mapToInt(RaceResult::getPoints).sum()))
    .sorted(Comparator.comparingInt(LeaderboardEntry::getPoints).reversed())
    .collect(Collectors.toList());

7.4. Top Schools Calculator

Similar to School calculator but takes only top N results per school:

// 4. For each school, take top N and sum
int totalPoints = entry.getValue().stream()
    .sorted(Comparator.comparingInt(RaceResult::getPoints).reversed())
    .limit(leaderboard.getTopCount())
    .mapToInt(RaceResult::getPoints)
    .sum();

7.5. Individual Rider Calculator

Aggregates Person points across ALL events in a series (not just one event):

// Query all results for series (not just one event)
List<RaceResult> results = raceResultRepository.findBySeriesAndCategories(
    seriesId, seriesCategoryIds, gender, levelFilterId);

// Group by Person ID
Map<Long, List<RaceResult>> byPerson = results.stream()
    .filter(r -> r.getPerson() != null)
    .collect(Collectors.groupingBy(r -> r.getPerson().getId()));

8. REST API

8.1. Manual Recalculation

Endpoint Purpose

POST /api/leaderboards/{id}/recalculate

Trigger immediate recalculation for specific leaderboard

POST /api/leaderboards/recalculate-all?seriesId={id}

Recalculate all active leaderboards for a series

Manual triggers bypass the 60-second debounce and return job status.

9. Failure Handling

9.1. FAILED State

When recalculation fails:

  1. ResultSet state is set to FAILED

  2. Error message stored in error_message column

  3. UI notification sent (via existing notification mechanism)

  4. Scheduled task polls for FAILED ResultSets and emails administrators

9.2. Failure Notification Email

Email contains:

  • Leaderboard name

  • Event name

  • Error message

  • Link to retry

9.3. Retry

Administrators can retry failed recalculations:

  • Via manual recalculation API

  • State returns to DRAFT/PROVISIONAL on success

10. Database Changes

Change Description

Add seq column

race_result.seq for deterministic ordering

Create leaderboard table

Configuration entity for leaderboards

Create leaderboard_category table

Maps leaderboards to series categories

Add leaderboard_id FK

result_set.leaderboard_id for direct linking

Add error_message column

result_set.error_message for FAILED state details

11. Upsert Strategy

public void upsertLeaderboardResults(ResultSet resultSet, List<LeaderboardEntry> entries) {
    // Build lookup map by custom field (school ID)
    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) {
            // Update existing
            existing.setPosition(entry.getPosition());
            existing.setPoints(entry.getPoints());
            existing.setSeq(entry.getSeq());
            raceResultRepository.save(existing);
            processedIds.add(existing.getId());
        } else {
            // Create new
            RaceResult newResult = new RaceResult()
                .resultSet(resultSet)
                .custom1(customListValueRepository.getReferenceById(entry.getCustomListValueId()))
                .position(entry.getPosition())
                .points(entry.getPoints())
                .seq(entry.getSeq());
            raceResultRepository.save(newResult);
        }
    }

    // Remove results no longer in leaderboard
    resultSet.getResults().stream()
        .filter(r -> !processedIds.contains(r.getId()))
        .forEach(raceResultRepository::delete);
}

12. PS/HS Level Filtering

Previously, PS (Primary School) vs HS (High School) was determined by hardcoded series_category_id lists in SQL:

-- Old approach (hardcoded)
ec.series_category_id IN (2163,2164,2165,2166,2167,2168,2169,2170)

With the new Leaderboard entity:

  • levelFilter field references the PS or HS CustomListValue

  • LeaderboardCategory links to specific series categories

  • No more hardcoded ID lists

13. Related Documentation