james
9c2b705880
Platform-owned points products and a redemption order lifecycle kept separate from cash orders. Redeeming locks the published product, reserves stock, creates the order, debits points through the archived customer-accounts ledger with the order as reference, and snapshots the line in one transaction; a failure leaves no order, no stock change, and no ledger entry. Fulfilment moves only from pending_fulfillment, and customers see only their own redemptions. Demo seeding credits the demo customer through the same guarded credit path with reason seed, once, so no balance is ever written absolutely. Surfaces (admin console, mall points page) and product seeding follow.
53 lines
2.1 KiB
SQL
53 lines
2.1 KiB
SQL
-- Points mall: a platform-owned catalog of redeemable products plus a
|
|
-- redemption order lifecycle kept separate from cash orders and payments.
|
|
-- Redemptions snapshot the product and the shipping address; they never share
|
|
-- merchant SKU inventory.
|
|
|
|
CREATE TYPE integral_order_status AS ENUM ('pending_fulfillment', 'fulfilled', 'cancelled');
|
|
|
|
CREATE SEQUENCE integral_order_no_seq;
|
|
|
|
CREATE TABLE integral_products (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
name JSONB NOT NULL,
|
|
subtitle JSONB,
|
|
content JSONB,
|
|
image TEXT,
|
|
points_price BIGINT NOT NULL CHECK (points_price > 0),
|
|
stock INT NOT NULL CHECK (stock >= 0),
|
|
published BOOLEAN NOT NULL DEFAULT FALSE,
|
|
recommend BOOLEAN NOT NULL DEFAULT FALSE,
|
|
position INT NOT NULL DEFAULT 0,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX integral_products_public_idx ON integral_products (published, position);
|
|
|
|
CREATE TABLE integral_orders (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
order_no TEXT NOT NULL UNIQUE,
|
|
user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
status integral_order_status NOT NULL DEFAULT 'pending_fulfillment',
|
|
total_points BIGINT NOT NULL CHECK (total_points > 0),
|
|
shipping_address JSONB NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX integral_orders_user_idx ON integral_orders (user_id, created_at DESC);
|
|
CREATE INDEX integral_orders_status_idx ON integral_orders (status);
|
|
|
|
CREATE TABLE integral_order_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
order_id UUID NOT NULL REFERENCES integral_orders (id) ON DELETE CASCADE,
|
|
-- Nullable so a deleted catalog item keeps the redemption snapshot.
|
|
product_id UUID REFERENCES integral_products (id) ON DELETE SET NULL,
|
|
name JSONB NOT NULL,
|
|
image TEXT,
|
|
points_price BIGINT NOT NULL CHECK (points_price > 0),
|
|
qty INT NOT NULL CHECK (qty > 0)
|
|
);
|
|
|
|
CREATE INDEX integral_order_items_order_idx ON integral_order_items (order_id);
|