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 priceUsd: number;36 grader: string | null;37 grade: string | null;38 quantity: number;39 isBundle: boolean;40 status: string;41 confidence: number;42 flags: string[];43}4445/** Value one asset: outliers → per-variant valuations → asset stats, snapshots, listing discounts. */46export async function valueAsset(assetId: string, opts: { now?: Date; rebuildHistory?: boolean } = {}): Promise<{ variants: number; riv: number | null }> {47 const now = opts.now ?? new Date();48 const [asset] = await db().select().from(assets).where(eq(assets.id, assetId)).limit(1);49 if (!asset) return { variants: 0, riv: null };50 const trust = await trustMap();51 const variants = await db().select().from(assetVariants).where(eq(assetVariants.assetId, assetId));52 const since3y = new Date(now.getTime() - 3 * 365 * DAY);53 const saleRows = (await db()54 .select({ id: sales.id, variantId: sales.variantId, sourceId: sales.sourceId, saleDate: sales.saleDate, priceUsd: sales.priceUsd, grader: sales.grader, grade: sales.grade, quantity: sales.quantity, isBundle: sales.isBundle, status: sales.status, confidence: sales.confidence, flags: sales.flags })55 .from(sales)56 .where(and(eq(sales.assetId, assetId), gte(sales.saleDate, since3y)))57 .orderBy(desc(sales.saleDate))) as SaleRow[];58 const allSalesCount = (await db().select({ n: sql<number>`count(*)::int`, min: sql<number>`min(${sales.priceUsd})`, max: sql<number>`max(${sales.priceUsd})`, minAt: sql<Date>`(array_agg(${sales.saleDate} order by ${sales.priceUsd} asc))[1]`, maxAt: sql<Date>`(array_agg(${sales.saleDate} order by ${sales.priceUsd} desc))[1]` }).from(sales).where(and(eq(sales.assetId, assetId), eq(sales.status, 'valid'))))[0]!;5960 // 1. outliers per variant (flag only new ones; never delete)61 const flagsToApply: Array<{ id: string; reason: string; score: number }> = [];62 const byVariant = new Map<string, SaleRow[]>();63 for (const s of saleRows) byVariant.set(s.variantId ?? '', [...(byVariant.get(s.variantId ?? '') ?? []), s]);64 for (const list of byVariant.values()) {65 const valid = list.filter((s) => s.status === 'valid');66 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);67 }68 if (flagsToApply.length) {69 for (const f of flagsToApply) {70 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')));71 const row = saleRows.find((s) => s.id === f.id);72 if (row) row.status = 'flagged';73 }74 await auditMany(flagsToApply.map((f) => ({ entityType: 'sale', entityId: f.id, action: 'flagged', reason: f.reason, details: { modifiedZ: f.score, assetId } })));75 }7677 // 2. observations (guide prices) last 90d per variant78 const obsRows = await db()79 .select({ variantId: priceObservations.variantId, priceUsd: priceObservations.priceUsd, date: priceObservations.observationDate, priceKind: priceObservations.priceKind, sourceId: priceObservations.sourceId })80 .from(priceObservations)81 .where(and(eq(priceObservations.assetId, assetId), gte(priceObservations.observationDate, toDateOnly(new Date(now.getTime() - 90 * DAY)))));82 const obsByVariant = new Map<string, typeof obsRows>();83 for (const o of obsRows) obsByVariant.set(o.variantId ?? '', [...(obsByVariant.get(o.variantId ?? '') ?? []), o]);8485 const premiums = await premiumsFor(asset.categorySlug);86 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'] });8788 // 3. per-variant valuations89 const results: Array<{ variantId: string; isDefault: boolean; out: ReturnType<typeof computeValuation>; salesCount: number; sales30d: number; activeL: { count: number; minAsk: number | null } }> = [];90 const today = toDateOnly(now);91 // Ask-vs-RIV is recomputed from scratch on every pass: a stale discount computed against a92 // previous (possibly wrong-variant) valuation must never survive (§84, §196).93 await db().update(listings).set({ discountToRiv: null }).where(and(eq(listings.assetId, assetId), eq(listings.availability, 'available'), sql`${listings.discountToRiv} is not null`));94 for (const v of variants) {95 const own = (byVariant.get(v.id) ?? []).map(toInput);96 const comps: NonNullable<Parameters<typeof computeValuation>[0]['comps']> = [];97 if (own.filter((s) => (s.status ?? 'valid') === 'valid').length < 3 && premiums.length) {98 for (const other of variants) {99 if (other.id === v.id) continue;100 const adj = adjustmentFactor(premiums, { grader: other.grader, grade: other.grade }, { grader: v.grader, grade: v.grade });101 if (adj === null) continue;102 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) });103 }104 }105 const out = computeValuation({106 sales: own,107 comps,108 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) })),109 now,110 categorySlug: asset.categorySlug,111 });112 const salesCount = own.filter((s) => (s.status ?? 'valid') === 'valid').length;113 const sales30d = own.filter((s) => (s.status ?? 'valid') === 'valid' && now.getTime() - s.date.getTime() <= 30 * DAY).length;114 const activeL = await activeListingStats(assetId, v.id);115 results.push({ variantId: v.id, isDefault: v.isDefault, out, salesCount, sales30d, activeL });116 if (out.riv === null && salesCount === 0 && (obsByVariant.get(v.id) ?? []).length === 0) continue;117 await db().insert(valuations).values({118 id: newId('valuation'),119 assetId,120 variantId: v.id,121 computedAt: now,122 rivUsd: out.riv,123 lowUsd: out.low,124 highUsd: out.high,125 confidence: out.confidence,126 confidenceLabel: out.label,127 sampleSize: out.sampleSize,128 windowDays: out.windowDays,129 methods: out.methods,130 salesUsed: out.salesUsed,131 observationsUsed: out.observationsUsed,132 method: `ensemble_v1:${out.basis}`,133 notes: out.notes,134 });135 const prev30 = await snapshotValue(assetId, v.id, new Date(now.getTime() - 30 * DAY));136 const prev1y = await snapshotValue(assetId, v.id, new Date(now.getTime() - 365 * DAY));137 const latest = own[0];138 await db()139 .insert(variantStats)140 .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 })141 .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 } });142 await db()143 .insert(priceSnapshots)144 .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 })145 .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 } });146 // Ask vs RIV on the active listings of THIS variant only (§84 data-quality gate):147 // – the valuation must rest on ≥ ASK_MIN_SAMPLE transactions with confidence ≥ ASK_MIN_CONFIDENCE148 // (comps-only / guide-only estimates never qualify an ask);149 // – the listing must be confidently matched and carry a readable grade when it is slabbed;150 // – an ask outside [0.1×, 10×] RIV is flagged `riv_anomaly` (identity/data problem), not priced.151 if (out.riv !== null && out.basis === 'transactions' && out.confidence >= ASK_MIN_CONFIDENCE && out.sampleSize >= ASK_MIN_SAMPLE) {152 const lo = out.riv * ASK_ANOMALY_LOW_RATIO;153 const hi = out.riv * ASK_ANOMALY_HIGH_RATIO;154 // Auction lots are excluded: a current/opening bid (often ¥1 on Yahoo! Auctions) is not an asking price.155 // Bid-vs-RIV belongs to auction intelligence with fees (§33–§35), not to the deal rails.156 const scope = and(eq(listings.variantId, v.id), eq(listings.availability, 'available'), sql`${listings.priceUsd} > 0`, sql`${listings.listingType} <> 'auction'`);157 await db()158 .update(listings)159 .set({160 discountToRiv: sql`round((${listings.priceUsd} - ${out.riv}) / ${out.riv}, 4)`,161 // > 50 % below RIV (§174): keep the number, flag `riv_review`, never surface as a deal162 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`,163 })164 .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)`));165 await db()166 .update(listings)167 .set({ discountToRiv: null, flags: sql`case when 'riv_anomaly' = any(${listings.flags}) then ${listings.flags} else array_append(${listings.flags}, 'riv_anomaly') end` })168 .where(and(scope, sql`(${listings.priceUsd} < ${lo} or ${listings.priceUsd} > ${hi})`));169 }170 }171172 // 4. asset-level representative variant (the headline RIV). Preference order:173 // transaction-based valuation with confidence ≥ 0.5 and ≥ 5 sales → the DEFAULT (raw/base) variant174 // when it qualifies, else the qualifying variant with the most sales; fallback: any priced variant175 // by confidence. Rationale: a graded sub-variant with a tighter distribution used to win over the176 // base market and made the headline value (and every ask comparison) wrong by an order of magnitude.177 const priced = results.filter((r) => r.out.riv !== null).sort((a, b) => b.out.confidence - a.out.confidence || b.salesCount - a.salesCount);178 const qualified = priced.filter((r) => r.out.basis === 'transactions' && r.out.confidence >= ASK_MIN_CONFIDENCE && r.out.sampleSize >= ASK_MIN_SAMPLE);179 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;180 const validSales = saleRows.filter((s) => s.status === 'valid');181 const latest = validSales[0];182 // Changes are measured on the representative variant's own series so that a change of183 // representative variant never shows up as a price move.184 const repSeries = rep?.variantId ?? '';185 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)) };186 const riv = rep?.out.riv ?? null;187 const activeAll = await activeListingStats(assetId, null);188 const repActive = rep?.activeL ?? null;189 // ATH / ATL / drawdown belong to the same series as the headline RIV: the representative variant.190 // (An asset-wide ATH mixed a sealed copy's record with a loose copy's valuation → −99 % "drawdowns".)191 const extremes = rep192 ? (await db().select({ n: sql<number>`count(*)::int`, min: sql<number>`min(${sales.priceUsd})`, max: sql<number>`max(${sales.priceUsd})`, minAt: sql<Date>`(array_agg(${sales.saleDate} order by ${sales.priceUsd} asc))[1]`, maxAt: sql<Date>`(array_agg(${sales.saleDate} order by ${sales.priceUsd} 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]!193 : allSalesCount;194 const salesCount = allSalesCount.n;195 const sales30d = validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 30 * DAY).length;196 const sales1y = validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 365 * DAY).length;197 const salesPrev30 = validSales.filter((s) => { const age = now.getTime() - s.saleDate.getTime(); return age > 30 * DAY && age <= 60 * DAY; }).length;198 const pop = await latestPopulation(assetId);199 const [obsCount] = await db().select({ n: sql<number>`count(*)::int` }).from(priceObservations).where(eq(priceObservations.assetId, assetId));200 const sourcesCount = new Set([...saleRows.map((s) => s.sourceId), ...obsRows.map((o) => o.sourceId)]).size;201 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 });202 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 });203 // ratio columns are numeric(8,6): anything ≥ ±99 (9,900 %) would overflow and abort the asset; such a204 // move is never a market signal but a data/identity artefact → stored as null (§143, §196).205 const safeRatio = (v: number | null): number | null => (v === null || !Number.isFinite(v) || Math.abs(v) >= 99 ? null : v);206 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)) };207 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 }) };208 const listingMomentum = activeAll.count > 0 ? Math.min(100, activeAll.count * 5) : null;209 const trending = trendingScore({ priceMomentum: mom.m30, volumeMomentum: momentumScore({ priceChange: null, volumeNow: sales30d, volumePrev: salesPrev30 }), searchMomentum: null, listingMomentum, newsMomentum: null });210 // Best gated ask across the asset's variants (negative = below its own variant's RIV). No fallback:211 // comparing the cheapest ask of ANY variant against the headline RIV was the −8,500 % bug.212 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}))`));213 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 };214 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 });215 const statsRow = {216 assetId,217 rivUsd: riv,218 rivLowUsd: rep?.out.low ?? null,219 rivHighUsd: rep?.out.high ?? null,220 rivConfidence: rep?.out.confidence ?? null,221 rivSampleSize: rep?.out.sampleSize ?? 0,222 rivVariantId: rep?.variantId ?? null,223 latestSaleUsd: latest ? Number(latest.priceUsd) : null,224 latestSaleAt: latest?.saleDate ?? null,225 change1d: ch.d1,226 change7d: ch.d7,227 change30d: ch.d30,228 change90d: ch.d90,229 change1y: ch.y1,230 athUsd: extremes.n ? Number(extremes.max) : null,231 athAt: extremes.n ? asDate(extremes.maxAt) : null,232 atlUsd: extremes.n ? Number(extremes.min) : null,233 atlAt: extremes.n ? asDate(extremes.minAt) : null,234 salesCount,235 sales30d,236 sales1y,237 volume30dUsd: sales30d ? validSales.filter((s) => now.getTime() - s.saleDate.getTime() <= 30 * DAY).reduce((a, s) => a + Number(s.priceUsd), 0) : null,238 activeListings: activeAll.count,239 // lowest ask of the representative variant (comparable to the headline RIV); all variants otherwise240 minAskUsd: repActive?.minAsk ?? (rep ? null : activeAll.minAsk), // never a graded slab's ask next to a raw RIV241 observationsCount: obsCount?.n ?? 0,242 sourcesCount,243 liquidityScore: liq,244 rarityScore: rar,245 momentum7d: mom.m7,246 momentum30d: mom.m30,247 momentum90d: mom.m90,248 momentum1y: mom.y1,249 trendingScore: trending,250 valueOpportunity: bestListing?.d !== null && bestListing?.d !== undefined ? Number(bestListing.d) : null,251 dataQuality: dq,252 updatedAt: now,253 };254 await db().insert(assetStats).values(statsRow).onConflictDoUpdate({ target: assetStats.assetId, set: { ...statsRow, watchers: sql`${assetStats.watchers}`, views30d: sql`${assetStats.views30d}` } });255 await db().update(assets).set({ dataQuality: dq, updatedAt: now }).where(eq(assets.id, assetId));256 await db()257 .insert(priceSnapshots)258 .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 })259 .onConflictDoUpdate({ target: [priceSnapshots.assetId, priceSnapshots.variantId, priceSnapshots.date], set: { rivUsd: riv, latestSaleUsd: latest ? Number(latest.priceUsd) : null, listingsCount: activeAll.count, minAskUsd: activeAll.minAsk } });260 if (opts.rebuildHistory) await rebuildSnapshotHistory(assetId, saleRows, variants.map((v) => v.id), now);261 if (riv !== null) await emit('valuation_updated', { type: 'asset', id: assetId }, { riv, confidence: rep?.out.confidence, sampleSize: rep?.out.sampleSize });262 return { variants: results.length, riv };263}264265async function snapshotValue(assetId: string, variantId: string, at: Date): Promise<number | null> {266 const [row] = await db()267 .select({ v: priceSnapshots.rivUsd })268 .from(priceSnapshots)269 .where(and(eq(priceSnapshots.assetId, assetId), eq(priceSnapshots.variantId, variantId), sql`${priceSnapshots.date} <= ${toDateOnly(at)}`, sql`${priceSnapshots.rivUsd} is not null`))270 .orderBy(desc(priceSnapshots.date))271 .limit(1);272 return row?.v === null || row?.v === undefined ? null : Number(row.v);273}274275async function activeListingStats(assetId: string, variantId: string | null): Promise<{ count: number; minAsk: number | null }> {276 const [row] = await db()277 // count = every live listing; min ask = fixed-price / best-offer / ask only (an auction bid is not an ask)278 .select({ n: sql<number>`count(*)::int`, min: sql<number | null>`min(${listings.priceUsd}) filter (where ${listings.listingType} <> 'auction' and ${listings.priceUsd} > 0)` })279 .from(listings)280 .where(and(eq(listings.assetId, assetId), eq(listings.availability, 'available'), variantId ? eq(listings.variantId, variantId) : undefined));281 return { count: row?.n ?? 0, minAsk: row?.min === null || row?.min === undefined ? null : Number(row.min) };282}283284async function latestPopulation(assetId: string): Promise<{ total: number; growth: number | null } | null> {285 const rows = await db().select({ total: populationReports.total, date: populationReports.reportDate }).from(populationReports).where(eq(populationReports.assetId, assetId)).orderBy(desc(populationReports.reportDate)).limit(2);286 if (!rows.length) return null;287 const growth = rows.length === 2 && rows[1]!.total > 0 ? (rows[0]!.total - rows[1]!.total) / rows[1]!.total : null;288 return { total: rows[0]!.total, growth };289}290291function medianGapDays(dates: Date[]): number | null {292 if (dates.length < 2) return null;293 const s = [...dates].sort((a, b) => a.getTime() - b.getTime());294 const gaps: number[] = [];295 for (let i = 1; i < s.length; i++) gaps.push((s[i]!.getTime() - s[i - 1]!.getTime()) / DAY);296 return median(gaps);297}298299/**300 * Historical snapshots (weekly) reconstructed from transactions only, computing the valuation301 * as of each week with the sales known up to that date. Gives asset charts and index history a302 * past without inventing observations.303 */304export async function rebuildSnapshotHistory(assetId: string, saleRows: SaleRow[], variantIds: string[], now: Date): Promise<number> {305 const valid = saleRows.filter((s) => s.status === 'valid').sort((a, b) => a.saleDate.getTime() - b.saleDate.getTime());306 if (valid.length < 2) return 0;307 const start = valid[0]!.saleDate.getTime();308 const rows: Array<typeof priceSnapshots.$inferInsert> = [];309 const trust = await trustMap();310 for (let t = start + 7 * DAY; t < now.getTime() - DAY; t += 7 * DAY) {311 const asOf = new Date(t);312 const date = toDateOnly(asOf);313 const upTo = valid.filter((s) => s.saleDate.getTime() <= t);314 // asset-level: representative = variant with most sales up to date315 const counts = new Map<string, number>();316 for (const s of upTo) counts.set(s.variantId ?? '', (counts.get(s.variantId ?? '') ?? 0) + 1);317 for (const vid of [...variantIds, '']) {318 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);319 if (subset.length < 2) continue;320 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 });321 if (out.riv === null) continue;322 const weekSales = subset.filter((s) => t - s.saleDate.getTime() < 7 * DAY);323 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 });324 }325 }326 for (let i = 0; i < rows.length; i += 500) {327 await db()328 .insert(priceSnapshots)329 .values(rows.slice(i, i + 500))330 .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` } });331 }332 return rows.length;333}334335/** Assets whose evidence changed since their last stats update (or all). */336export async function assetsNeedingValuation(opts: { all?: boolean; limit?: number } = {}): Promise<string[]> {337 const limit = opts.limit ?? 5000;338 if (opts.all) {339 const rows = await db().select({ id: assets.id }).from(assets).orderBy(assets.createdAt).limit(limit);340 return rows.map((r) => r.id);341 }342 const rows = await db().execute(sql`343 with touched as (344 select asset_id, max(created_at) as t from sales group by asset_id345 union all select asset_id, max(updated_at) from listings group by asset_id346 union all select asset_id, max(created_at) from price_observations group by asset_id347 ), agg as (select asset_id, max(t) as t from touched group by asset_id)348 select a.asset_id from agg a left join asset_stats s on s.asset_id = a.asset_id349 where s.asset_id is null or s.updated_at < a.t350 limit ${limit}`);351 return (rows as unknown as Array<{ asset_id: string }>).map((r) => r.asset_id);352}353354export async function valueMany(ids: string[], opts: { rebuildHistory?: boolean; concurrency?: number } = {}): Promise<{ valued: number; priced: number }> {355 let valued = 0;356 let priced = 0;357 const conc = opts.concurrency ?? 4;358 for (let i = 0; i < ids.length; i += conc) {359 const chunk = ids.slice(i, i + conc);360 const res = await Promise.all(chunk.map((id) => valueAsset(id, { rebuildHistory: opts.rebuildHistory }).catch((err) => { log.error({ err, id }, 'valueAsset failed'); return null; })));361 for (const r of res) {362 if (!r) continue;363 valued++;364 if (r.riv !== null) priced++;365 }366 if ((i / conc) % 50 === 0 && i > 0) log.info({ done: i, total: ids.length, priced }, 'valuation progress');367 }368 return { valued, priced };369}370371/** Empirical grade premiums per category from sales in the last 2 years (§118). */372export async function computePremiums(categorySlug?: string): Promise<number> {373 const since = new Date(Date.now() - 2 * 365 * DAY);374 const cats = categorySlug ? [categorySlug] : (await db().selectDistinct({ c: assets.categorySlug }).from(assets)).map((r) => r.c);375 let written = 0;376 for (const cat of cats) {377 const rows = await db()378 .select({ assetId: sales.assetId, grader: sales.grader, grade: sales.grade, priceUsd: sales.priceUsd })379 .from(sales)380 .innerJoin(assets, eq(assets.id, sales.assetId))381 .where(and(eq(assets.categorySlug, cat), eq(sales.status, 'valid'), gte(sales.saleDate, since)));382 const prem = computeGradePremiums(rows.map((r) => ({ assetId: r.assetId, grader: r.grader, grade: r.grade, priceUsd: Number(r.priceUsd) })));383 for (const p of prem) {384 await db()385 .insert(gradePremiums)386 .values({ id: newId('valuation'), categorySlug: cat, grader: p.grader, grade: p.grade, marketMultiplier: p.marketMultiplier, sampleSize: p.sampleSize, computedAt: new Date() })387 .onConflictDoUpdate({ target: [gradePremiums.categorySlug, gradePremiums.grader, gradePremiums.grade], set: { marketMultiplier: p.marketMultiplier, sampleSize: p.sampleSize, computedAt: new Date() } });388 written++;389 }390 premiumCache.delete(cat);391 }392 return written;393}394395export { inArray, isNull, or };396