spb/datacenterindex

1/**2 * Facility sanity report: implausible values and inconsistencies, grouped by the connector that produced them.3 *4 *   set -a; source .env; set +a5 *   node node_modules/tsx/dist/cli.mjs scripts/quality-facilities.ts            # report6 *   node node_modules/tsx/dist/cli.mjs scripts/quality-facilities.ts --apply    # apply the safe repairs (see below)7 *8 * Checks: single-facility MW > 1 000 without a campus designation · building_sqm > 500 000 · opened_on < 1980 from a9 * non-Wikidata source · coordinates outside the stated country (Natural Earth polygons, `countryFromPoint`) ·10 * names that are page / article titles or another kind of business · confidence inconsistent with the source mix.11 * `--apply` only performs repairs whose provenance is kept: drops pre-1980 OSM opening dates from the column, drops12 * coordinates that fall in another country (geo_precision → unknown), and recomputes confidence/completeness.13 */14import { closeDb, getDb, sql } from "@dci/db";15import { TITLE_LIKE_NAME_RE, NOT_A_FACILITY_NAME_RE } from "@dci/connectors";16import { countryFromPoint } from "../apps/worker/src/ingest/country-lookup.js";17import { BORDER_TOLERANT, refreshDerived } from "../apps/worker/src/ingest/facilities.js";1819const APPLY = process.argv.includes("--apply");2021interface F { id: string; name: string; connector: string; kind: string | null; country: string | null; lat: number | null; lng: number | null; precision: string | null; it: number | null; total: number | null; planned: number | null; sqm: number | null; opened: string | null; campus: string | null; confidence: string; sourceCount: number }2223async function main(): Promise<void> {24  const db = getDb();25  const rows = await db.execute(sql`26    select f.id, f.name, f.country_iso2, f.lat, f.lng, f.geo_precision, f.it_capacity_mw, f.total_power_mw, f.planned_power_mw, f.building_sqm, f.opened_on, f.confidence, f.source_count, c.name as campus,27      (select k.connector_id from entity_keys k where k.entity_id = f.id and k.entity_type = 'facility' order by k.created_at limit 1) as connector,28      (select string_agg(distinct s.kind::text, ',') from provenance p join sources s on s.id = p.source_id where p.entity_type = 'facility' and p.entity_id = f.id and p.is_current) as kinds29    from facilities f left join campuses c on c.id = f.campus_id where f.merged_into is null`);30  const fs: F[] = rows.map((r) => ({ id: String(r.id), name: String(r.name), connector: String(r.connector ?? "-"), kind: r.kinds == null ? null : String(r.kinds), country: r.country_iso2 == null ? null : String(r.country_iso2), lat: r.lat == null ? null : Number(r.lat), lng: r.lng == null ? null : Number(r.lng), precision: r.geo_precision == null ? null : String(r.geo_precision), it: r.it_capacity_mw == null ? null : Number(r.it_capacity_mw), total: r.total_power_mw == null ? null : Number(r.total_power_mw), planned: r.planned_power_mw == null ? null : Number(r.planned_power_mw), sqm: r.building_sqm == null ? null : Number(r.building_sqm), opened: r.opened_on == null ? null : String(r.opened_on), campus: r.campus == null ? null : String(r.campus), confidence: String(r.confidence), sourceCount: Number(r.source_count ?? 0) }));31  console.log(`live facilities: ${fs.length}`);3233  const groups = new Map<string, Array<{ f: F; detail: string }>>();34  const flag = (rule: string, f: F, detail: string) => (groups.get(rule) ?? groups.set(rule, []).get(rule)!).push({ f, detail });35  const geoDrops: F[] = [];36  const openedDrops: F[] = [];37  for (const f of fs) {38    const isCampus = /\b(campus|park|cluster|hub|complex)\b/i.test(`${f.name} ${f.campus ?? ""}`);39    for (const [k, v] of [["it_capacity_mw", f.it], ["total_power_mw", f.total]] as Array<[string, number | null]>) if (v != null && v > 1000 && !isCampus) flag("mw>1000 single facility", f, `${k}=${v}`);40    if (f.planned != null && f.planned > 5000 && !isCampus) flag("planned>5000 single facility", f, `planned=${f.planned}`);41    if (f.sqm != null && f.sqm > 500_000) flag("building_sqm>500000", f, `sqm=${f.sqm}`);42    if (f.opened && Number(f.opened.slice(0, 4)) < 1980 && f.connector !== "wikidata") { flag("opened_on<1980 non-wikidata", f, `opened=${f.opened}`); openedDrops.push(f); }43    if (f.lat != null && f.lng != null && f.country && f.precision !== "metro") {44      const pc = countryFromPoint(f.lat, f.lng);45      if (pc && pc !== f.country && !BORDER_TOLERANT.has(`${f.country}:${pc}`)) { flag("coordinates outside stated country", f, `stated=${f.country} point=${pc} (${f.lat},${f.lng}) ${f.precision}`); geoDrops.push(f); }46    }47    if (TITLE_LIKE_NAME_RE.test(f.name)) flag("name looks like a page title", f, "");48    if (NOT_A_FACILITY_NAME_RE.test(f.name)) flag("name describes another business", f, "");49    const kinds = new Set((f.kind ?? "").split(",").filter(Boolean));50    const authoritative = ["operator", "government", "filing", "utility", "cloud_provider"].some((k) => kinds.has(k));51    if ((f.confidence === "high" || f.confidence === "verified") && !authoritative) flag("confidence high without an authoritative source", f, `kinds=${f.kind}`);52    if (f.sourceCount <= 1 && ["community", "secondary", "news"].some((k) => kinds.has(k)) && kinds.size === 1 && f.confidence !== "unverified" && f.confidence !== "estimated") flag("single community source not unverified", f, `confidence=${f.confidence}`);53  }54  for (const [rule, items] of [...groups].sort((a, b) => b[1].length - a[1].length)) {55    const byConnector = new Map<string, number>();56    for (const { f } of items) byConnector.set(f.connector, (byConnector.get(f.connector) ?? 0) + 1);57    console.log(`\n== ${rule}: ${items.length}  (${[...byConnector].map(([c, n]) => `${c} ${n}`).join(", ")})`);58    for (const { f, detail } of items.slice(0, 25)) console.log(`   ${f.connector.padEnd(18)} ${f.name.slice(0, 60).padEnd(60)} ${detail}`);59    if (items.length > 25) console.log(`   … ${items.length - 25} more`);60  }6162  if (!APPLY) { console.log("\n(dry run — pass --apply to drop pre-1980 non-Wikidata opening dates and out-of-country coordinates, then recompute confidence)"); return; }63  let n = 0;64  for (const f of openedDrops) { await db.execute(sql`update facilities set opened_on = null, updated_at = now() where id = ${f.id}`); n++; }65  for (const f of geoDrops) { await db.execute(sql`update facilities set lat = null, lng = null, geo_precision = 'unknown', geo_source = null, geohash = null, updated_at = now() where id = ${f.id}`); n++; }66  console.log(`\nrepaired ${n} columns; recomputing confidence / completeness for ${fs.length} facilities…`);67  let i = 0;68  for (const f of fs) { await db.transaction(async (tx) => refreshDerived(tx, f.id)); if (++i % 500 === 0) console.log(`  ${i}`); }69  console.log("done");70}7172main().catch((e) => { console.error(e); process.exitCode = 1; }).finally(() => closeDb());73