TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import 'server-only';2import { cache } from 'react';3import { fairPrices, liquidationModel, marketDepth, type FairPrices, type LiquidationModel, type ListingLifecycle, type MarketDepth } from '@rareindex/valuation';4import { rows, one, sql, num, int } from './_util';56/**7 * Market depth (§25–§26): current asks of the representative variant against its RIV. Falls back to8 * all variants only when the asset has a single variant (then the comparison is unambiguous).9 */10export interface AssetDepth {11 depth: MarketDepth;12 riv: number;13 variantId: string | null;14 scope: 'variant' | 'asset';15}1617export const getAssetDepth = cache(async (assetId: string, variantId: string | null): Promise<AssetDepth | null> => {18 const stats = await one<Record<string, unknown>>(sql`19 SELECT s.riv_variant_id, s.riv_usd, (SELECT count(*) FROM asset_variants v WHERE v.asset_id = ${assetId}) AS variants,20 vs.riv_usd AS variant_riv21 FROM asset_stats s LEFT JOIN variant_stats vs ON vs.variant_id = ${variantId ?? sql`s.riv_variant_id`}22 WHERE s.asset_id = ${assetId}`);23 if (!stats) return null;24 const target = variantId ?? (stats.riv_variant_id ? String(stats.riv_variant_id) : null);25 const riv = variantId ? num(stats.variant_riv) : num(stats.variant_riv) ?? num(stats.riv_usd);26 if (riv === null) return null;27 const single = int(stats.variants) <= 1;28 const scope: AssetDepth['scope'] = target && !single ? 'variant' : 'asset';29 const asks = await rows<{ price_usd: unknown }>(sql`30 SELECT l.price_usd FROM listings l31 WHERE l.asset_id = ${assetId} AND l.availability = 'available' AND l.price_usd > 0 AND l.listing_type <> 'auction'32 ${scope === 'variant' ? sql`AND l.variant_id = ${target}` : sql``}33 AND NOT (l.grader IS NOT NULL AND l.grade IS NULL)`);34 const depth = marketDepth(asks.map((a) => num(a.price_usd)), riv);35 return depth ? { depth, riv, variantId: scope === 'variant' ? target : null, scope } : null;36});3738/**39 * Days on market / time-to-sale (§24, §30, §214) from observed listing lifecycles. Only listings the40 * connector marked `sold` are sale outcomes; `ended` / `removed` are censored. With fewer than 10 own41 * lifecycles the category's pooled lifecycles (last 365 days) are used and labelled as such.42 */43export interface AssetLiquidation {44 model: LiquidationModel;45 fair: FairPrices;46 level: 'asset' | 'category';47 lifecycles: number;48}4950const LIFECYCLE_SELECT = sql`l.first_seen_at, l.last_seen_at, l.availability,51 CASE WHEN l.price_usd > 0 AND coalesce(vs.riv_usd, s.riv_usd) > 0 THEN l.price_usd / coalesce(vs.riv_usd, s.riv_usd) END AS ask_to_riv`;5253function toLifecycle(x: Record<string, unknown>): ListingLifecycle | null {54 const first = x.first_seen_at instanceof Date ? x.first_seen_at : new Date(String(x.first_seen_at));55 const last = x.last_seen_at instanceof Date ? x.last_seen_at : new Date(String(x.last_seen_at));56 const outcome = String(x.availability);57 if (Number.isNaN(first.getTime()) || Number.isNaN(last.getTime())) return null;58 if (outcome !== 'sold' && outcome !== 'ended' && outcome !== 'removed') return null;59 return { firstSeen: first, lastSeen: last, outcome, askToRiv: num(x.ask_to_riv) };60}6162export const getAssetLiquidation = cache(async (asset: { id: string; categorySlug: string; rivUsd: number | null; rivLowUsd: number | null; rivHighUsd: number | null }): Promise<AssetLiquidation | null> => {63 const own = await rows<Record<string, unknown>>(sql`64 SELECT ${LIFECYCLE_SELECT} FROM listings l65 LEFT JOIN variant_stats vs ON vs.variant_id = l.variant_id66 LEFT JOIN asset_stats s ON s.asset_id = l.asset_id67 WHERE l.asset_id = ${asset.id} AND l.availability IN ('sold', 'ended', 'removed') AND l.last_seen_at > l.first_seen_at68 ORDER BY l.last_seen_at DESC LIMIT 2000`);69 let level: AssetLiquidation['level'] = 'asset';70 let raw = own;71 if (own.length < 10) {72 level = 'category';73 raw = await rows<Record<string, unknown>>(sql`74 SELECT ${LIFECYCLE_SELECT} FROM listings l75 JOIN assets a ON a.id = l.asset_id76 LEFT JOIN variant_stats vs ON vs.variant_id = l.variant_id77 LEFT JOIN asset_stats s ON s.asset_id = l.asset_id78 WHERE a.category_slug = ${asset.categorySlug} AND l.availability IN ('sold', 'ended', 'removed')79 AND l.last_seen_at > l.first_seen_at AND l.last_seen_at >= now() - interval '365 days'80 ORDER BY l.last_seen_at DESC LIMIT 5000`);81 }82 const lifecycles = raw.map(toLifecycle).filter((x): x is ListingLifecycle => x !== null);83 if (!lifecycles.length) return null;84 const model = liquidationModel(lifecycles);85 const fair = fairPrices({ riv: asset.rivUsd, low: asset.rivLowUsd, high: asset.rivHighUsd }, model);86 return { model, fair, level, lifecycles: lifecycles.length };87});88