TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import 'server-only';2import { and, asc, desc, eq, inArray, isNull, sql, count } from '@/lib/db';3import { db, assets, assetStats, assetVariants, variantStats, collections, collectionItems, collectionSnapshots, watchlists, watchlistItems, alerts, notifications, savedSearches, priceTargets, listings, indexValues, indices, categories, users } from '@/lib/db';4import { summarizePortfolio, type PortfolioItemInput, type PortfolioSummary } from './portfolio';56// ---------------------------------------------------------------- assets (minimal local search)78export interface AssetHit {9 id: string;10 slug: string;11 title: string;12 categorySlug: string;13 familySlug: string;14 year: number | null;15 heroImageUrl: string | null;16 rivUsd: number | null;17 rivConfidence: number | null;18 salesCount: number;19}2021/** Trigram + prefix search over asset titles for the "add item" flow (packages/search may replace it). */22export async function searchAssets(q: string, limit = 12, categorySlug?: string): Promise<AssetHit[]> {23 const term = q.trim();24 if (term.length < 2) return [];25 const rows = (await db().execute(sql`26 select a.id, a.slug, a.title, a.category_slug, a.family_slug, a.year, a.hero_image_url,27 s.riv_usd, s.riv_confidence, coalesce(s.sales_count, 0) as sales_count,28 greatest(similarity(a.title, ${term}), case when a.title ilike ${'%' + term + '%'} then 0.6 else 0 end) as score29 from assets a30 left join asset_stats s on s.asset_id = a.id31 where (a.title % ${term} or a.title ilike ${'%' + term + '%'} or a.search @@ plainto_tsquery('simple', ${term}))32 ${categorySlug ? sql`and a.category_slug = ${categorySlug}` : sql``}33 order by score desc, coalesce(s.sales_count, 0) desc34 limit ${limit}35 `)) as unknown as Array<Record<string, unknown>>;36 return rows.map((r) => ({37 id: String(r.id),38 slug: String(r.slug),39 title: String(r.title),40 categorySlug: String(r.category_slug),41 familySlug: String(r.family_slug),42 year: r.year === null ? null : Number(r.year),43 heroImageUrl: (r.hero_image_url as string | null) ?? null,44 rivUsd: r.riv_usd === null ? null : Number(r.riv_usd),45 rivConfidence: r.riv_confidence === null ? null : Number(r.riv_confidence),46 salesCount: Number(r.sales_count ?? 0),47 }));48}4950export async function getAssetBrief(assetId: string) {51 const rows = await db().select({ asset: assets, stats: assetStats }).from(assets).leftJoin(assetStats, eq(assetStats.assetId, assets.id)).where(eq(assets.id, assetId)).limit(1);52 return rows[0] ?? null;53}5455export async function listVariants(assetId: string) {56 return db().select({ v: assetVariants, s: variantStats }).from(assetVariants).leftJoin(variantStats, eq(variantStats.variantId, assetVariants.id)).where(eq(assetVariants.assetId, assetId)).orderBy(asc(assetVariants.label));57}5859// ---------------------------------------------------------------- collections6061export async function listCollections(userId: string) {62 const cols = await db().select().from(collections).where(eq(collections.userId, userId)).orderBy(asc(collections.createdAt));63 if (!cols.length) return [] as Array<{ collection: typeof collections.$inferSelect; summary: PortfolioSummary }>;64 const items = await loadItems(cols.map((c) => c.id));65 return cols.map((c) => ({ collection: c, summary: summarizePortfolio(items.filter((i) => i.collectionId === c.id)) }));66}6768export type LoadedItem = PortfolioItemInput & { collectionId: string; variantId: string | null; variantLabel: string | null; acquiredCurrency: string | null; purchasePriceNative: number | null; source: string | null; certificationNumber: string | null; serial: string | null; notes: string | null; tags: string[]; photos: string[]; assetSlug: string; heroImageUrl: string | null; condition: string | null; createdAt: Date; soldAt: string | null };6970export async function loadItems(collectionIds: string[]): Promise<LoadedItem[]> {71 if (!collectionIds.length) return [];72 const rows = await db()73 .select({ item: collectionItems, asset: assets, stats: assetStats, variant: assetVariants, vstats: variantStats })74 .from(collectionItems)75 .innerJoin(assets, eq(assets.id, collectionItems.assetId))76 .leftJoin(assetStats, eq(assetStats.assetId, assets.id))77 .leftJoin(assetVariants, eq(assetVariants.id, collectionItems.variantId))78 .leftJoin(variantStats, eq(variantStats.variantId, collectionItems.variantId))79 .where(inArray(collectionItems.collectionId, collectionIds))80 .orderBy(desc(collectionItems.createdAt));81 return rows.map(({ item, asset, stats, variant, vstats }) => ({82 id: item.id,83 collectionId: item.collectionId,84 assetId: asset.id,85 assetSlug: asset.slug,86 heroImageUrl: asset.heroImageUrl,87 title: asset.title,88 categorySlug: asset.categorySlug,89 familySlug: asset.familySlug,90 quantity: item.quantity,91 purchasePriceUsd: item.purchasePriceUsd,92 purchasePriceNative: item.purchasePrice,93 acquiredCurrency: item.purchaseCurrency,94 acquiredAt: item.acquiredAt ? String(item.acquiredAt) : null,95 grader: item.grader ?? variant?.grader ?? null,96 grade: item.grade ?? variant?.grade ?? null,97 variantId: item.variantId,98 variantLabel: variant?.label ?? null,99 variantRivUsd: vstats?.rivUsd ?? null,100 variantConfidence: vstats?.rivConfidence ?? null,101 assetRivUsd: stats?.rivUsd ?? null,102 assetConfidence: stats?.rivConfidence ?? null,103 manualValueUsd: item.manualValueUsd,104 liquidityScore: stats?.liquidityScore ?? null,105 rarityScore: stats?.rarityScore ?? null,106 change30d: stats?.change30d ?? null,107 source: item.source,108 certificationNumber: item.certificationNumber,109 serial: item.serial,110 notes: item.notes,111 tags: item.tags ?? [],112 photos: item.photos ?? [],113 condition: item.condition,114 createdAt: item.createdAt,115 soldAt: item.soldAt ? String(item.soldAt) : null,116 soldPriceUsd: item.soldPriceUsd,117 }));118}119120export async function getCollection(userId: string, id: string) {121 const rows = await db().select().from(collections).where(and(eq(collections.id, id), eq(collections.userId, userId))).limit(1);122 return rows[0] ?? null;123}124125export async function getCollectionDetail(userId: string, id: string) {126 const col = await getCollection(userId, id);127 if (!col) return null;128 const items = await loadItems([id]);129 const summary = summarizePortfolio(items);130 const history = await db().select().from(collectionSnapshots).where(eq(collectionSnapshots.collectionId, id)).orderBy(asc(collectionSnapshots.date));131 return { collection: col, items, summary, history };132}133134export async function portfolioHistory(userId: string): Promise<Array<{ date: string; valueUsd: number; costBasisUsd: number }>> {135 const rows = (await db().execute(sql`136 select s.date::text as date, sum(s.value_usd)::float as value_usd, sum(s.cost_basis_usd)::float as cost_basis_usd137 from collection_snapshots s join collections c on c.id = s.collection_id138 where c.user_id = ${userId}139 group by s.date order by s.date140 `)) as unknown as Array<{ date: string; value_usd: number; cost_basis_usd: number }>;141 return rows.map((r) => ({ date: r.date, valueUsd: Number(r.value_usd), costBasisUsd: Number(r.cost_basis_usd) }));142}143144export async function indexSeries(ticker: string, since?: string) {145 const idx = await db().select({ id: indices.id }).from(indices).where(eq(indices.ticker, ticker)).limit(1);146 if (!idx[0]) return [];147 const rows = await db().select({ date: indexValues.date, value: indexValues.value }).from(indexValues).where(since ? and(eq(indexValues.indexId, idx[0].id), sql`${indexValues.date} >= ${since}`) : eq(indexValues.indexId, idx[0].id)).orderBy(asc(indexValues.date));148 return rows.map((r) => ({ date: String(r.date), value: r.value }));149}150151// ---------------------------------------------------------------- watchlist152153export async function getOrCreateWatchlist(userId: string) {154 const rows = await db().select().from(watchlists).where(eq(watchlists.userId, userId)).orderBy(asc(watchlists.createdAt)).limit(1);155 if (rows[0]) return rows[0];156 const { newId } = await import('@rareindex/shared');157 const id = newId('watchlist');158 await db().insert(watchlists).values({ id, userId, name: 'Watchlist' });159 return (await db().select().from(watchlists).where(eq(watchlists.id, id)))[0]!;160}161162export interface WatchRow {163 item: typeof watchlistItems.$inferSelect;164 asset: typeof assets.$inferSelect | null;165 stats: typeof assetStats.$inferSelect | null;166 category: typeof categories.$inferSelect | null;167}168169export async function loadWatchlist(userId: string): Promise<WatchRow[]> {170 const wl = await getOrCreateWatchlist(userId);171 const items = await db().select().from(watchlistItems).where(eq(watchlistItems.watchlistId, wl.id)).orderBy(desc(watchlistItems.createdAt));172 const assetIds = items.filter((i) => i.targetType === 'asset').map((i) => i.targetId);173 const catIds = items.filter((i) => i.targetType === 'category').map((i) => i.targetId);174 const [assetRows, catRows] = await Promise.all([175 assetIds.length ? db().select({ asset: assets, stats: assetStats }).from(assets).leftJoin(assetStats, eq(assetStats.assetId, assets.id)).where(inArray(assets.id, assetIds)) : Promise.resolve([]),176 catIds.length ? db().select().from(categories).where(inArray(categories.slug, catIds)) : Promise.resolve([]),177 ]);178 const aMap = new Map(assetRows.map((r) => [r.asset.id, r]));179 const cMap = new Map(catRows.map((c) => [c.slug, c]));180 return items.map((item) => ({ item, asset: aMap.get(item.targetId)?.asset ?? null, stats: aMap.get(item.targetId)?.stats ?? null, category: cMap.get(item.targetId) ?? null }));181}182183export async function isWatched(userId: string, targetType: string, targetId: string): Promise<boolean> {184 const wl = await db().select({ id: watchlists.id }).from(watchlists).where(eq(watchlists.userId, userId));185 if (!wl.length) return false;186 const rows = await db().select({ id: watchlistItems.id }).from(watchlistItems).where(and(inArray(watchlistItems.watchlistId, wl.map((w) => w.id)), eq(watchlistItems.targetType, targetType), eq(watchlistItems.targetId, targetId))).limit(1);187 return Boolean(rows[0]);188}189190// ---------------------------------------------------------------- alerts / notifications / saved / targets191192export async function listAlerts(userId: string) {193 const rows = await db().select().from(alerts).where(eq(alerts.userId, userId)).orderBy(desc(alerts.createdAt));194 const assetIds = rows.filter((a) => a.targetType === 'asset').map((a) => a.targetId);195 const aRows = assetIds.length ? await db().select({ id: assets.id, title: assets.title, slug: assets.slug }).from(assets).where(inArray(assets.id, assetIds)) : [];196 const aMap = new Map(aRows.map((a) => [a.id, a]));197 return rows.map((a) => ({ alert: a, asset: aMap.get(a.targetId) ?? null }));198}199200export async function listNotifications(userId: string, limit = 50) {201 return db().select().from(notifications).where(eq(notifications.userId, userId)).orderBy(desc(notifications.createdAt)).limit(limit);202}203204export async function unreadCount(userId: string): Promise<number> {205 const rows = await db().select({ n: count() }).from(notifications).where(and(eq(notifications.userId, userId), isNull(notifications.readAt)));206 return Number(rows[0]?.n ?? 0);207}208209export async function listSavedSearches(userId: string) {210 return db().select().from(savedSearches).where(eq(savedSearches.userId, userId)).orderBy(desc(savedSearches.createdAt));211}212213export async function listTargets(userId: string) {214 const rows = await db().select({ target: priceTargets, asset: assets, stats: assetStats }).from(priceTargets).innerJoin(assets, eq(assets.id, priceTargets.assetId)).leftJoin(assetStats, eq(assetStats.assetId, assets.id)).where(eq(priceTargets.userId, userId)).orderBy(desc(priceTargets.createdAt));215 return rows;216}217218/** Deal Radar: available listings priced materially below RIV inside the member's universe (§123). */219export async function dealRadar(userId: string, opts: { minDiscount?: number; limit?: number } = {}) {220 const minDiscount = opts.minDiscount ?? 0.15;221 const limit = opts.limit ?? 50;222 const rows = (await db().execute(sql`223 with universe as (224 select distinct a.category_slug from collection_items ci join collections c on c.id = ci.collection_id join assets a on a.id = ci.asset_id where c.user_id = ${userId}225 union226 select distinct a.category_slug from watchlist_items wi join watchlists w on w.id = wi.watchlist_id join assets a on a.id = wi.target_id where w.user_id = ${userId} and wi.target_type = 'asset'227 union228 select wi.target_id from watchlist_items wi join watchlists w on w.id = wi.watchlist_id where w.user_id = ${userId} and wi.target_type = 'category'229 ), watched as (230 select wi.target_id as asset_id from watchlist_items wi join watchlists w on w.id = wi.watchlist_id where w.user_id = ${userId} and wi.target_type = 'asset'231 )232 select l.id, l.asset_id, a.slug, a.title, a.category_slug, a.hero_image_url, l.source_id, l.source_url, l.price_usd, l.currency, l.price, l.grader, l.grade, l.condition,233 l.discount_to_riv, s.riv_usd, s.riv_confidence, s.riv_sample_size, l.last_seen_at, l.ends_at,234 (a.id in (select asset_id from watched)) as watched235 from listings l236 join assets a on a.id = l.asset_id237 join asset_stats s on s.asset_id = a.id238 where l.availability = 'available'239 and l.discount_to_riv is not null and l.discount_to_riv <= ${-minDiscount} and l.discount_to_riv >= -0.5 and not ('riv_review' = any(l.flags))240 and s.riv_confidence >= 0.5 and s.riv_sample_size >= 5241 and (a.category_slug in (select category_slug from universe) or a.id in (select asset_id from watched))242 order by watched desc, l.discount_to_riv asc243 limit ${limit}244 `)) as unknown as Array<Record<string, unknown>>;245 return rows;246}247248export async function publicProfile(handle: string) {249 const rows = await db().select().from(users).where(and(eq(users.handle, handle.toLowerCase()), isNull(users.deletedAt))).limit(1);250 const u = rows[0];251 if (!u) return null;252 const cols = await db().select().from(collections).where(and(eq(collections.userId, u.id), eq(collections.isPublic, true))).orderBy(asc(collections.createdAt));253 const items = await loadItems(cols.map((c) => c.id));254 return { user: u, collections: cols.map((c) => ({ collection: c, summary: summarizePortfolio(items.filter((i) => i.collectionId === c.id)) })), items };255}256257export async function activeListingsForAssets(assetIds: string[]) {258 if (!assetIds.length) return [];259 return db().select({ assetId: listings.assetId, n: count(), minAsk: sql<number | null>`min(${listings.priceUsd})` }).from(listings).where(and(inArray(listings.assetId, assetIds), eq(listings.availability, 'available'))).groupBy(listings.assetId);260}261