spb/rareindex

1import { sql } from 'drizzle-orm';2import { benchmarks } from '@rareindex/database';3import { logger } from '@rareindex/shared';4import { db } from './lib/db.ts';56/**7 * Benchmark series (§187) from free public datasets:8 *  - SPX, XAUUSD, BTCUSD: stooq.com daily CSV (public quotes archive)9 *  - CPIAUCSL: FRED CSV export (no API key required)10 * Sources that fail are logged and left empty — never approximated.11 */12const log = logger.child({ component: 'benchmarks' });13const UA = { 'user-agent': 'RareIndexBot/0.1 (benchmark sync)' };1415async function stooq(symbol: string, ticker: string): Promise<number> {16  const res = await fetch(`https://stooq.com/q/d/l/?s=${encodeURIComponent(symbol)}&i=d`, { headers: UA });17  if (!res.ok) throw new Error(`stooq ${symbol} HTTP ${res.status}`);18  const text = await res.text();19  const lines = text.trim().split('\n');20  if (lines.length < 2 || !lines[0]!.toLowerCase().startsWith('date')) throw new Error(`stooq ${symbol}: unexpected payload (${lines[0]?.slice(0, 40)})`);21  const rows: Array<typeof benchmarks.$inferInsert> = [];22  for (const line of lines.slice(1)) {23    const [date, , , , close] = line.split(',');24    const v = Number(close);25    if (date && /^\d{4}-\d{2}-\d{2}$/.test(date) && Number.isFinite(v) && v > 0) rows.push({ ticker, date, value: v, source: 'stooq' });26  }27  await upsert(rows);28  return rows.length;29}3031async function fred(seriesId: string, ticker: string): Promise<number> {32  const res = await fetch(`https://fred.stlouisfed.org/graph/fredgraph.csv?id=${seriesId}`, { headers: UA });33  if (!res.ok) throw new Error(`fred ${seriesId} HTTP ${res.status}`);34  const text = await res.text();35  const rows: Array<typeof benchmarks.$inferInsert> = [];36  for (const line of text.trim().split('\n').slice(1)) {37    const [date, val] = line.split(',');38    const v = Number(val);39    if (date && Number.isFinite(v) && val !== '.') rows.push({ ticker, date, value: v, source: 'fred' });40  }41  await upsert(rows);42  return rows.length;43}4445async function upsert(rows: Array<typeof benchmarks.$inferInsert>): Promise<void> {46  for (let i = 0; i < rows.length; i += 1000) {47    await db().insert(benchmarks).values(rows.slice(i, i + 1000)).onConflictDoUpdate({ target: [benchmarks.ticker, benchmarks.date], set: { value: sql`excluded.value` } });48  }49}5051export async function syncBenchmarks(): Promise<Record<string, number | string>> {52  const out: Record<string, number | string> = {};53  // stooq first (long history); FRED as fallback (S&P 500 / NASDAQ / Coinbase BTC published by FRED, ~10y)54  const withFallback = (primary: () => Promise<number>, fallback: () => Promise<number>) => async () => {55    try {56      return await primary();57    } catch (err) {58      log.warn({ err }, 'primary benchmark source failed; trying fallback');59      return fallback();60    }61  };62  const jobs: Array<[string, () => Promise<number>]> = [63    ['SPX', withFallback(() => stooq('^spx', 'SPX'), () => fred('SP500', 'SPX'))],64    ['NDX', withFallback(() => stooq('^ndx', 'NDX'), () => fred('NASDAQCOM', 'NDX'))],65    ['GOLD', () => stooq('xauusd', 'GOLD')],66    ['BTC', withFallback(() => stooq('btcusd', 'BTC'), () => fred('CBBTCUSD', 'BTC'))],67    ['CPI', () => fred('CPIAUCSL', 'CPI')],68    ['CASE_SHILLER', () => fred('CSUSHPINSA', 'CASE_SHILLER')],69  ];70  for (const [name, fn] of jobs) {71    try {72      out[name] = await fn();73      log.info({ benchmark: name, rows: out[name] }, 'benchmark synced');74    } catch (err) {75      out[name] = `unavailable: ${err instanceof Error ? err.message : String(err)}`;76      log.warn({ err, benchmark: name }, 'benchmark unavailable');77    }78  }79  return out;80}81