Database Management

Database Management Skill

Database Architecture

PostgreSQL 16 with Prisma 7 (using @prisma/adapter-pg driver adapter with raw pg.Pool).

• 12 models, 10 enums, 13 migrations
• All IDs: native PostgreSQL UUID (@db.Uuid)
• All tables: snake_case via @@map()
• All columns: snake_case via @map()

Complete Table Reference

Entity Relationship Diagram

erDiagram
    users ||--o{ refresh_tokens : has
    users ||--o{ otp_codes : has
    users }o--|| roles : belongs_to
    teams }o--|| stadiums : home_stadium
    teams ||--o{ team_players : roster
    players ||--o{ team_players : roster
    seasons ||--o{ matches : contains
    seasons ||--o{ season_teams : registrations
    seasons ||--o{ regulations : config
    seasons ||--o{ standings : table
    teams ||--o{ season_teams : registered
    teams ||--o{ standings : ranked
    matches ||--o{ match_events : events
    matches }o--|| teams : home_team
    matches }o--|| teams : away_team
    matches }o--|| stadiums : venue
    match_events }o--|| players : player
    match_events }o--|| players : related_player
    match_events }o--|| teams : team

Schema Development Workflow

1. Modify Schema

Edit apps/api/prisma/schema.prisma

2. Create Migration

cd apps/api
pnpm dlx prisma migrate dev --name descriptive_name

3. Migration Naming Convention

YYYYMMDDHHMMSS_descriptive_name
# Examples:
20260128113243_init_registration
20260129180318_add_auth_models
20260201000000_add_indexes

4. Generate Client

pnpm dlx prisma generate

NOTE: postinstall script auto-runs prisma generate after pnpm install.

Current Migrations (13)

Database Seeding

Master Seeder (prisma/seed.ts)

Chains all seed scripts in order:

cd apps/api && pnpm run db:seed

Seed Scripts (7)

Common Database Operations

# Reset database (drop all, re-migrate, re-seed)
pnpm dlx prisma migrate reset

# Open Prisma Studio GUI
pnpm dlx prisma studio

# Generate client after schema change
pnpm dlx prisma generate

# Check migration status
pnpm dlx prisma migrate status

# Apply migrations in production
pnpm dlx prisma migrate deploy

# Format schema file
pnpm dlx prisma format

Schema Patterns

Soft Delete (Roster)

model TeamPlayer {
  joinedAt DateTime @default(now()) @map("joined_at")
  leftAt   DateTime? @map("left_at")        // null = active, set = removed
}

Enum with Default

model Team {
  status TeamStatus @default(ACTIVE)
}

Many-to-Many via Join Table

model Team {
  roster TeamPlayer[]               // Relation name: "roster"
}
model Player {
  roster TeamPlayer[]               // Same relation name
}
model TeamPlayer {
  team   Team   @relation(fields: [teamId], references: [id])
  player Player @relation(fields: [playerId], references: [id])
}

CRITICAL: Prisma relation name is roster, not teamPlayers. Use include: { roster: true }.

Indexes

model Match {
  @@index([seasonId])
  @@index([homeTeamId])
  @@index([awayTeamId])
  @@index([status])
}

Query Optimization Tips

1. Select only needed fields: Use select instead of returning entire records
2. Pagination: Always use skip/take with count() for large tables
3. Avoid N+1: Use include for relations instead of separate queries
4. Parallel queries: Use Promise.all([findMany(), count()]) for paginated results
5. Indexes: Key fields (seasonId, teamId, status) are already indexed
6. Driver adapter: Prisma 7's @prisma/adapter-pg uses raw pg.Pool for better performance

Environment Configuration

DATABASE_URL="postgresql://vleague:password@localhost:5432/vleague_db"

Docker Database

# Start PostgreSQL only
docker compose -f infra/docker-compose.db.yml up -d

# Connection: postgresql://vleague:vleague@localhost:5432/vleague

Backup & Restore

# Backup
pg_dump -U vleague -h localhost -d vleague_db > backup.sql

# Restore
psql -U vleague -h localhost -d vleague_db < backup.sql

Troubleshooting

Converted and distributed by TomeVault — claim your Tome and manage your conversions.