1import { and, eq, sql } from 'drizzle-orm';2import { auctionLots, auctions, images, listingEvents, listings, news, populationReports, priceObservations, sales, sources } from '@rareindex/database';3import { normalizeCondition, parseGradeFromTitle } from '@rareindex/taxonomy';4import type { Grade } from '@rareindex/shared';5import { newId, sha256, toDateOnly, type NormalizedAuctionLot, type NormalizedCatalogItem, type NormalizedListing, type NormalizedNewsItem, type NormalizedPopulationReport, type NormalizedPriceObservation, type NormalizedRecord, type NormalizedSale } from '@rareindex/shared';6import { db } from '../lib/db.ts';7import { toUsd } from '../lib/fx.ts';8import { audit } from '../lib/audit.ts';9import { enrichAttributesFromTitle, ensureVariant, resolveAsset, type Resolution } from './resolver.ts';10import { recordCertificate } from './certificates.ts';1112/** Record a slab certification sighting when a record carries one (SPEC §22); never throws into the writer. */13async function trackCert(rec: { grade: { grader: string | null; grade: string | null; qualifier: string | null; certificationNumber: string | null }; sourceId: string; connectorId: string; sourceUrl: string; rawTitle?: string | null }, kind: 'sale' | 'listing' | 'auction_lot', targetId: string | null, assetId: string | null, variantId: string | null, priceUsd: number | null, price: number | null, currency: string | null, observedAt: Date): Promise<void> {14 const g = refineGrade(rec.grade, rec.rawTitle);15 if (!g.certificationNumber || !g.grader) return;16 try {17 await recordCertificate({ grader: g.grader, certNumber: g.certificationNumber, assetId, variantId, grade: g.grade, qualifier: g.qualifier, kind, targetId, sourceId: rec.sourceId, connectorId: rec.connectorId, sourceUrl: rec.sourceUrl, priceUsd, price, currency, observedAt });18 } catch {19 /* cert tracking is best-effort; the canonical write already succeeded */20 }21}2223export class FxMissingError extends Error {24 constructor(public currency: string, public date: Date) {25 super(`fx_missing:${currency}:${toDateOnly(date)}`);26 }27}2829export interface ApplyResult {30 assetId: string | null;31 variantId: string | null;32 targetId: string | null;33 method: Resolution['method'] | null;34 confidence: number | null;35 status: 'applied' | 'unmatched' | 'rejected' | 'duplicate';36 reason?: string;37 event?: 'sale_detected' | 'listing_created' | 'listing_updated' | 'entity_created';38}3940const trustCache = new Map<string, number>();41async function sourceTrust(sourceId: string): Promise<number> {42 const hit = trustCache.get(sourceId);43 if (hit !== undefined) return hit;44 const [row] = await db().select({ t: sources.trustScore }).from(sources).where(eq(sources.id, sourceId)).limit(1);45 const t = row ? Number(row.t) : 0.5;46 trustCache.set(sourceId, t);47 return t;48}4950/** Data quality 0–100 (§150): completeness × source trust × identification confidence. */51function dataQuality(fields: Record<string, unknown>, trust: number, confidence: number, extra = 0): number {52 const keys = Object.keys(fields);53 const present = keys.filter((k) => fields[k] !== null && fields[k] !== undefined && fields[k] !== '' && !(Array.isArray(fields[k]) && (fields[k] as unknown[]).length === 0)).length;54 const completeness = keys.length ? present / keys.length : 0;55 return Math.round(Math.max(0, Math.min(100, (0.4 * completeness + 0.3 * trust + 0.3 * confidence + extra) * 100)));56}5758function normCond(categorySlug: string, raw: string | null | undefined, already: string | null | undefined): string | null {59 return already ?? normalizeCondition(categorySlug, raw);60}6162async function storeImages(assetId: string, urls: string[], sourceId: string, ref: { listingId?: string; saleId?: string }): Promise<void> {63 if (!urls.length) return;64 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' }));65 await db().insert(images).values(rows).onConflictDoNothing({ target: images.url });66}6768export async function applyRecord(rec: NormalizedRecord): Promise<ApplyResult> {69 switch (rec.kind) {70 case 'catalog_item':71 return applyCatalog(rec);72 case 'sale':73 return applySale(rec);74 case 'listing':75 return applyListing(rec);76 case 'price_observation':77 return applyObservation(rec);78 case 'auction_lot':79 return applyAuctionLot(rec);80 case 'population_report':81 return applyPopulation(rec);82 case 'news_item':83 return applyNews(rec);84 }85}8687/**88 * Grade refinement (§18, §84): when a connector did not read the slab (or read the grader but not the89 * grade), parse the raw title. A grader with no readable grade stays "grader · grade unknown" — it must90 * never fall into the raw/ungraded variant, which is what compared PSA slabs against raw valuations.91 * An explicit `raw` from the connector is trusted over the title.92 */93export function refineGrade(grade: Grade, rawTitle: string | null | undefined): Grade {94 if (!rawTitle || grade.grader === 'raw' || (grade.grader && grade.grade)) return grade;95 const parsed = parseGradeFromTitle(rawTitle);96 if (!parsed.grader || parsed.grader === 'raw') return grade;97 if (grade.grader && parsed.grader !== grade.grader) return grade; // never contradict the connector's grader98 // A grade recorded without a grader is a condition-scale value (e.g. a price-guide tab), not a slab99 // grade: when the title reveals the slab, the title's grade wins.100 const g = grade.grader ? (grade.grade ?? parsed.grade) : parsed.grade;101 return { ...grade, grader: grade.grader ?? parsed.grader, grade: g, qualifier: grade.qualifier ?? parsed.qualifier };102}103104async function applyCatalog(rec: NormalizedCatalogItem): Promise<ApplyResult> {105 const r = await resolveAsset(rec.attributes, { enrich: true, imageUrls: rec.imageUrls, description: rec.description });106 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };107 if (rec.imageUrls.length) await storeImages(r.assetId, rec.imageUrls.slice(0, 1), rec.sourceId, {});108 return { assetId: r.assetId, variantId: null, targetId: r.assetId, method: r.method, confidence: r.confidence, status: 'applied', event: r.created ? 'entity_created' : undefined };109}110111async function applySale(rec: NormalizedSale): Promise<ApplyResult> {112 const grade = refineGrade(rec.grade, rec.rawTitle);113 const r = await resolveAsset(enrichAttributesFromTitle(rec.attributes, rec.rawTitle), { imageUrls: rec.imageUrls });114 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };115 const condition = normCond(rec.attributes.categorySlug, rec.condition.conditionRaw, rec.condition.condition);116 const variant = await ensureVariant(r.assetId, grade, { condition, completeness: rec.condition.completeness }, rec.attributes.size);117 const fx = await toUsd(rec.price, rec.currency, rec.saleDate);118 if (!fx) throw new FxMissingError(rec.currency, rec.saleDate);119 const dedupeKey = sha256(['sale', rec.sourceId, rec.externalId ?? rec.sourceUrl, toDateOnly(rec.saleDate), rec.price.toFixed(2)].join('|'));120 const trust = await sourceTrust(rec.sourceId);121 const confidence = Math.min(rec.confidence, r.confidence);122 const flags: string[] = [];123 if (rec.isBundle || rec.quantity > 1) flags.push('bundle');124 if (rec.price <= 0) flags.push('zero_price');125 if (confidence < 0.6) flags.push('low_identification_confidence');126 const status = flags.includes('bundle') || flags.includes('zero_price') ? 'excluded' : 'valid';127 const id = newId('sale');128 const inserted = await db()129 .insert(sales)130 .values({131 id,132 assetId: r.assetId,133 variantId: variant.id,134 sourceId: rec.sourceId,135 connectorId: rec.connectorId,136 sourceUrl: rec.sourceUrl,137 externalId: rec.externalId,138 saleType: rec.saleType,139 saleDate: rec.saleDate,140 price: rec.price,141 currency: rec.currency,142 priceUsd: fx.usd,143 fxRate: fx.rate,144 fxDate: fx.fxDate,145 buyerPremiumIncluded: rec.buyerPremiumIncluded,146 quantity: rec.quantity,147 isBundle: rec.isBundle,148 condition,149 grader: grade.grader,150 grade: grade.grade,151 certificationNumber: grade.certificationNumber,152 location: rec.location,153 auctionHouse: rec.auctionHouse,154 lotNumber: rec.lotNumber,155 imageUrls: rec.imageUrls,156 rawTitle: rec.rawTitle,157 confidence,158 dataQuality: dataQuality({ date: rec.saleDate, price: rec.price, currency: rec.currency, images: rec.imageUrls, grade: grade.grade, condition, externalId: rec.externalId, type: rec.saleType === 'unknown' ? null : rec.saleType }, trust, confidence),159 status,160 flags,161 dedupeKey,162 })163 .onConflictDoNothing({ target: sales.dedupeKey })164 .returning({ id: sales.id });165 if (inserted.length === 0) return { assetId: r.assetId, variantId: variant.id, targetId: null, method: r.method, confidence, status: 'duplicate' };166 if (status === 'excluded') await audit({ entityType: 'sale', entityId: id, action: 'excluded', reason: flags.join(','), details: { rawTitle: rec.rawTitle, quantity: rec.quantity } });167 if (rec.imageUrls.length) await storeImages(r.assetId, rec.imageUrls.slice(0, 3), rec.sourceId, { saleId: id });168 await trackCert(rec, 'sale', id, r.assetId, variant.id, fx.usd, rec.price, rec.currency, rec.saleDate);169 return { assetId: r.assetId, variantId: variant.id, targetId: id, method: r.method, confidence, status: 'applied', event: 'sale_detected' };170}171172async function applyListing(rec: NormalizedListing): Promise<ApplyResult> {173 const grade = refineGrade(rec.grade, rec.rawTitle);174 const r = await resolveAsset(enrichAttributesFromTitle(rec.attributes, rec.rawTitle), { imageUrls: rec.imageUrls });175 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };176 const condition = normCond(rec.attributes.categorySlug, rec.condition.conditionRaw, rec.condition.condition);177 const variant = await ensureVariant(r.assetId, grade, { condition, completeness: rec.condition.completeness }, rec.attributes.size);178 let priceUsd: number | null = null;179 if (rec.price !== null && rec.currency) {180 const fx = await toUsd(rec.price, rec.currency, rec.observedAt);181 if (!fx) throw new FxMissingError(rec.currency, rec.observedAt);182 priceUsd = fx.usd;183 }184 const externalId = rec.externalId ?? sha256(rec.sourceUrl);185 const trust = await sourceTrust(rec.sourceId);186 const confidence = Math.min(rec.confidence, r.confidence);187 const now = rec.observedAt;188 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);189 const quality = dataQuality({ price: rec.price, currency: rec.currency, images: rec.imageUrls, seller: rec.seller, condition, grade: grade.grade, listedAt: rec.listedAt, description: rec.description }, trust, confidence);190 if (!existing) {191 const id = newId('listing');192 await db().insert(listings).values({193 id,194 assetId: r.assetId,195 variantId: variant.id,196 sourceId: rec.sourceId,197 connectorId: rec.connectorId,198 sourceUrl: rec.sourceUrl,199 externalId,200 listingType: rec.listingType,201 price: rec.price,202 currency: rec.currency,203 priceUsd,204 seller: rec.seller,205 sellerReputation: rec.sellerReputation,206 location: rec.location,207 shippingCost: rec.shippingCost,208 quantity: rec.quantity,209 condition,210 grader: grade.grader,211 grade: grade.grade,212 certificationNumber: grade.certificationNumber,213 imageUrls: rec.imageUrls,214 rawTitle: rec.rawTitle,215 description: rec.description,216 listedAt: rec.listedAt,217 endsAt: rec.endsAt,218 availability: rec.availability,219 bidCount: rec.bidCount,220 firstSeenAt: now,221 lastSeenAt: now,222 confidence,223 dataQuality: quality,224 flags: confidence < 0.6 ? ['low_identification_confidence'] : [],225 });226 await db().insert(listingEvents).values({ id: newId('event'), listingId: id, eventType: 'new', newPrice: rec.price, currency: rec.currency, occurredAt: now });227 if (rec.imageUrls.length) await storeImages(r.assetId, rec.imageUrls.slice(0, 3), rec.sourceId, { listingId: id });228 await trackCert(rec, 'listing', id, r.assetId, variant.id, priceUsd, rec.price, rec.currency, now);229 return { assetId: r.assetId, variantId: variant.id, targetId: id, method: r.method, confidence, status: 'applied', event: 'listing_created' };230 }231 const priceChanged = rec.price !== null && existing.price !== null && Math.abs(Number(existing.price) - rec.price) > 0.009;232 const relisted = existing.availability !== 'available' && rec.availability === 'available';233 const ended = existing.availability === 'available' && rec.availability !== 'available';234 await db()235 .update(listings)236 .set({237 price: rec.price,238 currency: rec.currency,239 priceUsd,240 availability: rec.availability,241 bidCount: rec.bidCount,242 endsAt: rec.endsAt ?? sql`${listings.endsAt}`,243 lastSeenAt: now,244 priceChangedAt: priceChanged ? now : sql`${listings.priceChangedAt}`,245 quantity: rec.quantity,246 dataQuality: quality,247 updatedAt: new Date(),248 })249 .where(eq(listings.id, existing.id));250 const ev = priceChanged ? 'price_changed' : relisted ? 'relisted' : ended ? (rec.availability === 'sold' ? 'sold' : rec.availability === 'ended' ? 'auction_ended' : 'removed') : null;251 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 });252 return { assetId: r.assetId, variantId: variant.id, targetId: existing.id, method: r.method, confidence, status: 'applied', event: ev ? 'listing_updated' : undefined };253}254255async function applyObservation(rec: NormalizedPriceObservation): Promise<ApplyResult> {256 const grade = refineGrade(rec.grade, rec.rawTitle);257 const r = await resolveAsset(rec.attributes, { imageUrls: rec.imageUrls });258 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };259 const condition = normCond(rec.attributes.categorySlug, rec.condition.conditionRaw, rec.condition.condition);260 const variant = await ensureVariant(r.assetId, grade, { condition, completeness: rec.condition.completeness }, rec.attributes.size);261 const fx = await toUsd(rec.price, rec.currency, rec.observationDate);262 if (!fx) throw new FxMissingError(rec.currency, rec.observationDate);263 const dedupeKey = sha256(['obs', rec.sourceId, rec.externalId ?? rec.sourceUrl, rec.priceKind, variant.key, toDateOnly(rec.observationDate)].join('|'));264 const id = newId('valuation');265 const inserted = await db()266 .insert(priceObservations)267 .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 })268 .onConflictDoNothing({ target: priceObservations.dedupeKey })269 .returning({ id: priceObservations.id });270 if (inserted.length === 0) return { assetId: r.assetId, variantId: variant.id, targetId: null, method: r.method, confidence: r.confidence, status: 'duplicate' };271 return { assetId: r.assetId, variantId: variant.id, targetId: id, method: r.method, confidence: r.confidence, status: 'applied' };272}273274async function applyAuctionLot(rec: NormalizedAuctionLot): Promise<ApplyResult> {275 const grade = refineGrade(rec.grade, rec.rawTitle);276 const r = await resolveAsset(enrichAttributesFromTitle(rec.attributes, rec.rawTitle), { imageUrls: rec.imageUrls, allowCreate: rec.confidence >= 0.7 });277 const auctionUrl = (rec.attributes.metadata?.auctionUrl as string | undefined) ?? `${new URL(rec.sourceUrl).origin}#${rec.auctionHouse}:${rec.auctionName ?? 'auction'}`;278 const auctionId = sha256(auctionUrl).slice(0, 24);279 await db()280 .insert(auctions)281 .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 })282 .onConflictDoUpdate({ target: auctions.url, set: { endsAt: sql`coalesce(excluded.ends_at, ${auctions.endsAt})`, status: sql`excluded.status`, updatedAt: new Date() } });283 let variantId: string | null = null;284 if (r) variantId = (await ensureVariant(r.assetId, grade, { condition: normCond(rec.attributes.categorySlug, rec.condition.conditionRaw, rec.condition.condition), completeness: rec.condition.completeness }, rec.attributes.size)).id;285 const id = `lot_${sha256(rec.sourceUrl).slice(0, 24)}`;286 await db()287 .insert(auctionLots)288 .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: grade.grader, grade: grade.grade })289 .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() } });290 await trackCert(rec, 'auction_lot', id, r?.assetId ?? null, variantId, null, rec.currentBid, rec.currency, rec.observedAt);291 return { assetId: r?.assetId ?? null, variantId, targetId: id, method: r?.method ?? null, confidence: r?.confidence ?? null, status: r ? 'applied' : 'unmatched' };292}293294async function applyPopulation(rec: NormalizedPopulationReport): Promise<ApplyResult> {295 const r = await resolveAsset(rec.attributes, {});296 if (!r) return { assetId: null, variantId: null, targetId: null, method: null, confidence: null, status: 'unmatched' };297 const id = newId('event');298 const inserted = await db()299 .insert(populationReports)300 .values({ id, assetId: r.assetId, grader: rec.grader, sourceId: rec.sourceId, sourceUrl: rec.sourceUrl, reportDate: toDateOnly(rec.reportDate), total: rec.total, byGrade: rec.byGrade })301 .onConflictDoNothing()302 .returning({ id: populationReports.id });303 return { assetId: r.assetId, variantId: null, targetId: inserted[0]?.id ?? null, method: r.method, confidence: r.confidence, status: inserted.length ? 'applied' : 'duplicate' };304}305306async function applyNews(rec: NormalizedNewsItem): Promise<ApplyResult> {307 const id = newId('news');308 const inserted = await db()309 .insert(news)310 .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() })311 .onConflictDoNothing({ target: news.url })312 .returning({ id: news.id });313 return { assetId: null, variantId: null, targetId: inserted[0]?.id ?? null, method: null, confidence: null, status: inserted.length ? 'applied' : 'duplicate' };314}315