1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
|
#!/usr/bin/env python3
"""BCG NYC Construction Early Warning System — lightweight, standard library only.
Reads NYC DOB NOW Build Job Application Filings via Socrata's public API.
Stores changes in SQLite, exports reviewable CSVs and an offline HTML dashboard.
Does NOT scrape contact information or send messages.
Usage: python3 bcg_nyc_watch.py --days 14 --max-rows 1500
"""
import argparse
import csv
from datetime import datetime, timedelta, timezone
import hashlib
import html
import json
import os
from pathlib import Path
import re
import sqlite3
import sys
import time
from urllib.parse import urlencode
from urllib.request import Request, urlopen
from urllib.error import HTTPError, URLError
DATASET_ID = "w9ak-ipjd"
API_URL = f"https://data.cityofnewyork.us/resource/{DATASET_ID}.json"
DATASET_PAGE = f"https://data.cityofnewyork.us/d/{DATASET_ID}"
FIELDS = [
"job_filing_number", "filing_date", "current_status_date", "filing_status",
"job_type", "job_description", "borough", "house_no", "street_name",
"owner_s_business_name", "applicant_business_name", "initial_cost",
"total_construction_floor_area", "building_type", "owner_type",
"general_construction_work_type_", "mechanical_systems_work_type_",
"structural_work_type_", "plumbing_work_type", "sprinkler_work_type",
"standpipe", "foundation_work_type_", "earth_work_work_type_",
"solar_work_type_", "full_demolition_work_type_", "approved_date",
"first_permit_date", "filing_review_type",
]
# Work type labels are interpreted as potential service relevance only.
WORK = [
("mechanical_systems_work_type_", 18, "MEP / HVAC coordination"),
("structural_work_type_", 17, "Structural detailing / coordination"),
("plumbing_work_type", 14, "Plumbing drawings / coordination"),
("sprinkler_work_type", 14, "Fire protection coordination"),
("standpipe", 10, "Fire protection coordination"),
("general_construction_work_type_", 11, "Shop drawings / quantity takeoffs"),
("foundation_work_type_", 13, "Concrete and foundation takeoffs"),
("earth_work_work_type_", 8, "Civil/site takeoffs"),
("solar_work_type_", 7, "Technical drawing coordination"),
]
COMMERCIAL_WORDS = [
"commercial", "office", "retail", "restaurant", "store", "warehouse",
"school", "hospital", "medical", "clinic", "hotel", "lobby",
"tenant", "fit-out", "fitout", "interior renovation", "industrial", "factory",
"mixed-use", "community facility", "gym", "laboratory",
]
TECHNICAL_WORDS = [
("hvac", "MEP / HVAC coordination"),
("duct", "MEP / HVAC coordination"),
("sprinkler", "Fire protection coordination"),
("plumbing", "Plumbing drawings / coordination"),
("structural", "Structural detailing / coordination"),
("steel", "Structural detailing / coordination"),
("concrete", "Concrete takeoffs / detailing"),
("curtain wall", "Glazing / envelope shop drawings"),
("storefront", "Glazing / envelope shop drawings"),
("partition", "Interior shop drawings / takeoffs"),
("drywall", "Interior shop drawings / takeoffs"),
("ceiling", "Interior shop drawings / takeoffs"),
("renovation", "Quantity takeoffs / shop drawings"),
("alteration", "Quantity takeoffs / shop drawings"),
]
DEAD_STATUS = ("withdrawn", "superseded", "signed off", "sign off", "canceled", "cancelled")
OUT_FIELDS = [
"score", "priority", "change_type", "filing_date", "filing_status",
"job_filing_number", "job_type", "borough", "address", "estimated_cost_usd",
"floor_area_sqft", "owner_business", "applicant_business", "service_matches",
"score_reasons", "description", "source_record", "buyer_intent", "review_status",
]
def clean(value):
if value is None:
return ""
return re.sub(r"\s+", " ", str(value)).strip()
def numeric(value):
value = clean(value).replace(",", "").replace("$", "")
try:
result = float(value)
return max(0.0, result) if result < 1e12 else 0.0
except (ValueError, OverflowError):
return 0.0
def yes(value):
return clean(value).lower() in ("yes", "y", "true", "1", "x")
def is_commercial_or_public(record):
txt = (clean(record.get("job_description")) + " " + clean(record.get("building_type"))).lower()
hits = [w for w in COMMERCIAL_WORDS if w in txt]
return hits[:4]
def classify(record):
desc = clean(record.get("job_description"))
job_type = clean(record.get("job_type"))
status = clean(record.get("filing_status"))
description_lower = desc.lower()
cost = numeric(record.get("initial_cost"))
area = numeric(record.get("total_construction_floor_area"))
score = 0
reasons = []
services = set()
if "new building" in job_type.lower():
score += 18
reasons.append("New-building filing")
elif "alteration" in job_type.lower():
score += 10
reasons.append("Alteration filing")
elif "demolition" in job_type.lower():
score += 3
for field, weight, service in WORK:
if yes(record.get(field)):
score += weight
services.add(service)
reasons.append(f"Work type: {field.replace('_work_type_', '').replace('_', ' ').strip()}")
# Guard against multi-trade flags driving the entire score.
work_score = min(43, sum(weight for field, weight, svc in WORK if yes(record.get(field))))
score = score - sum(weight for field, weight, svc in WORK if yes(record.get(field))) + work_score
for keyword, service in TECHNICAL_WORDS:
if keyword in description_lower:
services.add(service)
if services:
score += min(10, len(services) * 3)
commercial_hits = is_commercial_or_public(record)
if commercial_hits:
score += 14
reasons.append("Commercial/public-use language: " + ", ".join(commercial_hits))
if cost >= 2_000_000:
score += 16
reasons.append("Reported initial cost >= $2M (unverified)")
elif cost >= 500_000:
score += 11
reasons.append("Reported initial cost >= $500K (unverified)")
elif cost >= 100_000:
score += 5
if area >= 30000:
score += 10
reasons.append("Reported floor area >= 30K sq ft (unverified)")
elif area >= 10000:
score += 6
if "1-2-3 family" in clean(record.get("building_type")).lower():
score -= 13
reasons.append("Small residential building-type penalty")
if any(t in status.lower() for t in DEAD_STATUS):
score -= 45
reasons.append("Inactive/completed status penalty")
elif "filed" in status.lower() or "plan exam" in status.lower() or "incomplete" in status.lower():
score += 6
reasons.append("Early review/filing status")
score = max(0, min(100, score))
priority = "HIGH" if score >= 65 else ("MEDIUM" if score >= 40 else "LOW")
if not services:
services.add("Unspecified — investigate project scope")
return score, priority, "; ".join(sorted(services)), "; ".join(reasons)
def api_query(start, page_size=250, max_rows=1500, timeout=25):
offset = 0
end = False
while offset < max_rows and not end:
current_limit = min(page_size, max_rows - offset)
params = {
"$select": ",".join(FIELDS),
"$where": f"filing_date >= '{start}T00:00:00'",
"$order": "filing_date DESC,job_filing_number ASC",
"$limit": str(current_limit),
"$offset": str(offset),
}
url = API_URL + "?" + urlencode(params)
headers = {"Accept": "application/json", "User-Agent": "BCG-Permit-Research/1.0 (public data; low-rate polling)"}
token = os.getenv("SOCRATA_APP_TOKEN", "")
if token:
headers["X-App-Token"] = token
req = Request(url, headers=headers)
try:
with urlopen(req, timeout=timeout) as response:
result = json.load(response)
except HTTPError as exc:
detail = exc.read(400).decode("utf-8", errors="replace")
raise RuntimeError(f"NYC API returned HTTP {exc.code}: {detail}") from exc
except (URLError, TimeoutError, OSError) as exc:
raise RuntimeError(f"Cannot reach NYC Open Data API: {exc}. IPv6-only VPS networking may be the cause.") from exc
if not isinstance(result, list):
raise RuntimeError(f"Unexpected API response: {str(result)[:250]}")
for row in result:
if isinstance(row, dict):
yield row
print(f"Fetched {offset + len(result)} filings", flush=True)
end = len(result) < current_limit
offset += len(result)
if not end and offset < max_rows:
time.sleep(0.4) # polite pacing
def init_db(db_path):
conn = sqlite3.connect(db_path)
conn.execute("""CREATE TABLE IF NOT EXISTS filings (
job_filing_number TEXT PRIMARY KEY, raw_json TEXT NOT NULL,
fingerprint TEXT NOT NULL, first_seen TEXT NOT NULL, last_seen TEXT NOT NULL,
changed_at TEXT NOT NULL, last_change_type TEXT NOT NULL
)""")
conn.commit()
return conn
def fingerprint(row):
return hashlib.sha256(json.dumps(row, sort_keys=True, ensure_ascii=False).encode()).hexdigest()
def save_records(conn, items, now):
events = {}
baseline = conn.execute("SELECT COUNT(*) FROM filings").fetchone()[0] == 0
for item in items:
key = clean(item.get("job_filing_number"))
if not key:
continue
serial = json.dumps(item, ensure_ascii=False, sort_keys=True)
fp = fingerprint(item)
old = conn.execute("SELECT fingerprint FROM filings WHERE job_filing_number=?", (key,)).fetchone()
if old is None:
change = "BASELINE" if baseline else "NEW"
conn.execute("""INSERT INTO filings VALUES (?, ?, ?, ?, ?, ?, ?)""",
(key, serial, fp, now, now, now, change))
elif old[0] != fp:
change = "UPDATED"
conn.execute("""UPDATE filings SET raw_json=?, fingerprint=?, last_seen=?, changed_at=?, last_change_type=?
WHERE job_filing_number=?""", (serial, fp, now, now, change, key))
else:
change = "UNCHANGED"
conn.execute("UPDATE filings SET last_seen=? WHERE job_filing_number=?", (now, key))
events[key] = change
conn.commit()
return events, baseline
def safe_csv_cell(s):
"""Guard against CSV formula injection when imported into Excel."""
s = clean(s)
if s and s[0] in ("=", "+", "-", "@"): # literal strings only
return "'" + s
return s
def record_for_export(item, event):
score, priority, svc, reasons = classify(item)
name = clean(item.get("job_filing_number"))
url = DATASET_PAGE + "?job_filing_number=" + name
return {
"score": score,
"priority": priority,
"change_type": event,
"filing_date": clean(item.get("filing_date"))[:10],
"filing_status": clean(item.get("filing_status")),
"job_filing_number": name,
"job_type": clean(item.get("job_type")),
"borough": clean(item.get("borough")),
"address": " ".join([clean(item.get("house_no")), clean(item.get("street_name"))]).strip(),
"estimated_cost_usd": f"{numeric(item.get('initial_cost')):.0f}",
"floor_area_sqft": f"{numeric(item.get('total_construction_floor_area')):.0f}",
"owner_business": clean(item.get("owner_s_business_name")),
"applicant_business": clean(item.get("applicant_business_name")),
"service_matches": svc,
"score_reasons": reasons,
"description": clean(item.get("job_description")),
"source_record": url,
"buyer_intent": "NOT VERIFIED",
"review_status": "Human research required; no outreach sent",
}
def write_csv(dest, records):
with open(dest, "w", encoding="utf-8-sig", newline="") as f:
writer = csv.DictWriter(f, fieldnames=OUT_FIELDS)
writer.writeheader()
for row in records:
writer.writerow({field: safe_csv_cell(row.get(field, "")) for field in OUT_FIELDS})
def write_html(dest, rows, date_cutoff, now, baseline, new_count, update_count):
top = rows[:60]
blocks = []
for r in top:
url = html.escape(r["source_record"], quote=True)
title = html.escape(r["address"] or r["job_filing_number"])
description = html.escape(r["description"][:300])
blocks.append(
f'<tr><td><strong>{r["score"]}</strong><small>{html.escape(r["priority"])}</small></td>'
f'<td><a target="_blank" rel="noopener" href="{url}">{title}</a><small>{html.escape(r["borough"])} · {html.escape(r["job_filing_number"])}</small></td>'
f'<td>{html.escape(r["filing_date"])}<small>{html.escape(r["filing_status"])}</small></td>'
f'<td>{html.escape(r["owner_business"] or "Not listed")}<small>Applicant: {html.escape(r["applicant_business"] or "Not listed")}</small></td>'
f'<td>{html.escape(r["service_matches"])}<small>{description}</small></td></tr>'
)
counts = {k: sum(r["priority"] == k for r in rows) for k in ("HIGH", "MEDIUM", "LOW")}
head = f"""<!doctype html><html lang="en"><head><meta charset="utf-8">
<meta name="viewport" content="width=device-width,initial-scale=1">
<title>BCG NYC Project Intelligence</title><style>
:root{{color-scheme:dark}}body{{font:15px/1.5 system-ui,Arial;background:#111b1b;color:#e7f1ed;margin:0}}
header,main{{max-width:1220px;margin:auto;padding:24px}}header{{padding-bottom:5px}}
h1{{font-size:27px;margin:0;color:#f3e0b2}}p{{color:#b9cbc5}}.kpis{{display:grid;grid-template-columns:repeat(auto-fit,minmax(150px,1fr));gap:12px;margin:20px 0}}
.kpi{{padding:14px;border:1px solid #456259;border-radius:12px;background:#192b28}}.kpi b{{font-size:26px;display:block;color:#f1d58d}}
table{{border-collapse:collapse;width:100%;font-size:13px}}th,td{{padding:13px;text-align:left;border-bottom:1px solid #40504a;vertical-align:top}}
th{{position:sticky;top:0;background:#1d312c}}small{{display:block;color:#a8bdb4;font-size:11px;margin-top:5px}}
a{{color:#f1d58d}}.overflow{{overflow:auto}}footer{{padding:20px;color:#a8bdb4}}</style></head><body>
<header><h1>BCG / NYC Construction Project Intelligence</h1>
<p>Public NYC DOB NOW filing research · Generated {html.escape(now)} UTC · Filing dates from {date_cutoff} onward</p></header>
<main><div class="kpis"><div class="kpi"><b>{len(rows)}</b>Listings scanned</div>
<div class="kpi"><b>{counts['HIGH']}</b>High technical fit</div>
<div class="kpi"><b>{counts['MEDIUM']}</b>Medium technical fit</div>
<div class="kpi"><b>{new_count}</b>New since previous scan</div>
<div class="kpi"><b>{update_count}</b>Changed since previous scan</div></div>
<p>{"First run: baseline established; newly detected items will appear on later runs." if baseline else "Changes are tracked against the previous stored scan."}
The score is a project relevance heuristic, NOT evidence of available contracts, procurement, bid status or buyer intent. Cost and area are self-reported.</p>
<div class="overflow"><table><thead><tr><th>Fit</th><th>Project</th><th>Filing</th><th>Public organization</th><th>Potential service / description</th></tr></thead><tbody>"""
foot = """</tbody></table></div><footer>Source: NYC Department of Buildings via NYC Open Data.
Independent BCG research; not affiliated with NYC. Verify every filing in the DOB NOW public portal before any commercial approach.
Electrical, Elevator, and LAA filings are not covered by this dataset. No outreach is automated.</footer></main></body></html>"""
with open(dest, "w", encoding="utf-8") as f:
f.write(head + "\n".join(blocks) + foot)
def demo_test():
sample = {"job_filing_number": "M00999999-I1", "filing_date": "2026-11-01T00:00:00.000",
"filing_status": "Filed", "job_type": "Alteration", "job_description":
"Commercial office tenant HVAC and structural renovation", "borough": "Manhattan",
"house_no": "100", "street_name": "TEST STREET", "owner_s_business_name": "TEST OWNER",
"applicant_business_name": "TEST ARCHITECT", "initial_cost": "800000",
"mechanical_systems_work_type_": "YES", "structural_work_type_": "YES"}
score, p, services, reasons = classify(sample)
assert score >= 40 and "HVAC" in services
conn = sqlite3.connect(":memory:")
conn.execute("CREATE TABLE filings(job_filing_number TEXT PRIMARY KEY,raw_json TEXT,fingerprint TEXT,first_seen TEXT,last_seen TEXT,changed_at TEXT,last_change_type TEXT)")
events, first = save_records(conn, [sample], "2026-11-03T00:00:00")
assert first and events[sample["job_filing_number"]] == "BASELINE"
events, first = save_records(conn, [sample], "2026-11-03T01:00:00")
assert not first and events[sample["job_filing_number"]] == "UNCHANGED"
sample2 = dict(sample, filing_status="Approved")
events, first = save_records(conn, [sample2], "2026-11-03T02:00:00")
assert events[sample["job_filing_number"]] == "UPDATED"
assert safe_csv_cell("=HYPERLINK(1)").startswith("'")
print("Self-test passed: scoring, baseline, change detection, CSV safety.")
def main():
parser = argparse.ArgumentParser(description="BCG NYC Construction Early Warning")
parser.add_argument("--days", type=int, default=14, help="Look back days from UTC today (default: 14)")
parser.add_argument("--max-rows", type=int, default=1500, help="Max listings fetched per run (default: 1500)")
parser.add_argument("--page-size", type=int, default=250)
parser.add_argument("--out", default=".", help="Output directory, default current directory")
parser.add_argument("--self-test", action="store_true")
args = parser.parse_args()
if args.self_test:
demo_test()
return 0
if not 1 <= args.days <= 365 or not 1 <= args.max_rows <= 10000 or not 1 <= args.page_size <= 1000:
parser.error("days=1..365; max-rows=1..10000; page-size=1..1000")
folder = Path(args.out)
folder.mkdir(parents=True, exist_ok=True)
now = datetime.now(timezone.utc)
cutoff = (now - timedelta(days=args.days)).strftime("%Y-%m-%d")
print(f"BCG NYC Project Watch | UTC {now.strftime('%Y-%m-%d %H:%M')} | since {cutoff}")
print(f"NYC Open Data source: {DATASET_PAGE}")
try:
data = list(api_query(cutoff, args.page_size, args.max_rows))
except (RuntimeError, ValueError, json.JSONDecodeError) as exc:
print(f"ERROR: {exc}", file=sys.stderr)
print("Test connectivity: curl -6 -I --max-time 12 https://data.cityofnewyork.us/", file=sys.stderr)
return 2
if not data:
print("No filings returned for selected lookback; database left unchanged.")
return 0
with init_db(str(folder / "bcg_nyc.db")) as conn:
events, baseline = save_records(conn, data, now.isoformat(timespec="seconds"))
rows = [record_for_export(item, events.get(clean(item.get("job_filing_number")), "")) for item in data]
rows.sort(key=lambda r: (r["score"], r["filing_date"]), reverse=True)
changes = [row for row in rows if row["change_type"] in ("NEW", "UPDATED")]
# Output the full ranked sample and only newly seen or modified rows.
write_csv(folder / "bcg_nyc_opportunities.csv", rows)
write_csv(folder / "bcg_nyc_changes.csv", changes)
write_html(folder / "bcg_nyc_dashboard.html", rows, cutoff, now.strftime("%Y-%m-%d %H:%M"),
baseline, sum(e == "NEW" for e in events.values()), sum(e == "UPDATED" for e in events.values()))
print(f"Rows evaluated: {len(rows)} | High: {sum(r['priority'] == 'HIGH' for r in rows)} | Medium: {sum(r['priority'] == 'MEDIUM' for r in rows)}")
print(f"New: {sum(e == 'NEW' for e in events.values())} | Updated: {sum(e == 'UPDATED' for e in events.values())} | Baseline: {baseline}")
print("Files: bcg_nyc_opportunities.csv, bcg_nyc_changes.csv, bcg_nyc_dashboard.html, bcg_nyc.db")
for r in rows[:5]:
print(f" {r['score']:>3} {r['priority']:<6} {r['borough']:<12} {r['address'][:35]:<35} {r['job_filing_number']}")
return 0
if __name__ == "__main__":
sys.exit(main())
|
Cairnbin