← back to Nationalrealestate
src/ingest/parcels/arcgis_sales.ts
901 lines
/**
* Config-driven ArcGIS priced-DEED ingest — free/keyless county sales feeds that
* publish real recorded SALE PRICE (unlike SD, which AB-1785-gated it). Each
* source maps a page of ArcGIS features to normalized sale records → parcel row
* + a parcel_event(event_type='sale', amount=price) carrying grantor/grantee/doc
* where available, with a source_url back to the record.
*
* Verified 2026-07-26 (west-coast first):
* oregon-rlis — Portland metro tri-county (Multnomah/Washington/Clackamas),
* 428,872 priced (SALEPRICE + SALEDATE YYYYMM).
* spokane — Spokane County WA, 146,691 priced (gross_sale_price, buyer, deed type).
* alameda — Alameda County CA deed-transfer list, ~51,800 priced
* (value_from_trans_tax + grantor/grantee split across rows).
*
* npm run ingest:parcels -- oregon-rlis | spokane | alameda
*/
import { execFile } from 'node:child_process';
import { promisify } from 'node:util';
import { query } from '../../../db/pool.ts';
import { openRun, closeRun } from '../run.ts';
import { upsertParcels, upsertParcelHistory, normAddress, registerParcelSource, registerSourceFieldMap, type ParcelUpsertRow } from './upsert.ts';
import { sanitizeEventDate } from './date_guard.ts';
import { sanitizeSalePrice } from './price_guard.ts';
const PAGE = 2000;
const MAX_PER_RUN = Number(process.env.SALES_MAX_PER_RUN || 6000);
interface Rec {
fips: string; apn: string;
address?: string | null; city?: string | null; zip?: string | null;
lat?: number | null; lng?: number | null;
beds?: number | null; baths?: number | null; sqft?: number | null; year?: number | null;
use?: string | null; totalValue?: number | null;
price?: number | null; saleDate?: string | null;
docNum?: string | null; docType?: string | null; grantor?: string | null; grantee?: string | null;
raw?: Record<string, unknown> | null; // TK-50: the raw source-record attributes this Rec was mapped from
}
interface Source {
key: string; label: string; layer: string; where: string; outFields: string; cursorKey: string;
geometry: boolean; orderBy: string; pageSize?: number; // a valid field for stable resultOffset paging (OID name varies)
centroid?: boolean; // request returnCentroid+outSR=4326 (lightweight lat/lng from f.centroid) without downloading full polygons — for geometry-only assessor rolls (e.g. Orange County)
history?: boolean; // TK-10777: history layer — use upsertParcelHistory (date-guarded) instead of the full-overwrite upsert
toRecords: (features: any[]) => Rec[];
// TK-50 field-level provenance: our parcel field → the exact source attribute
// it was mapped from in toRecords(). Registered into source_field_map at
// ingest time so "where did field X come from?" resolves without reading code.
fieldMap: Record<string, string>;
}
const s = (v: unknown): string | null => { const t = v == null ? '' : String(v).trim(); return t ? t : null; };
const num = (v: unknown): number | null => { const x = Number(v); return Number.isFinite(x) && x !== 0 ? x : null; };
const epochToISO = (v: unknown): string | null => { const x = Number(v); if (!Number.isFinite(x) || x <= 0) return null; const d = new Date(x); return d.toISOString().slice(0, 10); };
const yyyymm = (v: unknown): string | null => { const t = String(v ?? '').trim(); return /^\d{6}$/.test(t) ? `${t.slice(0, 4)}-${t.slice(4, 6)}-01` : null; };
function centroid(geom: any): [number | null, number | null] {
const ring = geom?.rings?.[0]; if (!Array.isArray(ring) || !ring.length) return [null, null];
let sx = 0, sy = 0; for (const [x, y] of ring) { sx += x; sy += y; }
return [+(sx / ring.length).toFixed(6), +(sy / ring.length).toFixed(6)];
}
const clean = (v: unknown): string | null => { const t = s(v); return t ? t.replace(/\s+/g, ' ') : null; };
// price may be a real number OR a string ('117500', ' 000001060000.'); the shared
// guard strips currency formatting and rejects negative/zero/non-finite amounts.
// (Delegating here also fixes a latent bug: the old inline strip removed the
// minus sign, so a corrupt "-400" would have become 400 instead of being dropped.)
const anyPrice = (v: unknown): number | null => sanitizeSalePrice(v as string | number | null | undefined);
// date may be epoch-ms, 'YYYY-MM-DD', or 'M/D/YYYY'.
const anyDate = (v: unknown): string | null => {
if (v == null || v === '') return null;
if (typeof v === 'number' || /^\d{11,}$/.test(String(v).trim())) return epochToISO(Number(v));
const t = String(v).trim();
let m = t.match(/^(\d{4})-(\d{2})-(\d{2})/); if (m) return `${m[1]}-${m[2]}-${m[3]}`;
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' };
const SOURCES: Record<string, Source> = {
'oregon-rlis': {
key: 'oregon-rlis', label: 'Oregon Metro RLIS (Portland tri-county)',
layer: 'https://services2.arcgis.com/McQ0OlIABe29rJJy/arcgis/rest/services/Taxlots_(Public)/FeatureServer/3',
where: 'SALEPRICE>1000', cursorKey: 'oregon_rlis', geometry: true, orderBy: 'FID',
outFields: 'TLID,PRIMACCNUM,SITEADDR,SITECITY,SITEZIP,SALEDATE,SALEPRICE,COUNTY,YEARBUILT,BLDGSQFT,LANDUSE',
fieldMap: { source_id: 'PRIMACCNUM', address: 'SITEADDR', city: 'SITECITY', zip: 'SITEZIP',
sqft: 'BLDGSQFT', year_built: 'YEARBUILT', use_desc: 'LANDUSE',
last_sale_price: 'SALEPRICE', last_sale_date: 'SALEDATE', county_fips: 'COUNTY' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.PRIMACCNUM) || s(a.TLID); if (!apn) return null;
const [lng, lat] = centroid(f.geometry);
return { fips: OR_FIPS[s(a.COUNTY) || ''] || '41051', apn, address: clean(a.SITEADDR), city: clean(a.SITECITY), zip: s(a.SITEZIP),
lat, lng, sqft: num(a.BLDGSQFT), year: num(a.YEARBUILT), use: s(a.LANDUSE),
price: anyPrice(a.SALEPRICE), saleDate: yyyymm(a.SALEDATE), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
spokane: {
key: 'spokane', label: 'Spokane County WA', cursorKey: 'spokane', geometry: false,
layer: 'https://gismo.spokanecounty.org/arcgis/rest/services/SCOUT/PropertyLookup/MapServer/0',
where: 'gross_sale_price>1000', orderBy: 'PID_NUM', pageSize: 500, // slow MapServer (~50ms/row)
outFields: 'PID_NUM,gross_sale_price,document_date,excise_nbr,site_address,owner_name,transfer_type',
fieldMap: { source_id: 'PID_NUM', address: 'site_address', last_sale_price: 'gross_sale_price',
last_sale_date: 'document_date', owner_name: 'owner_name' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.PID_NUM); if (!apn) return null;
return { fips: '53063', apn, address: clean(a.site_address), city: 'Spokane', zip: null, lat: null, lng: null,
price: anyPrice(a.gross_sale_price), saleDate: epochToISO(a.document_date), docNum: s(a.excise_nbr),
docType: clean(a.transfer_type), grantee: clean(a.owner_name), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
alameda: {
key: 'alameda', label: 'Alameda County CA (deed transfers)', cursorKey: 'alameda', geometry: false,
layer: 'https://services5.arcgis.com/ROBnTHSNjoZ2Wm1P/arcgis/rest/services/Assessor_Office_Ownership_Transfer_List/FeatureServer/0',
where: 'value_from_trans_tax IS NOT NULL', orderBy: 'OBJECTID',
outFields: 'apn,use_name,street_num,street_name,street_suffix,city_name,zip_cd,transfer_dt,doc_prefix,doc_series,value_from_trans_tax,name_type',
fieldMap: { source_id: 'apn', address: 'street_num+street_name+street_suffix', city: 'city_name', zip: 'zip_cd',
use_desc: 'use_name', last_sale_price: 'value_from_trans_tax', last_sale_date: 'transfer_dt' },
// grantor+grantee are separate rows sharing doc_series — collapse per doc.
toRecords: (fs) => {
const byDoc = new Map<string, Rec>();
for (const f of fs) {
const a = f.attributes; const apn = s(a.apn); const doc = (s(a.doc_prefix) || '') + (s(a.doc_series) || '');
if (!apn || !doc) continue;
const price = anyPrice(a.value_from_trans_tax); // zero-padded dollars e.g. '000001060000.'
const addr = [s(a.street_num), clean(a.street_name), s(a.street_suffix)].filter(Boolean).join(' ').trim() || null;
const key = apn + '|' + doc;
const r = byDoc.get(key) || { fips: '06001', apn, address: addr, city: clean(a.city_name), zip: s(a.zip_cd),
lat: null, lng: null, use: clean(a.use_name), price, saleDate: epochToISO(a.transfer_dt), docNum: doc, raw: a } as Rec;
const nm = clean(a.name_type)?.toUpperCase();
// name_type marks the party; the party NAME itself isn't in this field set, so record role presence.
if (nm === 'TRANSFEROR') r.grantor = r.grantor || 'recorded';
if (nm === 'TRANSFEREE') r.grantee = r.grantee || 'recorded';
byDoc.set(key, r);
}
return [...byDoc.values()];
},
},
deschutes: {
key: 'deschutes', label: 'Deschutes County OR (Bend)', cursorKey: 'deschutes', geometry: false, orderBy: 'Taxlot', pageSize: 1000, // server caps pages at 1000
layer: 'https://maps.deschutes.org/arcgis/rest/services/OpenData/TablesFD/MapServer/9',
where: 'Total_Sales_Price_1>1000',
outFields: 'Taxlot,Total_Sales_Price_1,Sales_Date_1,Seller_1,Buyer_1,Book_Page_1,Total_Sales_Price_2,Sales_Date_2,Seller_2,Buyer_2,Book_Page_2',
fieldMap: { source_id: 'Taxlot', last_sale_price: 'Total_Sales_Price_1', last_sale_date: 'Sales_Date_1',
owner_name: 'Buyer_1' },
// Two sales per row (_1 newest, _2 prior), each with REAL grantor/grantee NAMES.
toRecords: (fs) => {
const out: Rec[] = [];
for (const f of fs) {
const a = f.attributes; const apn = s(a.Taxlot); if (!apn) continue;
for (const nx of ['1', '2']) {
const price = num(a['Total_Sales_Price_' + nx]); const date = epochToISO(a['Sales_Date_' + nx]);
if (!price || !date) continue;
out.push({ fips: '41017', apn, price, saleDate: date, grantor: clean(a['Seller_' + nx]),
grantee: clean(a['Buyer_' + nx]), docNum: s(a['Book_Page_' + nx]) || `${date}-${nx}`, raw: a } as Rec);
}
}
return out;
},
},
crook: {
key: 'crook', label: 'Crook County OR (Prineville)', cursorKey: 'crook', geometry: false, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://gis.crookcountyor.gov/server/rest/services/OpenData/TaxlotandTables/MapServer/6',
where: 'SALES_PRICE>0',
outFields: 'MAPTAXLOT,SALES_PRICE,SALES_DATE,GRANTOR_NAME,GRANTEE_NAME,BOOK,NUMBER,SALE_ID',
fieldMap: { source_id: 'MAPTAXLOT', last_sale_price: 'SALES_PRICE', last_sale_date: 'SALES_DATE',
owner_name: 'GRANTEE_NAME' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.MAPTAXLOT); if (!apn) return null;
return { fips: '41013', apn, price: anyPrice(a.SALES_PRICE), saleDate: anyDate(a.SALES_DATE),
grantor: clean(a.GRANTOR_NAME), grantee: clean(a.GRANTEE_NAME),
docNum: [s(a.BOOK), s(a.NUMBER)].filter(Boolean).join('-') || s(a.SALE_ID), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
sonoma: {
key: 'sonoma', label: 'Sonoma County CA', cursorKey: 'sonoma', geometry: false, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://socogis.sonomacounty.ca.gov/map/rest/services/CRAPublic/ParcelsPublic/FeatureServer/0',
where: 'SaleSalesPrice>0',
outFields: 'APN,SaleSalesPrice,SaleRecordingDate,SaleDocNum,SalePriorSalesPrice,SalePriorRecordingDate,SalePriorDocNum,SitusFormatted1,SitusCity',
fieldMap: { source_id: 'APN', address: 'SitusFormatted1', city: 'SitusCity',
last_sale_price: 'SaleSalesPrice', last_sale_date: 'SaleRecordingDate' },
toRecords: (fs) => { const out: Rec[] = [];
for (const f of fs) { const a = f.attributes; const apn = s(a.APN); if (!apn) continue;
const addr = clean(a.SitusFormatted1), city = clean(a.SitusCity);
const cur = anyPrice(a.SaleSalesPrice), cd = anyDate(a.SaleRecordingDate);
if (cur && cd) out.push({ fips: '06097', apn, address: addr, city, price: cur, saleDate: cd, docNum: s(a.SaleDocNum), raw: a } as Rec);
const pp = anyPrice(a.SalePriorSalesPrice), pd = anyDate(a.SalePriorRecordingDate);
if (pp && pd) out.push({ fips: '06097', apn, address: addr, city, price: pp, saleDate: pd, docNum: s(a.SalePriorDocNum), raw: a } as Rec);
} return out; },
},
colorado: {
key: 'colorado', label: 'Colorado (statewide composite)', cursorKey: 'colorado', geometry: false, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://gis.colorado.gov/Public/rest/services/Address_and_Parcel/Colorado_Public_Parcels/MapServer/0',
where: "salePrice>'0'",
outFields: 'parcel_id,salePrice,saleDate,countyFips,countyName',
fieldMap: { source_id: 'parcel_id', county_fips: 'countyFips', city: 'countyName',
last_sale_price: 'salePrice', last_sale_date: 'saleDate' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.parcel_id); if (!apn) return null;
const cf = (s(a.countyFips) || '').replace(/\D/g, '');
const fips = cf.length >= 5 ? cf.slice(0, 5) : '08' + cf.padStart(3, '0').slice(-3);
return { fips, apn, city: clean(a.countyName), price: anyPrice(a.salePrice), saleDate: anyDate(a.saleDate), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
maricopa: {
key: 'maricopa', label: 'Maricopa County AZ (Phoenix)', cursorKey: 'maricopa', geometry: false, orderBy: 'OBJECTID', pageSize: 1000,
layer: 'https://gis.mcassessor.maricopa.gov/arcgis/rest/services/MaricopaDynamicQueryService/MapServer/3',
where: "SALE_PRICE>'0'",
outFields: 'APN,SALE_PRICE,SALE_DATE,DEED_DATE,DEED_NUMBER,PHYSICAL_STREET_NUM,PHYSICAL_STREET_DIR,PHYSICAL_STREET_NAME,PHYSICAL_STREET_TYPE,PHYSICAL_CITY,PHYSICAL_ZIP',
fieldMap: { source_id: 'APN', address: 'PHYSICAL_STREET_NUM+PHYSICAL_STREET_DIR+PHYSICAL_STREET_NAME+PHYSICAL_STREET_TYPE',
city: 'PHYSICAL_CITY', zip: 'PHYSICAL_ZIP', last_sale_price: 'SALE_PRICE', last_sale_date: 'SALE_DATE' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null;
const addr = [s(a.PHYSICAL_STREET_NUM), s(a.PHYSICAL_STREET_DIR), clean(a.PHYSICAL_STREET_NAME), s(a.PHYSICAL_STREET_TYPE)].filter(Boolean).join(' ').trim() || null;
return { fips: '04013', apn, address: addr, city: clean(a.PHYSICAL_CITY), zip: s(a.PHYSICAL_ZIP),
price: anyPrice(a.SALE_PRICE), saleDate: anyDate(a.SALE_DATE) || anyDate(a.DEED_DATE), docNum: s(a.DEED_NUMBER), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'la-county': {
// Los Angeles County — the largest US county (~2.43M parcels), West Coast.
// Assessor roll layer: no per-parcel sale price or owner name (excluded by policy),
// but rich value + characteristics + parcel centroids (CENTER_LAT/CENTER_LON).
// TK-16: added CENTER_LAT/CENTER_LON so lat/lng populates as the sweep continues.
key: 'la-county', label: 'Los Angeles County CA (Assessor parcels)', cursorKey: 'la_county', geometry: false, orderBy: 'OBJECTID', pageSize: 1000, // server maxRecordCount=1000; pageSize>that broke the loop after 1 page
layer: 'https://public.gis.lacounty.gov/public/rest/services/LACounty_Cache/LACounty_Parcel/MapServer/0',
where: 'Roll_LandValue>0',
outFields: 'APN,SitusFullAddress,SitusCity,SitusZIP,UseDescription,YearBuilt1,Bedrooms1,Bathrooms1,SQFTmain1,Roll_LandValue,Roll_ImpValue,CENTER_LAT,CENTER_LON',
fieldMap: { source_id: 'APN', address: 'SitusFullAddress', city: 'SitusCity', zip: 'SitusZIP',
use_desc: 'UseDescription', year_built: 'YearBuilt1', beds: 'Bedrooms1', baths: 'Bathrooms1',
sqft: 'SQFTmain1', land_value: 'Roll_LandValue', improvement_value: 'Roll_ImpValue',
lat: 'CENTER_LAT', lng: 'CENTER_LON' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null;
const land = num(a.Roll_LandValue), imp = num(a.Roll_ImpValue);
return { fips: '06037', apn, address: clean(a.SitusFullAddress), city: clean(a.SitusCity), zip: s(a.SitusZIP),
lat: num(a.CENTER_LAT), lng: num(a.CENTER_LON),
use: clean(a.UseDescription), year: num(a.YearBuilt1), beds: num(a.Bedrooms1), baths: num(a.Bathrooms1),
sqft: num(a.SQFTmain1), totalValue: (land || imp) ? (land || 0) + (imp || 0) : null, raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'san-diego-sandag': {
// Full San Diego County (1.09M) from SANDAG. Codes only, BUT the SanGIS data
// dictionary gives the authoritative ASR_ZONE map, so we translate the zone code
// to its descriptive label at ingest → use_desc becomes text → from_parcel classifies.
key: 'san-diego-sandag', label: 'San Diego County CA (SANDAG full)', cursorKey: 'san_diego_sandag', geometry: false, orderBy: 'objectid', pageSize: 2000,
layer: 'https://geo.sandag.org/server/rest/services/Hosted/Parcels/FeatureServer/0',
where: 'apn IS NOT NULL',
outFields: 'apn,situs_address,situs_street,situs_juris,asr_zone,asr_total',
fieldMap: { source_id: 'apn', address: 'situs_address', use_desc: 'asr_zone(mapped)', total_value: 'asr_total' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.apn); if (!apn) return null;
// Authoritative ASR_ZONE → label (SanGIS data dictionary, verified 2026-07-30).
const ZONE: Record<string, string> = { '0': 'Unzoned', '1': 'Single Family Residential', '2': 'Minor Multiple Residential', '3': 'Restricted Multiple Residential', '4': 'Multiple Residential', '5': 'Restricted Commercial', '6': 'Commercial', '7': 'Industrial', '8': 'Agricultural', '9': 'Special/Misc' };
const use = ZONE[String(a.asr_zone)] ?? null;
const addr = clean(a.situs_address) || [s(a.situs_street)].filter(Boolean).join(' ') || null;
return { fips: '06073', apn, address: addr, city: clean(a.situs_juris), zip: null,
use, totalValue: num(a.asr_total), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'santa-clara': {
// Santa Clara County (San Jose) — public layer is address+APN only (no value/use),
// maxRecordCount=2000. Parcels key on county_fips so no region-resolve needed.
key: 'santa-clara', label: 'Santa Clara County CA (San Jose)', cursorKey: 'santa_clara', geometry: false, orderBy: 'FID', pageSize: 2000,
layer: 'https://services8.arcgis.com/fpjs8A5Vtkshblnd/arcgis/rest/services/Santa_Clara_County_Parcels/FeatureServer/0',
where: 'apn IS NOT NULL',
outFields: 'apn,situs_hous,situs_stre,situs_st_1,situs_st_2,situs_city,situs_zip_',
fieldMap: { source_id: 'apn', address: 'situs_hous+situs_stre+situs_st_1+situs_st_2', city: 'situs_city', zip: 'situs_zip_' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.apn); if (!apn) return null;
const addr = [s(a.situs_hous), s(a.situs_stre), clean(a.situs_st_1), s(a.situs_st_2)].filter(Boolean).join(' ').trim() || null;
return { fips: '06085', apn, address: addr, city: clean(a.situs_city), zip: s(a.situs_zip_), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
thurston: {
key: 'thurston', label: 'Thurston County WA (Olympia)', cursorKey: 'thurston', geometry: false, orderBy: 'OBJECTID', pageSize: 1000,
layer: 'https://map.co.thurston.wa.us/arcgis/rest/services/Thurston/Thurston_Parcels/FeatureServer/0',
where: 'SALE_PRICE>0',
outFields: 'PARCEL_NO,SALE_PRICE,SALE_DATE,SITUS_STRE,SITUS_CITY,SITUS_ZIP',
fieldMap: { source_id: 'PARCEL_NO', address: 'SITUS_STRE', city: 'SITUS_CITY', zip: 'SITUS_ZIP',
last_sale_price: 'SALE_PRICE', last_sale_date: 'SALE_DATE' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.PARCEL_NO); if (!apn) return null;
return { fips: '53067', apn, address: clean(a.SITUS_STRE), city: clean(a.SITUS_CITY), zip: s(a.SITUS_ZIP),
price: anyPrice(a.SALE_PRICE), saleDate: anyDate(a.SALE_DATE), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'palm-beach': {
key: 'palm-beach', label: 'Palm Beach County FL', cursorKey: 'palm_beach', geometry: false, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services1.arcgis.com/RTiKiFNGzgAobBzy/arcgis/rest/services/ParcelPropertyDetails/FeatureServer/1',
where: 'PRICE>0',
outFields: 'PARCEL_ID,PRICE,SALE_DATE,SITE_ADDR,CITY,OWNER_NAME1',
fieldMap: { source_id: 'PARCEL_ID', address: 'SITE_ADDR', city: 'CITY',
last_sale_price: 'PRICE', last_sale_date: 'SALE_DATE', owner_name: 'OWNER_NAME1' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.PARCEL_ID); if (!apn) return null;
return { fips: '12099', apn, price: anyPrice(a.PRICE), saleDate: anyDate(a.SALE_DATE),
address: clean(a.SITE_ADDR), city: clean(a.CITY), grantee: clean(a.OWNER_NAME1), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
klamath: {
key: 'klamath', label: 'Klamath County OR (Klamath Falls)', cursorKey: 'klamath', geometry: false, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services.arcgis.com/H6Mh1bySxR4oHx6x/arcgis/rest/services/KC_ParcelSales/FeatureServer/0',
where: 'SALE_PRICE>1000', // rich Klamath assessor sales layer: SALE_PRICE(int)+SALE_DATE(epoch-ms)+owner+situs+sqft/beds/baths/yrblt+total appraised
outFields: 'FIRST_PROP_ID,SALE_PRICE,SALE_DATE,SALEBK,OWNER_NAME,MIN_SITUS_ADDRESS,FIRST_CITY_NAME,SUM_FINSQFT,SUM_BEDRMS,SUM_BATH,MIN_YRBLT,SUM_Tot_Appr,FIRST_PCLCD',
fieldMap: { source_id: 'FIRST_PROP_ID', address: 'MIN_SITUS_ADDRESS', city: 'FIRST_CITY_NAME',
sqft: 'SUM_FINSQFT', beds: 'SUM_BEDRMS', baths: 'SUM_BATH', year_built: 'MIN_YRBLT',
total_value: 'SUM_Tot_Appr', use_desc: 'FIRST_PCLCD', owner_name: 'OWNER_NAME',
last_sale_price: 'SALE_PRICE', last_sale_date: 'SALE_DATE' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.FIRST_PROP_ID); if (!apn) return null;
return { fips: '41035', apn, price: anyPrice(a.SALE_PRICE), saleDate: anyDate(a.SALE_DATE),
address: clean(a.MIN_SITUS_ADDRESS), city: clean(a.FIRST_CITY_NAME),
sqft: num(a.SUM_FINSQFT), beds: num(a.SUM_BEDRMS), baths: num(a.SUM_BATH), year: num(a.MIN_YRBLT),
totalValue: num(a.SUM_Tot_Appr), use: clean(a.FIRST_PCLCD),
grantee: clean(a.OWNER_NAME), docNum: clean(a.SALEBK), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
marion: {
key: 'marion', label: 'Marion County OR (Salem)', cursorKey: 'marion', geometry: false, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services1.arcgis.com/sYGZnQPdJ0azuLyn/arcgis/rest/services/TaxParcel_Assessment/FeatureServer/0',
where: 'SALEPRICE>1000', // rich Marion assessor layer: SALEPRICE(int)+INSTDATE(epoch-ms deed date)+deed doc/type+owner+situs+livingarea+yearbuilt+RMV total appraised
outFields: 'TAXLOT,SALEPRICE,INSTDATE,INSTNUM,INSTTYPE,OWNERNAME,SITUS,YEARBUILT,LIVINGAREA,RMVTOTAL,PROPCLASS,STCLSDESC',
fieldMap: { source_id: 'TAXLOT', address: 'SITUS', sqft: 'LIVINGAREA', year_built: 'YEARBUILT',
total_value: 'RMVTOTAL', use_desc: 'STCLSDESC', owner_name: 'OWNERNAME',
last_sale_price: 'SALEPRICE', last_sale_date: 'INSTDATE' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.TAXLOT); if (!apn) return null;
return { fips: '41047', apn, price: anyPrice(a.SALEPRICE), saleDate: anyDate(a.INSTDATE),
address: clean(a.SITUS), sqft: num(a.LIVINGAREA), year: num(a.YEARBUILT), totalValue: num(a.RMVTOTAL),
use: clean(a.STCLSDESC) || clean(a.PROPCLASS),
grantee: clean(a.OWNERNAME), docNum: s(a.INSTNUM), docType: clean(a.INSTTYPE), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
yakima: {
key: 'yakima', label: 'Yakima County WA', cursorKey: 'yakima', geometry: false, orderBy: 'DBO.Parcels.OBJECTID', pageSize: 2000,
// rich Yakima assessor parcel layer — attribute keys come back FULLY-QUALIFIED (DBO.Parcels.*).
// GROSS_SALE_PRICE(double)+SALE_DATE(string M/D/YYYY)+EXCISE_NUMBER(doc)+GRANTOR_NAME+owner(LAST/FIRST/ORG)+situs+use+yrblt+sqft+beds/baths+market land/impvt.
layer: 'https://gis.yakimawa.gov/arcgis/rest/services/Assessor/AssessorParcels/MapServer/0',
where: 'DBO.Parcels.GROSS_SALE_PRICE>1000',
outFields: 'DBO.Parcels.ASSESSOR_NO,DBO.Parcels.GROSS_SALE_PRICE,DBO.Parcels.SALE_DATE,DBO.Parcels.EXCISE_NUMBER,DBO.Parcels.GRANTOR_NAME,DBO.Parcels.SITUS_ADDR,DBO.Parcels.SITUS_CITY,DBO.Parcels.SITUS_ZIP,DBO.Parcels.USE_CODE,DBO.Parcels.YEAR_BLT,DBO.Parcels.MAIN_SQFT,DBO.Parcels.BEDROOMS,DBO.Parcels.FULL_BATH,DBO.Parcels.MKT_LAND,DBO.Parcels.MKT_IMPVT,DBO.Parcels.LAST_NAME,DBO.Parcels.FIRST_NAME,DBO.Parcels.ORG_NAME',
fieldMap: { source_id: 'DBO.Parcels.ASSESSOR_NO', address: 'DBO.Parcels.SITUS_ADDR', city: 'DBO.Parcels.SITUS_CITY',
zip: 'DBO.Parcels.SITUS_ZIP', sqft: 'DBO.Parcels.MAIN_SQFT', beds: 'DBO.Parcels.BEDROOMS',
baths: 'DBO.Parcels.FULL_BATH', year_built: 'DBO.Parcels.YEAR_BLT', use_desc: 'DBO.Parcels.USE_CODE',
total_value: 'DBO.Parcels.MKT_LAND+DBO.Parcels.MKT_IMPVT', owner_name: 'DBO.Parcels.ORG_NAME|FIRST_NAME+LAST_NAME',
last_sale_price: 'DBO.Parcels.GROSS_SALE_PRICE', last_sale_date: 'DBO.Parcels.SALE_DATE' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a['DBO.Parcels.ASSESSOR_NO']); if (!apn) return null;
let situs = clean(a['DBO.Parcels.SITUS_ADDR']); if (situs && /^UNASSIGNED$/i.test(situs)) situs = null;
const org = clean(a['DBO.Parcels.ORG_NAME']);
const person = [clean(a['DBO.Parcels.FIRST_NAME']), clean(a['DBO.Parcels.LAST_NAME'])].filter(Boolean).join(' ').trim() || null;
const owner = org || person;
const land = num(a['DBO.Parcels.MKT_LAND']) || 0, impvt = num(a['DBO.Parcels.MKT_IMPVT']) || 0;
return { fips: '53077', apn, price: anyPrice(a['DBO.Parcels.GROSS_SALE_PRICE']), saleDate: anyDate(a['DBO.Parcels.SALE_DATE']),
address: situs, city: clean(a['DBO.Parcels.SITUS_CITY']), zip: s(a['DBO.Parcels.SITUS_ZIP']),
sqft: num(a['DBO.Parcels.MAIN_SQFT']), beds: num(a['DBO.Parcels.BEDROOMS']), baths: num(a['DBO.Parcels.FULL_BATH']),
year: num(a['DBO.Parcels.YEAR_BLT']), totalValue: (land + impvt) || null, use: clean(a['DBO.Parcels.USE_CODE']),
grantor: clean(a['DBO.Parcels.GRANTOR_NAME']), grantee: owner, docNum: s(a['DBO.Parcels.EXCISE_NUMBER']), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
lane: {
key: 'lane', label: 'Lane County OR (Eugene)', cursorKey: 'lane', geometry: false, orderBy: 'OBJECTID', pageSize: 2000,
// Lane County AddressParcelSales MapServer, "Sales (last 3 years)" layer — a JOIN layer, so every
// attribute key carries a long SalesLayerCityJoin_SalesforGISLayerAll_ prefix (same class of quirk
// as Yakima's DBO.Parcels.* qualified keys). Real recorded sale_price(int) + DeedDate(epoch-ms) +
// deed_transfer_no(doc, space-padded) + deed_type(WD/PR/...) + situs/city/zip + stat_class(use) +
// reject_code (ratio-study/arm's-length flag — kept, not filtered; preserved in raw for provenance).
layer: 'https://lcmaps.lanecounty.org/arcgis/rest/services/AT/AddressParcelSales/MapServer/1',
where: 'SalesLayerCityJoin_SalesforGISLayerAll_sale_price>1000',
// maplot (taxlot key) MUST be first — engine derives the per-parcel GIS deep-link as where=<first>='<apn>' and apn=maplot.
outFields: 'SalesLayerCityJoin_SalesforGISLayerAll_maplot,SalesLayerCityJoin_SalesforGISLayerAll_account,SalesLayerCityJoin_SalesforGISLayerAll_sale_price,DeedDate,SalesLayerCityJoin_SalesforGISLayerAll_deed_transfer_no,SalesLayerCityJoin_SalesforGISLayerAll_deed_type,SalesLayerCityJoin_SalesforGISLayerAll_reject_code,SalesLayerCityJoin_SalesforGISLayerAll_situs_address,SalesLayerCityJoin_SalesforGISLayerAll_city,SalesLayerCityJoin_SalesforGISLayerAll_zip,SalesLayerCityJoin_SalesforGISLayerAll_stat_class',
fieldMap: { source_id: 'SalesLayerCityJoin_SalesforGISLayerAll_maplot', address: 'SalesLayerCityJoin_SalesforGISLayerAll_situs_address',
city: 'SalesLayerCityJoin_SalesforGISLayerAll_city', zip: 'SalesLayerCityJoin_SalesforGISLayerAll_zip',
use_desc: 'SalesLayerCityJoin_SalesforGISLayerAll_stat_class', doc_type: 'SalesLayerCityJoin_SalesforGISLayerAll_deed_type',
last_sale_price: 'SalesLayerCityJoin_SalesforGISLayerAll_sale_price', last_sale_date: 'DeedDate' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const P = 'SalesLayerCityJoin_SalesforGISLayerAll_';
const apn = s(a[P + 'maplot']); if (!apn) return null; // maplot only — keeps the deep-link where-clause valid
return { fips: '41039', apn, price: anyPrice(a[P + 'sale_price']), saleDate: anyDate(a.DeedDate),
address: clean(a[P + 'situs_address']), city: clean(a[P + 'city']), zip: s(a[P + 'zip']),
use: clean(a[P + 'stat_class']),
docNum: clean(a[P + 'deed_transfer_no']), docType: clean(a[P + 'deed_type']), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
jackson: {
key: 'jackson', label: 'Jackson County OR (Medford)', cursorKey: 'jackson', geometry: false, orderBy: 'OBJECTID', pageSize: 1000, // server maxRecordCount=1000; pageSize>that early-breaks the loop after one page (same as la-county)
// Jackson County Assessment "LatestSales" FeatureServer — an unusually rich, clean (unqualified-key)
// priced-deed layer: SalesPrice(double)+SalesDate(epoch-ms)+DocumentNumber+DocumentTypeDescription
// (deed type e.g. BARGAIN & SALE)+Grantor+Grantee NAMES+SiteAddress/City+StatClassDescription(use)+
// YearBuilt+SquareFeet. maptaxlot is the parcel key. HTTPS only.
layer: 'https://spatial.jacksoncountyor.gov/arcgis/rest/services/Assessment/LatestSales/FeatureServer/0',
where: 'SalesPrice>1000',
// maptaxlot MUST be first — engine derives the per-parcel GIS deep-link as where=<first>='<apn>'.
outFields: 'maptaxlot,SalesPrice,SalesDate,DocumentNumber,DocumentTypeDescription,SiteAddress,SiteCity,StatClassDescription,Grantor,Grantee,YearBuilt,SquareFeet',
fieldMap: { source_id: 'maptaxlot', address: 'SiteAddress', city: 'SiteCity', use_desc: 'StatClassDescription',
sqft: 'SquareFeet', year_built: 'YearBuilt', doc_type: 'DocumentTypeDescription',
last_sale_price: 'SalesPrice', last_sale_date: 'SalesDate' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.maptaxlot); if (!apn) return null;
return { fips: '41029', apn, price: anyPrice(a.SalesPrice), saleDate: anyDate(a.SalesDate),
address: clean(a.SiteAddress), city: clean(a.SiteCity), use: clean(a.StatClassDescription),
year: num(a.YearBuilt), sqft: num(a.SquareFeet),
grantor: clean(a.Grantor), grantee: clean(a.Grantee),
docNum: clean(a.DocumentNumber), docType: clean(a.DocumentTypeDescription), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'orange-county': {
// Orange County CA — LA's neighbor, ~982k parcels. CA AB-1785 suppresses recorded
// sale price, so this is a COVERAGE config (no price/events, priced=0) like la-county
// /san-diego-sandag: address + assessed value + centroid lat/lng. Value fields are
// STRING-typed (num() parses); AssdAmt often null on condo units → fall back to
// LandVal+ImprovedVal. lat/lng via returnCentroid (centroid:true) — no polygon download.
key: 'orange-county', label: 'Orange County CA (Assessor legal lots)', cursorKey: 'orange_county',
geometry: false, centroid: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://ocgis.com/arcpub/rest/services/LegalLotsAttributeOpenData/FeatureServer/0',
where: 'AssessmentNo IS NOT NULL',
outFields: 'AssessmentNo,SiteAddress,SiteCityState,SiteZip5,LandVal,ImprovedVal,AssdAmt,GPLU_DESC,ZC_DESCR',
fieldMap: { source_id: 'AssessmentNo', address: 'SiteAddress', city: 'SiteCityState', zip: 'SiteZip5',
use_desc: 'GPLU_DESC|ZC_DESCR', total_value: 'AssdAmt|LandVal+ImprovedVal', lat: 'centroid.y', lng: 'centroid.x' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.AssessmentNo); if (!apn) return null;
const c = f.centroid || {}; const land = num(a.LandVal), imp = num(a.ImprovedVal), assd = num(a.AssdAmt);
return { fips: '06059', apn, address: clean(a.SiteAddress), city: clean(a.SiteCityState), zip: s(a.SiteZip5),
lat: num(c.y), lng: num(c.x), use: clean(a.GPLU_DESC) || clean(a.ZC_DESCR),
totalValue: assd || ((land || 0) + (imp || 0) || null), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'ventura-county': {
// Ventura County CA — LA-adjacent ring. AB-1785 (no sale price) AND Ventura's public
// REST carries no value/characteristics — this is situs-ADDRESS coverage only (like
// santa-clara). apn-keyed Address layer, 299,739 rows. lat/lng lives on a SEPARATE
// apn-keyed layer (DataDownloads/CommonData/2 has lat/lon) — a future join enhancement.
key: 'ventura-county', label: 'Ventura County CA (situs addresses)', cursorKey: 'ventura_county',
geometry: false, orderBy: 'objectid', pageSize: 2000,
layer: 'https://maps.ventura.org/arcgis/rest/services/DataDownloads/Address/MapServer/0',
where: 'apn IS NOT NULL',
outFields: 'apn,fullsitus,city,zip',
fieldMap: { source_id: 'apn', address: 'fullsitus', city: 'city', zip: 'zip' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.apn); if (!apn) return null;
return { fips: '06111', apn, address: clean(a.fullsitus), city: clean(a.city), zip: s(a.zip), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'santa-barbara': {
// Santa Barbara County CA — next ring out from LA. AB-1785 (no sale price) → COVERAGE config,
// but the richest CA roll so far: situs + LandUse text + assessed value + full characteristics + owner.
// Blanks come as ' '/0 (commercial parcels) → num() nulls them. lat/lng via ring centroid
// (returnCentroid unsupported here; native SR 3310, outSR=4326 requested by geometry:true).
key: 'santa-barbara', label: 'Santa Barbara County CA', cursorKey: 'santa_barbara',
geometry: true, orderBy: 'OBJECTID_1', pageSize: 1000, // OID field is OBJECTID_1 (not OBJECTID); maxRecordCount=1000
layer: 'https://maps.calagpermits.org/arcgis/rest/services/SantaBarbara/Parcels/MapServer/0',
where: 'APN IS NOT NULL',
outFields: 'APN,Situs1,SCity,SZip,LandUse,LandValue,StrImpr,Net_AV,YearBuilt,SqFootage,Bedrooms,Bathrooms,Owner',
fieldMap: { source_id: 'APN', address: 'Situs1', city: 'SCity', zip: 'SZip', use_desc: 'LandUse',
total_value: 'Net_AV', year_built: 'YearBuilt', sqft: 'SqFootage', beds: 'Bedrooms', baths: 'Bathrooms', owner_name: 'Owner' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null;
const [lng, lat] = centroid(f.geometry);
return { fips: '06083', apn, address: clean(a.Situs1), city: clean(a.SCity), zip: s(a.SZip),
lat, lng, use: clean(a.LandUse), year: num(a.YearBuilt), sqft: num(a.SqFootage),
beds: num(a.Bedrooms), baths: num(a.Bathrooms), totalValue: num(a.Net_AV),
grantee: clean(a.Owner), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
imperial: {
// Imperial County CA (Salton Sea / El Centro). AB-1785 → COVERAGE config. Has explicit
// Longitude/Latitude fields (cleanest lat/lng — no geometry needed). Land/Imp_ are STRING-typed
// (anyPrice strips formatting). LandUse1 is an assessor code only (no long text). No characteristics.
key: 'imperial', label: 'Imperial County CA', cursorKey: 'imperial',
geometry: false, orderBy: 'FID', pageSize: 2000, // OID field is FID
layer: 'https://services7.arcgis.com/RomaVqqozKczDNgd/ArcGIS/rest/services/Parcels_Oct_2023/FeatureServer/0',
where: 'APN IS NOT NULL',
outFields: 'APN,F_Situs1,Sit_Zip,LandUse1,Land,Imp_,Assessee,Longitude,Latitude',
fieldMap: { source_id: 'APN', address: 'F_Situs1', zip: 'Sit_Zip', use_desc: 'LandUse1',
total_value: 'Land+Imp_', owner_name: 'Assessee', lat: 'Latitude', lng: 'Longitude' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null;
const land = anyPrice(a.Land), imp = anyPrice(a.Imp_);
return { fips: '06025', apn, address: clean(a.F_Situs1), zip: s(a.Sit_Zip), use: clean(a.LandUse1),
lat: num(a.Latitude), lng: num(a.Longitude), totalValue: (land || 0) + (imp || 0) || null,
grantee: clean(a.Assessee), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'san-bernardino': {
// San Bernardino County CA — largest CA county by area, ~840k parcels (huge coverage win).
// AB-1785 → COVERAGE config. NO situs address on this roll. Value fields are COMMA-formatted
// strings ('42,345') → anyPrice() strips commas; plain num()/Number() would yield NaN. Owner is
// mostly redacted ("Protected Per CA Gov Code 7928.205") → filtered to null. lat/lng via ring centroid.
key: 'san-bernardino', label: 'San Bernardino County CA', cursorKey: 'san_bernardino',
geometry: true, orderBy: 'OBJECTID', pageSize: 1000, // maxRecordCount=1000
layer: 'https://services.arcgis.com/aA3snZwJfFkVyDuP/arcgis/rest/services/Parcels_for_San_Bernardino_County/FeatureServer/0',
where: 'ParcelNumber IS NOT NULL',
outFields: 'ParcelNumber,AssessClass,LandValue,ImprovementValue,OwnerName,Acreage',
fieldMap: { source_id: 'ParcelNumber', use_desc: 'AssessClass', total_value: 'LandValue+ImprovementValue', owner_name: 'OwnerName' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.ParcelNumber); if (!apn) return null;
const [lng, lat] = centroid(f.geometry);
const land = anyPrice(a.LandValue), imp = anyPrice(a.ImprovementValue);
const own = clean(a.OwnerName); const owner = own && !/^Protected Per/i.test(own) ? own : null;
return { fips: '06071', apn, lat, lng, use: clean(a.AssessClass),
totalValue: (land || 0) + (imp || 0) || null, grantee: owner, raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'san-joaquin': {
// San Joaquin County CA (Stockton/Tracy) — Ring 3. AB-1785 → COVERAGE config. Rich roll:
// FULL_ADDRESS (one-line incl city/state/zip), use desc, value (STRING, no commas), full characteristics.
// lat/lng via returnCentroid (centroid:true).
key: 'san-joaquin', label: 'San Joaquin County CA', cursorKey: 'san_joaquin',
geometry: false, centroid: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services2.arcgis.com/GQhSReJEO6f7tsvy/arcgis/rest/services/Parcels/FeatureServer/0',
where: 'APN IS NOT NULL',
outFields: 'APN,FULL_ADDRESS,SITUSZIP,DESCRIPTION,LAND_VALUE,IMPROVEMENT_VALUE,YEAR_BUILT,TOTALLIV_AREA,BEDROOMS,BATHROOM_WHOLE',
fieldMap: { source_id: 'APN', address: 'FULL_ADDRESS', zip: 'SITUSZIP', use_desc: 'DESCRIPTION',
total_value: 'LAND_VALUE+IMPROVEMENT_VALUE', year_built: 'YEAR_BUILT', sqft: 'TOTALLIV_AREA',
beds: 'BEDROOMS', baths: 'BATHROOM_WHOLE', lat: 'centroid.y', lng: 'centroid.x' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null;
const c = f.centroid || {}; const land = anyPrice(a.LAND_VALUE), imp = anyPrice(a.IMPROVEMENT_VALUE);
return { fips: '06077', apn, address: clean(a.FULL_ADDRESS), zip: s(a.SITUSZIP), use: clean(a.DESCRIPTION),
lat: num(c.y), lng: num(c.x), year: num(a.YEAR_BUILT), sqft: num(a.TOTALLIV_AREA),
beds: num(a.BEDROOMS), baths: num(a.BATHROOM_WHOLE),
totalValue: (land || 0) + (imp || 0) || null, raw: a } as Rec; }).filter(Boolean) as Rec[],
},
fresno: {
// Fresno County CA — Ring 3, largest here (~412k with real APN; empty-APN head rows filtered by where).
// AB-1785 → COVERAGE config. Enterprise SDE MapServer: attribute names are plain but a SHAPE.STArea()
// helper exists → always request explicit outFields. City+zip are packed into SITEADDRESS2
// ("DOS PALOS 93620") → parsed. Integer value fields. lat/lng via geometry+ring centroid (returnCentroid unsupported).
key: 'fresno', label: 'Fresno County CA', cursorKey: 'fresno',
geometry: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://gisprod10.co.fresno.ca.us/server/rest/services/FC_PARCEL_SELECT/MapServer/0',
where: "APN<>''",
outFields: 'APN,SITEADDRESS1,SITEADDRESS2,ASSESS_LAND_VAL,ASSESS_IMP_VAL,TOTAL_ASSESSED_VALUE,NAME1',
fieldMap: { source_id: 'APN', address: 'SITEADDRESS1', city: 'SITEADDRESS2(city)', zip: 'SITEADDRESS2(zip)',
total_value: 'TOTAL_ASSESSED_VALUE', owner_name: 'NAME1' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null;
const [lng, lat] = centroid(f.geometry);
const sa2 = clean(a.SITEADDRESS2) || ''; const zip = (sa2.match(/\b(\d{5})\b/) || [])[1] || null;
const city = clean(sa2.replace(/\s*\d{5}.*$/, '')) || null;
return { fips: '06019', apn, address: clean(a.SITEADDRESS1), city, zip,
lat, lng, totalValue: num(a.TOTAL_ASSESSED_VALUE), grantee: clean(a.NAME1), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
monterey: {
// Monterey County CA — Ring 3. AB-1785 → COVERAGE config. No situs address; qualifies via assessed
// value. Land_Use (desc) + integer Land_Value/Imp_Value. City is often the literal string "None" → null.
// lat/lng via returnCentroid (centroid:true).
key: 'monterey', label: 'Monterey County CA', cursorKey: 'monterey',
geometry: false, centroid: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services2.arcgis.com/nOGTdfb4kF4dZljH/arcgis/rest/services/Parcels_Data/FeatureServer/0',
where: 'APN IS NOT NULL',
outFields: 'APN,City,Land_Use,Land_Value,Imp_Value',
fieldMap: { source_id: 'APN', city: 'City', use_desc: 'Land_Use', total_value: 'Land_Value+Imp_Value', lat: 'centroid.y', lng: 'centroid.x' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null;
const c = f.centroid || {}; const land = num(a.Land_Value), imp = num(a.Imp_Value);
let city = clean(a.City); if (city && /^none$/i.test(city)) city = null;
return { fips: '06053', apn, city, use: clean(a.Land_Use), lat: num(c.y), lng: num(c.x),
totalValue: (land || 0) + (imp || 0) || null, raw: a } as Rec; }).filter(Boolean) as Rec[],
},
solano: {
// Solano County CA — Ring 4, richest (value + situs + characteristics). AB-1785 → COVERAGE.
// Explicit xcentroid/ycentroid fields (no geometry needed). Integer values. `situs` is a Y/N flag,
// NOT the address → use p_address; where filters blank-address govt parcels.
key: 'solano', label: 'Solano County CA', cursorKey: 'solano', geometry: false, orderBy: 'objectid', pageSize: 2000,
layer: 'https://services2.arcgis.com/SCn6czzcqKAFwdGU/arcgis/rest/services/Parcels_Public_Aumentum/FeatureServer/0',
where: "p_address<>''",
outFields: 'asmtnum,p_address,sitecity,use_desc,valland,valimp,valtv,yrbuilt,total_area,bedroom,bathroom,xcentroid,ycentroid',
fieldMap: { source_id: 'asmtnum', address: 'p_address', city: 'sitecity', use_desc: 'use_desc',
total_value: 'valtv|valland+valimp', year_built: 'yrbuilt', sqft: 'total_area', beds: 'bedroom', baths: 'bathroom',
lat: 'ycentroid', lng: 'xcentroid' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.asmtnum); if (!apn) return null;
const tv = num(a.valtv) || ((num(a.valland) || 0) + (num(a.valimp) || 0) || null);
return { fips: '06095', apn, address: clean(a.p_address), city: clean(a.sitecity), use: clean(a.use_desc),
lat: num(a.ycentroid), lng: num(a.xcentroid), year: num(a.yrbuilt), sqft: num(a.total_area),
beds: num(a.bedroom), baths: num(a.bathroom), totalValue: tv, raw: a } as Rec; }).filter(Boolean) as Rec[],
},
butte: {
// Butte County CA — Ring 4. AB-1785 → COVERAGE, no value on this roll. SITUS is a one-line address
// ("492 G ST, BIGGS CA 95917") or the literal "No Address Available" (→ null). Explicit Latitude/Longitude.
key: 'butte', label: 'Butte County CA', cursorKey: 'butte', geometry: false, orderBy: 'FID', pageSize: 2000,
layer: 'https://services.arcgis.com/3t3QfTXFRFX44zo8/arcgis/rest/services/Butte_County_Parcel_Public_Data/FeatureServer/0',
where: 'APN IS NOT NULL',
outFields: 'APN,SITUS,LandUse,Latitude,Longitude',
fieldMap: { source_id: 'APN', address: 'SITUS', use_desc: 'LandUse', lat: 'Latitude', lng: 'Longitude' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null;
let addr = clean(a.SITUS); if (addr && /^no address available$/i.test(addr)) addr = null;
return { fips: '06007', apn, address: addr, use: clean(a.LandUse), lat: num(a.Latitude), lng: num(a.Longitude), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
sacramento: {
// Sacramento County CA — Ring 4, biggest here (~502k). AB-1785 → COVERAGE, no value. Address from
// STREET_NBR+STREET_NAM; ZIP is a Double. lat/lng via returnCentroid.
key: 'sacramento', label: 'Sacramento County CA', cursorKey: 'sacramento', geometry: false, centroid: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services1.arcgis.com/5NARefyPVtAeuJPU/arcgis/rest/services/Parcels/FeatureServer/0',
where: 'APN IS NOT NULL',
outFields: 'APN,STREET_NBR,STREET_NAM,CITY,ZIP,LU_SPECIF,LU_GENERAL',
fieldMap: { source_id: 'APN', address: 'STREET_NBR+STREET_NAM', city: 'CITY', zip: 'ZIP', use_desc: 'LU_SPECIF', lat: 'centroid.y', lng: 'centroid.x' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null; const c = f.centroid || {};
const addr = [s(a.STREET_NBR), clean(a.STREET_NAM)].filter(Boolean).join(' ').trim() || null;
const zip = num(a.ZIP) ? String(num(a.ZIP)) : null;
return { fips: '06067', apn, address: addr, city: clean(a.CITY), zip, use: clean(a.LU_SPECIF) || clean(a.LU_GENERAL),
lat: num(c.y), lng: num(c.x), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
stanislaus: {
// Stanislaus County CA — Ring 4. AB-1785 → COVERAGE, no value (situs + zoning). Situs_City packs
// "City ST ZIP" ("Newman CA 95360") → trimmed to city. lat/lng via returnCentroid.
key: 'stanislaus', label: 'Stanislaus County CA', cursorKey: 'stanislaus', geometry: false, centroid: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services.arcgis.com/EeYBJFxLdUojipYa/arcgis/rest/services/Public_Parcels/FeatureServer/0',
where: 'APN IS NOT NULL',
outFields: 'APN,Situs_Address,Situs_City,Situs_Zip,Zoning',
fieldMap: { source_id: 'APN', address: 'Situs_Address', city: 'Situs_City', zip: 'Situs_Zip', lat: 'centroid.y', lng: 'centroid.x' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null; const c = f.centroid || {};
let city = clean(a.Situs_City); if (city) city = city.replace(/\s+[A-Z]{2}\s+\d{5}.*$/, '').trim() || null; // "Newman CA 95360" -> "Newman"
return { fips: '06099', apn, address: clean(a.Situs_Address), city, zip: s(a.Situs_Zip), lat: num(c.y), lng: num(c.x), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
napa: {
// Napa County CA — Ring 5, richest schema (value + situs + characteristics). AB-1785 → COVERAGE.
// NOTE: parcel layer is FeatureServer/1 (not /0). lat/lng via geometry ring centroid.
key: 'napa', label: 'Napa County CA', cursorKey: 'napa', geometry: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services1.arcgis.com/Ko5rxt00spOfjMqj/arcgis/rest/services/Napa_County_Public_Parcels/FeatureServer/1',
where: 'ASMT IS NOT NULL',
outFields: 'ASMTWithDash,FullSitusAddress1,Community,Zip,LandUseDescription,TotalLandImprValue,YearBuilt,Building_Size_SqFt,BedroomsCount,FullBathsCount',
fieldMap: { source_id: 'ASMTWithDash', address: 'FullSitusAddress1', city: 'Community', zip: 'Zip', use_desc: 'LandUseDescription',
total_value: 'TotalLandImprValue', year_built: 'YearBuilt', sqft: 'Building_Size_SqFt', beds: 'BedroomsCount', baths: 'FullBathsCount' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.ASMTWithDash); if (!apn) return null;
const [lng, lat] = centroid(f.geometry);
return { fips: '06055', apn, address: clean(a.FullSitusAddress1), city: clean(a.Community), zip: s(a.Zip),
use: clean(a.LandUseDescription), lat, lng, year: num(a.YearBuilt), sqft: num(a.Building_Size_SqFt),
beds: num(a.BedroomsCount), baths: num(a.FullBathsCount), totalValue: num(a.TotalLandImprValue), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'contra-costa': {
// Contra Costa County CA — Ring 5, largest here (~388k). AB-1785 → COVERAGE. MapServer layer.
// full_address_display is sometimes "NO ADDRESS - <CITY>" → nulled. lat/lng via geometry ring centroid.
key: 'contra-costa', label: 'Contra Costa County CA', cursorKey: 'contra_costa', geometry: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://ccmap.cccounty.us/arcgis/rest/services/CCMAP/Assessment_Parcels_ArcPro/MapServer/0',
where: 'APN IS NOT NULL',
outFields: 'APN,full_address_display,s_city,S_ZIP,Description,LAND_VALUE,IMP_VAL,YR_BUILT,BLDG_SQFT',
fieldMap: { source_id: 'APN', address: 'full_address_display', city: 's_city', zip: 'S_ZIP', use_desc: 'Description',
total_value: 'LAND_VALUE+IMP_VAL', year_built: 'YR_BUILT', sqft: 'BLDG_SQFT' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN); if (!apn) return null;
const [lng, lat] = centroid(f.geometry);
let addr = clean(a.full_address_display); if (addr && /^NO ADDRESS/i.test(addr)) addr = null;
const land = num(a.LAND_VALUE), imp = num(a.IMP_VAL);
return { fips: '06013', apn, address: addr, city: clean(a.s_city), zip: num(a.S_ZIP) ? String(num(a.S_ZIP)) : null,
use: clean(a.Description), lat, lng, year: num(a.YR_BUILT), sqft: num(a.BLDG_SQFT),
totalValue: (land || 0) + (imp || 0) || null, raw: a } as Rec; }).filter(Boolean) as Rec[],
},
'el-dorado': {
// El Dorado County CA — Ring 5. AB-1785 → COVERAGE. Has EXPLICIT LAT_Y/LONG_X (no geometry needed) + owner.
key: 'el-dorado', label: 'El Dorado County CA', cursorKey: 'el_dorado', geometry: false, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services1.arcgis.com/5b2oZtu5Eo64aE20/arcgis/rest/services/wlemp_master_parcels_info/FeatureServer/0',
where: 'PRCL_ID IS NOT NULL',
outFields: 'PRCL_ID,PRCL_ADDR,PO_NAME,ZIP_CODE,USE_CD_LITPRI,TOTAL_VAL,IMPR_SQ_FT,OWNER_NAME,LAT_Y,LONG_X',
fieldMap: { source_id: 'PRCL_ID', address: 'PRCL_ADDR', city: 'PO_NAME', zip: 'ZIP_CODE', use_desc: 'USE_CD_LITPRI',
total_value: 'TOTAL_VAL', sqft: 'IMPR_SQ_FT', owner_name: 'OWNER_NAME', lat: 'LAT_Y', lng: 'LONG_X' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.PRCL_ID); if (!apn) return null;
return { fips: '06017', apn, address: clean(a.PRCL_ADDR), city: clean(a.PO_NAME), zip: s(a.ZIP_CODE),
use: clean(a.USE_CD_LITPRI), lat: num(a.LAT_Y), lng: num(a.LONG_X), sqft: num(a.IMPR_SQ_FT),
totalValue: num(a.TOTAL_VAL), grantee: clean(a.OWNER_NAME), raw: a } as Rec; }).filter(Boolean) as Rec[],
},
nevada: {
// Nevada County CA — Ring 5. AB-1785 → COVERAGE. MapServer. lat/lng via geometry ring centroid.
key: 'nevada', label: 'Nevada County CA', cursorKey: 'nevada', geometry: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://maps.nevadacountyca.gov/arcgis/rest/services/web_public/parcel_mail_situs_external/MapServer/0',
where: 'APN IS NOT NULL',
outFields: 'APN_FORMATTED,ADDRESS,PostOffice,ZipCode,UseCode,TotalLandValue,TotalImproveValue',
fieldMap: { source_id: 'APN_FORMATTED', address: 'ADDRESS', city: 'PostOffice', zip: 'ZipCode', use_desc: 'UseCode',
total_value: 'TotalLandValue+TotalImproveValue' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN_FORMATTED); if (!apn) return null;
const [lng, lat] = centroid(f.geometry); const land = num(a.TotalLandValue), imp = num(a.TotalImproveValue);
return { fips: '06057', apn, address: clean(a.ADDRESS), city: clean(a.PostOffice), zip: s(a.ZipCode),
use: clean(a.UseCode), lat, lng, totalValue: (land || 0) + (imp || 0) || null, raw: a } as Rec; }).filter(Boolean) as Rec[],
},
tulare: {
// Tulare County CA — Ring 5. AB-1785 → COVERAGE. No situs (value-only). NOTE: use APN_TXT (string) not
// APN (numeric — drops leading zeros). lat/lng via geometry ring centroid.
key: 'tulare', label: 'Tulare County CA', cursorKey: 'tulare', geometry: true, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://services2.arcgis.com/bYBANhmQGwSSLC0l/ArcGIS/rest/services/Public_Parcels/FeatureServer/0',
where: 'APN_TXT IS NOT NULL',
outFields: 'APN_TXT,USEDSCRP,LAND_VAL,IMP_VAL',
fieldMap: { source_id: 'APN_TXT', use_desc: 'USEDSCRP', total_value: 'LAND_VAL+IMP_VAL' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.APN_TXT); if (!apn) return null;
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).
// Situs/owner/use live on WhatcomCo_Property; enriched offline via scripts/enrich-whatcom-situs.mjs.
// OID field reports 'None' but orderBy=OBJECTID + resultOffset paging works; maxRecordCount=1000.
key: 'whatcom', label: 'Whatcom County WA (Bellingham) — SWDSales2022', 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[],
},
'whatcom-2021': {
// TK-10777: SWDSales2021 historical layer — same schema as whatcom (2022).
// history: true → engine uses upsertParcelHistory (date-guarded, never regresses a newer sale).
key: 'whatcom-2021', label: 'Whatcom County WA (Bellingham) — SWDSales2021', cursorKey: 'whatcom_2021', geometry: false, orderBy: 'OBJECTID', pageSize: 1000,
layer: 'https://gis.whatcomcounty.us/arcgis/rest/services/EnterprisePublishing/WhatcomCo_PropertySales/MapServer/1',
where: 'sale_price>1000', history: true,
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[],
},
'whatcom-2020': {
// TK-10777: SWDSales2020 historical layer — same schema as whatcom (2022).
// history: true → engine uses upsertParcelHistory (date-guarded, never regresses a newer sale).
key: 'whatcom-2020', label: 'Whatcom County WA (Bellingham) — SWDSales2020', cursorKey: 'whatcom_2020', geometry: false, orderBy: 'OBJECTID', pageSize: 1000,
layer: 'https://gis.whatcomcounty.us/arcgis/rest/services/EnterprisePublishing/WhatcomCo_PropertySales/MapServer/0',
where: 'sale_price>1000', history: true,
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[],
},
josephine: {
// Josephine County OR (Grants Pass) — a RICH, clean-key priced-deed county on par with Jackson:
// the Assessor_Taxlots master layer carries SALE_PRICE(double)+SALE_DATE(epoch-ms)+DEED_TYPE+
// INST_NO(recorder doc)+situs+owner(NAME)+RMV(total real-market value)+YR_BLT+SQ_FT+BEDRMS+
// PROP_CLASS(use) AND explicit Latitude/Longitude fields (no geometry download — like imperial).
// where=SALE_PRICE>1000 → 30,140 priced sales of 41,991 total parcels. ACCOUNT is the parcel key.
// Some rural situs come as "* SPEAKER RD" (asterisk = missing house #) → leading "* " stripped.
key: 'josephine', label: 'Josephine County OR (Grants Pass)', cursorKey: 'josephine', geometry: false, orderBy: 'OBJECTID', pageSize: 2000,
layer: 'https://gis.co.josephine.or.us/arcgis/rest/services/Assessor/Assessor_Taxlots/FeatureServer/0',
where: 'SALE_PRICE>1000',
// ACCOUNT MUST be first — engine derives the per-parcel GIS deep-link as where=<first>='<apn>'.
outFields: 'ACCOUNT,SITUS,SITUS_CITY,SITUS_ZIP,SALE_PRICE,SALE_DATE,DEED_TYPE,INST_NO,NAME,RMV,YR_BLT,SQ_FT,BEDRMS,PROP_CLASS,Latitude,Longitude',
fieldMap: { source_id: 'ACCOUNT', address: 'SITUS', city: 'SITUS_CITY', zip: 'SITUS_ZIP', use_desc: 'PROP_CLASS',
total_value: 'RMV', year_built: 'YR_BLT', sqft: 'SQ_FT', beds: 'BEDRMS', owner_name: 'NAME',
doc_type: 'DEED_TYPE', doc_number: 'INST_NO', last_sale_price: 'SALE_PRICE', last_sale_date: 'SALE_DATE',
lat: 'Latitude', lng: 'Longitude' },
toRecords: (fs) => fs.map(f => { const a = f.attributes; const apn = s(a.ACCOUNT); if (!apn) return null;
let addr = clean(a.SITUS); if (addr) addr = addr.replace(/^\*\s*/, '') || null;
return { fips: '41033', apn, price: anyPrice(a.SALE_PRICE), saleDate: anyDate(a.SALE_DATE),
address: addr, city: clean(a.SITUS_CITY), zip: s(a.SITUS_ZIP), use: clean(a.PROP_CLASS),
year: num(a.YR_BLT), sqft: num(a.SQ_FT), beds: num(a.BEDRMS), totalValue: num(a.RMV),
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);
// curl fallback for servers whose responses undici (global fetch) rejects. Some
// county ArcGIS/IIS hosts (e.g. spatial.jacksoncountyor.gov) emit a non-RFC
// response header that fetch refuses ("Invalid header token / does not match the
// HTTP/1.1 protocol"); Node's http parser rejects it even with insecureHTTPParser,
// but curl tolerates it. The URL is passed as a single argv (no shell) so the
// where-clause chars can't inject. --compressed handles gzip.
async function curlGet(url: string, timeoutMs: number): Promise<string> {
const { stdout } = await execFileP('curl', ['-s', '--compressed', '--max-time', String(Math.ceil(timeoutMs / 1000)), url],
{ maxBuffer: 64 * 1024 * 1024 });
return stdout;
}
// fetch() first; only on the undici HTTP-compliance error, retry via curl.
async function fetchJson(u: URL, timeoutMs: number): Promise<any> {
try {
const res = await fetch(u, { signal: AbortSignal.timeout(timeoutMs) });
if (!res.ok) throw new Error(`${res.status}: ${(await res.text()).slice(0, 140)}`);
return await res.json();
} catch (e: any) {
const msg = String(e?.cause?.message || e?.message || '');
if (/Invalid header token|does not match the HTTP/i.test(msg)) return JSON.parse(await curlGet(u.toString(), timeoutMs));
throw e;
}
}
async function fetchPage(src: Source, offset: number): Promise<any[]> {
const u = new URL(src.layer + '/query');
u.searchParams.set('where', src.where);
u.searchParams.set('outFields', src.outFields);
u.searchParams.set('orderByFields', src.orderBy + ' ASC');
u.searchParams.set('resultOffset', String(offset));
u.searchParams.set('resultRecordCount', String(src.pageSize || PAGE));
u.searchParams.set('returnGeometry', src.geometry ? 'true' : 'false');
if (src.geometry) u.searchParams.set('outSR', '4326');
if (src.centroid) { u.searchParams.set('returnCentroid', 'true'); u.searchParams.set('outSR', '4326'); } // lightweight lat/lng via f.centroid, no polygon download
u.searchParams.set('f', 'json');
// some county servers (Spokane MapServer) are slow — retry transient timeouts.
let lastErr: any;
for (let attempt = 0; attempt < 3; attempt++) {
try {
const j: any = await fetchJson(u, 90_000);
if (j.error) throw new Error(`${src.key} error: ${JSON.stringify(j.error).slice(0, 140)}`);
return j.features || [];
} catch (e: any) { lastErr = e; if (attempt < 2) await new Promise(r => setTimeout(r, 2000 * (attempt + 1))); }
}
throw lastErr;
}
async function countTotal(src: Source): Promise<number> {
const u = new URL(src.layer + '/query');
u.searchParams.set('where', src.where); u.searchParams.set('returnCountOnly', 'true'); u.searchParams.set('f', 'json');
const j: any = await fetchJson(u, 30_000);
return Number(j.count || 0);
}
export function ingestArcgisSales(key: string) {
const src = SOURCES[key];
if (!src) throw new Error(`unknown sales source '${key}' — have: ${Object.keys(SOURCES).join(', ')}`);
return async (): Promise<{ upserted: number }> => {
const runId = await openRun(src.cursorKey, src.layer);
// TK-50: run start time — the fetched_at stamped on every row this run touches.
const fetchedAt = new Date().toISOString();
try {
// TK-50: declare this source's our_field → source_attr mapping up front, so
// "where did parcel field X come from?" is answerable from source_field_map
// without re-reading toRecords().
await registerSourceFieldMap(src.key, src.fieldMap, src.label);
const total = await countTotal(src);
const cur = await query<{ next_offset: number }>(`SELECT next_offset FROM ingest_cursor WHERE source=$1`, [src.cursorKey]);
let offset = cur.rows[0]?.next_offset ?? 0;
if (offset >= total) offset = 0;
console.log(`[${src.key}] ${src.label} total=${total}, resume offset=${offset}`);
const rows: ParcelUpsertRow[] = []; const events: Rec[] = []; const seenFips = new Set<string>();
let fetched = 0;
while (fetched < MAX_PER_RUN) {
const feats = await fetchPage(src, offset);
if (!feats.length) break;
for (const r of src.toRecords(feats)) {
if (!r.apn) continue;
r.saleDate = sanitizeEventDate(r.saleDate); // drop future/absurd bad-entry dates (shared guard)
seenFips.add(r.fips);
const gisUrl = `${src.layer}/query?where=${encodeURIComponent(src.outFields.split(',')[0] + "='" + r.apn + "'")}&outFields=*&f=html`;
rows.push({
county_fips: r.fips, source_id: r.apn,
address: r.address ?? null, norm_address: r.address ? normAddress(r.address) : null,
city: r.city ?? null, zip: r.zip ?? null, lat: r.lat ?? null, lng: r.lng ?? null,
year_built: r.year ?? null, sqft: r.sqft ?? null, beds: r.beds ?? null, baths: r.baths ?? null,
units: null, use_desc: r.use ?? null, land_value: null, improvement_value: null, total_value: r.totalValue ?? null,
tax_year: null, owner_name: r.grantee && r.grantee !== 'recorded' ? r.grantee : null, zoning: null,
last_sale_date: r.saleDate ?? null, last_sale_price: r.price ?? null,
extra: JSON.stringify({ source: src.key, doc: r.docNum, doc_type: r.docType }),
// ── TK-50 field-level provenance (record-level: one feature → this row) ──
sourceKey: src.key,
sourceUrl: gisUrl, // deep-links to the exact source record (layer + where APN='…')
fetchedAt, // run start time
rawSource: r.raw ? JSON.stringify(r.raw) : null, // the raw source attributes we mapped from
});
if (r.price && r.saleDate) events.push({ ...r, address: gisUrl }); // reuse address slot to carry the link
}
fetched += feats.length; offset += feats.length;
if (feats.length < (src.pageSize || PAGE)) break;
}
// A parcel can have MANY sales in one batch (Alameda) — the parcel row must be
// unique per (fips,source_id) or ON CONFLICT DO UPDATE errors "cannot affect row
// a second time". Keep the most-recent-sale row per parcel; every sale still lands
// in parcel_event (its UNIQUE key includes doc_number).
const byParcel = new Map<string, ParcelUpsertRow>();
for (const r of rows) {
const k = r.county_fips + '|' + r.source_id; const prev = byParcel.get(k);
if (!prev || (r.last_sale_date || '') > (prev.last_sale_date || '')) byParcel.set(k, r);
}
const dedupRows = [...byParcel.values()];
// History layers (e.g. Whatcom 2020/2021) use date-guarded upsert: never overwrite a newer sale.
const up = await (src.history ? upsertParcelHistory(dedupRows) : upsertParcels(dedupRows));
// sale events with price + parties + public-record link
for (let i = 0; i < events.length; i += 500) {
const chunk = events.slice(i, i + 500);
await query(
`INSERT INTO parcel_event (county_fips, source_id, event_type, event_date, amount, doc_type, doc_number, source, source_url, detail)
VALUES ${chunk.map((_, j) => { const b = j * 9; return `($${b+1},$${b+2},'sale',$${b+3}::date,$${b+4},$${b+5},$${b+6},$${b+7},$${b+8},$${b+9}::jsonb)`; }).join(',')}
ON CONFLICT (county_fips, source_id, event_type, event_date, doc_number) DO NOTHING`,
chunk.flatMap(e => [e.fips, e.apn, e.saleDate, e.price, e.docType ?? null, e.docNum ?? '', src.cursorKey,
e.address /* gisUrl */, JSON.stringify({ grantor: e.grantor ?? null, grantee: e.grantee ?? null })]),
);
}
// per-parcel GIS deep link (deduped — one link per parcel)
for (let i = 0; i < dedupRows.length; i += 400) {
const chunk = dedupRows.slice(i, i + 400);
await query(
`INSERT INTO parcel_links (county_fips, source_id, kind, url, label) VALUES ${
chunk.map((_, j) => { const b = j * 5; return `($${b+1},$${b+2},$${b+3},$${b+4},$${b+5})`; }).join(',')
} ON CONFLICT (county_fips, source_id, kind) DO UPDATE SET url=EXCLUDED.url, label=EXCLUDED.label`,
chunk.flatMap(r => [r.county_fips, r.source_id, 'gis',
`${src.layer}/query?where=${encodeURIComponent(src.outFields.split(',')[0] + "='" + r.source_id + "'")}&outFields=*&f=html`,
`${src.label} record`]),
);
}
for (const fips of seenFips) await registerParcelSource(fips, src.layer, `${src.label} — free priced deeds (ArcGIS REST)`);
const nextOffset = offset >= total ? 0 : offset;
await query(`INSERT INTO ingest_cursor (source, next_offset, total) VALUES ($1,$2,$3)
ON CONFLICT (source) DO UPDATE SET next_offset=$2, total=$3, updated_at=NOW()`, [src.cursorKey, nextOffset, total]);
await closeRun(runId, 'ok', { upserted: up, notes: `${src.label}: ${up} parcels, ${events.length} sales (offset ${offset}/${total})` });
console.log(`[${src.key}] done: ${up} parcels, ${events.length} priced sales`);
return { upserted: up };
} catch (e: any) {
await closeRun(runId, 'failed', { notes: e.message });
throw e;
}
};
}