refactor: extract assets into their own database table. #215 (#220)

* Allow downloading more content from a webpage and index it #215
added a new table that contains the information about assets for link bookmarks
created migration code that transfers the existing data into the new table

* Allow downloading more content from a webpage and index it #215
removed the old asset columns from the database
updated the UI to use the data from the linkBookmarkAssets array

* generalize the assets table to not be linked in particular to links

* fix migrations post merge

* fix missing asset ids in the getBookmarks call

---------

Co-authored-by: MohamedBassem <me@mbassem.com>

1 files changed, 37 insertions, 3 deletions

diff --git a/packages/db/schema.ts b/packages/db/schema.ts
index 07f1686e..63d23782 100644
--- a/packages/db/schema.ts
+++ b/packages/db/schema.ts
@@ -143,9 +143,6 @@ export const bookmarkLinks = sqliteTable(
     favicon: text("favicon"),
     content: text("content"),
     htmlContent: text("htmlContent"),
-    screenshotAssetId: text("screenshotAssetId"),
-    fullPageArchiveAssetId: text("fullPageArchiveAssetId"),
-    imageAssetId: text("imageAssetId"),
     crawledAt: integer("crawledAt", { mode: "timestamp" }),
     crawlStatus: text("crawlStatus", {
       enum: ["pending", "failure", "success"],
@@ -158,6 +155,35 @@ export const bookmarkLinks = sqliteTable(
   },
 );
 
+export const enum AssetTypes {
+  LINK_BANNER_IMAGE = "linkBannerImage",
+  LINK_SCREENSHOT = "linkScreenshot",
+  LINK_FULL_PAGE_ARCHIVE = "linkFullPageArchive",
+}
+
+export const assets = sqliteTable(
+  "assets",
+  {
+    // Asset ids don't have a default function as they are generated by the caller
+    id: text("id").notNull().primaryKey(),
+    assetType: text("assetType", {
+      enum: [
+        AssetTypes.LINK_BANNER_IMAGE,
+        AssetTypes.LINK_SCREENSHOT,
+        AssetTypes.LINK_FULL_PAGE_ARCHIVE,
+      ],
+    }).notNull(),
+    bookmarkId: text("bookmarkId")
+      .notNull()
+      .references(() => bookmarks.id, { onDelete: "cascade" }),
+  },
+
+  (tb) => ({
+    bookmarkIdIdx: index("assets_bookmarkId_idx").on(tb.bookmarkId),
+    assetTypeIdx: index("assets_assetType_idx").on(tb.assetType),
+  }),
+);
+
 export const bookmarkTexts = sqliteTable("bookmarkTexts", {
   id: text("id")
     .notNull()
@@ -292,6 +318,14 @@ export const bookmarkRelations = relations(bookmarks, ({ many, one }) => ({
   }),
   tagsOnBookmarks: many(tagsOnBookmarks),
   bookmarksInLists: many(bookmarksInLists),
+  assets: many(assets),
+}));
+
+export const assetRelations = relations(assets, ({ one }) => ({
+  bookmark: one(bookmarks, {
+    fields: [assets.bookmarkId],
+    references: [bookmarks.id],
+  }),
 }));
 
 export const bookmarkTagsRelations = relations(