Leaderboard Materialisation & Cascading Recalculation

1. Overview

Leaderboard Model describes how results are scored and classified. This page describes what happens next: how a calculated standing is persisted, ranked and published, and how a change to a race result propagates through to the season standing.

This is the specification for the outstanding Phase 6 work under Feature #143. The calculation engine was delivered under Phases 1–5; the persistence step was never built and never ticketed.

This page is the specification Phase 6 was built from. US #1379, #1380 and #1381 implemented it, merged on 2026-09-30. Standings are now persisted, ranked and published as specified here, and result_set.leaderboard_id exists. The passages that describe the code and schema as they were before that work are kept as the record it started from, not as the current state: the block below, Schema changes and Code defects to fix.

Today LeaderboardSynchronizationService.recalculateLeaderboard() obtains a calculator, computes the entries, logs the count, and discards them:

List<LeaderboardEntry> entries = calculator.calculateForSeries(leaderboard);
log.info("Leaderboard {} calculated {} entries", leaderboard.getId(), entries.size());
// TODO: Phase 6 - Upsert results into ResultSet (state-aware)

Every standing in production was therefore produced manually, by SQL. This page specifies the automation that replaces that, and is written against the verified behaviour of those manual standings rather than against the untested code.

2. The two tiers

A standing exists at two levels, both stored as a ResultSet of participant-less RaceResult rows.

Tier ResultSet.event_id Contents Produced by

Per-event

the event

that event’s ranking alone

aggregating rider results within the event

Cumulative

NULL

the season standing

summing the per-event tier

Both tiers already exist in the schema for the 2026 Schools League (series 9): 30 per-event standings across 5 events, and 6 cumulative placeholders that have never been populated (result_set 515–520). The cumulative tier is where Phase 6’s second step writes.

2.1. The cascade

race result changes
   -> the per-event standing for that event is recalculated
      -> the cumulative standing for the series is recalculated

The cascade is what makes the cumulative tier a pure roll-up: top_count and exclusions are applied once, in tier 1, and tier 2 inherits both. Tier 2 does no filtering and no rider-level arithmetic — it is a SUM over tier 1. Keep it that thin.

3. Tier 1 — the per-event standing

3.1. Verified aggregation rule

Verified 2026-09-25 against wpca_prod by reproducing a stored standing from the underlying rider results. Standing result_set 733 (Top Schools — Boys/Men — PS, event 147) reproduced with 21 of 21 stored values matching exactly.

Element Source Series 9 value

Candidate results

leaderboard_category.series_category_id joined to event_category.series_category_id

12 primary-school categories

Gender filter

event_category.gender vs leaderboard.gender

M / F

Level filter

event_participant.custom_2_id vs leaderboard.level_filter_id

1500 = PS, 1501 = HS

Grouping

event_participant.custom_1_id, the slot named by leaderboard.group_by_list_id

School

leaderboard.top_count

best N rider results per group

5 boys / 3 girls

leaderboard.result_limit

number of groups published

20

gender cannot be read from person. It is NULL for every participant in this series; gender is carried by the event_category. A leaderboard’s leaderboard_category links include both genders' categories — leaderboard 1500 (Boys/Men PS) links all 12 PS categories, Boys and Girls alike. The gender narrowing is a separate predicate on event_category.gender, not a property of the category links.

3.2. Order of operations

Exclusions are applied before result_limit, not after. This is verified, not assumed: in standing 733 the excluded group ranks 6th on points and is absent, yet 20 rows are stored. Had the limit been applied first, 19 rows would remain.

  1. Select candidate results (categories, gender, level).

  2. Group by the grouping slot; within each group take the best top_count results by points.

  3. Sum those to the group total.

  4. Remove excluded groups.

  5. Rank, and cut to result_limit.

3.3. Acceptance oracle

This query reproduces tier 1 from first principles and is the acceptance test for the implementation. Substituted for leaderboard 1500 / event 147, it must equal the stored standing row for row.

It carries the whole publication rule, not just the aggregation: the exclusion and the result_limit cut are part of what makes a standing the standing. An oracle that stops at the group totals returns every eligible group — 63 of them for event 117 against 19 published — and cannot be compared to stored data without a human applying the rest by eye.

WITH scoped AS (
  SELECT ep.custom_1_id AS group_id, rr.points,
         ROW_NUMBER() OVER (PARTITION BY ep.custom_1_id
                            ORDER BY rr.points DESC, rr.id) AS rn
  FROM race_result rr
  JOIN result_set rs ON rs.id = rr.result_set_id
                    AND rs.name = 'Results' AND rs.event_id = :eventId
  JOIN event_participant ep ON ep.id = rr.event_participant_id
  JOIN event_category ec ON ec.id = ep.category_id
  JOIN leaderboard_category lc ON lc.series_category_id = ec.series_category_id
                              AND lc.leaderboard_id = :leaderboardId
  WHERE ec.gender = :gender AND ep.custom_2_id = :levelFilterId
    AND ep.custom_1_id IS NOT NULL
),
totals AS (
  SELECT s.group_id, SUM(s.points) AS points
  FROM scoped s
  JOIN custom_list_value clv ON clv.id = s.group_id
  WHERE s.rn <= :topCount
    AND clv.<exclusion flag> = 'N'                          (1)
  GROUP BY s.group_id
),
ranked AS (
  SELECT t.*, RANK() OVER (ORDER BY t.points DESC) AS position   (2)
  FROM totals t
)
SELECT group_id, points, position
FROM ranked
WHERE position <= :resultLimit                                   (3)
ORDER BY position, group_id
1 Excluded groups are removed before the cut, which is why 20 rows are published although the excluded group ranks 6th. Column name per Exclusions.
2 RANK(), not ROW_NUMBER() — tied groups share a position, per Ranking, seq and ties.
3 Filtering on the rank rather than LIMIT :resultLimit is what extends the standing through a tie at the boundary: two groups level on 20th both carry position 20 and both publish.

3.4. Stored row shape

Standing rows are participant-less: person_id and event_participant_id are NULL, and the group is carried in custom_1_id. This is the classification convention described under Leaderboard Model — for a materialised standing there is no participant to classify from, so the CustomListValue slots are authoritative.

4. Tier 2 — the cumulative standing

The season standing is a SUM of the per-event tier, grouped by the same slot. It applies neither top_count nor exclusions, because tier 1 already did — both are inherited through the rows it sums.

It does apply result_limit. The cut is a property of publication, not of aggregation, so it is made again here rather than inherited: the top 20 schools of the season are not necessarily the union of each event’s top 20.

WITH totals AS (
  SELECT rr.custom_1_id AS group_id, SUM(rr.points) AS points
  FROM result_set rs
  JOIN race_result rr ON rr.result_set_id = rs.id
  JOIN event e ON e.id = rs.event_id
  WHERE rs.active = 'Y'                           (1)
    AND rs.leaderboard_id = :leaderboardId        (2)
    AND e.start_date_time <= :asAt                (3)
  GROUP BY rr.custom_1_id
),
ranked AS (
  SELECT t.*, RANK() OVER (ORDER BY t.points DESC) AS position
  FROM totals t
)
SELECT group_id, points, position
FROM ranked
WHERE position <= :resultLimit                    (4)
ORDER BY position, group_id
1 The single-active-standing invariant. Filtering on active and never on state is what keeps this a SUM rather than a double count — see The single-active-standing invariant.
2 Replaces identification by the (series, gender, custom_list_2_name) triple. See Schema changes.
3 Replaces rs.event_id ⇐ :eventId. See Ordering and identification.
4 Same rank-based cut as tier 1, so a tie at the boundary publishes both groups.

Only the current cumulative standing is materialised. An as-at-any-event standing remains derivable from tier 1 by the query above, because tier 1 is retained per event. Materialising one snapshot per event would multiply the write path for no gain.

The roll-up refuses while any event’s standing has a failed attempt. A FAILED draft (see The failed attempt’s draft) for any event standing of the leaderboard means that event’s published standing is behind its rider results. The season standing’s own draft is the roll-up’s target, never a block. Summing it would combine fresh and stale events with nothing on the season standing to say which. The roll-up therefore refuses, the attempt rolls back, and the board stays at its last consistent state until the cause is repaired. The check reads the flushed state of the attempt’s own transaction, so a retry that has just promoted the drafts it covers is not blocked by them.

5. Ordering and identification

Three habits carried by the existing manual queries should be designed out rather than ported into the implementation.

Habit Why it is fragile Use instead

result_set.event_id ⇐ :eventId as a chronological cut

event_id is a surrogate key, not a date. It matches the calendar for series 9 only by practice — a rescheduled or back-filled event breaks the window with no symptom.

event.start_date_time

result_set.custom_list_2_name = 'PS'

Filters on a display label. Renaming the label silently changes which rows aggregate.

the id — custom_2_id against leaderboard.level_filter_id

Identifying a standing by (series, gender, custom_list_2_name)

Re-derives the leaderboard from denormalised columns because no foreign key exists.

result_set.leaderboard_id — see Schema changes

6. The single-active-standing invariant

Tier 2 is arithmetically correct only while, per leaderboard and event, exactly one standing is active = 'Y'. If two are ever active, every group’s season total silently inflates for that event. There is no error and no warning, only wrong published numbers.

Beside that one active standing there is at most one FAILED draft: the record of a failed attempt to replace it. It is inactive and has no rows, so it is never summed. Together:

  • at most one active standing per leaderboard and event, and one per leaderboard for the season;

  • at most one FAILED draft beside each.

ResultSetCloneService.cloneForUpdate() retires the version it supersedes, and StandingMaterialisationService refuses to write where it finds two active standings.

6.1. The defect that broke it

Before the materialisation foundation landed, ResultSetCloneService.cloneForUpdate() did not maintain the invariant:

clone.setActive(original.getActive());           // the clone inherits active = 'Y'
...
original.setState(ResultSetState.REPLACED);      // state changes...
original.setLastUpdate(Instant.now());
resultSetRepository.save(original);              // ...but active is never cleared

The original was marked REPLACED but remained active = 'Y', so both rows were active, and the first clone of a standing would have double-counted that event. It was dormant only because every standing was DRAFT, which is modified in place, so the clone path was never reached.

cloneForUpdate() now retires the original: active = 'N', replaced_on stamped. Filtering on active alone selects the current standing, and a test proves exactly one version per leaderboard and event stays active after a clone.

6.2. Recalculation must delete, not only upsert

A group that falls out of result_limit on recalculation must have its row removed. Upserting only the survivors leaves a demoted group published forever.

The alternative — leaving stale rows and relying on every consumer to apply LIMIT — was considered and rejected: it pushes correctness into every report, and any consumer that forgets the limit reads a wrong standing.

7. Versioning and audit

Superseded standings are retained for audit and excluded from calculation. A failed attempt is recorded on a draft of its own, never on the standing it could not replace.

Field Role

active

The calculation filter, and the reader’s filter. Y for the current version, N for every superseded one and for a FAILED draft.

state

Approval lifecycle (DRAFT, PROVISIONAL, APPROVED, REPLACED, DELETED) and, on a draft, FAILED. Audit and workflow only; never a filter for selecting the current version.

replaced_on

When this version was superseded.

replaces_id

Set on the new row, pointing back to the row it replaced. On a FAILED draft, the standing the failed attempt was trying to replace.

error_message

On a FAILED draft, the cause of the latest attempt.

7.1. The failed attempt’s draft

A recalculation that fails has written nothing: its transaction rolled back. What it leaves behind is one draft per standing it would have rewritten, the event standings it attempted and the season standing: active = 'N', state = 'F', no rows, the cause on error_message, and replaces_id pointing at the target’s active standing, or null when the target has none yet. That null case is per target: a newly added event can fail its first calculation on a board whose other events and season standing are published. The published standing is untouched: its state, rows and approval stamp are as they were, and a reader selecting on active sees exactly what was there before the failure.

The draft is a clone created early and empty. A retry, from the sweep, a signal or the manual API, writes its rows into the draft. On success the draft is promoted (active = 'Y', state DRAFT, cause cleared) and its predecessor is retired as REPLACED with replaced_on stamped, keeping any approval it carried. On a further failure the draft stays and its cause is refreshed. There is never more than one draft per target; earlier attempts are in the log.

A target that fails before it has ever been published, a new leaderboard or a newly added event, has a draft and no active standing, so nothing is available for it until it succeeds.

A recalculation that succeeds leaves no draft behind. While an event standing has a draft, the tier-2 roll-up refuses (the season standing’s own draft is the roll-up’s target, never a block), so an event draft no retry could reach would freeze the board for good. Two rules prevent that:

  • A draft’s event is recalculated with the leaderboard even when the event no longer has rider results. It publishes the standing those results now give, which may be empty.

  • A draft whose target the recalculation no longer produces is promoted empty, as a standing in its place is emptied. This arises on an Individual Rider board, for a category that has lost its results at the event or a series category the board is no longer linked to.

The "replaced by" edge already exists as replaces_id, in the inverse direction. Adding a replaced_by_id column would give one relationship two representations that can drift out of step. If a forward pointer proves necessary for query convenience, add replaced_by_id and drop replaces_id, so there remains exactly one edge.

8. Ranking, seq and ties

position and seq are NULL on every standing row in production. Phase 6 must populate both.

Field Rule

position

Standard competition ranking. Tied groups share a position and the next position skips — 1, 2, 2, 4. This represents a tie honestly rather than inventing a winner.

seq

Unique and deterministic, assigned by (points DESC, group value id ASC). It is the upsert key and carries no sporting meaning.

8.1. Ties need no rule at the top_count cut

If the Nth and (N+1)th best rider results have equal points, either choice yields the same group total. A tie at the rider cut is arithmetically irrelevant; no rule is required.

8.2. Ties at the publication boundary

Where the groups at result_limit and result_limit + 1 are level on points, publish both. result_limit means "at least N, extended through ties at the boundary". A coin-flip must not decide who appears in a published standing.

This makes a hardcoded LIMIT 20 in a downstream report harmful — it would silently re-truncate a deliberately extended standing. Consumers must read leaderboard.result_limit or, better, read the pre-cut standing as stored.

8.3. Ties in the ranking itself

There is no business rule, and this specification deliberately does not invent one. Shared positions are correct and complete for publication. Should a rulebook later require a strict order, the natural candidates are countback — compare best individual result, then second-best — for tier 1, and most recent event’s points for tier 2. Both are rulebook-specific and differ between customers, so they belong in per-leaderboard configuration, not in the calculator.

9. Exclusions

Some grouping values are not competing entities. In series 9, "Home School" carries 148 participants and appears in zero published standings — it is a catch-all bucket, not a school.

Exclusion is not specific to schools. Any of the three CustomList slots available to an event participant or a result may be used as a leaderboard’s grouping dimension, and any of them may need exclusions.

Recommendation: a flag on custom_list_value, not on the leaderboard.

Consequence

Generality

Applies to whichever slot is the grouping dimension, with no per-slot code.

Maintenance

One row per excluded value, rather than one row per (leaderboard, value) pair — six rows for series 9’s six leaderboards.

Semantics

The value itself is not a competitor. That is a property of the value, not of a board.

The trade-off: the flag is global across every leaderboard using that list. That is the correct default for a non-competing bucket. If a genuine case appears where one board excludes a value another includes, add a per-leaderboard override table then, when the requirement is real.

Follow the varchar(1) boolean convention used throughout this schema (active, custom_list_1_required) with an attribute converter — see the project’s java-varchar-enum-persistence skill.

10. Schema changes

Author each as a Liquibase changeSet per the project’s liquibase-changelog-create skill.

Change Why

result_set.leaderboard_id → leaderboard(id)

The missing foreign key. Today the only link from a materialised standing back to its definition is that result_set.name equals leaderboard.name. Every consumer re-derives the leaderboard from a (series, gender, custom_list_2_name) triple. Note that Leaderboard Model already describes ResultSet as carrying "an optional leaderboard link" — the documentation is ahead of the schema, and that page needs correcting either way.

result_set.replaced_on

Audit: when this version was superseded. See Versioning and audit.

custom_list_value exclusion flag

Exclude non-competing grouping values. See Exclusions.

11. Code defects to fix

All verified against origin/develop on 2026-09-25. Each would produce a wrong or empty standing.

# Location Defect

1

SchoolLeaderboardCalculator.fetchResults()

Returns Collections.emptyList() — a TODO. The calculator aggregates nothing regardless of everything else.

2

SchoolLeaderboardCalculator.getSchool()

Reads the grouping value from getCustom3(). For series 9 the grouping slot is custom_1. The slot is per-series configuration and leaderboard.group_by_list_id exists to carry it; hardcoding it silently yields null for every row.

3

TopSchoolsLeaderboardCalculator.calculate()

Applies topCount as "top N groups". Verified behaviour is that top_count is the best N rider results per group, and result_limit is the group cut. The two are transposed.

4

PointsCalculatorFactory.getCalculator()

leaderboard.points_calculator_class holds a simple class name (SASchoolCyclingSeriesPointsCalculator). Neither a short code nor resolvable by Class.forName, so it throws, logs a warning, and falls back to the default — which happens to be the same class. It works by accident. Configure a second scheme this way and results are scored by the wrong calculator with only a WARN in the log.

5

ResultSetCloneService.cloneForUpdate()

Leaves the superseded ResultSet active = 'Y'. See The single-active-standing invariant.

Defect 4 matters more after Phase 6 than before it: today a mis-resolved calculator computes wrong points and discards them. Once persistence lands, it publishes them.

12. Once-off data alignment

The 30 existing per-event standings have NULL position and seq. A once-off script — not a Liquibase changeSet, as this is data repair and not schema evolution — must backfill them.

Rank from the stored points; do not recalculate. The script’s job is to add the missing ordering to values that were already published. Recalculating would silently restate published standings in the same pass, conflating a backfill with a correction.

At least one stored standing has already drifted from its inputs. Reproducing event 117’s Boys PS standing gives 18 of 19 groups matching, with one group at 423 stored against 436 calculated, and 19 rows stored where 20 or more were eligible. The stored figure is reconstructible as a different selection of five rider results, consistent with results having been corrected after the standing was computed manually.

The season standing is a SUM over these stored per-event values, so that discrepancy propagates into the published season total, understated by 13 points, with nothing to surface it. This is the cost of a materialised tier with no recalculation path — and the clearest argument for the cascade.

Correcting it is a deliberate, reviewed recalculation, separate from the backfill.

13. Out of scope

  • Result import — how results arrive is Result Import and is delivered separately.

  • The Signal & Sweep migration — moving recalculation onto the reactor framework is US #409, which followed this page’s work: its stated precondition was exactly the upsert specified here. See Reactor Catalogue.

  • Leaderboard configuration UI — administering definitions without SQL is Feature #129, and is gated on a use-case specification and screen design.

14. Related Documentation