TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import 'server-only';2import { sql, type SQL } from 'drizzle-orm';3import { categoryScope } from '@/lib/queries/assets';4import { int, joinAnd, num, rows, str } from '@/lib/queries/_util';5import { SCREENER_EXPORT_MAX, type ScreenerFilters, type ScreenerSort } from '@/lib/screener';67/**8 * Market Screener rows (§158). Every derived metric is computed once in the inner query so filters,9 * sorts and the output agree exactly:10 * - drawdown = RIV / ATH − 1 (≤ 0, null without an ATH)11 * - spread = (lowest ask of the representative variant − RIV) / RIV, only when the valuation is12 * transaction-grade (≥ 5 sales, confidence ≥ 0.5) and the ratio is plausible (0.1×–10×)13 * - accel = sales_30d / max(sales_1y / 12, 1) (volume acceleration vs the trailing year)14 * Data-quality gates always on: a valuation exists and rests on ≥ 3 sales (§143, §196).15 */16export interface ScreenerRow {17 id: string;18 slug: string;19 title: string;20 categorySlug: string;21 familySlug: string;22 heroImageUrl: string | null;23 year: number | null;24 brand: string | null;25 setName: string | null;26 rivUsd: number;27 rivLowUsd: number | null;28 rivHighUsd: number | null;29 rivConfidence: number | null;30 rivSampleSize: number;31 change30d: number | null;32 change1y: number | null;33 liquidityScore: number | null;34 rarityScore: number | null;35 sales30d: number;36 sales1y: number;37 salesCount: number;38 activeListings: number;39 minAskUsd: number | null;40 spread: number | null;41 valueOpportunity: number | null;42 athUsd: number | null;43 drawdown: number | null;44 volume30dUsd: number | null;45 accel: number | null;46 updatedAt: Date | null;47}4849const SORT_COL: Record<ScreenerSort, SQL> = {50 riv: sql`riv_usd`,51 confidence: sql`riv_confidence`,52 change30d: sql`change_30d`,53 change1y: sql`change_1y`,54 liquidity: sql`liquidity_score`,55 rarity: sql`rarity_score`,56 sales30d: sql`sales_30d`,57 sales1y: sql`sales_1y`,58 listings: sql`active_listings`,59 spread: sql`spread`,60 drawdown: sql`drawdown`,61 volume30d: sql`volume_30d_usd`,62 opportunity: sql`value_opportunity`,63 name: sql`title`,64};6566const INNER = sql`67 SELECT a.id, a.slug, a.title, a.category_slug, a.family_slug, a.hero_image_url, a.year, a.brand, a.set_name, a.set_slug,68 s.riv_usd, s.riv_low_usd, s.riv_high_usd, s.riv_confidence, coalesce(s.riv_sample_size, 0) AS riv_sample_size,69 s.change_30d, s.change_1y, s.liquidity_score, s.rarity_score,70 coalesce(s.sales_30d, 0) AS sales_30d, coalesce(s.sales_1y, 0) AS sales_1y, coalesce(s.sales_count, 0) AS sales_count,71 coalesce(s.active_listings, 0) AS active_listings, s.min_ask_usd, s.value_opportunity, s.ath_usd, s.volume_30d_usd, s.updated_at,72 CASE WHEN s.ath_usd > 0 THEN s.riv_usd / s.ath_usd - 1 END AS drawdown,73 CASE WHEN s.min_ask_usd > 0 AND s.riv_usd > 0 AND coalesce(s.riv_sample_size, 0) >= 5 AND s.riv_confidence >= 0.574 AND s.min_ask_usd / s.riv_usd BETWEEN 0.1 AND 1075 THEN s.min_ask_usd / s.riv_usd - 1 END AS spread,76 coalesce(s.sales_30d, 0)::float / greatest(coalesce(s.sales_1y, 0) / 12.0, 1) AS accel77 FROM assets a JOIN asset_stats s ON s.asset_id = a.id78 WHERE s.riv_usd IS NOT NULL AND coalesce(s.riv_sample_size, 0) >= 3`;7980function whereFor(f: ScreenerFilters): SQL {81 const w: SQL[] = [];82 if (f.category) w.push(sql`category_slug IN ${categoryScope(f.category)}`);83 if (f.brand) w.push(sql`lower(brand) = lower(${f.brand})`);84 if (f.set) w.push(sql`set_slug = ${f.set}`);85 if (f.rivMin !== null) w.push(sql`riv_usd >= ${f.rivMin}`);86 if (f.rivMax !== null) w.push(sql`riv_usd <= ${f.rivMax}`);87 if (f.confidenceMin !== null) w.push(sql`riv_confidence >= ${f.confidenceMin}`);88 if (f.liquidityMin !== null) w.push(sql`liquidity_score >= ${f.liquidityMin}`);89 if (f.rarityMin !== null) w.push(sql`rarity_score >= ${f.rarityMin}`);90 // change filters only consider plausible moves (|Δ| ≤ 500 %); artefacts never match a screen91 if (f.change30dMin !== null) w.push(sql`change_30d >= ${f.change30dMin} AND abs(change_30d) <= 5`);92 if (f.change30dMax !== null) w.push(sql`change_30d <= ${f.change30dMax} AND abs(change_30d) <= 5`);93 if (f.change1yMin !== null) w.push(sql`change_1y >= ${f.change1yMin} AND abs(change_1y) <= 5`);94 if (f.change1yMax !== null) w.push(sql`change_1y <= ${f.change1yMax} AND abs(change_1y) <= 5`);95 if (f.sales30dMin !== null) w.push(sql`sales_30d >= ${f.sales30dMin}`);96 if (f.sales1yMin !== null) w.push(sql`sales_1y >= ${f.sales1yMin}`);97 if (f.listingsMin !== null) w.push(sql`active_listings >= ${f.listingsMin}`);98 if (f.drawdownMin !== null) w.push(sql`drawdown >= ${f.drawdownMin}`);99 if (f.drawdownMax !== null) w.push(sql`drawdown <= ${f.drawdownMax}`);100 if (f.spreadMin !== null) w.push(sql`spread >= ${f.spreadMin}`);101 if (f.spreadMax !== null) w.push(sql`spread <= ${f.spreadMax}`);102 if (f.volumeAccelMin !== null) w.push(sql`accel >= ${f.volumeAccelMin}`);103 if (f.yearFrom !== null) w.push(sql`year >= ${f.yearFrom}`);104 if (f.yearTo !== null) w.push(sql`year <= ${f.yearTo}`);105 if (f.grader) w.push(sql`EXISTS (SELECT 1 FROM asset_variants v WHERE v.asset_id = x.id AND v.grader = ${f.grader} ${f.grade ? sql`AND v.grade = ${f.grade}` : sql``})`);106 if (f.q && f.q.trim().length >= 2) {107 const q = f.q.trim();108 w.push(sql`(title ILIKE ${'%' + q + '%'} OR title % ${q} OR set_name ILIKE ${'%' + q + '%'})`);109 }110 // the "Potentially Underpriced" sort/preset must never surface an ungated or implausible discount111 if (f.sort === 'opportunity') w.push(sql`value_opportunity IS NOT NULL AND value_opportunity >= -0.5 AND value_opportunity <= -0.1 AND riv_sample_size >= 5 AND riv_confidence >= 0.5`);112 return joinAnd(w);113}114115function orderFor(f: ScreenerFilters): SQL {116 const col = SORT_COL[f.sort];117 const dir = f.dir === 'asc' ? sql`ASC` : sql`DESC`;118 return sql`${col} ${dir} NULLS LAST, riv_usd DESC NULLS LAST, id ASC`;119}120121function toRow(x: Record<string, unknown>): ScreenerRow {122 return {123 id: String(x.id),124 slug: String(x.slug),125 title: String(x.title),126 categorySlug: String(x.category_slug),127 familySlug: String(x.family_slug),128 heroImageUrl: str(x.hero_image_url),129 year: num(x.year),130 brand: str(x.brand),131 setName: str(x.set_name),132 rivUsd: num(x.riv_usd) ?? 0,133 rivLowUsd: num(x.riv_low_usd),134 rivHighUsd: num(x.riv_high_usd),135 rivConfidence: num(x.riv_confidence),136 rivSampleSize: int(x.riv_sample_size),137 change30d: num(x.change_30d),138 change1y: num(x.change_1y),139 liquidityScore: num(x.liquidity_score),140 rarityScore: num(x.rarity_score),141 sales30d: int(x.sales_30d),142 sales1y: int(x.sales_1y),143 salesCount: int(x.sales_count),144 activeListings: int(x.active_listings),145 minAskUsd: num(x.min_ask_usd),146 spread: num(x.spread),147 valueOpportunity: num(x.value_opportunity),148 athUsd: num(x.ath_usd),149 drawdown: num(x.drawdown),150 volume30dUsd: num(x.volume_30d_usd),151 accel: num(x.accel),152 updatedAt: x.updated_at ? new Date(String(x.updated_at)) : null,153 };154}155156export async function screenAssets(f: ScreenerFilters): Promise<{ items: ScreenerRow[]; total: number; page: number; pageSize: number }> {157 const pageSize = Math.min(Math.max(f.pageSize, 1), 100);158 const page = Math.max(1, f.page);159 const r = await rows<Record<string, unknown>>(sql`160 SELECT x.*, count(*) OVER() AS total FROM (${INNER}) x161 WHERE ${whereFor(f)} ORDER BY ${orderFor(f)} LIMIT ${pageSize} OFFSET ${(page - 1) * pageSize}162 `);163 return { items: r.map(toRow), total: r.length ? int(r[0]!.total) : 0, page, pageSize };164}165166/** Export variant: no window count, hard cap (§160). */167export async function screenAssetsExport(f: ScreenerFilters, limit = SCREENER_EXPORT_MAX): Promise<ScreenerRow[]> {168 const r = await rows<Record<string, unknown>>(sql`169 SELECT x.* FROM (${INNER}) x WHERE ${whereFor(f)} ORDER BY ${orderFor(f)} LIMIT ${Math.min(limit, SCREENER_EXPORT_MAX)}170 `);171 return r.map(toRow);172}173