miami: contrarian gate fixes — placeholder-deed bulk false-positive + ghost sale events

d409f98adac14a4737d200a7f7406e4506d2613f · 2026-07-26 10:09:35 -0700 · Steve Abrams

(1) The PA's all-zero '00000-00000' 'no deed on record' placeholder was grouping 12,285 un-recorded parcels into a fake assemblage and bulk-flagging them. Now excluded from latest_deed + bulk SQL (regex ^0+-0+$). Bulk flags 62,745->50,447 (all now real multi-folio deeds). (2) $0 non-arms-length transfers set last_sale_date but were filtered out of parcel_event -> 70,594 parcels with a last_sale_date pointing to a missing event. Now every dated transfer gets an event with doc_type Qualified/Unqualified/Non-Arms-Length (+297,491 events). Ghost rows 70,594->0. (3) Deterministic same-date sort tiebreak by slot. Verified.

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

Files touched

M src/ingest/parcels/miami_dade.ts

Diff

commit d409f98adac14a4737d200a7f7406e4506d2613f
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 26 10:09:35 2026 -0700

    miami: contrarian gate fixes — placeholder-deed bulk false-positive + ghost sale events
    
    (1) The PA's all-zero '00000-00000' 'no deed on record' placeholder was grouping 12,285 un-recorded parcels into a fake assemblage and bulk-flagging them. Now excluded from latest_deed + bulk SQL (regex ^0+-0+$). Bulk flags 62,745->50,447 (all now real multi-folio deeds). (2) $0 non-arms-length transfers set last_sale_date but were filtered out of parcel_event -> 70,594 parcels with a last_sale_date pointing to a missing event. Now every dated transfer gets an event with doc_type Qualified/Unqualified/Non-Arms-Length (+297,491 events). Ghost rows 70,594->0. (3) Deterministic same-date sort tiebreak by slot. Verified.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 src/ingest/parcels/miami_dade.ts | 15 +++++++++++----
 1 file changed, 11 insertions(+), 4 deletions(-)

diff --git a/src/ingest/parcels/miami_dade.ts b/src/ingest/parcels/miami_dade.ts
index 4de2a63..5463565 100644
--- a/src/ingest/parcels/miami_dade.ts
+++ b/src/ingest/parcels/miami_dade.ts
@@ -83,7 +83,8 @@ function salesOf(a: any): Sale[] {
       qualified: String(a['QU_FLG_' + n] ?? '').trim().toUpperCase() === 'Q',
     });
   }
-  return out.sort((x, y) => (y.date).localeCompare(x.date)); // newest first
+  // newest first; deterministic tiebreak by slot (_1 is the county's newest) on date ties
+  return out.sort((x, y) => y.date.localeCompare(x.date) || (x.n - y.n));
 }
 
 export async function ingestMiami(): Promise<{ upserted: number }> {
@@ -117,12 +118,17 @@ export async function ingestMiami(): Promise<{ upserted: number }> {
           last_sale_date: latest?.date || null, last_sale_price: latest?.price ?? null,
           extra: JSON.stringify({
             owner2: s(a.TRUE_OWNER2) || undefined, dor_code: s(a.DOR_CODE_CUR) || undefined,
-            latest_deed: latest?.deed && latest.deed !== '-' ? latest.deed : undefined,
+            // exclude the PA's all-zero "no deed on record" placeholder so it never
+            // groups thousands of un-recorded parcels into a fake assemblage.
+            latest_deed: latest?.deed && latest.deed !== '-' && !/^0+-0+$/.test(latest.deed) ? latest.deed : undefined,
             latest_sale_qualified: latest ? latest.qualified : undefined,
             note: 'no assessed value / beds / baths / sqft / year-built in the public PaGis layer',
           }),
         });
-        for (const sale of sales) if (sale.price && sale.date) events.push({ folio, ...sale });
+        // every dated transfer becomes an event (incl. $0 non-arms-length) so the
+        // parcel's last_sale_date never points to a missing event row; price/doc_type
+        // distinguishes them. Only truly undated rows are skipped.
+        for (const sale of sales) if (sale.date) events.push({ folio, ...sale });
       }
       upserted += await upsertParcels(batch);
       for (let i = 0; i < events.length; i += 500) {
@@ -131,7 +137,7 @@ export async function ingestMiami(): 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.folio, e.date, e.price, e.qualified ? 'Qualified' : 'Unqualified', e.deed && e.deed !== '-' ? e.deed : `${e.date}-${e.n}`,
+          chunk.flatMap(e => [FIPS, e.folio, e.date, e.price, e.qualified ? 'Qualified' : (e.price ? 'Unqualified' : 'Non-Arms-Length'), e.deed && e.deed !== '-' ? e.deed : `${e.date}-${e.n}`,
             'parcel_miami_dade', `https://www.miamidade.gov/Apps/PA/propertysearch/#/?folio=${e.folio}`,
             JSON.stringify({ grantor: e.grantor, grantee: e.grantee, qualified: e.qualified })]),
         );
@@ -149,6 +155,7 @@ export async function ingestMiami(): Promise<{ upserted: number }> {
       `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' !~ '^0+-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)

← 7dfbf4f parcels: add Miami-Dade FL (12086) via keyless ArcGIS PaGis · back to Nationalrealestate · brokers: add CT broker-side REB roster (fwpc-pgqj) — 16.8k b ffad429 →