TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import { getDb, sql } from '@rareindex/database';2import { fairPrices, liquidationModel, marketDepth, type ListingLifecycle } from '@rareindex/valuation';34/**5 * Read-only query helpers for the public API. Plain SQL through drizzle's `sql` tag: every6 * function returns already-shaped rows so route handlers stay thin. Nothing here writes.7 */8type Row = Record<string, unknown>;9async function rows<T = Row>(q: ReturnType<typeof sql>): Promise<T[]> {10 const res = await getDb().execute(q);11 return res as unknown as T[];12}1314export const ASSET_COLUMNS = sql`a.id, a.slug, a.title, a.name, a.category_slug, a.family_slug, a.brand, a.franchise, a.set_name, a.set_code, a.number, a.year, a.edition, a.variant, a.language, a.hero_image_url, a.identifiers, a.data_quality,15 s.riv_usd, s.riv_low_usd, s.riv_high_usd, s.riv_confidence, s.riv_sample_size, s.latest_sale_usd, s.latest_sale_at, s.change_7d, s.change_30d, s.change_1y, s.ath_usd, s.atl_usd,16 s.sales_count, s.sales_30d, s.active_listings, s.min_ask_usd, s.liquidity_score, s.rarity_score, s.momentum_30d, s.trending_score, s.value_opportunity, s.updated_at as stats_updated_at`;1718export async function searchAssets(opts: { q?: string; category?: string; limit: number; offset: number }) {19 const q = opts.q?.trim();20 const catFilter = opts.category ? sql`and (a.category_slug = ${opts.category} or a.family_slug = ${opts.category})` : sql``;21 if (q) {22 const tsq = q23 .split(/\s+/)24 .filter(Boolean)25 .map((t) => t.replace(/[^\p{L}\p{N}./-]/gu, ''))26 .filter(Boolean)27 .map((t) => `${t}:*`)28 .join(' & ');29 return rows(sql`30 select ${ASSET_COLUMNS},31 (coalesce(ts_rank(a.search, to_tsquery('simple', ${tsq})), 0) * 2 + similarity(a.title, ${q}) + coalesce(log(1 + s.sales_count), 0) * 0.05) as score32 from assets a left join asset_stats s on s.asset_id = a.id33 where (a.search @@ to_tsquery('simple', ${tsq}) or a.title % ${q} or a.identifiers::text ilike ${'%' + q + '%'}) ${catFilter}34 order by score desc, s.sales_count desc nulls last35 limit ${opts.limit} offset ${opts.offset}`);36 }37 return rows(sql`38 select ${ASSET_COLUMNS}, 0 as score from assets a left join asset_stats s on s.asset_id = a.id39 where true ${catFilter}40 order by s.sales_count desc nulls last, a.updated_at desc41 limit ${opts.limit} offset ${opts.offset}`);42}4344export async function getAsset(idOrSlug: string) {45 const [asset] = await rows(sql`select ${ASSET_COLUMNS}, a.description, a.reference, a.model, a.series, a.rarity, a.production_quantity, a.original_msrp, a.original_msrp_currency, a.release_date, a.metadata, a.created_at, a.updated_at46 from assets a left join asset_stats s on s.asset_id = a.id where a.id = ${idOrSlug} or a.slug = ${idOrSlug} limit 1`);47 if (!asset) return null;48 const id = asset.id as string;49 const variants = await rows(sql`select v.id, v.variant_key, v.label, v.grader, v.grade, v.qualifier, v.condition, v.completeness, v.is_default,50 vs.riv_usd, vs.riv_low_usd, vs.riv_high_usd, vs.riv_confidence, vs.riv_sample_size, vs.latest_sale_usd, vs.latest_sale_at, vs.change_30d, vs.change_1y, vs.sales_count, vs.sales_30d, vs.active_listings, vs.min_ask_usd, vs.liquidity_score51 from asset_variants v left join variant_stats vs on vs.variant_id = v.id where v.asset_id = ${id} order by vs.sales_count desc nulls last, v.label`);52 const [valuation] = await rows(sql`select id, variant_id, computed_at, riv_usd, low_usd, high_usd, confidence, confidence_label, sample_size, window_days, methods, method, notes from valuations where asset_id = ${id} and variant_id is null order by computed_at desc limit 1`);53 const sources = await rows(sql`select source_id, count(*)::int as sales from sales where asset_id = ${id} and status = 'valid' group by source_id order by sales desc`);54 return { ...(asset as Row & { id: string }), variants, valuation: valuation ?? null, sources };55}5657export async function assetSales(assetId: string, opts: { limit: number; offset: number; variantId?: string; includeFlagged?: boolean }) {58 const statusFilter = opts.includeFlagged ? sql`and status <> 'excluded'` : sql`and status = 'valid'`;59 const variantFilter = opts.variantId ? sql`and variant_id = ${opts.variantId}` : sql``;60 return rows(sql`select id, variant_id, source_id, source_url, sale_type, sale_date, price, currency, price_usd, buyer_premium_included, all_in_usd, fee_basis, buyer_premium_rate, condition, grader, grade, certification_number, auction_house, lot_number, image_urls, raw_title, confidence, data_quality, status, flags61 from sales where asset_id = ${assetId} ${statusFilter} ${variantFilter} order by sale_date desc limit ${opts.limit} offset ${opts.offset}`);62}6364export async function assetListings(assetId: string, opts: { limit: number; offset: number; availability?: string }) {65 const avail = opts.availability ?? 'available';66 return rows(sql`select id, variant_id, source_id, source_url, listing_type, price, currency, price_usd, seller, location, shipping_cost, condition, grader, grade, certification_number, image_urls, raw_title, listed_at, ends_at, availability, bid_count, first_seen_at, last_seen_at, discount_to_riv, flags67 from listings where asset_id = ${assetId} and availability = ${avail} order by price_usd asc nulls last limit ${opts.limit} offset ${opts.offset}`);68}6970export async function assetHistory(assetId: string, opts: { variantId?: string; from?: string; to?: string }) {71 const variant = opts.variantId ?? '';72 const from = opts.from ?? '1900-01-01';73 const to = opts.to ?? '2999-12-31';74 return rows(sql`select date, riv_usd, latest_sale_usd, median_usd, sales_count, volume_usd, listings_count, min_ask_usd, observation_usd75 from price_snapshots where asset_id = ${assetId} and variant_id = ${variant} and date between ${from} and ${to} order by date asc`);76}7778export async function listCategories() {79 return rows(sql`select c.slug, c.parent_slug, c.family_slug, c.name, c.short_name, c.description, c.level, c.phase, c.active, c.index_ticker, c.condition_scale, c.graders,80 (select count(*)::int from assets a where a.category_slug = c.slug) as tracked_assets81 from categories c where c.active order by c.sort_order`);82}8384export async function listIndices() {85 return rows(sql`with latest as (86 select distinct on (index_id) index_id, date, value, constituents_count, transactions, volume_usd, median_sale_usd, market_cap_est_usd, market_cap_confidence, liquidity_score, momentum, tracked_assets, coverage87 from index_values order by index_id, date desc)88 select i.id, i.ticker, i.name, i.description, i.parent_ticker, i.family_slugs, i.methodology, i.weighting, i.base_date, i.base_value, i.min_constituents, i.is_flagship, i.color,89 l.date as as_of, l.value, l.constituents_count, l.transactions, l.volume_usd, l.median_sale_usd, l.market_cap_est_usd, l.market_cap_confidence, l.liquidity_score, l.momentum, l.tracked_assets, l.coverage,90 (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '1 day' order by date desc limit 1) as value_1d,91 (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '7 day' order by date desc limit 1) as value_7d,92 (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '30 day' order by date desc limit 1) as value_30d,93 (select value from index_values v where v.index_id = i.id and v.date <= l.date - interval '365 day' order by date desc limit 1) as value_1y,94 (select value from index_values v where v.index_id = i.id and v.date <= date_trunc('year', l.date)::date order by date desc limit 1) as value_ytd95 from indices i left join latest l on l.index_id = i.id where i.active order by i.is_flagship desc, i.ticker`);96}9798export async function indexHistory(ticker: string, opts: { from?: string; to?: string }) {99 const from = opts.from ?? '1900-01-01';100 const to = opts.to ?? '2999-12-31';101 return rows(sql`select v.date, v.value, v.constituents_count, v.transactions, v.volume_usd, v.median_sale_usd, v.avg_sale_usd, v.market_cap_est_usd, v.liquidity_score, v.momentum, v.coverage102 from index_values v join indices i on i.id = v.index_id where i.ticker = ${ticker} and v.date between ${from} and ${to} order by v.date asc`);103}104105export async function listMarkets() {106 return rows(sql`with latest as (107 select distinct on (category_slug) * from category_snapshots order by category_slug, date desc)108 select c.slug, c.name, c.family_slug, c.level, c.index_ticker, l.date as as_of, l.index_value, l.tracked_assets, l.assets_with_valuation, l.sales, l.volume_usd, l.median_sale_usd, l.active_listings, l.market_cap_est_usd, l.liquidity_score, l.change_1d, l.change_7d, l.change_30d, l.change_1y,109 (select count(*)::int from assets a where a.family_slug = c.slug or a.category_slug = c.slug) as assets_now110 from categories c left join latest l on l.category_slug = c.slug where c.active and c.level = 0 order by c.sort_order`);111}112113export async function getMarket(slug: string) {114 const [category] = await rows(sql`select slug, parent_slug, family_slug, name, description, level, phase, index_ticker, condition_scale, graders from categories where slug = ${slug}`);115 if (!category) return null;116 const [snapshot] = await rows(sql`select * from category_snapshots where category_slug = ${slug} order by date desc limit 1`);117 const scope = sql`(a.category_slug = ${slug} or a.family_slug = ${slug})`;118 const [counts] = await rows(sql`select count(*)::int as tracked_assets, count(s.riv_usd)::int as valued_assets, coalesce(sum(s.sales_count),0)::int as sales_total, coalesce(sum(s.active_listings),0)::int as active_listings from assets a left join asset_stats s on s.asset_id = a.id where ${scope}`);119 const gainers = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.change_30d is not null and s.riv_sample_size >= 3 order by s.change_30d desc limit 10`);120 const losers = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.change_30d is not null and s.riv_sample_size >= 3 order by s.change_30d asc limit 10`);121 const mostValuable = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.riv_usd is not null order by s.riv_usd desc limit 10`);122 const mostLiquid = await rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where ${scope} and s.liquidity_score is not null order by s.liquidity_score desc limit 10`);123 const recentSales = await rows(sql`select sa.id, sa.asset_id, a.slug as asset_slug, a.title, sa.source_id, sa.source_url, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade from sales sa join assets a on a.id = sa.asset_id where ${scope} and sa.status = 'valid' order by sa.sale_date desc limit 20`);124 const history = await rows(sql`select date, index_value, sales, volume_usd, median_sale_usd, active_listings, tracked_assets from category_snapshots where category_slug = ${slug} order by date asc`);125 return { category, snapshot: snapshot ?? null, counts, gainers, losers, most_valuable: mostValuable, most_liquid: mostLiquid, recent_sales: recentSales, history };126}127128export async function trending(opts: { category?: string; limit: number; offset: number }) {129 const cat = opts.category ? sql`and (a.category_slug = ${opts.category} or a.family_slug = ${opts.category})` : sql``;130 return rows(sql`select ${ASSET_COLUMNS} from assets a join asset_stats s on s.asset_id = a.id where s.trending_score is not null ${cat} order by s.trending_score desc limit ${opts.limit} offset ${opts.offset}`);131}132133export async function latestSales(opts: { category?: string; minUsd?: number; limit: number; offset: number }) {134 const cat = opts.category ? sql`and (a.category_slug = ${opts.category} or a.family_slug = ${opts.category})` : sql``;135 const min = opts.minUsd ? sql`and sa.price_usd >= ${opts.minUsd}` : sql``;136 return rows(sql`select sa.id, sa.asset_id, a.slug as asset_slug, a.title, a.category_slug, a.hero_image_url, sa.source_id, sa.source_url, sa.sale_type, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.all_in_usd, sa.fee_basis, sa.grader, sa.grade, sa.condition, sa.auction_house, sa.confidence137 from sales sa join assets a on a.id = sa.asset_id where sa.status = 'valid' ${cat} ${min} order by sa.sale_date desc, sa.created_at desc limit ${opts.limit} offset ${opts.offset}`);138}139140/** Record sales: highest verified transaction per family (§154). */141export async function recordSales() {142 return rows(sql`select distinct on (a.family_slug) a.family_slug, c.name as family_name, sa.id as sale_id, sa.asset_id, a.slug as asset_slug, a.title, sa.source_id, sa.source_url, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade, sa.auction_house143 from sales sa join assets a on a.id = sa.asset_id join categories c on c.slug = a.family_slug144 where sa.status = 'valid' and sa.confidence >= 0.8 and sa.is_bundle = false145 order by a.family_slug, sa.price_usd desc`);146}147148export async function platformStats() {149 const [r] = await rows(sql`select150 (select count(*)::int from assets) as assets,151 (select count(*)::int from sales where status = 'valid') as sales,152 (select count(*)::int from listings where availability = 'available') as listings,153 (select count(*)::int from sources where active) as sources,154 (select count(*)::int from connectors where status = 'active') as connectors,155 (select count(*)::int from categories where active) as categories`);156 return r;157}158159/**160 * Market depth (§25–§26) + time-to-sell model (§24, §30, §213–§214) for one asset. Depth uses the161 * current asks of the representative variant (all variants when the asset has one); lifecycles use162 * the asset's own completed listings, or the category's pooled last-365-day lifecycles when fewer163 * than 10 exist (`level: "category"`). Model estimates from observed listings, not advice.164 */165export async function assetDepth(assetId: string, variantId?: string) {166 const [st] = await rows(sql`select s.riv_variant_id, s.riv_usd, s.riv_low_usd, s.riv_high_usd, a.category_slug,167 (select count(*)::int from asset_variants v where v.asset_id = a.id) as variants,168 vs.riv_usd as variant_riv, vs.riv_low_usd as variant_low, vs.riv_high_usd as variant_high169 from assets a left join asset_stats s on s.asset_id = a.id170 left join variant_stats vs on vs.variant_id = ${variantId ?? sql`s.riv_variant_id`}171 where a.id = ${assetId}`);172 if (!st) return null;173 const target = variantId ?? (st.riv_variant_id ? String(st.riv_variant_id) : null);174 const riv = variantId ? n(st.variant_riv) : n(st.variant_riv) ?? n(st.riv_usd);175 const low = variantId ? n(st.variant_low) : n(st.variant_low) ?? n(st.riv_low_usd);176 const high = variantId ? n(st.variant_high) : n(st.variant_high) ?? n(st.riv_high_usd);177 const scope = target && Number(st.variants) > 1 ? 'variant' : 'asset';178 const asks = await rows(sql`select price_usd from listings l where l.asset_id = ${assetId} and l.availability = 'available' and l.price_usd > 0179 ${scope === 'variant' ? sql`and l.variant_id = ${target}` : sql``} and not (l.grader is not null and l.grade is null)`);180 const depth = marketDepth(asks.map((a) => n(a.price_usd)), riv);181 const lifecycleSelect = sql`l.first_seen_at, l.last_seen_at, l.availability,182 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`;183 let own = await rows(sql`select ${lifecycleSelect} from listings l left join variant_stats vs on vs.variant_id = l.variant_id left join asset_stats s on s.asset_id = l.asset_id184 where l.asset_id = ${assetId} and l.availability in ('sold','ended','removed') and l.last_seen_at > l.first_seen_at order by l.last_seen_at desc limit 2000`);185 let level: 'asset' | 'category' = 'asset';186 if (own.length < 10) {187 level = 'category';188 own = await rows(sql`select ${lifecycleSelect} from listings l join assets a on a.id = l.asset_id left join variant_stats vs on vs.variant_id = l.variant_id left join asset_stats s on s.asset_id = l.asset_id189 where a.category_slug = ${String(st.category_slug)} and l.availability in ('sold','ended','removed') and l.last_seen_at > l.first_seen_at and l.last_seen_at >= now() - interval '365 days'190 order by l.last_seen_at desc limit 5000`);191 }192 const lifecycles = own193 .map((x): ListingLifecycle | null => {194 const outcome = String(x.availability);195 if (outcome !== 'sold' && outcome !== 'ended' && outcome !== 'removed') return null;196 return { firstSeen: new Date(String(x.first_seen_at)), lastSeen: new Date(String(x.last_seen_at)), outcome, askToRiv: n(x.ask_to_riv) };197 })198 .filter((x): x is ListingLifecycle => x !== null);199 const model = lifecycles.length ? liquidationModel(lifecycles) : null;200 return {201 asset_id: assetId,202 variant_id: scope === 'variant' ? target : null,203 scope,204 riv_usd: riv,205 depth,206 liquidation: model ? { level, lifecycles: lifecycles.length, ...model } : null,207 fair_prices: riv !== null ? fairPrices({ riv, low, high }, model) : null,208 note: 'Model estimates from observed listings and the published valuation band. Asks are not transactions; only listings marked sold count as sales.',209 };210}211212function n(v: unknown): number | null {213 if (v === null || v === undefined) return null;214 const x = Number(v);215 return Number.isFinite(x) ? x : null;216}217218/**219 * Auction lots with their all-in assessment (§33–§35). Amounts: native (`currency`), USD at the220 * assessment-date rate, and buyer-pays (`all_in_*`, hammer + house premium per `fee_basis`).221 * `bid_vs_riv` / `estimate_vs_riv` follow the listing convention: (all-in − RIV) / RIV, negative =222 * below the valuation; null when the comparison did not pass the gates (`assessment_verdict`).223 * Taxes, duties and shipping are not included.224 */225export const LOT_COLUMNS = sql`l.id, l.auction_id, au.auction_house, au.name as auction_name, au.url as auction_url, l.asset_id, a.slug as asset_slug, a.title as asset_title, a.category_slug, l.variant_id, l.lot_number, l.title, l.url, l.source_id,226 l.estimate_low, l.estimate_high, l.current_bid, l.hammer_price, l.currency, l.bid_count, l.starts_at, l.ends_at, l.status, l.grader, l.grade, l.image_urls,227 l.estimate_low_usd, l.estimate_high_usd, l.current_bid_usd, l.hammer_price_usd, l.fx_rate, l.fx_date,228 l.buyer_premium_rate, l.fee_basis, l.all_in_bid_usd, l.all_in_estimate_low_usd, l.all_in_estimate_high_usd,229 l.riv_usd_at_assessment as riv_usd, l.bid_vs_riv, l.estimate_vs_riv, l.assessment_verdict, l.assessed_at`;230231export async function auctionLots(opts: { status?: string; endingWithinHours?: number; category?: string; house?: string; belowRiv?: boolean; assetId?: string; sort?: 'ending' | 'discount'; limit: number; offset: number }) {232 const w = [sql`true`];233 if (opts.status) w.push(sql`l.status = ${opts.status}`);234 else w.push(sql`l.status in ('live','upcoming')`);235 if (opts.endingWithinHours) w.push(sql`l.ends_at between now() and now() + (${opts.endingWithinHours}::int || ' hours')::interval`);236 if (opts.category) w.push(sql`(a.category_slug = ${opts.category} or a.family_slug = ${opts.category})`);237 if (opts.house) w.push(sql`lower(au.auction_house) = lower(${opts.house})`);238 // estimate-based deals count too (a lot with no bid yet is compared on its all-in low estimate); the row says which via bid_vs_riv / estimate_vs_riv239 if (opts.belowRiv) w.push(sql`l.assessment_verdict = 'deal' and coalesce(l.bid_vs_riv, l.estimate_vs_riv) is not null`);240 if (opts.assetId) w.push(sql`l.asset_id = ${opts.assetId}`);241 const where = sql.join(w, sql` and `);242 const order = opts.sort === 'discount' ? sql`coalesce(l.bid_vs_riv, l.estimate_vs_riv) asc nulls last, l.ends_at asc nulls last` : sql`l.ends_at asc nulls last`;243 return rows(sql`select ${LOT_COLUMNS} from auction_lots l join auctions au on au.id = l.auction_id left join assets a on a.id = l.asset_id244 where ${where} order by ${order} limit ${opts.limit} offset ${opts.offset}`);245}246