dodil/erp-brick-hr

src/brick_hr/schema.sql

-- brick-hr: people, starters and leavers. GENERATED from src/brick_hr/infrastructure/tables.py by
-- scripts/gen_schema.py — edit tables.py, then regenerate. Additive only: a new column goes at the END of its
-- CREATE and as an explicit ALTER below it (erp-migrate refuses DROP / RENAME / type changes).
-- Natural keys only (never SERIAL); every child table's key starts with its aggregate root's key.
-- DataK3 enforces no foreign key: this brick being the ONLY writer of hr_* is the integrity.

CREATE TABLE IF NOT EXISTS hr_assignment (
    person_id BIGINT NOT NULL,
    n INTEGER NOT NULL,
    valid_from DATE NOT NULL,
    rev INTEGER NOT NULL,
    org_id VARCHAR,
    org_unit_id VARCHAR,
    position_id VARCHAR,
    job_code VARCHAR,
    location_code VARCHAR,
    schedule_code VARCHAR,
    employment_pct NUMERIC(5,2),
    employee_subgroup VARCHAR,
    cost_centre VARCHAR,
    manager_override_person_id BIGINT,
    valid_to DATE NOT NULL,
    superseded_by INTEGER,
    recorded_at TIMESTAMP,
    superseded_at TIMESTAMP,
    recorded_by VARCHAR,
    reason VARCHAR,
    PRIMARY KEY (person_id, n, valid_from, rev)
);

CREATE TABLE IF NOT EXISTS hr_company_grant (
    principal VARCHAR NOT NULL,
    company_code VARCHAR NOT NULL,
    note VARCHAR,
    granted_by VARCHAR,
    PRIMARY KEY (principal, company_code)
);

CREATE TABLE IF NOT EXISTS hr_compliance_record (
    person_id BIGINT NOT NULL,
    n INTEGER NOT NULL,
    kind VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    rev INTEGER NOT NULL,
    pack VARCHAR,
    outcome VARCHAR,
    checked_on DATE,
    checked_by VARCHAR,
    follow_up_on DATE,
    evidence_ref VARCHAR,
    reasons VARCHAR,
    detail_json VARCHAR,
    valid_to DATE NOT NULL,
    superseded_by INTEGER,
    recorded_at TIMESTAMP,
    superseded_at TIMESTAMP,
    recorded_by VARCHAR,
    reason VARCHAR,
    PRIMARY KEY (person_id, n, kind, valid_from, rev)
);

CREATE TABLE IF NOT EXISTS hr_config_version (
    key VARCHAR NOT NULL,
    token VARCHAR NOT NULL,
    changed_at TIMESTAMP,
    PRIMARY KEY (key)
);

CREATE TABLE IF NOT EXISTS hr_document (
    person_id BIGINT NOT NULL,
    doc_id VARCHAR NOT NULL,
    document_type VARCHAR,
    access_class VARCHAR,
    filename VARCHAR,
    content_type VARCHAR,
    object_key VARCHAR,
    sha256 VARCHAR,
    size_bytes BIGINT,
    expires_on DATE,
    contract_id VARCHAR,
    uploaded_by VARCHAR,
    uploaded_at TIMESTAMP,
    PRIMARY KEY (person_id, doc_id)
);

CREATE TABLE IF NOT EXISTS hr_document_type (
    document_type VARCHAR NOT NULL,
    label VARCHAR,
    access_class VARCHAR,
    expires BOOLEAN,
    retention_category VARCHAR,
    sensitive_kind VARCHAR,
    active BOOLEAN,
    PRIMARY KEY (document_type)
);

CREATE TABLE IF NOT EXISTS hr_draft_instruction (
    draft_code VARCHAR NOT NULL,
    label VARCHAR,
    instructions VARCHAR,
    fields VARCHAR,
    PRIMARY KEY (draft_code)
);

CREATE TABLE IF NOT EXISTS hr_employee_subgroup (
    subgroup VARCHAR NOT NULL,
    label VARCHAR,
    active BOOLEAN,
    PRIMARY KEY (subgroup)
);

CREATE TABLE IF NOT EXISTS hr_engagement (
    person_id BIGINT NOT NULL,
    n INTEGER NOT NULL,
    worker_type VARCHAR NOT NULL,
    legal_entity_ref VARCHAR,
    start_date DATE NOT NULL,
    planned_end DATE,
    actual_end DATE,
    leave_reason VARCHAR,
    supplier_bp_id BIGINT,
    created_at TIMESTAMP,
    PRIMARY KEY (person_id, n)
);

CREATE TABLE IF NOT EXISTS hr_event (
    person_id BIGINT NOT NULL,
    n INTEGER NOT NULL,
    seq INTEGER NOT NULL,
    event_type VARCHAR NOT NULL,
    kind VARCHAR NOT NULL,
    reason VARCHAR,
    effective_date DATE NOT NULL,
    actor VARCHAR,
    recorded_at TIMESTAMP,
    slices_written VARCHAR,
    run_id VARCHAR,
    bp_synced_at TIMESTAMP,
    payload_json VARCHAR,
    PRIMARY KEY (person_id, n, seq)
);

CREATE TABLE IF NOT EXISTS hr_event_reason (
    event_type VARCHAR NOT NULL,
    reason VARCHAR NOT NULL,
    label VARCHAR,
    immediate BOOLEAN,
    active BOOLEAN,
    PRIMARY KEY (event_type, reason)
);

CREATE TABLE IF NOT EXISTS hr_event_type (
    event_type VARCHAR NOT NULL,
    label VARCHAR,
    kind VARCHAR,
    active BOOLEAN,
    PRIMARY KEY (event_type)
);

CREATE TABLE IF NOT EXISTS hr_holiday (
    holiday_calendar VARCHAR NOT NULL,
    holiday_date VARCHAR NOT NULL,
    name VARCHAR,
    PRIMARY KEY (holiday_calendar, holiday_date)
);

CREATE TABLE IF NOT EXISTS hr_infogroup (
    event_type VARCHAR NOT NULL,
    record VARCHAR NOT NULL,
    required BOOLEAN,
    PRIMARY KEY (event_type, record)
);

CREATE TABLE IF NOT EXISTS hr_intake (
    intake_id VARCHAR NOT NULL,
    person_id BIGINT,
    n INTEGER,
    worker_type VARCHAR,
    pack VARCHAR,
    link_ref VARCHAR,
    issued_at TIMESTAMP,
    expires_at TIMESTAMP,
    submitted_at TIMESTAMP,
    missing_fields VARCHAR,
    chases INTEGER,
    version INTEGER,
    PRIMARY KEY (intake_id)
);

CREATE TABLE IF NOT EXISTS hr_intake_field (
    pack VARCHAR NOT NULL,
    field VARCHAR NOT NULL,
    label VARCHAR,
    worker_types VARCHAR,
    required BOOLEAN,
    sensitive_kind VARCHAR,
    input VARCHAR,
    sort_no INTEGER,
    active BOOLEAN,
    PRIMARY KEY (pack, field)
);

CREATE TABLE IF NOT EXISTS hr_intake_versions (
    intake_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (intake_id, version)
);

CREATE TABLE IF NOT EXISTS hr_job (
    job_code VARCHAR NOT NULL,
    title VARCHAR,
    family VARCHAR,
    default_subgroup VARCHAR,
    default_pay_grade VARCHAR,
    active BOOLEAN,
    PRIMARY KEY (job_code)
);

CREATE TABLE IF NOT EXISTS hr_location (
    location_code VARCHAR NOT NULL,
    name VARCHAR,
    address VARCHAR,
    country VARCHAR,
    timezone VARCHAR,
    holiday_calendar VARCHAR,
    PRIMARY KEY (location_code)
);

CREATE TABLE IF NOT EXISTS hr_object_chunk (
    object_key VARCHAR NOT NULL,
    part_no INTEGER NOT NULL,
    content_b64 VARCHAR,
    sha256 VARCHAR,
    size_bytes BIGINT,
    content_type VARCHAR,
    stored_at TIMESTAMP,
    PRIMARY KEY (object_key, part_no)
);

CREATE TABLE IF NOT EXISTS hr_org (
    org_id VARCHAR NOT NULL,
    name VARCHAR,
    legal_entity_ref VARCHAR,
    version INTEGER,
    PRIMARY KEY (org_id)
);

CREATE TABLE IF NOT EXISTS hr_org_unit (
    org_id VARCHAR NOT NULL,
    org_unit_id VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    name VARCHAR,
    parent_org_unit_id VARCHAR,
    org_unit_type VARCHAR,
    cost_centre VARCHAR,
    chief_position_id VARCHAR,
    location_code VARCHAR,
    PRIMARY KEY (org_id, org_unit_id, valid_from)
);

CREATE TABLE IF NOT EXISTS hr_org_unit_type (
    org_unit_type VARCHAR NOT NULL,
    label VARCHAR,
    active BOOLEAN,
    PRIMARY KEY (org_unit_type)
);

CREATE TABLE IF NOT EXISTS hr_org_versions (
    org_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (org_id, version)
);

CREATE TABLE IF NOT EXISTS hr_pay (
    person_id BIGINT NOT NULL,
    n INTEGER NOT NULL,
    valid_from DATE NOT NULL,
    rev INTEGER NOT NULL,
    rate_basis VARCHAR,
    rate_amount NUMERIC(19,4),
    currency VARCHAR,
    frequency VARCHAR,
    pay_grade VARCHAR,
    valid_to DATE NOT NULL,
    superseded_by INTEGER,
    recorded_at TIMESTAMP,
    superseded_at TIMESTAMP,
    recorded_by VARCHAR,
    reason VARCHAR,
    PRIMARY KEY (person_id, n, valid_from, rev)
);

CREATE TABLE IF NOT EXISTS hr_pay_component (
    person_id BIGINT NOT NULL,
    n INTEGER NOT NULL,
    component VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    rev INTEGER NOT NULL,
    rate_basis VARCHAR,
    rate_amount NUMERIC(19,4),
    currency VARCHAR,
    valid_to DATE NOT NULL,
    superseded_by INTEGER,
    recorded_at TIMESTAMP,
    superseded_at TIMESTAMP,
    recorded_by VARCHAR,
    reason VARCHAR,
    PRIMARY KEY (person_id, n, component, valid_from, rev)
);

CREATE TABLE IF NOT EXISTS hr_pay_component_type (
    component VARCHAR NOT NULL,
    label VARCHAR,
    worker_types VARCHAR,
    rate_basis VARCHAR,
    active BOOLEAN,
    PRIMARY KEY (component)
);

CREATE TABLE IF NOT EXISTS hr_pay_grade (
    pay_grade VARCHAR NOT NULL,
    label VARCHAR,
    job_code VARCHAR,
    rate_basis VARCHAR,
    currency VARCHAR,
    min_amount VARCHAR,
    max_amount VARCHAR,
    PRIMARY KEY (pay_grade)
);

CREATE TABLE IF NOT EXISTS hr_payroll_batch (
    batch_id VARCHAR NOT NULL,
    legal_entity_ref VARCHAR,
    period VARCHAR,
    state VARCHAR,
    produced_at TIMESTAMP,
    sent_at TIMESTAMP,
    sent_by VARCHAR,
    version INTEGER,
    PRIMARY KEY (batch_id)
);

CREATE TABLE IF NOT EXISTS hr_payroll_batch_versions (
    batch_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (batch_id, version)
);

CREATE TABLE IF NOT EXISTS hr_payroll_line (
    batch_id VARCHAR NOT NULL,
    person_id BIGINT NOT NULL,
    n INTEGER NOT NULL,
    component VARCHAR NOT NULL,
    for_period VARCHAR NOT NULL,
    slice_from DATE NOT NULL,
    rate_basis VARCHAR,
    rate_amount NUMERIC(19,4),
    currency VARCHAR,
    prorate_from DATE,
    prorate_to DATE,
    quantity_minutes INTEGER,
    quantity_days NUMERIC(5,2),
    payable_amount NUMERIC(18,2),
    change_kind VARCHAR,
    org_unit_id VARCHAR,
    cost_centre VARCHAR,
    detail_json VARCHAR,
    PRIMARY KEY (batch_id, person_id, n, component, for_period, slice_from)
);

CREATE TABLE IF NOT EXISTS hr_person (
    person_id BIGINT NOT NULL,
    person_key VARCHAR NOT NULL,
    staff_no VARCHAR,
    bp_id BIGINT,
    pool_user_sub VARCHAR,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    version INTEGER,
    PRIMARY KEY (person_id)
);

CREATE TABLE IF NOT EXISTS hr_person_detail (
    person_id BIGINT NOT NULL,
    valid_from DATE NOT NULL,
    rev INTEGER NOT NULL,
    legal_given_name VARCHAR,
    legal_family_name VARCHAR,
    preferred_name VARCHAR,
    personal_email VARCHAR,
    personal_phone VARCHAR,
    work_email VARCHAR,
    work_phone VARCHAR,
    valid_to DATE NOT NULL,
    superseded_by INTEGER,
    recorded_at TIMESTAMP,
    superseded_at TIMESTAMP,
    recorded_by VARCHAR,
    reason VARCHAR,
    PRIMARY KEY (person_id, valid_from, rev)
);

CREATE TABLE IF NOT EXISTS hr_person_sensitive (
    person_id BIGINT NOT NULL,
    kind VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    rev INTEGER NOT NULL,
    value_json VARCHAR,
    object_key VARCHAR,
    sha256 VARCHAR,
    created_by VARCHAR,
    valid_to DATE NOT NULL,
    superseded_by INTEGER,
    recorded_at TIMESTAMP,
    superseded_at TIMESTAMP,
    recorded_by VARCHAR,
    reason VARCHAR,
    PRIMARY KEY (person_id, kind, valid_from, rev)
);

CREATE TABLE IF NOT EXISTS hr_person_versions (
    person_id BIGINT NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (person_id, version)
);

CREATE TABLE IF NOT EXISTS hr_playbook (
    playbook_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    name VARCHAR,
    event_types VARCHAR,
    worker_types VARCHAR,
    reasons VARCHAR,
    org_unit_id VARCHAR,
    status VARCHAR,
    created_by VARCHAR,
    created_at TIMESTAMP,
    PRIMARY KEY (playbook_id, version)
);

CREATE TABLE IF NOT EXISTS hr_playbook_step (
    playbook_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    step_no INTEGER NOT NULL,
    kind VARCHAR,
    title VARCHAR,
    responsible_group VARCHAR,
    capability VARCHAR,
    input_template VARCHAR,
    timing_rule VARCHAR,
    depends_on VARCHAR,
    condition VARCHAR,
    approval_group VARCHAR,
    draft_instruction VARCHAR,
    template_kind VARCHAR,
    fan_out VARCHAR,
    step_key VARCHAR,
    outcomes VARCHAR,
    PRIMARY KEY (playbook_id, version, step_no)
);

CREATE TABLE IF NOT EXISTS hr_position (
    org_id VARCHAR NOT NULL,
    position_id VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    valid_to DATE NOT NULL,
    org_unit_id VARCHAR,
    job_code VARCHAR,
    fte_pct VARCHAR,
    cost_centre VARCHAR,
    vacant_policy VARCHAR,
    PRIMARY KEY (org_id, position_id, valid_from)
);

CREATE TABLE IF NOT EXISTS hr_privacy_request (
    request_id VARCHAR NOT NULL,
    person_id BIGINT,
    kind VARCHAR,
    received_on DATE,
    due_on DATE,
    done_at TIMESTAMP,
    done_by VARCHAR,
    export_object_key VARCHAR,
    outcome VARCHAR,
    version INTEGER,
    PRIMARY KEY (request_id)
);

CREATE TABLE IF NOT EXISTS hr_privacy_request_versions (
    request_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (request_id, version)
);

CREATE TABLE IF NOT EXISTS hr_rate_basis (
    rate_basis VARCHAR NOT NULL,
    label VARCHAR,
    active BOOLEAN,
    PRIMARY KEY (rate_basis)
);

CREATE TABLE IF NOT EXISTS hr_responsible_group (
    group_code VARCHAR NOT NULL,
    label VARCHAR,
    resolution VARCHAR,
    active BOOLEAN,
    PRIMARY KEY (group_code)
);

CREATE TABLE IF NOT EXISTS hr_retention_log (
    run_date DATE NOT NULL,
    person_id BIGINT NOT NULL,
    category VARCHAR NOT NULL,
    action VARCHAR,
    basis VARCHAR,
    actor VARCHAR,
    logged_at TIMESTAMP,
    PRIMARY KEY (run_date, person_id, category)
);

CREATE TABLE IF NOT EXISTS hr_retention_rule (
    category VARCHAR NOT NULL,
    label VARCHAR,
    keep_months INTEGER,
    action VARCHAR,
    basis VARCHAR,
    PRIMARY KEY (category)
);

CREATE TABLE IF NOT EXISTS hr_run (
    run_id VARCHAR NOT NULL,
    direction VARCHAR,
    person_id BIGINT,
    n INTEGER,
    event_seq VARCHAR,
    playbook_id VARCHAR,
    playbook_version INTEGER,
    created_at TIMESTAMP,
    anchors_json VARCHAR,
    cancelled_at TIMESTAMP,
    cancel_reason VARCHAR,
    cancelled_by VARCHAR,
    version INTEGER,
    PRIMARY KEY (run_id)
);

CREATE TABLE IF NOT EXISTS hr_run_log (
    run_id VARCHAR NOT NULL,
    seq INTEGER NOT NULL,
    what VARCHAR,
    from_value VARCHAR,
    to_value VARCHAR,
    actor VARCHAR,
    event_at TIMESTAMP,
    PRIMARY KEY (run_id, seq)
);

CREATE TABLE IF NOT EXISTS hr_run_party (
    run_id VARCHAR NOT NULL,
    group_code VARCHAR NOT NULL,
    person_id BIGINT,
    set_by VARCHAR,
    PRIMARY KEY (run_id, group_code)
);

CREATE TABLE IF NOT EXISTS hr_run_step (
    run_id VARCHAR NOT NULL,
    step_no VARCHAR NOT NULL,
    sort_no INTEGER,
    kind VARCHAR,
    title VARCHAR,
    responsible_group VARCHAR,
    playbook_step_no INTEGER,
    capability VARCHAR,
    template_json VARCHAR,
    input_json VARCHAR,
    plan VARCHAR,
    manual BOOLEAN,
    manual_reason VARCHAR,
    timing_rule VARCHAR,
    due_at TIMESTAMP,
    state VARCHAR,
    attempts INTEGER,
    last_status VARCHAR,
    provider_ref VARCHAR,
    result_json VARCHAR,
    contract_id VARCHAR,
    draft_text VARCHAR,
    done_by VARCHAR,
    done_at TIMESTAMP,
    depends_on VARCHAR,
    condition VARCHAR,
    approval_group VARCHAR,
    destructive BOOLEAN,
    message VARCHAR,
    next_check_at TIMESTAMP,
    approved BOOLEAN,
    target VARCHAR,
    template_kind VARCHAR,
    draft_instruction VARCHAR,
    outcomes VARCHAR,
    PRIMARY KEY (run_id, step_no)
);

CREATE TABLE IF NOT EXISTS hr_run_versions (
    run_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (run_id, version)
);

CREATE TABLE IF NOT EXISTS hr_sensitive (
    person_id BIGINT NOT NULL,
    erased_at TIMESTAMP,
    anonymised_at TIMESTAMP,
    version INTEGER,
    PRIMARY KEY (person_id)
);

CREATE TABLE IF NOT EXISTS hr_sensitive_versions (
    person_id BIGINT NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP NOT NULL,
    PRIMARY KEY (person_id, version)
);

CREATE TABLE IF NOT EXISTS hr_setting_row (
    setting_key VARCHAR NOT NULL,
    scope VARCHAR NOT NULL,
    valid_from DATE NOT NULL,
    value VARCHAR NOT NULL,
    set_by VARCHAR,
    set_sub VARCHAR,
    set_at TIMESTAMP,
    note VARCHAR,
    PRIMARY KEY (setting_key, scope, valid_from)
);

CREATE TABLE IF NOT EXISTS hr_settings (
    key VARCHAR NOT NULL,
    value VARCHAR,
    PRIMARY KEY (key)
);

CREATE TABLE IF NOT EXISTS hr_step_claim (
    run_id VARCHAR NOT NULL,
    step_no VARCHAR NOT NULL,
    attempt INTEGER NOT NULL,
    claimed_at TIMESTAMP NOT NULL,
    PRIMARY KEY (run_id, step_no, attempt)
);

CREATE TABLE IF NOT EXISTS hr_unit_access (
    org_unit_id VARCHAR NOT NULL,
    kind VARCHAR NOT NULL,
    target VARCHAR NOT NULL,
    role VARCHAR,
    label VARCHAR,
    PRIMARY KEY (org_unit_id, kind, target)
);

CREATE TABLE IF NOT EXISTS hr_work_schedule (
    schedule_code VARCHAR NOT NULL,
    label VARCHAR,
    weekly_minutes INTEGER,
    daily_pattern VARCHAR,
    full_time_weekly_minutes INTEGER,
    working_weekdays VARCHAR,
    PRIMARY KEY (schedule_code)
);

CREATE TABLE IF NOT EXISTS hr_worker_type (
    worker_type VARCHAR NOT NULL,
    label VARCHAR,
    category VARCHAR,
    payroll BOOLEAN,
    via_company BOOLEAN,
    bp_roles VARCHAR,
    employee_subgroup VARCHAR,
    active BOOLEAN,
    PRIMARY KEY (worker_type)
);

-- Columns added after their table first shipped, for a bucket that predates them (at the END of the CREATE
-- above too). erp-migrate marks these DISRUPTIVE: reads on the table stall for minutes after an ADD COLUMN,
-- so migrate the test copy first and the live one at a quiet hour.
-- 0.4.0: the stable step key BPMN ids, scenario paths and deviations join on; a row from before it is keyed on read (repositories._playbook) until scripts/backfill_step_keys.py writes the key
ALTER TABLE hr_playbook_step ADD COLUMN step_key VARCHAR;
-- 0.6.0: the outcomes a step's completion records (the outplacement offer: accepted / declined); empty = none
ALTER TABLE hr_playbook_step ADD COLUMN outcomes VARCHAR;
-- 0.6.0: the playbook step's outcomes, frozen on the run step
ALTER TABLE hr_run_step ADD COLUMN outcomes VARCHAR;