spb/datacenterindex

1/**2 * Coverage report: how much of the index is actually known, per country / metro / operator / field / source kind.3 * Containment-aware (campus rows with buildings are not counted). Shares are 0..1.4 */5import type { CoverageReport, CoverageRow, SourceCoverage } from "@dci/core";6import { authorityTier } from "@dci/core";7import { pg, facilityView, countedAgg, hasMwAgg, projectLive, type Fragment, type Sql } from "../lib/sql.js";8import { int, iso, reqStr, str, type Row } from "../lib/rows.js";9import { asSourceKind, share } from "../lib/dto.js";10import { coverageRow, scopeFor } from "../lib/pipeline.js";1112const PRIMARY_KINDS = ["operator", "government", "filing", "utility", "cloud_provider", "registry"];1314/** Grouped version of lib/pipeline coverageRow: one row per dimension value. */15function coverageCols(sql: Sql): Fragment {16  const counted = countedAgg(sql), hasMw = hasMwAgg(sql);17  return sql`18    count(*) filter (where ${counted})::int as n,19    count(*) filter (where ${counted} and ${hasMw})::int as with_mw,20    count(*) filter (where ${counted} and f.operator_id is not null)::int as with_op,21    count(*) filter (where ${counted} and f.lat is not null and f.geo_precision in ('exact', 'parcel', 'street'))::int as precise,22    count(*) filter (where ${counted} and f.lat is not null)::int as any_loc,23    count(*) filter (where ${counted} and f.status <> 'unknown')::int as with_status,24    count(*) filter (where ${counted} and f.opened_on ~ '^\\d{4}')::int as with_open,25    count(*) filter (where ${counted} and f.source_count >= 2)::int as multi,26    count(*) filter (where ${counted} and (coalesce(f.carriers_count, 0) > 0 or coalesce(f.ixp_count, 0) > 0 or exists (select 1 from facility_tenants t where t.facility_id = f.id) or exists (select 1 from facility_ixps x where x.facility_id = f.id)))::int as conn,27    count(*) filter (where ${counted} and exists (select 1 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 and s.kind = any(${PRIMARY_KINDS})))::int as primary_src`;28}2930function toRow(r: Row, projects: { n: number; loc: number }): CoverageRow {31  const n = int(r.n);32  return {33    key: reqStr(r.key), name: reqStr(r.name), slug: reqStr(r.slug),34    facilities: n,35    capacityCoverage: share(int(r.with_mw), n),36    operatorCoverage: share(int(r.with_op), n),37    preciseLocationCoverage: share(int(r.precise), n),38    anyLocationCoverage: share(int(r.any_loc), n),39    statusCoverage: share(int(r.with_status), n),40    openingDateCoverage: share(int(r.with_open), n),41    multiSourceCoverage: share(int(r.multi), n),42    projects: projects.n,43    projectsWithLocation: projects.loc,44    connectivityCoverage: share(int(r.conn), n),45    primarySourceShare: share(int(r.primary_src), n),46  };47}4849async function projectCounts(sql: Sql, col: "country_iso2" | "metro_id" | "operator_id"): Promise<Map<string, { n: number; loc: number }>> {50  const rows = await sql<Row[]>`select p.${sql(col)} as key, count(*)::int as n, count(*) filter (where p.lat is not null)::int as loc from projects p where ${projectLive(sql)} and p.${sql(col)} is not null group by 1`;51  return new Map(rows.map((r) => [reqStr(r.key), { n: int(r.n), loc: int(r.loc) }]));52}5354export const COVERAGE_METHODOLOGY = "Shares of counted facilities (campus rows with building rows excluded; merged duplicates excluded) with each field known. capacityCoverage counts a building covered by its campus figure as covered. Precise location = exact / parcel / street. Primary sources = operator, government, filing, utility, cloud provider, registry. Nothing is extrapolated: a low share means the index does not know, not that the value is zero.";5556export async function coverageReport(): Promise<CoverageReport> {57  const sql = pg();58  const [global, countries, metros, operators, pc, pm, po, fields, bySourceKind] = await Promise.all([59    coverageRow("global", "Global", "global", scopeFor(sql, {})),60    sql<Row[]>`select c.iso2 as key, c.name, c.slug, ${coverageCols(sql)} from ${facilityView(sql)} f join countries c on c.iso2 = f.country_iso2 group by c.iso2, c.name, c.slug having count(*) filter (where ${countedAgg(sql)}) >= 1 order by n desc, c.name`,61    sql<Row[]>`select m.id as key, m.name, m.slug, ${coverageCols(sql)} from ${facilityView(sql)} f join metros m on m.id = f.metro_id group by m.id, m.name, m.slug having count(*) filter (where ${countedAgg(sql)}) >= 1 order by n desc, m.name`,62    sql<Row[]>`select o.id as key, o.name, o.slug, ${coverageCols(sql)} from ${facilityView(sql)} f join operators o on o.id = f.operator_id group by o.id, o.name, o.slug having count(*) filter (where ${countedAgg(sql)}) >= 5 order by n desc, o.name`,63    projectCounts(sql, "country_iso2"),64    projectCounts(sql, "metro_id"),65    projectCounts(sql, "operator_id"),66    sql<Row[]>`select count(*)::int as n,67        count(*) filter (where f.operator_id is not null)::int as operator,68        count(*) filter (where f.lat is not null)::int as coordinates,69        count(*) filter (where f.lat is not null and f.geo_precision in ('exact', 'parcel', 'street'))::int as precise_coordinates,70        count(*) filter (where f.status <> 'unknown')::int as status,71        count(*) filter (where f.facility_type <> 'unknown')::int as facility_type,72        count(*) filter (where f.it_capacity_mw is not null)::int as it_capacity_mw,73        count(*) filter (where f.total_power_mw is not null)::int as total_power_mw,74        count(*) filter (where f.planned_power_mw is not null)::int as planned_power_mw,75        count(*) filter (where f.opened_on ~ '^\\d{4}')::int as opened_on,76        count(*) filter (where f.address is not null)::int as address,77        count(*) filter (where coalesce(f.carriers_count, 0) > 0 or coalesce(f.ixp_count, 0) > 0 or exists (select 1 from facility_tenants t where t.facility_id = f.id) or exists (select 1 from facility_ixps x where x.facility_id = f.id))::int as connectivity,78        count(*) filter (where f.website is not null)::int as website,79        count(*) filter (where f.description is not null)::int as description,80        count(*) filter (where f.tier is not null)::int as tier81      from ${facilityView(sql)} f where ${countedAgg(sql)}`,82    sql<Row[]>`select s.kind, count(distinct p.entity_id) filter (where p.entity_type = 'facility')::int as facilities, count(*)::int as fields from provenance p join sources s on s.id = p.source_id where p.is_current group by s.kind order by facilities desc`,83  ]);84  const fr = fields[0] ?? {};85  const total = int(fr.n);86  const FIELDS: Array<[string, string]> = [["operator", "Operator"], ["coordinates", "Coordinates (any precision)"], ["precise_coordinates", "Precise coordinates (exact / parcel / street)"], ["status", "Lifecycle status"], ["facility_type", "Facility type"], ["it_capacity_mw", "IT capacity (MW)"], ["total_power_mw", "Total power (MW)"], ["planned_power_mw", "Planned power (MW)"], ["opened_on", "Opening date"], ["address", "Street address"], ["connectivity", "Carriers / IXPs"], ["website", "Website"], ["description", "Description"], ["tier", "Tier / certification"]];87  return {88    global,89    countries: countries.map((r) => toRow(r, pc.get(reqStr(r.key)) ?? { n: 0, loc: 0 })),90    metros: metros.map((r) => toRow(r, pm.get(reqStr(r.key)) ?? { n: 0, loc: 0 })),91    operators: operators.map((r) => toRow(r, po.get(reqStr(r.key)) ?? { n: 0, loc: 0 })),92    fields: FIELDS.map(([field, label]) => ({ field, label, coverage: share(int(fr[field]), total), count: int(fr[field]) })),93    bySourceKind: bySourceKind.map((r) => ({ kind: asSourceKind(r.kind), facilities: int(r.facilities), fields: int(r.fields) })),94    generatedAt: new Date().toISOString(),95  };96}9798export async function sourceCoverage(): Promise<SourceCoverage[]> {99  const sql = pg();100  const rows = await sql<Row[]>`101    with cur as (select source_id, entity_type, entity_id from provenance where is_current),102    per_entity as (select entity_type, entity_id, count(distinct source_id) as sources from cur group by 1, 2)103    select s.id, s.name, s.kind, s.license, s.redistribution, s.connector_id,104      (select count(distinct (c.entity_type, c.entity_id)) from cur c where c.source_id = s.id)::int as records,105      (select count(*) from cur c where c.source_id = s.id)::int as fields,106      (select count(distinct (c.entity_type, c.entity_id)) from cur c join per_entity pe on pe.entity_type = c.entity_type and pe.entity_id = c.entity_id where c.source_id = s.id and pe.sources = 1)::int as unique_records,107      (select max(r.finished_at) from connector_runs r where r.connector_id = s.connector_id and r.status in ('ok', 'partial')) as last_ok,108      (select count(*) from connector_runs r where r.connector_id = s.connector_id and r.started_at >= now() - interval '30 days' and r.status <> 'running')::int as runs30,109      (select count(*) from connector_runs r where r.connector_id = s.connector_id and r.started_at >= now() - interval '30 days' and r.status in ('failed', 'aborted'))::int as failed30110    from sources s order by records desc, s.name`;111  return rows.map((r) => {112    const lastOk = iso(r.last_ok);113    const runs = int(r.runs30);114    return {115      id: reqStr(r.id), name: reqStr(r.name), kind: asSourceKind(r.kind),116      recordsContributed: int(r.records), fieldsContributed: int(r.fields), uniqueRecords: int(r.unique_records),117      lastSuccessfulCrawl: lastOk,118      freshnessDays: lastOk ? Math.max(0, Math.round((Date.now() - new Date(lastOk).getTime()) / 86_400_000)) : null,119      authority: authorityTier({ field: "identity", sourceKind: str(r.kind) }),120      failureRate: runs ? Math.round((int(r.failed30) / runs) * 1000) / 1000 : null,121      license: str(r.license), redistribution: str(r.redistribution),122    };123  });124}125