Skip to main content

Database Schema

CheerKeeper uses PostgreSQL with Prisma ORM. This document covers the key data models.

Entity Relationship Overview​

Company (tenant)
├── User
│   └── Account (OAuth)
├── Event
│   ├── EventDate
│   ├── EventHall
│   ├── Session
│   ├── Division
│   ├── Level
│   └── EventUnit (Performance)
├── Organization
│   └── Team
└── PdfUpload
    └── ExtractionJob

Core Models​

Company​

Multi-tenant root entity. All data belongs to a company.

model Company {
  id        String   @id @default(cuid())
  name      String
  slug      String   @unique
  plan      Plan     @default(STARTER)
  createdAt DateTime @default(now())

  users         User[]
  events        Event[]
  organizations Organization[]
}

User​

User accounts with role-based permissions.

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  name      String?
  password  String?  // Hashed, null for OAuth-only
  role      Role     @default(MEMBER)
  companyId String

  company   Company   @relation(...)
  accounts  Account[] // OAuth connections
}

enum Role {
  OWNER
  ADMIN
  EDITOR
  MEMBER
}

Event​

Competition, tournament, or meet.

model Event {
  id              String    @id @default(cuid())
  name            String
  competitionType String
  startDate       DateTime
  endDate         DateTime?
  status          EventStatus @default(DRAFT)
  scheduleVersion Int       @default(0)
  companyId       String

  // Location
  venue       String?
  address     String?
  city        String?
  state       String?
  zip         String?

  company     Company      @relation(...)
  dates       EventDate[]
  halls       EventHall[]
  sessions    Session[]
  eventUnits  EventUnit[]  // Performances
}

enum EventStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}

Session​

Time blocks within an event.

model Session {
  id            String   @id @default(cuid())
  eventId       String
  sessionNumber Int
  date          DateTime
  hall          String?
  label         String?

  event      Event       @relation(...)
  eventUnits EventUnit[]

  @@unique([eventId, sessionNumber])
}

EventUnit (Performance)​

Individual schedule entries.

model EventUnit {
  id              String    @id @default(cuid())
  eventId         String
  sessionId       String?

  // Team info
  teamName        String
  organization    String?
  division        String?
  level           String?

  // Timing
  performanceTime DateTime
  checkInTime     DateTime?
  warmUpTime      DateTime?
  duration        Int?      // Minutes

  // Location
  hall            String?
  room            String?

  // Status
  isBreak         Boolean   @default(false)
  isScratch       Boolean   @default(false)
  sortOrder       Int       @default(0)

  event   Event    @relation(...)
  session Session? @relation(onDelete: SetNull)
}

Organization & Team​

Programs/gyms and their teams.

model Organization {
  id        String   @id @default(cuid())
  name      String
  companyId String

  company Company @relation(...)
  teams   Team[]
}

model Team {
  id             String @id @default(cuid())
  name           String
  organizationId String

  organization Organization @relation(...)
}

Supporting Models​

EventDate​

Multi-day event support.

model EventDate {
  id      String   @id @default(cuid())
  eventId String
  date    DateTime
  label   String?

  event Event @relation(...)
}

EventHall​

Venues/rooms within an event.

model EventHall {
  id      String @id @default(cuid())
  eventId String
  name    String

  event Event @relation(...)
}

Account (OAuth)​

OAuth provider connections (NextAuth).

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String?
  access_token      String?
  expires_at        Int?

  user User @relation(...)

  @@unique([provider, providerAccountId])
}

Key Constraints​

Unique Constraints​

  • Company.slug - URL-friendly identifier
  • User.email - One account per email
  • Session(eventId, sessionNumber) - No duplicate session numbers
  • Account(provider, providerAccountId) - One OAuth link per provider account

Foreign Key Behaviors​

RelationOn Delete
EventUnit.sessionIdSetNull
EventUnit.eventIdCascade
Session.eventIdCascade
Team.organizationIdCascade

Indexes​

Performance-critical indexes:

// Fast event lookups
@@index([companyId, status])
@@index([startDate])

// Fast performance queries
@@index([eventId, sessionId])
@@index([eventId, performanceTime])
@@index([teamName])

// Fast session queries
@@index([eventId, sessionNumber])

Version Control​

Schedule Versioning​

The scheduleVersion field on Event enables optimistic locking:

  1. Client reads schedule with version N
  2. Client sends changes with version N
  3. Server checks version matches
  4. If match: apply changes, increment to N+1
  5. If mismatch: return 409 Conflict

Migrations​

Running Migrations​

# Development (auto-apply)
npx prisma db push

# Production (migration files)
npx prisma migrate deploy

Creating Migrations​

npx prisma migrate dev --name description_of_change