NYC ACRIS recorded sales -> PLUTO parcels (188k lots, 5 boroughs, $0 open data)

e3663e8dae68e3bd598abad88afa5152e269e406 · 2026-07-21 19:24:08 -0700 · Steve

Extends King-depth recorded sale prices to NYC, the platform's #1 metro — the
'Sale/deed chain from ACRIS pending' gap on all five boroughs is now filled.

- Keyset-streams ACRIS Master (priced deed-family + RPTT transfer docs) and Legals
  (->BBL), joins in memory, single set-based UPDATE onto existing PLUTO lots.
- Single-lot price attribution: document_amt is per-DOCUMENT, so multi-lot/portfolio
  deeds are dropped (killed the $1.5B-on-a-house artifacts); trophy single-lot sales
  (245 Park $1.77B, 424 5th/Amazon $978M) verified real.
- Broad sale doc_type set (DEED family + RPTT&RET/RPTT) fixed Staten Island 1 -> 35,725.
- Idempotent clear-then-apply (no residue across re-runs); retry-hardened SODA client
  (429/5xx + network-timeout backoff, bounded per-request).
- Result: 188,046 lots with recorded sales to 2026-06, 95% clean sale history.
  Condo-unit deeds + buyer/seller (ACRIS Parties) noted as follow-up.

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

Files touched

M src/ingest/parcels/nyc_acris.ts

Diff

commit e3663e8dae68e3bd598abad88afa5152e269e406
Author: Steve <steve@designerwallcoverings.com>
Date:   Tue Jul 21 19:24:08 2026 -0700

    NYC ACRIS recorded sales -> PLUTO parcels (188k lots, 5 boroughs, $0 open data)
    
    Extends King-depth recorded sale prices to NYC, the platform's #1 metro — the
    'Sale/deed chain from ACRIS pending' gap on all five boroughs is now filled.
    
    - Keyset-streams ACRIS Master (priced deed-family + RPTT transfer docs) and Legals
      (->BBL), joins in memory, single set-based UPDATE onto existing PLUTO lots.
    - Single-lot price attribution: document_amt is per-DOCUMENT, so multi-lot/portfolio
      deeds are dropped (killed the $1.5B-on-a-house artifacts); trophy single-lot sales
      (245 Park $1.77B, 424 5th/Amazon $978M) verified real.
    - Broad sale doc_type set (DEED family + RPTT&RET/RPTT) fixed Staten Island 1 -> 35,725.
    - Idempotent clear-then-apply (no residue across re-runs); retry-hardened SODA client
      (429/5xx + network-timeout backoff, bounded per-request).
    - Result: 188,046 lots with recorded sales to 2026-06, 95% clean sale history.
      Condo-unit deeds + buyer/seller (ACRIS Parties) noted as follow-up.
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 src/ingest/parcels/nyc_acris.ts | 72 ++++++++++++++++++++++++++++-------------
 1 file changed, 50 insertions(+), 22 deletions(-)

diff --git a/src/ingest/parcels/nyc_acris.ts b/src/ingest/parcels/nyc_acris.ts
index 6d64ee6..0416ebd 100644
--- a/src/ingest/parcels/nyc_acris.ts
+++ b/src/ingest/parcels/nyc_acris.ts
@@ -15,10 +15,13 @@
  * The reconstructed BBL (borough + block.pad(5) + lot.pad(4)) matches PLUTO's
  * source_id integer part, so we UPDATE existing rows — never insert.
  *
- * KNOWN COVERAGE LIMIT: ACRIS records condo UNIT sales on lot >= 1001 while PLUTO
- * carries the building's billing BBL, so unit-level condo deeds don't join to a
- * PLUTO lot. v1 matches whole-building / 1-3 family / base-lot deeds; condo-unit
- * deed coverage and buyer/seller names (ACRIS Parties) are a follow-up pass.
+ * KNOWN COVERAGE LIMITS: (1) ACRIS records condo UNIT sales on lot >= 1001 while
+ * PLUTO carries the building's billing BBL, so unit-level condo deeds don't join to a
+ * PLUTO lot. v1 matches whole-building / 1-3 family / base-lot deeds. (2) Active-
+ * development lots (~2.7% of matched) accumulate many unit-sale records against the
+ * PARENT BBL before subdivision, so those show a cluster of same-period sales — real
+ * recorded transfers, but the single "last_sale" headline is the most recent of them.
+ * Condo-unit coverage and buyer/seller names (ACRIS Parties) are a follow-up pass.
  *
  * Run: npm run ingest:parcels -- nyc-acris   (NODE_OPTIONS=--max-old-space-size=4096 recommended)
  */
@@ -43,18 +46,27 @@ const FIPS: Record<string, string> = { '1': '36061', '2': '36005', '3': '36047',
 
 interface Sale { date: string; price: number; doc: string }
 
-/** GET SoQL JSON with polite retry on 429/5xx (no app token -> throttle-aware). */
+/** GET SoQL JSON, retrying 429/5xx AND network/timeout exceptions with backoff
+ *  (unauthenticated public API over long streams -> transient timeouts happen).
+ *  4xx (bad query) fails fast; each request is bounded so it never hangs. */
 async function soda(base: string, params: Record<string, string>): Promise<any[]> {
   const url = base + '?' + new URLSearchParams(params).toString();
-  for (let attempt = 0; ; attempt++) {
-    const res = await fetch(url, { headers: { 'accept': 'application/json' } });
-    if (res.ok) return res.json() as Promise<any[]>;
-    if ((res.status === 429 || res.status >= 500) && attempt < 5) {
-      await new Promise(r => setTimeout(r, 1000 * (attempt + 1)));
-      continue;
+  let lastErr: unknown;
+  for (let attempt = 0; attempt < 8; attempt++) {
+    try {
+      const res = await fetch(url, { headers: { accept: 'application/json' }, signal: AbortSignal.timeout(90_000) });
+      if (res.ok) return res.json() as Promise<any[]>;
+      if (res.status < 500 && res.status !== 429) {
+        throw new Error(`SODA ${res.status} ${res.statusText}: ${url.slice(0, 120)}…`); // non-retryable
+      }
+      lastErr = new Error(`SODA ${res.status} ${res.statusText}`); // 429/5xx -> retry
+    } catch (e: any) {
+      if (typeof e?.message === 'string' && e.message.startsWith('SODA ')) throw e; // fail-fast 4xx
+      lastErr = e; // network / headers-timeout / abort -> retry
     }
-    throw new Error(`SODA ${res.status} ${res.statusText}: ${url.slice(0, 120)}…`);
+    await new Promise(r => setTimeout(r, 1500 * (attempt + 1)));
   }
+  throw lastErr;
 }
 
 /** ACRIS document_date / recorded_datetime -> YYYY-MM-DD (null if unusable). */
@@ -89,8 +101,13 @@ export async function ingestNycAcris(): Promise<{ upserted: number }> {
     console.log(`[acris] master: ${deed.size} priced deeds`);
     if (deed.size < 200000) throw new Error(`only ${deed.size} deeds — ACRIS Master drift?`);
 
-    // 2) Legals: keyset-stream, keep only rows whose document_id is a priced deed -> BBL sales
-    const salesByBbl = new Map<string, { fips: string; sales: Sale[] }>();
+    // 2) Legals: keyset-stream; collect the (fips,bbl) legal rows PER deed document.
+    //    document_amt is the AGGREGATE consideration for the whole document, so a deed
+    //    that transfers many lots (portfolio/bulk sale) would wrongly attribute its
+    //    total to each lot ($1.5B "sale prices"). We therefore only keep a price when a
+    //    document maps to exactly ONE BBL — a single-lot deed's amount IS that lot's
+    //    sale price; multi-lot documents have no knowable per-lot price and are dropped.
+    const docHits = new Map<string, { fips: string; bbl: string }[]>();
     let legalRows = 0, matched = 0;
     cursor = DOC_FLOOR;
     for (;;) {
@@ -102,21 +119,32 @@ export async function ingestNycAcris(): Promise<{ upserted: number }> {
       if (!rows.length) break;
       legalRows += rows.length;
       for (const o of rows) {
-        const d = deed.get(o.document_id);
+        if (!deed.has(o.document_id)) continue;
         const fips = FIPS[String(o.borough)];
-        if (!d || !fips || !o.block || !o.lot) continue;
+        if (!fips || !o.block || !o.lot) continue;
         const bbl = String(o.borough) + String(o.block).padStart(5, '0') + String(o.lot).padStart(4, '0');
         matched++;
-        const rec = salesByBbl.get(bbl);
-        const sale: Sale = { date: d.date, price: d.price, doc: o.document_id };
-        if (rec) rec.sales.push(sale);
-        else salesByBbl.set(bbl, { fips, sales: [sale] });
+        const hits = docHits.get(o.document_id);
+        if (hits) hits.push({ fips, bbl }); else docHits.set(o.document_id, [{ fips, bbl }]);
       }
       cursor = rows[rows.length - 1].document_id;
       if (rows.length < PAGE) break;
-      if (legalRows % 1000000 < PAGE) console.log(`[acris] legals scanned ${legalRows}, ${salesByBbl.size} bbls so far`);
+      if (legalRows % 1000000 < PAGE) console.log(`[acris] legals scanned ${legalRows}, ${docHits.size} deed docs so far`);
+    }
+
+    // Attribute each single-lot document's price to its one BBL; drop multi-lot docs.
+    const salesByBbl = new Map<string, { fips: string; sales: Sale[] }>();
+    let multiLotDropped = 0;
+    for (const [doc, hits] of docHits) {
+      const uniq = new Map(hits.map(h => [h.fips + ':' + h.bbl, h]));
+      if (uniq.size !== 1) { multiLotDropped++; continue; }
+      const { fips, bbl } = [...uniq.values()][0];
+      const d = deed.get(doc)!;
+      const sale: Sale = { date: d.date, price: d.price, doc };
+      const rec = salesByBbl.get(bbl);
+      if (rec) rec.sales.push(sale); else salesByBbl.set(bbl, { fips, sales: [sale] });
     }
-    console.log(`[acris] legals: ${legalRows} rows scanned, ${matched} deed-legal hits, ${salesByBbl.size} bbls`);
+    console.log(`[acris] legals: ${legalRows} scanned, ${matched} hits, ${docHits.size} deed docs -> ${salesByBbl.size} single-lot bbls (${multiLotDropped} multi-lot docs dropped)`);
     if (salesByBbl.size < 20000) throw new Error(`only ${salesByBbl.size} BBLs matched — join drift?`);
 
     // 3) Stage the sales, then one set-based UPDATE onto PLUTO rows. A TEMP TABLE

← 14c95a8 auto-save: 2026-07-21T19:09:20 (2 files) — src/ingest/parcel · back to Nationalrealestate · LA commercial RE layer: taxonomy + materialized 153.9k parce 613b106 →