Athlete Relationships V0.1: Technical Plan

Source date: September 29, 2026. Related documents: relationship PRD, V0.2 model and feature plan, and V0.2 PRD. This is the implementation baseline proposed for V0.1. Later relationship scoring and a standalone page were separate iterations.

1. Goal and Existing State

The goal was a stable, cacheable, iterative relationship module on athlete profiles, without building an entire relationship platform. The first version would show “Athletes most closely connected to this person,” combining direct matchups, teammates, and repeated encounters in the same career stage. It would derive relationships from existing facts, add no primary relationship business table, and use precomputation plus lazy calculation backed by a cache table.

The project already had Person, Membership, RaceResult, Race, CompetitionEdition, and Competition. Its athlete page had a light relationship block based on shared-school memberships and identical raceId values, with labels assembled in the page request. The limitations were request-time computation, no reusable cache, no aggregate ranking, no distinction between core athletes and occasional profiles, and only simple co-race counts. The technical change was to turn temporary page assembly into a reusable derived result.

2. Design Constraints

Relationships are derived from facts; they are not themselves new source facts. V0.1 would not change the primary business data model. The cache should store related-athlete IDs, brief reasons, an internal ordering result, and metadata, without copying all race details.

The page should read the cache and calculate once on a miss. The proposed primary cache was a PostgreSQL table: the site had no production Redis, queue, or KV service, and Next.js cache was not selected as the business cache. A maintenance script would precompute core profiles; a non-core profile could calculate synchronously on its first page request. Redis or a queue could be reconsidered if traffic later justified them.

The architecture was:

Person / Membership / RaceResult / Race / CompetitionEdition / Competition
  -> direct-matchup, teammate, and repeated-stage calculators
  -> aggregate ranker
  -> PostgreSQL PlayerRelationCache per athlete
  -> athlete profile

Maintenance script -> precompute core athletes
Cache miss on a profile -> synchronous local calculation and write-back

3. Fact Sources and Relationship Semantics

Direct matchup: two athletes each have a RaceResult with the same raceId. Race already encodes event, discipline, and heat or group, so this matches the PRD's same-event/same-discipline/same-group definition. The calculator uses RaceResult, Race, CompetitionEdition, and Competition.

Teammates: use Membership, Organization, and RaceResult to find either an overlapping period in the same organization or joint participation for that organization in the same significant competition edition.

Repeated same-stage encounters: use results, race and competition context, and memberships to identify athletes repeatedly present in important events at the same career stage. It is not simply the same-race definition; V0.1 planned lightweight rules rather than clustering.

4. Cache Model

The proposed PlayerRelationCache is a derived cache table, not a source-of-truth relationship table:

model PlayerRelationCache {
  id          String   @id @default(cuid())
  personId    String   @unique
  payload     Json
  generatedAt DateTime @default(now())
  sourceHash  String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  person Person @relation(fields: [personId], references: [id], onDelete: Cascade)
}

There is one aggregate result per person. payload is ready for a page to consume; generatedAt supports freshness checks; sourceHash leaves room for later change detection and could be empty or basic in V0.1. Recalculation can replace this row.

type PlayerRelationCachePayload = {
  personId: string;
  generatedAt: string;
  topRelations: Array<{
    relatedPersonId: string;
    relationScore: number;
    reasons: Array<{
      type: "direct_matchup" | "teammate_overlap" | "frequent_same_stage";
      label: string;
      priority: number;
    }>;
    summary: {
      directMatchupCount: number;
      teammateOverlapYears: number | null;
      sameStageMeetCount: number;
    };
  }>;
};

relationScore was internal for sorting and not displayed in V0.1. reasons supplied short UI labels, while summary reserved useful counts for later detail. The cache did not contain complete race histories.

5. V0.1 Calculation Rules

The first release used fixed rules, without a configurable scoring system.

Direct Matchups

For the subject athlete, fetch all raceId values, fetch other athletes' results in those races, group by related person, count shared race IDs, and extract competition, edition, and career-stage context. The output is a matchup count and one or two representative factual explanations.

The preferred label order was same-leg ekiden meetings, cross-stage direct-match count, then recognizable competition names. Examples included “Three direct matchups on the same ekiden leg,” “Five shared races from high school to university,” and “Met repeatedly at Hakone and Izumo.” These describe co-participation, not a win/loss judgment.

Proposed internal score: +10 per direct matchup, an extra +5 when the competition is an ekiden, and +8 if the relationship spans at least two career stages.

Teammates

Fetch the subject's school, university, and corporate-team memberships; find other people in the same organizations; test date overlap; and check whether they appeared together for an organization in an important ekiden edition or career-stage event. The output covers overlapping memberships and joint appearances.

Example labels were “University teammates for four years,” “Appeared together in editions 101 and 102,” and “Shared key legs for the team during the same period.” Proposed internal score: +8 for a same-organization overlap, +2 per additional overlapping year, and +4 for each joint appearance in an important edition.

Repeated Same-Stage Encounters

Split results by high-school, university, and corporate-team stages. Find other athletes repeatedly appearing in important comparable events in the same stage, then avoid duplicating a relationship already strongly explained by direct matchups. This supplies supplementary long-running peers rather than another direct-race count.

Example labels were “Met repeatedly at national events during university” and “Appeared on the same competitive stage over several years.” Proposed score: +3 for a repeated important same-stage event and an extra +4 for persistence across years.

6. Aggregate Ranking

A single aggregator should merge an athlete found through several relationship types, add the internal scores, deduplicate reasons, and keep no more than three reasons. Order by total score, then direct-matchup count, teammate-overlap duration, and most recent relationship date. The profile shows the top six; the V0.1 cache was proposed to retain top twelve for a possible later “show more.” A later plan proposed caching the full qualifying set for a standalone network page, so this top-twelve rule is specific to V0.1.

7. Precomputation, Lazy Calculation, and Invalidation

The proposed maintenance script was scripts/maintenance/recompute-player-relations.ts. It would accept an athlete scope, calculate relationships in batches, and overwrite cache rows. A static initial list would cover featured athletes, athletes frequently appearing on the home page, and those with many imported major-ekiden appearances; automated popularity ranking was outside V0.1.

On a non-core profile read, a valid cache would return immediately. A missing cache would calculate synchronously in that request, write the row, and return it. There was no asynchronous queue in the first version because no queue infrastructure existed and anticipated traffic was manageable. This choice carries a first-visit latency risk.

When an athlete's Membership or RaceResult changed, their cache should be recomputed. After a major event import, the maintainer should run the batch recomputation script for important athletes. V0.1 did not specify fine-grained cascading invalidation: changing athlete A would not necessarily recompute everyone who had raced A immediately. The design explicitly accepted short-term eventual consistency.

8. Service and Page Boundaries

The plan separated calculation from page rendering:

src/lib/player-relations/types.ts
src/lib/player-relations/build-direct-matchups.ts
src/lib/player-relations/build-teammate-relations.ts
src/lib/player-relations/build-frequent-stage-relations.ts
src/lib/player-relations/build-player-relations.ts
src/lib/player-relations/get-player-relations.ts

build-* functions would calculate each source; build-player-relations would aggregate; get-player-relations would read cache, calculate on a miss, and write back. The athlete page would remove its temporary relatedPlayers assembly, call getPlayerRelations(personId), load basic details for returned people, and render names and reason labels. It would not own relationship calculation.

To limit query cost, calculations would start from one personId, form a candidate set from shared races, organizations, and important stage-level events, then aggregate. After reading the cache, the page would batch-fetch only the six to twelve displayed athletes' slug, displayNameJa, and other minimal display fields. Full person records should not be duplicated in the cache.

9. Localization and Delivery Order

The first cache could hold a reason type plus a stable short label written in the then-current Japanese/Chinese development context. The intended later form was structured reason type and parameters, rendered by locale. This was an expedient V0.1 compromise, contingent on keeping the data structure replaceable.

The proposed implementation sequence was: add and migrate PlayerRelationCache; create the service layer; build direct-matchup, teammate, and repeated-stage calculators; add the aggregator and getPlayerRelations; connect the athlete page; create the precomputation script; then manually prewarm and validate a batch of core athletes. Priority was direct matchups first, teammates second, repeated same-stage encounters third. A time-constrained release could start with the first two and add the third later.

Allowed V0.1 simplifications were an imprecise or empty sourceHash, plain same-stage rules, a manually curated core-athlete list, synchronous lazy calculation, and invalidation through batch recomputation or later page access. The plan explicitly avoided per-request full relationship assembly, Redis/queue/worker infrastructure, a whole-site prebuilt graph, a new primary relationship table, visible algorithm scores, and proactive win/loss labels.

10. Risks and Checks

Incomplete facts could yield weak relationships. The mitigation was to establish direct matchups first, use teammates to improve coverage beyond famous athletes, and accept that some ordinary profiles would have few results. A first cache miss could slow a page; prewarming core athletes, calculating only one person's local neighborhood, and limiting visible results would help. Imports could leave caches stale; the proposed response was a manual recomputation after major imports and explicit acceptance of short-term eventual consistency.

The design's central boundary was to add one lightweight PostgreSQL cache for derived results while keeping primary facts unchanged, move calculation out of the page, and use precomputation plus lazy calculation. This document specified the intended V0.1 implementation; later features should build on it rather than return to page-local relationship assembly.