← back to Nationalrealestate
src/score/opportunity.ts
202 lines
/**
* Opportunity scorer — deterministic, NO LLM. Counties and metros scored separately
* (percentiles within region_type). ONE frozen SQL snapshot read, then pure TS math,
* so same-day re-runs produce byte-identical components.
*
* Components (each a 0-1 sub-score):
* momentum = percentile of blend(zhvi_yoy, redfin median_sale_price_yoy fallback)
* affordability = INVERSE percentile of zhvi / median_hh_income
* rent_yield = percentile of derived rent_yield
* inventory_trend = INVERSE percentile of 12-mo % change in months_of_supply (tightening = higher)
* velocity = mean(inverse DOM percentile, sale_to_list percentile)
* Null component -> dropped + weights renormalized; data_completeness = fraction of 5 present.
* opportunity_score = renormalized weighted sum x 100, clamped 0-100.
* Rank = dense by score desc within region_type — THIN MARKETS EXCLUDED from rank
* (population < 50k or < 10 monthly sales: a 795-person county with one 637%-YoY sale
* must never outrank real markets; they keep a score + components.thin_market=true, rank NULL).
*/
import { readFileSync } from 'node:fs';
import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';
import { query, pool } from '../../db/pool.ts';
const __dirname = dirname(fileURLToPath(import.meta.url));
const CONFIG = JSON.parse(readFileSync(join(__dirname, '..', '..', 'score-config.json'), 'utf8'));
const WEIGHTS: Record<string, number> = CONFIG.weights;
const COMPONENTS = ['momentum', 'affordability', 'rent_yield', 'inventory_trend', 'velocity'] as const;
interface SnapRow { region_id: number; region_type: string; metric: string; value: string }
/** Mid-rank percentile in [0,1] over a sorted array. Deterministic under ties. */
function percentiler(values: number[]): (v: number) => number {
const sorted = [...values].sort((a, b) => a - b);
const n = sorted.length;
if (n <= 1) return () => 0.5;
const lowerBound = (v: number) => { let lo = 0, hi = n; while (lo < hi) { const m = (lo + hi) >> 1; if (sorted[m] < v) lo = m + 1; else hi = m; } return lo; };
const upperBound = (v: number) => { let lo = 0, hi = n; while (lo < hi) { const m = (lo + hi) >> 1; if (sorted[m] <= v) lo = m + 1; else hi = m; } return lo; };
return (v: number) => ((lowerBound(v) + upperBound(v) - 1) / 2) / (n - 1);
}
function round6(x: number): number { return Math.round(x * 1e6) / 1e6; }
async function main() {
// ---- single frozen snapshot read ----
const snap = await query<SnapRow>(
`WITH latest AS (
SELECT DISTINCT ON (ms.region_id, ms.metric)
ms.region_id, rg.region_type, ms.metric, ms.value, ms.period
FROM metric_series ms
JOIN region rg ON rg.id = ms.region_id AND rg.region_type IN ('county','metro')
WHERE ms.metric IN ('zhvi_yoy','median_sale_price_yoy','affordability','rent_yield',
'months_of_supply','dom','sale_to_list','homes_sold')
ORDER BY ms.region_id, ms.metric, ms.period DESC
)
SELECT region_id, region_type, metric, value::text AS value FROM latest
UNION ALL
SELECT l.region_id, l.region_type, 'months_of_supply_prior' AS metric, ms.value::text AS value
FROM latest l
JOIN metric_series ms
ON ms.region_id = l.region_id AND ms.metric = 'months_of_supply'
AND ms.period = l.period - INTERVAL '12 months'
WHERE l.metric = 'months_of_supply'
ORDER BY region_id, metric`,
);
const byRegion = new Map<number, { type: string; m: Record<string, number> }>();
for (const r of snap.rows) {
let o = byRegion.get(r.region_id);
if (!o) { o = { type: r.region_type, m: {} }; byRegion.set(r.region_id, o); }
o.m[r.metric] = Number(r.value);
}
const scoreDateR = await query<{ d: string }>(`SELECT CURRENT_DATE::text AS d`);
const scoreDate = scoreDateR.rows[0].d;
const popR = await query<{ id: number; population: number | null }>(
`SELECT id, population FROM region WHERE region_type IN ('county','metro')`,
);
const popById = new Map(popR.rows.map(r => [r.id, r.population]));
const TM = CONFIG.thin_market || { min_population: 50000, min_monthly_homes_sold: 10 };
let totalUpserts = 0;
for (const regionType of ['county', 'metro']) {
const regions = [...byRegion.entries()]
.filter(([, o]) => o.type === regionType)
.sort((a, b) => a[0] - b[0]); // deterministic order
// raw inputs per region
type Raw = {
momentum_raw: number | null; affordability_raw: number | null; rent_yield_raw: number | null;
mos_change: number | null; dom: number | null; sale_to_list: number | null;
zhvi_yoy: number | null; msp_yoy: number | null; mos_latest: number | null; mos_prior: number | null;
};
const raws = new Map<number, Raw>();
for (const [id, o] of regions) {
const g = (k: string) => (k in o.m && Number.isFinite(o.m[k]) ? o.m[k] : null);
const zhviYoy = g('zhvi_yoy'), mspYoy = g('median_sale_price_yoy');
const mosL = g('months_of_supply'), mosP = g('months_of_supply_prior');
raws.set(id, {
momentum_raw: zhviYoy ?? mspYoy,
affordability_raw: g('affordability'),
rent_yield_raw: g('rent_yield'),
mos_change: mosL != null && mosP != null && mosP > 0 ? (mosL - mosP) / mosP : null,
dom: g('dom'), sale_to_list: g('sale_to_list'),
zhvi_yoy: zhviYoy, msp_yoy: mspYoy, mos_latest: mosL, mos_prior: mosP,
});
}
const pool_ = (pick: (r: Raw) => number | null) =>
[...raws.values()].map(pick).filter((v): v is number => v != null);
const pMomentum = percentiler(pool_(r => r.momentum_raw));
const pAfford = percentiler(pool_(r => r.affordability_raw));
const pYield = percentiler(pool_(r => r.rent_yield_raw));
const pMos = percentiler(pool_(r => r.mos_change));
const pDom = percentiler(pool_(r => r.dom));
const pStl = percentiler(pool_(r => r.sale_to_list));
const results: { regionId: number; score: number; components: any }[] = [];
for (const [id] of regions) {
const r = raws.get(id)!;
const sub: Record<string, number | null> = {
momentum: r.momentum_raw != null ? round6(pMomentum(r.momentum_raw)) : null,
affordability: r.affordability_raw != null ? round6(1 - pAfford(r.affordability_raw)) : null,
rent_yield: r.rent_yield_raw != null ? round6(pYield(r.rent_yield_raw)) : null,
inventory_trend: r.mos_change != null ? round6(1 - pMos(r.mos_change)) : null,
velocity: null,
};
const velParts: number[] = [];
if (r.dom != null) velParts.push(1 - pDom(r.dom));
if (r.sale_to_list != null) velParts.push(pStl(r.sale_to_list));
if (velParts.length) sub.velocity = round6(velParts.reduce((a, b) => a + b, 0) / velParts.length);
const present = COMPONENTS.filter(c => sub[c] != null);
if (!present.length) continue;
const wSum = present.reduce((a, c) => a + WEIGHTS[c], 0);
const weightsUsed: Record<string, number> = {};
for (const c of present) weightsUsed[c] = round6(WEIGHTS[c] / wSum);
const score = Math.min(100, Math.max(0,
present.reduce((a, c) => a + weightsUsed[c] * (sub[c] as number), 0) * 100));
const population = popById.get(id) ?? null;
const homesSold = (id => { const o = byRegion.get(id); const v = o?.m['homes_sold']; return Number.isFinite(v) ? (v as number) : null; })(id);
const thinMarket =
(population != null && population < TM.min_population) ||
(homesSold != null && homesSold < TM.min_monthly_homes_sold) ||
(population == null && homesSold == null);
results.push({
regionId: id,
score: Math.round(score * 100) / 100,
components: {
sub_scores: sub,
weights_used: weightsUsed,
data_completeness: round6(present.length / COMPONENTS.length),
thin_market: thinMarket,
population,
homes_sold_latest: homesSold,
raw: {
zhvi_yoy: r.zhvi_yoy, median_sale_price_yoy: r.msp_yoy,
affordability: r.affordability_raw, rent_yield: r.rent_yield_raw,
months_of_supply_latest: r.mos_latest, months_of_supply_prior: r.mos_prior,
months_of_supply_change_12mo: r.mos_change != null ? round6(r.mos_change) : null,
dom: r.dom, sale_to_list: r.sale_to_list,
},
},
});
}
// dense rank by score desc within region_type — thin markets carry NO rank
results.sort((a, b) => b.score - a.score || a.regionId - b.regionId);
let rank = 0, prevScore: number | null = null;
for (const res of results) {
if (res.components.thin_market) { (res as any).rank = null; continue; }
if (res.score !== prevScore) { rank++; prevScore = res.score; }
(res as any).rank = rank;
}
for (let i = 0; i < results.length; i += 500) {
const batch = results.slice(i, i + 500);
const params: unknown[] = [];
const values = batch.map((res: any, j) => {
params.push(res.regionId, scoreDate, res.score, JSON.stringify(res.components), res.rank);
const b = j * 5;
return `($${b + 1},$${b + 2}::date,$${b + 3},$${b + 4}::jsonb,$${b + 5})`;
});
await query(
`INSERT INTO region_scores (region_id, score_date, opportunity_score, components, rank)
VALUES ${values.join(',')}
ON CONFLICT (region_id, score_date)
DO UPDATE SET opportunity_score = EXCLUDED.opportunity_score,
components = EXCLUDED.components, rank = EXCLUDED.rank`,
params,
);
}
totalUpserts += results.length;
console.log(`[score] ${regionType}: ${results.length} scored`);
}
console.log(`[score] ok: ${totalUpserts} region_scores rows for ${scoreDate}`);
await pool.end();
}
main().catch(e => { console.error('[score] FAILED:', e); process.exit(1); });