recally-io · vaayne · Feb 1, 2025 · Jan 27, 2025 · Jan 27, 2025 · Jan 27, 2025
diff --git a/database/bindata.go b/database/bindata.go
diff --git a/database/migrations/000012_split_bookmark_content.down.sql b/database/migrations/000012_split_bookmark_content.down.sql
@@ -0,0 +1,6 @@
+-- Drop tables in reverse order to handle dependencies
+DROP TABLE IF EXISTS bookmark_share;
+DROP TABLE IF EXISTS bookmark_tags_mapping;
+DROP TABLE IF EXISTS bookmark_tags;
+DROP TABLE IF EXISTS bookmarks;
+DROP TABLE IF EXISTS bookmark_content;
diff --git a/database/migrations/000012_split_bookmark_content.up.sql b/database/migrations/000012_split_bookmark_content.up.sql
@@ -0,0 +1,97 @@
+CREATE TABLE bookmark_content(
+    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+    type VARCHAR(50) NOT NULL, -- type of bookmark_content(bookmark, pdf, epub, image, podcast, video, etc.)
+    url TEXT NOT NULL, -- URL of the bookmark
+    user_id uuid DEFAULT NULL, -- when user is null, this content is shared, when it's not null, it will belong to the user
+    title TEXT,
+    description TEXT,
+    domain TEXT, -- domain of the URL
+    s3_key TEXT, -- S3 key for storing raw content like pdf, epub, video, etc.
+    summary TEXT, -- AI generated summary
+    content TEXT, -- content in markdown format
+    html TEXT, -- html content for web page
+    tags VARCHAR(50)[] DEFAULT '{}', -- tags for the content by default empty array
+    metadata JSONB DEFAULT '{}',
+    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    UNIQUE(url, user_id)
+  );
+
+-- Indexes
+CREATE INDEX idx_bookmark_content_type ON bookmark_content(type);
+CREATE INDEX idx_bookmark_content_url ON bookmark_content(url);
+CREATE INDEX idx_bookmark_content_domain ON bookmark_content(domain);
+CREATE INDEX idx_bookmark_content_created_at ON bookmark_content(created_at);
+CREATE INDEX idx_bookmark_content_metadata ON bookmark_content USING gin(metadata jsonb_path_ops);
+
+-- BM25 index on bookmark_content
+-- https://docs.paradedb.com/documentation/indexing/create_index
+CREATE INDEX idx_bookmark_content_bm25_search ON bookmark_content USING bm25(id, title, description, summary, content, metadata) WITH (key_field = 'id');
+CREATE TRIGGER update_bookmark_content_updated_at BEFORE UPDATE ON bookmark_content FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
+
+
+-- new bookmarks table
+DROP TABLE IF EXISTS bookmarks;
+CREATE TABLE bookmarks (
+    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id UUID REFERENCES users(uuid) ON DELETE CASCADE,
+    content_id UUID REFERENCES bookmark_content(id),
+    is_favorite BOOLEAN NOT NULL DEFAULT FALSE,
+    is_archive BOOLEAN NOT NULL DEFAULT FALSE,
+    metadata JSONB DEFAULT '{}',
+    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP
+  );
+
+CREATE INDEX idx_bookmarks_user_created_at ON bookmarks(user_id, created_at);
+CREATE INDEX idx_bookmarks_favorite ON bookmarks(user_id, is_favorite);
+CREATE INDEX idx_bookmarks_archive ON bookmarks(user_id, is_archive);
+CREATE INDEX idx_bookmarks_public ON bookmarks(user_id, is_public);
+CREATE INDEX idx_bookmarks_metadata ON bookmarks USING gin(metadata jsonb_path_ops);
+CREATE TRIGGER update_bookmarks_updated_at BEFORE UPDATE ON bookmarks FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
+
+
+-- bookmark_tags table
+CREATE TABLE bookmark_tags (
+    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+    name VARCHAR(50) NOT NULL,
+    user_id uuid NOT NULL REFERENCES users(uuid) ON DELETE CASCADE,
+    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    UNIQUE(user_id, name)
+  );
+
+CREATE INDEX idx_bookmark_tags_name ON bookmark_tags(name);
+CREATE TRIGGER update_bookmark_tags_updated_at BEFORE UPDATE ON bookmark_tags FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
+
+-- -- bookmark_content tags relationship
+CREATE TABLE bookmark_tags_mapping(
+    bookmark_id uuid REFERENCES bookmarks(id) ON DELETE CASCADE,
+    tag_id uuid REFERENCES bookmark_tags(id) ON DELETE CASCADE,
+    created_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP WITH TIME ZONE NOT NULL DEFAULT CURRENT_TIMESTAMP, 
+    PRIMARY KEY(bookmark_id, tag_id)
+  );
+
+CREATE INDEX idx_bookmark_tags_mapping_tag_id ON bookmark_tags_mapping(tag_id);
+CREATE TRIGGER update_bookmark_tags_mapping_updated_at BEFORE UPDATE ON bookmark_tags_mapping FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
+
+
+CREATE TABLE bookmark_share (
+    id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
+    user_id uuid NOT NULL REFERENCES users(uuid),
+    bookmark_id uuid REFERENCES bookmarks(id) ON DELETE CASCADE,
+    expires_at TIMESTAMP WITH TIME ZONE DEFAULT NULL,
+    created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
+    updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
+    UNIQUE(user_id, bookmark_id)
+);
+
+CREATE INDEX  idx_bookmark_share_user_id ON bookmark_share(user_id);
+CREATE INDEX idx_bookmark_share_content_id ON bookmark_share(bookmark_id);
+
+DROP TRIGGER IF EXISTS update_bookmark_share_updated_at ON bookmark_share;
+CREATE TRIGGER update_bookmark_share_updated_at
+    BEFORE UPDATE ON bookmark_share
+    FOR EACH ROW
+    EXECUTE FUNCTION update_updated_at_column();
diff --git a/database/queries/bookmark_content.sql b/database/queries/bookmark_content.sql
@@ -0,0 +1,56 @@
+-- name: IsBookmarkContentExistByURL :one
+SELECT EXISTS (
+  SELECT 1
+  FROM bookmark_content
+  WHERE url = $1
+);
+
+-- name: CreateBookmarkContent :one
+INSERT INTO bookmark_content (
+  type,
+  url,
+  user_id,
+  title,
+  description,
+  domain,
+  s3_key,
+  summary,
+  content,
+  html,
+  tags,
+  metadata
+) VALUES (
+  $1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11, $12
+) RETURNING *;
+
+
+-- name: GetBookmarkContentByID :one
+SELECT *
+FROM bookmark_content
+WHERE id = $1;
+
+-- name: GetBookmarkContentByBookmarkID :one
+SELECT bc.*
+FROM bookmarks b 
+  JOIN bookmark_content bc ON b.content_id = bc.id
+WHERE b.id = $1;
+
+-- name: GetBookmarkContentByURL :one
+-- First try to get user specific content, then the shared content
+SELECT *
+FROM bookmark_content
+WHERE url = $1 AND (user_id = $2 OR user_id IS NULL)
+LIMIT 1;
+
+-- name: UpdateBookmarkContent :one
+UPDATE bookmark_content
+SET title = COALESCE(sqlc.narg('title'), title),
+    description = COALESCE(sqlc.narg('description'), description),
+    s3_key = COALESCE(sqlc.narg('s3_key'), s3_key),
+    summary = COALESCE(sqlc.narg('summary'), summary),
+    content = COALESCE(sqlc.narg('content'), content),
+    html = COALESCE(sqlc.narg('html'), html),
+    tags = COALESCE(sqlc.narg('tags'), tags),
+    metadata = COALESCE(sqlc.narg('metadata'), metadata)
+WHERE id = $1
+RETURNING *;
diff --git a/database/queries/bookmark_share.sql b/database/queries/bookmark_share.sql
@@ -0,0 +1,39 @@
+-- name: CreateBookmarkShare :one
+INSERT INTO bookmark_share (user_id, bookmark_id, expires_at)
+VALUES ($1, $2, $3)
+RETURNING *;
+
+-- name: GetBookmarkShareContent :one
+SELECT bc.*
+FROM bookmark_share AS bs
+  JOIN bookmarks AS b ON bs.bookmark_id = b.id
+  JOIN bookmark_content AS bc ON b.content_id = bc.id
+WHERE bs.id = $1
+  AND (bs.expires_at is NULL OR bs.expires_at > now());
+
+-- name: GetBookmarkShare :one
+SELECT *
+FROM bookmark_share
+WHERE bookmark_id = $1
+  AND user_id = $2;
+
+-- name: UpdateBookmarkShareByBookmarkId :one
+UPDATE bookmark_share bs
+SET expires_at = $3
+FROM bookmarks b
+WHERE bs.bookmark_id = b.id
+  AND b.id = $1
+  AND b.user_id = $2
+RETURNING bs.*;
+
+-- name: DeleteBookmarkShareByBookmarkId :exec
+DELETE FROM bookmark_share bs
+USING bookmarks b
+WHERE bs.bookmark_id = b.id
+  AND b.id = $1
+  AND b.user_id = $2;
+
+-- name: DeleteExpiredBookmarkShare :exec
+DELETE
+FROM bookmark_share
+WHERE expires_at < now();
diff --git a/database/queries/bookmark_tags.sql b/database/queries/bookmark_tags.sql
@@ -0,0 +1,47 @@
+
+-- name: CreateBookmarkTag :one
+INSERT INTO bookmark_tags (name, user_id)
+VALUES ($1, $2)
+ON CONFLICT (name, user_id) DO UPDATE
+SET name = EXCLUDED.name
+RETURNING *;
+
+-- name: DeleteBookmarkTag :exec
+DELETE FROM bookmark_tags
+WHERE id = $1
+  AND user_id = $2;
+
+-- name: LinkBookmarkWithTags :exec
+INSERT INTO bookmark_tags_mapping (bookmark_id, tag_id)
+SELECT $1, bt.id
+FROM bookmark_tags bt
+WHERE bt.name = ANY ($2::text[])
+  AND bt.user_id = $3;
+
+-- name: UnLinkBookmarkWithTags :exec
+DELETE FROM bookmark_tags_mapping
+WHERE bookmark_id = $1
+  AND tag_id IN (SELECT id
+                 FROM bookmark_tags
+                 WHERE name = ANY ($2::text[])
+                   AND user_id = $3);
+
+-- name: ListExistingBookmarkTagsByTags :many
+SELECT name
+FROM bookmark_tags
+WHERE name = ANY ($1::text[])
+  AND user_id = $2;
+
+
+-- name: ListBookmarkTagsByUser :many
+SELECT name, count(*) as cnt
+FROM bookmark_tags
+WHERE user_id = $1
+GROUP BY name
+ORDER BY cnt DESC;
+
+-- name: ListBookmarkTagsByBookmarkId :many
+SELECT bt.name
+FROM bookmark_tags bt
+  JOIN bookmark_tags_mapping btm ON bt.id = btm.tag_id
+WHERE btm.bookmark_id = $1;