// Water Ball – Detailed Data Model capturing all fields the user requested
// Tech target: Prisma (PostgreSQL). TypeScript types included for clarity.
// -----------------------------
// ENUMS
// -----------------------------
enum Position { WING CENTER }
enum SlotKind {
START_W1
START_C
START_W2
BENCH_W1
BENCH_C
BENCH_W2
BENCH_FLEX1 // W or C
BENCH_FLEX2 // W or C
BENCH_FLEX3 // W or C
}
// -----------------------------
// CORE ENTITIES
// -----------------------------
model Coach {
id String @id @default(cuid())
name String
age Int
ratingPoints Int @default(0) // "Coaches Rating Points"
// Titles that follow the coach regardless of team
leagueTitles Int @default(0)
conferenceTitles Int @default(0)
divisionTitles Int @default(0)
// All-time coach record that follows the coach
allTimeWins Int @default(0)
allTimeLosses Int @default(0)
// Current employment
teamId String?
team Team? @relation(fields: [teamId], references: [id])
// History
histories CoachHistory[]
}
model CoachHistory {
id String @id @default(cuid())
coachId String
coach Coach @relation(fields: [coachId], references: [id])
teamId String?
team Team? @relation(fields: [teamId], references: [id])
seasonId String?
season Season? @relation(fields: [seasonId], references: [id])
note String?
createdAt DateTime @default(now())
}
model Player {
id String @id @default(cuid())
name String
number Int?
position Position
cost Int @default(0) // "Player Cost" in VD or salary units
age Int
ratingPoints Int @default(0) // "Player Rating Points" (ORP or PRP)
// Career stats
careerGoals Int @default(0)
careerPlayoffGoals Int @default(0)
// Current season
currentSeasonGoals Int @default(0)
// Team association (nullable for free agents/retired)
teamId String?
team Team? @relation(fields: [teamId], references: [id])
// Retirement flag
retired Boolean @default(false)
retirements Retirement[]
// Season histories
seasons PlayerSeason[]
}
model Retirement {
id String @id @default(cuid())
playerId String
player Player @relation(fields: [playerId], references: [id])
retiredAt DateTime @default(now())
// snapshot indicators at retirement
lastRatingPoints Int
totalGoals Int
teamsPlayedFor String // comma-separated list or migrate to join table if you want details
}
model Team {
id String @id @default(cuid())
name String @unique
city String?
mascot String?
// Ownership & identity
ownerId String? @unique
owner User? @relation(fields: [ownerId], references: [id])
logoUrl String? // Team Logo Slot (thumbnail)
// Hierarchy
leagueId String
league League @relation(fields: [leagueId], references: [id])
conferenceId String
conference Conference @relation(fields: [conferenceId], references: [id])
divisionId String
division Division @relation(fields: [divisionId], references: [id])
// Records
allTimeWins Int @default(0)
allTimeLosses Int @default(0)
allTimeLeagueTitles Int @default(0)
allTimeConferenceTitles Int @default(0)
allTimeDivisionTitles Int @default(0)
// Current season linkage for convenience (also tracked per Season)
currentSeasonId String?
currentSeason Season? @relation(fields: [currentSeasonId], references: [id])
// Coach
coachId String?
coach Coach? @relation(fields: [coachId], references: [id])
// Relations
players Player[]
lineups Lineup[]
seasons TeamSeason[] // each regular season record
gamesHome Game[] @relation("HomeTeam")
gamesAway Game[] @relation("AwayTeam")
retiredPlayers Retirement[]
}
model TeamSeason {
id String @id @default(cuid())
teamId String
team Team @relation(fields: [teamId], references: [id])
seasonId String
season Season @relation(fields: [seasonId], references: [id])
wins Int @default(0)
losses Int @default(0)
}
model Season {
id String @id @default(cuid())
year Int
leagueId String
league League @relation(fields: [leagueId], references: [id])
status SeasonStatus @default(DRAFT)
weeks Int @default(18)
teams TeamSeason[]
games Game[]
// schedule is implied by Game entries
}
enum SeasonStatus { DRAFT REGULAR PLAYOFFS COMPLETE }
model Game {
id String @id @default(cuid())
seasonId String
season Season @relation(fields: [seasonId], references: [id])
week Int
homeTeamId String
homeTeam Team @relation("HomeTeam", fields: [homeTeamId], references: [id])
awayTeamId String
awayTeam Team @relation("AwayTeam", fields: [awayTeamId], references: [id])
homeScore Int?
awayScore Int?
boxJson Json?
}
// Per-season snapshot for a player
model PlayerSeason {
id String @id @default(cuid())
playerId String
player Player @relation(fields: [playerId], references: [id])
seasonId String
season Season @relation(fields: [seasonId], references: [id])
teamId String?
team Team? @relation(fields: [teamId], references: [id])
goals Int @default(0) // regular season goals for that season
}
// Lineup with 9 player slots and strict constraints
model Lineup {
id String @id @default(cuid())
teamId String
team Team @relation(fields: [teamId], references: [id])
seasonId String
season Season @relation(fields: [seasonId], references: [id])
week Int
slots LineupSlot[]
@@unique([teamId, seasonId, week])
}
model LineupSlot {
id String @id @default(cuid())
lineupId String
lineup Lineup @relation(fields: [lineupId], references: [id])
slotKind SlotKind
playerId String
player Player @relation(fields: [playerId], references: [id])
}
// -----------------------------
// VALIDATION & BUSINESS RULES (pseudocode)
// -----------------------------
/*
function validateLineup(lineup: Lineup, players: Record<string, Player>): void {
// There must be exactly 9 slots with unique players
assert(lineup.slots.length === 9, 'Lineup must have 9 players');
const ids = new Set(lineup.slots.map(s => s.playerId));
assert(ids.size === 9, 'Duplicate player in lineup');
const byKind = (k: SlotKind) => lineup.slots.find(s => s.slotKind === k);
const pos = (k: SlotKind) => players[byKind(k)!.playerId].position;
// Starter constraints: W, C, W in order
assert(pos('START_W1' as any) === 'WING');
assert(pos('START_C' as any) === 'CENTER');
assert(pos('START_W2' as any) === 'WING');
// First three bench: W, C, W
assert(pos('BENCH_W1' as any) === 'WING');
assert(pos('BENCH_C' as any) === 'CENTER');
assert(pos('BENCH_W2' as any) === 'WING');
// Final three bench: FLEX and must be W or C (model only has those two anyway)
}
*/
// -----------------------------
// FILE UPLOAD (team logo)
// -----------------------------
// Use signed uploads to S3/Supabase Storage. Store the returned URL in Team.logoUrl.
// Accept PNG/JPG/WebP, max 1MB, recommended size 256x256 for thumbnail.
// -----------------------------
// OWNER / USER RELATION & DISPLAY
// -----------------------------
model User {
id String @id @default(cuid())
email String @unique
name String?
role Role @default(USER)
teams Team[]
}
enum Role { USER ADMIN }
// On the Team page, surface: owner name, logo, records, coach info, lineup, schedule, retired players, seasons list.
// -----------------------------
// EXAMPLE TEAM JSON SHAPE
// -----------------------------
/*
{
"team": {
"name": "Denver Stallions",
"logoUrl": "/logos/den-stallions.png",
"ownerName": "Jane Doe",
"records": {
"allTime": { "wins": 29, "losses": 38 },
"currentSeason": { "wins": 16, "losses": 14 }
},
"titles": { "league": 0, "conference": 0, "division": 0 },
"coach": {
"name": "Derrick Rogers",
"age": 47,
"ratingPoints": 12,
"allTimeRecord": { "wins": 210, "losses": 180 },
"leagueTitles": 1,
"conferenceTitles": 2,
"divisionTitles": 4
},
"lineup": {
"week": 3,
"slots": [
{ "slotKind": "START_W1", "player": { "name": "Christopher Redd", "position": "WING", "number": 5 } },
{ "slotKind": "START_C", "player": { "name": "Michael Mann", "position": "CENTER", "number": 8 } },
{ "slotKind": "START_W2", "player": { "name": "Ticky Smith", "position": "WING", "number": 6 } },
{ "slotKind": "BENCH_W1", "player": { "name": "Rich Barber", "position": "WING" } },
{ "slotKind": "BENCH_C", "player": { "name": "Richy McBeth", "position": "CENTER" } },
{ "slotKind": "BENCH_W2", "player": { "name": "Mc Jagger", "position": "WING" } },
{ "slotKind": "BENCH_FLEX1", "player": { "name": "Travis Loveless", "position": "WING" } },
{ "slotKind": "BENCH_FLEX2", "player": { "name": "Richard McMahon", "position": "WING" } },
{ "slotKind": "BENCH_FLEX3", "player": { "name": "Lendon Trees", "position": "WING" } }
]
},
"retiredPlayers": [
{
"name": "Victor Hale",
"totalGoals": 410,
"lastRatingPoints": 8,
"teamsPlayedFor": "Denver Stallions, Seattle Stars"
}
],
"seasons": [
{ "year": 1, "wins": 14, "losses": 6 },
{ "year": 2, "wins": 16, "losses": 4 }
],
"schedule": [
{ "week": 1, "opponent": "Seattle Stars", "home": true },
{ "week": 2, "opponent": "Los Angeles Sting", "home": false }
]
}
}
*/
