← back to Nationalrealestate
parcels: guard negative sale prices — shared sanitizeSalePrice() across king_wa + arcgis
994fedb565e3fc38c455306db48811dc44f01780 · 2026-07-28 06:58:07 -0700 · Steve Abrams
King County WA's EXTR_RPSale.csv carries occasional negative saleprice values
(source data-entry artifacts, -400..-100) that king_wa's num() let through
(it rejected only 0), landing 72 impossible negative prices in
parcel.last_sale_price. New shared src/ingest/parcels/price_guard.ts
sanitizeSalePrice() rejects negative/zero/non-finite amounts (strips currency,
NO upper cap — real >$1B tower sales are legit), wired into king_wa and
arcgis anyPrice (also fixes a latent bug where anyPrice stripped the minus
sign, turning -400 into 400). 18-case self-test passes; tsc clean.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M src/ingest/parcels/arcgis_sales.tsM src/ingest/parcels/king_wa.tsA src/ingest/parcels/price_guard.ts
Diff
commit 994fedb565e3fc38c455306db48811dc44f01780
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 28 06:58:07 2026 -0700
parcels: guard negative sale prices — shared sanitizeSalePrice() across king_wa + arcgis
King County WA's EXTR_RPSale.csv carries occasional negative saleprice values
(source data-entry artifacts, -400..-100) that king_wa's num() let through
(it rejected only 0), landing 72 impossible negative prices in
parcel.last_sale_price. New shared src/ingest/parcels/price_guard.ts
sanitizeSalePrice() rejects negative/zero/non-finite amounts (strips currency,
NO upper cap — real >$1B tower sales are legit), wired into king_wa and
arcgis anyPrice (also fixes a latent bug where anyPrice stripped the minus
sign, turning -400 into 400). 18-case self-test passes; tsc clean.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
src/ingest/parcels/arcgis_sales.ts | 15 +++---
src/ingest/parcels/king_wa.ts | 6 ++-
src/ingest/parcels/price_guard.ts | 95 ++++++++++++++++++++++++++++++++++++++
3 files changed, 109 insertions(+), 7 deletions(-)
diff --git a/src/ingest/parcels/arcgis_sales.ts b/src/ingest/parcels/arcgis_sales.ts
index 1019f9f..4c144d3 100644
--- a/src/ingest/parcels/arcgis_sales.ts
+++ b/src/ingest/parcels/arcgis_sales.ts
@@ -18,6 +18,7 @@ import { query } from '../../../db/pool.ts';
import { openRun, closeRun } from '../run.ts';
import { upsertParcels, normAddress, registerParcelSource, 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);
@@ -47,8 +48,11 @@ function centroid(geom: any): [number | null, number | null] {
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.'); pull the digits.
-const anyPrice = (v: unknown): number | null => { if (v == null) return null; const x = Number(String(v).replace(/[^\d.]/g, '')); return Number.isFinite(x) && x > 0 ? x : 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;
@@ -71,7 +75,7 @@ const SOURCES: Record<string, Source> = {
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: num(a.SALEPRICE), saleDate: yyyymm(a.SALEDATE) } as Rec; }).filter(Boolean) as Rec[],
+ price: anyPrice(a.SALEPRICE), saleDate: yyyymm(a.SALEDATE) } as Rec; }).filter(Boolean) as Rec[],
},
spokane: {
key: 'spokane', label: 'Spokane County WA', cursorKey: 'spokane', geometry: false,
@@ -80,7 +84,7 @@ const SOURCES: Record<string, Source> = {
outFields: 'PID_NUM,gross_sale_price,document_date,excise_nbr,site_address,owner_name,transfer_type',
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: num(a.gross_sale_price), saleDate: epochToISO(a.document_date), docNum: s(a.excise_nbr),
+ 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) } as Rec; }).filter(Boolean) as Rec[],
},
alameda: {
@@ -94,8 +98,7 @@ const SOURCES: Record<string, Source> = {
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 priceRaw = s(a.value_from_trans_tax); // zero-padded dollars e.g. '000001060000.'
- const price = priceRaw ? num(priceRaw.replace(/[^\d]/g, '')) : null;
+ 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),
diff --git a/src/ingest/parcels/king_wa.ts b/src/ingest/parcels/king_wa.ts
index 4cbe76d..5c0c4cc 100644
--- a/src/ingest/parcels/king_wa.ts
+++ b/src/ingest/parcels/king_wa.ts
@@ -21,6 +21,7 @@ import { openRun, closeRun, DOWNLOADS, download } from '../run.ts';
import { csvObjects } from './csv.ts';
import { upsertParcels, registerParcelSource, normAddress, type ParcelUpsertRow } from './upsert.ts';
import { sanitizeEventDate } from './date_guard.ts';
+import { sanitizeSalePrice } from './price_guard.ts';
const FIPS = '53033';
const BASE = 'https://aqua.kingcounty.gov/extranet/assessor/';
@@ -118,7 +119,10 @@ export async function ingestKing(): Promise<{ upserted: number }> {
const prev = lastDeed.get(pin);
if (!prev || date > prev.date) lastDeed.set(pin, { date, buyer });
}
- const price = num(o.saleprice);
+ // Route through the shared price guard: King's EXTR_RPSale.csv carries
+ // occasional NEGATIVE saleprice values (source data-entry artifacts) that
+ // the old `num()` (rejected only 0) let through into last_sale_price.
+ const price = sanitizeSalePrice(o.saleprice);
if (!price) continue;
const s: Sale = { date, price, buyer, seller: o.sellername.trim(), instrument: o.saleinstrument.trim() };
const list = sales.get(pin);
diff --git a/src/ingest/parcels/price_guard.ts b/src/ingest/parcels/price_guard.ts
new file mode 100644
index 0000000..18ee56c
--- /dev/null
+++ b/src/ingest/parcels/price_guard.ts
@@ -0,0 +1,95 @@
+// Shared sale-price sanity guard for every parcel-deed ingester.
+//
+// Why this exists: county sale feeds occasionally carry a corrupt sale amount —
+// most commonly a NEGATIVE price. King County WA's EXTR_RPSale.csv, for example,
+// contains rows whose `saleprice` is a small negative value (e.g. -400, -300,
+// -200) — a source-side data-entry / adjustment-code artifact, NOT a real sale.
+// A recorded sale price can never be negative. Before this util, king_wa.ts
+// parsed price with a bare `num()` that only rejected 0 (`n !== 0`), so those
+// negatives passed straight into parcel.last_sale_price (72 such rows were found
+// in the local mirror, all King County). arcgis_sales.anyPrice already required
+// `> 0` and nyc_acris required `>= MIN_AMT`, so this centralizes ONE tested rule
+// so every price-emit point rejects the same defect.
+//
+// IMPORTANT — what this does NOT do: it does not treat low-but-positive prices
+// as bad. Nominal-consideration deeds ($1 / $10 quitclaims, gift/family
+// transfers) are legitimate and abundant in county records (10k+ rows under $100
+// in the mirror), and there is no defensible universal upper bound either — real
+// Manhattan tower sales legitimately exceed $1B (245 Park Ave @ $1.77B). So the
+// only value this guard rejects is a negative (or non-finite) price; a 0 is
+// dropped to null to match the existing `num()`/`anyPrice()` "0 carries no
+// signal" semantics.
+//
+// Contract: input is a raw string/number/nullish sale amount. Output is a finite
+// POSITIVE number, or null. It never throws and never mutates its input.
+
+/**
+ * Parse and sanity-check a raw sale amount.
+ *
+ * Returns the amount as a finite number > 0, or null when the input is missing,
+ * non-numeric, zero, or negative. Strips currency formatting ($, commas) so
+ * "$1,250,000.00" parses; a bare number passes through unchanged.
+ *
+ * @param raw a sale price as string, number, null, or undefined
+ */
+export function sanitizeSalePrice(raw: string | number | null | undefined): number | null {
+ if (raw == null) return null;
+ // Strip currency formatting from strings ("$1,250,000.00", " 000001060000. ");
+ // keep digits, one decimal point, and a leading minus so negatives are still
+ // detectable (and then rejected below).
+ const cleaned = typeof raw === 'number' ? raw : Number(String(raw).replace(/[^\d.-]/g, ''));
+ const n = Number(cleaned);
+ if (!Number.isFinite(n)) return null;
+ if (n <= 0) return null; // reject negative AND zero (0 carries no sale signal)
+ return n;
+}
+
+// --- self-test (run: `tsx src/ingest/parcels/price_guard.ts`) ----------------
+// No test framework is installed in this repo, so the util ships its own
+// deterministic self-test that exits non-zero on any failure. Runs only when
+// executed directly, never on import.
+const isMain = (() => {
+ try {
+ return typeof process !== 'undefined'
+ && process.argv[1] != null
+ && import.meta.url === new URL(`file://${process.argv[1]}`).href;
+ } catch { return false; }
+})();
+
+if (isMain) {
+ const cases: Array<[string | number | null | undefined, number | null, string]> = [
+ // [input, expected, label]
+ [250000, 250000, 'ordinary positive number'],
+ ['250000', 250000, 'positive numeric string'],
+ ['$1,250,000.00', 1250000, 'currency-formatted string'],
+ [' 000001060000. ', 1060000, 'zero-padded arcgis-style string w/ trailing dot'],
+ [1, 1, '$1 nominal consideration kept (real deed class)'],
+ [10, 10, '$10 quitclaim kept'],
+ [1769900601.25, 1769900601.25, 'legit >$1B tower sale kept (no upper cap)'],
+ [-400, null, 'negative rejected (the King County defect)'],
+ ['-400', null, 'negative string rejected'],
+ [-1, null, 'small negative rejected'],
+ [0, null, '0 dropped to null'],
+ ['0', null, '"0" dropped to null'],
+ ['', null, 'empty string -> null'],
+ ['abc', null, 'non-numeric -> null'],
+ [null, null, 'null -> null'],
+ [undefined, null, 'undefined -> null'],
+ [NaN, null, 'NaN -> null'],
+ [Infinity, null, 'Infinity -> null'],
+ ];
+ let failed = 0;
+ for (const [input, expected, label] of cases) {
+ const got = sanitizeSalePrice(input);
+ const ok = got === expected;
+ if (!ok) {
+ failed++;
+ console.error(`FAIL: ${label}\n input=${JSON.stringify(input)} expected=${JSON.stringify(expected)} got=${JSON.stringify(got)}`);
+ }
+ }
+ if (failed) {
+ console.error(`\n${failed}/${cases.length} sanitizeSalePrice cases FAILED`);
+ process.exit(1);
+ }
+ console.log(`sanitizeSalePrice: all ${cases.length} cases passed`);
+}
← 1e35d6a parcels: add Yakima County WA (53077) free priced-deed feed
·
back to Nationalrealestate
·
parcels: add Fulton County GA / Atlanta (13121) — dedicated f01d750 →