Constraints and Data Integrity — Schema v2

This document groups the integrity rules, discarded artifacts, and season-related constraints extracted from the original schema v2 design document.

Discarded Tables (dead code verified)

Legacy table Design name File Reason
match_statistics (discarded) src/db/schema/match_statistics.ts Zero reads/writes in app code. Irrelevant for W/D/L scoring.
point_adjustments (discarded) src/db/schema/point_adjustments.ts Zero reads/writes in app code.
team_transfer_availability (discarded) src/db/schema/team_transfer_availability.ts Derivable from existing data.

Precondition: A fresh grep against the entire repo confirmed 0 reads and 0 writes per table before exclusion.

Discarded Columns (dead code verified)

Table Column Reason
fantasy_league_participants currentPosition Duplicated; lives in fantasy_rankings
fantasy_league_participants totalPoints Duplicated; lives in fantasy_teams.totalPoints
fantasy_roster_entries (was fantasy_team_players) weeklyPoints Grep confirms nothing writes it
fantasy_roster_entries (was fantasy_team_players) totalPoints Grep confirms nothing writes it
fantasy_roster_entries (was fantasy_team_players) points Grep confirms nothing writes it
fantasy_roster_entries (was fantasy_team_players) isActive Moved to window_lineups.is_active
fantasy_leagues maxTeamsPerParticipant Deceptive — see §4 evaluation below

Dormant Constraints Activated

These constraints are already defined and exported in the current schema but never applied to the database. They encode integrity invariants the team identified — they are activated as-is in schema v2:

Source File Constraint Purpose
active_rosters_history.ts sequential_versions UNIQUE (roster_id, version)
active_rosters_history.ts games_played_limit Subquery check
active_rosters_history.ts valid_status_transitions ACTIVE→DEACTIVATED→TRANSFERRED
active_rosters_history.ts valid_transfer_refs TRANSFERRED requires transfer_history_id
active_rosters_history.ts version_sequence Sequential version numbering
point_calculation_events.ts match_id_required_for_match_events —
point_calculation_events.ts valid_point_category BASE/BONUS/PENALTY consistency
point_calculation_events.ts valid_status_transitions PENDING→CONFIRMED→DISPUTED→ADJUSTED
fantasy_team_window_points.ts unique_team_window_points UNIQUE (fantasy_team_id, window_id)
fantasy_team_window_points.ts points_calculation_check total = base + bonus - penalty
fantasy_team_window_points.ts base_points_match_events Subquery check
fantasy_team_window_points.ts bonus_points_match_events Subquery check
fantasy_team_window_points.ts penalty_points_match_events Subquery check
fantasy_team_window_points.ts valid_status_transitions PROVISIONAL/FINAL
fantasy_team_window_points.ts unique_rank_per_window EXCLUDE on btree
transfer_requests.ts valid_priority_range Priority 1-3
transfer_requests.ts valid_user_order_range User order 1-3
transfer_requests.ts unique_priority_per_window UNIQUE per team/window
transfer_requests.ts unique_user_order_per_window UNIQUE per team/window
transfer_requests.ts requests_per_window_limit ≤3 per team per window

Note: The active_rosters_history constraints reference a table that is being replaced by window_lineups. These constraints should be re-evaluated and ported to window_lineups during the migration.

§7 — Season Constraint

Applied. Fantasy leagues are now explicitly tied to a season via fantasy_leagues.season.

Problem: The current code uses getCurrentSeasonYear() — a global function that returns the current year's season based on the calendar month. This means:

Solution: fantasy_leagues.season is the single source of truth. The constraint chain:

fantasy_leagues.season = 2025
  ├── fantasy_league_game_limits → soccer_leagues WHERE season = 2025
  ├── draft_settings reads season from fantasy_leagues (removes active_season)
  └── point_calculation filters matches WHERE soccer_leagues.season = fantasy_leagues.season

Code changes required:

File Current Proposed
point-calculation.ts:49 getCurrentSeasonYear() Read from fantasy_leagues.season
point-calculation.ts:230 getCurrentSeasonYear() Read from fantasy_leagues.season
point-calculation.ts:536 getCurrentSeasonYear() Read from fantasy_leagues.season
draft/[leagueId]/pick.ts:164 currentSettings.activeSeason fantasy_leagues.season
draft/[leagueId]/auto-pick.ts:217 currentSettings.activeSeason fantasy_leagues.season
publicLeagueService.ts:265 activeSeason: currentSeason season: getCurrentSeasonYear()

Constraints added: