TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import { sql } from 'drizzle-orm';2import { getDb } from '@rareindex/database';3import { descendants, getCategory, CATEGORIES } from '@rareindex/taxonomy';4import { parseQuery, toTsQuery, type ParsedQuery } from './parse.js';56export { parseQuery, toTsQuery, type ParsedQuery };78/**9 * Hybrid asset search (§138): lexical (tsvector, weighted A/B/C) + trigram similarity on the title10 * + popularity (sales count, watchers, observations) + recency, with structured filters parsed11 * from the natural-language query. Runs entirely in PostgreSQL; vector re-ranking can be added12 * on top when `asset_embeddings` is populated.13 */14export interface SearchOptions {15 limit?: number;16 offset?: number;17 categorySlug?: string | null;18 grader?: string | null;19 grade?: string | null;20 priceMinUsd?: number | null;21 priceMaxUsd?: number | null;22 yearFrom?: number | null;23 yearTo?: number | null;24}2526export interface SearchHit {27 id: string;28 slug: string;29 title: string;30 name: string;31 categorySlug: string;32 familySlug: string;33 setName: string | null;34 number: string | null;35 year: number | null;36 variant: string | null;37 heroImageUrl: string | null;38 rivUsd: number | null;39 rivConfidence: number | null;40 latestSaleUsd: number | null;41 change30d: number | null;42 salesCount: number;43 liquidityScore: number | null;44 score: number;45}4647export interface SearchResult {48 parsed: ParsedQuery;49 hits: SearchHit[];50 total: number;51 tookMs: number;52 categories: Array<{ slug: string; name: string; count: number }>;53}5455export async function searchAssets(query: string, opts: SearchOptions = {}): Promise<SearchResult> {56 const t0 = Date.now();57 const parsed = parseQuery(query);58 const limit = Math.min(Math.max(opts.limit ?? 24, 1), 100);59 const offset = Math.max(opts.offset ?? 0, 0);60 const db = getDb();6162 const tsq = toTsQuery(parsed.text || parsed.raw);63 const likeText = (parsed.text || parsed.raw).trim();64 const catSlug = opts.categorySlug ?? (parsed.categorySlugs.length === 1 ? parsed.categorySlugs[0]! : null);65 const catSlugs = catSlug ? [catSlug, ...descendants(catSlug)] : parsed.categorySlugs.length > 1 ? parsed.categorySlugs.flatMap((s) => [s, ...descendants(s)]) : null;66 const grader = opts.grader ?? parsed.grader;67 const grade = opts.grade ?? parsed.grade;68 const priceMin = opts.priceMinUsd ?? (parsed.currency === 'USD' || parsed.currency === null ? parsed.priceMin : null);69 const priceMax = opts.priceMaxUsd ?? (parsed.currency === 'USD' || parsed.currency === null ? parsed.priceMax : null);7071 const conditions = [sql`true`];72 // Sets and codes: resolve matching releases first (small table, indexed join on assets.set_slug) so73 // "base set", "neo genesis", "LOB" or "2XM" find every card of the release, not only titles.74 const setSlugs = likeText.length >= 2 ? await matchingSetSlugs(db, likeText) : [];75 const codeToken = codeLike(likeText);76 if (tsq && likeText.length >= 2) {77 const parts = [sql`a.search @@ to_tsquery('simple', ${tsq})`, sql`a.title % ${likeText}`, sql`a.title ILIKE ${'%' + likeText + '%'}`];78 if (setSlugs.length) parts.push(sql`a.set_slug IN ${setSlugs}`);79 if (codeToken) parts.push(sql`(a.number ILIKE ${codeToken} OR a.reference ILIKE ${codeToken} OR a.set_code ILIKE ${codeToken} OR a.identifiers @> ${JSON.stringify({ style_code: likeText })}::jsonb OR a.identifiers @> ${JSON.stringify({ reference: likeText })}::jsonb OR a.identifiers @> ${JSON.stringify({ lego_set_number: likeText })}::jsonb)`);80 conditions.push(sql`(${sql.join(parts, sql` OR `)})`);81 }82 if (catSlugs?.length) conditions.push(sql`a.category_slug IN ${catSlugs}`);83 if (opts.yearFrom) conditions.push(sql`a.year >= ${opts.yearFrom}`);84 if (opts.yearTo) conditions.push(sql`a.year <= ${opts.yearTo}`);85 if (priceMin !== null && priceMin !== undefined) conditions.push(sql`s.riv_usd >= ${priceMin}`);86 if (priceMax !== null && priceMax !== undefined) conditions.push(sql`s.riv_usd <= ${priceMax}`);87 if (grader && grader !== 'raw') {88 conditions.push(sql`EXISTS (SELECT 1 FROM asset_variants v WHERE v.asset_id = a.id AND v.grader = ${grader} ${grade ? sql`AND v.grade = ${grade}` : sql``})`);89 }90 const where = sql.join(conditions, sql` AND `);9192 const rank = tsq93 ? sql`(coalesce(ts_rank_cd(a.search, to_tsquery('simple', ${tsq})), 0) * 4 + coalesce(similarity(a.title, ${likeText}), 0) * 3 + ln(1 + coalesce(s.sales_count, 0)) * 0.35 + ln(1 + coalesce(s.observations_count, 0)) * 0.1 + ln(1 + coalesce(s.watchers, 0)) * 0.2 + (CASE WHEN a.year = ${parsed.year ?? -1} THEN 0.8 ELSE 0 END) + (CASE WHEN s.riv_usd IS NOT NULL THEN 0.3 ELSE 0 END) + (CASE WHEN a.hero_image_url IS NOT NULL THEN 0.15 ELSE 0 END) + (CASE WHEN ${codeToken ?? ''} <> '' AND (a.number ILIKE ${codeToken ?? ''} OR a.reference ILIKE ${codeToken ?? ''}) THEN 3 ELSE 0 END))`94 : sql`(ln(1 + coalesce(s.sales_count, 0)) * 0.35 + ln(1 + coalesce(s.watchers, 0)) * 0.2 + (CASE WHEN s.riv_usd IS NOT NULL THEN 0.3 ELSE 0 END))`;9596 const rows = (await db.execute(sql`97 SELECT a.id, a.slug, a.title, a.name, a.category_slug, a.family_slug, a.set_name, a.number, a.year, a.variant, a.hero_image_url,98 s.riv_usd, s.riv_confidence, s.latest_sale_usd, s.change_30d, coalesce(s.sales_count, 0) AS sales_count, s.liquidity_score,99 ${rank} AS score, count(*) OVER() AS total100 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id101 WHERE ${where}102 ORDER BY score DESC, a.updated_at DESC103 LIMIT ${limit} OFFSET ${offset}104 `)) as unknown as Array<Record<string, unknown>>;105106 const hits: SearchHit[] = rows.map((r) => ({107 id: String(r.id),108 slug: String(r.slug),109 title: String(r.title),110 name: String(r.name),111 categorySlug: String(r.category_slug),112 familySlug: String(r.family_slug),113 setName: (r.set_name as string | null) ?? null,114 number: (r.number as string | null) ?? null,115 year: r.year === null ? null : Number(r.year),116 variant: (r.variant as string | null) ?? null,117 heroImageUrl: (r.hero_image_url as string | null) ?? null,118 rivUsd: r.riv_usd === null || r.riv_usd === undefined ? null : Number(r.riv_usd),119 rivConfidence: r.riv_confidence === null || r.riv_confidence === undefined ? null : Number(r.riv_confidence),120 latestSaleUsd: r.latest_sale_usd === null || r.latest_sale_usd === undefined ? null : Number(r.latest_sale_usd),121 change30d: r.change_30d === null || r.change_30d === undefined ? null : Number(r.change_30d),122 salesCount: Number(r.sales_count ?? 0),123 liquidityScore: r.liquidity_score === null || r.liquidity_score === undefined ? null : Number(r.liquidity_score),124 score: Number(r.score ?? 0),125 }));126 const total = rows.length ? Number(rows[0]!.total) : 0;127128 // Category facets for the current lexical match (cheap, capped)129 let categories: SearchResult['categories'] = [];130 if (tsq && likeText.length >= 2) {131 const facets = (await db.execute(sql`132 SELECT a.category_slug AS slug, count(*)::int AS count FROM assets a133 WHERE (a.search @@ to_tsquery('simple', ${tsq}) OR a.title % ${likeText})134 GROUP BY a.category_slug ORDER BY count DESC LIMIT 8135 `)) as unknown as Array<{ slug: string; count: number }>;136 categories = facets.map((f) => ({ slug: f.slug, name: getCategory(f.slug)?.name ?? f.slug, count: Number(f.count) }));137 }138139 return { parsed, hits, total, tookMs: Date.now() - t0, categories };140}141142export interface Suggestion {143 type: 'asset' | 'category' | 'set' | 'brand' | 'index';144 label: string;145 sublabel: string | null;146 href: string;147}148149/** Instant suggestions for the header search (prefix / trigram). */150export async function suggest(prefix: string, limit = 8): Promise<Suggestion[]> {151 const q = prefix.trim();152 if (q.length < 2) return [];153 const db = getDb();154 const out: Suggestion[] = [];155 const lower = q.toLowerCase();156 for (const c of CATEGORIES) {157 if (c.name.toLowerCase().startsWith(lower) || c.name.toLowerCase().includes(` ${lower}`)) {158 out.push({ type: 'category', label: c.name, sublabel: c.level === 0 ? 'Category' : `${getCategory(c.familySlug)?.name ?? ''} · Category`, href: `/markets/${c.slug}` });159 if (out.length >= 3) break;160 }161 }162 const tsq = toTsQuery(q);163 const code = codeLike(q);164 const rows = (await db.execute(sql`165 SELECT a.slug, a.title, a.category_slug, s.riv_usd166 FROM assets a LEFT JOIN asset_stats s ON s.asset_id = a.id167 WHERE a.title ILIKE ${q + '%'} OR a.title ILIKE ${'% ' + q + '%'} ${tsq ? sql`OR a.search @@ to_tsquery('simple', ${tsq})` : sql``} ${code ? sql`OR a.number ILIKE ${code} OR a.reference ILIKE ${code}` : sql``}168 ORDER BY (a.hero_image_url IS NOT NULL) DESC, coalesce(s.sales_count, 0) DESC, similarity(a.title, ${q}) DESC169 LIMIT ${limit}170 `)) as unknown as Array<{ slug: string; title: string; category_slug: string; riv_usd: number | null }>;171 for (const r of rows) {172 out.push({ type: 'asset', label: r.title, sublabel: getCategory(r.category_slug)?.name ?? r.category_slug, href: `/asset/${r.slug}` });173 }174 const sets = (await db.execute(sql`SELECT slug, name, code, category_slug FROM sets WHERE name ILIKE ${q + '%'} OR name ILIKE ${'% ' + q + '%'} OR code ILIKE ${q} ORDER BY (code ILIKE ${q}) DESC, name LIMIT 3`)) as unknown as Array<{ slug: string; name: string; code: string | null; category_slug: string }>;175 for (const s of sets) out.push({ type: 'set', label: `${s.name}${s.code ? ` (${s.code})` : ''}`, sublabel: `Set · ${getCategory(s.category_slug)?.name ?? s.category_slug}`, href: `/set/${s.slug}` });176 return out.slice(0, limit + 3);177}178179/** A token that looks like a collector number, reference or set code (e.g. "LOB-001", "116500LN", "4/102", "10179"). */180export function codeLike(text: string): string | null {181 const t = text.trim();182 if (!t || t.length > 24 || /\s/.test(t)) return null;183 if (!/\d/.test(t)) return /^[A-Z]{2,6}$/i.test(t) ? t : null; // bare set codes like "LOB", "2XM" handled via sets; short caps allowed184 if (!/^[A-Za-z0-9./#-]+$/.test(t)) return null;185 return t.replace(/^#/, '');186}187188/** Set slugs whose name or code matches the text (sets table is small and cached per query). */189export async function matchingSetSlugs(db: ReturnType<typeof getDb>, text: string, limit = 25): Promise<string[]> {190 const t = text.trim();191 if (t.length < 2) return [];192 const rows = (await db.execute(sql`193 SELECT slug FROM sets WHERE code ILIKE ${t} OR name ILIKE ${t} OR name ILIKE ${t + '%'} OR name ILIKE ${'% ' + t + '%'} OR name ILIKE ${'% ' + t}194 ORDER BY (code ILIKE ${t}) DESC, (name ILIKE ${t}) DESC, name LIMIT ${limit}195 `)) as unknown as Array<{ slug: string }>;196 return rows.map((r) => r.slug);197}198199export interface SetHit {200 slug: string;201 name: string;202 code: string | null;203 categorySlug: string;204 releaseYear: number | null;205 assets: number;206 thumb: string | null;207}208209/** Sets matching a query, with asset counts (grouped results block). */210export async function searchSets(query: string, limit = 8): Promise<SetHit[]> {211 const t = query.trim();212 if (t.length < 2) return [];213 const db = getDb();214 const rows = (await db.execute(sql`215 SELECT st.slug, st.name, st.code, st.category_slug, st.release_year,216 (SELECT count(*) FROM assets a WHERE a.set_slug = st.slug) AS assets,217 (SELECT a.hero_image_url FROM assets a WHERE a.set_slug = st.slug AND a.hero_image_url IS NOT NULL LIMIT 1) AS thumb218 FROM sets st219 WHERE st.code ILIKE ${t} OR st.name ILIKE ${'%' + t + '%'}220 ORDER BY (st.code ILIKE ${t}) DESC, (st.name ILIKE ${t}) DESC, assets DESC LIMIT ${limit}221 `)) as unknown as Array<Record<string, unknown>>;222 return rows.map((r) => ({ slug: String(r.slug), name: String(r.name), code: (r.code as string | null) ?? null, categorySlug: String(r.category_slug), releaseYear: r.release_year == null ? null : Number(r.release_year), assets: Number(r.assets ?? 0), thumb: (r.thumb as string | null) ?? null }));223}224