Kanban/prisma/schema.prisma

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

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

// ─── Multi-Tenancy ────────────────────────────────────────────

model Tenant {
  id        String   @id @default(uuid()) @db.Uuid
  name      String
  slug      String   @unique
  logoUrl   String?  @map("logo_url")
  logoText  String?  @map("logo_text")
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  hostnames      TenantHostname[]
  users          User[]
  boards         Board[]
  tags           Tag[]
  categories     Category[]
  boardTemplates BoardTemplate[]
  theme          TenantTheme?
  invites        TenantInvite[]
  webhooks       Webhook[]
  apiKeys        ApiKey[]

  @@map("tenants")
}

model TenantHostname {
  id        String   @id @default(uuid()) @db.Uuid
  hostname  String   @unique
  tenantId  String   @map("tenant_id") @db.Uuid
  isPrimary Boolean  @default(false) @map("is_primary")
  createdAt DateTime @default(now()) @map("created_at")

  tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@map("tenant_hostnames")
}

// ─── Users & Auth ─────────────────────────────────────────────

enum GlobalRole {
  SITE_ADMIN
  USER
}

enum TenantRole {
  ADMIN
  MEMBER
}

model User {
  id           String     @id @default(uuid()) @db.Uuid
  tenantId     String     @map("tenant_id") @db.Uuid
  email        String
  name         String
  passwordHash String     @map("password_hash")
  globalRole   GlobalRole @default(USER) @map("global_role")
  tenantRole   TenantRole @default(MEMBER) @map("tenant_role")
  avatarUrl    String?    @map("avatar_url")
  createdAt    DateTime   @default(now()) @map("created_at")
  updatedAt    DateTime   @updatedAt @map("updated_at")

  tenant       Tenant         @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  sessions     Session[]
  boardMembers BoardMember[]
  cardAssignees CardAssignee[]
  comments     Comment[]
  activities   Activity[]
  notifications Notification[]
  cardWatchers  CardWatcher[]
  apiKeys      ApiKey[]

  @@unique([tenantId, email])
  @@map("users")
}

model Session {
  id        String   @id @default(uuid()) @db.Uuid
  userId    String   @map("user_id") @db.Uuid
  expiresAt DateTime @map("expires_at")
  createdAt DateTime @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("sessions")
}

// ─── Boards ───────────────────────────────────────────────────

enum BoardVisibility {
  PUBLIC
  PRIVATE
}

enum BoardMemberRole {
  OWNER
  EDITOR
  VIEWER
}

model Board {
  id          String          @id @default(uuid()) @db.Uuid
  tenantId    String          @map("tenant_id") @db.Uuid
  title       String
  description String?
  visibility  BoardVisibility @default(PRIVATE)
  background  String?
  categoryId  String?         @map("category_id") @db.Uuid
  archived    Boolean         @default(false)
  cardPrefix  String?         @map("card_prefix")
  nextCardNum Int             @default(1) @map("next_card_num")
  createdAt   DateTime        @default(now()) @map("created_at")
  updatedAt   DateTime        @updatedAt @map("updated_at")

  tenant          Tenant           @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  category        Category?        @relation(fields: [categoryId], references: [id], onDelete: SetNull)
  columns         Column[]
  members         BoardMember[]
  activities      Activity[]
  customFieldDefs CustomFieldDef[]
  automationRules AutomationRule[]

  @@map("boards")
}

model BoardMember {
  id      String          @id @default(uuid()) @db.Uuid
  boardId String          @map("board_id") @db.Uuid
  userId  String          @map("user_id") @db.Uuid
  role    BoardMemberRole @default(EDITOR)

  board Board @relation(fields: [boardId], references: [id], onDelete: Cascade)
  user  User  @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([boardId, userId])
  @@map("board_members")
}

// ─── Columns ──────────────────────────────────────────────────

model Column {
  id       String @id @default(uuid()) @db.Uuid
  boardId  String @map("board_id") @db.Uuid
  title    String
  position String // Fractional index (lexicographic)

  board Board  @relation(fields: [boardId], references: [id], onDelete: Cascade)
  cards Card[]

  @@map("columns")
}

// ─── Cards ────────────────────────────────────────────────────

model Card {
  id              String    @id @default(uuid()) @db.Uuid
  columnId        String    @map("column_id") @db.Uuid
  title           String
  description     String?
  position        String    // Fractional index (lexicographic)
  dueDate         DateTime? @map("due_date")
  startDate       DateTime? @map("start_date")
  archived        Boolean   @default(false)
  dueReminderSent Boolean   @default(false) @map("due_reminder_sent")
  cardNumber      Int?      @map("card_number")
  createdAt       DateTime  @default(now()) @map("created_at")
  updatedAt       DateTime  @updatedAt @map("updated_at")

  column           Column             @relation(fields: [columnId], references: [id], onDelete: Cascade)
  assignees        CardAssignee[]
  labels           CardLabel[]
  checklists       Checklist[]
  comments         Comment[]
  attachments      Attachment[]
  activities       Activity[]
  watchers         CardWatcher[]
  customFieldValues CustomFieldValue[]
  blocks           CardDependency[] @relation("CardBlocks")
  blockedBy        CardDependency[] @relation("CardBlockedBy")

  @@map("cards")
}

model CardAssignee {
  id     String @id @default(uuid()) @db.Uuid
  cardId String @map("card_id") @db.Uuid
  userId String @map("user_id") @db.Uuid

  card Card @relation(fields: [cardId], references: [id], onDelete: Cascade)
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([cardId, userId])
  @@map("card_assignees")
}

// ─── Tags & Labels ────────────────────────────────────────────

model Tag {
  id       String @id @default(uuid()) @db.Uuid
  tenantId String @map("tenant_id") @db.Uuid
  name     String
  color    String // hex color

  tenant Tenant      @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  cards  CardLabel[]

  @@unique([tenantId, name])
  @@map("tags")
}

model CardLabel {
  id    String @id @default(uuid()) @db.Uuid
  cardId String @map("card_id") @db.Uuid
  tagId  String @map("tag_id") @db.Uuid

  card Card @relation(fields: [cardId], references: [id], onDelete: Cascade)
  tag  Tag  @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@unique([cardId, tagId])
  @@map("card_labels")
}

// ─── Categories ───────────────────────────────────────────────

model Category {
  id       String @id @default(uuid()) @db.Uuid
  tenantId String @map("tenant_id") @db.Uuid
  name     String

  tenant Tenant  @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  boards Board[]

  @@unique([tenantId, name])
  @@map("categories")
}

// ─── Checklists ───────────────────────────────────────────────

model Checklist {
  id       String @id @default(uuid()) @db.Uuid
  cardId   String @map("card_id") @db.Uuid
  title    String
  position String

  card  Card            @relation(fields: [cardId], references: [id], onDelete: Cascade)
  items ChecklistItem[]

  @@map("checklists")
}

model ChecklistItem {
  id          String  @id @default(uuid()) @db.Uuid
  checklistId String  @map("checklist_id") @db.Uuid
  title       String
  completed   Boolean @default(false)
  position    String

  checklist Checklist @relation(fields: [checklistId], references: [id], onDelete: Cascade)

  @@map("checklist_items")
}

// ─── Comments ─────────────────────────────────────────────────

model Comment {
  id        String   @id @default(uuid()) @db.Uuid
  cardId    String   @map("card_id") @db.Uuid
  userId    String   @map("user_id") @db.Uuid
  content   String
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  card Card @relation(fields: [cardId], references: [id], onDelete: Cascade)
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("comments")
}

// ─── Attachments ──────────────────────────────────────────────

model Attachment {
  id        String   @id @default(uuid()) @db.Uuid
  cardId    String   @map("card_id") @db.Uuid
  filename  String
  filepath  String
  mimetype  String
  size      Int
  createdAt DateTime @default(now()) @map("created_at")

  card Card @relation(fields: [cardId], references: [id], onDelete: Cascade)

  @@map("attachments")
}

// ─── Activity Log ─────────────────────────────────────────────

model Activity {
  id        String   @id @default(uuid()) @db.Uuid
  boardId   String   @map("board_id") @db.Uuid
  cardId    String?  @map("card_id") @db.Uuid
  userId    String   @map("user_id") @db.Uuid
  action    String   // e.g. "card.created", "card.moved", "comment.added"
  metadata  Json?    // additional context
  createdAt DateTime @default(now()) @map("created_at")

  board Board  @relation(fields: [boardId], references: [id], onDelete: Cascade)
  card  Card?  @relation(fields: [cardId], references: [id], onDelete: SetNull)
  user  User   @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("activities")
}

// ─── Notifications ────────────────────────────────────────────

model Notification {
  id        String   @id @default(uuid()) @db.Uuid
  userId    String   @map("user_id") @db.Uuid
  type      String   // e.g. "assigned", "mentioned", "due_soon"
  title     String
  body      String?
  link      String?
  read      Boolean  @default(false)
  createdAt DateTime @default(now()) @map("created_at")

  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("notifications")
}

// ─── Board Templates ──────────────────────────────────────────

model BoardTemplate {
  id       String @id @default(uuid()) @db.Uuid
  tenantId String? @map("tenant_id") @db.Uuid // null = system template
  name     String
  description String?
  template Json   // JSON structure with columns/cards

  tenant Tenant? @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@map("board_templates")
}

// ─── Card Watchers ───────────────────────────────────────────

model CardWatcher {
  id     String @id @default(uuid()) @db.Uuid
  cardId String @map("card_id") @db.Uuid
  userId String @map("user_id") @db.Uuid

  card Card @relation(fields: [cardId], references: [id], onDelete: Cascade)
  user User @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([cardId, userId])
  @@map("card_watchers")
}

// ─── Tenant Invites ──────────────────────────────────────────

model TenantInvite {
  id        String    @id @default(uuid()) @db.Uuid
  tenantId  String    @map("tenant_id") @db.Uuid
  code      String    @unique
  role      TenantRole @default(MEMBER)
  expiresAt DateTime  @map("expires_at")
  createdBy String    @map("created_by") @db.Uuid
  usedCount Int       @default(0) @map("used_count")
  maxUses   Int?      @map("max_uses")

  tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@map("tenant_invites")
}

// ─── Webhooks ────────────────────────────────────────────────

model Webhook {
  id       String   @id @default(uuid()) @db.Uuid
  tenantId String   @map("tenant_id") @db.Uuid
  url      String
  events   String[]
  secret   String
  active   Boolean  @default(true)

  tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@map("webhooks")
}

// ─── Custom Fields ───────────────────────────────────────────

enum CustomFieldType {
  TEXT
  NUMBER
  DATE
  SELECT
  MULTI_SELECT
}

model CustomFieldDef {
  id       String          @id @default(uuid()) @db.Uuid
  boardId  String          @map("board_id") @db.Uuid
  name     String
  type     CustomFieldType
  options  Json?           // For SELECT/MULTI_SELECT: string[]
  position String

  board  Board              @relation(fields: [boardId], references: [id], onDelete: Cascade)
  values CustomFieldValue[]

  @@map("custom_field_defs")
}

model CustomFieldValue {
  id      String @id @default(uuid()) @db.Uuid
  cardId  String @map("card_id") @db.Uuid
  fieldId String @map("field_id") @db.Uuid
  value   String

  card  Card           @relation(fields: [cardId], references: [id], onDelete: Cascade)
  field CustomFieldDef @relation(fields: [fieldId], references: [id], onDelete: Cascade)

  @@unique([cardId, fieldId])
  @@map("custom_field_values")
}

// ─── Automation Rules ────────────────────────────────────────

enum AutomationTrigger {
  CARD_MOVED
  DUE_PASSED
  LABEL_ADDED
  CARD_CREATED
}

enum AutomationAction {
  ADD_LABEL
  REMOVE_LABEL
  ASSIGN
  SET_DUE
  MOVE_TO_COLUMN
}

model AutomationRule {
  id            String            @id @default(uuid()) @db.Uuid
  boardId       String            @map("board_id") @db.Uuid
  trigger       AutomationTrigger
  triggerConfig Json?             @map("trigger_config")
  action        AutomationAction
  actionConfig  Json?             @map("action_config")
  active        Boolean           @default(true)

  board Board @relation(fields: [boardId], references: [id], onDelete: Cascade)

  @@map("automation_rules")
}

// ─── Card Dependencies ──────────────────────────────────────

model CardDependency {
  id             String   @id @default(uuid()) @db.Uuid
  blockingCardId String   @map("blocking_card_id") @db.Uuid
  blockedCardId  String   @map("blocked_card_id") @db.Uuid
  createdAt      DateTime @default(now()) @map("created_at")

  blockingCard Card @relation("CardBlocks", fields: [blockingCardId], references: [id], onDelete: Cascade)
  blockedCard  Card @relation("CardBlockedBy", fields: [blockedCardId], references: [id], onDelete: Cascade)

  @@unique([blockingCardId, blockedCardId])
  @@map("card_dependencies")
}

// ─── API Keys ────────────────────────────────────────────────

model ApiKey {
  id         String    @id @default(uuid()) @db.Uuid
  tenantId   String    @map("tenant_id") @db.Uuid
  userId     String    @map("user_id") @db.Uuid
  name       String
  keyHash    String    @unique @map("key_hash")
  keyPrefix  String    @map("key_prefix") // first 8 chars for display
  lastUsedAt DateTime? @map("last_used_at")
  createdAt  DateTime  @default(now()) @map("created_at")

  tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  user   User   @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@map("api_keys")
}

// ─── Tenant Branding & Theming ───────────────────────────────

model TenantTheme {
  id       String @id @default(uuid()) @db.Uuid
  tenantId String @unique @map("tenant_id") @db.Uuid

  // Light mode
  lightPageBg        String? @map("light_page_bg")
  lightPageBgImage   String? @map("light_page_bg_image")
  lightNavBg         String? @map("light_nav_bg")
  lightNavBgImage    String? @map("light_nav_bg_image")
  lightColumnBg      String? @map("light_column_bg")
  lightColumnBorder  String? @map("light_column_border")
  lightColumnText    String? @map("light_column_text")
  lightColumnOpacity Float?  @map("light_column_opacity")
  lightCardBg        String? @map("light_card_bg")
  lightCardBorder    String? @map("light_card_border")
  lightCardText      String? @map("light_card_text")
  lightCardOpacity   Float?  @map("light_card_opacity")
  lightCardRadius    String? @map("light_card_radius")
  lightPrimary       String? @map("light_primary")
  lightPrimaryHover  String? @map("light_primary_hover")
  lightDanger        String? @map("light_danger")
  lightDangerHover   String? @map("light_danger_hover")
  lightSuccess       String? @map("light_success")
  lightWarning       String? @map("light_warning")

  // Dark mode
  darkPageBg        String? @map("dark_page_bg")
  darkPageBgImage   String? @map("dark_page_bg_image")
  darkNavBg         String? @map("dark_nav_bg")
  darkNavBgImage    String? @map("dark_nav_bg_image")
  darkColumnBg      String? @map("dark_column_bg")
  darkColumnBorder  String? @map("dark_column_border")
  darkColumnText    String? @map("dark_column_text")
  darkColumnOpacity Float?  @map("dark_column_opacity")
  darkCardBg        String? @map("dark_card_bg")
  darkCardBorder    String? @map("dark_card_border")
  darkCardText      String? @map("dark_card_text")
  darkCardOpacity   Float?  @map("dark_card_opacity")
  darkCardRadius    String? @map("dark_card_radius")
  darkPrimary       String? @map("dark_primary")
  darkPrimaryHover  String? @map("dark_primary_hover")
  darkDanger        String? @map("dark_danger")
  darkDangerHover   String? @map("dark_danger_hover")
  darkSuccess       String? @map("dark_success")
  darkWarning       String? @map("dark_warning")

  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")

  tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@map("tenant_themes")
}