wake: contrarian gate fixes — all-zero deed sentinel + pre-1971 epoch date

d39bdf333c0778bbc7797d60c8d024bf56acc275 · 2026-07-26 14:38:11 -0700 · Steve Abrams

(1) '000000-00000' null-deed sentinel (govt/ROW parcels, no deed on record) was bulk-flagging 429 parcels + creating 37 junk parcel_event doc_numbers — the in-memory guard caught '-' but not the all-zero form (same class as Miami's placeholder). Both deed guards now use ^[-0]*$ (memory + SQL + event doc_number). (2) epochToISO now rejects sub-1971 near-epoch artifacts. Verified: sentinel 0, junk events 0, bulk 62147->61718. Ticket TK-5.

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

Files touched

M src/ingest/parcels/wake_nc.ts

Diff

commit d39bdf333c0778bbc7797d60c8d024bf56acc275
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 26 14:38:11 2026 -0700

    wake: contrarian gate fixes — all-zero deed sentinel + pre-1971 epoch date
    
    (1) '000000-00000' null-deed sentinel (govt/ROW parcels, no deed on record) was bulk-flagging 429 parcels + creating 37 junk parcel_event doc_numbers — the in-memory guard caught '-' but not the all-zero form (same class as Miami's placeholder). Both deed guards now use ^[-0]*$ (memory + SQL + event doc_number). (2) epochToISO now rejects sub-1971 near-epoch artifacts. Verified: sentinel 0, junk events 0, bulk 62147->61718. Ticket TK-5.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 src/ingest/parcels/wake_nc.ts | 18 ++++++++++++------
 1 file changed, 12 insertions(+), 6 deletions(-)

diff --git a/src/ingest/parcels/wake_nc.ts b/src/ingest/parcels/wake_nc.ts
index 8b7511e..efbbdfd 100644
--- a/src/ingest/parcels/wake_nc.ts
+++ b/src/ingest/parcels/wake_nc.ts
@@ -29,8 +29,11 @@ const PAGE = 2000;
 
 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; return new Date(x).toISOString().slice(0, 10); };
-/** area-weighted-ish centroid of the first ring (cheap; good enough for a map pin) */
+// reject <=0 AND sub-1971 (31_536_000_000ms) — Wake's oldest real digital deed is
+// 1971; anything earlier is a mainframe near-epoch precision artifact, not a sale.
+const epochToISO = (v: unknown): string | null => { const x = Number(v); if (!Number.isFinite(x) || x < 31_536_000_000) return null; return new Date(x).toISOString().slice(0, 10); };
+/** plain vertex-average of ring[0] — NOT a true polygon centroid (fine for a map
+ *  pin; multipart parcels only get the first ring). */
 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; }
@@ -95,7 +98,9 @@ export async function ingestWake(): Promise<{ upserted: number }> {
           last_sale_date: saleDate, last_sale_price: salePrice,
           extra: JSON.stringify({
             pin: s(a.PIN_NUM) || undefined, design_style: s(a.DESIGN_STYLE_DECODE) || undefined,
-            latest_deed: deed !== '-' ? deed : undefined,
+            // exclude all-zero/blank deed sentinels ('-', '000000-00000') — the county
+          // writes those for govt/right-of-way parcels with no deed on record.
+          latest_deed: /^[-0]*$/.test(deed) ? undefined : deed,
             note: 'Wake County NC Assessor — assessed value; no beds/baths or zoning in this layer',
           }),
         });
@@ -108,8 +113,9 @@ export async function ingestWake(): Promise<{ upserted: number }> {
           `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 => [FIPS, e.reid, e.date, e.price, 'Deed', e.deed && e.deed !== '-' ? e.deed : e.date,
-            'parcel_wake_nc', `https://services.wake.gov/realestate/Account.asp?id=${e.reid}`, JSON.stringify({ deed: e.deed })]),
+          chunk.flatMap(e => { const realDeed = e.deed && !/^[-0]*$/.test(e.deed) ? e.deed : null;
+            return [FIPS, e.reid, e.date, e.price, 'Deed', realDeed ?? e.date,
+              'parcel_wake_nc', `https://services.wake.gov/realestate/Account.asp?id=${e.reid}`, JSON.stringify({ deed: realDeed })]; }),
         );
         sales += chunk.length;
       }
@@ -123,7 +129,7 @@ export async function ingestWake(): Promise<{ upserted: number }> {
     const b = await query(
       `WITH bulk AS (
          SELECT extra->>'latest_deed' AS deed, COUNT(*)::int AS cnt
-           FROM parcel WHERE county_fips=$1 AND extra->>'latest_deed' IS NOT NULL AND extra->>'latest_deed' !~ '^-?$'
+           FROM parcel WHERE county_fips=$1 AND extra->>'latest_deed' IS NOT NULL AND extra->>'latest_deed' !~ '^[-0]*$'
           GROUP BY 1 HAVING COUNT(*) > 1)
        UPDATE parcel p SET last_sale_price = NULL,
          extra = p.extra || jsonb_build_object('bulk_sale', true, 'bulk_deed_folio_count', bulk.cnt)

← 8699785 parcels: add Wake County NC (37183) — 437k parcels, richest · back to Nationalrealestate · Add King County WA (2.4M full-history deeds) to loop on week 467ca96 →