dodil/erp-brick-projects

src/brick_projects/schema.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,
    changes_reason  VARCHAR,
    work_ref        VARCHAR,
    submitted_sub   VARCHAR,
    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)
);

-- 00002_changes_reason (v0.1.1): what the client last asked to change. At the END of pj_item's CREATE above (a new
-- bucket) and added here for a bucket on 00001. erp-migrate marks an ADD COLUMN DISRUPTIVE: migrate the test copy
-- first, the live one at a quiet hour, before rolling the 0.1.1 image.
ALTER TABLE pj_item ADD COLUMN changes_reason VARCHAR;

-- 00003_work_ref (v0.1.4): where the team does the work (an https link; not evidence). At the END of pj_item's CREATE
-- above (a new bucket) and added here for a bucket on 00002. Migrate the test copy first, the live one at a quiet hour,
-- before rolling the 0.1.4 image.
ALTER TABLE pj_item ADD COLUMN work_ref VARCHAR;

-- 00004_submitted_sub (v0.1.4): the submitter's sub beside their email, so the accept_own gate holds for either
-- identity. At the END of pj_item's CREATE above (a new bucket) and added here for a bucket on 00003.
ALTER TABLE pj_item ADD COLUMN submitted_sub VARCHAR;