dodil/erp-brick-timesheets

DODIL ERP brick: timesheets

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

-- 00001_init - slice A (the timesheet workflow, cost to gl, deliveries to sales-order; TS-01..TS-08, TS-10..TS-12, TS-16..TS-21). The versioned layout
-- (migrations/NNNNN_<name>/{up,down}.sql) with a per-brick journal, ts_schema_migrations; `erp-migrate --schema schema.sql` applies the same tables today.

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

CREATE TABLE IF NOT EXISTS ts_activity (
    company_code      VARCHAR NOT NULL,
    activity_code     VARCHAR NOT NULL,
    valid_from        DATE NOT NULL,
    name              VARCHAR,
    billable_default  BOOLEAN,
    internal_only     BOOLEAN,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (company_code, activity_code, valid_from)
);

CREATE TABLE IF NOT EXISTS ts_billing_claim (
    sheet_id        VARCHAR NOT NULL,
    revision        INTEGER NOT NULL,
    line_no         INTEGER NOT NULL,
    purpose         VARCHAR NOT NULL,
    run_id          VARCHAR,
    delivery_ref    VARCHAR,
    sales_order_id  VARCHAR,
    so_line_no      INTEGER,
    quantity        NUMERIC(19,4),
    unit_price      NUMERIC(19,4),
    priced_from     VARCHAR,
    claimed_at      TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (sheet_id, revision, line_no, purpose)
);

CREATE TABLE IF NOT EXISTS ts_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 ts_cost_rate (
    company_code  VARCHAR NOT NULL,
    rate_group    VARCHAR NOT NULL,
    valid_from    DATE NOT NULL,
    rate_amount   NUMERIC(19,4),
    currency      VARCHAR,
    set_by        VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (company_code, rate_group, valid_from)
);

CREATE TABLE IF NOT EXISTS ts_entry (
    sheet_id    VARCHAR NOT NULL,
    revision    INTEGER NOT NULL,
    line_no     INTEGER NOT NULL,
    work_date   DATE NOT NULL,
    hours       NUMERIC(19,4),
    note        VARCHAR,
    unit_cost   NUMERIC(19,4),
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (sheet_id, revision, line_no, work_date)
);

CREATE TABLE IF NOT EXISTS ts_line (
    sheet_id        VARCHAR NOT NULL,
    revision        INTEGER NOT NULL,
    line_no         INTEGER NOT NULL,
    kind            VARCHAR,
    activity_code   VARCHAR,
    project_id      VARCHAR,
    item_id         VARCHAR,
    cost_centre     VARCHAR,
    billable        BOOLEAN,
    bill_via        VARCHAR,
    sales_order_id  VARCHAR,
    so_line_no      INTEGER,
    product_id      VARCHAR,
    rate_group      VARCHAR,
    unit_cost       NUMERIC(19,4),
    absence_ref     VARCHAR,
    description     VARCHAR,
    lead_person_id  BIGINT,
    billing         VARCHAR,
    stale           BOOLEAN,
    stale_reason    VARCHAR,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (sheet_id, revision, line_no)
);

CREATE TABLE IF NOT EXISTS ts_line_approval (
    sheet_id            VARCHAR NOT NULL,
    revision            INTEGER NOT NULL,
    line_no             INTEGER NOT NULL,
    approver_person_id  BIGINT,
    decided_by          VARCHAR,
    decided_sub         VARCHAR,
    decided_at          TIMESTAMP,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (sheet_id, revision, line_no)
);

CREATE TABLE IF NOT EXISTS ts_outbox (
    target        VARCHAR NOT NULL,
    ref           VARCHAR NOT NULL,
    sheet_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 ts_person_rate_group (
    person_id   BIGINT NOT NULL,
    valid_from  DATE NOT NULL,
    rate_group  VARCHAR,
    set_by      VARCHAR,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (person_id, valid_from)
);

CREATE TABLE IF NOT EXISTS ts_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 ts_sheet (
    sheet_id            VARCHAR NOT NULL,
    revision            INTEGER NOT NULL,
    person_id           BIGINT,
    company_code        VARCHAR,
    week_start          DATE,
    person_email        VARCHAR,
    person_sub          VARCHAR,
    status              VARCHAR,
    expected_minutes    INTEGER,
    expected_json       VARCHAR,
    home_cost_centre    VARCHAR,
    approval_route      VARCHAR,
    approver_person_id  BIGINT,
    approver_email      VARCHAR,
    approver_sub        VARCHAR,
    rerouted_from       VARCHAR,
    currency            VARCHAR,
    submitted_at        TIMESTAMP,
    submitted_by        VARCHAR,
    submitted_sub       VARCHAR,
    approved_by         VARCHAR,
    approved_sub        VARCHAR,
    approved_at         TIMESTAMP,
    approved_on         DATE,
    journal_id          BIGINT,
    ended_by            VARCHAR,
    ended_sub           VARCHAR,
    ended_at            TIMESTAMP,
    reason              VARCHAR,
    last_line_no        INTEGER,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (sheet_id, revision)
);