studio-auth-be/prisma/schema.prisma

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

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

enum UserRole {
  ADMIN
  USER
}

enum OrganizationType {
  COMPANY
  SCHOOL
  GOVERNMENT
  LAB
  OTHER
}

enum LicenseCode {
  FREE
  EDU
  PRO
}

model User {
  id                    String             @id @default(uuid()) @db.Uuid
  email                 String             @unique
  passwordHash          String             @map("password_hash")
  name                  String?
  role                  UserRole           @default(USER)
  isActive              Boolean            @default(true) @map("is_active")
  lastLoginAt           DateTime?          @map("last_login_at") @db.Timestamptz(3)
  profileImageData      Bytes?             @map("profile_image_data")
  profileImageMime      String?            @map("profile_image_mime")
  profileImageUpdatedAt DateTime?          @map("profile_image_updated_at") @db.Timestamptz(3)
  createdAt             DateTime           @default(now()) @map("created_at") @db.Timestamptz(3)
  updatedAt             DateTime           @updatedAt @map("updated_at") @db.Timestamptz(3)
  refreshTokens         RefreshToken[]
  affiliations          UserAffiliation[]
  userLicenses          UserLicense[]
  appRoles              UserAppRole[]
  oauthAccounts         UserOauthAccount[]

  @@map("users")
}

model UserOauthAccount {
  id             String   @id @default(uuid()) @db.Uuid
  userId         String   @map("user_id") @db.Uuid
  provider       String
  providerUserId String   @map("provider_user_id")
  email          String?
  createdAt      DateTime @default(now()) @map("created_at") @db.Timestamptz(3)
  updatedAt      DateTime @updatedAt @map("updated_at") @db.Timestamptz(3)
  user           User     @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([provider, providerUserId])
  @@index([userId])
  @@map("user_oauth_accounts")
}

model UserAppRole {
  id           String    @id @default(uuid()) @db.Uuid
  userId       String    @map("user_id") @db.Uuid
  appKey       String    @map("app_key")
  roleCode     String    @map("role_code")
  assignedById String?   @map("assigned_by") @db.Uuid
  assignedAt   DateTime  @default(now()) @map("assigned_at") @db.Timestamptz(3)
  startsAt     DateTime? @map("starts_at") @db.Timestamptz(3)
  endsAt       DateTime? @map("ends_at") @db.Timestamptz(3)
  createdAt    DateTime  @default(now()) @map("created_at") @db.Timestamptz(3)
  updatedAt    DateTime  @updatedAt @map("updated_at") @db.Timestamptz(3)
  user         User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([userId, appKey, roleCode])
  @@index([appKey, roleCode])
  @@index([userId, appKey])
  @@map("user_app_roles")
}

model License {
  id                  String        @id @default(uuid()) @db.Uuid
  code                LicenseCode   @unique
  name                String
  description         String?
  defaultDurationDays Int?          @default(365) @map("default_duration_days")
  isActive            Boolean       @default(true) @map("is_active")
  createdAt           DateTime      @default(now()) @map("created_at") @db.Timestamptz(3)
  updatedAt           DateTime      @updatedAt @map("updated_at") @db.Timestamptz(3)
  userLicenses        UserLicense[]
  appPermissions      LicenseAppPermission[]

  @@index([isActive])
  @@map("licenses")
}

model LicenseAppPermission {
  id        String   @id @default(uuid()) @db.Uuid
  licenseId String   @map("license_id") @db.Uuid
  appKey    String   @map("app_key")
  canLaunch Boolean  @default(true) @map("can_launch")
  createdAt DateTime @default(now()) @map("created_at") @db.Timestamptz(3)
  updatedAt DateTime @updatedAt @map("updated_at") @db.Timestamptz(3)
  license   License  @relation(fields: [licenseId], references: [id], onDelete: Cascade)

  @@unique([licenseId, appKey])
  @@index([appKey])
  @@map("license_app_permissions")
}

model UserLicense {
  id           String    @id @default(uuid()) @db.Uuid
  userId       String    @map("user_id") @db.Uuid
  licenseId    String    @map("license_id") @db.Uuid
  startsAt     DateTime  @default(now()) @map("starts_at") @db.Timestamptz(3)
  expiresAt    DateTime? @map("expires_at") @db.Timestamptz(3)
  assignedById String?   @map("assigned_by") @db.Uuid
  createdAt    DateTime  @default(now()) @map("created_at") @db.Timestamptz(3)
  user         User      @relation(fields: [userId], references: [id], onDelete: Cascade)
  license      License   @relation(fields: [licenseId], references: [id], onDelete: Restrict)

  @@index([userId, startsAt])
  @@index([licenseId])
  @@map("user_licenses")
}

model Organization {
  id                   String            @id @default(uuid()) @db.Uuid
  name                 String
  type                 OrganizationType  @default(OTHER)
  domain               String?
  defaultLicenseCode   String?           @map("default_license_code")
  instructorInviteCode String            @unique @map("instructor_invite_code")
  isActive             Boolean           @default(true) @map("is_active")
  createdAt            DateTime          @default(now()) @map("created_at") @db.Timestamptz(3)
  updatedAt            DateTime          @updatedAt @map("updated_at") @db.Timestamptz(3)
  affiliations         UserAffiliation[]

  @@unique([name, type])
  @@index([isActive, type])
  @@map("organizations")
}

model UserAffiliation {
  id             String       @id @default(uuid()) @db.Uuid
  userId         String       @map("user_id") @db.Uuid
  organizationId String       @map("organization_id") @db.Uuid
  department     String?
  position       String?
  isPrimary      Boolean      @default(false) @map("is_primary")
  startsAt       DateTime?    @map("starts_at") @db.Timestamptz(3)
  endsAt         DateTime?    @map("ends_at") @db.Timestamptz(3)
  createdAt      DateTime     @default(now()) @map("created_at") @db.Timestamptz(3)
  updatedAt      DateTime     @updatedAt @map("updated_at") @db.Timestamptz(3)
  user           User         @relation(fields: [userId], references: [id], onDelete: Cascade)
  organization   Organization @relation(fields: [organizationId], references: [id], onDelete: Restrict)

  @@index([userId, isPrimary])
  @@index([organizationId])
  @@map("user_affiliations")
}

model RefreshToken {
  id        String    @id @default(uuid()) @db.Uuid
  userId    String    @map("user_id") @db.Uuid
  tokenHash String    @unique @map("token_hash")
  expiresAt DateTime  @map("expires_at") @db.Timestamptz(3)
  revokedAt DateTime? @map("revoked_at") @db.Timestamptz(3)
  createdAt DateTime  @default(now()) @map("created_at") @db.Timestamptz(3)
  user      User      @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@index([userId])
  @@map("refresh_tokens")
}