james
a7bc476251
Shop-owned activities on one SKU, concrete groups with an open/successful/expired/cancelled lifecycle, and one paid membership row per paid order. Checkout accepts a single-SKU quantity-1 intent, snapshots the group price and identity on the pending order, and opens or references a group; a paid seat is claimed only at payment, which locks the group and fills it exactly at capacity. Pending-payment cancellation restores SKU stock only and never rolls back paid seats; an unpaid opener cancelling closes a still-empty group. Coupons are refused on a group shop order, and the flash-sale exclusion is now enforced in both directions because the activity table exists, which activates the guard add-flash-sales shipped dormant. The activity column names follow the contract recorded in this change's design. Surfaces (shop-admin, mall) and seeding follow.
67 lines
3.1 KiB
SQL
67 lines
3.1 KiB
SQL
-- Group buying: a shop-owned timed activity on one SKU, concrete group
|
|
-- instances with a lifecycle, and one paid membership row per paid order.
|
|
--
|
|
-- The activity column names are a contract: the archived flash-sales guard
|
|
-- queries `group_buying_activities` by `sku_id`, `enabled`, `starts_at`, and
|
|
-- `ends_at` to reject a SKU that is in both activities at once.
|
|
|
|
CREATE TYPE collective_group_status AS ENUM ('open', 'successful', 'expired', 'cancelled');
|
|
|
|
CREATE TABLE group_buying_activities (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
shop_id UUID NOT NULL REFERENCES shops (id) ON DELETE CASCADE,
|
|
sku_id UUID NOT NULL REFERENCES skus (id) ON DELETE CASCADE,
|
|
name JSONB NOT NULL,
|
|
description JSONB,
|
|
image TEXT,
|
|
group_price_minor BIGINT NOT NULL CHECK (group_price_minor > 0),
|
|
currency CHAR(3) NOT NULL REFERENCES currencies (code),
|
|
required_members INT NOT NULL CHECK (required_members >= 2),
|
|
starts_at TIMESTAMPTZ NOT NULL,
|
|
ends_at TIMESTAMPTZ NOT NULL,
|
|
group_lifetime_hours INT NOT NULL CHECK (group_lifetime_hours > 0),
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT group_buying_activities_window CHECK (ends_at >= starts_at)
|
|
);
|
|
|
|
CREATE INDEX group_buying_activities_shop_idx ON group_buying_activities (shop_id);
|
|
CREATE INDEX group_buying_activities_sku_idx ON group_buying_activities (sku_id);
|
|
|
|
CREATE TABLE collective_groups (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
activity_id UUID NOT NULL REFERENCES group_buying_activities (id) ON DELETE CASCADE,
|
|
-- The pending order that opened the group; it may be cancelled later.
|
|
leader_order_id UUID REFERENCES orders (id) ON DELETE SET NULL,
|
|
paid_member_count INT NOT NULL DEFAULT 0 CHECK (paid_member_count >= 0),
|
|
status collective_group_status NOT NULL DEFAULT 'open',
|
|
expires_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
CREATE INDEX collective_groups_activity_idx ON collective_groups (activity_id, status);
|
|
|
|
CREATE TABLE collective_group_members (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
group_id UUID NOT NULL REFERENCES collective_groups (id) ON DELETE CASCADE,
|
|
order_id UUID NOT NULL REFERENCES orders (id) ON DELETE CASCADE,
|
|
user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
joined_at TIMESTAMPTZ NOT NULL DEFAULT now()
|
|
);
|
|
|
|
-- One paid seat per order, and one membership per customer per group.
|
|
CREATE UNIQUE INDEX collective_group_members_order_idx ON collective_group_members (order_id);
|
|
CREATE UNIQUE INDEX collective_group_members_user_idx
|
|
ON collective_group_members (group_id, user_id);
|
|
CREATE INDEX collective_group_members_group_idx ON collective_group_members (group_id);
|
|
|
|
-- Group identity snapshotted on the order at checkout.
|
|
ALTER TABLE orders
|
|
ADD COLUMN group_activity_id UUID REFERENCES group_buying_activities (id) ON DELETE SET NULL;
|
|
ALTER TABLE orders
|
|
ADD COLUMN group_id UUID REFERENCES collective_groups (id) ON DELETE SET NULL;
|
|
|
|
CREATE INDEX idx_orders_group ON orders (group_id);
|