1import { and, eq, sql } from 'drizzle-orm';2import { auctionLots, auctions, images, listingEvents, listings, news, populationReports, priceObservations, sales, sources } from '@rareindex/database';3import { normalizeCondition } from '@rareindex/taxonomy';4import { newId, sha256, toDateOnly, type NormalizedAuctionLot, type NormalizedCatalogItem, type NormalizedListing, type NormalizedNewsItem, type NormalizedPopulationReport, type NormalizedPriceObservation, type NormalizedRecord, type NormalizedSale } from '@rareindex/shared';5import { db } from '../lib/db.ts';6import { toUsd } from '../lib/fx.ts';7import { audit } from '../lib/audit.ts';8import { enrichAttributesFromTitle, ensureVariant, resolveAsset, type Resolution } from './resolver.ts';910export class FxMissingError extends Error {11 constructor(public currency: string, public date: Date) {12 super(`fx_missing:${currency}:${toDateOnly(date)}`);13 }14}1516export interface ApplyResult {17 assetId: string | null;18 variantId: string | null;19 targetId: string | null;20 method: Resolution['method'] | null;21 confidence: number | null;22 status: 'applied' | 'unmatched' | 'rejected' | 'duplicate';23 reason?: string;24 event?: 'sale_detected' | 'listing_created' | 'listing_updated' | 'entity_created';25}2627const trustCache = new Map<string, number>();28async function sourceTrust(sourceId: string): Promise<number> {29 const hit = trustCache.get(sourceId);30 if (hit !== undefined) return hit;31 const [row] = await db().select({ t: sources.trustScore }).from(sources).where(eq(sources.id, sourceId)).limit(1);32 const t = row ? Number(row.t) : 0.5;33 trustCache.set(sourceId, t);34 return t;35}3637/** Data quality 0–100 (§150): completeness × source trust × identification confidence. */38function dataQuality(fields: Record<string, unknown>, trust: number, confidence: number, extra = 0): number {39 const keys = Object.keys(fields);40 const present = keys.filter((k) => fields[k] !== null && fields[k] !== undefined && fields[k] !== '' && !(Array.isArray(fields[k]) && (fields[k] as unknown[]).length === 0)).length;41 const completeness = keys.length ? present / keys.length : 0;42 return Math.round(Math.max(0, Math.min(100, (0.4 * completeness + 0.3 * trust + 0.3 * confidence + extra) * 100)));43}4445function normCond(categorySlug: string, raw: string | null | undefined, already: string | null | undefined): string | null {46 return already ?? normalizeCondition(categorySlug, raw);47}4849async function storeImages(assetId: string, urls: string[], sourceId: string, ref: { listingId?: string; saleId?: string }): Promise<void> {50 if (!urls.length) return;51 const rows = urls.slice(0, 12).map((url, i) => ({ id: newId('image'), assetId, listingId: ref.listingId ?? null, saleId: ref.saleId ?? null, sourceId, url, role: i === 0 && !ref.listingId && !ref.saleId ? 'hero' : 'source' }));52 await db().insert(images).values(rows).onConflictDoNothing({ target: images.url });53}5455export async function applyRecord(rec: NormalizedRecord): Promise<ApplyResult> {56 switch (rec.kind) {57 case 'catalog_item':58 return applyCatalog(rec);59 case 'sale':60 return applySale(rec);61 case 'listing':62 return applyListing(rec);63 case 'price_observation':64 return applyObservation(rec);65 case 'auction_lot':66 return applyAuctionLot(rec);67 case 'population_report':68 return applyPopulation(rec);69 case 'news_item':70 return applyNews(rec);71 }72}7374async function applyCatalog(rec: NormalizedCatalogItem): Promise<ApplyResult> {75 const r = await resolveAsset(rec.attributes, { enrich: true, imageUrls: rec.imageUrls, description: rec.description });76 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };77 if (rec.imageUrls.length) await storeImages(r.assetId, rec.imageUrls.slice(0, 1), rec.sourceId, {});78 return { assetId: r.assetId, variantId: null, targetId: r.assetId, method: r.method, confidence: r.confidence, status: 'applied', event: r.created ? 'entity_created' : undefined };79}8081async function applySale(rec: NormalizedSale): Promise<ApplyResult> {82 const r = await resolveAsset(enrichAttributesFromTitle(rec.attributes, rec.rawTitle), { imageUrls: rec.imageUrls });83 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };84 const condition = normCond(rec.attributes.categorySlug, rec.condition.conditionRaw, rec.condition.condition);85 const variant = await ensureVariant(r.assetId, rec.grade, { condition, completeness: rec.condition.completeness }, rec.attributes.size);86 const fx = await toUsd(rec.price, rec.currency, rec.saleDate);87 if (!fx) throw new FxMissingError(rec.currency, rec.saleDate);88 const dedupeKey = sha256(['sale', rec.sourceId, rec.externalId ?? rec.sourceUrl, toDateOnly(rec.saleDate), rec.price.toFixed(2)].join('|'));89 const trust = await sourceTrust(rec.sourceId);90 const confidence = Math.min(rec.confidence, r.confidence);91 const flags: string[] = [];92 if (rec.isBundle || rec.quantity > 1) flags.push('bundle');93 if (rec.price <= 0) flags.push('zero_price');94 if (confidence < 0.6) flags.push('low_identification_confidence');95 const status = flags.includes('bundle') || flags.includes('zero_price') ? 'excluded' : 'valid';96 const id = newId('sale');97 const inserted = await db()98 .insert(sales)99 .values({100 id,101 assetId: r.assetId,102 variantId: variant.id,103 sourceId: rec.sourceId,104 connectorId: rec.connectorId,105 sourceUrl: rec.sourceUrl,106 externalId: rec.externalId,107 saleType: rec.saleType,108 saleDate: rec.saleDate,109 price: rec.price,110 currency: rec.currency,111 priceUsd: fx.usd,112 fxRate: fx.rate,113 fxDate: fx.fxDate,114 buyerPremiumIncluded: rec.buyerPremiumIncluded,115 quantity: rec.quantity,116 isBundle: rec.isBundle,117 condition,118 grader: rec.grade.grader,119 grade: rec.grade.grade,120 certificationNumber: rec.grade.certificationNumber,121 location: rec.location,122 auctionHouse: rec.auctionHouse,123 lotNumber: rec.lotNumber,124 imageUrls: rec.imageUrls,125 rawTitle: rec.rawTitle,126 confidence,127 dataQuality: dataQuality({ date: rec.saleDate, price: rec.price, currency: rec.currency, images: rec.imageUrls, grade: rec.grade.grade, condition, externalId: rec.externalId, type: rec.saleType === 'unknown' ? null : rec.saleType }, trust, confidence),128 status,129 flags,130 dedupeKey,131 })132 .onConflictDoNothing({ target: sales.dedupeKey })133 .returning({ id: sales.id });134 if (inserted.length === 0) return { assetId: r.assetId, variantId: variant.id, targetId: null, method: r.method, confidence, status: 'duplicate' };135 if (status === 'excluded') await audit({ entityType: 'sale', entityId: id, action: 'excluded', reason: flags.join(','), details: { rawTitle: rec.rawTitle, quantity: rec.quantity } });136 if (rec.imageUrls.length) await storeImages(r.assetId, rec.imageUrls.slice(0, 3), rec.sourceId, { saleId: id });137 return { assetId: r.assetId, variantId: variant.id, targetId: id, method: r.method, confidence, status: 'applied', event: 'sale_detected' };138}139140async function applyListing(rec: NormalizedListing): Promise<ApplyResult> {141 const r = await resolveAsset(enrichAttributesFromTitle(rec.attributes, rec.rawTitle), { imageUrls: rec.imageUrls });142 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };143 const condition = normCond(rec.attributes.categorySlug, rec.condition.conditionRaw, rec.condition.condition);144 const variant = await ensureVariant(r.assetId, rec.grade, { condition, completeness: rec.condition.completeness }, rec.attributes.size);145 let priceUsd: number | null = null;146 if (rec.price !== null && rec.currency) {147 const fx = await toUsd(rec.price, rec.currency, rec.observedAt);148 if (!fx) throw new FxMissingError(rec.currency, rec.observedAt);149 priceUsd = fx.usd;150 }151 const externalId = rec.externalId ?? sha256(rec.sourceUrl);152 const trust = await sourceTrust(rec.sourceId);153 const confidence = Math.min(rec.confidence, r.confidence);154 const now = rec.observedAt;155 const [existing] = await db().select({ id: listings.id, price: listings.price, availability: listings.availability }).from(listings).where(and(eq(listings.sourceId, rec.sourceId), eq(listings.externalId, externalId))).limit(1);156 const quality = dataQuality({ price: rec.price, currency: rec.currency, images: rec.imageUrls, seller: rec.seller, condition, grade: rec.grade.grade, listedAt: rec.listedAt, description: rec.description }, trust, confidence);157 if (!existing) {158 const id = newId('listing');159 await db().insert(listings).values({160 id,161 assetId: r.assetId,162 variantId: variant.id,163 sourceId: rec.sourceId,164 connectorId: rec.connectorId,165 sourceUrl: rec.sourceUrl,166 externalId,167 listingType: rec.listingType,168 price: rec.price,169 currency: rec.currency,170 priceUsd,171 seller: rec.seller,172 sellerReputation: rec.sellerReputation,173 location: rec.location,174 shippingCost: rec.shippingCost,175 quantity: rec.quantity,176 condition,177 grader: rec.grade.grader,178 grade: rec.grade.grade,179 certificationNumber: rec.grade.certificationNumber,180 imageUrls: rec.imageUrls,181 rawTitle: rec.rawTitle,182 description: rec.description,183 listedAt: rec.listedAt,184 endsAt: rec.endsAt,185 availability: rec.availability,186 bidCount: rec.bidCount,187 firstSeenAt: now,188 lastSeenAt: now,189 confidence,190 dataQuality: quality,191 flags: confidence < 0.6 ? ['low_identification_confidence'] : [],192 });193 await db().insert(listingEvents).values({ id: newId('event'), listingId: id, eventType: 'new', newPrice: rec.price, currency: rec.currency, occurredAt: now });194 if (rec.imageUrls.length) await storeImages(r.assetId, rec.imageUrls.slice(0, 3), rec.sourceId, { listingId: id });195 return { assetId: r.assetId, variantId: variant.id, targetId: id, method: r.method, confidence, status: 'applied', event: 'listing_created' };196 }197 const priceChanged = rec.price !== null && existing.price !== null && Math.abs(Number(existing.price) - rec.price) > 0.009;198 const relisted = existing.availability !== 'available' && rec.availability === 'available';199 const ended = existing.availability === 'available' && rec.availability !== 'available';200 await db()201 .update(listings)202 .set({203 price: rec.price,204 currency: rec.currency,205 priceUsd,206 availability: rec.availability,207 bidCount: rec.bidCount,208 endsAt: rec.endsAt ?? sql`${listings.endsAt}`,209 lastSeenAt: now,210 priceChangedAt: priceChanged ? now : sql`${listings.priceChangedAt}`,211 quantity: rec.quantity,212 dataQuality: quality,213 updatedAt: new Date(),214 })215 .where(eq(listings.id, existing.id));216 const ev = priceChanged ? 'price_changed' : relisted ? 'relisted' : ended ? (rec.availability === 'sold' ? 'sold' : rec.availability === 'ended' ? 'auction_ended' : 'removed') : null;217 if (ev) await db().insert(listingEvents).values({ id: newId('event'), listingId: existing.id, eventType: ev, oldPrice: existing.price, newPrice: rec.price, currency: rec.currency, occurredAt: now });218 return { assetId: r.assetId, variantId: variant.id, targetId: existing.id, method: r.method, confidence, status: 'applied', event: ev ? 'listing_updated' : undefined };219}220221async function applyObservation(rec: NormalizedPriceObservation): Promise<ApplyResult> {222 const r = await resolveAsset(rec.attributes, { imageUrls: rec.imageUrls });223 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };224 const condition = normCond(rec.attributes.categorySlug, rec.condition.conditionRaw, rec.condition.condition);225 const variant = await ensureVariant(r.assetId, rec.grade, { condition, completeness: rec.condition.completeness }, rec.attributes.size);226 const fx = await toUsd(rec.price, rec.currency, rec.observationDate);227 if (!fx) throw new FxMissingError(rec.currency, rec.observationDate);228 const dedupeKey = sha256(['obs', rec.sourceId, rec.externalId ?? rec.sourceUrl, rec.priceKind, variant.key, toDateOnly(rec.observationDate)].join('|'));229 const id = newId('valuation');230 const inserted = await db()231 .insert(priceObservations)232 .values({ id, assetId: r.assetId, variantId: variant.id, sourceId: rec.sourceId, connectorId: rec.connectorId, sourceUrl: rec.sourceUrl, priceKind: rec.priceKind, price: rec.price, currency: rec.currency, priceUsd: fx.usd, observationDate: toDateOnly(rec.observationDate), sampleSize: rec.sampleSize, dedupeKey })233 .onConflictDoNothing({ target: priceObservations.dedupeKey })234 .returning({ id: priceObservations.id });235 if (inserted.length === 0) return { assetId: r.assetId, variantId: variant.id, targetId: null, method: r.method, confidence: r.confidence, status: 'duplicate' };236 return { assetId: r.assetId, variantId: variant.id, targetId: id, method: r.method, confidence: r.confidence, status: 'applied' };237}238239async function applyAuctionLot(rec: NormalizedAuctionLot): Promise<ApplyResult> {240 const r = await resolveAsset(enrichAttributesFromTitle(rec.attributes, rec.rawTitle), { imageUrls: rec.imageUrls, allowCreate: rec.confidence >= 0.7 });241 const auctionUrl = (rec.attributes.metadata?.auctionUrl as string | undefined) ?? `${new URL(rec.sourceUrl).origin}#${rec.auctionHouse}:${rec.auctionName ?? 'auction'}`;242 const auctionId = sha256(auctionUrl).slice(0, 24);243 await db()244 .insert(auctions)245 .values({ id: `auc_${auctionId}`, sourceId: rec.sourceId, auctionHouse: rec.auctionHouse, name: rec.auctionName ?? rec.auctionHouse, url: auctionUrl, startsAt: rec.startsAt, endsAt: rec.endsAt, location: rec.location, categorySlugs: [rec.attributes.categorySlug], status: rec.status === 'unknown' ? 'upcoming' : rec.status, currency: rec.currency })246 .onConflictDoUpdate({ target: auctions.url, set: { endsAt: sql`coalesce(excluded.ends_at, ${auctions.endsAt})`, status: sql`excluded.status`, updatedAt: new Date() } });247 let variantId: string | null = null;248 if (r) variantId = (await ensureVariant(r.assetId, rec.grade, { condition: normCond(rec.attributes.categorySlug, rec.condition.conditionRaw, rec.condition.condition), completeness: rec.condition.completeness }, rec.attributes.size)).id;249 const id = `lot_${sha256(rec.sourceUrl).slice(0, 24)}`;250 await db()251 .insert(auctionLots)252 .values({ id, auctionId: `auc_${auctionId}`, assetId: r?.assetId ?? null, variantId, sourceId: rec.sourceId, lotNumber: rec.lotNumber, title: rec.rawTitle, url: rec.sourceUrl, estimateLow: rec.estimateLow, estimateHigh: rec.estimateHigh, currentBid: rec.currentBid, currency: rec.currency, startsAt: rec.startsAt, endsAt: rec.endsAt, status: rec.status === 'unknown' ? 'upcoming' : rec.status, imageUrls: rec.imageUrls, grader: rec.grade.grader, grade: rec.grade.grade })253 .onConflictDoUpdate({ target: auctionLots.url, set: { currentBid: sql`excluded.current_bid`, status: sql`excluded.status`, endsAt: sql`coalesce(excluded.ends_at, ${auctionLots.endsAt})`, assetId: sql`coalesce(${auctionLots.assetId}, excluded.asset_id)`, updatedAt: new Date() } });254 return { assetId: r?.assetId ?? null, variantId, targetId: id, method: r?.method ?? null, confidence: r?.confidence ?? null, status: r ? 'applied' : 'unmatched' };255}256257async function applyPopulation(rec: NormalizedPopulationReport): Promise<ApplyResult> {258 const r = await resolveAsset(rec.attributes, {});259 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };260 const id = newId('event');261 const inserted = await db()262 .insert(populationReports)263 .values({ id, assetId: r.assetId, grader: rec.grader, sourceId: rec.sourceId, sourceUrl: rec.sourceUrl, reportDate: toDateOnly(rec.reportDate), total: rec.total, byGrade: rec.byGrade })264 .onConflictDoNothing()265 .returning({ id: populationReports.id });266 return { assetId: r.assetId, variantId: null, targetId: inserted[0]?.id ?? null, method: r.method, confidence: r.confidence, status: inserted.length ? 'applied' : 'duplicate' };267}268269async function applyNews(rec: NormalizedNewsItem): Promise<ApplyResult> {270 const id = newId('news');271 const inserted = await db()272 .insert(news)273 .values({ id, sourceId: rec.sourceId, url: rec.sourceUrl, title: rec.title, summary: rec.summary, publishedAt: rec.publishedAt, categorySlugs: rec.categorySlugs, imageUrl: rec.imageUrl, fetchedAt: new Date() })274 .onConflictDoNothing({ target: news.url })275 .returning({ id: news.id });276 return { assetId: null, variantId: null, targetId: inserted[0]?.id ?? null, method: null, confidence: null, status: inserted.length ? 'applied' : 'duplicate' };277}278