From e18dc4c93eedc925bed6cc0754b200c58bcdf7f8 Mon Sep 17 00:00:00 2001
From: Mohamed Bassem <me@mbassem.com>
Date: Mon, 22 Dec 2025 15:52:46 +0200
Subject: fix: optimize tagging db queries (#2287)

* fix: optimize tagging db queries

* review

* parallel queries

* refactoring
---
 packages/db/schema.ts | 11 ++++++++++-
 1 file changed, 10 insertions(+), 1 deletion(-)

(limited to 'packages/db/schema.ts')

diff --git a/packages/db/schema.ts b/packages/db/schema.ts
index cc4d4b56..fbb8d24f 100644
--- a/packages/db/schema.ts
+++ b/packages/db/schema.ts
@@ -1,6 +1,6 @@
 import type { AdapterAccount } from "@auth/core/adapters";
 import { createId } from "@paralleldrive/cuid2";
-import { relations } from "drizzle-orm";
+import { relations, sql, SQL } from "drizzle-orm";
 import {
   AnySQLiteColumn,
   foreignKey,
@@ -366,6 +366,14 @@ export const bookmarkTags = sqliteTable(
       .primaryKey()
       .$defaultFn(() => createId()),
     name: text("name").notNull(),
+    normalizedName: text("normalizedName").generatedAlwaysAs(
+      (): SQL =>
+        // This function needs to be in sync with the tagNormalizer function in tagging.ts
+        sql`lower(replace(replace(replace(${bookmarkTags.name}, ' ', ''), '-', ''), '_', ''))`,
+      {
+        mode: "virtual",
+      },
+    ),
     createdAt: createdAtField(),
     userId: text("userId")
       .notNull()
@@ -376,6 +384,7 @@ export const bookmarkTags = sqliteTable(
     unique("bookmarkTags_userId_id_idx").on(bt.userId, bt.id),
     index("bookmarkTags_name_idx").on(bt.name),
     index("bookmarkTags_userId_idx").on(bt.userId),
+    index("bookmarkTags_normalizedName_idx").on(bt.normalizedName),
   ],
 );
 
-- 
cgit v1.2.3-70-g09d2