Turfi Platform Documentation

Every domain engine in Turfi depends on stable runtime foundations, environment configuration, provider integrations, and operational maintenance rules. This document explains those lower-level foundations and how the platform is deployed and governed in practice. It fits beneath the higher-level engine docs because it describes the services, APIs, tables, triggers, and environment concerns that support all other systems. As the platform scales, this file should remain the infrastructure and operational reference point.

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

---

Platform Stack and External Services

The Turfi platform relies on a number of external infrastructure providers and services to operate. These systems provide hosting, database infrastructure, authentication, email delivery, media storage, and deployment capabilities.

This section documents the external platform stack and the operational accounts used to access each system.

Passwords are intentionally not documented here for security reasons.

External Service Reference

Infrastructure Engine

Status: IMPLEMENTED

The Infrastructure Engine is the technical foundation that makes Turfi work. It doesn't handle players, games, or media directly—instead, it provides the database, storage, security, and execution layers that every other engine depends on. When data is read through API views, when a file is stored, or when a privileged operation runs in an edge function, the Infrastructure Engine is enabling it.

Every sports platform needs a reliable way to store data, enforce access control, and run background tasks. Rather than having each domain engine implement its own database access, storage, and security, Turfi centralizes these concerns in the Infrastructure Engine. This keeps the system consistent, secure, and easier to evolve.

This engine owns PostgreSQL database architecture and API views, Row Level Security and authentication integration, object storage for media assets, trigger-driven and RPC-capable server-side logic, edge functions for privileged or asynchronous operations, and the contract that application code reads from api_* views rather than raw tables.

The Infrastructure Engine underlies everything in Turfi. Identity Engine uses it for auth and session; Player Engine, Competition Engine, and Media Engine store and read data through it; Import System executes within its transactional boundaries. Discovery, Broadcast, and Social Engine all depend on its read models and storage.

Planned evolution includes background job queues, media processing pipelines, schema migration tooling, and potential multi-region or multi-tenant infrastructure as the platform scales.

---

Purpose

The Infrastructure Engine is the foundational operational layer that supports all other Turfi engines.

It is responsible for:

• database architecture
• API exposure through views and function-based server execution patterns
• background processing foundations
• edge function execution
• file/object storage integration
• system orchestration for auth, roles, and workspace-aware access

This engine provides the shared services required by every domain, including players, competitions, media, social, discovery, and import workflows.

Architecture Overview

Turfi is built on a PostgreSQL-first architecture with Supabase as the managed infrastructure layer.

Key infrastructure layers include:

• PostgreSQL database layer for canonical relational data and transactions
• database views that act as application APIs (api_*)
• PostgreSQL function layer for controlled server-side operations (trigger-driven today, RPC-compatible by design)
• Supabase Edge Functions for secure or asynchronous execution paths
• object storage for media and file assets
• Row Level Security (RLS) for authenticated access control

The application interacts with the database primarily through API views rather than raw tables. This provides:

• consistent query logic across frontend surfaces
• clearer security boundaries
• stable API contracts even when underlying schema internals evolve

Key Database Models

Core infrastructure models and patterns:

Identity and access foundation

• auth.users
• users
• roles
• user_roles
• workspaces
• workspace_members

API view contract layer

Views prefixed with api_ are the primary application interface.

Examples in current architecture include:

• api_player_profiles
• api_match_timeline_unified
• api_player_activity
• api_user_workspace_summary

Equivalent naming such as api_players, api_match_timeline, or api_activity_feed may exist across schema versions, but the architecture standard is to expose application reads through api_* views.

Function execution layer (RPC-capable pattern)

PostgreSQL functions are used for controlled server-side operations, with trigger-driven workflows actively implemented and RPC endpoints available as an execution pattern for controlled operations.

Function-oriented platform workflows include:

• moment candidate and timeline derivation pipelines
• match state and sequence updates
• stat aggregation and context projection
• user/profile synchronization and role propagation

Edge function layer

Supabase edge functions run secure server logic and asynchronous workflows.

Current implemented example:

• delete-user

Planned/expanding examples:

• background processing jobs
• media processing pipelines
• video moment generation workers
• video clip extraction workers

Storage model

Media files are stored in Supabase object storage.

The media_assets table references storage objects and tracks metadata such as ownership/uploader context, linked entities (players, teams, games, competitions), and timing/processing fields used by clip and highlight workflows.

Key Workflows

Authentication flow

• Authentication is handled by Supabase Auth.
• OAuth providers (Google, Facebook, LinkedIn) are integrated through Supabase auth configuration.
• Session and profile context are hydrated for role/workspace-aware UI behavior.

Database access flow

• Frontend services read from API views first (api_*), not base tables.
• This enforces consistent read models and reduces coupling to raw relational structures.

Data processing flow

• Complex operations run in PostgreSQL function pipelines (trigger-backed today, RPC-capable where controlled server execution is needed).
• Typical processing includes match event handling, moment-candidate derivation, and stat/context updates.

Secure server execution flow

• Privileged/destructive actions execute in edge functions with explicit auth token validation and service-role boundaries.
• Example: account deletion through delete-user.

Background task flow

• Infrastructure is designed to support asynchronous processing through edge functions and background services.
• Planned workloads include video analysis, highlight detection support jobs, and operational data cleanup tasks.
• Worker-facing read models such as v_video_moment_generation_jobs and v_video_clip_generation_jobs give asynchronous services a stable contract for polling media-intelligence work without coupling directly to every queue table.

Interactions with Other Engines

• Player Engine: provides persistence, API view contracts, and access control for player identity and profile data.
• Competition Engine: provides storage and processing infrastructure for games, events, timelines, and standings-related context.
• Media Engine: provides object storage integration and metadata lifecycle for media ingestion and clip/highlight linkage.
• Social Engine: provides infrastructure for activity/feed data access and role-aware visibility boundaries.
• Discovery Engine: provides query/read infrastructure used by search and discovery surfaces.
• Import System: provides transactional ingestion infrastructure, resolver-connected workflows, and job tracking boundaries.
• Platform Systems / Identity Engine: shares auth, role, and workspace orchestration that all engines depend on.

API and Services

API Views

Turfi uses API views as a stable contract layer between UI components and database storage models.

UI components should not query base tables directly for operational pages. Instead, they use API views that:

• enforce consistent field naming
• expose pre-joined, UI-friendly data shapes
• reduce frontend coupling to schema internals
• support safer evolution of underlying tables

Common admin and domain examples include:

• api_players (or project equivalent api_player_profiles)
• api_teams (or api_team_profiles)
• api_clubs (or api_club_profiles)
• api_competitions (or api_competitions_profiles)
• api_venues
• api_turfs
• api_businesses
• api_seasons

Import-related API views:

api_import_jobs

Purpose Job-level tracking view for import execution status, row counts, and timestamps.

api_import_rows

Purpose Row-level tracking view for success/warning/error outcomes and per-row messages.

api_import_mappings

Purpose Mapping template view used to save and reuse source-to-destination field mappings.

api_import_entity_adapters

Purpose Entity adapter metadata view defining destination fields and required fields used during mapping and validation.

api_player_profiles

Purpose Primary player profile projection consumed by player pages.

api_player_competition_analytics

Purpose Aggregated player competition metrics used for player overview surfaces.

api_player_matches

Purpose Player match history projection used by player match tabs.

api_player_trends

Purpose Trend/advanced stat projection for player performance context.

api_player_media

Purpose Player media projection used to render player media collections.

api_player_activity

Purpose Player activity-feed projection for player activity surfaces.

api_games

Purpose Game/match projection used as the canonical match record for game pages.

api_match_timeline_unified

Purpose Unified timeline projection for match events and timeline rendering.

api_player_game_stats

Purpose Game stats projection for match statistics tabs.

api_user_workspace_summary

Purpose Workspace summary projection used for role/workspace hydration and routing.

RPC Functions

The following RPC functions are invoked by the application through supabase.rpc().

Entity Resolution

Player Lifecycle

Edge Functions

Supabase Edge Functions (Deno runtime) handle privileged and asynchronous operations. Functions are invoked via HTTP at /functions/v1/<function-name> with CORS, authentication, and environment configuration.

delete-user

Purpose Handles user account deletion across identity and platform data. Invoked when a user requests account deletion from settings. Securely deletes the currently authenticated account across user_roles, users, and auth.users.

Deletion Order Application-level identity rows (user_roles, users) are deleted before auth identity to ensure clean state if the user re-signs up with the same email.

Input Parameters

• HTTP method: POST (with OPTIONS preflight support)
• Headers:
• Authorization: Bearer <access_token> (required)
• apikey (used by client caller)
• Content-Type: application/json
• Request body: no required payload fields in current implementation

Security Model

• Verifies bearer token using Supabase anon client context (supabase.auth.getUser()).
• Uses service-role client (SUPABASE_SERVICE_ROLE_KEY) for privileged delete operations.
• Rejects missing/invalid auth with 401.
• Rejects missing environment config with 500.

Expected Behavior

• Validates authenticated user from token.
• Deletes user_roles rows for user_id.
• Deletes users profile row for id.
• Deletes auth identity via admin.auth.admin.deleteUser(user.id).
• Returns { success: true } on success; returns explicit JSON error messages on failure.

Future Background Processing Functions

Planned background processing workers will handle asynchronous operations that cannot run in the request cycle:

• Media processing: transcoding, thumbnail generation, format conversion
• Video analysis: automated detection, clip extraction, highlight candidate identification
• Data cleanup: orphan record removal, cache invalidation, archival
• Analytics tasks: aggregation, reporting, trend computation

Database Documentation

Validation Scope

The objects below are documented only after confirming they are actively referenced by the current implementation through:

• Supabase table access (.from("..."))
• PostgREST schema endpoints (/rest/v1/...)

If a legacy/older note conflicts with current code usage, this file reflects the current implementation.

---

Identity Engine Tables

auth.users

Purpose Canonical authentication identity table managed by Supabase Auth.

Primary Relationships Linked 1:1 with users through shared id (users.id = auth.users.id).

Important Constraints Auth lifecycle is controlled by Supabase; application flows assume a single auth identity row per user id.

users

Purpose Primary application profile table used by onboarding, account settings, and session hydration.

Primary Relationships Linked to auth.users by id and referenced by user_roles.user_id.

Important Constraints id is treated as the canonical user identity key across all profile updates/selects.

roles

Purpose Role catalog table used for capability/workspace classification.

Primary Relationships Referenced by user_roles; resolved in session role hydration through relational join.

Important Constraints Role names are consumed as stable capability labels in routing and UI authorization behavior.

user_roles

Purpose Assignment table mapping users to roles.

Primary Relationships user_roles.user_id -> users.id; role relation points to roles.

Important Constraints Insert/delete operations on this table directly impact workspace visibility and role-aware access.

---

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:

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.

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

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.

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 on round_id. 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 becomes official, 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.

---

Infrastructure Engine Tables

api_user_workspace_summary

Purpose Workspace summary read model used by shared session/workspace infrastructure.

Primary Relationships Keyed by user_id, derived from user-role/profile state.

Important Constraints Must return consistent workspace rows for role-based navigation and context setup.

---

Match Intelligence Tables

api_match_timeline_unified

Purpose Unified match-event timeline read model.

Primary Relationships Keyed by game_id; combines event, player, and team timeline context.

Important Constraints Must preserve event ordering and timestamp/minute semantics for playback/timeline rendering.

api_player_game_stats

Purpose Match statistics read model for game stat panels.

Primary Relationships Keyed by game_id; exposes home/away values for normalized stat keys.

Important Constraints Current UI expects consistent stat-key labeling and numeric values per row.

---

Media Engine Tables

media_assets

Purpose Root media entity used by the Studio layer for uploaded media ingestion and shared media lifecycle management.

Primary Relationships Links uploaded media to player/team/game/competition/event context and acts as the source media reference for moments, clips, and highlight collections.

Important Constraints Must preserve stable source identity and storage metadata so downstream moment/highlight pipelines can reuse one canonical media object across multiple contexts without duplication.

video_moments

Purpose Stores/exposes moment-level clips tied to games.

Primary Relationships Keyed by game_id; consumed by match timeline/media playback.

Important Constraints Requires stable ids and timestamp/minute fields to support deterministic playback navigation.

highlight_collections

Purpose Stores/exposes grouped highlight content for a game.

Primary Relationships Keyed by game_id; consumed by match highlight and recap surfaces.

Important Constraints Requires stable ids and playable URL fields for highlight rendering.

---

Recruitment Engine Tables

Recruitment shares database models with the Discovery Engine and Player Engine. Tables referenced for recruitment workflows include:

• api_player_profiles — Player discovery read model
• api_player_competition_analytics — Performance context for scouting
• api_player_matches — Match history
• player_search_index — (if present) Search-optimized player index
• saved_player_searches — (PLANNED) Stored search criteria for recruiters

Recruitment-specific tables (e.g. scouting_notes, player_reports) are planned but not yet confirmed in the schema.

---

Social Engine Tables

api_player_activity

Purpose Player activity feed read model used in player profile activity surfaces.

Primary Relationships Keyed by player_id; provides activity text/timestamp/engagement fields.

Important Constraints Must support multi-row player feeds and stable event ids.

---

Platform System Tables

users

Purpose Shared profile system table across onboarding/account/session services.

Primary Relationships Referenced by user_roles and tied to auth.users identity.

Important Constraints id consistency is critical for auth, deletion, and profile synchronization paths.

user_roles

Purpose Shared authorization mapping for platform workspaces.

Primary Relationships Bridges users and roles; drives workspace summary derivation.

Important Constraints Role assignment changes have direct effect on access, workspace routing, and role-aware UI.

roles

Purpose Shared system role dictionary.

Primary Relationships Referenced by user_roles; consumed during session role expansion.

Important Constraints Role-name stability is required for consistent capability and workspace mapping behavior.

api_user_workspace_summary

Purpose Shared read model for workspace context and selection.

Primary Relationships Derived by user identity and role configuration.

Important Constraints Must remain aligned with users and user_roles state for accurate workspace exposure.

---

Trigger Inventory (Validated)

auth.users.on_auth_user_created

• Purpose: Automatically mirrors new auth identities into public.users through handle_new_auth_user().
• Primary relationships: auth.users -> public.users (shared identity key).
• Key constraints: Must execute successfully for signup/OAuth provisioning to complete.

auth.users.on_auth_user_updated_sync_public_user

• Purpose: Synchronizes auth-user verification state to application profile via sync_auth_user_verification().
• Primary relationships: auth.users -> public.users.
• Key constraints: Update trigger must keep verification state aligned across schemas.

public.user_roles.trg_refresh_workspace_members_from_user_roles

• Purpose: Refreshes workspace membership projection when role assignments change.
• Primary relationships: public.user_roles to workspace summary/membership layer.
• Key constraints: Fires on insert/update/delete of role mappings.

public.users.assign_default_player_role

• Purpose: Assigns default player role on user creation through assign_default_player_role().
• Primary relationships: public.users -> public.user_roles / roles.
• Key constraints: Trigger introduces automatic role side effects on insert.

public.users.trg_assign_default_player_role

• Purpose: Additional trigger invoking assign_default_player_role() on user insert.
• Primary relationships: Same role-assignment path as above.
• Key constraints: Duplicate/default-role triggers can create overlapping role-assignment behavior if not controlled.

public.users.set_users_updated_at

• Purpose: Maintains updated_at on profile row updates via set_updated_at().
• Primary relationships: Internal lifecycle management on public.users.
• Key constraints: Must fire before update for consistent modification timestamps.

public.users.trg_update_user_location

• Purpose: Normalizes/updates location fields via update_user_location() on insert/update.
• Primary relationships: Operates on profile address/location attributes.
• Key constraints: Trigger function behavior directly affects onboarding/profile save flows.

---

Function Inventory (Non-RPC, Trigger-Driven)

The following database functions are currently validated as trigger-linked operational functions (not application-invoked RPC endpoints):

• handle_new_auth_user()
• sync_auth_user_verification()
• trg_refresh_workspace_members_from_user_roles()
• assign_default_player_role()
• set_updated_at()
• update_user_location()

Development Reset Workflow

Purpose:

Reset the platform to a clean testing state while preserving infrastructure.

The reset clears registry and competition data but must not delete:

• lookup tables
• labels
• system configuration
• roles
• permissions
• platform infrastructure tables

The reset preserves the admin user danny@digitalsteam.ca.

---

Browser and CDN caching (Next.js deployments)

After a new deployment, hashed JavaScript and CSS live under /_next/static/.... If a browser or CDN caches the HTML document for a route but the deployment serves new chunk filenames, the page shell can reference missing assets—white screen, broken styles, or console 404s for /_next/static/chunks/....

Mitigations in this repository:

• Middleware (middleware.ts) sets Cache-Control: private, no-cache, no-store, max-age=0, must-revalidate on HTML-like routes (paths without a file extension) so browsers and intermediaries do not serve stale shells after deploy.
• Next headers (next.config.mjs) mark /_next/static/:path* as public, max-age=31536000, immutable so hashed assets remain cacheable while filenames change each build.
• Node is aligned via .nvmrc and package.json engines so local and CI builds stay consistent.

If production still looks broken after deploy:

1. Confirm which build is live (for example NEXT_PUBLIC_DEPLOYMENT_TIMESTAMP or your host’s deployment id).
2. Hard refresh or a private window; if a CDN fronts the app, purge HTML/document routes or invalidate the deployment’s cache.
3. Ensure CI uses npm ci and the same Node major as .nvmrc.