Schema

EPGOAT Documentation - AI Reference (Educational)

Database Schema Reference (LLM)

Status: Auto-Generated Last Updated: 2025-11-12 13:15 Database: Supabase PostgreSQL

Quick Reference

Tables

  • _migrations (3 columns) -
  • access_keys (20 columns) -
  • api_call_failures (28 columns) - System-wide API failure tracking with automatic GitHub issue creation
  • api_leagues_canonical (21 columns) - Canonical leagues from TheSportsDB - source of truth for all API operations
  • api_sports_canonical (13 columns) - Canonical sports from TheSportsDB - source of truth for all API operations
  • audit_log (11 columns) -
  • channel_families (12 columns) -
  • channel_names (14 columns) - One record per unique channel per provider
  • channel_patterns (20 columns) - 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.
  • confidence_metrics (8 columns) -
  • credential_access_log (7 columns) - Audit trail of all credential access. Logs when credentials are decrypted and used, including GitHub Actions run ID for traceability.
  • daily_metrics (23 columns) -
  • epg_data (47 columns) - Final output-ready EPG entries with full XMLTV fields
  • epg_file_cache (21 columns) - Tracks every EPG file in R2 (pre-generated and on-demand)
  • espn_league_mappings (16 columns) - Maps ESPN-specific league names to canonical TheSportsDB leagues with confidence scoring
  • espn_sport_mappings (15 columns) - Maps ESPN-specific sport names to canonical TheSportsDB sports with confidence scoring
  • event_identifiers (7 columns) -
  • event_participants (8 columns) -
  • events (42 columns) - Canonical sports events from TheSportsDB API
  • family_league_mappings (9 columns) -
  • invoices (26 columns) -
  • key_access_logs (10 columns) -
  • learned_aliases (10 columns) -
  • learned_patterns (18 columns) -
  • llm_parse_cache (16 columns) - LLM result deduplication - prevents duplicate API calls for similar channels
  • match_cache (14 columns) -
  • match_overrides (16 columns) -
  • match_verifications (12 columns) -
  • parsed_data (21 columns) - Every parse attempt, whether matched or not. JSONB stores flexible extracted fields.
  • participant_aliases (10 columns) -
  • participants (17 columns) -
  • pattern_performance (14 columns) - Performance tracking for individual channel patterns (NBA, ESPN+, etc). Tracks match success rate, processing time, and API efficiency per pattern.
  • provider_credentials (10 columns) - Stores encrypted M3U URL credentials for provider access. Credentials are encrypted using AES-256-GCM (Fernet) with key stored in GitHub Actions secrets.
  • provider_health_status (10 columns) - Provider health monitoring. Detects anomalies like credential failures, channel count changes, or match rate degradation.
  • provider_metrics (15 columns) - Time-series metrics for provider performance tracking. Records EPG generation stats, match success rates, and cost data per provider run.
  • provider_patterns (16 columns) - Provider-specific channel matching patterns with LLM verification
  • providers (22 columns) - IPTV service providers with M3U sources
  • subscription_events (11 columns) -
  • successful_matches (13 columns) -
  • team_aliases (13 columns) - All team name variations for intelligent fuzzy matching
  • team_discovery_cache (17 columns) - LLM-powered team name enrichment cache - converts abbreviations/nicknames to canonical names
  • team_pairings (10 columns) -
  • team_sync_audit_log (14 columns) -
  • team_sync_auto_resolution_rules (13 columns) -
  • team_sync_conflicts (28 columns) -
  • team_sync_runs (18 columns) -
  • teams (14 columns) - Canonical team records with API source IDs for intelligent matching
  • tvg_id_mappings (14 columns) -
  • unmatched_channels (30 columns) -
  • user_subscriptions (23 columns) -
  • users (20 columns) -
  • vod_filter_patterns (9 columns) -
  • webhook_events (11 columns) -

_migrations

Column Type Nullable Description
applied_at DATETIME No
migration_name TEXT No
migration_number 🔑 INTEGER Yes

access_keys

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → user_subscriptions KEY Yes
abuse_details TEXT Yes
abuse_flags INTEGER Yes
created_at TEXT No
description TEXT Yes
expires_at TEXT Yes
id 🔑 INTEGER Yes
key_type TEXT No
key_uuid TEXT No
last_abuse_flag_at TEXT Yes
last_used_at TEXT Yes
provider_ids TEXT No
status TEXT No
subscription_id INTEGER No
suspended_at TEXT Yes
suspended_reason TEXT Yes
total_requests INTEGER Yes
updated_at TEXT No
user_id INTEGER No

Indexes: - idx_keys_uuid on key_uuid - idx_keys_user on user_id - idx_keys_subscription on subscription_id - idx_keys_status on status

api_call_failures

Purpose: System-wide API failure tracking with automatic GitHub issue creation

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

Indexes: - idx_api_failures_api_name on api_name - idx_api_failures_endpoint on endpoint - idx_api_failures_error_type on error_type - idx_api_failures_status on resolution_status - idx_api_failures_github_issue on github_issue_number - idx_api_failures_open on resolution_status - idx_api_failures_created_at on created_at DESC - idx_api_failures_last_occurred on last_occurred_at DESC - idx_api_failures_occurrence_count on occurrence_count DESC - idx_api_failures_dedup on api_name, endpoint, error_type, resolution_status

api_leagues_canonical

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

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

Indexes: - idx_api_leagues_canonical_name on canonical_name - idx_api_leagues_sport_id on sport_id - idx_api_leagues_thesportsdb_id on thesportsdb_league_id - idx_api_leagues_country on country - idx_api_leagues_active on is_active - idx_api_leagues_verified on is_verified - idx_api_leagues_sport_country on sport_id, country - idx_api_leagues_created_at on created_at DESC

api_sports_canonical

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

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

Indexes: - idx_api_sports_canonical_name on canonical_name - idx_api_sports_thesportsdb_id on thesportsdb_sport_id - idx_api_sports_verified on is_verified - idx_api_sports_discovery_source on discovery_source - idx_api_sports_created_at on created_at DESC

audit_log

Column Type Nullable Description
FOREIGN → users KEY Yes
action TEXT No
created_at TEXT No
entity_id INTEGER Yes
entity_type TEXT No
id 🔑 INTEGER Yes
metadata TEXT Yes
new_value TEXT Yes
old_value TEXT Yes
provider_id INTEGER Yes
user_id INTEGER Yes

Indexes: - idx_audit_action on action - idx_audit_entity on entity_type, entity_id - idx_audit_created on created_at DESC - idx_audit_provider on provider_id

channel_families

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → providers KEY Yes
active BOOLEAN Yes
created_at TEXT No
description TEXT Yes
family_name TEXT No
family_pattern TEXT No
id 🔑 INTEGER Yes
match_behavior TEXT No
priority INTEGER Yes
provider_id INTEGER No
updated_at TEXT No

Indexes: - idx_families_provider on provider_id - idx_families_active on active - idx_families_priority on priority DESC

channel_names

Purpose: One record per unique channel per provider

Column Type Nullable Description
channel_name TEXT No
channel_name_normalized TEXT No Lowercase, special chars removed, for deduplication
created_at TIMESTAMPTZ Yes
first_seen TIMESTAMPTZ Yes
group_title TEXT Yes
id 🔑 BIGSERIAL Yes
last_seen TIMESTAMPTZ Yes
provider_id → providers BIGINT No
record_status TEXT Yes Soft delete: active, archived, deleted
stream_url TEXT Yes
tvg_id TEXT Yes
tvg_logo TEXT Yes
tvg_name TEXT Yes
updated_at TIMESTAMPTZ Yes

Indexes: - idx_channel_names_provider on provider_id - idx_channel_names_status on record_status - idx_channel_names_tvg_id on tvg_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.

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

Indexes: - idx_channel_patterns_league on league - idx_channel_patterns_confidence on confidence DESC - idx_channel_patterns_occurrence on occurrence_count DESC - idx_channel_patterns_provider_id on provider_id - idx_channel_patterns_active on is_active - idx_channel_patterns_pattern_type on pattern_type

confidence_metrics

Column Type Nullable Description
CHECK UNKNOWN Yes
average_confidence REAL Yes
created_at TEXT No
date TEXT No
id 🔑 INTEGER Yes
match_rate REAL Yes
successful_matches INTEGER Yes
total_attempts INTEGER Yes

Indexes: - idx_confidence_metrics_date on date DESC

credential_access_log

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

Column Type Nullable Description
access_context TEXT No Context of credential access: epg_generation (scheduled run), onboarding_validation (testing provider), manual_refresh (on-demand)
accessed_at TIMESTAMPTZ No
error_message TEXT Yes
github_run_id TEXT Yes GitHub Actions run ID from ${{ github.run_id }} for workflow traceability
id 🔑 SERIAL Yes
provider_id → providers INTEGER No
success BOOLEAN No

Indexes: - idx_credential_access_log_provider_id on provider_id - idx_credential_access_log_accessed_at on accessed_at DESC - idx_credential_access_log_github_run_id on github_run_id

daily_metrics

Column Type Nullable Description
CHECK UNKNOWN Yes
active_subscriptions INTEGER Yes
arr_cents INTEGER Yes
created_at TEXT No
id 🔑 INTEGER Yes
llm_cost_cents INTEGER Yes
llm_matched_channels INTEGER Yes
llm_tokens_cached INTEGER Yes
llm_tokens_input INTEGER Yes
llm_tokens_output INTEGER Yes
match_rate REAL Yes
metric_date TEXT No
mrr_cents INTEGER Yes
new_signups INTEGER Yes
regex_matched_channels INTEGER Yes
revenue_cents INTEGER Yes
total_bytes_transferred INTEGER Yes
total_channels_processed INTEGER Yes
total_epg_requests INTEGER Yes
total_users INTEGER Yes
trial_subscriptions INTEGER Yes
unmatched_channels INTEGER Yes
updated_at TEXT No

Indexes: - idx_metrics_date on metric_date DESC

epg_data

Purpose: Final output-ready EPG entries with full XMLTV fields

Column Type Nullable Description
"away_logo": UNKNOWN Yes
"away_team": UNKNOWN Yes
"broadcaster": UNKNOWN Yes
"city": UNKNOWN Yes
"event_status": UNKNOWN Yes
"home_logo": UNKNOWN Yes
"home_team": UNKNOWN Yes
"league": UNKNOWN Yes
"league_logo": UNKNOWN Yes
"round": UNKNOWN Yes
"season": UNKNOWN Yes
"show_debug_info": FALSE Yes
"show_emoji": TRUE Yes
"show_logos": TRUE Yes
"show_venue": TRUE Yes
"sport": UNKNOWN Yes
"timezone": UNKNOWN Yes
"venue": UNKNOWN Yes
audio_quality TEXT Yes
category TEXT Yes
channel_name_id → channel_names BIGINT No
confidence REAL Yes
content_rating TEXT Yes
created_at TIMESTAMPTZ Yes
description TEXT Yes
end_time TIMESTAMPTZ No
episode_num TEXT Yes
icon_url TEXT Yes
id 🔑 BIGSERIAL Yes
is_live BOOLEAN Yes
is_new BOOLEAN Yes
is_premiere BOOLEAN Yes
is_repeat BOOLEAN Yes
language TEXT Yes
match_status TEXT Yes
original_air_date DATE Yes
parsed_data_id → parsed_data BIGINT Yes
record_status TEXT Yes
sports_metadata JSONB Yes JSONB: venue, teams, logos, league, season, round, broadcaster, event_status
star_rating REAL Yes
start_time TIMESTAMPTZ No
sub_title TEXT Yes
tier_features JSONB Yes JSONB: Controls what gets rendered for each tier (emojis, logos, debug info, timezone)
title TEXT No
updated_at TIMESTAMPTZ Yes
url TEXT Yes
video_quality TEXT Yes

Indexes: - idx_epg_data_channel on channel_name_id - idx_epg_data_time on start_time, end_time - idx_epg_data_parsed on parsed_data_id - idx_epg_data_status on match_status - idx_epg_data_live on is_live

epg_file_cache

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

Column Type Nullable Description
access_count INTEGER Yes
created_at TIMESTAMPTZ Yes
custom_config_hash TEXT Yes
file_size_bytes BIGINT Yes
generation_completed_at TIMESTAMPTZ Yes
generation_duration_ms INTEGER Yes
generation_error TEXT Yes
generation_started_at TIMESTAMPTZ Yes
generation_status TEXT Yes
id 🔑 BIGSERIAL Yes
invalidated_at TIMESTAMPTZ Yes Triggers regeneration when EPGData changes
last_accessed_at TIMESTAMPTZ Yes
last_regenerated_at TIMESTAMPTZ Yes
promoted_at TIMESTAMPTZ Yes
promotion_status TEXT Yes on_demand → pre_generated when accessed 3+ times in 24h
provider_id → providers BIGINT No
r2_etag TEXT Yes
r2_key TEXT No
tier TEXT No
timezone TEXT No
updated_at TIMESTAMPTZ Yes

Indexes: - idx_epg_cache_unique_standard on provider_id, tier, timezone (UNIQUE) - idx_epg_cache_unique_custom on provider_id, tier, timezone, custom_config_hash (UNIQUE) - idx_epg_cache_provider on provider_id - idx_epg_cache_status on generation_status - idx_epg_cache_promotion on promotion_status - idx_epg_cache_access on access_count DESC, last_accessed_at DESC - idx_epg_cache_invalidated on invalidated_at

espn_league_mappings

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

Column Type Nullable Description
(is_verified UNKNOWN No
canonical_league_id → api_leagues_canonical BIGINT No FK to api_leagues_canonical - the TheSportsDB canonical league this maps to
confidence REAL Yes Mapping confidence: 0.0 = guess, 0.5 = fuzzy match, 1.0 = human verified
created_at TIMESTAMPTZ Yes
espn_league_id TEXT Yes ESPN internal league ID - used for API queries to ESPN
espn_name TEXT No ESPN league name (e.g., "National Football League") - may differ from canonical
espn_slug TEXT Yes
id 🔑 BIGSERIAL Yes
is_verified BOOLEAN Yes
last_used_at TIMESTAMPTZ Yes
mapping_method TEXT Yes
updated_at TIMESTAMPTZ Yes
use_count INTEGER Yes
verification_notes TEXT Yes
verified_at TIMESTAMPTZ Yes
verified_by TEXT Yes

Indexes: - idx_espn_league_name on espn_name - idx_espn_league_canonical on canonical_league_id - idx_espn_league_espn_id on espn_league_id - idx_espn_league_confidence on confidence - idx_espn_league_verified on is_verified - idx_espn_league_low_confidence on confidence - idx_espn_league_last_used on last_used_at DESC

espn_sport_mappings

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

Column Type Nullable Description
(is_verified UNKNOWN No
canonical_sport_id → api_sports_canonical BIGINT No FK to api_sports_canonical - the TheSportsDB canonical sport this maps to
confidence REAL Yes Mapping confidence: 0.0 = guess, 0.5 = fuzzy match, 1.0 = human verified
created_at TIMESTAMPTZ Yes
espn_name TEXT No ESPN sport name (e.g., "football", "basketball") - may differ from canonical
espn_slug TEXT Yes
id 🔑 BIGSERIAL Yes
is_verified BOOLEAN Yes
last_used_at TIMESTAMPTZ Yes
mapping_method TEXT Yes How mapping was created: "manual", "fuzzy_match", "exact_match", "api_verified"
updated_at TIMESTAMPTZ Yes
use_count INTEGER Yes Number of times this mapping was used - tracks importance
verification_notes TEXT Yes
verified_at TIMESTAMPTZ Yes
verified_by TEXT Yes

Indexes: - idx_espn_sport_name on espn_name - idx_espn_sport_canonical on canonical_sport_id - idx_espn_sport_confidence on confidence - idx_espn_sport_verified on is_verified - idx_espn_sport_low_confidence on confidence - idx_espn_sport_last_used on last_used_at DESC

event_identifiers

Column Type Nullable Description
FOREIGN → events KEY Yes
created_at TEXT No
event_id INTEGER No
id 🔑 INTEGER Yes
identifier_type TEXT No
identifier_value TEXT No
source TEXT No

Indexes: - idx_identifiers_event on event_id - idx_identifiers_type_value on identifier_type, identifier_value

event_participants

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → participants KEY Yes
created_at TEXT No
event_id INTEGER No
id 🔑 INTEGER Yes
participant_id INTEGER No
role TEXT No
score INTEGER Yes

Indexes: - idx_event_participants_event on event_id - idx_event_participants_participant on participant_id

events

Purpose: Canonical sports events from TheSportsDB API

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

Indexes: - idx_events_datetime on event_datetime - idx_events_league on league - idx_events_sport on sport - idx_events_date on event_date - idx_events_normalized on event_name_normalized - idx_events_datetime on event_datetime - idx_events_league on league - idx_events_sport on sport - idx_events_date on event_date - idx_events_normalized on event_name_normalized - idx_events_espn on espn_id - idx_events_teams on home_team_normalized, away_team_normalized - idx_events_unfetched on details_fetched - idx_events_home_team_id on home_team_id - idx_events_away_team_id on away_team_id - idx_events_teams on home_team_id, away_team_id - idx_events_espn_id on espn_id

family_league_mappings

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → channel_families KEY Yes
confidence_boost REAL Yes
created_at TEXT No
family_id INTEGER No
id 🔑 INTEGER Yes
league TEXT No
require_exact_league BOOLEAN Yes
sport TEXT No

Indexes: - idx_family_mappings_family on family_id - idx_family_mappings_league on league

invoices

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → user_subscriptions KEY Yes
amount_due_cents INTEGER No
amount_paid_cents INTEGER Yes
created_at TEXT No
created_at_stripe TEXT No
currency TEXT No
description TEXT Yes
due_date TEXT Yes
hosted_invoice_url TEXT Yes
id 🔑 INTEGER Yes
invoice_number TEXT Yes
invoice_pdf_url TEXT Yes
metadata TEXT Yes
paid_at TEXT Yes
period_end TEXT No
period_start TEXT No
status TEXT No
stripe_customer_id TEXT No
stripe_invoice_id TEXT No
subscription_id INTEGER Yes
subtotal_cents INTEGER No
tax_cents INTEGER Yes
total_cents INTEGER No
updated_at TEXT No
user_id INTEGER No

Indexes: - idx_invoices_user on user_id - idx_invoices_stripe on stripe_invoice_id - idx_invoices_status on status - idx_invoices_paid_at on paid_at DESC

key_access_logs

Column Type Nullable Description
FOREIGN → access_keys KEY Yes
accessed_at TEXT No
bytes_transferred INTEGER Yes
id 🔑 INTEGER Yes
ip_address TEXT No
key_id INTEGER No
provider_slug TEXT No
response_time_ms INTEGER Yes
status_code INTEGER No
user_agent TEXT Yes

Indexes: - idx_access_logs_key on key_id - idx_access_logs_accessed on accessed_at DESC - idx_access_logs_ip on ip_address, key_id

learned_aliases

Column Type Nullable Description
CHECK UNKNOWN Yes
alias TEXT No
alias_normalized TEXT No
confidence REAL No
created_at TEXT No
id 🔑 INTEGER Yes
last_seen TEXT No
occurrence_count INTEGER Yes
team_name TEXT No
team_name_normalized TEXT No

Indexes: - idx_learned_aliases_team on team_name_normalized - idx_learned_aliases_confidence on confidence DESC - idx_learned_aliases_occurrence on occurrence_count DESC

learned_patterns

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → events KEY Yes
approved_at TEXT Yes
approved_by TEXT Yes
confidence REAL No
created_at TEXT No
discovery_method TEXT No
false_positive_count INTEGER Yes
id 🔑 INTEGER Yes
league TEXT No
learned_from_channel TEXT Yes
learned_from_event_id INTEGER Yes
match_count INTEGER Yes
pattern_description TEXT No
pattern_regex TEXT No
sport TEXT No
status TEXT Yes
updated_at TEXT No

Indexes: - idx_patterns_league on league - idx_patterns_status on status - idx_patterns_confidence on confidence DESC

llm_parse_cache

Purpose: LLM result deduplication - prevents duplicate API calls for similar channels

Column Type Nullable Description
channel_name_normalized TEXT No
created_at TIMESTAMPTZ Yes
extracted_league TEXT Yes
extracted_sport TEXT Yes
extracted_teams TEXT Yes
extracted_time TEXT Yes
id 🔑 BIGSERIAL Yes
last_reused_at TIMESTAMPTZ Yes
llm_cost_cents REAL Yes
llm_model TEXT Yes
llm_prompt TEXT Yes
llm_provider TEXT Yes
llm_response JSONB Yes
llm_tokens_used INTEGER Yes
raw_channel_name TEXT No
reuse_count INTEGER Yes Tracks cost savings: each reuse = one saved LLM call

Indexes: - idx_llm_cache_similarity on extracted_teams, extracted_time, extracted_league - idx_llm_cache_reuse on reuse_count DESC - idx_llm_cache_sport on extracted_sport

match_cache

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → events KEY Yes
channel_name TEXT No
channel_name_normalized TEXT No
confidence REAL No
created_at TEXT No
event_datetime TEXT No
event_id INTEGER No
expires_at TEXT No
hit_count INTEGER Yes
id 🔑 INTEGER Yes
last_hit_at TEXT Yes
match_method TEXT No
provider_id INTEGER No

Indexes: - idx_cache_lookup on channel_name_normalized, provider_id - idx_cache_expires on expires_at - idx_cache_event on event_id

match_overrides

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → events KEY Yes
active BOOLEAN Yes
channel_id TEXT Yes
channel_name TEXT No
channel_name_normalized TEXT No
confidence INTEGER Yes
created_at TEXT No
event_id INTEGER No
event_title TEXT Yes
family TEXT Yes
id 🔑 INTEGER Yes
notes TEXT Yes
target_date TEXT No
updated_at TEXT No
verified_by TEXT Yes

Indexes: - idx_match_overrides_channel_date on channel_name_normalized, target_date - idx_match_overrides_active on active - idx_match_overrides_family on family - idx_match_overrides_target_date on target_date

match_verifications

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → events KEY Yes
api_source TEXT Yes
channel_name TEXT No
channel_name_normalized TEXT No
confidence INTEGER Yes
created_at TEXT No
event_id INTEGER No
id 🔑 INTEGER Yes
notes TEXT Yes
target_date TEXT No
verified_by TEXT Yes

Indexes: - idx_match_verifications_channel_date on channel_name_normalized, target_date - idx_match_verifications_event on event_id - idx_match_verifications_target_date on target_date

parsed_data

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

Column Type Nullable Description
"confidence": UNKNOWN Yes
"date": UNKNOWN Yes
"event_type": UNKNOWN Yes
"league": UNKNOWN Yes
"overall": UNKNOWN Yes
"sport": UNKNOWN Yes
"teams": UNKNOWN Yes
"time": UNKNOWN Yes
"timezone": UNKNOWN Yes
"venue": UNKNOWN Yes
channel_name_id → channel_names BIGINT No
created_at TIMESTAMPTZ Yes
id 🔑 BIGSERIAL Yes
match_confidence REAL Yes
match_method TEXT Yes
match_reason TEXT Yes
matched_event_id → events BIGINT Yes
parse_date DATE No
parsed_data JSONB No JSONB structure: time, timezone, date, teams[], league, sport, event_type, confidence{}
raw_title TEXT No
record_status TEXT Yes

Indexes: - idx_parsed_data_channel on channel_name_id - idx_parsed_data_date on parse_date - idx_parsed_data_event on matched_event_id - idx_parsed_data_method on match_method

participant_aliases

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → participants KEY Yes
alias TEXT No
alias_normalized TEXT No
alias_type TEXT No
confidence REAL Yes
created_at TEXT No
id 🔑 INTEGER Yes
participant_id INTEGER No
source TEXT No

Indexes: - idx_aliases_participant on participant_id - idx_aliases_normalized on alias_normalized - idx_aliases_unique on participant_id, alias_normalized (UNIQUE)

participants

Column Type Nullable Description
CHECK UNKNOWN Yes
abbreviation TEXT Yes
badge_url TEXT Yes
country TEXT Yes
created_at TEXT No
description TEXT Yes
formed_year INTEGER Yes
id 🔑 INTEGER Yes
league TEXT Yes
logo_url TEXT Yes
name TEXT No
name_normalized TEXT No
participant_type TEXT No
short_name TEXT Yes
sport TEXT No
thesportsdb_id TEXT Yes
updated_at TEXT No

Indexes: - idx_participants_name on name_normalized - idx_participants_sport on sport - idx_participants_league on league

pattern_performance

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

Column Type Nullable Description
avg_api_calls_per_match DECIMAL(5,2) Yes Average number of API calls (TheSportsDB, etc.) needed to resolve one channel match
avg_match_time_ms INTEGER Yes
failed_matches INTEGER No
id 🔑 SERIAL Yes
league TEXT Yes
match_success_rate DECIMAL(5,2) Yes
measured_at TIMESTAMPTZ No
pattern_prefix TEXT No
pattern_type IN Yes
provider_id → providers INTEGER No
requires_llm_resolution BOOLEAN Yes True if this pattern frequently needs LLM team name resolution (expensive)
sport TEXT Yes
successful_matches INTEGER No
total_channels INTEGER No

Indexes: - idx_pattern_performance_provider_id on provider_id - idx_pattern_performance_pattern_prefix on pattern_prefix - idx_pattern_performance_measured_at on measured_at DESC

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.

Column Type Nullable Description
base_url TEXT No Base URL without credentials (e.g., https://provider.com/get.php)
created_at TIMESTAMPTZ No
credential_type IN Yes
encrypted_credentials TEXT No AES-256-GCM encrypted JSON containing username/password/token
encryption_version INTEGER No Encryption key version for supporting key rotation
id 🔑 SERIAL Yes
last_used_at TIMESTAMPTZ Yes
provider_id → providers INTEGER No
static_params JSONB Yes Non-credential URL parameters (e.g., {"type": "m3u", "output": "ts"})
updated_at TIMESTAMPTZ No

Indexes: - idx_provider_credentials_provider_id on provider_id

provider_health_status

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

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

Indexes: - idx_provider_health_status_provider_id on provider_id - idx_provider_health_status_checked_at on checked_at DESC - idx_provider_health_status_status on status

provider_metrics

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

Column Type Nullable Description
channels_processed INTEGER Yes
epg_generation_time_seconds INTEGER Yes
estimated_cost_usd DECIMAL(10,4) Yes
failed_matches INTEGER Yes
id 🔑 SERIAL Yes
llm_team_resolutions INTEGER Yes
match_success_rate DECIMAL(5,2) Yes Percentage of processed channels that successfully matched to events (0-100)
measured_at TIMESTAMPTZ No
parseable_channels INTEGER No
provider_id → providers INTEGER No
sports_channels INTEGER No
successful_matches INTEGER Yes
thesportsdb_api_calls INTEGER Yes
total_channels INTEGER No
tvg_id_coverage_percent DECIMAL(5,2) Yes Percentage of channels that have tvg-id attribute (0-100)

Indexes: - idx_provider_metrics_provider_id on provider_id - idx_provider_metrics_measured_at on measured_at DESC

provider_patterns

Column Type Nullable Description
FOREIGN → providers KEY Yes
created_at TEXT Yes
id 🔑 INTEGER Yes
is_active INTEGER Yes
last_matched_at TEXT Yes
llm_confidence FLOAT Yes LLM confidence score (0.0-1.0)
llm_verified BOOLEAN Yes Whether LLM verified this pattern
llm_verification_reason TEXT Yes LLM reasoning for verification result
match_count INTEGER Yes
normalized_sport TEXT Yes
pattern TEXT No
pattern_type TEXT No
priority INTEGER Yes
provider_id INTEGER No
sport_family TEXT Yes
updated_at TEXT Yes

Indexes: - idx_provider_patterns_provider on provider_id - idx_provider_patterns_active on is_active, provider_id - idx_provider_patterns_priority on priority DESC - idx_provider_patterns_sport on sport_family - idx_provider_patterns_llm_verified on llm_verified (WHERE llm_verified IS NOT NULL)

providers

Purpose: IPTV service providers with M3U sources

Column Type Nullable Description
active BOOLEAN Yes
channel_prefix TEXT Yes
config_json TEXT Yes
created_at TIMESTAMPTZ No
default_timezone TEXT Yes
description TEXT Yes
display_name TEXT No
enable_llm_fallback BOOLEAN Yes
id 🔑 BIGSERIAL Yes
identification_method TEXT No 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 Yes
last_error TEXT Yes
last_processed_at TIMESTAMPTZ Yes
m3u_password TEXT Yes
m3u_requires_auth BOOLEAN Yes
m3u_url TEXT No
m3u_username TEXT Yes
name TEXT No
slug TEXT No
support_email TEXT Yes
updated_at TIMESTAMPTZ No
website_url TEXT Yes

Indexes: - idx_providers_active on active - idx_providers_slug on slug - idx_providers_slug on slug - idx_providers_active on is_active - idx_providers_active on active - idx_providers_slug on slug

subscription_events

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → users KEY Yes
admin_user_id INTEGER Yes
created_at TEXT No
event_type TEXT No
id 🔑 INTEGER Yes
metadata TEXT Yes
new_status TEXT Yes
previous_status TEXT Yes
subscription_id INTEGER No
triggered_by TEXT Yes

Indexes: - idx_sub_events_subscription on subscription_id - idx_sub_events_type on event_type - idx_sub_events_created on created_at DESC

successful_matches

Column Type Nullable Description
CHECK UNKNOWN Yes
api_source TEXT Yes
channel_name TEXT No
channel_name_normalized TEXT No
confidence REAL Yes
created_at TEXT No
id 🔑 INTEGER Yes
league TEXT Yes
match_method TEXT Yes
matched_team1 TEXT Yes
matched_team2 TEXT Yes
parsed_team1 TEXT Yes
parsed_team2 TEXT Yes

Indexes: - idx_successful_matches_league on league - idx_successful_matches_confidence on confidence DESC - idx_successful_matches_created on created_at DESC - idx_successful_matches_matched_teams on matched_team1, matched_team2

team_aliases

Purpose: All team name variations for intelligent fuzzy matching

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

Indexes: - idx_team_aliases_normalized on alias_normalized - idx_team_aliases_team_id on team_id - idx_team_aliases_source on source - idx_team_aliases_priority on priority DESC

team_discovery_cache

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

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

Indexes: - idx_team_discovery_raw_name on raw_team_name - idx_team_discovery_canonical on canonical_team_name - idx_team_discovery_team_id on team_id - idx_team_discovery_league_sport on league, sport - idx_team_discovery_created_at on created_at DESC

team_pairings

Column Type Nullable Description
CHECK UNKNOWN Yes
created_at TEXT No
id 🔑 INTEGER Yes
last_seen TEXT No
league TEXT No
occurrence_count INTEGER Yes
team1 TEXT No
team1_normalized TEXT No
team2 TEXT No
team2_normalized TEXT No

Indexes: - idx_team_pairings_teams on team1_normalized, team2_normalized - idx_team_pairings_league on league - idx_team_pairings_occurrence on occurrence_count DESC

team_sync_audit_log

Column Type Nullable Description
'alias_added', UNKNOWN Yes
'conflict_detected', UNKNOWN Yes
'team_created', UNKNOWN Yes
action_type TEXT No
changes TEXT Yes
conflict_id → team_sync_conflicts INTEGER Yes
entity_id INTEGER No
entity_type TEXT No
id 🔑 INTEGER Yes
notes TEXT Yes
performed_at DATETIME No
performed_by TEXT Yes
sync_run_id → team_sync_runs TEXT Yes
team_id → teams INTEGER Yes

Indexes: - idx_team_sync_audit_log_sync_run on sync_run_id - idx_team_sync_audit_log_team on team_id - idx_team_sync_audit_log_performed on performed_at DESC - idx_team_sync_audit_log_action on action_type - idx_team_sync_audit_log_sync_run on sync_run_id - idx_team_sync_audit_log_team on team_id - idx_team_sync_audit_log_performed on performed_at DESC - idx_team_sync_audit_log_action on action_type

team_sync_auto_resolution_rules

Column Type Nullable Description
'equals', UNKNOWN Yes
'merge', UNKNOWN Yes
action TEXT No
condition_field TEXT No
condition_operator TEXT No
condition_value TEXT No
conflict_type TEXT No
created_at DATETIME No
created_by TEXT Yes
description TEXT No
enabled BOOLEAN No
id 🔑 INTEGER Yes
priority INTEGER No

team_sync_conflicts

Column Type Nullable Description
'alias_conflict' UNKNOWN Yes
'api_id_mismatch', UNKNOWN Yes
'auto_resolved', UNKNOWN Yes
'canonical_name_conflict', UNKNOWN Yes
'create_new', UNKNOWN Yes
'duplicate_normalized_name', UNKNOWN Yes
'ignore', UNKNOWN Yes
'ignored' UNKNOWN Yes
'manual_edit' UNKNOWN Yes
'merge', UNKNOWN Yes
'pending', UNKNOWN Yes
'resolved', UNKNOWN Yes
'update_existing', UNKNOWN Yes
api_source TEXT No
conflict_field TEXT No
conflict_type TEXT No
conflict_value TEXT No
detected_at DATETIME No
existing_team_id → teams INTEGER Yes
id 🔑 INTEGER Yes
new_team_data TEXT No
resolution_action TEXT Yes
resolution_notes TEXT Yes
resolved_at DATETIME Yes
resolved_by TEXT Yes
severity TEXT No
status TEXT No
sync_run_id TEXT Yes

Indexes: - idx_team_sync_conflicts_status on status - idx_team_sync_conflicts_type on conflict_type - idx_team_sync_conflicts_team on existing_team_id - idx_team_sync_conflicts_sync_run on sync_run_id - idx_team_sync_conflicts_detected on detected_at DESC - idx_team_sync_conflicts_status on status - idx_team_sync_conflicts_type on conflict_type - idx_team_sync_conflicts_team on existing_team_id - idx_team_sync_conflicts_sync_run on sync_run_id - idx_team_sync_conflicts_detected on detected_at DESC

team_sync_runs

Column Type Nullable Description
'running', UNKNOWN Yes
api_sources TEXT No
auto_resolve_enabled BOOLEAN No
completed_at DATETIME Yes
conflicts_detected INTEGER No
dry_run BOOLEAN No
error_log TEXT Yes
errors INTEGER No
id 🔑 TEXT Yes
league_filter TEXT Yes
sport_filter TEXT Yes
started_at DATETIME No
status TEXT No
summary TEXT Yes
teams_created INTEGER No
teams_discovered INTEGER No
teams_updated INTEGER No
triggered_by TEXT Yes

Indexes: - idx_team_sync_runs_started on started_at DESC - idx_team_sync_runs_status on status - idx_team_sync_runs_started on started_at DESC - idx_team_sync_runs_status on status

teams

Purpose: Canonical team records with API source IDs for intelligent matching

Column Type Nullable Description
abbreviation TEXT Yes
canonical_name TEXT No Primary team name (e.g., "Los Angeles Lakers")
canonical_name_normalized TEXT No
city TEXT Yes
country TEXT Yes
created_at TIMESTAMPTZ No
discovery_source TEXT Yes
espn_team_id TEXT Yes Team ID from ESPN API
id 🔑 BIGSERIAL Yes
is_placeholder BOOLEAN Yes TRUE if auto-created, needs verification
league TEXT No
sport TEXT No
thesportsdb_team_id TEXT Yes Team ID from TheSportsDB API
updated_at TIMESTAMPTZ No

Indexes: - idx_teams_league on league - idx_teams_sport on sport - idx_teams_thesportsdb_id on thesportsdb_team_id - idx_teams_espn_id on espn_team_id - idx_teams_is_placeholder on is_placeholder

tvg_id_mappings

Column Type Nullable Description
FOREIGN → providers KEY Yes
channel_name TEXT Yes
created_at TEXT Yes
id 🔑 INTEGER Yes
is_active INTEGER Yes
last_matched_at TEXT Yes
league TEXT Yes
match_count INTEGER Yes
normalized_sport TEXT Yes
provider_id INTEGER No
sport_family TEXT Yes
tvg_id TEXT No
tvg_id_prefix TEXT Yes
updated_at TEXT Yes

Indexes: - idx_tvg_id_mappings_provider on provider_id - idx_tvg_id_mappings_tvg_id on tvg_id - idx_tvg_id_mappings_prefix on tvg_id_prefix - idx_tvg_id_mappings_active on is_active, provider_id - idx_tvg_id_mappings_provider on provider_id - idx_tvg_id_mappings_tvg_id on tvg_id - idx_tvg_id_mappings_prefix on tvg_id_prefix - idx_tvg_id_mappings_active on is_active, provider_id - idx_tvg_id_mappings_provider on provider_id - idx_tvg_id_mappings_tvg_id on tvg_id - idx_tvg_id_mappings_prefix on tvg_id_prefix - idx_tvg_id_mappings_active on is_active, provider_id

unmatched_channels

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → events KEY Yes
channel_name TEXT No
channel_name_normalized TEXT No
failure_reason TEXT Yes
family TEXT Yes
first_seen TEXT No
group_title TEXT Yes
id 🔑 INTEGER Yes
last_seen TEXT No
llm_attempted BOOLEAN Yes
llm_response TEXT Yes
manual_match_confidence REAL Yes
manual_match_event_id INTEGER Yes
manual_matched_at TEXT Yes
manual_matched_by TEXT Yes
metadata TEXT Yes
occurrence_count INTEGER Yes
payload TEXT Yes
provider TEXT Yes
provider_id INTEGER No
regex_stage_reached INTEGER Yes
resolution_notes TEXT Yes
resolved BOOLEAN Yes
target_date TEXT Yes
team1 TEXT Yes
team2 TEXT Yes
timestamp TEXT Yes
tvg_id TEXT Yes
tvg_name TEXT Yes

Indexes: - idx_unmatched_provider on provider_id - idx_unmatched_resolved on resolved - idx_unmatched_normalized on channel_name_normalized - idx_unmatched_occurrence on occurrence_count DESC - idx_unmatched_family on family - idx_unmatched_target_date on target_date - idx_unmatched_team1 on team1 - idx_unmatched_provider_name on provider - idx_unmatched_timestamp on timestamp DESC

user_subscriptions

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → users KEY Yes
amount_cents INTEGER No
cancel_at_period_end BOOLEAN Yes
canceled_at TEXT Yes
created_at TEXT No
currency TEXT No
current_period_end TEXT No
current_period_start TEXT No
ended_at TEXT Yes
grace_period_emails_sent INTEGER Yes
grace_period_end TEXT Yes
id 🔑 INTEGER Yes
plan_type TEXT No
provider_packs TEXT Yes
status TEXT No
stripe_customer_id TEXT No
stripe_price_id TEXT No
stripe_subscription_id TEXT No
trial_end TEXT Yes
trial_start TEXT Yes
updated_at TEXT No
user_id INTEGER No

Indexes: - idx_subscriptions_user on user_id - idx_subscriptions_stripe on stripe_subscription_id - idx_subscriptions_status on status - idx_subscriptions_period_end on current_period_end

users

Column Type Nullable Description
CHECK UNKNOWN Yes
FOREIGN → users KEY Yes
auth0_id TEXT No
created_at TEXT No
email TEXT No
email_notifications BOOLEAN Yes
email_verified BOOLEAN Yes
id 🔑 INTEGER Yes
last_login_at TEXT Yes
name TEXT Yes
picture_url TEXT Yes
preferred_language TEXT Yes
preferred_timezone TEXT Yes
referrer_user_id INTEGER Yes
role TEXT No
signup_source TEXT Yes
status TEXT No
suspended_at TEXT Yes
suspended_reason TEXT Yes
updated_at TEXT No

Indexes: - idx_users_auth0 on auth0_id - idx_users_email on email - idx_users_role on role - idx_users_status on status

vod_filter_patterns

Column Type Nullable Description
FOREIGN → providers KEY Yes
created_at TEXT Yes
description TEXT Yes
id 🔑 INTEGER Yes
is_active INTEGER Yes
match_count INTEGER Yes
pattern TEXT No
pattern_type TEXT Yes
provider_id INTEGER Yes

Indexes: - idx_vod_patterns_provider on provider_id - idx_vod_patterns_active on is_active - idx_vod_patterns_provider on provider_id - idx_vod_patterns_active on is_active - idx_vod_patterns_provider on provider_id - idx_vod_patterns_active on is_active

webhook_events

Column Type Nullable Description
CHECK UNKNOWN Yes
created_at_stripe TEXT No
event_type TEXT No
id 🔑 INTEGER Yes
payload TEXT No
processed BOOLEAN Yes
processed_at TEXT Yes
processing_error TEXT Yes
received_at TEXT No
retry_count INTEGER Yes
stripe_event_id TEXT No

Indexes: - idx_webhooks_stripe_id on stripe_event_id - idx_webhooks_type on event_type - idx_webhooks_processed on processed - idx_webhooks_received on received_at DESC