diff options
Diffstat (limited to 'packages/db/schema.ts')
| -rw-r--r-- | packages/db/schema.ts | 139 |
1 files changed, 135 insertions, 4 deletions
diff --git a/packages/db/schema.ts b/packages/db/schema.ts index 8f259d04..2b237e25 100644 --- a/packages/db/schema.ts +++ b/packages/db/schema.ts @@ -1,12 +1,13 @@ 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, index, integer, primaryKey, + real, sqliteTable, text, unique, @@ -69,6 +70,32 @@ export const users = sqliteTable("user", { .notNull() .default("weekly"), backupsRetentionDays: integer("backupsRetentionDays").notNull().default(30), + + // Reader view settings (nullable = opt-in, null means use client default) + readerFontSize: integer("readerFontSize"), + readerLineHeight: real("readerLineHeight"), + readerFontFamily: text("readerFontFamily", { + enum: ["serif", "sans", "mono"], + }), + + // AI Settings (nullable = opt-in, null means use server default) + autoTaggingEnabled: integer("autoTaggingEnabled", { mode: "boolean" }), + autoSummarizationEnabled: integer("autoSummarizationEnabled", { + mode: "boolean", + }), + tagStyle: text("tagStyle", { + enum: [ + "lowercase-hyphens", + "lowercase-spaces", + "lowercase-underscores", + "titlecase-spaces", + "titlecase-hyphens", + "camelCase", + "as-generated", + ], + }).default("titlecase-spaces"), + curatedTagIds: text("curatedTagIds", { mode: "json" }).$type<string[]>(), + inferredTagLang: text("inferredTagLang"), }); export const accounts = sqliteTable( @@ -142,6 +169,7 @@ export const apiKeys = sqliteTable( .$defaultFn(() => createId()), name: text("name").notNull(), createdAt: createdAtField(), + lastUsedAt: integer("lastUsedAt", { mode: "timestamp" }), keyId: text("keyId").notNull().unique(), keyHash: text("keyHash").notNull(), userId: text("userId") @@ -194,9 +222,21 @@ export const bookmarks = sqliteTable( }, (b) => [ index("bookmarks_userId_idx").on(b.userId), - index("bookmarks_archived_idx").on(b.archived), - index("bookmarks_favourited_idx").on(b.favourited), index("bookmarks_createdAt_idx").on(b.createdAt), + // Composite indexes for optimized pagination queries + index("bookmarks_userId_createdAt_id_idx").on(b.userId, b.createdAt, b.id), + index("bookmarks_userId_archived_createdAt_id_idx").on( + b.userId, + b.archived, + b.createdAt, + b.id, + ), + index("bookmarks_userId_favourited_createdAt_id_idx").on( + b.userId, + b.favourited, + b.createdAt, + b.id, + ), ], ); @@ -233,6 +273,7 @@ export const bookmarkLinks = sqliteTable( export const enum AssetTypes { LINK_BANNER_IMAGE = "linkBannerImage", LINK_SCREENSHOT = "linkScreenshot", + LINK_PDF = "linkPdf", ASSET_SCREENSHOT = "assetScreenshot", LINK_FULL_PAGE_ARCHIVE = "linkFullPageArchive", LINK_PRECRAWLED_ARCHIVE = "linkPrecrawledArchive", @@ -240,6 +281,7 @@ export const enum AssetTypes { LINK_HTML_CONTENT = "linkHtmlContent", BOOKMARK_ASSET = "bookmarkAsset", USER_UPLOADED = "userUploaded", + AVATAR = "avatar", BACKUP = "backup", UNKNOWN = "unknown", } @@ -253,6 +295,7 @@ export const assets = sqliteTable( enum: [ AssetTypes.LINK_BANNER_IMAGE, AssetTypes.LINK_SCREENSHOT, + AssetTypes.LINK_PDF, AssetTypes.ASSET_SCREENSHOT, AssetTypes.LINK_FULL_PAGE_ARCHIVE, AssetTypes.LINK_PRECRAWLED_ARCHIVE, @@ -260,6 +303,7 @@ export const assets = sqliteTable( AssetTypes.LINK_HTML_CONTENT, AssetTypes.BOOKMARK_ASSET, AssetTypes.USER_UPLOADED, + AssetTypes.AVATAR, AssetTypes.BACKUP, AssetTypes.UNKNOWN, ], @@ -346,6 +390,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() @@ -356,6 +408,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), ], ); @@ -378,6 +431,8 @@ export const tagsOnBookmarks = sqliteTable( primaryKey({ columns: [tb.bookmarkId, tb.tagId] }), index("tagsOnBookmarks_tagId_idx").on(tb.tagId), index("tagsOnBookmarks_bookmarkId_idx").on(tb.bookmarkId), + // Composite index for tag-first queries (when filtering by tagId) + index("tagsOnBookmarks_tagId_bookmarkId_idx").on(tb.tagId, tb.bookmarkId), ], ); @@ -437,6 +492,11 @@ export const bookmarksInLists = sqliteTable( primaryKey({ columns: [tb.bookmarkId, tb.listId] }), index("bookmarksInLists_bookmarkId_idx").on(tb.bookmarkId), index("bookmarksInLists_listId_idx").on(tb.listId), + // Composite index for list-first queries (when filtering by listId) + index("bookmarksInLists_listId_bookmarkId_idx").on( + tb.listId, + tb.bookmarkId, + ), ], ); @@ -584,6 +644,11 @@ export const rssFeedImportsTable = sqliteTable( index("rssFeedImports_feedIdIdx_idx").on(bl.rssFeedId), index("rssFeedImports_entryIdIdx_idx").on(bl.entryId), unique().on(bl.rssFeedId, bl.entryId), + // Composite index for RSS feed filter queries (when filtering by rssFeedId) + index("rssFeedImports_rssFeedId_bookmarkId_idx").on( + bl.rssFeedId, + bl.bookmarkId, + ), ], ); @@ -769,10 +834,19 @@ export const importSessions = sqliteTable( rootListId: text("rootListId").references(() => bookmarkLists.id, { onDelete: "set null", }), + status: text("status", { + enum: ["staging", "pending", "running", "paused", "completed", "failed"], + }) + .notNull() + .default("staging"), + lastProcessedAt: integer("lastProcessedAt", { mode: "timestamp" }), createdAt: createdAtField(), modifiedAt: modifiedAtField(), }, - (is) => [index("importSessions_userId_idx").on(is.userId)], + (is) => [ + index("importSessions_userId_idx").on(is.userId), + index("importSessions_status_idx").on(is.status), + ], ); export const importSessionBookmarks = sqliteTable( @@ -797,6 +871,63 @@ export const importSessionBookmarks = sqliteTable( ], ); +export const importStagingBookmarks = sqliteTable( + "importStagingBookmarks", + { + id: text("id") + .notNull() + .primaryKey() + .$defaultFn(() => createId()), + importSessionId: text("importSessionId") + .notNull() + .references(() => importSessions.id, { onDelete: "cascade" }), + + // Bookmark data to create + type: text("type", { enum: ["link", "text", "asset"] }).notNull(), + url: text("url"), + title: text("title"), + content: text("content"), + note: text("note"), + tags: text("tags", { mode: "json" }).$type<string[]>(), + listIds: text("listIds", { mode: "json" }).$type<string[]>(), + sourceAddedAt: integer("sourceAddedAt", { mode: "timestamp" }), + + // Processing state + status: text("status", { + enum: ["pending", "processing", "completed", "failed"], + }) + .notNull() + .default("pending"), + processingStartedAt: integer("processingStartedAt", { + mode: "timestamp", + }), + + // Result (for observability) + result: text("result", { + enum: ["accepted", "rejected", "skipped_duplicate"], + }), + resultReason: text("resultReason"), + resultBookmarkId: text("resultBookmarkId").references(() => bookmarks.id, { + onDelete: "set null", + }), + + createdAt: createdAtField(), + completedAt: integer("completedAt", { mode: "timestamp" }), + }, + (isb) => [ + index("importStaging_session_status_idx").on( + isb.importSessionId, + isb.status, + ), + index("importStaging_completedAt_idx").on(isb.completedAt), + index("importStaging_status_idx").on(isb.status), + index("importStaging_status_processingStartedAt_idx").on( + isb.status, + isb.processingStartedAt, + ), + ], +); + // Relations export const userRelations = relations(users, ({ many, one }) => ({ |