Supabase Database Design

Database Schema Overview

The BBX platform leverages Supabase (PostgreSQL) to manage tournament lifecycles, player registration, and real-time match progression. The schema is designed to handle two distinct tournament phases: Swiss Rounds and Single-Elimination Top Cut.

Core Tables

tournaments

This table stores the metadata and current state for every hosted event.

| Column | Type | Description | | :--- | :--- | :--- | | id | uuid (PK) | Unique identifier for the tournament. | | name | text | Display name (e.g., "City Championship"). | | slug | text | Custom URL identifier (optional). | | status | text | Current state: registration, started, or completed. | | cut_size | integer | The number of players advancing to the Top Cut (e.g., 8, 16). | | created_at | timestamp | Record creation time. |

participants

Stores player records linked to specific tournaments.

| Column | Type | Description | | :--- | :--- | :--- | | id | uuid (PK) | Unique identifier for the player. | | tournament_id | uuid (FK) | Reference to the parent tournament. | | name | text | Player username or display name. |

matches

The central engine of the platform. This table tracks pairings, scoring, and results for both Swiss and Bracket stages.

| Column | Type | Description | | :--- | :--- | :--- | | id | uuid (PK) | Unique identifier for the match. | | tournament_id | uuid (FK) | Reference to the parent tournament. | | stage | text | The phase of the match: swiss or top_cut. | | status | text | Match state: pending or complete. | | participant_a_id | uuid (FK) | Reference to the first player. | | participant_b_id | uuid (FK) | Reference to the second player (null for BYEs). | | score_a | integer | Points earned by participant A. | | score_b | integer | Points earned by participant B. | | winner_id | uuid (FK) | Reference to the winning participant. | | swiss_round_number| integer | The specific round in the Swiss phase. | | bracket_round | integer | The round in the Top Cut (1 = Finals, 2 = Semis, etc.). | | match_number | integer | Positioning within a specific round for bracket ordering. |

Database Views

swiss_standings

A calculated view that aggregates match data to provide live rankings. This view is essential for determining who qualifies for the Top Cut.

Ranking Logic Priority:

1. Match Wins: Primary ranking factor.
2. Buchholz Score: Tie-breaker based on the strength of opponents' schedules.
3. Point Differential: Tie-breaker based on total points scored vs. points conceded.

-- Usage Example: Fetching Top 8 for a tournament
SELECT participant_id, match_wins, buchholz
FROM swiss_standings
WHERE tournament_id = 'your-uuid'
ORDER BY match_wins DESC, buchholz DESC, point_diff DESC
LIMIT 8;

Key Relations & Logic

Querying the Bracket

To render the Top Cut bracket, the application filters matches by the top_cut stage. The bracket_round and match_number columns are used to map matches to their specific positions in the visual tree.

const { data: matches } = await supabase
    .from("matches")
    .select("*")
    .eq("tournament_id", TOURNAMENT_ID)
    .eq("stage", "top_cut")
    .order("bracket_round", { ascending: true });

Match Progression

• Swiss Phase: Matches are generated round-by-round. A match is considered decided when status is set to complete and a winner_id is assigned.
• Top Cut Phase: Winners of bracket_round N are automatically promoted to bracket_round N-1 in a specific match_number slot calculated by the application logic.

Data Integrity

The schema utilizes PostgreSQL Foreign Key constraints with ON DELETE CASCADE where appropriate to ensure that deleting a tournament automatically cleans up all associated participants and match records.