From a0b4a26ad398137e13c35f3fe0dad99154537d91 Mon Sep 17 00:00:00 2001 From: Mohamed Bassem Date: Tue, 30 Dec 2025 12:52:50 +0200 Subject: feat: 2025 wrapped (#2322) * feat: 2025 wrapped * don't add wrapped for new users --- packages/shared/types/users.ts | 67 +++++++++ packages/trpc/models/users.ts | 305 ++++++++++++++++++++++++++++++++++++++++- packages/trpc/routers/users.ts | 11 ++ 3 files changed, 382 insertions(+), 1 deletion(-) (limited to 'packages') diff --git a/packages/shared/types/users.ts b/packages/shared/types/users.ts index 3ba56583..35db0e98 100644 --- a/packages/shared/types/users.ts +++ b/packages/shared/types/users.ts @@ -114,6 +114,73 @@ export const zUserStatsResponseSchema = z.object({ ), }); +export const zWrappedStatsResponseSchema = z.object({ + year: z.number(), + totalBookmarks: z.number(), + totalFavorites: z.number(), + totalArchived: z.number(), + totalHighlights: z.number(), + totalTags: z.number(), + totalLists: z.number(), + + firstBookmark: z + .object({ + id: z.string(), + title: z.string().nullable(), + createdAt: z.date(), + type: z.enum(["link", "text", "asset"]), + }) + .nullable(), + + mostActiveDay: z + .object({ + date: z.string(), + count: z.number(), + }) + .nullable(), + + topDomains: z + .array( + z.object({ + domain: z.string(), + count: z.number(), + }), + ) + .max(5), + + topTags: z + .array( + z.object({ + name: z.string(), + count: z.number(), + }), + ) + .max(5), + + bookmarksByType: z.object({ + link: z.number(), + text: z.number(), + asset: z.number(), + }), + + bookmarksBySource: z.array( + z.object({ + source: zBookmarkSourceSchema.nullable(), + count: z.number(), + }), + ), + + monthlyActivity: z.array( + z.object({ + month: z.number(), + count: z.number(), + }), + ), + + peakHour: z.number(), + peakDayOfWeek: z.number(), +}); + export const zReaderFontFamilySchema = z.enum(["serif", "sans", "mono"]); export type ZReaderFontFamily = z.infer; diff --git a/packages/trpc/models/users.ts b/packages/trpc/models/users.ts index d8a84ffa..b1719200 100644 --- a/packages/trpc/models/users.ts +++ b/packages/trpc/models/users.ts @@ -1,6 +1,6 @@ import { randomBytes } from "crypto"; import { TRPCError } from "@trpc/server"; -import { and, count, desc, eq, gte, sql } from "drizzle-orm"; +import { and, count, desc, eq, gte, lte, sql } from "drizzle-orm"; import invariant from "tiny-invariant"; import { z } from "zod"; @@ -27,6 +27,7 @@ import { zUserSettingsSchema, zUserStatsResponseSchema, zWhoAmIResponseSchema, + zWrappedStatsResponseSchema, } from "@karakeep/shared/types/users"; import { AuthedContext, Context } from ".."; @@ -870,6 +871,308 @@ export class User { }; } + async hasWrapped(): Promise { + const [{ numBookmarks }] = await this.ctx.db + .select({ + numBookmarks: count(bookmarks.id), + }) + .from(bookmarks) + .where(eq(bookmarks.userId, this.user.id)); + + return numBookmarks >= 20; + } + + async getWrappedStats( + year: number, + ): Promise> { + const userObj = await this.ctx.db.query.users.findFirst({ + where: eq(users.id, this.user.id), + columns: { + timezone: true, + }, + }); + const userTimezone = userObj?.timezone || "UTC"; + + // Define year range for 2025 + const yearStart = new Date(`${year}-01-01T00:00:00Z`); + const yearEnd = new Date(`${year}-12-31T23:59:59Z`); + + const yearFilter = and( + eq(bookmarks.userId, this.user.id), + gte(bookmarks.createdAt, yearStart), + lte(bookmarks.createdAt, yearEnd), + ); + + const [ + [{ totalBookmarks }], + [{ totalFavorites }], + [{ totalArchived }], + [{ numTags }], + [{ numLists }], + [{ numHighlights }], + firstBookmarkResult, + bookmarksByType, + topDomains, + topTags, + bookmarksBySource, + bookmarkTimestamps, + ] = await Promise.all([ + // Total bookmarks in year + this.ctx.db + .select({ totalBookmarks: count() }) + .from(bookmarks) + .where(yearFilter), + + // Total favorites in year + this.ctx.db + .select({ totalFavorites: count() }) + .from(bookmarks) + .where(and(yearFilter, eq(bookmarks.favourited, true))), + + // Total archived in year + this.ctx.db + .select({ totalArchived: count() }) + .from(bookmarks) + .where(and(yearFilter, eq(bookmarks.archived, true))), + + // Total unique tags (created in year) + this.ctx.db + .select({ numTags: count() }) + .from(bookmarkTags) + .where( + and( + eq(bookmarkTags.userId, this.user.id), + gte(bookmarkTags.createdAt, yearStart), + lte(bookmarkTags.createdAt, yearEnd), + ), + ), + + // Total lists (created in year) + this.ctx.db + .select({ numLists: count() }) + .from(bookmarkLists) + .where( + and( + eq(bookmarkLists.userId, this.user.id), + gte(bookmarkLists.createdAt, yearStart), + lte(bookmarkLists.createdAt, yearEnd), + ), + ), + + // Total highlights (created in year) + this.ctx.db + .select({ numHighlights: count() }) + .from(highlights) + .where( + and( + eq(highlights.userId, this.user.id), + gte(highlights.createdAt, yearStart), + lte(highlights.createdAt, yearEnd), + ), + ), + + // First bookmark of the year + this.ctx.db + .select({ + id: bookmarks.id, + title: bookmarks.title, + createdAt: bookmarks.createdAt, + type: bookmarks.type, + }) + .from(bookmarks) + .where(yearFilter) + .orderBy(bookmarks.createdAt) + .limit(1), + + // Bookmarks by type + this.ctx.db + .select({ + type: bookmarks.type, + count: count(), + }) + .from(bookmarks) + .where(yearFilter) + .groupBy(bookmarks.type), + + // Top 5 domains + this.ctx.db + .select({ + domain: sql`CASE + WHEN ${bookmarkLinks.url} LIKE 'https://%' THEN + CASE + WHEN INSTR(SUBSTR(${bookmarkLinks.url}, 9), '/') > 0 THEN + SUBSTR(${bookmarkLinks.url}, 9, INSTR(SUBSTR(${bookmarkLinks.url}, 9), '/') - 1) + ELSE + SUBSTR(${bookmarkLinks.url}, 9) + END + WHEN ${bookmarkLinks.url} LIKE 'http://%' THEN + CASE + WHEN INSTR(SUBSTR(${bookmarkLinks.url}, 8), '/') > 0 THEN + SUBSTR(${bookmarkLinks.url}, 8, INSTR(SUBSTR(${bookmarkLinks.url}, 8), '/') - 1) + ELSE + SUBSTR(${bookmarkLinks.url}, 8) + END + ELSE + CASE + WHEN INSTR(${bookmarkLinks.url}, '/') > 0 THEN + SUBSTR(${bookmarkLinks.url}, 1, INSTR(${bookmarkLinks.url}, '/') - 1) + ELSE + ${bookmarkLinks.url} + END + END`, + count: count(), + }) + .from(bookmarkLinks) + .innerJoin(bookmarks, eq(bookmarks.id, bookmarkLinks.id)) + .where(yearFilter) + .groupBy( + sql`CASE + WHEN ${bookmarkLinks.url} LIKE 'https://%' THEN + CASE + WHEN INSTR(SUBSTR(${bookmarkLinks.url}, 9), '/') > 0 THEN + SUBSTR(${bookmarkLinks.url}, 9, INSTR(SUBSTR(${bookmarkLinks.url}, 9), '/') - 1) + ELSE + SUBSTR(${bookmarkLinks.url}, 9) + END + WHEN ${bookmarkLinks.url} LIKE 'http://%' THEN + CASE + WHEN INSTR(SUBSTR(${bookmarkLinks.url}, 8), '/') > 0 THEN + SUBSTR(${bookmarkLinks.url}, 8, INSTR(SUBSTR(${bookmarkLinks.url}, 8), '/') - 1) + ELSE + SUBSTR(${bookmarkLinks.url}, 8) + END + ELSE + CASE + WHEN INSTR(${bookmarkLinks.url}, '/') > 0 THEN + SUBSTR(${bookmarkLinks.url}, 1, INSTR(${bookmarkLinks.url}, '/') - 1) + ELSE + ${bookmarkLinks.url} + END + END`, + ) + .orderBy(desc(count())) + .limit(5), + + // Top 5 tags (used in bookmarks created this year) + this.ctx.db + .select({ + name: bookmarkTags.name, + count: count(), + }) + .from(bookmarkTags) + .innerJoin(tagsOnBookmarks, eq(tagsOnBookmarks.tagId, bookmarkTags.id)) + .innerJoin(bookmarks, eq(bookmarks.id, tagsOnBookmarks.bookmarkId)) + .where(yearFilter) + .groupBy(bookmarkTags.name) + .orderBy(desc(count())) + .limit(5), + + // Bookmarks by source + this.ctx.db + .select({ + source: bookmarks.source, + count: count(), + }) + .from(bookmarks) + .where(yearFilter) + .groupBy(bookmarks.source) + .orderBy(desc(count())), + + // All bookmark timestamps in the year for activity calculations + this.ctx.db + .select({ + createdAt: bookmarks.createdAt, + }) + .from(bookmarks) + .where(yearFilter), + ]); + + // Process bookmarks by type + const bookmarkTypeMap = { link: 0, text: 0, asset: 0 }; + bookmarksByType.forEach((item) => { + if (item.type in bookmarkTypeMap) { + bookmarkTypeMap[item.type as keyof typeof bookmarkTypeMap] = item.count; + } + }); + + // Process timestamps with user timezone for hourly/daily activity + const hourCounts = Array.from({ length: 24 }, () => 0); + const dayCounts = Array.from({ length: 7 }, () => 0); + const monthCounts = Array.from({ length: 12 }, () => 0); + const dayCounts_full: Record = {}; + + bookmarkTimestamps.forEach(({ createdAt }) => { + if (createdAt) { + const date = new Date(createdAt); + const userDate = new Date( + date.toLocaleString("en-US", { timeZone: userTimezone }), + ); + + const hour = userDate.getHours(); + const day = userDate.getDay(); + const month = userDate.getMonth(); + const dateKey = userDate.toISOString().split("T")[0]; + + hourCounts[hour]++; + dayCounts[day]++; + monthCounts[month]++; + dayCounts_full[dateKey] = (dayCounts_full[dateKey] || 0) + 1; + } + }); + + // Find peak hour and day + const peakHour = hourCounts.indexOf(Math.max(...hourCounts)); + const peakDayOfWeek = dayCounts.indexOf(Math.max(...dayCounts)); + + // Find most active day + let mostActiveDay: { date: string; count: number } | null = null; + if (Object.keys(dayCounts_full).length > 0) { + const sortedDays = Object.entries(dayCounts_full).sort( + ([, a], [, b]) => b - a, + ); + mostActiveDay = { + date: sortedDays[0][0], + count: sortedDays[0][1], + }; + } + + // Monthly activity + const monthlyActivity = Array.from({ length: 12 }, (_, i) => ({ + month: i + 1, + count: monthCounts[i], + })); + + // First bookmark + const firstBookmark = + firstBookmarkResult.length > 0 + ? { + id: firstBookmarkResult[0].id, + title: firstBookmarkResult[0].title, + createdAt: firstBookmarkResult[0].createdAt, + type: firstBookmarkResult[0].type, + } + : null; + + return { + year, + totalBookmarks: totalBookmarks || 0, + totalFavorites: totalFavorites || 0, + totalArchived: totalArchived || 0, + totalHighlights: numHighlights || 0, + totalTags: numTags || 0, + totalLists: numLists || 0, + firstBookmark, + mostActiveDay, + topDomains: topDomains.filter((d) => d.domain && d.domain.length > 0), + topTags, + bookmarksByType: bookmarkTypeMap, + bookmarksBySource, + monthlyActivity, + peakHour, + peakDayOfWeek, + }; + } + asWhoAmI(): z.infer { return { id: this.user.id, diff --git a/packages/trpc/routers/users.ts b/packages/trpc/routers/users.ts index dbfbbc3c..71c23a39 100644 --- a/packages/trpc/routers/users.ts +++ b/packages/trpc/routers/users.ts @@ -9,6 +9,7 @@ import { zUserSettingsSchema, zUserStatsResponseSchema, zWhoAmIResponseSchema, + zWrappedStatsResponseSchema, } from "@karakeep/shared/types/users"; import { @@ -136,6 +137,16 @@ export const usersAppRouter = router({ const user = await User.fromCtx(ctx); return await user.getStats(); }), + wrapped: authedProcedure + .output(zWrappedStatsResponseSchema) + .query(async ({ ctx }) => { + const user = await User.fromCtx(ctx); + return await user.getWrappedStats(2025); + }), + hasWrapped: authedProcedure.output(z.boolean()).query(async ({ ctx }) => { + const user = await User.fromCtx(ctx); + return await user.hasWrapped(); + }), settings: authedProcedure .output(zUserSettingsSchema) .query(async ({ ctx }) => { -- cgit v1.2.3-70-g09d2