dodil/erp-brick-payroll

DODIL ERP brick: payroll

src/brick_payroll/schema.sql

-- payroll (prl_): UK pay runs, payslips and their lines, the payroll record of each employment with its dated tax code, NI
-- category and pension membership, PAYE schemes, pay schedules, pension schemes, pay elements, dated statutory rates, payslip
-- documents and who read them, the claim guard rows, the period claims, the outbox of side effects and dated settings. Every
-- table prefixed `prl_`, a natural primary key each (never SERIAL), money NUMERIC(18,2), rates NUMERIC(19,4), creation columns
-- last so a later column is an append. Additive only. Kept equal to infrastructure/tables.py by tests/test_schema.py.

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_p45 (
    payroll_id    VARCHAR NOT NULL,
    direction     VARCHAR NOT NULL,
    paye_ref      VARCHAR,
    leaving_date  DATE,
    tax_code      VARCHAR,
    basis         VARCHAR,
    pay_to_date   NUMERIC(18,2),
    tax_to_date   NUMERIC(18,2),
    student_loan  BOOLEAN,
    ni_category   VARCHAR,
    source_ref    VARCHAR,
    document_key  VARCHAR,
    recorded_by   VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (payroll_id, direction)
);

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_rti_submission (
    submission_id   VARCHAR NOT NULL,
    kind            VARCHAR,
    pay_run_id      VARCHAR,
    company_code    VARCHAR,
    paye_ref        VARCHAR,
    tax_year        VARCHAR,
    tax_period      INTEGER,
    scheme_code     VARCHAR,
    n               INTEGER,
    object_key      VARCHAR,
    sha256          VARCHAR,
    status          VARCHAR,
    correlation_id  VARCHAR,
    message         VARCHAR,
    errors_json     VARCHAR,
    totals_json     VARCHAR,
    attempts        INTEGER,
    due_on          DATE,
    sent_at         TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (submission_id)
);

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)
);

CREATE TABLE IF NOT EXISTS prl_ytd_opening (
    ytd_ref             VARCHAR NOT NULL,
    payroll_id          VARCHAR,
    tax_year            VARCHAR,
    paye_ref            VARCHAR,
    run_id              VARCHAR,
    tax_code            VARCHAR,
    basis               VARCHAR,
    gross_amount        NUMERIC(18,2),
    taxable_pay_amount  NUMERIC(18,2),
    tax_amount          NUMERIC(18,2),
    ee_ni_amount        NUMERIC(18,2),
    er_ni_amount        NUMERIC(18,2),
    ni_by_letter_json   VARCHAR,
    statutory_json      VARCHAR,
    pension_amount      NUMERIC(18,2),
    source_file_key     VARCHAR,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (ytd_ref)
);

CREATE TABLE IF NOT EXISTS prl_ytd_run (
    run_id          VARCHAR NOT NULL,
    tax_year        VARCHAR,
    paye_ref        VARCHAR,
    company_code    VARCHAR,
    schedule_code   VARCHAR,
    cutover_date    DATE,
    source_ref      VARCHAR,
    status          VARCHAR,
    fps_json        VARCHAR,
    checks_json     VARCHAR,
    loaded_by       VARCHAR,
    signed_by       VARCHAR,
    signed_sub      VARCHAR,
    signed_on       DATE,
    discarded_by    VARCHAR,
    discard_reason  VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (run_id)
);