بنقرة واحدة
database-management
Prisma schema, migrations, seeding, and database operations for SE104_VLEAGUE
التثبيت باستخدام Codex أو Claude انسخ هذا Prompt والصقه في Codex أو Claude أو مساعد آخر ليراجع صفحة Skill ويثبّتها لك.
القائمة
Prisma schema, migrations, seeding, and database operations for SE104_VLEAGUE
التثبيت باستخدام Codex أو Claude انسخ هذا Prompt والصقه في Codex أو Claude أو مساعد آخر ليراجع صفحة Skill ويثبّتها لك.
استنادا إلى تصنيف SOC المهني
Swagger/OpenAPI setup and documentation patterns for SE104_VLEAGUE
Complete guide for the SE104_VLEAGUE React frontend — pages, services, components, auth flow, i18n, and patterns
Complete guide for testing patterns, test structure, CI pipeline, and development workflow for SE104_VLEAGUE
Complete guide for the SE104_VLEAGUE NestJS backend — modules, endpoints, Prisma schema, guards, interceptors, and patterns
JWT auth, OAuth, OTP verification, session management, RBAC for SE104_VLEAGUE
All business rules, state machines, scoring, regulations, and domain constraints for SE104_VLEAGUE
| name | Database Management |
| description | Prisma schema, migrations, seeding, and database operations for SE104_VLEAGUE |
PostgreSQL 16 with Prisma 7 (using @prisma/adapter-pg driver adapter with raw pg.Pool).
@db.Uuid)snake_case via @@map()snake_case via @map()| Table | Purpose | Key Relations |
|---|---|---|
users | User accounts (auth, profile, role) | → Role, ← RefreshTokens, ← OtpCodes |
roles | Role definitions (RBAC) | ← Users |
refresh_tokens | JWT refresh tokens (hashed) | → User |
otp_codes | OTP codes (email verify, password reset) | → User |
teams | Football teams | → Stadium, ← TeamPlayers, ← SeasonTeams, ← Matches, ← Standings |
players | Football players | ← TeamPlayers, ← MatchEvents |
stadiums | Stadiums | ← Teams, ← Matches |
seasons | League seasons | ← Matches, ← SeasonTeams, ← Regulations, ← Standings |
team_players | Team-player roster (join table) | → Team, → Player |
season_teams | Season-team registration (join table) | → Season, → Team |
matches | Match records | → Season, → HomeTeam, → AwayTeam, → Stadium, ← MatchEvents |
match_events | Match events (goals, cards, subs) | → Match, → Player, → RelatedPlayer, → Team |
regulations | Season-scoped key-value configs | → Season |
standings | League standings (computed/cached) | → Season, → Team |
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
Edit apps/api/prisma/schema.prisma
cd apps/api
pnpm dlx prisma migrate dev --name descriptive_name
YYYYMMDDHHMMSS_descriptive_name
# Examples:
20260128113243_init_registration
20260129180318_add_auth_models
20260201000000_add_indexes
pnpm dlx prisma generate
NOTE:
postinstallscript auto-runsprisma generateafterpnpm install.
| Migration | Purpose |
|---|---|
init_registration | Teams, players, team_players tables |
init_matches | Matches, match_events tables |
add_auth_models | Users, refresh_tokens |
add_team_manager_referee_roles | Role enum expansion |
add_roles_table | Separate roles table |
add_otp_and_email_verification | OTP codes, email verification |
add_session_profile_oauth | Session tracking, OAuth fields, profile |
add_season_stadium_roster_models | Seasons, stadiums, roster |
add_indexes | Performance indexes |
add_regulations_season_teams_standings | Regulations, season_teams, standings |
convert_text_to_uuid | Migrate text IDs to native UUID |
add_role_id_to_users | Add roleId FK to users |
add_penalty_event_types | Add PENALTY, PENALTY_MISS to EventType enum |
prisma/seed.ts)Chains all seed scripts in order:
cd apps/api && pnpm run db:seed
| Script | Purpose |
|---|---|
seed.ts | Master orchestrator |
verify-demo-users.ts | 5 demo accounts (admin, manager, referee, supervisor, user) |
seed-teams.ts | 10 V-League teams with stadium mappings |
seed-players.ts | 100+ Vietnamese + foreign players, randomized positions/stats |
seed-stadiums.ts | 15 Vietnamese stadiums |
register-teams-to-season.ts | Auto-register teams to active season |
cleanup-stale-matches.ts | Remove orphaned match records |
# 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
model TeamPlayer {
joinedAt DateTime @default(now()) @map("joined_at")
leftAt DateTime? @map("left_at") // null = active, set = removed
}
model Team {
status TeamStatus @default(ACTIVE)
}
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, notteamPlayers. Useinclude: { roster: true }.
model Match {
@@index([seasonId])
@@index([homeTeamId])
@@index([awayTeamId])
@@index([status])
}
select instead of returning entire recordsskip/take with count() for large tablesinclude for relations instead of separate queriesPromise.all([findMany(), count()]) for paginated results@prisma/adapter-pg uses raw pg.Pool for better performanceDATABASE_URL="postgresql://vleague:password@localhost:5432/vleague_db"
# Start PostgreSQL only
docker compose -f infra/docker-compose.db.yml up -d
# Connection: postgresql://vleague:vleague@localhost:5432/vleague
# Backup
pg_dump -U vleague -h localhost -d vleague_db > backup.sql
# Restore
psql -U vleague -h localhost -d vleague_db < backup.sql
| Problem | Solution |
|---|---|
| "Prisma client not generated" | Run pnpm dlx prisma generate |
| Migration conflict | Run pnpm dlx prisma migrate reset (dev only) |
| Connection refused | Check PostgreSQL is running + DATABASE_URL is correct |
| Schema drift | Run pnpm dlx prisma migrate dev to reconcile |
| UUID type mismatch | Ensure all ID fields use @db.Uuid |
| Relation not found | Check Prisma relation names (e.g., roster not teamPlayers) |