TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import 'server-only';2import { getDb, sql } from '@rareindex/database';3import { descendants } from '@rareindex/taxonomy';4import type { Identification } from '@rareindex/ai';56export interface Candidate {7 assetId: string;8 slug: string;9 title: string;10 categorySlug: string;11 heroImageUrl: string | null;12 year: number | null;13 setName: string | null;14 number: string | null;15 variant: string | null;16 score: number;17 rivUsd: number | null;18 rivLowUsd: number | null;19 rivHighUsd: number | null;20 rivConfidence: number | null;21 rivSampleSize: number;22 latestSaleUsd: number | null;23 latestSaleAt: string | null;24 salesCount: number;25 activeListings: number;26 minAskUsd: number | null;27}2829/**30 * Candidate assets for an identification guess (§114): identifier hits first, then trigram/FTS31 * over titles restricted to the guessed category subtree. Pure DB, no AI.32 */33export async function findCandidates(guess: Pick<Identification, 'categorySlug' | 'name' | 'set' | 'number' | 'year' | 'variant' | 'brand' | 'searchQueries' | 'certificationNumber'>, identifiers: Record<string, string> = {}, limit = 8): Promise<Candidate[]> {34 const db = getDb();35 const scope = guess.categorySlug ? [guess.categorySlug, ...descendants(guess.categorySlug)] : null;36 const scopeSql = scope ? sql`and a.category_slug in (${sql.join(scope.map((s) => sql`${s}`), sql`, `)})` : sql``;37 const found = new Map<string, Candidate>();3839 const cols = sql`a.id as "assetId", a.slug, a.title, a.category_slug as "categorySlug", a.hero_image_url as "heroImageUrl", a.year, a.set_name as "setName", a.number, a.variant,40 s.riv_usd as "rivUsd", s.riv_low_usd as "rivLowUsd", s.riv_high_usd as "rivHighUsd", s.riv_confidence as "rivConfidence", coalesce(s.riv_sample_size,0) as "rivSampleSize", s.latest_sale_usd as "latestSaleUsd", s.latest_sale_at as "latestSaleAt", coalesce(s.sales_count,0) as "salesCount", coalesce(s.active_listings,0) as "activeListings", s.min_ask_usd as "minAskUsd"`;4142 // 1) deterministic identifiers43 const idEntries = Object.entries(identifiers).filter(([, v]) => v);44 for (const [k, v] of idEntries) {45 const rows = (await db.execute(sql`select ${cols}, 1.0 as score from assets a left join asset_stats s on s.asset_id = a.id where a.identifiers ->> ${k} = ${v} limit 3`)) as unknown as Candidate[];46 for (const r of rows) found.set(r.assetId, { ...r, score: 1 });47 }4849 // 2) text queries: model-proposed search strings + a composed one50 const composed = [guess.year, guess.brand, guess.set, guess.name, guess.number, guess.variant].filter(Boolean).join(' ');51 const queries = [...new Set([composed, ...(guess.searchQueries ?? [])].map((q) => q.trim()).filter((q) => q.length >= 3))].slice(0, 4);52 for (const q of queries) {53 const tsq = q54 .split(/\s+/)55 .map((t) => t.replace(/[^\p{L}\p{N}./-]/gu, ''))56 .filter(Boolean)57 .map((t) => `${t}:*`)58 .join(' | ');59 const rows = (await db.execute(sql`select ${cols},60 (similarity(a.title, ${q}) * 0.6 + coalesce(ts_rank(a.search, to_tsquery('simple', ${tsq})), 0) * 0.461 + case when ${guess.number ?? null}::text is not null and a.number = ${guess.number ?? null}::text then 0.25 else 0 end62 + case when ${guess.year ?? null}::int is not null and a.year = ${guess.year ?? null}::int then 0.1 else 0 end) as score63 from assets a left join asset_stats s on s.asset_id = a.id64 where (a.title % ${q} or a.search @@ to_tsquery('simple', ${tsq})) ${scopeSql}65 order by score desc limit ${limit}`)) as unknown as Candidate[];66 for (const r of rows) {67 const prev = found.get(r.assetId);68 if (!prev || prev.score < Number(r.score)) found.set(r.assetId, { ...r, score: Number(r.score) });69 }70 }71 return [...found.values()].sort((a, b) => b.score - a.score).slice(0, limit);72}7374export async function assetContext(assetId: string) {75 const db = getDb();76 const sales = (await db.execute(sql`select id, sale_date as "saleDate", price, currency, price_usd as "priceUsd", grader, grade, source_id as "sourceId", source_url as "sourceUrl", raw_title as "rawTitle" from sales where asset_id = ${assetId} and status = 'valid' order by sale_date desc limit 8`)) as unknown as Array<Record<string, unknown>>;77 const listings = (await db.execute(sql`select id, price, currency, price_usd as "priceUsd", source_id as "sourceId", source_url as "sourceUrl", grader, grade, condition, raw_title as "rawTitle", discount_to_riv as "discountToRiv" from listings where asset_id = ${assetId} and availability = 'available' order by price_usd asc nulls last limit 8`)) as unknown as Array<Record<string, unknown>>;78 const variants = (await db.execute(sql`select v.id, v.label, v.grader, v.grade, vs.riv_usd as "rivUsd", vs.riv_confidence as "rivConfidence", vs.riv_sample_size as "rivSampleSize", vs.sales_count as "salesCount" from asset_variants v left join variant_stats vs on vs.variant_id = v.id where v.asset_id = ${assetId} order by vs.sales_count desc nulls last limit 12`)) as unknown as Array<Record<string, unknown>>;79 return { sales, listings, variants };80}81