Turfi Platform Documentation

Turfi's sports model only works if competitions, stages, games, and standings are represented consistently. This document explains the operational structure that turns real-world soccer formats into usable platform records. It fits into the architecture as the backbone for results, standings, event context, and match-linked media. As Turfi supports more competition types, this file should remain the authoritative structural explanation of how competition entities and games are modeled on the platform.

Strategic framing: Turfi reconstructs competition context; it does not govern real-world competitions. Read [Platform Philosophy and Scope](./platform-philosophy-and-scope.md) before interpreting words like “official” or “authority” as external sporting governance. For official vs observed truth and the locking model (intent for standings vs evolving events), see Official vs Observed Truth and Locking Model in that document.

Shared status legend: [docs/_shared/status-legend.md](./_shared/status-legend.md)

---

Strategic positioning (read first)

• Phases, groups, and rounds are optional organizational layers for scheduling, grouping, standings scope, and UI. They exist for structure and rendering, not for federation rule enforcement.
• The platform must not require a complete tournament hierarchy before a game can exist. Nullable links and progressive enrichment are intentional.
• Database constraints below enforce internal model consistency (valid references, venue/turf consistency, team participation in a competition). They are not registration eligibility, sanctioning, or compliance gates for external bodies.
• Standings are derived outputs from games and official-state rules inside Turfi, not a claim that Turfi is the league’s external system of record.
• Consensus lens: Competitions, schedules, and bracket-style views are reconstructed from games, events, and validated data on the platform—not “governed” by Turfi as an external authority. Standings, schedules, and brackets depend on what has been ingested, validated, and promoted to canonical layers (see Platform Philosophy and Scope: Truth Layers, Consensus Engine Model).

Competition optionality

Validated platform behavior:

• Games may exist without a competition. competition_id is optional on games so matches can be captured as structural records before (or without) competition context.
• Competition is a context container, not a prerequisite for every game row. It groups schedules, membership, and derived outputs when present; it does not gate creating a minimal game record.
• Standings rows exist only in competition scope. There is no meaningful standings table row for a game with no competition_id; standings are always tied to a competition (and optional group) when the engine computes them.

---

Competition Engine

Status: IMPLEMENTED

The Competition Engine is the part of Turfi that models how sport is structured on the platform: tournaments, league structures, stages, matchdays, and the matches that feed standings, events, highlights, and media. It does not assert authority over how federations or leagues must run outside Turfi.

This engine exists because sports data is not just a list of games. A competition evolves through stages, teams may be divided into groups, rounds organize when games happen, and games generate the event stream that powers statistics, highlights, and storytelling. The Competition Engine provides the structure that keeps all of those layers consistent.

It also acts as the bridge between the structural side of the platform and the intelligence side of the platform. Match Intelligence depends on games and events. Media depends on games and moments. Discovery and recruitment depend on competition context. The Competition Engine is the backbone that tells the rest of Turfi where a game belongs, what stage it is part of, and why that result matters.

---

Competition Hierarchy

Turfi models real sports competition using a layered progression:

Organization → League (optional) → Competition → Groups → Phases → Rounds → Games → Standings

Each layer has a clear purpose:

• Organization is always the owning organizational context for a competition record on the platform.
• Competition remains the operational root of the sports engine for Turfi’s data model.
• League provides optional league association when a competition belongs to a league structure.
• Phase allows a competition to evolve through stages such as regular season, playoffs, or finals.
• Groups divide teams into pools when a competition needs segmented standings or scheduling.
• Rounds represent matchdays or tournament stages within a phase.
• Games are the individual matches played between teams.
• Standings hold derived ranking state produced from games when results are treated as official for standings rebuild rules inside Turfi.

This hierarchy allows Turfi to represent leagues, tournaments, showcases, scouting events, and friendlies without changing the underlying competition model.

League-Linked Competition Model

Every competition belongs to an organization through organization_id uuid not null references organizations(id). Competitions may optionally reference a league through league_id uuid nullable references leagues(id). Competition type and format are now normalized through dedicated lookup tables rather than application-managed text values.

Core rules:

• every competition must have organization_id
• competitions may have league_id
• competitions.competition_type_id resolves through competition_types.id
• competitions.competition_format_id resolves through competition_formats.id
• if competition_types.key = league, then league_id must exist
• if competition_types.key is tournament, showcase, or friendly_series, then league_id may be NULL

Examples:

• League competition:
• Name: League1 Quebec Senior Men
• Organization: Soccer Quebec
• League: League1 Quebec
• Season: 2026 Summer
• Type: League
• Format: Round Robin
• Standalone competition:
• Name: Quebec U17 Showcase
• Organization: Soccer Quebec
• League: NULL
• Season: 2026 Summer
• Type: Showcase
• Format: Group + Knockout

Category Model

Turfi is now category-aware across the competition stack so grassroots, academy, amateur, and pro structures can all use the same normalized model.

• Leagues may be mixed or gender-specific through leagues.gender_id
• Competitions are age-group and gender aware through competitions.age_group_id and competitions.gender_id
• Teams are age-group and gender specific through teams.age_group_id and teams.gender_id
• Players are gender specific through players.gender_id

Lookup rules:

• age_group_id resolves through age_groups.id
• gender_id resolves through genders.id
• competition_type_id resolves through competition_types.id
• competition_format_id resolves through competition_formats.id
• UI labels for those lookups are rendered through the centralized multilingual label layer

Transition note:

• teams.age_group and teams.gender remain temporarily for backward compatibility
• teams.age_group_id and teams.gender_id are now the authoritative fields for team category within the platform model (replacing legacy text fields)
• a database sync trigger keeps the legacy text fields aligned with the lookup ids during the transition

Competition Phases

Competition phases exist so a single competition can evolve over time without losing structure. Many real sports competitions do not stay in one flat format from start to finish. A league may begin with a regular season, transition to playoffs, and finish with knockout rounds.

Turfi uses the competition_phases table to represent those stages in a way that can be understood by schedules, groups, rounds, and eventually standings and media context. Phases are optional, but they become essential once a competition uses multiple stages or bracket-style progression.

Typical examples include:

• Regular Season
• Playoffs
• Quarter Finals
• Semi Finals
• Final

Key fields in competition_phases:

Phases organize both groups and rounds. They give the platform a way to say not just "this game belongs to competition X," but also "this game belongs to the playoff stage of competition X."

Competition Groups

Competition groups exist so a competition can divide teams into pools while still preserving one parent competition. This is common in tournaments, youth events, and multi-pool league structures where teams do not all play inside one unified table.

The competition_groups table represents these subdivisions. In practice, this is where Turfi can express structures such as:

• Group A
• Group B

Groups belong to both:

• competition_id
• phase_id

That relationship matters because groups often make sense only inside a particular phase. A regular season may have Group A and Group B, while a playoff phase may have no groups at all.

Groups also influence standings calculations. Standings in Turfi are not just competition-wide by default; they can be scoped to the relevant competition group so the table reflects the actual structure teams are playing inside.

Competition Rounds

Competition rounds exist to represent matchdays or tournament stages within the competition structure. They answer questions such as "Which games are Matchday 3?" or "Which games belong to the Semi Final round?"

The competition_rounds table gives Turfi a formal way to organize scheduled games into chronological or structural batches. Rounds may belong to phases, which means a competition can have one set of rounds for the regular season and another for the knockout stage.

Typical examples include:

• Matchday 1
• Matchday 2
• Matchday 3

In the hardened architecture, rounds are not something the application should try to manually compute. They are generated from scheduled games, and the database is responsible for assigning games to the correct round structure.

Games

Games are the core match records inside the platform. A game represents the actual sporting contest between two teams at a scheduled place and time, with a status, scores, and structural links back to the competition model.

The games table is where Turfi connects competition context, venue context, and actual sporting outcome. It is the primary match record that anchors timelines, media, and derived aggregates. Scores on the game (home_score, away_score) are the declared result fields used for scheduling and for standings when the game is standings-eligible; a computed score from approved events may differ—see Consensus Scoring in [Media and Match Intelligence](./media-match-intelligence.md). Detailed attribution (goals, cards, etc.) lives in events and derived stats layers.

Game creation is designed to stay lightweight: required links satisfy integrity; optional fields (group, round, etc.) may be filled later so workflows are not blocked by missing non-critical structure.

Important fields in games:

The lifecycle of a game is intentionally staged:

• scheduled
• in_progress
• finished
• official

The difference between finished and official is important:

• Finished means the game has ended on the field.
• Official means the result has been validated and is allowed to influence standings.

Validated standings rule: a game affects standings only when is_final is true and is_official is true. Operational status values (for example finished) are not a substitute for that pair in the standings engine.

This distinction lets the platform separate operational match progress from results that are allowed to drive standings and other derived outputs—within Turfi, not as external federation certification.

Admin competition workspace

Turfi now exposes competition administration through the domain workspace route family /admin/games_competitions/*.

Current operator routes:

• hub: /admin/games_competitions
• competitions list: /admin/games_competitions/competitions
• competition detail: /admin/games_competitions/competitions/{competitionId}
• games list: /admin/games_competitions/games
• game detail: /admin/games_competitions/games/{gameId}

This split matters conceptually:

• Admin -> Data -> Registries -> Competitions remains the structural registry surface for editing competition rows as data.
• Admin -> Games & Competitions is the domain workspace for competition context, game navigation, leaderboard review, and match-level operational flows.

Compatibility note: the older /admin/competitions/ routes still exist during migration, but the admin navigation now treats /admin/games_competitions/ as the canonical path family.

Competition detail read model

The competition detail page is now a composite operator view rather than a single table.

It combines:

• leaderboard context from api_competition_leaderboard
• a competition-scoped games grid
• a competition-scoped player stats grid backed by player_competition_stats

The games section now shows a visible count in the header (Games (X)) after the grid loads so operators can quickly understand scope before paging or filtering.

The player stats grid deliberately uses strict database columns only:

• player_id
• team_id
• games_played
• goals
• assists
• minutes_played
• yellow_cards
• red_cards
• optional display columns player_name and team_name

Display behavior stays tolerant even when identity is incomplete:

• Player display prefers player_name, then player_id, then —
• Team display prefers team_name, then team_id, then —

This preserves a strict schema contract for the surface while still allowing historically detached or partially resolved rows to remain visible in admin review.

Automatic Round Assignment

Turfi uses the trigger assign_game_round() to assign rounds automatically from the game date and scheduling context.

This exists so the platform has one authoritative source of round assignment. The application does not decide what round a game belongs to, and administrators are not expected to hand-manage round numbering in the UI. Instead, the database applies the rule consistently whenever games are scheduled or updated.

That keeps rounds dependable across imports, admin edits, and future automation.

Standings Rebuild Engine

Turfi uses the trigger trg_games_rebuild_standings to keep standings aligned with official game results.

The key architectural rule is that standings are never incrementally edited by the application. Turfi does not try to adjust one row at a time in the frontend or "patch" standings after a result change. Instead, when a game is standings-eligible—validated behavior: is_final and is_official are both true—the standings system performs a full rebuild for the affected competition scope.

This design exists to guarantee standings integrity. A full rebuild avoids drift, hidden edge cases, and partial-update mistakes, especially when groups, official-state changes, or score corrections are involved.

Structural integrity and database constraints

The hardened database enforces internal consistency rules that matter for correct linking of games, teams, venues, and standings. These checks exist because invalid structural combinations (wrong competition membership, impossible venue/turf pairing, etc.) corrupt derived outputs.

They should be read as data-model integrity, not as strict governance workflows or mandatory completeness for every real-world competition format.

Examples of protected rules include:

• When competition_id is set, both teams must be registered for that competition through the active registration path (see Known schema inconsistency: competition_teams vs team_competitions). Games without a competition skip membership checks tied to a competition row.
• A team assigned through the registration join table must match the competition's age group and gender categories where those triggers apply.
• A turf must belong to the venue where the game is played.
• Official games must have scores.
• Duplicate roster entries are prevented.

These constraints protect derived outputs from inconsistent links. The application layer should guide users into valid combinations; the database remains the last line of defense for referential and structural correctness, not for off-platform eligibility policy.

Competition-team category consistency

Turfi enforces category alignment at the database level when teams are attached to competitions through the registration join (historically documented as team_competitions; live schema uses competition_teams for engine-backed membership—see Known schema inconsistency), so age/gender dimensions stay consistent within the modeled competition.

The enforcement trigger prevents assignments where:

• a team's gender_id does not match the competition's gender_id
• a team's age_group_id does not match the competition's age_group_id

This is platform model consistency (avoiding accidental mixed-category rows in one competition record), not a substitute for external eligibility or federation rules.

Historical Data Preservation

Competition data is treated as permanent sporting history. Once a match is played and recorded, the platform must preserve the readable history even if linked identities change later.

That is especially important for events, moments, and media. Turfi follows a strict preservation rule: match history and media moments must never be deleted as part of identity cleanup.

If a player is deleted:

• player_id may become NULL
• snapshot fields preserve the historical identity

That means the game timeline, event stream, and highlights remain understandable even if the live player profile is no longer present. The sporting record survives beyond the lifecycle of the identity record.

Performance Optimization

The Competition Engine is designed to scale through targeted indexing on the queries the platform depends on most.

The most important optimized access patterns include:

• games by competition
• games by round
• games by group
• games by scheduled date
• standings by competition
• standings by group

These indexes exist so competition pages, schedules, standings tables, and public match views remain responsive even as the number of games and historical results grows over time.

Match Participation Model

The game_participations table represents the official player lineup and participation records for a match.

This table links players, teams, and roster entries to a specific game and captures the lineup and substitution timeline for the match.

The model allows Turfi to represent:

• Starting lineup
• Substitutions
• Minutes played
• Match specific player positions
• Roster validation for match events

This structure ensures that match events and statistics are always tied to players who actually participated in the match.

Table: game_participations

Fields

• id

Primary identifier for the participation record.

• game_id

Reference to the match in the games table.

• player_id

Reference to the player participating in the match.

• team_id

Reference to the team the player represents.

• roster_entry_id

Reference to the roster snapshot for the player during the season.

• position

Position played during the match. This may differ from the player's normal roster position.

• starter

Boolean indicating whether the player was in the starting lineup.

• minute_in

Minute the player entered the match.

• minute_out

Minute the player left the match.

• created_at

Timestamp of record creation.

Relationship Model

games ↓ game_participations ↓ players

Match events reference players who must exist in the participation list. This ensures the system always knows which players were on the field at any point during a match.

Developer Note

A player must appear only once per game in the game_participations table. The combination of game_id and player_id should remain unique to prevent duplicate participation records.

Event Identity Model

Player identities in match events are represented by player_id, player_name_text, and (for assists) assist_player_id. The platform uses a simple rendering rule: if player_id exists, display the player profile name; if player_id is NULL but player_name_text exists, display the snapshot name; if neither exists, display "Unidentified player." Snapshot identities preserve historical accuracy after a player profile is removed—without them, match timelines would show gaps or broken references.

The Video Intelligence rollout extends game_events with review and source metadata so canonical match truth can still be grounded in approved events even when those events originate from video analysis. Relevant metadata includes:

• event_source_type
• event_confidence
• review_status
• verified_by
• verified_at
• primary_video_id

Approved events can then trigger downstream media-intelligence automation such as moment generation and clip extraction without bypassing the Competition Engine's canonical event model.

Competition Engine Tables

The Competition Engine tables define how Turfi turns a competition structure into playable matches, standings, and downstream media context. These objects are not just storage records. Together they describe where a game belongs, what stage of the competition it represents, and when a result is allowed to affect standings.

At a high level, the table model follows this progression:

Team → team_competitions → Competition → Phase → Group → Round → Game

This structure gives the platform enough flexibility to support straightforward leagues, grouped tournaments, and multi-stage knockout systems without rewriting the competition model each time a format changes.

team_competitions

Purpose Represents team registration inside a competition.

Why it exists Competition membership is not the same as team identity. A team can exist in the platform before it is admitted to a specific competition, and a competition needs a clean place to store group placement and seed values for scheduling and validation.

How it interacts with the platform Schedule generation reads registered teams from team_competitions. Game creation validation also uses this table so a match cannot be created unless both teams are registered in the same competition.

Operational rules

• Teams may participate in multiple competitions.
• Competitions may include teams from multiple organizations.
• group_name supports explicit pool placement before scheduling.
• seed gives scheduling algorithms a stable input for balanced group and fixture generation.

Scheduler flow

Competition → registered teams from team_competitions → optional group creation → schedule generation → games creation

Important fields

competition_phases

Purpose Represents optional stages inside a competition, such as regular season, playoffs, quarter finals, semi finals, or final.

Why it exists Not every competition stays in one flat stage. Phases allow Turfi to model how a competition evolves over time while keeping later groups, rounds, and games tied to the correct stage.

How it interacts with the platform Phases organize groups and rounds and provide context for scheduling, standings scope, and future bracket-style competition views.

Important fields

competition_groups

Purpose Represents pools or subdivisions inside a competition phase.

Why it exists Many competitions divide teams into separate pools such as Group A and Group B. Groups allow the platform to keep those subdivisions explicit instead of flattening them into one schedule.

How it interacts with the platform Groups belong to both competition_id and phase_id, and they influence how standings are calculated and displayed for teams inside that pool.

Important fields

competition_rounds

Purpose Represents matchdays or stage-specific round buckets inside a competition.

Why it exists Rounds are how Turfi groups scheduled matches into meaningful chronological or tournament units such as Matchday 1 or Semi Final Round.

How it interacts with the platform Rounds may belong to phases and are linked to games. They help schedule views, reporting, and competition presentation stay organized without asking the application to manually assign round numbering.

Important fields

games

Purpose Stores the individual matches played between two teams.

Why it exists Games are the core sporting records that drive standings, events, player statistics, moments, and public match views.

How it interacts with the platform Games sit at the point where competition structure, participation, venue context, and outcome all meet. They feed standings, event timelines, moment generation, and media workflows.

Important fields

Game lifecycle

Validated behavior from database testing and admin workflows:

• Games can be created with minimal data (for example teams plus timestamps); venue, turf, scores, and competition context can be filled in later.
• competition_id is optional at creation and may be set or changed afterward.
• Scores may be null at creation; they can be entered or corrected as the match progresses.
• A game can progress through result states in line with operational use: incomplete → final (is_final) → official (is_official). Edits after creation are supported for scores, flags, and competition linkage; the database (not the UI) owns standings side effects when official rules apply.

Standings impact rule (validated): a game affects standings only when is_final is true and is_official is true. Until both are true, the match does not drive derived standings for its competition scope.

The separate status field (for example scheduled, in_progress) remains the operational lifecycle; is_final / is_official are the pair used for validated, standings-eligible results.

standings

Purpose Stores the competition standings derived from official game results.

Why it exists Standings are one of the main outputs of competition play, but they are not treated as hand-maintained source data. They are derived records generated from official results.

How it interacts with the platform Standings are rebuilt when official game results change, and they are scoped by competition and group so the table reflects the real structure of play.

Standings rebuild behavior

Validated trigger behavior:

• Standings are fully rebuilt, not incrementally patched row-by-row in application code.
• A rebuild runs on games changes that matter to derived rankings, including:
• INSERT when a row is in scope for standings (results treated as official and final per engine rules),
• UPDATE of scores, is_final, is_official, or competition_id (and related scope fields as implemented in the rebuild function),
• DELETE of a game that previously contributed to standings.
• Changing competition_id moves the game’s contribution from the old competition’s rebuild scope to the new one; if the game no longer has a competition, it does not contribute to standings and does not “pollute” another competition’s table.

Canonical rule: standings rows are always derived from canonical game results and flags in the database, not edited as a source of truth.

api_games

Purpose Canonical game read model for match pages.

Why it exists The platform needs one stable read contract for public and application match views without requiring every surface to manually join the underlying competition and game tables.

How it interacts with the platform api_games is the read model consumed by match pages and other match-facing UI surfaces. It exposes game identity, structural context, score state, and media references in one place.

Primary Relationships Includes game identity, competition context, team references, and score/media fields.

Important Constraints Queried by id; pages assume one canonical game row for a game id.

Trigger: assign_game_round()

Purpose Automatically assigns a game to the correct round based on scheduling context.

Why it exists Round placement needs one deterministic, database-owned assignment path so admin edits and imports do not diverge. This is about internal consistency of round_id, not a claim that one external feed owns all sport truth. If the application manually assigned rounds ad hoc, different flows could drift; the trigger centralizes that rule.

How it interacts with the platform The application may display round_number, but it does not own round assignment logic. The trigger determines round placement so scheduled games remain structurally consistent.

Trigger: trg_games_rebuild_standings

Purpose Rebuilds standings whenever official game results change.

Why it exists Standings integrity is too important for incremental frontend updates or patch-style recalculation. A full rebuild ensures the official table always matches the official results.

How it interacts with the platform When a game is standings-eligible (validated: is_final and is_official both true), the trigger recalculates the standings for the affected competition scope. The application should treat standings as derived outputs, not editable source records.

Competition Integrity Model

Turfi’s database enforces several rules to protect the competition graph from inconsistent admin input.

Examples include:

• teams must belong to the competition they play in
• turfs must belong to the selected venue
• official games must include scores
• duplicate roster entries are prevented

These protections ensure the game model remains trustworthy enough to power standings, match intelligence, and historical reporting.

---

Competition Model Update

Competitions remain the operational root of the sports engine.

Hierarchy:

Organization -> League optional -> Competition -> Groups -> Phases -> Rounds -> Games -> Standings

New league relationship:

• competitions.organization_id uuid not null references organizations(id)
• competitions.league_id uuid nullable references leagues(id)

Rule summary:

• every competition must belong to an organization
• league is optional unless competition_types.key = league
• standalone events such as showcases, invitationals, tournaments, and friendly series may keep league_id null

Competition Data Model

Competitions now include structured age_group and gender attributes through normalized lookup references:

• competitions.age_group_id -> age_groups.id
• competitions.gender_id -> genders.id
• competitions.competition_type_id -> competition_types.id
• competitions.competition_format_id -> competition_formats.id

These values are intentionally stored as lookup references rather than flattened strings such as MU17 or FU17.

Why this matters:

• grassroots soccer often classifies competitions as MU17, FU17, or similar shorthand
• the platform still needs normalized data for filtering, translation, and future reporting
• the UI can render shorthand federation-style labels while the database keeps structured values

Structured model:

• age_group = u17
• gender = male

Possible UI rendering:

• MU17
• FU17

This keeps the database normalized while preserving the familiar presentation used by youth and amateur competitions.

Known schema inconsistency

Competition registration tables

Database review shows two tables that can represent team participation in a competition:

• competition_teams — active in the live schema: used by engine logic and foreign keys for competition membership.
• team_competitions — legacy or parallel usage (older naming and/or admin/import paths).

The engine relies on competition_teams for structural integrity where enforced. team_competitions should be reviewed for consolidation, deprecation, or a single migration path so application code and documentation do not drift.

Development guidelines: Game engine rules

• Do not implement incremental standings logic in the application layer; use the database’s full rebuild path.
• Always rely on full rebuild functions / triggers tied to games when changing results or scope.
• Do not require competition_id on game creation in product or API design; allow nullable competition consistent with the data model.
• Do not block creating incomplete games (missing scores, not final, not official).
• Always respect is_final and is_official when interpreting whether a result is standings-eligible (is_final and is_official).

Consensus scoring (non-destructive):

• Never auto-update stored scores on games from events without an explicit user or workflow action.
• Always preserve original game row data as the declared result unless a deliberate edit or import replaces it.
• Computed scores from events and consensus status are advisory for visibility and review—not authoritative over declared scores (for now).

Official vs observed truth and lock (product intent):

• See [Platform Philosophy and Scope](./platform-philosophy-and-scope.md): Official vs Observed Truth and Locking Model.
• After lock, official declared results must not be rewritten automatically; observed data may still evolve; consensus mismatches may remain visible without auto-reconciliation.