TK-16 pass4: add Whatcom County WA (53073) priced-deed ArcGIS feed (5189 sales)

6c891d075868b63e99257b2140b443b32c590764 · 2026-08-20 19:59:21 -0700 · Steve Abrams

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

M SOURCES.md
M src/ingest/parcels/arcgis_sales.ts
M src/ingest/parcels/engine.ts
M src/jobs/hourly_loop.ts

Diff

commit 6c891d075868b63e99257b2140b443b32c590764
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Thu Aug 20 19:59:21 2026 -0700

    TK-16 pass4: add Whatcom County WA (53073) priced-deed ArcGIS feed (5189 sales)
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 SOURCES.md                         |  6 +++++-
 src/ingest/parcels/arcgis_sales.ts | 22 ++++++++++++++++++++++
 src/ingest/parcels/engine.ts       |  1 +
 src/jobs/hourly_loop.ts            |  2 ++
 4 files changed, 30 insertions(+), 1 deletion(-)

diff --git a/SOURCES.md b/SOURCES.md
index 964cd44..83dd726 100644
--- a/SOURCES.md
+++ b/SOURCES.md
@@ -108,7 +108,7 @@ incl. 8 gated/blocked states → `BROKER-COVERAGE.md`. Adapters: `src/ingest/bro
 
 ## 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).
+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).
 
 - **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).
 
@@ -150,3 +150,7 @@ Ring 5 out from LA (pass 11, all COVERAGE-only — AB-1785; all `maxRecordCount=
 - **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.
diff --git a/src/ingest/parcels/arcgis_sales.ts b/src/ingest/parcels/arcgis_sales.ts
index 15de04c..15f9762 100644
--- a/src/ingest/parcels/arcgis_sales.ts
+++ b/src/ingest/parcels/arcgis_sales.ts
@@ -634,6 +634,28 @@ const SOURCES: Record<string, Source> = {
       const [lng, lat] = centroid(f.geometry); const land = num(a.LAND_VAL), imp = num(a.IMP_VAL);
       return { fips: '06107', apn, use: clean(a.USEDSCRP), lat, lng, totalValue: (land || 0) + (imp || 0) || null, raw: a } as Rec; }).filter(Boolean) as Rec[],
   },
+  whatcom: {
+    // Whatcom County WA (Bellingham, 53073) — a genuine PRICED-DEED feed (unlike the CA
+    // AB-1785 coverage configs). Whatcom's enterprise "WhatcomCo_PropertySales" MapServer
+    // publishes real recorded excise sales, one layer per year (2020/2021/2022); we ingest
+    // the freshest published layer (SWDSales2022, layer /2). Keys are PLAIN (not DBO.*-qualified
+    // like Yakima): gid (16-char parcel geo_id → apn/source_id, also the deep-link key), pid,
+    // deed_date (epoch-ms → last_sale_date), sale_price (int), 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 WhatcomCo_Property; 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.
+    key: 'whatcom', label: 'Whatcom County WA (Bellingham)', cursorKey: 'whatcom', geometry: false, orderBy: 'OBJECTID', pageSize: 1000,
+    layer: 'https://gis.whatcomcounty.us/arcgis/rest/services/EnterprisePublishing/WhatcomCo_PropertySales/MapServer/2',
+    where: 'sale_price>1000',
+    // gid MUST be first — the engine derives the per-parcel GIS deep-link as where=<first>='<apn>'.
+    outFields: 'gid,pid,deed_date,sale_id,sale_price,sale_type,sale_land_acres',
+    fieldMap: { source_id: 'gid', last_sale_price: 'sale_price', last_sale_date: 'deed_date',
+      doc_type: 'sale_type', doc_number: 'sale_id' },
+    toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.gid); if (!apn) return null;
+      return { fips: '53073', apn, price: anyPrice(a.sale_price), saleDate: anyDate(a.deed_date),
+        docNum: s(a.sale_id), docType: clean(a.sale_type), raw: a } as Rec; }).filter(Boolean) as Rec[],
+  },
 };
 
 const execFileP = promisify(execFile);
diff --git a/src/ingest/parcels/engine.ts b/src/ingest/parcels/engine.ts
index 1997287..e361d67 100644
--- a/src/ingest/parcels/engine.ts
+++ b/src/ingest/parcels/engine.ts
@@ -62,6 +62,7 @@ const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: nu
   'el-dorado': async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('el-dorado') }; },
   nevada: async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('nevada') }; },
   tulare: async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('tulare') }; },
+  whatcom: async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('whatcom') }; },
 };
 
 async function main() {
diff --git a/src/jobs/hourly_loop.ts b/src/jobs/hourly_loop.ts
index 51fa67f..617a5c9 100644
--- a/src/jobs/hourly_loop.ts
+++ b/src/jobs/hourly_loop.ts
@@ -92,6 +92,8 @@ const JOBS: Job[] = [
   { name: 'parcels-eldorado',script:'src/ingest/parcels/engine.ts',   args: ['el-dorado'],     minIntervalHours: 2, runSources: ['el_dorado'] },
   { name: 'parcels-nevada',script:'src/ingest/parcels/engine.ts',     args: ['nevada'],        minIntervalHours: 2, runSources: ['nevada'] },
   { name: 'parcels-tulare',script:'src/ingest/parcels/engine.ts',     args: ['tulare'],        minIntervalHours: 2, runSources: ['tulare'] },
+  // TK-16 pass 4: Whatcom County WA (Bellingham, 53073) — genuine priced-deed feed (WhatcomCo_PropertySales excise sales).
+  { name: 'deeds-whatcom',script:'src/ingest/parcels/engine.ts',      args: ['whatcom'],       minIntervalHours: 3, runSources: ['whatcom'] },
   // TK-5: full San Diego County (1.09M) via SANDAG — zoning + assessed value; was in engine but missing from rotation.
   { name: 'parcels-sd-sandag', script: 'src/ingest/parcels/engine.ts', args: ['san-diego-sandag'], minIntervalHours: 2, runSources: ['san_diego_sandag'] },
   // TK-5: Santa Clara County CA (San Jose, 493k) — situs address; was in engine but missing from rotation.

← e41fdd7 auto-data-snapshot: 2026-08-20T15:11:42 (1 data files) — dat · back to Nationalrealestate · auto-data-snapshot: 2026-08-21T03:25:40 (1 data files) — dat 58652bd →