/** * Rankings + summary stats. `computeRankings()` writes one `rankings` row per key (previous rows → is_current=false); * `refreshStats()` refreshes countries/metros/operators `stats` jsonb for fast API summaries. * Methodology texts are public (docs/RANKINGS.md mirrors them). */ import { getDb, sql, type Db } from "@dci/db"; import { newId, type Ranking, type RankingRow } from "@dci/core"; const OPERATIONAL = ["operational", "partially_operational", "expansion"]; const PLANNED = ["rumored", "proposed", "announced", "permitting", "approved", "delayed"]; /** Coverage / size floor for MW-based rankings (per-row `coverage` is exposed regardless). */ export const MIN_MW_COVERAGE = 0.35; export const MIN_FACILITIES = 5; export interface Aggregate { id: string; slug: string; name: string; countryIso2: string | null; facilities: number; operational: number; construction: number; planned: number; knownMw: number; constructionMw: number; plannedMw: number; hyperscale: number; ai: number; withMw: number; openedRecent: number; operators: number; countries: number; metros: number; cloudRegions: number; projects: number; /** planned MW published on project records (pipeline statuses) — kept apart from facility figures */ projectPlannedMw: number; projectConstructionMw: number; expansionMw: number; aiConfirmed: number; ixps: number; population: number | null; gdpUsd: number | null; coverage: number; } const num = (v: unknown) => (v == null ? 0 : Number(v)); const numOrNull = (v: unknown) => (v == null ? null : Number(v)); function recentYear(): string { return String(new Date().getUTCFullYear() - 3); } /** * Containment-aware facility view used by every aggregate: * - `agg_mw` is a facility's own known MW (IT, else total) unless it is a campus whose buildings publish their own * figures (then the campus row contributes nothing — the buildings do); a building without a figure under a campus * with one contributes nothing either (the campus figure already covers it). Never both. * - `counted` excludes campus rows that have building rows (a campus with 4 buildings is 4 facilities, not 5) and * hidden / merged rows. */ export const FACILITY_VIEW = sql` select f.*, exists (select 1 from facilities c where c.parent_facility_id = f.id and c.merged_into is null) as has_children, exists (select 1 from facilities c where c.parent_facility_id = f.id and c.merged_into is null and coalesce(c.it_capacity_mw, c.total_power_mw, c.planned_power_mw) is not null) as children_have_mw, (f.parent_facility_id is not null and exists (select 1 from facilities pp where pp.id = f.parent_facility_id and pp.merged_into is null and coalesce(pp.it_capacity_mw, pp.total_power_mw, pp.planned_power_mw) is not null) and coalesce(f.it_capacity_mw, f.total_power_mw, f.planned_power_mw) is null) as covered_by_parent from facilities f where f.merged_into is null`; /** MW expressions honouring containment (campus vs buildings never double count). */ const KNOWN_MW_EXPR = sql`case when f.children_have_mw then null else coalesce(f.it_capacity_mw, f.total_power_mw) end`; const PIPELINE_MW_EXPR = sql`case when f.children_have_mw then null else coalesce(f.planned_power_mw, f.it_capacity_mw, f.total_power_mw) end`; const COUNTED = sql`(not f.has_children)`; const FACILITY_AGG = (dim: ReturnType) => sql` count(f.id) filter (where ${COUNTED})::int as facilities, count(f.id) filter (where ${COUNTED} and f.status in ${OPERATIONAL})::int as operational, count(f.id) filter (where ${COUNTED} and f.status = 'under_construction')::int as construction, count(f.id) filter (where ${COUNTED} and f.status in ${PLANNED})::int as planned, coalesce(sum(${KNOWN_MW_EXPR}) filter (where f.status in ${OPERATIONAL}), 0) as known_mw, coalesce(sum(${PIPELINE_MW_EXPR}) filter (where f.status = 'under_construction'), 0) as construction_mw, coalesce(sum(${PIPELINE_MW_EXPR}) filter (where f.status in ${PLANNED}), 0) as planned_mw, coalesce(sum(case when f.children_have_mw then null else f.planned_power_mw end) filter (where f.status = 'expansion'), 0) as expansion_mw, count(f.id) filter (where ${COUNTED} and f.is_hyperscale)::int as hyperscale, count(f.id) filter (where ${COUNTED} and f.is_ai)::int as ai, count(f.id) filter (where ${COUNTED} and f.ai_evidence = 'confirmed')::int as ai_confirmed, count(f.id) filter (where ${COUNTED} and (coalesce(f.it_capacity_mw, f.total_power_mw, f.planned_power_mw) is not null or f.covered_by_parent))::int as with_mw, count(f.id) filter (where ${COUNTED} and f.opened_on ~ '^\d{4}' and substring(f.opened_on from 1 for 4) >= ${recentYear()} and substring(f.opened_on from 1 for 4) <= ${String(new Date().getUTCFullYear())})::int as opened_recent, count(distinct f.operator_id)::int as operators, count(distinct f.country_iso2)::int as countries, count(distinct f.metro_id)::int as metros, ${dim}`; function toAgg(r: Record): Aggregate { const facilities = num(r.facilities); return { id: String(r.id), slug: String(r.slug), name: String(r.name), countryIso2: r.country_iso2 == null ? null : String(r.country_iso2), facilities, operational: num(r.operational), construction: num(r.construction), planned: num(r.planned), knownMw: num(r.known_mw), constructionMw: num(r.construction_mw), plannedMw: num(r.planned_mw), hyperscale: num(r.hyperscale), ai: num(r.ai), withMw: num(r.with_mw), openedRecent: num(r.opened_recent), operators: num(r.operators), countries: num(r.countries), metros: num(r.metros), cloudRegions: num(r.cloud_regions), projects: num(r.projects), projectPlannedMw: num(r.project_planned_mw), projectConstructionMw: num(r.project_construction_mw), expansionMw: num(r.expansion_mw), aiConfirmed: num(r.ai_confirmed), ixps: num(r.ixps), population: numOrNull(r.population), gdpUsd: numOrNull(r.gdp_usd), coverage: facilities ? Math.round((num(r.with_mw) / facilities) * 1000) / 1000 : 0, }; } export async function aggregateCountries(db: Db): Promise { const rows = await db.execute(sql` select c.iso2 as id, c.slug, c.name, c.iso2 as country_iso2, c.population, c.gdp_usd, (select count(*) from cloud_regions cr where cr.country_iso2 = c.iso2 and cr.status <> 'retired')::int as cloud_regions, (select count(*) from projects p where p.country_iso2 = c.iso2 and p.merged_into is null and not p.hidden)::int as projects, (select coalesce(sum(p.planned_mw), 0) from projects p where p.country_iso2 = c.iso2 and p.merged_into is null and not p.hidden and p.status in ${PLANNED})::double precision as project_planned_mw, (select coalesce(sum(p.planned_mw), 0) from projects p where p.country_iso2 = c.iso2 and p.merged_into is null and not p.hidden and p.status = 'under_construction')::double precision as project_construction_mw, (select count(*) from ixps x where x.country_iso2 = c.iso2)::int as ixps, ${FACILITY_AGG(sql`1 as _`)} from countries c left join (${FACILITY_VIEW}) f on f.country_iso2 = c.iso2 group by c.iso2, c.slug, c.name, c.population, c.gdp_usd`); return rows.map(toAgg); } export async function aggregateMetros(db: Db): Promise { const rows = await db.execute(sql` select m.id, m.slug, m.name, m.country_iso2, null::bigint as population, null::double precision as gdp_usd, (select count(*) from cloud_regions cr where cr.metro_id = m.id and cr.status <> 'retired')::int as cloud_regions, (select count(*) from projects p where p.metro_id = m.id and p.merged_into is null and not p.hidden)::int as projects, (select coalesce(sum(p.planned_mw), 0) from projects p where p.metro_id = m.id and p.merged_into is null and not p.hidden and p.status in ${PLANNED})::double precision as project_planned_mw, (select coalesce(sum(p.planned_mw), 0) from projects p where p.metro_id = m.id and p.merged_into is null and not p.hidden and p.status = 'under_construction')::double precision as project_construction_mw, (select count(*) from ixps x where x.metro_id = m.id)::int as ixps, ${FACILITY_AGG(sql`1 as _`)} from metros m left join (${FACILITY_VIEW}) f on f.metro_id = m.id group by m.id, m.slug, m.name, m.country_iso2`); return rows.map(toAgg); } export async function aggregateOperators(db: Db, limit = 5000): Promise { const rows = await db.execute(sql` select o.id, o.slug, o.name, o.hq_country_iso2 as country_iso2, null::bigint as population, null::double precision as gdp_usd, (select count(*) from cloud_regions cr where cr.provider_id = o.id and cr.status <> 'retired')::int as cloud_regions, (select count(*) from projects p where p.operator_id = o.id and p.merged_into is null and not p.hidden)::int as projects, (select coalesce(sum(p.planned_mw), 0) from projects p where p.operator_id = o.id and p.merged_into is null and not p.hidden and p.status in ${PLANNED})::double precision as project_planned_mw, (select coalesce(sum(p.planned_mw), 0) from projects p where p.operator_id = o.id and p.merged_into is null and not p.hidden and p.status = 'under_construction')::double precision as project_construction_mw, 0 as ixps, ${FACILITY_AGG(sql`1 as _`)} from operators o left join (${FACILITY_VIEW}) f on f.operator_id = o.id group by o.id, o.slug, o.name, o.hq_country_iso2 order by count(f.id) desc, o.name asc limit ${limit}`); return rows.map(toAgg); } // --------------------------------------------------------------------------------------------------------------- interface RankingSpec { key: string; scope: Ranking["scope"]; label: string; unit: string; methodology: string; minCoverage?: number; value: (a: Aggregate) => number | null; secondary?: (a: Aggregate) => number | null; eligible?: (a: Aggregate) => boolean; } const mwGate = (a: Aggregate) => a.facilities >= MIN_FACILITIES && a.coverage >= MIN_MW_COVERAGE; const OPERATIONAL_TXT = "operational, partially operational or expanding"; const KNOWN_MW_TXT = `Known MW = sum of IT capacity (or total power when IT capacity is not published) over ${OPERATIONAL_TXT} facilities with a published or filed figure. A campus and its buildings are never both counted: when buildings publish their own figures the campus figure is ignored, otherwise the campus figure stands for its buildings. Estimates are included only when no measured figure exists and are flagged on the facility. Coverage = share of the entity's facilities with any MW figure (a building covered by its campus figure counts as covered). Figures are published lower bounds, never extrapolated.`; const GATE_TXT = `Entities with fewer than ${MIN_FACILITIES} facilities or MW coverage below ${Math.round(MIN_MW_COVERAGE * 100)} % are excluded to avoid ranking on sparse data.`; export const RANKING_SPECS: RankingSpec[] = [ // countries { key: "countries_facilities", scope: "countries", label: "Countries by facility count", unit: "facilities", methodology: "Number of indexed facilities (all statuses, merged duplicates excluded) located in the country.", value: (a) => a.facilities, secondary: (a) => a.knownMw }, { key: "countries_known_mw", scope: "countries", label: "Countries by known operational MW", unit: "MW", methodology: `${KNOWN_MW_TXT} ${GATE_TXT}`, minCoverage: MIN_MW_COVERAGE, value: (a) => a.knownMw, secondary: (a) => a.operational, eligible: mwGate }, { key: "countries_planned_mw", scope: "countries", label: "Countries by planned MW", unit: "MW", methodology: `Sum of planned power (or IT/total power when no planned figure is published) over facilities in planning statuses (rumored, proposed, announced, permitting, approved, delayed). ${GATE_TXT}`, minCoverage: MIN_MW_COVERAGE, value: (a) => a.plannedMw, secondary: (a) => a.planned, eligible: mwGate }, { key: "countries_construction_mw", scope: "countries", label: "Countries by MW under construction", unit: "MW", methodology: `Sum of planned power (or IT/total power) over facilities under construction. ${GATE_TXT}`, minCoverage: MIN_MW_COVERAGE, value: (a) => a.constructionMw, secondary: (a) => a.construction, eligible: mwGate }, { key: "countries_hyperscale", scope: "countries", label: "Countries by hyperscale facilities", unit: "facilities", methodology: "Facilities flagged hyperscale (self-described, or operated by a hyperscaler: AWS, Microsoft, Google, Meta, Oracle, Alibaba Cloud, Tencent Cloud, Apple, IBM, Huawei Cloud).", value: (a) => a.hyperscale, secondary: (a) => a.facilities }, { key: "countries_ai", scope: "countries", label: "Countries by AI facilities", unit: "facilities", methodology: "Facilities flagged as AI/accelerated-computing sites by their operator or a primary source.", value: (a) => a.ai, secondary: (a) => a.facilities }, { key: "countries_cloud_regions", scope: "countries", label: "Countries by cloud regions", unit: "regions", methodology: "Number of public cloud regions (announced or operational, retired excluded) across all indexed providers.", value: (a) => a.cloudRegions, secondary: (a) => a.facilities }, { key: "countries_facilities_per_million", scope: "countries", label: "Facilities per million residents", unit: "per million", methodology: `Facility count divided by population in millions (population from the latest World Bank vintage stored on the country). Countries with fewer than ${MIN_FACILITIES} facilities or no population figure are excluded.`, value: (a) => (a.population && a.population > 0 ? a.facilities / (a.population / 1e6) : null), secondary: (a) => a.facilities, eligible: (a) => a.facilities >= MIN_FACILITIES && !!a.population }, { key: "countries_mw_per_capita", scope: "countries", label: "Known MW per capita", unit: "kW per 1,000 residents", methodology: `Known operational MW × 1,000 ÷ population in thousands (i.e. kW per 1,000 residents). ${KNOWN_MW_TXT} ${GATE_TXT}`, minCoverage: MIN_MW_COVERAGE, value: (a) => (a.population && a.population > 0 ? (a.knownMw * 1e6) / a.population : null), secondary: (a) => a.knownMw, eligible: (a) => mwGate(a) && !!a.population }, { key: "countries_mw_per_gdp", scope: "countries", label: "Known MW per $bn GDP", unit: "MW per $bn", methodology: `Known operational MW divided by GDP in billions of current US dollars (World Bank). ${KNOWN_MW_TXT} ${GATE_TXT}`, minCoverage: MIN_MW_COVERAGE, value: (a) => (a.gdpUsd && a.gdpUsd > 0 ? a.knownMw / (a.gdpUsd / 1e9) : null), secondary: (a) => a.knownMw, eligible: (a) => mwGate(a) && !!a.gdpUsd }, { key: "countries_growth", scope: "countries", label: "Countries by facilities opened in the last 3 years", unit: "facilities", methodology: `Facilities whose opening date (full or partial) falls in ${recentYear()} or later. Facilities without a known opening date are not counted.`, value: (a) => a.openedRecent, secondary: (a) => a.facilities }, // metros { key: "metros_facilities", scope: "metros", label: "Metros by facility count", unit: "facilities", methodology: "Facilities assigned to the metro: within the metro radius of its reference point, or in a town listed as part of the market when no coordinates are known.", value: (a) => a.facilities, secondary: (a) => a.knownMw }, { key: "metros_known_mw", scope: "metros", label: "Metros by known operational MW", unit: "MW", methodology: `${KNOWN_MW_TXT} ${GATE_TXT}`, minCoverage: MIN_MW_COVERAGE, value: (a) => a.knownMw, secondary: (a) => a.operational, eligible: mwGate }, { key: "metros_pipeline_mw", scope: "metros", label: "Metros by pipeline MW", unit: "MW", methodology: `Planned MW plus MW under construction (planned power, or IT/total power when no planned figure exists) over facilities not yet operational, plus the planned power of expanding facilities. ${GATE_TXT}`, minCoverage: MIN_MW_COVERAGE, value: (a) => a.plannedMw + a.constructionMw + a.expansionMw, secondary: (a) => a.planned + a.construction, eligible: mwGate }, { key: "metros_ai", scope: "metros", label: "Metros by AI/HPC facilities", unit: "facilities", methodology: "Facilities whose AI evidence is confirmed or likely (source explicitly describes AI/HPC/accelerated computing or GPU / high-density infrastructure). Keyword mentions alone never qualify.", value: (a) => a.ai, secondary: (a) => a.facilities }, { key: "metros_cloud_regions", scope: "metros", label: "Metros by cloud regions", unit: "regions", methodology: "Public cloud regions associated with the metro (announced or operational).", value: (a) => a.cloudRegions, secondary: (a) => a.facilities }, { key: "metros_projects", scope: "metros", label: "Metros by project count", unit: "projects", methodology: "Tracked infrastructure projects (announced, permitting, approved, under construction, delayed) located in the metro; false positives hidden by review are excluded.", value: (a) => a.projects, secondary: (a) => a.projectPlannedMw + a.projectConstructionMw }, { key: "metros_operators", scope: "metros", label: "Metros by operator count", unit: "operators", methodology: "Distinct operators with at least one facility assigned to the metro.", value: (a) => a.operators, secondary: (a) => a.facilities }, // operators { key: "operators_facilities", scope: "operators", label: "Operators by facility count", unit: "facilities", methodology: "Facilities operated (not merely owned or tenanted) by the operator, subsidiaries folded into the parent brand where the curated alias table says so.", value: (a) => a.facilities, secondary: (a) => a.countries }, { key: "operators_countries", scope: "operators", label: "Operators by country footprint", unit: "countries", methodology: "Distinct countries with at least one facility operated by the operator.", value: (a) => a.countries, secondary: (a) => a.facilities, eligible: (a) => a.facilities > 0 }, { key: "operators_metros", scope: "operators", label: "Operators by metro footprint", unit: "metros", methodology: "Distinct metros with at least one facility operated by the operator (facilities outside any defined metro are not counted).", value: (a) => a.metros, secondary: (a) => a.facilities, eligible: (a) => a.facilities > 0 }, { key: "operators_known_mw", scope: "operators", label: "Operators by known operational MW", unit: "MW", methodology: `${KNOWN_MW_TXT} ${GATE_TXT}`, minCoverage: MIN_MW_COVERAGE, value: (a) => a.knownMw, secondary: (a) => a.operational, eligible: mwGate }, { key: "operators_pipeline_mw", scope: "operators", label: "Operators by pipeline MW", unit: "MW", methodology: `Planned MW plus MW under construction over the operator's facilities not yet operational, plus the planned power of expanding facilities. ${GATE_TXT}`, minCoverage: MIN_MW_COVERAGE, value: (a) => a.plannedMw + a.constructionMw + a.expansionMw, secondary: (a) => a.planned + a.construction, eligible: mwGate }, { key: "operators_ai", scope: "operators", label: "Operators by AI/HPC facilities", unit: "facilities", methodology: "Facilities whose AI evidence is confirmed or likely. Keyword mentions alone never qualify.", value: (a) => a.ai, secondary: (a) => a.facilities }, { key: "operators_projects", scope: "operators", label: "Operators by tracked projects", unit: "projects", methodology: "Infrastructure projects (announced, permitting, approved, under construction, delayed) attributed to the operator; the secondary figure is the published planned MW on those records.", value: (a) => a.projects, secondary: (a) => a.projectPlannedMw + a.projectConstructionMw }, { key: "operators_project_mw", scope: "operators", label: "Operators by published project MW", unit: "MW", methodology: "Sum of planned MW published on the operator's project records (pipeline statuses), site-scoped figures only — company-wide or portfolio totals are stored as claims and never summed here.", value: (a) => a.projectPlannedMw + a.projectConstructionMw, secondary: (a) => a.projects, eligible: (a) => a.projects >= 2 }, { key: "countries_project_mw", scope: "countries", label: "Countries by published project MW", unit: "MW", methodology: "Sum of planned MW published on project records located in the country (pipeline statuses), site-scoped figures only.", value: (a) => a.projectPlannedMw + a.projectConstructionMw, secondary: (a) => a.projects, eligible: (a) => a.projects >= 2 }, { key: "countries_projects", scope: "countries", label: "Countries by tracked projects", unit: "projects", methodology: "Infrastructure projects located in the country (announced, permitting, approved, under construction, delayed).", value: (a) => a.projects, secondary: (a) => a.projectPlannedMw + a.projectConstructionMw }, { key: "countries_ixps", scope: "countries", label: "Countries by internet exchanges", unit: "IXPs", methodology: "Internet exchange points indexed in the country.", value: (a) => a.ixps, secondary: (a) => a.facilities }, ]; const HREF: Record string> = { countries: (s) => `/countries/${s}`, metros: (s) => `/metros/${s}`, operators: (s) => `/operators/${s}`, facilities: (s) => `/facilities/${s}` }; export function buildRows(spec: RankingSpec, aggs: Aggregate[], previous?: Map): RankingRow[] { const rows: RankingRow[] = []; for (const a of aggs) { if (spec.eligible && !spec.eligible(a)) continue; const v = spec.value(a); if (v == null || !Number.isFinite(v) || v <= 0) continue; rows.push({ rank: 0, id: a.id, slug: a.slug, name: a.name, href: HREF[spec.scope](a.slug), value: Math.round(v * 1000) / 1000, secondary: spec.secondary ? spec.secondary(a) : null, countryIso2: a.countryIso2, coverage: a.coverage }); } rows.sort((x, y) => (y.value ?? 0) - (x.value ?? 0) || x.name.localeCompare(y.name)); rows.forEach((r, i) => { r.rank = i + 1; const prev = previous?.get(r.id); r.delta = prev == null ? null : prev - r.rank; // positive = moved up }); return rows; } export interface RankingsResult { rankings: number; rows: number; } export async function computeRankings(db: Db = getDb()): Promise { const [countries, metros, operators] = await Promise.all([aggregateCountries(db), aggregateMetros(db), aggregateOperators(db)]); const byScope: Record = { countries, metros, operators, facilities: [] }; let total = 0; const now = new Date().toISOString(); await db.transaction(async (tx) => { for (const spec of RANKING_SPECS) { const prevRow = (await tx.execute(sql`select rows from rankings where key = ${spec.key} and is_current = true order by computed_at desc limit 1`))[0]; const previous = new Map(); for (const r of ((prevRow?.rows as RankingRow[] | undefined) ?? [])) previous.set(r.id, r.rank); const rows = buildRows(spec, byScope[spec.scope] ?? [], previous); await tx.execute(sql`update rankings set is_current = false where key = ${spec.key} and is_current = true`); await tx.execute(sql`insert into rankings (id, key, scope, label, unit, methodology, min_coverage, rows, total, computed_at, is_current) values (${newId("ranking")}, ${spec.key}, ${spec.scope}, ${spec.label}, ${spec.unit}, ${spec.methodology}, ${spec.minCoverage ?? null}, ${JSON.stringify(rows)}::jsonb, ${rows.length}, ${now}, true)`); // keep the table small: drop superseded snapshots older than 90 days await tx.execute(sql`delete from rankings where key = ${spec.key} and is_current = false and computed_at < now() - interval '90 days'`); total += rows.length; // "ALL OBSERVED" companion for gated rankings: every entity with a published figure, coverage shown per row, never estimated if (spec.minCoverage != null) { const allKey = `${spec.key}:all`; const allSpec: RankingSpec = { ...spec, key: allKey, eligible: (a) => a.facilities > 0, minCoverage: undefined, label: `${spec.label} — all observed`, methodology: `${spec.methodology} This "all observed" variant lists every entity with at least one published figure regardless of coverage; the coverage column shows how incomplete each row is. Nothing is estimated.` }; const prevAll = (await tx.execute(sql`select rows from rankings where key = ${allKey} and is_current = true order by computed_at desc limit 1`))[0]; const prevAllMap = new Map(); for (const r of ((prevAll?.rows as RankingRow[] | undefined) ?? [])) prevAllMap.set(r.id, r.rank); const allRows = buildRows(allSpec, byScope[spec.scope] ?? [], prevAllMap); await tx.execute(sql`update rankings set is_current = false where key = ${allKey} and is_current = true`); await tx.execute(sql`insert into rankings (id, key, scope, label, unit, methodology, min_coverage, rows, total, computed_at, is_current) values (${newId("ranking")}, ${allKey}, ${spec.scope}, ${allSpec.label}, ${spec.unit}, ${allSpec.methodology}, null, ${JSON.stringify(allRows)}::jsonb, ${allRows.length}, ${now}, true)`); await tx.execute(sql`delete from rankings where key = ${allKey} and is_current = false and computed_at < now() - interval '90 days'`); } } }); return { rankings: RANKING_SPECS.length, rows: total }; } // --------------------------------------------------------------------------------------------------------------- async function statusBreakdown(db: Db, column: "country_iso2" | "metro_id" | "operator_id"): Promise>> { const rows = await db.execute(sql`select ${sql.identifier(column)} as dim, status, count(*)::int as n from facilities where merged_into is null and ${sql.identifier(column)} is not null group by 1, 2`); const out = new Map>(); for (const r of rows) { const k = String(r.dim); const m = out.get(k) ?? {}; m[String(r.status)] = Number(r.n); out.set(k, m); } return out; } function statsJson(a: Aggregate, breakdown: Record | undefined, computedAt: string): Record { return { facilityCount: a.facilities, operationalCount: a.operational, constructionCount: a.construction, plannedCount: a.planned, knownMw: a.knownMw || null, constructionMw: a.constructionMw || null, plannedMw: a.plannedMw || null, hyperscaleCount: a.hyperscale, aiCount: a.ai, operatorCount: a.operators, countryCount: a.countries, metroCount: a.metros, cloudRegionCount: a.cloudRegions, projectCount: a.projects, projectPlannedMw: a.projectPlannedMw || null, projectConstructionMw: a.projectConstructionMw || null, expansionMw: a.expansionMw || null, aiConfirmedCount: a.aiConfirmed, ixpCount: a.ixps, openedLast3Years: a.openedRecent, mwCoverage: a.coverage, statusBreakdown: breakdown ?? {}, computedAt, }; } /** Refresh `stats` jsonb on countries, metros and operators (merges into existing keys, e.g. country indicators). */ export async function refreshStats(db: Db = getDb()): Promise<{ countries: number; metros: number; operators: number }> { const now = new Date().toISOString(); const [countries, metros, operators, bc, bm, bo] = await Promise.all([aggregateCountries(db), aggregateMetros(db), aggregateOperators(db, 100000), statusBreakdown(db, "country_iso2"), statusBreakdown(db, "metro_id"), statusBreakdown(db, "operator_id")]); await db.transaction(async (tx) => { for (const a of countries) await tx.execute(sql`update countries set stats = stats || ${JSON.stringify(statsJson(a, bc.get(a.id), now))}::jsonb where iso2 = ${a.id}`); for (const a of metros) await tx.execute(sql`update metros set stats = stats || ${JSON.stringify(statsJson(a, bm.get(a.id), now))}::jsonb where id = ${a.id}`); for (const a of operators) await tx.execute(sql`update operators set stats = stats || ${JSON.stringify(statsJson(a, bo.get(a.id), now))}::jsonb where id = ${a.id}`); }); return { countries: countries.length, metros: metros.length, operators: operators.length }; }