Snohomish County WA (53061): add free priced-deed feed (arcgis_sales) — TK-16

86cb190c9782283270379354138f2d2de85f24b5 · 2026-08-24 09:31:18 -0700 · steve@designerwallcoverings.com

ArcGIS Online Recent_Property_Sales FeatureServer (keyless). 17,171 priced
sales of 21,734. Mon-YYYY date parse via local snoDate() (shared anyDate
untouched); comma-string SALE_PRICE via anyPrice; polygon->centroid latlng.
OBJECTID in outFields for stable resultOffset paging (contrarian-flagged).

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 86cb190c9782283270379354138f2d2de85f24b5
Author: steve@designerwallcoverings.com <steve@designerwallcoverings.com>
Date:   Mon Aug 24 09:31:18 2026 -0700

    Snohomish County WA (53061): add free priced-deed feed (arcgis_sales) — TK-16
    
    ArcGIS Online Recent_Property_Sales FeatureServer (keyless). 17,171 priced
    sales of 21,734. Mon-YYYY date parse via local snoDate() (shared anyDate
    untouched); comma-string SALE_PRICE via anyPrice; polygon->centroid latlng.
    OBJECTID in outFields for stable resultOffset paging (contrarian-flagged).
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 SOURCES.md                         |  4 +++-
 src/ingest/parcels/arcgis_sales.ts | 38 ++++++++++++++++++++++++++++++++++++++
 src/ingest/parcels/engine.ts       |  1 +
 src/jobs/hourly_loop.ts            |  1 +
 4 files changed, 43 insertions(+), 1 deletion(-)

diff --git a/SOURCES.md b/SOURCES.md
index 5276800..3005b39 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), `whatcom` (53073, priced deeds), `josephine` (41033, priced deeds).
+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).
 
@@ -122,6 +122,8 @@ Each entry is a keyless ArcGIS REST layer that publishes real recorded SALE PRIC
 
 - **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.
diff --git a/src/ingest/parcels/arcgis_sales.ts b/src/ingest/parcels/arcgis_sales.ts
index 6c1726f..578ac6b 100644
--- a/src/ingest/parcels/arcgis_sales.ts
+++ b/src/ingest/parcels/arcgis_sales.ts
@@ -71,6 +71,17 @@ const anyDate = (v: unknown): string | null => {
   m = t.match(/^(\d{1,2})\/(\d{1,2})\/(\d{4})/); if (m) return `${m[3]}-${m[1].padStart(2, '0')}-${m[2].padStart(2, '0')}`;  // M/D/YYYY -> YYYY-MM-DD
   return null;
 };
+// Snohomish TRNSF_DATE is "Mon-YYYY" (e.g. "Oct-2025"), which anyDate() does NOT parse; map it to
+// month precision "YYYY-MM-01", falling back to YEAR_SOLD ("2025") → "YYYY-01-01". Local to Snohomish
+// only — do NOT fold into the shared anyDate() (every other source depends on its current behavior).
+const MONTHS: Record<string, string> = { jan: '01', feb: '02', mar: '03', apr: '04', may: '05', jun: '06', jul: '07', aug: '08', sep: '09', oct: '10', nov: '11', dec: '12' };
+const snoDate = (trnsf: unknown, yearSold: unknown): string | null => {
+  const t = String(trnsf ?? '').trim();
+  const m = t.match(/^([A-Za-z]{3})-(\d{4})$/);
+  if (m) { const mm = MONTHS[m[1].toLowerCase()]; if (mm) return `${m[2]}-${mm}-01`; }
+  const y = String(yearSold ?? '').trim(); if (/^\d{4}$/.test(y)) return `${y}-01-01`;
+  return null;
+};
 
 // ── source configs ────────────────────────────────────────────────────────
 const OR_FIPS: Record<string, string> = { M: '41051', W: '41067', C: '41005' };
@@ -706,6 +717,33 @@ const SOURCES: Record<string, Source> = {
         lat: num(a.Latitude), lng: num(a.Longitude),
         grantee: clean(a.NAME), docNum: clean(a.INST_NO), docType: clean(a.DEED_TYPE), raw: a } as Rec; }).filter(Boolean) as Rec[],
   },
+  snohomish: {
+    // Snohomish County WA (Everett) — net-new WA priced-deed county (local DB previously covered
+    // WA 53033/53063/53067/53073/53077 only). KEYLESS ArcGIS Online "Recent_Property_Sales" layer.
+    // 21,734 sales / 17,171 priced. Polygon geometry → centroid:true for lightweight lat/lng (~48.x/-122.x).
+    // GOTCHAS handled locally: SALE_PRICE is a STRING with commas ("1,150,000") → anyPrice() strips them;
+    // TRNSF_DATE is "Mon-YYYY" ("Oct-2025") which the shared anyDate() does NOT parse, so snoDate() below
+    // maps it → "YYYY-MM-01" (month precision), falling back to YEAR_SOLD → "YYYY-01-01". Rows whose price
+    // is null are dropped. PARCEL_ID (14-digit) is the parcel key AND the GIS deep-link key → first outField.
+    key: 'snohomish', label: 'Snohomish County WA (Everett)', cursorKey: 'snohomish',
+    geometry: false, centroid: true, orderBy: 'OBJECTID', pageSize: 2000,
+    layer: 'https://services6.arcgis.com/z6WYi9VRHfgwgtyW/arcgis/rest/services/Recent_Property_Sales/FeatureServer/0',
+    where: 'SALE_PRICE IS NOT NULL',
+    // PARCEL_ID MUST be first — the engine derives the per-parcel GIS deep-link as where=<first>='<apn>'.
+    // OBJECTID included so orderBy:'OBJECTID' resultOffset paging is unambiguously stable server-side
+    // (defensive — the loop wraps over weeks; an unstable sort would silently skip/dupe rows). Unmapped in toRecords.
+    outFields: 'PARCEL_ID,SALE_PRICE,TRNSF_DATE,YEAR_SOLD,YEAR_BUILT,PROP_CLASS,STYLE,IMPRV_TYPE,OBJECTID',
+    fieldMap: { source_id: 'PARCEL_ID', last_sale_price: 'SALE_PRICE', last_sale_date: 'TRNSF_DATE',
+      use_desc: 'PROP_CLASS', year_built: 'YEAR_BUILT', lat: 'centroid.y', lng: 'centroid.x' },
+    // NOTE: TRNSF_DATE is month-precision only → every last_sale_date lands on day=01; this layer exposes
+    // no recorder doc_number, so two sales of the SAME parcel in the SAME month collapse to one event via
+    // the (county_fips,source_id,date) upsert key (known limitation of a month-precision source, not a bug).
+    toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.PARCEL_ID); if (!apn) return null;
+      const price = anyPrice(a.SALE_PRICE); if (price == null) return null;   // drop unpriced rows
+      const c = f.centroid || {};
+      return { fips: '53061', apn, price, saleDate: snoDate(a.TRNSF_DATE, a.YEAR_SOLD),
+        year: num(a.YEAR_BUILT), use: clean(a.PROP_CLASS), lat: num(c.y), lng: num(c.x), 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 b6d687f..a2e3af0 100644
--- a/src/ingest/parcels/engine.ts
+++ b/src/ingest/parcels/engine.ts
@@ -67,6 +67,7 @@ const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: nu
   'whatcom-2021': async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('whatcom-2021') }; },
   'whatcom-2020': async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('whatcom-2020') }; },
   josephine: async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('josephine') }; },   // TK-16: OR priced-deed (Grants Pass)
+  snohomish: async () => { const m = await import('./arcgis_sales.ts'); return { run: m.ingestArcgisSales('snohomish') }; },   // TK-16: WA priced-deed (Everett, 53061)
 };
 
 async function main() {
diff --git a/src/jobs/hourly_loop.ts b/src/jobs/hourly_loop.ts
index 493572d..70f1960 100644
--- a/src/jobs/hourly_loop.ts
+++ b/src/jobs/hourly_loop.ts
@@ -76,6 +76,7 @@ const JOBS: Job[] = [
   { name: 'deeds-lane',  script: 'src/ingest/parcels/engine.ts',     args: ['lane'],        minIntervalHours: 4, runSources: ['lane'] },
   { name: 'deeds-jackson',script:'src/ingest/parcels/engine.ts',     args: ['jackson'],     minIntervalHours: 3, runSources: ['jackson'] },
   { name: 'deeds-josephine',script:'src/ingest/parcels/engine.ts',   args: ['josephine'],   minIntervalHours: 3, runSources: ['josephine'] },  // TK-16: OR priced-deed (Grants Pass, 30k priced)
+  { name: 'deeds-snohomish',script:'src/ingest/parcels/engine.ts',   args: ['snohomish'],   minIntervalHours: 3, runSources: ['snohomish'] },  // TK-16: WA priced-deed (Everett, 53061, 17k priced)
   { name: 'parcels-orange',script:'src/ingest/parcels/engine.ts',    args: ['orange-county'], minIntervalHours: 2, runSources: ['orange_county'] },
   { name: 'parcels-ventura',script:'src/ingest/parcels/engine.ts',   args: ['ventura-county'], minIntervalHours: 2, runSources: ['ventura_county'] },
   { name: 'parcels-sb',    script: 'src/ingest/parcels/engine.ts',    args: ['santa-barbara'], minIntervalHours: 2, runSources: ['santa_barbara'] },

← d7d2c15 TK-16: add Josephine County OR (41033) free priced-deed feed · back to Nationalrealestate · chore: v0.22.0 (session close — 3 west-coast priced-deed fee 122ebb1 →