vidreview/packages/api/prisma/schema.prisma

// This is your Prisma schema file,
// learn more about it in the docs: https://pris.ly/d/prisma-schema

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

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model User {
  id            String    @id @default(cuid())
  email         String    @unique
  name          String
  password      String
  avatarUrl     String?
  globalRole    GlobalRole @default(MEMBER)
  active        Boolean   @default(true)
  storageQuota  BigInt     @default(524288000) // 500 MB in bytes
  storageUsed   BigInt     @default(0)         // bytes consumed
  createdAt     DateTime  @default(now())
  updatedAt     DateTime  @updatedAt

  memberships        ProjectMember[]    @relation("ProjectMembers")
  comments           Comment[]
  projects           Project[]          @relation("ProjectOwner")
  resolvedComments   Comment[]         @relation("ResolvedBy")
  requestedComments  Comment[]         @relation("RequestedBy")
  assets             Asset[]
  shareLinks         AssetShareLink[]
}

model Project {
  id          String   @id @default(cuid())
  name        String
  description String?
  ownerId     String
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  assets      Asset[]
  members     ProjectMember[] @relation("ProjectMembers")
  invitations Invitation[]
  owner       User     @relation("ProjectOwner", fields: [ownerId], references: [id])
  folders     Folder[]
}

model SiteSetting {
  id    String @id @default(cuid())
  name  String @unique
  value String
}

model ProjectMember {
  id        String   @id @default(cuid())
  userId    String
  projectId String
  role      Role     @default(REVIEWER)
  joinedAt  DateTime @default(now())
  invitedBy String?    // userId who sent the invite

  user    User    @relation("ProjectMembers", fields: [userId], references: [id], onDelete: Cascade)
  project Project @relation("ProjectMembers", fields: [projectId], references: [id], onDelete: Cascade)

  @@unique([userId, projectId])
  @@index([projectId])
  @@index([userId])
}

model Asset {
  id              String          @id @default(cuid())
  projectId       String
  uploaderId      String?         // null for legacy assets before this feature
  title           String
  filename        String
  originalFilename String?
  filePath        String
  thumbnail       String?
  hlsPath         String?
  originalFilePath String?
  maxResolution    String?
  duration        Float?
  fps             Float           @default(30)
  codec           String?
  mimeType        String
  fileSize        BigInt           @default(0)   // raw video file size in bytes
  bitrate         BigInt           @default(0)   // video bitrate in bits/s
  status          AssetStatus     @default(PENDING_REVIEW)
  transcodeStatus TranscodeStatus @default(PENDING)
  transcodeProgress Int            @default(0)
  transcodeError  String?
  transcodePaused Boolean         @default(false)
  createdAt       DateTime        @default(now())
  updatedAt       DateTime        @updatedAt

  project  Project  @relation(fields: [projectId], references: [id], onDelete: Cascade)
  uploader User?    @relation(fields: [uploaderId], references: [id], onDelete: SetNull)
  comments Comment[]
  shareLinks AssetShareLink[]
  folderAssets FolderAsset[]

  @@index([transcodeStatus, transcodePaused]) // claimOneJob() hot path
  @@index([transcodeStatus, updatedAt])      // resetStuckJobs() watchdog
}

model Comment {
  id            String         @id @default(cuid())
  assetId      String
  userId       String?
  guestName    String?
  content      String
  timestamp    Float?
  annotations  Json?
  resolved     Boolean        @default(false)
  resolveStatus ResolveStatus @default(UNRESOLVED)
  resolvedById String?
  resolvedByAt DateTime?
  requestedById String?
  requestedByAt DateTime?
  parentId     String?
  deleted      Boolean        @default(false)
  deletedAt    DateTime?
  deletedById  String?
  createdAt    DateTime       @default(now())
  updatedAt    DateTime       @updatedAt

  asset       Asset     @relation(fields: [assetId], references: [id], onDelete: Cascade)
  user        User?      @relation(fields: [userId], references: [id], onDelete: Cascade)
  parent      Comment?  @relation("Replies", fields: [parentId], references: [id], onDelete: Cascade)
  replies     Comment[] @relation("Replies")
  resolvedBy  User?     @relation("ResolvedBy", fields: [resolvedById], references: [id])
  requestedBy User?    @relation("RequestedBy", fields: [requestedById], references: [id])
}

enum Role {
  ADMIN
  EDITOR
  REVIEWER
  VIEWER
}

enum GlobalRole {
  ADMIN        // system-wide admin: manage users, all projects, quotas
  MEMBER       // registered user: create own projects, invite members
  PROJECT_USER // invited user: no project creation, workspace visibility only via invite
}

enum InvitationStatus {
  PENDING
  ACCEPTED
  EXPIRED
  REVOKED
}

model Invitation {
  id         String           @id @default(cuid())
  email     String            // invitee email
  projectId String?           // null = workspace invite (creates MEMBER); set = project invite (creates PROJECT_USER)
  type      InvitationType   @default(PROJECT)
  role      Role              @default(REVIEWER)
  token     String            @unique
  status    InvitationStatus  @default(PENDING)
  invitedBy String?           // userId who sent the invite
  expiresAt DateTime
  createdAt DateTime         @default(now())

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

  @@index([projectId])
  @@index([email])
  @@index([token])
}

enum InvitationType {
  WORKSPACE  // admin invites MEMBER — no project attached, user registers as MEMBER
  PROJECT   // admin/project member invites PROJECT_USER — attached to a project
}

enum AssetStatus {
  PENDING_REVIEW
  CHANGES_REQUESTED
  APPROVED
  REJECTED
}

enum ResolveStatus {
  UNRESOLVED       // no request made
  PENDING_APPROVAL // someone requested resolve, awaiting approval
  RESOLVED         // approved and closed
}

enum TranscodeStatus {
  PENDING
  UPLOADING
  PROCESSING
  COMPLETED
  FAILED
  UNSUPPORTED_CODEC
}

// ── Public share links ────────────────────────────────────────────────────────

model AssetShareLink {
  id             String   @id @default(cuid())
  assetId       String
  token         String   @unique
  password      String?  // bcrypt hash, null = no password required
  allowDownload                Boolean  @default(false)
  allowUnregisteredComments   Boolean  @default(false)
  maxViews                    Int      @default(20)  // 0 or -1 = unlimited
  viewCount     Int      @default(0)
  createdById  String
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt

  asset     Asset @relation(fields: [assetId], references: [id], onDelete: Cascade)
  createdBy User  @relation(fields: [createdById], references: [id])

  @@index([assetId])
  @@index([token])
}

// ── Folder system ──────────────────────────────────────────────────────────────

model Folder {
  id        String   @id @default(cuid())
  name      String
  projectId String
  parentId  String?
  order     Int      @default(0)
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  project  Project        @relation(fields: [projectId], references: [id], onDelete: Cascade)
  parent   Folder?        @relation("FolderTree", fields: [parentId], references: [id], onDelete: Cascade)
  children Folder[]       @relation("FolderTree")
  assets   FolderAsset[]

  @@index([projectId])
  @@index([parentId])
}

model FolderAsset {
  id       String   @id @default(cuid())
  folderId String
  assetId  String
  addedAt  DateTime @default(now())

  folder Folder @relation(fields: [folderId], references: [id], onDelete: Cascade)
  asset  Asset  @relation(fields: [assetId], references: [id], onDelete: Cascade)

  @@unique([folderId, assetId])
}