Database Schema Documentation

Auto-Generated: 2025-11-12 13:15 Database: Supabase PostgreSQL (Production) Total Tables: 53

_migrations
access_keys
api_call_failures
api_leagues_canonical
api_sports_canonical
audit_log
channel_families
channel_names
channel_patterns
confidence_metrics
credential_access_log
daily_metrics
epg_data
epg_file_cache
espn_league_mappings
espn_sport_mappings
event_identifiers
event_participants
events
family_league_mappings
invoices
key_access_logs
learned_aliases
learned_patterns
llm_parse_cache
match_cache
match_overrides
match_verifications
parsed_data
participant_aliases
participants
pattern_performance
provider_credentials
provider_health_status
provider_metrics
provider_patterns
providers
subscription_events
successful_matches
team_aliases
team_discovery_cache
team_pairings
team_sync_audit_log
team_sync_auto_resolution_rules
team_sync_conflicts
team_sync_runs
teams
tvg_id_mappings
unmatched_channels
user_subscriptions
users
vod_filter_patterns
webhook_events

_migrations

Used In

backend/epgoat/infrastructure/database/migration_runner.py

Schema

Column	Type	Constraints	Default
`applied_at`	DATETIME	NOT NULL	CURRENT_TIMESTAMP
`migration_name`	TEXT	NOT NULL	-
`migration_number`	INTEGER	PRIMARY KEY	-

access_keys

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → user_subscriptions.id ON DELETE CASCADE	-
`abuse_details`	TEXT	-	-
`abuse_flags`	INTEGER	-	0
`created_at`	TEXT	NOT NULL	(datetime('now'))
`description`	TEXT	-	-
`expires_at`	TEXT	-	-
`id`	INTEGER	PRIMARY KEY	-
`key_type`	TEXT	NOT NULL	'consumer'
`key_uuid`	TEXT	NOT NULL, UNIQUE	-
`last_abuse_flag_at`	TEXT	-	-
`last_used_at`	TEXT	-	-
`provider_ids`	TEXT	NOT NULL	-
`status`	TEXT	NOT NULL	'active'
`subscription_id`	INTEGER	NOT NULL	-
`suspended_at`	TEXT	-	-
`suspended_reason`	TEXT	-	-
`total_requests`	INTEGER	-	0
`updated_at`	TEXT	NOT NULL	(datetime('now'))
`user_id`	INTEGER	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_keys_uuid`	`key_uuid`	BTREE
`idx_keys_user`	`user_id`	BTREE
`idx_keys_subscription`	`subscription_id`	BTREE
`idx_keys_status`	`status`	BTREE

Triggers

update_access_keys_timestamp

Relationships

FOREIGN references user_subscriptions.id

api_call_failures

Purpose

System-wide API failure tracking with automatic GitHub issue creation

Used In

backend/epgoat/infrastructure/clients/tracked_api_clients.py
backend/epgoat/infrastructure/error_handling/__init__.py
backend/epgoat/infrastructure/error_handling/api_decorators.py
backend/epgoat/infrastructure/error_handling/api_error_handler.py

Schema

Column	Type	Constraints	Default	Description
`api_name`	TEXT	NOT NULL	-	API identifier: "thesportsdb", "espn", "supabase", "anthropic", etc.
`created_at`	TIMESTAMPTZ	-	NOW()
`deployment_version`	TEXT	-	-
`endpoint`	TEXT	NOT NULL	-	API endpoint path (e.g., "/search_teams.php", "/v2/scoreboard")
`environment`	TEXT	-	'production'
`error_code`	TEXT	-	-
`error_message`	TEXT	NOT NULL	-
`error_type`	TEXT	NOT NULL	-	Exception class name (e.g., "HTTPError", "TimeoutError", "ConnectionError")
`first_occurred_at`	TIMESTAMPTZ	-	NOW()
`fix_version`	TEXT	-	-	Git tag/commit SHA where fix was deployed - enables verification
`github_issue_created_at`	TIMESTAMPTZ	-	-
`github_issue_number`	INTEGER	-	-	GitHub issue number created for this failure (NULL until created)
`github_issue_url`	TEXT	-	-
`http_method`	TEXT	-	'GET'
`http_status`	INTEGER	-	-
`id`	BIGSERIAL	PRIMARY KEY	-
`last_occurred_at`	TIMESTAMPTZ	-	NOW()
`occurrence_count`	INTEGER	-	1	Number of times this exact error occurred (incremented on duplicates)
`request_body`	TEXT	-	-
`request_headers`	JSONB	-	-
`request_params`	JSONB	-	-	Full request parameters as JSON - sanitize sensitive data before storing
`resolution_notes`	TEXT	-	-
`resolution_status`	IN	-	-	open
`resolved_at`	TIMESTAMPTZ	-	-
`resolved_by`	TEXT	-	-
`server_hostname`	TEXT	-	-
`stack_trace`	TEXT	-	-
`updated_at`	TIMESTAMPTZ	-	NOW()

Indexes

Index Name	Columns	Type
`idx_api_failures_api_name`	`api_name`	BTREE
`idx_api_failures_endpoint`	`endpoint`	BTREE
`idx_api_failures_error_type`	`error_type`	BTREE
`idx_api_failures_status`	`resolution_status`	BTREE
`idx_api_failures_github_issue`	`github_issue_number`	BTREE
`idx_api_failures_open`	`resolution_status`	BTREE
`idx_api_failures_created_at`	`created_at DESC`	BTREE
`idx_api_failures_last_occurred`	`last_occurred_at DESC`	BTREE
`idx_api_failures_occurrence_count`	`occurrence_count DESC`	BTREE
`idx_api_failures_dedup`	`api_name, endpoint, error_type, resolution_status`	BTREE

api_leagues_canonical

Purpose

Canonical leagues from TheSportsDB - source of truth for all API operations

Used In

backend/epgoat/infrastructure/error_handling/api_error_handler.py
backend/epgoat/utilities/api_operations/discover_espn_mappings.py
backend/epgoat/utilities/data_management/bootstrap_canonical_data.py
backend/epgoat/utilities/data_management/sync_canonical_data.py

Schema

Column	Type	Constraints	Default	Description
`canonical_name`	TEXT	NOT NULL, UNIQUE	-	Canonical league name (e.g., "NFL", "NBA", "English Premier League")
`country`	TEXT	-	-	Primary country (e.g., "United States", "England") - leagues can span multiple countries
`country_code`	TEXT	-	-
`created_at`	TIMESTAMPTZ	-	NOW()
`description`	TEXT	-	-
`discovered_at`	TIMESTAMPTZ	-	NOW()
`discovery_source`	TEXT	-	'thesportsdb'
`end_year`	INTEGER	-	-
`id`	BIGSERIAL	PRIMARY KEY	-
`is_active`	BOOLEAN	-	TRUE	FALSE if league has ended/dissolved. Check end_year for termination date.
`is_verified`	BOOLEAN	-	FALSE	TRUE if human verified, FALSE if auto-discovered. Review low-confidence entries.
`logo_url`	TEXT	-	-
`region`	TEXT	-	-
`sport_id`	BIGINT	NOT NULL, FK → api_sports_canonical.id ON DELETE CASCADE	-	Foreign key to api_sports_canonical - each league belongs to exactly one sport
`start_year`	INTEGER	-	-
`thesportsdb_league_id`	TEXT	-	-	TheSportsDB league ID (e.g., "4391" for NFL) - required for API queries
`thesportsdb_name`	TEXT	-	-
`updated_at`	TIMESTAMPTZ	-	NOW()
`verified_at`	TIMESTAMPTZ	-	-
`verified_by`	TEXT	-	-
`website_url`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_api_leagues_canonical_name`	`canonical_name`	BTREE
`idx_api_leagues_sport_id`	`sport_id`	BTREE
`idx_api_leagues_thesportsdb_id`	`thesportsdb_league_id`	BTREE
`idx_api_leagues_country`	`country`	BTREE
`idx_api_leagues_active`	`is_active`	BTREE
`idx_api_leagues_verified`	`is_verified`	BTREE
`idx_api_leagues_sport_country`	`sport_id, country`	BTREE
`idx_api_leagues_created_at`	`created_at DESC`	BTREE

Relationships

sport_id references api_sports_canonical.id

api_sports_canonical

Purpose

Canonical sports from TheSportsDB - source of truth for all API operations

Used In

backend/epgoat/infrastructure/error_handling/api_error_handler.py
backend/epgoat/utilities/api_operations/discover_espn_mappings.py
backend/epgoat/utilities/data_management/bootstrap_canonical_data.py
backend/epgoat/utilities/data_management/sync_canonical_data.py

Schema

Column	Type	Constraints	Default	Description
`canonical_name`	TEXT	NOT NULL, UNIQUE	-	Canonical sport name (e.g., "American Football", "Basketball", "Ice Hockey")
`created_at`	TIMESTAMPTZ	-	NOW()
`description`	TEXT	-	-
`discovered_at`	TIMESTAMPTZ	-	NOW()
`discovery_source`	TEXT	-	'thesportsdb'	How this sport was discovered: "thesportsdb", "bootstrap", "sync", "manual"
`icon_url`	TEXT	-	-
`id`	BIGSERIAL	PRIMARY KEY	-
`is_verified`	BOOLEAN	-	FALSE	TRUE if human verified, FALSE if auto-discovered. Review low-confidence entries.
`thesportsdb_name`	TEXT	-	-
`thesportsdb_sport_id`	TEXT	-	-	TheSportsDB sport ID if available (some APIs return string IDs)
`updated_at`	TIMESTAMPTZ	-	NOW()
`verified_at`	TIMESTAMPTZ	-	-
`verified_by`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_api_sports_canonical_name`	`canonical_name`	BTREE
`idx_api_sports_thesportsdb_id`	`thesportsdb_sport_id`	BTREE
`idx_api_sports_verified`	`is_verified`	BTREE
`idx_api_sports_discovery_source`	`discovery_source`	BTREE
`idx_api_sports_created_at`	`created_at DESC`	BTREE

audit_log

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/repositories/base_repository.py
backend/epgoat/infrastructure/services/team_sync_scheduler.py
backend/epgoat/services/matching/match_manager.py

Schema

Column	Type	Constraints	Default
`FOREIGN`	KEY	FK → users.id ON DELETE SET NULL	-
`action`	TEXT	NOT NULL	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`entity_id`	INTEGER	-	-
`entity_type`	TEXT	NOT NULL	-
`id`	INTEGER	PRIMARY KEY	-
`metadata`	TEXT	-	-
`new_value`	TEXT	-	-
`old_value`	TEXT	-	-
`provider_id`	INTEGER	-	-
`user_id`	INTEGER	-	-

Indexes

Index Name	Columns	Type
`idx_audit_action`	`action`	BTREE
`idx_audit_entity`	`entity_type, entity_id`	BTREE
`idx_audit_created`	`created_at DESC`	BTREE
`idx_audit_provider`	`provider_id`	BTREE

Relationships

FOREIGN references users.id

channel_families

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/repositories/base_repository.py
backend/epgoat/services/channels/family_discovery.py
backend/epgoat/tests/test_family_discovery.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → providers.id ON DELETE CASCADE	-
`active`	BOOLEAN	-	1
`created_at`	TEXT	NOT NULL	(datetime('now'))
`description`	TEXT	-	-
`family_name`	TEXT	NOT NULL	-
`family_pattern`	TEXT	NOT NULL	-
`id`	INTEGER	PRIMARY KEY	-
`match_behavior`	TEXT	NOT NULL	'single_event'
`priority`	INTEGER	-	0
`provider_id`	INTEGER	NOT NULL	-
`updated_at`	TEXT	NOT NULL	(datetime('now'))

Indexes

Index Name	Columns	Type
`idx_families_provider`	`provider_id`	BTREE
`idx_families_active`	`active`	BTREE
`idx_families_priority`	`priority DESC`	BTREE

Triggers

update_channel_families_timestamp

Relationships

FOREIGN references providers.id

channel_names

Purpose

One record per unique channel per provider

Used In

backend/epgoat/application/epg_generator.py
backend/epgoat/cli/cleanup_onboarding.py
backend/epgoat/domain/xmltv.py
backend/epgoat/services/providers/provider_onboarding_service.py
backend/epgoat/tests/parsers/test_channel_parser.py
...and 9 more files

Schema

Column	Type	Constraints	Default	Description
`channel_name`	TEXT	NOT NULL	-
`channel_name_normalized`	TEXT	NOT NULL	-	Lowercase, special chars removed, for deduplication
`created_at`	TIMESTAMPTZ	-	NOW()
`first_seen`	TIMESTAMPTZ	-	NOW()
`group_title`	TEXT	-	-
`id`	BIGSERIAL	PRIMARY KEY	-
`last_seen`	TIMESTAMPTZ	-	NOW()
`provider_id`	BIGINT	NOT NULL, FK → providers.id	-
`record_status`	TEXT	CHECK(record_status IN ('active', 'archived', 'deleted')	'active'	Soft delete: active, archived, deleted
`stream_url`	TEXT	-	-
`tvg_id`	TEXT	-	-
`tvg_logo`	TEXT	-	-
`tvg_name`	TEXT	-	-
`updated_at`	TIMESTAMPTZ	-	NOW()

Indexes

Index Name	Columns	Type
`idx_channel_names_provider`	`provider_id`	BTREE
`idx_channel_names_status`	`record_status`	BTREE
`idx_channel_names_tvg_id`	`tvg_id`	BTREE

Triggers

update_channel_names_updated_at

Relationships

provider_id references providers.id

channel_patterns

Purpose

Channel name patterns discovered during provider onboarding. Stores regex patterns that identify sports channels (e.g., "NBA \d+ :"). Used for filtering live M3U playlists during EPG generation.

Used In

backend/epgoat/application/epg_generator.py
backend/epgoat/services/channels/pattern_loading_service.py
backend/epgoat/services/matching/match_learner.py
backend/epgoat/services/providers/provider_config_manager.py
backend/epgoat/utilities/diagnostics/analyze_mismatches.py

Schema

Column	Type	Constraints	Default	Description
`CHECK`	UNKNOWN	-	-
`confidence`	REAL	NOT NULL	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`discovered_at`	TIMESTAMPTZ	NOT NULL	NOW()
`example_channel`	TEXT	-	-
`example_channels`	JSONB	-	-
`frequency`	INTEGER	NOT NULL	-	Number of channels matching this pattern. Helps identify most common/important patterns.
`id`	SERIAL	PRIMARY KEY	-
`is_active`	BOOLEAN	-	true
`is_league`	BOOLEAN	-	false
`last_seen`	TEXT	NOT NULL	(datetime('now'))
`last_verified_at`	TIMESTAMPTZ	-	-
`league`	TEXT	-	-
`occurrence_count`	INTEGER	-	1
`pattern`	TEXT	NOT NULL	-
`pattern_prefix`	TEXT	NOT NULL	-	Pattern prefix (e.g., "NBA", "ESPN+", "UFC"). Stable identifier that does not change daily.
`pattern_regex`	TEXT	NOT NULL	-	Compiled regex pattern (e.g., r
`pattern_type`	IN	-	-	Pattern type: numbered (sports channels like "NBA 01") or category (country codes like "FR:"). Numbered patterns are more useful for EPG matching.
`provider_id`	INTEGER	NOT NULL, FK → providers.id	-
`sport`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_channel_patterns_league`	`league`	BTREE
`idx_channel_patterns_confidence`	`confidence DESC`	BTREE
`idx_channel_patterns_occurrence`	`occurrence_count DESC`	BTREE
`idx_channel_patterns_provider_id`	`provider_id`	BTREE
`idx_channel_patterns_active`	`is_active`	BTREE
`idx_channel_patterns_pattern_type`	`pattern_type`	BTREE

Relationships

provider_id references providers.id

confidence_metrics

Used In

backend/epgoat/services/matching/match_learner.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`average_confidence`	REAL	-	0.0
`created_at`	TEXT	NOT NULL	(datetime('now'))
`date`	TEXT	NOT NULL, UNIQUE	-
`id`	INTEGER	PRIMARY KEY	-
`match_rate`	REAL	-	0.0
`successful_matches`	INTEGER	-	0
`total_attempts`	INTEGER	-	0

Indexes

Index Name	Columns	Type
`idx_confidence_metrics_date`	`date DESC`	BTREE

credential_access_log

Purpose

Audit trail of all credential access. Logs when credentials are decrypted and used, including GitHub Actions run ID for traceability.

Used In

backend/epgoat/cli/cleanup_onboarding.py
backend/epgoat/services/providers/provider_credential_service.py
backend/epgoat/services/providers/provider_onboarding_service.py

Schema

Column	Type	Constraints	Default	Description
`access_context`	TEXT	NOT NULL	-	Context of credential access: epg_generation (scheduled run), onboarding_validation (testing provider), manual_refresh (on-demand)
`accessed_at`	TIMESTAMPTZ	NOT NULL	NOW()
`error_message`	TEXT	-	-
`github_run_id`	TEXT	-	-	GitHub Actions run ID from ${{ github.run_id }} for workflow traceability
`id`	SERIAL	PRIMARY KEY	-
`provider_id`	INTEGER	NOT NULL, FK → providers.id	-
`success`	BOOLEAN	NOT NULL	false

Indexes

Index Name	Columns	Type
`idx_credential_access_log_provider_id`	`provider_id`	BTREE
`idx_credential_access_log_accessed_at`	`accessed_at DESC`	BTREE
`idx_credential_access_log_github_run_id`	`github_run_id`	BTREE

Relationships

provider_id references providers.id

daily_metrics

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`active_subscriptions`	INTEGER	-	0
`arr_cents`	INTEGER	-	0
`created_at`	TEXT	NOT NULL	(datetime('now'))
`id`	INTEGER	PRIMARY KEY	-
`llm_cost_cents`	INTEGER	-	0
`llm_matched_channels`	INTEGER	-	0
`llm_tokens_cached`	INTEGER	-	0
`llm_tokens_input`	INTEGER	-	0
`llm_tokens_output`	INTEGER	-	0
`match_rate`	REAL	-	0.0
`metric_date`	TEXT	NOT NULL, UNIQUE	-
`mrr_cents`	INTEGER	-	0
`new_signups`	INTEGER	-	0
`regex_matched_channels`	INTEGER	-	0
`revenue_cents`	INTEGER	-	0
`total_bytes_transferred`	INTEGER	-	0
`total_channels_processed`	INTEGER	-	0
`total_epg_requests`	INTEGER	-	0
`total_users`	INTEGER	-	0
`trial_subscriptions`	INTEGER	-	0
`unmatched_channels`	INTEGER	-	0
`updated_at`	TEXT	NOT NULL	(datetime('now'))

Indexes

Index Name	Columns	Type
`idx_metrics_date`	`metric_date DESC`	BTREE

epg_data

Purpose

Final output-ready EPG entries with full XMLTV fields

Used In

backend/epgoat/domain/xmltv.py
backend/epgoat/tests/test_xmltv_generator.py
backend/epgoat/tests/utilities/test_epg_invalidation_trigger.py
backend/epgoat/utilities/development/regenerate_invalidated_epg.py
backend/epgoat/utilities/diagnostics/check_supabase_schema.py
...and 2 more files

Schema

Column	Type	Constraints	Default	Description
`"away_logo":`	UNKNOWN	-	-
`"away_team":`	UNKNOWN	-	-
`"broadcaster":`	UNKNOWN	-	-
`"city":`	UNKNOWN	-	-
`"event_status":`	UNKNOWN	-	-
`"home_logo":`	UNKNOWN	-	-
`"home_team":`	UNKNOWN	-	-
`"league":`	UNKNOWN	-	-
`"league_logo":`	UNKNOWN	-	-
`"round":`	UNKNOWN	-	-
`"season":`	UNKNOWN	-	-
`"show_debug_info":`	FALSE	-	-
`"show_emoji":`	TRUE	-	-
`"show_logos":`	TRUE	-	-
`"show_venue":`	TRUE	-	-
`"sport":`	UNKNOWN	-	-
`"timezone":`	UNKNOWN	-	-
`"venue":`	UNKNOWN	-	-
`audio_quality`	TEXT	CHECK(audio_quality IN ('mono', 'stereo', 'surround', '5.1', '7.1')	-
`category`	TEXT	-	-
`channel_name_id`	BIGINT	NOT NULL, FK → channel_names.id ON DELETE CASCADE	-
`confidence`	REAL	CHECK(confidence IS NULL OR (confidence >= 0 AND confidence <= 1)	-
`content_rating`	TEXT	-	-
`created_at`	TIMESTAMPTZ	-	NOW()
`description`	TEXT	-	-
`end_time`	TIMESTAMPTZ	NOT NULL	-
`episode_num`	TEXT	-	-
`icon_url`	TEXT	-	-
`id`	BIGSERIAL	PRIMARY KEY	-
`is_live`	BOOLEAN	-	false
`is_new`	BOOLEAN	-	false
`is_premiere`	BOOLEAN	-	false
`is_repeat`	BOOLEAN	-	false
`language`	TEXT	-	'en'
`match_status`	TEXT	CHECK(match_status IN ('matched', 'partial', 'unmatched', 'manual')	-
`original_air_date`	DATE	-	-
`parsed_data_id`	BIGINT	FK → parsed_data.id ON DELETE SET NULL	-
`record_status`	TEXT	CHECK(record_status IN ('active', 'archived', 'deleted')	'active'
`sports_metadata`	JSONB	-	-	JSONB: venue, teams, logos, league, season, round, broadcaster, event_status
`star_rating`	REAL	CHECK(star_rating IS NULL OR (star_rating >= 0 AND star_rating <= 5)	-
`start_time`	TIMESTAMPTZ	NOT NULL	-
`sub_title`	TEXT	-	-
`tier_features`	JSONB	-	-	JSONB: Controls what gets rendered for each tier (emojis, logos, debug info, timezone)
`title`	TEXT	NOT NULL	-
`updated_at`	TIMESTAMPTZ	-	NOW()
`url`	TEXT	-	-
`video_quality`	TEXT	CHECK(video_quality IN ('SD', 'HD', '4K', 'UHD')	-

Indexes

Index Name	Columns	Type
`idx_epg_data_channel`	`channel_name_id`	BTREE
`idx_epg_data_time`	`start_time, end_time`	BTREE
`idx_epg_data_parsed`	`parsed_data_id`	BTREE
`idx_epg_data_status`	`match_status`	BTREE
`idx_epg_data_live`	`is_live`	BTREE

Triggers

update_epg_data_updated_at
invalidate_on_epg_data_change

Relationships

channel_name_id references channel_names.id
parsed_data_id references parsed_data.id

epg_file_cache

Purpose

Tracks every EPG file in R2 (pre-generated and on-demand)

Used In

backend/epgoat/services/core/r2_storage.py
backend/epgoat/tests/utilities/test_epg_invalidation_trigger.py
backend/epgoat/utilities/development/generate_epg_files_batch.py
backend/epgoat/utilities/development/regenerate_invalidated_epg.py
backend/epgoat/utilities/diagnostics/check_supabase_schema.py
...and 3 more files

Schema

Column	Type	Constraints	Default	Description
`access_count`	INTEGER	-	0
`created_at`	TIMESTAMPTZ	-	NOW()
`custom_config_hash`	TEXT	-	-
`file_size_bytes`	BIGINT	-	-
`generation_completed_at`	TIMESTAMPTZ	-	-
`generation_duration_ms`	INTEGER	-	-
`generation_error`	TEXT	-	-
`generation_started_at`	TIMESTAMPTZ	-	-
`generation_status`	TEXT	CHECK(generation_status IN ('pending', 'generating', 'ready', 'failed')	'pending'
`id`	BIGSERIAL	PRIMARY KEY	-
`invalidated_at`	TIMESTAMPTZ	-	-	Triggers regeneration when EPGData changes
`last_accessed_at`	TIMESTAMPTZ	-	-
`last_regenerated_at`	TIMESTAMPTZ	-	-
`promoted_at`	TIMESTAMPTZ	-	-
`promotion_status`	TEXT	CHECK(promotion_status IN ('on_demand', 'pre_generated')	'on_demand'	on_demand → pre_generated when accessed 3+ times in 24h
`provider_id`	BIGINT	NOT NULL, FK → providers.id	-
`r2_etag`	TEXT	-	-
`r2_key`	TEXT	NOT NULL, UNIQUE	-
`tier`	TEXT	NOT NULL, CHECK(tier IN ('basic', 'mid', 'top', 'custom')	-
`timezone`	TEXT	NOT NULL	-
`updated_at`	TIMESTAMPTZ	-	NOW()

Indexes

Index Name	Columns	Type
`idx_epg_cache_unique_standard`	`provider_id, tier, timezone`	UNIQUE
`idx_epg_cache_unique_custom`	`provider_id, tier, timezone, custom_config_hash`	UNIQUE
`idx_epg_cache_provider`	`provider_id`	BTREE
`idx_epg_cache_status`	`generation_status`	BTREE
`idx_epg_cache_promotion`	`promotion_status`	BTREE
`idx_epg_cache_access`	`access_count DESC, last_accessed_at DESC`	BTREE
`idx_epg_cache_invalidated`	`invalidated_at`	BTREE

Triggers

update_epg_file_cache_updated_at

Relationships

provider_id references providers.id

espn_league_mappings

Purpose

Maps ESPN-specific league names to canonical TheSportsDB leagues with confidence scoring

Used In

backend/epgoat/utilities/api_operations/discover_espn_mappings.py

Schema

Column	Type	Constraints	Default	Description
`(is_verified`	UNKNOWN	NOT NULL	-
`canonical_league_id`	BIGINT	NOT NULL, FK → api_leagues_canonical.id ON DELETE CASCADE	-	FK to api_leagues_canonical - the TheSportsDB canonical league this maps to
`confidence`	REAL	CHECK(confidence >= 0.0 AND confidence <= 1.0)	0.0	Mapping confidence: 0.0 = guess, 0.5 = fuzzy match, 1.0 = human verified
`created_at`	TIMESTAMPTZ	-	NOW()
`espn_league_id`	TEXT	-	-	ESPN internal league ID - used for API queries to ESPN
`espn_name`	TEXT	NOT NULL, UNIQUE	-	ESPN league name (e.g., "National Football League") - may differ from canonical
`espn_slug`	TEXT	-	-
`id`	BIGSERIAL	PRIMARY KEY	-
`is_verified`	BOOLEAN	-	FALSE
`last_used_at`	TIMESTAMPTZ	-	-
`mapping_method`	TEXT	-	-
`updated_at`	TIMESTAMPTZ	-	NOW()
`use_count`	INTEGER	-	0
`verification_notes`	TEXT	-	-
`verified_at`	TIMESTAMPTZ	-	-
`verified_by`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_espn_league_name`	`espn_name`	BTREE
`idx_espn_league_canonical`	`canonical_league_id`	BTREE
`idx_espn_league_espn_id`	`espn_league_id`	BTREE
`idx_espn_league_confidence`	`confidence`	BTREE
`idx_espn_league_verified`	`is_verified`	BTREE
`idx_espn_league_low_confidence`	`confidence`	BTREE
`idx_espn_league_last_used`	`last_used_at DESC`	BTREE

Relationships

canonical_league_id references api_leagues_canonical.id

espn_sport_mappings

Purpose

Maps ESPN-specific sport names to canonical TheSportsDB sports with confidence scoring

Used In

backend/epgoat/utilities/api_operations/discover_espn_mappings.py
backend/epgoat/utilities/data_management/bootstrap_canonical_data.py

Schema

Column	Type	Constraints	Default	Description
`(is_verified`	UNKNOWN	NOT NULL	-
`canonical_sport_id`	BIGINT	NOT NULL, FK → api_sports_canonical.id ON DELETE CASCADE	-	FK to api_sports_canonical - the TheSportsDB canonical sport this maps to
`confidence`	REAL	CHECK(confidence >= 0.0 AND confidence <= 1.0)	0.0	Mapping confidence: 0.0 = guess, 0.5 = fuzzy match, 1.0 = human verified
`created_at`	TIMESTAMPTZ	-	NOW()
`espn_name`	TEXT	NOT NULL, UNIQUE	-	ESPN sport name (e.g., "football", "basketball") - may differ from canonical
`espn_slug`	TEXT	-	-
`id`	BIGSERIAL	PRIMARY KEY	-
`is_verified`	BOOLEAN	-	FALSE
`last_used_at`	TIMESTAMPTZ	-	-
`mapping_method`	TEXT	-	-	How mapping was created: "manual", "fuzzy_match", "exact_match", "api_verified"
`updated_at`	TIMESTAMPTZ	-	NOW()
`use_count`	INTEGER	-	0	Number of times this mapping was used - tracks importance
`verification_notes`	TEXT	-	-
`verified_at`	TIMESTAMPTZ	-	-
`verified_by`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_espn_sport_name`	`espn_name`	BTREE
`idx_espn_sport_canonical`	`canonical_sport_id`	BTREE
`idx_espn_sport_confidence`	`confidence`	BTREE
`idx_espn_sport_verified`	`is_verified`	BTREE
`idx_espn_sport_low_confidence`	`confidence`	BTREE
`idx_espn_sport_last_used`	`last_used_at DESC`	BTREE

Relationships

canonical_sport_id references api_sports_canonical.id

event_identifiers

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/repositories/base_repository.py
backend/epgoat/infrastructure/database/repositories/event_repository.py
backend/epgoat/tests/infrastructure/database/repositories/test_base_repository.py

Schema

Column	Type	Constraints	Default
`FOREIGN`	KEY	FK → events.id ON DELETE CASCADE	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`event_id`	INTEGER	NOT NULL	-
`id`	INTEGER	PRIMARY KEY	-
`identifier_type`	TEXT	NOT NULL	-
`identifier_value`	TEXT	NOT NULL	-
`source`	TEXT	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_identifiers_event`	`event_id`	BTREE
`idx_identifiers_type_value`	`identifier_type, identifier_value`	BTREE

Relationships

FOREIGN references events.id

event_participants

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/repositories/base_repository.py
backend/epgoat/infrastructure/database/repositories/event_repository.py
backend/epgoat/tests/infrastructure/database/repositories/test_base_repository.py
backend/epgoat/tests/test_regex_matcher.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → participants.id ON DELETE CASCADE	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`event_id`	INTEGER	NOT NULL	-
`id`	INTEGER	PRIMARY KEY	-
`participant_id`	INTEGER	NOT NULL	-
`role`	TEXT	NOT NULL	-
`score`	INTEGER	-	-

Indexes

Index Name	Columns	Type
`idx_event_participants_event`	`event_id`	BTREE
`idx_event_participants_participant`	`participant_id`	BTREE

Relationships

FOREIGN references participants.id

events

Purpose

Canonical sports events from TheSportsDB API

Used In

backend/epgoat/application/epg_generator.py
backend/epgoat/cli/provider_runner/task_orchestrator.py
backend/epgoat/cli/run_provider.py
backend/epgoat/data/database_interface.py
backend/epgoat/data/enhanced_event_matcher.py
...and 107 more files

Schema

Column	Type	Constraints	Default	Description
`CHECK`	UNKNOWN	-	-
`alternate_ids`	TEXT	-	-
`away_logo_url`	TEXT	-	-
`away_team`	TEXT	-	-
`away_team_id`	BIGINT	FK → teams.id	-	FK to canonical team record
`away_team_normalized`	TEXT	-	-
`away_team_original_espn`	TEXT	-	-
`away_team_original_thesportsdb`	TEXT	-	-
`broadcaster`	TEXT	-	-
`city`	TEXT	-	-
`country`	TEXT	-	-
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()
`data_sources`	TEXT	-	-	Array of source APIs, e.g., [
`description`	TEXT	-	-
`details_fetched`	BOOLEAN	-	FALSE	TRUE if full details fetched from APIs
`details_fetched_at`	TIMESTAMPTZ	-	-
`espn_id`	TEXT	UNIQUE	-	Event ID from ESPN API
`espn_metadata`	JSONB	-	-	Complete ESPN API response (JSONB)
`event_date`	DATE	NOT NULL	-
`event_datetime`	TIMESTAMPTZ	NOT NULL	-
`event_name`	TEXT	NOT NULL	-
`event_name_normalized`	TEXT	NOT NULL	-
`event_time`	TIME	-	-
`home_logo_url`	TEXT	-	-
`home_team`	TEXT	-	-
`home_team_id`	BIGINT	FK → teams.id	-	FK to canonical team record
`home_team_normalized`	TEXT	-	-
`home_team_original_espn`	TEXT	-	-	Original team name from ESPN (audit trail)
`home_team_original_thesportsdb`	TEXT	-	-	Original team name from TheSportsDB (audit trail)
`id`	BIGSERIAL	PRIMARY KEY	-
`league`	TEXT	NOT NULL	-
`league_normalized`	TEXT	NOT NULL	-
`round`	TEXT	-	-
`season`	TEXT	-	-
`sport`	TEXT	NOT NULL	-
`status`	TEXT	CHECK(status IN ('scheduled', 'live', 'completed', 'postponed', 'cancelled')	'scheduled'
`thesportsdb_id`	TEXT	UNIQUE	-
`thesportsdb_metadata`	JSONB	-	-	Complete TheSportsDB API response (JSONB)
`thumbnail_url`	TEXT	-	-
`timezone`	TEXT	-	-
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()
`venue`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_events_datetime`	`event_datetime`	BTREE
`idx_events_league`	`league`	BTREE
`idx_events_sport`	`sport`	BTREE
`idx_events_date`	`event_date`	BTREE
`idx_events_normalized`	`event_name_normalized`	BTREE
`idx_events_datetime`	`event_datetime`	BTREE
`idx_events_league`	`league`	BTREE
`idx_events_sport`	`sport`	BTREE
`idx_events_date`	`event_date`	BTREE
`idx_events_normalized`	`event_name_normalized`	BTREE
`idx_events_espn`	`espn_id`	BTREE
`idx_events_teams`	`home_team_normalized, away_team_normalized`	BTREE
`idx_events_unfetched`	`details_fetched`	BTREE
`idx_events_home_team_id`	`home_team_id`	BTREE
`idx_events_away_team_id`	`away_team_id`	BTREE
`idx_events_teams`	`home_team_id, away_team_id`	BTREE
`idx_events_espn_id`	`espn_id`	BTREE

Triggers

update_events_timestamp

Relationships

home_team_id references teams.id
away_team_id references teams.id

family_league_mappings

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/repositories/base_repository.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → channel_families.id ON DELETE CASCADE	-
`confidence_boost`	REAL	-	0.1
`created_at`	TEXT	NOT NULL	(datetime('now'))
`family_id`	INTEGER	NOT NULL	-
`id`	INTEGER	PRIMARY KEY	-
`league`	TEXT	NOT NULL	-
`require_exact_league`	BOOLEAN	-	1
`sport`	TEXT	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_family_mappings_family`	`family_id`	BTREE
`idx_family_mappings_league`	`league`	BTREE

Relationships

FOREIGN references channel_families.id

invoices

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → user_subscriptions.id ON DELETE SET NULL	-
`amount_due_cents`	INTEGER	NOT NULL	-
`amount_paid_cents`	INTEGER	-	0
`created_at`	TEXT	NOT NULL	(datetime('now'))
`created_at_stripe`	TEXT	NOT NULL	-
`currency`	TEXT	NOT NULL	'usd'
`description`	TEXT	-	-
`due_date`	TEXT	-	-
`hosted_invoice_url`	TEXT	-	-
`id`	INTEGER	PRIMARY KEY	-
`invoice_number`	TEXT	-	-
`invoice_pdf_url`	TEXT	-	-
`metadata`	TEXT	-	-
`paid_at`	TEXT	-	-
`period_end`	TEXT	NOT NULL	-
`period_start`	TEXT	NOT NULL	-
`status`	TEXT	NOT NULL	-
`stripe_customer_id`	TEXT	NOT NULL	-
`stripe_invoice_id`	TEXT	NOT NULL, UNIQUE	-
`subscription_id`	INTEGER	-	-
`subtotal_cents`	INTEGER	NOT NULL	-
`tax_cents`	INTEGER	-	0
`total_cents`	INTEGER	NOT NULL	-
`updated_at`	TEXT	NOT NULL	(datetime('now'))
`user_id`	INTEGER	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_invoices_user`	`user_id`	BTREE
`idx_invoices_stripe`	`stripe_invoice_id`	BTREE
`idx_invoices_status`	`status`	BTREE
`idx_invoices_paid_at`	`paid_at DESC`	BTREE

Relationships

FOREIGN references user_subscriptions.id

key_access_logs

Schema

Column	Type	Constraints	Default
`FOREIGN`	KEY	FK → access_keys.id ON DELETE CASCADE	-
`accessed_at`	TEXT	NOT NULL	(datetime('now'))
`bytes_transferred`	INTEGER	-	-
`id`	INTEGER	PRIMARY KEY	-
`ip_address`	TEXT	NOT NULL	-
`key_id`	INTEGER	NOT NULL	-
`provider_slug`	TEXT	NOT NULL	-
`response_time_ms`	INTEGER	-	-
`status_code`	INTEGER	NOT NULL	-
`user_agent`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_access_logs_key`	`key_id`	BTREE
`idx_access_logs_accessed`	`accessed_at DESC`	BTREE
`idx_access_logs_ip`	`ip_address, key_id`	BTREE

Relationships

FOREIGN references access_keys.id

learned_aliases

Used In

backend/epgoat/services/matching/match_learner.py
backend/epgoat/tests/test_enhanced_matching.py
backend/epgoat/utilities/diagnostics/diagnose_match.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`alias`	TEXT	NOT NULL	-
`alias_normalized`	TEXT	NOT NULL	-
`confidence`	REAL	NOT NULL	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`id`	INTEGER	PRIMARY KEY	-
`last_seen`	TEXT	NOT NULL	(datetime('now'))
`occurrence_count`	INTEGER	-	1
`team_name`	TEXT	NOT NULL	-
`team_name_normalized`	TEXT	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_learned_aliases_team`	`team_name_normalized`	BTREE
`idx_learned_aliases_confidence`	`confidence DESC`	BTREE
`idx_learned_aliases_occurrence`	`occurrence_count DESC`	BTREE

learned_patterns

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/repositories/base_repository.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → events.id ON DELETE SET NULL	-
`approved_at`	TEXT	-	-
`approved_by`	TEXT	-	-
`confidence`	REAL	NOT NULL	0.8
`created_at`	TEXT	NOT NULL	(datetime('now'))
`discovery_method`	TEXT	NOT NULL	-
`false_positive_count`	INTEGER	-	0
`id`	INTEGER	PRIMARY KEY	-
`league`	TEXT	NOT NULL	-
`learned_from_channel`	TEXT	-	-
`learned_from_event_id`	INTEGER	-	-
`match_count`	INTEGER	-	0
`pattern_description`	TEXT	NOT NULL	-
`pattern_regex`	TEXT	NOT NULL	-
`sport`	TEXT	NOT NULL	-
`status`	TEXT	-	'candidate'
`updated_at`	TEXT	NOT NULL	(datetime('now'))

Indexes

Index Name	Columns	Type
`idx_patterns_league`	`league`	BTREE
`idx_patterns_status`	`status`	BTREE
`idx_patterns_confidence`	`confidence DESC`	BTREE

Triggers

update_learned_patterns_timestamp

Relationships

FOREIGN references events.id

llm_parse_cache

Purpose

LLM result deduplication - prevents duplicate API calls for similar channels

Used In

backend/epgoat/infrastructure/parsers/channel_parser.py
backend/epgoat/tests/parsers/test_channel_parser.py
backend/epgoat/utilities/diagnostics/check_supabase_schema.py
backend/epgoat/utilities/migrations/apply_migration_010.py
backend/epgoat/utilities/migrations/verify_migration_010_indexes.py

Schema

Column	Type	Constraints	Default	Description
`channel_name_normalized`	TEXT	NOT NULL, UNIQUE	-
`created_at`	TIMESTAMPTZ	-	NOW()
`extracted_league`	TEXT	-	-
`extracted_sport`	TEXT	-	-
`extracted_teams`	TEXT	-	-
`extracted_time`	TEXT	-	-
`id`	BIGSERIAL	PRIMARY KEY	-
`last_reused_at`	TIMESTAMPTZ	-	-
`llm_cost_cents`	REAL	-	-
`llm_model`	TEXT	-	'claude-3-haiku-20240307'
`llm_prompt`	TEXT	-	-
`llm_provider`	TEXT	-	'anthropic'
`llm_response`	JSONB	-	-
`llm_tokens_used`	INTEGER	-	-
`raw_channel_name`	TEXT	NOT NULL	-
`reuse_count`	INTEGER	-	0	Tracks cost savings: each reuse = one saved LLM call

Indexes

Index Name	Columns	Type
`idx_llm_cache_similarity`	`extracted_teams, extracted_time, extracted_league`	BTREE
`idx_llm_cache_reuse`	`reuse_count DESC`	BTREE
`idx_llm_cache_sport`	`extracted_sport`	BTREE

match_cache

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/repositories/base_repository.py
backend/epgoat/services/api/api_enrichment.py
backend/epgoat/services/enrichment/factory.py
backend/epgoat/services/enrichment/handlers/__init__.py
...and 4 more files

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → events.id ON DELETE CASCADE	-
`channel_name`	TEXT	NOT NULL	-
`channel_name_normalized`	TEXT	NOT NULL	-
`confidence`	REAL	NOT NULL	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`event_datetime`	TEXT	NOT NULL	-
`event_id`	INTEGER	NOT NULL	-
`expires_at`	TEXT	NOT NULL	-
`hit_count`	INTEGER	-	0
`id`	INTEGER	PRIMARY KEY	-
`last_hit_at`	TEXT	-	-
`match_method`	TEXT	NOT NULL	-
`provider_id`	INTEGER	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_cache_lookup`	`channel_name_normalized, provider_id`	BTREE
`idx_cache_expires`	`expires_at`	BTREE
`idx_cache_event`	`event_id`	BTREE

Relationships

FOREIGN references events.id

match_overrides

Used In

backend/epgoat/infrastructure/database/repositories/base_repository.py
backend/epgoat/infrastructure/parsers/event_matcher.py
backend/epgoat/services/matching/match_manager.py
backend/epgoat/tests/infrastructure/database/repositories/test_base_repository.py
backend/epgoat/utilities/diagnostics/manage_matches.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → events.id ON DELETE CASCADE	-
`active`	BOOLEAN	-	1
`channel_id`	TEXT	-	-
`channel_name`	TEXT	NOT NULL	-
`channel_name_normalized`	TEXT	NOT NULL	-
`confidence`	INTEGER	-	100
`created_at`	TEXT	NOT NULL	(datetime('now'))
`event_id`	INTEGER	NOT NULL	-
`event_title`	TEXT	-	-
`family`	TEXT	-	-
`id`	INTEGER	PRIMARY KEY	-
`notes`	TEXT	-	-
`target_date`	TEXT	NOT NULL	-
`updated_at`	TEXT	NOT NULL	(datetime('now'))
`verified_by`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_match_overrides_channel_date`	`channel_name_normalized, target_date`	BTREE
`idx_match_overrides_active`	`active`	BTREE
`idx_match_overrides_family`	`family`	BTREE
`idx_match_overrides_target_date`	`target_date`	BTREE

Triggers

Relationships

FOREIGN references events.id

match_verifications

Used In

backend/epgoat/services/matching/match_manager.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → events.id ON DELETE CASCADE	-
`api_source`	TEXT	-	-
`channel_name`	TEXT	NOT NULL	-
`channel_name_normalized`	TEXT	NOT NULL	-
`confidence`	INTEGER	-	100
`created_at`	TEXT	NOT NULL	(datetime('now'))
`event_id`	INTEGER	NOT NULL	-
`id`	INTEGER	PRIMARY KEY	-
`notes`	TEXT	-	-
`target_date`	TEXT	NOT NULL	-
`verified_by`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_match_verifications_channel_date`	`channel_name_normalized, target_date`	BTREE
`idx_match_verifications_event`	`event_id`	BTREE
`idx_match_verifications_target_date`	`target_date`	BTREE

Relationships

FOREIGN references events.id

parsed_data

Purpose

Every parse attempt, whether matched or not. JSONB stores flexible extracted fields.

Used In

backend/epgoat/domain/xmltv.py
backend/epgoat/infrastructure/parsers/event_matcher.py
backend/epgoat/services/enrichment/tests/test_context.py
backend/epgoat/tests/parsers/test_event_matcher.py
backend/epgoat/tests/test_xmltv_generator.py
...and 3 more files

Schema

Column	Type	Constraints	Default	Description
`"confidence":`	UNKNOWN	-	-
`"date":`	UNKNOWN	-	-
`"event_type":`	UNKNOWN	-	-
`"league":`	UNKNOWN	-	-
`"overall":`	UNKNOWN	-	-
`"sport":`	UNKNOWN	-	-
`"teams":`	UNKNOWN	-	-
`"time":`	UNKNOWN	-	-
`"timezone":`	UNKNOWN	-	-
`"venue":`	UNKNOWN	-	-
`channel_name_id`	BIGINT	NOT NULL, FK → channel_names.id ON DELETE CASCADE	-
`created_at`	TIMESTAMPTZ	-	NOW()
`id`	BIGSERIAL	PRIMARY KEY	-
`match_confidence`	REAL	CHECK(match_confidence IS NULL OR (match_confidence >= 0 AND match_confidence <= 1)	-
`match_method`	TEXT	CHECK(match_method IN ('regex', 'llm', 'llm_cached', 'manual_override', 'api')	-
`match_reason`	TEXT	-	-
`matched_event_id`	BIGINT	FK → events.id ON DELETE SET NULL	-
`parse_date`	DATE	NOT NULL	-
`parsed_data`	JSONB	NOT NULL	-	JSONB structure: time, timezone, date, teams[], league, sport, event_type, confidence{}
`raw_title`	TEXT	NOT NULL	-
`record_status`	TEXT	CHECK(record_status IN ('active', 'archived', 'deleted')	'active'

Indexes

Index Name	Columns	Type
`idx_parsed_data_channel`	`channel_name_id`	BTREE
`idx_parsed_data_date`	`parse_date`	BTREE
`idx_parsed_data_event`	`matched_event_id`	BTREE
`idx_parsed_data_method`	`match_method`	BTREE

Relationships

channel_name_id references channel_names.id
matched_event_id references events.id

participant_aliases

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/repositories/base_repository.py
backend/epgoat/infrastructure/database/repositories/participant_repository.py
backend/epgoat/tests/infrastructure/database/repositories/test_base_repository.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → participants.id ON DELETE CASCADE	-
`alias`	TEXT	NOT NULL	-
`alias_normalized`	TEXT	NOT NULL	-
`alias_type`	TEXT	NOT NULL	-
`confidence`	REAL	-	1.0
`created_at`	TEXT	NOT NULL	(datetime('now'))
`id`	INTEGER	PRIMARY KEY	-
`participant_id`	INTEGER	NOT NULL	-
`source`	TEXT	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_aliases_participant`	`participant_id`	BTREE
`idx_aliases_normalized`	`alias_normalized`	BTREE
`idx_aliases_unique`	`participant_id, alias_normalized`	UNIQUE

Relationships

FOREIGN references participants.id

participants

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/import_data.py
backend/epgoat/infrastructure/database/repositories/base_repository.py
backend/epgoat/infrastructure/database/repositories/event_repository.py
backend/epgoat/infrastructure/database/repositories/participant_repository.py
...and 18 more files

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`abbreviation`	TEXT	-	-
`badge_url`	TEXT	-	-
`country`	TEXT	-	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`description`	TEXT	-	-
`formed_year`	INTEGER	-	-
`id`	INTEGER	PRIMARY KEY	-
`league`	TEXT	-	-
`logo_url`	TEXT	-	-
`name`	TEXT	NOT NULL	-
`name_normalized`	TEXT	NOT NULL	-
`participant_type`	TEXT	NOT NULL	-
`short_name`	TEXT	-	-
`sport`	TEXT	NOT NULL	-
`thesportsdb_id`	TEXT	UNIQUE	-
`updated_at`	TEXT	NOT NULL	(datetime('now'))

Indexes

Index Name	Columns	Type
`idx_participants_name`	`name_normalized`	BTREE
`idx_participants_sport`	`sport`	BTREE
`idx_participants_league`	`league`	BTREE

Triggers

update_participants_timestamp

pattern_performance

Purpose

Performance tracking for individual channel patterns (NBA, ESPN+, etc). Tracks match success rate, processing time, and API efficiency per pattern.

Schema

Column	Type	Constraints	Default	Description
`avg_api_calls_per_match`	DECIMAL(5,2)	-	-	Average number of API calls (TheSportsDB, etc.) needed to resolve one channel match
`avg_match_time_ms`	INTEGER	-	-
`failed_matches`	INTEGER	NOT NULL	-
`id`	SERIAL	PRIMARY KEY	-
`league`	TEXT	-	-
`match_success_rate`	DECIMAL(5,2)	-	-
`measured_at`	TIMESTAMPTZ	NOT NULL	NOW()
`pattern_prefix`	TEXT	NOT NULL	-
`pattern_type`	IN	-	-
`provider_id`	INTEGER	NOT NULL, FK → providers.id	-
`requires_llm_resolution`	BOOLEAN	-	false	True if this pattern frequently needs LLM team name resolution (expensive)
`sport`	TEXT	-	-
`successful_matches`	INTEGER	NOT NULL	-
`total_channels`	INTEGER	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_pattern_performance_provider_id`	`provider_id`	BTREE
`idx_pattern_performance_pattern_prefix`	`pattern_prefix`	BTREE
`idx_pattern_performance_measured_at`	`measured_at DESC`	BTREE

Relationships

provider_id references providers.id

provider_credentials

Purpose

Stores encrypted M3U URL credentials for provider access. Credentials are encrypted using AES-256-GCM (Fernet) with key stored in GitHub Actions secrets.

Used In

backend/epgoat/cli/cleanup_onboarding.py
backend/epgoat/services/providers/provider_credential_service.py
backend/epgoat/services/providers/provider_onboarding_service.py

Schema

Column	Type	Constraints	Default	Description
`base_url`	TEXT	NOT NULL	-	Base URL without credentials (e.g., https://provider.com/get.php)
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()
`credential_type`	IN	-	-
`encrypted_credentials`	TEXT	NOT NULL	-	AES-256-GCM encrypted JSON containing username/password/token
`encryption_version`	INTEGER	NOT NULL	1	Encryption key version for supporting key rotation
`id`	SERIAL	PRIMARY KEY	-
`last_used_at`	TIMESTAMPTZ	-	-
`provider_id`	INTEGER	NOT NULL, UNIQUE, FK → providers.id	-
`static_params`	JSONB	-	'{}'::jsonb	Non-credential URL parameters (e.g., {"type": "m3u", "output": "ts"})
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()

Indexes

Index Name	Columns	Type
`idx_provider_credentials_provider_id`	`provider_id`	BTREE

Relationships

provider_id references providers.id

provider_health_status

Purpose

Provider health monitoring. Detects anomalies like credential failures, channel count changes, or match rate degradation.

Schema

Column	Type	Constraints	Default	Description
`alert_message`	TEXT	-	-
`alert_triggered`	BOOLEAN	-	false
`channel_count_drift`	INTEGER	-	-	Change in total channel count since last check. Large changes may indicate credential expiration or provider changes.
`checked_at`	TIMESTAMPTZ	NOT NULL	NOW()
`credential_status`	IN	-	-
`id`	SERIAL	PRIMARY KEY	-
`match_rate_drift`	DECIMAL(5,2)	-	-
`pattern_changes_detected`	INTEGER	-	-	Number of patterns that changed format (e.g., "NBA 01 :" → "NBA 01 ::"). May indicate provider naming scheme update.
`provider_id`	INTEGER	NOT NULL, FK → providers.id	-
`status`	IN	-	-

Indexes

Index Name	Columns	Type
`idx_provider_health_status_provider_id`	`provider_id`	BTREE
`idx_provider_health_status_checked_at`	`checked_at DESC`	BTREE
`idx_provider_health_status_status`	`status`	BTREE

Relationships

provider_id references providers.id

provider_metrics

Purpose

Time-series metrics for provider performance tracking. Records EPG generation stats, match success rates, and cost data per provider run.

Schema

Column	Type	Constraints	Default	Description
`channels_processed`	INTEGER	-	-
`epg_generation_time_seconds`	INTEGER	-	-
`estimated_cost_usd`	DECIMAL(10,4)	-	-
`failed_matches`	INTEGER	-	-
`id`	SERIAL	PRIMARY KEY	-
`llm_team_resolutions`	INTEGER	-	0
`match_success_rate`	DECIMAL(5,2)	-	-	Percentage of processed channels that successfully matched to events (0-100)
`measured_at`	TIMESTAMPTZ	NOT NULL	NOW()
`parseable_channels`	INTEGER	NOT NULL	-
`provider_id`	INTEGER	NOT NULL, FK → providers.id	-
`sports_channels`	INTEGER	NOT NULL	-
`successful_matches`	INTEGER	-	-
`thesportsdb_api_calls`	INTEGER	-	0
`total_channels`	INTEGER	NOT NULL	-
`tvg_id_coverage_percent`	DECIMAL(5,2)	-	-	Percentage of channels that have tvg-id attribute (0-100)

Indexes

Index Name	Columns	Type
`idx_provider_metrics_provider_id`	`provider_id`	BTREE
`idx_provider_metrics_measured_at`	`measured_at DESC`	BTREE

Relationships

provider_id references providers.id

provider_patterns

Used In

backend/epgoat/cli/cleanup_onboarding.py
backend/epgoat/infrastructure/parsers/provider_m3u_parser.py
backend/epgoat/services/channels/pattern_loading_service.py
backend/epgoat/services/providers/provider_config_manager.py
backend/epgoat/services/providers/provider_onboarding_service.py

Schema

Column	Type	Constraints	Default	Description
`FOREIGN`	KEY	FK → providers.id ON DELETE CASCADE	-
`created_at`	TEXT	-	(datetime('now'))
`id`	INTEGER	PRIMARY KEY	-
`is_active`	INTEGER	-	1
`last_matched_at`	TEXT	-	-
`llm_confidence`	FLOAT	-	NULL	LLM confidence score (0.0-1.0)
`llm_verified`	BOOLEAN	-	NULL	Whether LLM verified this pattern
`llm_verification_reason`	TEXT	-	NULL	LLM reasoning for verification result
`match_count`	INTEGER	-	0
`normalized_sport`	TEXT	-	-
`pattern`	TEXT	NOT NULL	-
`pattern_type`	TEXT	NOT NULL	'regex'
`priority`	INTEGER	-	0
`provider_id`	INTEGER	NOT NULL	-
`sport_family`	TEXT	-	-
`updated_at`	TEXT	-	(datetime('now'))

Indexes

Index Name	Columns	Type
`idx_provider_patterns_provider`	`provider_id`	BTREE
`idx_provider_patterns_active`	`is_active, provider_id`	BTREE
`idx_provider_patterns_priority`	`priority DESC`	BTREE
`idx_provider_patterns_sport`	`sport_family`	BTREE
`idx_provider_patterns_llm_verified`	`llm_verified` (WHERE llm_verified IS NOT NULL)	BTREE

Relationships

FOREIGN references providers.id

providers

Purpose

IPTV service providers with M3U sources

Used In

backend/epgoat/application/epg_generator.py
backend/epgoat/cli/cleanup_onboarding.py
backend/epgoat/cli/onboard_provider.py
backend/epgoat/cli/provider_runner/__init__.py
backend/epgoat/cli/run_provider.py
...and 35 more files

Schema

Column	Type	Constraints	Default	Description
`active`	BOOLEAN	-	true
`channel_prefix`	TEXT	-	-
`config_json`	TEXT	-	-
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()
`default_timezone`	TEXT	-	'America/New_York'
`description`	TEXT	-	-
`display_name`	TEXT	NOT NULL	-
`enable_llm_fallback`	BOOLEAN	-	true
`id`	BIGSERIAL	PRIMARY KEY	-
`identification_method`	TEXT	NOT NULL	'name_prefix'	DEPRECATED: No longer used for business logic. Kept for backward compatibility.
Originally intended to route between tvg_id and name_prefix matching strategies.
Architecture simplified to use pattern-based filtering exclusively, making this
field obsolete. Always defaults to
`is_active`	INTEGER	-	1
`last_error`	TEXT	-	-
`last_processed_at`	TIMESTAMPTZ	-	-
`m3u_password`	TEXT	-	-
`m3u_requires_auth`	BOOLEAN	-	false
`m3u_url`	TEXT	NOT NULL	-
`m3u_username`	TEXT	-	-
`name`	TEXT	NOT NULL, UNIQUE	-
`slug`	TEXT	NOT NULL, UNIQUE	-
`support_email`	TEXT	-	-
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()
`website_url`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_providers_active`	`active`	BTREE
`idx_providers_slug`	`slug`	BTREE
`idx_providers_slug`	`slug`	BTREE
`idx_providers_active`	`is_active`	BTREE
`idx_providers_active`	`active`	BTREE
`idx_providers_slug`	`slug`	BTREE

Triggers

update_providers_timestamp

subscription_events

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → users.id ON DELETE SET NULL	-
`admin_user_id`	INTEGER	-	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`event_type`	TEXT	NOT NULL	-
`id`	INTEGER	PRIMARY KEY	-
`metadata`	TEXT	-	-
`new_status`	TEXT	-	-
`previous_status`	TEXT	-	-
`subscription_id`	INTEGER	NOT NULL	-
`triggered_by`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_sub_events_subscription`	`subscription_id`	BTREE
`idx_sub_events_type`	`event_type`	BTREE
`idx_sub_events_created`	`created_at DESC`	BTREE

Relationships

FOREIGN references users.id

successful_matches

Used In

backend/epgoat/services/matching/match_learner.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`api_source`	TEXT	-	-
`channel_name`	TEXT	NOT NULL	-
`channel_name_normalized`	TEXT	NOT NULL	-
`confidence`	REAL	-	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`id`	INTEGER	PRIMARY KEY	-
`league`	TEXT	-	-
`match_method`	TEXT	-	-
`matched_team1`	TEXT	-	-
`matched_team2`	TEXT	-	-
`parsed_team1`	TEXT	-	-
`parsed_team2`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_successful_matches_league`	`league`	BTREE
`idx_successful_matches_confidence`	`confidence DESC`	BTREE
`idx_successful_matches_created`	`created_at DESC`	BTREE
`idx_successful_matches_matched_teams`	`matched_team1, matched_team2`	BTREE

team_aliases

Purpose

All team name variations for intelligent fuzzy matching

Used In

backend/epgoat/data/team_alias_index.py
backend/epgoat/infrastructure/services/team_name_resolution_service.py
backend/epgoat/services/core/team_alias_index.py
backend/epgoat/services/matching/match_learner.py
backend/epgoat/services/matching/matching_config.py
...and 2 more files

Schema

Column	Type	Constraints	Default	Description
`alias`	TEXT	NOT NULL	-	Original alias (e.g., "Lakers")
`alias_normalized`	TEXT	NOT NULL	-	Normalized for matching (e.g., "lakers")
`channel_context`	TEXT	-	-	Original channel name that led to this alias discovery
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()
`id`	BIGSERIAL	PRIMARY KEY	-
`last_matched_at`	TIMESTAMPTZ	-	-
`llm_confidence`	FLOAT	-	-	LLM confidence score (0.0-1.0), NULL if not from LLM
`llm_cost_cents`	FLOAT	-	-	Cost in cents for LLM enrichment, NULL if not from LLM
`llm_tokens_used`	INTEGER	-	-	Tokens used by LLM call, NULL if not from LLM
`match_count`	INTEGER	-	0	Times this alias led to successful match
`priority`	INTEGER	-	0	0-10 scale, higher = more trusted
`source`	TEXT	NOT NULL	-
`team_id`	BIGINT	NOT NULL, FK → teams.id ON DELETE CASCADE	-

Indexes

Index Name	Columns	Type
`idx_team_aliases_normalized`	`alias_normalized`	BTREE
`idx_team_aliases_team_id`	`team_id`	BTREE
`idx_team_aliases_source`	`source`	BTREE
`idx_team_aliases_priority`	`priority DESC`	BTREE

Relationships

team_id references teams.id

team_discovery_cache

Purpose

LLM-powered team name enrichment cache - converts abbreviations/nicknames to canonical names

Used In

backend/epgoat/infrastructure/services/team_enrichment_service.py
backend/epgoat/utilities/diagnostics/analyze_team_discovery.py
backend/epgoat/utilities/migrations/migrate_team_discovery_to_aliases.py

Schema

Column	Type	Constraints	Default	Description
`canonical_team_name`	TEXT	NOT NULL	-	Canonical team name returned by LLM (e.g., "Galatasaray S.K.", "Los Angeles Lakers", "Boston Celtics")
`channel_context`	TEXT	NOT NULL	-	Full channel name for context (e.g., "Turkish Basketball: GSK vs FBU 8:00 PM")
`confidence`	FLOAT	-	-	LLM confidence score (0-1) - reject entries with confidence < 0.5
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()
`id`	BIGSERIAL	PRIMARY KEY	-
`last_reused_at`	TIMESTAMPTZ	-	-
`league`	TEXT	-	-
`llm_cost_cents`	FLOAT	-	-
`llm_model`	TEXT	-	'claude-3-haiku-20240307'
`llm_prompt`	TEXT	-	-
`llm_provider`	TEXT	-	'anthropic'
`llm_response`	JSONB	-	-	Full JSON response from Haiku including canonical name, league, sport, confidence
`llm_tokens_used`	INTEGER	-	-
`raw_team_name`	TEXT	NOT NULL	-	Raw team name from channel (e.g., "GSK", "Lakers", "Celtiks")
`reuse_count`	INTEGER	-	0	Number of times this cache entry was reused - tracks cost savings
`sport`	TEXT	-	-
`team_id`	BIGINT	FK → teams.id	-	Foreign key to teams table - set after team is created via API lookup

Indexes

Index Name	Columns	Type
`idx_team_discovery_raw_name`	`raw_team_name`	BTREE
`idx_team_discovery_canonical`	`canonical_team_name`	BTREE
`idx_team_discovery_team_id`	`team_id`	BTREE
`idx_team_discovery_league_sport`	`league, sport`	BTREE
`idx_team_discovery_created_at`	`created_at DESC`	BTREE

Relationships

team_id references teams.id

team_pairings

Used In

backend/epgoat/services/matching/match_learner.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`id`	INTEGER	PRIMARY KEY	-
`last_seen`	TEXT	NOT NULL	(datetime('now'))
`league`	TEXT	NOT NULL	-
`occurrence_count`	INTEGER	-	1
`team1`	TEXT	NOT NULL	-
`team1_normalized`	TEXT	NOT NULL	-
`team2`	TEXT	NOT NULL	-
`team2_normalized`	TEXT	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_team_pairings_teams`	`team1_normalized, team2_normalized`	BTREE
`idx_team_pairings_league`	`league`	BTREE
`idx_team_pairings_occurrence`	`occurrence_count DESC`	BTREE

team_sync_audit_log

Used In

backend/epgoat/infrastructure/services/team_sync_scheduler.py

Schema

Column	Type	Constraints	Default
`'alias_added',`	UNKNOWN	-	-
`'conflict_detected',`	UNKNOWN	-	-
`'team_created',`	UNKNOWN	-	-
`action_type`	TEXT	NOT NULL	-
`changes`	TEXT	-	-
`conflict_id`	INTEGER	FK → team_sync_conflicts.id ON DELETE SET NULL	-
`entity_id`	INTEGER	NOT NULL	-
`entity_type`	TEXT	NOT NULL, CHECK(entity_type IN ('team', 'alias', 'conflict')	-
`id`	INTEGER	PRIMARY KEY	-
`notes`	TEXT	-	-
`performed_at`	DATETIME	NOT NULL	CURRENT_TIMESTAMP
`performed_by`	TEXT	-	-
`sync_run_id`	TEXT	FK → team_sync_runs.id ON DELETE CASCADE	-
`team_id`	INTEGER	FK → teams.id ON DELETE SET NULL	-

Indexes

Index Name	Columns	Type
`idx_team_sync_audit_log_sync_run`	`sync_run_id`	BTREE
`idx_team_sync_audit_log_team`	`team_id`	BTREE
`idx_team_sync_audit_log_performed`	`performed_at DESC`	BTREE
`idx_team_sync_audit_log_action`	`action_type`	BTREE
`idx_team_sync_audit_log_sync_run`	`sync_run_id`	BTREE
`idx_team_sync_audit_log_team`	`team_id`	BTREE
`idx_team_sync_audit_log_performed`	`performed_at DESC`	BTREE
`idx_team_sync_audit_log_action`	`action_type`	BTREE

Relationships

sync_run_id references team_sync_runs.id
conflict_id references team_sync_conflicts.id
team_id references teams.id

team_sync_auto_resolution_rules

Used In

backend/epgoat/infrastructure/services/team_sync_conflict_api.py
backend/epgoat/infrastructure/services/team_sync_conflict_resolver.py

Schema

Column	Type	Constraints	Default
`'equals',`	UNKNOWN	-	-
`'merge',`	UNKNOWN	-	-
`action`	TEXT	NOT NULL	-
`condition_field`	TEXT	NOT NULL, CHECK(e.g., 'api_source', 'severity')	-
`condition_operator`	TEXT	NOT NULL	-
`condition_value`	TEXT	NOT NULL	-
`conflict_type`	TEXT	NOT NULL	-
`created_at`	DATETIME	NOT NULL	CURRENT_TIMESTAMP
`created_by`	TEXT	-	-
`description`	TEXT	NOT NULL	-
`enabled`	BOOLEAN	NOT NULL	1
`id`	INTEGER	PRIMARY KEY	-
`priority`	INTEGER	NOT NULL	100

team_sync_conflicts

Used In

backend/epgoat/infrastructure/services/team_sync_conflict_api.py
backend/epgoat/infrastructure/services/team_sync_conflict_detector.py
backend/epgoat/infrastructure/services/team_sync_conflict_resolver.py
backend/epgoat/utilities/data_management/sync_teams_from_apis.py

Schema

Column	Type	Constraints	Default
`'alias_conflict'`	UNKNOWN	-	-
`'api_id_mismatch',`	UNKNOWN	-	-
`'auto_resolved',`	UNKNOWN	-	-
`'canonical_name_conflict',`	UNKNOWN	-	-
`'create_new',`	UNKNOWN	-	-
`'duplicate_normalized_name',`	UNKNOWN	-	-
`'ignore',`	UNKNOWN	-	-
`'ignored'`	UNKNOWN	-	-
`'manual_edit'`	UNKNOWN	-	-
`'merge',`	UNKNOWN	-	-
`'pending',`	UNKNOWN	-	-
`'resolved',`	UNKNOWN	-	-
`'update_existing',`	UNKNOWN	-	-
`api_source`	TEXT	NOT NULL, CHECK(api_source IN ('thesportsdb', 'espn')	-
`conflict_field`	TEXT	NOT NULL	-
`conflict_type`	TEXT	NOT NULL	-
`conflict_value`	TEXT	NOT NULL	-
`detected_at`	DATETIME	NOT NULL	CURRENT_TIMESTAMP
`existing_team_id`	INTEGER	FK → teams.id ON DELETE CASCADE	-
`id`	INTEGER	PRIMARY KEY	-
`new_team_data`	TEXT	NOT NULL	-
`resolution_action`	TEXT	-	-
`resolution_notes`	TEXT	-	-
`resolved_at`	DATETIME	-	-
`resolved_by`	TEXT	-	-
`severity`	TEXT	NOT NULL, CHECK(severity IN ('low', 'medium', 'high', 'critical')	'medium'
`status`	TEXT	NOT NULL	'pending'
`sync_run_id`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_team_sync_conflicts_status`	`status`	BTREE
`idx_team_sync_conflicts_type`	`conflict_type`	BTREE
`idx_team_sync_conflicts_team`	`existing_team_id`	BTREE
`idx_team_sync_conflicts_sync_run`	`sync_run_id`	BTREE
`idx_team_sync_conflicts_detected`	`detected_at DESC`	BTREE
`idx_team_sync_conflicts_status`	`status`	BTREE
`idx_team_sync_conflicts_type`	`conflict_type`	BTREE
`idx_team_sync_conflicts_team`	`existing_team_id`	BTREE
`idx_team_sync_conflicts_sync_run`	`sync_run_id`	BTREE
`idx_team_sync_conflicts_detected`	`detected_at DESC`	BTREE

Relationships

existing_team_id references teams.id

team_sync_runs

Used In

backend/epgoat/infrastructure/services/team_sync_conflict_api.py
backend/epgoat/infrastructure/services/team_sync_scheduler.py

Schema

Column	Type	Constraints	Default
`'running',`	UNKNOWN	-	-
`api_sources`	TEXT	NOT NULL	-
`auto_resolve_enabled`	BOOLEAN	NOT NULL	0
`completed_at`	DATETIME	-	-
`conflicts_detected`	INTEGER	NOT NULL	0
`dry_run`	BOOLEAN	NOT NULL	0
`error_log`	TEXT	-	-
`errors`	INTEGER	NOT NULL	0
`id`	TEXT	PRIMARY KEY	-
`league_filter`	TEXT	-	-
`sport_filter`	TEXT	-	-
`started_at`	DATETIME	NOT NULL	CURRENT_TIMESTAMP
`status`	TEXT	NOT NULL	'running'
`summary`	TEXT	-	-
`teams_created`	INTEGER	NOT NULL	0
`teams_discovered`	INTEGER	NOT NULL	0
`teams_updated`	INTEGER	NOT NULL	0
`triggered_by`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_team_sync_runs_started`	`started_at DESC`	BTREE
`idx_team_sync_runs_status`	`status`	BTREE
`idx_team_sync_runs_started`	`started_at DESC`	BTREE
`idx_team_sync_runs_status`	`status`	BTREE

teams

Purpose

Canonical team records with API source IDs for intelligent matching

Used In

backend/epgoat/data/api_cache.py
backend/epgoat/data/enhanced_team_matcher.py
backend/epgoat/data/event_details_cache.py
backend/epgoat/data/services/event_matcher.py
backend/epgoat/data/team_alias_index.py
...and 83 more files

Schema

Column	Type	Constraints	Default	Description
`abbreviation`	TEXT	-	-
`canonical_name`	TEXT	NOT NULL	-	Primary team name (e.g., "Los Angeles Lakers")
`canonical_name_normalized`	TEXT	NOT NULL	-
`city`	TEXT	-	-
`country`	TEXT	-	-
`created_at`	TIMESTAMPTZ	NOT NULL	NOW()
`discovery_source`	TEXT	-	-
`espn_team_id`	TEXT	UNIQUE	-	Team ID from ESPN API
`id`	BIGSERIAL	PRIMARY KEY	-
`is_placeholder`	BOOLEAN	-	FALSE	TRUE if auto-created, needs verification
`league`	TEXT	NOT NULL	-
`sport`	TEXT	NOT NULL	-
`thesportsdb_team_id`	TEXT	UNIQUE	-	Team ID from TheSportsDB API
`updated_at`	TIMESTAMPTZ	NOT NULL	NOW()

Indexes

Index Name	Columns	Type
`idx_teams_league`	`league`	BTREE
`idx_teams_sport`	`sport`	BTREE
`idx_teams_thesportsdb_id`	`thesportsdb_team_id`	BTREE
`idx_teams_espn_id`	`espn_team_id`	BTREE
`idx_teams_is_placeholder`	`is_placeholder`	BTREE

Triggers

update_teams_updated_at

tvg_id_mappings

Used In

backend/epgoat/domain/provider_config.py
backend/epgoat/infrastructure/parsers/provider_m3u_parser.py
backend/epgoat/services/providers/provider_config_manager.py
backend/epgoat/tests/test_config_manager.py

Schema

Column	Type	Constraints	Default
`FOREIGN`	KEY	FK → providers.id ON DELETE CASCADE	-
`channel_name`	TEXT	-	-
`created_at`	TEXT	-	(datetime('now'))
`id`	INTEGER	PRIMARY KEY	-
`is_active`	INTEGER	-	1
`last_matched_at`	TEXT	-	-
`league`	TEXT	-	-
`match_count`	INTEGER	-	0
`normalized_sport`	TEXT	-	-
`provider_id`	INTEGER	NOT NULL	-
`sport_family`	TEXT	-	-
`tvg_id`	TEXT	NOT NULL	-
`tvg_id_prefix`	TEXT	-	-
`updated_at`	TEXT	-	(datetime('now'))

Indexes

Index Name	Columns	Type
`idx_tvg_id_mappings_provider`	`provider_id`	BTREE
`idx_tvg_id_mappings_tvg_id`	`tvg_id`	BTREE
`idx_tvg_id_mappings_prefix`	`tvg_id_prefix`	BTREE
`idx_tvg_id_mappings_active`	`is_active, provider_id`	BTREE
`idx_tvg_id_mappings_provider`	`provider_id`	BTREE
`idx_tvg_id_mappings_tvg_id`	`tvg_id`	BTREE
`idx_tvg_id_mappings_prefix`	`tvg_id_prefix`	BTREE
`idx_tvg_id_mappings_active`	`is_active, provider_id`	BTREE
`idx_tvg_id_mappings_provider`	`provider_id`	BTREE
`idx_tvg_id_mappings_tvg_id`	`tvg_id`	BTREE
`idx_tvg_id_mappings_prefix`	`tvg_id_prefix`	BTREE
`idx_tvg_id_mappings_active`	`is_active, provider_id`	BTREE

Relationships

FOREIGN references providers.id

unmatched_channels

Used In

backend/epgoat/infrastructure/database/clear_d1.py
backend/epgoat/infrastructure/database/repositories/base_repository.py
backend/epgoat/infrastructure/database/repositories/unmatched_channel_repository.py
backend/epgoat/infrastructure/parsers/provider_m3u_parser.py
backend/epgoat/services/channels/channel_filter_service.py
...and 7 more files

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → events.id ON DELETE SET NULL	-
`channel_name`	TEXT	NOT NULL	-
`channel_name_normalized`	TEXT	NOT NULL	-
`failure_reason`	TEXT	-	-
`family`	TEXT	-	-
`first_seen`	TEXT	NOT NULL	(datetime('now'))
`group_title`	TEXT	-	-
`id`	INTEGER	PRIMARY KEY	-
`last_seen`	TEXT	NOT NULL	(datetime('now'))
`llm_attempted`	BOOLEAN	-	0
`llm_response`	TEXT	-	-
`manual_match_confidence`	REAL	-	-
`manual_match_event_id`	INTEGER	-	-
`manual_matched_at`	TEXT	-	-
`manual_matched_by`	TEXT	-	-
`metadata`	TEXT	-	-
`occurrence_count`	INTEGER	-	1
`payload`	TEXT	-	-
`provider`	TEXT	-	-
`provider_id`	INTEGER	NOT NULL	-
`regex_stage_reached`	INTEGER	-	0
`resolution_notes`	TEXT	-	-
`resolved`	BOOLEAN	-	0
`target_date`	TEXT	-	-
`team1`	TEXT	-	-
`team2`	TEXT	-	-
`timestamp`	TEXT	-	(datetime('now'))
`tvg_id`	TEXT	-	-
`tvg_name`	TEXT	-	-

Indexes

Index Name	Columns	Type
`idx_unmatched_provider`	`provider_id`	BTREE
`idx_unmatched_resolved`	`resolved`	BTREE
`idx_unmatched_normalized`	`channel_name_normalized`	BTREE
`idx_unmatched_occurrence`	`occurrence_count DESC`	BTREE
`idx_unmatched_family`	`family`	BTREE
`idx_unmatched_target_date`	`target_date`	BTREE
`idx_unmatched_team1`	`team1`	BTREE
`idx_unmatched_provider_name`	`provider`	BTREE
`idx_unmatched_timestamp`	`timestamp DESC`	BTREE

Triggers

increment_unmatched_occurrence

Relationships

FOREIGN references events.id

user_subscriptions

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → users.id ON DELETE CASCADE	-
`amount_cents`	INTEGER	NOT NULL	-
`cancel_at_period_end`	BOOLEAN	-	0
`canceled_at`	TEXT	-	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`currency`	TEXT	NOT NULL	'usd'
`current_period_end`	TEXT	NOT NULL	-
`current_period_start`	TEXT	NOT NULL	-
`ended_at`	TEXT	-	-
`grace_period_emails_sent`	INTEGER	-	0
`grace_period_end`	TEXT	-	-
`id`	INTEGER	PRIMARY KEY	-
`plan_type`	TEXT	NOT NULL	-
`provider_packs`	TEXT	-	-
`status`	TEXT	NOT NULL	-
`stripe_customer_id`	TEXT	NOT NULL	-
`stripe_price_id`	TEXT	NOT NULL	-
`stripe_subscription_id`	TEXT	NOT NULL, UNIQUE	-
`trial_end`	TEXT	-	-
`trial_start`	TEXT	-	-
`updated_at`	TEXT	NOT NULL	(datetime('now'))
`user_id`	INTEGER	NOT NULL	-

Indexes

Index Name	Columns	Type
`idx_subscriptions_user`	`user_id`	BTREE
`idx_subscriptions_stripe`	`stripe_subscription_id`	BTREE
`idx_subscriptions_status`	`status`	BTREE
`idx_subscriptions_period_end`	`current_period_end`	BTREE

Triggers

update_user_subscriptions_timestamp

Relationships

FOREIGN references users.id

users

Used In

backend/epgoat/services/core/r2_storage.py
backend/epgoat/tests/infrastructure/database/repositories/test_base_repository.py
backend/epgoat/tests/infrastructure/database/repositories/test_security_audit.py

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`FOREIGN`	KEY	FK → users.id ON DELETE SET NULL	-
`auth0_id`	TEXT	NOT NULL, UNIQUE	-
`created_at`	TEXT	NOT NULL	(datetime('now'))
`email`	TEXT	NOT NULL, UNIQUE	-
`email_notifications`	BOOLEAN	-	1
`email_verified`	BOOLEAN	-	0
`id`	INTEGER	PRIMARY KEY	-
`last_login_at`	TEXT	-	-
`name`	TEXT	-	-
`picture_url`	TEXT	-	-
`preferred_language`	TEXT	-	'en'
`preferred_timezone`	TEXT	-	'America/New_York'
`referrer_user_id`	INTEGER	-	-
`role`	TEXT	NOT NULL	'consumer'
`signup_source`	TEXT	-	-
`status`	TEXT	NOT NULL	'active'
`suspended_at`	TEXT	-	-
`suspended_reason`	TEXT	-	-
`updated_at`	TEXT	NOT NULL	(datetime('now'))

Indexes

Index Name	Columns	Type
`idx_users_auth0`	`auth0_id`	BTREE
`idx_users_email`	`email`	BTREE
`idx_users_role`	`role`	BTREE
`idx_users_status`	`status`	BTREE

Triggers

update_users_timestamp

Relationships

FOREIGN references users.id

vod_filter_patterns

Used In

backend/epgoat/services/providers/provider_config_manager.py

Schema

Column	Type	Constraints	Default
`FOREIGN`	KEY	FK → providers.id ON DELETE CASCADE	-
`created_at`	TEXT	-	(datetime('now'))
`description`	TEXT	-	-
`id`	INTEGER	PRIMARY KEY	-
`is_active`	INTEGER	-	1
`match_count`	INTEGER	-	0
`pattern`	TEXT	NOT NULL	-
`pattern_type`	TEXT	-	'regex'
`provider_id`	INTEGER	-	-

Indexes

Index Name	Columns	Type
`idx_vod_patterns_provider`	`provider_id`	BTREE
`idx_vod_patterns_active`	`is_active`	BTREE
`idx_vod_patterns_provider`	`provider_id`	BTREE
`idx_vod_patterns_active`	`is_active`	BTREE
`idx_vod_patterns_provider`	`provider_id`	BTREE
`idx_vod_patterns_active`	`is_active`	BTREE

Relationships

FOREIGN references providers.id

webhook_events

Schema

Column	Type	Constraints	Default
`CHECK`	UNKNOWN	-	-
`created_at_stripe`	TEXT	NOT NULL	-
`event_type`	TEXT	NOT NULL	-
`id`	INTEGER	PRIMARY KEY	-
`payload`	TEXT	NOT NULL	-
`processed`	BOOLEAN	-	0
`processed_at`	TEXT	-	-
`processing_error`	TEXT	-	-
`received_at`	TEXT	NOT NULL	(datetime('now'))
`retry_count`	INTEGER	-	0
`stripe_event_id`	TEXT	NOT NULL, UNIQUE	-

Indexes

Index Name	Columns	Type
`idx_webhooks_stripe_id`	`stripe_event_id`	BTREE
`idx_webhooks_type`	`event_type`	BTREE
`idx_webhooks_processed`	`processed`	BTREE
`idx_webhooks_received`	`received_at DESC`	BTREE

Database Schema Documentation

Table of Contents

_migrations

Used In

Schema

access_keys

Schema

Indexes

Triggers

Relationships

api_call_failures

Purpose

Used In

Schema

Indexes

api_leagues_canonical

Purpose

Used In

Schema

Indexes

Relationships

api_sports_canonical

Purpose

Used In

Schema

Indexes

audit_log

Used In

Schema

Indexes

Relationships

channel_families

Used In

Schema

Indexes

Triggers

Relationships

channel_names

Purpose

Used In

Schema

Indexes

Triggers

Relationships

channel_patterns

Purpose

Used In

Schema

Indexes

Relationships

confidence_metrics

Used In

Schema

Indexes

credential_access_log

Purpose

Used In

Schema

Indexes

Relationships

daily_metrics

Schema

Indexes

epg_data

Purpose

Used In

Schema

Indexes

Triggers

Relationships

epg_file_cache

Purpose

Used In

Schema

Indexes

Triggers

Relationships

espn_league_mappings

Purpose

Used In