# Uwezo Fund HRMS — Prisma Database Schema

**Database:** `uwezo_fund_hrms` (PostgreSQL)
**Last Updated:** May 2026
**Version:** v2.4.0

---

## Enums

```prisma
enum UserRole { admin, hr, employee, hod, hos, finance, intern }
enum CommentRole { HOD, HR, HOS, Admin }
enum Gender { Male, Female }
enum EmploymentType { Permanent, Contract, Intern }
enum LeaveStatus { Pending, PendingReview, HODApproved, HOSApproved, Approved, Rejected, Cancelled }
enum TrainingMode { Physical, Virtual, Hybrid }
enum TrainingStatus { Submitted, HODApproved, HOSApproved, FinanceApproved, Completed, Rejected }
enum AppraisalStatus { PendingSelfAssessment, SelfAssessmentSubmitted, HODReview, HODApproved, Completed }
enum PIPStatus { Active, Completed, Successful, Failed }
enum RecruitmentStatus { Draft, Open, Shortlisting, Interviewing, Offer, Closed }
enum CandidateStatus { New, Shortlisted, Interviewed, Offered, Hired, Rejected }
enum TerminationStatus { Pending, Approved, Completed, Cancelled }
enum DisciplinaryStatus { Open, UnderInvestigation, Resolved, Closed, Appealed }
```

---

## Core Tables

### User
| Column | Type | PK | Unique | Nullable | Default | Index |
|--------|------|----|--------|----------|---------|-------|
| id | String (uuid) | ✅ | | | uuid() | |
| username | String | | ✅ | | | |
| passwordHash | String | | | | | |
| email | String | | ✅ | | | |
| name | String | | | | | |
| role | UserRole | | | | | role |
| employeeId | String | | ✅ | ✅ | | |
| dept | String | | | ✅ | | dept |
| position | String | | | ✅ | | |
| avatar | String | | | ✅ | | |
| gender | Gender | | | ✅ | | |
| isFirstLogin | Boolean | | | | true | |
| isIntern | Boolean | | | | false | isIntern |
| isActive | Boolean | | | | true | isActive |
| createdAt | DateTime | | | | now() | |
| updatedAt | DateTime | | | | updatedAt | |
| lastLoginAt | DateTime | | | ✅ | | |

**FK:** employeeId → Employee.employeeNumber (nullable)
**Composite Index:** [role, isActive], [dept, role]

---

### Employee
| Column | Type | PK | Unique | Nullable | Default | Index |
|--------|------|----|--------|----------|---------|-------|
| id | String (uuid) | ✅ | | | uuid() | |
| employeeNumber | String | | ✅ | | | |
| firstName | String | | | | | |
| lastName | String | | | | | |
| fullName | String | | | | | fullName |
| email | String | | ✅ | | | |
| phone | String | | | ✅ | | |
| nationalId | String | | ✅ | ✅ | | |
| kraPin | String | | ✅ | ✅ | | |
| dob | DateTime | | | ✅ | | |
| gender | Gender | | | | | |
| employmentType | EmploymentType | | | | Permanent | employmentType |
| dept | String | | | | | dept |
| position | String | | | | | |
| grade | String | | | ✅ | | |
| joinDate | DateTime | | | | | |
| contractEndDate | DateTime | | | ✅ | | |
| salary | Decimal(12,2) | | | ✅ | | |
| bankName | String | | | ✅ | | |
| bankAccount | String | | | ✅ | | |
| nhifNo | String | | | ✅ | | |
| nssfNo | String | | | ✅ | | |
| supervisorId | String | | | ✅ | | supervisorId |
| isIntern | Boolean | | | | false | isIntern |
| isActive | Boolean | | | | true | isActive |
| postalAddress | String | | | ✅ | | |
| createdAt | DateTime | | | | now() | |
| updatedAt | DateTime | | | | updatedAt | |

**FK:** supervisorId → Employee.id (self-relation, nullable)
**Composite Index:** [dept, isActive], [employmentType, isActive], [supervisorId]

---

### Session
| Column | Type | PK | Unique | Nullable | Default |
|--------|------|----|--------|----------|---------|
| id | String (uuid) | ✅ | | | uuid() |
| userId | String | | | | |
| token | String | | ✅ | | |
| ipAddress | String | | | ✅ | |
| userAgent | String | | | ✅ | |
| expiresAt | DateTime | | | | |
| createdAt | DateTime | | | | now() |

**FK:** userId → User.id (Cascade)
**Indexes:** token, userId, expiresAt

---

## Leave Management

### LeavePolicy
| Column | Type | PK | Unique | Nullable | Default |
|--------|------|----|--------|----------|---------|
| id | String (uuid) | ✅ | | | uuid() |
| type | String | | ✅ | | |
| label | String | | | | |
| entitlement | Int | | | | |
| internEntitlement | Int | | | | |
| accrualRate | Decimal(5,2) | | | | |
| maxCarryover | Int | | | | 0 |
| unpaidAllowed | Boolean | | | | false |
| notes | String | | | ✅ | |
| color | String | | | | |
| icon | String | | | | |
| requiresDocument | Boolean | | | | false |
| requiresReason | Boolean | | | | true |
| createdAt | DateTime | | | | now() |
| updatedAt | DateTime | | | | updatedAt |

**Note:** internEntitlement is the authoritative intern leave days. computeEmployeeBalance() picks internEntitlement for interns and entitlement for employees.

---

### LeaveBalance
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| policyId | String | | | |
| fiscalYear | String | | | |
| entitlement | Int | | | |
| taken | Int | | | 0 |
| remaining | Int | | | |
| carryover | Int | | | 0 |
| expiryDate | DateTime | | ✅ | |
| updatedAt | DateTime | | | updatedAt |

**Composite PK / Unique:** [employeeId, policyId, fiscalYear]
**FKs:** employeeId → Employee.id (Cascade), policyId → LeavePolicy.id
**Indexes:** employeeId, [employeeId, fiscalYear]

---

### LeaveRequest
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String | ✅ | | |
| employeeId | String | | | |
| employeeName | String | | | |
| dept | String | | | |
| position | String | | | |
| employeeType | String | | | |
| gender | Gender | | | |
| type | String | | | |
| startDate | DateTime | | | |
| endDate | DateTime | | | |
| days | Int | | | |
| reason | String | | ✅ | |
| status | LeaveStatus | | | Pending |
| submittedOn | DateTime | | | now() |
| dutyDelegate | String | | ✅ | |
| hasDocument | Boolean | | | false |
| hodApprovedBy | String | | ✅ | |
| hodApprovedOn | DateTime | | ✅ | |
| hosApprovedBy | String | | ✅ | |
| hosApprovedOn | DateTime | | ✅ | |
| hosComment | String | | ✅ | |
| approvedBy | String | | ✅ | |
| approvedOn | DateTime | | ✅ | |
| rejectionReason | String | | ✅ | |
| rejectedBy | String | | ✅ | |
| rejectedStage | String | | ✅ | |
| reviewRequested | Boolean | | | false |
| reviewComment | String | | ✅ | |
| reviewNewStart | DateTime | | ✅ | |
| reviewNewEnd | DateTime | | ✅ | |
| reviewBy | String | | ✅ | |
| reviewDate | DateTime | | ✅ | |
| hosDigitalSignatureId | String | | ✅ | |
| hosComment | String | | ✅ | |
| hrNotifiedAt | DateTime | | ✅ | |
| hrCommentSeenAt | DateTime | | ✅ | |
| computationDownloadedByHR | Boolean | | | false |
| computationDownloadedByEmployee | Boolean | | | false |
| computationDownloadedByHOD | Boolean | | | false |
| computationAvailableAt | DateTime | | ✅ | | — set when status becomes HOSApproved, triggers download button in all portals |
| calendarHighlightedAt | DateTime | | ✅ | |
| updatedAt | DateTime | | | updatedAt |

**DigitalSignature trigger:** When status changes to HOSApproved, system creates a DigitalSignature record linked to this LeaveRequest, sets computationAvailableAt = now(), and sends notification to HR, HOD, and Employee/Intern.
**Computation button rule:** Download button for leave computation form is ONLY shown when status = HOSApproved OR Approved. Visible in HR, HOD, Employee, and Intern portals ONLY after HOS approval.

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, status, [startDate, endDate], [employeeId, status], [dept, status]

**Leave Workflow (ALL staff including Interns):**
- Employee/Intern submits → status: Pending
- HOD approves → status: HODApproved (ALL types go HOD → HOS)
- HOS confirms → status: HOSApproved (final approval authority)
- HR records HOS-approved requests for compliance/filing only (no HR approval step)
- Any stage can "Request Review" → status: PendingReview (employee must edit & resubmit)

---

## Training & Development

### TrainingProgram
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| title | String | | | |
| dept | String | | | |
| trainer | String | | | |
| mode | TrainingMode | | | Physical |
| cost | Decimal(12,2) | | | |
| capacity | Int | | | |
| enrolled | Int | | | 0 |
| startDate | DateTime | | | |
| endDate | DateTime | | | |
| status | String | | | "Upcoming" |
| venue | String | | ✅ | |
| description | String | | ✅ | |
| objectives | String[] | | | [] |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**Indexes:** dept, status, [startDate, endDate]

---

### TrainingEnrollment
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| programId | String | | | |
| employeeId | String | | | |
| enrolledDate | DateTime | | | now() |
| completionStatus | String | | | "Enrolled" |
| completionDate | DateTime | | ✅ | |
| score | Int | | ✅ | |

**Composite Unique:** [programId, employeeId]
**FKs:** programId → TrainingProgram.id (Cascade), employeeId → Employee.id (Cascade)
**Indexes:** programId, employeeId

---

### TrainingRequest
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| employeeEmail | String | | | |
| courseRequested | String | | | |
| trainingProvider | String | | | |
| trainingMode | TrainingMode | | | Physical |
| regNo | String | | ✅ | |
| estimatedCost | Decimal(12,2) | | | |
| proformaInvoice | String | | ✅ | |
| admissionLetter | String | | ✅ | |
| preferredStartDate | DateTime | | | |
| preferredEndDate | DateTime | | | |
| justification | String | | | |
| expectedOutcomes | String | | ✅ | |
| previousTraining | Boolean | | | false |
| status | TrainingStatus | | | Submitted |
| currentStage | String | | | "HOD" |
| dateSubmitted | DateTime | | | now() |
| hrApprovedBy | String | | ✅ | |
| hrApprovedOn | DateTime | | ✅ | |
| hosApprovedBy | String | | ✅ | |
| hosApprovedOn | DateTime | | ✅ | |
| hosDigitalSignatureId | String | | ✅ | |
| hosComment | String | | ✅ | |
| financeApprovedBy | String | | ✅ | |
| financeApprovedOn | DateTime | | ✅ | |
| rejectReason | String | | ✅ | |
| rejectedBy | String | | ✅ | |
| rejectedStage | String | | ✅ | |
| assignedProgramId | String | | ✅ | |
| computationDownloadedByHR | Boolean | | | false |
| computationDownloadedByEmployee | Boolean | | | false |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, currentStage, status, [employeeId, status]

**Training Request Workflow:**
- Employee submits → status: Submitted, currentStage: HOD
- HOD approves → status: HODApproved, currentStage: HOS
- HOS approves → status: HOSApproved, currentStage: Finance
- Finance approves → status: FinanceApproved
- HR does NOT approve training requests. HR only records and tracks training completions.

---

### TrainingRecord
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| employeeName | String | | | |
| dept | String | | | |
| courseName | String | | | |
| provider | String | | | |
| completionDate | DateTime | | | |
| certStatus | String | | | "Missing" |
| certUploadDate | DateTime | | ✅ | |
| certFileUrl | String | | ✅ | |
| expiryDate | DateTime | | ✅ | |
| daysToExpiry | Int | | ✅ | |
| hoursAwarded | Int | | | 0 |
| cost | Decimal(12,2) | | | 0 |
| daysSinceCompletion | Int | | | 0 |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, certStatus, [employeeId, certStatus]

---

### TrainingEvaluation
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| programId | String | | | |
| employeeId | String | | | |
| evalDate | DateTime | | | now() |
| l1Rating | Int | | | |
| l1Feedback | String | | ✅ | |
| l2PreScore | Int | | | 0 |
| l2PostScore | Int | | | 0 |
| l2CertUploaded | Boolean | | | false |
| l2Status | String | | | |
| l3Status | String | | | "Pending" |
| l3AssessmentDate | DateTime | | ✅ | |
| l3ManagerRating | Int | | ✅ | |
| l3Application | Int | | ✅ | |
| l3Confidence | Int | | ✅ | |
| l3LessSupervision | Int | | ✅ | |
| l3SharesLearning | Int | | ✅ | |
| l3Notes | String | | ✅ | |
| l4KPI | String | | ✅ | |
| l4Before | String | | ✅ | |
| l4After | String | | ✅ | |
| l4Improvement | String | | ✅ | |
| l4ROI | String | | ✅ | |
| l4MetricsImproved | Boolean | | | false |
| l4Notes | String | | ✅ | |
| completedDate | DateTime | | | |
| createdAt | DateTime | | | now() |

**Composite Unique:** [programId, employeeId]
**FKs:** programId → TrainingProgram.id (Cascade), employeeId → Employee.id (Cascade)
**Indexes:** employeeId, programId

---

## Performance Management

### WorkPlan
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| title | String | | | |
| objective | String | | | |
| target | String | | | |
| progress | Int | | | 0 |
| dueDate | DateTime | | | |
| status | String | | | "Not Started" |
| quarter | String | | | |
| year | Int | | | |
| linkedTargetId | String | | ✅ | |
| tasks | Json | | | [] |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade), linkedTargetId → DepartmentTarget.id (nullable)
**Indexes:** employeeId, [quarter, year], [employeeId, quarter, year]

---

### WorkPlanTask
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| workPlanId | String | | | |
| title | String | | | |
| objective | String | | | |
| expectedOutput | String | | | |
| startDate | DateTime | | | |
| endDate | DateTime | | | |
| remarks | String | | ✅ | |
| status | String | | | "Not Started" |
| weeklyProgress | Json | | | [] |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** workPlanId → WorkPlan.id (Cascade)
**Indexes:** workPlanId

---

### QuarterlyTarget
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| title | String | | | |
| quarter | String | | | |
| year | Int | | | |
| target | String | | | |
| kpiMeasurement | String | | ✅ | |
| expectedOutcome | String | | ✅ | |
| completionTimeline | String | | ✅ | |
| weight | Int | | | 0 |
| progress | Int | | | 0 |
| status | String | | | "Not Started" |
| score | Int | | ✅ | |
| linkedWorkPlanId | String | | ✅ | |
| linkedDeptTargetId | String | | ✅ | |
| supervisorComments | String | | ✅ | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, [quarter, year], [employeeId, quarter, year]

---

### KPI
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| title | String | | | |
| description | String | | ✅ | |
| targetValue | String | | | |
| currentValue | String | | ✅ | |
| measurementMethod | String | | ✅ | |
| frequency | String | | | "Quarterly" |
| performanceScore | Int | | | 0 |
| progress | Int | | | 0 |
| targetDeadline | DateTime | | ✅ | |
| linkedWorkPlanId | String | | ✅ | |
| linkedGoalId | String | | ✅ | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade), linkedGoalId → CompanyGoal.id (nullable)
**Indexes:** employeeId

---

### IndividualPerformanceIndicator
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| workPlanId | String | | | |
| workPlanTaskId | String | | ✅ | |
| employeeId | String | | | |
| indicatorName | String | | | |
| indicator | String | | | |
| baseline | String | | | |
| target | String | | | |
| current | String | | | |
| unit | String | | | |
| progress | Int | | | 0 |
| measurementCriteria | String | | ✅ | |
| expectedPerformance | String | | ✅ | |
| ratingScale | String | | ✅ | |
| supervisorRating | Int | | ✅ | |
| supervisorComment | String | | ✅ | |
| taskTitle | String | | ✅ | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FKs:** workPlanId → WorkPlan.id (Cascade), employeeId → Employee.id (Cascade)
**Indexes:** workPlanId, employeeId

---

### Appraisal
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| period | String | | | |
| status | AppraisalStatus | | | PendingSelfAssessment |
| overallRating | Decimal(3,1) | | ✅ | |
| overallScore | Int | | ✅ | |
| grade | String | | ✅ | |
| supervisor | String | | ✅ | |
| submittedDate | DateTime | | ✅ | |
| hodApprovedDate | DateTime | | ✅ | |
| hodComments | String | | ✅ | |
| selfAssessmentComments | String | | ✅ | |
| pipStatus | String | | | "None" |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, status, period, [employeeId, period]

---

### GoalRating
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| appraisalId | String | | | |
| goalId | String | | | |
| goalType | String | | | |
| selfRating | Int | | | |
| selfComment | String | | ✅ | |
| hodRating | Int | | ✅ | |
| hodComment | String | | ✅ | |
| createdAt | DateTime | | | now() |

**FK:** appraisalId → Appraisal.id (Cascade)
**Indexes:** appraisalId, goalId

---

### PerformanceImprovementPlan
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| triggerRating | Decimal(3,1) | | | |
| appraisalPeriod | String | | | |
| startDate | DateTime | | | |
| endDate | DateTime | | | |
| duration | String | | | |
| improvementAreas | String[] | | | [] |
| actionPlan | String | | | |
| successMetrics | String | | | |
| status | PIPStatus | | | Active |
| supervisor | String | | ✅ | |
| aiGenerated | Boolean | | | false |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, status

---

### PIPCheckIn
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| pipId | String | | | |
| date | DateTime | | | now() |
| author | String | | | |
| progress | Int | | | |
| notes | String | | ✅ | |
| createdAt | DateTime | | | now() |

**FK:** pipId → PerformanceImprovementPlan.id (Cascade)
**Indexes:** pipId

---

## Payroll

### PayrollRecord
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| month | Int | | | |
| year | Int | | | |
| basicSalary | Decimal(12,2) | | | |
| houseAllowance | Decimal(12,2) | | | 0 |
| transportAllowance | Decimal(12,2) | | | 0 |
| otherAllowances | Decimal(12,2) | | | 0 |
| grossPay | Decimal(12,2) | | | |
| paye | Decimal(12,2) | | | 0 |
| nhif | Decimal(12,2) | | | 0 |
| nssf | Decimal(12,2) | | | 0 |
| otherDeductions | Decimal(12,2) | | | 0 |
| netPay | Decimal(12,2) | | | |
| payDate | DateTime | | ✅ | |
| status | String | | | "Draft" |
| processedBy | String | | ✅ | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**Composite Unique:** [employeeId, month, year]
**FK:** employeeId → Employee.id (Cascade)
**Indexes:** [month, year], status, [employeeId, year]

---

## Recruitment

### RecruitmentRequisition
| Column | Type | PK | Unique | Nullable | Default |
|--------|------|----|--------|----------|---------|
| id | String (uuid) | ✅ | | | uuid() |
| position | String | | | | |
| dept | String | | | | |
| employmentType | EmploymentType | | | | Permanent |
| vacancies | Int | | | | 1 |
| justification | String | | | | |
| requiredByDate | DateTime | | | | |
| status | RecruitmentStatus | | | Draft |
| requestedBy | String | | | | |
| approvedBy | String | | ✅ | |
| approvedOn | DateTime | | ✅ | |
| jobDescription | String | | ✅ | |
| requirements | String[] | | | [] |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**Indexes:** dept, status, employmentType

---

### Candidate
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| requisitionId | String | | ✅ | |
| name | String | | | |
| email | String | | | |
| phone | String | | ✅ | |
| position | String | | | |
| dept | String | | | |
| status | CandidateStatus | | | New |
| interviewDate | DateTime | | ✅ | |
| interviewScore | Int | | ✅ | |
| interviewNotes | String | | ✅ | |
| offerStatus | String | | ✅ | |
| offerDate | DateTime | | ✅ | |
| cvUrl | String | | ✅ | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** requisitionId → RecruitmentRequisition.id (nullable)
**Indexes:** requisitionId, status, dept

---

## Disciplinary

### DisciplinaryCase
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| caseNumber | String | | ✅ | |
| employeeId | String | | | |
| employeeName | String | | | |
| dept | String | | | |
| offenceType | String | | | |
| offenceDate | DateTime | | | |
| description | String | | | |
| status | DisciplinaryStatus | | | Open |
| severity | String | | | "Minor" |
| investigatingOfficer | String | | ✅ | |
| hearingDate | DateTime | | ✅ | |
| outcome | String | | ✅ | |
| penalty | String | | ✅ | |
| appealDeadline | DateTime | | ✅ | |
| closedDate | DateTime | | ✅ | |
| createdBy | String | | | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, status, caseNumber, [dept, status]

---

## Termination

### TerminationRequest
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| employeeName | String | | | |
| dept | String | | | |
| terminationType | String | | | |
| terminationDate | DateTime | | | |
| reason | String | | | |
| status | TerminationStatus | | | Pending |
| initiatedBy | String | | | |
| approvedBy | String | | ✅ | |
| approvedOn | DateTime | | ✅ | |
| noticePeriod | Int | | | 0 |
| finalSettlement | Decimal(12,2) | | ✅ | |
| exitInterviewDate | DateTime | | ✅ | |
| exitInterviewNotes | String | | ✅ | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, status, terminationType

---

## Organization & Goals

### CompanyGoal
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| kra | String | | | |
| title | String | | | |
| description | String | | | |
| target | String | | | |
| fiscalYear | String | | | |
| progress | Int | | | 0 |
| createdBy | String | | | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**Indexes:** fiscalYear

---

### UwezoObjective
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| title | String | | | |
| description | String | | | |
| fiscalYear | String | | | |
| createdAt | DateTime | | | now() |

**Indexes:** fiscalYear

---

### DepartmentTarget
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| dept | String | | | |
| fiscalYear | String | | | |
| title | String | | | |
| objective | String | | | |
| target | String | | | |
| description | String | | ✅ | |
| quarter | String | | ✅ | |
| expectedOutcomes | String | | ✅ | |
| deadline | DateTime | | ✅ | |
| priority | String | | | "Medium" |
| measurableObjectives | String | | ✅ | |
| status | String | | | "Pending" |
| progress | Int | | | 0 |
| alignedToCompanyGoalId | String | | ✅ | |
| alignedToUwezoObjectiveId | String | | ✅ | |
| hodId | String | | ✅ | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FKs:** alignedToCompanyGoalId → CompanyGoal.id (nullable), hodId → User.id (nullable)
**Indexes:** dept, fiscalYear, [dept, fiscalYear], quarter

---

## System & Config

### AuditLog
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| userId | String | | ✅ | |
| userName | String | | ✅ | |
| action | String | | | |
| entityType | String | | | |
| entityId | String | | ✅ | |
| oldValue | Json | | ✅ | |
| newValue | Json | | ✅ | |
| ipAddress | String | | ✅ | |
| userAgent | String | | ✅ | |
| createdAt | DateTime | | | now() |

**FK:** userId → User.id (nullable, no cascade — preserve logs)
**Indexes:** userId, action, entityType, createdAt, [entityType, entityId]

---

### Notification
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| userId | String | | | |
| title | String | | | |
| message | String | | | |
| alertType | String | | | "info" |
| read | Boolean | | | false |
| link | String | | ✅ | |
| relatedEntityType | String | | ✅ | |
| relatedEntityId | String | | ✅ | |
| timestamp | DateTime | | | now() |

**FK:** userId → User.id (Cascade)
**Indexes:** userId, read, timestamp, [userId, read]

---

### SystemConfig
| Column | Type | PK | Unique | Nullable |
|--------|------|----|--------|----------|
| id | String (uuid) | ✅ | | |
| key | String | | ✅ | |
| value | Json | | | |
| updatedBy | String | | ✅ | |
| updatedAt | DateTime | | | updatedAt |

**Indexes:** key

---

### Holiday
| Column | Type | PK | Unique | Default |
|--------|------|----|--------|---------|
| id | String (uuid) | ✅ | | uuid() |
| date | DateTime | | ✅ | |
| name | String | | | |
| isRecurring | Boolean | | | false |
| createdAt | DateTime | | | now() |

**Indexes:** date

---

### Document
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | ✅ | |
| title | String | | | |
| fileUrl | String | | | |
| fileType | String | | | |
| fileSize | Int | | ✅ | |
| category | String | | | |
| uploadedBy | String | | | |
| uploadedAt | DateTime | | | now() |
| metadata | Json | | ✅ | |

**FK:** employeeId → Employee.id (nullable, Cascade)
**Indexes:** employeeId, category, uploadedAt

---

### GradeScale
| Column | Type | PK | Unique | Default |
|--------|------|----|--------|---------|
| id | String (uuid) | ✅ | | uuid() |
| grade | String | | ✅ | |
| label | String | | | |
| minScore | Int | | | |
| maxScore | Int | | | |
| description | String | | ✅ | |
| createdAt | DateTime | | | now() |

---

### Department
| Column | Type | PK | Unique | Default |
|--------|------|----|--------|---------|
| id | String (uuid) | ✅ | | uuid() |
| name | String | | ✅ | |
| code | String | | ✅ | |
| hodId | String | | ✅ | |
| description | String | | ✅ | |
| isActive | Boolean | | | true |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** hodId → User.id (nullable)
**Indexes:** isActive

---

---

## Performance Comments & Reviews

### PerformanceComment
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| itemType | String | | | | — workplan/kpi/ipi/quarterly/pip |
| itemId | String | | | |
| itemTitle | String | | | |
| commentBy | String | | | | — Display name of commenter |
| commentById | String | | ✅ | | — FK to User.id |
| commentByRole | String | | | | — HOD/HOS/HR/Admin |
| comment | String | | | |
| read | Boolean | | | false |
| readAt | DateTime | | ✅ | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FK:** employeeId → Employee.id (Cascade), commentById → User.id (nullable, no cascade)
**Indexes:** employeeId, [employeeId, itemType], [employeeId, read], itemId, commentByRole
**Visibility rules:**
- HOD: can view and add comments on KPIs, Quarterly Targets, IPIs, PIPs, WorkPlans for employees in their department
- HR Manager: can view and add comments on ALL employee/intern KPIs, Quarterly Targets, IPIs, PIPs across all departments
- Employees/Interns: can view comments addressed to them (by employeeId), cannot see other employees' comments
- All comments from any role (HOD/HR/HOS) are visible to the employee they reference

---

### DigitalSignature
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| documentType | String | | | | — leave/training/appraisal/termination |
| documentId | String | | | |
| signedBy | String | | | |
| signerName | String | | | |
| signerRole | String | | | | — HOS/CEO/HOD/HR |
| signerTitle | String | | | |
| signedAt | DateTime | | | now() |
| ipAddress | String | | ✅ | |
| isValid | Boolean | | | true |
| revokedAt | DateTime | | ✅ | |
| revokedBy | String | | ✅ | |
| signatureHash | String | | ✅ | | — SHA-256 hash of signed content |
| metadata | Json | | ✅ | |

**Composite Unique:** [documentType, documentId]
**Indexes:** documentId, [documentType, documentId], signedBy, signedAt

---

### LeaveComputationDownload
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| leaveRequestId | String | | | |
| downloadedBy | String | | | |
| downloaderRole | String | | | | — HR/Employee/Intern/HOD |
| downloadedAt | DateTime | | | now() |
| format | String | | | "PDF" | — PDF/Word |
| ipAddress | String | | ✅ | |

**FK:** leaveRequestId → LeaveRequest.id (Cascade)
**Indexes:** leaveRequestId, downloadedBy, downloadedAt

---

### HODAccount
| Column | Type | PK | Unique | Nullable | Default |
|--------|------|----|--------|----------|---------|
| id | String (uuid) | ✅ | | | uuid() |
| userId | String | | ✅ | | |
| username | String | | ✅ | | |
| dept | String | | | | |
| phone | String | | | ✅ | |
| passwordChangedAt | DateTime | | | ✅ | |
| mustChangePassword | Boolean | | | | true |
| createdByAdminId | String | | | ✅ | |
| isActive | Boolean | | | | true |
| loginCode | String | | ✅ | ✅ | |
| createdAt | DateTime | | | | now() |
| updatedAt | DateTime | | | | updatedAt |

**FK:** userId → User.id (Cascade), createdByAdminId → User.id (nullable)
**Indexes:** userId, dept, isActive

---

### PromotionRecord
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| fromRole | String | | | |
| toRole | String | | | |
| fromDept | String | | ✅ | |
| toDept | String | | ✅ | |
| effectiveDate | DateTime | | | now() |
| authorizedBy | String | | | |
| notes | String | | ✅ | |
| createdAt | DateTime | | | now() |

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, effectiveDate

---

### DepartmentHODAssignment
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| departmentId | String | | | |
| hodUserId | String | | | |
| assignedBy | String | | | |
| assignedAt | DateTime | | | now() |
| endedAt | DateTime | | ✅ | |
| isActive | Boolean | | | true |
| notes | String | | ✅ | |

**FK:** departmentId → Department.id (Cascade), hodUserId → User.id
**Indexes:** departmentId, hodUserId, isActive, [departmentId, isActive]

---

### PasswordResetLog
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| userId | String | | | |
| resetBy | String | | | |
| resetReason | String | | ✅ | |
| ipAddress | String | | ✅ | |
| createdAt | DateTime | | | now() |

**FK:** userId → User.id (Cascade)
**Indexes:** userId, createdAt

---

### EmployeePasswordResetRequest
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| requestedByAdminId | String | | | |
| reason | String | | ✅ | |
| newPasswordHash | String | | | |
| mustChangeOnNextLogin | Boolean | | | true |
| fulfilledAt | DateTime | | ✅ | |
| ipAddress | String | | ✅ | |
| createdAt | DateTime | | | now() |

**FK:** employeeId → Employee.id (Cascade)
**Indexes:** employeeId, requestedByAdminId, createdAt

---

---

## HR Office Configuration

### HROfficeConfig
| Column | Type | PK | Unique | Nullable | Default |
|--------|------|----|--------|----------|---------|
| id | String (uuid) | ✅ | | | uuid() |
| key | String | | ✅ | | | — e.g. 'stamp', 'signature', 'hr_name' |
| value | String (text) | | | ✅ | | — base64 image or text value |
| updatedBy | String | | | ✅ | |
| updatedAt | DateTime | | | | updatedAt |
| createdAt | DateTime | | | | now() |

**Indexes:** key
**Note:** Stores HR office stamp (base64 image), HR officer signature (base64 image), and other HR config values. Frontend reads these via `/api/hr-config` or directly from the store.

---

### HRStampAuditLog
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| action | String | | | | — 'stamp_uploaded', 'signature_uploaded', 'stamp_removed' |
| performedBy | String | | | |
| ipAddress | String | | ✅ | |
| createdAt | DateTime | | | now() |

**FK:** performedBy → User.id
**Indexes:** performedBy, createdAt

---

## Performance Management — Extended

### SharedDepartmentTarget
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| dept | String | | | | — department name |
| title | String | | | |
| description | String | | ✅ | |
| quarter | String | | | | — Q1/Q2/Q3/Q4 |
| expectedOutcomes | String | | ✅ | |
| deadline | DateTime | | ✅ | |
| priority | String | | | 'Medium' | — High/Medium/Low |
| measurableObjectives | String | | ✅ | |
| progress | Int | | | 0 |
| status | String | | | 'Pending' | — Pending/Ongoing/Completed/Delayed |
| fiscalYear | String | | | | — e.g. '2025/2026' |
| createdByHodId | String | | ✅ | |
| alignedToOrgGoalId | String | | ✅ | |
| createdAt | DateTime | | | now() |
| updatedAt | DateTime | | | updatedAt |

**FKs:** createdByHodId → User.id (nullable), alignedToOrgGoalId → CompanyGoal.id (nullable)
**Indexes:** dept, fiscalYear, [dept, fiscalYear], quarter, [dept, quarter]
**Note:** When HOD creates a departmental target, it is automatically visible to all employees/interns in that department. Employees base their KPIs, quarterly targets, and IPIs on these shared targets.

---

### EmployeePerformanceSummary (view/computed)
| Column | Type | Notes |
|--------|------|-------|
| employeeId | String | FK → Employee.id |
| workPlanCount | Int | Total work plans |
| kpiCount | Int | Total KPIs |
| ipiCount | Int | Total IPIs |
| quarterlyTargetCount | Int | Total quarterly targets |
| activePIPCount | Int | Active PIPs |
| avgKPIProgress | Decimal | Average KPI progress |
| lastUpdated | DateTime | Last performance update |

**Note:** This is a computed/materialized view updated when any performance record changes. Visible to HOD (own dept), HR (all employees), HOS (all employees).

---

### PIPInitiation
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| pipId | String | | | | FK → PerformanceImprovementPlan.id |
| initiatedByRole | String | | | | — HR/HOD/System/Employee |
| initiatedById | String | | ✅ | |
| aiGenerated | Boolean | | | false |
| triggerType | String | | | | — 'low_rating'/'bottom_10pct'/'manual'/'employee_self' |
| triggerDetails | Json | | ✅ | |
| createdAt | DateTime | | | now() |

**FK:** pipId → PerformanceImprovementPlan.id (Cascade), initiatedById → User.id (nullable)
**Indexes:** pipId, initiatedByRole, aiGenerated
**Note:** Tracks who/what initiated each PIP. AI-generated PIPs have aiGenerated=true and triggerType='low_rating' or 'bottom_10pct'.

---

### PerformanceVisibility
| Column | Type | PK | Nullable | Default |
|--------|------|----|----------|---------|
| id | String (uuid) | ✅ | | uuid() |
| employeeId | String | | | |
| viewerRole | String | | | | — HOD/HR/HOS |
| viewerId | String | | ✅ | |
| canViewWorkPlans | Boolean | | | true |
| canViewKPIs | Boolean | | | true |
| canViewIPIs | Boolean | | | true |
| canViewQuarterlyTargets | Boolean | | | true |
| canViewPIPs | Boolean | | | true |
| canAddComments | Boolean | | | true |
| createdAt | DateTime | | | now() |

**FK:** employeeId → Employee.id (Cascade)
**Composite Unique:** [employeeId, viewerRole, viewerId]
**Indexes:** employeeId, viewerRole
**Note:** Default: HOD can view/comment on all performance items of employees in their dept. HR can view all employee/intern performance items. HOS can view all. Employees can only view their own.

---

## Design Decisions

1. **Leave Workflow (ALL staff including Interns):** Employee/Intern → HOD → HOS (final approval). HR does NOT approve leave requests. HR only views HOS-approved requests for records and compliance.
2. **Pending Review:** When HOD or HOS requests review, status = PendingReview. Employee must edit & resubmit. Status resets to Pending on resubmit.
3. **Computation Form:** Uses Tahoma font, 12pt throughout. Title "UWEZO FUND OVERSIGHT BOARD" is horizontal single-line in bold uppercase with heavy sans-serif font and dark grayscale textured styling.
4. **PDF Downloads:** All computation forms, approval letters, training certificates download as HTML-renderable files via anchor tag (triggerFileDownload). No print dialogs, no redirects, no new tabs.
5. **Dual Format Downloads:** Computation forms available in both PDF (.pdf extension) and Word (.doc extension) formats via dropdown on all portals (HOD, HOS, HR, Employee, Intern).
6. **Training Certificate:** Downloads as styled HTML with Tahoma font and Uwezo Fund branding. Not a .txt file.
7. **Self-referencing Employee:** supervisorId → Employee.id for reporting hierarchy.
8. **Composite Keys:** LeaveBalance [employeeId, policyId, fiscalYear], PayrollRecord [employeeId, month, year], TrainingEnrollment [programId, employeeId], TrainingEvaluation [programId, employeeId].
9. **Cascade Deletes:** Most child tables cascade on employee deletion to maintain referential integrity.
10. **Audit Trail:** All mutations tracked in AuditLog with old/new JSON values for compliance.
11. **Notifications:** Real-time bell counts driven by Notification table with read flag per user.
12. **Intern flag:** User.isIntern and Employee.isIntern flags for UI differentiation (leave entitlement, portal routing).
13. **Password Rules:** Enforced via frontend regex: min 8 chars, uppercase, lowercase, number, special character.
14. **No Casual Leave:** Casual leave removed from all leave types. Only Annual, Sick, Maternity/Paternity, Bereavement, Unpaid.
15. **Supporting Documents:** Only Sick Leave and Maternity Leave require mandatory supporting documents.
16. **HOD Cannot Approve Own Leave:** HOD leave goes directly to HOS. HOS cannot approve own leave — goes to HR for records only.
17. **HR Cannot Approve Leave:** HR Manager portal shows only HOS-approved requests. No approve button, no letter button. Only computation download and view.
18. **Edit & Resubmit:** Employees and interns can edit a PendingReview leave request directly from their leave page. On resubmit, status resets to Pending and review fields are cleared.
19. **HOD Approve Button:** Always shows "Approve → HOS" regardless of employee type.
20. **HOS Approve Button:** Shows "Approve → HR" to indicate forwarding to HR for records (not approval).
21. **Calendar Auto-Highlight:** LeaveCalendarTab shows both "Approved" and "HOS Approved" requests, automatically updating daily based on current date.
22. **Training Workflow:** HOD → HOS → Finance. HR does NOT approve training requests. HR only records and tracks.
23. **HOS Portal Actions:** No Part III button. No HR-approved comments column. Only Part II download + computation downloads (PDF/Word).
24. **View Button:** All View buttons open inline modal only. No print redirects, no new tabs.
25. **Digital Signature:** When HOS/CEO approves a leave or training request, a DigitalSignature record is created. The downloaded document includes a green "Digitally Authorized" block with HOS name, title, and approval date. All documents use Tahoma 12pt font.
26. **Performance Comments:** HOD, HOS, or HR can add PerformanceComment records linked to any performance item (workplan, KPI, IPI, quarterly target). Employees see unread comments as a purple notification banner on their performance page and inline on each item.
27. **HOD Account Creation:** Admin creates HOD accounts with username + initial password. HOD must change password on first login. Records in HODAccount. Promote/demote tracked in PromotionRecord.
28. **Password Reset Log:** All admin-initiated password resets are logged in PasswordResetLog for audit trail.
29. **Computation Button Placement:** Computation download button appears ONLY when status = HOSApproved OR Approved. It is shown in: (a) HR portal LeaveRequestsTab, (b) HOD portal Requests page, (c) Employee leave history tab, (d) Intern leave history tab. The button does NOT appear at any earlier stage — not for Pending, HOD Approved, or Pending Review. This is enforced in all four portals.
30. **Leave Computation Font:** All computation forms and approval letters use Tahoma font at 12pt for ALL content — body text, tables, signatures, notes, headers (including the note below the table).
31. **Training Digital Signature:** When HOS approves a training request, a DigitalSignature record is created with documentType='training'. The hosDigitalSignatureId field in TrainingRequest links to this record. The downloaded training approval document includes the green digital signature block.
32. **HOS Comment Visibility:** hosComment field is stored in both LeaveRequest and TrainingRequest. HR portal LeaveRequestsTab shows hosComment inline in the table. All relevant parties (employee, HOD, HR) can see the comment via the View modal and inline table display.
33. **Calendar Auto-Highlight Real-time:** Once HOS approves a leave request (status = HOSApproved), the approved dates appear immediately highlighted in both the HR Manager LeaveCalendarTab (shows both Approved and HOS Approved) and the individual employee/intern/HOD calendar. Green = HOS/Final Approved, colored = other approved. singleUserView prop filters to show only that user's own leaves.
34. **Live Leave Balance Calculation:** LeaveBalanceTab in HR portal uses useLeaveStore to fetch all approved requests and calls computeEmployeeBalance() for each employee. Balance is always recalculated from approved requests, NOT from static data. Individual portals (employee, intern, HOD) also show live balances.
35. **Admin HOD Creation:** System Administrator creates HOD accounts via AdminEmployeesTab > Add HOD Account section. Must provide: full name, email, username, initial password, phone (optional), department assignment. HOD must change password on first login. HODAccount record created. Department table updated with new hodId.
36. **Department Management:** Admin can create new departments (AdminEmployeesTab > Add Department) or change HOD assignment (AdminEmployeesTab > Change HOD). DepartmentHODAssignment tracks history of HOD assignments per department.
37. **Employee Promote/Demote:** Admin uses AdminEmployeesTab > Promote/Demote section. Each change logged in PromotionRecord with fromRole, toRole, effectiveDate, authorizedBy. Employee's type and department are updated immediately.
38. **Password Reset:** Admin uses AdminEmployeesTab > Reset Password or UserManagementTab force-password. Each reset logged in PasswordResetLog AND EmployeePasswordResetRequest. Employee must change password on next login (mustChangeOnNextLogin=true).
39. **Training Request Workflow (with Digital Signature):** Intern/Employee/HOD submits training request → HOD approves → HOS approves (creates DigitalSignature, sets hosDigitalSignatureId) → Finance approves. Computation form with HOS digital signature downloadable by HR and applicant after HOS approval.
40. **Composite DB Indexes:** All high-traffic query patterns covered: [role, isActive], [dept, role], [dept, isActive], [employeeType, isActive], [startDate, endDate] on LeaveRequest, [month, year] on PayrollRecord, [programId, employeeId] on TrainingEnrollment/TrainingEvaluation.
41. **HR Stamp/Signature Storage:** HR uploads scanned office stamp and signature images (base64). Stored in HROfficeConfig table with key='stamp' and key='signature'. Frontend uses localStorage for immediate access; synced to DB for persistence. Both are automatically embedded at the bottom of Leave Computation Forms downloaded after HOS approval.
42. **Shared Departmental Targets:** When HOD creates a SharedDepartmentTarget, it is automatically visible to all employees/interns in that department via the SharedDepartmentTarget table filtered by dept. Employees see these in their Work Plan section. Employees base their KPIs, quarterly targets, and IPIs on both org strategic goals and dept targets.
43. **Performance Visibility Matrix:** 
    - HOD: full view + can add comments on KPIs, Quarterly Targets, IPIs, PIPs, WorkPlans of ALL employees/interns in their department
    - HR Manager: full read-only view of ALL employee/intern KPIs, Quarterly Targets, IPIs, PIPs across all departments. Can add HR comments visible to the employee
    - HOS: full read-only view of all performance data
    - Employees/Interns: view ONLY their own performance items. Also see: (a) organization strategic goals (CompanyGoal), (b) their department's SharedDepartmentTarget set by HOD. Employees base their KPIs, Quarterly Targets, and IPIs on these org/dept goals
    - Comments: All comments (from HOD, HR, HOS) on an employee's items are visible to that employee with a notification banner. Employee cannot see other employees' comments.
    - PIP visibility: All PIPs visible to both HOD (for their dept) and HR Manager (all depts)
44. **AI PIP Generation:** PIPs can be (a) AI-suggested by HR/HOD based on low appraisal ratings or bottom 10%; (b) System-initiated form for employee; (c) Employee self-initiated. All PIPs visible to HOD and HR. PIPInitiation table tracks origin with aiGenerated flag.
45. **Computation Form Download Policy:** Computation form is available ONLY after HOS/CEO approves the request (status = HOSApproved or Approved). It becomes simultaneously available in ALL four portals: HR, HOD, Employee, Intern. The form is a compact 1-page document containing: (a) Uwezo Fund logo at top, (b) Ref No + Date, (c) Employee details, (d) Leave computation table, (e) HR signature + office stamp, (f) HOS digital authorization block. All within one A4 page using 10pt Tahoma font. LeaveRequest.computationAvailableAt is set when HOS approves.
46. **Computation Form Enhancements:** Tahoma 12pt throughout. Uwezo Fund official logo at top. HR stamp (scanned) in signature section. HR signature image above 'FOR: HEAD OF SECRETARIAT'. Optional — placeholder text if not uploaded.
47. **Auto-Generated Credentials on Employee Creation:** When HR/Admin creates an employee, the system auto-generates: (a) Employee ID = `UF{3digits}` for permanent/contract or `INT{3digits}` for interns; (b) Username = lowercase employee ID; (c) Temporary password = `{username}@{year}` e.g. `uf001@2026`. Password is SHA-256 hashed before storage.
48. **Welcome Email on Account Creation:** Upon creating any user/employee/HOD, the system calls the Supabase Edge Function `send-welcome-email` which sends a formatted HTML email containing username, temporary password, role, and department. Email uses Resend API (requires `RESEND_API_KEY` in edge function secrets). If unconfigured, credentials are displayed in the UI for manual sharing.
49. **First Login Password Change:** All newly created accounts have `is_first_login = true`. On first login, the user is redirected to the password setup page. After changing, `is_first_login = false` is persisted.
50. **HOD Account Creation:** Admin creates HOD accounts with: full name, email, username, initial password, phone, department. System creates both `hr_employees` + `hr_users` records with role='hod', updates `hr_departments.hod_id`, creates `hr_department_hod_assignments` record, creates `hr_hod_accounts` record, sends welcome email. HOD must change password on first login.
51. **Department Creation:** Admin creates departments via `hr_departments` table with name, code (3-5 chars), description. HOD can be assigned at creation time or later via `assignHODToDepartment()`.
52. **HOD Assignment History:** Every HOD assignment creates a record in `hr_department_hod_assignments`. Previous assignments are not deleted — `is_active=false`, `ended_at=now()`. Full audit history preserved.
53. **Promote/Demote:** Role changes logged in `hr_promotion_records` with from/to role, from/to dept, effective date, and authorizing admin. `hr_users.role` is updated immediately.
54. **Password Reset by Admin:** Admin resets via AdminEmployeesTab or UserManagementTab. New hash stored, `is_first_login=true` set, logged in `hr_password_reset_log`, welcome email resent with new credentials.
55. **Dynamic Departments:** Department list throughout the app is fetched from `hr_departments` (not hardcoded). New departments created by admin appear immediately in all dropdowns across the HRMS.
41. **UserRole Enum:** Added 'intern' as distinct role to UserRole enum for cleaner RBAC. isIntern flag on User/Employee retained for backward compatibility.