dodil/erp-brick-pos

DODIL ERP brick: point of sale

src/brick_pos/schema.sql

-- pos v1, slice A: every table prefixed `pos_`, a natural primary key each (never SERIAL), money NUMERIC(18,2), a unit price
-- NUMERIC(19,4). Additive only: a later slice appends tables and columns, never drops or renames.

CREATE TABLE IF NOT EXISTS pos_cash_deposit (
    deposit_ref     VARCHAR NOT NULL,
    shop_id         VARCHAR,
    bag_no          VARCHAR,
    amount          NUMERIC(18,2),
    currency        VARCHAR,
    company_code    VARCHAR,
    bank_account_id VARCHAR,
    expected_on     DATE,
    denominations   VARCHAR,
    sealed_by       VARCHAR,
    sealed_sub      VARCHAR,
    sealed_at       TIMESTAMP,
    status          VARCHAR,
    handed_over_at  TIMESTAMP,
    created_at      TIMESTAMP,
    updated_at      TIMESTAMP,
    PRIMARY KEY (deposit_ref)
);

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_category_rule (
    category_code     VARCHAR NOT NULL,
    restock_on_refund BOOLEAN,
    set_by            VARCHAR,
    created_at        TIMESTAMP,
    updated_at        TIMESTAMP,
    PRIMARY KEY (category_code)
);

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_line_component (
    receipt_id   VARCHAR NOT NULL,
    line_no      INTEGER NOT NULL,
    component_no INTEGER NOT NULL,
    product_id   VARCHAR,
    quantity     NUMERIC(19,6),
    unit_code    VARCHAR,
    created_at   TIMESTAMP,
    updated_at   TIMESTAMP,
    PRIMARY KEY (receipt_id, line_no, component_no)
);

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,
    voucher_code     VARCHAR,
    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,
    original_receipt_id VARCHAR,
    void_reason         VARCHAR,
    voided_by           VARCHAR,
    voided_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,
    original_line_no INTEGER,
    restock          BOOLEAN,
    kind             VARCHAR,
    voucher_code     VARCHAR,
    recipe_source    VARCHAR,
    recipe_ref       VARCHAR,
    recipe_version   INTEGER,
    modifiers        VARCHAR,
    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_recipe (
    product_id VARCHAR NOT NULL,
    version    INTEGER NOT NULL,
    valid_from DATE,
    created_by VARCHAR,
    created_at TIMESTAMP,
    PRIMARY KEY (product_id, version)
);

CREATE TABLE IF NOT EXISTS pos_recipe_line (
    product_id   VARCHAR NOT NULL,
    version      INTEGER NOT NULL,
    line_no      INTEGER NOT NULL,
    component_id VARCHAR,
    quantity     NUMERIC(18,3),
    unit_code    VARCHAR,
    modifier_id  VARCHAR,
    replaces     VARCHAR,
    PRIMARY KEY (product_id, version, line_no)
);

CREATE TABLE IF NOT EXISTS pos_refund_claim (
    original_receipt_id VARCHAR NOT NULL,
    line_no             INTEGER NOT NULL,
    claim_no            INTEGER NOT NULL,
    refund_receipt_id   VARCHAR,
    quantity            NUMERIC(18,3),
    gross               NUMERIC(18,2),
    vat                 NUMERIC(18,2),
    refunded_after      NUMERIC(18,3),
    created_at          TIMESTAMP,
    PRIMARY KEY (original_receipt_id, line_no, claim_no)
);

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_register_snapshot (
    shop_id            VARCHAR NOT NULL,
    business_date      DATE NOT NULL,
    snapshot_no        INTEGER NOT NULL,
    price_list_id      VARCHAR,
    country            VARCHAR,
    currency           VARCHAR,
    prices_include_tax BOOLEAN,
    valid_until        DATE,
    digest             VARCHAR,
    products_json      VARCHAR,
    rates_json         VARCHAR,
    built_at           TIMESTAMP,
    PRIMARY KEY (shop_id, business_date, snapshot_no)
);

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,
    last_receipt_no  INTEGER,
    claimed_gap_from INTEGER,
    claimed_gap_to   INTEGER,
    posted_at        TIMESTAMP,
    journal_id       VARCHAR,
    snapshot_no      INTEGER,
    snapshot_digest  VARCHAR,
    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)
);

CREATE TABLE IF NOT EXISTS pos_voucher (
    voucher_code      VARCHAR NOT NULL,
    company_code      VARCHAR,
    currency          VARCHAR,
    face_value        NUMERIC(18,2),
    profile           VARCHAR,
    issued_receipt_id VARCHAR,
    expires_on        DATE,
    status            VARCHAR,
    created_at        TIMESTAMP,
    PRIMARY KEY (voucher_code)
);

CREATE TABLE IF NOT EXISTS pos_voucher_claim (
    voucher_code  VARCHAR NOT NULL,
    claim_no      INTEGER NOT NULL,
    operation     VARCHAR,
    receipt_id    VARCHAR,
    pay_no        INTEGER,
    amount        NUMERIC(18,2),
    balance_after NUMERIC(18,2),
    created_at    TIMESTAMP,
    PRIMARY KEY (voucher_code, claim_no)
);