← back to Lifestyle Asset Intel
lib/valuation.js
827 lines
// valuation.js — v0 valuation engine with compositional confidence calc.
//
// This file is the contract that future ML replaces in v0.5+. It computes a
// blueprint-shaped { asset, latest_snapshot, comps, confidence_breakdown }
// response from the seeded transactions table using simple SQL aggregations.
//
// In v0.2 the constant-stub `confidence_breakdown` was replaced with a real
// compositional calculator (`computeBreakdown`) that weighs source quality,
// sample depth, comp recency, and authenticity/region/condition risks. The
// public response shape is unchanged — only the BODY of the calculation.
const { many, one } = require('./db');
// Tier-tier fallback weights when a source.weight column is unreachable.
const TIER_FALLBACK = { 1: 1.0, 2: 0.7, 3: 0.4, 4: 0.2 };
// Single source of truth for the methodology stamp emitted by /api/version,
// the audit log middleware, and any newly written valuation snapshots. Bump
// this whenever a confidence-breakdown component changes meaning, per
// METHODOLOGY § 7. v0-stub.1: comparable_similarity_weight became
// source-weight-aware (was constant 0.18).
const METHODOLOGY_VERSION = 'v0-stub.1';
async function valueAsset(slug) {
const asset = await one(
`SELECT ca.*, mf.name AS family_name, mf.slug AS family_slug,
b.name AS brand_name, b.slug AS brand_slug
FROM canonical_assets ca
JOIN model_families mf ON mf.id = ca.model_family_id
JOIN brands b ON b.id = mf.brand_id
WHERE ca.slug = $1`,
[slug]
);
if (!asset) return null;
const comps = await many(
`SELECT t.*, s.slug AS source_slug, s.name AS source_name,
s.tier AS source_tier, s.weight AS source_weight,
ro.payload->>'source_url' AS source_url
FROM transactions t
JOIN sources s ON s.id = t.source_id
LEFT JOIN raw_observations ro ON ro.id = t.raw_observation_id
WHERE t.canonical_asset_id = $1
ORDER BY t.transacted_at DESC
LIMIT 50`,
[asset.id]
);
const snapshot = await one(
`SELECT *
FROM valuation_snapshots
WHERE canonical_asset_id = $1
ORDER BY as_of DESC
LIMIT 1`,
[asset.id]
);
// Latest MSRP observation — Hermès doesn't sell online so MSRP is
// backfilled from boutique receipts / specialist trackers / auction
// commentary. Returns null if we have no authoritative data point.
const msrp = await one(
`SELECT msrp_usd, currency, observed_at, source
FROM msrp_observations
WHERE canonical_asset_id = $1
ORDER BY observed_at DESC
LIMIT 1`,
[asset.id]
);
// Build a tier→weight lookup from the joined comps; falls back to constants.
const sourceWeights = {};
for (const c of comps) {
if (c.source_weight != null) {
sourceWeights[c.source_tier] = parseFloat(c.source_weight);
}
}
const breakdown = computeBreakdown({
asset,
comps: comps.map((c) => ({
source_tier: c.source_tier,
source_weight: c.source_weight,
transacted_at: c.transacted_at,
condition_grade: c.condition_grade,
region: c.region
})),
snapshot,
sourceWeights
});
const confidence = clamp(
Object.values(breakdown).reduce((a, b) => a + b, 0),
0,
1
);
// Live-compute liquidity + expected_dts from comps in the trailing 24mo
// window. Mirrors the logic in listAssets() — see computeLiquidity below.
const recent = comps.filter((c) => {
if (!c.transacted_at) return false;
const t = new Date(c.transacted_at).getTime();
return isFinite(t) && (Date.now() - t) <= 24 * 30 * 86400000;
});
const grossPrices = recent
.map((c) => parseFloat(c.gross_transaction_price))
.filter(Number.isFinite)
.sort((a, b) => a - b);
const avg_gross = grossPrices.length
? grossPrices.reduce((a, b) => a + b, 0) / grossPrices.length
: null;
const spread = grossPrices.length >= 2
? quantile(grossPrices, 0.9) - quantile(grossPrices, 0.1)
: null;
const newest_comp = recent.length
? recent.reduce((acc, c) => {
const t = new Date(c.transacted_at).getTime();
return (acc === null || t > acc) ? t : acc;
}, null)
: null;
const liquidity_score = computeLiquidity({
n_comps: recent.length,
newest_comp,
avg_gross,
spread
});
const expected_dts = computeExpectedDts(liquidity_score);
// Premium-to-retail ratio — Sotheby's-style "2.4x retail" headline.
// Q50 / latest MSRP, rounded to 2 dp. Null if we have no MSRP.
const msrp_usd = msrp ? numberOrNull(msrp.msrp_usd) : null;
const q50 = snapshot ? numberOrNull(snapshot.q50) : null;
const premium_to_msrp = (msrp_usd != null && msrp_usd > 0 && q50 != null)
? +(q50 / msrp_usd).toFixed(2)
: null;
return {
asset: {
slug: asset.slug,
brand: asset.brand_name,
family: asset.family_name,
size: asset.size,
material: asset.material,
color: asset.color,
hardware: asset.hardware,
construction: asset.construction,
region: asset.region,
attrs: asset.attrs
},
latest_snapshot: snapshot ? {
as_of: snapshot.as_of,
q10: numberOrNull(snapshot.q10),
q50: numberOrNull(snapshot.q50),
q90: numberOrNull(snapshot.q90),
// liquidity + dts are recomputed from the live 24mo comp window —
// read-only enrichment, just like confidence below.
liquidity_score: liquidity_score,
expected_dts: expected_dts,
confidence: confidence,
auth_risk: numberOrNull(snapshot.auth_risk),
comp_count: snapshot.comp_count,
methodology_version: snapshot.methodology_version
} : null,
msrp: msrp ? {
msrp_usd,
currency: msrp.currency,
observed_at: msrp.observed_at,
source: msrp.source
} : null,
premium_to_msrp,
comps: comps.map((c) => ({
source: c.source_slug,
source_tier: c.source_tier,
source_url: c.source_url || null,
transacted_at: c.transacted_at,
gross: numberOrNull(c.gross_transaction_price),
net: numberOrNull(c.expected_net_seller_proceeds),
condition_grade: c.condition_grade,
condition_facets: c.condition_facets,
currency: c.currency,
region: c.region
})),
confidence_breakdown: breakdown
};
}
// computeBreakdown — pure function so tests can call it without booting Express.
//
// Inputs:
// asset: { region, ... } (canonical_assets row)
// comps: [{ source_tier, source_weight, transacted_at,
// condition_grade, region }, ...]
// snapshot: { auth_risk, ... } | null (valuation_snapshots row)
// sourceWeights: { [tier]: weight } (DB-derived; falls back
// to TIER_FALLBACK below)
//
// Returns the 9-key breakdown object. Sum is intentionally NOT clamped here —
// the caller clamps to [0,1] when assembling latest_snapshot.confidence.
function computeBreakdown({ asset, comps, snapshot, sourceWeights }) {
const compArr = Array.isArray(comps) ? comps : [];
const n = compArr.length;
const weights = sourceWeights || {};
// 1. source_quality_weight — weighted avg of source.weight across comps,
// scaled to a 0-0.30 contribution band.
let source_quality_weight = 0;
if (n > 0) {
let acc = 0;
for (const c of compArr) {
let w;
if (c.source_weight != null) {
w = parseFloat(c.source_weight);
} else if (weights[c.source_tier] != null) {
w = weights[c.source_tier];
} else {
w = TIER_FALLBACK[c.source_tier] != null ? TIER_FALLBACK[c.source_tier] : 0.2;
}
acc += isFinite(w) ? w : 0.2;
}
const avg = acc / n; // 0..1
source_quality_weight = clamp(avg * 0.30, 0, 0.30);
}
// 2. comparable_similarity_weight — source-weight-aware proxy for v0.
// Until real CLIP embeddings land in v0.3, the best signal we have for
// "how similar are these comps to the target" is how trusted the comp
// SOURCES are: a tier-1 auction-house lot describing the exact
// configuration is a tighter similarity signal than a tier-3 aggregator
// listing. We scale the 0.18 cap by the avg source weight already
// computed for source_quality_weight (re-derived here for purity),
// so a comp pool of all tier-1 lots scores the full 0.18, a mix of
// tier-1/2 scores ~0.15, and a tier-4 pool drops to ~0.04. Bumps
// methodology_version per METHODOLOGY § 7.
let comparable_similarity_weight = 0;
if (n > 0) {
let acc = 0;
for (const c of compArr) {
let w;
if (c.source_weight != null) {
w = parseFloat(c.source_weight);
} else if (weights[c.source_tier] != null) {
w = weights[c.source_tier];
} else {
w = TIER_FALLBACK[c.source_tier] != null ? TIER_FALLBACK[c.source_tier] : 0.2;
}
acc += isFinite(w) ? w : 0.2;
}
const avg = clamp(acc / n, 0, 1);
comparable_similarity_weight = clamp(avg * 0.18, 0, 0.18);
}
// 3. sample_depth_weight — log-saturating in comp count, capped at 20 comps.
const sample_depth_weight = clamp(
(Math.log1p(n) / Math.log1p(20)) * 0.20,
0,
0.20
);
// 4. recency_weight — newest comp's age in days; linear decay over a year.
let recency_weight = 0;
if (n > 0) {
let newest = -Infinity;
for (const c of compArr) {
if (!c.transacted_at) continue;
const t = new Date(c.transacted_at).getTime();
if (isFinite(t) && t > newest) newest = t;
}
if (isFinite(newest)) {
const daysOld = Math.max(0, (Date.now() - newest) / 86400000);
recency_weight = clamp(0.15 * (1 - daysOld / 365), 0, 0.15);
}
}
// 5. image_match_weight — constant for v0; needs image-intake pipeline.
// TODO v0.3 image-intake.
const image_match_weight = n > 0 ? 0.09 : 0;
// 6. authenticity_risk_penalty — half of the snapshot's stated auth_risk.
let authenticity_risk_penalty = 0;
if (snapshot && snapshot.auth_risk != null) {
const r = parseFloat(snapshot.auth_risk);
if (isFinite(r)) {
authenticity_risk_penalty = clamp(-1 * r * 0.5, -0.15, 0);
}
}
// 7. condition_uncertainty_penalty — penalize missing or weak grades.
let condition_uncertainty_penalty = 0;
if (n > 0) {
const anyMissing = compArr.some(
(c) => c.condition_grade === null || c.condition_grade === undefined
);
if (anyMissing) {
condition_uncertainty_penalty = -0.05;
} else {
const allHigh = compArr.every((c) => c.condition_grade >= 4);
condition_uncertainty_penalty = allHigh ? 0 : -0.02;
}
condition_uncertainty_penalty = clamp(condition_uncertainty_penalty, -0.10, 0);
}
// 8. region_gap_penalty — 0 if all comps match the asset's region.
let region_gap_penalty = 0;
if (n > 0 && asset && asset.region) {
const allSame = compArr.every((c) => c.region === asset.region);
region_gap_penalty = clamp(allSame ? 0 : -0.03, -0.10, 0);
}
// 9. fee_model_uncertainty_penalty — flat haircut until per-source fee
// schedules land in v0.4.
const fee_model_uncertainty_penalty = -0.01;
return {
source_quality_weight,
comparable_similarity_weight,
sample_depth_weight,
recency_weight,
image_match_weight,
authenticity_risk_penalty,
condition_uncertainty_penalty,
region_gap_penalty,
fee_model_uncertainty_penalty
};
}
async function listAssets(opts) {
// Joins each asset to its latest snapshot AND a 24-month transaction
// window for live-computed liquidity/dts. The snapshot's stored
// liquidity_score/expected_dts are constants in v0 seed data; we
// override with live values before returning so the grid sorts and
// filters reflect actual signal, not seed defaults.
//
// opts.q (string) — case-insensitive filter token. Matches
// against any of: slug, brand, family,
// size, material, color, hardware,
// construction.
// opts.sort (string) — see LIST_SORTS below. Sort happens in JS
// post-enrichment so computed columns
// (liquidity_score, expected_dts) sort
// against their live values, not seed.
const q = opts && typeof opts.q === 'string' ? opts.q.trim() : '';
const sort = opts && typeof opts.sort === 'string' ? opts.sort.trim() : '';
const params = [];
let whereSql = '';
if (q) {
params.push(`%${q.toLowerCase()}%`);
const i = '$' + params.length;
whereSql = `
WHERE LOWER(ca.slug) LIKE ${i}
OR LOWER(b.name) LIKE ${i}
OR LOWER(mf.name) LIKE ${i}
OR LOWER(COALESCE(ca.size,'')) LIKE ${i}
OR LOWER(COALESCE(ca.material,'')) LIKE ${i}
OR LOWER(COALESCE(ca.color,'')) LIKE ${i}
OR LOWER(COALESCE(ca.hardware,'')) LIKE ${i}
OR LOWER(COALESCE(ca.construction,'')) LIKE ${i}`;
}
const rows = await many(
`SELECT ca.id, ca.slug,
mf.name AS family_name, mf.slug AS family_slug,
b.name AS brand_name, b.slug AS brand_slug,
ca.size, ca.material, ca.color, ca.hardware, ca.region, ca.attrs,
vs.q10, vs.q50, vs.q90, vs.liquidity_score AS seed_liquidity_score,
vs.confidence, vs.expected_dts AS seed_expected_dts,
vs.comp_count, vs.as_of,
stats.n_comps,
stats.newest_comp,
stats.avg_gross,
stats.spread
FROM canonical_assets ca
JOIN model_families mf ON mf.id = ca.model_family_id
JOIN brands b ON b.id = mf.brand_id
LEFT JOIN LATERAL (
SELECT * FROM valuation_snapshots vs
WHERE vs.canonical_asset_id = ca.id
ORDER BY vs.as_of DESC LIMIT 1
) vs ON true
LEFT JOIN LATERAL (
SELECT COUNT(*)::int AS n_comps,
MAX(t.transacted_at) AS newest_comp,
AVG(t.gross_transaction_price)::numeric AS avg_gross,
(PERCENTILE_CONT(0.9) WITHIN GROUP (ORDER BY t.gross_transaction_price)
- PERCENTILE_CONT(0.1) WITHIN GROUP (ORDER BY t.gross_transaction_price))::numeric AS spread
FROM transactions t
WHERE t.canonical_asset_id = ca.id
AND t.transacted_at > now() - interval '24 months'
) stats ON true
${whereSql}
ORDER BY b.name, mf.name, ca.size, ca.material, ca.color`,
params
);
const enriched = rows.map((r) => {
const stats = {
n_comps: r.n_comps || 0,
newest_comp: r.newest_comp,
avg_gross: numberOrNull(r.avg_gross),
spread: numberOrNull(r.spread)
};
const liquidity = computeLiquidity(stats);
const expected_dts = computeExpectedDts(liquidity);
// Strip the helper columns + replace with live values. We keep
// newest_comp inline as a sort key — exposed under that name so
// sort=newest-comp can use it without a second SQL hit.
const { n_comps, avg_gross, spread,
seed_liquidity_score, seed_expected_dts, ...rest } = r;
return {
...rest,
liquidity_score: liquidity,
expected_dts
};
});
return applyListSort(enriched, sort);
}
// applyListSort — stable, in-place-safe sort over the enriched list.
// Sort keys live here (not SQL) so computed columns (liquidity_score,
// expected_dts) sort against live values, not the v0 seed constants.
// Unknown sort values fall through to 'default'.
const LIST_SORTS = new Set([
'default', 'value-desc', 'value-asc', 'liquidity-desc',
'comps-desc', 'newest-comp', 'slug'
]);
function applyListSort(rows, sort) {
if (!sort || !LIST_SORTS.has(sort) || sort === 'default') return rows;
const sorted = rows.slice();
const cmpNullsLast = (av, bv, desc) => {
const an = av == null, bn = bv == null;
if (an && bn) return 0;
if (an) return 1;
if (bn) return -1;
return desc ? (bv - av) : (av - bv);
};
switch (sort) {
case 'value-desc':
sorted.sort((a, b) => cmpNullsLast(numberOrNull(a.q50), numberOrNull(b.q50), true));
break;
case 'value-asc':
sorted.sort((a, b) => cmpNullsLast(numberOrNull(a.q50), numberOrNull(b.q50), false));
break;
case 'liquidity-desc':
sorted.sort((a, b) => cmpNullsLast(a.liquidity_score, b.liquidity_score, true));
break;
case 'comps-desc':
sorted.sort((a, b) => cmpNullsLast(a.comp_count, b.comp_count, true));
break;
case 'newest-comp':
sorted.sort((a, b) => {
const at = a.newest_comp ? new Date(a.newest_comp).getTime() : null;
const bt = b.newest_comp ? new Date(b.newest_comp).getTime() : null;
return cmpNullsLast(at, bt, true);
});
break;
case 'slug':
sorted.sort((a, b) => String(a.slug).localeCompare(String(b.slug)));
break;
}
return sorted;
}
// computeLiquidity — pure function combining comp depth, recency, and
// price-spread tightness into a 0–1 score. Heuristic (no embeddings yet).
//
// stats: { n_comps, newest_comp (Date|string|null), avg_gross, spread }
//
// Weights: 0.4 count + 0.4 recency + 0.2 spread. Spread term is gross-
// normalized (spread / avg_gross), so a $30k asset with $5k Q10–Q90 gap
// scores the same liquidity as a $3k asset with $500 gap.
function computeLiquidity(stats) {
if (!stats || !stats.n_comps) return 0;
const count_factor = clamp(Math.log1p(stats.n_comps) / Math.log1p(20), 0, 1);
let recency_factor = 0;
if (stats.newest_comp) {
const t = new Date(stats.newest_comp).getTime();
if (isFinite(t)) {
const days_old = Math.max(0, (Date.now() - t) / 86400000);
recency_factor = clamp(1 - days_old / 365, 0, 1);
}
}
let spread_factor = 0;
if (stats.avg_gross != null && stats.avg_gross > 0 && stats.spread != null) {
const norm_spread = stats.spread / stats.avg_gross;
spread_factor = clamp(1 - norm_spread, 0, 1);
} else if (stats.n_comps > 0) {
// Single-comp or zero-spread case: neutral middle value.
spread_factor = 0.5;
}
const liq = 0.4 * count_factor + 0.4 * recency_factor + 0.2 * spread_factor;
return clamp(+liq.toFixed(3), 0, 1);
}
// computeExpectedDts — inverse of liquidity, capped to [7, 120] days.
// liquidity=1.0 → 10 days; liquidity=0.5 → 35 days; liquidity=0 → 60 days.
function computeExpectedDts(liquidity) {
const l = clamp(numberOrNull(liquidity) || 0, 0, 1);
return Math.round(clamp(60 - l * 50, 7, 120));
}
// listFamilies() — index of every model family with a one-row cohort
// summary (asset_count, total comps in trailing 24m, avg Q50). Powers
// the /api/family (no slug) endpoint and any "browse by family" UI.
// Single SQL pass — does NOT call listAssets() per family, so this
// stays cheap as the catalog grows.
//
// opts.q (string) — case-insensitive filter token. Matches against
// any of: family slug, family name, brand slug,
// brand name. Mirrors the /api/assets q semantics.
// opts.sort (string) — see FAMILY_SORTS. Default ordering is brand+name.
async function listFamilies(opts) {
const q = opts && typeof opts.q === 'string' ? opts.q.trim() : '';
const sort = opts && typeof opts.sort === 'string' ? opts.sort.trim() : '';
const params = [];
let whereSql = '';
if (q) {
params.push(`%${q.toLowerCase()}%`);
const i = '$' + params.length;
whereSql = `
WHERE LOWER(mf.slug) LIKE ${i}
OR LOWER(mf.name) LIKE ${i}
OR LOWER(b.slug) LIKE ${i}
OR LOWER(b.name) LIKE ${i}`;
}
const rows = await many(
`SELECT mf.slug, mf.name,
b.slug AS brand_slug, b.name AS brand_name,
COUNT(DISTINCT ca.id)::int AS asset_count,
COALESCE(SUM(stats.n_comps), 0)::int AS total_comps_24m,
AVG(vs.q50)::numeric AS avg_q50
FROM model_families mf
JOIN brands b ON b.id = mf.brand_id
LEFT JOIN canonical_assets ca ON ca.model_family_id = mf.id
LEFT JOIN LATERAL (
SELECT q50 FROM valuation_snapshots vs
WHERE vs.canonical_asset_id = ca.id
ORDER BY vs.as_of DESC LIMIT 1
) vs ON true
LEFT JOIN LATERAL (
SELECT COUNT(*)::int AS n_comps
FROM transactions t
WHERE t.canonical_asset_id = ca.id
AND t.transacted_at > now() - interval '24 months'
) stats ON true
${whereSql}
GROUP BY mf.slug, mf.name, b.slug, b.name
ORDER BY b.name, mf.name`,
params
);
const enriched = rows.map((r) => ({
slug: r.slug,
name: r.name,
brand: { slug: r.brand_slug, name: r.brand_name },
asset_count: r.asset_count,
total_comps_24m: r.total_comps_24m,
avg_q50: r.avg_q50 != null ? +parseFloat(r.avg_q50).toFixed(0) : null
}));
return applyFamilySort(enriched, sort);
}
// Supported sort modes for /api/family. Default (brand+name) is the SQL
// ORDER BY above; everything else is applied in JS post-aggregation so
// computed columns (avg_q50, total_comps_24m) sort against the values
// the caller actually sees in the response, not raw SQL numerics.
const FAMILY_SORTS = new Set([
'default', 'name', 'slug',
'assets-desc', 'comps-desc', 'value-desc', 'value-asc'
]);
function applyFamilySort(rows, sort) {
if (!sort || !FAMILY_SORTS.has(sort) || sort === 'default') return rows;
const sorted = rows.slice();
const cmpNullsLast = (av, bv, desc) => {
const an = av == null, bn = bv == null;
if (an && bn) return 0;
if (an) return 1;
if (bn) return -1;
return desc ? (bv - av) : (av - bv);
};
switch (sort) {
case 'name':
sorted.sort((a, b) => String(a.name).localeCompare(String(b.name)));
break;
case 'slug':
sorted.sort((a, b) => String(a.slug).localeCompare(String(b.slug)));
break;
case 'assets-desc':
sorted.sort((a, b) => cmpNullsLast(a.asset_count, b.asset_count, true));
break;
case 'comps-desc':
sorted.sort((a, b) => cmpNullsLast(a.total_comps_24m, b.total_comps_24m, true));
break;
case 'value-desc':
sorted.sort((a, b) => cmpNullsLast(a.avg_q50, b.avg_q50, true));
break;
case 'value-asc':
sorted.sort((a, b) => cmpNullsLast(a.avg_q50, b.avg_q50, false));
break;
}
return sorted;
}
// getFamily(slug) — cohort detail. Returns the family + brand + the
// list of canonical assets (with their live snapshot values) plus a
// summary block (count, weighted-avg Q50, total comps in trailing 24m,
// liquidity histogram). Returns null when the slug is unknown.
async function getFamily(slug) {
const fam = await one(
`SELECT mf.id, mf.name, mf.slug,
b.id AS brand_id, b.name AS brand_name, b.slug AS brand_slug
FROM model_families mf
JOIN brands b ON b.id = mf.brand_id
WHERE mf.slug = $1`,
[slug]
);
if (!fam) return null;
// Reuse listAssets's enrichment by SQL-filtering on family slug. We
// can't pass a structured filter to listAssets today (it only takes
// q), so call it then post-filter — fine for v0 catalog size.
const all = await listAssets();
const assets = all.filter((a) => a.family_slug === fam.slug && a.brand_slug === fam.brand_slug);
// Find indices whose definition references this family. Today
// index definitions store filters like {"size":"30","material":"Togo",
// "colors":[…],"region":"US"}; family is implicit in the assets the
// definition selects. v0: match on slug substring (e.g. 'birkin-30-…').
const indices = await many(
`SELECT slug, name, definition, methodology_version
FROM indices
WHERE slug LIKE $1
ORDER BY slug`,
[`%${fam.slug}%`]
);
// Cohort summary
const q50s = assets.map((a) => numberOrNull(a.q50)).filter((x) => x !== null);
const compCounts = assets.map((a) => a.comp_count || 0);
const liquidities = assets.map((a) => numberOrNull(a.liquidity_score)).filter((x) => x !== null);
const summary = {
asset_count: assets.length,
avg_q50: q50s.length ? +(q50s.reduce((a, b) => a + b, 0) / q50s.length).toFixed(0) : null,
total_comps: compCounts.reduce((a, b) => a + b, 0),
avg_liquidity: liquidities.length
? +(liquidities.reduce((a, b) => a + b, 0) / liquidities.length).toFixed(3)
: null
};
return {
family: { slug: fam.slug, name: fam.name },
brand: { slug: fam.brand_slug, name: fam.brand_name },
summary,
assets,
indices
};
}
async function getIndex(slug) {
const idx = await one(`SELECT * FROM indices WHERE slug = $1`, [slug]);
if (!idx) return null;
const points = await many(
`SELECT as_of, value, change_pct
FROM index_points
WHERE index_id = $1
ORDER BY as_of`,
[idx.id]
);
return {
index: {
slug: idx.slug,
name: idx.name,
definition: idx.definition,
methodology_version: idx.methodology_version
},
points: points.map((p) => ({
as_of: p.as_of,
value: numberOrNull(p.value),
change_pct: numberOrNull(p.change_pct)
}))
};
}
// listIndices() — index of every benchmark index with a one-row
// summary: identity (slug/name/methodology_version/definition), point
// counts, and the latest point's value + as_of + change_pct.
// Single SQL pass with a lateral subquery for the latest point, so this
// stays cheap as the indices table grows.
//
// opts.q (string) — case-insensitive filter token. Matches against
// slug or name. Mirrors /api/family q semantics.
// opts.sort (string) — see INDEX_SORTS. Default ordering is by slug.
async function listIndices(opts) {
const q = opts && typeof opts.q === 'string' ? opts.q.trim() : '';
const sort = opts && typeof opts.sort === 'string' ? opts.sort.trim() : '';
const params = [];
let whereSql = '';
if (q) {
params.push(`%${q.toLowerCase()}%`);
const i = '$' + params.length;
whereSql = `WHERE LOWER(i.slug) LIKE ${i}
OR LOWER(i.name) LIKE ${i}`;
}
const rows = await many(
`SELECT i.slug, i.name, i.definition, i.methodology_version,
COALESCE(stats.point_count, 0)::int AS point_count,
stats.first_as_of, latest.as_of AS latest_as_of,
latest.value AS latest_value,
latest.change_pct AS latest_change_pct
FROM indices i
LEFT JOIN LATERAL (
SELECT COUNT(*)::int AS point_count,
MIN(as_of) AS first_as_of
FROM index_points ip
WHERE ip.index_id = i.id
) stats ON true
LEFT JOIN LATERAL (
SELECT as_of, value, change_pct
FROM index_points ip
WHERE ip.index_id = i.id
ORDER BY as_of DESC
LIMIT 1
) latest ON true
${whereSql}
ORDER BY i.slug`,
params
);
const enriched = rows.map((r) => ({
slug: r.slug,
name: r.name,
definition: r.definition,
methodology_version: r.methodology_version,
point_count: r.point_count,
first_as_of: r.first_as_of,
latest_as_of: r.latest_as_of,
latest_value: r.latest_value != null ? +parseFloat(r.latest_value).toFixed(2) : null,
latest_change_pct: r.latest_change_pct != null ? +parseFloat(r.latest_change_pct).toFixed(4) : null
}));
return applyIndexSort(enriched, sort);
}
// Supported sort modes for /api/index. Default (slug ASC) matches the
// SQL ORDER BY above; other modes are applied in JS post-aggregation so
// computed columns sort against what callers actually see.
const INDEX_SORTS = new Set([
'default', 'slug', 'name',
'value-desc', 'value-asc',
'change-desc', 'change-asc',
'points-desc', 'recent-desc'
]);
function applyIndexSort(rows, sort) {
if (!sort || !INDEX_SORTS.has(sort) || sort === 'default') return rows;
const sorted = rows.slice();
const cmpNullsLast = (av, bv, desc) => {
const an = av == null, bn = bv == null;
if (an && bn) return 0;
if (an) return 1;
if (bn) return -1;
return desc ? (bv - av) : (av - bv);
};
switch (sort) {
case 'slug':
sorted.sort((a, b) => String(a.slug).localeCompare(String(b.slug)));
break;
case 'name':
sorted.sort((a, b) => String(a.name).localeCompare(String(b.name)));
break;
case 'value-desc':
sorted.sort((a, b) => cmpNullsLast(a.latest_value, b.latest_value, true));
break;
case 'value-asc':
sorted.sort((a, b) => cmpNullsLast(a.latest_value, b.latest_value, false));
break;
case 'change-desc':
sorted.sort((a, b) => cmpNullsLast(a.latest_change_pct, b.latest_change_pct, true));
break;
case 'change-asc':
sorted.sort((a, b) => cmpNullsLast(a.latest_change_pct, b.latest_change_pct, false));
break;
case 'points-desc':
sorted.sort((a, b) => cmpNullsLast(a.point_count, b.point_count, true));
break;
case 'recent-desc':
sorted.sort((a, b) => {
const ad = a.latest_as_of ? new Date(a.latest_as_of).getTime() : null;
const bd = b.latest_as_of ? new Date(b.latest_as_of).getTime() : null;
return cmpNullsLast(ad, bd, true);
});
break;
}
return sorted;
}
function numberOrNull(v) {
if (v === null || v === undefined) return null;
const n = typeof v === 'string' ? parseFloat(v) : v;
return Number.isFinite(n) ? n : null;
}
function clamp(v, lo, hi) {
if (!isFinite(v)) return lo;
if (v < lo) return lo;
if (v > hi) return hi;
return v;
}
// Linear-interpolated quantile on a SORTED-ASCENDING numeric array.
// Mirrors PostgreSQL PERCENTILE_CONT semantics so server + DB agree.
function quantile(sorted, q) {
if (!sorted || !sorted.length) return null;
if (sorted.length === 1) return sorted[0];
const pos = (sorted.length - 1) * q;
const base = Math.floor(pos);
const rest = pos - base;
if (sorted[base + 1] !== undefined) {
return sorted[base] + rest * (sorted[base + 1] - sorted[base]);
}
return sorted[base];
}
module.exports = {
valueAsset, listAssets, getIndex, getFamily, listFamilies, listIndices,
computeBreakdown, computeLiquidity, computeExpectedDts, quantile,
METHODOLOGY_VERSION
};