Merge pull request #2 from nsfisis/claude/deduplicate-feed-items-JDLw3HEADmain

Prevent duplicate feed items with same GUID

4 files changed, 32 insertions, 0 deletions

diff --git a/backend/db/articles.sql.go b/backend/db/articles.sql.go
index bf6ea1d..7f6400b 100644
--- a/backend/db/articles.sql.go
+++ b/backend/db/articles.sql.go
@@ -28,6 +28,20 @@ func (q *Queries) CheckArticleExists(ctx context.Context, arg CheckArticleExists
 	return article_exists, err
 }
 
+const checkArticleExistsByGUID = `-- name: CheckArticleExistsByGUID :one
+SELECT EXISTS(
+    SELECT 1 FROM articles
+    WHERE guid = ?
+) as article_exists
+`
+
+func (q *Queries) CheckArticleExistsByGUID(ctx context.Context, guid string) (int64, error) {
+	row := q.db.QueryRowContext(ctx, checkArticleExistsByGUID, guid)
+	var article_exists int64
+	err := row.Scan(&article_exists)
+	return article_exists, err
+}
+
 const createArticle = `-- name: CreateArticle :one
 INSERT INTO articles (feed_id, guid, title, url, is_read)
 VALUES (?, ?, ?, ?, ?)
diff --git a/backend/db/migrations/005_add_guid_index.sql b/backend/db/migrations/005_add_guid_index.sql
new file mode 100644
index 0000000..e3625ee
--- /dev/null
+++ b/backend/db/migrations/005_add_guid_index.sql
@@ -0,0 +1,10 @@
+-- Add index on guid for deduplication
+CREATE INDEX IF NOT EXISTS idx_articles_guid ON articles(guid);
+
+-- Remove duplicate articles by guid, keeping only the one with the smallest id
+DELETE FROM articles
+WHERE id NOT IN (
+    SELECT MIN(id)
+    FROM articles
+    GROUP BY guid
+);
diff --git a/backend/db/queries/articles.sql b/backend/db/queries/articles.sql
index 5acdada..2c00678 100644
--- a/backend/db/queries/articles.sql
+++ b/backend/db/queries/articles.sql
@@ -71,3 +71,9 @@ SELECT EXISTS(
     SELECT 1 FROM articles
     WHERE feed_id = ? AND guid = ?
 ) as article_exists;
+
+-- name: CheckArticleExistsByGUID :one
+SELECT EXISTS(
+    SELECT 1 FROM articles
+    WHERE guid = ?
+) as article_exists;
diff --git a/backend/db/schema.sql b/backend/db/schema.sql
index adf3b37..07ac72d 100644
--- a/backend/db/schema.sql
+++ b/backend/db/schema.sql
@@ -34,4 +34,6 @@ CREATE INDEX IF NOT EXISTS idx_articles_feed_guid ON articles(feed_id, guid);
 
 CREATE INDEX IF NOT EXISTS idx_articles_is_read ON articles(is_read);
 
+CREATE INDEX IF NOT EXISTS idx_articles_guid ON articles(guid);
+
 CREATE INDEX IF NOT EXISTS idx_feeds_user_id ON feeds(user_id);