-- ===================================================================== -- PAYROLL MODULE SCHEMA -- Designed to sit alongside your existing tables: -- employee, groups, users, attendance_monthly_summaries, -- load_deductions, payroll_adjustments -- Run this once against your existing database. -- ===================================================================== -- --------------------------------------------------------------------- -- 1. STATUTORY SETTINGS (Pension, NHF, NHIS %, rent relief, tax-free -- threshold). Kept in DB (not hardcoded) so you can update them the -- day the law changes without touching code. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS statutory_settings ( id INT AUTO_INCREMENT PRIMARY KEY, setting_key VARCHAR(60) NOT NULL UNIQUE, setting_value DECIMAL(14,2) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, description VARCHAR(255) DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO statutory_settings (setting_key, setting_value, is_active, description) VALUES ('pension_employee_pct', 8.00, 1, 'Employee pension contribution % (deducted from pay)'), ('pension_employer_pct', 10.00, 1, 'Employer pension contribution % (not deducted, for records)'), ('nhf_pct', 2.50, 1, 'National Housing Fund % of basic salary'), ('nhis_pct', 5.00, 1, 'NHIS contribution % (adjust to your scheme)'), ('rent_relief_pct', 20.00, 1, 'Rent relief: % of annual rent paid, allowable against tax'), ('rent_relief_cap', 500000.00, 1, 'Rent relief maximum allowable per year (NGN)'), ('paye_tax_free_threshold', 800000.00, 1, 'Annual income exempt from PAYE (NGN)') ON DUPLICATE KEY UPDATE setting_key = setting_key; -- --------------------------------------------------------------------- -- 2. PAYE TAX BANDS (Nigeria Tax Act 2025, effective 1 Jan 2026) -- Stored as rows so future reforms only need new rows, not new code. -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS tax_bands ( id INT AUTO_INCREMENT PRIMARY KEY, effective_year INT NOT NULL, band_order INT NOT NULL, min_amount DECIMAL(14,2) NOT NULL, max_amount DECIMAL(14,2) DEFAULT NULL, -- NULL = no upper limit rate DECIMAL(5,2) NOT NULL, -- percentage label VARCHAR(100) DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, UNIQUE KEY uniq_year_band (effective_year, band_order) ) ENGINE=InnoDB; INSERT INTO tax_bands (effective_year, band_order, min_amount, max_amount, rate, label) VALUES (2026, 1, 0, 800000, 0.00, 'Tax-free threshold'), (2026, 2, 800000.01, 3000000, 15.00, 'First band above threshold'), (2026, 3, 3000000.01, 12000000, 18.00, 'Second band'), (2026, 4, 12000000.01, 25000000, 21.00, 'Third band'), (2026, 5, 25000000.01, 50000000, 23.00, 'Fourth band'), (2026, 6, 50000000.01, NULL, 25.00, 'Top band') ON DUPLICATE KEY UPDATE min_amount = VALUES(min_amount), max_amount = VALUES(max_amount), rate = VALUES(rate), label = VALUES(label); -- --------------------------------------------------------------------- -- 3. PAY COMPONENTS (master catalogue: Basic Salary, Housing Allowance, -- Transport, Bonus, PAYE, Pension, NHF, NHIS, etc.) -- is_default = 1 -> auto-applied to every employee unless removed -- is_statutory = 1 -> system-calculated, not manually editable per line -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS payroll_pay_components ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, code VARCHAR(50) NOT NULL UNIQUE, type ENUM('earning','deduction') NOT NULL, category ENUM('statutory','allowance','bonus','deduction_other','custom') NOT NULL DEFAULT 'custom', is_statutory TINYINT(1) NOT NULL DEFAULT 0, is_taxable TINYINT(1) NOT NULL DEFAULT 1, is_default TINYINT(1) NOT NULL DEFAULT 0, calculation_type ENUM('fixed','percentage_of_gross') NOT NULL DEFAULT 'fixed', percentage_value DECIMAL(5,2) DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, display_order INT NOT NULL DEFAULT 0, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO payroll_pay_components (name, code, type, category, is_statutory, is_taxable, is_default, calculation_type, display_order) VALUES ('Basic Salary', 'basic_salary', 'earning', 'allowance', 0, 1, 1, 'fixed', 1), ('Housing Allowance', 'housing_allowance', 'earning', 'allowance', 0, 1, 1, 'fixed', 2), ('Transport Allowance', 'transport_allowance', 'earning', 'allowance', 0, 1, 0, 'fixed', 3), ('PAYE Tax', 'paye_tax', 'deduction', 'statutory', 1, 0, 1, 'fixed', 90), ('Pension (Employee)', 'pension_employee', 'deduction', 'statutory', 1, 0, 1, 'fixed', 91), ('NHF', 'nhf', 'deduction', 'statutory', 1, 0, 1, 'fixed', 92), ('NHIS', 'nhis', 'deduction', 'statutory', 1, 0, 1, 'fixed', 93) ON DUPLICATE KEY UPDATE code = code; -- --------------------------------------------------------------------- -- 4. EMPLOYEE-LEVEL PAY COMPONENTS (individual salary, rent, and any -- added/removed component per employee -- highest precedence) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS employee_pay_components ( id INT AUTO_INCREMENT PRIMARY KEY, employee_id INT NOT NULL, component_id INT NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, -- 0 = explicitly removed for this employee effective_from DATE DEFAULT NULL, effective_to DATE DEFAULT NULL, updated_by INT DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_employee_component (employee_id, component_id), KEY idx_employee (employee_id), CONSTRAINT fk_epc_component FOREIGN KEY (component_id) REFERENCES payroll_pay_components(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 4b. EMPLOYEE TAX PROFILE (annual rent actually paid by the employee, -- used only for the PAYE rent-relief calculation. This is -- deliberately separate from any "Rent Allowance" pay component -- -- that's salary the employer pays the employee; this is what the -- employee pays their landlord, which is what the law cares about.) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS employee_tax_profile ( employee_id INT NOT NULL PRIMARY KEY, annual_rent_paid DECIMAL(14,2) NOT NULL DEFAULT 0, rent_receipt_reference VARCHAR(150) DEFAULT NULL, updated_by INT DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 4c. EMPLOYEE SALARY BASE (the single "annual gross salary" figure an -- admin enters per employee. Used to auto-calculate any pay -- component configured as a percentage of gross -- e.g. set Basic -- Salary = 40% of gross, Housing = 30%, Transport = 15% once in -- payroll_pay_components, then just enter each employee's gross -- here and those component amounts fill in automatically.) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS employee_salary_base ( employee_id INT NOT NULL PRIMARY KEY, annual_gross_salary DECIMAL(14,2) NOT NULL DEFAULT 0, updated_by INT DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 5. PAY-GROUP-LEVEL COMPONENTS (applies to everyone in a group, -- overridden by employee-level rows above) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS group_pay_components ( id INT AUTO_INCREMENT PRIMARY KEY, group_id INT NOT NULL, component_id INT NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, is_active TINYINT(1) NOT NULL DEFAULT 1, updated_by INT DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_group_component (group_id, component_id), CONSTRAINT fk_gpc_component FOREIGN KEY (component_id) REFERENCES payroll_pay_components(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 6. PAYROLL CYCLES -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS payroll_cycles ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, -- e.g. "January 2026" payroll_month INT NOT NULL, -- matches attendance_monthly_summaries.payroll_month payroll_year INT NOT NULL, -- matches attendance_monthly_summaries.payroll_year start_date DATE NOT NULL, end_date DATE NOT NULL, status ENUM('draft','processing','pending_approval','approved','rejected','paid') NOT NULL DEFAULT 'draft', current_stage_order INT NOT NULL DEFAULT 0, workflow_id INT DEFAULT NULL, created_by INT DEFAULT NULL, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, UNIQUE KEY uniq_month_year (payroll_month, payroll_year) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 7. PAYROLL RUNS (one row per employee per cycle -- the computed payslip) -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS payroll_payslip_runs ( id INT AUTO_INCREMENT PRIMARY KEY, payroll_cycle_id INT NOT NULL, employee_id INT NOT NULL, group_id INT DEFAULT NULL, gross_earnings DECIMAL(14,2) NOT NULL DEFAULT 0, taxable_income DECIMAL(14,2) NOT NULL DEFAULT 0, rent_relief_applied DECIMAL(14,2) NOT NULL DEFAULT 0, paye_tax DECIMAL(14,2) NOT NULL DEFAULT 0, pension_employee DECIMAL(14,2) NOT NULL DEFAULT 0, pension_employer DECIMAL(14,2) NOT NULL DEFAULT 0, nhf DECIMAL(14,2) NOT NULL DEFAULT 0, nhis DECIMAL(14,2) NOT NULL DEFAULT 0, other_earnings DECIMAL(14,2) NOT NULL DEFAULT 0, other_deductions DECIMAL(14,2) NOT NULL DEFAULT 0, absent_days DECIMAL(6,2) NOT NULL DEFAULT 0, lop_amount DECIMAL(14,2) NOT NULL DEFAULT 0, surcharge_amount DECIMAL(14,2) NOT NULL DEFAULT 0, total_deductions DECIMAL(14,2) NOT NULL DEFAULT 0, net_pay DECIMAL(14,2) NOT NULL DEFAULT 0, status ENUM('draft','approved','paid') NOT NULL DEFAULT 'draft', generated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, UNIQUE KEY uniq_cycle_employee (payroll_cycle_id, employee_id), CONSTRAINT fk_run_cycle FOREIGN KEY (payroll_cycle_id) REFERENCES payroll_cycles(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Line-item breakdown per payslip (what actually shows on the slip) CREATE TABLE IF NOT EXISTS payroll_payslip_items ( id INT AUTO_INCREMENT PRIMARY KEY, payroll_run_id INT NOT NULL, component_id INT DEFAULT NULL, -- NULL for ad-hoc adjustments/loans source ENUM('component','adjustment','loan') NOT NULL DEFAULT 'component', name VARCHAR(120) NOT NULL, type ENUM('earning','deduction') NOT NULL, amount DECIMAL(14,2) NOT NULL DEFAULT 0, is_statutory TINYINT(1) NOT NULL DEFAULT 0, CONSTRAINT fk_item_run FOREIGN KEY (payroll_run_id) REFERENCES payroll_payslip_runs(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 8. CONFIGURABLE APPROVAL WORKFLOW -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS payroll_approval_workflows ( id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(100) NOT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB; CREATE TABLE IF NOT EXISTS payroll_approval_stages ( id INT AUTO_INCREMENT PRIMARY KEY, workflow_id INT NOT NULL, stage_order INT NOT NULL, stage_name VARCHAR(100) NOT NULL, approver_type ENUM('role','user') NOT NULL DEFAULT 'role', approver_role VARCHAR(50) DEFAULT NULL, -- e.g. 'manager', 'admin' approver_user_id INT DEFAULT NULL, is_active TINYINT(1) NOT NULL DEFAULT 1, -- deactivated (not deleted) once used in a real approval, to preserve history CONSTRAINT fk_stage_workflow FOREIGN KEY (workflow_id) REFERENCES payroll_approval_workflows(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- Tracks the actual approval instances for a given cycle CREATE TABLE IF NOT EXISTS payroll_cycle_approvals ( id INT AUTO_INCREMENT PRIMARY KEY, payroll_cycle_id INT NOT NULL, stage_id INT NOT NULL, status ENUM('pending','approved','rejected') NOT NULL DEFAULT 'pending', acted_by INT DEFAULT NULL, acted_at TIMESTAMP NULL DEFAULT NULL, comment VARCHAR(255) DEFAULT NULL, CONSTRAINT fk_ca_cycle FOREIGN KEY (payroll_cycle_id) REFERENCES payroll_cycles(id) ON DELETE CASCADE, CONSTRAINT fk_ca_stage FOREIGN KEY (stage_id) REFERENCES payroll_approval_stages(id) ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- 9. EMAIL (MAILTRAP) SETTINGS + SEND LOG -- --------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS email_settings ( id INT AUTO_INCREMENT PRIMARY KEY, smtp_host VARCHAR(150) NOT NULL DEFAULT 'sandbox.smtp.mailtrap.io', smtp_port INT NOT NULL DEFAULT 587, smtp_username VARCHAR(150) DEFAULT NULL, smtp_password VARCHAR(150) DEFAULT NULL, from_email VARCHAR(150) NOT NULL DEFAULT 'payroll@yourhotel.com', from_name VARCHAR(150) NOT NULL DEFAULT 'HR Payroll', encryption ENUM('tls','ssl','none') NOT NULL DEFAULT 'tls', is_active TINYINT(1) NOT NULL DEFAULT 1 ) ENGINE=InnoDB; INSERT INTO email_settings (smtp_host, smtp_port, from_email, from_name, encryption) SELECT 'sandbox.smtp.mailtrap.io', 587, 'payroll@yourhotel.com', 'HR Payroll', 'tls' WHERE NOT EXISTS (SELECT 1 FROM email_settings); CREATE TABLE IF NOT EXISTS payslip_email_log ( id INT AUTO_INCREMENT PRIMARY KEY, payroll_run_id INT NOT NULL, recipient_email VARCHAR(150) DEFAULT NULL, status ENUM('sent','failed') NOT NULL, error_message TEXT DEFAULT NULL, sent_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP, CONSTRAINT fk_log_run FOREIGN KEY (payroll_run_id) REFERENCES payroll_payslip_runs(id) ON DELETE CASCADE ) ENGINE=InnoDB; -- --------------------------------------------------------------------- -- Seed a default single-stage workflow so the module works immediately. -- Reconfigure any time from approval_settings.php -- --------------------------------------------------------------------- INSERT INTO payroll_approval_workflows (id, name, is_active) VALUES (1, 'Default Workflow', 1) ON DUPLICATE KEY UPDATE id = id; INSERT INTO payroll_approval_stages (workflow_id, stage_order, stage_name, approver_type, approver_role) SELECT 1, 1, 'Admin Approval', 'role', 'admin' WHERE NOT EXISTS (SELECT 1 FROM payroll_approval_stages WHERE workflow_id = 1); CREATE TABLE IF NOT EXISTS company_settings ( id INT AUTO_INCREMENT PRIMARY KEY, company_name VARCHAR(150) NOT NULL DEFAULT 'Your Company', logo_path VARCHAR(255) DEFAULT NULL, footer_text VARCHAR(500) DEFAULT NULL, updated_by INT DEFAULT NULL, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP ) ENGINE=InnoDB; INSERT INTO company_settings (company_name) SELECT 'Your Company' WHERE NOT EXISTS (SELECT 1 FROM company_settings);