#!/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())