dodil/erp-brick-time-off

DODIL ERP brick: time off

src/brick_time_off/migrations/00001_init/up.sql

-- 00001_init - slice A (leave_request workflow TOF-01..06, TOF-15, TOF-19, TOF-21/22). The versioned layout
-- (migrations/NNNNN_<name>/{up,down}.sql) with a per-brick journal, tof_schema_migrations; `erp-migrate --schema schema.sql` applies the same tables today.

CREATE TABLE IF NOT EXISTS tof_schema_migrations (
    migration  VARCHAR NOT NULL,
    applied_at TIMESTAMP NOT NULL,
    PRIMARY KEY (migration)
);

CREATE TABLE IF NOT EXISTS tof_balance_entry (
    entry_ref        VARCHAR NOT NULL,
    person_id        BIGINT,
    leave_type_code  VARCHAR,
    leave_year       INTEGER,
    kind             VARCHAR,
    quantity         NUMERIC(19,4),
    unit             VARCHAR,
    on_date          DATE,
    by_email         VARCHAR,
    by_sub           VARCHAR,
    reason           VARCHAR,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (entry_ref)
);

CREATE TABLE IF NOT EXISTS tof_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 tof_day_claim (
    person_id     BIGINT NOT NULL,
    absence_date  DATE NOT NULL,
    part          VARCHAR NOT NULL,
    absence_ref   VARCHAR,
    minutes       INTEGER,
    quantity      NUMERIC(19,4),
    claimed_at    TIMESTAMP,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (person_id, absence_date, part)
);

CREATE TABLE IF NOT EXISTS tof_leave_type (
    company_code           VARCHAR NOT NULL,
    leave_type_code        VARCHAR NOT NULL,
    valid_from             DATE NOT NULL,
    name                   VARCHAR,
    pay_treatment          VARCHAR,
    recorded_or_requested  VARCHAR,
    counts_against         VARCHAR,
    evidence_rule          VARCHAR,
    max_days_per_year      INTEGER,
    created_at             TIMESTAMP,
    updated_at             TIMESTAMP,
    PRIMARY KEY (company_code, leave_type_code, valid_from)
);

CREATE TABLE IF NOT EXISTS tof_leaver (
    source_ref      VARCHAR NOT NULL,
    person_id       BIGINT,
    n               INTEGER,
    last_day        DATE,
    cancelled_json  VARCHAR,
    balance_json    VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (source_ref)
);

CREATE TABLE IF NOT EXISTS tof_policy (
    company_code        VARCHAR NOT NULL,
    policy_code         VARCHAR NOT NULL,
    valid_from          DATE NOT NULL,
    leave_type_code     VARCHAR,
    applies_json        VARCHAR,
    unit                VARCHAR,
    annual_days         NUMERIC(9,4),
    bank_holidays       VARCHAR,
    accrual             VARCHAR,
    accrual_pct         NUMERIC(9,4),
    leave_year_start    VARCHAR,
    rounding            VARCHAR,
    carry_over_max      NUMERIC(9,4),
    carry_over_expires  VARCHAR,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (company_code, policy_code, valid_from)
);

CREATE TABLE IF NOT EXISTS tof_request (
    request_id             VARCHAR NOT NULL,
    person_id              BIGINT,
    company_code           VARCHAR,
    requester_email        VARCHAR,
    requester_sub          VARCHAR,
    leave_type_code        VARCHAR,
    policy_code            VARCHAR,
    leave_year             INTEGER,
    from_date              DATE,
    from_part              VARCHAR,
    to_date                DATE,
    to_part                VARCHAR,
    cost                   NUMERIC(19,4),
    unit                   VARCHAR,
    status                 VARCHAR,
    revision               INTEGER,
    approver_person_id     BIGINT,
    approver_email         VARCHAR,
    approver_sub           VARCHAR,
    approved_by            VARCHAR,
    approved_sub           VARCHAR,
    decided_at             TIMESTAMP,
    reason                 VARCHAR,
    note                   VARCHAR,
    overlap_ack            VARCHAR,
    stale                  BOOLEAN,
    stale_cost             NUMERIC(19,4),
    cancel_asked_at        TIMESTAMP,
    cancel_asked_by        VARCHAR,
    cancel_refused_reason  VARCHAR,
    created_at             TIMESTAMP,
    updated_at             TIMESTAMP,
    PRIMARY KEY (request_id)
);

CREATE TABLE IF NOT EXISTS tof_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)
);