# Playoffs System Documentation ## 1. Overview The League of Dreams (LOD) playoff system runs during the **POSTSEASON** phase (`league_status_id = 6`). It supports multiple independent **brackets** (e.g. "Draft Picks 1-4", "Draft Picks 5-8", "Draft Picks 9-12"), each with its own multi-round tournament structure. Teams are manually seeded into matchups, accumulate fantasy points over a date range per round, and advance through rounds (Semi-Finals -> Finals). > **Current implementation addendum (2026):** Commissioner setup, automatic winner/loser advancement, slot overrides, scoring operations, and draft-award application now exist. Eligibility follows the 2025 reseeding model: failed teams remain in full brackets and score normally, but receive the final normal draft pick(s) and cannot win supplemental awards. Eligible teams compress forward into picks 1-14 when one team fails; the failed team receives pick 15. Sections below that describe SQL-only setup or no automatic advancement document the legacy implementation. Key characteristics: - **Points-based**, not head-to-head wins. Each team accumulates total fantasy points across the round's date range. - **Position limits** are enforced per-round (max SP games, max hitter games per position). Exceeding limits causes the excess games to be excluded from scoring. - **Standings** aggregate cumulative points across all rounds within a bracket. - **Scoring hooks** into the same daily lineup update pipeline used during the regular season. - **No automatic advancement** - winners are manually seeded into the next round. --- ## 2. Database Schema ### 2.1 Core Playoff Tables (6 tables) #### `playoffs_bracket_types` Lookup table for bracket names. Currently 3 brackets exist. ```sql CREATE TABLE `playoffs_bracket_types` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(30) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `playoffs_bracket_name` (`name`) ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4; ``` | Column | Type | Notes | | ------ | ----------------------- | ------------------------------------------------------------- | | `id` | int, PK, auto-increment | | | `name` | varchar(30), unique | e.g. "Draft Picks 1-4", "Draft Picks 5-8", "Draft Picks 9-12" | #### `playoffs_config` Top-level config: one row per bracket per year. Defines the overall date window for that bracket's playoffs. ```sql CREATE TABLE `playoffs_config` ( `id` int(11) NOT NULL AUTO_INCREMENT, `year` int(11) NOT NULL, `bracket_type_id` int(11) NOT NULL, `start_date` date NOT NULL, `end_date` date NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `playoffs_config_year` (`year`,`bracket_type_id`) ) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8mb4; ``` | Column | Type | Notes | | ----------------- | ----------------------- | --------------------------------------- | | `id` | int, PK, auto-increment | | | `year` | int | Season year | | `bracket_type_id` | int | FK -> `playoffs_bracket_types.id` | | `start_date` | date | Overall playoffs start for this bracket | | `end_date` | date | Overall playoffs end for this bracket | **Unique constraint**: `(year, bracket_type_id)` - one config per bracket per year. #### `playoffs_rounds_config` Defines each round within a bracket (e.g. Semi-Finals, Finals). Each round has its own date range and position limits. ```sql CREATE TABLE `playoffs_rounds_config` ( `id` int(11) NOT NULL AUTO_INCREMENT, `playoffs_config_id` int(11) NOT NULL, `name` varchar(30) DEFAULT NULL, `max_sp_games` int(11) NOT NULL DEFAULT '14', `round_num` int(11) NOT NULL, `start_date` date NOT NULL, `end_date` date NOT NULL, `next_round_id` int(11) DEFAULT NULL, `max_hitter_games` int(11) NOT NULL DEFAULT '14', PRIMARY KEY (`id`), UNIQUE KEY `playoffs_rounds_config_year_round` (`playoffs_config_id`,`round_num`), CONSTRAINT `playoffs_rounds_playoff_config` FOREIGN KEY (`playoffs_config_id`) REFERENCES `playoffs_config` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=19 DEFAULT CHARSET=utf8mb4; ``` | Column | Type | Notes | | -------------------- | ----------------------- | ----------------------------------------------------------------------------- | | `id` | int, PK, auto-increment | | | `playoffs_config_id` | int, FK | -> `playoffs_config.id` | | `name` | varchar(30) | "Semi-Finals", "Finals", etc. | | `max_sp_games` | int, default 14 | Max SP starts allowed in this round | | `max_hitter_games` | int, default 14 | Max games per hitter position in this round (OF gets 3x) | | `round_num` | int | Ordering: 1 = first round, 2 = second round, etc. | | `start_date` | date | Round start | | `end_date` | date | Round end | | `next_round_id` | int, nullable | Self-referencing FK -> `playoffs_rounds_config.id`. NULL for the final round. | **Unique constraint**: `(playoffs_config_id, round_num)` - one round per number per bracket config. #### `playoffs_rounds` The actual matchups. Each row is one matchup within a round, tracking the two teams and their accumulated points/SP games. ```sql CREATE TABLE `playoffs_rounds` ( `id` int(11) NOT NULL AUTO_INCREMENT, `playoffs_rounds_config_id` int(11) NOT NULL, `round_status` varchar(30) DEFAULT NULL, `team_1_id` int(11) DEFAULT NULL, `team_1_points` float NOT NULL DEFAULT '0', `team_1_sp_games` int(11) NOT NULL DEFAULT '0', `team_2_id` int(11) DEFAULT NULL, `team_2_points` float NOT NULL DEFAULT '0', `team_2_sp_games` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `playoffs_rounds_round_team_1` (`playoffs_rounds_config_id`,`team_1_id`), UNIQUE KEY `playoffs_rounds_round_team_2` (`playoffs_rounds_config_id`,`team_2_id`), KEY `playoffs_rounds_team_1_id` (`team_1_id`), KEY `playoffs_rounds_team_2_id` (`team_2_id`), CONSTRAINT `playoffs_rounds_team_1_id` FOREIGN KEY (`team_1_id`) REFERENCES `master_teams` (`id`) ON DELETE CASCADE, CONSTRAINT `playoffs_rounds_team_2_id` FOREIGN KEY (`team_2_id`) REFERENCES `master_teams` (`id`) ON DELETE CASCADE ) ENGINE=InnoDB AUTO_INCREMENT=74989 DEFAULT CHARSET=utf8mb4; ``` | Column | Type | Notes | | --------------------------- | ----------------------- | ---------------------------------------------------- | | `id` | int, PK, auto-increment | | | `playoffs_rounds_config_id` | int | FK -> `playoffs_rounds_config.id` | | `round_status` | varchar(30) | `"IN_PROGRESS"` or `"FINAL"` | | `team_1_id` | int, nullable | FK -> `master_teams.id`. NULL if TBD (future round). | | `team_1_points` | float, default 0 | Accumulated total points (hitter + pitcher) | | `team_1_sp_games` | int, default 0 | Count of SP games used | | `team_2_id` | int, nullable | FK -> `master_teams.id`. NULL if TBD. | | `team_2_points` | float, default 0 | | | `team_2_sp_games` | int, default 0 | | **Unique constraints**: A team can only appear once per round config in each slot. **UPSERT behavior**: `updatePlayoffsRound` uses `ON DUPLICATE KEY UPDATE` keyed on `(playoffs_rounds_config_id, team_1_id)`. #### `playoffs_positions_exceeded` Tracks which teams have exceeded position limits in which round. ```sql CREATE TABLE `playoffs_positions_exceeded` ( id int NOT NULL AUTO_INCREMENT, year INT NOT NULL, master_team_id INT NOT NULL, playoffs_round_id INT NOT NULL, position_code VARCHAR(10) NOT NULL, insert_timestamp TIMESTAMP NOT NULL, UNIQUE KEY `current_teams_positions_exceeded_pos_key` (year, master_team_id, position_code), CONSTRAINT `playoffs_positions_exceeded_team` FOREIGN KEY (`master_team_id`) REFERENCES `master_teams` (`id`) ON DELETE CASCADE, PRIMARY KEY (id) ); ``` | Column | Type | Notes | | ------------------- | ----------------------- | --------------------------------------------- | | `id` | int, PK, auto-increment | | | `year` | int | Season year | | `master_team_id` | int, FK | -> `master_teams.id` | | `playoffs_round_id` | int | FK -> `playoffs_rounds_config.id` | | `position_code` | varchar(10) | e.g. "OF", "SP", "C", "UTIL" | | `insert_timestamp` | timestamp | When the exceeded position was first recorded | **Unique constraint**: `(year, master_team_id, position_code)` - one exceeded record per team per position per year. #### `playoffs_standings` Aggregated cumulative standings across all rounds within a bracket. ```sql -- Created via TypeORM entity (no separate DDL found; uses UPSERT pattern) ``` | Column | Type | Notes | | --------------------- | ----------------------- | -------------------------------------------------- | | `id` | int, PK, auto-generated | | | `playoffs_bracket_id` | int, FK | -> `playoffs_bracket_types.id` | | `year` | int, nullable | Season year | | `master_team_id` | int, nullable, FK | -> `master_teams.id` | | `points` | float, default 0 | Cumulative points across all rounds in the bracket | **Unique constraint**: `(year, master_team_id, playoffs_bracket_id)` **Indexes**: `playoffs_standings_team` on `master_team_id`, `playoffs_standings_bracket` on `playoffs_bracket_id` ### 2.2 Extension Columns on Existing Tables Two boolean columns were added to existing tables (via `db_migration22.sql`): #### `current_teams_daily_lineups.playoffs_position_exceeded_max` ```sql ALTER TABLE current_teams_daily_lineups ADD COLUMN playoffs_position_exceeded_max BOOLEAN NOT NULL DEFAULT FALSE; ``` This column is set to `TRUE` on daily lineup rows for a team when a position has been exceeded during playoffs. **Note**: This column exists on the entity but is managed via raw SQL queries in `lod-mysql.js`, not the TypeORM entity definition. #### `daily_lineups_mlb_games.playoffs_position_exceeded_max` ```sql ALTER TABLE daily_lineups_mlb_games ADD COLUMN playoffs_position_exceeded_max BOOLEAN NOT NULL DEFAULT FALSE; ``` This column flags individual game-to-lineup linkage rows that should be excluded from playoff scoring because the team exceeded the position limit. The hitter playoff scoring query (`getTeamHitterPlayoffStartingPoints`) filters on `playoffs_position_exceeded_max = FALSE`. **Note**: Not reflected in the TypeORM entity definition for `DailyLineupsMlbGame`. ### 2.3 Entity-Relationship Summary ``` playoffs_bracket_types (1) ---< (N) playoffs_config (per year) playoffs_config (1) ---< (N) playoffs_rounds_config (rounds within a bracket) playoffs_rounds_config (1) ---< (N) playoffs_rounds (matchups within a round) playoffs_rounds (N) >--- (1) master_teams (team_1_id, team_2_id) playoffs_bracket_types (1) ---< (N) playoffs_standings (per bracket per team) playoffs_rounds_config (1) ---< (N) playoffs_positions_exceeded master_teams (1) ---< (N) playoffs_positions_exceeded master_teams (1) ---< (N) playoffs_standings playoffs_rounds_config.next_round_id -> playoffs_rounds_config.id (self-referencing, nullable) ``` --- ## 3. Bracket Structure ### 3.1 Bracket Types There are currently 3 brackets, each determining different draft pick rewards: - **Draft Picks 1-4** (bracket_type_id = 1) - **Draft Picks 5-8** (bracket_type_id = 2) - **Draft Picks 9-12** (bracket_type_id = 3) ### 3.2 Round Structure Each bracket has multiple rounds (typically 2: Semi-Finals and Finals): - **Semi-Finals** (round_num = 1): Two matchups of 2 teams each (4 teams total) - **Finals** (round_num = 2): The championship matchup Rounds are linked via `next_round_id`. The Semi-Finals' `next_round_id` points to the Finals' `playoffs_rounds_config.id`. The Finals has `next_round_id = NULL`. ### 3.3 Seeding Seeding is **manual** via direct SQL inserts: - Semi-Finals matchups have `team_1_id` and `team_2_id` populated at setup time. - Finals matchups can be pre-populated with projected teams or left as `NULL` (TBD). - The `getPlayoffs` method handles NULL teams by returning an empty `teams` array for that seed. **Example 2025 seeding** (from `2025-playoffs.sql`): ``` Bracket "Draft Picks 1-4": Semi-Finals: Team 15 vs 8, Team 12 vs 9 Bracket "Draft Picks 5-8": Semi-Finals: Team 1 vs 2, Team 7 vs 3 Bracket "Draft Picks 9-12": Semi-Finals: Team 11 vs 14, Team 6 vs 10 ``` ### 3.4 Advancement There is **no automatic advancement** logic. After a round ends (status = `FINAL`), an admin manually: 1. Updates the Finals round's `team_1_id` and `team_2_id` with the winners. 2. This is done via SQL or an admin tool, not through the API. --- ## 4. Scoring Logic ### 4.1 Overall Flow For each active round (whose `start_date <= now`), the `updatePlayoffs` method calculates: ``` team_total_points = hitter_points + pitcher_points ``` Where: - `hitter_points` = sum of `mlb_hitter_games.points` for starting hitter lineup slots, filtered to exclude games where `playoffs_position_exceeded_max = TRUE` - `pitcher_points` = sum of SP points (capped at `max_sp_games`) + sum of RP points (uncapped) ### 4.2 Hitter Scoring **Query**: `getTeamHitterPlayoffStartingPoints(masterTeamId, startDate, endDate)` ```sql SELECT sum(points) as points, count(*) as count FROM mlb_hitter_games WHERE mlb_sport_id = 1 AND id IN ( SELECT mlb_hitter_game_id FROM daily_lineups_mlb_games WHERE playoffs_position_exceeded_max = FALSE AND daily_lineup_id IN ( SELECT id FROM current_teams_daily_lineups WHERE master_team_id = ? AND lineup_date <= ? AND lineup_date >= ? AND position_id IN ( SELECT id FROM positions WHERE is_hitter_pos = 1 AND is_starting_pos = 1 ) ) ) ``` Key points: - Only counts games where the player had `pa > 0` (plate appearances) for hitter position counting, but the points query itself does not filter on PA. - Uses `playoffs_position_exceeded_max` on `daily_lineups_mlb_games` (NOT the regular season `position_exceeded_max` on `current_teams_daily_lineups`). - Only starting hitter positions count (C, 1B, 2B, 3B, SS, OF, UTIL - not bench). ### 4.3 SP (Starting Pitcher) Scoring **Query**: `getTeamPitcherPlayoffsPointsWithMaxSpGames(masterTeamId, startDate, endDate, maxSpGames)` This method: 1. Calls `getTeamPitcherPlayoffsGames` to get all pitcher games in the date range. 2. Filters pitchers into SP and RP categories. 3. Sorts SP games by `game_date` ascending. 4. **Caps SP games** at `max_sp_games` (takes only the first N chronologically). 5. Sums the points for the capped SP set. **SP classification** (a pitcher is counted as SP if): - `started_game = 1` (was the starting pitcher), OR - `outs >= 12` (pitched at least 4 innings, even in relief — treated as SP-equivalent) **Critical filtering** (from `getTeamPitcherPlayoffsGames`): ```sql WHERE pos.is_hitter_pos = 0 AND pos.is_starting_pos = 1 AND ctdl.playoffs_position_exceeded_max IS FALSE AND ctdl.master_team_id = ? AND ctdl.lineup_date BETWEEN ? AND ? AND rp_started_mlb_game IS FALSE AND sp_relieved_mlb_game IS FALSE AND mlb_sport_id = 1 ``` - `rp_started_mlb_game IS FALSE`: Excludes pitchers who were slotted in an RP lineup slot but actually started the MLB game. - `sp_relieved_mlb_game IS FALSE`: Excludes pitchers who were slotted in an SP lineup slot, relieved, and recorded fewer than 12 outs. This means: - **A pitcher in an SP slot who relieved for fewer than 12 outs** -> scores 0 points (excluded by `sp_relieved_mlb_game`) - **A pitcher in an SP slot who relieved for 12+ outs** -> scores normally as SP-equivalent - **A pitcher in an RP slot who started** -> scores 0 points (excluded by `rp_started_mlb_game`) ### 4.4 RP (Relief Pitcher) Scoring From the same `getTeamPitcherPlayoffsGames` query, RP rows are: - `started_game = 0` AND `outs < 12` (came in as relief and pitched less than 4 IP) RP games are **not capped** - all RP games count toward scoring. ### 4.5 Verified Point Values (from tests) | Scenario | Expected Points | | ---------------------------------------------------- | -------------------------- | | Hitter (Ronald Acuna Jr, 2023-08-02, OF slot) | 3.6 | | SP (Eduardo Rodriguez, 2023-08-02, SP slot) | 3.23 | | RP (Holby Milner, 2023-08-02, RP slot) | 0.32 | | All positions combined (Hitter + SP + RP) | 7.15 (= 3.6 + 3.23 + 0.32) | | SP-slot relief appearance under 12 outs | 0 | | RP in RP slot who actually started (Scott Alexander) | 0 | --- ## 5. Position Limits ### 5.1 Max Games Per Position Per Round Each `playoffs_rounds_config` defines: - `max_sp_games`: Maximum SP starts allowed in the round (default 14). - `max_hitter_games`: Maximum games per hitter position (default 14). Position-specific limits: | Position | Max Games Allowed | |----------|------------------| | C | `max_hitter_games` | | 1B | `max_hitter_games` | | 2B | `max_hitter_games` | | 3B | `max_hitter_games` | | SS | `max_hitter_games` | | OF | `max_hitter_games * 3` (because 3 OF slots exist) | | UTIL | `max_hitter_games` | | SP | `max_sp_games` | | RP | **No limit** (RP is never checked for exceeding) | ### 5.2 How Position Limits Are Enforced The `checkExceededMaxPositions` method (in `PlayoffsUtil.js`): 1. Gets the current position counts for a team in the round's date range. 2. Compares each position's count against the limit. 3. Returns exceeded position codes. The `updateRoundExceededPositions` method: 1. Calls `checkExceededMaxPositions`. 2. Inserts into `playoffs_positions_exceeded` table (UPSERT). 3. Calls `updatePlayoffsDailyLineupExceededPositions` to flag individual game rows. ### 5.3 How Exceeded Games Are Flagged `updatePlayoffsDailyLineupExceededPositions(startDate, endDate, masterTeamId, positionCodes, maxHitterGames)`: For each exceeded position: 1. Gets all `daily_lineups_mlb_games` rows for that position in the date range (ordered by `lineup_date`). 2. For SP, uses `getCurrentTeamsDailyLineupStartingPitchingRows` (which filters on `started_game = 1`). 3. If total games > max allowed, the **excess rows** (everything after index `maxGames`) get `playoffs_position_exceeded_max = TRUE`. 4. This means the **earliest games count**, and only the overflow games are excluded. ### 5.4 SP Limit Enforcement (Dual Mechanism) SP limits are enforced in **two ways**: 1. **At scoring time**: `getTeamPitcherPlayoffsPointsWithMaxSpGames` slices SP rows to `maxSpGames` (chronologically), so only the first N SP starts get points. 2. **At flagging time**: `updatePlayoffsDailyLineupExceededPositions` sets `playoffs_position_exceeded_max = TRUE` on overflow rows for downstream queries. ### 5.5 Verified Position Limit Scenarios (from tests) **Hitter over limit** (max_hitter_games = 1): - Team starts OF and UTIL across 4 days. - OF gets 3 games (3 > 1*3=3... actually OF is allowed max_hitter_games * 3 = 3), UTIL gets 1 game (capped at 1). - Expected points: 1.5 (only games within limits counted). **SP over limit** (max_sp_games = 1): - Team starts 4 SPs across 4 days. Only 1 SP start counts. - Taijuan Walker (earliest start, 2023-07-31) with 3.3 points is the one that counts. - Expected points: 3.3 --- ## 6. Round Status & Progression ### 6.1 Status Values | Status | Meaning | | ------------- | --------------------------------------------- | | `IN_PROGRESS` | Round is active, points are being accumulated | | `FINAL` | Round has ended, points are locked | ### 6.2 Status Transition Logic In `updatePlayoffs()`, for each round: ```javascript let status = 'IN_PROGRESS' if (DateTime.fromISO(rc.end_date).toISODate() < now.toISODate()) { status = 'FINAL' } ``` A round becomes `FINAL` when `now > end_date` (strictly after the end date, not on it). ### 6.3 Date Clamping Throughout the scoring logic, the `endDate` used for queries is clamped: ```javascript const endDate = now > DateTime.fromISO(rc.end_date) ? DateTime.fromISO(rc.end_date) : now ``` This ensures: - During a round, only games up to "today" are counted. - After a round ends, only games within the round's date range are counted. ### 6.4 Manual Advancement There is no code for automatic round advancement. After Semi-Finals are FINAL: 1. Admin determines the winners (higher points). 2. Admin manually updates the Finals `playoffs_rounds` rows with the winning `team_1_id` and `team_2_id`. 3. The system then starts calculating points for the Finals round once its `start_date` arrives. --- ## 7. Daily Update Integration ### 7.1 Entry Point The playoff scoring hooks into `TeamsUtil.updateDailyLineupsInGame()` (line ~610): ```javascript if (leagueConfig.league_status_id === 6) { const playoffUtil = new PlayoffsUtil() await playoffUtil.updatePlayoffs(leagueConfig.current_year) } ``` This runs **after** the regular season scoring updates (`updateDailyLineupsInGameStats` and `updateAllTeamsPoints`). ### 7.2 Full Pipeline Order 1. `TeamsUtil.updateDailyLineupsInGame(date)` is called (by scheduled job or test). 2. Regular season stats are updated via `updateDailyLineupsInGameStats`. 3. Regular season team points are updated via `updateAllTeamsPoints`. 4. **If `league_status_id === 6` (POSTSEASON)**: a. `PlayoffsUtil.updatePlayoffs(year)` is called. b. For each active round config (start_date <= now): - For each matchup in the round: - `updateRoundExceededPositions` for both teams (checks and flags exceeded positions). - For each matchup (second pass): - Calculate pitcher points (SP capped + RP uncapped) for both teams. - Calculate hitter points for both teams. - Update `playoffs_rounds` with new points, SP game counts, and status. c. Aggregate standings: sum points across all rounds for each team in each bracket. d. Upsert `playoffs_standings` for each team. 5. Regular season exceeded positions are checked (separate from playoffs). ### 7.3 Important: Two-Pass Processing The exceeded positions check runs **before** the scoring pass. This ensures that: 1. First pass: Flag which positions are exceeded and mark `playoffs_position_exceeded_max` on game rows. 2. Second pass: Score points, which naturally excludes flagged rows. --- ## 8. API Endpoints ### 8.1 GET `/playoffs/:year` **Route file**: `server/app/routes/league/getPlayoffs.js` **Authentication**: Standard auth middleware (inherited from parent router). **Request**: `GET /playoffs/2023` **Response**: Array of bracket objects. Full response shape: ```typescript type PlayoffsResponse = PlayoffsBracket[] interface PlayoffsBracket { name: string // e.g. "Draft Picks 1-4" rounds: PlayoffRound[] standings: PlayoffStanding[] } interface PlayoffRound { config: { id: number // playoffs_rounds_config.id name: string // "Semi-Finals", "Finals" next_round_id: number | null // playoffs_rounds_config.id of next round, or null start_date: string // ISO date string (e.g. "2023-07-15T04:00:00.000Z") end_date: string // ISO date string } seeds: PlayoffSeed[] } interface PlayoffSeed { teams: PlayoffTeam[] // Empty array if teams are TBD (both team IDs null) status: string // "IN_PROGRESS" or "FINAL" } interface PlayoffTeam { id: number // master_team_id points: number // Accumulated total points (hitter + pitcher) sp_games: number // SP game count hitter_games: PositionCount[] // Array of position counts for the round } interface PositionCount { count: number position_code: string // "C", "1B", "2B", "3B", "SS", "OF", "UTIL" } interface PlayoffStanding { id: number playoffs_bracket_id: number year: number master_team_id: number points: number // Cumulative points across all rounds } ``` **Example response** (single bracket, one hitter playing): ```json [ { "name": "Draft Picks 1-4", "rounds": [ { "config": { "id": 2, "name": "Semi-Finals", "next_round_id": 1, "start_date": "2023-07-15T04:00:00.000Z", "end_date": "2023-08-14T04:00:00.000Z" }, "seeds": [ { "teams": [ { "id": 1, "points": 3.6, "sp_games": 0, "hitter_games": [{ "count": 1, "position_code": "OF" }] }, { "id": 2, "points": 0, "sp_games": 0, "hitter_games": [] } ], "status": "IN_PROGRESS" }, { "teams": [ { "id": 3, "points": 0, "sp_games": 0, "hitter_games": [] }, { "id": 4, "points": 0, "sp_games": 0, "hitter_games": [] } ], "status": "IN_PROGRESS" } ] }, { "config": { "id": 1, "name": "Finals", "next_round_id": null, "start_date": "2023-08-15T04:00:00.000Z", "end_date": "2023-08-30T04:00:00.000Z" }, "seeds": [ { "teams": [ { "id": 1, "points": 0, "sp_games": 0, "hitter_games": [] }, { "id": 3, "points": 0, "sp_games": 0, "hitter_games": [] } ], "status": "IN_PROGRESS" } ] } ], "standings": [ { "id": 1, "playoffs_bracket_id": 1, "year": 2023, "master_team_id": 1, "points": 3.6 }, { "id": 2, "playoffs_bracket_id": 1, "year": 2023, "master_team_id": 2, "points": 0 }, { "id": 3, "playoffs_bracket_id": 1, "year": 2023, "master_team_id": 3, "points": 0 }, { "id": 4, "playoffs_bracket_id": 1, "year": 2023, "master_team_id": 4, "points": 0 } ] } ] ``` ### 8.2 Additional DB-Level Admin Operations These are called from `PlayoffsUtil` but don't have dedicated REST routes — they're used internally or via admin tools: | Method | Purpose | | --------------------------------------------------------------------------------------------- | -------------------------------------------------- | | `updatePlayoffsConfig(year, bracketTypeId, startDate, endDate)` | UPSERT a bracket's config for a year | | `updatePlayoffsRoundConfig(playoffConfigId, roundNum, startDate, endDate, nextRoundId, name)` | UPSERT a round config | | `updatePlayoffsRound(...)` | UPSERT a matchup with points, SP games, and status | | `updatePlayoffStandings(year, masterTeamId, bracketId, points)` | UPSERT cumulative standings | | `updatePlayoffsExceededPositions(exceededRowsArray)` | Batch UPSERT exceeded positions | | `updatePlayoffsDailyLineupExceededPositions(...)` | Flag individual game rows as exceeded | --- ## 9. Client UI ### 9.1 Component Hierarchy ``` pages/index.jsx └── (conditional: league_status_id === POSTSEASON) └── Playoffs.jsx ├── PlayoffsBracket.jsx (per bracket) │ └── react-brackets component │ └── RenderSeed (custom seed renderer) └── StandingsByPointsTable.jsx (per bracket) ``` ### 9.2 Conditional Display The Playoffs component only renders when the league is in POSTSEASON status: ```jsx // pages/index.jsx {leagueData.league_status_id === Constants.LEAGUE_STATUS.POSTSEASON ? ( ) : ( ... )} ``` `LEAGUE_STATUS.POSTSEASON = 6` ### 9.3 Playoffs.jsx (Main Container) **File**: `client/components/league/playoffs/Playoffs.jsx` Responsibilities: 1. Fetches playoff data from `GET /playoffs/:year` via `ApiUtil.getPlayoffs()`. 2. **Auto-refreshes every 30 seconds** via `setInterval`. 3. Transforms API response into format needed by `react-brackets`. 4. Renders one `PlayoffsBracket` + one `StandingsByPointsTable` per bracket. Data transformation: - Maps `seed.teams` to include resolved `team_name` (from global `teams` context). - Formats date range as `"YYYY-MM-DD to YYYY-MM-DD"` for display. - Maps `sp_games` -> `spGames`, `hitter_games` -> `hitterGames`. ### 9.4 PlayoffsBracket.jsx (Bracket Visualization) **File**: `client/components/league/playoffs/PlayoffsBracket.jsx` Uses the `react-brackets` library (`Bracket`, `Seed`, `SeedItem`, `SeedTeam`, `SeedTime` components). Custom `RenderSeed` component features: - **Team names and points** displayed side by side for each matchup. - **Visual weight by status**: - `FINAL` + winning team: `fontWeight: 800` (bold) - `IN_PROGRESS`: `fontWeight: 400` (normal) - `FINAL` + losing team: `fontWeight: 100` (light), `color: #b3b1b1` (grayed out) - **Position counts tooltip**: Hovering over "Position Counts" shows a tooltip with game counts for each position (SP, C, 1B, 2B, 3B, SS, OF, UTIL). Defaults to 0 for positions with no games. - **Date range** shown below each matchup via `SeedTime`. - Mobile breakpoint at 767px. ### 9.5 StandingsByPointsTable.jsx **File**: `client/components/global/StandingsByPointsTable.jsx` A simple table showing cumulative standings: - Columns: Place, Team Name, Points - Sorted by points descending (highest first). - Place is computed dynamically (1, 2, 3...). - Team names resolved via `formatTeamName()`. - Alternating row colors (`#ecf0f1` / `#ffffff`). ### 9.6 PositionCounts.jsx (Team Page) **File**: `client/components/team/PositionCounts.jsx` This is on the **team page** (not the playoffs page directly), but is relevant because it shows position usage during the season to help teams manage their games. During playoffs, teams need to be aware of their position counts relative to playoff round limits. The component shows: - Games played per position - Max allowed (from league config, not playoffs-specific) - Projected pace - Warning/error highlighting for over-max or under-min **Note**: This component uses regular season `position_max_games` and `pitcher_max_games` from `leagueData`, **not** the per-round playoff limits from `playoffs_rounds_config`. --- ## 10. Standings ### 10.1 Aggregation Logic In `updatePlayoffs()`, after all rounds are scored, standings are computed by: ```javascript // For each round with populated teams: bracketTeams[round.team_1_id].points += round.team_1_points bracketTeams[round.team_2_id].points += round.team_2_points ``` This sums points across **all rounds** in the bracket (Semi-Finals + Finals), not just the current round. ### 10.2 Standings UPSERT ```sql INSERT INTO playoffs_standings (year, master_team_id, playoffs_bracket_id, points) VALUES (?, ?, ?, ?) ON DUPLICATE KEY UPDATE year = ?, master_team_id = ?, playoffs_bracket_id = ?, points = ? ``` Keyed on `(year, master_team_id, playoffs_bracket_id)`. ### 10.3 Display Standings are displayed per bracket via `StandingsByPointsTable`, sorted by total points descending. This gives a comprehensive view of each team's performance across the entire playoff run. --- ## 11. Test Coverage **File**: `server/app/__tests__/playoffs.test.ts` ### 11.1 Test Setup Each test: 1. Truncates all relevant tables. 2. Resets league config data (sets MLB games to Final status, resets limits). 3. Inserts master team test data (teams 1-6). 4. Inserts playoffs test data (one bracket "Draft Picks 1-4" with Semi-Finals and Finals). 5. Signs players, sets lineups, then calls `teamsUtil.updateDailyLineupsInGame(date)`. 6. Asserts on both `playoffs_standings` table AND the `GET /playoffs/2023` API response. Test fixture data (from `insertPlayoffsTestData`): - One bracket: "Draft Picks 1-4" - Semi-Finals: Team 1 vs Team 2, Team 3 vs Team 4 (round dates: 2023-07-15 to 2023-08-14) - Finals: Team 1 vs Team 3, Team 2 vs Team 4 (round dates: 2023-08-15 to 2023-08-30) - Default `max_sp_games = 1`, `max_hitter_games = 3` ### 11.2 Test Scenarios | # | Scenario | Key Assertion | | --- | ------------------------------ | ------------------------------------------------------------------------------------------------------------------------------------------------------------ | | 1 | **Hitter one day** | Ronald Acuna Jr in OF slot, 2023-08-02. Points = 3.6. | | 2 | **SP one day** | Eduardo Rodriguez in SP slot, 2023-08-02. Points = 3.23, sp_games = 1. | | 3 | **RP one day** | Holby Milner in RP slot, 2023-08-02. Points = 0.32, sp_games = 0. | | 4 | **All positions one day** | Hitter + SP + RP combined. Points = 7.15 (3.6 + 3.23 + 0.32). | | 5 | **SP slot, short relief** | Pitcher relieved for 9 outs from an SP slot. Points = 0. Excluded by `sp_relieved_mlb_game` flag. | | 6 | **RP slot but started** | Scott Alexander in RP slot, but actually started. Points = 0. Excluded by `rp_started_mlb_game` flag. | | 7 | **Hitter over position limit** | `max_hitter_games = 1`. OF plays 3 games (OF limit = 1\*3 = 3, so not exceeded actually... UTIL plays across days). Points = 1.5 (only games within limits). | | 8 | **SP over pitcher limit** | `max_sp_games = 1`. 4 SPs across 4 days, but only 1 SP start counts. Points = 3.3 (Taijuan Walker, earliest start). | ### 11.3 What Each Test Validates Every test validates **both**: 1. Direct DB query: `PlayoffsStandings.find({ master_team_id: 1 })` for the expected points value. 2. API response: Full `GET /playoffs/2023` response shape including rounds, seeds, teams, hitter_games, standings. --- ## 12. Rewrite Notes ### 12.1 Known Issues 1. **No automatic advancement**: Winners must be manually seeded into the next round. This is error-prone and requires admin SQL access. 2. **Entity/schema mismatch**: The `DailyLineupsMlbGame` entity does not include `playoffs_position_exceeded_max` as a column. The `current_teams_daily_lineups` entity similarly lacks the `playoffs_position_exceeded_max` column. These are managed purely via raw SQL. 3. **Stale `spRows` reference**: In `lod-mysql.js` line ~2714, there's a dangling `spRows;` statement after the `getPlayersByTeam` method that appears to be a copy-paste artifact. 4. **Dual SP limit enforcement**: SP games are limited both by the `slice(0, maxSpGames)` in scoring AND by the `playoffs_position_exceeded_max` flag. This is potentially confusing and could lead to double-counting if not carefully maintained. 5. **Exceeded positions unique constraint**: The `playoffs_positions_exceeded` table's unique key is `(year, master_team_id, position_code)` — it does NOT include `playoffs_round_id`. This means a team can only have one exceeded record per position per year, not per round. If a team exceeds OF in Semi-Finals and also in Finals, only one record would exist. 6. **N+1 query pattern**: `getPlayoffs()` calls `getCurrentTeamsDailyLineupPlayoffsStartingHittingPositions` inside a nested loop (per seed, per round, per bracket), leading to many individual DB queries. 7. **Error handling**: `updatePlayoffs()` catches all errors at the top level with only `console.error`, potentially masking issues. 8. **No admin API for seeding/advancement**: All bracket setup is via direct SQL. ### 12.2 Improvement Opportunities for Rewrite 1. **Automatic advancement**: When a round goes FINAL, automatically populate the next round's matchup based on the winner (higher points). 2. **Consolidated position limit enforcement**: Choose one mechanism for SP limits (either the slice approach or the flag approach, not both). 3. **Batch queries**: Replace the N+1 pattern in `getPlayoffs()` with a single query that gets all position counts for all teams in all rounds at once. 4. **Admin API**: Add REST endpoints for: - Creating/editing bracket configs and round configs. - Seeding teams into matchups. - Advancing winners to the next round. - Viewing/clearing exceeded positions. 5. **Real-time updates**: Consider WebSocket/SSE for live playoff scoring instead of 30-second polling. 6. **TypeORM entity alignment**: Add `playoffs_position_exceeded_max` columns to the relevant entities so TypeORM and raw SQL stay in sync. 7. **Standings per round**: Add a view or API endpoint showing standings per round (not just cumulative), so users can see how each team performed in each round individually. 8. **Flexible bracket sizes**: Support brackets with more than 4 teams (quarter-finals, etc.) without requiring code changes. 9. **Position limit visibility on playoffs page**: Show the round's `max_sp_games` and `max_hitter_games` in the bracket UI so users know the limits without looking elsewhere. 10. **Historical playoffs**: Support viewing past years' playoff results, not just the current year.

League Rules

Table of Contents

Overview And Calendar

Contracts

Roster Management

Position Eligibility

Lineup Changes

Salary Cap Details

Salary Rules

Trading

Scoring System

Draft

League Operations

Definitions

Examples

Player Search Overview

Changelog

Draft Guide

Draft Owner Guide

Notification Preferences

Payroll Calculator Tabs

Playoffs E2e Test Results

Playoffs Plan