dodil/erp-brick-pos

DODIL ERP brick: point of sale

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

-- 00001_init: the brick's first schema (pos v1, slice A). The versioned layout migrations/NNNNN_<name>/{up,down}.sql with a
-- per-brick journal, pos_schema_migrations. A later slice adds 00002_<name>; it never edits this file.

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

CREATE TABLE IF NOT EXISTS pos_cash_move (
    cash_move_id VARCHAR NOT NULL,
    session_id   VARCHAR,
    seq          INTEGER,
    kind         VARCHAR,
    amount       NUMERIC(18,2),
    reason_code  VARCHAR,
    approved_by  VARCHAR,
    approved_sub VARCHAR,
    created_by   VARCHAR,
    created_sub  VARCHAR,
    created_at   TIMESTAMP,
    PRIMARY KEY (cash_move_id)
);

CREATE TABLE IF NOT EXISTS pos_claim (
    claim_key  VARCHAR NOT NULL,
    kind       VARCHAR,
    session_id VARCHAR,
    claimed_by VARCHAR,
    claimed_at TIMESTAMP,
    PRIMARY KEY (claim_key)
);

CREATE TABLE IF NOT EXISTS pos_outbox (
    target     VARCHAR NOT NULL,
    ref        VARCHAR NOT NULL,
    payload    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 pos_payment_line (
    receipt_id       VARCHAR NOT NULL,
    pay_no           INTEGER NOT NULL,
    method           VARCHAR,
    amount           NUMERIC(18,2),
    tip_amount       NUMERIC(18,2),
    change_amount    NUMERIC(18,2),
    terminal_txn_ref VARCHAR,
    terminal_status  VARCHAR,
    idempotency_key  VARCHAR,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (receipt_id, pay_no)
);

CREATE TABLE IF NOT EXISTS pos_receipt (
    receipt_id    VARCHAR NOT NULL,
    session_id    VARCHAR,
    shop_id       VARCHAR,
    register_id   VARCHAR,
    receipt_no    INTEGER,
    kind          VARCHAR,
    business_date DATE,
    currency      VARCHAR,
    eat_in        BOOLEAN,
    gross         NUMERIC(18,2),
    net           NUMERIC(18,2),
    vat           NUMERIC(18,2),
    created_by    VARCHAR,
    created_sub   VARCHAR,
    finalised_at  TIMESTAMP,
    status        VARCHAR,
    version       INTEGER,
    created_at    TIMESTAMP,
    updated_at    TIMESTAMP,
    PRIMARY KEY (receipt_id)
);

CREATE TABLE IF NOT EXISTS pos_receipt_line (
    receipt_id   VARCHAR NOT NULL,
    line_no      INTEGER NOT NULL,
    product_id   VARCHAR,
    description  VARCHAR,
    quantity     NUMERIC(18,3),
    unit_code    VARCHAR,
    unit_price   NUMERIC(19,4),
    line_gross   NUMERIC(18,2),
    tax_category VARCHAR,
    tax_code     VARCHAR,
    rate_pct     NUMERIC(9,4),
    vat          NUMERIC(18,2),
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (receipt_id, line_no)
);

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

CREATE TABLE IF NOT EXISTS pos_register (
    shop_id              VARCHAR NOT NULL,
    register_id          VARCHAR NOT NULL,
    terminal_id          VARCHAR,
    fiscal_device_serial VARCHAR,
    fiscal_device_status VARCHAR,
    status               VARCHAR,
    created_at           TIMESTAMP,
    updated_at           TIMESTAMP,
    PRIMARY KEY (shop_id, register_id)
);

CREATE TABLE IF NOT EXISTS pos_session (
    session_id       VARCHAR NOT NULL,
    shop_id          VARCHAR,
    register_id      VARCHAR,
    session_no       INTEGER,
    business_date    DATE,
    currency         VARCHAR,
    opened_by        VARCHAR,
    opened_sub       VARCHAR,
    opened_at        TIMESTAMP,
    opening_float    NUMERIC(18,2),
    float_difference NUMERIC(18,2),
    counted_by       VARCHAR,
    counted_sub      VARCHAR,
    counted_at       TIMESTAMP,
    counted_cash     NUMERIC(18,2),
    expected_cash    NUMERIC(18,2),
    difference       NUMERIC(18,2),
    closing_float    NUMERIC(18,2),
    approved_by      VARCHAR,
    approved_sub     VARCHAR,
    approved_at      TIMESTAMP,
    reason           VARCHAR,
    status           VARCHAR,
    version          INTEGER,
    created_at       TIMESTAMP,
    updated_at       TIMESTAMP,
    PRIMARY KEY (session_id)
);

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

CREATE TABLE IF NOT EXISTS pos_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 pos_shop (
    shop_id                 VARCHAR NOT NULL,
    name                    VARCHAR,
    company_code            VARCHAR,
    warehouse_id            VARCHAR,
    consumption_location_id VARCHAR,
    price_list_id           VARCHAR,
    fiscal_profile          VARCHAR,
    country                 VARCHAR,
    cost_centre             VARCHAR,
    day_cutoff_time         VARCHAR,
    status                  VARCHAR,
    version                 INTEGER,
    created_at              TIMESTAMP,
    updated_at              TIMESTAMP,
    PRIMARY KEY (shop_id)
);

CREATE TABLE IF NOT EXISTS pos_shop_user (
    shop_id     VARCHAR NOT NULL,
    sub         VARCHAR NOT NULL,
    email       VARCHAR,
    assigned_by VARCHAR,
    created_at  TIMESTAMP,
    updated_at  TIMESTAMP,
    PRIMARY KEY (shop_id, sub)
);

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