32 lines
1.6 KiB
SQL
32 lines
1.6 KiB
SQL
-- Subscriptions: a logged-in user follows someone else's shared list or a
|
|
-- single shared item, to receive the same price-drop emails the owner gets.
|
|
-- Exactly one of list_id / item_id is set per row. A list subscription
|
|
-- implicitly covers every item on that list — present and future — expanded
|
|
-- at notify time rather than materialised per item.
|
|
CREATE TABLE subscriptions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
list_id UUID REFERENCES lists(id) ON DELETE CASCADE,
|
|
item_id UUID REFERENCES items(id) ON DELETE CASCADE,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
CONSTRAINT subscription_target_exactly_one
|
|
CHECK ((list_id IS NOT NULL) <> (item_id IS NOT NULL))
|
|
);
|
|
|
|
-- One subscription per (user, list) and per (user, item).
|
|
CREATE UNIQUE INDEX subscriptions_user_list
|
|
ON subscriptions (user_id, list_id) WHERE list_id IS NOT NULL;
|
|
CREATE UNIQUE INDEX subscriptions_user_item
|
|
ON subscriptions (user_id, item_id) WHERE item_id IS NOT NULL;
|
|
|
|
-- Per-subscriber, per-item de-dupe latch for target-price alerts — the
|
|
-- subscriber-side mirror of items.notified_at (which latches the owner).
|
|
-- Present = already announced this drop; absent = armed. Cleared when the
|
|
-- price climbs back above the item's target.
|
|
CREATE TABLE subscription_notify (
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE,
|
|
notified_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
PRIMARY KEY (user_id, item_id)
|
|
);
|