Database Architecture Strategy

-- Prospective leagues from various sources
CREATE TABLE prospective_leagues (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    sport_name VARCHAR(255),
    sport_tier VARCHAR(50),
    country_codes TEXT[],
 
    -- Source information
    source_type VARCHAR(50), -- 'web_scrape', 'email_ingest', 'form_submission', 'market_research'
    source_url TEXT,
    discovered_at TIMESTAMP DEFAULT NOW(),
 
    -- Enrichment data
    website_url TEXT,
    social_media JSONB,
    contact_info JSONB,
    estimated_teams INTEGER,
    estimated_fans INTEGER,
 
    -- Scoring and evaluation
    opportunity_score FLOAT,
    data_quality_score FLOAT,
    market_potential_score FLOAT,
    betting_readiness_score FLOAT,
 
    -- Status tracking
    verification_status VARCHAR(50) DEFAULT 'unverified', -- 'unverified', 'investigating', 'contacted', 'verified', 'rejected'
    contact_attempts INTEGER DEFAULT 0,
    last_contact_attempt TIMESTAMP,
 
    -- Promotion to Firebase
    promoted_to_firebase BOOLEAN DEFAULT FALSE,
    promoted_at TIMESTAMP,
    firebase_league_id VARCHAR(255),
 
    -- Metadata
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW(),
    created_by VARCHAR(255),
 
    CONSTRAINT unique_league_source UNIQUE (name, sport_name, source_url)
);
 
-- Track web scraping activities
CREATE TABLE scrape_sessions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    source_type VARCHAR(100),
    target_url TEXT,
    leagues_discovered INTEGER,
    success_rate FLOAT,
    started_at TIMESTAMP,
    completed_at TIMESTAMP,
    error_log JSONB,
    created_at TIMESTAMP DEFAULT NOW()
);
 
-- Store enriched league data from research
CREATE TABLE league_enrichment (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    league_id UUID REFERENCES prospective_leagues(id) ON DELETE CASCADE,
    enrichment_type VARCHAR(100), -- 'web_research', 'social_media', 'betting_markets', 'financial'
    data_json JSONB,
    confidence_score FLOAT,
    source_url TEXT,
    enriched_at TIMESTAMP DEFAULT NOW()
);
 
-- Opportunity scoring history
CREATE TABLE opportunity_evaluations (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    league_id UUID REFERENCES prospective_leagues(id) ON DELETE CASCADE,
    evaluation_version VARCHAR(50),
    scores JSONB, -- All dimension scores
    rationale TEXT,
    recommended_action VARCHAR(100), -- 'pursue', 'monitor', 'reject', 'needs_more_data'
    evaluated_at TIMESTAMP DEFAULT NOW(),
    evaluated_by VARCHAR(255)
);
 
-- Track contact attempts and outreach
CREATE TABLE contact_history (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    league_id UUID REFERENCES prospective_leagues(id) ON DELETE CASCADE,
    contact_type VARCHAR(50), -- 'email', 'phone', 'linkedin', 'website_form'
    contact_details JSONB,
    response_received BOOLEAN DEFAULT FALSE,
    response_details JSONB,
    contacted_at TIMESTAMP DEFAULT NOW()
);
 
CREATE INDEX idx_prospective_leagues_sport ON prospective_leagues(sport_name);
CREATE INDEX idx_prospective_leagues_tier ON prospective_leagues(sport_tier);
CREATE INDEX idx_prospective_leagues_status ON prospective_leagues(verification_status);
CREATE INDEX idx_prospective_leagues_score ON prospective_leagues(opportunity_score DESC);
CREATE INDEX idx_prospective_leagues_source ON prospective_leagues(source_type);

// Leagues
(:League {id, name, tier, sport})
 
// Sports
(:Sport {name, category, betting_popularity})
 
// Markets
(:Market {region, country, betting_legal})
 
// Organizations
(:Organization {name, type}) // governing bodies, betting operators
 
// Relationships
(:League)-[:BELONGS_TO_SPORT]->(:Sport)
(:League)-[:OPERATES_IN]->(:Market)
(:League)-[:SIMILAR_TO]->(:League)
(:League)-[:COMPETES_WITH]->(:League)
(:League)-[:PARTNERED_WITH]->(:Organization)

// "Show me high-potential basketball leagues we haven't contacted yet"
const opportunities = await supabase
  .from('prospective_leagues')
  .select('*, opportunity_evaluations(*)')
  .eq('sport_name', 'Basketball')
  .eq('verification_status', 'unverified')
  .gte('opportunity_score', 70)
  .order('opportunity_score', { ascending: false })
  .limit(10);

// "Show me all active basketball league partnerships"
const partners = await firestore
  .collection('verified_leagues')
  .where('sportName', '==', 'Basketball')
  .where('partnershipStatus', '==', 'active')
  .get();

// "Which leagues are similar to our top performers?"
MATCH (top:League {tier: 'TIER1'})-[:SIMILAR_TO]-(similar:League)
WHERE NOT similar.tier = 'TIER1'
RETURN similar
ORDER BY similar.opportunity_score DESC
LIMIT 10