TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import { and, eq, gte, inArray, sql } from 'drizzle-orm';2import { assetStats, assets, categories, categorySnapshots, correlations, indexConstituents, indexValues, indices, listings, populationReports, priceSnapshots, radarFindings, sales, variantStats } from '@rareindex/database';3import { chainLinkedIndex, correlationMatrix, horizonReturns, type ConstituentSeries } from '@rareindex/indices';4import { logger, median, newId, round, toDateOnly } from '@rareindex/shared';5import { db } from '../lib/db.ts';6import { emit } from '../lib/events.ts';78const log = logger.child({ component: 'indices' });9const DAY = 86_400_000;1011interface Constituent {12 assetId: string;13 variantId: string;14 familySlug: string;15 categorySlug: string;16 sales12m: number;17 confidence: number;18}1920/** Eligible constituents: valuation confidence ≥ 0.4 and ≥ 3 valid sales in the last 12 months; one variant per asset (most sales). */21async function eligibleConstituents(now: Date): Promise<Constituent[]> {22 const since = new Date(now.getTime() - 365 * DAY);23 const rows = await db()24 .select({ assetId: variantStats.assetId, variantId: variantStats.variantId, confidence: variantStats.rivConfidence, familySlug: assets.familySlug, categorySlug: assets.categorySlug, sales12m: sql<number>`(select count(*)::int from sales s where s.variant_id = ${variantStats.variantId} and s.status = 'valid' and s.sale_date >= ${since.toISOString()}::timestamptz)` })25 .from(variantStats)26 .innerJoin(assets, eq(assets.id, variantStats.assetId))27 .where(and(sql`${variantStats.rivConfidence} >= 0.4`, sql`${variantStats.rivUsd} is not null`));28 const best = new Map<string, Constituent>();29 for (const r of rows) {30 if (r.sales12m < 3) continue;31 const c: Constituent = { assetId: r.assetId, variantId: r.variantId, familySlug: r.familySlug, categorySlug: r.categorySlug, sales12m: r.sales12m, confidence: Number(r.confidence) };32 const prev = best.get(r.assetId);33 if (!prev || c.sales12m > prev.sales12m) best.set(r.assetId, c);34 }35 return [...best.values()];36}3738async function seriesFor(cons: Constituent[], baseDate: string): Promise<ConstituentSeries[]> {39 if (!cons.length) return [];40 const out: ConstituentSeries[] = [];41 for (let i = 0; i < cons.length; i += 200) {42 const chunk = cons.slice(i, i + 200);43 const rows = await db()44 .select({ variantId: priceSnapshots.variantId, date: priceSnapshots.date, value: priceSnapshots.rivUsd })45 .from(priceSnapshots)46 .where(and(inArray(priceSnapshots.variantId, chunk.map((c) => c.variantId)), gte(priceSnapshots.date, baseDate), sql`${priceSnapshots.rivUsd} is not null`))47 .orderBy(priceSnapshots.variantId, priceSnapshots.date);48 const byV = new Map<string, Array<{ date: string; value: number }>>();49 for (const r of rows) byV.set(r.variantId, [...(byV.get(r.variantId) ?? []), { date: r.date, value: Number(r.value) }]);50 for (const c of chunk) out.push({ id: c.variantId, points: byV.get(c.variantId) ?? [], weight: c.sales12m });51 }52 return out;53}5455interface DayStats {56 transactions: number;57 volume: number | null;58 medianSale: number | null;59 avgSale: number | null;60}61async function dailySaleStats(variantIds: string[], dates: string[]): Promise<Map<string, DayStats>> {62 const m = new Map<string, DayStats>();63 if (!variantIds.length || !dates.length) return m;64 const rows = await db().execute(sql`65 select to_char(sale_date at time zone 'UTC', 'YYYY-MM-DD') as d, count(*)::int as n, sum(coalesce(all_in_usd, price_usd))::float as vol,66 percentile_cont(0.5) within group (order by coalesce(all_in_usd, price_usd))::float as med, avg(coalesce(all_in_usd, price_usd))::float as avg67 from sales where status = 'valid' and variant_id in ${variantIds} and sale_date >= ${dates[0]}::date68 group by 1`);69 for (const r of rows as unknown as Array<{ d: string; n: number; vol: number; med: number; avg: number }>) m.set(r.d, { transactions: r.n, volume: r.vol, medianSale: r.med, avgSale: r.avg });70 return m;71}7273/** Market cap estimate (§124): Σ population × RIV where population is known; confidence by coverage. */74async function marketCap(assetIds: string[]): Promise<{ value: number | null; confidence: string }> {75 if (!assetIds.length) return { value: null, confidence: 'insufficient' };76 const rows = await db().execute(sql`77 select coalesce(sum(p.total * s.riv_usd), 0)::float as cap, count(p.asset_id)::int as covered78 from asset_stats s79 left join lateral (select total, asset_id from population_reports pr where pr.asset_id = s.asset_id order by report_date desc limit 1) p on true80 where s.asset_id in ${assetIds} and s.riv_usd is not null`);81 const r = (rows as unknown as Array<{ cap: number; covered: number }>)[0];82 if (!r || r.covered === 0) return { value: null, confidence: 'insufficient' };83 const coverage = r.covered / assetIds.length;84 return { value: r.cap, confidence: coverage >= 0.8 ? 'medium' : coverage >= 0.3 ? 'low' : 'insufficient' };85}8687export async function runIndices(opts: { now?: Date } = {}): Promise<{ published: number; skipped: string[] }> {88 const now = opts.now ?? new Date();89 const defs = await db().select().from(indices).where(eq(indices.active, true));90 const cons = await eligibleConstituents(now);91 log.info({ constituents: cons.length }, 'eligible constituents');92 const published: string[] = [];93 const skipped: string[] = [];94 const subSeries: Record<string, Array<{ date: string; value: number; transactions: number; constituents: number }>> = {};95 const today = toDateOnly(now);9697 for (const def of defs.filter((d) => !d.isFlagship)) {98 const members = cons.filter((c) => def.familySlugs.includes(c.familySlug));99 const series = await seriesFor(members, def.baseDate);100 const points = chainLinkedIndex(series, { baseDate: def.baseDate, baseValue: def.baseValue, minConstituents: def.minConstituents, weighting: def.weighting === 'liquidity' ? 'liquidity' : 'equal' });101 await syncConstituents(def.id, members, today);102 if (!points.length) {103 skipped.push(`${def.ticker} (${members.length}/${def.minConstituents} constituents)`);104 continue;105 }106 const stats = await dailySaleStats(members.map((m) => m.variantId), points.map((p) => p.date));107 const cap = await marketCap(members.map((m) => m.assetId));108 const [tracked] = await db().select({ n: sql<number>`count(*)::int` }).from(assets).where(inArray(assets.familySlug, def.familySlugs));109 const [liq] = await db().select({ l: sql<number | null>`avg(${assetStats.liquidityScore})::float` }).from(assetStats).where(inArray(assetStats.assetId, members.map((m) => m.assetId)));110 const h = horizonReturns(points);111 const breadth = new Set(members.map((m) => m.categorySlug)).size;112 await db().delete(indexValues).where(eq(indexValues.indexId, def.id)); // series is fully recomputed each run113 const rows = points.map((p) => {114 const s = stats.get(p.date);115 return { indexId: def.id, date: p.date, value: p.value, constituentsCount: p.constituents, transactions: s?.transactions ?? 0, volumeUsd: s?.volume ?? null, medianSaleUsd: s?.medianSale ?? null, avgSaleUsd: s?.avgSale ?? null, marketCapEstUsd: p.date === points.at(-1)!.date ? cap.value : null, marketCapConfidence: p.date === points.at(-1)!.date ? cap.confidence : null, liquidityScore: liq?.l ?? null, momentum: p.date === points.at(-1)!.date ? h['30d'] : null, breadth, trackedAssets: tracked?.n ?? 0, coverage: p.coverage };116 });117 for (let i = 0; i < rows.length; i += 500) {118 await db()119 .insert(indexValues)120 .values(rows.slice(i, i + 500))121 .onConflictDoUpdate({ target: [indexValues.indexId, indexValues.date], set: { value: sql`excluded.value`, constituentsCount: sql`excluded.constituents_count`, transactions: sql`excluded.transactions`, volumeUsd: sql`excluded.volume_usd`, medianSaleUsd: sql`excluded.median_sale_usd`, avgSaleUsd: sql`excluded.avg_sale_usd`, marketCapEstUsd: sql`excluded.market_cap_est_usd`, marketCapConfidence: sql`excluded.market_cap_confidence`, liquidityScore: sql`excluded.liquidity_score`, momentum: sql`excluded.momentum`, breadth: sql`excluded.breadth`, trackedAssets: sql`excluded.tracked_assets`, coverage: sql`excluded.coverage` } });122 }123 subSeries[def.ticker] = rows.map((r) => ({ date: r.date, value: r.value, transactions: r.transactions, constituents: r.constituentsCount }));124 published.push(def.ticker);125 await emit('index_updated', { type: 'index', id: def.id }, { ticker: def.ticker, value: points.at(-1)!.value, date: points.at(-1)!.date, constituents: points.at(-1)!.constituents });126 }127128 // RARE: transaction-weighted average of subindex daily log returns129 const flagship = defs.find((d) => d.isFlagship);130 if (flagship) {131 const tickers = Object.keys(subSeries);132 // The flagship publishes only when its own minimum (25 distinct priced constituents across the133 // published subindices, as stated on /rareindex) is met — not as soon as one subindex exists.134 const flagshipConstituents = tickers.reduce((a, t) => a + (subSeries[t]!.at(-1)?.constituents ?? 0), 0);135 if (tickers.length >= 1 && flagshipConstituents >= flagship.minConstituents) {136 const dates = [...new Set(tickers.flatMap((t) => subSeries[t]!.map((p) => p.date)))].sort();137 const last = new Map<string, number>();138 let level = flagship.baseValue;139 const rows: Array<typeof indexValues.$inferInsert> = [];140 let started = false;141 for (const date of dates) {142 let num = 0;143 let den = 0;144 let cnt = 0;145 let tx = 0;146 for (const t of tickers) {147 const p = subSeries[t]!.find((x) => x.date === date);148 if (!p) continue;149 cnt++;150 tx += p.transactions;151 const prev = last.get(t);152 if (prev) {153 const w = 1 + p.transactions;154 num += Math.log(p.value / prev) * w;155 den += w;156 }157 last.set(t, p.value);158 }159 if (!started) {160 started = true;161 rows.push({ indexId: flagship.id, date, value: level, constituentsCount: cnt, transactions: tx, breadth: cnt, coverage: cnt / Math.max(1, defs.length - 1) });162 continue;163 }164 if (den > 0) level = level * Math.exp(num / den);165 rows.push({ indexId: flagship.id, date, value: round(level, 4), constituentsCount: cnt, transactions: tx, breadth: cnt, coverage: cnt / Math.max(1, defs.length - 1) });166 }167 await db().delete(indexValues).where(eq(indexValues.indexId, flagship.id));168 for (let i = 0; i < rows.length; i += 500) {169 await db().insert(indexValues).values(rows.slice(i, i + 500)).onConflictDoUpdate({ target: [indexValues.indexId, indexValues.date], set: { value: sql`excluded.value`, constituentsCount: sql`excluded.constituents_count`, transactions: sql`excluded.transactions`, breadth: sql`excluded.breadth`, coverage: sql`excluded.coverage` } });170 }171 published.push(flagship.ticker);172 } else skipped.push(tickers.length ? `RARE (${flagshipConstituents}/${flagship.minConstituents} constituents across ${tickers.length} subindices)` : 'RARE (no subindex published)');173 }174175 // correlations between subindices176 const corrSeries: Record<string, Array<{ date: string; value: number }>> = {};177 for (const [t, s] of Object.entries(subSeries)) corrSeries[t] = s.map((p) => ({ date: p.date, value: p.value }));178 for (const win of [30, 90, 365]) {179 const since = toDateOnly(new Date(now.getTime() - win * DAY));180 for (const c of correlationMatrix(corrSeries, since)) {181 await db().insert(correlations).values({ a: c.a, b: c.b, windowDays: win, coefficient: c.r, observations: c.n, computedAt: now }).onConflictDoUpdate({ target: [correlations.a, correlations.b, correlations.windowDays], set: { coefficient: c.r, observations: c.n, computedAt: now } });182 }183 }184 log.info({ published, skipped }, 'indices run done');185 return { published: published.length, skipped };186}187188async function syncConstituents(indexId: string, members: Constituent[], today: string): Promise<void> {189 const current = await db().select({ assetId: indexConstituents.assetId, variantId: indexConstituents.variantId, addedAt: indexConstituents.addedAt }).from(indexConstituents).where(and(eq(indexConstituents.indexId, indexId), sql`${indexConstituents.removedAt} is null`));190 const currentKeys = new Set(current.map((c) => `${c.assetId}|${c.variantId}`));191 const memberKeys = new Set(members.map((m) => `${m.assetId}|${m.variantId}`));192 for (const m of members) {193 if (currentKeys.has(`${m.assetId}|${m.variantId}`)) continue;194 await db().insert(indexConstituents).values({ indexId, assetId: m.assetId, variantId: m.variantId, weight: m.sales12m, addedAt: today, reason: `confidence ${m.confidence.toFixed(2)}, ${m.sales12m} sales/12m` }).onConflictDoNothing();195 }196 for (const c of current) {197 if (memberKeys.has(`${c.assetId}|${c.variantId}`)) continue;198 await db().update(indexConstituents).set({ removedAt: today }).where(and(eq(indexConstituents.indexId, indexId), eq(indexConstituents.assetId, c.assetId), eq(indexConstituents.variantId, c.variantId), eq(indexConstituents.addedAt, c.addedAt)));199 }200}201202/** Category snapshots (§125, §151) for every category with tracked assets. */203export async function runCategorySnapshots(opts: { now?: Date } = {}): Promise<number> {204 const now = opts.now ?? new Date();205 const today = toDateOnly(now);206 const since30 = new Date(now.getTime() - 30 * DAY);207 const cats = await db().select({ slug: categories.slug, level: categories.level, family: categories.familySlug }).from(categories);208 const cons = await eligibleConstituents(now);209 let n = 0;210 for (const cat of cats) {211 const scope = cat.level === 0 ? eq(assets.familySlug, cat.slug) : eq(assets.categorySlug, cat.slug);212 const [agg] = await db()213 .select({ tracked: sql<number>`count(*)::int`, valued: sql<number>`count(s.riv_usd)::int` })214 .from(assets)215 .leftJoin(sql`asset_stats s`, sql`s.asset_id = ${assets.id}`)216 .where(scope);217 if (!agg || agg.tracked === 0) continue;218 const [sal] = await db()219 .select({ n: sql<number>`count(*)::int`, vol: sql<number | null>`sum(${sales.priceUsd})::float`, med: sql<number | null>`percentile_cont(0.5) within group (order by ${sales.priceUsd})::float` })220 .from(sales)221 .innerJoin(assets, eq(assets.id, sales.assetId))222 .where(and(scope, eq(sales.status, 'valid'), gte(sales.saleDate, since30)));223 const [lst] = await db().select({ n: sql<number>`count(*)::int` }).from(listings).innerJoin(assets, eq(assets.id, listings.assetId)).where(and(scope, eq(listings.availability, 'available')));224 const [liq] = await db().select({ l: sql<number | null>`avg(s.liquidity_score)::float` }).from(assets).innerJoin(sql`asset_stats s`, sql`s.asset_id = ${assets.id}`).where(scope);225 const members = cons.filter((c) => (cat.level === 0 ? c.familySlug === cat.slug : c.categorySlug === cat.slug));226 let indexValue: number | null = null;227 let ch: Record<string, number | null> = {};228 if (members.length >= 5) {229 const series = await seriesFor(members, '2024-01-01');230 const pts = chainLinkedIndex(series, { baseDate: '2024-01-01', baseValue: 1000, minConstituents: 5 });231 if (pts.length) {232 indexValue = pts.at(-1)!.value;233 ch = horizonReturns(pts);234 }235 }236 const idsForCap = (await db().select({ id: assets.id }).from(assets).where(scope).limit(5000)).map((r) => r.id);237 const cap = await marketCap(idsForCap);238 await db()239 .insert(categorySnapshots)240 .values({ categorySlug: cat.slug, date: today, indexValue, trackedAssets: agg.tracked, assetsWithValuation: agg.valued, sales: sal?.n ?? 0, volumeUsd: sal?.vol ?? null, medianSaleUsd: sal?.med ?? null, activeListings: lst?.n ?? 0, marketCapEstUsd: cap.value, liquidityScore: liq?.l ?? null, change1d: ch['1d'] ?? null, change7d: ch['7d'] ?? null, change30d: ch['30d'] ?? null, change1y: ch['1y'] ?? null })241 .onConflictDoUpdate({ target: [categorySnapshots.categorySlug, categorySnapshots.date], set: { indexValue, trackedAssets: agg.tracked, assetsWithValuation: agg.valued, sales: sal?.n ?? 0, volumeUsd: sal?.vol ?? null, medianSaleUsd: sal?.med ?? null, activeListings: lst?.n ?? 0, marketCapEstUsd: cap.value, liquidityScore: liq?.l ?? null, change1d: ch['1d'] ?? null, change7d: ch['7d'] ?? null, change30d: ch['30d'] ?? null, change1y: ch['1y'] ?? null } });242 n++;243 }244 log.info({ categories: n }, 'category snapshots done');245 return n;246}247248/** Rare Radar + record sales (§154–§155). Derived strictly from stored transactions/listings. */249export async function runRadar(opts: { now?: Date } = {}): Promise<number> {250 const now = opts.now ?? new Date();251 let n = 0;252 // record sales per category (all-time and 1y)253 const records = await db().execute(sql`254 select distinct on (a.category_slug) a.category_slug, s.id as sale_id, s.asset_id, s.price_usd::float as price, s.sale_date255 from sales s join assets a on a.id = s.asset_id where s.status = 'valid'256 order by a.category_slug, s.price_usd desc`);257 for (const r of records as unknown as Array<{ category_slug: string; sale_id: string; asset_id: string; price: number; sale_date: Date }>) {258 await db().insert(radarFindings).values({ id: newId('event'), assetId: r.asset_id, kind: 'record_sale', score: Math.log10(1 + r.price), evidence: { categorySlug: r.category_slug, priceUsd: r.price, saleDate: r.sale_date, scope: 'all_time' }, entityType: 'sale', entityId: r.sale_id, detectedAt: now }).onConflictDoUpdate({ target: [radarFindings.kind, radarFindings.entityType, radarFindings.entityId], set: { detectedAt: now, evidence: { categorySlug: r.category_slug, priceUsd: r.price, saleDate: r.sale_date, scope: 'all_time' } } });259 n++;260 }261 // price discrepancy: active listing ≥ 25% below RIV with confidence ≥ 0.6262 const cheap = await db().execute(sql`263 select l.id, l.asset_id, l.discount_to_riv::float as d, l.price_usd::float as ask, s.riv_usd::float as riv, s.riv_confidence::float as conf264 from listings l join asset_stats s on s.asset_id = l.asset_id265 where l.availability = 'available' and l.discount_to_riv <= -0.25 and l.discount_to_riv >= -0.5 and not ('riv_review' = any(l.flags)) and s.riv_confidence >= 0.6 and s.riv_sample_size >= 5 and l.price_usd > 0 limit 2000`);266 for (const r of cheap as unknown as Array<{ id: string; asset_id: string; d: number; ask: number; riv: number; conf: number }>) {267 await db().insert(radarFindings).values({ id: newId('event'), assetId: r.asset_id, kind: 'price_discrepancy', score: -r.d * r.conf, evidence: { askUsd: r.ask, rivUsd: r.riv, discount: r.d, confidence: r.conf }, entityType: 'listing', entityId: r.id, detectedAt: now, expiresAt: new Date(now.getTime() + 7 * DAY) }).onConflictDoUpdate({ target: [radarFindings.kind, radarFindings.entityType, radarFindings.entityId], set: { detectedAt: now, score: -r.d * r.conf, evidence: { askUsd: r.ask, rivUsd: r.riv, discount: r.d, confidence: r.conf } } });268 n++;269 }270 // auction lots whose buyer-pays bid is ≥ 10 % below the variant RIV (§33–§35), ending within 7 days.271 // Neutral wording (§41–§42): a gap between a bid and a valuation, never a statement about the seller.272 const lots = await db().execute(sql`273 select l.id, l.asset_id, l.bid_vs_riv::float as d, l.all_in_bid_usd::float as bid, l.riv_usd_at_assessment::float as riv, l.fee_basis, l.ends_at, l.url, au.auction_house,274 coalesce(vs.riv_confidence, st.riv_confidence)::float as conf275 from auction_lots l join auctions au on au.id = l.auction_id276 left join variant_stats vs on vs.variant_id = l.variant_id left join asset_stats st on st.asset_id = l.asset_id277 where l.status in ('live','upcoming') and l.assessment_verdict = 'deal' and l.bid_vs_riv is not null and l.all_in_bid_usd > 0278 and l.ends_at between now() and now() + interval '7 days' and coalesce(vs.riv_confidence, st.riv_confidence) >= 0.6279 limit 2000`);280 for (const r of lots as unknown as Array<{ id: string; asset_id: string; d: number; bid: number; riv: number; fee_basis: string | null; ends_at: Date; url: string; auction_house: string; conf: number }>) {281 const evidence = { allInBidUsd: r.bid, rivUsd: r.riv, bidVsRiv: r.d, feeBasis: r.fee_basis, endsAt: r.ends_at, house: r.auction_house, url: r.url };282 await db().insert(radarFindings).values({ id: newId('event'), assetId: r.asset_id, kind: 'auction_below_riv', score: -r.d * r.conf, evidence, entityType: 'auction_lot', entityId: r.id, detectedAt: now, expiresAt: new Date(r.ends_at) }).onConflictDoUpdate({ target: [radarFindings.kind, radarFindings.entityType, radarFindings.entityId], set: { detectedAt: now, score: -r.d * r.conf, evidence, expiresAt: new Date(r.ends_at) } });283 n++;284 }285 // first listing in years: asset with a new listing (first_seen last 7d) and no listing/sale in the prior 2 years286 const firsts = await db().execute(sql`287 select l.id, l.asset_id from listings l288 where l.first_seen_at >= now() - interval '7 days'289 and not exists (select 1 from listings l2 where l2.asset_id = l.asset_id and l2.id <> l.id and l2.first_seen_at >= now() - interval '2 years')290 and exists (select 1 from sales s where s.asset_id = l.asset_id and s.sale_date < now() - interval '2 years')291 and not exists (select 1 from sales s where s.asset_id = l.asset_id and s.sale_date >= now() - interval '2 years')292 limit 500`);293 for (const r of firsts as unknown as Array<{ id: string; asset_id: string }>) {294 await db().insert(radarFindings).values({ id: newId('event'), assetId: r.asset_id, kind: 'first_listing_in_years', score: 1, evidence: {}, entityType: 'listing', entityId: r.id, detectedAt: now, expiresAt: new Date(now.getTime() + 14 * DAY) }).onConflictDoNothing();295 n++;296 }297 // ultra-low population: latest population report total ≤ 10 with a valuation298 const lowPop = await db()299 .select({ assetId: populationReports.assetId, total: populationReports.total })300 .from(populationReports)301 .where(sql`${populationReports.total} <= 10 and ${populationReports.reportDate} = (select max(report_date) from population_reports p2 where p2.asset_id = ${populationReports.assetId})`)302 .limit(1000);303 for (const r of lowPop) {304 await db().insert(radarFindings).values({ id: newId('event'), assetId: r.assetId, kind: 'ultra_low_population', score: 1 / (1 + r.total), evidence: { population: r.total }, entityType: 'asset', entityId: r.assetId, detectedAt: now }).onConflictDoUpdate({ target: [radarFindings.kind, radarFindings.entityType, radarFindings.entityId], set: { detectedAt: now, evidence: { population: r.total } } });305 n++;306 }307 log.info({ findings: n }, 'radar done');308 return n;309}310311export { median };312