TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import 'server-only';2import { z } from 'zod';3import { getDb, sql } from '@rareindex/database';4import { CATEGORIES, descendants } from '@rareindex/taxonomy';5import type { ToolDefinition } from '@rareindex/ai';67/**8 * Structured tools for AI Research (§128). The model can only call these; each one is a fixed,9 * parameterised query — never free-form SQL. Results are compact and always carry provenance10 * columns (slug/url/date) so the assistant can cite them.11 */12type Row = Record<string, unknown>;13const run = async (q: ReturnType<typeof sql>): Promise<Row[]> => (await getDb().execute(q)) as unknown as Row[];1415const WINDOWS = { '7d': 'change_7d', '30d': 'change_30d', '90d': 'change_90d', '1y': 'change_1y' } as const;16type Window = keyof typeof WINDOWS;1718function scope(category?: string | null) {19 if (!category) return sql``;20 const slugs = [category, ...descendants(category)];21 return sql`and a.category_slug in (${sql.join(slugs.map((s) => sql`${s}`), sql`, `)})`;22}2324const ASSET_COLS = sql`a.slug, a.title, a.category_slug, a.year, a.hero_image_url, s.riv_usd, s.riv_low_usd, s.riv_high_usd, s.riv_confidence, s.riv_sample_size, s.latest_sale_usd, s.latest_sale_at, s.change_7d, s.change_30d, s.change_90d, s.change_1y, s.sales_count, s.sales_30d, s.active_listings, s.min_ask_usd, s.liquidity_score, s.rarity_score, s.trending_score, s.value_opportunity`;2526export const TOOL_SCHEMAS = {27 search_assets: z.object({ query: z.string().min(1).max(200), category: z.string().nullable().optional(), limit: z.number().int().min(1).max(25).default(10) }),28 get_asset: z.object({ slug: z.string() }),29 get_asset_sales: z.object({ slug: z.string(), limit: z.number().int().min(1).max(50).default(20) }),30 get_asset_listings: z.object({ slug: z.string(), limit: z.number().int().min(1).max(50).default(20) }),31 get_price_history: z.object({ slug: z.string(), days: z.number().int().min(7).max(3650).default(365) }),32 list_categories: z.object({ family: z.string().nullable().optional() }),33 get_category_market: z.object({ category: z.string() }),34 get_index: z.object({ ticker: z.string() }),35 get_index_history: z.object({ ticker: z.string(), days: z.number().int().min(7).max(3650).default(365) }),36 top_movers: z.object({ category: z.string().nullable().optional(), window: z.enum(['7d', '30d', '90d', '1y']).default('30d'), direction: z.enum(['gainers', 'losers']).default('gainers'), min_sales: z.number().int().min(0).default(3), limit: z.number().int().min(1).max(25).default(10) }),37 compare: z.object({ items: z.array(z.string()).min(2).max(6).describe('asset slugs, category slugs or index tickers'), window: z.enum(['7d', '30d', '90d', '1y']).default('1y') }),38 screen: z.object({39 category: z.string().nullable().optional(),40 min_price_usd: z.number().nullable().optional(),41 max_price_usd: z.number().nullable().optional(),42 min_change: z.number().nullable().optional().describe('minimum return over the window as a fraction, e.g. 0.3'),43 max_change: z.number().nullable().optional(),44 window: z.enum(['7d', '30d', '90d', '1y']).default('1y'),45 min_sales: z.number().int().min(0).default(3),46 max_sales_1y: z.number().int().nullable().optional(),47 min_liquidity: z.number().min(0).max(100).nullable().optional(),48 sort: z.enum(['change', 'value', 'liquidity', 'sales', 'trending', 'opportunity']).default('change'),49 limit: z.number().int().min(1).max(50).default(20),50 }),51 record_sales: z.object({ category: z.string().nullable().optional(), limit: z.number().int().min(1).max(25).default(10) }),52 radar: z.object({ kind: z.string().nullable().optional(), limit: z.number().int().min(1).max(25).default(10) }),53 platform_coverage: z.object({}),54} as const;5556export type ToolName = keyof typeof TOOL_SCHEMAS;5758const DESCRIPTIONS: Record<ToolName, string> = {59 search_assets: 'Full-text search over canonical assets (cards, watches, sneakers, LEGO, games…). Returns slugs, valuations and activity. Use before any asset-specific tool when you only have a name.',60 get_asset: 'Asset detail: attributes, RareIndex Valuation with low/high/confidence/sample size, changes, sales/listings counts, variants (grades) with their own valuations.',61 get_asset_sales: 'Recent observed sales for an asset (date, price, currency, USD, grade, source URL).',62 get_asset_listings: 'Current listings (asks, not sales) for an asset with discount vs RIV.',63 get_price_history: 'Daily RIV / latest-sale / median / volume series for an asset over N days.',64 list_categories: 'Taxonomy with tracked asset counts; optionally children of a family.',65 get_category_market: 'Category market snapshot: index value, tracked assets, sales, volume, median sale, listings, changes; plus top gainers/losers and most valuable.',66 get_index: 'RareIndex index (RARE or RARE-XXX) latest value, changes, constituents, transactions, market cap estimate with confidence.',67 get_index_history: 'Index daily values over N days.',68 top_movers: 'Best or worst performing assets over a window, optionally within a category, requiring a minimum number of sales.',69 compare: 'Compare returns over a window across asset slugs, category slugs and/or index tickers (mixed allowed). Returns start/end values and return.',70 screen: 'Screen assets by category, price range, return over window, sales counts, liquidity; sort by change/value/liquidity/sales/trending/opportunity.',71 record_sales: 'Highest verified sales (optionally within a category).',72 radar: 'Rare Radar findings: first listing in years, ultra-low population, unusual price discrepancy, record sale…',73 platform_coverage: 'Counts of assets, sales, listings, sources and the latest data timestamps — use to explain coverage limits.',74};7576export function toolDefinitions(): ToolDefinition[] {77 return (Object.keys(TOOL_SCHEMAS) as ToolName[]).map((name) => ({78 name,79 description: DESCRIPTIONS[name],80 inputSchema: z.toJSONSchema(TOOL_SCHEMAS[name], { target: 'draft-2020-12', io: 'input' }) as Record<string, unknown>,81 }));82}8384async function assetBySlug(slug: string) {85 const [a] = await run(sql`select a.id, ${ASSET_COLS}, a.brand, a.franchise, a.set_name, a.number, a.variant, a.edition, a.language, a.identifiers from assets a left join asset_stats s on s.asset_id = a.id where a.slug = ${slug} or a.id = ${slug} limit 1`);86 return a ?? null;87}8889export async function executeTool(name: string, rawInput: unknown): Promise<unknown> {90 if (!(name in TOOL_SCHEMAS)) throw new Error(`unknown tool ${name}`);91 const schema = TOOL_SCHEMAS[name as ToolName];92 const input = schema.parse(rawInput ?? {}) as z.infer<typeof schema>;93 switch (name as ToolName) {94 case 'search_assets': {95 const i = input as z.infer<typeof TOOL_SCHEMAS.search_assets>;96 const tsq = i.query.split(/\s+/).map((t) => t.replace(/[^\p{L}\p{N}./-]/gu, '')).filter(Boolean).map((t) => `${t}:*`).join(' & ');97 const rows = await run(sql`select ${ASSET_COLS}, (coalesce(ts_rank(a.search, to_tsquery('simple', ${tsq})),0)*2 + similarity(a.title, ${i.query})) as score98 from assets a left join asset_stats s on s.asset_id = a.id where (a.search @@ to_tsquery('simple', ${tsq}) or a.title % ${i.query}) ${scope(i.category)} order by score desc, s.sales_count desc nulls last limit ${i.limit}`);99 return { count: rows.length, results: rows };100 }101 case 'get_asset': {102 const i = input as z.infer<typeof TOOL_SCHEMAS.get_asset>;103 const a = await assetBySlug(i.slug);104 if (!a) return { error: 'asset not found', hint: 'use search_assets first' };105 const variants = await run(sql`select v.label, v.grader, v.grade, vs.riv_usd, vs.riv_confidence, vs.riv_sample_size, vs.sales_count, vs.latest_sale_usd, vs.change_30d, vs.change_1y from asset_variants v left join variant_stats vs on vs.variant_id = v.id where v.asset_id = ${a.id as string} order by vs.sales_count desc nulls last limit 15`);106 const rest = { ...a };107 delete rest.id;108 return { ...rest, url: `/asset/${a.slug as string}`, variants };109 }110 case 'get_asset_sales': {111 const i = input as z.infer<typeof TOOL_SCHEMAS.get_asset_sales>;112 const a = await assetBySlug(i.slug);113 if (!a) return { error: 'asset not found' };114 const sales = await run(sql`select sale_date, price, currency, price_usd, grader, grade, condition, sale_type, source_id, source_url, auction_house from sales where asset_id = ${a.id as string} and status = 'valid' order by sale_date desc limit ${i.limit}`);115 return { asset: a.title, count: sales.length, sales };116 }117 case 'get_asset_listings': {118 const i = input as z.infer<typeof TOOL_SCHEMAS.get_asset_listings>;119 const a = await assetBySlug(i.slug);120 if (!a) return { error: 'asset not found' };121 const listings = await run(sql`select price, currency, price_usd, grader, grade, condition, seller, source_id, source_url, ends_at, discount_to_riv, listing_type from listings where asset_id = ${a.id as string} and availability = 'available' order by price_usd asc nulls last limit ${i.limit}`);122 return { asset: a.title, note: 'asking prices, not transactions', count: listings.length, listings };123 }124 case 'get_price_history': {125 const i = input as z.infer<typeof TOOL_SCHEMAS.get_price_history>;126 const a = await assetBySlug(i.slug);127 if (!a) return { error: 'asset not found' };128 const rows = await run(sql`select date, riv_usd, latest_sale_usd, median_usd, sales_count, volume_usd, listings_count from price_snapshots where asset_id = ${a.id as string} and variant_id = '' and date >= current_date - ${i.days}::int order by date`);129 return { asset: a.title, points: thin(rows, 120) };130 }131 case 'list_categories': {132 const i = input as z.infer<typeof TOOL_SCHEMAS.list_categories>;133 const rows = await run(sql`select c.slug, c.name, c.parent_slug, c.family_slug, c.level, c.phase, c.index_ticker, (select count(*)::int from assets a where a.category_slug = c.slug) as tracked_assets from categories c where c.active ${i.family ? sql`and (c.family_slug = ${i.family})` : sql`and c.level = 0`} order by c.sort_order`);134 return { count: rows.length, categories: rows };135 }136 case 'get_category_market': {137 const i = input as z.infer<typeof TOOL_SCHEMAS.get_category_market>;138 const cat = CATEGORIES.find((c) => c.slug === i.category);139 if (!cat) return { error: 'unknown category', hint: 'call list_categories' };140 const [snap] = await run(sql`select * from category_snapshots where category_slug = ${i.category} order by date desc limit 1`);141 const [counts] = await run(sql`select count(*)::int as tracked_assets, count(s.riv_usd)::int as valued_assets, coalesce(sum(s.sales_count),0)::int as sales_total, coalesce(sum(s.active_listings),0)::int as active_listings, percentile_cont(0.5) within group (order by s.riv_usd) as median_riv_usd from assets a left join asset_stats s on s.asset_id = a.id where true ${scope(i.category)}`);142 const gainers = await run(sql`select a.slug, a.title, s.riv_usd, s.change_30d, s.sales_count from assets a join asset_stats s on s.asset_id = a.id where s.change_30d is not null and s.riv_sample_size >= 3 ${scope(i.category)} order by s.change_30d desc limit 5`);143 const losers = await run(sql`select a.slug, a.title, s.riv_usd, s.change_30d, s.sales_count from assets a join asset_stats s on s.asset_id = a.id where s.change_30d is not null and s.riv_sample_size >= 3 ${scope(i.category)} order by s.change_30d asc limit 5`);144 const valuable = await run(sql`select a.slug, a.title, s.riv_usd, s.riv_confidence, s.sales_count from assets a join asset_stats s on s.asset_id = a.id where s.riv_usd is not null ${scope(i.category)} order by s.riv_usd desc limit 5`);145 return { category: { slug: cat.slug, name: cat.name, index: cat.index }, snapshot: snap ?? null, counts, gainers, losers, most_valuable: valuable, url: `/markets/${cat.slug}` };146 }147 case 'get_index': {148 const i = input as z.infer<typeof TOOL_SCHEMAS.get_index>;149 const t = i.ticker.toUpperCase();150 const [idx] = await run(sql`select i.ticker, i.name, i.description, i.methodology, i.weighting, i.base_date, i.base_value, i.min_constituents from indices i where i.ticker = ${t}`);151 if (!idx) return { error: 'unknown index' };152 const [latest] = await run(sql`select v.* from index_values v join indices i on i.id = v.index_id where i.ticker = ${t} order by v.date desc limit 1`);153 if (!latest) return { ...idx, published: false, note: 'Index not yet published: not enough priced constituents.' };154 const change = async (days: number) => {155 const [p] = await run(sql`select value from index_values v join indices i on i.id = v.index_id where i.ticker = ${t} and v.date <= ${latest.date as string}::date - ${days}::int order by v.date desc limit 1`);156 return p && Number(p.value) > 0 ? (Number(latest.value) - Number(p.value)) / Number(p.value) : null;157 };158 return { ...idx, published: true, as_of: latest.date, value: latest.value, constituents: latest.constituents_count, transactions: latest.transactions, volume_usd: latest.volume_usd, median_sale_usd: latest.median_sale_usd, market_cap_est_usd: latest.market_cap_est_usd, market_cap_confidence: latest.market_cap_confidence, liquidity_score: latest.liquidity_score, momentum: latest.momentum, change_1d: await change(1), change_7d: await change(7), change_30d: await change(30), change_1y: await change(365), url: `/rareindex/${t}` };159 }160 case 'get_index_history': {161 const i = input as z.infer<typeof TOOL_SCHEMAS.get_index_history>;162 const rows = await run(sql`select v.date, v.value, v.transactions, v.volume_usd from index_values v join indices i on i.id = v.index_id where i.ticker = ${i.ticker.toUpperCase()} and v.date >= current_date - ${i.days}::int order by v.date`);163 return { ticker: i.ticker.toUpperCase(), points: thin(rows, 120) };164 }165 case 'top_movers': {166 const i = input as z.infer<typeof TOOL_SCHEMAS.top_movers>;167 const col = sql.raw(`s.${WINDOWS[i.window as Window]}`);168 const order = i.direction === 'gainers' ? sql`desc` : sql`asc`;169 const rows = await run(sql`select ${ASSET_COLS} from assets a join asset_stats s on s.asset_id = a.id where ${col} is not null and s.riv_sample_size >= ${i.min_sales} ${scope(i.category)} order by ${col} ${order} limit ${i.limit}`);170 return { window: i.window, direction: i.direction, count: rows.length, results: rows };171 }172 case 'compare': {173 const i = input as z.infer<typeof TOOL_SCHEMAS.compare>;174 const days = { '7d': 7, '30d': 30, '90d': 90, '1y': 365 }[i.window as Window];175 const out: Row[] = [];176 for (const item of i.items) {177 const up = item.toUpperCase();178 if (up === 'RARE' || up.startsWith('RARE-')) {179 const [end] = await run(sql`select v.date, v.value from index_values v join indices x on x.id = v.index_id where x.ticker = ${up} order by v.date desc limit 1`);180 const [start] = end ? await run(sql`select v.date, v.value from index_values v join indices x on x.id = v.index_id where x.ticker = ${up} and v.date <= ${end.date as string}::date - ${days}::int order by v.date desc limit 1`) : [];181 out.push({ item: up, type: 'index', start_date: start?.date ?? null, start_value: start?.value ?? null, end_date: end?.date ?? null, end_value: end?.value ?? null, return: ret(start?.value, end?.value) });182 continue;183 }184 const cat = CATEGORIES.find((c) => c.slug === item);185 if (cat) {186 const [end] = await run(sql`select date, index_value, median_sale_usd from category_snapshots where category_slug = ${item} and index_value is not null order by date desc limit 1`);187 const [start] = end ? await run(sql`select date, index_value from category_snapshots where category_slug = ${item} and index_value is not null and date <= ${end.date as string}::date - ${days}::int order by date desc limit 1`) : [];188 out.push({ item, type: 'category', start_date: start?.date ?? null, start_value: start?.index_value ?? null, end_date: end?.date ?? null, end_value: end?.index_value ?? null, return: ret(start?.index_value, end?.index_value) });189 continue;190 }191 const a = await assetBySlug(item);192 if (!a) {193 out.push({ item, type: 'unknown', error: 'not found' });194 continue;195 }196 const [end] = await run(sql`select date, riv_usd from price_snapshots where asset_id = ${a.id as string} and variant_id = '' and riv_usd is not null order by date desc limit 1`);197 const [start] = end ? await run(sql`select date, riv_usd from price_snapshots where asset_id = ${a.id as string} and variant_id = '' and riv_usd is not null and date <= ${end.date as string}::date - ${days}::int order by date desc limit 1`) : [];198 out.push({ item, type: 'asset', title: a.title, start_date: start?.date ?? null, start_value: start?.riv_usd ?? null, end_date: end?.date ?? null, end_value: end?.riv_usd ?? null, return: ret(start?.riv_usd, end?.riv_usd), riv_usd: a.riv_usd, sales_count: a.sales_count });199 }200 return { window: i.window, items: out, note: 'return = null means insufficient history for the window' };201 }202 case 'screen': {203 const i = input as z.infer<typeof TOOL_SCHEMAS.screen>;204 const col = sql.raw(`s.${WINDOWS[i.window as Window]}`);205 const sortCol = { change: col, value: sql`s.riv_usd`, liquidity: sql`s.liquidity_score`, sales: sql`s.sales_count`, trending: sql`s.trending_score`, opportunity: sql`s.value_opportunity` }[i.sort as string] ?? col;206 const sortDir = i.sort === 'opportunity' ? sql`ASC NULLS LAST` : sql`DESC NULLS LAST`; // opportunity: most below RIV first207 const rows = await run(sql`select ${ASSET_COLS} from assets a join asset_stats s on s.asset_id = a.id where s.riv_sample_size >= ${i.min_sales} ${scope(i.category)}208 ${i.min_price_usd != null ? sql`and s.riv_usd >= ${i.min_price_usd}` : sql``} ${i.max_price_usd != null ? sql`and s.riv_usd <= ${i.max_price_usd}` : sql``}209 ${i.min_change != null ? sql`and ${col} >= ${i.min_change}` : sql``} ${i.max_change != null ? sql`and ${col} <= ${i.max_change}` : sql``}210 ${i.max_sales_1y != null ? sql`and s.sales_1y <= ${i.max_sales_1y}` : sql``} ${i.min_liquidity != null ? sql`and s.liquidity_score >= ${i.min_liquidity}` : sql``}211 order by ${sortCol} ${sortDir} limit ${i.limit}`);212 return { filters: i, count: rows.length, results: rows };213 }214 case 'record_sales': {215 const i = input as z.infer<typeof TOOL_SCHEMAS.record_sales>;216 const rows = await run(sql`select a.slug, a.title, a.category_slug, sa.sale_date, sa.price, sa.currency, sa.price_usd, sa.grader, sa.grade, sa.source_id, sa.source_url, sa.auction_house from sales sa join assets a on a.id = sa.asset_id where sa.status = 'valid' and sa.confidence >= 0.8 and sa.is_bundle = false ${scope(i.category)} order by sa.price_usd desc limit ${i.limit}`);217 return { count: rows.length, sales: rows };218 }219 case 'radar': {220 const i = input as z.infer<typeof TOOL_SCHEMAS.radar>;221 const rows = await run(sql`select r.kind, r.score, r.evidence, r.detected_at, a.slug, a.title from radar_findings r join assets a on a.id = r.asset_id where (r.expires_at is null or r.expires_at > now()) ${i.kind ? sql`and r.kind = ${i.kind}` : sql``} order by r.detected_at desc limit ${i.limit}`);222 return { count: rows.length, findings: rows };223 }224 case 'platform_coverage': {225 const [r] = await run(sql`select (select count(*)::int from assets) as assets, (select count(*)::int from assets a join asset_stats s on s.asset_id=a.id where s.riv_usd is not null) as valued_assets, (select count(*)::int from sales where status='valid') as sales, (select max(sale_date) from sales) as latest_sale, (select count(*)::int from listings where availability='available') as listings, (select count(*)::int from sources where active) as sources, (select max(date) from index_values) as latest_index_date`);226 const families = await run(sql`select a.family_slug, count(*)::int as assets, coalesce(sum(s.sales_count),0)::int as sales from assets a left join asset_stats s on s.asset_id = a.id group by a.family_slug order by assets desc`);227 return { ...r, families };228 }229 }230 return { error: 'unhandled' };231}232233function ret(start: unknown, end: unknown): number | null {234 const s = Number(start);235 const e = Number(end);236 return Number.isFinite(s) && Number.isFinite(e) && s > 0 ? (e - s) / s : null;237}238239/** Keep tool payloads small: at most n evenly spaced points. */240function thin<T>(rows: T[], n: number): T[] {241 if (rows.length <= n) return rows;242 const step = rows.length / n;243 const out: T[] = [];244 for (let i = 0; i < n; i++) out.push(rows[Math.floor(i * step)]!);245 if (out[out.length - 1] !== rows[rows.length - 1]) out.push(rows[rows.length - 1]!);246 return out;247}248249/** One-line summary of a tool result for the transparent trace shown to users. */250export function summarizeToolResult(name: string, output: unknown): string {251 if (!output || typeof output !== 'object') return '';252 const o = output as Record<string, unknown>;253 if (o.error) return `error: ${String(o.error)}`;254 if (typeof o.count === 'number') return `${o.count} rows`;255 if (Array.isArray(o.points)) return `${o.points.length} points`;256 if (Array.isArray(o.items)) return `${o.items.length} items`;257 if (name === 'get_asset' && o.title) return String(o.title);258 if (name === 'get_index') return o.published ? `value ${o.value}` : 'not published';259 return 'ok';260}261