Team System Setup Guide

Complete setup guide for the dual-source team management system Last Updated: 2025-11-09

Prerequisites

Supabase project configured
Environment variables in .env.supabase:
SUPABASE_URL
SUPABASE_SERVICE_ROLE_KEY
THESPORTSDB_API_KEY (optional, will use free tier if not set)

Quick Start

# Navigate to backend directory
cd backend/epgoat

# Activate virtual environment
source ../../venv/bin/activate

# Load environment variables
set -a && source ../../.env.supabase && set +a

Step-by-Step Setup

Step 1: Apply Database Migration (One-time)

Create the teams and team_aliases tables:

python utilities/run_migration_011.py

Expected Output:

Connecting to Supabase: https://your-project.supabase.co
Reading migration from: infrastructure/backend/epgoat/infrastructure/database/migrations/011_add_dual_source_team_system.sql
⚠️  Note: Supabase Python client doesn't support raw SQL execution
Please run the SQL migration manually via Supabase dashboard or psql

Manual Migration Option:

If the script doesn't work, apply via Supabase SQL Editor:

Go to Supabase Dashboard → SQL Editor
Copy contents of backend/epgoat/infrastructure/backend/epgoat/infrastructure/database/migrations/011_add_dual_source_team_system.sql
Paste and execute

Step 2: Seed Teams and Aliases

Populate the database with 62 canonical teams and 200+ aliases:

python utilities/seed_teams_and_aliases.py

Expected Output:

Starting team and alias seeding...
Seeding 62 teams and 233 aliases...
  ✓ Created team: Boston Celtics (id=1)
  ✓ Created team: Brooklyn Nets (id=2)
  ...
✓ Successfully seeded 62 teams and 233 aliases

What This Does: - Creates canonical team records (30 NBA + 32 NFL) - Adds team aliases with priority scoring - Sets up normalized names for fuzzy matching

Step 3: Sync API IDs from TheSportsDB and ESPN

Option A: Quick IPTV Seeding (Recommended for IPTV Services)

For IPTV services, use the convenience script to seed only major leagues:

cd backend/epgoat/utilities

# Dry run first (preview without changes)
./seed_iptv_teams.sh

# Execute actual seeding
./seed_iptv_teams.sh --execute

What This Seeds: - Basketball: NBA, WNBA, NCAAB - American Football: NFL, NCAAF - Hockey: NHL - Baseball: MLB - Soccer: MLS, English Premier League, German Bundesliga, Spanish La Liga, Italian Serie A, French Ligue 1

Result: ~470 teams from major IPTV leagues. Minor/international leagues and UFC/WWE fighters are NOT seeded (discovered on-demand).

Option B: Manual Sync (Full Control)

Populate thesportsdb_team_id and espn_team_id columns:

# Sync all sports (recommended)
python utilities/sync_teams_from_apis.py --all-sports

# Or sync specific sport (all leagues)
python utilities/sync_teams_from_apis.py --sport Basketball
python utilities/sync_teams_from_apis.py --sport "American Football"

# Or sync specific leagues within a sport (recommended for IPTV)
python utilities/sync_teams_from_apis.py --sport Basketball --league NBA --league WNBA --league NCAAB
python utilities/sync_teams_from_apis.py --sport "American Football" --league NFL --league NCAAF
python utilities/sync_teams_from_apis.py --sport Soccer --league MLS --league "English Premier League"

Expected Output:

============================================================
Team Sync Summary (Dynamic Discovery)
============================================================
Leagues discovered: 154
Teams processed: 188
TheSportsDB IDs added: 62
ESPN IDs added: 30
Teams not found in DB: 128
Errors: 0
============================================================

What This Does: - Dynamically discovers 150+ leagues from APIs - Matches API teams to canonical teams by normalized name - Updates thesportsdb_team_id and espn_team_id columns - No hardcoding - all leagues discovered automatically

Step 4: Migrate Existing Events (If Applicable)

If you have existing events without team FKs:

# Preview changes (recommended first)
python utilities/migrate_event_teams.py --dry-run

# Apply migration
python utilities/migrate_event_teams.py

# Migrate specific league
python utilities/migrate_event_teams.py --league NBA

Expected Output:

============================================================
Event Migration Summary
============================================================
Mode: LIVE
Total events found: 150
Events processed: 150
Events updated: 145
Events skipped: 5
Teams resolved: 290
Teams not found: 10
Errors: 0
============================================================

What This Does: - Parses team names from event_name - Resolves teams using TeamAliasIndex - Updates home_team_id and away_team_id columns - Handles multiple naming formats (vs, @, at)

Verification

Check Teams Table

python -c "
from supabase import create_client
import os

client = create_client(
    os.getenv('SUPABASE_URL'),
    os.getenv('SUPABASE_SERVICE_ROLE_KEY')
)

result = client.table('teams').select('id, canonical_name, league, thesportsdb_team_id, espn_team_id').limit(10).execute()

for team in result.data:
    print(f\"{team['canonical_name']:30} | TSDB: {team['thesportsdb_team_id'] or 'None':8} | ESPN: {team['espn_team_id'] or 'None':8}\")
"

Check Team Aliases

python -c "
from supabase import create_client
import os

client = create_client(
    os.getenv('SUPABASE_URL'),
    os.getenv('SUPABASE_SERVICE_ROLE_KEY')
)

result = client.table('team_aliases').select('alias, priority, source').order('priority', desc=True).limit(10).execute()

for alias in result.data:
    print(f\"{alias['alias']:30} | Priority: {alias['priority']} | Source: {alias['source']}\")
"

Troubleshooting

"No teams need seeding"

Already seeded. To re-seed:

-- In Supabase SQL Editor
DELETE FROM team_aliases;
DELETE FROM teams;

Then run seed script again.

"Teams not found in DB"

The sync script found teams in APIs that aren't in your canonical teams table yet. This is normal - you can:

Run sync_teams_from_apis.py periodically to catch new teams
Manually add missing teams to seed script
Use Step 2 (Full-featured sync) to auto-create placeholder teams

Migration Script Shows Errors

Check: - Environment variables are loaded (echo $SUPABASE_URL) - Database credentials are correct - Network connectivity to Supabase

"Could not parse teams from event"

Event name doesn't match expected patterns. Update parse_team_names_from_event() in utilities/migrate_event_teams.py to handle your format.

Maintenance

Weekly: Sync New Teams

# Cron job: Every Sunday at 2 AM
0 2 * * 0 cd /app/backend/epgoat && python utilities/sync_teams_from_apis.py --all-sports

Monthly: Full Audit

# Check for missing API IDs
python utilities/sync_teams_from_apis.py --all-sports

# Verify all events have team FKs
python utilities/migrate_event_teams.py --dry-run

Architecture Notes

Why Two API Sources?

TheSportsDB: Comprehensive global coverage, rich metadata
ESPN: Real-time US sports, high accuracy for major leagues
Deduplication: Merge events from both sources using team IDs

Team Resolution Strategy

Normalize input team name (lowercase, remove special chars)
Query team_aliases table by alias_normalized
Return team with highest priority match
Cache result for performance

Performance

TeamAliasIndex: In-memory caching for 10x faster lookups
Batch Processing: Sync processes 100+ teams/second
Idempotent: Safe to run scripts multiple times

Last Updated: 2025-11-07 Author: Claude (CTO) Status: Production Ready

Team System Setup Guide

Prerequisites

Quick Start

Step-by-Step Setup

Step 1: Apply Database Migration (One-time)

Step 2: Seed Teams and Aliases

Step 3: Sync API IDs from TheSportsDB and ESPN

Step 4: Migrate Existing Events (If Applicable)

Verification

Check Teams Table

Check Team Aliases

Troubleshooting

"No teams need seeding"

"Teams not found in DB"

Migration Script Shows Errors

"Could not parse teams from event"

Maintenance

Weekly: Sync New Teams

Monthly: Full Audit

Architecture Notes

Why Two API Sources?

Team Resolution Strategy

Performance

Related Documentation