Tables — Schema v2

Tables

1. users — Preserved

Column Type Changes
id uuid PK —
email varchar —
... (all existing columns) —

Role: Authentication and identity. Each user can own fantasy teams and participate in fantasy leagues.

Derives from: src/db/schema/users.ts — preserved as-is. Justification: No audit finding against this table. Auth integration (Supabase) is a preserved primitive per the audit (Scenario B).

2. fantasy_leagues — Preserved + modified

Derives from: src/db/schema/fantasy_leagues.ts Justification: Core domain entity. Structure, status, and ownership preserved.

Role: A fantasy league is the competition container. Users join as participants, each fields a fantasy team, and compete across matchweek windows. The league defines scoring rules, team limits, and season boundaries.

Column Type Change
id uuid PK —
name varchar(255) —
description text —
ownerId uuid FK → users.id —
season integer NOT NULL NEW — start year of the season (e.g., 2025 for 2025-2026)
scoringRules jsonb —
maxTeams integer —
isPrivate boolean —
inviteCode varchar(255) —
status varchar(20) —
format varchar(20) NEW — values: BLITZ, MID, FULL
createdAt timestamp —
updatedAt timestamp —

Season convention:

European football leagues span two calendar years (e.g., August 2025 to May 2026). The season column stores the start year as an integer, following the API-Football convention:

season value Meaning Example date range
2025 2025-2026 season Aug 2025 – May 2026
2024 2024-2025 season Aug 2024 – May 2025
2026 2026-2027 season Aug 2026 – May 2027

This matches the API-Football v3 convention where GET /fixtures?league=39&season=2025 returns matches for the 2025-2026 season. The exact start/end dates are stored on soccer_leagues.season_start and soccer_leagues.season_end.

Constraint chain — how leagues tie to a season:

fantasy_leagues.season = 2025
  │
  ├──▶ fantasy_league_game_limits
  │      Links to soccer_leagues WHERE season = 2025
  │      Constraint: soccer league season must match fantasy league season
  │
  ├──▶ draft_settings.active_season = 2025
  │      Validated against fantasy_leagues.season
  │
  └──▶ point_calculation
         Filters matches WHERE soccer_leagues.season = fantasy_leagues.season
         (replaces global getCurrentSeasonYear() call)

Columns removed:

New CHECK constraints:

-- Team count constraint
CHECK (
  (is_private = false AND max_teams = 6) OR
  (is_private = true AND max_teams BETWEEN 4 AND 10)
)

-- Season sanity check
CHECK (season >= 2020 AND season <= 2100)

3. fantasy_league_participants — Modified

Derives from: src/db/schema/fantasy_league_participants.ts Justification: Standard M:N relation between users and fantasy leagues.

Role: Links a user to a fantasy league as a participant. Each participant fields exactly one fantasy team within the league (enforced by the unique index on fantasy_teams(user_id, league_id)). This table is the "seat" at the table — it also determines draft order via draft_order.

Column Type Change
id uuid PK —
leagueId uuid FK → fantasy_leagues.id —
userId uuid FK → users.id —
fantasyTeamId uuid FK → fantasy_teams.id —
joinedAt timestamp —
createdAt timestamp —
updatedAt timestamp —

Columns removed:

Relationships:

4. fantasy_teams — Preserved

Derives from: src/db/schema/fantasy_teams.ts Justification: Core domain entity. Ownership and league assignment preserved.

Role: A fantasy team is the user's construct within a fantasy league. It has a budget, roster of real clubs (fantasy_roster_entries), points, and ranking. One team per user per league (enforced by unique index on (user_id, league_id)).

[Diagram]

Key columns:

5. soccer_teams — Renamed (was teams)

Derives from: src/db/schema/teams.ts Justification: Real-world clubs. Preserved as-is, only renamed for clarity.

Role: Real-world football clubs (e.g., River Plate, Boca Juniors, Manchester City). These are the assets that fantasy teams draft, roster, and activate. Soccer teams belong to real-world competitions (soccer_leagues) and play matches against each other.

Column Type Change
id uuid PK —
apiId varchar(255) —
name varchar(255) —
tla varchar(3) —
shortName varchar(255) —
logoUrl varchar(255) —
leagueId uuid FK → soccer_leagues.id —
isActive boolean —

Relationships:

6. soccer_leagues — Renamed (was leagues)

Derives from: src/db/schema/leagues.ts Justification: Real-world competitions (4 leagues). Preserved as-is, only renamed for clarity.

Role: Real-world leagues/competitions (e.g., Premier League, La Liga, Primera División). These are the source of truth for match schedules, matchdays, and real team standings. Fantasy leagues reference these indirectly through fantasy_league_game_limits.

Column Type Change
id uuid PK —
apiId varchar(255) —
name varchar(255) —
code varchar(10) —
season integer Start year of the season (e.g., 2025 for 2025-2026)
seasonStart timestamp Exact start date (e.g., 2025-08-15)
seasonEnd timestamp Exact end date (e.g., 2026-05-24)
currentMatchweek integer —

Season convention:

The season column stores the start year as an integer, matching the API-Football v3 convention. This is the same format used by fantasy_leagues.season — the two must align via the fantasy_league_game_limits constraint.

API endpoint Parameter Value Meaning
GET /leagues?id=39&season=2025 season 2025 Premier League 2025-2026
GET /fixtures?league=39&season=2025 season 2025 Matches for 2025-2026
GET /standings?league=39&season=2025 season 2025 Standings for 2025-2026

Unique constraint:

CONSTRAINT unique_league_season UNIQUE (api_id, season)

Relationships:

7. soccer_matches — Renamed (was matches)

Derives from: src/db/schema/matches.ts Justification: Core entity. Audit flagged missing FKs and varchar PK.

Role: Real-world matches between two soccer teams. Results feed into point calculations for fantasy teams that have those clubs active.

Column Type Change
id uuid PK NEW (was varchar PK)
apiId varchar NEW — holds the original football-data.org ID
competitionId uuid FK → soccer_leagues.id CHANGED from varchar to real FK
homeTeamId uuid FK → soccer_teams.id CHANGED from varchar to real FK
awayTeamId uuid FK → soccer_teams.id CHANGED from varchar to real FK
utcDate timestamp —
status varchar —
matchday integer —
homeScore integer —
awayScore integer —
... (remaining columns preserved) —

8. point_calculation_events — Preserved

Derives from: src/db/schema/point_calculation_events.ts Justification: Core scoring table. Add matchId real FK.

Role: Records every point-awarding event for a fantasy team's club in a matchweek window. Each event has a type (MATCH_OUTCOME, CLEAN_SHEET, GOALS_SCORED, etc.), a category (BASE/BONUS/PENALTY), and a status lifecycle (PENDING → CONFIRMED → DISPUTED → ADJUSTED).

Constraints activated (dormant, already exported in current code):

9. fantasy_team_points — Renamed (was fantasy_team_window_points)

Derives from: src/db/schema/fantasy_team_window_points.ts Justification: Points per window per team. The "window" qualifier is redundant — all points are per-window by context.

Role: Aggregated points for a fantasy team in a single matchweek window. The total is computed as base + bonus - penalty and cross-checked against individual point_calculation_events. Also carries the per-window rank and rank change.

Constraints activated (dormant, already exported in current code):

10. transfer_requests — Preserved

Derives from: src/db/schema/transfer_requests.ts Justification: Core transfer system.

Role: A fantasy team's request to swap one real club for another in an upcoming transfer window. Each request has a priority (1–3) and references both the team to add and the team to remove.

Constraints activated (dormant, already exported in current code):

11. transfer_windows — Preserved

Derives from: src/db/schema/transfer_windows.ts Justification: Window lifecycle.

Role: A time-bounded period during which fantasy teams can submit and process transfer requests. Linked 1:1 to a fantasy_matchweek_window.

12. transfer_history — Preserved

Derives from: src/db/schema/transfer_history.ts Justification: Audit trail.

Role: Immutable log of every completed transfer. Records which club was removed, which was added, who authorized it, and the fantasy team's rank/points at the time.

13. draft_settings — Modified

Derives from: src/db/schema/draft.ts Justification: Draft lifecycle.

Role: Configuration for a fantasy league's initial draft. Controls timing, pick order, auto-pick behavior, and current round/pick tracking. One draft_settings per fantasy league.

Season handling:

The current draft_settings has an independent active_season column (default 2025). In v2, this is replaced by referencing fantasy_leagues.season — the season is a property of the league, not the draft.

Column Type Change
id uuid PK —
leagueId uuid FK → fantasy_leagues.id —
active_season removed Derived from fantasy_leagues.season
pickTimeLimit integer —
status varchar(20) —
currentRound integer —
currentPick integer —
... (other columns) —

Validation: When creating a draft, the active_season is read from fantasy_leagues.season. When drafting a team, the pick endpoint validates that the team's soccer league season matches the fantasy league's season (currently done via draft_settings.active_season, line 164 of src/pages/api/draft/[leagueId]/pick.ts).

14. draft_picks — Modified

Derives from: src/db/schema/draft.ts Justification: Add real FK to draft_settings so Drizzle Studio does not crash. Add fantasyTeamId to make ownership explicit.

Role: Records each pick made during a draft. A pick assigns a real club (soccer_team) to a fantasy team. The fantasyTeamId column makes the owner explicit (previously inferred via (user_id, league_id) → fantasy_teams).

Column Type Change
id uuid PK —
draftId uuid FK → draft_settings.id NEW
fantasyTeamId uuid FK → fantasy_teams.id NEW — makes ownership explicit
leagueId uuid FK → fantasy_leagues.id —
userId uuid FK → users.id —
teamId uuid FK → soccer_teams.id —
round integer —
pickNumber integer —
isAutoPick boolean —
pickedAt timestamp —

15. draft_order — Preserved

Derives from: src/db/schema/draft.ts Justification: Draft order tracking.

Role: Defines the pick order for participants in a draft. Each participant gets a pick_order value, and the draft snakes through the order.

16. fantasy_matchweek_windows — Preserved

Derives from: src/db/schema/fantasy_matchweeks.ts Justification: Window lifecycle (UPCOMING → ACTIVE → LOCKED → PROCESSING → COMPLETED).

Role: A time-bounded window corresponding to a fantasy matchweek. Windows group real-world matches that count toward a single scoring period. Each window has a lock time (when lineups freeze) and an unlock time (when points are finalized).

17. fantasy_roster_games — Renamed (was fantasy_team_games_played)

Derives from: src/db/schema/fantasy_team_games_played.ts Justification: Games-played tracking for Match Cap enforcement. Renamed to match the roster_entries / roster_games naming convention.

Role: Tracks each individual match a fantasy team's owned club has played within a window. Used to enforce the games-per-window limit (Match Cap). Each row corresponds to one roster entry (fantasy_roster_entries) playing in one real match.

Relationship diagram:

fantasy_roster_entries (ownership)
    │
    ├──▶ window_lineups (active/inactive per window)
    │       │
    │       └──▶ fantasy_roster_games (which matches the club played)
    │
    └──▶ point_calculation_events (points earned from those matches)

18. fantasy_rankings — Renamed (was fantasy_team_rankings)

Derives from: src/db/schema/fantasy_team_rankings.ts Justification: Rankings/standings. The "team" qualifier is redundant — only fantasy teams are ranked.

Role: Per-window snapshot of each fantasy team's rank within their league. Stores both overall rank and window-specific rank, plus rank change.

19. fantasy_league_game_limits — Renamed (was league_game_limits)

Derives from: src/db/schema/league_game_limits.ts Justification: Game limits per league. Renamed to clarify it's a fantasy-league concept.

Role: Defines how many games from each real-world competition (soccer_league) count toward a fantasy league's scoring windows. Links fantasy_leagues to soccer_leagues with a max games per team threshold.

Critical constraint: The linked soccer_league must have the same season as the fantasy_league. This prevents linking a 2025 fantasy league to a 2024 soccer league.

FK References Purpose
fantasy_league_id fantasy_leagues.id Which fantasy league this limit applies to
league_id soccer_leagues.id Which real-world competition is being limited

CHECK constraint (season alignment):

CONSTRAINT matching_season CHECK (
  league_id IN (
    SELECT sl.id FROM soccer_leagues sl
    WHERE sl.season = (
      SELECT fl.season FROM fantasy_leagues fl
      WHERE fl.id = fantasy_league_id
    )
  )
)

Composite unique constraint:

CONSTRAINT unique_fantasy_soccer_league UNIQUE (fantasy_league_id, league_id)

20. soccer_league_standings — Renamed (was league_standings)

Derives from: src/db/schema/league_standings.ts Justification: Standings snapshots from the real world. Renamed to clarify this is a real-world concept.

Role: Periodic snapshots of real-world league standings per matchweek. Used as input for point calculations and scouting.

21. notifications — Preserved

Derives from: src/db/schema/notifications.ts Justification: User notification system.

22. fantasy_league_invitations — Preserved

Derives from: src/db/schema/fantasy_league_invitations.ts Justification: League invite system.

23. system_logs — Preserved

Derives from: src/db/schema/system_logs.ts Justification: Audit logging.

24. email_verification_tokens — Preserved

Derives from: src/db/schema/email_verification_tokens.ts Justification: Auth support.

25. password_reset_tokens — Preserved

Derives from: src/db/schema/password_reset_tokens.ts Justification: Auth support.

26. window_lineups — Consolidated (NEW)

Derives from: active_rosters + active_rosters_history + fantasy_roster_entries.isActive

Justification: The audit found three tables managing the same concept (which clubs are active for a fantasy team in a given window). They collapse into one versioned snapshot table. The point-calculation.ts:117-122 snapshot-at-lock-time logic reads from this table — only the source changes, the algorithm stays identical.

Role: A per-window snapshot of which real clubs a fantasy team has activated for scoring. When a window locks, this snapshot freezes — clubs activated after the lock do not count. This replaces active_rosters (current active state), active_rosters_history (versioned history), and the isActive column on fantasy_roster_entries (redundant boolean flag).

CREATE TABLE window_lineups (
  id              uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  fantasy_team_id uuid NOT NULL REFERENCES fantasy_teams(id),
  window_id       uuid NOT NULL REFERENCES fantasy_matchweek_windows(id),
  team_id         uuid NOT NULL REFERENCES soccer_teams(id),
  position        integer NOT NULL DEFAULT 0,       -- slot 1-4
  is_active       boolean NOT NULL DEFAULT true,
  version         integer NOT NULL DEFAULT 1,
  locked_at       timestamp,                         -- when window locked, this snapshot freezes
  activated_at    timestamp NOT NULL DEFAULT now(),
  deactivated_at  timestamp,
  created_at      timestamp NOT NULL DEFAULT now(),
  updated_at      timestamp NOT NULL DEFAULT now(),

  CONSTRAINT unique_team_per_window UNIQUE (fantasy_team_id, window_id, team_id)
);

Lifecycle:

Window UPCOMING → user can freely modify lineups
Window LOCKS   → `locked_at` is set, lineup freezes
Window PROCESSING → points calculated against frozen lineup
Window COMPLETED → final state, lineup is historical

27. fantasy_roster_entries — Renamed (was fantasy_team_players)

Derives from: src/db/schema/fantasy_team_players.ts

Justification: Still tracks which real clubs a fantasy team owns (roster). Renamed from fantasy_team_players to fantasy_roster_entries because the table stores real clubs, not "players" — the old name misled every new developer. The weeklyPoints, totalPoints, points columns are removed (grep-confirmed dead). The isActive column is removed — active status now lives in window_lineups.

Role: The roster table — defines which real clubs a fantasy team has acquired (via draft or transfer). This is the "collection" or "portfolio" of owned clubs. It does NOT track which clubs are currently active — that is window_lineups's job. It does NOT track which games were played — that is fantasy_roster_games's job.

Columns removed: isActive, weeklyPoints, totalPoints, points

Relationship summary:

fantasy_roster_entries (ownership — what clubs do I own?)
    │ owned clubs are available for activation
    ▼
window_lineups (which owned clubs are active THIS window)
    │
    ▼
fantasy_roster_games (which matches did each active club play?)

28. public_league_pool — NEW

Derives from: N/A (new table)

Justification: Spec requires public league join with overflow queue. When no public league is available, the user enters the pool. A periodic task creates leagues from pooled users. No merge logic — the join endpoint already prioritizes existing leagues with space.

CREATE TABLE public_league_pool (
  id          uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id     uuid NOT NULL REFERENCES users(id),
  format      varchar(20) NOT NULL CHECK (format IN ('BLITZ', 'MID', 'FULL')),
  status      varchar(20) NOT NULL DEFAULT 'WAITING'
              CHECK (status IN ('WAITING', 'ASSIGNED', 'EXPIRED')),
  created_at  timestamp NOT NULL DEFAULT now(),

  CONSTRAINT one_pool_entry_per_user UNIQUE (user_id, format, status)
);