warmbox/prisma/schema.prisma

generator client {
  provider = "prisma-client"
  output   = "../apps/api/src/generated/prisma"
}

datasource db {
  provider = "sqlite"
}

enum InboxStatus {
  active
  paused
  error
}

enum WarmupStatus {
  sent
  rescued_from_spam
  engaged_no_reply
  replied
  complete
}

enum MailboxRole {
  unassigned
  primary
  satellite
}

enum MailboxProvider {
  google_workspace
  gmail
  yahoo
  outlook
  custom
}

enum MailboxAuthType {
  password
  microsoft_oauth
}

enum CampaignRecipe {
  grow
  flat
  randomized
  custom
}

enum CampaignStatus {
  draft
  active
  paused
  maintenance
  completed
  failed
}

model Workspace {
  id        String   @id @default(cuid())
  name      String   @unique
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  inboxes   Inbox[]
  campaigns WarmupCampaign[]
}

enum SendJobStatus {
  pending
  processing
  completed
  failed
}

model SendJob {
  id             String        @id @default(cuid())
  campaignId     String
  scheduledAt    DateTime
  status         SendJobStatus @default(pending)
  idempotencyKey String        @unique
  processedAt    DateTime?
  errorMessage   String?
  createdAt      DateTime      @default(now())
  updatedAt      DateTime      @updatedAt

  campaign WarmupCampaign @relation(fields: [campaignId], references: [id], onDelete: Cascade)

  @@index([campaignId, status, scheduledAt])
}

model JobRunLog {
  id        String   @id @default(cuid())
  jobName   String
  ranAt     DateTime @default(now())
  success   Boolean  @default(true)
  message   String?

  @@index([jobName, ranAt])
}

model Inbox {
  id          String          @id @default(cuid())
  workspaceId String
  email       String
  smtpHost    String
  smtpPort    Int
  imapHost    String
  imapPort    Int
  username    String
  password    String
  authType    MailboxAuthType @default(password)
  oauthRefreshToken String?
  role        MailboxRole     @default(unassigned)
  provider    MailboxProvider @default(custom)
  status      InboxStatus     @default(active)
  dailyLimit  Int             @default(40)
  currentRamp Int             @default(5)
  industry    String          @default("SaaS")
  senderName  String?
  companyName String?
  lastError            String?
  errorAt              DateTime?
  consecutiveFailures  Int      @default(0)
  spamFolderPath    String?
  lastSpamRescuedAt DateTime?
  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt

  workspace          Workspace           @relation(fields: [workspaceId], references: [id], onDelete: Cascade)
  sentLogs           WarmupLog[]         @relation("Sender")
  receivedLogs       WarmupLog[]         @relation("Receiver")
  primaryCampaigns   WarmupCampaign[]    @relation("PrimaryMailbox")
  satelliteCampaigns CampaignSatellite[]

  @@unique([workspaceId, email])
  @@index([status])
  @@index([workspaceId, role])
}

model WarmupCampaign {
  id                       String         @id @default(cuid())
  workspaceId              String
  primaryMailboxId         String
  name                     String
  recipe                   CampaignRecipe @default(grow)
  status                   CampaignStatus @default(draft)
  durationDays             Int            @default(30)
  minEmailsPerDay          Int            @default(1)
  maxEmailsPerDay          Int            @default(40)
  replyRatePercent         Int            @default(30)
  maintenanceVolumePercent Int            @default(20)
  timezone                 String         @default("America/Vancouver")
  sendWindowStart          String         @default("08:00")
  sendWindowEnd            String         @default("18:00")
  customSchedule           String?
  startedAt                DateTime?
  endsAt                   DateTime?
  currentDay               Int            @default(0)
  pausedFromStatus         CampaignStatus?
  lastSendAt               DateTime?
  createdAt                DateTime       @default(now())
  updatedAt                DateTime       @updatedAt

  workspace      Workspace           @relation(fields: [workspaceId], references: [id], onDelete: Cascade)
  primaryMailbox Inbox               @relation("PrimaryMailbox", fields: [primaryMailboxId], references: [id], onDelete: Cascade)
  satellites     CampaignSatellite[]
  dailySchedules DailySchedule[]
  dailyStats     DailyStat[]
  sendJobs       SendJob[]
  warmupLogs     WarmupLog[]

  @@index([workspaceId, status])
  @@index([primaryMailboxId, status])
}

model CampaignSatellite {
  id                 String   @id @default(cuid())
  campaignId         String
  satelliteMailboxId String
  weight             Int      @default(1)
  active             Boolean  @default(true)
  createdAt          DateTime @default(now())
  updatedAt          DateTime @updatedAt

  campaign         WarmupCampaign @relation(fields: [campaignId], references: [id], onDelete: Cascade)
  satelliteMailbox Inbox          @relation(fields: [satelliteMailboxId], references: [id], onDelete: Cascade)

  @@unique([campaignId, satelliteMailboxId])
  @@index([satelliteMailboxId])
}

model DailySchedule {
  id             String   @id @default(cuid())
  campaignId     String
  dayIndex       Int
  date           DateTime?
  plannedSends   Int
  plannedReplies Int
  actualSends    Int      @default(0)
  actualReplies  Int      @default(0)
  spamDetected   Int      @default(0)
  spamRescued    Int      @default(0)
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  campaign WarmupCampaign @relation(fields: [campaignId], references: [id], onDelete: Cascade)

  @@unique([campaignId, dayIndex])
  @@index([campaignId])
}

model DailyStat {
  id                 String   @id @default(cuid())
  campaignId         String
  date               DateTime
  dayIndex           Int
  plannedSends       Int
  plannedReplies     Int
  actualSends        Int      @default(0)
  actualReplies      Int      @default(0)
  spamDetected       Int      @default(0)
  spamRescued        Int      @default(0)
  replyRateActual    Float?
  inboxPlacementRate Float?
  createdAt          DateTime @default(now())
  updatedAt          DateTime @updatedAt

  campaign WarmupCampaign @relation(fields: [campaignId], references: [id], onDelete: Cascade)

  @@unique([campaignId, date])
  @@index([campaignId])
}

model WarmupLog {
  id              String       @id @default(cuid())
  campaignId      String?
  senderId        String
  receiverId      String
  messageId       String?
  inReplyTo       String?
  subject         String
  body            String
  status          WarmupStatus @default(sent)
  placement       String?
  rescuedFromSpam Boolean      @default(false)
  validationResult String?
  createdAt       DateTime     @default(now())
  updatedAt       DateTime     @updatedAt

  campaign WarmupCampaign? @relation(fields: [campaignId], references: [id], onDelete: SetNull)
  sender   Inbox           @relation("Sender", fields: [senderId], references: [id], onDelete: Cascade)
  receiver Inbox           @relation("Receiver", fields: [receiverId], references: [id], onDelete: Cascade)

  @@index([senderId, status])
  @@index([receiverId, status])
  @@index([messageId])
  @@index([campaignId])
}

model Setting {
  id        String   @id @default(cuid())
  key       String   @unique
  value     String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}