| 1 |
|
| 2 |
CREATE TABLE collections ( |
| 3 |
id UUID PRIMARY KEY DEFAULT gen_random_uuid(), |
| 4 |
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, |
| 5 |
slug TEXT NOT NULL, |
| 6 |
title TEXT NOT NULL, |
| 7 |
description TEXT, |
| 8 |
is_public BOOLEAN NOT NULL DEFAULT false, |
| 9 |
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), |
| 10 |
updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), |
| 11 |
CONSTRAINT uq_collection_user_slug UNIQUE (user_id, slug) |
| 12 |
); |
| 13 |
|
| 14 |
CREATE INDEX idx_collections_user_id ON collections(user_id); |
| 15 |
CREATE INDEX idx_collections_public ON collections(is_public) WHERE is_public = true; |
| 16 |
|
| 17 |
CREATE TABLE collection_items ( |
| 18 |
collection_id UUID NOT NULL REFERENCES collections(id) ON DELETE CASCADE, |
| 19 |
item_id UUID NOT NULL REFERENCES items(id) ON DELETE CASCADE, |
| 20 |
position INT NOT NULL DEFAULT 0, |
| 21 |
added_at TIMESTAMPTZ NOT NULL DEFAULT NOW(), |
| 22 |
PRIMARY KEY (collection_id, item_id) |
| 23 |
); |
| 24 |
|
| 25 |
CREATE INDEX idx_collection_items_item_id ON collection_items(item_id); |
| 26 |
|