spb/datacenterindex

1/**2 * Repair facilities that swallowed several connector keys through a shared, non-identifying external id3 * (`operator_wikidata`, `google_location`, `investment_currency`…): 121 AWS OpenStreetMap features folded into one4 * "Amazon Web Services" row, 28 Meta sites into "Meta Cheyenne Data Center", Google's Texas sites into Wilbarger County.5 *6 *   set -a; source .env; set +a7 *   node node_modules/tsx/dist/cli.mjs scripts/quality-unfold.ts                 # report8 *   node node_modules/tsx/dist/cli.mjs scripts/quality-unfold.ts --apply         # detach the extra keys9 *   then: pnpm dci run <connector> --task reprocess    (re-extracts from archived bodies → the detached keys become facilities again)10 *11 * For each (facility, connector) holding > 1 key the script keeps the key that names the facility (the `osm`12 * external id for OSM, the key slug closest to the current name otherwise), deletes the other keys, the connector's13 * aliases (all renames from the fold) and the connector's provenance rows (restored by the reprocess), and clears the14 * facility's external ids. Nothing else is touched; reprocessing recreates the detached facilities through the normal15 * reconciliation path, which now only links on identifying external ids.16 */17import { closeDb, getDb, sql } from "@dci/db";18import { normalizeName } from "@dci/core";19import { refreshDerived } from "../apps/worker/src/ingest/facilities.js";2021const APPLY = process.argv.includes("--apply");22const ONLY = process.argv.includes("--connector") ? process.argv[process.argv.indexOf("--connector") + 1] : null;2324async function main(): Promise<void> {25  const db = getDb();26  const rows = await db.execute(sql`27    select f.id, f.name, f.external_ids, k.connector_id, (select s.id from sources s where s.connector_id = k.connector_id limit 1) as source_id, array_agg(k.key order by k.key) as keys28    from entity_keys k join facilities f on f.id = k.entity_id29    where k.entity_type = 'facility' and f.merged_into is null ${ONLY ? sql`and k.connector_id = ${ONLY}` : sql``}30    group by f.id, f.name, f.external_ids, k.connector_id having count(*) > 131    order by count(*) desc`);32  console.log(`facilities holding several keys of one connector: ${rows.length}`);33  const perConnector = new Map<string, { facilities: number; extraKeys: number }>();34  let detached = 0;35  for (const r of rows) {36    const id = String(r.id), name = String(r.name), connector = String(r.connector_id);37    const keys = r.keys as string[];38    const ext = (r.external_ids as Record<string, string | number>) ?? {};39    // the key that names this facility40    let keep: string | undefined;41    if (ext.osm) keep = keys.find((k) => k === `osm:${ext.osm}`);42    if (!keep) {43      const nameTokens = new Set(normalizeName(name).split(" ").filter(Boolean));44      let best = -1;45      for (const k of keys) {46        const slug = k.split(":").pop() ?? "";47        const toks = slug.split("-").filter(Boolean);48        const overlap = toks.filter((t) => nameTokens.has(t)).length;49        if (overlap > best) { best = overlap; keep = k; }50      }51    }52    keep ??= keys[0]!;53    const extra = keys.filter((k) => k !== keep);54    const stat = perConnector.get(connector) ?? { facilities: 0, extraKeys: 0 };55    stat.facilities++; stat.extraKeys += extra.length; perConnector.set(connector, stat);56    console.log(`${connector.padEnd(18)} ${name} — ${keys.length} keys, keep ${keep}, detach ${extra.length}`);57    if (!APPLY) continue;58    await db.transaction(async (tx) => {59      await tx.execute(sql`delete from entity_keys where entity_type = 'facility' and entity_id = ${id} and connector_id = ${connector} and key <> ${keep}`);60      if (r.source_id != null) await tx.execute(sql`delete from facility_aliases where facility_id = ${id} and source_id = ${String(r.source_id)}`);61      await tx.execute(sql`delete from provenance where entity_type = 'facility' and entity_id = ${id} and connector_id = ${connector}`);62      await tx.execute(sql`update facilities set external_ids = '{}'::jsonb, updated_at = now() where id = ${id}`);63      await refreshDerived(tx, id);64    });65    detached += extra.length;66  }67  console.log("\nper connector:");68  for (const [c, s] of perConnector) console.log(`  ${c.padEnd(18)} facilities ${s.facilities}, keys to detach ${s.extraKeys}`);69  console.log(APPLY ? `\ndetached ${detached} keys — now run: pnpm dci run <connector> --task reprocess for ${[...perConnector.keys()].join(", ")}` : "\n(dry run — pass --apply to detach)");70}7172main().catch((e) => { console.error(e); process.exitCode = 1; }).finally(() => closeDb());73