spb/datacenterindex

1/**2 * One-shot data repair after the claim-first upgrade (safe to re-run; every step is idempotent and reversible —3 * projects are hidden, never deleted; figures are nulled only when no site-scoped current claim backs them and the4 * old value stays in provenance).5 *6 *   set -a; source .env; set +a7 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts                       # report only8 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --hide-false-positives  # hide vetoed projects9 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --null-unbacked-mw      # after `dci reprocess … --stale`10 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --fix-slugs             # non-Latin operator slugs ("item")11 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --link-campuses         # OSM buildings inside a campus footprint12 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --hide-unconfirmed     # after reprocess: source re-extracted, no project record / no claim → hide13 *   node node_modules/tsx/dist/cli.mjs scripts/quality-apply.ts --all14 */15import { closeDb, getDb, sql } from "@dci/db";16import { classifyProjectEvent, PHYSICAL_CLASSES, slugify, sha256 } from "@dci/core";17import { isNonProjectTitle, OPENING_TITLE_RE } from "../apps/worker/src/connectors/news/extract-project.js";18import { hideProject } from "../apps/worker/src/ingest/projects.js";1920const args = process.argv.slice(2);21const ALL = args.includes("--all");22const has = (f: string) => ALL || args.includes(f);2324async function hideFalsePositives(): Promise<void> {25  const db = getDb();26  const rows = await db.execute<{ id: string; name: string; slug: string; planned_mw: number | null; project_class: string | null; status: string; title: string | null; source_kind: string | null; summary: string | null; operator_id: string | null; city: string | null; country_iso2: string | null }>(sql`27    select p.id, p.name, p.slug, p.planned_mw, p.project_class, p.status, n.title, n.summary, p.operator_id, p.city, p.country_iso2, c.kind as source_kind28    from projects p left join news_items n on n.url = p.source_url left join connectors c on c.id = n.connector_id29    where p.merged_into is null and not p.hidden`);30  let hidden = 0, kept = 0;31  const reasons: Record<string, number> = {};32  for (const r of rows) {33    const title = r.title ?? r.name;34    const cls = classifyProjectEvent({ title, lead: r.summary ?? "", hasOperator: !!r.operator_id, hasLocation: !!(r.city || r.country_iso2), hasExplicitName: !/ \| |—| - /.test(r.name) && r.name.length < 90, status: r.status, trustLead: ["operator", "cloud_provider", "government", "utility"].includes(r.source_kind ?? "") });35    let reason: string | null = null;36    if (!PHYSICAL_CLASSES.has(cls.class)) reason = `class:${cls.class}`;37    else if (isNonProjectTitle(title)) reason = "veto:title";38    else if (OPENING_TITLE_RE.test(title) && !/\b(to|will|set to|plans? to|due to|expected to|slated to) (open|be online|go live)\b/i.test(title)) reason = "existing-facility";39    else if (cls.evidence.strength === "none") reason = "evidence:none";40    if (!reason) { kept++; continue; }41    reasons[reason] = (reasons[reason] ?? 0) + 1;42    console.log(`  hide ${r.slug.padEnd(70)} ${String(r.planned_mw ?? "").padStart(7)} MW  ${reason}  «${title.slice(0, 80)}»`);43    if (has("--hide-false-positives")) await db.transaction((tx) => hideProject(tx, r.id, `${reason}: ${title.slice(0, 200)}`, "quality-apply"));44    hidden++;45  }46  console.log(`\nprojects: ${kept} kept · ${hidden} ${has("--hide-false-positives") ? "hidden" : "would be hidden"} · ${JSON.stringify(reasons)}`);47}4849async function nullUnbackedMw(): Promise<void> {50  const db = getDb();51  // projects whose planned_mw / investment is not backed by any current site-scoped claim (after reprocessing) → null52  const mw = await db.execute<{ id: string; slug: string; planned_mw: number }>(sql`53    select p.id, p.slug, p.planned_mw from projects p where p.merged_into is null and p.planned_mw is not null54      and exists (select 1 from claims c where c.subject_type = 'project' and c.subject_id = p.id and c.predicate like '%_mw')55      and not exists (select 1 from claims c where c.subject_type = 'project' and c.subject_id = p.id and c.predicate like '%_mw' and c.status = 'current' and c.scope in ('building','facility','campus') and abs(c.value - p.planned_mw) < 0.01)`);56  for (const r of mw) console.log(`  null planned_mw ${r.slug} (${r.planned_mw} MW: only unscoped / rejected claims back it)`);57  const inv = await db.execute<{ id: string; slug: string; investment_usd: number }>(sql`58    select p.id, p.slug, p.investment_usd from projects p where p.merged_into is null and p.investment_usd is not null59      and exists (select 1 from claims c where c.subject_type = 'project' and c.subject_id = p.id and c.predicate like '%_usd')60      and not exists (select 1 from claims c where c.subject_type = 'project' and c.subject_id = p.id and c.predicate = 'project_investment_usd' and c.status = 'current' and c.scope in ('building','facility','campus') and abs(c.value - p.investment_usd) < 1)`);61  for (const r of inv) console.log(`  null investment_usd ${r.slug} ($${(r.investment_usd / 1e9).toFixed(2)}B: only unscoped / deal-value claims back it)`);62  if (has("--null-unbacked-mw")) {63    for (const r of mw) await db.execute(sql`update projects set planned_mw = null, capacity_scope = null, capacity_semantics = null, updated_at = now() where id = ${r.id}`);64    for (const r of inv) await db.execute(sql`update projects set investment_usd = null, investment_scope = null, investment_semantics = null, updated_at = now() where id = ${r.id}`);65  }66  console.log(`\nunbacked figures: ${mw.length} planned_mw · ${inv.length} investment_usd ${has("--null-unbacked-mw") ? "nulled" : "would be nulled"}`);67}6869/**70 * After a full reprocess, a live project whose source document was re-extracted with the current parser but produced71 * no claim at all (not even a status claim) is one the new pipeline no longer recognises as a project — the article72 * was vetoed by class or evidence. Hide it (kept for audit; the news item and its event remain).73 */74async function hideUnconfirmed(): Promise<void> {75  const db = getDb();76  const rows = await db.execute<{ id: string; slug: string; name: string; planned_mw: number | null; url: string; ev: string | null; cur: string | null }>(sql`77    select p.id, p.slug, p.name, p.planned_mw, p.source_url as url, d.extractor_version as ev, c.parser_version as cur78    from projects p79    join documents d on d.canonical_url = p.source_url or d.url = p.source_url80    join connectors c on c.id = d.connector_id81    where p.merged_into is null and not p.hidden and p.project_class is null82      and d.extractor_version = c.parser_version and d.extract_ok83      and not exists (select 1 from claims cl where cl.subject_type = 'project' and cl.subject_id = p.id)84      and p.updated_at < d.updated_at`);85  for (const r of rows) console.log(`  hide ${r.slug.padEnd(70)} ${String(r.planned_mw ?? "").padStart(7)} MW  re-extraction produced no project record  «${r.name.slice(0, 70)}»`);86  if (has("--hide-unconfirmed")) for (const r of rows) await db.transaction((tx) => hideProject(tx, r.id, "re-extraction with the current parser produced no project record (class veto / evidence threshold)", "quality-apply"));87  console.log(`\nunconfirmed projects: ${rows.length} ${has("--hide-unconfirmed") ? "hidden" : "would be hidden"}`);88}8990async function fixSlugs(): Promise<void> {91  const db = getDb();92  const rows = await db.execute<{ id: string; name: string; slug: string; website: string | null; aliases: string[] }>(sql`select id, name, slug, website, aliases from operators where slug ~ '^(item|op|operator)(-\\d+)?$' or slug = ''`);93  for (const r of rows) {94    const latin = (r.aliases ?? []).find((a) => /^[A-Za-z0-9 .&'-]+$/.test(a) && slugify(a)) ?? (r.website ? r.website.replace(/^https?:\/\/(www\.)?/, "").split("/")[0]?.split(".")[0] : null);95    const base = latin ? slugify(latin) : `operator-${sha256(r.name).slice(0, 8)}`;96    let slug = base, i = 2;97    while ((await db.execute(sql`select 1 from operators where slug = ${slug} and id <> ${r.id}`)).length) slug = `${base}-${i++}`;98    console.log(`  slug ${r.slug} → ${slug}  (${r.name})`);99    if (has("--fix-slugs")) await db.execute(sql`update operators set slug = ${slug}, updated_at = now() where id = ${r.id}`);100  }101  console.log(`\nslugs: ${rows.length} ${has("--fix-slugs") ? "fixed" : "would be fixed"}`);102}103104async function linkCampuses(): Promise<void> {105  const db = getDb();106  // Containment, not proximity: a building joins a campus only when it is within 400 m AND (same non-null operator, or a107  // nameless / operator-less record whose name shares a distinctive token with the campus name). Nearest campus wins.108  const rows = await db.execute<{ campus_id: string; campus: string; building_id: string; building: string; m: number; same_op: boolean; campus_operator: string | null }>(sql`109    with pairs as (110      select c.id as campus_id, c.name as campus, b.id as building_id, b.name as building,111        6371000 * acos(least(1, cos(radians(c.lat)) * cos(radians(b.lat)) * cos(radians(b.lng - c.lng)) + sin(radians(c.lat)) * sin(radians(b.lat)))) as m,112        (b.operator_id is not null and b.operator_id = c.operator_id) as same_op,113        (select o.name from operators o where o.id = c.operator_id) as campus_operator,114        row_number() over (partition by b.id order by 6371000 * acos(least(1, cos(radians(c.lat)) * cos(radians(b.lat)) * cos(radians(b.lng - c.lng)) + sin(radians(c.lat)) * sin(radians(b.lat))))) as rn115      from facilities c join facilities b on b.id <> c.id and b.merged_into is null and b.parent_facility_id is null and b.lat is not null116        and abs(b.lat - c.lat) < 0.005 and abs(b.lng - c.lng) < 0.007117        and b.name !~* '(campus|park|complex|hub|cluster|mega ?site)'118      where c.merged_into is null and c.lat is not null and c.name ~* '(campus|park|complex|hub|cluster|mega ?site)')119    select campus_id, campus, building_id, building, round(m) as m, same_op, campus_operator from pairs where rn = 1 and m <= 400 order by campus, m`);120  const GENERIC = new Set(["data", "center", "centre", "campus", "park", "complex", "hub", "cluster", "site", "mega", "the", "of", "at", "dc", "datacenter", "datacentre", "building", "and", "&", "inc", "ltd", "gmbh", "sa", "llc", "co", "corp", "global", "digital", "cloud", "services", "technologies", "technology", "group", "holdings"]);121  const tokens = (s: string) => new Set(s.toLowerCase().normalize("NFKD").replace(/[^a-z0-9 ]/g, " ").split(/\s+/).filter((t) => t.length >= 3 && !GENERIC.has(t)));122  // an operator-less record (OSM building tagged only with a name) joins when its name carries the campus operator's name123  const keep = rows.filter((r) => { if (r.same_op) return true; if (!r.campus_operator) return false; const a = tokens(r.building), b = tokens(r.campus_operator); return b.size > 0 && [...b].every((t) => a.has(t)); });124  for (const r of keep) console.log(`  ${r.building.padEnd(50)} → building of ${r.campus} (${r.m} m${r.same_op ? ", same operator" : ", operator name in building name"})`);125  if (has("--link-campuses")) {126    for (const r of keep) await db.execute(sql`update facilities set parent_facility_id = ${r.campus_id}, record_scope = 'building', updated_at = now() where id = ${r.building_id} and parent_facility_id is null`);127    const campusIds = [...new Set(keep.map((r) => r.campus_id))];128    if (campusIds.length) await db.execute(sql`update facilities set record_scope = 'campus', updated_at = now() where id in ${campusIds}`);129  }130  console.log(`\ncampus links: ${keep.length} ${has("--link-campuses") ? "applied" : "would be applied"} (${rows.length - keep.length} nearby pairs rejected: different or unknown operator)`);131}132133async function main(): Promise<void> {134  console.log("── projects: announcement class veto");135  await hideFalsePositives();136  console.log("\n── unbacked figures");137  await nullUnbackedMw();138  console.log("\n── unconfirmed after re-extraction");139  await hideUnconfirmed();140  console.log("\n── operator slugs");141  await fixSlugs();142  console.log("\n── campus containment");143  await linkCampuses();144  await closeDb();145}146main().catch(async (e) => { console.error(e); await closeDb(); process.exit(1); });147