← back to Nationalrealestate
parcels: guard null/placeholder/junk owner names — shared sanitizeOwnerName() at upsert chokepoint
1c33393f0e18ea5f04f625b41edde08f7edb2b72 · 2026-07-28 07:38:14 -0700 · Steve Abrams
New src/ingest/parcels/owner_guard.ts drops owner values that are not real
names: exact placeholder tokens (CURRENT OWNER, UNKNOWN, OWNER OF RECORD,
TAXPAYER, N/A, NULL, ...) and non-name junk with no letter at all (leaked
Cook-IL PINs like 09364120170000, address fragments '189-191', bare years).
Wired centrally into upsertParcels() (covers all 11 ingesters that route
through it) + nyc_pluto's own upsert path. Mirrors date_guard/price_guard:
30-case tsx self-test, tsc --noEmit clean.
Over-correction guard: legit 2-char initials (JS/MH/PI) and names containing
digits (3M CO, 7-ELEVEN) are KEPT; only zero-letter values and known
placeholders are dropped. ~1,785 existing corrupt rows quantified in the
local mirror; the one-time backfill of those is drafted to pending-approval
(gated persistent DB write), not run here.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M src/ingest/parcels/nyc_pluto.tsA src/ingest/parcels/owner_guard.tsM src/ingest/parcels/upsert.ts
Diff
commit 1c33393f0e18ea5f04f625b41edde08f7edb2b72
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 28 07:38:14 2026 -0700
parcels: guard null/placeholder/junk owner names — shared sanitizeOwnerName() at upsert chokepoint
New src/ingest/parcels/owner_guard.ts drops owner values that are not real
names: exact placeholder tokens (CURRENT OWNER, UNKNOWN, OWNER OF RECORD,
TAXPAYER, N/A, NULL, ...) and non-name junk with no letter at all (leaked
Cook-IL PINs like 09364120170000, address fragments '189-191', bare years).
Wired centrally into upsertParcels() (covers all 11 ingesters that route
through it) + nyc_pluto's own upsert path. Mirrors date_guard/price_guard:
30-case tsx self-test, tsc --noEmit clean.
Over-correction guard: legit 2-char initials (JS/MH/PI) and names containing
digits (3M CO, 7-ELEVEN) are KEPT; only zero-letter values and known
placeholders are dropped. ~1,785 existing corrupt rows quantified in the
local mirror; the one-time backfill of those is drafted to pending-approval
(gated persistent DB write), not run here.
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
src/ingest/parcels/nyc_pluto.ts | 3 +-
src/ingest/parcels/owner_guard.ts | 147 ++++++++++++++++++++++++++++++++++++++
src/ingest/parcels/upsert.ts | 8 ++-
3 files changed, 156 insertions(+), 2 deletions(-)
diff --git a/src/ingest/parcels/nyc_pluto.ts b/src/ingest/parcels/nyc_pluto.ts
index c00ca2e..8e4e2f8 100644
--- a/src/ingest/parcels/nyc_pluto.ts
+++ b/src/ingest/parcels/nyc_pluto.ts
@@ -9,6 +9,7 @@
*/
import { query, pool } from '../../../db/pool.ts';
import { openRun, closeRun } from '../run.ts';
+import { sanitizeOwnerName } from './owner_guard.ts';
const DATASET = 'https://data.cityofnewyork.us/resource/64uk-42ks.json';
const PAGE = 50000;
@@ -86,7 +87,7 @@ export async function ingestNycPluto(): Promise<{ upserted: number }> {
use_desc: LANDUSE[d.landuse] || (d.bldgclass ? `Class ${d.bldgclass}` : null),
land_value: land, improvement_value: total != null && land != null ? total - land : null,
total_value: total, tax_year: d.version ? String(d.version) : null,
- owner_name: (d.ownername || '').trim() || null,
+ owner_name: sanitizeOwnerName(d.ownername),
zoning: d.zonedist1 || null,
extra: JSON.stringify({ landuse: d.landuse, bldgclass: d.bldgclass, unitsres: d.unitsres,
lotarea: d.lotarea, numfloors: d.numfloors, borough: d.borough }),
diff --git a/src/ingest/parcels/owner_guard.ts b/src/ingest/parcels/owner_guard.ts
new file mode 100644
index 0000000..6f29d18
--- /dev/null
+++ b/src/ingest/parcels/owner_guard.ts
@@ -0,0 +1,147 @@
+// Shared owner-name sanity guard for every parcel ingester.
+//
+// Why this exists: county owner feeds occasionally carry a value in the owner
+// field that is not an owner name at all — it's a placeholder token or a stray
+// identifier that leaked in from an adjacent column. Two defect shapes recur in
+// the local mirror (usre.parcel):
+//
+// 1. PLACEHOLDER TOKENS — the feed fills the owner column with a literal
+// stand-in when the real owner is withheld/unknown: "CURRENT OWNER" (809),
+// "UNKNOWN" (719), "OWNER OF RECORD" (32), "OWNER" (28), "CURRENT RESIDENT"
+// (25), "TAXPAYER" (16), plus "NA"/"NONE"/"N/A"/"NULL"/"PRIVATE"/"REDACTED".
+// ~1,641 such rows, spread across cook_il (17031), deschutes (41017),
+// wake_nc (37183), and NYC boroughs.
+//
+// 2. NON-NAME JUNK — a numeric/punctuation-only value with NO letter at all:
+// Cook County PINs like "09364120170000", address fragments like "189-191",
+// or a bare year "1960" that landed in the owner field. ~144 such rows. A
+// real person or entity name always contains at least one letter, so a
+// value with zero letters is never a valid owner.
+//
+// Before this util, ingesters emitted owner_name with a variety of one-off
+// helpers (`s()`, `clean()`, `.trim() || null`) that pass these straight
+// through. This centralizes ONE tested rule, applied at the shared upsert
+// chokepoint, so every owner-emit point drops the same defect to null.
+//
+// IMPORTANT — what this does NOT do (over-correction guard):
+// * It does NOT reject short names. Two-character owner values (JS, MH, DM,
+// "PI" ×126) are legitimate initials / short entity names and are KEPT —
+// the earlier "len <= 2 is junk" hypothesis was rejected after inspecting
+// the data (those are real owners, not defects).
+// * It does NOT reject a name merely because it contains digits — "3M CO",
+// "7-ELEVEN INC", "1ST NATIONAL BANK", "JOHN SMITH 2ND" are all real; the
+// rule is "has at least one LETTER", not "has no digits".
+// * A NULL/empty owner is left as null (already-absent data, not a defect to
+// act on — many feeds legitimately withhold owner for privacy).
+//
+// Contract: input is a raw string/nullish owner value. Output is a cleaned,
+// trimmed, whitespace-collapsed owner string, or null when the input is missing,
+// empty, a known placeholder token, or contains no letters. It never throws and
+// never mutates its input.
+
+// Placeholder tokens that mean "no real owner given" — compared case-insensitively
+// against the trimmed, whitespace-collapsed value.
+const PLACEHOLDER_TOKENS = new Set<string>([
+ 'OWNER',
+ 'CURRENT OWNER',
+ 'CURRENT RESIDENT',
+ 'OWNER OF RECORD',
+ 'TAXPAYER',
+ 'PRIVATE',
+ 'REDACTED',
+ 'UNKNOWN',
+ 'NONE',
+ 'NULL',
+ 'N/A',
+ 'NA',
+ 'TBD',
+ 'NOT AVAILABLE',
+ 'NOT ON FILE',
+ 'NOT PROVIDED',
+ 'SEE OWNER',
+]);
+
+/**
+ * Parse and sanity-check a raw owner name.
+ *
+ * Returns a cleaned owner string, or null when the input is missing, blank, a
+ * known placeholder token (case-insensitive), or contains no letter at all
+ * (numeric/punctuation-only leak). Legitimate short names and names containing
+ * digits are preserved.
+ *
+ * @param raw an owner name as string, null, or undefined
+ */
+export function sanitizeOwnerName(raw: string | null | undefined): string | null {
+ if (raw == null) return null;
+ // Collapse internal whitespace runs and trim so " CURRENT OWNER " matches
+ // the placeholder set and so the stored value is tidy.
+ const cleaned = String(raw).replace(/\s+/g, ' ').trim();
+ if (cleaned === '') return null;
+ if (PLACEHOLDER_TOKENS.has(cleaned.toUpperCase())) return null;
+ // A real owner name has at least one letter (ASCII or Unicode). A value with
+ // zero letters (all digits / punctuation) is a leaked identifier, not a name.
+ if (!/\p{L}/u.test(cleaned)) return null;
+ return cleaned;
+}
+
+// --- self-test (run: `tsx src/ingest/parcels/owner_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 | null | undefined, string | null, string]> = [
+ // [input, expected, label]
+ ['JOHN SMITH', 'JOHN SMITH', 'ordinary owner name kept'],
+ ['Smith, John A', 'Smith, John A', 'name with comma/initial kept'],
+ [' SMITH FAMILY TRUST ', 'SMITH FAMILY TRUST', 'whitespace collapsed + trimmed'],
+ ['JS', 'JS', 'legit 2-char initials kept (NOT over-corrected)'],
+ ['PI', 'PI', 'legit 2-char entity kept (126 rows in mirror)'],
+ ['C', 'C', 'legit single-letter kept'],
+ ['3M CO', '3M CO', 'name starting with a digit kept'],
+ ['7-ELEVEN INC', '7-ELEVEN INC', 'digit+punct+letters kept'],
+ ['1ST NATIONAL BANK', '1ST NATIONAL BANK', 'ordinal-with-letters kept'],
+ ["O'BRIEN & SONS", "O'BRIEN & SONS", 'apostrophe/ampersand name kept'],
+ ['CURRENT OWNER', null, 'placeholder token rejected (809 rows)'],
+ ['current owner', null, 'placeholder token case-insensitive'],
+ ['UNKNOWN', null, 'UNKNOWN placeholder rejected (719 rows)'],
+ ['OWNER', null, 'OWNER placeholder rejected'],
+ ['OWNER OF RECORD', null, 'OWNER OF RECORD rejected'],
+ ['TAXPAYER', null, 'TAXPAYER rejected'],
+ ['N/A', null, 'N/A rejected'],
+ ['NA', null, 'NA rejected'],
+ ['NONE', null, 'NONE rejected'],
+ ['NULL', null, 'literal NULL string rejected'],
+ ['09364120170000', null, 'all-digit Cook PIN leak rejected'],
+ ['189-191', null, 'address fragment (digits+dash, no letter) rejected'],
+ ['1960', null, 'bare year leaked into owner rejected'],
+ ['14 21 101 054 2417', null, 'spaced numeric PIN rejected'],
+ ['.', null, 'lone punctuation rejected'],
+ ['---', null, 'all-dash rejected'],
+ ['', null, 'empty string -> null'],
+ [' ', null, 'whitespace-only -> null'],
+ [null, null, 'null -> null'],
+ [undefined, null, 'undefined -> null'],
+ ];
+ let failed = 0;
+ for (const [input, expected, label] of cases) {
+ const got = sanitizeOwnerName(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} sanitizeOwnerName cases FAILED`);
+ process.exit(1);
+ }
+ console.log(`sanitizeOwnerName: all ${cases.length} cases passed`);
+}
diff --git a/src/ingest/parcels/upsert.ts b/src/ingest/parcels/upsert.ts
index f4cf3ff..3740c7d 100644
--- a/src/ingest/parcels/upsert.ts
+++ b/src/ingest/parcels/upsert.ts
@@ -4,6 +4,7 @@
* 23 cols -> chunks of 2000 rows = 46k params.
*/
import { query } from '../../../db/pool.ts';
+import { sanitizeOwnerName } from './owner_guard.ts';
export interface ParcelUpsertRow {
county_fips: string; source_id: string;
@@ -29,7 +30,12 @@ export async function upsertParcels(rows: ParcelUpsertRow[]): Promise<number> {
const params: unknown[] = [];
const values = chunk.map((r, j) => {
const b = j * COLS.length;
- for (const c of COLS) params.push(r[c] ?? null);
+ // Sanitize owner_name at the shared chokepoint so every ingester routing
+ // through upsertParcels drops placeholder/junk owner values (see
+ // owner_guard.ts) without each emit point having to remember to.
+ for (const c of COLS) {
+ params.push(c === 'owner_name' ? sanitizeOwnerName(r.owner_name) : (r[c] ?? null));
+ }
return '(' + COLS.map((_, k) => `$${b + k + 1}`).join(',') + ')';
});
await query(
← f01d750 parcels: add Fulton County GA / Atlanta (13121) — dedicated
·
back to Nationalrealestate
·
parcels: field-level data provenance — source every parcel f 536b408 →