V0.2 Data Model and Feature Migration Plan

Source date: September 24, 2026. Related documents: V0.2 PRD, V0.1 technical plan, and data model map. The plan describes the intended migration and its acceptance target; it does not claim that every phase was completed at publication.

1. Why Change the Model?

V0.1 established an athlete-centered information site. Adding Hakone, Izumo, and All-Japan University Ekiden data exposed three limitations: Person pages assumed everyone was an athlete; the ekiden model had individual leg results but no team's final edition result or cumulative state after each leg; and Competition.type was still free text, making category-specific pages and queries fragile.

V0.2 aimed to improve the fact layer, not launch complex analytics. It would broaden the site from athletes to people, formally support team results, and reflect those facts in organization, person, and edition pages. Honors, charts, complex aggregates, and analysis were deferred.

2. Scope

Included in the proposed release:

• Restrict Person.type to athlete, coach, and staff.
• Add Membership.role, using the same values but describing the role in one affiliation.
• Change Competition.type from string to an enum.
• Add TeamCompetitionResult and TeamCompetitionLegSnapshot.
• Show athletes, coaches, and staff on organization pages; show imported ekiden results there.
• Show final team results and cumulative leg tables on edition pages.
• Allow person pages to work when the subject is not an athlete.

Excluded were an honors model, an organization-event-summary entity, analytical rank charts or line graphs, a separate manager value, fine-grained Japanese job-role enums such as supervisor or student manager, and a complex title hierarchy.

3. Modeling Principles

Person is a common entity for athletes, coaches, and staff, rather than an athlete-only table. A person's role in an organization at a particular time belongs on Membership.role. Person.type remains a derived primary category for site navigation, not a field the maintainer must manually keep synchronized.

Ekiden facts have distinct levels. CompetitionEdition is the whole edition; Race is one leg; RaceResult holds an individual's leg result; TeamCompetitionResult holds a team's final edition result; and TeamCompetitionLegSnapshot holds its cumulative position after a leg. V0.2 adds these facts before honors, summaries, or analytics can be derived from them.

4. Enum and Existing-Field Changes

The proposed enums were:

enum PersonType {
  athlete
  coach
  staff
}

enum MembershipRole {
  athlete
  coach
  staff
}

enum CompetitionType {
  university_ekiden
  high_school_ekiden
  corporate_ekiden
  mixed_ekiden
  track_meet
  road_race
  marathon
}

Existing manager people would map to staff. Membership.role and Person.type use the same values, but mean different things: the former is a dated affiliation fact, the latter a derived primary site category. Competition.type becomes CompetitionType?, nullable during historical backfill. Race.discipline.ekiden_leg stays in place for the race-unit level; the two enums describe different levels.

The proposed Person structure retained id, unique slug, Japanese/kana/romanized/Chinese/English names, birth date, hometown, nationality, height, weight, status, notes, and timestamps. Only type changed to PersonType @default(athlete). No redundant “current role” field was added; a unified calculation would backfill type from memberships.

Membership retained person and organization IDs, relationship type, start/end dates and years, faculty, department, status, notes, source, and timestamps. It added role MembershipRole @default(athlete). type still answers whether the relationship is origin, enrolled, affiliated, graduated, or staff; role answers whether the person was an athlete, coach, or staff member during it. Valid examples include type = enrolled, role = athlete, type = affiliated, role = coach, and type = staff, role = staff.

The proposed Competition retained identity, multilingual names, organizer, region, website, notes, and timestamps, changing only type to CompetitionType?. Null was allowed to migrate and backfill older records incrementally; making it required could be reconsidered after that work.

5. New Team Result Tables

TeamCompetitionResult

One row represents one organization's final result in one competition edition. The proposed fields were:

Field	Purpose
id	cuid() primary key
competitionEditionId	Parent edition, cascading deletion
organizationId	Team/organization, cascading deletion
finalRank	Final placing, nullable
finalMark, finalMarkMillis	Final cumulative time in display and numeric forms, nullable
status	DataStatus, default pending
notes, sourceId	Context and optional provenance
createdAt, updatedAt	Timestamps
legSnapshots	Related cumulative leg states

The table would enforce @@unique([competitionEditionId, organizationId]), with indexes on [competitionEditionId, finalRank] and [organizationId, competitionEditionId]. It owns final team outcome, not leg runners or individual leg marks.

TeamCompetitionLegSnapshot

One row represents a team's cumulative state after leg N of an edition. It links to TeamCompetitionResult and an optional Source, and carries leg, cumulativeRank, display and millisecond cumulative mark, display and millisecond gap from the leader, status, notes, and timestamps. It would enforce @@unique([teamCompetitionResultId, leg]) and index [leg, cumulativeRank].

It does not copy individual runners or their leg marks. A page asking who ran that leg should join through RaceResult instead.

RaceResult.teamRank would remain temporarily for historical compatibility, but would no longer be the primary team-result fact. New features should prefer TeamCompetitionResult.finalRank and TeamCompetitionLegSnapshot.cumulativeRank.

6. Migration and Backfill

The migration was designed for gradual transition, rather than preserving old semantics indefinitely.

Person Types and Membership Roles

Existing athlete, coach, and staff values stay as they are; manager becomes staff. Because most existing memberships were for athletes, the initial conservative backfill could set Membership.role = athlete on existing rows. New coach and staff imports should then record their real role from source evidence.

The proposed Person.type recalculation chooses the role from a currently valid membership first; otherwise, the most recently ended membership; otherwise, it retains the previous value or falls back to athlete if empty. A current membership has a missing or past start date and a missing or future end date. If several current memberships exist, the derived primary-type priority is coach, then staff, then athlete. This priority is only for the site's primary category; it does not change the historical membership facts.

Competition Types and Team Results

The known Hakone, Izumo, and All-Japan University Ekiden series would first be backfilled as university_ekiden, with other competitions classified later. V0.2 did not require complete historical team data for every edition before release. The proposed sequence was to add final results for already well-documented Hakone editions, then cumulative leg snapshots edition by edition; later Izumo and All-Japan imports should enter through the new model directly.

A transitional database could legitimately have complete individual RaceResult rows, partial TeamCompetitionResult rows, and no leg snapshots yet. Pages should reveal team sections only where supporting data exists.

7. Page Changes

Organization Page

The organization page would become an organization profile rather than only a member list. Current and former people would be grouped by Membership.role into Athletes, Coaches, and Staff, with person name, affiliation interval, and person-page link. An Ekiden Results section would show imported results for competitions whose Competition.type is an ekiden value, including competition, edition, final rank, total time, and edition link. It would read TeamCompetitionResult and avoid historical summaries, title counts, or trend charts in V0.2.

Person Page

An athlete page would retain and improve affiliation history, personal bests, race results, and related people. A coach or staff page could be smaller: primary details, affiliation history, related organizations, and any available competition traces. V0.2 did not include coaching-performance analysis or a large coach-specific feature.

The existing /players route would remain during this version to avoid a disruptive URL and SEO migration. Copy could gradually move from “athlete” to “person/athlete” where appropriate. A later /people route would require a separate SEO and routing plan.

Ekiden Edition Page

For an ekiden edition, a final team table would read TeamCompetitionResult and show school, final rank, and total time. A cumulative-leg table would read TeamCompetitionLegSnapshot and show leg, cumulative rank, cumulative time, and gap to leader. Graphs, line charts, and multi-team analytical comparisons were deferred.

8. Query Layer

Organization queries should separate current from former memberships, group them by role, and fetch imported ekiden teamCompetitionResults. The former “current players/former players” query shape was too narrow.

Person-page queries must handle Person.type, Membership.role, and a non-athlete with no personal bests. PB and race-result sections can appear conditionally based on type and available data.

An ekiden edition page would load its CompetitionEdition, related TeamCompetitionResult rows, each result's legSnapshots, and only join through Race -> RaceResult when individual leg performances are needed.

9. Proposed Implementation Order

Phase	Work
1. Schema	Narrow PersonType; add MembershipRole and CompetitionType; update fields; add both team-result models
2. Migration and scripts	Backfill membership roles, recalculate person types, classify known competitions, and import an initial set of Hakone team results
3. Queries	Change organization and person queries; add ekiden edition queries
4. Pages	Update organization, person, and edition/detail pages

10. Risks and Completion Criteria

Historical records could be incomplete for a period: individual leg results might be present before team-level results. The page should render only supported sections. Person.type must be recalculated centrally or it would become a manual synchronization burden. RaceResult.teamRank needed a compatibility period but should stop carrying the team-result responsibility.

After V0.2, the intended acceptance target was support for athlete, coach, and staff profiles; role-grouped organization people; final and cumulative ekiden team results; and person pages no longer restricted to athlete semantics. A stronger fact layer would then support later charts, summaries, honors, and analysis. This paragraph states the planned target, not verified delivery metrics.