← back to Nationalrealestate
initial scaffold: TS+Express+pg, schema v1 (regions/metrics/scores/broker-track/listings), regions seeder, server :9913
cb1009c48514ce5d3fa143d1d7a86d657459cb11 · 2026-07-21 12:35:44 -0700 · Steve Abrams
Files touched
A .gitignoreA db/migrate.tsA db/migrations/001_init.sqlA db/pool.tsA package.jsonA public/index.htmlA src/ingest/regions_seed.tsA src/ingest/run.tsA src/server/index.tsA tsconfig.json
Diff
commit cb1009c48514ce5d3fa143d1d7a86d657459cb11
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 21 12:35:44 2026 -0700
initial scaffold: TS+Express+pg, schema v1 (regions/metrics/scores/broker-track/listings), regions seeder, server :9913
---
.gitignore | 10 ++++
db/migrate.ts | 58 ++++++++++++++++++
db/migrations/001_init.sql | 144 +++++++++++++++++++++++++++++++++++++++++++++
db/pool.ts | 25 ++++++++
package.json | 32 ++++++++++
public/index.html | 32 ++++++++++
src/ingest/regions_seed.ts | 124 ++++++++++++++++++++++++++++++++++++++
src/ingest/run.ts | 53 +++++++++++++++++
src/server/index.ts | 84 ++++++++++++++++++++++++++
tsconfig.json | 14 +++++
10 files changed, 576 insertions(+)
diff --git a/.gitignore b/.gitignore
new file mode 100644
index 0000000..bc099d6
--- /dev/null
+++ b/.gitignore
@@ -0,0 +1,10 @@
+node_modules/
+.env
+.env.*
+tmp/
+*.log
+.DS_Store
+dist/
+build/
+.next/
+data/downloads/
diff --git a/db/migrate.ts b/db/migrate.ts
new file mode 100644
index 0000000..7c55ffc
--- /dev/null
+++ b/db/migrate.ts
@@ -0,0 +1,58 @@
+/**
+ * Runs every .sql file in db/migrations/ in lexical order, idempotent.
+ * Records applied filenames in schema_migrations.
+ */
+import 'dotenv/config';
+import { readFileSync, readdirSync } from 'node:fs';
+import { join, dirname } from 'node:path';
+import { fileURLToPath } from 'node:url';
+import { pool } from './pool.ts';
+
+const __dirname = dirname(fileURLToPath(import.meta.url));
+const migrationsDir = join(__dirname, 'migrations');
+
+async function ensureLedger() {
+ await pool.query(`
+ CREATE TABLE IF NOT EXISTS schema_migrations (
+ filename TEXT PRIMARY KEY,
+ applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+ )
+ `);
+}
+
+async function applied(): Promise<Set<string>> {
+ const r = await pool.query<{ filename: string }>(`SELECT filename FROM schema_migrations`);
+ return new Set(r.rows.map((x) => x.filename));
+}
+
+async function main() {
+ await ensureLedger();
+ const seen = await applied();
+ const files = readdirSync(migrationsDir).filter((f) => f.endsWith('.sql')).sort();
+ for (const f of files) {
+ if (seen.has(f)) {
+ console.log(`[skip] ${f}`);
+ continue;
+ }
+ const sql = readFileSync(join(migrationsDir, f), 'utf8');
+ console.log(`[apply] ${f}`);
+ try {
+ await pool.query('BEGIN');
+ await pool.query(sql);
+ await pool.query(`INSERT INTO schema_migrations (filename) VALUES ($1) ON CONFLICT DO NOTHING`, [f]);
+ await pool.query('COMMIT');
+ console.log(`[done] ${f}`);
+ } catch (e: any) {
+ try { await pool.query('ROLLBACK'); } catch {}
+ console.error(`[fail] ${f}: ${e.message}`);
+ process.exit(1);
+ }
+ }
+ await pool.end();
+ console.log('migrations complete');
+}
+
+main().catch((e) => {
+ console.error(e);
+ process.exit(1);
+});
diff --git a/db/migrations/001_init.sql b/db/migrations/001_init.sql
new file mode 100644
index 0000000..d7a2516
--- /dev/null
+++ b/db/migrations/001_init.sql
@@ -0,0 +1,144 @@
+-- USRealEstate canonical schema v1.
+-- Regions are the anchor; every metric, score, broker, and listing hangs off region_id.
+
+CREATE TABLE region (
+ id SERIAL PRIMARY KEY,
+ region_type TEXT NOT NULL CHECK (region_type IN ('county','metro','state','zip')),
+ canonical_key TEXT NOT NULL UNIQUE, -- 'county:06037' | 'metro:31080' | 'state:CA' | 'zip:90210'
+ name TEXT NOT NULL,
+ state_code TEXT,
+ fips TEXT,
+ cbsa_code TEXT,
+ lat DOUBLE PRECISION,
+ lng DOUBLE PRECISION,
+ population INTEGER,
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+CREATE INDEX idx_region_type ON region (region_type);
+CREATE INDEX idx_region_fips ON region (fips) WHERE fips IS NOT NULL;
+
+-- crosswalk: every source's own region id -> our region (Zillow RegionID, Redfin table_id, ACS geoid)
+CREATE TABLE source_region_map (
+ source TEXT NOT NULL,
+ source_region_id TEXT NOT NULL,
+ region_id INT NOT NULL REFERENCES region(id),
+ match_method TEXT, -- 'fips' | 'cbsa' | 'name' | 'manual'
+ PRIMARY KEY (source, source_region_id)
+);
+
+CREATE TABLE ingest_runs (
+ id SERIAL PRIMARY KEY,
+ source TEXT NOT NULL,
+ url TEXT,
+ file_hash TEXT,
+ started_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ finished_at TIMESTAMPTZ,
+ status TEXT NOT NULL DEFAULT 'running', -- running|ok|failed
+ rows_upserted INT DEFAULT 0,
+ rows_skipped INT DEFAULT 0,
+ notes TEXT
+);
+
+CREATE TABLE metric_series (
+ region_id INT NOT NULL REFERENCES region(id),
+ source TEXT NOT NULL, -- 'redfin'|'zillow'|'acs'|'fhfa'|'derived'
+ metric TEXT NOT NULL, -- 'median_sale_price','inventory','dom','sale_to_list',
+ -- 'zhvi','zori','hpi','median_hh_income','median_gross_rent',
+ -- 'vacancy_rate','affordability','rent_yield', ...
+ period DATE NOT NULL,
+ value NUMERIC,
+ ingest_run_id INT REFERENCES ingest_runs(id),
+ PRIMARY KEY (region_id, source, metric, period)
+);
+CREATE INDEX idx_ms_metric_period ON metric_series (metric, period);
+
+CREATE TABLE region_scores (
+ region_id INT NOT NULL REFERENCES region(id),
+ score_date DATE NOT NULL,
+ opportunity_score NUMERIC NOT NULL,
+ components JSONB NOT NULL, -- {momentum:.., affordability:.., rent_yield:.., inventory_trend:.., velocity:.., data_completeness:.., weights:{...}}
+ rank INT,
+ PRIMARY KEY (region_id, score_date)
+);
+
+CREATE TABLE watchlist (
+ id SERIAL PRIMARY KEY,
+ region_id INT NOT NULL REFERENCES region(id),
+ label TEXT,
+ thresholds JSONB, -- {metric:'zhvi_yoy', op:'>', value:0.08}
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
+);
+
+CREATE TABLE alerts_log (
+ id SERIAL PRIMARY KEY,
+ watchlist_id INT REFERENCES watchlist(id),
+ fired_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ metric TEXT,
+ observed NUMERIC,
+ detail JSONB
+);
+
+-- ── Broker track (Steve 2026-07-21): all US brokers/brokerages, national CRCP model ──
+CREATE TABLE firm (
+ id SERIAL PRIMARY KEY,
+ name TEXT NOT NULL,
+ normalized_name TEXT, -- lower/stripped for dedupe across states
+ website TEXT,
+ phone TEXT,
+ hq_city TEXT,
+ hq_state TEXT,
+ license_no TEXT,
+ license_state TEXT,
+ source TEXT NOT NULL, -- 'ca_dre'|'tx_trec'|'fl_dbpr'|'ny_dos'|...
+ source_id TEXT NOT NULL,
+ agent_count INT,
+ region_id INT REFERENCES region(id),
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (source, source_id)
+);
+CREATE INDEX idx_firm_norm ON firm (normalized_name);
+CREATE INDEX idx_firm_state ON firm (license_state);
+
+CREATE TABLE broker (
+ id SERIAL PRIMARY KEY,
+ firm_id INT REFERENCES firm(id),
+ name TEXT NOT NULL,
+ license_no TEXT,
+ license_state TEXT,
+ license_type TEXT, -- broker | salesperson | associate-broker (per state vocab)
+ license_status TEXT, -- active | expired | suspended ...
+ email TEXT,
+ phone TEXT,
+ city TEXT,
+ state_code TEXT,
+ source TEXT NOT NULL,
+ source_id TEXT NOT NULL,
+ region_id INT REFERENCES region(id),
+ created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ UNIQUE (source, source_id)
+);
+CREATE INDEX idx_broker_firm ON broker (firm_id);
+CREATE INDEX idx_broker_state ON broker (license_state);
+
+-- pluggable listings slot: fills from broker-site pilots (M-B3) / future licensed feeds
+CREATE TABLE listing (
+ id SERIAL PRIMARY KEY,
+ region_id INT REFERENCES region(id),
+ firm_id INT REFERENCES firm(id),
+ broker_id INT REFERENCES broker(id),
+ source TEXT NOT NULL,
+ source_id TEXT NOT NULL,
+ address TEXT,
+ price NUMERIC,
+ beds NUMERIC,
+ baths NUMERIC,
+ sqft NUMERIC,
+ lat DOUBLE PRECISION,
+ lng DOUBLE PRECISION,
+ url TEXT,
+ status TEXT,
+ first_seen TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+ last_seen TIMESTAMPTZ,
+ UNIQUE (source, source_id)
+);
+CREATE INDEX idx_listing_region ON listing (region_id);
diff --git a/db/pool.ts b/db/pool.ts
new file mode 100644
index 0000000..db0e116
--- /dev/null
+++ b/db/pool.ts
@@ -0,0 +1,25 @@
+/**
+ * PG connection pool — single instance, used everywhere.
+ * Reads DATABASE_URL from .env (Unix-socket path on local Mac).
+ */
+import 'dotenv/config';
+import pg from 'pg';
+
+const { Pool } = pg;
+
+export const pool = new Pool({
+ connectionString: process.env.DATABASE_URL || 'postgresql:///usre?host=/tmp',
+ max: 8,
+ idleTimeoutMillis: 30_000,
+});
+
+pool.on('error', (err) => {
+ console.error('[pg pool error]', err);
+});
+
+export async function query<T extends pg.QueryResultRow = pg.QueryResultRow>(
+ text: string,
+ params?: unknown[],
+): Promise<pg.QueryResult<T>> {
+ return pool.query<T>(text, params);
+}
diff --git a/package.json b/package.json
new file mode 100644
index 0000000..a0b0f86
--- /dev/null
+++ b/package.json
@@ -0,0 +1,32 @@
+{
+ "name": "nationalrealestate",
+ "version": "0.1.0",
+ "private": true,
+ "type": "module",
+ "description": "USRealEstate — national U.S. residential market explorer. Free-data v1 (Redfin Data Center, Zillow Research, Census ACS, FHFA). Internal analyst tool behind basic auth.",
+ "scripts": {
+ "server": "tsx src/server/index.ts",
+ "migrate": "tsx db/migrate.ts",
+ "seed:regions": "tsx src/ingest/regions_seed.ts",
+ "ingest:zillow": "tsx src/ingest/zillow_research.ts",
+ "ingest:redfin": "tsx src/ingest/redfin_tracker.ts",
+ "ingest:acs": "tsx src/ingest/census_acs.ts",
+ "ingest:fhfa": "tsx src/ingest/fhfa_hpi.ts",
+ "derive": "tsx src/ingest/derive_metrics.ts",
+ "score": "tsx src/score/opportunity.ts",
+ "refresh": "tsx src/jobs/refresh_all.ts",
+ "alerts": "tsx src/jobs/alerts_check.ts"
+ },
+ "dependencies": {
+ "dotenv": "^16.4.5",
+ "express": "^4.21.1",
+ "pg": "^8.13.1",
+ "tsx": "^4.19.2"
+ },
+ "devDependencies": {
+ "@types/express": "^5.0.0",
+ "@types/node": "^22.10.2",
+ "@types/pg": "^8.11.10",
+ "typescript": "^5.7.2"
+ }
+}
diff --git a/public/index.html b/public/index.html
new file mode 100644
index 0000000..4dd2db8
--- /dev/null
+++ b/public/index.html
@@ -0,0 +1,32 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<title>USRealEstate — National Market Explorer</title>
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;}
+ *{box-sizing:border-box;} body{margin:0;background:var(--bg);color:var(--fg);font:15px/1.5 -apple-system,Segoe UI,Roboto,sans-serif;
+ display:flex;min-height:100vh;align-items:center;justify-content:center;}
+ .card{background:var(--panel);border:1px solid var(--line);border-radius:14px;padding:28px 34px;max-width:520px;}
+ h1{margin:0 0 4px;font-size:22px;} h1 b{color:var(--gold);}
+ .sub{color:var(--muted);font-size:13px;margin-bottom:18px;}
+ .counts{display:flex;gap:14px;} .ct{background:var(--bg);border:1px solid var(--line);border-radius:10px;padding:10px 16px;text-align:center;}
+ .ct .n{font-size:22px;font-weight:700;color:var(--gold);font-variant-numeric:tabular-nums;} .ct .l{font-size:11px;color:var(--muted);text-transform:uppercase;letter-spacing:.5px;}
+</style>
+</head>
+<body>
+<div class="card">
+ <h1><b>USRealEstate</b> — National Market Explorer</h1>
+ <div class="sub">M1 scaffold · choropleth map lands in M2</div>
+ <div class="counts" id="counts">loading…</div>
+</div>
+<script>
+fetch('/api/health').then(r=>r.json()).then(d=>{
+ const el = document.getElementById('counts');
+ el.innerHTML = Object.entries(d.regions||{}).map(([k,v])=>
+ `<div class="ct"><div class="n">${v.toLocaleString()}</div><div class="l">${k}</div></div>`).join('') || 'no regions yet';
+});
+</script>
+</body>
+</html>
diff --git a/src/ingest/regions_seed.ts b/src/ingest/regions_seed.ts
new file mode 100644
index 0000000..4b5eb54
--- /dev/null
+++ b/src/ingest/regions_seed.ts
@@ -0,0 +1,124 @@
+/**
+ * Seeds the region anchor table:
+ * - all ~3,144 counties from the Census Gazetteer counties file (GEOID = 5-digit FIPS)
+ * - all ~935 CBSAs (metro + micro) from the Census Gazetteer CBSA file
+ * - 50 states + DC statically
+ * Gazetteer zips hold one tab-delimited txt; parsed via `unzip -p` (no zip dep).
+ * Idempotent: ON CONFLICT (canonical_key) DO UPDATE.
+ */
+import { execFileSync } from 'node:child_process';
+import { query, pool } from '../../db/pool.ts';
+import { openRun, closeRun, download } from './run.ts';
+
+const GAZ_BASE = 'https://www2.census.gov/geo/docs/maps-data/data/gazetteer/2024_Gazetteer';
+const COUNTIES_ZIP = `${GAZ_BASE}/2024_Gaz_counties_national.zip`;
+const CBSA_ZIP = `${GAZ_BASE}/2024_Gaz_cbsa_national.zip`;
+
+const STATES: Array<[string, string, string]> = [
+ ['AL','01','Alabama'],['AK','02','Alaska'],['AZ','04','Arizona'],['AR','05','Arkansas'],
+ ['CA','06','California'],['CO','08','Colorado'],['CT','09','Connecticut'],['DE','10','Delaware'],
+ ['DC','11','District of Columbia'],['FL','12','Florida'],['GA','13','Georgia'],['HI','15','Hawaii'],
+ ['ID','16','Idaho'],['IL','17','Illinois'],['IN','18','Indiana'],['IA','19','Iowa'],
+ ['KS','20','Kansas'],['KY','21','Kentucky'],['LA','22','Louisiana'],['ME','23','Maine'],
+ ['MD','24','Maryland'],['MA','25','Massachusetts'],['MI','26','Michigan'],['MN','27','Minnesota'],
+ ['MS','28','Mississippi'],['MO','29','Missouri'],['MT','30','Montana'],['NE','31','Nebraska'],
+ ['NV','32','Nevada'],['NH','33','New Hampshire'],['NJ','34','New Jersey'],['NM','35','New Mexico'],
+ ['NY','36','New York'],['NC','37','North Carolina'],['ND','38','North Dakota'],['OH','39','Ohio'],
+ ['OK','40','Oklahoma'],['OR','41','Oregon'],['PA','42','Pennsylvania'],['RI','44','Rhode Island'],
+ ['SC','45','South Carolina'],['SD','46','South Dakota'],['TN','47','Tennessee'],['TX','48','Texas'],
+ ['UT','49','Utah'],['VT','50','Vermont'],['VA','51','Virginia'],['WA','53','Washington'],
+ ['WV','54','West Virginia'],['WI','55','Wisconsin'],['WY','56','Wyoming'],
+];
+
+function parseGazetteer(zipPath: string): Array<Record<string, string>> {
+ const text = execFileSync('unzip', ['-p', zipPath], { maxBuffer: 64 * 1024 * 1024 }).toString('utf8');
+ const lines = text.split('\n').filter((l) => l.trim().length > 0);
+ const headers = lines[0].split('\t').map((h) => h.trim());
+ return lines.slice(1).map((line) => {
+ const cells = line.split('\t');
+ const row: Record<string, string> = {};
+ headers.forEach((h, i) => { row[h] = (cells[i] ?? '').trim(); });
+ return row;
+ });
+}
+
+async function upsertRegion(r: {
+ region_type: string; canonical_key: string; name: string;
+ state_code?: string | null; fips?: string | null; cbsa_code?: string | null;
+ lat?: number | null; lng?: number | null;
+}): Promise<void> {
+ await query(
+ `INSERT INTO region (region_type, canonical_key, name, state_code, fips, cbsa_code, lat, lng)
+ VALUES ($1,$2,$3,$4,$5,$6,$7,$8)
+ ON CONFLICT (canonical_key) DO UPDATE
+ SET name = EXCLUDED.name, state_code = EXCLUDED.state_code,
+ fips = EXCLUDED.fips, cbsa_code = EXCLUDED.cbsa_code,
+ lat = EXCLUDED.lat, lng = EXCLUDED.lng`,
+ [r.region_type, r.canonical_key, r.name, r.state_code ?? null, r.fips ?? null,
+ r.cbsa_code ?? null, r.lat ?? null, r.lng ?? null],
+ );
+}
+
+async function main() {
+ const runId = await openRun('census_gazetteer', COUNTIES_ZIP);
+ let upserted = 0;
+ try {
+ // states (static)
+ for (const [code, fips, name] of STATES) {
+ await upsertRegion({ region_type: 'state', canonical_key: `state:${code}`, name, state_code: code, fips });
+ upserted++;
+ }
+
+ // counties
+ const counties = await download(COUNTIES_ZIP, 'gaz_counties_2024.zip', { reuseCached: true });
+ const countyRows = parseGazetteer(counties.path);
+ if (countyRows.length < 3000) throw new Error(`county gazetteer parsed only ${countyRows.length} rows — format drift?`);
+ for (const row of countyRows) {
+ const fips = row['GEOID'];
+ if (!fips || fips.length !== 5) continue;
+ // last header often carries trailing whitespace in the raw file; keys are trimmed at parse
+ const lat = parseFloat(row['INTPTLAT'] ?? '');
+ const lng = parseFloat(row['INTPTLONG'] ?? '');
+ await upsertRegion({
+ region_type: 'county',
+ canonical_key: `county:${fips}`,
+ name: row['NAME'],
+ state_code: row['USPS'],
+ fips,
+ lat: Number.isFinite(lat) ? lat : null,
+ lng: Number.isFinite(lng) ? lng : null,
+ });
+ upserted++;
+ }
+
+ // metros (CBSA — metro + micro areas)
+ const cbsa = await download(CBSA_ZIP, 'gaz_cbsa_2024.zip', { reuseCached: true });
+ const cbsaRows = parseGazetteer(cbsa.path);
+ if (cbsaRows.length < 800) throw new Error(`cbsa gazetteer parsed only ${cbsaRows.length} rows — format drift?`);
+ for (const row of cbsaRows) {
+ const code = row['GEOID'];
+ if (!code) continue;
+ const lat = parseFloat(row['INTPTLAT'] ?? '');
+ const lng = parseFloat(row['INTPTLONG'] ?? '');
+ await upsertRegion({
+ region_type: 'metro',
+ canonical_key: `metro:${code}`,
+ name: row['NAME'],
+ cbsa_code: code,
+ lat: Number.isFinite(lat) ? lat : null,
+ lng: Number.isFinite(lng) ? lng : null,
+ });
+ upserted++;
+ }
+
+ await closeRun(runId, 'ok', { upserted, fileHash: counties.sha256, notes: `counties=${countyRows.length} cbsa=${cbsaRows.length} states=${STATES.length}` });
+ console.log(`regions seeded: ${upserted} upserts (${countyRows.length} counties, ${cbsaRows.length} CBSAs, ${STATES.length} states)`);
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: String(e.message || e) });
+ throw e;
+ } finally {
+ await pool.end();
+ }
+}
+
+main().catch((e) => { console.error(e); process.exit(1); });
diff --git a/src/ingest/run.ts b/src/ingest/run.ts
new file mode 100644
index 0000000..86b9883
--- /dev/null
+++ b/src/ingest/run.ts
@@ -0,0 +1,53 @@
+/**
+ * Shared ingest harness: every job opens an ingest_runs row, downloads to
+ * data/downloads/ (sha256 recorded), and closes the run with counts.
+ * HTTP != 200 or a 0-row parse must end as status='failed' + non-zero exit —
+ * URL drift shows up as a red run, never silent staleness.
+ */
+import { createHash } from 'node:crypto';
+import { mkdirSync, writeFileSync, readFileSync, existsSync } from 'node:fs';
+import { join, dirname } from 'node:path';
+import { fileURLToPath } from 'node:url';
+import { query } from '../../db/pool.ts';
+
+const __dirname = dirname(fileURLToPath(import.meta.url));
+export const DOWNLOADS = join(__dirname, '..', '..', 'data', 'downloads');
+
+export async function openRun(source: string, url?: string): Promise<number> {
+ const r = await query<{ id: number }>(
+ `INSERT INTO ingest_runs (source, url) VALUES ($1, $2) RETURNING id`,
+ [source, url ?? null],
+ );
+ return r.rows[0].id;
+}
+
+export async function closeRun(
+ id: number,
+ status: 'ok' | 'failed',
+ counts: { upserted?: number; skipped?: number; notes?: string; fileHash?: string } = {},
+): Promise<void> {
+ await query(
+ `UPDATE ingest_runs SET finished_at = NOW(), status = $2,
+ rows_upserted = COALESCE($3, rows_upserted),
+ rows_skipped = COALESCE($4, rows_skipped),
+ notes = COALESCE($5, notes),
+ file_hash = COALESCE($6, file_hash)
+ WHERE id = $1`,
+ [id, status, counts.upserted ?? null, counts.skipped ?? null, counts.notes ?? null, counts.fileHash ?? null],
+ );
+}
+
+/** Download url -> data/downloads/<filename>. Returns { path, sha256 }. Throws on non-200. */
+export async function download(url: string, filename: string, opts: { reuseCached?: boolean } = {}): Promise<{ path: string; sha256: string }> {
+ mkdirSync(DOWNLOADS, { recursive: true });
+ const dest = join(DOWNLOADS, filename);
+ if (opts.reuseCached && existsSync(dest)) {
+ const buf = readFileSync(dest);
+ return { path: dest, sha256: createHash('sha256').update(buf).digest('hex') };
+ }
+ const res = await fetch(url, { redirect: 'follow' });
+ if (!res.ok) throw new Error(`download failed ${res.status} ${res.statusText}: ${url}`);
+ const buf = Buffer.from(await res.arrayBuffer());
+ writeFileSync(dest, buf);
+ return { path: dest, sha256: createHash('sha256').update(buf).digest('hex') };
+}
diff --git a/src/server/index.ts b/src/server/index.ts
new file mode 100644
index 0000000..ff3e097
--- /dev/null
+++ b/src/server/index.ts
@@ -0,0 +1,84 @@
+/**
+ * USRealEstate server — Express on :9913, whole-site Basic Auth (CRCP pattern).
+ * /healthz stays OPEN so deploy smoke-tests + uptime canaries get a 200 without creds.
+ */
+import 'dotenv/config';
+import express from 'express';
+import { join, dirname } from 'node:path';
+import { fileURLToPath } from 'node:url';
+import { query } from '../../db/pool.ts';
+
+const __dirname = dirname(fileURLToPath(import.meta.url));
+const ROOT = join(__dirname, '..', '..');
+const PORT = Number(process.env.PORT || 9913);
+
+const app = express();
+app.use(express.json({ limit: '256kb' }));
+
+app.get('/healthz', (_req, res) => res.type('text').send('ok'));
+
+const AUTH_USER = process.env.USRE_USER || 'admin';
+const AUTH_PASS = process.env.USRE_PASS || 'DW2024!';
+app.use((req, res, next) => {
+ const hdr = req.headers.authorization || '';
+ const [scheme, encoded] = hdr.split(' ');
+ if (scheme === 'Basic' && encoded) {
+ const [u, ...rest] = Buffer.from(encoded, 'base64').toString().split(':');
+ if (u === AUTH_USER && rest.join(':') === AUTH_PASS) return next();
+ }
+ res.set('WWW-Authenticate', 'Basic realm="USRealEstate", charset="UTF-8"');
+ return res.status(401).send('Authentication required');
+});
+
+app.get('/api/health', async (_req, res) => {
+ try {
+ const r = await query<{ region_type: string; n: string }>(
+ `SELECT region_type, COUNT(*)::text AS n FROM region GROUP BY region_type`,
+ );
+ const regions: Record<string, number> = {};
+ for (const row of r.rows) regions[row.region_type] = Number(row.n);
+ res.json({ ok: true, regions });
+ } catch (e: any) {
+ res.status(500).json({ ok: false, error: String(e.message || e) });
+ }
+});
+
+app.get('/api/regions', async (req, res) => {
+ try {
+ const type = String(req.query.type || 'county');
+ const r = await query(
+ `SELECT id, region_type, canonical_key, name, state_code, fips, cbsa_code, lat, lng, population
+ FROM region WHERE region_type = $1 ORDER BY name`,
+ [type],
+ );
+ res.json({ regions: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+});
+
+// choropleth data: {fips: value} for the latest period of a metric (M2 fills metric_series)
+app.get('/api/choropleth', async (req, res) => {
+ try {
+ const metric = String(req.query.metric || 'zhvi');
+ const r = await query<{ fips: string; value: string }>(
+ `SELECT rg.fips, ms.value::text AS value
+ FROM metric_series ms
+ JOIN region rg ON rg.id = ms.region_id AND rg.region_type = 'county'
+ WHERE ms.metric = $1
+ AND ms.period = (SELECT MAX(period) FROM metric_series WHERE metric = $1)`,
+ [metric],
+ );
+ const out: Record<string, number> = {};
+ for (const row of r.rows) if (row.fips) out[row.fips] = Number(row.value);
+ res.json({ metric, count: r.rows.length, values: out });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+});
+
+app.use(express.static(join(ROOT, 'public')));
+
+app.listen(PORT, () => {
+ console.log(`USRealEstate listening on http://127.0.0.1:${PORT}`);
+});
diff --git a/tsconfig.json b/tsconfig.json
new file mode 100644
index 0000000..bc416f5
--- /dev/null
+++ b/tsconfig.json
@@ -0,0 +1,14 @@
+{
+ "compilerOptions": {
+ "target": "ES2022",
+ "module": "ESNext",
+ "moduleResolution": "Bundler",
+ "allowImportingTsExtensions": true,
+ "noEmit": true,
+ "strict": true,
+ "esModuleInterop": true,
+ "skipLibCheck": true,
+ "resolveJsonModule": true
+ },
+ "include": ["src/**/*", "db/**/*"]
+}
(oldest)
·
back to Nationalrealestate
·
M1 complete: usre DB migrated, 3222 counties + 935 metros + 3859e63 →