Schema design for B2B orders with per-customer price lists and validity ranges

-- ======================================================================
-- Q: Design a schema for B2B orders with per-customer price lists that are valid
-- from-to.
--
-- Points I explain out loud in the interview:
-- - EXCLUDE with daterange: the database forbids two price lists that overlap in
--   time for the same customer. With a check in code there would be a race
--   condition between two concurrent admins.
-- - Partial unique index: one draft cart per customer, but unlimited completed
--   orders. Same for the ERP external_ref, which makes the sync idempotent
--   (INSERT ... ON CONFLICT).
-- - Snapshot of price and VAT on the line: if the price list changes, the
--   invoice does not change.
-- - ON DELETE RESTRICT for customers with history, CASCADE only for "children"
--   that make no sense on their own.
-- - In Django: ExclusionConstraint (django.contrib.postgres),
--   UniqueConstraint(condition=Q(...)), CheckConstraint.
-- ======================================================================

CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE customers (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    vat_number  varchar(20) NOT NULL UNIQUE,
    name        text        NOT NULL,
    kind        text        NOT NULL CHECK (kind IN ('b2b', 'b2c')),
    deleted_at  timestamptz
);

CREATE TABLE products (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    sku         varchar(40) NOT NULL UNIQUE,
    name        text        NOT NULL,
    vat_rate    numeric(4,2) NOT NULL CHECK (vat_rate >= 0)
);

CREATE TABLE price_lists (
    id          bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id bigint NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
    currency    char(3) NOT NULL DEFAULT 'EUR',
    valid       daterange NOT NULL,
    -- overlapping price lists for the same customer are not allowed
    EXCLUDE USING gist (customer_id WITH =, valid WITH &&)
);

CREATE TABLE price_list_items (
    price_list_id bigint NOT NULL REFERENCES price_lists(id) ON DELETE CASCADE,
    product_id    bigint NOT NULL REFERENCES products(id),
    unit_cents    integer NOT NULL CHECK (unit_cents >= 0),
    PRIMARY KEY (price_list_id, product_id)
);

CREATE TABLE orders (
    id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    customer_id  bigint NOT NULL REFERENCES customers(id),
    status       text   NOT NULL DEFAULT 'draft'
                 CHECK (status IN ('draft','placed','paid','shipped','cancelled')),
    external_ref varchar(64),               -- ERP code
    total_cents  bigint NOT NULL DEFAULT 0 CHECK (total_cents >= 0),
    created_at   timestamptz NOT NULL DEFAULT now()
);
-- only one open cart (draft) per customer: partial unique index
CREATE UNIQUE INDEX ux_orders_one_draft ON orders (customer_id) WHERE status = 'draft';
CREATE UNIQUE INDEX ux_orders_external_ref ON orders (external_ref)
    WHERE external_ref IS NOT NULL;
CREATE INDEX ix_orders_cust_created ON orders (customer_id, created_at DESC);

CREATE TABLE order_lines (
    order_id    bigint  NOT NULL REFERENCES orders(id) ON DELETE CASCADE,
    line_no     smallint NOT NULL,
    product_id  bigint  NOT NULL REFERENCES products(id),
    qty         integer NOT NULL CHECK (qty > 0),
    unit_cents  integer NOT NULL,           -- snapshot of the price at the time of the order
    vat_rate    numeric(4,2) NOT NULL,      -- snapshot of VAT
    PRIMARY KEY (order_id, line_no)
);