Turfi Platform Documentation

Players are central to Turfi, but athlete identity requires different rules than generic registry entities. This document explains the player-specific identity, ownership, claims, and merge workflows that keep athlete records reliable over time. It fits into the architecture as the connection point between player profiles, competition history, media context, and recruiting visibility. As duplicate, claim, and merge tooling evolves, this file should remain the long-term guide to player identity behavior.

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

---

Player Engine

Status: IMPLEMENTED

The Player Engine is the home of every athlete on Turfi. It stores who they are, what teams they play for, how they perform, and who can control their profile. When a scout looks up a player, a coach builds a roster, or an import adds hundreds of names from a CSV, the Player Engine is what makes that data consistent and usable.

Sports platforms run on player data. Without one canonical identity record per athlete on Turfi, the platform would struggle with duplicates, conflicting records, and broken links between games, media, and recruitment. The Player Engine exists to give every athlete one canonical profile row that the rest of the platform can reliably reference. That identity concern is separate from match-level consensus: games and events may still be assembled from media, AI, official feeds, and community input as described in Platform Philosophy and Scope.

This engine owns player profile data (name, birth year, position, status), registration and roster links (which teams and clubs a player belongs to), identity resolution (matching text references in a CSV to the right player record), duplicate detection (flagging when the same person appears multiple times), profile claims (letting users request ownership of their player record after review), and player lifecycle (archive, safe delete, force delete for admins).

Player category model:

• players are gender specific through players.gender_id
• the value resolves through genders.id
• labels are displayed through the centralized multilingual lookup-label system

This keeps player identity aligned with the normalized category model used by leagues, competitions, and teams.

The Player Engine feeds almost everything else in Turfi. Competitions need players for lineups and events; Match Intelligence tracks their stats and moments; Media links highlights to players; Recruitment and Discovery surface players for scouts and coaches. The Import System uses Player Engine views to resolve names during bulk imports, and the Entity Resolution system fixes unresolved references after ingestion.

Future work includes fuzzy duplicate matching, federation ID linkage, richer identity scoring for claims, merge tools for consolidating duplicates, and pre-import duplicate warnings so admins catch problems before they're written to the database.

---

Purpose

The Player Engine manages athlete identity, player profiles, and player ownership across Turfi. It provides the canonical player data layer used by competitions, media, recruitment, and discovery. Subsystems include Player Identity Infrastructure, Player Duplicate Detection, Player Profile Claim Engine, and Claim Suggestion Engine.

Architecture Overview

Player data flows from base tables (players, player_registrations, teams, clubs) through API views (api_player_profiles, api_player_competition_analytics, api_player_matches, etc.) to UI surfaces. The Player Identity Infrastructure aggregates identity into player_identity_index and resolver views (api_player_resolver, api_player_resolver_search) for fast lookup in imports, claims, and search. Duplicate detection uses api_player_duplicate_groups and api_player_duplicates. Profile claims use player_profile_claims and user_player_links.

Key Database Models

• players — Base player records (first_name, last_name, birth_year, status, etc.)
• players.gender_id — Normalized player gender classification
• player_registrations — Season/team/club participation
• player_identity_index — Aggregated identity for resolution
• api_player_profiles — Primary profile read model
• api_player_competition_analytics, api_player_matches, api_player_trends, api_player_media, api_player_activity
• api_player_duplicate_groups, api_player_duplicates
• player_profile_claims, user_player_links
• api_player_claim_candidates, api_claimable_players, api_player_claim_search

Key Workflows

1. Player CRUD: Admin Data registries manage players; bulk operations use archive_player, delete_player_safe, force_delete_player RPCs.
2. Import resolution: Entity resolver and player identity views resolve names to IDs during CSV import.
3. Profile claims: User submits claim → reviewer approves → user_player_links created.
4. Duplicate detection: Identity rows grouped by full_name + birth_year; groups with count > 1 surfaced for review.

Interactions with Other Engines

• Competition Engine: Players participate in games via game_participations, game_events.
• Match Intelligence: Player stats, career timeline (player_career_entries), moments.
• Media Engine: Player-linked media, highlight participation.
• Recruitment Engine: Player discovery, scouting notes.
• Discovery Engine: Player search, profiles, activity feeds.
• Import System: Player adapter, entity resolution, reference mapping.

Player Lifecycle and Deletion Strategy

The player lifecycle model defines how athlete profiles move through the platform from active use to archival or removal. Turfi provides three distinct deletion modes to balance data governance with historical integrity.

A core architectural rule governs all deletion operations: match history must never be deleted when a player profile is removed. Goals, assists, participations, and statistics represent what actually happened in sport; they are permanent records. The system is designed so that removing a player profile never erases that history.

Archive Player — Archive changes the player status to archived while keeping the player profile in the database. The player becomes hidden from discovery and search results, but all historical references remain intact. Match events, statistics, and media associations stay linked to the player. Archive is the preferred first step when a player retires or when an admin needs to hide a record without losing context. Archived players can be restored to active if needed.

Safe Delete — Safe Delete removes the player record from the database while preserving all historical match data. Instead of deleting rows in match-related tables, the system detaches the player reference by setting player_id to NULL. Game events, participations, statistics, moment candidates, and media assets all keep their records; only the foreign key pointing to the player is cleared. Player-owned data such as visibility settings, follows, and scouting notes is deleted. Safe Delete ensures match timelines and historical statistics remain accurate and queryable even after the player profile is gone.

Force Delete — Force Delete is an administrative operation available only to system administrators (system_admin and super_admin roles). It uses the same detachment strategy as Safe Delete—match history is never deleted, only detached. The difference is that Force Delete bypasses additional safety checks and removes the player immediately without the same confirmation flow. It is reserved for urgent governance needs.

Architectural Rule — Match history tables must never cascade delete when a player profile is removed. The following tables preserve historical data: game_events, game_participations, player_game_stats, moment_candidates, highlight_moments, media_assets, player_competition_stats. Player deletion detaches references (SET player_id = NULL) instead of deleting records.

Historical Identity Preservation

When a player profile is removed from Turfi, match history must remain readable. A goal scored by Cristiano Ronaldo in 2020 should still display his name years later, even if his player record has been deleted. Historical identity preservation is the system that makes this possible.

Turfi uses an identity fallback model in match events. Each event stores both a reference to the player (player_id) and a snapshot of the player's name at the time the event was recorded (player_name_text). When the player profile exists, the system shows the live profile name. When the profile is gone, it falls back to the stored snapshot. This ensures match timelines and statistics remain human-readable regardless of whether the player record still exists.

Identity Fields — Match events use player_id (foreign key to players) and player_name_text (text snapshot at event creation). When both are present, the system prefers the profile; when the profile is removed, the snapshot provides the fallback.

Three Identity States — Linked Identity: player_id references an existing profile; system displays the current name (e.g., Goal by #10 Cristiano Ronaldo). Snapshot Identity: Profile removed but player_name_text stores the name; system displays the snapshot. Unidentified Identity: Both NULL; system displays "Unidentified player." Unidentified identities may later be claimed through the identity resolution system.

Snapshot Identity Trigger

The trigger trg_snapshot_player_name_on_event ensures that every match event permanently stores a historical identity. Whenever a new game_events row is inserted with a player_id and player_name_text is empty, the database automatically copies the player's name from the players table into player_name_text. This happens at insert time, regardless of how the event is created—admin UI, bulk import, API, or automation. The trigger protects match history by guaranteeing that a snapshot exists before any deletion can occur.

Player Identity Infrastructure

Status: IMPLEMENTED

Purpose

Player Identity Infrastructure provides a dedicated identity layer for fast player lookup and consistent player matching across platform workflows.

It is designed to support:

• fast player lookup
• duplicate detection
• profile claim suggestions
• recruiting search
• scouting search
• import matching

Architecture Overview

Turfi does not repeatedly query multiple base tables for identity matching in every feature path. Instead, base player and roster context is aggregated into a canonical identity index:

players + player_registrations + teams + clubs -> player_identity_index

Higher-level systems then query identity-focused resolver views:

• api_player_resolver
• api_player_resolver_search

These views are consumed by:

• Import Engine reference matching
• claim suggestion workflows
• duplicate detection workflows
• future scouting/recruiting resolution tools

Database Model

Base sources:

• players
• player_registrations
• teams
• clubs

Canonical identity layer:

• player_identity_index

Resolver read models:

• api_player_resolver
• api_player_resolver_search

Core index fields:

• player_id
• first_name
• last_name
• full_name
• birth_year
• team_id
• team_name
• club_id
• club_name

Workflow

1. Player, registration, team, or club data changes.
2. Identity index refresh process updates player_identity_index.
3. Resolver views expose normalized identity rows.
4. Import/claim/search features query resolver views instead of rebuilding joins.

Example Scenarios

• Import row includes Danny Malouin and 2008; resolver finds canonical player identity row quickly.
• Claim suggestion flow presents player candidates with team and club context.
• Scouting search surfaces consistent identity records across current season rosters.

Future Considerations

• richer fuzzy identity scoring
• phonetic/alias matching
• federation ID linkage
• confidence scoring surfaced in UI workflows

Player Duplicate Detection

Status: IMPLEMENTED (views and grouping logic); PLANNED (merge workflow, pre-import warnings)

Purpose

Player Duplicate Detection identifies likely duplicate player records created by repeated imports or parallel administrative data entry.

Architecture Overview

Duplicate detection is built on Player Identity Infrastructure and currently uses exact grouping on:

• full_name
• birth_year

This creates a deterministic first-pass duplicate signal without requiring fuzzy heuristics.

Database Model

Primary views:

• api_player_duplicate_groups
• api_player_duplicates

api_player_duplicate_groups identifies duplicate identity groups. api_player_duplicates lists concrete player rows inside each detected group.

Workflow

1. Identity index rows are grouped by full_name + birth_year.
2. Groups with count > 1 are emitted as duplicate groups.
3. Detailed member rows are exposed for review.
4. Admin/review tools can use this output for merge or conflict workflows.

Example Scenario

Josh Tremblay, birth year 2008, appears 3 times across imported datasets.

The duplicate group view flags one group; the duplicate rows view lists all three player records with their linked team/club context for review.

Future Considerations

• fuzzy duplicate matching
• confidence scoring for merge suggestions
• duplicate review queue and merge operations
• pre-import duplicate warnings during CSV preview

Moments Participation Engine

Concept Explanation

The Moments Participation Engine separates official match truth from open media associations.

In Turfi, match statistics remain the official source of truth for what happened in the game (for example, who scored). Moments and highlights, however, can be associated with multiple relevant participants so additional contributions can be represented in media workflows.

Example:

• Josh scores a goal and is the official scorer in stats.
• Mark contributes to the buildup and wants that same moment in his highlight context.
• The same video moment is therefore associated with both players through participation links.

Architecture Overview

The engine follows a layered event-to-highlight pipeline:

1. game_actions / game_events record official timeline events.
2. moment_candidates captures detected interesting events (goal, assist, key play).
3. video_moments attaches playable media clips to timeline context.
4. highlight_moments represents curated/confirmed moments for highlight workflows.
5. moment_participants associates one or many players to the same moment.
6. highlight_collections and highlight_items assemble personal/team reels from shared moments.

This architecture allows one moment to remain canonical while being reused in multiple player highlight feeds.

Database Model

Existing related tables:

• game_actions
• game_events
• moment_candidates
• video_moments
• highlight_moments
• highlight_items
• media_assets
• player_action_claims

Prepared table for participation links:

• moment_participants

Supporting participant/highlight feed views:

• api_moment_participant_moments
• api_player_highlight_moments
• api_player_highlight_feed

Proposed structure:

• id
• moment_id
• player_id
• role
• source
• created_by_user_id
• claim_id
• created_at

Role examples:

• scorer
• assist
• buildup
• defender
• goalkeeper
• participant

Source examples:

• stats
• system
• player_claim
• coach_tag
• ai_detection

Workflow Description

Moment creation flow:

1. A game action occurs.
2. The system produces a candidate moment.
3. A video clip is attached when media is available.
4. A curated highlight moment is confirmed.

Participation flow:

1. Official stat assignment is preserved in match/stat tables.
2. Additional players are associated through moment_participants.
3. Participation source and role are captured for moderation and auditability.

Highlight distribution flow:

1. Player highlight feeds are generated from moments where the player is a participant.
2. The same underlying moment may appear in multiple player reels.
3. Canonical event/media identity remains single-source to prevent duplication.

View responsibilities:

• api_moment_participant_moments exposes moments joined with participant-role context.
• api_player_highlight_moments builds player-scoped highlight moments including both primary and participant associations.
• api_player_highlight_feed provides feed-ready player highlight rows for UI playback/reel assembly.

Example Use Cases

• Goal moment: Josh = scorer, Mark = buildup.
• Defensive sequence: goalkeeper + defender both tagged on a save sequence.
• Scouting workflow: scout tags secondary participant without changing official match stats.

Important behavior:

• A moment is not strictly owned by one player for highlight purposes.
• A single moment may appear in multiple player highlight feeds.
• Official stats remain unchanged and authoritative in match/stat systems.
• Media ownership remains with the media creator even when many players are associated with the same moment.

Future Extensions

• Participation moderation and approval queues.
• AI detection of secondary participants from event and clip context.
• Coach tagging tools for structured participation review.
• Scouting annotations layered on top of participant roles.

Player Profile Claim Engine

Concept Explanation

The Player Profile Claim Engine enables users to securely claim pre-existing player profiles created through imports, clubs, or administrative workflows.

Turfi separates:

• Players: athlete records in the sports graph.
• Users: authenticated platform accounts.
• Claims: controlled requests linking users to player profiles.

This prevents automatic ownership conflicts while keeping profile integrity intact.

Architecture Overview

The engine runs as a claim-and-review workflow:

1. User account exists (or is newly created).
2. System suggests likely player profiles based on identity metadata.
3. User submits claim request.
4. Authorized reviewers approve/reject/flag conflict.
5. Approved claims create the user-player ownership link.

Ownership transfer is explicit and audited rather than inferred from name-only matching.

Database Model

Existing related tables:

• players
• users
• user_player_links

Prepared claim workflow table:

• player_profile_claims

Supporting moderation/review views:

• api_pending_player_claims
• api_player_claim_conflicts
• api_player_claim_review

Proposed structure:

• id
• player_id
• user_id
• status
• verification_method
• requested_at
• reviewed_by_user_id
• reviewed_at
• notes

Claim statuses:

• pending
• approved
• rejected
• conflict

user_player_links is the approved relationship table already used by Turfi before this workflow expansion; claims now govern how new links are created.

Workflow Description

Step 1 - Account creation:

• User creates a Turfi account.

Step 2 - Profile suggestions:

• System suggests likely player profiles using name and context metadata.

Example suggestion context:

• Danny Malouin
• U17 Montreal
• Birth year 2008

Step 3 - Claim submission:

• User submits a claim.
• A player_profile_claims row is created with status = pending.

Step 4 - Claim review:

• Approval authority reviews the claim (club admin, league admin, or Turfi admin).

Moderation view usage:

• api_pending_player_claims powers the pending moderation queue.
• api_player_claim_conflicts surfaces contested player claims.
• api_player_claim_review provides review dashboard context for decisions and notes.

Step 5 - Approval and linking:

• On approval, user_player_links is created/updated.
• User gains profile control permissions in player-facing workflows.

Step 6 - Conflict handling:

• If multiple claimants target the same player, claim enters conflict.
• Manual review resolves final ownership.

Example Use Cases

• Club imports roster first, player joins platform later and claims profile.
• Youth player account created after season import; claim reviewed by club admin.
• Duplicate claimant scenario escalated to conflict review workflow.

Media Ownership Rules

Player profile claims do not transfer media ownership automatically.

Example:

• Coach uploads a clip and remains media creator.
• Player becomes the subject/linked participant.
• Claim approval links profile access, not media authorship.

Future Extensions

• Identity verification providers for high-confidence approvals.
• Club-level approval queues and SLA tracking.
• Parent-managed youth account claim workflows.
• Agent/scout representation models with delegated permissions.

Player Claim Suggestion Engine

Purpose

The Player Claim Suggestion Engine helps users find likely claimable player profiles quickly instead of manually browsing the full player database.

Architecture Overview

The engine combines identity data and ownership state to surface likely claim matches with team/club context.

Primary views:

• api_player_claim_candidates
• api_claimable_players
• api_player_claim_search

Database Model

Supporting tables and identity context include:

• players
• user_player_links
• player_registrations
• teams
• clubs

View purposes:

• api_player_claim_candidates counts existing links and identifies candidate profiles for claims.
• api_claimable_players filters to profiles without linked user ownership.
• api_player_claim_search provides search-friendly identity rows with team and club context.

Team and club context is derived through player_registrations and teams relationships, not from players alone.

Workflow

1. User opens claim flow.
2. System searches identity records in api_player_claim_search.
3. Claimable candidates are filtered through api_claimable_players.
4. Candidate confidence/context is derived from api_player_claim_candidates.
5. User selects a profile and submits a claim request.

Example Scenario

• Danny Malouin
• birth year 2008
• U17 Montreal
• CF Montreal

The engine surfaces this as a likely claim candidate with enough context for user confirmation.

Future Considerations

• weighted suggestion scoring
• typo-tolerant matching
• federation/club verification boosts
• claim confidence indicators in the UI

Claim Governance Workflow

Purpose

Claim Governance introduces a moderation buffer between account creation and player-profile ownership to protect identity integrity.

Users should not automatically gain profile control from name matching alone.

Architecture Overview

Governance is implemented as a reviewed workflow around player_profile_claims and user_player_links.

Approval hierarchy:

• club admin
• league admin
• platform admin

Workflow

1. User creates account.
2. System suggests possible player profiles.
3. User submits claim request.
4. Claim enters pending moderation queue.
5. Approval authority reviews claim evidence/context.
6. Claim is approved or rejected.
7. If approved, user is linked to profile in user_player_links.

Conflict Handling

When multiple users claim the same player:

• claim status transitions to conflict
• manual investigation is required
• reviewer records final decision (approve/reject) with notes

Some low-risk claim classes may be auto-approved in future policy models, but governed moderation remains the default architecture.

Media Impact

Claim approval does not transfer media ownership.

• media remains owned by the original creator
• claimed player becomes subject/participant context where applicable
• moment/media association and media authorship remain separate concerns

Player Profile Media System

Status: PARTIAL

Player profiles are one of the main destinations for approved moments, derived clips, and recruiting-ready media context.

Expected player profile navigation:

• Overview
• Stats
• Matches
• Highlights
• Media
• Recruitment

Highlights and Media Sources

Player highlights may come from:

• system generated clips
• player uploads
• coach uploads
• club uploads

Those assets should appear on player profiles only after they are connected through approved events, verified relationships, or governed media links in the Media Engine.

Moment Claiming Workflow

Provider-assisted ingestion introduces a second claim path alongside traditional player-profile ownership claims.

Example workflow:

1. A provider imports a goal.
2. The player is still unknown or unresolved.
3. A player submits a claim for that event or moment.
4. A coach or admin verifies the claim.
5. The event is updated with the approved player identity.
6. Player stats rebuild from approved game_events.
7. Player highlights and media views update from the rebuilt event and moment graph.

This keeps event participation, stat truth, and player media aligned with the same canonical review flow.

Player Attribution Cascade

When provider or AI-assisted video analysis detects a player, Turfi should resolve that athlete through a roster-aware cascade before asking a human reviewer.

Example detection:

• Team: Lakeshore U17
• Jersey: 7

Attribution order:

1. Game lineup roster: match by game_id + jersey_number
2. Team roster: match by team_id + jersey_number
3. Historical inference: analyze prior events for the same team and jersey number
4. Fallback: insert a task into video_player_resolution_queue

This design keeps the first attempt grounded in the most authoritative match context, then broadens only when necessary. If no confident player match exists, admins or coaches resolve the player from the queue instead of letting uncertain provider labels become canonical player identity.

Automatic Player Highlights

Turfi automatically generates player highlight feeds from the canonical event graph rather than requiring manual clip attachment for every player.

Pipeline:

game_event
    ↓
video_moment
    ↓
highlight_clip
    ↓
player_highlight_items
    ↓
v_player_highlights

This powers:

• player profiles
• recruiter discovery
• highlight reels
• league content feeds

Relationship to Other Engines

The Player Profile Media System extends:

• Media Engine for source media, clips, and collections
• Match Intelligence Engine for approved event-driven stats and moments
• Broadcast Engine for playback delivery
• Recruitment Engine for player exposure and evaluation surfaces
• Admin Operations for verification, moderation, and unresolved-player review queues

Player Engine Tables

players

Purpose Base table for athlete identity and profile data used across competitions, media, and recruitment. Player records can exist independently of platform user accounts and are linked through user_player_links and player_profile_claims when athletes claim ownership.

Player Identity Lifecycle Player records move through lifecycle states: active, archived, and deleted. Active players appear in discovery and search. Archived players are hidden from discovery but remain in the database with all historical references intact. Deleted players have had their profile row removed, but deletion never removes match history—game events, participations, and statistics are preserved by detaching the player reference (SET player_id = NULL) instead of deleting rows. Historical match identity is preserved through snapshot fields stored in match event tables (player_name_text in game_events), so match timelines remain readable even after the player profile is gone.

api_player_profiles

Purpose Read model/view for player profile identity and display fields.

Primary Relationships Used as the profile root for player pages and linked player-facing datasets by player id.

Important Constraints Queried by id; UI expects a deterministic single profile row per id.

api_player_competition_analytics

Purpose Player competition analytics read model.

Primary Relationships Joined by player_id to expose aggregate competition metrics.

Important Constraints Player page usage expects one analytics summary record for the selected player.

api_player_matches

Purpose Player match-history read model.

Primary Relationships Joined by player_id with competition/game context.

Important Constraints Must support multi-row historical output for a single player.

api_player_trends

Purpose Trend/performance read model for player metrics.

Primary Relationships Joined by player_id with trend aggregates.

Important Constraints Expected to provide one summary trend row per player in current UI usage.

api_player_media

Purpose Player-scoped media read model.

Primary Relationships Joined by player_id to media/moment context.

Important Constraints Must support one-to-many media rows for player media tabs.

---

Player Identity Engine

Players use a specialized identity system rather than the generic registry identity trigger.

Authoritative triggers:

• trg_refresh_player_identity_players
• trg_players_normalized_name
• players_slug_trigger

This identity system supports:

• duplicate detection
• player merge workflows
• profile claim resolution
• identity snapshots