Persistent Database

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider     = "mysql"
  url          = env("DATABASE_URL")
  relationMode = "prisma"
}

enum RecordType {
  IMAGE
  TEXT
}

enum Role {
  ADMIN
  USER
}

enum Theme {
  DARK
  LIGHT
}

enum Platform {
  UNKNOWN

  CLI
  WEB
  DESKTOP
  WINDOWS
  MACOS
  LINUX
  CHROMEOS

  MOBILE
  ANDROID
  IOS
  HARMONYOS
}

model Record {
  id            Int
  createdAt     DateTime   @default(now())
  // Adding userId to record violates is not the best schema design but simplifies the code a lot, and could potentially reduce runtime
  // This could be potentially removed later after testing performance
  user          User       @relation(fields: [userId], references: [id])
  userId        Int
  device        Device     @relation(fields: [deviceId, deviceUserId], references: [id, userId], onDelete: Cascade, onUpdate: Cascade)
  deviceId      Int
  deviceUserId  Int
  profile       Profile    @relation(fields: [profileId, profileUserId], references: [id, userId], onDelete: Cascade, onUpdate: Cascade)
  profileId     Int
  profileUserId Int
  uuid          String
  type          RecordType @default(TEXT)
  // value         String     @db.Text // 64 KiB, should be enough, MediumText: 16MB, LongText: 4GB
  value         Bytes      @db.LongBlob // 4GB
  expired       Boolean    @default(false)
  deleted       Boolean    @default(false)
  deletedAt     DateTime   @default(now())
  size          Int
  globalId      BigInt     @default(autoincrement()) // global count

  @@id([id, userId])
  @@unique([userId, uuid], name: "UniquePUUIDWithinUser")
  @@index([globalId])
  @@index([deviceId, deviceUserId])
  @@index([profileId, profileUserId])
}

model Profile {
  id          Int
  profileName String
  createdAt   DateTime @default(now())
  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  userId      Int
  preferences Json     @default("{}")
  Record      Record[]
  globalId    BigInt   @default(autoincrement()) // global count

  @@id([id, userId])
  @@unique([userId, profileName], name: "UniqueProfileNameWithinUser")
  @@index([globalId])
}

model Device {
  id          Int
  createdAt   DateTime @default(now())
  user        User     @relation(fields: [userId], references: [id], onDelete: Cascade, onUpdate: Cascade)
  userId      Int
  preferences Json     @default("{}")
  deviceName  String   @default("unknown")
  platform    Platform @default(UNKNOWN)
  Record      Record[]
  globalId    BigInt   @default(autoincrement()) // global count

  @@id([id, userId])
  @@unique([userId, deviceName], name: "UniqueDeviceNameWithinUser")
  @@unique([userId, id], name: "UniqueDeviceIdWithinUser")
  @@index([globalId])
}

model User {
  id              Int       @id @default(autoincrement())
  activated       Boolean   @default(false)
  createdAt       DateTime  @default(now())
  username        String    @unique
  email           String    @unique
  lastRecordId    Int       @default(0)
  password        String
  recordCount     Int       @default(0)
  recordQuota     Int       @default(5)
  connectionQuota Int       @default(2)
  role            Role      @default(USER)
  preferences     Json      @default("{}")
  tokenVersion    String    @default("0")
  defaultTheme    Theme     @default(DARK)
  profiles        Profile[]
  devices         Device[]
  records         Record[]
}