DB Design — March Madness Pool

Entity Relationship Overview

High-level relationships between the core tables.

  ┌──────────┐        ┌──────────────────┐        ┌────────┐
  │  users   │ 1───n  │    entries        │ 1───n  │ picks  │
  │ (id)     │        │ (id, user_id,     │        │(id,    │
  └──────────┘        │  year, bracket)   │        │entry_id│
                      └──────────────────┘        │player_id)
                                                   └────┬───┘
                                                        │ n
                                                        │
  ┌──────────┐                                    ┌─────┴──────┐
  │  teams   │ 1───n  player_pts  n───1  ┌──────┐ │  players   │
  │(espnTeam │        (espnTeam,         │teams │ │(espnPlayer,│
  │  Id,     │         espnPlayer,       └──────┘ │ espnTeam,  │
  │  seed,   │         pts, round,                │ playerName,│
  │  year)   │         result, coach)             │ ppg, year) │
  └──────────┘                                    └────────────┘

users

Column	Type	Notes
id	INT PK	Auto-increment
full_name	VARCHAR(100)
email	VARCHAR(200)	Unique; used for login
cell	VARCHAR(20)	Unique; formatted (XXX) XXX-XXXX
password_hash	VARCHAR(255)	Werkzeug PBKDF2 hash
created_at	TIMESTAMP	Auto

entries

Column	Type	Notes
id	INT PK	Auto-increment
user_id	INT FK	→ users.id
year	INT	Tournament year
bracket_name	VARCHAR(100)	Unique per year
full_name	VARCHAR(100)	Copied from user at submit
tiebreaker	INT	Championship total guess
token	CHAR(64)	Magic link access
paid	TINYINT(1)	0 or 1

picks

Column	Type	Notes
id	INT PK
entry_id	INT FK	→ entries.id (CASCADE DELETE)
espnPlayerId	VARCHAR(20)	→ players.espnPlayerId
groupId	INT	1–5 (seed group / coaches)

password_reset_tokens

Column	Type	Notes
id	INT PK
user_id	INT FK	→ users.id (CASCADE DELETE)
token	CHAR(64)	Unique random hex
expires_at	DATETIME	24 hours from creation
used	TINYINT(1)	0 = active, 1 = consumed

Tournament Data Tables

These tables are loaded from ESPN data by the score-fetching pipeline and are not user-editable.

teams

Column	Notes
espnTeamId	ESPN team identifier
teamName	Full team name
seed	1–16
gameYear	Tournament year
play_in	Flag: play-in game team
play_in_result	'undecided' / 'in' (advanced) / 'out' (eliminated)
predict_from_round	Round scoring begins (play-in adjustment)
out	Flag: eliminated from the tournament
predict_finals	Flag: seed projected to reach the Final Four
rank	Admin-set ranking, nullable
espnRank	ESPN's own ranking, nullable

players

Column	Notes
espnPlayerId	ESPN player ID
espnTeamId	→ teams.espnTeamId
playerName	"Coach" for coaching picks
ppg	Season points per game
pick	Display string shown in entry form
gameYear	Tournament year

player_pts (one row per player per game)

Column	Notes
espnPlayerId	Player or team ID (for coach rows, equals espnTeamId)
espnTeamId	The team that played
vsEspnTeamId	The opponent team
pts	Points scored in this game (actual or 10 for coach wins)
round	1 = Rd 1 … 6 = Championship
result	'win', 'loss', 'In-Play'
gameId	ESPN game ID
gameYear	Tournament year
seed	Team seed
coach	'Y' = coach/team row, 'N' = player row

Operational Tables

settings (key-value store)

Key	Purpose
active_year	Current tournament year
entries_live	'Y'/'N' — lock flag
entries_live_at	Scheduled go-live datetime
tournament_rounds	Number of rounds (default 6)
payout_example_threshold	Paid entries needed to show real pool
scores_*	Score-fetcher config (date, round, frequency)

score_run_log

Column	Notes
run_id	UUID for each fetch run
game_id	ESPN game ID (if game-specific)
message	Log message text
status	info / winner / playing / ok / error
created_at	Log timestamp

tournament_config

Column	Notes
year	Unique per year
state	SETUP / OPEN / LOCKED / IN_PROGRESS / COMPLETE
lock_time	When entries locked
current_round	Round the tournament is on

Season Games & Odds Tables

Regular-season data (never NCAA tournament games) and round-of-64 betting lines, loaded by the admin Workflow steps — see How It's Built and Data Warehouse for how this feeds analysis.

games (one row per team per regular-season game)

Column	Notes
espnGameId	ESPN game ID
espnTeamId / opponentEspnTeamId	The two teams
gameYear / gameDate	Season year and date
game_type	Regular season / conference tournament
result / site	Win/loss, home/away/neutral
opponent_conference / opponent_conference_tier	Opponent's conference, High/Low tier
opponent_final_wins / opponent_final_losses	Opponent's final season record (not as-of-game-date)
opponent_point_diff / opponent_standing_summary	Opponent strength context
team_streak_entering	Win/loss streak entering this game (signed)

player_game_pts (one row per player per regular-season game)

Column	Notes
espnPlayerId	Player
espnTeamId / espnGameId	Team and game
gameYear / gameDate	Season year and date
pts	Points scored in this game
game_type	Regular season / conference tournament

game_odds (one row per team's round-of-64 odds)

Column	Notes
espnTeamId / opponentEspnTeamId	The matchup
gameYear / gameDate	Tournament year and date
spread / moneyline / total	Betting lines
favorite_espnTeamId	Which team was favored
result / is_upset	Outcome, whether the underdog won

season_games_load_status (completion marker, one row per team per year)

Column	Notes
espnTeamId + gameYear	Composite primary key
completed_at	Only written once a team's full season-game loop finishes — lets an interrupted import re-run cleanly instead of skipping partial data

Daily Commentary Tables

Support the admin AI-generated daily update feature (Claude via MCP).

daily_commentary

Column	Notes
year	Tournament year
content	Generated commentary text
generated_at	When it was generated
scores_snapshot	Score state at generation time

daily_scores_cache

Column	Notes
year	Primary key
snapshot	Score snapshot, for next-day comparison
saved_at	Last saved

commentary_jobs

Column	Notes
year / status	pending / running / done / error
result / error_message	Job outcome
created_at / updated_at	Timestamps

Database Design