Turfi Platform Documentation

Bulk data ingestion is one of the core operational needs of the platform, because leagues, clubs, and administrators cannot maintain Turfi one row at a time. This document explains how import workflows translate external spreadsheets into governed platform records. It fits into the architecture as the bridge between external operational data and Turfi's internal registry, player, and competition systems. As import capabilities expand, this file should remain the reference for resolver behavior, performance strategy, and ingestion rules.

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

---

Import Engine

Purpose

The Turfi Import Engine allows administrators to import structured datasets into the platform using CSV files or League Packages.

The engine is designed to support large scale data ingestion such as importing entire leagues, tournaments, or academy structures.

Location in application

Admin Imports

Navigation structure

Admin Imports New Import Import Jobs Import Templates

Import types

The engine supports two import methods.

CSV Import

Allows importing individual entity datasets such as players, teams, clubs, competitions, seasons, venues, turfs, businesses, addresses, contacts, and roster registrations.

Users upload a CSV file and map columns to platform fields before executing the import.

League Package Import

Allows importing an entire league structure using a single archive file.

A League Package typically contains multiple files including:

• league.json
• clubs.csv
• teams.csv
• players.csv
• rosters.csv
• games.csv

The platform processes these files in dependency order to construct the full competition structure.

Import processing pipeline

All imports follow the same processing pipeline.

Upload Parse Normalize Validate Preview Execute Report

Detailed flow:

1. Upload CSV (or package file) in Admin Imports.
2. Parse source columns and rows.
3. Map source columns to destination fields.
4. Validate required fields and detect obvious issues.
5. Preview transformed rows.
6. Run import adapter per entity.
7. Persist job and row results for audit/review.

During preview the system highlights:

• Missing required fields
• Reference mismatches
• Potential duplicates

After execution the engine produces an import report summarizing:

• Total rows processed
• Successful rows
• Failed rows
• Warnings

Import Architecture

All imports run through a shared import service.

The import service delegates row processing to entity specific adapters such as:

• player import adapter
• team import adapter
• club import adapter
• competition import adapter
• season import adapter
• venue import adapter
• turf import adapter
• business import adapter
• address import adapter
• contact import adapter
• player registration import adapter

This architecture ensures that new entities can be added to the import engine without modifying the core import system.

Import Tracking

Import jobs are recorded through API-facing views and underlying system tables.

Primary API views used by the admin interface:

• api_import_jobs
• api_import_rows
• api_import_mappings
• api_import_entity_adapters

Underlying tables used for persistence and execution tracking include:

• import_jobs
• import_rows
• import_mappings
• import_entity_adapters
• import_reference_cache
• import_job_files

These tables allow administrators to inspect previous imports and diagnose failures.

Key tracking behavior:

• api_import_jobs exposes job-level status, counts, and timing
• api_import_rows exposes row-level outcomes (success/warning/failed)
• api_import_mappings stores reusable column mapping templates
• api_import_entity_adapters defines destination/required field metadata per entity

Relationship to Data Management Interface

The Import Engine is designed to complement the Admin Data Management Interface.

Bulk data can be imported using the Import Engine while individual records can still be managed manually through the Admin Data pages.

Together these systems provide a complete solution for both large scale and small scale data management within the Turfi platform.

Smart Reference Mapping in the Import Engine

Purpose

Smart Reference Mapping removes the need for administrators to provide internal UUIDs in CSV files for common relationship fields.

Users can provide human-readable values such as:

• team
• club
• venue
• competition
• season

instead of only:

• team_id
• club_id
• venue_id
• competition_id
• season_id

Architecture Overview

The Import Engine resolves references during row processing by combining:

1. field mapping metadata from import adapters
2. entity resolver lookup logic
3. per-job resolution cache and exception mapping

If a row contains a relationship name but no ID, the import pipeline attempts to resolve that value before running entity adapters.

Database Model

Core tables/views in this workflow:

• api_import_entity_adapters
• api_import_jobs
• api_import_rows
• import_jobs
• import_rows
• import_mappings
• import_reference_cache

import_reference_cache supports "resolve once, apply everywhere" behavior when the same source value appears repeatedly.

Workflow

1. Upload CSV
2. Configure field mapping
3. Auto-match reference values
4. Resolve exceptions (if ambiguous/not found)
5. Preview transformed rows
6. Choose import mode
7. Execute import
8. Review job and row outcomes

Resolve Exceptions

When the resolver cannot confidently match values (for example CF Montreal vs Montreal CF), the import flow must surface those rows for manual resolution.

Once a user picks the correct entity, the mapping is reused across all matching rows in the same import job.

Import Modes

• Insert only
• Update existing
• Upsert

Duplicate creation should never be the default platform behavior.

Import System

Status: IMPLEMENTED

The Import System is how large amounts of data get into Turfi. When a league uploads a roster CSV, when an admin migrates historical games, or when a club bulk-loads venues and turfs, the Import System handles the upload, mapping, validation, and execution. It turns spreadsheets and League Packages into structured records that the rest of the platform can use.

Manual data entry doesn't scale. A single competition might have hundreds of players and dozens of games; federations may need to backfill years of history. The Import System exists so admins can use the tools they already have (CSV, Excel) and get data into Turfi without typing each record by hand. It also insulates the platform from requiring internal UUIDs—names and references are resolved automatically.

This system owns CSV and League Package upload and parsing, field mapping (source columns to destination fields), entity resolution (matching keys, slugs, and names to internal IDs), preview validation and row-level error reporting, import job execution and tracking, and adapters for each entity type (players, teams, clubs, competitions, competition registrations, etc.).

Import feeds the Player Engine, Competition Engine, and Admin Data registries. Registry entities use database-enforced identity generation for key and slug behavior, while player imports continue to rely on the separate Player Identity infrastructure, resolver views, duplicate detection, and merge workflows. The Infrastructure Engine provides transactional execution; Admin Data provides the UI entry point and post-import review.

Future work includes pre-import duplicate warnings, smarter mapping suggestions, incremental and delta imports, and integration with federation data feeds for automated league sync.

---

Purpose

The Import System enables administrators to import structured sports data into Turfi using CSV-based workflows. It supports operational scenarios including large scale data ingestion, league roster imports, historical backfills, and admin data migration.

The system is designed so import files do not need internal UUIDs. Administrators can provide _key values as the preferred registry reference format, then slug or name when needed, and the platform resolves those values to internal IDs during processing.

Architecture Overview

The Import System is implemented as a staged pipeline:

1. CSV upload
2. field mapping
3. automatic entity resolution
4. exception resolution
5. preview validation
6. import execution

Current implementation is split across:

1. Admin Import UI (/admin/imports/new, /admin/imports/jobs, /admin/imports/templates)
2. shared import orchestration service (lib/imports/importService.ts)
3. entity adapters (player, team, club, competition, season, venue, turf, business, address, contact, player_registration)
4. shared resolver service (lib/entityResolver.ts)

For registry entities (organizations, leagues, seasons, competitions, clubs, teams, venues, turfs, businesses, addresses, contacts) the resolver follows key -> slug -> normalized_name -> name fallback when those entities themselves are being created or updated. organizations, clubs, and leagues use database-maintained canonical names for that name-based match step. Players remain on the existing Player Engine identity and resolver flow rather than the generic registry identity path, even though they now participate in the standardized registry lifecycle model through status.

Normalized category lookups used by imports:

• leagues.gender_id
• competitions.age_group_id
• competitions.gender_id
• teams.age_group_id
• teams.gender_id
• players.gender_id

The resolver translates import relationships into foreign keys before adapter writes, for example:

• team_key -> team_id
• club_key -> club_id
• competition_key -> competition_id
• owner_organization_key -> owner_organization_id
• venue_key -> venue_id

This prevents import users from needing to know internal identifiers ahead of time.

Key Database Models

Current import execution and tracking model:

• import_jobs
• import_rows
• import_mappings
• import_entity_adapters
• import_job_files
• import_reference_cache
• api_import_jobs
• api_import_rows
• api_import_mappings
• api_import_entity_adapters

Legacy/compatibility import model tables that may exist in some schema snapshots:

• player_data_imports
• imported_games
• imported_media

Reference resolution targets used during import mapping:

• Registry relationships resolved primarily through _key fields (organization_key, league_key, season_key, competition_key, club_key, team_key, venue_key, turf_key, business_key)
• Registry identity trigger: turfi_registry_identity() on organizations, leagues, seasons, competitions, clubs, teams, venues, turfs, businesses, addresses, contacts
• Centralized lookup labels: ui_field_labels
• Club licensing lookup: club_license_levels
• Age group lookup: age_groups
• Gender lookup: genders

Player identity-assisted matching:

• player_identity_index (with resolver views used for player match assistance in import flows)

Key Workflows

1. Upload CSV file

Admin uploads a CSV for a selected entity in Admin Imports.

1. Map CSV columns to platform fields

Source headers are mapped to destination fields using mapping templates and adapter metadata. Example: Player Name can be mapped into first_name and last_name transformations in the import configuration path.

1. Automatic entity resolution

Registry resolvers attempt key -> slug -> normalized-name-aware name matching, while player resolution continues to use the Player Engine identity infrastructure. Example: club_key = cf-montreal -> resolved club_id.

1. Resolve Exceptions

Unresolved or ambiguous values are surfaced for manual correction. Example ambiguous values: CF Montreal vs Montreal CF. Once selected, the manual mapping is reused for all matching rows in the job.

1. Preview Import

The system displays transformed output and row statuses, including how many rows are valid for insert/update and which rows still contain warnings/errors.

1. Choose Import Mode

Operationally exposed as insert-only, update-existing, or upsert behavior (mapped internally to adapter/service modes).

1. Execute Import

The import service runs entity adapters and writes to domain tables, then records job-level and row-level outcomes for audit and retry. Registry inserts may omit key and slug, in which case the database trigger generates them. If status is omitted on registry records, the database lifecycle defaults should preserve active behavior.

External Stats and Video Import Path

Turfi's Import System also acts as the structured intake layer for provider-assisted video and stats workflows. This does not replace the Media or Match Intelligence engines; instead, it stages raw provider payloads so they can be normalized into canonical match truth.

Provider-assisted imports are expected to support:

• source video registration into game_videos
• ingestion-job tracking through video_ingestion_jobs
• raw provider event capture in video_event_imports
• event-source lineage through game_event_sources
• unresolved player review through video_player_resolution_queue
• moment generation queueing through video_moment_generation_queue
• clip generation queueing through video_clip_generation_queue

Import workflow for provider data:

1. Register or resolve the target game and source video.
2. Load provider payload rows through an import adapter or provider-specific parser.
3. Store raw event rows before canonicalization.
4. Map provider event types into Turfi event types and assign event_source_type such as provider_import.
5. Resolve players, teams, and timestamps where possible.
6. Route unresolved items into video_player_resolution_queue instead of forcing incorrect canonical data.
7. Approve normalized events into game_events.
8. Enqueue approved events for moment generation and downstream clip extraction.

This keeps the Import System aligned with the broader rule that canonical truth is always approved game_events, never raw provider feeds.

Entity Address and Contact Import Rules

The current import model distinguishes between entity-owned communication data and centralized address records.

For the core governance entities:

• organizations (shown as Associations in the frontend)
• leagues
• clubs

the import engine now supports these direct entity fields:

• phone
• email
• website
• address_id

and these address-source fields:

• address_line1
• address_line2
• city
• province
• postal_code
• country

Import behavior:

1. If address fields are present, the import adapter inserts or reuses a row in addresses
2. The returned UUID is written to the entity's address_id
3. phone, email, and website are written directly to the entity row
4. If address fields are missing, address_id remains NULL

Operational effect:

• location data is normalized in addresses
• communication data stays on the entity
• registry imports do not require operators to create address rows manually first

Additional import rules still apply:

• venues remain facility records and may import their own location and website fields
• turfs must resolve to a venue through venue_key, venue_slug, or venue_name; a turf import must not create an unlinked turf
• businesses may resolve their linked venue through venue_key, venue_slug, or venue_name
• addresses and contacts still import as standalone reusable records where needed
• contacts import structured person names through first_name and last_name, while legacy full_name files are still accepted and split automatically during adapter normalization

Recommended dependency order for infrastructure-oriented imports:

1. organizations (Associations), leagues, clubs, and seasons where required by the broader dataset
2. venues
3. turfs
4. businesses
5. addresses
6. contacts

Current contact template example:

• first_name,last_name,title,email,phone,status
• legacy files using full_name,title,email,phone,status remain compatible through import-time splitting

Import Validation and Troubleshooting

Recent registry import hardening added a few operational rules that matter when validating facility imports:

• venues must load before turfs. Turf rows resolve venue_id from the live venues table through venue_key, venue_slug, or venue_name; a companion CSV is not enough if the parent venue row has not been created yet.
• Inline venue address text is normalized into the shared addresses table and linked through entity_addresses, but the live venues table still expects its own base write fields such as city, province, and country. Import adapters must not assume that every entity table exposes an address_id column directly.
• Blank CSV strings must be normalized before adapter writes when the destination column is typed. Common failure cases are empty strings hitting boolean columns such as is_indoor / is_lit or integer columns such as length_m / width_m.
• Import preview should be treated as a resolver check, not as proof that database writes will succeed. A row can resolve every relationship correctly and still fail on table-level type or not-null constraints during execution.
• Destructive validation and wipe flows must remain manual-only. Import validation may inspect row counts, relationship coverage, and preview results, but must never trigger a data wipe implicitly.

Facility-specific execution checklist:

1. import venues and confirm the expected live row count
2. verify any missing venue keys before turf import
3. import turfs only after every referenced venue_key exists live
4. if the registry grid does not show address data after a successful import, inspect the admin v_* read model before changing table writes

Import Resolution System

The Import Resolution System exists so incoming files can identify entities deterministically without requiring operators to know UUIDs in advance.

Resolution order for registry entities:

1. key
2. slug
3. name

Preferred relationship fields:

• organization_key
• league_key
• season_key
• competition_key
• club_key
• team_key
• venue_key
• turf_key
• business_key
• age_group_key
• gender_key
• owner_organization_key

Example import values:

• organization_key = qcsls
• league_key = lsl-premier
• season_key = 2025-winter

Resolution behavior:

• if key exists, reuse the UUID
• if key is missing on a new registry row, the database may generate it
• if slug is missing on a new registry row, the database may generate it
• if status is missing on a lifecycle-aware registry row, the database defaults should fall back to active
• imports must never create duplicates when a matching key already exists

Category lookup resolution rules:

• gender_key -> genders.key -> gender_id
• age_group_key -> age_groups.key -> age_group_id
• missing lookup values must not be auto-created during import
• leagues, competitions, teams, and players must consume the existing static lookups only

Transition rule for teams:

• imports should now treat teams.age_group_id and teams.gender_id as the source of truth
• legacy teams.age_group and teams.gender may still appear temporarily for compatibility
• database sync automation keeps the legacy text fields aligned during the transition

Club licensing resolution rules:

• Club licensing is optional descriptive metadata on clubs
• When an import flow surfaces licensing, it should resolve against club_license_levels.key
• Human-readable license labels must come from ui_field_labels, not from per-lookup translation tables
• club_license_level_translations must not be used in new import architecture
• Licensing must never be treated as an eligibility or federation-compliance gate during import

Import Performance Strategy

Large imports cache registry lookups so repeated references do not trigger repeated database queries.

Examples:

• league_key -> league_id
• organization_key -> organization_id
• owner_organization_key -> owner_organization_id
• venue_key -> venue_id
• age_group_key -> age_group_id
• gender_key -> gender_id

This improves throughput and keeps resolution behavior consistent across large batches.

Competition Import Model

Competition imports must support the following CSV fields:

• organization
• league
• season
• name
• age_group_key
• gender_key
• type
• format
• start_date
• end_date

League is optional.

Age group and gender are optional as well. When provided, they resolve as:

• age_group_key -> age_groups.key -> competitions.age_group_id
• gender_key -> genders.key -> competitions.gender_id
• type -> competition_types.key -> competitions.competition_type_id
• format -> competition_formats.key -> competitions.competition_format_id

The importer must not create missing lookup values automatically. age_group_key and gender_key must already exist in their lookup tables.

League resolution is based on name normalization rather than slug matching. Slug remains reserved for URL identity and should not be treated as the competition import identity field.

Interactions with Other Engines

• Player Engine: imports create/update player profiles, registrations, and player-linked references.
• Competition Engine: imports can create or update competitions, team registrations (team_competitions), teams, games, and related structural data.
• Media Engine: imported media metadata can seed moment/highlight-related workflows and media associations.
• Discovery Engine: imported entity records become available to search/discovery surfaces after ingestion.
• Infrastructure Engine: import jobs execute on Supabase/PostgreSQL read/write paths using API views and service-layer orchestration.
• Data Management Interface: imports complement manual Admin Data CRUD workflows for bulk operations.

Example Use Cases

• Import an annual season list before opening competition setup.
• Bulk load venue and turf inventory before publishing schedules.
• Re-import corrected club/team data with update_only mode.
• Save mapping templates for recurring federation-provided CSV files.

Import Resolution System

Turfi imports resolve registry entities in the following order:

1. key
2. slug
3. name

Preferred relationship fields include:

• organization_key
• league_key
• season_key
• competition_key
• club_key
• team_key
• venue_key
• turf_key
• business_key
• age_group_key
• gender_key

Resolution behavior:

• If key exists, reuse the entity UUID
• If key is missing, the entity may be created
• If slug is missing, the database generates slug
• If key is missing, the database generates key

Imports must never create duplicates when a matching key already exists.

Import Performance Strategy

Registry lookups should be cached during import execution so repeated references do not trigger repeated database queries.

Example:

• league_key -> league_id
• age_group_key -> age_group_id
• gender_key -> gender_id

This keeps large import runs efficient and ensures resolution stays consistent across the same job.

Competition Import Rules

Competition CSV imports must support:

• organization_key or another supported organization reference
• league_key or another supported league reference
• season_key or another supported season reference
• name
• age_group_key
• gender_key
• type
• format
• start_date
• end_date

Competition import rules:

• every competition import row must resolve an organization
• type resolves through competition_types.key
• format resolves through competition_formats.key
• league is optional for standalone competition types
• if type = League, the row must resolve to an existing league
• if type is Tournament, Showcase, or Friendly Series, league may be blank

Age group and gender are optional, but when they are supplied they must resolve against the lookup tables:

• age_group_key -> age_groups.key
• gender_key -> genders.key
• type -> competition_types.key
• format -> competition_formats.key

League resolution must use name normalization rather than slug identity.

Example matching rule:

• LOWER(name)

Example competition rows:

• League competition: organization_key = soccer-quebec, league_key = league1-quebec, season_key = 2026-summer, name = League1 Quebec Senior Men, type = League, format = Round Robin
• Standalone competition: organization_key = soccer-quebec, league_key = , season_key = 2026-summer, name = Quebec U17 Showcase, type = Showcase, format = Group + Knockout
• youth competition category example: age_group_key = u17, gender_key = female
• senior competition category example: age_group_key = senior, gender_key = male

Centralized Address and Contact Relationship Imports

Address and contact roles are now normalized relationship metadata rather than free-text link fields.

Rules:

• entity_addresses.address_role_id resolves through address_roles.id
• entity_contacts.contact_role_id resolves through contact_roles.id
• portable import values should use lookup keys such as address_role and contact_role
• the import layer resolves those values through address_roles.key and contact_roles.key
• deprecated text columns entity_addresses.address_role and entity_contacts.contact_role remain transitional only and must not be used as application write targets