TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import 'server-only';2import { cache } from 'react';3import { CATEGORIES, FAMILIES, getCategory, type CategoryNode } from '@rareindex/taxonomy';4import { rows, one, sql, num, int, str, date, textArray } from './_util';5import { categoryScope, getSetFromAssets } from './assets';67export interface CategorySnapshot {8 categorySlug: string;9 date: string;10 indexValue: number | null;11 trackedAssets: number;12 assetsWithValuation: number;13 sales: number;14 volumeUsd: number | null;15 medianSaleUsd: number | null;16 activeListings: number;17 marketCapEstUsd: number | null;18 liquidityScore: number | null;19 change1d: number | null;20 change7d: number | null;21 change30d: number | null;22 change1y: number | null;23}2425function toSnap(x: Record<string, unknown>): CategorySnapshot {26 return { categorySlug: String(x.category_slug), date: String(x.date), indexValue: num(x.index_value), trackedAssets: int(x.tracked_assets), assetsWithValuation: int(x.assets_with_valuation), sales: int(x.sales), volumeUsd: num(x.volume_usd), medianSaleUsd: num(x.median_sale_usd), activeListings: int(x.active_listings), marketCapEstUsd: num(x.market_cap_est_usd), liquidityScore: num(x.liquidity_score), change1d: num(x.change_1d), change7d: num(x.change_7d), change30d: num(x.change_30d), change1y: num(x.change_1y) };27}2829/** Latest snapshot per category (may be missing for categories without data). */30export const getLatestCategorySnapshots = cache(async (): Promise<Map<string, CategorySnapshot>> => {31 const r = await rows<Record<string, unknown>>(sql`SELECT DISTINCT ON (category_slug) * FROM category_snapshots ORDER BY category_slug, date DESC`);32 return new Map(r.map((x) => [String(x.category_slug), toSnap(x)]));33});3435/** Live counts straight from the canonical tables (works even before snapshots exist). */36export interface CategoryCounts {37 slug: string;38 assets: number;39 priced: number;40 sales: number;41 sales30d: number;42 volume30dUsd: number | null;43 listings: number;44 observations: number;45 medianRivUsd: number | null;46 lastSaleAt: Date | null;47}4849export const getCategoryCounts = cache(async (): Promise<Map<string, CategoryCounts>> => {50 // Counts come from the canonical tables directly (not asset_stats) so they are right even before the valuation worker runs.51 const r = await rows<Record<string, unknown>>(sql`52 WITH sa AS (53 SELECT a.category_slug AS slug, count(*) AS sales, count(*) FILTER (WHERE s.sale_date >= now() - interval '30 days') AS sales_30d,54 sum(s.price_usd) FILTER (WHERE s.sale_date >= now() - interval '30 days') AS volume_30d, max(s.sale_date) AS last_sale_at55 FROM sales s JOIN assets a ON a.id = s.asset_id WHERE s.status = 'valid' GROUP BY a.category_slug56 ), li AS (57 SELECT a.category_slug AS slug, count(*) AS listings FROM listings l JOIN assets a ON a.id = l.asset_id WHERE l.availability = 'available' GROUP BY a.category_slug58 ), ob AS (59 SELECT a.category_slug AS slug, count(*) AS observations FROM price_observations o JOIN assets a ON a.id = o.asset_id GROUP BY a.category_slug60 ), st AS (61 SELECT a.category_slug AS slug, count(*) AS assets, count(s.riv_usd) AS priced, percentile_cont(0.5) WITHIN GROUP (ORDER BY s.riv_usd) AS median_riv62 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id GROUP BY a.category_slug63 )64 SELECT st.slug, st.assets, st.priced, coalesce(sa.sales, 0) AS sales, coalesce(sa.sales_30d, 0) AS sales_30d, sa.volume_30d, coalesce(li.listings, 0) AS listings,65 coalesce(ob.observations, 0) AS observations, st.median_riv, sa.last_sale_at66 FROM st LEFT JOIN sa ON sa.slug = st.slug LEFT JOIN li ON li.slug = st.slug LEFT JOIN ob ON ob.slug = st.slug67 `);68 return new Map(r.map((x) => [String(x.slug), { slug: String(x.slug), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), sales30d: int(x.sales_30d), volume30dUsd: num(x.volume_30d), listings: int(x.listings), observations: int(x.observations), medianRivUsd: num(x.median_riv), lastSaleAt: date(x.last_sale_at) }]));69});7071export interface MarketRow {72 node: CategoryNode;73 snapshot: CategorySnapshot | null;74 counts: CategoryCounts; // aggregated over the node and its descendants75}7677function aggregate(slugs: string[], counts: Map<string, CategoryCounts>, slug: string): CategoryCounts {78 const out: CategoryCounts = { slug, assets: 0, priced: 0, sales: 0, sales30d: 0, volume30dUsd: null, listings: 0, observations: 0, medianRivUsd: null, lastSaleAt: null };79 for (const s of slugs) {80 const c = counts.get(s);81 if (!c) continue;82 out.assets += c.assets;83 out.priced += c.priced;84 out.sales += c.sales;85 out.sales30d += c.sales30d;86 out.listings += c.listings;87 out.observations += c.observations;88 if (c.volume30dUsd != null) out.volume30dUsd = (out.volume30dUsd ?? 0) + c.volume30dUsd;89 if (c.lastSaleAt && (!out.lastSaleAt || c.lastSaleAt > out.lastSaleAt)) out.lastSaleAt = c.lastSaleAt;90 if (out.medianRivUsd == null && c.medianRivUsd != null) out.medianRivUsd = c.medianRivUsd;91 }92 return out;93}9495/** Market overview rows for families (level 0) or children of a family. */96export const getMarketRows = cache(async (parent: string | null = null): Promise<MarketRow[]> => {97 const [snaps, counts] = await Promise.all([getLatestCategorySnapshots(), getCategoryCounts()]);98 const nodes = parent ? CATEGORIES.filter((c) => c.parent === parent) : FAMILIES;99 return nodes.map((node) => ({ node, snapshot: snaps.get(node.slug) ?? null, counts: aggregate(categoryScope(node.slug), counts, node.slug) }));100});101102export const getCategorySeries = cache(async (slug: string, days: number | null = null): Promise<CategorySnapshot[]> => {103 const r = await rows<Record<string, unknown>>(sql`SELECT * FROM category_snapshots WHERE category_slug = ${slug} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date`);104 return r.map(toSnap);105});106107/** Compact per-category index series (values only) for sparklines; categories without snapshots are absent. */108export const getCategorySparklines = cache(async (slugs: string[], days = 90): Promise<Map<string, number[]>> => {109 if (!slugs.length) return new Map();110 const r = await rows<{ category_slug: string; values: unknown }>(sql`111 SELECT category_slug, array_agg(index_value ORDER BY date) AS values112 FROM category_snapshots113 WHERE category_slug = ANY(${textArray(slugs)}) AND index_value IS NOT NULL AND date >= current_date - ${days}::int114 GROUP BY category_slug115 `);116 const out = new Map<string, number[]>();117 for (const x of r) {118 const vals = (Array.isArray(x.values) ? x.values : []).map((v) => Number(v)).filter((v) => Number.isFinite(v));119 if (vals.length >= 3) out.set(String(x.category_slug), vals);120 }121 return out;122});123124export const getCategoryRow = cache(async (slug: string): Promise<MarketRow | null> => {125 const node = getCategory(slug);126 if (!node) return null;127 const [snaps, counts] = await Promise.all([getLatestCategorySnapshots(), getCategoryCounts()]);128 return { node, snapshot: snaps.get(slug) ?? null, counts: aggregate(categoryScope(slug), counts, slug) };129});130131export const getSetsInCategory = cache(async (slug: string, limit = 60): Promise<Array<{ slug: string; name: string; code: string | null; releaseYear: number | null; assets: number; priced: number; sales: number; thumb: string | null }>> => {132 // Grouped from the assets table (LEFT JOIN sets for metadata) so releases appear as soon as catalog items land.133 const r = await rows<Record<string, unknown>>(sql`134 SELECT a.set_slug AS slug, coalesce(st.name, min(a.set_name)) AS name, coalesce(st.code, min(a.set_code)) AS code, coalesce(st.release_year, min(a.year)) AS release_year,135 count(*) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales,136 (array_agg(a.hero_image_url) FILTER (WHERE a.hero_image_url IS NOT NULL))[1] AS thumb137 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id LEFT JOIN sets st ON st.slug = a.set_slug138 WHERE a.category_slug IN ${categoryScope(slug)} AND a.set_slug IS NOT NULL139 GROUP BY a.set_slug, st.name, st.code, st.release_year ORDER BY sales DESC, assets DESC, release_year DESC NULLS LAST LIMIT ${limit}140 `);141 return r.map((x) => ({ slug: String(x.slug), name: String(x.name ?? x.slug), code: str(x.code), releaseYear: num(x.release_year), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), thumb: str(x.thumb) }));142});143144export const getBrandsInCategory = cache(async (slug: string, limit = 40): Promise<Array<{ brand: string; assets: number; priced: number; sales: number; medianRivUsd: number | null }>> => {145 const r = await rows<Record<string, unknown>>(sql`146 SELECT a.brand, count(*) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales, percentile_cont(0.5) WITHIN GROUP (ORDER BY s.riv_usd) AS median_riv147 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.brand IS NOT NULL AND a.category_slug IN ${categoryScope(slug)}148 GROUP BY a.brand ORDER BY sales DESC, assets DESC LIMIT ${limit}149 `);150 return r.map((x) => ({ brand: String(x.brand), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), medianRivUsd: num(x.median_riv) }));151});152153export const getSet = cache(async (slug: string) => {154 const x = await one<Record<string, unknown>>(sql`SELECT * FROM sets WHERE slug = ${slug}`);155 if (!x) return getSetFromAssets(slug);156 return { slug: String(x.slug), name: String(x.name), code: str(x.code), categorySlug: String(x.category_slug), franchiseSlug: str(x.franchise_slug), brandSlug: str(x.brand_slug), releaseYear: num(x.release_year), releaseDate: str(x.release_date), language: str(x.language), totalItems: num(x.total_items), identifiers: (x.identifiers as Record<string, string>) ?? {}, metadata: (x.metadata as Record<string, unknown>) ?? {} };157});158159export const getSetStats = cache(async (slug: string) => {160 const x = await one<Record<string, unknown>>(sql`161 SELECT count(a.id) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales, coalesce(sum(s.active_listings), 0) AS listings, sum(s.riv_usd) AS sum_riv, max(s.riv_usd) AS max_riv, percentile_cont(0.5) WITHIN GROUP (ORDER BY s.riv_usd) AS median_riv, max(s.latest_sale_at) AS last_sale_at162 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE a.set_slug = ${slug}163 `);164 return { assets: int(x?.assets), priced: int(x?.priced), sales: int(x?.sales), listings: int(x?.listings), sumRivUsd: num(x?.sum_riv), maxRivUsd: num(x?.max_riv), medianRivUsd: num(x?.median_riv), lastSaleAt: date(x?.last_sale_at) };165});166167export const getBrandStats = cache(async (brand: string) => {168 const x = await one<Record<string, unknown>>(sql`169 SELECT min(a.brand) AS brand, count(a.id) AS assets, count(s.riv_usd) AS priced, coalesce(sum(s.sales_count), 0) AS sales, coalesce(sum(s.active_listings), 0) AS listings, max(s.riv_usd) AS max_riv, percentile_cont(0.5) WITHIN GROUP (ORDER BY s.riv_usd) AS median_riv, array_agg(DISTINCT a.category_slug) AS categories170 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id WHERE lower(a.brand) = lower(${brand})171 `);172 if (!x || int(x.assets) === 0) return null;173 return { brand: String(x.brand), assets: int(x.assets), priced: int(x.priced), sales: int(x.sales), listings: int(x.listings), maxRivUsd: num(x.max_riv), medianRivUsd: num(x.median_riv), categories: (x.categories as string[]) ?? [] };174});175176export const getSetSlugsForSitemap = cache(async (): Promise<string[]> => {177 const r = await rows<Record<string, unknown>>(sql`SELECT slug FROM sets UNION SELECT DISTINCT set_slug FROM assets WHERE set_slug IS NOT NULL ORDER BY 1 LIMIT 20000`);178 return r.map((x) => String(x.slug));179});180181export const getPopulationTrend = cache(async (slug: string): Promise<Array<{ grader: string; reportDate: string; total: number; assets: number }>> => {182 const r = await rows<Record<string, unknown>>(sql`183 SELECT p.grader, p.report_date, sum(p.total) AS total, count(DISTINCT p.asset_id) AS assets FROM population_reports p JOIN assets a ON a.id = p.asset_id184 WHERE a.category_slug IN ${categoryScope(slug)} GROUP BY p.grader, p.report_date ORDER BY p.grader, p.report_date185 `);186 return r.map((x) => ({ grader: String(x.grader), reportDate: String(x.report_date), total: int(x.total), assets: int(x.assets) }));187});188