← back to Nationalrealestate
M2: Zillow ZHVI/ZORI + Redfin tracker ingests (1.19M rows), crosswalk w/ overrides, dark Leaflet county choropleth
509b9f883df9167719fdfaab21411c2b8727a22a · 2026-07-21 12:45:06 -0700 · Steve Abrams
Files touched
A SOURCES.mdA data/crosswalk-overrides.jsonA data/crosswalk-unmatched.jsonM public/index.htmlA src/ingest/crosswalk.tsA src/ingest/redfin_tracker.tsA src/ingest/zillow_research.ts
Diff
commit 509b9f883df9167719fdfaab21411c2b8727a22a
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 21 12:45:06 2026 -0700
M2: Zillow ZHVI/ZORI + Redfin tracker ingests (1.19M rows), crosswalk w/ overrides, dark Leaflet county choropleth
---
SOURCES.md | 60 +++
data/crosswalk-overrides.json | 1 +
data/crosswalk-unmatched.json | 1003 +++++++++++++++++++++++++++++++++++++++++
public/index.html | 180 +++++++-
src/ingest/crosswalk.ts | 196 ++++++++
src/ingest/redfin_tracker.ts | 203 +++++++++
src/ingest/zillow_research.ts | 160 +++++++
7 files changed, 1786 insertions(+), 17 deletions(-)
diff --git a/SOURCES.md b/SOURCES.md
new file mode 100644
index 0000000..9925db0
--- /dev/null
+++ b/SOURCES.md
@@ -0,0 +1,60 @@
+# Data sources — probe log
+
+Probed: 2026-07-21 (curl -sI / ranged GET). All 7 URLs live — no drift found.
+
+## Zillow Research (files.zillowstatic.com) — source `zillow`
+
+| File | HTTP | Content-Length | Last-Modified |
+|---|---|---|---|
+| zhvi/County_zhvi_uc_sfrcondo_tier_0.33_0.67_sm_sa_month.csv | 200 | 13,465,693 | 2026-07-16 |
+| zhvi/Metro_zhvi_uc_sfrcondo_tier_0.33_0.67_sm_sa_month.csv | 200 | 4,444,528 | 2026-07-16 |
+| zori/County_zori_uc_sfrcondomfr_sm_sa_month.csv | 200 | 1,892,289 | 2026-07-16 |
+| zori/Metro_zori_uc_sfrcondomfr_sm_sa_month.csv | 200 | 1,038,531 | 2026-07-16 |
+
+Format: wide CSV, one row per region, one column per month (`YYYY-MM-DD` month-end headers).
+
+- County header: `RegionID,SizeRank,RegionName,RegionType,StateName,State,Metro,StateCodeFIPS,MunicipalCodeFIPS,<dates...>`
+- Metro header: `RegionID,SizeRank,RegionName,RegionType,StateName,<dates...>`
+- County crosswalk: zero-pad `StateCodeFIPS` (2) + `MunicipalCodeFIPS` (3) → 5-digit FIPS → `region.fips`. match_method `fips`, source_region_id = Zillow RegionID.
+- Metro crosswalk: RegionName is `"Los Angeles, CA"`; region.name is Gazetteer CBSA style `"Los Angeles-Long Beach-Anaheim, CA Metro Area"`. Match: overrides file → exact match after stripping " Metro Area"/" Micro Area" → primary-city prefix (Zillow city == first `-`-segment of CBSA city part AND Zillow state ∈ CBSA state segment). NOTE: metro rows in `region` have empty `state_code`, so state is parsed from the name suffix.
+- Only the LAST 36 monthly columns are ingested. **Period normalization: Zillow month-end header dates are stored as first-of-month** (2026-06-30 → 2026-06-01) so periods line up with Redfin's `period_begin`.
+
+## Redfin Data Center market tracker (redfin-public-data S3) — source `redfin`
+
+| File | HTTP | Content-Length | Last-Modified |
+|---|---|---|---|
+| county_market_tracker.tsv000.gz | 200 | 241,131,599 | 2026-06-02 |
+| redfin_metro_market_tracker.tsv000.gz | 200 | 111,494,355 | 2026-06-02 |
+| state_market_tracker.tsv000.gz | 200 | 8,979,710 | 2026-06-02 |
+
+Format: gzipped TSV, full history, strings double-quoted, numerics/booleans bare. Streamed (gunzip + readline), never buffered.
+
+Actual header columns (identical across county/metro/state files):
+
+```
+PERIOD_BEGIN PERIOD_END PERIOD_DURATION REGION_TYPE REGION_TYPE_ID TABLE_ID
+IS_SEASONALLY_ADJUSTED REGION CITY STATE STATE_CODE PROPERTY_TYPE PROPERTY_TYPE_ID
+MEDIAN_SALE_PRICE[_MOM/_YOY] MEDIAN_LIST_PRICE[...] MEDIAN_PPSF[...] MEDIAN_LIST_PPSF[...]
+HOMES_SOLD[...] PENDING_SALES[...] NEW_LISTINGS[...] INVENTORY[...] MONTHS_OF_SUPPLY[...]
+MEDIAN_DOM[...] AVG_SALE_TO_LIST[...] SOLD_ABOVE_LIST[...] PRICE_DROPS[...]
+OFF_MARKET_IN_TWO_WEEKS[...] PARENT_METRO_REGION PARENT_METRO_REGION_METRO_CODE LAST_UPDATED
+```
+
+Filter choices (recorded per M2 spec):
+
+- `PROPERTY_TYPE = 'All Residential'`
+- `IS_SEASONALLY_ADJUSTED` exists; values are bare `false`/`true` (NOT `f`/`t`) → keep `false`.
+- `PERIOD_DURATION = 30` (monthly) kept as an extra guard.
+- `PERIOD_BEGIN >= 36 months ago` (first-of-month cutoff computed at run time).
+- **No FIPS column exists** in any of the three files → county match is by name: `REGION` `"Los Angeles County, CA"` → strip `", XX"`, match name+state against Gazetteer county names.
+- **Metro `TABLE_ID` IS the CBSA code** (verified: 35840 = North Port FL, 21340 = El Paso TX) → metro match is `TABLE_ID → region.cbsa_code` (match_method `cbsa`), name-match fallback.
+- State match: `STATE_CODE → region.state_code` (match_method `state`).
+- source_region_id stored in source_region_map = Redfin `TABLE_ID`.
+- `period` stored = `PERIOD_BEGIN` (already first-of-month).
+
+Metrics ingested per row: `median_sale_price`, `median_sale_price_yoy`, `homes_sold`, `inventory`, `new_listings`, `dom` (MEDIAN_DOM), `sale_to_list` (AVG_SALE_TO_LIST), `months_of_supply`.
+
+## Crosswalk files
+
+- `data/crosswalk-overrides.json` — manual overrides consulted BEFORE fuzzy matching. Shape: `{ "zillow": { "<RegionName>": "<canonical_key>" }, "redfin": { "<REGION or TABLE_ID>": "<canonical_key>" } }`.
+- `data/crosswalk-unmatched.json` — written by each run; unmatched regions are SKIPPED, never guessed.
diff --git a/data/crosswalk-overrides.json b/data/crosswalk-overrides.json
new file mode 100644
index 0000000..0967ef4
--- /dev/null
+++ b/data/crosswalk-overrides.json
@@ -0,0 +1 @@
+{}
diff --git a/data/crosswalk-unmatched.json b/data/crosswalk-unmatched.json
new file mode 100644
index 0000000..61f334b
--- /dev/null
+++ b/data/crosswalk-unmatched.json
@@ -0,0 +1,1003 @@
+{
+ "zillow_county": [
+ {
+ "regionId": "2694",
+ "regionName": "Fairfield County",
+ "fips": "09/001"
+ },
+ {
+ "regionId": "2337",
+ "regionName": "Hartford County",
+ "fips": "09/003"
+ },
+ {
+ "regionId": "2815",
+ "regionName": "New Haven County",
+ "fips": "09/009"
+ },
+ {
+ "regionId": "2987",
+ "regionName": "New London County",
+ "fips": "09/011"
+ },
+ {
+ "regionId": "2955",
+ "regionName": "Litchfield County",
+ "fips": "09/005"
+ },
+ {
+ "regionId": "2800",
+ "regionName": "Middlesex County",
+ "fips": "09/007"
+ },
+ {
+ "regionId": "2054",
+ "regionName": "Tolland County",
+ "fips": "09/013"
+ },
+ {
+ "regionId": "2097",
+ "regionName": "Windham County",
+ "fips": "09/015"
+ },
+ {
+ "regionId": "2694",
+ "regionName": "Fairfield County",
+ "fips": "09/001"
+ },
+ {
+ "regionId": "2337",
+ "regionName": "Hartford County",
+ "fips": "09/003"
+ },
+ {
+ "regionId": "2815",
+ "regionName": "New Haven County",
+ "fips": "09/009"
+ },
+ {
+ "regionId": "2987",
+ "regionName": "New London County",
+ "fips": "09/011"
+ },
+ {
+ "regionId": "2955",
+ "regionName": "Litchfield County",
+ "fips": "09/005"
+ },
+ {
+ "regionId": "2800",
+ "regionName": "Middlesex County",
+ "fips": "09/007"
+ },
+ {
+ "regionId": "2054",
+ "regionName": "Tolland County",
+ "fips": "09/013"
+ },
+ {
+ "regionId": "2097",
+ "regionName": "Windham County",
+ "fips": "09/015"
+ }
+ ],
+ "zillow_metro": [
+ {
+ "regionId": "394814",
+ "regionName": "Madera, CA"
+ },
+ {
+ "regionId": "394802",
+ "regionName": "London, KY"
+ },
+ {
+ "regionId": "395153",
+ "regionName": "The Villages, FL"
+ },
+ {
+ "regionId": "753872",
+ "regionName": "California, MD"
+ },
+ {
+ "regionId": "394932",
+ "regionName": "Ogdensburg, NY"
+ },
+ {
+ "regionId": "394340",
+ "regionName": "Ashtabula, OH"
+ },
+ {
+ "regionId": "394928",
+ "regionName": "Ocean City, NJ"
+ },
+ {
+ "regionId": "394907",
+ "regionName": "New Castle, PA"
+ },
+ {
+ "regionId": "753881",
+ "regionName": "Glenwood Springs, CO"
+ },
+ {
+ "regionId": "394629",
+ "regionName": "Georgetown, SC"
+ },
+ {
+ "regionId": "394592",
+ "regionName": "Fernley, NV"
+ },
+ {
+ "regionId": "753877",
+ "regionName": "Cullowhee, NC"
+ },
+ {
+ "regionId": "394989",
+ "regionName": "Point Pleasant, WV"
+ },
+ {
+ "regionId": "395122",
+ "regionName": "St. Marys, GA"
+ },
+ {
+ "regionId": "394608",
+ "regionName": "Fort Polk South, LA"
+ },
+ {
+ "regionId": "394437",
+ "regionName": "Ca±on City, CO"
+ },
+ {
+ "regionId": "394819",
+ "regionName": "Malone, NY"
+ },
+ {
+ "regionId": "753865",
+ "regionName": "Bardstown, KY"
+ },
+ {
+ "regionId": "394574",
+ "regionName": "Espa±ola, NM"
+ },
+ {
+ "regionId": "753866",
+ "regionName": "Big Stone Gap, VA"
+ },
+ {
+ "regionId": "753878",
+ "regionName": "Dayton, TN"
+ },
+ {
+ "regionId": "753892",
+ "regionName": "Jackson, OH"
+ },
+ {
+ "regionId": "394481",
+ "regionName": "Coffeyville, KS"
+ },
+ {
+ "regionId": "394383",
+ "regionName": "Berlin, NH"
+ },
+ {
+ "regionId": "394921",
+ "regionName": "North Vernon, IN"
+ },
+ {
+ "regionId": "394642",
+ "regionName": "Grants, NM"
+ },
+ {
+ "regionId": "394381",
+ "regionName": "Bennettsville, SC"
+ },
+ {
+ "regionId": "394706",
+ "regionName": "Indianola, MS"
+ },
+ {
+ "regionId": "395214",
+ "regionName": "Wauchula, FL"
+ },
+ {
+ "regionId": "395004",
+ "regionName": "Prineville, OR"
+ },
+ {
+ "regionId": "394784",
+ "regionName": "Levelland, TX"
+ },
+ {
+ "regionId": "394965",
+ "regionName": "Parsons, KS"
+ },
+ {
+ "regionId": "394996",
+ "regionName": "Portales, NM"
+ },
+ {
+ "regionId": "394841",
+ "regionName": "Maysville, KY"
+ },
+ {
+ "regionId": "394676",
+ "regionName": "Helena, AR"
+ },
+ {
+ "regionId": "753880",
+ "regionName": "Fairfield, IA"
+ },
+ {
+ "regionId": "394968",
+ "regionName": "Pecos, TX"
+ },
+ {
+ "regionId": "753874",
+ "regionName": "Craig, CO"
+ },
+ {
+ "regionId": "394767",
+ "regionName": "Lamesa, TX"
+ },
+ {
+ "regionId": "845159",
+ "regionName": "Poughkeepsie, NY"
+ },
+ {
+ "regionId": "394814",
+ "regionName": "Madera, CA"
+ },
+ {
+ "regionId": "394802",
+ "regionName": "London, KY"
+ },
+ {
+ "regionId": "395153",
+ "regionName": "The Villages, FL"
+ },
+ {
+ "regionId": "753872",
+ "regionName": "California, MD"
+ },
+ {
+ "regionId": "394932",
+ "regionName": "Ogdensburg, NY"
+ },
+ {
+ "regionId": "394340",
+ "regionName": "Ashtabula, OH"
+ },
+ {
+ "regionId": "394928",
+ "regionName": "Ocean City, NJ"
+ },
+ {
+ "regionId": "394907",
+ "regionName": "New Castle, PA"
+ },
+ {
+ "regionId": "753881",
+ "regionName": "Glenwood Springs, CO"
+ },
+ {
+ "regionId": "845163",
+ "regionName": "Jasper, AL"
+ },
+ {
+ "regionId": "394629",
+ "regionName": "Georgetown, SC"
+ },
+ {
+ "regionId": "394592",
+ "regionName": "Fernley, NV"
+ },
+ {
+ "regionId": "753877",
+ "regionName": "Cullowhee, NC"
+ },
+ {
+ "regionId": "394989",
+ "regionName": "Point Pleasant, WV"
+ },
+ {
+ "regionId": "395122",
+ "regionName": "St. Marys, GA"
+ },
+ {
+ "regionId": "394608",
+ "regionName": "Fort Polk South, LA"
+ },
+ {
+ "regionId": "394437",
+ "regionName": "Ca±on City, CO"
+ },
+ {
+ "regionId": "753865",
+ "regionName": "Bardstown, KY"
+ },
+ {
+ "regionId": "845172",
+ "regionName": "Winfield, KS"
+ },
+ {
+ "regionId": "753878",
+ "regionName": "Dayton, TN"
+ },
+ {
+ "regionId": "394481",
+ "regionName": "Coffeyville, KS"
+ },
+ {
+ "regionId": "394383",
+ "regionName": "Berlin, NH"
+ },
+ {
+ "regionId": "395004",
+ "regionName": "Prineville, OR"
+ },
+ {
+ "regionId": "845169",
+ "regionName": "Rockport, TX"
+ },
+ {
+ "regionId": "394996",
+ "regionName": "Portales, NM"
+ },
+ {
+ "regionId": "786253",
+ "regionName": "Brownsville, TN"
+ },
+ {
+ "regionId": "753880",
+ "regionName": "Fairfield, IA"
+ }
+ ],
+ "redfin_county": [
+ {
+ "tableId": "3053",
+ "region": "Manassas Park, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3062",
+ "region": "Richmond City County, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "195",
+ "region": "Juneau Borough, AK",
+ "stateCode": "AK"
+ },
+ {
+ "tableId": "2984",
+ "region": "King & Queen County, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "428",
+ "region": "Middlesex County, CT",
+ "stateCode": "CT"
+ },
+ {
+ "tableId": "3048",
+ "region": "Harrisonburg, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3067",
+ "region": "Virginia Beach, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3069",
+ "region": "Williamsburg, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "430",
+ "region": "New London County, CT",
+ "stateCode": "CT"
+ },
+ {
+ "tableId": "3034",
+ "region": "Bristol, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3040",
+ "region": "Danville, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3070",
+ "region": "Winchester, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3038",
+ "region": "Colonial Heights, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "1333",
+ "region": "Baltimore City County, MD",
+ "stateCode": "MD"
+ },
+ {
+ "tableId": "3050",
+ "region": "Lexington, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3043",
+ "region": "Falls Church, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3066",
+ "region": "Suffolk, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "426",
+ "region": "Hartford County, CT",
+ "stateCode": "CT"
+ },
+ {
+ "tableId": "3060",
+ "region": "Portsmouth, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3039",
+ "region": "Covington, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3037",
+ "region": "Chesapeake, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3041",
+ "region": "Emporia, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3032",
+ "region": "Alexandria, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3036",
+ "region": "Charlottesville, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "211",
+ "region": "Yakutat Borough, AK",
+ "stateCode": "AK"
+ },
+ {
+ "tableId": "3047",
+ "region": "Hampton, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "1259",
+ "region": "La Salle Parish, LA",
+ "stateCode": "LA"
+ },
+ {
+ "tableId": "427",
+ "region": "Litchfield County, CT",
+ "stateCode": "CT"
+ },
+ {
+ "tableId": "208",
+ "region": "Valdez-Cordova Census Area, AK",
+ "stateCode": "AK"
+ },
+ {
+ "tableId": "1714",
+ "region": "St. Louis City County, MO",
+ "stateCode": "MO"
+ },
+ {
+ "tableId": "188",
+ "region": "Anchorage Borough, AK",
+ "stateCode": "AK"
+ },
+ {
+ "tableId": "3051",
+ "region": "Lynchburg, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3042",
+ "region": "Fairfax City County, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3045",
+ "region": "Fredericksburg, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3049",
+ "region": "Hopewell, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3065",
+ "region": "Staunton, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "429",
+ "region": "New Haven County, CT",
+ "stateCode": "CT"
+ },
+ {
+ "tableId": "3068",
+ "region": "Waynesboro, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "210",
+ "region": "Wrangell Borough, AK",
+ "stateCode": "AK"
+ },
+ {
+ "tableId": "3052",
+ "region": "Manassas, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3044",
+ "region": "Franklin City County, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3054",
+ "region": "Martinsville, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "432",
+ "region": "Windham County, CT",
+ "stateCode": "CT"
+ },
+ {
+ "tableId": "425",
+ "region": "Fairfield County, CT",
+ "stateCode": "CT"
+ },
+ {
+ "tableId": "1739",
+ "region": "Lewis & Clark County, MT",
+ "stateCode": "MT"
+ },
+ {
+ "tableId": "3059",
+ "region": "Poquoson, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "431",
+ "region": "Tolland County, CT",
+ "stateCode": "CT"
+ },
+ {
+ "tableId": "3058",
+ "region": "Petersburg, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3063",
+ "region": "Roanoke City County, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3064",
+ "region": "Salem, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3061",
+ "region": "Radford, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3057",
+ "region": "Norton, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3035",
+ "region": "Buena Vista, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3046",
+ "region": "Galax, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "3056",
+ "region": "Norfolk, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "205",
+ "region": "Sitka Borough, AK",
+ "stateCode": "AK"
+ },
+ {
+ "tableId": "3055",
+ "region": "Newport News, VA",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "206",
+ "region": "Skagway Borough, AK",
+ "stateCode": "AK"
+ }
+ ],
+ "redfin_metro": [
+ {
+ "tableId": "36300",
+ "region": "Ogdensburg, NY metro area",
+ "stateCode": "NY"
+ },
+ {
+ "tableId": "35260",
+ "region": "New Castle, PA metro area",
+ "stateCode": "PA"
+ },
+ {
+ "tableId": "35154",
+ "region": "New Brunswick, NJ metro area",
+ "stateCode": "NJ"
+ },
+ {
+ "tableId": "45540",
+ "region": "The Villages, FL metro area",
+ "stateCode": "FL"
+ },
+ {
+ "tableId": "27660",
+ "region": "Jennings, LA metro area",
+ "stateCode": "LA"
+ },
+ {
+ "tableId": "22860",
+ "region": "Fort Polk South, LA metro area",
+ "stateCode": "LA"
+ },
+ {
+ "tableId": "35860",
+ "region": "North Vernon, IN metro area",
+ "stateCode": "IN"
+ },
+ {
+ "tableId": "13720",
+ "region": "Big Stone Gap, VA metro area",
+ "stateCode": "VA"
+ },
+ {
+ "tableId": "39100",
+ "region": "Poughkeepsie, NY metro area",
+ "stateCode": "NY"
+ },
+ {
+ "tableId": "23104",
+ "region": "Fort Worth, TX metro area",
+ "stateCode": "TX"
+ },
+ {
+ "tableId": "45104",
+ "region": "Tacoma, WA metro area",
+ "stateCode": "WA"
+ },
+ {
+ "tableId": "16420",
+ "region": "Central City, KY metro area",
+ "stateCode": "KY"
+ },
+ {
+ "tableId": "17700",
+ "region": "Coffeyville, KS metro area",
+ "stateCode": "KS"
+ },
+ {
+ "tableId": "35084",
+ "region": "Newark, NJ metro area",
+ "stateCode": "NJ"
+ },
+ {
+ "tableId": "19420",
+ "region": "Dayton, TN metro area",
+ "stateCode": "TN"
+ },
+ {
+ "tableId": "40530",
+ "region": "Rockport, TX metro area",
+ "stateCode": "TX"
+ },
+ {
+ "tableId": "22744",
+ "region": "Fort Lauderdale, FL metro area",
+ "stateCode": "FL"
+ },
+ {
+ "tableId": "12120",
+ "region": "Atmore, AL metro area",
+ "stateCode": "AL"
+ },
+ {
+ "tableId": "38580",
+ "region": "Point Pleasant, WV metro area",
+ "stateCode": "WV"
+ },
+ {
+ "tableId": "26260",
+ "region": "Hope, AR metro area",
+ "stateCode": "AR"
+ },
+ {
+ "tableId": "34350",
+ "region": "Mount Gay, WV metro area",
+ "stateCode": "WV"
+ },
+ {
+ "tableId": "35004",
+ "region": "Nassau County, NY metro area",
+ "stateCode": "NY"
+ },
+ {
+ "tableId": "46420",
+ "region": "Union, SC metro area",
+ "stateCode": "SC"
+ },
+ {
+ "tableId": "31460",
+ "region": "Madera, CA metro area",
+ "stateCode": "CA"
+ },
+ {
+ "tableId": "36084",
+ "region": "Oakland, CA metro area",
+ "stateCode": "CA"
+ },
+ {
+ "tableId": "27530",
+ "region": "Jasper, AL metro area",
+ "stateCode": "AL"
+ },
+ {
+ "tableId": "38780",
+ "region": "Portales, NM metro area",
+ "stateCode": "NM"
+ },
+ {
+ "tableId": "30220",
+ "region": "Levelland, TX metro area",
+ "stateCode": "TX"
+ },
+ {
+ "tableId": "23860",
+ "region": "Georgetown, SC metro area",
+ "stateCode": "SC"
+ },
+ {
+ "tableId": "11780",
+ "region": "Ashtabula, OH metro area",
+ "stateCode": "OH"
+ },
+ {
+ "tableId": "20994",
+ "region": "Elgin, IL metro area",
+ "stateCode": "IL"
+ },
+ {
+ "tableId": "48864",
+ "region": "Wilmington, DE metro area",
+ "stateCode": "DE"
+ },
+ {
+ "tableId": "33874",
+ "region": "Montgomery County, PA metro area",
+ "stateCode": "PA"
+ },
+ {
+ "tableId": "26940",
+ "region": "Indianola, MS metro area",
+ "stateCode": "MS"
+ },
+ {
+ "tableId": "41220",
+ "region": "St. Marys, GA metro area",
+ "stateCode": "GA"
+ },
+ {
+ "tableId": "14160",
+ "region": "Bluffton, IN metro area",
+ "stateCode": "IN"
+ },
+ {
+ "tableId": "47664",
+ "region": "Warren, MI metro area",
+ "stateCode": "MI"
+ },
+ {
+ "tableId": "23844",
+ "region": "Gary, IN metro area",
+ "stateCode": "IN"
+ },
+ {
+ "tableId": "39260",
+ "region": "Prineville, OR metro area",
+ "stateCode": "OR"
+ },
+ {
+ "tableId": "48424",
+ "region": "West Palm Beach, FL metro area",
+ "stateCode": "FL"
+ },
+ {
+ "tableId": "15140",
+ "region": "Brownsville, TN metro area",
+ "stateCode": "TN"
+ },
+ {
+ "tableId": "48100",
+ "region": "Wauchula, FL metro area",
+ "stateCode": "FL"
+ },
+ {
+ "tableId": "49060",
+ "region": "Winfield, KS metro area",
+ "stateCode": "KS"
+ },
+ {
+ "tableId": "40484",
+ "region": "Rockingham County, NH metro area",
+ "stateCode": "NH"
+ },
+ {
+ "tableId": "36140",
+ "region": "Ocean City, NJ metro area",
+ "stateCode": "NJ"
+ },
+ {
+ "tableId": "42500",
+ "region": "Scottsburg, IN metro area",
+ "stateCode": "IN"
+ },
+ {
+ "tableId": "13500",
+ "region": "Bennettsville, SC metro area",
+ "stateCode": "SC"
+ },
+ {
+ "tableId": "37660",
+ "region": "Parsons, KS metro area",
+ "stateCode": "KS"
+ },
+ {
+ "tableId": "23224",
+ "region": "Frederick, MD metro area",
+ "stateCode": "MD"
+ },
+ {
+ "tableId": "32500",
+ "region": "Maysville, KY metro area",
+ "stateCode": "KY"
+ },
+ {
+ "tableId": "27160",
+ "region": "Jackson, OH metro area",
+ "stateCode": "OH"
+ },
+ {
+ "tableId": "18780",
+ "region": "Craig, CO metro area",
+ "stateCode": "CO"
+ },
+ {
+ "tableId": "24060",
+ "region": "Glenwood Springs, CO metro area",
+ "stateCode": "CO"
+ },
+ {
+ "tableId": "25760",
+ "region": "Helena, AR metro area",
+ "stateCode": "AR"
+ },
+ {
+ "tableId": "19000",
+ "region": "Cullowhee, NC metro area",
+ "stateCode": "NC"
+ },
+ {
+ "tableId": "24380",
+ "region": "Grants, NM metro area",
+ "stateCode": "NM"
+ },
+ {
+ "tableId": "37780",
+ "region": "Pecos, TX metro area",
+ "stateCode": "TX"
+ },
+ {
+ "tableId": "37770",
+ "region": "Pearsall, TX metro area",
+ "stateCode": "TX"
+ },
+ {
+ "tableId": "12680",
+ "region": "Bardstown, KY metro area",
+ "stateCode": "KY"
+ },
+ {
+ "tableId": "42034",
+ "region": "San Rafael, CA metro area",
+ "stateCode": "CA"
+ },
+ {
+ "tableId": "22280",
+ "region": "Fernley, NV metro area",
+ "stateCode": "NV"
+ },
+ {
+ "tableId": "15804",
+ "region": "Camden, NJ metro area",
+ "stateCode": "NJ"
+ },
+ {
+ "tableId": "29404",
+ "region": "Lake County, IL metro area",
+ "stateCode": "IL"
+ },
+ {
+ "tableId": "29500",
+ "region": "Lamesa, TX metro area",
+ "stateCode": "TX"
+ },
+ {
+ "tableId": "11244",
+ "region": "Anaheim, CA metro area",
+ "stateCode": "CA"
+ },
+ {
+ "tableId": "30940",
+ "region": "London, KY metro area",
+ "stateCode": "KY"
+ },
+ {
+ "tableId": "15680",
+ "region": "California, MD metro area",
+ "stateCode": "MD"
+ },
+ {
+ "tableId": "13620",
+ "region": "Berlin, NH metro area",
+ "stateCode": "NH"
+ },
+ {
+ "tableId": "31660",
+ "region": "Malone, NY metro area",
+ "stateCode": "NY"
+ },
+ {
+ "tableId": "21840",
+ "region": "Fairfield, IA metro area",
+ "stateCode": "IA"
+ },
+ {
+ "tableId": "48500",
+ "region": "West Point, MS metro area",
+ "stateCode": "MS"
+ }
+ ]
+}
\ No newline at end of file
diff --git a/public/index.html b/public/index.html
index 4dd2db8..874e5e8 100644
--- a/public/index.html
+++ b/public/index.html
@@ -3,30 +3,176 @@
<head>
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1">
-<title>USRealEstate — National Market Explorer</title>
+<title>USRealEstate — National Market Map</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;}
- *{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;}
+ *{box-sizing:border-box;}
+ html,body{margin:0;height:100%;background:var(--bg);color:var(--fg);
+ font:14px/1.5 -apple-system,Segoe UI,Roboto,sans-serif;}
+ #topbar{position:fixed;inset:0 0 auto 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);}
+ #metrics{display:flex;gap:6px;margin-left:auto;flex-wrap:wrap;}
+ #metrics button{background:var(--bg);border:1px solid var(--line);color:var(--muted);border-radius:8px;
+ padding:6px 12px;font:12px -apple-system,Segoe UI,Roboto,sans-serif;cursor:pointer;}
+ #metrics button:hover{color:var(--fg);border-color:var(--muted);}
+ #metrics button.sel{color:var(--bg);background:var(--gold);border-color:var(--gold);font-weight:700;}
+ #map{position:absolute;inset:0;background:var(--bg);}
+ .leaflet-container{background:var(--bg);}
+ .basemap-dark{filter:brightness(.55) contrast(1.1) saturate(.4);}
+ #legend{position:fixed;left:14px;bottom:18px;z-index:1000;background:rgba(17,21,29,.94);
+ border:1px solid var(--line);border-radius:10px;padding:10px 14px;min-width:170px;}
+ #legend .t{font-size:12px;color:var(--gold);font-weight:700;margin-bottom:6px;}
+ #legend .row{display:flex;align-items:center;gap:8px;font-size:11px;color:var(--muted);margin:2px 0;
+ font-variant-numeric:tabular-nums;}
+ #legend .sw{width:16px;height:11px;border-radius:2px;border:1px solid rgba(255,255,255,.15);flex:none;}
+ #status{position:fixed;right:14px;bottom:18px;z-index:1000;font-size:11px;color:var(--muted);
+ background:rgba(17,21,29,.9);border:1px solid var(--line);border-radius:8px;padding:5px 10px;}
+ .leaflet-tooltip.county-tip{background:var(--panel);border:1px solid var(--gold);color:var(--fg);
+ border-radius:8px;box-shadow:0 4px 16px rgba(0,0,0,.5);font:12px -apple-system,Segoe UI,Roboto,sans-serif;}
+ .county-tip .nm{font-weight:700;} .county-tip .vl{color:var(--gold);font-variant-numeric:tabular-nums;}
+ .leaflet-control-attribution{background:rgba(17,21,29,.8)!important;color:var(--muted)!important;}
+ .leaflet-control-attribution a{color:var(--muted)!important;}
</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 id="topbar">
+ <div class="brand"><b>USRealEstate</b> — National Market Map</div>
+ <nav>
+ <a href="/" class="active">Map</a>
+ <a href="/markets.html">Markets</a>
+ <a href="/watchlist.html">Watchlist</a>
+ <a href="/admin.html">Admin</a>
+ </nav>
+ <div id="metrics"></div>
</div>
+<div id="map"></div>
+<div id="legend"><div class="t">Loading…</div></div>
+<div id="status">loading county data…</div>
+
+<script src="https://unpkg.com/leaflet@1.9.4/dist/leaflet.js"
+ integrity="sha256-20nQCchB9co0qIjJZRGuk2/Z9VM+kNiyxNV1lvTlZBo=" crossorigin=""></script>
<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';
-});
+const METRICS = [
+ { key: 'zhvi', label: 'ZHVI', fmt: usd0 },
+ { key: 'median_sale_price', label: 'Median Sale Price', fmt: usd0 },
+ { key: 'inventory', label: 'Inventory', fmt: int0 },
+ { key: 'dom', label: 'DOM', fmt: v => int0(v) + ' days' },
+ { key: 'months_of_supply', label: 'Months Supply', fmt: v => v.toFixed(1) + ' mo' },
+];
+// gold -> deep blue, 7 classes (high = gold)
+const RAMP = ['#1b2a4a','#27406b','#3b5a83','#5f7a8e','#8f9377','#b59b5c','#c8a24b'];
+const NO_DATA = '#141922';
+
+function usd0(v){ return '$' + Math.round(v).toLocaleString(); }
+function int0(v){ return Math.round(v).toLocaleString(); }
+
+let currentMetric = localStorage.getItem('usre.metric');
+if (!METRICS.some(m => m.key === currentMetric)) currentMetric = 'zhvi';
+
+const map = L.map('map', { zoomControl: true, minZoom: 3, maxZoom: 11 }).setView([38.5, -96.5], 4);
+const tiles = L.tileLayer('https://{s}.basemaps.cartocdn.com/dark_nolabels/{z}/{x}/{y}{r}.png', {
+ attribution: '© OpenStreetMap © CARTO', subdomains: 'abcd', className: 'basemap-dark',
+}).addTo(map);
+
+let geoLayer = null;
+let values = {};
+let breaks = [];
+let geojson = null;
+
+function classColor(v){
+ if (v == null || !Number.isFinite(v)) return NO_DATA;
+ for (let i = breaks.length - 1; i >= 0; i--) if (v >= breaks[i]) return RAMP[i];
+ return RAMP[0];
+}
+
+function quantileBreaks(vals, n){
+ const s = [...vals].sort((a,b)=>a-b);
+ const out = [];
+ for (let i = 0; i < n; i++) out.push(s[Math.min(s.length - 1, Math.floor(i * s.length / n))]);
+ return out;
+}
+
+function styleFeature(f){
+ return { weight: .35, color: '#222936', fillOpacity: .82, fillColor: classColor(values[f.id]) };
+}
+
+function metricDef(){ return METRICS.find(m => m.key === currentMetric); }
+
+function renderLegend(count){
+ const md = metricDef();
+ const el = document.getElementById('legend');
+ if (!breaks.length){ el.innerHTML = `<div class="t">${md.label}</div><div class="row">no data yet</div>`; return; }
+ let html = `<div class="t">${md.label}</div>`;
+ for (let i = RAMP.length - 1; i >= 0; i--){
+ const lo = md.fmt(breaks[i]);
+ const hi = i < RAMP.length - 1 ? md.fmt(breaks[i + 1]) : null;
+ html += `<div class="row"><span class="sw" style="background:${RAMP[i]}"></span>${hi ? lo + ' – ' + hi : lo + '+'}</div>`;
+ }
+ html += `<div class="row"><span class="sw" style="background:${NO_DATA}"></span>no data</div>`;
+ el.innerHTML = html;
+ document.getElementById('status').textContent = `${count.toLocaleString()} counties · latest period`;
+}
+
+async function loadMetric(key){
+ currentMetric = key;
+ localStorage.setItem('usre.metric', key);
+ renderButtons();
+ document.getElementById('status').textContent = 'loading ' + key + '…';
+ const d = await fetch('/api/choropleth?metric=' + encodeURIComponent(key)).then(r => r.json());
+ values = d.values || {};
+ const vals = Object.values(values).filter(Number.isFinite);
+ breaks = vals.length ? quantileBreaks(vals, RAMP.length) : [];
+ if (geoLayer) geoLayer.setStyle(styleFeature);
+ renderLegend(vals.length);
+}
+
+function renderButtons(){
+ const el = document.getElementById('metrics');
+ el.innerHTML = '';
+ for (const m of METRICS){
+ const b = document.createElement('button');
+ b.textContent = m.label;
+ b.className = m.key === currentMetric ? 'sel' : '';
+ b.onclick = () => loadMetric(m.key);
+ el.appendChild(b);
+ }
+}
+
+function onEachFeature(f, layer){
+ layer.on('mouseover', () => {
+ const md = metricDef();
+ const v = values[f.id];
+ const nm = `${f.properties.NAME} ${f.properties.LSAD || 'County'}`;
+ layer.setStyle({ weight: 1.6, color: '#c8a24b' });
+ layer.bringToFront();
+ layer.bindTooltip(
+ `<div class="nm">${nm}</div><div class="vl">${v != null && Number.isFinite(v) ? md.fmt(v) : 'no data'}</div>`,
+ { className: 'county-tip', sticky: true },
+ ).openTooltip();
+ });
+ layer.on('mouseout', () => { geoLayer.resetStyle(layer); layer.closeTooltip(); });
+ layer.on('click', () => {
+ const url = `/market.html?key=county:${f.id}`;
+ // detail page lands in M3 — href is wired, alert placeholder until then
+ alert(`${f.properties.NAME} ${f.properties.LSAD || 'County'}\nDetail page lands in M3:\n${url}`);
+ });
+}
+
+(async function init(){
+ renderButtons();
+ const [geo] = await Promise.all([
+ fetch('/data/counties.geojson').then(r => r.json()),
+ loadMetric(currentMetric),
+ ]);
+ geojson = geo;
+ geoLayer = L.geoJSON(geo, { style: styleFeature, onEachFeature }).addTo(map);
+})();
</script>
</body>
</html>
diff --git a/src/ingest/crosswalk.ts b/src/ingest/crosswalk.ts
new file mode 100644
index 0000000..d867517
--- /dev/null
+++ b/src/ingest/crosswalk.ts
@@ -0,0 +1,196 @@
+/**
+ * Region-crosswalk helpers shared by ingest jobs.
+ * Loads the full region table once, builds lookup maps, consults
+ * data/crosswalk-overrides.json before any fuzzy match, and collects
+ * unmatched source regions into data/crosswalk-unmatched.json (skipped, never guessed).
+ */
+import { readFileSync, writeFileSync, existsSync, mkdirSync } 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 DATA_DIR = join(__dirname, '..', '..', 'data');
+const OVERRIDES_PATH = join(DATA_DIR, 'crosswalk-overrides.json');
+const UNMATCHED_PATH = join(DATA_DIR, 'crosswalk-unmatched.json');
+
+export interface RegionRow {
+ id: number;
+ region_type: string;
+ canonical_key: string;
+ name: string;
+ state_code: string | null;
+ fips: string | null;
+ cbsa_code: string | null;
+}
+
+export interface RegionMaps {
+ byFips: Map<string, RegionRow>;
+ byCbsa: Map<string, RegionRow>;
+ byStateCode: Map<string, RegionRow>;
+ byCountyNameState: Map<string, RegionRow>; // "los angeles county|CA"
+ byCanonicalKey: Map<string, RegionRow>;
+ metros: RegionRow[];
+}
+
+export async function loadRegionMaps(): Promise<RegionMaps> {
+ const r = await query<RegionRow>(
+ `SELECT id, region_type, canonical_key, name, state_code, fips, cbsa_code FROM region`,
+ );
+ const maps: RegionMaps = {
+ byFips: new Map(),
+ byCbsa: new Map(),
+ byStateCode: new Map(),
+ byCountyNameState: new Map(),
+ byCanonicalKey: new Map(),
+ metros: [],
+ };
+ for (const row of r.rows) {
+ maps.byCanonicalKey.set(row.canonical_key, row);
+ if (row.region_type === 'county' && row.fips) {
+ maps.byFips.set(row.fips, row);
+ if (row.state_code) maps.byCountyNameState.set(normName(row.name) + '|' + row.state_code, row);
+ }
+ if (row.region_type === 'metro') {
+ maps.metros.push(row);
+ if (row.cbsa_code) maps.byCbsa.set(row.cbsa_code, row);
+ }
+ if (row.region_type === 'state' && row.state_code) maps.byStateCode.set(row.state_code, row);
+ }
+ return maps;
+}
+
+export function normName(s: string): string {
+ // strip diacritics via NFD + combining-mark range (Dona Ana vs Doña Ana)
+ return s.toLowerCase().normalize('NFD').replace(/[̀-ͯ]/g, '').replace(/\s+/g, ' ').trim();
+}
+
+export function loadOverrides(source: string): Record<string, string> {
+ if (!existsSync(OVERRIDES_PATH)) return {};
+ const all = JSON.parse(readFileSync(OVERRIDES_PATH, 'utf8'));
+ return all[source] || {};
+}
+
+/** Metro matcher for Gazetteer CBSA names like "Los Angeles-Long Beach-Anaheim, CA Metro Area". */
+export function buildMetroMatcher(maps: RegionMaps) {
+ const exact = new Map<string, RegionRow>(); // "los angeles-long beach-anaheim, ca"
+ const cityState = new Map<string, RegionRow>(); // "los angeles|CA" (each -segment × each state)
+ for (const m of maps.metros) {
+ const stripped = m.name.replace(/\s+(Metro|Micro)\s+Area$/i, '');
+ exact.set(normName(stripped), m);
+ const comma = stripped.lastIndexOf(',');
+ if (comma < 0) continue;
+ const cityPart = stripped.slice(0, comma);
+ const states = stripped.slice(comma + 1).trim().split('-');
+ const firstCity = cityPart.split(/[-/]/)[0]; // "Louisville/Jefferson County" -> "Louisville"
+ for (const st of states) {
+ const key = normName(firstCity) + '|' + st.trim();
+ if (!cityState.has(key)) cityState.set(key, m); // first (largest CBSA seeded order) wins; ties skipped by has-check
+ }
+ }
+ /** name = e.g. "Los Angeles, CA" (Zillow style). Returns region or null. */
+ return function matchMetro(name: string): { region: RegionRow; method: string } | null {
+ const comma = name.lastIndexOf(',');
+ const ex = exact.get(normName(name));
+ if (ex) return { region: ex, method: 'cbsa_name_exact' };
+ if (comma < 0) return null;
+ const city = name.slice(0, comma);
+ const st = name.slice(comma + 1).trim();
+ const cs = cityState.get(normName(city) + '|' + st);
+ if (cs) return { region: cs, method: 'cbsa_city_prefix' };
+ return null;
+ };
+}
+
+export class UnmatchedCollector {
+ private items: Record<string, unknown[]> = {};
+ add(source: string, item: unknown) {
+ (this.items[source] ||= []).push(item);
+ }
+ count(source: string): number {
+ return (this.items[source] || []).length;
+ }
+ /** Merge this run's unmatched sets into data/crosswalk-unmatched.json (replaces per source key). */
+ flush() {
+ mkdirSync(DATA_DIR, { recursive: true });
+ let existing: Record<string, unknown[]> = {};
+ if (existsSync(UNMATCHED_PATH)) {
+ try { existing = JSON.parse(readFileSync(UNMATCHED_PATH, 'utf8')); } catch { existing = {}; }
+ }
+ for (const [k, v] of Object.entries(this.items)) existing[k] = v;
+ writeFileSync(UNMATCHED_PATH, JSON.stringify(existing, null, 2));
+ }
+}
+
+export interface MetricRow {
+ regionId: number;
+ source: string;
+ metric: string;
+ period: string; // YYYY-MM-DD
+ value: number;
+}
+
+/** Batched multi-VALUES upsert into metric_series. Dedupes within the batch (last wins). */
+export async function upsertMetricRows(rows: MetricRow[], ingestRunId: number, batchSize = 1000): Promise<number> {
+ const deduped = new Map<string, MetricRow>();
+ for (const r of rows) deduped.set(`${r.regionId}|${r.source}|${r.metric}|${r.period}`, r);
+ const all = [...deduped.values()];
+ let n = 0;
+ for (let i = 0; i < all.length; i += batchSize) {
+ const batch = all.slice(i, i + batchSize);
+ const params: unknown[] = [];
+ const values = batch.map((r, j) => {
+ params.push(r.regionId, r.source, r.metric, r.period, r.value, ingestRunId);
+ const b = j * 6;
+ return `($${b + 1},$${b + 2},$${b + 3},$${b + 4},$${b + 5},$${b + 6})`;
+ });
+ await query(
+ `INSERT INTO metric_series (region_id, source, metric, period, value, ingest_run_id)
+ VALUES ${values.join(',')}
+ ON CONFLICT (region_id, source, metric, period)
+ DO UPDATE SET value = EXCLUDED.value, ingest_run_id = EXCLUDED.ingest_run_id`,
+ params,
+ );
+ n += batch.length;
+ }
+ return n;
+}
+
+/** Batched upsert into source_region_map. */
+export async function upsertSourceRegionMap(
+ entries: { source: string; sourceRegionId: string; regionId: number; matchMethod: string }[],
+ batchSize = 1000,
+): Promise<void> {
+ const deduped = new Map<string, (typeof entries)[number]>();
+ for (const e of entries) deduped.set(`${e.source}|${e.sourceRegionId}`, e);
+ const all = [...deduped.values()];
+ for (let i = 0; i < all.length; i += batchSize) {
+ const batch = all.slice(i, i + batchSize);
+ const params: unknown[] = [];
+ const values = batch.map((e, j) => {
+ params.push(e.source, e.sourceRegionId, e.regionId, e.matchMethod);
+ const b = j * 4;
+ return `($${b + 1},$${b + 2},$${b + 3},$${b + 4})`;
+ });
+ await query(
+ `INSERT INTO source_region_map (source, source_region_id, region_id, match_method)
+ VALUES ${values.join(',')}
+ ON CONFLICT (source, source_region_id)
+ DO UPDATE SET region_id = EXCLUDED.region_id, match_method = EXCLUDED.match_method`,
+ params,
+ );
+ }
+}
+
+/** First-of-month normalization: '2026-06-30' -> '2026-06-01'. */
+export function firstOfMonth(dateStr: string): string {
+ return dateStr.slice(0, 7) + '-01';
+}
+
+/** First-of-month cutoff N months back from now. */
+export function monthsAgoCutoff(months: number): string {
+ const d = new Date();
+ d.setUTCDate(1);
+ d.setUTCMonth(d.getUTCMonth() - (months - 1));
+ return d.toISOString().slice(0, 7) + '-01';
+}
diff --git a/src/ingest/redfin_tracker.ts b/src/ingest/redfin_tracker.ts
new file mode 100644
index 0000000..9ab1b5d
--- /dev/null
+++ b/src/ingest/redfin_tracker.ts
@@ -0,0 +1,203 @@
+/**
+ * Redfin market-tracker ingest — county + metro + state, gzipped full-history TSVs.
+ * NEVER buffers a whole file: createReadStream -> gunzip -> readline, flat memory.
+ * Filters: PROPERTY_TYPE='All Residential', IS_SEASONALLY_ADJUSTED=false (bare boolean),
+ * PERIOD_DURATION=30, PERIOD_BEGIN within the last 36 months.
+ * County match = REGION name ("Los Angeles County, CA") -> Gazetteer name+state;
+ * metro match = TABLE_ID (verified to be the CBSA code) -> region.cbsa_code;
+ * state match = STATE_CODE. Overrides consulted first; unmatched recorded + skipped.
+ * Run with --cached to reuse data/downloads copies during dev.
+ */
+import { createReadStream } from 'node:fs';
+import { createGunzip } from 'node:zlib';
+import { createInterface } from 'node:readline';
+import { openRun, closeRun, download } from './run.ts';
+import {
+ loadRegionMaps, loadOverrides, buildMetroMatcher, UnmatchedCollector,
+ upsertMetricRows, upsertSourceRegionMap, monthsAgoCutoff,
+ type MetricRow, type RegionMaps, normName,
+} from './crosswalk.ts';
+import { pool } from '../../db/pool.ts';
+
+const BASE = 'https://redfin-public-data.s3.us-west-2.amazonaws.com/redfin_market_tracker';
+const FILES = [
+ { url: `${BASE}/county_market_tracker.tsv000.gz`, file: 'redfin_county.tsv.gz', level: 'county' as const },
+ { url: `${BASE}/redfin_metro_market_tracker.tsv000.gz`, file: 'redfin_metro.tsv.gz', level: 'metro' as const },
+ { url: `${BASE}/state_market_tracker.tsv000.gz`, file: 'redfin_state.tsv.gz', level: 'state' as const },
+];
+
+const METRIC_COLS: [string, string][] = [
+ ['median_sale_price', 'median_sale_price'],
+ ['median_sale_price_yoy', 'median_sale_price_yoy'],
+ ['homes_sold', 'homes_sold'],
+ ['inventory', 'inventory'],
+ ['new_listings', 'new_listings'],
+ ['median_dom', 'dom'],
+ ['avg_sale_to_list', 'sale_to_list'],
+ ['months_of_supply', 'months_of_supply'],
+];
+
+const unq = (s: string) => (s.length >= 2 && s[0] === '"' && s[s.length - 1] === '"' ? s.slice(1, -1) : s);
+
+interface Ctx {
+ maps: RegionMaps;
+ matchMetro: ReturnType<typeof buildMetroMatcher>;
+ overrides: Record<string, string>;
+ unmatched: UnmatchedCollector;
+ cutoff: string;
+ runId: number;
+}
+
+async function processFile(path: string, level: 'county' | 'metro' | 'state', ctx: Ctx): Promise<{ upserted: number; skipped: number }> {
+ const rl = createInterface({ input: createReadStream(path).pipe(createGunzip()), crlfDelay: Infinity });
+ let header: string[] | null = null;
+ let idx: Record<string, number> = {};
+ let lineNo = 0;
+ let buffer: MetricRow[] = [];
+ let mapBuffer: { source: string; sourceRegionId: string; regionId: number; matchMethod: string }[] = [];
+ let upserted = 0;
+ let skipped = 0;
+ const seenUnmatched = new Set<string>();
+ const regionCache = new Map<string, { regionId: number; method: string } | null>();
+
+ const flush = async () => {
+ if (mapBuffer.length) { await upsertSourceRegionMap(mapBuffer); mapBuffer = []; }
+ if (buffer.length) { upserted += await upsertMetricRows(buffer, ctx.runId); buffer = []; }
+ };
+
+ for await (const raw of rl) {
+ lineNo++;
+ if (lineNo % 100_000 === 0) console.log(`[redfin:${level}] ${lineNo.toLocaleString()} lines, ${upserted.toLocaleString()} rows upserted`);
+ if (!raw) continue;
+ const f = raw.split('\t');
+ if (!header) {
+ header = f.map((h) => unq(h).toLowerCase());
+ header.forEach((h, i) => { idx[h] = i; });
+ for (const req of ['period_begin', 'property_type', 'region', 'table_id']) {
+ if (!(req in idx)) throw new Error(`redfin ${level}: missing column ${req}`);
+ }
+ continue;
+ }
+ const get = (c: string) => (idx[c] !== undefined ? unq(f[idx[c]] ?? '') : '');
+
+ if (get('property_type') !== 'All Residential') continue;
+ const sa = get('is_seasonally_adjusted');
+ if (sa && sa !== 'false' && sa !== 'f') continue;
+ const dur = get('period_duration');
+ if (dur && dur !== '30') continue;
+ const periodBegin = get('period_begin');
+ if (!periodBegin || periodBegin < ctx.cutoff) continue;
+
+ const region = get('region');
+ const tableId = get('table_id');
+ const stateCode = get('state_code');
+ const cacheKey = tableId || region;
+ let match = regionCache.get(cacheKey);
+ if (match === undefined) {
+ match = resolveRegion(level, region, tableId, stateCode, ctx);
+ regionCache.set(cacheKey, match);
+ if (match) mapBuffer.push({ source: 'redfin', sourceRegionId: tableId || region, regionId: match.regionId, matchMethod: match.method });
+ }
+ if (!match) {
+ skipped++;
+ if (!seenUnmatched.has(cacheKey)) {
+ seenUnmatched.add(cacheKey);
+ ctx.unmatched.add(`redfin_${level}`, { tableId, region, stateCode });
+ }
+ continue;
+ }
+
+ const period = periodBegin.slice(0, 7) + '-01';
+ for (const [colName, metric] of METRIC_COLS) {
+ if (idx[colName] === undefined) continue;
+ const v = unq(f[idx[colName]] ?? '');
+ if (v === '') continue;
+ const num = Number(v);
+ if (!Number.isFinite(num)) continue;
+ buffer.push({ regionId: match.regionId, source: 'redfin', metric, period, value: num });
+ }
+ if (buffer.length >= 5000) await flush();
+ }
+ await flush();
+ console.log(`[redfin:${level}] done: ${lineNo.toLocaleString()} lines, ${upserted.toLocaleString()} rows upserted, ${skipped} row-skips (unmatched regions: ${seenUnmatched.size})`);
+ return { upserted, skipped };
+}
+
+function resolveRegion(level: string, region: string, tableId: string, stateCode: string, ctx: Ctx): { regionId: number; method: string } | null {
+ const ovr = ctx.overrides[tableId] || ctx.overrides[region];
+ if (ovr) {
+ const r = ctx.maps.byCanonicalKey.get(ovr);
+ if (r) return { regionId: r.id, method: 'override' };
+ }
+ if (level === 'state') {
+ const r = ctx.maps.byStateCode.get(stateCode);
+ return r ? { regionId: r.id, method: 'state' } : null;
+ }
+ if (level === 'metro') {
+ const byCbsa = ctx.maps.byCbsa.get(tableId);
+ if (byCbsa) return { regionId: byCbsa.id, method: 'cbsa' };
+ const name = region.replace(/\s+metro area$/i, '');
+ const m = ctx.matchMetro(name);
+ return m ? { regionId: m.region.id, method: m.method } : null;
+ }
+ // county: "Los Angeles County, CA" -> name + state
+ const comma = region.lastIndexOf(',');
+ if (comma < 0) return null;
+ const name = region.slice(0, comma);
+ const st = region.slice(comma + 1).trim();
+ const r = ctx.maps.byCountyNameState.get(normName(name) + '|' + st);
+ return r ? { regionId: r.id, method: 'name' } : null;
+}
+
+async function main() {
+ const cached = process.argv.includes('--cached');
+ const runId = await openRun('redfin', BASE);
+ try {
+ const maps = await loadRegionMaps();
+ const ctx: Ctx = {
+ maps,
+ matchMetro: buildMetroMatcher(maps),
+ overrides: loadOverrides('redfin'),
+ unmatched: new UnmatchedCollector(),
+ cutoff: monthsAgoCutoff(36),
+ runId,
+ };
+ console.log(`[redfin] cutoff period_begin >= ${ctx.cutoff}`);
+
+ let totalUpserted = 0;
+ let totalSkipped = 0;
+ const hashes: string[] = [];
+ for (const spec of FILES) {
+ console.log(`[redfin] downloading ${spec.file}${cached ? ' (cached ok)' : ''}`);
+ const { path, sha256 } = await download(spec.url, spec.file, { reuseCached: cached });
+ hashes.push(`${spec.file}:${sha256.slice(0, 12)}`);
+ const r = await processFile(path, spec.level, ctx);
+ totalUpserted += r.upserted;
+ totalSkipped += r.skipped;
+ }
+ ctx.unmatched.flush();
+ const uc = ctx.unmatched.count('redfin_county');
+ const um = ctx.unmatched.count('redfin_metro');
+ const us = ctx.unmatched.count('redfin_state');
+ console.log(`[redfin] TOTAL upserted=${totalUpserted} unmatched: county=${uc} metro=${um} state=${us}`);
+
+ if (totalUpserted === 0) {
+ await closeRun(runId, 'failed', { notes: '0 rows parsed — format drift?' });
+ process.exitCode = 1;
+ } else {
+ await closeRun(runId, 'ok', {
+ upserted: totalUpserted, skipped: totalSkipped,
+ notes: `unmatched_county=${uc} unmatched_metro=${um} unmatched_state=${us}`,
+ fileHash: hashes.join(' '),
+ });
+ }
+ } catch (e) {
+ console.error('[redfin] FAILED', e);
+ await closeRun(runId, 'failed', { notes: String((e as Error).message || e) });
+ process.exitCode = 1;
+ } finally {
+ await pool.end();
+ }
+}
+
+main();
diff --git a/src/ingest/zillow_research.ts b/src/ingest/zillow_research.ts
new file mode 100644
index 0000000..1dd9f1f
--- /dev/null
+++ b/src/ingest/zillow_research.ts
@@ -0,0 +1,160 @@
+/**
+ * Zillow Research ingest — ZHVI + ZORI, county + metro, last 36 monthly columns.
+ * Wide CSV: one row per region, one column per month (month-end headers,
+ * normalized to first-of-month on store). County crosswalk = StateCodeFIPS+MunicipalCodeFIPS
+ * -> region.fips; metro crosswalk = overrides -> exact CBSA-name -> primary-city prefix.
+ * Unmatched metros are recorded + skipped, never guessed.
+ * Run with --cached to reuse data/downloads copies during dev.
+ */
+import { openRun, closeRun, download } from './run.ts';
+import {
+ loadRegionMaps, loadOverrides, buildMetroMatcher, UnmatchedCollector,
+ upsertMetricRows, upsertSourceRegionMap, firstOfMonth,
+ type MetricRow, type RegionMaps,
+} from './crosswalk.ts';
+import { readFileSync } from 'node:fs';
+import { pool } from '../../db/pool.ts';
+
+const BASE = 'https://files.zillowstatic.com/research/public_csvs';
+const FILES = [
+ { url: `${BASE}/zhvi/County_zhvi_uc_sfrcondo_tier_0.33_0.67_sm_sa_month.csv`, file: 'zillow_county_zhvi.csv', metric: 'zhvi', level: 'county' as const },
+ { url: `${BASE}/zhvi/Metro_zhvi_uc_sfrcondo_tier_0.33_0.67_sm_sa_month.csv`, file: 'zillow_metro_zhvi.csv', metric: 'zhvi', level: 'metro' as const },
+ { url: `${BASE}/zori/County_zori_uc_sfrcondomfr_sm_sa_month.csv`, file: 'zillow_county_zori.csv', metric: 'zori', level: 'county' as const },
+ { url: `${BASE}/zori/Metro_zori_uc_sfrcondomfr_sm_sa_month.csv`, file: 'zillow_metro_zori.csv', metric: 'zori', level: 'metro' as const },
+];
+const MONTHS = 36;
+
+function parseCsvLine(line: string): string[] {
+ const out: string[] = [];
+ let field = '';
+ let inQ = false;
+ for (let i = 0; i < line.length; i++) {
+ const c = line[i];
+ if (inQ) {
+ if (c === '"') {
+ if (line[i + 1] === '"') { field += '"'; i++; }
+ else inQ = false;
+ } else field += c;
+ } else if (c === '"') inQ = true;
+ else if (c === ',') { out.push(field); field = ''; }
+ else field += c;
+ }
+ out.push(field);
+ return out;
+}
+
+interface FileResult { rows: MetricRow[]; maps: { source: string; sourceRegionId: string; regionId: number; matchMethod: string }[]; parsed: number; skipped: number; }
+
+function processFile(
+ path: string, metric: string, level: 'county' | 'metro',
+ maps: RegionMaps, matchMetro: ReturnType<typeof buildMetroMatcher>,
+ overrides: Record<string, string>, unmatched: UnmatchedCollector,
+): FileResult {
+ const text = readFileSync(path, 'utf8');
+ const lines = text.split('\n');
+ const header = parseCsvLine(lines[0].replace(/\r$/, ''));
+ const col = (name: string) => header.indexOf(name);
+ const dateCols: { idx: number; period: string }[] = [];
+ header.forEach((h, i) => { if (/^\d{4}-\d{2}-\d{2}$/.test(h)) dateCols.push({ idx: i, period: firstOfMonth(h) }); });
+ const useCols = dateCols.slice(-MONTHS);
+ if (useCols.length === 0) throw new Error(`no date columns found in ${path}`);
+
+ const iRegionId = col('RegionID');
+ const iRegionName = col('RegionName');
+ const iStateFips = col('StateCodeFIPS');
+ const iMuniFips = col('MunicipalCodeFIPS');
+
+ const res: FileResult = { rows: [], maps: [], parsed: 0, skipped: 0 };
+ for (let li = 1; li < lines.length; li++) {
+ const line = lines[li].replace(/\r$/, '');
+ if (!line) continue;
+ const f = parseCsvLine(line);
+ const regionName = f[iRegionName];
+ let regionId: number | null = null;
+ let method = '';
+ if (level === 'county') {
+ const fips = f[iStateFips].padStart(2, '0') + f[iMuniFips].padStart(3, '0');
+ const region = maps.byFips.get(fips);
+ if (region) { regionId = region.id; method = 'fips'; }
+ } else {
+ const ovr = overrides[regionName];
+ if (ovr) {
+ const region = maps.byCanonicalKey.get(ovr);
+ if (region) { regionId = region.id; method = 'override'; }
+ }
+ if (regionId === null && regionName !== 'United States') {
+ const m = matchMetro(regionName);
+ if (m) { regionId = m.region.id; method = m.method; }
+ }
+ }
+ if (regionId === null) {
+ res.skipped++;
+ if (level === 'metro' && regionName !== 'United States') {
+ unmatched.add('zillow_metro', { regionId: f[iRegionId], regionName });
+ } else if (level === 'county') {
+ unmatched.add('zillow_county', { regionId: f[iRegionId], regionName, fips: f[iStateFips] + '/' + f[iMuniFips] });
+ }
+ continue;
+ }
+ res.maps.push({ source: 'zillow', sourceRegionId: f[iRegionId], regionId, matchMethod: method });
+ for (const { idx, period } of useCols) {
+ const v = f[idx];
+ if (v === '' || v === undefined) continue;
+ const num = Number(v);
+ if (!Number.isFinite(num)) continue;
+ res.rows.push({ regionId, source: 'zillow', metric, period, value: num });
+ }
+ res.parsed++;
+ }
+ return res;
+}
+
+async function main() {
+ const cached = process.argv.includes('--cached');
+ const runId = await openRun('zillow', BASE);
+ try {
+ const maps = await loadRegionMaps();
+ const matchMetro = buildMetroMatcher(maps);
+ const overrides = loadOverrides('zillow');
+ const unmatched = new UnmatchedCollector();
+
+ let totalUpserted = 0;
+ let totalSkipped = 0;
+ let totalParsed = 0;
+ const hashes: string[] = [];
+ for (const spec of FILES) {
+ console.log(`[zillow] downloading ${spec.file}${cached ? ' (cached ok)' : ''}`);
+ const { path, sha256 } = await download(spec.url, spec.file, { reuseCached: cached });
+ hashes.push(`${spec.file}:${sha256.slice(0, 12)}`);
+ const r = processFile(path, spec.metric, spec.level, maps, matchMetro, overrides, unmatched);
+ console.log(`[zillow] ${spec.file}: ${r.parsed} regions matched, ${r.skipped} skipped, ${r.rows.length} metric rows`);
+ await upsertSourceRegionMap(r.maps);
+ totalUpserted += await upsertMetricRows(r.rows, runId);
+ totalSkipped += r.skipped;
+ totalParsed += r.parsed;
+ }
+ unmatched.flush();
+ const unmatchedMetro = unmatched.count('zillow_metro');
+ const unmatchedCounty = unmatched.count('zillow_county');
+ console.log(`[zillow] TOTAL upserted=${totalUpserted} regions=${totalParsed} unmatched: county=${unmatchedCounty} metro=${unmatchedMetro}`);
+
+ if (totalUpserted === 0) {
+ await closeRun(runId, 'failed', { notes: '0 rows parsed — format drift?' });
+ process.exitCode = 1;
+ } else {
+ await closeRun(runId, 'ok', {
+ upserted: totalUpserted, skipped: totalSkipped,
+ notes: `regions=${totalParsed} unmatched_county=${unmatchedCounty} unmatched_metro=${unmatchedMetro}`,
+ fileHash: hashes.join(' '),
+ });
+ }
+ } catch (e) {
+ console.error('[zillow] FAILED', e);
+ await closeRun(runId, 'failed', { notes: String((e as Error).message || e) });
+ process.exitCode = 1;
+ } finally {
+ await pool.end();
+ }
+}
+
+main();
← 3859e63 M1 complete: usre DB migrated, 3222 counties + 935 metros +
·
back to Nationalrealestate
·
M3: ACS 2023 5-yr county demographics (19.3k rows), FHFA HPI aae13b5 →