# HR Payroll Schema Alignment Backend path: `app/Modules/HRPayroll` Frontend path: `C:\Apache24\htdocs\railserp` ## Schema Ownership The HR Payroll module uses two schema areas: - `hr_payroll` owns HR transaction and payroll records. - `org` owns shared organization structure used by HR and other modules. Organization structure tables already exist in `org`, so they should not be duplicated into `hr_payroll`. Relevant `org` tables: - `org.departments` - `org.designations` - `org.locations` - `org.cost_centers` - `org.business_units` - `org.reporting_lines` - `org.user_departments` - `org.user_locations` Relevant `hr_payroll` tables: - `hr_payroll.attendance_logs` - `hr_payroll.employees` - `hr_payroll.leave_requests` - `hr_payroll.leave_types` - `hr_payroll.payroll_run_items` - `hr_payroll.payroll_runs` - `hr_payroll.shifts` ## Backend Fix Applied The Departments backend now keeps using `org.departments`, but no longer casts the UUID tenant context to `int`. This fixes the real backend mismatch: - `tenant_id` is a UUID in the database. - The middleware provides tenant IDs as UUID strings. - HR code must pass tenant IDs as strings. ## Department Frontend vs Current Table The RailsERP Departments screen expects: - Department Name - Department Code - Cost Center - Primary Location - Status - Description - Head of Department - Work Email / Work Phone - Annual Budget - Currency - Employee count - Active count Current `org.departments` provides: - `id` - `tenant_id` - `department_code` - `department_name` - `parent_department_id` - `manager_user_id` - `is_active` - soft-delete and audit timestamps Missing from `org.departments` or not directly linked: - Cost center - Primary location - Annual budget - Currency - Description Recommended structural change: - Add `cost_center_id bigint NULL REFERENCES org.cost_centers(id)` - Add `location_id bigint NULL REFERENCES org.locations(id)` - Add `annual_budget numeric(18,2) NULL` - Add `currency_code varchar(10) NULL DEFAULT 'GHS'` - Add `description text NULL` Migration added: - `database/migrations/20260508_000006_align_org_structure_with_hr_frontend.sql` Alternative: - Keep departments clean and create `org.department_profiles` with `department_id`, `cost_center_id`, `location_id`, `annual_budget`, `currency_code`, and `description`. The direct-column approach is simpler for this frontend screen. ## Positions Frontend vs Current Table The Positions screen maps naturally to `org.designations`. Current `org.designations` provides: - `id` - `tenant_id` - `department_id` - `designation_code` - `designation_name` - `description` - `is_active` Missing fields expected by the frontend: - Reports To - Salary Band - Salary Min - Salary Max - Planned Headcount - Open Positions Recommended structural change: - Add `reports_to_designation_id bigint NULL REFERENCES org.designations(id)` - Add `salary_band varchar(100) NULL` - Add `salary_min numeric(18,2) NULL` - Add `salary_max numeric(18,2) NULL` - Add `planned_headcount integer NOT NULL DEFAULT 0` - Add `open_positions integer NOT NULL DEFAULT 0` Migration added: - `database/migrations/20260508_000006_align_org_structure_with_hr_frontend.sql` ## HR Payroll Tables Added Existing `hr_payroll` tables cover the base of employees, leave, payroll, shifts, and attendance. The frontend has a wider HR suite, so this migration closes the first structural schema gap: - `database/migrations/20260508_000007_close_hr_schema_gap.sql` New tables added: - `hr_payroll.employee_emergency_contacts` - `hr_payroll.employee_leave_balances` - `hr_payroll.employee_documents` - `hr_payroll.employee_lifecycle_events` - `hr_payroll.employee_promotions` - `hr_payroll.employee_transfers` - `hr_payroll.disciplinary_cases` - `hr_payroll.training_programs` - `hr_payroll.employee_training_enrollments` - `hr_payroll.salary_structures` - `hr_payroll.salary_structure_components` - `hr_payroll.payroll_components` - `hr_payroll.employee_payroll_components` - `hr_payroll.payroll_adjustments` - `hr_payroll.employee_loans` - `hr_payroll.payslips` - `hr_payroll.payroll_currencies` - `hr_payroll.payroll_exchange_rates` - `hr_payroll.statutory_deduction_rules` - `hr_payroll.statutory_contribution_records` - `hr_payroll.attendance_devices` - `hr_payroll.attendance_device_logs` - `hr_payroll.attendance_device_mappings` - `hr_payroll.attendance_data_sources` - `hr_payroll.attendance_import_templates` - `hr_payroll.attendance_import_batches` - `hr_payroll.attendance_import_rows` - `hr_payroll.attendance_sync_jobs` - `hr_payroll.shift_assignments` - `hr_payroll.attendance_rules` - `hr_payroll.attendance_exceptions` - `hr_payroll.attendance_review_items` - `hr_payroll.attendance_adjustments` - `hr_payroll.attendance_approval_requests` - `hr_payroll.attendance_disciplinary_flags` - `hr_payroll.attendance_export_runs` - `hr_payroll.recruitment_jobs` - `hr_payroll.recruitment_candidates` - `hr_payroll.recruitment_candidate_stage_history` - `hr_payroll.recruitment_interviews` - `hr_payroll.recruitment_offers` - `hr_payroll.performance_review_cycles` - `hr_payroll.performance_reviews` - `hr_payroll.performance_review_categories` - `hr_payroll.performance_kpis` - `hr_payroll.employee_feedback` Seed added: - `database/seeds/20260508_seed_hr_foundation_a3f5d1e2.sql` The seed creates tenant defaults for currencies, leave types, shifts, payroll components, salary structures, statutory rules, attendance rules, attendance devices, attendance data sources, and performance review cycles. ## Backend Endpoints Added The HR backend now exposes CRUD endpoints for the new schema through whitelisted HR resources. Existing dedicated endpoints remain for employees, departments, positions, leave requests, payroll runs, shifts, and attendance logs. Canonical module endpoints use `/api/v1/hr-payroll/...`. Frontend-friendly aliases remain available under `/api/v1/hr/...`. New endpoint groups: - `/api/v1/hr/employees/documents` - `/api/v1/hr/employees/emergency-contacts` - `/api/v1/hr/employees/lifecycle-events` - `/api/v1/hr/leave/balances` - `/api/v1/hr/payroll/currencies` - `/api/v1/hr/payroll/exchange-rates` - `/api/v1/hr/payroll/components` - `/api/v1/hr/payroll/salary-structures` - `/api/v1/hr/payroll/salary-structure-components` - `/api/v1/hr/payroll/employee-components` - `/api/v1/hr/payroll/adjustments` - `/api/v1/hr/payroll/run-items` - `/api/v1/hr/payroll/loans` - `/api/v1/hr/payroll/payslips` - `/api/v1/hr/payroll/statutory-rules` - `/api/v1/hr/payroll/statutory-contributions` - `/api/v1/hr/attendance/devices` - `/api/v1/hr/attendance/device-logs` - `/api/v1/hr/attendance/device-mappings` - `/api/v1/hr/attendance/data-sources` - `/api/v1/hr/attendance/import-templates` - `/api/v1/hr/attendance/import-batches` - `/api/v1/hr/attendance/import-rows` - `/api/v1/hr/attendance/sync-jobs` - `/api/v1/hr/attendance/shift-assignments` - `/api/v1/hr/attendance/rules` - `/api/v1/hr/attendance/exceptions` - `/api/v1/hr/attendance/review-items` - `/api/v1/hr/attendance/adjustments` - `/api/v1/hr/attendance/approval-requests` - `/api/v1/hr/attendance/disciplinary-flags` - `/api/v1/hr/attendance/export-runs` - `/api/v1/hr/recruitment/jobs` - `/api/v1/hr/recruitment/candidates` - `/api/v1/hr/recruitment/stage-history` - `/api/v1/hr/recruitment/interviews` - `/api/v1/hr/recruitment/offers` - `/api/v1/hr/lifecycle/promotions` - `/api/v1/hr/lifecycle/transfers` - `/api/v1/hr/lifecycle/disciplinary-cases` - `/api/v1/hr/lifecycle/training-programs` - `/api/v1/hr/lifecycle/training-enrollments` - `/api/v1/hr/performance/cycles` - `/api/v1/hr/performance/reviews` - `/api/v1/hr/performance/review-categories` - `/api/v1/hr/performance/kpis` - `/api/v1/hr/performance/feedback` ## Frontend HR Pages Reviewed The RailsERP frontend currently has HR pages under: - `src/pages/hr` - `src/pages/hr/leave` - `src/pages/hr/payroll` - `src/pages/hr/statutory` - `src/pages/hr/recruitment` - `src/pages/hr/lifecycle` - `src/pages/hr/performance` - `src/pages/hr/self-service` - `src/pages/hr/attendance` - HR approval pages under `src/pages/approvals/LeaveApprovals.tsx` and `src/pages/approvals/PayrollApprovals.tsx` ## Additional Schema Needs By Area ### Core Employee Profile The existing `hr_payroll.employees` table is the base employee record. The frontend also expects profile subrecords: - `employee_emergency_contacts` for emergency contact name, relationship, phone, email, and address. - `employee_documents` for HR uploads such as identification, credentials, statutory documents, financial documents, and HR forms. Store file metadata here and link the binary/file object to the existing `files` schema if available. - `employee_lifecycle_events` for a unified timeline of hire, promotion, transfer, salary change, role change, and termination. ### Leave The current tables `leave_types` and `leave_requests` cover the base workflow, but the leave screens also need balances, calendars, and approval metadata: - `employee_leave_balances` for annual, sick, maternity, paternity, used, carried-over, and available balances per employee/leave type/year. - Add approval fields to `leave_requests` or model them through the shared `workflow` schema: approved/rejected by, approval date, rejection reason, escalation status. ### Payroll The current `payroll_runs` and `payroll_run_items` cover generated payroll output. The payroll setup screens need configuration tables before payroll can be calculated: - `salary_structures` for pay band, currency, basic salary min/max, description, status. - `salary_structure_components` to attach components to salary structures. - `payroll_components` for earnings, deductions, statutory components, calculation method, default value, taxable flag, and status. - `employee_payroll_components` for recurring employee-specific allowances/deductions. - `payroll_adjustments` for bonuses, allowances, overtime, backpay, promotion arrears, missed increments, leave encashment, gratuity, and one-off corrections. - `employee_loans` for loan requests, approval status, repayment schedule summary, monthly deduction, amount repaid, and settlement state. - `payslips` for published payslip records linked to payroll run items and downloadable files. - `payroll_currencies` and `payroll_exchange_rates` for currency settings and historical exchange-rate changes. - `statutory_deduction_rules` for PAYE, SSNIT, pension, and other statutory rules. - `statutory_contribution_records` for calculated statutory contributions by employee/payroll run. ### Attendance The current `shifts` and `attendance_logs` tables cover only the minimum clock record. The attendance module has a full device/import/review/settings suite: - `attendance_devices` for biometric/RFID/API/GPS/manual devices, serial number, firmware, IP, status, location, and last sync. - `attendance_device_logs` for heartbeat, sync, maintenance, firmware, and error logs per device. - `attendance_device_mappings` for mapping external device/attendant IDs to employees. - `attendance_data_sources` for CSV, SQL, API, and file/SFTP feeds, connection metadata, schedules, and auto-sync flag. - `attendance_import_templates` for reusable source-to-target field mappings and validation rules. - `attendance_import_batches` for each import/sync run. - `attendance_import_rows` for raw/validated imported records and row-level errors. - `attendance_sync_jobs` for queued/running/completed/failed sync attempts. - `shift_assignments` for employee-to-shift schedules. - `attendance_rules` for grace periods, overtime, absence, early departure, duplicate detection, geofence, timezone, and source parsing policies. - `attendance_exceptions` for late arrivals, early departures, missed clock-outs, absences, duplicate punches, and device failures. - `attendance_review_items` for HR review queue decisions and severity/status tracking. - `attendance_adjustments` for manual corrections and applied clock changes. - `attendance_approval_requests` for attendance correction, manual override, overtime approval, and absence override requests. - `attendance_disciplinary_flags` for attendance-generated HR flags. - `attendance_export_runs` for CSV/XLSX/JSON/payroll export history. Attendance analytics and intelligence pages can initially be computed from these operational tables. Persisted analytics tables are optional later if performance requires snapshots. ### Recruitment The recruitment frontend is not represented in current `hr_payroll` or `org` tables: - `recruitment_jobs` for job opening title, department, location, open positions, salary range, salary band, employment type, status, posted/closing dates, hiring manager, description, and requirements. - `recruitment_candidates` for candidate profile, source, contact info, applied role, current stage, rating, notes, and resume file link. - `recruitment_candidate_stage_history` for candidate movement through Applied, Screening, Interview, Offer, and Hired. - `recruitment_interviews` for interview scheduling, interview type, interviewer/panel, date/time, status, score, and notes. - `recruitment_offers` for offer package, status, sent/expiry/response dates, salary, benefits, and acceptance state. ### Lifecycle Lifecycle pages should use normalized event tables rather than only updating the employee row: - `employee_promotions` for old/new role, pay band, salary, effective date, reason, performance score, and approval status. - `employee_transfers` for department/location/role transfer details, effective date, reason, and approval status. - `disciplinary_cases` for type, severity, incident date, investigator, status, action taken, appeal state, and notes. - `training_programs` for training catalog, category, date range, capacity, status, trainer, and budget. - `employee_training_enrollments` for employee participation, completion, score/certification, and feedback. ### Performance Performance pages require their own review model: - `performance_review_cycles` for cycle name, period, date range, status, and tenant-wide review setup. - `performance_reviews` for employee, reviewer, cycle, status, overall score, supervisor comment, next review date, and promotion recommendation. - `performance_review_categories` for weighted category scores and comments. - `performance_kpis` for assigned KPI targets, scores, actuals, weights, and review period. - `employee_feedback` for peer/supervisor/self feedback records, visibility, sentiment/rating, and comments. ### Shared Schemas To Reuse Do not duplicate these concerns inside `hr_payroll` unless the shared schemas are missing: - Use `org` for departments, designations/positions, branches, locations, warehouses, cost centers, business units, reporting lines, and user org mappings. - Use `workflow` for generic approval routing where practical, especially leave, payroll, promotion, transfer, loan, attendance adjustment, and document review approvals. - Use `files` for uploaded document binaries and generated PDFs. HR tables should store category/status/ownership metadata and a `file_id`/reference. - Use `audit` for immutable decision trails and status-change history. - Use `notify` for notifications triggered by approvals, payroll publication, attendance issues, and document review outcomes. ## Recommended Build Order For backend development, implement in this order: 1. Core HR: employees, emergency contacts, documents, departments, designations, locations, cost centers. 2. Leave: leave types, leave requests, leave balances, approval metadata. 3. Payroll setup: salary structures, payroll components, employee payroll components, adjustments, currencies/rates. 4. Payroll execution: payroll runs, payroll run items, statutory contribution records, payslips. 5. Attendance core: shifts, shift assignments, attendance logs, devices, device mappings. 6. Attendance operations: data sources, import templates, import batches/rows, exceptions, review items, adjustments. 7. Recruitment, lifecycle, performance, and self-service enhancements.