TypeScript 61.9%
HTML 37.2%
SQL 0.7%
1import 'server-only';2import { cache } from 'react';3import { maxDrawdown, volatility, pctChange } from '@rareindex/shared';4import { rows, one, sql, num, int, str } from './_util';56export interface IndexRow {7 id: string;8 ticker: string;9 name: string;10 description: string | null;11 color: string | null;12 isFlagship: boolean;13 parentTicker: string | null;14 familySlugs: string[];15 categorySlugs: string[];16 minConstituents: number;17 baseDate: string;18 baseValue: number;19 methodology: string;20 weighting: string;21 latest: IndexPoint | null;22 change1d: number | null;23 change7d: number | null;24 change30d: number | null;25 changeYtd: number | null;26 change1y: number | null;27 /** priced constituents today (from index_constituents active) */28 constituents: number;29 pricedAssets: number;30 firstDate: string | null;31 points: number;32}3334export interface IndexPoint {35 date: string;36 value: number;37 constituentsCount: number;38 transactions: number;39 volumeUsd: number | null;40 medianSaleUsd: number | null;41 avgSaleUsd: number | null;42 marketCapEstUsd: number | null;43 marketCapConfidence: string | null;44 liquidityScore: number | null;45 momentum: number | null;46 breadth: number | null;47 trackedAssets: number | null;48 coverage: number | null;49}5051function toPoint(x: Record<string, unknown>): IndexPoint {52 return {53 date: String(x.date),54 value: Number(x.value),55 constituentsCount: int(x.constituents_count),56 transactions: int(x.transactions),57 volumeUsd: num(x.volume_usd),58 medianSaleUsd: num(x.median_sale_usd),59 avgSaleUsd: num(x.avg_sale_usd),60 marketCapEstUsd: num(x.market_cap_est_usd),61 marketCapConfidence: str(x.market_cap_confidence),62 liquidityScore: num(x.liquidity_score),63 momentum: num(x.momentum),64 breadth: num(x.breadth),65 trackedAssets: num(x.tracked_assets),66 coverage: num(x.coverage),67 };68}6970/** All indices with latest value and window changes computed from index_values. */71export const listIndices = cache(async (): Promise<IndexRow[]> => {72 const r = await rows<Record<string, unknown>>(sql`73 WITH latest AS (74 SELECT DISTINCT ON (index_id) * FROM index_values ORDER BY index_id, date DESC75 ), agg AS (76 SELECT index_id, min(date) AS first_date, count(*) AS points FROM index_values GROUP BY index_id77 ), cons AS (78 SELECT index_id, count(*) AS n FROM index_constituents WHERE removed_at IS NULL GROUP BY index_id79 )80 SELECT i.*, to_jsonb(l.*) AS latest, g.first_date, coalesce(g.points, 0) AS points, coalesce(c.n, 0) AS constituents,81 (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= l.date - 1 ORDER BY v.date DESC LIMIT 1) AS v_1d,82 (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= l.date - 7 ORDER BY v.date DESC LIMIT 1) AS v_7d,83 (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= l.date - 30 ORDER BY v.date DESC LIMIT 1) AS v_30d,84 (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= date_trunc('year', l.date)::date ORDER BY v.date DESC LIMIT 1) AS v_ytd,85 (SELECT value FROM index_values v WHERE v.index_id = i.id AND v.date <= l.date - 365 ORDER BY v.date DESC LIMIT 1) AS v_1y,86 (SELECT count(*) FROM assets a JOIN asset_stats s ON s.asset_id = a.id WHERE s.riv_usd IS NOT NULL AND (i.is_flagship OR a.family_slug = ANY(i.family_slugs))) AS priced_assets87 FROM indices i88 LEFT JOIN latest l ON l.index_id = i.id89 LEFT JOIN agg g ON g.index_id = i.id90 LEFT JOIN cons c ON c.index_id = i.id91 WHERE i.active92 ORDER BY i.is_flagship DESC, i.ticker93 `);94 return r.map((x) => {95 const latest = x.latest ? toPoint(x.latest as Record<string, unknown>) : null;96 return {97 id: String(x.id),98 ticker: String(x.ticker),99 name: String(x.name),100 description: str(x.description),101 color: str(x.color),102 isFlagship: Boolean(x.is_flagship),103 parentTicker: str(x.parent_ticker),104 familySlugs: (x.family_slugs as string[]) ?? [],105 categorySlugs: (x.category_slugs as string[]) ?? [],106 minConstituents: int(x.min_constituents),107 baseDate: String(x.base_date),108 baseValue: Number(x.base_value),109 methodology: String(x.methodology),110 weighting: String(x.weighting),111 latest,112 change1d: latest ? pctChange(num(x.v_1d), latest.value) : null,113 change7d: latest ? pctChange(num(x.v_7d), latest.value) : null,114 change30d: latest ? pctChange(num(x.v_30d), latest.value) : null,115 changeYtd: latest ? pctChange(num(x.v_ytd), latest.value) : null,116 change1y: latest ? pctChange(num(x.v_1y), latest.value) : null,117 constituents: int(x.constituents),118 pricedAssets: int(x.priced_assets),119 firstDate: str(x.first_date),120 points: int(x.points),121 };122 });123});124125export const getIndex = cache(async (ticker: string): Promise<IndexRow | null> => {126 const all = await listIndices();127 return all.find((i) => i.ticker.toLowerCase() === ticker.toLowerCase()) ?? null;128});129130export const getIndexSeries = cache(async (indexId: string, days: number | null = null): Promise<IndexPoint[]> => {131 const r = await rows<Record<string, unknown>>(sql`132 SELECT * FROM index_values WHERE index_id = ${indexId} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date ASC133 `);134 return r.map(toPoint);135});136137export interface IndexAnalytics {138 returns: Record<'1d' | '7d' | '30d' | 'ytd' | '1y' | '3y' | '5y' | '10y' | 'all', number | null>;139 volatility: number | null;140 maxDrawdown: number | null;141 high: { value: number; date: string } | null;142 low: { value: number; date: string } | null;143}144145export function analyzeSeries(points: IndexPoint[]): IndexAnalytics {146 const empty: IndexAnalytics = { returns: { '1d': null, '7d': null, '30d': null, ytd: null, '1y': null, '3y': null, '5y': null, '10y': null, all: null }, volatility: null, maxDrawdown: null, high: null, low: null };147 if (!points.length) return empty;148 const last = points[points.length - 1]!;149 const lastDate = new Date(`${last.date}T00:00:00Z`);150 const at = (daysBack: number): number | null => {151 const target = lastDate.getTime() - daysBack * 86_400_000;152 let best: IndexPoint | null = null;153 for (const p of points) {154 if (new Date(`${p.date}T00:00:00Z`).getTime() <= target) best = p;155 else break;156 }157 return best?.value ?? null;158 };159 const ytdStart = points.find((p) => p.date >= `${last.date.slice(0, 4)}-01-01`);160 const values = points.map((p) => p.value);161 let high = points[0]!;162 let low = points[0]!;163 for (const p of points) {164 if (p.value > high.value) high = p;165 if (p.value < low.value) low = p;166 }167 return {168 returns: {169 '1d': pctChange(at(1), last.value),170 '7d': pctChange(at(7), last.value),171 '30d': pctChange(at(30), last.value),172 ytd: ytdStart && ytdStart !== last ? pctChange(ytdStart.value, last.value) : null,173 '1y': pctChange(at(365), last.value),174 '3y': pctChange(at(365 * 3), last.value),175 '5y': pctChange(at(365 * 5), last.value),176 '10y': pctChange(at(365 * 10), last.value),177 all: points.length > 1 ? pctChange(points[0]!.value, last.value) : null,178 },179 volatility: volatility(values),180 maxDrawdown: maxDrawdown(values),181 high: { value: high.value, date: high.date },182 low: { value: low.value, date: low.date },183 };184}185186export interface ConstituentRow {187 assetId: string;188 slug: string;189 title: string;190 categorySlug: string;191 variantLabel: string | null;192 weight: number;193 addedAt: string;194 rivUsd: number | null;195 change30d: number | null;196 liquidityScore: number | null;197 salesCount: number;198}199200export const getIndexConstituents = cache(async (indexId: string, limit = 100): Promise<ConstituentRow[]> => {201 const r = await rows<Record<string, unknown>>(sql`202 SELECT c.asset_id, a.slug, a.title, a.category_slug, v.label AS variant_label, c.weight, c.added_at, s.riv_usd, s.change_30d, s.liquidity_score, coalesce(s.sales_count, 0) AS sales_count203 FROM index_constituents c JOIN assets a ON a.id = c.asset_id204 LEFT JOIN asset_variants v ON v.id = c.variant_id LEFT JOIN asset_stats s ON s.asset_id = a.id205 WHERE c.index_id = ${indexId} AND c.removed_at IS NULL206 ORDER BY c.weight DESC, s.riv_usd DESC NULLS LAST LIMIT ${limit}207 `);208 return r.map((x) => ({ assetId: String(x.asset_id), slug: String(x.slug), title: String(x.title), categorySlug: String(x.category_slug), variantLabel: str(x.variant_label), weight: Number(x.weight), addedAt: String(x.added_at), rivUsd: num(x.riv_usd), change30d: num(x.change_30d), liquidityScore: num(x.liquidity_score), salesCount: int(x.sales_count) }));209});210211export const getCorrelations = cache(async (ticker: string): Promise<Array<{ other: string; coefficient: number; observations: number; windowDays: number }>> => {212 const r = await rows<Record<string, unknown>>(sql`213 SELECT CASE WHEN a = ${ticker} THEN b ELSE a END AS other, coefficient, observations, window_days FROM correlations WHERE a = ${ticker} OR b = ${ticker} ORDER BY window_days, other214 `);215 return r.map((x) => ({ other: String(x.other), coefficient: Number(x.coefficient), observations: int(x.observations), windowDays: int(x.window_days) }));216});217218export const getCorrelationMatrix = cache(async (windowDays = 365): Promise<{ tickers: string[]; matrix: Record<string, Record<string, number>> }> => {219 const r = await rows<Record<string, unknown>>(sql`SELECT a, b, coefficient FROM correlations WHERE window_days = ${windowDays}`);220 const tickers = new Set<string>();221 const matrix: Record<string, Record<string, number>> = {};222 for (const x of r) {223 const a = String(x.a);224 const b = String(x.b);225 tickers.add(a);226 tickers.add(b);227 (matrix[a] ??= {})[b] = Number(x.coefficient);228 (matrix[b] ??= {})[a] = Number(x.coefficient);229 }230 return { tickers: [...tickers].sort(), matrix };231});232233export const getBenchmarkSeries = cache(async (ticker: string, days: number | null = null): Promise<Array<{ date: string; value: number; source: string }>> => {234 const r = await rows<Record<string, unknown>>(sql`SELECT date, value, source FROM benchmarks WHERE ticker = ${ticker} ${days ? sql`AND date >= current_date - ${days}::int` : sql``} ORDER BY date`);235 return r.map((x) => ({ date: String(x.date), value: Number(x.value), source: String(x.source) }));236});237238export const listBenchmarkTickers = cache(async (): Promise<Array<{ ticker: string; source: string; points: number; last: string }>> => {239 const r = await rows<Record<string, unknown>>(sql`SELECT ticker, min(source) AS source, count(*) AS points, max(date) AS last FROM benchmarks GROUP BY ticker ORDER BY ticker`);240 return r.map((x) => ({ ticker: String(x.ticker), source: String(x.source), points: int(x.points), last: String(x.last) }));241});242243export const getIndexLatestOnly = cache(async (): Promise<Record<string, { value: number; change1d: number | null; date: string }>> => {244 const all = await listIndices();245 const out: Record<string, { value: number; change1d: number | null; date: string }> = {};246 for (const i of all) if (i.latest) out[i.ticker] = { value: i.latest.value, change1d: i.change1d, date: i.latest.date };247 return out;248});249250export async function getIndexById(id: string): Promise<IndexRow | null> {251 const r = await one<Record<string, unknown>>(sql`SELECT ticker FROM indices WHERE id = ${id}`);252 return r ? getIndex(String(r.ticker)) : null;253}254