← back to Nationalrealestate
M-P1 property-level detail layer: 13-section property.html + /api/property (LA 2.43M-parcel SQLite read-in-place), FEMA NRI v1.20 + HUD FY26 FMR county ingests, parcel_source registry
d86921e7c9d1a83ecbcbcf20cf08f8f4fff541b9 · 2026-07-21 14:19:05 -0700 · Steve Abrams
Files touched
M SOURCES.mdA db/migrations/003_property.sqlM package-lock.jsonM package.jsonM public/market.htmlA public/property.htmlA src/ingest/fema_nri.tsA src/ingest/hud_fmr.tsM src/ingest/run.tsM src/server/index.tsA src/server/property.ts
Diff
commit d86921e7c9d1a83ecbcbcf20cf08f8f4fff541b9
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 21 14:19:05 2026 -0700
M-P1 property-level detail layer: 13-section property.html + /api/property (LA 2.43M-parcel SQLite read-in-place), FEMA NRI v1.20 + HUD FY26 FMR county ingests, parcel_source registry
---
SOURCES.md | 19 +++
db/migrations/003_property.sql | 19 +++
package-lock.json | 129 +++++++++++++++++-
package.json | 11 +-
public/market.html | 17 ++-
public/property.html | 300 +++++++++++++++++++++++++++++++++++++++++
src/ingest/fema_nri.ts | 114 ++++++++++++++++
src/ingest/hud_fmr.ts | 82 +++++++++++
src/ingest/run.ts | 4 +-
src/server/index.ts | 2 +
src/server/property.ts | 290 +++++++++++++++++++++++++++++++++++++++
11 files changed, 980 insertions(+), 7 deletions(-)
diff --git a/SOURCES.md b/SOURCES.md
index aa68dbb..3880904 100644
--- a/SOURCES.md
+++ b/SOURCES.md
@@ -86,3 +86,22 @@ active), **CO** (data.colorado.gov `4zse-6bnw`, RE-filtered from 108,811), **CT*
(data.ct.gov `6tja-6vdt`, 18,281 affiliation pairs). Socrata bulk endpoint pattern:
`https://<domain>/api/views/<id>/rows.csv?accessType=DOWNLOAD`. Full per-state matrix
incl. 8 gated/blocked states → `BROKER-COVERAGE.md`. Adapters: `src/ingest/brokers/`.
+
+## FEMA National Risk Index — source `fema_nri` (M-P1, probed 2026-07-21)
+
+- **URL DRIFT**: the documented `hazards.fema.gov/nri/Content/StaticDocuments/DataDownload/NRI_Table_Counties/NRI_Table_Counties.zip` 301s to a generic FEMA RAPT page — the standalone NRI site was retired. Current home is OpenFEMA: `https://www.fema.gov/about/reports-and-data/openfema/nri/v120/NRI_Table_Counties.zip` (200, 25.0 MB zip → NRI_Table_Counties.csv + NRIDataDictionary.csv + NRI_HazardInfo.csv). Found via fema.gov/flood-maps/products-tools/national-risk-index → openfema/data-sets/national-risk-index-data.
+- Release: **v1.20.0, December 2025** (per NRI_HazardInfo NRI_VER + file Last-Modified 2025-12-18). **Period stored = 2025-12-01** (fixed release date; NRI is a versioned snapshot, not a time series).
+- 3,232 county rows; join `STCOFIPS` (zero-padded 5) → region.fips. 3,222 matched, 10 unmatched (territories/CT planning regions).
+- Metrics (composite 0–100 scores): `nri_risk_score` (RISK_SCORE), `nri_eal_score` (EAL_SCORE), `nri_sovi_score` (SOVI_SCORE), `nri_resl_score` (RESL_SCORE). Display strings in `nri_ratings` (risk_rating = RISK_RATNG; top_hazards = top 3 of the 18 per-hazard `<PFX>_RISKS` scores, names mapped via NRI_HazardInfo).
+
+## HUD Fair Market Rents — source `hud_fmr` (M-P1, probed 2026-07-21)
+
+- File: `https://www.huduser.gov/portal/datasets/fmr/fmr2026/FY26_FMRs_revised.xlsx` — the FY2026 county-level FMR file. **XLSX-ONLY**: HUD publishes no CSV variant for FY25/FY26 (verified against the full fmr.html link inventory); parsed with the `xlsx` npm package (recorded choice — light, read-only).
+- **Gotcha**: huduser.gov bot-challenges non-browser UAs (202 + Incapsula HTML, and a bare 404 page for wrong paths) — the ingest sends a Chrome UA (run.ts `download()` grew an optional `headers` opt).
+- 4,764 rows: whole-county rows have 10-digit `fips` = county5 + `99999`; New England rows are per-town with real sub-codes → aggregated to county by `pop2023`-weighted mean. 3,229 counties, 3,222 matched to region.fips.
+- Metrics: `fmr_0br` … `fmr_4br` (fmr_0..fmr_4 columns). **Period = 2025-10-01** (FY2026 start).
+
+## LA County parcels — parcel_source `06037` (M-P1)
+
+- Read IN PLACE from the CRCP read-only SQLite (`~/Projects/commercialrealestate/data/assessor.sqlite`, 2,427,010 rows, 2025 secured roll) — never copied into PG. Registered in `parcel_source`.
+- Columns actually present: ain (PK), situs_house_no, situs_street, property_location, year_built, effective_year_built, sqft_main, bedrooms, bathrooms, units, use_desc1, use_desc2, roll_total_value, roll_land_value, roll_imp_value, roll_year (single year: 2025), recording_date. **NOT present**: owner names, mailing address, zoning, sale prices, lat/lng, situs_city as its own column (city is inside property_location).
diff --git a/db/migrations/003_property.sql b/db/migrations/003_property.sql
new file mode 100644
index 0000000..5d01655
--- /dev/null
+++ b/db/migrations/003_property.sql
@@ -0,0 +1,19 @@
+-- M-P1 property-level detail layer.
+-- parcel_source: registry of which counties have parcel-level data and where it lives.
+-- County-level NRI/FMR metrics go into metric_series (no new columns needed).
+
+CREATE TABLE parcel_source (
+ county_fips TEXT PRIMARY KEY,
+ source_kind TEXT, -- 'sqlite' | 'pg' | 'api' | ...
+ path_or_url TEXT,
+ parcel_count INT,
+ notes TEXT,
+ loaded_at TIMESTAMPTZ
+);
+
+-- FEMA National Risk Index display strings (scores live in metric_series source 'fema_nri')
+CREATE TABLE nri_ratings (
+ county_fips TEXT PRIMARY KEY,
+ risk_rating TEXT, -- 'Very Low' .. 'Very High'
+ top_hazards JSONB -- top 3 by hazard risk score: [{hazard, score}, ...]
+);
diff --git a/package-lock.json b/package-lock.json
index 68d63f2..c526df1 100644
--- a/package-lock.json
+++ b/package-lock.json
@@ -8,11 +8,13 @@
"name": "nationalrealestate",
"version": "0.1.0",
"dependencies": {
+ "better-sqlite3": "^13.0.1",
"dotenv": "^16.4.5",
"express": "^4.21.1",
"pg": "^8.13.1",
"playwright": "^1.59.1",
- "tsx": "^4.19.2"
+ "tsx": "^4.19.2",
+ "xlsx": "^0.18.5"
},
"devDependencies": {
"@types/express": "^5.0.0",
@@ -560,12 +562,34 @@
"node": ">= 0.6"
}
},
+ "node_modules/adler-32": {
+ "version": "1.3.1",
+ "resolved": "https://registry.npmjs.org/adler-32/-/adler-32-1.3.1.tgz",
+ "integrity": "sha512-ynZ4w/nUUv5rrsR8UUGoe1VC9hZj6V5hU9Qw1HlMDJGEJw5S7TfTErWTjMys6M7vr0YWcPqs3qAr4ss0nDfP+A==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/array-flatten": {
"version": "1.1.1",
"resolved": "https://registry.npmjs.org/array-flatten/-/array-flatten-1.1.1.tgz",
"integrity": "sha512-PCVAQswWemu6UdxsDFFX/+gVeYqKAod3D3UVm91jHwynguOwAvYPhx8nNlM++NqRcK6CxxpUafjmhIdKiHibqg==",
"license": "MIT"
},
+ "node_modules/better-sqlite3": {
+ "version": "13.0.1",
+ "resolved": "https://registry.npmjs.org/better-sqlite3/-/better-sqlite3-13.0.1.tgz",
+ "integrity": "sha512-LYpmOXdkpQYf4wmlxkdzW01XGlOXNIbjLg45yNkh0FQ4814VbK9PdOFmhZpYbej+EZtR/i3FDdhEG98HqZdgnA==",
+ "hasInstallScript": true,
+ "license": "MIT",
+ "dependencies": {
+ "node-addon-api": "^8.0.0"
+ },
+ "engines": {
+ "node": ">=22"
+ }
+ },
"node_modules/body-parser": {
"version": "1.20.6",
"resolved": "https://registry.npmjs.org/body-parser/-/body-parser-1.20.6.tgz",
@@ -628,6 +652,28 @@
"url": "https://github.com/sponsors/ljharb"
}
},
+ "node_modules/cfb": {
+ "version": "1.2.2",
+ "resolved": "https://registry.npmjs.org/cfb/-/cfb-1.2.2.tgz",
+ "integrity": "sha512-KfdUZsSOw19/ObEWasvBP/Ac4reZvAGauZhs6S/gqNhXhI7cKwvlH7ulj+dOEYnca4bm4SGo8C1bTAQvnTjgQA==",
+ "license": "Apache-2.0",
+ "dependencies": {
+ "adler-32": "~1.3.0",
+ "crc-32": "~1.2.0"
+ },
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
+ "node_modules/codepage": {
+ "version": "1.15.0",
+ "resolved": "https://registry.npmjs.org/codepage/-/codepage-1.15.0.tgz",
+ "integrity": "sha512-3g6NUTPd/YtuuGrhMnOMRjFc+LJw/bnMp3+0r/Wcz3IXUuCosKRJvMphm5+Q+bvTVGcJJuRvVLuYba+WojaFaA==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/content-disposition": {
"version": "0.5.4",
"resolved": "https://registry.npmjs.org/content-disposition/-/content-disposition-0.5.4.tgz",
@@ -664,6 +710,18 @@
"integrity": "sha512-NXdYc3dLr47pBkpUCHtKSwIOQXLVn8dZEuywboCOJY/osA0wFSLlSawr3KN8qXJEyX66FcONTH8EIlVuK0yyFA==",
"license": "MIT"
},
+ "node_modules/crc-32": {
+ "version": "1.2.2",
+ "resolved": "https://registry.npmjs.org/crc-32/-/crc-32-1.2.2.tgz",
+ "integrity": "sha512-ROmzCKrTnOwybPcJApAA6WBWij23HVfGVNKqqrZpuyZOHqK2CwHSvpGuyt/UNNvaIjEd8X5IFGp4Mh+Ie1IHJQ==",
+ "license": "Apache-2.0",
+ "bin": {
+ "crc32": "bin/crc32.njs"
+ },
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/debug": {
"version": "2.6.9",
"resolved": "https://registry.npmjs.org/debug/-/debug-2.6.9.tgz",
@@ -892,6 +950,15 @@
"node": ">= 0.6"
}
},
+ "node_modules/frac": {
+ "version": "1.1.2",
+ "resolved": "https://registry.npmjs.org/frac/-/frac-1.1.2.tgz",
+ "integrity": "sha512-w/XBfkibaTl3YDqASwfDUqkna4Z2p9cFSr1aHDt0WoMTECnRfBOv2WArlZILlqgWlmdIlALXGpM2AOhEk5W3IA==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/fresh": {
"version": "0.5.2",
"resolved": "https://registry.npmjs.org/fresh/-/fresh-0.5.2.tgz",
@@ -1128,6 +1195,15 @@
"node": ">= 0.6"
}
},
+ "node_modules/node-addon-api": {
+ "version": "8.9.0",
+ "resolved": "https://registry.npmjs.org/node-addon-api/-/node-addon-api-8.9.0.tgz",
+ "integrity": "sha512-ekZMeaaIzSQTSpr7X2X3iJM7lTzgnx8ahAG9pJfT/7+14mlEM8ZYQ9cgCDvSSRbReFK0oHli3WrZdCiRsgAT9Q==",
+ "license": "MIT",
+ "engines": {
+ "node": "^18 || ^20 || >= 21"
+ }
+ },
"node_modules/object-inspect": {
"version": "1.13.4",
"resolved": "https://registry.npmjs.org/object-inspect/-/object-inspect-1.13.4.tgz",
@@ -1550,6 +1626,18 @@
"node": ">= 10.x"
}
},
+ "node_modules/ssf": {
+ "version": "0.11.2",
+ "resolved": "https://registry.npmjs.org/ssf/-/ssf-0.11.2.tgz",
+ "integrity": "sha512-+idbmIXoYET47hH+d7dfm2epdOMUDjqcB4648sTZ+t2JwoyBFL/insLfB/racrDmsKB3diwsDA696pZMieAC5g==",
+ "license": "Apache-2.0",
+ "dependencies": {
+ "frac": "~1.1.2"
+ },
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/statuses": {
"version": "2.0.2",
"resolved": "https://registry.npmjs.org/statuses/-/statuses-2.0.2.tgz",
@@ -1647,6 +1735,45 @@
"node": ">= 0.8"
}
},
+ "node_modules/wmf": {
+ "version": "1.0.2",
+ "resolved": "https://registry.npmjs.org/wmf/-/wmf-1.0.2.tgz",
+ "integrity": "sha512-/p9K7bEh0Dj6WbXg4JG0xvLQmIadrner1bi45VMJTfnbVHsc7yIajZyoSoK60/dtVBs12Fm6WkUI5/3WAVsNMw==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
+ "node_modules/word": {
+ "version": "0.3.0",
+ "resolved": "https://registry.npmjs.org/word/-/word-0.3.0.tgz",
+ "integrity": "sha512-OELeY0Q61OXpdUfTp+oweA/vtLVg5VDOXh+3he3PNzLGG/y0oylSOC1xRVj0+l4vQ3tj/bB1HVHv1ocXkQceFA==",
+ "license": "Apache-2.0",
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
+ "node_modules/xlsx": {
+ "version": "0.18.5",
+ "resolved": "https://registry.npmjs.org/xlsx/-/xlsx-0.18.5.tgz",
+ "integrity": "sha512-dmg3LCjBPHZnQp5/F/+nnTa+miPJxUXB6vtk42YjBBKayDNagxGEeIdWApkYPOf3Z3pm3k62Knjzp7lMeTEtFQ==",
+ "license": "Apache-2.0",
+ "dependencies": {
+ "adler-32": "~1.3.0",
+ "cfb": "~1.2.1",
+ "codepage": "~1.15.0",
+ "crc-32": "~1.2.1",
+ "ssf": "~0.11.2",
+ "wmf": "~1.0.1",
+ "word": "~0.3.0"
+ },
+ "bin": {
+ "xlsx": "bin/xlsx.njs"
+ },
+ "engines": {
+ "node": ">=0.8"
+ }
+ },
"node_modules/xtend": {
"version": "4.0.2",
"resolved": "https://registry.npmjs.org/xtend/-/xtend-4.0.2.tgz",
diff --git a/package.json b/package.json
index 89f8fcf..490d005 100644
--- a/package.json
+++ b/package.json
@@ -18,14 +18,18 @@
"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"
+ "alerts": "tsx src/jobs/alerts_check.ts",
+ "ingest:nri": "tsx src/ingest/fema_nri.ts",
+ "ingest:fmr": "tsx src/ingest/hud_fmr.ts"
},
"dependencies": {
+ "better-sqlite3": "^13.0.1",
"dotenv": "^16.4.5",
"express": "^4.21.1",
"pg": "^8.13.1",
"playwright": "^1.59.1",
- "tsx": "^4.19.2"
+ "tsx": "^4.19.2",
+ "xlsx": "^0.18.5"
},
"devDependencies": {
"@types/express": "^5.0.0",
@@ -34,6 +38,7 @@
"typescript": "^5.7.2"
},
"allowScripts": {
- "esbuild@0.28.1": true
+ "esbuild@0.28.1": true,
+ "better-sqlite3@13.0.1": true
}
}
diff --git a/public/market.html b/public/market.html
index ccce346..7a480f8 100644
--- a/public/market.html
+++ b/public/market.html
@@ -68,7 +68,7 @@
</div>
<div class="panel" style="margin-top:16px;">
<h3>Parcels</h3>
- <div class="nodata">Parcels — county data not yet loaded</div>
+ <div class="nodata" id="parcels">Parcels — county data not yet loaded</div>
</div>
<div id="foot">Income, rent, population, vacancy, renter share: ACS 2023 5-yr vintage.
ZHVI/ZORI: Zillow Research. Sale metrics: Redfin Data Center. HPI: FHFA.</div>
@@ -158,6 +158,21 @@ function chart(elId, entries, fmt){
];
document.getElementById('stats').innerHTML = STATS.map(([k, v]) =>
`<div class="stat"><div class="k">${k}</div><div class="v">${v}</div></div>`).join('');
+
+ // M-P1: counties with parcel coverage get a property-level search entry point
+ if (region.region_type === 'county' && region.fips === '06037') {
+ document.getElementById('parcels').innerHTML =
+ `<form style="display:flex;gap:8px;justify-content:center;align-items:center;flex-wrap:wrap;"
+ onsubmit="event.preventDefault();location.href='/property.html?county=06037&loc='+encodeURIComponent(this.q.value);">
+ <span style="color:var(--fg);">2.43M LA County parcels loaded —</span>
+ <input name="q" placeholder="search an address, e.g. 9321 Ventura Way" autocomplete="off"
+ style="background:var(--bg);border:1px solid var(--line);border-radius:8px;color:var(--fg);
+ padding:7px 12px;font-size:13px;min-width:260px;outline:none;">
+ <button type="submit" style="background:var(--gold);color:#0a0d13;border:0;border-radius:8px;
+ padding:7px 14px;font-weight:700;cursor:pointer;">Open property</button>
+ <a href="/property.html?county=06037" style="color:var(--gold);font-size:12px;">property detail →</a>
+ </form>`;
+ }
})();
</script>
</body>
diff --git a/public/property.html b/public/property.html
new file mode 100644
index 0000000..7bc8667
--- /dev/null
+++ b/public/property.html
@@ -0,0 +1,300 @@
+<!doctype html>
+<html lang="en">
+<head>
+<meta charset="utf-8">
+<meta name="viewport" content="width=device-width, initial-scale=1">
+<title>USRealEstate — Property Detail</title>
+<link rel="stylesheet" href="https://unpkg.com/leaflet@1.9.4/dist/leaflet.css"
+ integrity="sha256-p4NxAoJBhIIN+hmNHrzRCf9tD/miZyoHS5obTRR9BMY=" crossorigin="">
+<style>
+ :root{--bg:#0a0d13;--panel:#11151d;--line:#222936;--fg:#e8ecf2;--muted:#8a93a3;--gold:#c8a24b;
+ --green:#5fae7c;--red:#c96a5a;}
+ *{box-sizing:border-box;}
+ html,body{margin:0;background:var(--bg);color:var(--fg);
+ font:14px/1.5 -apple-system,Segoe UI,Roboto,sans-serif;}
+ #topbar{position:sticky;top:0;z-index:1000;display:flex;align-items:center;gap:18px;
+ padding:10px 18px;background:rgba(17,21,29,.94);border-bottom:1px solid var(--line);backdrop-filter:blur(6px);}
+ #topbar .brand{font-size:16px;font-weight:700;white-space:nowrap;}
+ #topbar .brand b{color:var(--gold);}
+ nav{display:flex;gap:14px;font-size:13px;}
+ nav a{color:var(--muted);text-decoration:none;} nav a:hover,nav a.active{color:var(--gold);}
+ main{max-width:1180px;margin:0 auto;padding:24px 18px 60px;}
+ #search{display:flex;gap:10px;margin-bottom:14px;}
+ #search input{flex:1;max-width:520px;background:var(--panel);border:1px solid var(--line);
+ border-radius:10px;color:var(--fg);padding:10px 14px;font-size:14px;outline:none;}
+ #search input:focus{border-color:var(--gold);}
+ #search button{background:var(--gold);color:#0a0d13;border:0;border-radius:10px;padding:10px 18px;
+ font-weight:700;cursor:pointer;font-size:14px;}
+ #results{margin-bottom:18px;}
+ .result{display:flex;gap:14px;align-items:baseline;padding:8px 12px;border:1px solid var(--line);
+ border-radius:8px;margin-bottom:6px;cursor:pointer;background:var(--panel);flex-wrap:wrap;}
+ .result:hover{border-color:var(--gold);}
+ .result .addr{font-weight:700;}
+ .result .facts{color:var(--muted);font-size:12px;font-variant-numeric:tabular-nums;}
+ #hdr{display:flex;align-items:baseline;gap:18px;flex-wrap:wrap;margin-bottom:6px;}
+ #hdr h1{margin:0;font-size:24px;font-weight:700;}
+ #hdr .st{color:var(--muted);font-size:14px;}
+ #hdr .val{font-size:20px;color:var(--gold);font-variant-numeric:tabular-nums;font-weight:700;}
+ #sub{color:var(--muted);font-size:12px;margin-bottom:18px;}
+ .grid2{display:grid;grid-template-columns:repeat(auto-fit,minmax(440px,1fr));gap:16px;}
+ .panel{background:var(--panel);border:1px solid var(--line);border-radius:12px;padding:14px 16px;}
+ .panel h3{margin:0 0 8px;font-size:13px;color:var(--gold);font-weight:700;text-transform:uppercase;
+ letter-spacing:.5px;}
+ .pending{color:var(--muted);font-size:12px;padding:14px 0;font-style:italic;}
+ .kv{display:grid;grid-template-columns:repeat(auto-fill,minmax(150px,1fr));gap:10px;}
+ .stat{background:var(--bg);border:1px solid var(--line);border-radius:10px;padding:10px 12px;}
+ .stat .k{font-size:11px;color:var(--muted);text-transform:uppercase;letter-spacing:.4px;}
+ .stat .v{font-size:16px;font-weight:700;font-variant-numeric:tabular-nums;margin-top:2px;}
+ table{width:100%;border-collapse:collapse;font-size:12px;font-variant-numeric:tabular-nums;}
+ th{color:var(--muted);text-align:left;font-weight:600;padding:5px 8px;border-bottom:1px solid var(--line);
+ text-transform:uppercase;font-size:10px;letter-spacing:.4px;}
+ td{padding:6px 8px;border-bottom:1px solid var(--line);}
+ tr:last-child td{border-bottom:0;}
+ td a{color:var(--gold);text-decoration:none;}
+ .note{color:var(--muted);font-size:11px;margin-top:8px;font-style:italic;}
+ .badge{display:inline-block;padding:3px 10px;border-radius:14px;font-size:12px;font-weight:700;
+ border:1px solid var(--line);margin-right:6px;}
+ .badge.warn{color:var(--red);border-color:var(--red);}
+ .badge.ok{color:var(--green);border-color:var(--green);}
+ #map_el{height:260px;border-radius:8px;}
+ .leaflet-container{background:var(--bg);}
+ #foot{margin-top:22px;color:var(--muted);font-size:11px;}
+</style>
+</head>
+<body>
+<div id="topbar">
+ <div class="brand"><b>USRealEstate</b> — Property Detail</div>
+ <nav>
+ <a href="/">Map</a>
+ <a href="/markets.html">Markets</a>
+ <a href="/watchlist.html">Watchlist</a>
+ <a href="/brokers.html">Brokers</a>
+ <a href="/admin.html">Admin</a>
+ </nav>
+</div>
+<main>
+ <form id="search">
+ <input id="q" type="text" placeholder="Search an LA County address — e.g. 9321 Ventura Way" autocomplete="off">
+ <button type="submit">Search</button>
+ </form>
+ <div id="results"></div>
+
+ <div id="prop">
+ <div id="hdr"><h1></h1></div>
+ <div id="sub"></div>
+ <div class="grid2">
+ <div class="panel" id="s_history"><h3>Property History</h3><div class="body"></div></div>
+ <div class="panel" id="s_tax"><h3>Tax History</h3><div class="body"></div></div>
+ <div class="panel" id="s_contact"><h3>Contact Information</h3><div class="body"></div></div>
+ <div class="panel" id="s_ownership"><h3>Ownership Information</h3><div class="body"></div></div>
+ <div class="panel" id="s_zoning"><h3>Zoning</h3><div class="body"></div></div>
+ <div class="panel" id="s_details"><h3>Property Details</h3><div class="body"></div></div>
+ <div class="panel" id="s_contacts"><h3>Contacts</h3><div class="body"></div></div>
+ <div class="panel" id="s_map"><h3>Map</h3><div class="body"></div></div>
+ </div>
+ <div class="panel" style="margin-top:16px;" id="s_comps"><h3>Comparable Properties</h3><div class="body"></div></div>
+ <div class="grid2" style="margin-top:16px;">
+ <div class="panel" id="s_climate"><h3>Climate Risk</h3><div class="body"></div></div>
+ <div class="panel" id="s_rental"><h3>Rental Market Overview</h3><div class="body"></div></div>
+ <div class="panel" id="s_demo"><h3>Demographics</h3><div class="body"></div></div>
+ <div class="panel" id="s_location"><h3>Location Insights</h3><div class="body"></div></div>
+ </div>
+ <div id="foot">Parcel facts: LA County assessor roll (2025 secured, via CRCP). Climate: FEMA National Risk
+ Index v1.20.0. Rents: HUD FMR FY2026 + Zillow ZORI. Demographics: Census ACS 2023 5-yr. $0 — all free public data.</div>
+ </div>
+</main>
+
+<script src="https://unpkg.com/leaflet@1.9.4/dist/leaflet.js"
+ integrity="sha256-20nQCchB9co0qIjJZRGuk2/Z9VM+kNiyxNV1lvTlZBo=" crossorigin=""></script>
+<script>
+const qs = new URLSearchParams(location.search);
+const county = qs.get('county') || '06037';
+
+const usd0 = v => v == null ? '—' : '$' + Math.round(v).toLocaleString();
+const int0 = v => v == null ? '—' : Math.round(v).toLocaleString();
+const pct1 = v => v == null ? '—' : (v * 100).toFixed(1) + '%';
+const num1 = v => v == null ? '—' : (+v).toFixed(1);
+const esc = s => String(s ?? '').replace(/[&<>"']/g, c => ({'&':'&','<':'<','>':'>','"':'"',"'":'''}[c]));
+
+const PENDING = '<div class="pending">Coverage pending for this county.</div>';
+function pendingNote(note){ return '<div class="pending">' + esc(note || 'Coverage pending for this county.') + '</div>'; }
+function stats(pairs){
+ return '<div class="kv">' + pairs.map(([k, v]) =>
+ `<div class="stat"><div class="k">${esc(k)}</div><div class="v">${v}</div></div>`).join('') + '</div>';
+}
+function body(id, html){ document.querySelector('#' + id + ' .body').innerHTML = html; }
+
+let mapObj = null;
+
+async function search(q){
+ const el = document.getElementById('results');
+ el.innerHTML = '<div class="pending">Searching…</div>';
+ const res = await fetch(`/api/property/search?county=${county}&q=${encodeURIComponent(q)}`);
+ const d = await res.json();
+ if (!res.ok){ el.innerHTML = pendingNote(d.error); return; }
+ if (!d.matches.length){ el.innerHTML = '<div class="pending">No parcels matched. Try "number street", e.g. 9321 Ventura.</div>'; return; }
+ el.innerHTML = d.matches.map(m =>
+ `<div class="result" data-ain="${esc(m.ain)}">
+ <span class="addr">${esc(m.address)}</span>
+ <span class="facts">${esc(m.use || '')} · ${m.bedrooms ?? '—'}bd/${m.bathrooms ?? '—'}ba ·
+ ${int0(m.sqft)} sqft · built ${m.year_built || '—'} · assessed ${usd0(m.assessed_value)}</span>
+ </div>`).join('');
+ el.querySelectorAll('.result').forEach(r => r.addEventListener('click', () => {
+ history.replaceState(null, '', `?county=${county}&loc=${encodeURIComponent(r.dataset.ain)}`);
+ load(r.dataset.ain);
+ el.innerHTML = '';
+ }));
+}
+
+async function load(loc){
+ const res = await fetch(`/api/property?county=${county}&loc=${encodeURIComponent(loc)}`);
+ const d = await res.json();
+ if (!res.ok){
+ document.querySelector('#hdr h1').textContent = 'Not found: ' + loc;
+ return;
+ }
+
+ const p = d.property_details;
+ document.title = 'USRealEstate — ' + (p.address || p.ain);
+ document.querySelector('#hdr h1').textContent = p.address || 'AIN ' + p.ain;
+ document.querySelector('#hdr').innerHTML =
+ `<h1>${esc(p.address || 'AIN ' + p.ain)}</h1><span class="st">${esc(p.use || '')}</span>` +
+ `<span class="val">${usd0(d.tax_history.rolls[0]?.total_value)} assessed</span>`;
+ document.getElementById('sub').textContent =
+ `AIN ${p.ain} · county ${d.county} · ${p.bedrooms ?? '—'} bd / ${p.bathrooms ?? '—'} ba · ` +
+ `${int0(p.sqft)} sqft · built ${p.year_built || '—'}`;
+
+ // 1 Property History
+ body('s_history', d.property_history.last_recording_date
+ ? stats([['Last Recording Date', esc(d.property_history.last_recording_date)],
+ ['Sale Price', usd0(d.property_history.sale_price)]]) +
+ `<div class="note">${esc(d.property_history.note)}</div>`
+ : pendingNote(d.property_history.note));
+
+ // 2 Tax History
+ const rolls = d.tax_history.rolls.filter(r => r.roll_year);
+ body('s_tax', rolls.length
+ ? `<table><tr><th>Roll Year</th><th>Total</th><th>Land</th><th>Improvements</th></tr>` +
+ rolls.map(r => `<tr><td>${esc(r.roll_year)}</td><td>${usd0(r.total_value)}</td>
+ <td>${usd0(r.land_value)}</td><td>${usd0(r.improvement_value)}</td></tr>`).join('') +
+ `</table><div class="note">${esc(d.tax_history.note)}</div>`
+ : pendingNote(d.tax_history.note));
+
+ // 3 Contact Information
+ body('s_contact', (d.contact_information.phone || d.contact_information.email)
+ ? stats([['Phone', esc(d.contact_information.phone)], ['Email', esc(d.contact_information.email)]])
+ : pendingNote(d.contact_information.note));
+
+ // 4 Ownership Information
+ body('s_ownership', d.ownership_information.owner_name
+ ? stats([['Owner', esc(d.ownership_information.owner_name)],
+ ['Mailing Address', esc(d.ownership_information.mailing_address)]])
+ : pendingNote(d.ownership_information.note));
+
+ // 5 Zoning
+ body('s_zoning', d.zoning.code
+ ? stats([['Zoning', esc(d.zoning.code)]])
+ : pendingNote(d.zoning.note));
+
+ // 6 Property Details
+ body('s_details', stats([
+ ['Use', esc(p.use)], ['Category', esc(p.use_category)],
+ ['Year Built', p.year_built || '—'], ['Effective Year', p.effective_year_built || '—'],
+ ['Sqft (main)', int0(p.sqft)], ['Bedrooms', p.bedrooms ?? '—'],
+ ['Bathrooms', p.bathrooms ?? '—'], ['Units', p.units ?? '—'],
+ ]));
+
+ // 7 Contacts
+ const ca = d.contacts.county_assessor;
+ body('s_contacts',
+ stats([['County Assessor', esc(ca.name)], ['Phone', esc(ca.phone)]]) +
+ `<div class="note"><a href="${esc(ca.website)}" target="_blank" rel="noopener noreferrer" style="color:var(--gold)">${esc(ca.website)}</a>
+ · <a href="${esc(d.contacts.brokers_link)}" style="color:var(--gold)">CA brokers directory</a></div>` +
+ `<div class="note">${esc(d.contacts.note)}</div>`);
+
+ // 8 Map
+ if (d.map.lat != null && d.map.lng != null){
+ body('s_map', `<div id="map_el"></div><div class="note">${esc(d.map.note)}</div>`);
+ if (mapObj) mapObj.remove();
+ mapObj = L.map('map_el', { scrollWheelZoom: false }).setView([d.map.lat, d.map.lng], d.map.precision === 'parcel' ? 17 : 9);
+ L.tileLayer('https://{s}.basemaps.cartocdn.com/dark_all/{z}/{x}/{y}{r}.png',
+ { attribution: '© OpenStreetMap © CARTO', maxZoom: 19 }).addTo(mapObj);
+ L.marker([d.map.lat, d.map.lng]).addTo(mapObj)
+ .bindPopup(esc(p.address || p.ain) + '<br><small>' + esc(d.map.precision) + '</small>');
+ } else {
+ body('s_map', pendingNote(d.map.note));
+ }
+
+ // 9 Comparable Properties
+ body('s_comps', d.comps.length
+ ? `<table><tr><th>Address</th><th>Built</th><th>Sqft</th><th>Bd/Ba</th><th>Assessed</th><th>Recorded</th></tr>` +
+ d.comps.map(c => `<tr><td><a href="?county=${county}&loc=${encodeURIComponent(c.ain)}">${esc(c.address)}</a></td>
+ <td>${c.year_built || '—'}</td><td>${int0(c.sqft)}</td>
+ <td>${c.bedrooms ?? '—'}/${c.bathrooms ?? '—'}</td>
+ <td>${usd0(c.assessed_value)}</td><td>${esc(c.recording_date || '—')}</td></tr>`).join('') +
+ `</table><div class="note">Same use type, sqft ±30%, most recent recordings; same-street matches first.</div>`
+ : pendingNote('No comparable parcels matched (needs sqft + use type on the subject parcel).'));
+
+ // 10 Climate Risk
+ if (d.climate && d.climate.risk_score != null){
+ const rt = d.climate.risk_rating || '—';
+ const cls = /high/i.test(rt) ? 'warn' : 'ok';
+ body('s_climate',
+ `<div style="margin-bottom:10px;"><span class="badge ${cls}">${esc(rt)}</span>
+ ${(d.climate.top_hazards || []).map(h => `<span class="badge">${esc(h.hazard)} ${num1(h.score)}</span>`).join('')}</div>` +
+ stats([
+ ['Risk Score', num1(d.climate.risk_score)], ['Expected Annual Loss', num1(d.climate.eal_score)],
+ ['Social Vulnerability', num1(d.climate.social_vulnerability)],
+ ['Community Resilience', num1(d.climate.community_resilience)],
+ ]) + `<div class="note">${esc(d.climate.source)}</div>`);
+ } else body('s_climate', PENDING);
+
+ // 11 Rental Market Overview
+ if (d.rental && (d.rental.fmr || d.rental.zori != null)){
+ const f = d.rental.fmr || {};
+ body('s_rental', stats([
+ ['FMR Studio', usd0(f.fmr_0br)], ['FMR 1BR', usd0(f.fmr_1br)], ['FMR 2BR', usd0(f.fmr_2br)],
+ ['FMR 3BR', usd0(f.fmr_3br)], ['FMR 4BR', usd0(f.fmr_4br)],
+ ['ZORI (observed rent)', usd0(d.rental.zori)], ['Rent Yield', pct1(d.rental.rent_yield)],
+ ['Median Gross Rent', usd0(d.rental.median_gross_rent)],
+ ]) + `<div class="note">${esc(d.rental.source)}</div>`);
+ } else body('s_rental', PENDING);
+
+ // 12 Demographics
+ if (d.demographics && d.demographics.population != null){
+ body('s_demo', stats([
+ ['Population', int0(d.demographics.population)],
+ ['Median HH Income', usd0(d.demographics.median_hh_income)],
+ ['Median Gross Rent', usd0(d.demographics.median_gross_rent)],
+ ['Vacancy Rate', pct1(d.demographics.vacancy_rate)],
+ ['Renter Share', pct1(d.demographics.renter_share)],
+ ]) + `<div class="note">${esc(d.demographics.source)}</div>`);
+ } else body('s_demo', PENDING);
+
+ // 13 Location Insights
+ if (d.location){
+ body('s_location', stats([
+ ['County', esc(d.location.county_name)], ['State', esc(d.location.state)],
+ ['Metro', esc(d.location.metro || '—')], ['FIPS', esc(d.location.fips)],
+ ['County ZHVI', usd0(d.location.zhvi)], ['ZHVI YoY', pct1(d.location.zhvi_yoy)],
+ ]) + `<div class="note"><a href="${esc(d.location.links.market)}" style="color:var(--gold)">Full county market detail →</a></div>`);
+ } else body('s_location', PENDING);
+}
+
+document.getElementById('search').addEventListener('submit', e => {
+ e.preventDefault();
+ const q = document.getElementById('q').value.trim();
+ if (q) search(q);
+});
+
+// full 13-section skeleton always visible — never blank/hidden
+document.querySelector('#hdr h1').textContent = 'Search an address to load a property';
+document.getElementById('sub').textContent =
+ 'Parcel coverage: Los Angeles County (06037) — 2.43M parcels from the 2025 secured roll. Other counties: coverage pending.';
+document.querySelectorAll('#prop .panel .body').forEach(b => { b.innerHTML = PENDING; });
+
+const loc = qs.get('loc');
+if (loc) load(loc);
+</script>
+</body>
+</html>
diff --git a/src/ingest/fema_nri.ts b/src/ingest/fema_nri.ts
new file mode 100644
index 0000000..a4afa30
--- /dev/null
+++ b/src/ingest/fema_nri.ts
@@ -0,0 +1,114 @@
+/**
+ * FEMA National Risk Index — county table, source 'fema_nri'.
+ * URL DRIFT (2026-07-21): the old hazards.fema.gov NRI site 301s to a generic FEMA page.
+ * Current home is OpenFEMA: fema.gov/about/reports-and-data/openfema/nri/v120/NRI_Table_Counties.zip
+ * (v1.20.0, December 2025 release — period stored as 2025-12-01). See SOURCES.md.
+ * Metrics: nri_risk_score, nri_eal_score, nri_sovi_score, nri_resl_score.
+ * Display strings (rating + top-3 hazards by <PFX>_RISKS score) go to nri_ratings.
+ */
+import { execFileSync } from 'node:child_process';
+import { readFileSync, mkdirSync } from 'node:fs';
+import { join } from 'node:path';
+import { openRun, closeRun, download, DOWNLOADS } from './run.ts';
+import { loadRegionMaps, upsertMetricRows, type MetricRow } from './crosswalk.ts';
+import { query, pool } from '../../db/pool.ts';
+
+const NRI_URL = 'https://www.fema.gov/about/reports-and-data/openfema/nri/v120/NRI_Table_Counties.zip';
+const RELEASE_PERIOD = '2025-12-01'; // NRI v1.20.0, December 2025 release
+
+const HAZARDS: Record<string, string> = {
+ AVLN: 'Avalanche', CFLD: 'Coastal Flooding', CWAV: 'Cold Wave', DRGT: 'Drought',
+ ERQK: 'Earthquake', HAIL: 'Hail', HWAV: 'Heat Wave', HRCN: 'Hurricane',
+ ISTM: 'Ice Storm', IFLD: 'Inland Flooding', LNDS: 'Landslide', LTNG: 'Lightning',
+ SWND: 'Strong Wind', TRND: 'Tornado', TSUN: 'Tsunami', VLCN: 'Volcanic Activity',
+ WFIR: 'Wildfire', WNTW: 'Winter Weather',
+};
+
+function parseCsvLine(line: string): string[] {
+ const out: string[] = [];
+ let cur = '', inQ = false;
+ for (let i = 0; i < line.length; i++) {
+ const ch = line[i];
+ if (inQ) {
+ if (ch === '"') { if (line[i + 1] === '"') { cur += '"'; i++; } else inQ = false; }
+ else cur += ch;
+ } else if (ch === '"') inQ = true;
+ else if (ch === ',') { out.push(cur); cur = ''; }
+ else cur += ch;
+ }
+ out.push(cur);
+ return out;
+}
+
+async function main() {
+ const runId = await openRun('fema_nri', NRI_URL);
+ try {
+ const { path: zipPath, sha256 } = await download(NRI_URL, 'NRI_Table_Counties.zip', { reuseCached: true });
+ const extractDir = join(DOWNLOADS, 'nri_counties');
+ mkdirSync(extractDir, { recursive: true });
+ execFileSync('unzip', ['-o', '-q', zipPath, '-d', extractDir]);
+
+ const text = readFileSync(join(extractDir, 'NRI_Table_Counties.csv'), 'utf8');
+ const lines = text.split('\n');
+ const header = parseCsvLine(lines[0].replace(/^/, '').replace(/\r$/, ''));
+ const col = new Map(header.map((h, i) => [h, i]));
+ for (const c of ['STCOFIPS', 'RISK_SCORE', 'RISK_RATNG', 'EAL_SCORE', 'SOVI_SCORE', 'RESL_SCORE']) {
+ if (!col.has(c)) throw new Error(`NRI header drift: missing column ${c}`);
+ }
+ const hazardCols = Object.keys(HAZARDS)
+ .map(pfx => ({ pfx, idx: col.get(pfx + '_RISKS') }))
+ .filter((h): h is { pfx: string; idx: number } => h.idx != null);
+
+ const maps = await loadRegionMaps();
+ const metricRows: MetricRow[] = [];
+ const ratingRows: { fips: string; rating: string | null; top: { hazard: string; score: number }[] }[] = [];
+ let skipped = 0;
+
+ for (let i = 1; i < lines.length; i++) {
+ const line = lines[i].replace(/\r$/, '');
+ if (!line) continue;
+ const f = parseCsvLine(line);
+ const fips = (f[col.get('STCOFIPS')!] || '').padStart(5, '0');
+ const region = maps.byFips.get(fips);
+ if (!region) { skipped++; continue; }
+ const metrics: [string, string][] = [
+ ['nri_risk_score', 'RISK_SCORE'], ['nri_eal_score', 'EAL_SCORE'],
+ ['nri_sovi_score', 'SOVI_SCORE'], ['nri_resl_score', 'RESL_SCORE'],
+ ];
+ for (const [metric, colName] of metrics) {
+ const v = Number(f[col.get(colName)!]);
+ if (!Number.isFinite(v)) continue;
+ metricRows.push({ regionId: region.id, source: 'fema_nri', metric, period: RELEASE_PERIOD, value: v });
+ }
+ const top = hazardCols
+ .map(h => ({ hazard: HAZARDS[h.pfx], score: Number(f[h.idx]) }))
+ .filter(h => Number.isFinite(h.score))
+ .sort((a, b) => b.score - a.score)
+ .slice(0, 3)
+ .map(h => ({ hazard: h.hazard, score: Math.round(h.score * 100) / 100 }));
+ ratingRows.push({ fips, rating: f[col.get('RISK_RATNG')!] || null, top });
+ }
+
+ const upserted = await upsertMetricRows(metricRows, runId);
+ for (const r of ratingRows) {
+ await query(
+ `INSERT INTO nri_ratings (county_fips, risk_rating, top_hazards) VALUES ($1, $2, $3)
+ ON CONFLICT (county_fips) DO UPDATE SET risk_rating = EXCLUDED.risk_rating, top_hazards = EXCLUDED.top_hazards`,
+ [r.fips, r.rating, JSON.stringify(r.top)],
+ );
+ }
+ if (upserted === 0) throw new Error('0 metric rows parsed — NRI format drift');
+ await closeRun(runId, 'ok', {
+ upserted, skipped, fileHash: sha256,
+ notes: `v1.20.0 Dec-2025 release, period ${RELEASE_PERIOD}; ${ratingRows.length} counties, ${upserted} metric rows`,
+ });
+ console.log(`fema_nri: ${ratingRows.length} counties, ${upserted} metric rows, ${skipped} unmatched`);
+ } 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/hud_fmr.ts b/src/ingest/hud_fmr.ts
new file mode 100644
index 0000000..2b369f8
--- /dev/null
+++ b/src/ingest/hud_fmr.ts
@@ -0,0 +1,82 @@
+/**
+ * HUD Fair Market Rents — FY2026 county-level, source 'hud_fmr'.
+ * File: huduser.gov/portal/datasets/fmr/fmr2026/FY26_FMRs_revised.xlsx (XLSX-ONLY — HUD stopped
+ * publishing a CSV variant after FY2024; parsed with the `xlsx` package, choice recorded in
+ * SOURCES.md). huduser.gov bot-challenges the default fetch UA (202 + html), so we send a
+ * browser UA. Period = FY2026 start = 2025-10-01.
+ * fips column is 10-digit: county FIPS(5) + '99999' for whole-county rows; New England rows are
+ * per-town (real sub-codes) — aggregated to county by pop2023-weighted mean.
+ * Metrics: fmr_0br .. fmr_4br.
+ */
+import XLSX from 'xlsx';
+import { openRun, closeRun, download } from './run.ts';
+import { loadRegionMaps, upsertMetricRows, type MetricRow } from './crosswalk.ts';
+import { pool } from '../../db/pool.ts';
+
+const FMR_URL = 'https://www.huduser.gov/portal/datasets/fmr/fmr2026/FY26_FMRs_revised.xlsx';
+const FY_PERIOD = '2025-10-01'; // FY2026 starts Oct 1 2025
+const UA = 'Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/126.0 Safari/537.36';
+
+interface FmrRow {
+ fips: string | number | null;
+ pop2023: number | null;
+ fmr_0: number | null; fmr_1: number | null; fmr_2: number | null; fmr_3: number | null; fmr_4: number | null;
+}
+
+async function main() {
+ const runId = await openRun('hud_fmr', FMR_URL);
+ try {
+ const { path, sha256 } = await download(FMR_URL, 'FY26_FMRs_revised.xlsx', {
+ reuseCached: true, headers: { 'User-Agent': UA },
+ });
+ const wb = XLSX.readFile(path);
+ const rows = XLSX.utils.sheet_to_json<FmrRow>(wb.Sheets[wb.SheetNames[0]], { defval: null });
+ if (!rows.length || !('fips' in rows[0]) || !('fmr_0' in rows[0])) {
+ throw new Error('HUD FMR format drift: expected columns fips + fmr_0..fmr_4');
+ }
+
+ // aggregate per 5-digit county FIPS (pop-weighted across New England town rows)
+ const byCounty = new Map<string, { wsum: number[]; pop: number; n: number }>();
+ for (const r of rows) {
+ const fips10 = String(r.fips ?? '').padStart(10, '0');
+ const county = fips10.slice(0, 5);
+ if (!/^\d{5}$/.test(county)) continue;
+ const vals = [r.fmr_0, r.fmr_1, r.fmr_2, r.fmr_3, r.fmr_4].map(Number);
+ if (vals.some(v => !Number.isFinite(v))) continue;
+ const w = Math.max(Number(r.pop2023) || 0, 1);
+ let agg = byCounty.get(county);
+ if (!agg) { agg = { wsum: [0, 0, 0, 0, 0], pop: 0, n: 0 }; byCounty.set(county, agg); }
+ for (let i = 0; i < 5; i++) agg.wsum[i] += vals[i] * w;
+ agg.pop += w;
+ agg.n++;
+ }
+
+ const maps = await loadRegionMaps();
+ const metricRows: MetricRow[] = [];
+ let skipped = 0;
+ for (const [fips, agg] of byCounty) {
+ const region = maps.byFips.get(fips);
+ if (!region) { skipped++; continue; }
+ for (let i = 0; i < 5; i++) {
+ metricRows.push({
+ regionId: region.id, source: 'hud_fmr', metric: `fmr_${i}br`,
+ period: FY_PERIOD, value: Math.round(agg.wsum[i] / agg.pop),
+ });
+ }
+ }
+ const upserted = await upsertMetricRows(metricRows, runId);
+ if (upserted === 0) throw new Error('0 metric rows parsed — HUD FMR format drift');
+ await closeRun(runId, 'ok', {
+ upserted, skipped, fileHash: sha256,
+ notes: `FY2026 revised, period ${FY_PERIOD}; ${byCounty.size} counties (${rows.length} source rows incl. NE towns)`,
+ });
+ console.log(`hud_fmr: ${byCounty.size} counties, ${upserted} metric rows, ${skipped} unmatched`);
+ } 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
index 86b9883..7d7999e 100644
--- a/src/ingest/run.ts
+++ b/src/ingest/run.ts
@@ -38,14 +38,14 @@ export async function closeRun(
}
/** 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 }> {
+export async function download(url: string, filename: string, opts: { reuseCached?: boolean; headers?: Record<string, string> } = {}): 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' });
+ const res = await fetch(url, { redirect: 'follow', headers: opts.headers });
if (!res.ok) throw new Error(`download failed ${res.status} ${res.statusText}: ${url}`);
const buf = Buffer.from(await res.arrayBuffer());
writeFileSync(dest, buf);
diff --git a/src/server/index.ts b/src/server/index.ts
index 71e6757..c3b90d8 100644
--- a/src/server/index.ts
+++ b/src/server/index.ts
@@ -8,6 +8,7 @@ import { join, dirname } from 'node:path';
import { fileURLToPath } from 'node:url';
import { query } from '../../db/pool.ts';
import { mountWatchlistAdmin } from './watchlist_admin.ts';
+import { mountProperty } from './property.ts';
const __dirname = dirname(fileURLToPath(import.meta.url));
const ROOT = join(__dirname, '..', '..');
@@ -301,6 +302,7 @@ app.get('/api/firm/:id', async (req, res) => {
});
mountWatchlistAdmin(app);
+mountProperty(app);
app.use(express.static(join(ROOT, 'public')));
diff --git a/src/server/property.ts b/src/server/property.ts
new file mode 100644
index 0000000..049e475
--- /dev/null
+++ b/src/server/property.ts
@@ -0,0 +1,290 @@
+/**
+ * M-P1 property-level detail layer. Parcel data is read straight from each county's
+ * registered source (parcel_source table) — LA County (06037) is a read-only SQLite
+ * file built by CRCP (2.43M rows, opened lazily, CRCP scripts/serve.js pattern) —
+ * never copied into PG. County-level context (NRI climate, HUD FMR, ACS, Zillow)
+ * joins in from metric_series.
+ *
+ * The LA roll carries NO owner names, mailing addresses, zoning codes, or sale
+ * prices — those sections return nulls ("coverage pending"), never fabricated.
+ */
+import type { Express, Request, Response } from 'express';
+import { existsSync } from 'node:fs';
+import { join } from 'node:path';
+import { homedir } from 'node:os';
+import { createRequire } from 'node:module';
+import { query } from '../../db/pool.ts';
+
+const require = createRequire(import.meta.url); // better-sqlite3 is native CJS, loaded lazily
+
+const LA_FIPS = '06037';
+export const LA_SQLITE_PATH =
+ process.env.ASSESSOR_SQLITE || join(homedir(), 'Projects', 'commercialrealestate', 'data', 'assessor.sqlite');
+
+interface SqliteStmt { all(...params: unknown[]): unknown[]; get(...params: unknown[]): unknown; }
+interface SqliteDb { prepare(sql: string): SqliteStmt; }
+const dbCache = new Map<string, SqliteDb | null>();
+
+function openParcelDb(path: string): SqliteDb | null {
+ if (dbCache.has(path)) return dbCache.get(path)!;
+ let db: SqliteDb | null = null;
+ try {
+ if (existsSync(path)) {
+ const Database = require('better-sqlite3');
+ db = new Database(path, { readonly: true, fileMustExist: true });
+ }
+ } catch (e) {
+ console.error('[property] sqlite open failed:', e);
+ db = null;
+ }
+ dbCache.set(path, db);
+ return db;
+}
+
+interface ParcelRow {
+ ain: string;
+ situs_house_no: number | null;
+ situs_street: string | null;
+ property_location: string | null;
+ year_built: number | null;
+ effective_year_built: number | null;
+ sqft_main: number | null;
+ bedrooms: number | null;
+ bathrooms: number | null;
+ units: number | null;
+ use_desc1: string | null;
+ use_desc2: string | null;
+ roll_total_value: number | null;
+ roll_land_value: number | null;
+ roll_imp_value: number | null;
+ roll_year: string | null;
+ recording_date: string | null;
+}
+
+async function parcelSourceFor(countyFips: string): Promise<{ source_kind: string; path_or_url: string } | null> {
+ const r = await query<{ source_kind: string; path_or_url: string }>(
+ `SELECT source_kind, path_or_url FROM parcel_source WHERE county_fips = $1`, [countyFips],
+ );
+ return r.rows[0] ?? null;
+}
+
+async function getCountyDb(countyFips: string): Promise<SqliteDb | null> {
+ const src = await parcelSourceFor(countyFips);
+ if (!src || src.source_kind !== 'sqlite') return null;
+ return openParcelDb(src.path_or_url);
+}
+
+function parseAddressQuery(q: string): { houseNo: number | null; street: string } {
+ const m = q.trim().toUpperCase().match(/^(\d+)\s+(.+)$/);
+ if (m) return { houseNo: Number(m[1]), street: m[2].trim() };
+ return { houseNo: null, street: q.trim().toUpperCase() };
+}
+
+const likeEscape = (s: string) => s.replace(/[%_]/g, '\\$&');
+
+const PENDING = 'coverage pending for this county';
+
+export function mountProperty(app: Express) {
+ // address search within a parcel-covered county (LA-only for now)
+ app.get('/api/property/search', async (req: Request, res: Response) => {
+ try {
+ const county = String(req.query.county || LA_FIPS);
+ const q = String(req.query.q || '').trim();
+ if (!q) return res.status(400).json({ error: 'q required' });
+ const db = await getCountyDb(county);
+ if (!db) return res.status(404).json({ error: `no parcel source loaded for county ${county}` });
+
+ const { houseNo, street } = parseAddressQuery(q);
+ let rows: ParcelRow[];
+ if (houseNo != null) {
+ rows = db.prepare(
+ `SELECT * FROM assessor_parcel
+ WHERE situs_house_no = ? AND situs_street LIKE ? ESCAPE '\\'
+ ORDER BY situs_street LIMIT 25`,
+ ).all(houseNo, likeEscape(street) + '%') as ParcelRow[];
+ } else {
+ rows = db.prepare(
+ `SELECT * FROM assessor_parcel
+ WHERE situs_street LIKE ? ESCAPE '\\'
+ ORDER BY situs_street, situs_house_no LIMIT 25`,
+ ).all(likeEscape(street) + '%') as ParcelRow[];
+ }
+ res.json({
+ county, q, count: rows.length,
+ matches: rows.map(r => ({
+ ain: r.ain, address: r.property_location,
+ year_built: r.year_built, sqft: r.sqft_main,
+ bedrooms: r.bedrooms, bathrooms: r.bathrooms, units: r.units,
+ use: r.use_desc2 || r.use_desc1, assessed_value: r.roll_total_value,
+ })),
+ });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+ });
+
+ // full 13-section property payload
+ app.get('/api/property', async (req: Request, res: Response) => {
+ try {
+ const county = String(req.query.county || LA_FIPS);
+ const loc = String(req.query.loc || '').trim();
+ if (!loc) return res.status(400).json({ error: 'loc required (ain or property_location)' });
+ const db = await getCountyDb(county);
+ if (!db) return res.status(404).json({ error: `no parcel source loaded for county ${county}` });
+
+ let parcel = db.prepare(`SELECT * FROM assessor_parcel WHERE ain = ?`).get(loc) as ParcelRow | undefined;
+ if (!parcel) {
+ parcel = db.prepare(`SELECT * FROM assessor_parcel WHERE property_location = ?`).get(loc.toUpperCase()) as ParcelRow | undefined;
+ }
+ if (!parcel) {
+ const { houseNo, street } = parseAddressQuery(loc);
+ if (houseNo != null) {
+ parcel = db.prepare(
+ `SELECT * FROM assessor_parcel WHERE situs_house_no = ? AND situs_street LIKE ? ESCAPE '\\' LIMIT 1`,
+ ).get(houseNo, likeEscape(street) + '%') as ParcelRow | undefined;
+ }
+ }
+ if (!parcel) return res.status(404).json({ error: 'parcel not found: ' + loc });
+
+ // county region + county-level metrics (NRI / FMR / ACS / Zillow / derived)
+ const rg = await query(
+ `SELECT id, canonical_key, name, state_code, fips, cbsa_code, lat, lng, population
+ FROM region WHERE region_type = 'county' AND fips = $1`, [county],
+ );
+ const region = rg.rows[0] ?? null;
+ let latest: Record<string, number> = {};
+ let metroName: string | null = null;
+ let nri: { risk_rating: string | null; top_hazards: any } | null = null;
+ if (region) {
+ const mr = await query<{ metric: string; value: string }>(
+ `SELECT DISTINCT ON (metric) metric, value::text AS value
+ FROM metric_series WHERE region_id = $1
+ ORDER BY metric, period DESC`, [region.id],
+ );
+ for (const row of mr.rows) latest[row.metric] = Number(row.value);
+ if (region.cbsa_code) {
+ const met = await query<{ name: string }>(
+ `SELECT name FROM region WHERE region_type = 'metro' AND cbsa_code = $1`, [region.cbsa_code],
+ );
+ metroName = met.rows[0]?.name ?? null;
+ }
+ const nr = await query<{ risk_rating: string | null; top_hazards: any }>(
+ `SELECT risk_rating, top_hazards FROM nri_ratings WHERE county_fips = $1`, [county],
+ );
+ nri = nr.rows[0] ?? null;
+ }
+
+ // comps: same use, sqft ±30%, recent recording; same street floats up
+ let comps: any[] = [];
+ if (parcel.sqft_main && parcel.use_desc2) {
+ const lo = Math.round(parcel.sqft_main * 0.7), hi = Math.round(parcel.sqft_main * 1.3);
+ comps = (db.prepare(
+ `SELECT ain, property_location, year_built, sqft_main, bedrooms, bathrooms,
+ roll_total_value, roll_year, recording_date
+ FROM assessor_parcel
+ WHERE ain != ? AND use_desc2 = ? AND sqft_main BETWEEN ? AND ?
+ AND recording_date IS NOT NULL
+ ORDER BY (situs_street = ?) DESC, recording_date DESC
+ LIMIT 8`,
+ ).all(parcel.ain, parcel.use_desc2, lo, hi, parcel.situs_street) as any[]).map(c => ({
+ ain: c.ain, address: c.property_location, year_built: c.year_built,
+ sqft: c.sqft_main, bedrooms: c.bedrooms, bathrooms: c.bathrooms,
+ assessed_value: c.roll_total_value, roll_year: c.roll_year, recording_date: c.recording_date,
+ }));
+ }
+
+ const fmr = ['fmr_0br', 'fmr_1br', 'fmr_2br', 'fmr_3br', 'fmr_4br']
+ .some(k => latest[k] != null)
+ ? { fmr_0br: latest.fmr_0br ?? null, fmr_1br: latest.fmr_1br ?? null, fmr_2br: latest.fmr_2br ?? null,
+ fmr_3br: latest.fmr_3br ?? null, fmr_4br: latest.fmr_4br ?? null }
+ : null;
+
+ res.json({
+ county, region_key: region?.canonical_key ?? null,
+ property_details: {
+ ain: parcel.ain, address: parcel.property_location,
+ use: parcel.use_desc2 || parcel.use_desc1, use_category: parcel.use_desc1,
+ year_built: parcel.year_built || null, effective_year_built: parcel.effective_year_built || null,
+ sqft: parcel.sqft_main || null, bedrooms: parcel.bedrooms, bathrooms: parcel.bathrooms,
+ units: parcel.units,
+ },
+ property_history: {
+ last_recording_date: parcel.recording_date,
+ sale_price: null, // not in the LA assessor roll extract
+ note: 'Recording date from the LA County assessor roll; sale prices and full deed chain pending (county recorder feed, M-P2).',
+ },
+ tax_history: {
+ rolls: [{
+ roll_year: parcel.roll_year, total_value: parcel.roll_total_value,
+ land_value: parcel.roll_land_value, improvement_value: parcel.roll_imp_value,
+ }],
+ note: 'Single roll year in the current extract (2025 secured roll); multi-year history pending.',
+ },
+ contact_information: {
+ phone: null, email: null,
+ note: 'Owner contact details are not in the public assessor roll; ' + PENDING + '.',
+ },
+ ownership_information: {
+ owner_name: null, mailing_address: null, owner_occupied: null,
+ note: 'Owner fields are not in the CRCP roll extract; ' + PENDING + ' (full AIN roll or recorder grantee feed, M-P2).',
+ },
+ zoning: {
+ code: null,
+ note: 'Zoning is not in the assessor roll extract; ' + PENDING + ' (ZIMAS / city zoning layers, M-P2).',
+ },
+ contacts: {
+ county_assessor: {
+ name: 'Los Angeles County Office of the Assessor',
+ phone: '(213) 974-3211', website: 'https://assessor.lacounty.gov',
+ },
+ brokers_link: '/brokers.html?state=CA',
+ note: 'Local brokers from the 926k-broker registry; per-parcel listing agent pending.',
+ },
+ map: {
+ lat: region?.lat ?? null, lng: region?.lng ?? null,
+ precision: 'county_centroid',
+ note: 'Parcel lat/lng not in the roll extract — showing the county centroid (parcel geocoding, M-P2).',
+ },
+ comps,
+ climate: region ? {
+ risk_score: latest.nri_risk_score ?? null, eal_score: latest.nri_eal_score ?? null,
+ social_vulnerability: latest.nri_sovi_score ?? null, community_resilience: latest.nri_resl_score ?? null,
+ risk_rating: nri?.risk_rating ?? null, top_hazards: nri?.top_hazards ?? [],
+ source: 'FEMA National Risk Index v1.20.0 (Dec 2025), county level',
+ } : null,
+ rental: region ? {
+ fmr, fmr_year: fmr ? 'FY2026' : null,
+ zori: latest.zori ?? null, rent_yield: latest.rent_yield ?? null,
+ median_gross_rent: latest.median_gross_rent ?? null,
+ source: 'HUD FMR FY2026 + Zillow ZORI + derived, county level',
+ } : null,
+ demographics: region ? {
+ population: latest.population ?? region.population ?? null,
+ median_hh_income: latest.median_hh_income ?? null,
+ median_gross_rent: latest.median_gross_rent ?? null,
+ vacancy_rate: latest.vacancy_rate ?? null, renter_share: latest.renter_share ?? null,
+ source: 'Census ACS 2023 5-yr, county level',
+ } : null,
+ location: region ? {
+ county_name: region.name, state: region.state_code, fips: region.fips,
+ metro: metroName, cbsa_code: region.cbsa_code,
+ lat: region.lat, lng: region.lng,
+ zhvi: latest.zhvi ?? null, zhvi_yoy: latest.zhvi_yoy ?? null,
+ links: { market: '/market.html?key=' + encodeURIComponent(region.canonical_key) },
+ } : null,
+ });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+ });
+
+ // which counties have parcel coverage (drives the market.html entry point)
+ app.get('/api/property/coverage', async (_req: Request, res: Response) => {
+ try {
+ const r = await query(`SELECT county_fips, source_kind, parcel_count, loaded_at FROM parcel_source ORDER BY county_fips`);
+ res.json({ counties: r.rows });
+ } catch (e: any) {
+ res.status(500).json({ error: String(e.message || e) });
+ }
+ });
+}
← 31f2bbc auto-save: 2026-07-21T14:07:54 (8 files) — package-lock.json
·
back to Nationalrealestate
·
CBSA crosswalk backfill: 1915 counties get cbsa_code from Ce 12bd5ee →