TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import { and, eq, sql } from 'drizzle-orm';2import { assetVariants, assets, brands, sets } from '@rareindex/database';3import { categoryFamily, getCategory, variantKey } from '@rareindex/taxonomy';4import { deterministicId, extractYear, jaccard, newId, normalizeForMatch, slugify, type AssetAttributes, type Grade } from '@rareindex/shared';5import { db } from '../lib/db.ts';6import { buildCanonicalKey, composeTitle, deterministicIdentifiers } from './canonical-key.ts';78export type MatchMethod = 'identifier' | 'canonical_key' | 'fuzzy' | 'created';910export interface Resolution {11 assetId: string;12 method: MatchMethod;13 confidence: number;14 created: boolean;15 canonicalKey: string;16}1718const keyCache = new Map<string, string>();19const MAX_CACHE = 200_000;20function remember(key: string, id: string) {21 if (keyCache.size > MAX_CACHE) keyCache.clear();22 keyCache.set(key, id);23}2425const REFERENCE_FAMILIES = new Set(['watches', 'sneakers', 'lego', 'cameras', 'audio_equipment']);2627/**28 * Score a fuzzy candidate against attributes: 0 when a hard discriminator disagrees29 * (card number, reference/model number, year, set). When only one side carries a discriminator30 * the match is penalised; for reference-driven families (watches, sneakers, LEGO) it is refused —31 * a wrong merge is worse than a duplicate asset (data quality first).32 */33export function fuzzyCandidateScore(cand: { title: string; number: string | null; year: number | null; setName: string | null; reference?: string | null }, a: AssetAttributes, trgm: number): number {34 const norm = (s: string | null | undefined) => (s ? s.replace(/^#/, '').toLowerCase().replace(/[\s.\-/]+/g, '') : '');35 const numA = norm(a.number);36 const numC = norm(cand.number);37 if (numA && numC && numA !== numC) return 0;38 const refA = norm(a.reference ?? a.identifiers?.style_code ?? a.identifiers?.lego_set_number);39 const refC = norm(cand.reference);40 if (refA && refC && refA !== refC) return 0;41 const family = categoryFamily(a.categorySlug);42 if (REFERENCE_FAMILIES.has(family) && (refA ? !refC : refC)) return 0;43 if (a.year && cand.year && Math.abs(a.year - cand.year) > 1) return 0;44 if (a.set && cand.setName && jaccard(a.set, cand.setName) < 0.3) return 0;45 let penalty = 1;46 if ((numA && !numC) || (!numA && numC)) penalty *= 0.85;47 if ((refA && !refC) || (!refA && refC)) penalty *= 0.85;48 const j = jaccard(composeTitle(a), cand.title);49 return (Math.max(trgm, 0) * 0.6 + j * 0.4) * penalty;50}5152const WATCH_REF_RE = /\b(?:ref(?:erence)?\.?\s*)([A-Z0-9][A-Z0-9./-]{2,24})/i;53/** Fill reference-like attributes from a raw title when the connector could not (watches). */54export function enrichAttributesFromTitle(a: AssetAttributes, rawTitle: string | null | undefined): AssetAttributes {55 if (!rawTitle) return a;56 if (categoryFamily(a.categorySlug) === 'watches' && !a.reference) {57 const m = rawTitle.match(WATCH_REF_RE);58 if (m) return { ...a, reference: m[1]!.replace(/[.,]$/, '') };59 }60 return a;61}6263/**64 * Resolve attributes to a canonical asset (§112): identifiers → canonical key → fuzzy → create.65 * Existing assets are enriched with missing attributes when `enrich` is set (catalog sources).66 */67export async function resolveAsset(a: AssetAttributes, opts: { enrich?: boolean; imageUrls?: string[]; description?: string | null; allowCreate?: boolean; minFuzzy?: number } = {}): Promise<Resolution | null> {68 const canonicalKey = buildCanonicalKey(a);69 const ids = deterministicIdentifiers(a.identifiers);70 const cached = keyCache.get(canonicalKey);71 if (cached) {72 if (opts.enrich) await enrichAsset(cached, a, opts);73 return { assetId: cached, method: 'canonical_key', confidence: 0.97, created: false, canonicalKey };74 }7576 // 1. deterministic identifiers. Some ids are card-level, not printing-level (pokemontcg_id, ygo_id share77 // one id across Holo / 1st Edition / Reverse variants), so a hit must also agree on the variant when both78 // sides state one; otherwise fall through to the canonical key (which includes the variant).79 const normVariant = (x: string | null | undefined) => (x ? x.toLowerCase().replace(/[\s.\-/]+/g, '') : '');80 const incomingVariant = normVariant(a.variant);81 for (const [k, v] of Object.entries(ids)) {82 const rows = await db()83 .select({ id: assets.id, variant: assets.variant })84 .from(assets)85 .where(and(sql`${assets.identifiers} @> ${JSON.stringify({ [k]: v })}::jsonb`, eq(assets.familySlug, categoryFamily(a.categorySlug))))86 .limit(5);87 const row = rows.find((r) => !incomingVariant || !normVariant(r.variant) || normVariant(r.variant) === incomingVariant);88 if (row) {89 remember(canonicalKey, row.id);90 if (opts.enrich) await enrichAsset(row.id, a, opts);91 return { assetId: row.id, method: 'identifier', confidence: 0.99, created: false, canonicalKey };92 }93 }9495 // 2. canonical key96 const [byKey] = await db().select({ id: assets.id }).from(assets).where(eq(assets.canonicalKey, canonicalKey)).limit(1);97 if (byKey) {98 remember(canonicalKey, byKey.id);99 if (opts.enrich || Object.keys(ids).length) await enrichAsset(byKey.id, a, opts);100 return { assetId: byKey.id, method: 'canonical_key', confidence: 0.96, created: false, canonicalKey };101 }102103 // 3. fuzzy (pg_trgm) within category104 const title = composeTitle(a);105 const minFuzzy = opts.minFuzzy ?? 0.55;106 const candidates = await db()107 .select({ id: assets.id, title: assets.title, number: assets.number, year: assets.year, setName: assets.setName, reference: assets.reference, sim: sql<number>`similarity(${assets.title}, ${title})` })108 .from(assets)109 .where(and(eq(assets.categorySlug, a.categorySlug), sql`similarity(${assets.title}, ${title}) >= ${minFuzzy}`))110 .orderBy(sql`similarity(${assets.title}, ${title}) desc`)111 .limit(5);112 let best: { id: string; score: number } | null = null;113 for (const c of candidates) {114 const score = fuzzyCandidateScore({ title: c.title, number: c.number, year: c.year, setName: c.setName, reference: c.reference }, a, Number(c.sim));115 if (score >= 0.72 && (!best || score > best.score)) best = { id: c.id, score };116 }117 if (best) {118 remember(canonicalKey, best.id);119 if (Object.keys(ids).length) await enrichAsset(best.id, a, opts);120 return { assetId: best.id, method: 'fuzzy', confidence: Math.min(0.9, best.score), created: false, canonicalKey };121 }122123 if (opts.allowCreate === false) return null;124 // 4. create125 const id = await createAsset(a, canonicalKey, title, opts);126 remember(canonicalKey, id);127 return { assetId: id, method: 'created', confidence: 0.9, created: true, canonicalKey };128}129130async function createAsset(a: AssetAttributes, canonicalKey: string, title: string, opts: { imageUrls?: string[]; description?: string | null }): Promise<string> {131 const id = deterministicId('asset', canonicalKey);132 const family = categoryFamily(a.categorySlug);133 const base = slugify(title) || slugify(canonicalKey) || 'asset';134 const slug = `${base}-${id.slice(-6)}`;135 const setSlug = a.set || a.setCode ? await ensureSet(a) : null;136 if (a.brand) await ensureBrand(a.brand, a.categorySlug);137 await db()138 .insert(assets)139 .values({140 id,141 slug,142 canonicalKey,143 categorySlug: a.categorySlug,144 subcategorySlug: a.subcategorySlug,145 familySlug: family,146 franchise: a.franchise,147 brand: a.brand,148 series: a.series,149 setSlug,150 setName: a.set,151 setCode: a.setCode,152 name: a.name,153 title,154 model: a.model,155 reference: a.reference,156 number: a.number,157 year: a.year ?? extractYear(title),158 edition: a.edition,159 variant: a.variant,160 language: a.language,161 region: a.region,162 country: a.country,163 material: a.material,164 size: a.size,165 color: a.color,166 rarity: a.rarity,167 productionQuantity: a.productionQuantity,168 originalMsrp: a.originalMsrp,169 originalMsrpCurrency: a.originalMsrpCurrency,170 description: opts.description ?? null,171 heroImageUrl: opts.imageUrls?.[0] ?? null,172 identifiers: deterministicIdentifiers(a.identifiers),173 metadata: a.metadata ?? {},174 dataQuality: 0,175 })176 .onConflictDoNothing({ target: assets.canonicalKey });177 return id;178}179180/** Fill missing attributes / identifiers on an existing asset (coalesce semantics; never overwrite with null). */181async function enrichAsset(assetId: string, a: AssetAttributes, opts: { imageUrls?: string[]; description?: string | null }): Promise<void> {182 const ids = deterministicIdentifiers(a.identifiers);183 await db()184 .update(assets)185 .set({186 identifiers: Object.keys(ids).length ? sql`${assets.identifiers} || ${JSON.stringify(ids)}::jsonb` : sql`${assets.identifiers}`,187 franchise: sql`coalesce(${assets.franchise}, ${a.franchise ?? null})`,188 brand: sql`coalesce(${assets.brand}, ${a.brand ?? null})`,189 series: sql`coalesce(${assets.series}, ${a.series ?? null})`,190 setName: sql`coalesce(${assets.setName}, ${a.set ?? null})`,191 setCode: sql`coalesce(${assets.setCode}, ${a.setCode ?? null})`,192 number: sql`coalesce(${assets.number}, ${a.number ?? null})`,193 year: sql`coalesce(${assets.year}, ${a.year ?? null})`,194 edition: sql`coalesce(${assets.edition}, ${a.edition ?? null})`,195 variant: sql`coalesce(${assets.variant}, ${a.variant ?? null})`,196 language: sql`coalesce(${assets.language}, ${a.language ?? null})`,197 rarity: sql`coalesce(${assets.rarity}, ${a.rarity ?? null})`,198 reference: sql`coalesce(${assets.reference}, ${a.reference ?? null})`,199 model: sql`coalesce(${assets.model}, ${a.model ?? null})`,200 productionQuantity: sql`coalesce(${assets.productionQuantity}, ${a.productionQuantity ?? null})`,201 originalMsrp: sql`coalesce(${assets.originalMsrp}, ${a.originalMsrp ?? null})`,202 originalMsrpCurrency: sql`coalesce(${assets.originalMsrpCurrency}, ${a.originalMsrpCurrency ?? null})`,203 description: sql`coalesce(${assets.description}, ${opts.description ?? null})`,204 heroImageUrl: sql`coalesce(${assets.heroImageUrl}, ${opts.imageUrls?.[0] ?? null})`,205 metadata: a.metadata && Object.keys(a.metadata).length ? sql`${JSON.stringify(a.metadata)}::jsonb || ${assets.metadata}` : sql`${assets.metadata}`,206 updatedAt: new Date(),207 })208 .where(eq(assets.id, assetId));209}210211const setCache = new Set<string>();212export async function ensureSet(a: AssetAttributes): Promise<string> {213 const name = a.set ?? a.setCode!;214 const slug = slugify(`${a.categorySlug}-${a.setCode ?? name}`);215 if (setCache.has(slug)) return slug;216 await db()217 .insert(sets)218 .values({ slug, name, code: a.setCode ?? null, categorySlug: a.categorySlug, franchiseSlug: a.franchise ? slugify(a.franchise) : null, brandSlug: a.brand ? slugify(a.brand) : null, releaseYear: a.year ?? null, language: a.language ?? null })219 .onConflictDoNothing();220 setCache.add(slug);221 return slug;222}223224const brandCache = new Set<string>();225export async function ensureBrand(name: string, categorySlug: string): Promise<void> {226 const slug = slugify(name);227 if (!slug || brandCache.has(slug)) return;228 await db()229 .insert(brands)230 .values({ slug, name, categorySlugs: [categorySlug] })231 .onConflictDoUpdate({ target: brands.slug, set: { categorySlugs: sql`(select array_agg(distinct x) from unnest(${brands.categorySlugs} || ${sql`ARRAY[${categorySlug}]::text[]`}) x)` } });232 brandCache.add(slug);233}234235const variantCache = new Map<string, string>();236/** Ensure the grade/condition variant row for an asset; returns variant id (§117–§118). */237export async function ensureVariant(assetId: string, grade: Grade, condition: { condition?: string | null; completeness?: string | null }, size?: string | null): Promise<{ id: string; key: string }> {238 const { key, label } = variantKey({ grader: grade.grader, grade: grade.grade, qualifier: grade.qualifier, condition: condition.condition, completeness: condition.completeness, size });239 const cacheKey = `${assetId}|${key}`;240 const hit = variantCache.get(cacheKey);241 if (hit) return { id: hit, key };242 const id = deterministicId('variant', assetId, key);243 await db()244 .insert(assetVariants)245 .values({ id, assetId, variantKey: key, grader: grade.grader && grade.grader !== 'raw' ? grade.grader : null, grade: grade.grade ?? null, qualifier: grade.qualifier ?? null, condition: condition.condition ?? null, completeness: condition.completeness ?? null, sizeLabel: size ?? null, label, isDefault: key === 'raw' })246 .onConflictDoNothing();247 if (variantCache.size > MAX_CACHE) variantCache.clear();248 variantCache.set(cacheKey, id);249 return { id, key };250}251252export function categoryLabel(slug: string): string {253 return getCategory(slug)?.name ?? slug;254}255256export { normalizeForMatch, newId };257