Select a result to preview

Leaderboard-Implementation: Technische Spezifikation

Zurück zur MOC - Map of Content

Überblick

Dieses Dokument beschreibt die technische Implementierung des Leaderboard-Systems. Es deckt SQL Functions, Supabase-Integration, Dashboard-Komponenten und Performance-Überlegungen ab.

Supabase Function: Creator Jury Score

CREATE OR REPLACE FUNCTION calculate_creator_jury_score(p_creator_id UUID)
RETURNS INTEGER AS $
DECLARE
  revenue_score INTEGER;
  session_score INTEGER;
  entry_score INTEGER;
  viewer_score INTEGER;
  vote_score INTEGER;
  total_score INTEGER;
BEGIN
  -- Revenue: 1 Punkt pro 100€, max 40
  SELECT LEAST(FLOOR(COALESCE(SUM(p.amount_cents), 0) / 10000), 40)
  INTO revenue_score
  FROM payments p
  JOIN queue_entries qe ON p.queue_entry_id = qe.id
  JOIN sessions s ON qe.session_id = s.id
  WHERE s.creator_id = p_creator_id
    AND p.status = 'completed';

  -- Sessions: 2 Punkte pro Session, max 20
  SELECT LEAST(COUNT(*) * 2, 20)
  INTO session_score
  FROM sessions
  WHERE creator_id = p_creator_id
    AND status = 'completed';

  -- Entries Reviewed: 1 Punkt pro 10 Entries, max 15
  SELECT LEAST(FLOOR(COUNT(*) / 10), 15)
  INTO entry_score
  FROM queue_entries qe
  JOIN sessions s ON qe.session_id = s.id
  WHERE s.creator_id = p_creator_id
    AND qe.status IN ('played', 'rated');

  -- Viewers: 1 Punkt pro 100 Viewer, max 15
  SELECT LEAST(FLOOR(COALESCE(SUM(viewer_count), 0) / 100), 15)
  INTO viewer_score
  FROM sessions
  WHERE creator_id = p_creator_id;

  -- Community Votes: 1 Punkt pro 50 Votes, max 10
  SELECT LEAST(FLOOR(COALESCE(SUM(br.vote_count), 0) / 50), 10)
  INTO vote_score
  FROM breakout_rounds br
  JOIN sessions s ON br.session_id = s.id
  WHERE s.creator_id = p_creator_id;

  total_score := COALESCE(revenue_score, 0) 
    + COALESCE(session_score, 0) 
    + COALESCE(entry_score, 0) 
    + COALESCE(viewer_score, 0) 
    + COALESCE(vote_score, 0);

  RETURN total_score;
END;
$ LANGUAGE plpgsql STABLE;

-- Index für Performance
CREATE INDEX IF NOT EXISTS idx_sessions_creator_id ON sessions(creator_id);
CREATE INDEX IF NOT EXISTS idx_payments_status ON payments(status);
CREATE INDEX IF NOT EXISTS idx_queue_entries_status ON queue_entries(status);

Supabase Function: Artist Qualification Score

CREATE OR REPLACE FUNCTION calculate_artist_qualification_score(p_artist_id UUID)
RETURNS INTEGER AS $
DECLARE
  breakout_count INTEGER;
  upcoming_count INTEGER;
  reviewed_count INTEGER;
  breakout_score INTEGER;
  upcoming_score INTEGER;
  session_score INTEGER;
  rating_score INTEGER;
  vote_score INTEGER;
  total_score INTEGER;
BEGIN
  -- Breakout Wins zählen
  SELECT COUNT(*) INTO breakout_count
  FROM breakout_results
  WHERE artist_id = p_artist_id 
    AND outcome = 'breakout';

  -- Upcoming Placements zählen (verschiedene Sessions!)
  SELECT COUNT(DISTINCT session_id) INTO upcoming_count
  FROM breakout_results br
  JOIN breakout_rounds brd ON br.round_id = brd.id
  WHERE br.artist_id = p_artist_id 
    AND br.outcome = 'upcoming';

  -- Bewertete Entries zählen
  SELECT COUNT(*) INTO reviewed_count
  FROM queue_entries
  WHERE artist_id = p_artist_id 
    AND status IN ('played', 'rated');

  -- Hard Gate Check: (1 Breakout ODER 2 Upcoming) UND 3+ Entries
  IF (breakout_count < 1 AND upcoming_count < 2) OR reviewed_count < 3 THEN
    RETURN 0;
  END IF;

  -- Breakout: 20 Punkte pro Win, max 40
  breakout_score := LEAST(breakout_count * 20, 40);

  -- Upcoming: 8 Punkte pro Placement, max 24
  upcoming_score := LEAST(upcoming_count * 8, 24);

  -- Sessions: 2 Punkte pro bewerteter Entry, max 20
  session_score := LEAST(reviewed_count * 2, 20);

  -- Average Rating: direkt (7.5 = 7 Punkte), max 10
  SELECT LEAST(FLOOR(COALESCE(AVG(
    (SELECT AVG(er.score) FROM entry_ratings er WHERE er.entry_id = qe.id)
  ), 0)), 10)
  INTO rating_score
  FROM queue_entries qe
  WHERE qe.artist_id = p_artist_id 
    AND qe.status IN ('played', 'rated');

  -- Community Votes: 1 Punkt pro 100, max 5
  SELECT LEAST(FLOOR(COALESCE(SUM(bv.id IS NOT NULL::int), 0) / 100), 5)
  INTO vote_score
  FROM breakout_votes bv
  JOIN breakout_rounds br ON bv.round_id = br.id
  JOIN breakout_results bres ON bres.round_id = br.id
  WHERE bres.artist_id = p_artist_id;

  total_score := COALESCE(breakout_score, 0)
    + COALESCE(upcoming_score, 0)
    + COALESCE(session_score, 0)
    + COALESCE(rating_score, 0)
    + COALESCE(vote_score, 0);

  RETURN total_score;
END;
$ LANGUAGE plpgsql STABLE;

Leaderboard-Tabellen

-- Materialized View für Creator Leaderboard (stündlich aktualisiert)
CREATE MATERIALIZED VIEW IF NOT EXISTS creator_leaderboard AS
SELECT 
  c.id AS creator_id,
  c.display_name,
  calculate_creator_jury_score(c.id) AS score,
  RANK() OVER (ORDER BY calculate_creator_jury_score(c.id) DESC) AS rank
FROM creators c
WHERE c.id IN (SELECT DISTINCT creator_id FROM sessions WHERE status = 'completed')
ORDER BY score DESC;

-- Materialized View für Artist Leaderboard
CREATE MATERIALIZED VIEW IF NOT EXISTS artist_leaderboard AS
SELECT 
  a.id AS artist_id,
  a.artist_name,
  calculate_artist_qualification_score(a.id) AS score,
  RANK() OVER (ORDER BY calculate_artist_qualification_score(a.id) DESC) AS rank,
  CASE 
    WHEN RANK() OVER (ORDER BY calculate_artist_qualification_score(a.id) DESC) <= 50 
    THEN 'qualified'
    ELSE 'waitlist'
  END AS status
FROM artists a
WHERE calculate_artist_qualification_score(a.id) > 0
ORDER BY score DESC;

-- Cron Job: Refresh alle 60 Minuten
-- (via Supabase Edge Function oder pg_cron)
SELECT cron.schedule(
  'refresh-leaderboards',
  '0 * * * *',
  'REFRESH MATERIALIZED VIEW CONCURRENTLY creator_leaderboard; REFRESH MATERIALIZED VIEW CONCURRENTLY artist_leaderboard;'
);

Dashboard React-Komponenten

LeaderboardCard (Shared)

// apps/web/src/components/shared/LeaderboardCard.tsx

interface LeaderboardEntry {
  rank: number;
  name: string;
  score: number;
  breakdown: {
    category: string;
    value: number;
    max: number;
  }[];
  status: 'jury' | 'backup' | 'top' | 'standard' | 'qualified' | 'waitlist';
  lockCountdown: string; // "23d 14h"
}

// Komponente zeigt:
// - Rang + Name
// - Score mit Fortschrittsbalken
// - Status-Badge (Jury/Qualified/Waitlist)
// - Countdown bis Ranking Lock
// - Verbesserungstipps

CreatorLeaderboard

// apps/web/src/components/host/CreatorLeaderboard.tsx

// Hook: useCreatorLeaderboard()
// - Holt Daten aus creator_leaderboard View
// - Aktualisiert beim Laden der Seite
// - Zeigt Top 10 + eigene Position

ArtistLeaderboard

// apps/web/src/components/viewer/ArtistLeaderboard.tsx

// Hook: useArtistLeaderboard()
// - Holt Daten aus artist_leaderboard View
// - Zeigt Position ±10 um eigenen Rang
// - Qualified/Waitlist-Status prominent

Performance-Optimierungen

  1. Materialized Views statt Live-Berechnung: Scores werden vorab berechnet
  2. Stündlicher Refresh statt Realtime: Reduziert DB-Last um ~95%
  3. Indizes auf allen Join-Spalten: creator_id, artist_id, session_id, status
  4. CONCURRENTLY Refresh: Keine Downtime beim Aktualisieren
  5. Pagination: Leaderboard-API gibt max 50 Einträge pro Request zurück

Ranking Lock Implementation

-- Config-Tabelle für Tournament-Settings
CREATE TABLE IF NOT EXISTS tournament_config (
  key VARCHAR(50) PRIMARY KEY,
  value JSONB,
  updated_at TIMESTAMP DEFAULT NOW()
);

-- Ranking Lock Flag
INSERT INTO tournament_config (key, value) 
VALUES ('ranking_locked', '{"locked": false, "lock_date": "2026-04-XX"}');

-- Check in Score-Functions:
-- IF (SELECT boolean FROM tournament_config WHERE key = 'ranking_locked' THEN
--   RETURN cached_score; -- Gib gecachten Score zurück, keine Neuberechnung
-- END IF;

MOC - Map of Content | Leaderboard-System | Aktueller Stand der App | Fehlende Komponenten | Technische Anforderungen Tournament