The full route catalog and the live operational schema behind it, plus the warehouse vocabulary and the one SQL pattern that keeps a galaxy schema from silently multiplying rows. The warehouse's actual findings and the coach-row bug are on the main engineering page's data-model section - this page is everything underneath that.
Twelve real routes, each doing one job - no page tries to be two things at once.
| Page | Route | What it's for |
|---|---|---|
| Home | / | Rules, scoring, payout table; your own entries if logged in |
| My Entries | /my-entries | Your brackets, a live "how am I doing" rank card during the tournament |
| Submit / Edit Entry | /enter, /edit/<id> | Draft a bracket; locked once the tournament goes live |
| Standings | /leaderboard | Full ranked leaderboard, sortable, with a 30-second live-score widget |
| Details | /report/entries | Every entry's picks, round by round, filterable by player status |
| Compare | /compare | Two brackets head to head - only-A, only-B, both-picked columns |
| Prediction | /predict | Projected final score per entry, from seed-based expected advancement |
| Today's Games | /all-games | Live and upcoming games, each linked to its ESPN box score |
| Upsets | /upsets | Every higher-seed-beats-lower-seed result, with affected-entry counts |
| Player Data | /tourney-data | Every draftable player/coach, season PPG, how many brackets picked them |
| Submitted | /roster | Plain entry + payment-status list, for the organizer to verify who's paid |
| Daily Update | /daily | AI-generated commentary; a code-gated link opens the live generator comparison |
Four tables carry the real weight; the rest are support tables around them.
| Table | Key columns | What it enforces |
|---|---|---|
entries | user_id FK, bracket_name (unique per year), token, paid | One bracket name per participant per year; a magic-link token for access without a full session |
picks | entry_id FK (cascade delete), espnPlayerId FK, groupId 1-5 | Every pick tied to exactly one entry and one seed group; deleting an entry cleans up its picks automatically |
password_reset_tokens | token (unique random hex), expires_at, used | A reset link that's single-use and time-boxed to 24 hours, enforced by two flags, not by trust |
score_run_log | run_id (UUID), status (info/winner/playing/ok/error) | Every ESPN fetch run's outcome, per game, so a bad run is diagnosable after the fact |
Around those four: users and tournament_config hold identity and season state; teams, players, and player_pts are the ESPN-sourced tournament data, reloaded each year and never hand-edited; settings is a plain key-value store for everything from the active year to score-fetch cadence; and a games / player_game_pts / game_odds family holds regular-season data and betting lines - the same tables the warehouse's ETL reads from, never the tournament tables. Three more (daily_commentary, daily_scores_cache, commentary_jobs) exist only to support the AI daily-update feature.
players reuses that team's own ESPN ID as its player ID, because ESPN's roster API has no independent coach identity to key off. It's the same reuse that produced the 756-row inner-join bug on the warehouse side - see the main engineering page.Three terms, each with a one-question test that tells them apart. Get the first one right and the other two mostly fall out of it.
fact_player_points's grain: one player's points, in one tournament game.WHERE or GROUP BY on this column? The nouns - who, what, when, where. dim_team.seed, dim_player.playerName.SUM(), AVG(), or COUNT() on this column? The numbers a real process produced. fact_player_points.pts - meaningless without the dimensions saying whose points, in which game.dim_year has exactly one column, gameYear - nothing to look up. It behaves like a dimension but carries no attributes, so it could live as a plain column with no join at all; it only earns a real table if it grows one."Who scored the most in the tournament, then how did they do in the season" is exactly the move a galaxy schema is for - but joining fact_player_points straight to fact_player_points_season before aggregating is the wrong move. Both facts sit at the same grain (one row per player per game), so a raw join multiplies rows for anyone who played more than one game in either fact.
The fix is drill-across: collapse each fact to the grain actually being compared - player, per year - independently, then join those two already-summarized results through the dimension keys they share.
WITH tourney AS (
SELECT espnPlayerId, gameYear, SUM(pts) AS tourney_pts
FROM fact_player_points GROUP BY espnPlayerId, gameYear
),
season AS (
SELECT espnPlayerId, gameYear, SUM(pts)/COUNT(*) AS season_ppg
FROM fact_player_points_season GROUP BY espnPlayerId, gameYear
)
SELECT p.playerName, t.tourney_pts, s.season_ppg
FROM tourney t
JOIN season s ON s.espnPlayerId = t.espnPlayerId AND s.gameYear = t.gameYear
JOIN dim_player p ON p.espnPlayerId = t.espnPlayerId AND p.gameYear = t.gameYear
ORDER BY t.tourney_pts DESCBy the time the two CTEs meet, neither is a raw fact table anymore - each is already one row per player, so the join can't fan anything out. dim_player only gets pulled in at the end, purely to attach a name to an ID.
The core question: out of season scoring average, seed, and opponent strength, what actually predicts tournament scoring? Answering it takes three distinct steps, each doing a different job - reporting (a chart, available the moment data loads), statistics/ML (testing every candidate factor against real results), and AI narrative (explaining a confirmed finding in plain English, never discovering one).
scikit-learn GradientBoostingRegressor, built on a similar feature set. It's kept as reference for feature ideas only - unregistered, no reachable routes - since this warehouse is the cleaner, more reproducible foundation to build the real answer on.