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) │
└──────────┘ └────────────┘
| Column | Type | Notes |
|---|---|---|
| id | INT PK | Auto-increment |
| full_name | VARCHAR(100) | |
| 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 |
| 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 |
| 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) |
| 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 |
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) |
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 |
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 |
allTeams (season schedule)
| Column | Notes |
|---|---|
| espnTeamId | Team |
| gameId | ESPN game ID |
| result | Win/loss |
| gameYear | Season year |
| vsEspnTeamId | Opponent |
| name / vsName | Team names |
| rank / vsRank | AP poll ranks |