dodil/erp-brick-payroll
DODIL ERP brick: payroll
src/brick_payroll/migrations/00001_init/up.sql
-- 00001_init - slice A (the monthly pay run for salaried employees: PRL-04, PRL-06, PRL-12, PRL-16, PRL-28 and the
-- payroll record they need). The versioned layout (migrations/NNNNN_<name>/{up,down}.sql) with a per-brick journal,
-- prl_schema_migrations; `erp-migrate --schema schema.sql` applies the same tables today.
CREATE TABLE IF NOT EXISTS prl_schema_migrations (
migration VARCHAR NOT NULL,
applied_at TIMESTAMP NOT NULL,
PRIMARY KEY (migration)
);
CREATE TABLE IF NOT EXISTS prl_access_log (
object_key VARCHAR NOT NULL,
reader VARCHAR NOT NULL,
read_at TIMESTAMP NOT NULL,
reader_sub VARCHAR,
person_id BIGINT,
what VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (object_key, reader, read_at)
);
CREATE TABLE IF NOT EXISTS prl_claim (
claim_scope VARCHAR NOT NULL,
claim_key VARCHAR NOT NULL,
n INTEGER NOT NULL,
ref VARCHAR,
claimed_by VARCHAR,
claimed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (claim_scope, claim_key, n)
);
CREATE TABLE IF NOT EXISTS prl_document (
object_key VARCHAR NOT NULL,
kind VARCHAR,
payroll_id VARCHAR,
person_id BIGINT,
ref VARCHAR,
pay_run_id VARCHAR,
content_type VARCHAR,
sha256 VARCHAR,
size_bytes INTEGER,
content_b64 VARCHAR,
published_on DATE,
retain_until DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (object_key)
);
CREATE TABLE IF NOT EXISTS prl_element (
company_code VARCHAR NOT NULL,
element_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
name VARCHAR,
kind VARCHAR,
taxable BOOLEAN,
niable BOOLEAN,
pensionable BOOLEAN,
gl_purpose VARCHAR,
maps_from VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, element_code, valid_from)
);
CREATE TABLE IF NOT EXISTS prl_employee (
payroll_id VARCHAR NOT NULL,
person_id BIGINT,
n INTEGER,
company_code VARCHAR,
schedule_code VARCHAR,
status VARCHAR,
start_date DATE,
leaving_date DATE,
starter_declaration VARCHAR,
p45_pay_to_date NUMERIC(18,2),
p45_tax_to_date NUMERIC(18,2),
details_by VARCHAR,
details_sub VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (payroll_id)
);
CREATE TABLE IF NOT EXISTS prl_ni_category (
payroll_id VARCHAR NOT NULL,
valid_from DATE NOT NULL,
category VARCHAR,
reason VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (payroll_id, valid_from)
);
CREATE TABLE IF NOT EXISTS prl_outbox (
target VARCHAR NOT NULL,
ref VARCHAR NOT NULL,
pay_run_id VARCHAR,
payload_json VARCHAR,
status VARCHAR,
attempts INTEGER,
last_error VARCHAR,
sent_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (target, ref)
);
CREATE TABLE IF NOT EXISTS prl_pay_run (
pay_run_id VARCHAR NOT NULL,
company_code VARCHAR,
schedule_code VARCHAR,
frequency VARCHAR,
kind VARCHAR,
tax_year VARCHAR,
tax_period INTEGER,
period_code VARCHAR,
period_from DATE,
period_to DATE,
pay_date DATE,
paye_ref VARCHAR,
status VARCHAR,
calc_no INTEGER,
opened_by VARCHAR,
opened_sub VARCHAR,
prepared_by VARCHAR,
prepared_sub VARCHAR,
calculated_at TIMESTAMP,
approved_by VARCHAR,
approved_sub VARCHAR,
approved_at TIMESTAMP,
approved_on DATE,
journal_id BIGINT,
journal_status VARCHAR,
journal_reason VARCHAR,
hr_batch_id VARCHAR,
cancel_reason VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (pay_run_id)
);
CREATE TABLE IF NOT EXISTS prl_pay_schedule (
company_code VARCHAR NOT NULL,
schedule_code VARCHAR NOT NULL,
frequency VARCHAR,
period_rule VARCHAR,
pay_day_rule VARCHAR,
paye_ref VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, schedule_code)
);
CREATE TABLE IF NOT EXISTS prl_paye_scheme (
company_code VARCHAR NOT NULL,
paye_ref VARCHAR NOT NULL,
accounts_office_ref VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, paye_ref)
);
CREATE TABLE IF NOT EXISTS prl_payslip (
pay_run_id VARCHAR NOT NULL,
payroll_id VARCHAR NOT NULL,
person_id BIGINT,
calc_no INTEGER,
gross_amount NUMERIC(18,2),
taxable_amount NUMERIC(18,2),
tax_amount NUMERIC(18,2),
ee_ni_amount NUMERIC(18,2),
er_ni_amount NUMERIC(18,2),
ee_pension_amount NUMERIC(18,2),
er_pension_amount NUMERIC(18,2),
student_loan_amount NUMERIC(18,2),
net_amount NUMERIC(18,2),
tax_code_used VARCHAR,
basis_used VARCHAR,
ni_category_used VARCHAR,
ytd_json VARCHAR,
cost_centre VARCHAR,
bp_id BIGINT,
inputs_sha VARCHAR,
stale BOOLEAN,
stale_reason VARCHAR,
approved_by VARCHAR,
approved_sub VARCHAR,
payout_ref VARCHAR,
payout_status VARCHAR,
payout_reason VARCHAR,
paid_on DATE,
payment_id VARCHAR,
document_key VARCHAR,
published_on DATE,
leaving_date DATE,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (pay_run_id, payroll_id)
);
CREATE TABLE IF NOT EXISTS prl_payslip_line (
pay_run_id VARCHAR NOT NULL,
payroll_id VARCHAR NOT NULL,
calc_no INTEGER NOT NULL,
line_no INTEGER NOT NULL,
element_code VARCHAR,
kind VARCHAR,
quantity NUMERIC(19,4),
rate_amount NUMERIC(19,4),
amount NUMERIC(18,2),
source_kind VARCHAR,
source_ref VARCHAR,
rate_rows_used VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (pay_run_id, payroll_id, calc_no, line_no)
);
CREATE TABLE IF NOT EXISTS prl_pension_membership (
payroll_id VARCHAR NOT NULL,
scheme_code VARCHAR NOT NULL,
enrolled_on DATE NOT NULL,
status VARCHAR,
assessed_as VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (payroll_id, scheme_code, enrolled_on)
);
CREATE TABLE IF NOT EXISTS prl_pension_scheme (
company_code VARCHAR NOT NULL,
scheme_code VARCHAR NOT NULL,
valid_from DATE NOT NULL,
basis VARCHAR,
employee_pct NUMERIC(19,4),
employer_pct NUMERIC(19,4),
tax_relief VARCHAR,
provider_capability VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, scheme_code, valid_from)
);
CREATE TABLE IF NOT EXISTS prl_period_claim (
company_code VARCHAR NOT NULL,
schedule_code VARCHAR NOT NULL,
tax_year VARCHAR NOT NULL,
period_code VARCHAR NOT NULL,
kind VARCHAR NOT NULL,
pay_run_id VARCHAR,
claimed_at TIMESTAMP,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (company_code, schedule_code, tax_year, period_code, kind)
);
CREATE TABLE IF NOT EXISTS prl_settings (
setting_key VARCHAR NOT NULL,
valid_from DATE NOT NULL,
value VARCHAR,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (setting_key, valid_from)
);
CREATE TABLE IF NOT EXISTS prl_statutory_rate (
rate_key VARCHAR NOT NULL,
region VARCHAR NOT NULL,
frequency VARCHAR NOT NULL,
valid_from DATE NOT NULL,
value NUMERIC(19,4),
unit VARCHAR,
source VARCHAR,
to_confirm BOOLEAN,
set_by VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (rate_key, region, frequency, valid_from)
);
CREATE TABLE IF NOT EXISTS prl_tax_code (
payroll_id VARCHAR NOT NULL,
valid_from DATE NOT NULL,
tax_code VARCHAR,
basis VARCHAR,
source VARCHAR,
notice_ref VARCHAR,
set_by VARCHAR,
set_sub VARCHAR,
created_at TIMESTAMP,
updated_at TIMESTAMP,
PRIMARY KEY (payroll_id, valid_from)
);