22 lines
947 B
SQL
22 lines
947 B
SQL
-- Bookmarks are always private — they're the user's "save my place" markers.
|
|
-- We don't store the full passage text, only the source URL + a short excerpt
|
|
-- so the bookmarks index can render meaningful previews without a separate
|
|
-- content fetch.
|
|
--
|
|
-- UNIQUE (user_id, source_url) means re-bookmarking the same passage is a
|
|
-- no-op rather than producing a duplicate; the service maps the unique
|
|
-- violation to a 200 idempotent response.
|
|
CREATE TABLE IF NOT EXISTS bookmarks (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
|
source_url TEXT NOT NULL,
|
|
source_title VARCHAR(200),
|
|
excerpt TEXT,
|
|
note TEXT,
|
|
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
|
|
UNIQUE (user_id, source_url)
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS bookmarks_user_created_idx
|
|
ON bookmarks (user_id, created_at DESC);
|