Bluewoo HRMS
AI Development GuideFoundations

Database Schema

Complete Prisma schema and Row-Level Security for HRMS

Database Schema

This is the complete Prisma schema for the HRMS system. Copy this exactly when setting up the database.

Prisma Configuration

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

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

Tenant Model

model Tenant {
  id        String       @id @default(cuid())
  name      String
  domain    String?      @unique
  status    TenantStatus @default(ACTIVE)
  settings  Json         @default("{}")
  createdAt DateTime     @default(now())
  updatedAt DateTime     @updatedAt

  users               User[]
  employees           Employee[]
  departments         Department[]
  teams               Team[]
  roles               OrgRole[]
  timeOffPolicies     TimeOffPolicy[]
  documents           Document[]
  posts               Post[]
  goals               Goal[]
  workflowTemplates   WorkflowTemplate[]
  tagCategories       TagCategory[]
  tags                Tag[]
  tagPermissions      TagPermission[]
  customFieldDefs     CustomFieldDefinition[]
  customFieldValues   CustomFieldValue[]
  dashboards          Dashboard[]

  @@map("tenants")
}

enum TenantStatus {
  ACTIVE
  SUSPENDED
  TRIAL
}

User & Auth Models

Note: The User model uses optional tenantId because Auth.js PrismaAdapter creates the user record BEFORE the createUser event fires. The event handler then updates the user with tenant context. See Phase 01: Multi-Tenant Auth for details.

model User {
  id            String     @id @default(cuid())
  tenantId      String?    // Optional - Auth.js creates user BEFORE createUser event assigns tenant
  email         String
  emailVerified DateTime?  // Required by Auth.js adapter
  name          String?
  image         String?
  systemRole    SystemRole @default(EMPLOYEE)
  status        UserStatus @default(ACTIVE)
  employeeId    String?    @unique
  lastLoginAt   DateTime?
  createdAt     DateTime   @default(now())
  updatedAt     DateTime   @updatedAt

  tenant     Tenant?     @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  employee   Employee?   @relation(fields: [employeeId], references: [id])
  accounts   Account[]
  sessions   Session[]
  dashboards Dashboard[]

  @@unique([email])        // Email unique across system (simpler for MVP)
  @@index([tenantId])      // Index for tenant queries
  @@map("users")
}

enum SystemRole {
  SYSTEM_ADMIN
  HR_ADMIN
  MANAGER
  EMPLOYEE
}

enum UserStatus {
  ACTIVE
  INACTIVE
  PENDING
}

Employee Model

model Employee {
  id             String   @id @default(cuid())
  tenantId       String
  employeeNumber String?

  // Personal Info
  firstName  String
  lastName   String
  email      String
  phone      String?
  pictureUrl String?

  // Emergency Contact
  emergencyContactName     String?
  emergencyContactPhone    String?
  emergencyContactRelation String?  // e.g., "Spouse", "Parent", "Sibling"

  // Job Information
  jobTitle  String?
  jobFamily String?
  jobLevel  String?

  // Employment Details
  employmentType  EmploymentType @default(FULL_TIME)
  workMode        WorkMode       @default(ONSITE)
  status          EmployeeStatus @default(ACTIVE)
  hireDate        DateTime?
  terminationDate DateTime?

  // Metadata
  customFields Json     @default("{}")
  createdAt    DateTime @default(now())
  updatedAt    DateTime @updatedAt

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

  // Org Relations
  orgRelations EmployeeOrgRelations?

  // As a manager (receiving reports)
  primaryReports    EmployeeOrgRelations[] @relation("PrimaryManager")
  dottedLineReports EmployeeDottedLine[]   @relation("DottedLineManager")
  additionalReports EmployeeAdditionalManager[] @relation("AdditionalManager")

  // Leadership positions
  ledTeams       Team[]       @relation("TeamLead")
  ledDepartments Department[] @relation("DepartmentHead")

  // Other relations
  timeOffRequests TimeOffRequest[]
  timeOffBalances TimeOffBalance[]
  posts           Post[]
  comments        Comment[]
  goals           Goal[]
  skills          EmployeeSkill[]
  tags            EmployeeTag[]

  @@unique([tenantId, email])
  @@unique([tenantId, employeeNumber])
  @@index([tenantId, status])
  @@map("employees")
}

enum EmploymentType {
  FULL_TIME
  PART_TIME
  CONTRACTOR
  INTERN
  TEMPORARY
}

enum WorkMode {
  ONSITE
  REMOTE
  HYBRID
}

enum EmployeeStatus {
  ACTIVE
  INACTIVE
  ON_LEAVE
  TERMINATED
}

Org Relations Models

model EmployeeOrgRelations {
  id         String @id @default(cuid())
  employeeId String @unique

  // Primary (direct) manager - 0..1
  primaryManagerId String?

  updatedAt DateTime @updatedAt

  // Relations
  employee       Employee  @relation(fields: [employeeId], references: [id], onDelete: Cascade)
  primaryManager Employee? @relation("PrimaryManager", fields: [primaryManagerId], references: [id])

  // Many-to-many via junction tables
  dottedLineManagers EmployeeDottedLine[]
  additionalManagers EmployeeAdditionalManager[]
  departments        EmployeeDepartment[]
  teams              EmployeeTeam[]
  roles              EmployeeOrgRole[]

  @@map("employee_org_relations")
}

// Junction: Dotted Line Managers (0..N)
model EmployeeDottedLine {
  id             String   @id @default(cuid())
  orgRelationsId String
  managerId      String
  createdAt      DateTime @default(now())

  orgRelations EmployeeOrgRelations @relation(fields: [orgRelationsId], references: [id], onDelete: Cascade)
  manager      Employee             @relation("DottedLineManager", fields: [managerId], references: [id])

  @@unique([orgRelationsId, managerId])
  @@map("employee_dotted_lines")
}

// Junction: Additional Real Managers (0..N)
model EmployeeAdditionalManager {
  id             String   @id @default(cuid())
  orgRelationsId String
  managerId      String
  createdAt      DateTime @default(now())

  orgRelations EmployeeOrgRelations @relation(fields: [orgRelationsId], references: [id], onDelete: Cascade)
  manager      Employee             @relation("AdditionalManager", fields: [managerId], references: [id])

  @@unique([orgRelationsId, managerId])
  @@map("employee_additional_managers")
}

// Junction: Employee <-> Department (0..N)
model EmployeeDepartment {
  id             String   @id @default(cuid())
  orgRelationsId String
  departmentId   String
  isPrimary      Boolean  @default(false)
  createdAt      DateTime @default(now())

  orgRelations EmployeeOrgRelations @relation(fields: [orgRelationsId], references: [id], onDelete: Cascade)
  department   Department           @relation(fields: [departmentId], references: [id])

  @@unique([orgRelationsId, departmentId])
  @@map("employee_departments")
}

// Junction: Employee <-> Team (0..N)
model EmployeeTeam {
  id             String   @id @default(cuid())
  orgRelationsId String
  teamId         String
  role           String?  // Role within team: "Tech Lead", "Member"
  createdAt      DateTime @default(now())

  orgRelations EmployeeOrgRelations @relation(fields: [orgRelationsId], references: [id], onDelete: Cascade)
  team         Team                 @relation(fields: [teamId], references: [id])

  @@unique([orgRelationsId, teamId])
  @@map("employee_teams")
}

// Junction: Employee <-> OrgRole (0..N)
model EmployeeOrgRole {
  id             String   @id @default(cuid())
  orgRelationsId String
  roleId         String
  isPrimary      Boolean  @default(false)
  createdAt      DateTime @default(now())

  orgRelations EmployeeOrgRelations @relation(fields: [orgRelationsId], references: [id], onDelete: Cascade)
  role         OrgRole              @relation(fields: [roleId], references: [id])

  @@unique([orgRelationsId, roleId])
  @@map("employee_org_roles")
}

Department & Team Models

model Department {
  id          String           @id @default(cuid())
  tenantId    String
  name        String
  code        String?
  description String?
  parentId    String?
  headId      String?
  status      DepartmentStatus @default(ACTIVE)
  customFields Json            @default("{}")
  createdAt   DateTime         @default(now())
  updatedAt   DateTime         @updatedAt

  tenant   Tenant      @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  parent   Department? @relation("DepartmentHierarchy", fields: [parentId], references: [id])
  children Department[] @relation("DepartmentHierarchy")
  head     Employee?   @relation("DepartmentHead", fields: [headId], references: [id])

  employeeDepartments EmployeeDepartment[]

  @@unique([tenantId, code])
  @@map("departments")
}

enum DepartmentStatus {
  ACTIVE
  INACTIVE
}

model Team {
  id          String     @id @default(cuid())
  tenantId    String
  name        String
  description String?
  type        TeamType   @default(PERMANENT)
  parentId    String?
  leadId      String?
  status      TeamStatus @default(ACTIVE)
  customFields Json      @default("{}")
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt

  tenant   Tenant    @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  parent   Team?     @relation("TeamHierarchy", fields: [parentId], references: [id])
  children Team[]    @relation("TeamHierarchy")
  lead     Employee? @relation("TeamLead", fields: [leadId], references: [id])

  employeeTeams EmployeeTeam[]

  @@map("teams")
}

enum TeamType {
  PERMANENT
  PROJECT
  SQUAD
  GUILD
  TRIBE
}

enum TeamStatus {
  ACTIVE
  INACTIVE
  ARCHIVED
}

model OrgRole {
  id          String   @id @default(cuid())
  tenantId    String
  name        String
  category    String?
  description String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

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

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

Time-Off Models

Multi-Tenant Note: TimeOffBalance and TimeOffRequest include tenantId with a Tenant relation for proper data isolation. All queries MUST filter by tenantId to ensure tenant isolation.

model TimeOffPolicy {
  id              String      @id @default(cuid())
  tenantId        String
  name            String
  code            String?
  description     String?
  leaveType       LeaveType
  accrualType     AccrualType @default(ANNUAL)
  annualAllowance Float       @default(0)
  carryOverLimit  Float       @default(0)
  carryOverExpiry Int?
  requiresApproval Boolean    @default(true)
  autoApproveRules Json?
  appliesTo       Json        @default("{}")
  status          PolicyStatus @default(ACTIVE)
  createdAt       DateTime    @default(now())
  updatedAt       DateTime    @updatedAt

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

  @@unique([tenantId, code])
  @@map("time_off_policies")
}

enum LeaveType {
  VACATION
  SICK
  PERSONAL
  PARENTAL
  MATERNITY
  PATERNITY
  BEREAVEMENT
  UNPAID
  COMPENSATORY
  OTHER
}

enum AccrualType {
  ANNUAL
  MONTHLY
  DAILY
  NONE
}

enum PolicyStatus {
  ACTIVE
  INACTIVE
  ARCHIVED
}

model TimeOffRequest {
  id          String        @id @default(cuid())
  tenantId    String
  employeeId  String
  policyId    String
  startDate   DateTime
  endDate     DateTime
  days        Float
  halfDay     Boolean       @default(false)
  halfDayPart HalfDayPart?
  status      RequestStatus @default(PENDING)
  reason      String?
  approverId  String?
  approvedAt  DateTime?
  approverComment String?
  createdAt   DateTime      @default(now())
  updatedAt   DateTime      @updatedAt

  employee Employee      @relation(fields: [employeeId], references: [id], onDelete: Cascade)
  policy   TimeOffPolicy @relation(fields: [policyId], references: [id])

  @@index([tenantId, status])
  @@map("time_off_requests")
}

enum RequestStatus {
  PENDING
  APPROVED
  REJECTED
  CANCELLED
  EXPIRED
}

enum HalfDayPart {
  MORNING
  AFTERNOON
}

model TimeOffBalance {
  id         String   @id @default(cuid())
  tenantId   String
  employeeId String
  policyId   String
  year       Int
  entitled   Float    @default(0)
  used       Float    @default(0)
  pending    Float    @default(0)
  carryOver  Float    @default(0)
  adjustment Float    @default(0)
  createdAt  DateTime @default(now())
  updatedAt  DateTime @updatedAt

  employee Employee      @relation(fields: [employeeId], references: [id], onDelete: Cascade)
  policy   TimeOffPolicy @relation(fields: [policyId], references: [id])

  @@unique([employeeId, policyId, year])
  @@map("time_off_balances")
}

Tag System Models

model TagCategory {
  id          String   @id @default(cuid())
  tenantId    String
  name        String   // "skills", "departments", "status", etc.
  assetTypes  String[] // ["employee", "document", "goal"]
  color       String?  // Default color for tags in this category
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

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

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

model Tag {
  id          String    @id @default(cuid())
  tenantId    String
  categoryId  String
  name        String
  color       String?   // Override category color
  description String?
  status      TagStatus @default(ACTIVE)
  createdAt   DateTime  @default(now())
  updatedAt   DateTime  @updatedAt

  tenant      Tenant        @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  category    TagCategory   @relation(fields: [categoryId], references: [id])
  permissions TagPermission[]
  employees   EmployeeTag[]
  documents   DocumentTag[]

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

enum TagStatus {
  ACTIVE
  ARCHIVED
}

model TagPermission {
  id        String     @id @default(cuid())
  tenantId  String
  tagId     String
  role      SystemRole // Who can use this tag
  canAssign Boolean    @default(true)
  canRemove Boolean    @default(true)

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

  @@unique([tenantId, tagId, role])
  @@index([tenantId])
  @@map("tag_permissions")
}

model EmployeeTag {
  employeeId String
  tagId      String
  assignedAt DateTime @default(now())
  assignedBy String?

  employee Employee @relation(fields: [employeeId], references: [id], onDelete: Cascade)
  tag      Tag      @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([employeeId, tagId])
  @@map("employee_tags")
}

model DocumentTag {
  documentId String
  tagId      String
  assignedAt DateTime @default(now())
  assignedBy String?

  document Document @relation(fields: [documentId], references: [id], onDelete: Cascade)
  tag      Tag      @relation(fields: [tagId], references: [id], onDelete: Cascade)

  @@id([documentId, tagId])
  @@map("document_tags")
}

Custom Fields Models

model CustomFieldDefinition {
  id          String          @id @default(cuid())
  tenantId    String
  entityType  EntityType
  fieldName   String
  fieldType   FieldType
  label       String
  description String?
  required    Boolean         @default(false)
  visibility  FieldVisibility @default(ALL)
  editable    Boolean         @default(true)
  options     Json?           // For DROPDOWN: ["option1", "option2"]
  validation  Json?           // { min, max, pattern, etc. }
  section     String?         // UI grouping
  sortOrder   Int             @default(0)
  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt

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

  @@unique([tenantId, entityType, fieldName])
  @@index([tenantId])
  @@map("custom_field_definitions")
}

model CustomFieldValue {
  id         String     @id @default(cuid())
  tenantId   String
  fieldId    String
  entityType EntityType
  entityId   String     // employeeId, departmentId, teamId, goalId
  value      String?    // Stored as JSON-compatible string
  createdAt  DateTime   @default(now())
  updatedAt  DateTime   @updatedAt

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

  @@unique([tenantId, fieldId, entityType, entityId])
  @@index([tenantId])
  @@index([entityType, entityId])
  @@map("custom_field_values")
}

enum EntityType {
  EMPLOYEE
  DEPARTMENT
  TEAM
  GOAL
}

enum FieldType {
  TEXT
  TEXTAREA
  NUMBER
  DATE
  DROPDOWN
  CHECKBOX
  URL
  EMAIL
}

enum FieldVisibility {
  ALL
  MANAGER_PLUS
  HR_ADMIN_ONLY
}

Document Models (with Access Control)

model Document {
  id          String             @id @default(cuid())
  tenantId    String
  employeeId  String?
  uploadedById String
  title       String
  description String?
  category    DocumentCategory
  fileUrl     String
  fileName    String
  mimeType    String
  fileSize    Int
  visibility  DocumentVisibility @default(PRIVATE)
  vectorId    String?
  indexed     Boolean            @default(false)
  status      DocumentStatus     @default(ACTIVE)
  createdAt   DateTime           @default(now())
  updatedAt   DateTime           @updatedAt

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

  @@index([tenantId, category])
  @@index([tenantId, visibility])
  @@map("documents")
}

model DocumentAccess {
  id         String             @id @default(cuid())
  documentId String
  accessType AccessType
  targetId   String?            // userId, teamId, departmentId, roleId
  permission DocumentPermission @default(VIEW)
  grantedBy  String
  grantedAt  DateTime           @default(now())
  expiresAt  DateTime?

  document Document @relation(fields: [documentId], references: [id], onDelete: Cascade)

  @@index([documentId])
  @@index([targetId])
  @@map("document_access")
}

enum DocumentCategory {
  POLICY
  HANDBOOK
  CONTRACT
  PERFORMANCE
  TRAINING
  CERTIFICATE
  PERSONAL
  OTHER
}

enum DocumentVisibility {
  PRIVATE      // Only document owner
  TEAM         // Owner's team(s)
  DEPARTMENT   // Owner's department(s)
  MANAGERS     // All employees with manager role
  COMPANY      // All employees in tenant
  CUSTOM       // Use DocumentAccess table for explicit grants
}

enum AccessType {
  USER
  TEAM
  DEPARTMENT
  ROLE
}

enum DocumentPermission {
  VIEW
  DOWNLOAD
  EDIT
}

enum DocumentStatus {
  ACTIVE
  ARCHIVED
  DELETED
}

model Post {
  id        String     @id @default(cuid())
  tenantId  String
  authorId  String
  type      PostType   @default(TEXT)
  content   String
  mediaUrls String[]   @default([])
  scope     PostScope  @default(COMPANY)
  scopeRefId String?
  sentiment Sentiment?
  summary   String?
  likeCount Int        @default(0)
  likedBy   String[]   @default([])
  status    PostStatus @default(PUBLISHED)
  createdAt DateTime   @default(now())
  updatedAt DateTime   @updatedAt

  tenant   Tenant    @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  author   Employee  @relation(fields: [authorId], references: [id])
  comments Comment[]

  @@index([tenantId, scope, createdAt])
  @@map("posts")
}

enum PostType {
  TEXT
  IMAGE
  VIDEO
  ANNOUNCEMENT
  POLL
}

enum PostScope {
  COMPANY
  DEPARTMENT
  TEAM
}

enum Sentiment {
  POSITIVE
  NEUTRAL
  NEGATIVE
}

enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
  DELETED
}

model Comment {
  id        String   @id @default(cuid())
  postId    String
  authorId  String
  content   String
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  post   Post     @relation(fields: [postId], references: [id], onDelete: Cascade)
  author Employee @relation(fields: [authorId], references: [id])

  @@map("comments")
}

model Goal {
  id          String     @id @default(cuid())
  tenantId    String
  ownerId     String
  title       String
  description String?
  level       GoalLevel  @default(PERSONAL)
  progress    Float      @default(0)
  status      GoalStatus @default(DRAFT)
  startDate   DateTime?
  dueDate     DateTime?
  completedAt DateTime?
  parentId    String?
  tags        String[]   @default([])
  createdAt   DateTime   @default(now())
  updatedAt   DateTime   @updatedAt

  tenant     Tenant      @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  owner      Employee    @relation(fields: [ownerId], references: [id])
  parent     Goal?       @relation("GoalHierarchy", fields: [parentId], references: [id])
  children   Goal[]      @relation("GoalHierarchy")
  keyResults KeyResult[]

  @@index([tenantId, ownerId])
  @@map("goals")
}

enum GoalLevel {
  COMPANY
  DEPARTMENT
  TEAM
  PERSONAL
}

enum GoalStatus {
  DRAFT
  ACTIVE
  COMPLETED
  CANCELLED
  ON_HOLD
}

model KeyResult {
  id           String          @id @default(cuid())
  goalId       String
  title        String
  targetValue  Float           @default(100)
  currentValue Float           @default(0)
  unit         String?
  status       KeyResultStatus @default(ON_TRACK)
  createdAt    DateTime        @default(now())
  updatedAt    DateTime        @updatedAt

  goal Goal @relation(fields: [goalId], references: [id], onDelete: Cascade)

  @@map("key_results")
}

enum KeyResultStatus {
  ON_TRACK
  AT_RISK
  OFF_TRACK
  COMPLETED
}

Workflow & Skills Models

model Skill {
  id          String   @id @default(cuid())
  tenantId    String?
  name        String
  category    String?
  description String?
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  employeeSkills EmployeeSkill[]

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

model EmployeeSkill {
  id          String      @id @default(cuid())
  employeeId  String
  skillId     String
  level       Int         @default(1)
  source      SkillSource @default(SELF)
  validatedAt DateTime?
  validatedBy String?
  createdAt   DateTime    @default(now())
  updatedAt   DateTime    @updatedAt

  employee Employee @relation(fields: [employeeId], references: [id], onDelete: Cascade)
  skill    Skill    @relation(fields: [skillId], references: [id])

  @@unique([employeeId, skillId])
  @@map("employee_skills")
}

enum SkillSource {
  SELF
  MANAGER
  PEER
  AI_INFERRED
  ASSESSMENT
}

model WorkflowTemplate {
  id            String      @id @default(cuid())
  tenantId      String
  name          String
  description   String?
  triggerType   TriggerType
  triggerConfig Json        @default("{}")
  steps         Json
  isActive      Boolean     @default(true)
  version       Int         @default(1)
  createdAt     DateTime    @default(now())
  updatedAt     DateTime    @updatedAt

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

  @@map("workflow_templates")
}

enum TriggerType {
  EMPLOYEE_CREATED
  EMPLOYEE_UPDATED
  EMPLOYEE_TERMINATED
  TIMEOFF_REQUESTED
  TIMEOFF_APPROVED
  GOAL_CREATED
  SCHEDULED
  MANUAL
}

model WorkflowInstance {
  id          String         @id @default(cuid())
  templateId  String
  entityType  String
  entityId    String
  status      WorkflowStatus @default(PENDING)
  currentStep Int            @default(0)
  stepResults Json           @default("[]")
  error       String?
  startedAt   DateTime?
  completedAt DateTime?
  createdAt   DateTime       @default(now())
  updatedAt   DateTime       @updatedAt

  template WorkflowTemplate @relation(fields: [templateId], references: [id])

  @@map("workflow_instances")
}

enum WorkflowStatus {
  PENDING
  RUNNING
  PAUSED
  COMPLETED
  FAILED
  CANCELLED
}

Dashboard Models

model Dashboard {
  id          String   @id @default(cuid())
  tenantId    String
  userId      String
  name        String
  description String?
  isDefault   Boolean  @default(false)
  layout      Json     // Grid layout configuration
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

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

  @@index([tenantId])
  @@index([userId])
  @@map("dashboards")
}

model DashboardWidget {
  id          String   @id @default(cuid())
  dashboardId String
  widgetType  String   // "headcount", "turnover", "timeoff_calendar", "pending_approvals", etc.
  title       String?  // Optional title override
  config      Json     // Widget-specific configuration
  position    Json     // { x, y, w, h } for grid layout

  dashboard Dashboard @relation(fields: [dashboardId], references: [id], onDelete: Cascade)

  @@index([dashboardId])
  @@map("dashboard_widgets")
}

model DashboardShare {
  id          String          @id @default(cuid())
  dashboardId String
  shareType   ShareType
  targetId    String?         // userId, teamId, departmentId
  permission  SharePermission @default(VIEW)

  dashboard Dashboard @relation(fields: [dashboardId], references: [id], onDelete: Cascade)

  @@index([dashboardId])
  @@index([targetId])
  @@map("dashboard_shares")
}

enum ShareType {
  USER
  TEAM
  DEPARTMENT
  COMPANY
}

enum SharePermission {
  VIEW
  EDIT
}

Auth.js & Audit Models

model Account {
  id                String  @id @default(cuid())
  userId            String
  type              String
  provider          String
  providerAccountId String
  refresh_token     String? @db.Text
  access_token      String? @db.Text
  expires_at        Int?
  token_type        String?
  scope             String?
  id_token          String? @db.Text
  session_state     String?

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

  @@unique([provider, providerAccountId])
  @@map("accounts")
}

model Session {
  id           String   @id @default(cuid())
  sessionToken String   @unique
  userId       String
  expires      DateTime

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

  @@map("sessions")
}

model VerificationToken {
  identifier String
  token      String   @unique
  expires    DateTime

  @@unique([identifier, token])
  @@map("verification_tokens")
}

model AuditLog {
  id         String   @id @default(cuid())
  tenantId   String
  userId     String?
  action     String
  entityType String
  entityId   String
  changes    Json?
  metadata   Json?
  createdAt  DateTime @default(now())

  @@index([tenantId, entityType, createdAt])
  @@index([tenantId, userId, createdAt])
  @@map("audit_logs")
}

Row-Level Security

Apply these policies after running migrations:

-- Enable RLS on all tenant-scoped tables
ALTER TABLE employees ENABLE ROW LEVEL SECURITY;
ALTER TABLE employee_org_relations ENABLE ROW LEVEL SECURITY;
ALTER TABLE departments ENABLE ROW LEVEL SECURITY;
ALTER TABLE teams ENABLE ROW LEVEL SECURITY;
ALTER TABLE time_off_policies ENABLE ROW LEVEL SECURITY;
ALTER TABLE time_off_requests ENABLE ROW LEVEL SECURITY;
ALTER TABLE documents ENABLE ROW LEVEL SECURITY;
ALTER TABLE posts ENABLE ROW LEVEL SECURITY;
ALTER TABLE goals ENABLE ROW LEVEL SECURITY;

-- Tenant ID function
CREATE OR REPLACE FUNCTION current_tenant_id()
RETURNS TEXT AS $$
  SELECT current_setting('app.current_tenant_id', true)
$$ LANGUAGE SQL SECURITY DEFINER;

-- Apply policy (repeat for all tables)
CREATE POLICY tenant_isolation ON employees
  USING (tenant_id = current_tenant_id());

CREATE POLICY tenant_isolation ON departments
  USING (tenant_id = current_tenant_id());

CREATE POLICY tenant_isolation ON teams
  USING (tenant_id = current_tenant_id());

CREATE POLICY tenant_isolation ON time_off_policies
  USING (tenant_id = current_tenant_id());

CREATE POLICY tenant_isolation ON time_off_requests
  USING (tenant_id = current_tenant_id());

CREATE POLICY tenant_isolation ON documents
  USING (tenant_id = current_tenant_id());

CREATE POLICY tenant_isolation ON posts
  USING (tenant_id = current_tenant_id());

CREATE POLICY tenant_isolation ON goals
  USING (tenant_id = current_tenant_id());

Setting Tenant Context

In NestJS middleware:

// Set tenant context before queries
await prisma.$executeRaw`SELECT set_config('app.current_tenant_id', ${tenantId}, true)`

Domain Models

Core data models for HRMS flexible org structure

Development Workflow

Step-by-step workflow for AI-assisted development

On this page

Database SchemaPrisma ConfigurationTenant ModelUser & Auth ModelsEmployee ModelOrg Relations ModelsDepartment & Team ModelsTime-Off ModelsTag System ModelsCustom Fields ModelsDocument Models (with Access Control)Workflow & Skills ModelsDashboard ModelsAuth.js & Audit ModelsRow-Level SecuritySetting Tenant Context