TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import { and, desc, eq, gte, inArray, isNull, or, sql } from 'drizzle-orm';2import { assetStats, assetVariants, assets, gradePremiums, listings, populationReports, priceObservations, priceSnapshots, sales, sources, valuations, variantStats } from '@rareindex/database';3import { ASK_ANOMALY_HIGH_RATIO, ASK_ANOMALY_LOW_RATIO, ASK_MIN_CONFIDENCE, ASK_MIN_MATCH_CONFIDENCE, ASK_MIN_SAMPLE, DEAL_REVIEW_THRESHOLD, adjustmentFactor, assetDataQuality, computeGradePremiums, computeValuation, detectOutliers, liquidityScore, momentumScore, rarityScore, trendingScore, type GradePremium, type SaleInput } from '@rareindex/valuation';4import { logger, median, newId, pctChange, toDateOnly } from '@rareindex/shared';5import { db } from '../lib/db.ts';6import { auditMany } from '../lib/audit.ts';7import { emit } from '../lib/events.ts';89const log = logger.child({ component: 'valuation' });10const DAY = 86_400_000;11const safeRatioV = (v: number | null): number | null => (v === null || !Number.isFinite(v) || Math.abs(v) >= 99 ? null : v);12const asDate = (v: unknown): Date | null => (v instanceof Date ? v : typeof v === 'string' ? new Date(v) : null);1314const premiumCache = new Map<string, { at: number; rows: GradePremium[] }>();15async function premiumsFor(categorySlug: string): Promise<GradePremium[]> {16 const hit = premiumCache.get(categorySlug);17 if (hit && Date.now() - hit.at < 3600_000) return hit.rows;18 const rows = await db().select({ grader: gradePremiums.grader, grade: gradePremiums.grade, marketMultiplier: gradePremiums.marketMultiplier, sampleSize: gradePremiums.sampleSize }).from(gradePremiums).where(eq(gradePremiums.categorySlug, categorySlug));19 premiumCache.set(categorySlug, { at: Date.now(), rows });20 return rows;21}2223const trustCache = new Map<string, number>();24async function trustMap(): Promise<Map<string, number>> {25 if (trustCache.size) return trustCache;26 for (const s of await db().select({ id: sources.id, t: sources.trustScore }).from(sources)) trustCache.set(s.id, Number(s.t));27 return trustCache;28}2930interface SaleRow {31 id: string;32 variantId: string | null;33 sourceId: string;34 saleDate: Date;35 /** buyer-pays price: coalesce(all_in_usd, price_usd) — hammer + estimated premium when applicable (§35) */36 priceUsd: number;37 /** included | added_published | added_approximate | added_default | none | unknown | null */38 feeBasis: string | null;39 grader: string | null;40 grade: string | null;41 quantity: number;42 isBundle: boolean;43 status: string;44 confidence: number;45 flags: string[];46}47const BUYER_PAYS = sql<number>`coalesce(${sales.allInUsd}, ${sales.priceUsd})`;4849/** Value one asset: outliers → per-variant valuations → asset stats, snapshots, listing discounts. */50export async function valueAsset(assetId: string, opts: { now?: Date; rebuildHistory?: boolean } = {}): Promise<{ variants: number; riv: number | null }> {51 const now = opts.now ?? new Date();52 const [asset] = await db().select().from(assets).where(eq(assets.id, assetId)).limit(1);53 if (!asset) return { variants: 0, riv: null };54 const trust = await trustMap();55 const variants = await db().select().from(assetVariants).where(eq(assetVariants.assetId, assetId));56 const since3y = new Date(now.getTime() - 3 * 365 * DAY);57 const saleRows = (await db()58 .select({ id: sales.id, variantId: sales.variantId, sourceId: sales.sourceId, saleDate: sales.saleDate, priceUsd: BUYER_PAYS, feeBasis: sales.feeBasis, grader: sales.grader, grade: sales.grade, quantity: sales.quantity, isBundle: sales.isBundle, status: sales.status, confidence: sales.confidence, flags: sales.flags })59 .from(sales)60 .where(and(eq(sales.assetId, assetId), gte(sales.saleDate, since3y)))61 .orderBy(desc(sales.saleDate))) as SaleRow[];62 const allSalesCount = (await db().select({ n: sql<number>`count(*)::int`, min: sql<number>`min(${BUYER_PAYS})`, max: sql<number>`max(${BUYER_PAYS})`, minAt: sql<Date>`(array_agg(${sales.saleDate} order by ${BUYER_PAYS} asc))[1]`, maxAt: sql<Date>`(array_agg(${sales.saleDate} order by ${BUYER_PAYS} desc))[1]` }).from(sales).where(and(eq(sales.assetId, assetId), eq(sales.status, 'valid'))))[0]!;6364 // 1. outliers per variant (flag only new ones; never delete)65 const flagsToApply: Array<{ id: string; reason: string; score: number }> = [];66 const byVariant = new Map<string, SaleRow[]>();67 for (const s of saleRows) byVariant.set(s.variantId ?? '', [...(byVariant.get(s.variantId ?? '') ?? []), s]);68 for (const list of byVariant.values()) {69 const valid = list.filter((s) => s.status === 'valid');70 for (const f of detectOutliers(valid.map((s) => ({ id: s.id, priceUsd: Number(s.priceUsd), date: s.saleDate, quantity: s.quantity, isBundle: s.isBundle })))) flagsToApply.push(f);71 }72 if (flagsToApply.length) {73 for (const f of flagsToApply) {74 await db().update(sales).set({ status: 'flagged', flags: sql`array_append(${sales.flags}, ${f.reason})` }).where(and(eq(sales.id, f.id), eq(sales.status, 'valid')));75 const row = saleRows.find((s) => s.id === f.id);76 if (row) row.status = 'flagged';77 }78 await auditMany(flagsToApply.map((f) => ({ entityType: 'sale', entityId: f.id, action: 'flagged', reason: f.reason, details: { modifiedZ: f.score, assetId } })));79 }8081 // 2. observations (guide prices) last 90d per variant82 const obsRows = await db()83 .select({ variantId: priceObservations.variantId, priceUsd: priceObservations.priceUsd, date: priceObservations.observationDate, priceKind: priceObservations.priceKind, sourceId: priceObservations.sourceId })84 .from(priceObservations)85 .where(and(eq(priceObservations.assetId, assetId), gte(priceObservations.observationDate, toDateOnly(new Date(now.getTime() - 90 * DAY)))));86 const obsByVariant = new Map<string, typeof obsRows>();87 for (const o of obsRows) obsByVariant.set(o.variantId ?? '', [...(obsByVariant.get(o.variantId ?? '') ?? []), o]);8889 const premiums = await premiumsFor(asset.categorySlug);90 const toInput = (s: SaleRow): SaleInput => ({ id: s.id, priceUsd: Number(s.priceUsd), date: s.saleDate, trust: trust.get(s.sourceId) ?? 0.6, confidence: Number(s.confidence), grader: s.grader, grade: s.grade, quantity: s.quantity, isBundle: s.isBundle, status: s.status as SaleInput['status'] });9192 // 3. per-variant valuations93 const results: Array<{ variantId: string; isDefault: boolean; out: ReturnType<typeof computeValuation>; salesCount: number; sales30d: number; activeL: { count: number; minAsk: number | null } }> = [];94 const today = toDateOnly(now);95 // Ask-vs-RIV is recomputed from scratch on every pass: a stale discount computed against a96 // previous (possibly wrong-variant) valuation must never survive (§84, §196).97 await db().update(listings).set({ discountToRiv: null }).where(and(eq(listings.assetId, assetId), eq(listings.availability, 'available'), sql`${listings.discountToRiv} is not null`));98 for (const v of variants) {99 const own = (byVariant.get(v.id) ?? []).map(toInput);100 const comps: NonNullable<Parameters<typeof computeValuation>[0]['comps']> = [];101 if (own.filter((s) => (s.status ?? 'valid') === 'valid').length < 3 && premiums.length) {102 for (const other of variants) {103 if (other.id === v.id) continue;104 const adj = adjustmentFactor(premiums, { grader: other.grader, grade: other.grade }, { grader: v.grader, grade: v.grade });105 if (adj === null) continue;106 for (const s of byVariant.get(other.id) ?? []) if (s.status === 'valid') comps.push({ priceUsd: Number(s.priceUsd), date: s.saleDate, adjustment: adj, trust: trust.get(s.sourceId) });107 }108 }109 const out = computeValuation({110 sales: own,111 comps,112 observations: (obsByVariant.get(v.id) ?? []).map((o) => ({ priceUsd: Number(o.priceUsd), date: new Date(`${o.date}T00:00:00Z`), priceKind: o.priceKind, trust: trust.get(o.sourceId) })),113 now,114 categorySlug: asset.categorySlug,115 });116 const salesCount = own.filter((s) => (s.status ?? 'valid') === 'valid').length;117 const sales30d = own.filter((s) => (s.status ?? 'valid') === 'valid' && now.getTime() - s.date.getTime() <= 30 * DAY).length;118 const activeL = await activeListingStats(assetId, v.id);119 // §35: say when the inputs include an estimated (not invoiced) buyer premium120 if (out.salesUsed.length) {121 const used = new Set(out.salesUsed);122 const est = (byVariant.get(v.id) ?? []).filter((s) => used.has(s.id) && s.feeBasis?.startsWith('added_')).length;123 if (est) out.notes.push(`${Math.round((100 * est) / out.salesUsed.length)} % of inputs include an estimated buyer premium`);124 }125 results.push({ variantId: v.id, isDefault: v.isDefault, out, salesCount, sales30d, activeL });126 if (out.riv === null && salesCount === 0 && (obsByVariant.get(v.id) ?? []).length === 0) continue;127 await db().insert(valuations).values({128 id: newId('valuation'),129 assetId,130 variantId: v.id,131 computedAt: now,132 rivUsd: out.riv,133 lowUsd: out.low,134 highUsd: out.high,135 confidence: out.confidence,136 confidenceLabel: out.label,137 sampleSize: out.sampleSize,138 windowDays: out.windowDays,139 methods: out.methods,140 salesUsed: out.salesUsed,141 observationsUsed: out.observationsUsed,142 method: `ensemble_v1:${out.basis}`,143 notes: out.notes,144 });145 const prev30 = await snapshotValue(assetId, v.id, new Date(now.getTime() - 30 * DAY));146 const prev1y = await snapshotValue(assetId, v.id, new Date(now.getTime() - 365 * DAY));147 const latest = own[0];148 await db()149 .insert(variantStats)150 .values({ variantId: v.id, assetId, rivUsd: out.riv, rivLowUsd: out.low, rivHighUsd: out.high, rivConfidence: out.confidence, rivSampleSize: out.sampleSize, latestSaleUsd: latest?.priceUsd ?? null, latestSaleAt: latest?.date ?? null, change30d: safeRatioV(pctChange(prev30, out.riv)), change1y: safeRatioV(pctChange(prev1y, out.riv)), salesCount, sales30d, activeListings: activeL.count, minAskUsd: activeL.minAsk, liquidityScore: liquidityScore({ salesPerMonth: (own.length / 36) || 0, activeListings: activeL.count, sources: new Set((byVariant.get(v.id) ?? []).map((s) => s.sourceId)).size, medianDaysBetweenSales: medianGapDays(own.map((s) => s.date)), askSoldSpread: activeL.minAsk && out.riv ? (activeL.minAsk - out.riv) / out.riv : null }), updatedAt: now })151 .onConflictDoUpdate({ target: variantStats.variantId, set: { rivUsd: out.riv, rivLowUsd: out.low, rivHighUsd: out.high, rivConfidence: out.confidence, rivSampleSize: out.sampleSize, latestSaleUsd: latest?.priceUsd ?? null, latestSaleAt: latest?.date ?? null, change30d: safeRatioV(pctChange(prev30, out.riv)), change1y: safeRatioV(pctChange(prev1y, out.riv)), salesCount, sales30d, activeListings: activeL.count, minAskUsd: activeL.minAsk, updatedAt: now } });152 await db()153 .insert(priceSnapshots)154 .values({ assetId, variantId: v.id, date: today, rivUsd: out.riv, latestSaleUsd: latest?.priceUsd ?? null, medianUsd: out.distribution.median, salesCount: own.filter((s) => toDateOnly(s.date) === today).length, volumeUsd: own.filter((s) => toDateOnly(s.date) === today).reduce((a, s) => a + s.priceUsd, 0) || null, listingsCount: activeL.count, minAskUsd: activeL.minAsk, observationUsd: out.methods.guide })155 .onConflictDoUpdate({ target: [priceSnapshots.assetId, priceSnapshots.variantId, priceSnapshots.date], set: { rivUsd: out.riv, latestSaleUsd: latest?.priceUsd ?? null, medianUsd: out.distribution.median, listingsCount: activeL.count, minAskUsd: activeL.minAsk, observationUsd: out.methods.guide } });156 // Ask vs RIV on the active listings of THIS variant only (§84 data-quality gate):157 // – the valuation must rest on ≥ ASK_MIN_SAMPLE transactions with confidence ≥ ASK_MIN_CONFIDENCE158 // (comps-only / guide-only estimates never qualify an ask);159 // – the listing must be confidently matched and carry a readable grade when it is slabbed;160 // – an ask outside [0.1×, 10×] RIV is flagged `riv_anomaly` (identity/data problem), not priced.161 if (out.riv !== null && out.basis === 'transactions' && out.confidence >= ASK_MIN_CONFIDENCE && out.sampleSize >= ASK_MIN_SAMPLE) {162 const lo = out.riv * ASK_ANOMALY_LOW_RATIO;163 const hi = out.riv * ASK_ANOMALY_HIGH_RATIO;164 // Auction lots are excluded: a current/opening bid (often ¥1 on Yahoo! Auctions) is not an asking price.165 // Bid-vs-RIV belongs to auction intelligence with fees (§33–§35), not to the deal rails.166 const scope = and(eq(listings.variantId, v.id), eq(listings.availability, 'available'), sql`${listings.priceUsd} > 0`, sql`${listings.listingType} <> 'auction'`);167 await db()168 .update(listings)169 .set({170 discountToRiv: sql`round((${listings.priceUsd} - ${out.riv}) / ${out.riv}, 4)`,171 // > 50 % below RIV (§174): keep the number, flag `riv_review`, never surface as a deal172 flags: sql`case when ${listings.priceUsd} < ${out.riv * (1 + DEAL_REVIEW_THRESHOLD)} then (case when 'riv_review' = any(${listings.flags}) then array_remove(${listings.flags}, 'riv_anomaly') else array_append(array_remove(${listings.flags}, 'riv_anomaly'), 'riv_review') end) else array_remove(array_remove(${listings.flags}, 'riv_anomaly'), 'riv_review') end`,173 })174 .where(and(scope, sql`${listings.priceUsd} between ${lo} and ${hi}`, sql`${listings.confidence} >= ${ASK_MIN_MATCH_CONFIDENCE}`, sql`not (${listings.grader} is not null and ${listings.grade} is null)`));175 await db()176 .update(listings)177 .set({ discountToRiv: null, flags: sql`case when 'riv_anomaly' = any(${listings.flags}) then ${listings.flags} else array_append(${listings.flags}, 'riv_anomaly') end` })178 .where(and(scope, sql`(${listings.priceUsd} < ${lo} or ${listings.priceUsd} > ${hi})`));179 }180 }181182 // 4. asset-level representative variant (the headline RIV). Preference order:183 // transaction-based valuation with confidence ≥ 0.5 and ≥ 5 sales → the DEFAULT (raw/base) variant184 // when it qualifies, else the qualifying variant with the most sales; fallback: any priced variant185 // by confidence. Rationale: a graded sub-variant with a tighter distribution used to win over the186 // base market and made the headline value (and every ask comparison) wrong by an order of magnitude.187 const priced = results.filter((r) => r.out.riv !== null).sort((a, b) => b.out.confidence - a.out.confidence || b.salesCount - a.salesCount);188 const qualified = priced.filter((r) => r.out.basis === 'transactions' && r.out.confidence >= ASK_MIN_CONFIDENCE && r.out.sampleSize >= ASK_MIN_SAMPLE);189 const rep = qualified.find((r) => r.isDefault) ?? qualified.sort((a, b) => b.salesCount - a.salesCount || b.out.confidence - a.out.confidence)[0] ?? priced[0] ?? null;190 const validSales = saleRows.filter((s) => s.status === 'valid');191 const latest = validSales[0];192 // Changes are measured on the representative variant's own series so that a change of193 // representative variant never shows up as a price move.194 const repSeries = rep?.variantId ?? '';195 const prevAsset = { d1: await snapshotValue(assetId, repSeries, new Date(now.getTime() - 1 * DAY)), d7: await snapshotValue(assetId, repSeries, new Date(now.getTime() - 7 * DAY)), d30: await snapshotValue(assetId, repSeries, new Date(now.getTime() - 30 * DAY)), d90: await snapshotValue(assetId, repSeries, new Date(now.getTime() - 90 * DAY)), y1: await snapshotValue(assetId, repSeries, new Date(now.getTime() - 365 * DAY)) };196 const riv = rep?.out.riv ?? null;197 const activeAll = await activeListingStats(assetId, null);198 const repActive = rep?.activeL ?? null;199 // ATH / ATL / drawdown belong to the same series as the headline RIV: the representative variant.200 // (An asset-wide ATH mixed a sealed copy's record with a loose copy's valuation → −99 % "drawdowns".)201 const extremes = rep202 ? (await db().select({ n: sql<number>`count(*)::int`, min: sql<number>`min(${BUYER_PAYS})`, max: sql<number>`max(${BUYER_PAYS})`, minAt: sql<Date>`(array_agg(${sales.saleDate} order by ${BUYER_PAYS} asc))[1]`, maxAt: sql<Date>`(array_agg(${sales.saleDate} order by ${BUYER_PAYS} desc))[1]` }).from(sales).where(and(eq(sales.assetId, assetId), eq(sales.variantId, rep.variantId), eq(sales.status, 'valid'), sql`${sales.quantity} = 1`, sql`not ${sales.isBundle}`)))[0]!203 : allSalesCount;204 const salesCount = allSalesCount.n;205 const sales30d = validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 30 * DAY).length;206 const sales1y = validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 365 * DAY).length;207 const salesPrev30 = validSales.filter((s) => { const age = now.getTime() - s.saleDate.getTime(); return age > 30 * DAY && age <= 60 * DAY; }).length;208 const pop = await latestPopulation(assetId);209 const [obsCount] = await db().select({ n: sql<number>`count(*)::int` }).from(priceObservations).where(eq(priceObservations.assetId, assetId));210 const sourcesCount = new Set([...saleRows.map((s) => s.sourceId), ...obsRows.map((o) => o.sourceId)]).size;211 const liq = liquidityScore({ salesPerMonth: sales1y / 12, activeListings: activeAll.count, sources: sourcesCount, medianDaysBetweenSales: medianGapDays(validSales.map((s) => s.saleDate)), askSoldSpread: repActive?.minAsk && riv ? (repActive.minAsk - riv) / riv : null });212 const rar = rarityScore({ population: pop?.total ?? null, productionQuantity: asset.productionQuantity, listingsPerYear: activeAll.count > 0 || sales1y > 0 ? activeAll.count * 4 : null, salesPerYear: sales1y > 0 || salesCount > 0 ? sales1y : null, populationGrowthPct: pop?.growth ?? null });213 // ratio columns are numeric(8,6): anything ≥ ±99 (9,900 %) would overflow and abort the asset; such a214 // move is never a market signal but a data/identity artefact → stored as null (§143, §196).215 const safeRatio = (v: number | null): number | null => (v === null || !Number.isFinite(v) || Math.abs(v) >= 99 ? null : v);216 const ch = { d1: safeRatio(pctChange(prevAsset.d1, riv)), d7: safeRatio(pctChange(prevAsset.d7, riv)), d30: safeRatio(pctChange(prevAsset.d30, riv)), d90: safeRatio(pctChange(prevAsset.d90, riv)), y1: safeRatio(pctChange(prevAsset.y1, riv)) };217 const mom = { m7: momentumScore({ priceChange: ch.d7, volumeNow: validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 7 * DAY).length, volumePrev: validSales.filter((s) => { const a = now.getTime() - s.saleDate.getTime(); return a > 7 * DAY && a <= 14 * DAY; }).length }), m30: momentumScore({ priceChange: ch.d30, volumeNow: sales30d, volumePrev: salesPrev30 }), m90: momentumScore({ priceChange: ch.d90, volumeNow: validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 90 * DAY).length, volumePrev: validSales.filter((s) => { const a = now.getTime() - s.saleDate.getTime(); return a > 90 * DAY && a <= 180 * DAY; }).length }), y1: momentumScore({ priceChange: ch.y1, volumeNow: sales1y, volumePrev: validSales.length - sales1y }) };218 const listingMomentum = activeAll.count > 0 ? Math.min(100, activeAll.count * 5) : null;219 const trending = trendingScore({ priceMomentum: mom.m30, volumeMomentum: momentumScore({ priceChange: null, volumeNow: sales30d, volumePrev: salesPrev30 }), searchMomentum: null, listingMomentum, newsMomentum: null });220 // Best gated ask across the asset's variants (negative = below its own variant's RIV). No fallback:221 // comparing the cheapest ask of ANY variant against the headline RIV was the −8,500 % bug.222 const [bestListing] = await db().select({ d: sql<number | null>`min(${listings.discountToRiv})` }).from(listings).where(and(eq(listings.assetId, assetId), eq(listings.availability, 'available'), sql`${listings.discountToRiv} is not null`, sql`not ('riv_review' = any(${listings.flags}))`));223 const fields = { brand: asset.brand, set: asset.setName, number: asset.number, year: asset.year, variant: asset.variant, image: asset.heroImageUrl, description: asset.description, identifiers: Object.keys(asset.identifiers).length ? 1 : null };224 const dq = assetDataQuality({ fieldsPresent: Object.values(fields).filter((x) => x !== null && x !== undefined).length, fieldsTotal: Object.keys(fields).length, sourceTrustAvg: sourcesCount ? median([...new Set(saleRows.map((s) => s.sourceId))].map((s) => trust.get(s) ?? 0.5)) : null, identificationConfidence: rep ? Number(median(validSales.map((s) => Number(s.confidence))) ?? 0.8) : null, hasImage: Boolean(asset.heroImageUrl), salesCount });225 const statsRow = {226 assetId,227 rivUsd: riv,228 rivLowUsd: rep?.out.low ?? null,229 rivHighUsd: rep?.out.high ?? null,230 rivConfidence: rep?.out.confidence ?? null,231 rivSampleSize: rep?.out.sampleSize ?? 0,232 rivVariantId: rep?.variantId ?? null,233 latestSaleUsd: latest ? Number(latest.priceUsd) : null,234 latestSaleAt: latest?.saleDate ?? null,235 change1d: ch.d1,236 change7d: ch.d7,237 change30d: ch.d30,238 change90d: ch.d90,239 change1y: ch.y1,240 athUsd: extremes.n ? Number(extremes.max) : null,241 athAt: extremes.n ? asDate(extremes.maxAt) : null,242 atlUsd: extremes.n ? Number(extremes.min) : null,243 atlAt: extremes.n ? asDate(extremes.minAt) : null,244 salesCount,245 sales30d,246 sales1y,247 volume30dUsd: sales30d ? validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 30 * DAY).reduce((a, s) => a + Number(s.priceUsd), 0) : null,248 activeListings: activeAll.count,249 // lowest ask of the representative variant (comparable to the headline RIV); all variants otherwise250 minAskUsd: repActive?.minAsk ?? (rep ? null : activeAll.minAsk), // never a graded slab's ask next to a raw RIV251 observationsCount: obsCount?.n ?? 0,252 sourcesCount,253 liquidityScore: liq,254 rarityScore: rar,255 momentum7d: mom.m7,256 momentum30d: mom.m30,257 momentum90d: mom.m90,258 momentum1y: mom.y1,259 trendingScore: trending,260 valueOpportunity: bestListing?.d !== null && bestListing?.d !== undefined ? Number(bestListing.d) : null,261 dataQuality: dq,262 updatedAt: now,263 };264 await db().insert(assetStats).values(statsRow).onConflictDoUpdate({ target: assetStats.assetId, set: { ...statsRow, watchers: sql`${assetStats.watchers}`, views30d: sql`${assetStats.views30d}` } });265 await db().update(assets).set({ dataQuality: dq, updatedAt: now }).where(eq(assets.id, assetId));266 await db()267 .insert(priceSnapshots)268 .values({ assetId, variantId: '', date: today, rivUsd: riv, latestSaleUsd: latest ? Number(latest.priceUsd) : null, medianUsd: rep?.out.distribution.median ?? null, salesCount: validSales.filter((s) => toDateOnly(s.saleDate) === today).length, volumeUsd: null, listingsCount: activeAll.count, minAskUsd: activeAll.minAsk, observationUsd: rep?.out.methods.guide ?? null })269 .onConflictDoUpdate({ target: [priceSnapshots.assetId, priceSnapshots.variantId, priceSnapshots.date], set: { rivUsd: riv, latestSaleUsd: latest ? Number(latest.priceUsd) : null, listingsCount: activeAll.count, minAskUsd: activeAll.minAsk } });270 if (opts.rebuildHistory) await rebuildSnapshotHistory(assetId, saleRows, variants.map((v) => v.id), now);271 if (riv !== null) await emit('valuation_updated', { type: 'asset', id: assetId }, { riv, confidence: rep?.out.confidence, sampleSize: rep?.out.sampleSize });272 return { variants: results.length, riv };273}274275async function snapshotValue(assetId: string, variantId: string, at: Date): Promise<number | null> {276 const [row] = await db()277 .select({ v: priceSnapshots.rivUsd })278 .from(priceSnapshots)279 .where(and(eq(priceSnapshots.assetId, assetId), eq(priceSnapshots.variantId, variantId), sql`${priceSnapshots.date} <= ${toDateOnly(at)}`, sql`${priceSnapshots.rivUsd} is not null`))280 .orderBy(desc(priceSnapshots.date))281 .limit(1);282 return row?.v === null || row?.v === undefined ? null : Number(row.v);283}284285async function activeListingStats(assetId: string, variantId: string | null): Promise<{ count: number; minAsk: number | null }> {286 const [row] = await db()287 // count = every live listing; min ask = fixed-price / best-offer / ask only (an auction bid is not an ask)288 .select({ n: sql<number>`count(*)::int`, min: sql<number | null>`min(${listings.priceUsd}) filter (where ${listings.listingType} <> 'auction' and ${listings.priceUsd} > 0)` })289 .from(listings)290 .where(and(eq(listings.assetId, assetId), eq(listings.availability, 'available'), variantId ? eq(listings.variantId, variantId) : undefined));291 return { count: row?.n ?? 0, minAsk: row?.min === null || row?.min === undefined ? null : Number(row.min) };292}293294async function latestPopulation(assetId: string): Promise<{ total: number; growth: number | null } | null> {295 const rows = await db().select({ total: populationReports.total, date: populationReports.reportDate }).from(populationReports).where(eq(populationReports.assetId, assetId)).orderBy(desc(populationReports.reportDate)).limit(2);296 if (!rows.length) return null;297 const growth = rows.length === 2 && rows[1]!.total > 0 ? (rows[0]!.total - rows[1]!.total) / rows[1]!.total : null;298 return { total: rows[0]!.total, growth };299}300301function medianGapDays(dates: Date[]): number | null {302 if (dates.length < 2) return null;303 const s = [...dates].sort((a, b) => a.getTime() - b.getTime());304 const gaps: number[] = [];305 for (let i = 1; i < s.length; i++) gaps.push((s[i]!.getTime() - s[i - 1]!.getTime()) / DAY);306 return median(gaps);307}308309/**310 * Historical snapshots (weekly) reconstructed from transactions only, computing the valuation311 * as of each week with the sales known up to that date. Gives asset charts and index history a312 * past without inventing observations.313 */314export async function rebuildSnapshotHistory(assetId: string, saleRows: SaleRow[], variantIds: string[], now: Date): Promise<number> {315 const valid = saleRows.filter((s) => s.status === 'valid').sort((a, b) => a.saleDate.getTime() - b.saleDate.getTime());316 if (valid.length < 2) return 0;317 const start = valid[0]!.saleDate.getTime();318 const rows: Array<typeof priceSnapshots.$inferInsert> = [];319 const trust = await trustMap();320 for (let t = start + 7 * DAY; t < now.getTime() - DAY; t += 7 * DAY) {321 const asOf = new Date(t);322 const date = toDateOnly(asOf);323 const upTo = valid.filter((s) => s.saleDate.getTime() <= t);324 // asset-level: representative = variant with most sales up to date325 const counts = new Map<string, number>();326 for (const s of upTo) counts.set(s.variantId ?? '', (counts.get(s.variantId ?? '') ?? 0) + 1);327 for (const vid of [...variantIds, '']) {328 const subset = vid === '' ? upTo.filter((s) => (s.variantId ?? '') === [...counts.entries()].sort((a, b) => b[1] - a[1])[0]?.[0]) : upTo.filter((s) => s.variantId === vid);329 if (subset.length < 2) continue;330 const out = computeValuation({ sales: subset.map((s) => ({ id: s.id, priceUsd: Number(s.priceUsd), date: s.saleDate, trust: trust.get(s.sourceId) ?? 0.6, confidence: Number(s.confidence) })), now: asOf });331 if (out.riv === null) continue;332 const weekSales = subset.filter((s) => t - s.saleDate.getTime() < 7 * DAY);333 rows.push({ assetId, variantId: vid, date, rivUsd: out.riv, latestSaleUsd: subset.at(-1)!.priceUsd, medianUsd: out.distribution.median, salesCount: weekSales.length, volumeUsd: weekSales.reduce((a, s) => a + Number(s.priceUsd), 0) || null, listingsCount: 0, minAskUsd: null, observationUsd: null });334 }335 }336 for (let i = 0; i < rows.length; i += 500) {337 await db()338 .insert(priceSnapshots)339 .values(rows.slice(i, i + 500))340 .onConflictDoUpdate({ target: [priceSnapshots.assetId, priceSnapshots.variantId, priceSnapshots.date], set: { rivUsd: sql`excluded.riv_usd`, medianUsd: sql`excluded.median_usd`, latestSaleUsd: sql`excluded.latest_sale_usd`, salesCount: sql`excluded.sales_count`, volumeUsd: sql`excluded.volume_usd` } });341 }342 return rows.length;343}344345/** Assets whose evidence changed since their last stats update (or all). */346export async function assetsNeedingValuation(opts: { all?: boolean; limit?: number } = {}): Promise<string[]> {347 const limit = opts.limit ?? 5000;348 if (opts.all) {349 const rows = await db().select({ id: assets.id }).from(assets).orderBy(assets.createdAt).limit(limit);350 return rows.map((r) => r.id);351 }352 const rows = await db().execute(sql`353 with touched as (354 select asset_id, max(created_at) as t from sales group by asset_id355 union all select asset_id, max(updated_at) from listings group by asset_id356 union all select asset_id, max(created_at) from price_observations group by asset_id357 ), agg as (select asset_id, max(t) as t from touched group by asset_id)358 select a.asset_id from agg a left join asset_stats s on s.asset_id = a.asset_id359 where s.asset_id is null or s.updated_at < a.t360 limit ${limit}`);361 return (rows as unknown as Array<{ asset_id: string }>).map((r) => r.asset_id);362}363364export async function valueMany(ids: string[], opts: { rebuildHistory?: boolean; concurrency?: number } = {}): Promise<{ valued: number; priced: number }> {365 let valued = 0;366 let priced = 0;367 const conc = opts.concurrency ?? 4;368 for (let i = 0; i < ids.length; i += conc) {369 const chunk = ids.slice(i, i + conc);370 const res = await Promise.all(chunk.map((id) => valueAsset(id, { rebuildHistory: opts.rebuildHistory }).catch((err) => { log.error({ err, id }, 'valueAsset failed'); return null; })));371 for (const r of res) {372 if (!r) continue;373 valued++;374 if (r.riv !== null) priced++;375 }376 if ((i / conc) % 50 === 0 && i > 0) log.info({ done: i, total: ids.length, priced }, 'valuation progress');377 }378 return { valued, priced };379}380381/** Empirical grade premiums per category from sales in the last 2 years (§118). */382export async function computePremiums(categorySlug?: string): Promise<number> {383 const since = new Date(Date.now() - 2 * 365 * DAY);384 const cats = categorySlug ? [categorySlug] : (await db().selectDistinct({ c: assets.categorySlug }).from(assets)).map((r) => r.c);385 let written = 0;386 for (const cat of cats) {387 const rows = await db()388 .select({ assetId: sales.assetId, grader: sales.grader, grade: sales.grade, priceUsd: BUYER_PAYS })389 .from(sales)390 .innerJoin(assets, eq(assets.id, sales.assetId))391 .where(and(eq(assets.categorySlug, cat), eq(sales.status, 'valid'), gte(sales.saleDate, since)));392 const prem = computeGradePremiums(rows.map((r) => ({ assetId: r.assetId, grader: r.grader, grade: r.grade, priceUsd: Number(r.priceUsd) })));393 for (const p of prem) {394 await db()395 .insert(gradePremiums)396 .values({ id: newId('valuation'), categorySlug: cat, grader: p.grader, grade: p.grade, marketMultiplier: p.marketMultiplier, sampleSize: p.sampleSize, computedAt: new Date() })397 .onConflictDoUpdate({ target: [gradePremiums.categorySlug, gradePremiums.grader, gradePremiums.grade], set: { marketMultiplier: p.marketMultiplier, sampleSize: p.sampleSize, computedAt: new Date() } });398 written++;399 }400 premiumCache.delete(cat);401 }402 return written;403}404405export { inArray, isNull, or };406