dodil/erp-brick-expenses

DODIL ERP brick: expenses

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

-- 00001_init - slice A (expense report workflow, EXP-08..EXP-13, EXP-19 leaver intake). The versioned layout
-- (migrations/NNNNN_<name>/{up,down}.sql) with a per-brick journal, exp_schema_migrations; `erp-migrate --schema schema.sql` applies the same tables today.

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

CREATE TABLE IF NOT EXISTS exp_approval_step (
    report_id           VARCHAR NOT NULL,
    revision            INTEGER NOT NULL,
    level               INTEGER NOT NULL,
    kind                VARCHAR,
    approver_person_id  BIGINT,
    decision            VARCHAR,
    decided_by          VARCHAR,
    decided_sub         VARCHAR,
    decided_at          TIMESTAMP,
    reason              VARCHAR,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (report_id, revision, level)
);

CREATE TABLE IF NOT EXISTS exp_category (
    company_code      VARCHAR NOT NULL,
    category_code     VARCHAR NOT NULL,
    valid_from        DATE NOT NULL,
    name              VARCHAR,
    gl_purpose        VARCHAR,
    tax_category      VARCHAR,
    receipt_required  BOOLEAN,
    kind              VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (company_code, category_code, valid_from)
);

CREATE TABLE IF NOT EXISTS exp_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 exp_leaver (
    source_ref     VARCHAR NOT NULL,
    person_id      BIGINT,
    last_day       DATE,
    claims_until   DATE,
    open_reports   INTEGER,
    cards_stopped  INTEGER,
    rerouted_json  VARCHAR,
    created_at     TIMESTAMP,
    updated_at     TIMESTAMP,
    PRIMARY KEY (source_ref)
);

CREATE TABLE IF NOT EXISTS exp_line (
    report_id         VARCHAR NOT NULL,
    revision          INTEGER NOT NULL,
    line_no           INTEGER NOT NULL,
    kind              VARCHAR,
    category_code     VARCHAR,
    expense_date      DATE,
    merchant          VARCHAR,
    receipt_amount    NUMERIC(18,2),
    receipt_currency  VARCHAR,
    amount            NUMERIC(18,2),
    paid_with         VARCHAR,
    vat_amount        NUMERIC(18,2),
    cost_centre       VARCHAR,
    receipt_key       VARCHAR,
    description       VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (report_id, revision, line_no)
);

CREATE TABLE IF NOT EXISTS exp_outbox (
    target        VARCHAR NOT NULL,
    ref           VARCHAR NOT NULL,
    report_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 exp_report (
    report_id            VARCHAR NOT NULL,
    person_id            BIGINT,
    company_code         VARCHAR,
    bp_id                BIGINT,
    claimant_email       VARCHAR,
    claimant_sub         VARCHAR,
    revision             INTEGER,
    title                VARCHAR,
    currency             VARCHAR,
    status               VARCHAR,
    submitted_at         TIMESTAMP,
    submitted_by         VARCHAR,
    submitted_sub        VARCHAR,
    submitted_on         DATE,
    chain_json           VARCHAR,
    waiting_level        INTEGER,
    waiting_kind         VARCHAR,
    waiting_email        VARCHAR,
    waiting_sub          VARCHAR,
    journal_id           BIGINT,
    payout_ref           VARCHAR,
    approved_on          DATE,
    paid_on              DATE,
    payment_id           VARCHAR,
    fail_reason          VARCHAR,
    total_amount         NUMERIC(18,2),
    reimbursable_amount  NUMERIC(18,2),
    created_at           TIMESTAMP,
    updated_at           TIMESTAMP,
    PRIMARY KEY (report_id)
);

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