← back to Nationalrealestate
auto-save: 2026-07-21T18:39:12 (3 files) — src/ingest/parcels/engine.ts src/server/property.ts src/ingest/parcels/nyc_acris.ts
4ade9ca0f99977f88799812dae396200f605f580 · 2026-07-21 18:39:13 -0700 · Steve Abrams
Files touched
M src/ingest/parcels/engine.tsA src/ingest/parcels/nyc_acris.tsM src/server/property.ts
Diff
commit 4ade9ca0f99977f88799812dae396200f605f580
Author: Steve Abrams <steve@designerwallcoverings.com>
Date: Tue Jul 21 18:39:13 2026 -0700
auto-save: 2026-07-21T18:39:12 (3 files) — src/ingest/parcels/engine.ts src/server/property.ts src/ingest/parcels/nyc_acris.ts
---
src/ingest/parcels/engine.ts | 4 +-
src/ingest/parcels/nyc_acris.ts | 158 ++++++++++++++++++++++++++++++++++++++++
src/server/property.ts | 2 +-
3 files changed, 162 insertions(+), 2 deletions(-)
diff --git a/src/ingest/parcels/engine.ts b/src/ingest/parcels/engine.ts
index 8e68df5..7cc3f1a 100644
--- a/src/ingest/parcels/engine.ts
+++ b/src/ingest/parcels/engine.ts
@@ -1,12 +1,14 @@
/**
* Parcel-ingest dispatcher: npm run ingest:parcels <county>
- * Counties: nyc (five boroughs, PLUTO) | king (King WA EXTR) | cook (Cook IL Socrata)
+ * Counties: nyc (five boroughs, PLUTO) | nyc-acris (ACRIS deed sales onto PLUTO)
+ * | king (King WA EXTR) | cook (Cook IL Socrata)
* Per-county adapters own download + parse + upsert + parcel_source registration.
*/
import { pool } from '../../../db/pool.ts';
const ADAPTERS: Record<string, () => Promise<{ run: () => Promise<{ upserted: number }> }>> = {
nyc: async () => ({ run: (await import('./nyc_pluto.ts')).ingestNycPluto }),
+ 'nyc-acris': async () => ({ run: (await import('./nyc_acris.ts')).ingestNycAcris }),
king: async () => ({ run: (await import('./king_wa.ts')).ingestKing }),
cook: async () => ({ run: (await import('./cook_il.ts')).ingestCook }),
};
diff --git a/src/ingest/parcels/nyc_acris.ts b/src/ingest/parcels/nyc_acris.ts
new file mode 100644
index 0000000..41c11c8
--- /dev/null
+++ b/src/ingest/parcels/nyc_acris.ts
@@ -0,0 +1,158 @@
+/**
+ * NYC ACRIS recorded-deed sales -> existing PLUTO parcels (NYC Open Data, $0).
+ * Fills the "Sale/deed chain from ACRIS pending" gap on all five boroughs by
+ * layering REAL recorded sale prices onto the lots already loaded by nyc_pluto.ts.
+ *
+ * Real Property Master (bnx9-e6tj) -> document_id, doc_type, document_date, document_amt
+ * Real Property Legals (8h5j-fqxa) -> document_id -> borough/block/lot (=> BBL)
+ *
+ * Join is by document_id. Master is filtered to priced DEED/DEEDO documents
+ * (~300k since 2018); Legals is keyset-streamed and filtered in memory to those
+ * document_ids (chunked IN() 414s at ~250 ids, so keyset-stream is fewer requests).
+ * 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.
+ *
+ * Run: npm run ingest:parcels -- nyc-acris (NODE_OPTIONS=--max-old-space-size=4096 recommended)
+ */
+import { query, pool } from '../../../db/pool.ts';
+import { openRun, closeRun } from '../run.ts';
+
+const MASTER = 'https://data.cityofnewyork.us/resource/bnx9-e6tj.json';
+const LEGALS = 'https://data.cityofnewyork.us/resource/8h5j-fqxa.json';
+const PAGE = 50000;
+const SINCE = '2018-01-01T00:00:00';
+const DOC_FLOOR = '2018000000000000'; // keyset lower bound for legals (modern numeric doc_ids)
+
+// borocode -> county_fips (mirrors nyc_pluto.ts BORO)
+const FIPS: Record<string, string> = { '1': '36061', '2': '36005', '3': '36047', '4': '36081', '5': '36085' };
+
+interface Sale { date: string; price: number; doc: string }
+
+/** GET SoQL JSON with polite retry on 429/5xx (no app token -> throttle-aware). */
+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;
+ }
+ throw new Error(`SODA ${res.status} ${res.statusText}: ${url.slice(0, 120)}…`);
+ }
+}
+
+/** ACRIS document_date / recorded_datetime -> YYYY-MM-DD (null if unusable). */
+function isoDate(d: string | undefined): string | null {
+ if (!d) return null;
+ const s = String(d).slice(0, 10);
+ return /^\d{4}-\d{2}-\d{2}$/.test(s) ? s : null;
+}
+
+export async function ingestNycAcris(): Promise<{ upserted: number }> {
+ const runId = await openRun('parcel_nyc_acris', MASTER);
+ try {
+ // 1) Master: priced DEED/DEEDO docs since 2018, keyset by document_id -> {date, price}
+ const deed = new Map<string, { date: string; price: number }>();
+ let cursor = '';
+ for (;;) {
+ const where = `document_amt > 0 AND doc_type in('DEED','DEEDO') ` +
+ `AND recorded_datetime > '${SINCE}'` + (cursor ? ` AND document_id > '${cursor}'` : '');
+ const rows = await soda(MASTER, {
+ $select: 'document_id,document_date,document_amt,recorded_datetime',
+ $where: where, $order: 'document_id', $limit: String(PAGE),
+ });
+ if (!rows.length) break;
+ for (const o of rows) {
+ const date = isoDate(o.document_date) || isoDate(o.recorded_datetime);
+ const price = Number(o.document_amt);
+ if (date && Number.isFinite(price) && price > 0) deed.set(o.document_id, { date, price });
+ }
+ cursor = rows[rows.length - 1].document_id;
+ if (rows.length < PAGE) break;
+ }
+ 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[] }>();
+ let legalRows = 0, matched = 0;
+ cursor = DOC_FLOOR;
+ for (;;) {
+ const rows = await soda(LEGALS, {
+ $select: 'document_id,borough,block,lot',
+ $where: `document_id > '${cursor}'`, $order: 'document_id', $limit: String(PAGE),
+ });
+ if (!rows.length) break;
+ legalRows += rows.length;
+ for (const o of rows) {
+ const d = deed.get(o.document_id);
+ const fips = FIPS[String(o.borough)];
+ if (!d || !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] });
+ }
+ 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`);
+ }
+ console.log(`[acris] legals: ${legalRows} rows scanned, ${matched} deed-legal hits, ${salesByBbl.size} bbls`);
+ if (salesByBbl.size < 20000) throw new Error(`only ${salesByBbl.size} BBLs matched — join drift?`);
+
+ // 3) Stage the sales into a temp table, then one set-based UPDATE onto PLUTO rows
+ await query(`CREATE TEMP TABLE tmp_acris (fips text, bbl bigint, last_date date, last_price numeric, sales jsonb) ON COMMIT DROP`);
+ const rowsForInsert: [string, string, string, number, string][] = [];
+ for (const [bbl, { fips, sales }] of salesByBbl) {
+ sales.sort((a, b) => b.date.localeCompare(a.date));
+ const top = sales.slice(0, 10);
+ rowsForInsert.push([fips, bbl, top[0].date, top[0].price,
+ JSON.stringify(top.map(s => ({ date: s.date, price: s.price, buyer: null, seller: null, document_id: s.doc })))]);
+ }
+ for (let i = 0; i < rowsForInsert.length; i += 1000) {
+ const chunk = rowsForInsert.slice(i, i + 1000);
+ const params: unknown[] = [];
+ const vals = chunk.map((r, j) => {
+ const b = j * 5; params.push(r[0], r[1], r[2], r[3], r[4]);
+ return `($${b + 1},$${b + 2},$${b + 3},$${b + 4},$${b + 5}::jsonb)`;
+ });
+ await query(`INSERT INTO tmp_acris (fips,bbl,last_date,last_price,sales) VALUES ${vals.join(',')}`, params);
+ }
+
+ const upd = await query<{ n: string }>(
+ `WITH u AS (
+ UPDATE parcel p SET
+ last_sale_date = t.last_date,
+ last_sale_price = t.last_price,
+ extra = COALESCE(p.extra, '{}'::jsonb)
+ || jsonb_build_object('sales', t.sales,
+ 'sale_source', 'NYC ACRIS recorded priced deeds (DEED/DEEDO, 2018–present)')
+ FROM tmp_acris t
+ WHERE p.county_fips = t.fips AND floor(p.source_id::numeric)::bigint = t.bbl
+ RETURNING 1)
+ SELECT count(*)::text AS n FROM u`);
+ const upserted = Number(upd.rows[0].n);
+ console.log(`[acris] updated ${upserted} PLUTO parcels with recorded sales`);
+
+ await closeRun(runId, 'ok', {
+ upserted,
+ notes: `NYC ACRIS: ${deed.size} priced deeds, ${salesByBbl.size} BBLs, ${upserted} PLUTO lots updated (condo-unit + buyer/seller pending)`,
+ });
+ return { upserted };
+ } catch (e: any) {
+ await closeRun(runId, 'failed', { notes: String(e.message || e) });
+ throw e;
+ }
+}
+
+if (import.meta.url === `file://${process.argv[1]}`) {
+ ingestNycAcris().then(() => pool.end()).catch(e => { console.error(e); process.exit(1); });
+}
diff --git a/src/server/property.ts b/src/server/property.ts
index f65e323..ef00db0 100644
--- a/src/server/property.ts
+++ b/src/server/property.ts
@@ -178,7 +178,7 @@ const NYC_META: PgCountyMeta = {
assessor: { name: 'NYC Department of Finance', phone: '311',
website: 'https://www.nyc.gov/site/finance/property/property.page' },
detailsNote: 'NYC MapPLUTO is lot-level — building sqft shown; unit-level beds/baths not in PLUTO.',
- historyNote: 'Sale/deed chain from ACRIS pending; PLUTO carries current attributes only.',
+ historyNote: 'Recorded deed sales (price + date) from NYC ACRIS priced DEED/DEEDO documents (2018–present) where available; condo-unit deeds and buyer/seller names pending. PLUTO supplies current attributes.',
taxNote: 'NYC assessed values from MapPLUTO (current version).',
ownerNote: 'Owner of record from NYC MapPLUTO.',
zoningNote: 'Primary zoning district (zonedist1) from NYC MapPLUTO.',
← 5a71ca7 M-B3 firm↔listing pilot: facts-only JSON-LD ingest of coldwe
·
back to Nationalrealestate
·
auto-save: 2026-07-21T19:09:20 (2 files) — src/ingest/parcel 14c95a8 →