HRMS Database Schema Guide

Employee Core Table

The employees table is the central entity. Fields include employee_id (PK), employee_code (unique alphanumeric identifier), first_name, last_name, date_of_birth, gender, phone, personal_email, work_email, department_id (FK), position_id (FK), manager_id (self-referencing FK for reporting hierarchy), employment_type (full-time/part-time/contract/intern), date_of_joining, confirmation_date, exit_date, and employment_status. An employee_documents table stores references to offer letters, contracts, and ID proofs, each with an expiry_date field to trigger renewal reminders.

Department and Position Hierarchy

The departments table organizes the company structure: department_id (PK), department_name, department_code, head_of_department (FK to employee_id), parent_department_id (self-referencing FK), and cost_center_code. The positions table defines job roles: position_id (PK), position_title, job_grade, minimum_salary, maximum_salary, and required_qualifications. An employee_positions junction table tracks position history: employee_id (FK), position_id (FK), effective_date, end_date, and is_current. This design supports employees who change roles over time while maintaining a complete career history.

Attendance and Time Tracking

The attendance table records daily check-in/check-out: attendance_id (PK), employee_id (FK), date, check_in_time, check_out_time, total_hours_worked, overtime_hours, and status. An attendance_logs table stores raw biometric or RFID punches for audit purposes. For shift-based organizations, a shifts table (shift_id, shift_name, start_time, end_time, grace_period_minutes) combines with an employee_shifts assignment table. Late arrivals and early departures are calculated by comparing actual check-in/out times against the assigned shift window.

Leave Management

The leave_types table lists available leave categories: leave_type_id (PK), leave_name (Annual/Sick/Maternity/Paternity/Compassionate/Unpaid), is_paid, and carry_forward_allowed. The employee_leave_balance table tracks entitlement: balance_id (PK), employee_id (FK), leave_type_id (FK), fiscal_year, total_days_allocated, days_used, days_pending_approval, and days_carried_forward. The leave_applications table handles requests: leave_application_id (PK), employee_id (FK), leave_type_id (FK), start_date, end_date, total_days, reason, status, and approved_by.

Payroll Schema

The payroll schema is the most financially sensitive part. The payroll_cycles table defines periods: cycle_id (PK), cycle_name, start_date, end_date, payment_date, and status. The salary_structures table defines components: structure_id (PK), employee_id (FK), effective_date, basic_pay, hra, conveyance_allowance, medical_allowance, special_allowance, pf_deduction, professional_tax, income_tax, and net_salary. The payroll_runs table stores generated payslips: payroll_run_id (PK), employee_id (FK), cycle_id (FK), gross_pay, total_deductions, net_pay, overtime_pay, reimbursement, leave_deductions, and payment_status. A payroll_audit table logs every change to salary structures for compliance.

Performance Management

The performance_reviews table records appraisals: review_id (PK), employee_id (FK), reviewer_id (FK), review_period, review_date, overall_rating, and summary. A review_goals table tracks objectives: goal_id (PK), review_id (FK), goal_description, weight_percent, self_rating, manager_rating, and comments. The review_skills table evaluates competencies: skill_id (PK), review_id (FK), skill_name, skill_category, and rating. Performance scores can feed into bonus calculations and promotion recommendations.

Recruitment and Onboarding

The job_openings table lists positions: opening_id (PK), department_id (FK), position_title, required_skills, experience_required, openings_count, filled_count, status, and posted_date. The candidates table tracks applicants: candidate_id (PK), first_name, last_name, email, phone, resume_url, source, current_company, current_ctc, expected_ctc, notice_period, and status. An interviews table schedules and scores: interview_id (PK), candidate_id (FK), job_opening_id (FK), interviewer_id (FK), interview_date, interview_type, round_number, rating, and feedback.

HRMS Database Best Practices

Implement row-level security for payroll and performance data — HR managers should only see their reporting chain. Use soft deletes on all employee-related tables to maintain audit trails. Encrypt sensitive columns like salary, bank account numbers, and personal addresses at the database level. Index employee_code and work_email for fast lookups. For organizations with thousands of employees, partition attendance_logs by month and archive records older than 2 years. Always use prepared statements for payroll calculations. Maintain a separate audit_trail table that records who changed what and when for compliance with labor regulations.

Full System Prompt — Copy & Paste

Complete HRMS design prompt. Covers database, API, authentication, business logic, and frontend. Paste this into the Business Prompt Builder.

Design and build a complete Human Resource Management System (HRMS) with the following components. Use Node.js/Express for the backend, PostgreSQL for the database, and React for the frontend. All API endpoints must use JWT authentication with role-based access control.

--- DATABASE SCHEMA ---

Use PostgreSQL with UUID primary keys, foreign key constraints, indexes on all FK columns, and soft deletes on all employee-related tables.

1. departments: department_id (PK), department_name, department_code (UNIQUE), head_of_department (FK to employees), parent_department_id (self-referencing FK), cost_center_code, is_active, created_at, updated_at

2. positions: position_id (PK), position_title, job_grade, minimum_salary, maximum_salary, required_qualifications, is_active, created_at

3. employees: employee_id (PK), employee_code (UNIQUE), first_name, last_name, date_of_birth, gender, phone, personal_email, work_email (UNIQUE), department_id (FK), position_id (FK), manager_id (self-referencing FK), employment_type (ENUM: full-time/part-time/contract/intern), date_of_joining, confirmation_date, exit_date, employment_status (ENUM: active/on_notice/terminated/resigned), is_active, created_at, updated_at

4. employee_documents: document_id (PK), employee_id (FK), document_type (ENUM: offer_letter/contract/id_proof/address_proof), document_name, file_path, expiry_date, created_at

5. employee_positions: emp_position_id (PK), employee_id (FK), position_id (FK), effective_date, end_date, is_current, created_at

6. attendance: attendance_id (PK), employee_id (FK), date, check_in_time, check_out_time, total_hours_worked, overtime_hours, status (ENUM: present/absent/late/half_day/leave), created_at

7. attendance_logs: log_id (PK), employee_id (FK), punch_time, punch_type (ENUM: check_in/check_out), device_id, created_at

8. shifts: shift_id (PK), shift_name, start_time, end_time, grace_period_minutes, is_active, created_at

9. employee_shifts: emp_shift_id (PK), employee_id (FK), shift_id (FK), effective_date, end_date, is_current

10. leave_types: leave_type_id (PK), leave_name (ENUM: annual/sick/maternity/paternity/compassionate/unpaid), is_paid, carry_forward_allowed, max_carry_forward_days, is_active, created_at

11. leave_balances: balance_id (PK), employee_id (FK), leave_type_id (FK), fiscal_year, total_days_allocated, days_used, days_pending_approval, days_carried_forward, created_at, updated_at

12. leave_applications: leave_application_id (PK), employee_id (FK), leave_type_id (FK), start_date, end_date, total_days, reason, status (ENUM: pending/approved/rejected/cancelled), approved_by (FK to employees), approval_date, created_at

13. payroll_cycles: cycle_id (PK), cycle_name, start_date, end_date, payment_date, status (ENUM: draft/processing/completed/paid), created_at, updated_at

14. salary_structures: structure_id (PK), employee_id (FK), effective_date, basic_pay, hra, conveyance_allowance, medical_allowance, special_allowance, pf_deduction, professional_tax, income_tax, net_salary, is_active, created_at

15. payroll_runs: payroll_run_id (PK), employee_id (FK), cycle_id (FK), gross_pay, total_deductions, net_pay, overtime_pay, reimbursement, leave_deductions, payment_status (ENUM: pending/processed/paid), processed_at, created_at

16. performance_reviews: review_id (PK), employee_id (FK), reviewer_id (FK to employees), review_period, review_date, overall_rating, summary, created_at, updated_at

17. review_goals: goal_id (PK), review_id (FK), goal_description, weight_percent, self_rating, manager_rating, comments, created_at

18. review_skills: skill_id (PK), review_id (FK), skill_name, skill_category, rating, comments, created_at

19. job_openings: opening_id (PK), department_id (FK), position_title, required_skills, experience_required, openings_count, filled_count, status (ENUM: open/on_hold/closed), posted_date, created_at

20. candidates: candidate_id (PK), first_name, last_name, email, phone, resume_url, source, current_company, current_ctc, expected_ctc, notice_period, status (ENUM: new/screening/interview/offer/hired/rejected), created_at, updated_at

21. interviews: interview_id (PK), candidate_id (FK), job_opening_id (FK), interviewer_id (FK to employees), interview_date, interview_type (ENUM: phone/technical/hr/panel), round_number, rating, feedback, status (ENUM: scheduled/completed/cancelled), created_at

--- REST API ENDPOINTS ---

Employees:
- POST /api/employees — add employee (generate employee_code auto-increment)
- GET /api/employees — list employees (search by name/code, filter by department/status/type)
- GET /api/employees/:id — get employee details with position history
- PUT /api/employees/:id — update employee info
- GET /api/employees/:id/documents — list employee documents
- POST /api/employees/:id/documents — upload document
- GET /api/employees/org-chart — org chart tree structure

Attendance:
- POST /api/attendance/check-in — record check-in (validate against assigned shift)
- POST /api/attendance/check-out — record check-out (calculate total_hours_worked and overtime)
- GET /api/attendance — list attendance records (filter by employee, date range, status)
- GET /api/attendance/summary — monthly attendance summary (present/absent/late days)
- POST /api/attendance/bulk — bulk attendance update (for HR)

Leave:
- POST /api/leave/apply — submit leave application (validate against balance)
- GET /api/leave/applications — list applications (employee sees own, HR sees all)
- PUT /api/leave/applications/:id/approve — approve leave (update balance: days_used += total_days)
- PUT /api/leave/applications/:id/reject — reject leave
- GET /api/leave/balance/:employeeId — get leave balance for all leave types
- GET /api/leave/types — list available leave types

Payroll:
- POST /api/payroll/cycles — create payroll cycle
- POST /api/payroll/run — run payroll for a cycle (calculate gross_pay, deductions, net_pay for all active employees)
- GET /api/payroll/runs — list payroll runs (filter by cycle, status)
- GET /api/payroll/runs/:id — get payslip details
- PUT /api/payroll/runs/:id/process — mark as processed
- PUT /api/payroll/runs/:id/pay — mark as paid
- GET /api/salary-structures/:employeeId — get salary structure
- PUT /api/salary-structures/:employeeId — update salary structure

Performance:
- POST /api/performance/reviews — create review cycle
- GET /api/performance/reviews — list reviews (filter by employee, period)
- PUT /api/performance/reviews/:id — update review with ratings and summary
- POST /api/performance/reviews/:id/goals — add goals to review
- POST /api/performance/reviews/:id/skills — add skill evaluations

Recruitment:
- POST /api/job-openings — post job opening
- GET /api/job-openings — list openings (filter by department, status)
- PUT /api/job-openings/:id — update opening
- POST /api/candidates — add candidate (apply to opening)
- GET /api/candidates — list candidates (filter by status, opening)
- PUT /api/candidates/:id/status — update candidate status (new/screening/interview/offer/hired/rejected)
- POST /api/interviews — schedule interview
- PUT /api/interviews/:id — update interview with rating and feedback

Departments:
- POST /api/departments — add department
- GET /api/departments — list departments as tree structure
- PUT /api/departments/:id — update department

--- AUTHENTICATION & ROLES ---

JWT-based authentication with these roles:
- admin: full access to all modules, salary structures, system settings
- hr_manager: manage employees, attendance, leave approvals, payroll processing, recruitment
- manager: view team attendance, approve team leave requests, submit team performance reviews
- employee: self-service — view own attendance, apply for leave, view own payslips, view org chart

--- BUSINESS LOGIC ---

1. Attendance tracking: Employee check-in validates against assigned shift start_time. If check_in_time > start_time + grace_period_minutes, status = late. Check-out calculates total_hours_worked. If total_hours_worked > shift duration, overtime_hours = total_hours_worked - shift duration.

2. Leave approval workflow: Employee applies -> status = pending. Manager approves -> status = approved, days_used incremented, days_pending_approval decremented. If insufficient balance, reject with reason. Annual leaves carry forward only if carry_forward_allowed = true and days_carried_forward < max_carry_forward_days.

3. Payroll calculation: gross_pay = basic_pay + hra + conveyance_allowance + medical_allowance + special_allowance + overtime_pay + reimbursement. total_deductions = pf_deduction + professional_tax + income_tax + leave_deductions. net_pay = gross_pay - total_deductions. Payroll runs process all active employees in one cycle atomically.

4. Performance scoring: overall_rating = weighted average of review_goals (each goal has weight_percent). Skills are rated independently. Reviewer and self-rating are stored separately for calibration.

5. Recruitment pipeline: candidates move through status flow: new -> screening -> interview -> offer -> hired (or rejected at any stage). When hired, automatically create employee record from candidate data. Update job_openings filled_count.

6. Document expiry alerts: When employee_documents.expiry_date is within 30 days, flag in dashboard. Contracts and ID proofs trigger renewal reminders.

--- FRONTEND PAGES ---

1. Employee Self-Service Portal: Dashboard with today's attendance status, pending leave requests, recent payslips. Apply for leave form, view attendance history, download payslips.

2. HR Dashboard: Company headcount, department-wise strength, attendance heatmap, pending leave approvals, upcoming contract expirations, recruitment pipeline summary.

3. Attendance Management: Calendar view of attendance, check-in/out buttons, bulk attendance edit, shift assignment, attendance reports (daily/monthly).

4. Leave Management: Leave calendar, application form with balance check, approval queue for managers, leave reports (team-wise, type-wise).

5. Payroll View: Payroll cycle list, run payroll button, payslip preview, salary structure editor, tax deduction calculator, payment status tracking.

6. Performance Reviews: Review cycle management, goal setting interface, skill rating forms, performance comparison charts, 360-degree feedback view.

7. Recruitment Pipeline: Kanban board for candidates (new/screening/interview/offer/hired), job opening management, interview scheduler, candidate detail with resume viewer.

Database Schema Prompt — Copy & Paste

Database-only prompt for generating the complete HRMS schema.

Design a complete Human Resource Management System (HRMS) database schema with the following tables and relationships. Use PostgreSQL syntax with UUID primary keys, foreign key constraints, and indexes on all FK columns.

1. departments: department_id (PK), department_name, department_code (UNIQUE), head_of_department (FK to employees), parent_department_id (self-referencing FK), cost_center_code, is_active, created_at, updated_at

2. positions: position_id (PK), position_title, job_grade, minimum_salary, maximum_salary, required_qualifications, is_active, created_at

3. employees: employee_id (PK), employee_code (UNIQUE), first_name, last_name, date_of_birth, gender, phone, personal_email, work_email (UNIQUE), department_id (FK), position_id (FK), manager_id (self-referencing FK), employment_type (ENUM: full-time/part-time/contract/intern), date_of_joining, confirmation_date, exit_date, employment_status (ENUM: active/on_notice/terminated/resigned), is_active, created_at, updated_at

4. employee_documents: document_id (PK), employee_id (FK), document_type (ENUM: offer_letter/contract/id_proof/address_proof), document_name, file_path, expiry_date, created_at

5. employee_positions: emp_position_id (PK), employee_id (FK), position_id (FK), effective_date, end_date, is_current, created_at

6. attendance: attendance_id (PK), employee_id (FK), date, check_in_time, check_out_time, total_hours_worked, overtime_hours, status (ENUM: present/absent/late/half_day/leave), created_at

7. attendance_logs: log_id (PK), employee_id (FK), punch_time, punch_type (ENUM: check_in/check_out), device_id, created_at

8. shifts: shift_id (PK), shift_name, start_time, end_time, grace_period_minutes, is_active, created_at

9. employee_shifts: emp_shift_id (PK), employee_id (FK), shift_id (FK), effective_date, end_date, is_current

10. leave_types: leave_type_id (PK), leave_name (ENUM: annual/sick/maternity/paternity/compassionate/unpaid), is_paid, carry_forward_allowed, max_carry_forward_days, is_active, created_at

11. leave_balances: balance_id (PK), employee_id (FK), leave_type_id (FK), fiscal_year, total_days_allocated, days_used, days_pending_approval, days_carried_forward, created_at, updated_at

12. leave_applications: leave_application_id (PK), employee_id (FK), leave_type_id (FK), start_date, end_date, total_days, reason, status (ENUM: pending/approved/rejected/cancelled), approved_by (FK to employees), approval_date, created_at

13. payroll_cycles: cycle_id (PK), cycle_name, start_date, end_date, payment_date, status (ENUM: draft/processing/completed/paid), created_at, updated_at

14. salary_structures: structure_id (PK), employee_id (FK), effective_date, basic_pay, hra, conveyance_allowance, medical_allowance, special_allowance, pf_deduction, professional_tax, income_tax, net_salary, is_active, created_at

15. payroll_runs: payroll_run_id (PK), employee_id (FK), cycle_id (FK), gross_pay, total_deductions, net_pay, overtime_pay, reimbursement, leave_deductions, payment_status (ENUM: pending/processed/paid), processed_at, created_at

16. performance_reviews: review_id (PK), employee_id (FK), reviewer_id (FK to employees), review_period, review_date, overall_rating, summary, created_at, updated_at

17. review_goals: goal_id (PK), review_id (FK), goal_description, weight_percent, self_rating, manager_rating, comments, created_at

18. review_skills: skill_id (PK), review_id (FK), skill_name, skill_category, rating, comments, created_at

19. job_openings: opening_id (PK), department_id (FK), position_title, required_skills, experience_required, openings_count, filled_count, status (ENUM: open/on_hold/closed), posted_date, created_at

20. candidates: candidate_id (PK), first_name, last_name, email, phone, resume_url, source, current_company, current_ctc, expected_ctc, notice_period, status (ENUM: new/screening/interview/offer/hired/rejected), created_at, updated_at

21. interviews: interview_id (PK), candidate_id (FK), job_opening_id (FK), interviewer_id (FK to employees), interview_date, interview_type (ENUM: phone/technical/hr/panel), round_number, rating, feedback, status (ENUM: scheduled/completed/cancelled), created_at

Add the following indexes: idx_employees_department ON employees(department_id), idx_employees_manager ON employees(manager_id), idx_employees_code ON employees(employee_code), idx_employees_email ON employees(work_email), idx_attendance_employee_date ON attendance(employee_id, date), idx_leave_app_employee ON leave_applications(employee_id), idx_payroll_employee ON payroll_runs(employee_id), idx_payroll_cycle ON payroll_runs(cycle_id), idx_performance_employee ON performance_reviews(employee_id), idx_candidates_status ON candidates(status). Add CHECK constraints for positive salary amounts, valid date ranges (end_date > start_date), and unique constraint on employees(employee_code).

Accent Color