`SOURCES.md`

118 lines
# 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.

## Census ACS 5-year 2023 (api.census.gov) — source `acs` (M3, probed 2026-07-21)

- URL: `https://api.census.gov/data/2023/acs/acs5?get=NAME,B19013_001E,B25077_001E,B25064_001E,B01003_001E,B25002_001E,B25002_003E,B25003_001E,B25003_003E&for=county:*`
- **Keyless FAILED**: the keyless call 302-redirects to `api.census.gov/data/missing_key.html` — a key is now required even at low volume. Ran with `CENSUS_API_KEY` from `~/Projects/secrets-manager/.env` → 200, 3,223 rows (header + 3,222 counties), every county FIPS matched.
- Format: JSON array-of-arrays, row 0 = header, then `[NAME, ...values, state, county]`. FIPS = `state`(2) + `county`(3) zero-padded.
- Metrics (period `2023-07-01`): median_hh_income (B19013), median_home_value_acs (B25077), median_gross_rent (B25064), population (B01003, also written to `region.population`), vacancy_rate (B25002_003/B25002_001), renter_share (B25003_003/B25003_001). Negative sentinels (e.g. -666666666) skipped.

## FHFA HPI (fhfa.gov) — source `fhfa` (M3, probed 2026-07-21)

- **URL DRIFT**: the documented `https://www.fhfa.gov/DataTools/Downloads/Documents/HPI/HPI_master.csv` → 404 (Drupal 11 rebuild). Current path: `https://www.fhfa.gov/hpi/download/monthly/hpi_master.csv` → 200, text/csv, ~18.9 MB (serves the full master file despite the `/monthly/` path segment).
- **Flavor choice**: `purchase-only` quarterly covers only **100** MSA place_ids vs **410** for `all-transactions` → we use `hpi_type='traditional'`, `hpi_flavor='all-transactions'`, `frequency='quarterly'`, levels `MSA` + `State`. Value = `index_nsa` (index_sa blank for many MSAs).
- Metro crosswalk: `place_id` IS the CBSA code for MSA rows (verified against region.cbsa_code, 373 direct matches). For the largest metros FHFA lists **(MSAD) divisions instead of the parent CBSA** — the primary division (first-named city == parent CBSA first city, e.g. `31084 Los Angeles-Long Beach-Glendale, CA (MSAD)` → `metro:31080`) is matched via the metro city-prefix matcher (match_method `msad_primary_city`, never overwrites a direct cbsa match); 24 secondary divisions (Anaheim, Camden, Cambridge…) stay unmatched-and-skipped by design. Total metro coverage: 386/935. State: 2-letter `place_id` → region.state_code (51/51).
- Period = quarter-end date (Q1→03-31 … Q4→12-31), last 12 quarters kept. Latest at probe: 2026Q1.

## Derived metrics — source `derived` (M3)

- `affordability` = latest zhvi / ACS median_hh_income (annual ACS joins to any zhvi month); `rent_yield` = (zori×12)/zhvi at latest common period; `zhvi_yoy` = zhvi latest vs 12 months prior (fraction). period = the zhvi period used. Counts on first run: affordability 3,063 · rent_yield 2,047 · zhvi_yoy 3,918.

## Broker registry — state licensing rosters (M-B1, probed 2026-07-21)

5 states genuinely-open + ingested this pass: **TX** (data.texas.gov Socrata `s7ft-44qi`,
321,788 rows), **FL** (DBPR free extracts `RE_rgn1..14.csv`, filenames recovered via
Wayback CDX since directory listing is off), **NY** (data.ny.gov `yg7h-zjbf`, 147,941
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).

## Free priced-DEED feeds — config-driven ArcGIS (`src/ingest/parcels/arcgis_sales.ts`)

Each entry is a keyless ArcGIS REST layer that publishes real recorded SALE PRICE; one `SOURCES` config maps a page of features → parcel row + `parcel_event(event_type='sale')` with grantor/grantee/doc + a `source_url` deep-link. Registered in `parcel_source`, paginated over time by the hourly loop (`src/jobs/hourly_loop.ts`, `runSources` = the config's `cursorKey`). Live configs: `oregon-rlis` (41051/67/05), `spokane` (53063), `alameda` (06001), `deschutes` (41017), `crook` (41013), `sonoma` (06097), `colorado` (statewide composite `08*`), `maricopa` (04013), `thurston` (53067), `palm-beach` (12099), `klamath` (41035), `marion` (41047), `yakima` (53077).

- **Klamath County OR (41035)** — added 2026-07-27 (TK-16). ArcGIS Online FeatureServer `https://services.arcgis.com/H6Mh1bySxR4oHx6x/arcgis/rest/services/KC_ParcelSales/FeatureServer/0` (item `f6314b12b8cc4c35899157172601bf59`, "KC_ParcelSales" = Klamath County Parcel Sales). `where=SALE_PRICE>1000` → **38,796** priced sales. Rich fields: `SALE_PRICE` (int), `SALE_DATE` (epoch-ms), `OWNER_NAME` (→ grantee), `MIN_SITUS_ADDRESS`+`FIRST_CITY_NAME`, `SUM_FINSQFT`/`SUM_BEDRMS`/`SUM_BATH`, `MIN_YRBLT`, `SUM_Tot_Appr` (total appraised → `total_value`), `SALEBK` (→ doc_number, internal double-spaces collapsed by `clean()`), `FIRST_PROP_ID` (→ apn/source_id), `FIRST_PCLCD` (→ use_desc). Server caps pages at 1000. Verified end-to-end against the local `usre` DB (1,000 parcels + 1,000 sale events + links + source registration on first batch).

- **Marion County OR / Salem (41047)** — added 2026-07-27 (TK-16, pass 2). ArcGIS Online FeatureServer `https://services1.arcgis.com/sYGZnQPdJ0azuLyn/arcgis/rest/services/TaxParcel_Assessment/FeatureServer/0` (Marion County official org). `where=SALEPRICE>1000` → **96,115** priced sales. Rich fields: `SALEPRICE` (int), `INSTDATE` (instrument/deed date, epoch-ms → `last_sale_date`), `INSTNUM` (→ doc_number), `INSTTYPE` (deed type WD/B&S/DEED → doc_type), `OWNERNAME` (→ grantee), `SITUS` (situs address, often blank on land parcels), `YEARBUILT`, `LIVINGAREA` (→ sqft), `RMVTOTAL` (real market total value → `total_value`), `PROPCLASS`+`STCLSDESC` (→ use_desc, prefers the description). `TAXLOT` (→ apn/source_id). Deed dates span decades so the future-date guard passes all rows. Verified end-to-end against the local `usre` DB (1,000 parcels + sale events + GIS links + `parcel_source` registration on first batch); `tsc` clean.

- **Yakima County WA (53077)** — added 2026-07-28 (TK-16, pass 3). Yakima County Assessor ArcGIS MapServer `https://gis.yakimawa.gov/arcgis/rest/services/Assessor/AssessorParcels/MapServer/0`. `where=DBO.Parcels.GROSS_SALE_PRICE>1000` → **56,030** priced sales. NOTE: this SQL-Server-backed MapServer returns attribute keys **fully-qualified** (`DBO.Parcels.*`), so `outFields` and `toRecords` both use the qualified names, and `orderBy` = `DBO.Parcels.OBJECTID`. Rich fields: `GROSS_SALE_PRICE` (double → `last_sale_price`), `SALE_DATE` (string `M/D/YYYY`, handled by `anyDate()` → `last_sale_date`), `EXCISE_NUMBER` (→ doc_number), `GRANTOR_NAME` (→ grantor), owner from `ORG_NAME` else `FIRST_NAME`+`LAST_NAME` (→ grantee/owner_name), `SITUS_ADDR`/`SITUS_CITY`/`SITUS_ZIP` (situs "UNASSIGNED" mapped to null), `USE_CODE` (→ use_desc), `YEAR_BLT`, `MAIN_SQFT` (→ sqft), `BEDROOMS`/`FULL_BATH`, `MKT_LAND`+`MKT_IMPVT` (summed → `total_value`), `ASSESSOR_NO` (→ apn/source_id). Empty fields come back as `" "` (single space) and are trimmed to null by `s()`/`clean()`. Verified end-to-end against the local `usre` DB (1,000 parcels + sale events + GIS links + `parcel_source` registration on first batch); `tsc` clean.