franklin: contrarian gate fixes — full-timestamp dedupe + bulk-sale price nulling

aa4b31c532bdae4aa1623d7a0f331b573e4189e5 · 2026-07-26 08:57:29 -0700 · Steve Abrams

(1) Same-date sales: date-only dedupe let a $0 grantor-side transfer (recorded at HH:00:00) beat the real arms-length sale one second later (HH:00:01), yielding wrong owner + null price on ~1289 parcels. Now rank by FULL timestamp, price-desc as secondary tiebreak. Verified 010-001903: FOX/null -> WILLIAMS/$206k. (2) Bulk sales (ParcelCount>1) stamped the full portfolio total on each parcel (1100 impossible >$10M, max $120M). Now null the unallocatable price + flag bulk_sale/bulk_parcel_count in extra (24,116 flagged); 72 legit large-commercial singles retained.

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

Files touched

M src/ingest/parcels/franklin_oh.ts

Diff

commit aa4b31c532bdae4aa1623d7a0f331b573e4189e5
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Sun Jul 26 08:57:29 2026 -0700

    franklin: contrarian gate fixes — full-timestamp dedupe + bulk-sale price nulling
    
    (1) Same-date sales: date-only dedupe let a $0 grantor-side transfer (recorded at HH:00:00) beat the real arms-length sale one second later (HH:00:01), yielding wrong owner + null price on ~1289 parcels. Now rank by FULL timestamp, price-desc as secondary tiebreak. Verified 010-001903: FOX/null -> WILLIAMS/$206k. (2) Bulk sales (ParcelCount>1) stamped the full portfolio total on each parcel (1100 impossible >$10M, max $120M). Now null the unallocatable price + flag bulk_sale/bulk_parcel_count in extra (24,116 flagged); 72 legit large-commercial singles retained.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 src/ingest/parcels/franklin_oh.ts | 33 +++++++++++++++++++++++++--------
 1 file changed, 25 insertions(+), 8 deletions(-)

diff --git a/src/ingest/parcels/franklin_oh.ts b/src/ingest/parcels/franklin_oh.ts
index 3a688c2..9cab643 100644
--- a/src/ingest/parcels/franklin_oh.ts
+++ b/src/ingest/parcels/franklin_oh.ts
@@ -49,6 +49,13 @@ const toIsoDate = (v: unknown): string | null => {
   const m = String(v ?? '').match(/(\d{4})[/-](\d{2})[/-](\d{2})/);
   return m ? `${m[1]}-${m[2]}-${m[3]}` : null;
 };
+/** Full sortable timestamp "2021-12-02T00:00:01" — the SUB-DAY seconds matter:
+ *  the county records a same-day $0 grantor-side transfer at HH:00 and the real
+ *  arms-length sale one second later, so date-only dedupe picks the wrong row. */
+const toTimestamp = (v: unknown): string => {
+  const m = String(v ?? '').match(/(\d{4})[/-](\d{2})[/-](\d{2})[ T](\d{2}):(\d{2}):(\d{2})/);
+  return m ? `${m[1]}-${m[2]}-${m[3]}T${m[4]}:${m[5]}:${m[6]}` : (toIsoDate(v) ?? '');
+};
 
 async function ogr2ogrExists(): Promise<boolean> {
   try { await execFileP('ogr2ogr', ['--version']); return true; } catch { return false; }
@@ -97,12 +104,14 @@ export async function ingestFranklin(): Promise<{ upserted: number }> {
     const ndjson = join(work, 'sales.ndjson');
     console.log(`[franklin] ogr2ogr extracting ${LAYER} -> WGS84 NDJSON`);
     await execFileP('ogr2ogr', ['-f', 'GeoJSONSeq', ndjson, gdb, LAYER, '-t_srs', 'EPSG:4326',
-      '-select', 'PARCELID,SITEADDRESS,ZIPCD,CLASSDSCRP,GranteeName1,GrantorName1,SALEDATE,SalePrice,SaleType,RESFLRAREA_AG,RESFLRAREA_BG',
+      '-select', 'PARCELID,SITEADDRESS,ZIPCD,CLASSDSCRP,GranteeName1,GrantorName1,SALEDATE,SalePrice,SaleType,ParcelCount,RESFLRAREA_AG,RESFLRAREA_BG',
     ], { maxBuffer: 1 << 30 });
 
-    // Stream NDJSON, keep the LATEST sale per parcel (current owner = latest grantee)
+    // Stream NDJSON, keep the LATEST sale per parcel. Rank by FULL timestamp
+    // (sub-day seconds disambiguate same-date $0 transfer vs real sale), tie
+    // broken by higher SalePrice so the arms-length sale wins a true tie.
     const best = new Map<string, ParcelUpsertRow>();
-    const bestDate = new Map<string, string>();
+    const bestRank = new Map<string, string>(); // "<timestamp>|<zero-padded price>"
     let seen = 0, minSale = '9999', maxSale = '0000';
     const rl = createInterface({ input: createReadStream(ndjson), crlfDelay: Infinity });
     for await (const line of rl) {
@@ -114,15 +123,21 @@ export async function ingestFranklin(): Promise<{ upserted: number }> {
       seen++;
       const saleDate = toIsoDate(p.SALEDATE);
       if (saleDate) { if (saleDate < minSale) minSale = saleDate; if (saleDate > maxSale) maxSale = saleDate; }
-      const prevDate = bestDate.get(pid);
-      // keep the row with the newest sale date (undated rows never beat a dated one)
-      if (prevDate !== undefined && (saleDate ?? '') <= prevDate) continue;
+      const priceRaw = Number(p.SalePrice) || 0;
+      const rank = `${toTimestamp(p.SALEDATE)}|${String(Math.max(0, Math.min(priceRaw, 1e12))).padStart(13, '0')}`;
+      const prev = bestRank.get(pid);
+      if (prev !== undefined && rank <= prev) continue; // keep the higher-ranked (later, then pricier) sale
       const coords = f.geometry?.coordinates;
       const lng = Array.isArray(coords) ? num(coords[0]) : null;
       const lat = Array.isArray(coords) ? num(coords[1]) : null;
       const addr = (p.SITEADDRESS || '').trim();
       const norm = addr ? normAddress(addr) : null;
       const sqft = num(p.RESFLRAREA_AG);
+      const parcelCount = Number(p.ParcelCount) || 1;
+      const bulk = parcelCount > 1;
+      // bulk sale rows carry the FULL bundle price on every parcel — can't allocate
+      // per-parcel, so null the price and flag it rather than ship an inflated value.
+      const salePrice = bulk ? null : num(p.SalePrice);
       best.set(pid, {
         county_fips: FIPS, source_id: pid,
         address: norm, norm_address: norm,
@@ -133,17 +148,19 @@ export async function ingestFranklin(): Promise<{ upserted: number }> {
         land_value: null, improvement_value: null, total_value: null, tax_year: null,
         owner_name: (p.GranteeName1 || '').trim() || null,
         zoning: null,
-        last_sale_date: saleDate, last_sale_price: num(p.SalePrice),
+        last_sale_date: saleDate, last_sale_price: salePrice,
         extra: JSON.stringify({
           class_desc: (p.CLASSDSCRP || '').trim() || undefined,
           res_sqft_below_grade: num(p.RESFLRAREA_BG) || undefined,
           seller: (p.GrantorName1 || '').trim() || undefined,
           sale_type: (p.SaleType || '').trim() || undefined,
+          bulk_sale: bulk || undefined,
+          bulk_parcel_count: bulk ? parcelCount : undefined,
           owner_source: p.GranteeName1 ? 'grantee of latest recorded sale (see sale_window in coverage), Franklin County Auditor TaxParcelSale' : undefined,
           note: 'owner/sale from the open TaxParcelSale window (see coverage notes); no assessed value / year-built / beds-baths in this layer',
         }),
       });
-      bestDate.set(pid, saleDate ?? '');
+      bestRank.set(pid, rank);
     }
     rl.close();

← 4c2251e parcels: add Franklin County OH (39049) via .gdb + GDAL — 12 · back to Nationalrealestate · auto-save: 2026-07-26T09:42:34 (2 files) — src/ingest/parcel 9305f09 →