dodil/erp-brick-projects

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

-- erp-brick-projects (projects): every table prefixed pj_, a natural primary key each (never SERIAL), creation
-- columns last so a later column is an append. Additive only: a change is a new migration, never an edit here.
-- pj_project_versions / pj_item_versions: a save CLAIMS version n+1 with a plain INSERT, in the same
-- transaction as its rows; a racer from the same version fails 23505 -> 409 (erp_platform.repository).

CREATE TABLE IF NOT EXISTS pj_event (
    event_id   VARCHAR NOT NULL,
    subject_id VARCHAR,
    kind       VARCHAR,
    from_state VARCHAR,
    to_state   VARCHAR,
    actor      VARCHAR,
    detail     VARCHAR,
    event_at   TIMESTAMP,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (event_id)
);

CREATE TABLE IF NOT EXISTS pj_item (
    item_id         VARCHAR NOT NULL,
    project_id      VARCHAR,
    bp_id           BIGINT,
    source_kind     VARCHAR,
    source_id       VARCHAR,
    line_id         VARCHAR,
    product_id      VARCHAR,
    billing_kind    VARCHAR,
    billing_plan    VARCHAR,
    source          VARCHAR,
    title           VARCHAR,
    owner           VARCHAR,
    due_on          DATE,
    blocked_reason  VARCHAR,
    blocked_since   DATE,
    blocked_days    INTEGER,
    submitted_at    TIMESTAMP,
    submitted_by    VARCHAR,
    revision        INTEGER,
    warned_at       TIMESTAMP,
    approved_at     TIMESTAMP,
    approved_by     VARCHAR,
    approved_how    VARCHAR,
    delivered_at    TIMESTAMP,
    evidence        VARCHAR,
    evidence_kind   VARCHAR,
    makegood_for    VARCHAR,
    billing_status  VARCHAR,
    delivery_status VARCHAR,
    state           VARCHAR,
    version         INTEGER,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (item_id)
);

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

CREATE TABLE IF NOT EXISTS pj_project (
    project_id          VARCHAR NOT NULL,
    bp_id               BIGINT,
    source_kind         VARCHAR,
    source_id           VARCHAR,
    quote_id            VARCHAR,
    owner               VARCHAR,
    starts_on           DATE,
    ends_on             DATE,
    reconciled_at       TIMESTAMP,
    reconciliation_json VARCHAR,
    state               VARCHAR,
    version             INTEGER,
    created_at          TIMESTAMP,
    updated_at          TIMESTAMP,
    PRIMARY KEY (project_id)
);

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

CREATE TABLE IF NOT EXISTS pj_setting (
    setting_key VARCHAR NOT NULL,
    value       VARCHAR,
    changes     VARCHAR,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (setting_key)
);

CREATE TABLE IF NOT EXISTS pj_state (
    state_id   VARCHAR NOT NULL,
    name       VARCHAR,
    position   INTEGER,
    created_at TIMESTAMP,
    updated_at TIMESTAMP,
    PRIMARY KEY (state_id)
);