Schema v2 — Overview

This folder splits the original Schema v2 — Design Document into focused documents by review purpose.

Reading order

  1. 00-schema-v2-overview.md — entry point, metadata, global naming note, preservation policy, legend, namespace map, conventions.
  2. 01-naming-audit.md — table/file renames and rationale.
  3. 02-domain-model.md — conceptual model and domain diagrams.
  4. 03-schema-tables.md — table-by-table schema reference.
  5. 04-constraints-and-data-integrity.md — constraints, discarded artifacts, and integrity rules.
  6. 05-code-impact-analysis.md — files and code paths affected by the schema changes.
  7. 06-migration-plan.md — migration sequence and implementation decisions.
  8. 07-validation-and-examples.md — concrete validation data and post-migration examples.

Schema v2 — Design Document

Audit date: 2026-05-13 Author: Senior Status: Draft

Naming Note

Throughout this document, tables are referred to by their design-level name — the name they will have after migration. Current code files use legacy names; the migration plan applies the renames listed below.

Current code name Design name What it represents
teams soccer_teams Real-world football clubs (River Plate, Boca Juniors)
leagues soccer_leagues Real-world competitions (Primera División, Premier League)
matches soccer_matches Real-world match fixtures
league_standings soccer_league_standings Real-world league standings snapshots
league_game_limits fantasy_league_game_limits Game limits per fantasy league per soccer league
fantasy_team_players fantasy_roster_entries Roster entries — which real clubs a fantasy team owns
fantasy_team_games_played fantasy_roster_games Per-roster-entry game tracking for Match Cap
fantasy_team_window_points fantasy_team_points Scored points per fantasy team per window
fantasy_team_rankings fantasy_rankings Per-window ranking of fantasy teams
fantasy_teams (unchanged) A user's fantasy construct within a fantasy league
active_rosters / active_rosters_history window_lineups Per-window active lineup (consolidated)

Preservation Policy

Per §4.1 of the Refactor Plan: every table in schema v2 is justified by a mapping back to the current schema and a citation to either the spec or the audit. Nothing is added, removed, or renamed without a recorded reason.

Legend

Category Meaning
Preserved Table kept as-is; only missing FKs / type mismatches adjusted
Consolidated Multiple legacy tables folded into one
Modified Table kept but columns removed or added
Discarded Table removed (grep-confirmed dead)
New Table created for spec-required functionality

Namespace Map

The v2 schema organizes tables into clear namespaces that signal their domain:

users/                     Identity & auth
  users
  email_verification_tokens
  password_reset_tokens

fantasy_/                  Fantasy constructs
  fantasy_leagues
  fantasy_league_participants
  fantasy_league_invitations
  fantasy_league_game_limits
  fantasy_teams
  fantasy_team_points
  fantasy_rankings
  fantasy_matchweek_windows
  fantasy_roster_entries    ← roster sub-namespace
  fantasy_roster_games      ← roster sub-namespace

window_/                   Per-window state
  window_lineups

soccer_/                   Real-world football data
  soccer_teams
  soccer_leagues
  soccer_matches
  soccer_league_standings

draft_/                    Draft system
  draft_settings
  draft_order
  draft_picks

transfer_/                 Transfer system
  transfer_windows
  transfer_requests
  transfer_history

scoring/                   Points calculation
  point_calculation_events

utility/                   Supporting tables
  notifications
  system_logs
  public_league_pool

Naming Conventions