TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1/**2 * Auction lot assessment job (§33–§35): converts estimates, current bids and hammer prices to USD,3 * adds the house's buyer premium (lots are hammer-basis) and compares the buyer-pays figure with the4 * RIV of the lot's own variant through the same gates as marketplace asks. Writes the result on the5 * lot (all_in_*, bid_vs_riv, estimate_vs_riv, assessment_verdict). Never writes an ungated /6 * anomalous / review discount into bid_vs_riv.7 */8import { sql } from 'drizzle-orm';9import { logger } from '@rareindex/shared';10import { assessLot, feeScheduleFor } from '@rareindex/valuation';11import { db } from '../lib/db.ts';12import { usdRateFor } from '../lib/fx.ts';1314const log = logger.child({ component: 'auctions-assess' });1516interface LotRow {17 id: string;18 asset_id: string | null;19 variant_id: string | null;20 grader: string | null;21 grade: string | null;22 currency: string | null;23 estimate_low: number | null;24 estimate_high: number | null;25 current_bid: number | null;26 hammer_price: number | null;27 bid_count: number | null;28 status: string;29 auction_house: string | null;30 source_id: string;31 // valuation of the lot's own variant (variant_stats) when the variant is known32 v_riv: number | null;33 v_conf: number | null;34 v_n: number | null;35 v_method: string | null;36 // asset-level fallback37 a_riv: number | null;38 a_conf: number | null;39 a_n: number | null;40 a_method: string | null;41 rep_grader: string | null;42 rep_grade: string | null;43}4445export interface AssessResult {46 scanned: number;47 assessed: number;48 verdicts: Record<string, number>;49 fxMissing: number;50}5152const basisOf = (method: string | null): 'transactions' | 'comps' | 'guide' | 'none' | null => {53 const m = method?.split(':')[1];54 return m === 'transactions' || m === 'comps' || m === 'guide' || m === 'none' ? m : null;55};5657export async function assessLots(opts: { limit?: number; all?: boolean; now?: Date } = {}): Promise<AssessResult> {58 const now = opts.now ?? new Date();59 const limit = opts.limit ?? 20_000;60 const res: AssessResult = { scanned: 0, assessed: 0, verdicts: {}, fxMissing: 0 };61 const rows = (await db().execute(sql`62 select l.id, l.asset_id, l.variant_id, l.grader, l.grade, l.currency, l.estimate_low::float as estimate_low, l.estimate_high::float as estimate_high,63 l.current_bid::float as current_bid, l.hammer_price::float as hammer_price, l.bid_count, l.status, au.auction_house, l.source_id,64 vs.riv_usd::float as v_riv, vs.riv_confidence::float as v_conf, vs.riv_sample_size as v_n,65 (select v.method from valuations v where v.variant_id = l.variant_id order by v.computed_at desc limit 1) as v_method,66 st.riv_usd::float as a_riv, st.riv_confidence::float as a_conf, st.riv_sample_size as a_n,67 (select v.method from valuations v where v.variant_id = st.riv_variant_id order by v.computed_at desc limit 1) as a_method,68 rv.grader as rep_grader, rv.grade as rep_grade69 from auction_lots l70 join auctions au on au.id = l.auction_id71 left join variant_stats vs on vs.variant_id = l.variant_id72 left join asset_stats st on st.asset_id = l.asset_id73 left join asset_variants rv on rv.id = st.riv_variant_id74 where l.asset_id is not null75 and ((l.status in ('live','upcoming') and (l.ends_at is null or l.ends_at > now() - interval '1 day')) or (l.status = 'ended' and l.hammer_price is not null))76 ${opts.all ? sql`` : sql`and (l.assessed_at is null or l.assessed_at < l.updated_at or l.assessed_at < now() - interval '6 hours')`}77 order by l.ends_at asc nulls last78 limit ${limit}`)) as unknown as LotRow[];79 res.scanned = rows.length;80 for (const r of rows) {81 const currency = r.currency ?? 'USD';82 const fx = await usdRateFor(currency, now);83 if (!fx || fx.rate <= 0) {84 res.fxMissing++;85 continue;86 }87 const toUsd = (v: number | null) => (v === null || v === undefined ? null : v / fx.rate);88 const schedule = feeScheduleFor(r.auction_house) ?? feeScheduleFor(r.source_id);89 let fxUsdToScheduleCurrency = 1;90 if (schedule && schedule.currency !== 'USD') {91 const sr = await usdRateFor(schedule.currency, now);92 if (sr && sr.rate > 0) fxUsdToScheduleCurrency = sr.rate;93 }94 // Which valuation is comparable: the lot's own variant; else the asset headline only when the95 // lot is raw (no grader) and the representative variant is raw too, or grades match.96 let riv: Parameters<typeof assessLot>[0]['riv'] = null;97 let sameVariant: boolean | undefined = undefined;98 if (r.variant_id && r.v_riv !== null) riv = { rivUsd: r.v_riv, confidence: r.v_conf, sampleSize: r.v_n, basis: basisOf(r.v_method) ?? (Number(r.v_n ?? 0) >= 5 ? 'transactions' : null) };99 else if (r.a_riv !== null) {100 const lotRaw = !r.grader || r.grader === 'raw';101 const repRaw = !r.rep_grader;102 sameVariant = (lotRaw && repRaw) || (!lotRaw && r.grader === r.rep_grader && (r.grade ?? null) === (r.rep_grade ?? null));103 riv = { rivUsd: r.a_riv, confidence: r.a_conf, sampleSize: r.a_n, basis: basisOf(r.a_method) ?? (Number(r.a_n ?? 0) >= 5 ? 'transactions' : null) };104 }105 const estLowUsd = toUsd(r.estimate_low);106 const estHighUsd = toUsd(r.estimate_high);107 const bidUsd = toUsd(r.current_bid);108 const hammerUsd = toUsd(r.hammer_price);109 const a = assessLot({ house: schedule?.id ?? r.auction_house ?? r.source_id, currentBidUsd: bidUsd, bidCount: r.bid_count, estimateLowUsd: estLowUsd, estimateHighUsd: estHighUsd, hammerPriceUsd: hammerUsd, buyerPremiumIncluded: false, fxUsdToScheduleCurrency, riv, sameVariant });110 await db().execute(sql`111 update auction_lots set112 estimate_low_usd = ${estLowUsd}, estimate_high_usd = ${estHighUsd}, current_bid_usd = ${bidUsd}, hammer_price_usd = ${hammerUsd},113 fx_rate = ${fx.rate}, fx_date = ${fx.date}::date,114 buyer_premium_rate = ${a.buyerPremiumRate}, fee_basis = ${a.feeBasis},115 all_in_bid_usd = ${a.allInBidUsd}, all_in_estimate_low_usd = ${a.allInEstimateLowUsd}, all_in_estimate_high_usd = ${a.allInEstimateHighUsd},116 riv_usd_at_assessment = ${a.rivUsd}, bid_vs_riv = ${a.bidVsRiv}, estimate_vs_riv = ${a.estimateVsRiv},117 assessment_verdict = ${a.verdict}, assessed_at = ${now.toISOString()}::timestamptz118 where id = ${r.id}`); // postgres.js needs an ISO string for a Date parameter in raw sql119 res.assessed++;120 res.verdicts[a.verdict] = (res.verdicts[a.verdict] ?? 0) + 1;121 }122 log.info(res, 'auction lots assessed');123 return res;124}125