james
393e7f7209
Shop-owned timed sessions carry SKU activity items with their own reserved inventory and per-customer limit. Checkout resolves eligibility on the server, locks candidate items by primary key after the SKU locks, and splits a cart line into an activity-priced item plus a standard-priced remainder, so every unit price snapshot is honest and a customer cannot exceed the limit. Reserved activity stock and SKU stock decrement together under guards, and a pending-payment cancellation restores both plus any redeemed coupon. Coupons are rejected on a shop order that applied activity pricing, and a SKU cannot join two overlapping enabled sessions. The overlap check against group buying is present but dormant: that capability lands later, so the check activates only once its table exists. Surfaces (shop-admin, mall) and seeding follow.
43 lines
2.0 KiB
SQL
43 lines
2.0 KiB
SQL
-- Flash sales: a shop-owned timed session with SKU-level activity items that
|
|
-- hold their own reserved inventory and per-customer limit. An order line that
|
|
-- received activity pricing points at the item it consumed, so cancellation can
|
|
-- restore both the SKU stock and the reserved activity stock.
|
|
|
|
CREATE TABLE flash_sale_sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
shop_id UUID NOT NULL REFERENCES shops (id) ON DELETE CASCADE,
|
|
label JSONB NOT NULL,
|
|
starts_at TIMESTAMPTZ NOT NULL,
|
|
ends_at TIMESTAMPTZ NOT NULL,
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT flash_sale_sessions_window CHECK (ends_at >= starts_at)
|
|
);
|
|
|
|
CREATE INDEX flash_sale_sessions_shop_idx ON flash_sale_sessions (shop_id);
|
|
|
|
CREATE TABLE flash_sale_items (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
session_id UUID NOT NULL REFERENCES flash_sale_sessions (id) ON DELETE CASCADE,
|
|
sku_id UUID NOT NULL REFERENCES skus (id) ON DELETE CASCADE,
|
|
sale_price_minor BIGINT NOT NULL CHECK (sale_price_minor > 0),
|
|
currency CHAR(3) NOT NULL REFERENCES currencies (code),
|
|
-- Remaining activity inventory; decremented conditionally at checkout.
|
|
reserved_stock INT NOT NULL CHECK (reserved_stock >= 0),
|
|
sold_count INT NOT NULL DEFAULT 0 CHECK (sold_count >= 0),
|
|
per_customer_limit INT NOT NULL CHECK (per_customer_limit > 0),
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE UNIQUE INDEX flash_sale_items_session_sku_idx ON flash_sale_items (session_id, sku_id);
|
|
CREATE INDEX flash_sale_items_sku_idx ON flash_sale_items (sku_id);
|
|
|
|
-- Nullable: standard-priced lines keep NULL, and deleting an activity item
|
|
-- leaves the order line intact.
|
|
ALTER TABLE order_items
|
|
ADD COLUMN flash_sale_item_id UUID REFERENCES flash_sale_items (id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX idx_order_items_flash ON order_items (flash_sale_item_id);
|