james
23955434c6
Templates belong to a shop; claiming copies their terms into a customer-owned snapshot so a later edit or disable cannot rewrite a held coupon. Claim stock is taken with a guarded decrement after locking the template, and a unique (user, template) index makes a duplicate claim a 409. Deleting a template leaves claimed snapshots standing via ON DELETE SET NULL. Checkout accepts at most one owned coupon per generated shop order, locks the selected coupons by primary key after the SKU locks, and resolves eligibility and the discount server-side (ownership, shop, status, window, converted threshold). The realized discount and coupon id land on the order, and a pending-payment cancellation restores the coupon in the same transaction as stock. The shared contract gains the coupon types, claim/list/manage methods, and the checkout coupon map; the fixed-data adapter implements the same surface. Surfaces (shop-admin management, mall coupon pages, checkout selection) and seeding still follow in tasks 3.1-4.2.
52 lines
2.4 KiB
SQL
52 lines
2.4 KiB
SQL
-- Shop coupons: a shop-owned template plus a customer-owned snapshot taken at
|
|
-- claim time. The snapshot copies terms and window so editing or disabling a
|
|
-- template cannot change a coupon a customer already holds.
|
|
--
|
|
-- `orders` gains the realized discount and the coupon it redeemed. Both tables
|
|
-- reference each other; ON DELETE SET NULL keeps either side deletable.
|
|
|
|
CREATE TYPE coupon_status AS ENUM ('claimed', 'redeemed', 'expired');
|
|
|
|
CREATE TABLE coupon_templates (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
shop_id UUID NOT NULL REFERENCES shops (id) ON DELETE CASCADE,
|
|
title JSONB NOT NULL,
|
|
amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
|
|
threshold_minor BIGINT NOT NULL CHECK (threshold_minor >= 0),
|
|
currency CHAR(3) NOT NULL REFERENCES currencies (code),
|
|
stock INT NOT NULL CHECK (stock >= 0),
|
|
enabled BOOLEAN NOT NULL DEFAULT TRUE,
|
|
starts_at TIMESTAMPTZ NOT NULL,
|
|
ends_at TIMESTAMPTZ NOT NULL,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
updated_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT coupon_templates_window CHECK (ends_at >= starts_at)
|
|
);
|
|
|
|
CREATE INDEX coupon_templates_shop_idx ON coupon_templates (shop_id);
|
|
|
|
CREATE TABLE coupons (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users (id) ON DELETE CASCADE,
|
|
-- Nullable so deleting a template keeps the snapshots customers already hold.
|
|
template_id UUID REFERENCES coupon_templates (id) ON DELETE SET NULL,
|
|
shop_id UUID NOT NULL REFERENCES shops (id) ON DELETE CASCADE,
|
|
title JSONB NOT NULL,
|
|
amount_minor BIGINT NOT NULL CHECK (amount_minor > 0),
|
|
threshold_minor BIGINT NOT NULL CHECK (threshold_minor >= 0),
|
|
currency CHAR(3) NOT NULL REFERENCES currencies (code),
|
|
starts_at TIMESTAMPTZ NOT NULL,
|
|
ends_at TIMESTAMPTZ NOT NULL,
|
|
status coupon_status NOT NULL DEFAULT 'claimed',
|
|
order_id UUID REFERENCES orders (id) ON DELETE SET NULL,
|
|
claimed_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
redeemed_at TIMESTAMPTZ
|
|
);
|
|
|
|
-- One claim per customer per template.
|
|
CREATE UNIQUE INDEX coupons_user_template_idx ON coupons (user_id, template_id);
|
|
CREATE INDEX coupons_user_idx ON coupons (user_id, status);
|
|
|
|
ALTER TABLE orders ADD COLUMN coupon_id UUID REFERENCES coupons (id) ON DELETE SET NULL;
|
|
ALTER TABLE orders ADD COLUMN discount_minor BIGINT NOT NULL DEFAULT 0 CHECK (discount_minor >= 0);
|