Database Models (Prisma Schema)

The database uses PostgreSQL with Prisma ORM. Schema is in /prisma/schema.prisma.

Enums

User & Employment

enum UserRole {
  job_seeker      // Formal job seeker
  service_worker  // Informal service provider
  client          // Needs services
  hybrid          // Both worker and client
}

enum CompanySize {
  startup | small | medium | large | enterprise
}

enum JobType {
  full_time | part_time | contract | internship | remote
}

enum ExperienceLevel {
  entry | mid | senior | executive
}

enum ApplicationStatus {
  pending | reviewed | shortlisted | rejected | hired
}

enum SwipeAction {
  like | dislike | superlike
}

Services

enum BookingStatus {
  pending | accepted | declined | in_progress | completed | cancelled | disputed
}

enum YeboTier {
  bronze | silver | gold | platinum
}

Billing

enum SubscriptionTier {
  free | pro | business | enterprise
}

enum SubscriptionStatus {
  active | trialing | past_due | canceled | unpaid | paused
}

enum SubscriberType {
  user | employer
}

enum VoucherType {
  percentage | fixed_amount | free_trial | credits
}

enum BillingInterval {
  monthly | yearly
}

Messaging

enum MessageStatus {
  sent | delivered | read
}

enum MessageType {
  text | image | system
}

enum ConversationType {
  job_inquiry      // About a job application
  booking_inquiry  // About a service booking/quote
}

---

Core Models

User

model User {
  id                  String    @id @default(cuid())
  name                String
  email               String?
  password            String
  phone               String    @unique
  location            String
  bio                 String?
  profilePicture      String?
  profileVideo        String?
  personalityMatch    String[]  @default([])
  culturePreferences  String[]  @default([])
  isActive            Boolean   @default(true)
  lastLogin           DateTime?
  refreshToken        String?
  showInterviewScores Boolean   @default(true)  // AI score privacy
  role                UserRole  @default(job_seeker)
  latitude            Float?
  longitude           Float?
  createdAt           DateTime  @default(now())
  updatedAt           DateTime  @updatedAt

  // Relations
  education            Education[]
  experience           Experience[]
  swipeHistory         SwipeHistory[]
  savedJobs            SavedJob[]
  applications         Application[]
  serviceWorkerProfile ServiceWorkerProfile?
  credits              UserCredits?
  stripeCustomer       StripeCustomer?
  // ... more relations
}

Employer

model Employer {
  id             String      @id @default(cuid())
  companyName    String
  email          String      @unique
  password       String
  industry       String
  companySize    CompanySize
  description    String
  phone          String
  whatsappNumber String?
  logo           String?
  website        String?
  location       String
  isVerified     Boolean     @default(false)
  jobsPosted     Int         @default(0)
  activeJobs     Int         @default(0)
  joinedDate     DateTime    @default(now())
  refreshToken   String?
  createdAt      DateTime    @default(now())
  updatedAt      DateTime    @updatedAt

  jobs           Job[]
  applications   Application[]
  stripeCustomer StripeCustomer?
}

Job

model Job {
  id                 String          @id @default(cuid())
  title              String
  company            String
  location           String
  salary             Decimal         @db.Decimal(12, 2)
  currency           String          @default("USD")
  description        String
  requirements       String[]        @default([])
  benefits           String[]        @default([])
  jobType            JobType
  experienceLevel    ExperienceLevel
  applicationsCount  Int             @default(0)
  savesCount         Int             @default(0)
  sharesCount        Int             @default(0)
  videoContent       String?
  isActive           Boolean         @default(true)
  postedDate         DateTime        @default(now())
  expiryDate         DateTime
  hashtags           String[]        @default([])
  trending           Boolean         @default(false)
  boostLevel         Int             @default(0)
  cultureFit         String[]        @default([])
  personalityMatch   String[]        @default([])
  whatsappContact    String?
  email              String
  employerId         String
  
  // AI Interview settings
  aiInterviewEnabled Boolean         @default(false)
  autoInviteOnApply  Boolean         @default(false)
  interviewQuestions Int             @default(10)
  interviewTimeLimit Int             @default(15)

  employer     Employer       @relation(fields: [employerId], references: [id], onDelete: Cascade)
  applications Application[]
  swipeHistory SwipeHistory[]
  savedBy      SavedJob[]
}

Application

model Application {
  id                   String            @id @default(cuid())
  userId               String
  jobId                String
  employerId           String
  status               ApplicationStatus @default(pending)
  coverLetter          String?
  cvUrl                String?
  appliedDate          DateTime          @default(now())
  
  // AI Interview fields
  interviewSessionId   String?
  interviewStatus      String?   // pending, in_progress, completed, expired
  interviewScore       Int?      // 0-100 composite score
  interviewGrade       String?   // A+, A, B+, B, C, D, F
  interviewCompletedAt DateTime?

  user      User     @relation(fields: [userId], references: [id], onDelete: Cascade)
  job       Job      @relation(fields: [jobId], references: [id], onDelete: Cascade)
  employer  Employer @relation(fields: [employerId], references: [id], onDelete: Cascade)

  @@unique([userId, jobId])
}

---

Services Models

ServiceWorkerProfile

model ServiceWorkerProfile {
  id              String   @id @default(cuid())
  userId          String   @unique
  headline        String
  bio             String?
  skills          String[] @default([])
  yearsExperience Int      @default(0)

  // Pricing
  priceMin   Decimal? @db.Decimal(10, 2)
  priceMax   Decimal? @db.Decimal(10, 2)
  priceNote  String?
  currency   String   @default("SZL")
  negotiable Boolean  @default(true)

  // Location & Availability
  latitude      Float?
  longitude     Float?
  locationName  String
  serviceRadius Int      @default(25)
  isAvailable   Boolean  @default(true)
  availableDays String[] @default(["monday", "tuesday", "wednesday", "thursday", "friday"])
  availableFrom String?  @default("08:00")
  availableTo   String?  @default("17:00")

  // Media
  introVideo      String?
  portfolioVideos String[] @default([])
  portfolioImages String[] @default([])
  certificateUrls String[] @default([])

  // Verification
  isVerified    Boolean @default(false)
  idVerified    Boolean @default(false)
  phoneVerified Boolean @default(true)

  // Stats
  jobsCompleted   Int     @default(0)
  averageRating   Decimal @default(0) @db.Decimal(2, 1)
  reviewCount     Int     @default(0)
  responseRate    Int     @default(100)
  responseTimeMin Int     @default(60)

  user       User             @relation(fields: [userId], references: [id], onDelete: Cascade)
  categories WorkerCategory[]
  bookings   ServiceBooking[] @relation("WorkerBookings")
  quotes     Quote[]
  reviews    Review[]         @relation("WorkerReviews")
}

ServiceCategory

model ServiceCategory {
  id          String   @id @default(cuid())
  name        String   @unique
  slug        String   @unique
  parentId    String?
  description String?
  icon        String?
  aiGenerated Boolean  @default(true)
  usageCount  Int      @default(0)
  isActive    Boolean  @default(true)

  parent   ServiceCategory?  @relation("CategoryHierarchy", fields: [parentId], references: [id])
  children ServiceCategory[] @relation("CategoryHierarchy")
  workerCategories WorkerCategory[]
}

ServiceBooking

model ServiceBooking {
  id         String  @id @default(cuid())
  clientId   String
  workerId   String
  listingId  String?
  requestId  String?
  categoryId String?

  description       String
  categoryTags      String[] @default([])
  scheduledDate     DateTime
  scheduledTime     String?
  estimatedDuration String?

  latitude     Float
  longitude    Float
  locationName String
  address      String

  agreedPrice Decimal       @db.Decimal(10, 2)
  currency    String        @default("SZL")
  priceNotes  String?
  status      BookingStatus @default(pending)

  acceptedAt  DateTime?
  startedAt   DateTime?
  completedAt DateTime?
  cancelledAt DateTime?

  clientPhone String
  workerPhone String
  notes       String?

  isOnPlatform  Boolean @default(true)
  sourceNote    String?
  paymentStatus String  @default("unpaid")
  paymentMethod String?
  platformFee   Decimal? @db.Decimal(10, 2)

  client   User                 @relation("ClientBookings", fields: [clientId], references: [id])
  worker   ServiceWorkerProfile @relation("WorkerBookings", fields: [workerId], references: [id])
  review   Review?
}

---

Messaging Models

Conversation

model Conversation {
  id              String           @id @default(cuid())
  type            ConversationType
  applicationId   String?
  bookingId       String?
  quoteId         String?
  isActive        Boolean          @default(true)
  lastMessageAt   DateTime?
  lastMessageText String?
  createdAt       DateTime         @default(now())

  participants ConversationParticipant[]
  messages     Message[]
}

Message

model Message {
  id             String        @id @default(cuid())
  conversationId String
  senderId       String
  type           MessageType   @default(text)
  content        String
  images         String[]      @default([])
  status         MessageStatus @default(sent)
  deliveredAt    DateTime?
  readAt         DateTime?
  isDeleted      Boolean       @default(false)
  deletedAt      DateTime?
  replyToId      String?
  createdAt      DateTime      @default(now())

  replyTo      Message?     @relation("MessageReplies", fields: [replyToId], references: [id])
  conversation Conversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)
  sender       User         @relation("SentMessages", fields: [senderId], references: [id])
}

---

Billing Models

UserCredits

model UserCredits {
  id             String   @id @default(cuid())
  userId         String   @unique
  balance        Int      @default(0)
  frozenBalance  Int      @default(0)  // In escrow
  totalPurchased Int      @default(0)
  totalSpent     Int      @default(0)
  totalEarned    Int      @default(0)  // Workers
  totalBonuses   Int      @default(0)

  user         User                @relation(fields: [userId], references: [id])
  transactions CreditTransaction[]
}

Subscription

model Subscription {
  id                   String             @id @default(cuid())
  stripeSubscriptionId String?            @unique
  stripeCustomerId     String
  tier                 SubscriptionTier   @default(free)
  status               SubscriptionStatus @default(active)
  priceId              String?
  currentPeriodStart   DateTime?
  currentPeriodEnd     DateTime?
  billingInterval      BillingInterval?
  trialStart           DateTime?
  trialEnd             DateTime?
  cancelAtPeriodEnd    Boolean            @default(false)
  canceledAt           DateTime?

  stripeCustomer StripeCustomer @relation(fields: [stripeCustomerId], references: [id])
}

---

Experience Lab Models

ExperienceTrack

model ExperienceTrack {
  id             String   @id @default(cuid())
  name           String
  slug           String   @unique
  description    String
  category       String   // "customer_service", "data_entry"
  difficulty     String   // "beginner", "intermediate", "advanced"
  estimatedHours Float
  iconUrl        String?
  isActive       Boolean  @default(true)
  isFree         Boolean  @default(true)

  tasks        ExperienceTask[]
  enrollments  TrackEnrollment[]
  certificates ExperienceCertificate[]
}

ExperienceTask

model ExperienceTask {
  id               String  @id @default(cuid())
  trackId          String
  orderIndex       Int
  title            String
  description      String
  instructions     String  // markdown
  taskType         String  // "multiple_choice", "text_response", "scenario"
  taskData         Json    // Questions, options, rubric
  estimatedMinutes Int
  passingScore     Int     @default(70)
  maxAttempts      Int     @default(3)
  xpReward         Int     @default(50)

  track       ExperienceTrack  @relation(fields: [trackId], references: [id])
  submissions TaskSubmission[]

  @@unique([trackId, orderIndex])
}

ExperienceCertificate

model ExperienceCertificate {
  id               String   @id @default(cuid())
  userId           String
  trackId          String
  verificationCode String   @unique
  overallScore     Int
  pdfUrl           String?
  issuedAt         DateTime @default(now())

  user  User            @relation(fields: [userId], references: [id])
  track ExperienceTrack @relation(fields: [trackId], references: [id])

  @@unique([userId, trackId])
}

---

Database Indexes

Key indexes for performance:

@@index([phone])                      // User phone lookup
@@index([email])                      // Employer email lookup
@@index([latitude, longitude])        // GPS queries
@@index([isActive, postedDate(sort: Desc)])  // Job feed
@@index([employerId, status])         // Application filtering
@@index([conversationId, createdAt(sort: Desc)])  // Message pagination