dodil/erp-brick-payroll
DODIL ERP brick: payroll
src/brick_payroll/migrations/00002_seed_rows/up.sql
-- 00002_seed_rows - data only: the pay elements every company starts with (company_code '*'; a company's own row of the
-- same code wins) and the 2026/27 statutory rows from 2026-04-06, each flagged to_confirm: a payroll manager checks them
-- against HMRC's published rates before the first run. Plain INSERTs: the migration journal runs this once.
INSERT INTO prl_element (company_code, element_code, valid_from, name, kind, taxable, niable, pensionable, gl_purpose, maps_from, created_at, updated_at) VALUES
('*', 'SALARY', DATE '2000-01-01', 'Salary', 'earning', TRUE, TRUE, TRUE, 'gross_pay', 'hr:base', NOW(), NOW()),
('*', 'UNPAID_DEDUCTION', DATE '2000-01-01', 'Unpaid leave', 'earning', TRUE, TRUE, TRUE, 'gross_pay', 'tof:unpaid', NOW(), NOW()),
('*', 'HOLIDAY_IN_LIEU', DATE '2000-01-01', 'Holiday pay in lieu', 'earning', TRUE, TRUE, TRUE, 'gross_pay', 'tof:balance', NOW(), NOW()),
('*', 'TAX', DATE '2000-01-01', 'PAYE income tax', 'deduction', FALSE, FALSE, FALSE, 'paye_ni_payable', NULL, NOW(), NOW()),
('*', 'EE_NI', DATE '2000-01-01', 'Employee National Insurance', 'deduction', FALSE, FALSE, FALSE, 'paye_ni_payable', NULL, NOW(), NOW()),
('*', 'EE_PENSION', DATE '2000-01-01', 'Employee pension (net pay arrangement)', 'deduction', FALSE, FALSE, FALSE, 'pension_payable', NULL, NOW(), NOW()),
('*', 'ER_NI', DATE '2000-01-01', 'Employer National Insurance', 'employer', FALSE, FALSE, FALSE, 'employer_ni', NULL, NOW(), NOW()),
('*', 'ER_PENSION', DATE '2000-01-01', 'Employer pension', 'employer', FALSE, FALSE, FALSE, 'employer_pension', NULL, NOW(), NOW());
INSERT INTO prl_statutory_rate (rate_key, region, frequency, valid_from, value, unit, source, to_confirm, set_by, created_at, updated_at) VALUES
('tax_basic_rate', 'ruk', 'any', DATE '2026-04-06', 20.0000, 'percent', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('tax_basic_limit', 'ruk', 'annual', DATE '2026-04-06', 37700.0000, 'pounds', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('tax_higher_rate', 'ruk', 'any', DATE '2026-04-06', 40.0000, 'percent', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('tax_higher_limit', 'ruk', 'annual', DATE '2026-04-06', 125140.0000, 'pounds', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('tax_additional_rate', 'ruk', 'any', DATE '2026-04-06', 45.0000, 'percent', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('tax_regulatory_limit', 'uk', 'any', DATE '2026-04-06', 50.0000, 'percent', 'PAYE regulations 2003 reg. 23 (the overriding limit)', TRUE, 'seed', NOW(), NOW()),
('ni_primary_threshold', 'uk', 'monthly', DATE '2026-04-06', 1048.0000, 'amount', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ni_primary_threshold', 'uk', 'weekly', DATE '2026-04-06', 242.0000, 'amount', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ni_upper_earnings_limit', 'uk', 'monthly', DATE '2026-04-06', 4189.0000, 'amount', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ni_upper_earnings_limit', 'uk', 'weekly', DATE '2026-04-06', 967.0000, 'amount', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ni_secondary_threshold', 'uk', 'monthly', DATE '2026-04-06', 417.0000, 'amount', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ni_secondary_threshold', 'uk', 'weekly', DATE '2026-04-06', 96.0000, 'amount', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ni_a_employee_main', 'uk', 'any', DATE '2026-04-06', 8.0000, 'percent', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ni_a_employee_additional', 'uk', 'any', DATE '2026-04-06', 2.0000, 'percent', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ni_a_employer', 'uk', 'any', DATE '2026-04-06', 15.0000, 'percent', 'HMRC Rates and thresholds for employers 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ae_qualifying_lower', 'uk', 'monthly', DATE '2026-04-06', 520.0000, 'amount', 'The Pensions Regulator: automatic enrolment earnings thresholds 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ae_qualifying_lower', 'uk', 'weekly', DATE '2026-04-06', 120.0000, 'amount', 'The Pensions Regulator: automatic enrolment earnings thresholds 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ae_qualifying_upper', 'uk', 'monthly', DATE '2026-04-06', 4189.0000, 'amount', 'The Pensions Regulator: automatic enrolment earnings thresholds 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ae_qualifying_upper', 'uk', 'weekly', DATE '2026-04-06', 967.0000, 'amount', 'The Pensions Regulator: automatic enrolment earnings thresholds 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('ae_trigger', 'uk', 'annual', DATE '2026-04-06', 10000.0000, 'amount', 'The Pensions Regulator: automatic enrolment earnings thresholds 2026 to 2027 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('nmw_21_and_over', 'uk', 'hourly', DATE '2026-04-06', 12.7100, 'amount', 'National Minimum Wage and National Living Wage rates from April 2026 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('nmw_18_to_20', 'uk', 'hourly', DATE '2026-04-06', 10.8500, 'amount', 'National Minimum Wage and National Living Wage rates from April 2026 (to confirm)', TRUE, 'seed', NOW(), NOW()),
('nmw_under_18', 'uk', 'hourly', DATE '2026-04-06', 8.0000, 'amount', 'National Minimum Wage and National Living Wage rates from April 2026 (to confirm)', TRUE, 'seed', NOW(), NOW());