← back to Nationalrealestate

SOURCES.md

161 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), `lane` (41039), `jackson` (41029), `orange-county` (06059, coverage-only — AB-1785, no price), `ventura-county` (06111, coverage-only — address), `santa-barbara` (06083, coverage), `imperial` (06025, coverage), `san-bernardino` (06071, coverage), `san-joaquin` (06077, coverage), `fresno` (06019, coverage), `monterey` (06053, coverage), `solano` (06095, coverage), `butte` (06007, coverage), `sacramento` (06067, coverage), `stanislaus` (06099, coverage), `napa` (06055, coverage), `contra-costa` (06013, coverage), `el-dorado` (06017, coverage), `nevada` (06057, coverage), `tulare` (06107, coverage), `whatcom` (53073, priced deeds), `josephine` (41033, priced deeds), `snohomish` (53061, priced deeds).

- **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.

- **Lane County OR / Eugene (41039)** — added 2026-08-10 (TK-16, pass 4). Lane County "AddressParcelSales" ArcGIS MapServer, layer 1 "Sales (last 3 years)" `https://lcmaps.lanecounty.org/arcgis/rest/services/AT/AddressParcelSales/MapServer/1` (HTTPS only — the HTTP endpoint returns an Esri "Application Error" page). `where=SalesLayerCityJoin_SalesforGISLayerAll_sale_price>1000` → **~6,731** priced sales (a rolling last-3-years window, so it stays fresh rather than deep-historical). NOTE: this is a JOIN layer, so every attribute key is prefixed `SalesLayerCityJoin_SalesforGISLayerAll_` (bound to a local `P` const in `toRecords`; the un-prefixed `DeedDate` is the exception). Rich fields: `sale_price` (int → `last_sale_price`), `DeedDate` (epoch-ms → `last_sale_date`), `deed_transfer_no` (space-padded → doc_number, trimmed by `clean()`), `deed_type` (WD/PR/... → doc_type), `situs_address`/`city`/`zip`, `stat_class` (property class → use_desc), `maplot` (13-char taxlot → apn/source_id — MUST be the first `outField` since the per-parcel GIS deep-link is `where=<first>='<apn>'`). `reject_code` (ratio-study/arm's-length flag e.g. "Sale Adjusted") is kept, not filtered, and preserved in the row's raw-source JSON for provenance. Verified end-to-end against the local `usre` DB; `tsc` clean.

- **Jackson County OR / Medford (41029)** — added 2026-08-10 (TK-16, pass 5). Jackson County Assessment "LatestSales" FeatureServer `https://spatial.jacksoncountyor.gov/arcgis/rest/services/Assessment/LatestSales/FeatureServer/0` (HTTPS only — HTTP 302-redirects). `where=SalesPrice>1000` → **58,400** priced sales. An unusually rich, CLEAN (unqualified-key) layer: `SalesPrice` (double → `last_sale_price`), `SalesDate` (epoch-ms → `last_sale_date`), `DocumentNumber` (→ doc_number, e.g. `2025-18743`), `DocumentTypeDescription` (deed type e.g. "BARGAIN & SALE" → doc_type; null on some rows), `Grantor`+`Grantee` NAMES (both real), `SiteAddress`/`SiteCity`, `StatClassDescription` (→ use_desc), `YearBuilt`, `SquareFeet` (→ sqft), `maptaxlot` (→ apn/source_id — first `outField` for the deep-link). Also carries `DocumentURL` (recorded-instrument image link) preserved in raw. Verified end-to-end against the local `usre` DB; `tsc` clean.

- **Josephine County OR / Grants Pass (41033)** — added 2026-08-24 (TK-16, claude-run-16). Josephine County Assessor "Assessor_Taxlots" master FeatureServer `https://gis.co.josephine.or.us/arcgis/rest/services/Assessor/Assessor_Taxlots/FeatureServer/0` (keyless). `where=SALE_PRICE>1000` → **30,140** priced sales of 41,991 total parcels. A RICH, CLEAN-key priced-deed layer on par with Jackson, and rarer still it combines the priced-deed fields WITH explicit lat/lng: `SALE_PRICE` (double → `last_sale_price`), `SALE_DATE` (epoch-ms → `last_sale_date`), `DEED_TYPE` (WD/BS/... → doc_type), `INST_NO` (recorder doc, e.g. `20-010845` → doc_number), `SITUS`/`SITUS_CITY`/`SITUS_ZIP` (situs — some rural rows come as `* SPEAKER RD` where `*` is a missing house #, so the leading `* ` is stripped), `NAME` (owner → grantee), `RMV` (real-market total value → total_value), `YR_BLT`/`SQ_FT`/`BEDRMS` (characteristics), `PROP_CLASS` (assessor class code → use_desc), `ACCOUNT` (→ apn/source_id — first `outField` for the per-parcel GIS deep-link `where=ACCOUNT='<apn>'`), and explicit `Latitude`/`Longitude` fields (so `geometry:false` — no polygon download, like `imperial`/`butte`). `maxRecordCount=2000` (pageSize matched). Verified end-to-end against the local `usre` DB; `tsc` clean.

- **Snohomish County WA / Everett (53061)** — added 2026-08-24 (TK-16, claude-run-16). Net-new WA county (the local DB previously covered WA `53033`/`53063`/`53067`/`53073`/`53077` only). KEYLESS ArcGIS Online "Recent_Property_Sales" FeatureServer `https://services6.arcgis.com/z6WYi9VRHfgwgtyW/arcgis/rest/services/Recent_Property_Sales/FeatureServer/0` (no auth, no host-block). **21,734** sales total; **17,171** with a numeric `SALE_PRICE`. `where=SALE_PRICE IS NOT NULL` — every attribute is `esriFieldTypeString`, and `SALE_PRICE` is a **comma-formatted STRING** (e.g. `"1,150,000"`) which `anyPrice()`/`sanitizeSalePrice()` strips; the mapper drops rows whose price is null. **DATE GOTCHA:** `TRNSF_DATE` is `"Mon-YYYY"` (e.g. `"Oct-2025"`) which the shared `anyDate()` does NOT parse, so a Snohomish-local `snoDate()` helper maps it → `"YYYY-MM-01"` (month precision), falling back to `YEAR_SOLD` (`"2025"`) → `"YYYY-01-01"` when `TRNSF_DATE` is empty/unparseable (the shared `anyDate()` is intentionally left untouched — every other source depends on it). Polygon geometry → `centroid:true` (`returnCentroid=true&outSR=4326`, reads `f.centroid.{x,y}`, no polygon download — like `orange-county`/`santa-barbara`) → lightweight lat/lng (~48.x / -122.x, NW WA). Priced-deed only, **no situs** (address/owner not exposed on this layer). Fields: `SALE_PRICE` (→ last_sale_price), `TRNSF_DATE` (→ last_sale_date via `snoDate`), `YEAR_SOLD` (date fallback), `YEAR_BUILT` (→ year), `PROP_CLASS` (assessor class code → use_desc), `STYLE`/`IMPRV_TYPE` (kept in raw), `PARCEL_ID` (14-digit → apn/source_id — first `outField` for the per-parcel GIS deep-link `where=PARCEL_ID='<apn>'`). `maxRecordCount=2000` (pageSize matched); `orderBy=OBJECTID`. Verified end-to-end against the local `usre` DB; `tsc` clean.

- **Orange County CA (06059)** — added 2026-08-10 (TK-16, pass 6; first of the "all California, radiating from LA" sweep). OC Public Works ArcGIS `https://ocgis.com/arcpub/rest/services/LegalLotsAttributeOpenData/FeatureServer/0`, **~982,299** parcels. **COVERAGE-ONLY** — CA **AB-1785** suppresses recorded sale price, so like `la-county`/`san-diego-sandag` this config carries NO price/sale-events (`priced=0`); it ingests address + assessed value + centroid lat/lng. Fields: `AssessmentNo` (→ apn/source_id — NOT `LegalLotID`, which repeats across condo units), `SiteAddress`, `SiteCityState` (→ city), `SiteZip5`, `LandVal`/`ImprovedVal`/`AssdAmt` (all `esriFieldTypeString` holding numbers — `num()` parses; `total_value = AssdAmt || LandVal+ImprovedVal` since `AssdAmt` is often null on condo units), `GPLU_DESC`/`ZC_DESCR` (use — almost entirely null, ~1.5k of 982k). lat/lng via the new `centroid:true` Source flag → `returnCentroid=true&outSR=4326` (reads `f.centroid.{x,y}`; native SR is CA State Plane VI feet, so `outSR=4326` is required), which avoids downloading ~982k polygons. `maxRecordCount=2000` (pageSize matched). Verified end-to-end against the local `usre` DB; `tsc` clean.

- **Ventura County CA (06111)** — added 2026-08-10 (TK-16, pass 7; LA-adjacent ring). `https://maps.ventura.org/arcgis/rest/services/DataDownloads/Address/MapServer/0` (HTTPS only — HTTP 301-redirects), **299,739** rows, 100% apn. **COVERAGE-ONLY** — AB-1785 (no price) AND Ventura's public REST exposes no value or characteristics: this is situs-ADDRESS coverage (like `santa-clara`). Fields: `apn` (→ source_id), `fullsitus` (→ address), `city`, `zip`. `maxRecordCount=2000`; the layer's `objectIdField` is declared `None` but `orderByFields=objectid` + `resultOffset` paging works. lat/lng lives on a SEPARATE apn-keyed layer (`DataDownloads/CommonData/MapServer/2` has explicit `lat`/`lon`) — a future join enhancement (the one-layer-per-source engine can't join mid-pass). Verified end-to-end against the local `usre` DB; `tsc` clean.

The next ring out from LA (pass 8, all COVERAGE-only — AB-1785, no price/events):

- **Santa Barbara County CA (06083)** — `https://maps.calagpermits.org/arcgis/rest/services/SantaBarbara/Parcels/MapServer/0`, **132,504** parcels. The richest CA roll: `APN`, `Situs1` (→ address), `SCity`, `SZip`, `LandUse` (text use-desc), `Net_AV` (→ total_value), `YearBuilt`/`SqFootage`/`Bedrooms`/`Bathrooms` (blank as `' '`/`0` on commercial → `num()` nulls), `Owner` (→ owner_name). OID field is `OBJECTID_1`; `maxRecordCount=1000`. lat/lng via `geometry:true` + ring `centroid()` (returnCentroid NOT honored; native SR 3310, `outSR=4326`).
- **Imperial County CA (06025)** — `https://services7.arcgis.com/RomaVqqozKczDNgd/ArcGIS/rest/services/Parcels_Oct_2023/FeatureServer/0` (the `Parcels_Default_2026` sibling is a 253-row tax-default subset — do NOT use), **88,170** parcels. Explicit `Longitude`/`Latitude` fields (cleanest lat/lng, `geometry:false`). `APN`, `F_Situs1` (→ address), `Sit_Zip`, `LandUse1` (assessor code only), `Land`/`Imp_` (STRING → `anyPrice()` parses; summed → total_value), `Assessee` (→ owner_name). OID field is `FID`; `maxRecordCount=2000`.
- **San Bernardino County CA (06071)** — `https://services.arcgis.com/aA3snZwJfFkVyDuP/arcgis/rest/services/Parcels_for_San_Bernardino_County/FeatureServer/0`, **~839,795** parcels (largest CA county by area — big coverage win). NO situs address on this roll. `ParcelNumber` (→ apn), `AssessClass` (→ use_desc), `LandValue`/`ImprovementValue` (COMMA-formatted strings `'42,345'` → `anyPrice()` strips commas; plain `Number()` yields NaN — summed → total_value), `OwnerName` (mostly redacted `"Protected Per CA Gov Code 7928.205"` → filtered to null). `maxRecordCount=1000`; lat/lng via `geometry:true` + ring `centroid()`. All three verified end-to-end against the local `usre` DB; `tsc` clean.

Ring 3 out from LA (pass 9, all COVERAGE-only — AB-1785):

- **San Joaquin County CA (06077)** — `https://services2.arcgis.com/GQhSReJEO6f7tsvy/arcgis/rest/services/Parcels/FeatureServer/0`, **252,719** parcels. Rich: `APN`, `FULL_ADDRESS` (one-line incl city/state/zip → address), `SITUSZIP`, `DESCRIPTION` (use), `LAND_VALUE`/`IMPROVEMENT_VALUE` (STRING, no commas → `anyPrice()`; summed → total_value), `YEAR_BUILT`, `TOTALLIV_AREA` (→ sqft), `BEDROOMS`, `BATHROOM_WHOLE`. `maxRecordCount=2000`, OID `OBJECTID`. lat/lng via `centroid:true` (returnCentroid honored).
- **Fresno County CA (06019)** — `https://gisprod10.co.fresno.ca.us/server/rest/services/FC_PARCEL_SELECT/MapServer/0`, **~412k** parcels with real APN (`where=APN<>''` skips empty-APN head rows). Enterprise SDE MapServer (a `SHAPE.STArea()` helper exists → always explicit outFields). `APN`, `SITEADDRESS1` (street → address), `SITEADDRESS2` (city+zip packed, e.g. "DOS PALOS 93620" → parsed), `TOTAL_ASSESSED_VALUE` (int), `NAME1` (→ owner_name). `maxRecordCount=2000`, OID `OBJECTID` (works though `objectIdField` reports None). lat/lng via `geometry:true` + ring `centroid()` (returnCentroid unsupported).
- **Monterey County CA (06053)** — `https://services2.arcgis.com/nOGTdfb4kF4dZljH/arcgis/rest/services/Parcels_Data/FeatureServer/0`, **122,448** parcels. No situs address — qualifies via value. `APN`, `Land_Use` (use desc), `Land_Value`/`Imp_Value` (int, summed → total_value), `City` (often literal "None" → null). `maxRecordCount=2000`, OID `OBJECTID`. lat/lng via `centroid:true`. All three verified end-to-end against the local `usre` DB; `tsc` clean.

Ring 4 out from LA (pass 10, all COVERAGE-only — AB-1785; all `maxRecordCount=2000`, integer values):

- **Solano County CA (06095)** — `.../SCn6czzcqKAFwdGU/.../Parcels_Public_Aumentum/FeatureServer/0`, **~139,708** addressed parcels (`where=p_address<>''`). Richest Ring-4 roll: `asmtnum` (→ apn), `p_address`, `sitecity`, `use_desc`, `valland`/`valimp`/`valtv` (total = `valtv` else land+imp), `yrbuilt`, `total_area` (→ sqft), `bedroom`/`bathroom`, explicit `xcentroid`/`ycentroid` (lat/lng). OID `objectid` (lowercase). NOTE: `situs` is a Y/N flag, NOT the address.
- **Butte County CA (06007)** — `.../3t3QfTXFRFX44zo8/.../Butte_County_Parcel_Public_Data/FeatureServer/0`, **~190k** parcels. No value. `APN`, `SITUS` (one-line address; literal "No Address Available" → null), `LandUse`, explicit `Latitude`/`Longitude`. OID `FID`.
- **Sacramento County CA (06067)** — `.../5NARefyPVtAeuJPU/.../Parcels/FeatureServer/0`, **502,081** parcels (biggest Ring-4). No value. `APN`, `STREET_NBR`+`STREET_NAM` (→ address), `CITY`, `ZIP` (Double → string), `LU_SPECIF`/`LU_GENERAL` (use). lat/lng via `centroid:true`. OID `OBJECTID`.
- **Stanislaus County CA (06099)** — `.../EeYBJFxLdUojipYa/.../Public_Parcels/FeatureServer/0`, **168,709** parcels. No value (situs + zoning). `APN`, `Situs_Address`, `Situs_City` (packs "City ST ZIP" → trimmed to city), `Situs_Zip`, `Zoning`. lat/lng via `centroid:true`. OID `OBJECTID`. All four verified end-to-end against the local `usre` DB; `tsc` clean.

Ring 5 out from LA (pass 11, all COVERAGE-only — AB-1785; all `maxRecordCount=2000`, OID `OBJECTID`):

- **Napa County CA (06055)** — `.../Ko5rxt00spOfjMqj/.../Napa_County_Public_Parcels/FeatureServer/1` (layer **/1**, not /0), **51,238** parcels. Richest Ring-5 schema: `ASMTWithDash` (→ apn), `FullSitusAddress1`, `Community` (city), `Zip`, `LandUseDescription`, `TotalLandImprValue`, `YearBuilt`, `Building_Size_SqFt`, `BedroomsCount`, `FullBathsCount`. lat/lng via `geometry:true` + ring `centroid()`.
- **Contra Costa County CA (06013)** — `https://ccmap.cccounty.us/arcgis/rest/services/CCMAP/Assessment_Parcels_ArcPro/MapServer/0`, **387,862** (largest Ring-5). `APN`, `full_address_display` ("NO ADDRESS - <city>" → null), `s_city`, `S_ZIP` (int), `Description` (use), `LAND_VALUE`+`IMP_VAL` (→ total), `YR_BUILT`, `BLDG_SQFT`. lat/lng via `geometry:true` + ring `centroid()`.
- **El Dorado County CA (06017)** — `.../5b2oZtu5Eo64aE20/.../wlemp_master_parcels_info/FeatureServer/0`, **115,731**. Has EXPLICIT `LAT_Y`/`LONG_X` (no geometry) + owner. `PRCL_ID` (→ apn), `PRCL_ADDR`, `PO_NAME` (city), `ZIP_CODE`, `USE_CD_LITPRI`, `TOTAL_VAL`, `IMPR_SQ_FT`, `OWNER_NAME`.
- **Nevada County CA (06057)** — `https://maps.nevadacountyca.gov/arcgis/rest/services/web_public/parcel_mail_situs_external/MapServer/0`, **68,095**. `APN_FORMATTED`, `ADDRESS`, `PostOffice` (city), `ZipCode`, `UseCode`, `TotalLandValue`+`TotalImproveValue` (→ total). lat/lng via `geometry:true` + ring `centroid()`.
- **Tulare County CA (06107)** — `.../bYBANhmQGwSSLC0l/.../Public_Parcels/FeatureServer/0`, **159,318**. Value-only (no situs). NOTE: use `APN_TXT` (string), NOT `APN` (numeric — drops leading zeros). `USEDSCRP` (use), `LAND_VAL`+`IMP_VAL` (→ total). lat/lng via `geometry:true` + ring `centroid()`. All five verified end-to-end against the local `usre` DB; `tsc` clean.

Back to the WA priced-deed track (a genuine PRICED feed, not AB-1785 coverage):

- **Whatcom County WA / Bellingham (53073)** — added 2026-08-20 (TK-16, /yoloforever PASS 4). Whatcom's enterprise "WhatcomCo_PropertySales" MapServer `https://gis.whatcomcounty.us/arcgis/rest/services/EnterprisePublishing/WhatcomCo_PropertySales/MapServer/2` — a REAL recorded-excise-sales layer published one-layer-per-year (`SWDSales2020`/`2021`/`2022`); we ingest the freshest published, `SWDSales2022` (layer `/2`). `where=sale_price>1000` → **5,189** priced sales. Keys are PLAIN (not `DBO.*`-qualified like Yakima): `gid` (16-char parcel geo_id → apn/source_id, MUST be first outField for the per-parcel GIS deep-link `where=gid='<apn>'`; also joins to `WhatcomCo_Property`'s `geo_id`), `pid`, `deed_date` (epoch-ms → `last_sale_date`, handled by `anyDate()`), `sale_price` (int → `last_sale_price`), `sale_type` (WA excise deed code K/N/P/L/Q/M → doc_type), `sale_id` (int → doc_number), `sale_land_acres` (kept in raw). This sales layer carries **NO situs/owner/use** (those live on the separate `WhatcomCo_Property` parcel layer — a future one-pass-can't-join enhancement), so it's a lean price+date+doc feed. OID field reports `None` but `orderBy=OBJECTID` + `resultOffset` paging works; `maxRecordCount=1000` (pageSize matched). Verified end-to-end against the local `usre` DB (5,157 parcels + 5,189 sale events, all priced $1.1k–$22.75M, dates 2022-01..2022-12, 5,157 GIS deep-links, `parcel_source` registration on first batch; date-guard passed all rows); `tsc` clean.