[object Object]

← back to Nationalrealestate

CCK review fixes: legacy CT counties seeded (CT choropleth hole closed), mojibake metro overrides, unmatched-collector dedupe, migrate.ts txn constraint doc

c188ec3b8a24fec89dc7296db3d367d9632ae151 · 2026-07-21 13:11:56 -0700 · Steve Abrams

Files touched

Diff

commit c188ec3b8a24fec89dc7296db3d367d9632ae151
Author: Steve Abrams <steve@designerwallcoverings.com>
Date:   Tue Jul 21 13:11:56 2026 -0700

    CCK review fixes: legacy CT counties seeded (CT choropleth hole closed), mojibake metro overrides, unmatched-collector dedupe, migrate.ts txn constraint doc
---
 data/crosswalk-overrides.json |  7 ++++++-
 db/migrate.ts                 |  2 ++
 src/ingest/crosswalk.ts       |  4 ++++
 src/ingest/regions_seed.ts    | 20 ++++++++++++++++++++
 4 files changed, 32 insertions(+), 1 deletion(-)

diff --git a/data/crosswalk-overrides.json b/data/crosswalk-overrides.json
index 0967ef4..7b4c2be 100644
--- a/data/crosswalk-overrides.json
+++ b/data/crosswalk-overrides.json
@@ -1 +1,6 @@
-{}
+{
+  "zillow": {
+    "Ca±on City, CO": "metro:15860",
+    "Espa±ola, NM": "metro:21580"
+  }
+}
diff --git a/db/migrate.ts b/db/migrate.ts
index 7c55ffc..760a790 100644
--- a/db/migrate.ts
+++ b/db/migrate.ts
@@ -1,6 +1,8 @@
 /**
  * Runs every .sql file in db/migrations/ in lexical order, idempotent.
  * Records applied filenames in schema_migrations.
+ * Constraint: migration files must NOT contain their own BEGIN/COMMIT —
+ * the runner wraps each file in a transaction.
  */
 import 'dotenv/config';
 import { readFileSync, readdirSync } from 'node:fs';
diff --git a/src/ingest/crosswalk.ts b/src/ingest/crosswalk.ts
index d867517..52857c8 100644
--- a/src/ingest/crosswalk.ts
+++ b/src/ingest/crosswalk.ts
@@ -104,7 +104,11 @@ export function buildMetroMatcher(maps: RegionMaps) {
 
 export class UnmatchedCollector {
   private items: Record<string, unknown[]> = {};
+  private seen = new Set<string>();
   add(source: string, item: unknown) {
+    const key = source + '|' + JSON.stringify(item);
+    if (this.seen.has(key)) return; // same region unmatched by multiple files in one run (zhvi+zori)
+    this.seen.add(key);
     (this.items[source] ||= []).push(item);
   }
   count(source: string): number {
diff --git a/src/ingest/regions_seed.ts b/src/ingest/regions_seed.ts
index 4b5eb54..e14ad31 100644
--- a/src/ingest/regions_seed.ts
+++ b/src/ingest/regions_seed.ts
@@ -30,6 +30,20 @@ const STATES: Array<[string, string, string]> = [
   ['WV','54','West Virginia'],['WI','55','Wisconsin'],['WY','56','Wyoming'],
 ];
 
+// Connecticut switched to planning-region FIPS (09110+) in the 2024 Gazetteer, but
+// Zillow/Redfin still publish by legacy county (09001-09015) and the choropleth
+// GeoJSON carries legacy county shapes — seed both so CT data lands and renders.
+const LEGACY_CT_COUNTIES: Array<[string, string, number, number]> = [
+  ['09001', 'Fairfield County', 41.27, -73.39],
+  ['09003', 'Hartford County', 41.81, -72.73],
+  ['09005', 'Litchfield County', 41.79, -73.24],
+  ['09007', 'Middlesex County', 41.43, -72.52],
+  ['09009', 'New Haven County', 41.35, -72.90],
+  ['09011', 'New London County', 41.47, -72.10],
+  ['09013', 'Tolland County', 41.85, -72.34],
+  ['09015', 'Windham County', 41.83, -71.99],
+];
+
 function parseGazetteer(zipPath: string): Array<Record<string, string>> {
   const text = execFileSync('unzip', ['-p', zipPath], { maxBuffer: 64 * 1024 * 1024 }).toString('utf8');
   const lines = text.split('\n').filter((l) => l.trim().length > 0);
@@ -91,6 +105,12 @@ async function main() {
       upserted++;
     }
 
+    // legacy CT counties (coexist with the planning regions)
+    for (const [fips, name, lat, lng] of LEGACY_CT_COUNTIES) {
+      await upsertRegion({ region_type: 'county', canonical_key: `county:${fips}`, name, state_code: 'CT', fips, lat, lng });
+      upserted++;
+    }
+
     // metros (CBSA — metro + micro areas)
     const cbsa = await download(CBSA_ZIP, 'gaz_cbsa_2024.zip', { reuseCached: true });
     const cbsaRows = parseGazetteer(cbsa.path);

← effc2fd M4: deterministic opportunity scorer (5-component percentile  ·  back to Nationalrealestate  ·  M5 (partial): refresh orchestrator, alerts checker, monthly f42ca12 →