dodil/erp-platform

tests/scenario_bricks/scn_orders/schema.sql

-- orders (scenario-harness test brick): an order and its lines (versioned), and manual tasks.
CREATE TABLE IF NOT EXISTS orders_orders (
    order_id      VARCHAR NOT NULL,
    customer      VARCHAR NOT NULL,
    owner         VARCHAR NOT NULL,
    status        VARCHAR NOT NULL,
    currency      VARCHAR NOT NULL,
    placed_at     TIMESTAMP,
    due_at        TIMESTAMP,
    reserved      BOOLEAN,
    reserve_note  VARCHAR,
    note          VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    version       INTEGER,
    PRIMARY KEY (order_id)
);

CREATE TABLE IF NOT EXISTS orders_lines (
    order_id      VARCHAR NOT NULL,
    line_no       INTEGER NOT NULL,
    sku           VARCHAR NOT NULL,
    quantity      INTEGER NOT NULL,
    unit_price    NUMERIC(18,2) NOT NULL,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (order_id, line_no)
);

CREATE TABLE IF NOT EXISTS orders_tasks (
    task_key      VARCHAR NOT NULL,
    order_id      VARCHAR NOT NULL,
    capability    VARCHAR NOT NULL,
    input_json    VARCHAR NOT NULL,
    plan          VARCHAR,
    reason        VARCHAR,
    state         VARCHAR,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (task_key)
);

-- Optimistic versions (erp_platform.repository): a save CLAIMS version n+1 with a plain INSERT here,
-- in the same transaction as its rows; a racer from the same version fails 23505 -> 409.
CREATE TABLE IF NOT EXISTS orders_orders_versions (
    order_id VARCHAR NOT NULL,
    version INTEGER NOT NULL,
    saved_at TIMESTAMP WITHOUT TIME ZONE NOT NULL,
    PRIMARY KEY (order_id, version)
);