stack/apps/backend/prisma/schema.prisma

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

datasource db {
  provider = "postgresql"
}

model SchemaMigration {
  id            String   @id @default(dbgenerated("gen_random_uuid()"))
  finishedAt    DateTime
  migrationName String   @unique

  @@ignore
}

model Project {
  // Note that the project with ID `internal` is handled as a special case. All other project IDs are UUIDs.
  id String @id

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  displayName      String
  description      String  @default("")
  isProductionMode Boolean
  isDevelopmentEnvironment Boolean @default(false)
  ownerTeamId      String? @db.Uuid
  onboardingStatus String  @default("completed")
  onboardingState  Json?

  logoUrl             String?
  logoFullUrl         String?
  logoDarkModeUrl     String?
  logoFullDarkModeUrl String?

  projectConfigOverride Json?
  stripeAccountId       String?

  apiKeySets                 ApiKeySet[]
  projectUsers               ProjectUser[]
  provisionedProject         ProvisionedProject?
  tenancies                  Tenancy[]
  branchConfigOverrides      BranchConfigOverride[]
  environmentConfigOverrides EnvironmentConfigOverride[]
  localEmulatorProject       LocalEmulatorProject?
  aiConversations            AiConversation[]

  @@index([ownerTeamId], map: "Project_ownerTeamId_idx")
}

model LocalEmulatorProject {
  absoluteFilePath String @id
  projectId        String @unique

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  project Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
}

model Tenancy {
  id String @id @default(uuid()) @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  projectId String
  project   Project @relation(fields: [projectId], references: [id], onDelete: Cascade)

  branchId String

  // If organizationId is NULL, hasNoOrganization must be TRUE. If organizationId is not NULL, hasNoOrganization must be NULL.
  organizationId      String?              @db.Uuid
  hasNoOrganization   BooleanTrue?
  emailOutboxes       EmailOutbox[]
  sessionReplays      SessionReplay[]
  sessionReplayChunks SessionReplayChunk[]
  managedEmailDomains ManagedEmailDomain[]
  conversations       Conversation[]
  conversationEntryPoints ConversationEntryPoint[]
  conversationMessages ConversationMessage[]

  // Email capacity boost - when set and in the future, email capacity is multiplied by 4
  emailCapacityBoostExpiresAt DateTime?

  @@unique([projectId, branchId, organizationId])
  @@unique([projectId, branchId, hasNoOrganization])
}

enum ManagedEmailDomainStatus {
  PENDING_DNS
  PENDING_VERIFICATION
  VERIFIED
  APPLIED
  FAILED
}

model ManagedEmailDomain {
  id String @id @default(uuid()) @db.Uuid

  tenancyId String  @db.Uuid
  tenancy   Tenancy @relation(fields: [tenancyId], references: [id], onDelete: Cascade)

  projectId String
  branchId  String

  subdomain         String
  senderLocalPart   String
  resendDomainId    String @unique
  nameServerRecords Json

  status            ManagedEmailDomainStatus @default(PENDING_VERIFICATION)
  providerStatusRaw String?
  isActive          Boolean                  @default(true)
  lastError         String?
  verifiedAt        DateTime?
  appliedAt         DateTime?
  lastWebhookAt     DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@unique([tenancyId, subdomain])
  @@index([tenancyId, status, isActive], map: "ManagedEmailDomain_tenancy_status_active_idx")
}

model BranchConfigOverride {
  projectId String
  branchId  String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  config Json
  source Json?

  project Project @relation(fields: [projectId], references: [id], onDelete: Cascade)

  @@id([projectId, branchId])
}

model EnvironmentConfigOverride {
  projectId String
  branchId  String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  config Json

  project Project @relation(fields: [projectId], references: [id], onDelete: Cascade)

  @@id([projectId, branchId])
}

model ExternalDbSyncMetadata {
  id String @id @default(dbgenerated("gen_random_uuid()"))

  singleton BooleanTrue @unique @default(TRUE)

  sequencerEnabled Boolean @default(true)
  pollerEnabled    Boolean @default(true)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model Team {
  tenancyId String @db.Uuid
  teamId    String @default(uuid()) @db.Uuid

  // Team IDs must be unique across all organizations (but not necessarily across all branches).
  // To model this in the DB, we add two columns that are always equal to tenancy.projectId and tenancy.branchId.
  mirroredProjectId String
  mirroredBranchId  String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  displayName            String
  clientMetadata         Json?
  clientReadOnlyMetadata Json?
  serverMetadata         Json?
  profileImageUrl        String?

  sequenceId             BigInt? @unique
  shouldUpdateSequenceId Boolean @default(true)

  teamMembers   TeamMember[]
  projectApiKey ProjectApiKey[]

  @@id([tenancyId, teamId])
  @@unique([mirroredProjectId, mirroredBranchId, teamId])
  @@index([tenancyId, sequenceId], name: "Team_tenancyId_sequenceId_idx")
  @@index([shouldUpdateSequenceId, tenancyId], name: "Team_shouldUpdateSequenceId_idx")
}

// This is used for fields that are boolean but only the true value is part of a unique constraint.
// For example if you want to allow only one selected team per user, you can make an optional field with this type and add a unique constraint.
// Only the true value is considered for the unique constraint, the null value is not.
enum BooleanTrue {
  TRUE
}

model TeamMember {
  tenancyId     String @db.Uuid
  projectUserId String @db.Uuid
  teamId        String @db.Uuid

  // This will override the displayName of the user in this team.
  displayName     String?
  profileImageUrl String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  sequenceId             BigInt? @unique
  shouldUpdateSequenceId Boolean @default(true)

  projectUser                 ProjectUser                  @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)
  team                        Team                         @relation(fields: [tenancyId, teamId], references: [tenancyId, teamId], onDelete: Cascade)
  isSelected                  BooleanTrue?
  teamMemberDirectPermissions TeamMemberDirectPermission[]

  @@id([tenancyId, projectUserId, teamId])
  @@unique([tenancyId, projectUserId, isSelected])
  @@index([tenancyId, projectUserId, isSelected], map: "TeamMember_projectUserId_isSelected_idx")
  @@index([tenancyId, sequenceId], name: "TeamMember_tenancyId_sequenceId_idx")
  @@index([shouldUpdateSequenceId, tenancyId], name: "TeamMember_shouldUpdateSequenceId_idx")
}

model ProjectUserDirectPermission {
  id            String @id @default(uuid()) @db.Uuid
  tenancyId     String @db.Uuid
  projectUserId String @db.Uuid
  permissionId  String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  projectUser ProjectUser @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)

  sequenceId             BigInt?  @unique
  shouldUpdateSequenceId Boolean  @default(true)

  @@unique([tenancyId, projectUserId, permissionId])
  @@index([shouldUpdateSequenceId, tenancyId], name: "ProjectUserDirectPermission_shouldUpdateSequenceId_idx")
  @@index([tenancyId, sequenceId], name: "ProjectUserDirectPermission_tenancyId_sequenceId_idx")
}

model TeamMemberDirectPermission {
  id            String @id @default(uuid()) @db.Uuid
  tenancyId     String @db.Uuid
  projectUserId String @db.Uuid
  teamId        String @db.Uuid
  permissionId  String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  teamMember TeamMember @relation(fields: [tenancyId, projectUserId, teamId], references: [tenancyId, projectUserId, teamId], onDelete: Cascade)

  sequenceId             BigInt?  @unique
  shouldUpdateSequenceId Boolean  @default(true)

  @@unique([tenancyId, projectUserId, teamId, permissionId])
  @@index([shouldUpdateSequenceId, tenancyId], name: "TeamMemberDirectPermission_shouldUpdateSequenceId_idx")
  @@index([tenancyId, sequenceId], name: "TeamMemberDirectPermission_tenancyId_sequenceId_idx")
}

model ProjectUser {
  tenancyId     String @db.Uuid
  projectUserId String @default(uuid()) @db.Uuid

  // User IDs must be unique across all organizations (but not necessarily across all branches).
  // To model this in the DB, we add two columns that are always equal to tenancy.projectId and tenancy.branchId.
  mirroredProjectId String
  mirroredBranchId  String

  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt
  lastActiveAt DateTime @default(now())

  sequenceId             BigInt? @unique
  shouldUpdateSequenceId Boolean @default(true)

  displayName            String?
  serverMetadata         Json?
  clientReadOnlyMetadata Json?
  clientMetadata         Json?
  profileImageUrl        String?
  requiresTotpMfa        Boolean @default(false)
  totpSecret             Bytes?
  isAnonymous            Boolean @default(false)
  signUpCountryCode      String?

  // Admin restriction fields - can be set by signup rules or manually by admins
  restrictedByAdmin               Boolean @default(false)
  restrictedByAdminReason         String? // Publicly viewable reason (shown to user)
  restrictedByAdminPrivateDetails String? // Private details (server access only)

  // Sign-up metadata
  signedUpAt            DateTime @default(now())
  signUpIp              String?
  signUpIpTrusted       Boolean?
  signUpEmailNormalized String?
  signUpEmailBase       String?

  // Sign-up risk scores (0-100, set at sign-up time)
  signUpRiskScoreBot            Int @default(0) @db.SmallInt
  signUpRiskScoreFreeTrialAbuse Int @default(0) @db.SmallInt

  projectUserOAuthAccounts ProjectUserOAuthAccount[]
  teamMembers              TeamMember[]
  contactChannels          ContactChannel[]
  authMethods              AuthMethod[]

  // some backlinks for the unique constraints on some auth methods
  passwordAuthMethod         PasswordAuthMethod[]
  passkeyAuthMethod          PasskeyAuthMethod[]
  otpAuthMethod              OtpAuthMethod[]
  oauthAuthMethod            OAuthAuthMethod[]
  projectApiKey              ProjectApiKey[]
  directPermissions          ProjectUserDirectPermission[]
  Project                    Project?                      @relation(fields: [projectId], references: [id])
  projectId                  String?
  userNotificationPreference UserNotificationPreference[]
  sessionReplays             SessionReplay[]

  @@id([tenancyId, projectUserId])
  @@unique([mirroredProjectId, mirroredBranchId, projectUserId])
  // indices for sorting and filtering
  @@index([tenancyId, displayName(sort: Asc)], name: "ProjectUser_displayName_asc")
  @@index([tenancyId, displayName(sort: Desc)], name: "ProjectUser_displayName_desc")
  @@index([tenancyId, createdAt(sort: Asc)], name: "ProjectUser_createdAt_asc")
  @@index([tenancyId, createdAt(sort: Desc)], name: "ProjectUser_createdAt_desc")
  @@index([tenancyId, isAnonymous, signedUpAt(sort: Asc)], name: "ProjectUser_signedUpAt_asc")
  @@index([tenancyId, isAnonymous, lastActiveAt], name: "ProjectUser_lastActiveAt")
  @@index([tenancyId, isAnonymous, signUpIp, signedUpAt], name: "ProjectUser_signUpIp_recent_idx")
  @@index([tenancyId, isAnonymous, signUpEmailNormalized, signedUpAt], name: "ProjectUser_signUpEmailNormalized_recent_idx")
  @@index([tenancyId, isAnonymous, signUpEmailBase, signedUpAt], name: "ProjectUser_signUpEmailBase_recent_idx")
  @@index([tenancyId, sequenceId], name: "ProjectUser_tenancyId_sequenceId_idx")
  @@index([shouldUpdateSequenceId, tenancyId], name: "ProjectUser_shouldUpdateSequenceId_idx")
}

// This should be renamed to "OAuthAccount" as it is not always bound to a user
// When ever a user goes through the OAuth flow and gets an account ID from the OAuth provider, we store that here.
model ProjectUserOAuthAccount {
  id                    String  @default(uuid()) @db.Uuid
  tenancyId             String  @db.Uuid
  projectUserId         String? @db.Uuid
  configOAuthProviderId String
  providerAccountId     String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // This is used for the user to distinguish between multiple accounts from the same provider.
  // we might want to add more user info here later
  email String?

  // Before the OAuth account is connected to a user (for example, in the link oauth process), the projectUser is null.
  projectUser      ProjectUser?       @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)
  oauthTokens      OAuthToken[]
  oauthAccessToken OAuthAccessToken[]

  // if allowSignIn is true, oauthAuthMethod must be set
  oauthAuthMethod        OAuthAuthMethod?
  allowConnectedAccounts Boolean          @default(true)
  allowSignIn            Boolean          @default(true)

  sequenceId             BigInt?  @unique
  shouldUpdateSequenceId Boolean  @default(true)

  @@id([tenancyId, id])
  @@unique([tenancyId, configOAuthProviderId, projectUserId, providerAccountId])
  @@index([tenancyId, projectUserId])
  @@index([tenancyId, sequenceId], name: "ProjectUserOAuthAccount_tenancyId_sequenceId_idx")
  @@index([shouldUpdateSequenceId, tenancyId], name: "ProjectUserOAuthAccount_shouldUpdateSequenceId_idx")
}

model SessionReplay {
  id String @db.Uuid

  tenancyId      String @db.Uuid
  projectUserId  String @db.Uuid
  refreshTokenId String @db.Uuid

  startedAt   DateTime
  lastEventAt DateTime

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  sequenceId             BigInt? @unique
  shouldUpdateSequenceId Boolean @default(true)

  projectUser ProjectUser @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)
  tenancy     Tenancy     @relation(fields: [tenancyId], references: [id], onDelete: Cascade)

  chunks SessionReplayChunk[]

  @@id([tenancyId, id])
  @@index([tenancyId, projectUserId, startedAt])
  @@index([tenancyId, lastEventAt])
  // index by updatedAt instead of lastEventAt because event timing can be spoofed
  @@index([tenancyId, refreshTokenId, updatedAt])
  @@index([tenancyId, sequenceId], name: "SessionReplay_tenancyId_sequenceId_idx")
  @@index([shouldUpdateSequenceId, tenancyId], name: "SessionReplay_shouldUpdateSequenceId_idx")
  @@map("SessionReplay")
}

model SessionReplayChunk {
  id String @id @default(uuid()) @db.Uuid

  tenancyId       String @db.Uuid
  sessionReplayId String @map("sessionReplayId") @db.Uuid

  // Unique per uploaded batch for a given session id.
  batchId String @db.Uuid

  // Ephemeral in-memory id generated by the client. Used to group recording chunks into per-tab replay segments.
  sessionReplaySegmentId String

  // Client-generated session id from localStorage, stored as metadata.
  browserSessionId String

  s3Key      String
  eventCount Int
  byteLength Int

  firstEventAt DateTime
  lastEventAt  DateTime

  createdAt DateTime @default(now())

  sessionReplay SessionReplay @relation(fields: [tenancyId, sessionReplayId], references: [tenancyId, id], onDelete: Cascade)
  tenancy       Tenancy       @relation(fields: [tenancyId], references: [id], onDelete: Cascade)

  @@unique([tenancyId, sessionReplayId, batchId])
  @@index([tenancyId, sessionReplayId, createdAt])
  @@map("SessionReplayChunk")
}

enum ContactChannelType {
  EMAIL
  // PHONE
}

model ContactChannel {
  tenancyId     String @db.Uuid
  projectUserId String @db.Uuid
  id            String @default(uuid()) @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  sequenceId             BigInt? @unique
  shouldUpdateSequenceId Boolean @default(true)

  type        ContactChannelType
  isPrimary   BooleanTrue?
  usedForAuth BooleanTrue?
  isVerified  Boolean
  value       String

  projectUser ProjectUser @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)

  @@id([tenancyId, projectUserId, id])
  // each user has at most one primary contact channel of each type
  @@unique([tenancyId, projectUserId, type, isPrimary])
  // value must be unique per user per type
  @@unique([tenancyId, projectUserId, type, value])
  // only one contact channel per project with the same value and type can be used for auth
  @@unique([tenancyId, type, value, usedForAuth])
  @@index([tenancyId, sequenceId], name: "ContactChannel_tenancyId_sequenceId_idx")
  @@index([shouldUpdateSequenceId, tenancyId], name: "ContactChannel_shouldUpdateSequenceId_idx")
}

model AuthMethod {
  tenancyId     String @db.Uuid
  id            String @default(uuid()) @db.Uuid
  projectUserId String @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // exactly one of the xyzAuthMethods should be set
  otpAuthMethod      OtpAuthMethod?
  passwordAuthMethod PasswordAuthMethod?
  passkeyAuthMethod  PasskeyAuthMethod?
  oauthAuthMethod    OAuthAuthMethod?

  projectUser ProjectUser @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)

  @@id([tenancyId, id])
  @@index([tenancyId, projectUserId])
}

model OtpAuthMethod {
  tenancyId     String @db.Uuid
  authMethodId  String @db.Uuid
  projectUserId String @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  authMethod  AuthMethod  @relation(fields: [tenancyId, authMethodId], references: [tenancyId, id], onDelete: Cascade)
  projectUser ProjectUser @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)

  @@id([tenancyId, authMethodId])
  // a user can only have one OTP auth method
  @@unique([tenancyId, projectUserId])
}

model PasswordAuthMethod {
  tenancyId     String @db.Uuid
  authMethodId  String @db.Uuid
  projectUserId String @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  passwordHash String

  authMethod  AuthMethod  @relation(fields: [tenancyId, authMethodId], references: [tenancyId, id], onDelete: Cascade)
  projectUser ProjectUser @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)

  @@id([tenancyId, authMethodId])
  // a user can only have one password auth method
  @@unique([tenancyId, projectUserId])
}

model PasskeyAuthMethod {
  tenancyId     String @db.Uuid
  authMethodId  String @db.Uuid
  projectUserId String @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  credentialId         String
  publicKey            String
  userHandle           String
  transports           String[]
  credentialDeviceType String
  counter              Int

  authMethod  AuthMethod  @relation(fields: [tenancyId, authMethodId], references: [tenancyId, id], onDelete: Cascade)
  projectUser ProjectUser @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)

  @@id([tenancyId, authMethodId])
  // a user can only have one password auth method
  @@unique([tenancyId, projectUserId])
}

// This connects to projectUserOauthAccount, which might be shared between auth method and connected account.
model OAuthAuthMethod {
  tenancyId             String @db.Uuid
  authMethodId          String @db.Uuid
  configOAuthProviderId String
  providerAccountId     String
  projectUserId         String @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  authMethod   AuthMethod              @relation(fields: [tenancyId, authMethodId], references: [tenancyId, id], onDelete: Cascade)
  oauthAccount ProjectUserOAuthAccount @relation(fields: [tenancyId, configOAuthProviderId, projectUserId, providerAccountId], references: [tenancyId, configOAuthProviderId, projectUserId, providerAccountId])
  projectUser  ProjectUser             @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)

  @@id([tenancyId, authMethodId])
  @@unique([tenancyId, configOAuthProviderId, providerAccountId])
  @@unique([tenancyId, projectUserId, configOAuthProviderId])
  @@unique([tenancyId, configOAuthProviderId, projectUserId, providerAccountId])
}

enum StandardOAuthProviderType {
  GITHUB
  FACEBOOK
  GOOGLE
  MICROSOFT
  SPOTIFY
  DISCORD
  GITLAB
  BITBUCKET
  LINKEDIN
  APPLE
  X
  TWITCH
}

model OAuthToken {
  id String @id @default(uuid()) @db.Uuid

  tenancyId      String @db.Uuid
  oauthAccountId String @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  projectUserOAuthAccount ProjectUserOAuthAccount @relation(fields: [tenancyId, oauthAccountId], references: [tenancyId, id], onDelete: Cascade)

  refreshToken String
  scopes       String[]
  isValid      Boolean  @default(true)
}

model OAuthAccessToken {
  id String @id @default(uuid()) @db.Uuid

  tenancyId      String @db.Uuid
  oauthAccountId String @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  projectUserOAuthAccount ProjectUserOAuthAccount @relation(fields: [tenancyId, oauthAccountId], references: [tenancyId, id], onDelete: Cascade)

  accessToken String
  scopes      String[]
  expiresAt   DateTime?
  isValid     Boolean  @default(true)
}

model OAuthOuterInfo {
  id String @id @default(uuid()) @db.Uuid

  info       Json
  innerState String   @unique
  expiresAt  DateTime

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt
}

model ProjectUserRefreshToken {
  id            String @default(uuid()) @db.Uuid
  tenancyId     String @db.Uuid
  projectUserId String @db.Uuid

  createdAt          DateTime @default(now())
  updatedAt          DateTime @updatedAt
  lastActiveAt       DateTime @default(now())
  lastActiveAtIpInfo Json?

  refreshToken    String    @unique
  expiresAt       DateTime?
  isImpersonation Boolean   @default(false)

  sequenceId             BigInt?  @unique
  shouldUpdateSequenceId Boolean  @default(true)

  @@id([tenancyId, id])
  @@index([tenancyId, sequenceId], name: "ProjectUserRefreshToken_tenancyId_sequenceId_idx")
  @@index([shouldUpdateSequenceId, tenancyId], name: "ProjectUserRefreshToken_shouldUpdateSequenceId_idx")
}

model ProjectUserAuthorizationCode {
  tenancyId     String @db.Uuid
  projectUserId String @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  authorizationCode String   @unique
  redirectUri       String
  expiresAt         DateTime

  codeChallenge       String
  codeChallengeMethod String

  newUser                  Boolean
  afterCallbackRedirectUrl String?
  /// Refresh token ID that should be granted when this authorization code is exchanged.
  /// NULL means no specific refresh token is preselected, so the token endpoint follows normal issuance/reuse logic.
  grantedRefreshTokenId    String? @db.Uuid

  @@id([tenancyId, authorizationCode])
}

model VerificationCode {
  projectId String
  branchId  String
  id        String @default(uuid()) @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  type         VerificationCodeType
  code         String
  expiresAt    DateTime
  usedAt       DateTime?
  redirectUrl  String?
  method       Json                 @default("null")
  data         Json
  attemptCount Int                  @default(0)

  sequenceId             BigInt?  @unique
  shouldUpdateSequenceId Boolean  @default(true)

  @@id([projectId, branchId, id])
  @@unique([projectId, branchId, code])
  @@index([data(ops: JsonbPathOps)], type: Gin)
  @@index([shouldUpdateSequenceId, type], name: "VerificationCode_shouldUpdateSequenceId_type_idx")
}

enum VerificationCodeType {
  ONE_TIME_PASSWORD
  PASSWORD_RESET
  CONTACT_CHANNEL_VERIFICATION
  TEAM_INVITATION
  MFA_ATTEMPT
  PASSKEY_REGISTRATION_CHALLENGE
  PASSKEY_AUTHENTICATION_CHALLENGE
  INTEGRATION_PROJECT_TRANSFER
  PURCHASE_URL
}

//#region API keys
// Internal API keys
model ApiKeySet {
  projectId String
  project   Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  id        String   @default(uuid()) @db.Uuid
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  description          String
  expiresAt            DateTime
  manuallyRevokedAt    DateTime?
  publishableClientKey String?   @unique
  secretServerKey      String?   @unique
  superSecretAdminKey  String?   @unique

  @@id([projectId, id])
}

//#endregion

model ProjectApiKey {
  tenancyId String @db.Uuid

  id           String @default(uuid()) @db.Uuid
  secretApiKey String @unique

  // Validity and revocation
  createdAt         DateTime  @default(now())
  updatedAt         DateTime  @updatedAt
  expiresAt         DateTime?
  manuallyRevokedAt DateTime?
  description       String
  isPublic          Boolean

  // exactly one of [teamId] or [projectUserId] must be set
  teamId        String? @db.Uuid
  projectUserId String? @db.Uuid

  projectUser ProjectUser? @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)
  team        Team?        @relation(fields: [tenancyId, teamId], references: [tenancyId, teamId], onDelete: Cascade)

  @@id([tenancyId, id])
}

enum EmailTemplateType {
  EMAIL_VERIFICATION
  PASSWORD_RESET
  MAGIC_LINK
  TEAM_INVITATION
  SIGN_IN_INVITATION
}

model EmailTemplate {
  projectId String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  content Json
  type    EmailTemplateType
  subject String

  @@id([projectId, type])
}

//#region IdP
model IdPAccountToCdfcResultMapping {
  idpId String
  id    String

  idpAccountId String @unique @db.Uuid
  cdfcResult   Json

  @@id([idpId, id])
}

model ProjectWrapperCodes {
  idpId String
  id    String @default(uuid()) @db.Uuid

  interactionUid    String
  authorizationCode String @unique

  cdfcResult Json

  @@id([idpId, id])
}

model IdPAdapterData {
  idpId String
  model String
  id    String

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  payload   Json
  expiresAt DateTime

  @@id([idpId, model, id])
  @@index([payload(ops: JsonbPathOps)], type: Gin)
  @@index([expiresAt])
}

//#endregion

model ProvisionedProject {
  projectId String  @id
  project   Project @relation(fields: [projectId], references: [id], onDelete: Cascade)

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  clientId String
}

//#region Events

model Event {
  id String @id @default(uuid()) @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // if isWide == false, then eventEndedAt is always equal to eventStartedAt
  isWide         Boolean
  eventStartedAt DateTime
  eventEndedAt   DateTime

  // TODO: add event_type, and at least one of either system_event_type or event_type is always set
  systemEventTypeIds String[]
  data               Json

  // ============================== BEGIN END USER PROPERTIES ==============================
  // Below are properties describing the end user that caused this event to be logged
  // This is different from a request IP. See: apps/backend/src/lib/end-users.tsx

  // Note that the IP may have been spoofed, unless isEndUserIpInfoGuessTrusted is true
  endUserIpInfoGuessId        String?      @db.Uuid
  endUserIpInfoGuess          EventIpInfo? @relation("EventIpInfo", fields: [endUserIpInfoGuessId], references: [id])
  // If true, then endUserIpInfoGuess is not spoofed (might still be behind VPNs/proxies). If false, then the values may be spoofed.
  isEndUserIpInfoGuessTrusted Boolean      @default(false)
  // =============================== END END USER PROPERTIES ===============================

  @@index([data(ops: JsonbPathOps)], type: Gin)
  @@index([createdAt])
  @@index([createdAt, id])
}

// An IP address that was seen in an event. Use the location fields instead of refetching the location from the ip, as the real-world geoip data may have changed since the event was logged.
model EventIpInfo {
  id String @id @default(uuid()) @db.Uuid

  ip String

  countryCode  String?
  regionCode   String?
  cityName     String?
  latitude     Float?
  longitude    Float?
  tzIdentifier String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  events Event[] @relation("EventIpInfo")
}

//#endregion

enum EmailOutboxStatus {
  PAUSED
  PREPARING
  RENDERING
  RENDER_ERROR
  SCHEDULED
  QUEUED
  SENDING
  SERVER_ERROR
  SENT
  SKIPPED
  DELIVERY_DELAYED
  BOUNCED
  OPENED
  CLICKED
  MARKED_AS_SPAM
}

enum EmailOutboxSimpleStatus {
  IN_PROGRESS
  ERROR
  OK
}

enum EmailOutboxSkippedReason {
  USER_UNSUBSCRIBED
  USER_ACCOUNT_DELETED
  USER_HAS_NO_PRIMARY_EMAIL
  NO_EMAIL_PROVIDED
  LIKELY_NOT_DELIVERABLE
  MANUALLY_CANCELLED
}

enum EmailOutboxCreatedWith {
  DRAFT
  PROGRAMMATIC_CALL
}

model EmailOutbox {
  tenancyId String @db.Uuid

  id String @default(uuid()) @db.Uuid

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // note: [tsxSource, themeId, isHighPriority, toUserIds, toEmails, extraRenderVariables, shouldSkipDeliverabilityCheck, overrideSubject, overrideNotificationCategoryId] can be changed, but only while startedSendingAt is not set, and any modification should reset fields like [renderedByWorkerId, startedRenderingAt, finishedRenderingAt, renderError*, rendered*, isQueued] and any other fields with similar semantics to null.
  tsxSource                      String
  themeId                        String?
  isHighPriority                 Boolean
  // Who the email is being sent to. { type: "user-primary-email", userId: string } | { type: "user-custom-emails", userId: string, emails: string[] } | { type: "custom-emails", emails: string[] }
  to                             Json
  extraRenderVariables           Json
  overrideSubject                String?
  overrideNotificationCategoryId String?
  shouldSkipDeliverabilityCheck  Boolean

  createdWith                     EmailOutboxCreatedWith
  // If the email was created from a draft, it is kept around so we can later group by it. Must be set if and only if createdWith is DRAFT. (Enforced by EmailOutbox_email_draft_check and EmailOutbox_email_draft_reverse_check constraints.)
  emailDraftId                    String?
  // If the email was created from a template programmatically, it is kept around so we can later group by it. Must be NOT set if createdWith is NOT PROGRAMMATIC_CALL. If createdWith is PROGRAMMATIC_CALL and this is not set, then no template was used (eg. email was sent directly as HTML). (Enforced by EmailOutbox_email_programmatic_call_template_check constraint.)
  emailProgrammaticCallTemplateId String?

  // Computed from EmailOutbox can be the `status` of the email:
  //
  // - ⚪ `paused`           : isPaused (can happen at any time)
  // - ⚫ `skipped`          : !isPaused && skippedReason (can happen at any time, like paused)
  // - ⚪ `preparing`        : !isPaused && !skippedReason && !startedRenderingAt
  // - ⚪ `rendering`        : !isPaused && !skippedReason && !finishedRenderingAt
  // - 🔴 `render-error`     : !isPaused && !skippedReason && finishedRenderingAt && renderError
  // - ⚪ `scheduled`        : !isPaused && !skippedReason && finishedRenderingAt && !renderError && !isQueued
  // - ⚪ `queued`           : !isPaused && !skippedReason && finishedRenderingAt && !renderError && isQueued && !startedSendingAt
  // - ⚪ `sending`          : !isPaused && !skippedReason && startedSendingAt && (canHaveDeliveryInfo ? !deliveredAt : !finishedSendingAt)
  // - 🔴 `server-error`     : !isPaused && !skippedReason && finishedSendingAt && sendServerErrorMessage
  // - 🟢 `sent`             : !isPaused && !skippedReason && finishedSendingAt && !openedAt && !markedAsSpamAt && !sendServerErrorMessage && (canHaveDeliveryInfo ? deliveredAt : finishedSendingAt)
  // - ⚪ `delivery-delayed` : !isPaused && !skippedReason && canHaveDeliveryInfo && deliveryDelayedAt
  // - 🔴 `bounced`          : !isPaused && !skippedReason && canHaveDeliveryInfo && bouncedAt
  // - 🔵 `opened`           : !isPaused && !skippedReason && openedAt && !clickedAt && !markedAsSpamAt
  // - 🟣 `clicked`          : !isPaused && !skippedReason && clickedAt && !markedAsSpamAt
  // - 🟡 `marked-as-spam`   : !isPaused && !skippedReason && markedAsSpamAt
  //
  // NOTE: The `sending` status is NOT the same as `finishedSendingAt`! If the outbox can have delivery info, then
  // `sending` is about deliveredAt. A better name would be `delivering`, but that name is less catchy than `sending`.
  //
  // This column is auto-generated as defined in the SQL migration. It can not be set manually. Note: Setting the dbgenerated value is NOT sufficient to create a generated column in Postgres! (Prisma dbgenerated only generates a value for the *default*, and won't reflect any updates.) You must create one manually in the migration file instead, and then update the value here to match.
  status EmailOutboxStatus @default(dbgenerated("\nCASE\n    WHEN \"isPaused\" THEN 'PAUSED'::\"EmailOutboxStatus\"\n    WHEN (\"skippedReason\" IS NOT NULL) THEN 'SKIPPED'::\"EmailOutboxStatus\"\n    WHEN (\"startedRenderingAt\" IS NULL) THEN 'PREPARING'::\"EmailOutboxStatus\"\n    WHEN (\"finishedRenderingAt\" IS NULL) THEN 'RENDERING'::\"EmailOutboxStatus\"\n    WHEN (\"renderErrorExternalMessage\" IS NOT NULL) THEN 'RENDER_ERROR'::\"EmailOutboxStatus\"\n    WHEN ((\"startedSendingAt\" IS NULL) AND (\"isQueued\" IS FALSE)) THEN 'SCHEDULED'::\"EmailOutboxStatus\"\n    WHEN (\"startedSendingAt\" IS NULL) THEN 'QUEUED'::\"EmailOutboxStatus\"\n    WHEN (\"finishedSendingAt\" IS NULL) THEN 'SENDING'::\"EmailOutboxStatus\"\n    WHEN ((\"canHaveDeliveryInfo\" IS TRUE) AND (\"deliveredAt\" IS NULL)) THEN 'SENDING'::\"EmailOutboxStatus\"\n    WHEN (\"sendServerErrorExternalMessage\" IS NOT NULL) THEN 'SERVER_ERROR'::\"EmailOutboxStatus\"\n    WHEN (\"canHaveDeliveryInfo\" IS FALSE) THEN 'SENT'::\"EmailOutboxStatus\"\n    WHEN (\"markedAsSpamAt\" IS NOT NULL) THEN 'MARKED_AS_SPAM'::\"EmailOutboxStatus\"\n    WHEN (\"clickedAt\" IS NOT NULL) THEN 'CLICKED'::\"EmailOutboxStatus\"\n    WHEN (\"openedAt\" IS NOT NULL) THEN 'OPENED'::\"EmailOutboxStatus\"\n    WHEN (\"bouncedAt\" IS NOT NULL) THEN 'BOUNCED'::\"EmailOutboxStatus\"\n    WHEN (\"deliveryDelayedAt\" IS NOT NULL) THEN 'DELIVERY_DELAYED'::\"EmailOutboxStatus\"\n    ELSE 'SENT'::\"EmailOutboxStatus\"\nEND"))

  // A simplified version of the status property.
  // In terms of the color mapping of `status`, white statuses have a `simpleStatus` of `in-progress`, red statuses have a `simpleStatus` of `error`, and everything else has a `simpleStatus` of `ok`.
  // 
  // This column is auto-generated as defined in the SQL migration. It can not be set manually. See the note above on EmailOutboxStatus.status for more details on dbgenerated values.
  simpleStatus EmailOutboxSimpleStatus @default(dbgenerated("\nCASE\n    WHEN (\"skippedReason\" IS NOT NULL) THEN 'OK'::\"EmailOutboxSimpleStatus\"\n    WHEN ((\"renderErrorExternalMessage\" IS NOT NULL) OR (\"sendServerErrorExternalMessage\" IS NOT NULL) OR (\"bouncedAt\" IS NOT NULL)) THEN 'ERROR'::\"EmailOutboxSimpleStatus\"\n    WHEN ((\"finishedSendingAt\" IS NOT NULL) AND ((\"canHaveDeliveryInfo\" IS FALSE) OR (\"deliveredAt\" IS NOT NULL))) THEN 'OK'::\"EmailOutboxSimpleStatus\"\n    WHEN ((\"finishedSendingAt\" IS NULL) OR ((\"canHaveDeliveryInfo\" IS TRUE) AND (\"deliveredAt\" IS NULL))) THEN 'IN_PROGRESS'::\"EmailOutboxSimpleStatus\"\n    ELSE 'OK'::\"EmailOutboxSimpleStatus\"\nEND"))

  // priority is the sending priority of the email among all emails that are not yet sent but already past their scheduled time. Higher priority means it should be sent sooner.
  //
  // This column is auto-generated based on the following formula:
  //  priority = (isHighPriority ? 100 : 0) + (renderedIsTransactional ? 10 : 0)
  // See the note above on EmailOutboxStatus.status for more details on dbgenerated values.
  priority Int @default(dbgenerated("(\nCASE\n    WHEN \"isHighPriority\" THEN 100\n    ELSE 0\nEND +\nCASE\n    WHEN \"renderedIsTransactional\" THEN 10\n    ELSE 0\nEND)"))

  isPaused Boolean @default(false)

  // either both or neither of [renderedByWorkerId, startedRenderingAt] must be set
  renderedByWorkerId  String?   @db.Uuid
  startedRenderingAt  DateTime?
  // if startedRenderingAt is not set, then finishedRenderingAt is also not set
  finishedRenderingAt DateTime?

  // if finishedRenderingAt is set, then exactly one of [renderedHtml, renderedText, renderedSubject, renderedIsTransactional, renderedNotificationCategoryId] or [renderErrorExternalMessage, renderErrorExternalDetails, renderErrorInternalMessage, renderErrorInternalDetails] must be set; if finishedRenderingAt is not set, then none of the aforementioned fields are set
  renderErrorExternalMessage     String?
  renderErrorExternalDetails     Json?
  renderErrorInternalMessage     String?
  renderErrorInternalDetails     Json?
  renderedHtml                   String?
  renderedText                   String?
  renderedSubject                String?
  renderedIsTransactional        Boolean?
  renderedNotificationCategoryId String?

  // The scheduled time of when the email should be added to the queue. Can be edited, but only if the email has not yet started sending. Doing so should set isQueued to false.
  scheduledAt DateTime

  // Whether the email has been queued for sending. Once queued, this stays true unless the email is retried or rescheduled.
  isQueued Boolean @default(false)

  // A generated column that is equal to scheduledAt if isQueued is false, otherwise null. See the note above on EmailOutboxStatus.status for more details on dbgenerated values.
  scheduledAtIfNotYetQueued DateTime? @default(dbgenerated("\nCASE\n    WHEN \"isQueued\" THEN NULL::timestamp without time zone\n    ELSE \"scheduledAt\"\nEND"))

  // if finishedRenderingAt is not set, then startedSendingAt is also not set
  startedSendingAt  DateTime?
  // if startedSendingAt is not set, then finishedSendingAt is also not set
  finishedSendingAt DateTime?

  // Deferred retry fields for email sending
  // Number of send retries attempted (starts at 0, incremented on each failure). Reset when email content is edited.
  sendRetries       Int       @default(0)
  // When to retry sending (null = not waiting for retry). Set when a retryable error occurs. Reset when email content is edited. Must be null if sendRetries is 0 (enforced by EmailOutbox_nextSendRetryAt_requires_failure). Must be null when finishedSendingAt is set (enforced by EmailOutbox_no_retry_after_finished).
  nextSendRetryAt   DateTime?
  // JSON array of errors from each failed send attempt. Each entry has: { attemptNumber, timestamp, externalMessage, externalDetails, internalMessage, internalDetails }. Reset when email content is edited. Must be null if sendRetries is 0 (enforced by EmailOutbox_sendAttemptErrors_requires_failure).
  sendAttemptErrors Json?

  // A generated column that is equal to finishedSendingAt if canHaveDeliveryInfo is false, otherwise deliveredAt.
  sentAt DateTime? @default(dbgenerated("\nCASE\n    WHEN (\"canHaveDeliveryInfo\" IS TRUE) THEN \"deliveredAt\"\n    WHEN (\"canHaveDeliveryInfo\" IS FALSE) THEN \"finishedSendingAt\"\n    ELSE NULL::timestamp without time zone\nEND"))

  // either all or none of [sendServerErrorExternalMessage, sendServerErrorExternalDetails, sendServerErrorInternalMessage, sendServerErrorInternalDetails] must be set. If finishedSendingAt is not set, then none of these are set.
  sendServerErrorExternalMessage String?
  sendServerErrorExternalDetails Json?
  sendServerErrorInternalMessage String?
  sendServerErrorInternalDetails Json?

  // The reason why the email was skipped. Unlike most status-related fields, this can be set at any point in the email lifecycle (like isPaused). skippedDetails must be set if and only if skippedReason is set. (Enforced by EmailOutbox_skipped_details_consistency_check constraint.)
  skippedReason  EmailOutboxSkippedReason?
  skippedDetails Json?

  // Whether this email was sent through a server that provides delivery info. This is set if and only if finishedSendingAt is set (it is only determined once the email has been sent). If canHaveDeliveryInfo is false, then [deliveredAt, deliveryDelayedAt, bouncedAt] are all not set. This flag is usually set to true if the email provider is Resend.
  canHaveDeliveryInfo Boolean?

  // at most one of [deliveredAt, deliveryDelayedAt, bouncedAt] can be set. If finishedSendingAt is not set, then none of these are set.
  deliveredAt       DateTime?
  deliveryDelayedAt DateTime?
  bouncedAt         DateTime?

  // if finishedSendingAt is not set, then openedAt is also not set
  openedAt       DateTime?
  // note: setting clickedAt should also set openedAt if it's not set yet
  clickedAt      DateTime?
  unsubscribedAt DateTime?
  markedAsSpamAt DateTime?

  sequenceId             BigInt? @unique
  shouldUpdateSequenceId Boolean @default(true)

  tenancy Tenancy @relation(fields: [tenancyId], references: [id], onDelete: Cascade)

  @@id([tenancyId, id])
  @@index([tenancyId, finishedSendingAt(sort: Desc), scheduledAtIfNotYetQueued(sort: Desc), priority, id], map: "EmailOutbox_ordering_idx")
  @@index([tenancyId, simpleStatus], map: "EmailOutbox_simple_status_tenancy_idx")
  @@index([tenancyId, status], map: "EmailOutbox_status_tenancy_idx")
  @@index([isQueued], map: "EmailOutbox_isQueued_idx")
  @@index([tenancyId, sequenceId], name: "EmailOutbox_tenancyId_sequenceId_idx")
  @@index([shouldUpdateSequenceId, tenancyId], name: "EmailOutbox_shouldUpdateSequenceId_idx")
}

model EmailOutboxProcessingMetadata {
  key String @id

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  lastExecutedAt DateTime?
}

model EmailDraft {
  tenancyId String @db.Uuid

  id String @default(uuid()) @db.Uuid

  displayName String
  themeMode   DraftThemeMode @default(PROJECT_DEFAULT)
  themeId     String?
  tsxSource   String
  sentAt      DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@id([tenancyId, id])
}

enum DraftThemeMode {
  PROJECT_DEFAULT
  NONE
  CUSTOM
}

model CliAuthAttempt {
  tenancyId String @db.Uuid

  id           String    @default(uuid()) @db.Uuid
  pollingCode      String    @unique
  loginCode        String    @unique
  refreshToken     String?
  anonRefreshToken String?
  expiresAt        DateTime
  usedAt           DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@id([tenancyId, id])
}

model UserNotificationPreference {
  id                     String @default(uuid()) @db.Uuid
  tenancyId              String @db.Uuid
  projectUserId          String @db.Uuid
  notificationCategoryId String @db.Uuid

  enabled     Boolean
  projectUser ProjectUser @relation(fields: [tenancyId, projectUserId], references: [tenancyId, projectUserId], onDelete: Cascade)

  sequenceId             BigInt?  @unique
  shouldUpdateSequenceId Boolean  @default(true)

  @@id([tenancyId, id])
  @@unique([tenancyId, projectUserId, notificationCategoryId])
  @@index([shouldUpdateSequenceId, tenancyId], name: "UserNotificationPreference_shouldUpdateSequenceId_idx")
  @@index([tenancyId, sequenceId], name: "UserNotificationPreference_tenancyId_sequenceId_idx")
}

model Conversation {
  id            String @default(uuid()) @db.Uuid
  tenancyId     String @db.Uuid
  projectUserId String? @db.Uuid
  teamId        String? @db.Uuid

  subject       String
  status        String
  priority      String
  source        String
  assignedToUserId      String?
  assignedToDisplayName String?
  tags                  Json?
  firstResponseDueAt    DateTime?
  firstResponseAt       DateTime?
  nextResponseDueAt     DateTime?
  lastCustomerReplyAt   DateTime?
  lastAgentReplyAt      DateTime?
  metadata              Json?
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt
  lastMessageAt DateTime @default(now())
  lastInboundAt DateTime?
  lastOutboundAt DateTime?
  closedAt      DateTime?

  tenancy     Tenancy                @relation(fields: [tenancyId], references: [id], onDelete: Cascade)
  messages    ConversationMessage[]
  entryPoints ConversationEntryPoint[]

  @@id([tenancyId, id])
  @@index([tenancyId, projectUserId, lastMessageAt(sort: Desc)], name: "Conversation_user_lastMessageAt_idx")
  @@index([tenancyId, status, lastMessageAt(sort: Desc)], name: "Conversation_status_lastMessageAt_idx")
  @@index([tenancyId, teamId, lastMessageAt(sort: Desc)], name: "Conversation_team_lastMessageAt_idx")
}

model ConversationEntryPoint {
  id             String @default(uuid()) @db.Uuid
  tenancyId      String @db.Uuid
  conversationId String @db.Uuid

  channelType       String
  adapterKey        String
  externalChannelId String?
  isEntryPoint      Boolean @default(false)
  metadata          Json?
  createdAt         DateTime @default(now())
  updatedAt         DateTime @updatedAt

  tenancy      Tenancy      @relation(fields: [tenancyId], references: [id], onDelete: Cascade)
  conversation Conversation @relation(fields: [tenancyId, conversationId], references: [tenancyId, id], onDelete: Cascade)
  messages     ConversationMessage[]

  @@id([tenancyId, id])
  @@index([tenancyId, conversationId, createdAt], name: "ConversationEntryPoint_conversation_createdAt_idx")
  @@index([tenancyId, channelType, adapterKey], name: "ConversationEntryPoint_type_adapter_idx")
}

model ConversationMessage {
  id        String @default(uuid()) @db.Uuid
  tenancyId String @db.Uuid
  conversationId String @db.Uuid
  channelId      String? @db.Uuid

  messageType        String
  senderType         String
  senderId           String?
  senderDisplayName  String?
  senderPrimaryEmail String?
  body               String?
  attachments        Json?
  metadata           Json?
  createdAt          DateTime @default(now())

  tenancy      Tenancy      @relation(fields: [tenancyId], references: [id], onDelete: Cascade)
  conversation Conversation @relation(fields: [tenancyId, conversationId], references: [tenancyId, id], onDelete: Cascade)
  channel      ConversationEntryPoint? @relation(fields: [tenancyId, channelId], references: [tenancyId, id], onDelete: NoAction)

  @@id([tenancyId, id])
  @@index([tenancyId, conversationId, createdAt], name: "ConversationMessage_conversation_createdAt_idx")
  @@index([tenancyId, channelId, createdAt], name: "ConversationMessage_channel_createdAt_idx")
}

model ThreadMessage {
  id        String @default(uuid()) @db.Uuid
  tenancyId String @db.Uuid
  threadId  String @db.Uuid

  content   Json
  createdAt DateTime @default(now())

  @@id([tenancyId, id])
}

model AiConversation {
  id            String   @id @default(uuid()) @db.Uuid
  projectUserId String   @db.Uuid
  projectId     String
  project       Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  title         String
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt
  messages      AiMessage[]

  @@index([projectUserId, projectId, updatedAt(sort: Desc)])
}

model AiMessage {
  id             String         @id @default(uuid()) @db.Uuid
  conversationId String         @db.Uuid
  position       Int
  role           String
  content        Json
  createdAt      DateTime       @default(now())
  conversation   AiConversation @relation(fields: [conversationId], references: [id], onDelete: Cascade)

  @@index([conversationId, position])
}

enum CustomerType {
  USER
  TEAM
  CUSTOM
}

enum SubscriptionStatus {
  active
  trialing
  canceled
  paused
  incomplete
  incomplete_expired
  past_due
  unpaid
}

enum PurchaseCreationSource {
  PURCHASE_PAGE
  TEST_MODE
  API_GRANT
}

model Subscription {
  id           String       @default(uuid()) @db.Uuid
  tenancyId    String       @db.Uuid
  customerId   String
  customerType CustomerType
  productId    String?
  priceId      String?
  product      Json
  quantity     Int          @default(1)

  stripeSubscriptionId   String?
  status                 SubscriptionStatus
  currentPeriodEnd       DateTime
  currentPeriodStart     DateTime
  cancelAtPeriodEnd      Boolean
  canceledAt             DateTime?
  endedAt                DateTime?

  refundedAt DateTime?

  // Set when a refund explicitly ends product access via end_action="now".
  // Distinct from `endedAt` (which fires for natural expiry, webhook cancel,
  // etc.) so phase-1's subscription-end mapper can tell refund-driven ends
  // apart and avoid emitting a second product-revocation entry — the refund
  // row already carries one. See refund/route.tsx and phase-1/transactions.ts
  // for the consumer side.
  productRevokedAt DateTime?

  creationSource PurchaseCreationSource
  createdAt      DateTime               @default(now())
  updatedAt      DateTime               @updatedAt

  SubscriptionInvoices SubscriptionInvoice[]

  @@id([tenancyId, id])
  @@unique([tenancyId, stripeSubscriptionId])
}

model ProductVersion {
  tenancyId        String   @db.Uuid
  productVersionId String
  productId        String?
  productJson      Json
  createdAt        DateTime @default(now())

  @@id([tenancyId, productVersionId])
}

model ItemQuantityChange {
  id           String       @default(uuid()) @db.Uuid
  tenancyId    String       @db.Uuid
  customerId   String
  customerType CustomerType
  itemId       String
  quantity     Int
  description  String?
  expiresAt    DateTime?
  createdAt    DateTime     @default(now())

  @@id([tenancyId, id])
  @@index([tenancyId, customerId, expiresAt])
}

model OneTimePurchase {
  id                    String                 @default(uuid()) @db.Uuid
  tenancyId             String                 @db.Uuid
  customerId            String
  customerType          CustomerType
  productId             String?
  priceId               String?
  product               Json
  quantity              Int
  stripePaymentIntentId String?
  createdAt             DateTime               @default(now())
  revokedAt             DateTime?
  refundedAt            DateTime?
  creationSource        PurchaseCreationSource

  @@id([tenancyId, id])
  @@unique([tenancyId, stripePaymentIntentId])
}

model DataVaultEntry {
  id        String   @default(uuid()) @db.Uuid
  tenancyId String   @db.Uuid
  storeId   String
  hashedKey String
  encrypted Json // Contains { edkBase64, ciphertextBase64 } from encryptWithKms()
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@id([tenancyId, id])
  @@unique([tenancyId, storeId, hashedKey])
  @@index([tenancyId, storeId])
}

model CacheEntry {
  id        String   @id @default(uuid()) @db.Uuid
  namespace String
  cacheKey  String
  payload   Json
  expiresAt DateTime
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  @@unique([namespace, cacheKey])
}

model SubscriptionInvoice {
  id                            String  @default(uuid()) @db.Uuid
  tenancyId                     String  @db.Uuid
  stripeSubscriptionId          String
  stripeInvoiceId               String
  isSubscriptionCreationInvoice Boolean
  status                        String?
  amountTotal                   Int?
  hostedInvoiceUrl              String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  subscription Subscription @relation(fields: [tenancyId, stripeSubscriptionId], references: [tenancyId, stripeSubscriptionId])

  @@id([tenancyId, id])
  @@unique([tenancyId, stripeInvoiceId])
}

model OutgoingRequest {
  id String @id @default(uuid()) @db.Uuid

  createdAt DateTime @default(now())

  qstashOptions       Json
  startedFulfillingAt DateTime?
  deduplicationKey    String?

  // Partial unique index on deduplicationKey WHERE startedFulfillingAt IS NULL
  // is created in a custom migration (not expressible in Prisma schema)
  @@index([startedFulfillingAt, createdAt])
  @@index([startedFulfillingAt, deduplicationKey])
}

// BulldozerStorageEngine is managed externally (see prisma.config.ts
// `tables.external`). It's created by migrations and interacted with
// via raw SQL — not through the Prisma client. Keeping it out of the
// Prisma schema avoids drift warnings for features Prisma can't represent
// (generated columns, self-referential FKs on jsonb[] columns, etc.).

model BulldozerTimeFoldQueue {
  id               String   @id @default(dbgenerated("gen_random_uuid()")) @db.Uuid
  tableStoragePath Json[]
  groupKey         Json
  rowIdentifier    String
  scheduledAt      DateTime @db.Timestamptz
  stateAfter       Json
  rowData          Json
  reducerSql       String
  createdAt        DateTime @default(now())
  updatedAt        DateTime @default(now()) @updatedAt

  @@unique([tableStoragePath, groupKey, rowIdentifier], map: "BulldozerTimeFoldQueue_table_group_row_key")
  @@index([scheduledAt], map: "BulldozerTimeFoldQueue_scheduledAt_idx")
}

model BulldozerTimeFoldMetadata {
  key             String   @id
  createdAt       DateTime @default(now())
  updatedAt       DateTime @default(now()) @updatedAt
  lastProcessedAt DateTime @db.Timestamptz
}

// BulldozerTimeFoldDownstreamCascade is managed externally (see
// prisma.config.ts `tables.external`). Same reason as BulldozerStorageEngine
// above: its primary key is on a JSONB[] column, which Prisma's @id
// attribute rejects ("fields that are marked as id must be required"; list
// types are treated as non-required). It's written only by bulldozer
// internals (declareTimeFoldTable.init()/.delete()) and read by
// public.bulldozer_timefold_process_queue() — never via the Prisma client.

model DeletedRow {
  id        String @id @default(uuid()) @db.Uuid
  tenancyId String @db.Uuid
  tableName String

  sequenceId             BigInt? @unique
  shouldUpdateSequenceId Boolean @default(true)

  primaryKey Json
  data       Json?

  deletedAt           DateTime  @default(now())
  startedFulfillingAt DateTime?

  @@index([tableName])
  @@index([tenancyId])
  // composite index for efficient querying of deleted rows by tenant and table, ordered by sequence
  @@index([tenancyId, tableName, sequenceId])
  @@index([shouldUpdateSequenceId, tenancyId], name: "DeletedRow_shouldUpdateSequenceId_idx")
}