255 lines
13 KiB
Markdown
255 lines
13 KiB
Markdown
# Plan: Generalized Match Import, Swiss Stage Tracking, Live Scores, and Display System
|
||
|
||
## Context
|
||
|
||
The current system tracks tournament stage entry/exit manually with no match-level data, no automated schedule/result imports, and no public tournament display. The goal is a sport-agnostic match sync infrastructure (usable for MLB schedules, CS2 Swiss rounds, EPL fixtures, etc.) with CS2 as the first implementation target.
|
||
|
||
**Scope decision**: The new `season_matches` table is generic from day one. CS2 Swiss uses nullable `match_stage`/`match_round` columns; MLB just leaves those NULL. Playoff bracket matches remain in the existing `playoff_matches` table (already works well with complex progression logic). This avoids proliferating per-sport match tables.
|
||
|
||
---
|
||
|
||
## Status
|
||
|
||
- [x] Phase 1 — Generic Data Model + CS2 Admin UI ✓
|
||
- [x] Phase 2 — Generic Match Sync Adapter + Cron Job ✓
|
||
- [x] Phase 3 — Public Tournament & Schedule Display + Live Scores ✓
|
||
- [x] Phase 4 — Automated Playoff Bracket Updates ✓
|
||
|
||
---
|
||
|
||
## Phase 1 — Generic Data Model + CS2 Admin UI (no external API)
|
||
|
||
### 1a. Schema additions (`database/schema.ts`)
|
||
|
||
**New enum**: `matchStatusEnum` = `["scheduled", "in_progress", "complete", "canceled", "postponed"]`
|
||
|
||
**New table: `season_matches`** — generic match record for regular season, Swiss rounds, group stages, fixtures
|
||
```
|
||
id (uuid PK)
|
||
sports_season_id (uuid FK → sportsSeasons, onDelete: cascade)
|
||
scoring_event_id (uuid FK → scoringEvents, nullable — set for CS2 stage matches, null for MLB regular season)
|
||
participant1_id (uuid FK → seasonParticipants, nullable)
|
||
participant2_id (uuid FK → seasonParticipants, nullable)
|
||
winner_id (uuid FK → seasonParticipants, nullable)
|
||
participant1_score (integer nullable)
|
||
participant2_score (integer nullable)
|
||
match_stage (integer nullable) — CS2: 1/2/3 for Opening/Challengers/Legends; NULL for MLB
|
||
match_round (integer nullable) — CS2: round within stage (1-5); MLB: series game number or NULL
|
||
matchday (integer nullable) — EPL/MLS fixture week; NULL for CS2
|
||
is_series (boolean default false) — true for Bo3/Bo5 CS2 matches
|
||
status (matchStatusEnum default 'scheduled')
|
||
scheduled_at (timestamp nullable)
|
||
started_at (timestamp nullable)
|
||
completed_at (timestamp nullable)
|
||
external_match_id (varchar 255 nullable) — API ID for upsert conflict key
|
||
created_at, updated_at (timestamps)
|
||
```
|
||
Indexes: unique on `external_match_id` (partial, WHERE NOT NULL), index on `(sports_season_id, status)`, index on `(sports_season_id, match_stage, match_round)`, index on `(scoring_event_id)`.
|
||
|
||
**New table: `match_sub_games`** — per-sub-game scores (CS2 maps, MLB innings if desired, etc.)
|
||
```
|
||
id (uuid PK)
|
||
season_match_id (uuid FK → seasonMatches, onDelete: cascade)
|
||
game_number (integer) — map 1/2/3 for CS2, game 1-7 for series, inning for baseball
|
||
game_label (varchar 100 nullable) — CS2: "Mirage", "Inferno"; others: NULL or "Game 1"
|
||
participant1_score (integer nullable)
|
||
participant2_score (integer nullable)
|
||
winner_id (uuid FK → seasonParticipants, nullable)
|
||
status (matchStatusEnum default 'scheduled')
|
||
started_at, completed_at (timestamps nullable)
|
||
external_game_id (varchar 255 nullable)
|
||
```
|
||
Unique index on `(season_match_id, game_number)`.
|
||
|
||
**New column on `sportsSeasons`**: `external_season_id varchar(255) nullable` — links to the API's identifier for this season/tournament (PandaScore tournament ID for CS2, ESPN league season ID for MLB, etc.)
|
||
|
||
Add Drizzle `relations()` for both new tables following the existing patterns.
|
||
|
||
Run `npm run db:generate` after changes.
|
||
|
||
### 1b. New model: `app/models/season-match.ts`
|
||
- `upsertSeasonMatch(data)` — conflict on `external_match_id`, update scores/status/timestamps
|
||
- `findSeasonMatchesBySportsSeasonId(sportsSeasonId, filters?)` — joined with participant names
|
||
- `findSeasonMatchesByScoringEventId(scoringEventId)` — for CS2 stage view, ordered stage → round
|
||
- `findSeasonMatchesByStage(sportsSeasonId, stage)` — CS2 per-stage query
|
||
- `upsertMatchSubGame(data)`
|
||
- `findMatchSubGamesByMatchId(matchId)`
|
||
|
||
### 1c. Extend cs2-setup admin route
|
||
File: `app/routes/admin.sports-seasons.$id.events.$eventId.cs2-setup.tsx`
|
||
- Add loader query calling `findSeasonMatchesByScoringEventId`
|
||
- Add read-only Swiss rounds section below existing stage assignment UI: per round shows `Team A (W-L) vs Team B (W-L) → maps [16-14, 14-16, 16-12]`
|
||
- "Sync from API" button (fetcher POST to cron endpoint, wired in Phase 2)
|
||
|
||
### 1d. New admin route: manual match entry
|
||
File: `app/routes/admin.sports-seasons.$id.events.$eventId.swiss-matches.tsx`
|
||
Actions: `update-match-result`, `create-manual-match`, `delete-match` — manual fallback when API unavailable
|
||
Register in `app/routes.ts` under admin layout.
|
||
|
||
### 1e. Tests
|
||
- `app/models/__tests__/season-match.test.ts` — upsert conflict resolution, ordering, null stage/round handling
|
||
|
||
---
|
||
|
||
## Phase 2 — Generic Match Sync Adapter + Cron Job
|
||
|
||
### External Data Sources
|
||
- **CS2** (and future Dota2/LoL): **PandaScore** — free tier, 1,000 req/hour, no credit card needed. `PANDASCORE_API_KEY` env var.
|
||
- **MLB/NBA/NFL/MLS/NHL etc.**: ESPN public API (already used for standings), or sport-specific APIs already in `standings-sync/`. Same free, no-auth pattern.
|
||
|
||
### 2a. New service directory: `app/services/match-sync/`
|
||
|
||
**`types.ts`** — generic adapter interface:
|
||
```typescript
|
||
interface MatchRecord {
|
||
externalMatchId: string;
|
||
team1ExternalId: string;
|
||
team2ExternalId: string;
|
||
team1Score: number | null;
|
||
team2Score: number | null;
|
||
winnerExternalId: string | null;
|
||
status: "scheduled" | "in_progress" | "complete" | "canceled" | "postponed";
|
||
scheduledAt: Date | null;
|
||
startedAt: Date | null;
|
||
completedAt: Date | null;
|
||
matchStage?: number | null; // CS2: 1/2/3; omit for MLB
|
||
matchRound?: number | null; // CS2: round within stage; omit for MLB
|
||
matchday?: number | null; // EPL/MLS fixture week
|
||
isSeries?: boolean;
|
||
subGames?: SubGameRecord[];
|
||
}
|
||
interface SubGameRecord {
|
||
externalGameId?: string;
|
||
gameNumber: number;
|
||
gameLabel?: string; // CS2 map name, etc.
|
||
team1Score: number | null;
|
||
team2Score: number | null;
|
||
winnerExternalId?: string | null;
|
||
status: "scheduled" | "in_progress" | "complete";
|
||
}
|
||
interface MatchSyncAdapter {
|
||
fetchMatches(externalSeasonId: string): Promise<MatchRecord[]>;
|
||
supportsLiveScores: boolean;
|
||
}
|
||
interface MatchSyncResult {
|
||
created: number;
|
||
updated: number;
|
||
unchanged: number;
|
||
unmatchedTeams: {externalId: string; name: string}[];
|
||
errors: {externalMatchId: string; error: string}[];
|
||
}
|
||
```
|
||
|
||
**`pandascore.ts`** — `PandaScoreMatchSyncAdapter implements MatchSyncAdapter`. Calls PandaScore CS2 matches endpoint. Maps stage/round/game (map) fields.
|
||
|
||
**`espn-schedule.ts`** — `EspnScheduleAdapter implements MatchSyncAdapter` (Phase 2b). Calls ESPN schedule endpoints for MLB, NBA, MLS, etc. `team1Score`/`team2Score` from ESPN scores.
|
||
|
||
**`index.ts`** — `syncMatches(sportsSeasonId)` orchestrator, mirrors `syncStandings()` exactly:
|
||
1. Load sportsSeason + sport, extract `externalSeasonId`
|
||
2. `getMatchSyncAdapter(simulatorType)` → picks adapter
|
||
3. Fetch matches, resolve participants via `externalId` → name fallback
|
||
4. Bulk upsert into `season_matches` + `match_sub_games`
|
||
5. Return `MatchSyncResult`
|
||
|
||
```typescript
|
||
function getMatchSyncAdapter(simulatorType: string): MatchSyncAdapter {
|
||
switch (simulatorType) {
|
||
case "cs2_major_qualifying_points": return new PandaScoreMatchSyncAdapter();
|
||
case "mlb_bracket": return new EspnScheduleAdapter("baseball/mlb");
|
||
case "nba_bracket": return new EspnScheduleAdapter("basketball/nba");
|
||
case "mls_bracket": return new EspnScheduleAdapter("soccer/mls");
|
||
// ... expand as needed
|
||
default: throw new Error(`No match sync adapter for "${simulatorType}"`);
|
||
}
|
||
}
|
||
```
|
||
|
||
### 2b. New cron job: `app/routes/admin/jobs.sync-matches.ts`
|
||
Exact same pattern as `jobs.sync-and-simulate.ts`:
|
||
- `POST /admin/jobs/sync-matches` with `requireCronSecret` auth
|
||
- Finds active sports seasons where `externalSeasonId IS NOT NULL`
|
||
- Calls `syncMatches(season.id)` for each; triggers simulation if results changed
|
||
- Returns `{ synced, unchanged, errors }`
|
||
|
||
Register in `app/routes.ts`.
|
||
|
||
### 2c. Admin form: add `external_season_id` field to sports season admin form
|
||
|
||
### 2d. Tests
|
||
- `app/services/match-sync/__tests__/pandascore.test.ts`
|
||
- `app/services/match-sync/__tests__/espn-schedule.test.ts`
|
||
- `app/services/match-sync/__tests__/sync.test.ts` — idempotency, name matching, null stage/round handling
|
||
|
||
---
|
||
|
||
## Phase 3 — Public Tournament & Schedule Display + Live Scores
|
||
|
||
### 3a. CS2 Tournament component: `app/components/scoring/Cs2TournamentBracket.tsx`
|
||
Tab layout: **Swiss Stages** | **Champions Stage**
|
||
- Swiss Stages tab: sub-tabs Stage 1 / 2 / 3. Each shows W-L standings table + rounds accordion. Each round: pairing, map scores, status badge.
|
||
- Champions Stage tab: reuses existing `<PlayoffBracket />` with `bracketTemplateId="simple_8"`.
|
||
- Status badges: `scheduled` (gray), `in_progress` (amber pulse), `complete` (green).
|
||
|
||
### 3b. Generic schedule component: `app/components/scoring/MatchSchedule.tsx`
|
||
Reusable across all sports. Shows upcoming + completed matches with scores. Used for MLB schedule view, EPL fixtures, etc. Props: `matches: SeasonMatch[]`, `sport`.
|
||
|
||
### 3c. New public route: `app/routes/sports-seasons.$sportsSeasonId.tournament.tsx`
|
||
URL: `/sports-seasons/:sportsSeasonId/tournament` — league-independent.
|
||
Loader: fetches `season_matches` + playoff matches (Champions Stage) + cs2 stage results.
|
||
CS2 renders `<Cs2TournamentBracket>`, other sports render `<MatchSchedule>` based on `simulatorType`.
|
||
Live polling: if any match `in_progress`, `useRevalidator()` re-fetches every 30 seconds.
|
||
|
||
Register in `app/routes.ts` outside admin layout.
|
||
|
||
### 3d. Optional: Socket.IO push
|
||
Emit `season-match-updated` from `syncMatches` when a match transitions to `in_progress` or `complete`. Phase 3 enhancement, deferred until polling is stable.
|
||
|
||
---
|
||
|
||
## Phase 4 — Automated Playoff Bracket Updates (future)
|
||
|
||
Playoff bracket matches (`playoff_matches`) can also be auto-synced using the same `MatchSyncAdapter` interface. This is deferred because playoff sync is more complex than schedule sync — it must trigger `processMatchResult()` and bracket progression logic correctly, not just upsert data.
|
||
|
||
**What's needed to enable this:**
|
||
1. Add `external_match_id varchar(255) nullable` column to `playoff_matches` (one migration)
|
||
2. Add `fetchPlayoffMatches(externalSeasonId)` to the adapter (or reuse `fetchMatches` with a `matchType` flag)
|
||
3. Add a `syncPlayoffResults(sportsSeasonId)` path in `match-sync/index.ts` that writes to `playoff_matches` and calls the existing `setMatchWinner()` / `processMatchResult()` pipeline
|
||
4. Handle idempotency — don't re-trigger scoring if winner is already set
|
||
|
||
The same `PandaScoreMatchSyncAdapter` and `EspnScheduleAdapter` used for schedule sync will handle playoff data too, since they already return match results regardless of format.
|
||
|
||
---
|
||
|
||
## What Does NOT Change (Phases 1–3)
|
||
- `cs2_major_stage_results` — still the source of truth for CS2 stage entry/exit/placement
|
||
- Fantasy scoring pipeline — fully untouched
|
||
- `playoff_matches` / `playoff_match_games` — unchanged, still used for all bracket progression
|
||
- Existing CS2 admin setup UI — works as before, just gains a new read-only section
|
||
|
||
---
|
||
|
||
## Key Files
|
||
|
||
| File | Action |
|
||
|------|--------|
|
||
| `database/schema.ts` | Add `matchStatusEnum`, `season_matches`, `match_sub_games` tables, `externalSeasonId` on sportsSeasons |
|
||
| `app/models/season-match.ts` | New — all DB queries for new tables |
|
||
| `app/services/match-sync/types.ts` | New — generic adapter interface |
|
||
| `app/services/match-sync/pandascore.ts` | New — PandaScore CS2 implementation |
|
||
| `app/services/match-sync/espn-schedule.ts` | New — ESPN schedule implementation (MLB etc.) |
|
||
| `app/services/match-sync/index.ts` | New — orchestrator (mirrors standings-sync/index.ts) |
|
||
| `app/routes/admin/jobs.sync-matches.ts` | New — cron job (mirrors jobs.sync-and-simulate.ts) |
|
||
| `app/routes/admin.sports-seasons.$id.events.$eventId.cs2-setup.tsx` | Extend loader + Swiss rounds display section |
|
||
| `app/routes/admin.sports-seasons.$id.events.$eventId.swiss-matches.tsx` | New — manual match entry admin route |
|
||
| `app/components/scoring/Cs2TournamentBracket.tsx` | New — CS2 tournament display |
|
||
| `app/components/scoring/MatchSchedule.tsx` | New — generic match schedule display |
|
||
| `app/routes/sports-seasons.$sportsSeasonId.tournament.tsx` | New — public tournament/schedule route |
|
||
| `app/routes.ts` | Register 3 new routes |
|
||
|
||
## Verification
|
||
1. `npm run db:generate` produces migration for 2 new tables + column
|
||
2. `npm run db:migrate` applies cleanly
|
||
3. `npm run typecheck` passes
|
||
4. `npm run test:run` passes including new model + adapter tests
|
||
5. Manual: set `externalSeasonId` on a CS2 sports season, call `/admin/jobs/sync-matches`, verify `season_matches` rows created with stage/round populated
|
||
6. Manual: navigate to `/sports-seasons/:id/tournament`, confirm CS2 Swiss stage display renders with rounds and map scores
|