spb/datacenterindex
Public
HTML 53.9%
TypeScript 44.5%
JavaScript 0.6%
SQL 0.5%
1/**2 * Rankings + summary stats. `computeRankings()` writes one `rankings` row per key (previous rows → is_current=false);3 * `refreshStats()` refreshes countries/metros/operators `stats` jsonb for fast API summaries.4 * Methodology texts are public (docs/RANKINGS.md mirrors them).5 */6import { getDb, sql, type Db } from "@dci/db";7import { newId, type Ranking, type RankingRow } from "@dci/core";89const OPERATIONAL = ["operational", "partially_operational", "expansion"];10const PLANNED = ["rumored", "proposed", "announced", "permitting", "approved", "delayed"];1112/** Coverage / size floor for MW-based rankings (per-row `coverage` is exposed regardless). */13export const MIN_MW_COVERAGE = 0.35;14export const MIN_FACILITIES = 5;1516export interface Aggregate {17 id: string;18 slug: string;19 name: string;20 countryIso2: string | null;21 facilities: number;22 operational: number;23 construction: number;24 planned: number;25 knownMw: number;26 constructionMw: number;27 plannedMw: number;28 hyperscale: number;29 ai: number;30 withMw: number;31 openedRecent: number;32 operators: number;33 countries: number;34 metros: number;35 cloudRegions: number;36 projects: number;37 /** planned MW published on project records (pipeline statuses) — kept apart from facility figures */38 projectPlannedMw: number;39 projectConstructionMw: number;40 expansionMw: number;41 aiConfirmed: number;42 ixps: number;43 population: number | null;44 gdpUsd: number | null;45 coverage: number;46}4748const num = (v: unknown) => (v == null ? 0 : Number(v));49const numOrNull = (v: unknown) => (v == null ? null : Number(v));5051function recentYear(): string {52 return String(new Date().getUTCFullYear() - 3);53}5455/**56 * Containment-aware facility view used by every aggregate:57 * - `agg_mw` is a facility's own known MW (IT, else total) unless it is a campus whose buildings publish their own58 * figures (then the campus row contributes nothing — the buildings do); a building without a figure under a campus59 * with one contributes nothing either (the campus figure already covers it). Never both.60 * - `counted` excludes campus rows that have building rows (a campus with 4 buildings is 4 facilities, not 5) and61 * hidden / merged rows.62 */63export const FACILITY_VIEW = sql`64 select f.*,65 exists (select 1 from facilities c where c.parent_facility_id = f.id and c.merged_into is null) as has_children,66 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,67 (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)68 and coalesce(f.it_capacity_mw, f.total_power_mw, f.planned_power_mw) is null) as covered_by_parent69 from facilities f where f.merged_into is null`;7071/** MW expressions honouring containment (campus vs buildings never double count). */72const KNOWN_MW_EXPR = sql`case when f.children_have_mw then null else coalesce(f.it_capacity_mw, f.total_power_mw) end`;73const 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`;74const COUNTED = sql`(not f.has_children)`;7576const FACILITY_AGG = (dim: ReturnType<typeof sql>) => sql`77 count(f.id) filter (where ${COUNTED})::int as facilities,78 count(f.id) filter (where ${COUNTED} and f.status in ${OPERATIONAL})::int as operational,79 count(f.id) filter (where ${COUNTED} and f.status = 'under_construction')::int as construction,80 count(f.id) filter (where ${COUNTED} and f.status in ${PLANNED})::int as planned,81 coalesce(sum(${KNOWN_MW_EXPR}) filter (where f.status in ${OPERATIONAL}), 0) as known_mw,82 coalesce(sum(${PIPELINE_MW_EXPR}) filter (where f.status = 'under_construction'), 0) as construction_mw,83 coalesce(sum(${PIPELINE_MW_EXPR}) filter (where f.status in ${PLANNED}), 0) as planned_mw,84 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,85 count(f.id) filter (where ${COUNTED} and f.is_hyperscale)::int as hyperscale,86 count(f.id) filter (where ${COUNTED} and f.is_ai)::int as ai,87 count(f.id) filter (where ${COUNTED} and f.ai_evidence = 'confirmed')::int as ai_confirmed,88 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,89 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,90 count(distinct f.operator_id)::int as operators,91 count(distinct f.country_iso2)::int as countries,92 count(distinct f.metro_id)::int as metros,93 ${dim}`;9495function toAgg(r: Record<string, unknown>): Aggregate {96 const facilities = num(r.facilities);97 return {98 id: String(r.id),99 slug: String(r.slug),100 name: String(r.name),101 countryIso2: r.country_iso2 == null ? null : String(r.country_iso2),102 facilities,103 operational: num(r.operational),104 construction: num(r.construction),105 planned: num(r.planned),106 knownMw: num(r.known_mw),107 constructionMw: num(r.construction_mw),108 plannedMw: num(r.planned_mw),109 hyperscale: num(r.hyperscale),110 ai: num(r.ai),111 withMw: num(r.with_mw),112 openedRecent: num(r.opened_recent),113 operators: num(r.operators),114 countries: num(r.countries),115 metros: num(r.metros),116 cloudRegions: num(r.cloud_regions),117 projects: num(r.projects),118 projectPlannedMw: num(r.project_planned_mw),119 projectConstructionMw: num(r.project_construction_mw),120 expansionMw: num(r.expansion_mw),121 aiConfirmed: num(r.ai_confirmed),122 ixps: num(r.ixps),123 population: numOrNull(r.population),124 gdpUsd: numOrNull(r.gdp_usd),125 coverage: facilities ? Math.round((num(r.with_mw) / facilities) * 1000) / 1000 : 0,126 };127}128129export async function aggregateCountries(db: Db): Promise<Aggregate[]> {130 const rows = await db.execute(sql`131 select c.iso2 as id, c.slug, c.name, c.iso2 as country_iso2, c.population, c.gdp_usd,132 (select count(*) from cloud_regions cr where cr.country_iso2 = c.iso2 and cr.status <> 'retired')::int as cloud_regions,133 (select count(*) from projects p where p.country_iso2 = c.iso2 and p.merged_into is null and not p.hidden)::int as projects,134 (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,135 (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,136 (select count(*) from ixps x where x.country_iso2 = c.iso2)::int as ixps,137 ${FACILITY_AGG(sql`1 as _`)}138 from countries c left join (${FACILITY_VIEW}) f on f.country_iso2 = c.iso2139 group by c.iso2, c.slug, c.name, c.population, c.gdp_usd`);140 return rows.map(toAgg);141}142143export async function aggregateMetros(db: Db): Promise<Aggregate[]> {144 const rows = await db.execute(sql`145 select m.id, m.slug, m.name, m.country_iso2, null::bigint as population, null::double precision as gdp_usd,146 (select count(*) from cloud_regions cr where cr.metro_id = m.id and cr.status <> 'retired')::int as cloud_regions,147 (select count(*) from projects p where p.metro_id = m.id and p.merged_into is null and not p.hidden)::int as projects,148 (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,149 (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,150 (select count(*) from ixps x where x.metro_id = m.id)::int as ixps,151 ${FACILITY_AGG(sql`1 as _`)}152 from metros m left join (${FACILITY_VIEW}) f on f.metro_id = m.id153 group by m.id, m.slug, m.name, m.country_iso2`);154 return rows.map(toAgg);155}156157export async function aggregateOperators(db: Db, limit = 5000): Promise<Aggregate[]> {158 const rows = await db.execute(sql`159 select o.id, o.slug, o.name, o.hq_country_iso2 as country_iso2, null::bigint as population, null::double precision as gdp_usd,160 (select count(*) from cloud_regions cr where cr.provider_id = o.id and cr.status <> 'retired')::int as cloud_regions,161 (select count(*) from projects p where p.operator_id = o.id and p.merged_into is null and not p.hidden)::int as projects,162 (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,163 (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,164 0 as ixps,165 ${FACILITY_AGG(sql`1 as _`)}166 from operators o left join (${FACILITY_VIEW}) f on f.operator_id = o.id167 group by o.id, o.slug, o.name, o.hq_country_iso2168 order by count(f.id) desc, o.name asc169 limit ${limit}`);170 return rows.map(toAgg);171}172173// ---------------------------------------------------------------------------------------------------------------174175interface RankingSpec {176 key: string;177 scope: Ranking["scope"];178 label: string;179 unit: string;180 methodology: string;181 minCoverage?: number;182 value: (a: Aggregate) => number | null;183 secondary?: (a: Aggregate) => number | null;184 eligible?: (a: Aggregate) => boolean;185}186187const mwGate = (a: Aggregate) => a.facilities >= MIN_FACILITIES && a.coverage >= MIN_MW_COVERAGE;188const OPERATIONAL_TXT = "operational, partially operational or expanding";189const 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.`;190const 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.`;191192export const RANKING_SPECS: RankingSpec[] = [193 // countries194 { 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 },195 { 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 },196 { 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 },197 { 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 },198 { 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 },199 { 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 },200 { 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 },201 { 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 },202 { 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 },203 { 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 },204 { 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 },205 // metros206 { 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 },207 { 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 },208 { 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 },209 { 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 },210 { 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 },211 { 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 },212 { 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 },213 // operators214 { 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 },215 { 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 },216 { 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 },217 { 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 },218 { 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 },219 { 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 },220 { 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 },221 { 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 },222 { 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 },223 { 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 },224 { 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 },225];226227const HREF: Record<Ranking["scope"], (slug: string) => string> = { countries: (s) => `/countries/${s}`, metros: (s) => `/metros/${s}`, operators: (s) => `/operators/${s}`, facilities: (s) => `/facilities/${s}` };228229export function buildRows(spec: RankingSpec, aggs: Aggregate[], previous?: Map<string, number>): RankingRow[] {230 const rows: RankingRow[] = [];231 for (const a of aggs) {232 if (spec.eligible && !spec.eligible(a)) continue;233 const v = spec.value(a);234 if (v == null || !Number.isFinite(v) || v <= 0) continue;235 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 });236 }237 rows.sort((x, y) => (y.value ?? 0) - (x.value ?? 0) || x.name.localeCompare(y.name));238 rows.forEach((r, i) => {239 r.rank = i + 1;240 const prev = previous?.get(r.id);241 r.delta = prev == null ? null : prev - r.rank; // positive = moved up242 });243 return rows;244}245246export interface RankingsResult {247 rankings: number;248 rows: number;249}250251export async function computeRankings(db: Db = getDb()): Promise<RankingsResult> {252 const [countries, metros, operators] = await Promise.all([aggregateCountries(db), aggregateMetros(db), aggregateOperators(db)]);253 const byScope: Record<string, Aggregate[]> = { countries, metros, operators, facilities: [] };254 let total = 0;255 const now = new Date().toISOString();256 await db.transaction(async (tx) => {257 for (const spec of RANKING_SPECS) {258 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];259 const previous = new Map<string, number>();260 for (const r of ((prevRow?.rows as RankingRow[] | undefined) ?? [])) previous.set(r.id, r.rank);261 const rows = buildRows(spec, byScope[spec.scope] ?? [], previous);262 await tx.execute(sql`update rankings set is_current = false where key = ${spec.key} and is_current = true`);263 await tx.execute(sql`insert into rankings (id, key, scope, label, unit, methodology, min_coverage, rows, total, computed_at, is_current)264 values (${newId("ranking")}, ${spec.key}, ${spec.scope}, ${spec.label}, ${spec.unit}, ${spec.methodology}, ${spec.minCoverage ?? null}, ${JSON.stringify(rows)}::jsonb, ${rows.length}, ${now}, true)`);265 // keep the table small: drop superseded snapshots older than 90 days266 await tx.execute(sql`delete from rankings where key = ${spec.key} and is_current = false and computed_at < now() - interval '90 days'`);267 total += rows.length;268 // "ALL OBSERVED" companion for gated rankings: every entity with a published figure, coverage shown per row, never estimated269 if (spec.minCoverage != null) {270 const allKey = `${spec.key}:all`;271 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.` };272 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];273 const prevAllMap = new Map<string, number>();274 for (const r of ((prevAll?.rows as RankingRow[] | undefined) ?? [])) prevAllMap.set(r.id, r.rank);275 const allRows = buildRows(allSpec, byScope[spec.scope] ?? [], prevAllMap);276 await tx.execute(sql`update rankings set is_current = false where key = ${allKey} and is_current = true`);277 await tx.execute(sql`insert into rankings (id, key, scope, label, unit, methodology, min_coverage, rows, total, computed_at, is_current)278 values (${newId("ranking")}, ${allKey}, ${spec.scope}, ${allSpec.label}, ${spec.unit}, ${allSpec.methodology}, null, ${JSON.stringify(allRows)}::jsonb, ${allRows.length}, ${now}, true)`);279 await tx.execute(sql`delete from rankings where key = ${allKey} and is_current = false and computed_at < now() - interval '90 days'`);280 }281 }282 });283 return { rankings: RANKING_SPECS.length, rows: total };284}285286// ---------------------------------------------------------------------------------------------------------------287288async function statusBreakdown(db: Db, column: "country_iso2" | "metro_id" | "operator_id"): Promise<Map<string, Record<string, number>>> {289 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`);290 const out = new Map<string, Record<string, number>>();291 for (const r of rows) {292 const k = String(r.dim);293 const m = out.get(k) ?? {};294 m[String(r.status)] = Number(r.n);295 out.set(k, m);296 }297 return out;298}299300function statsJson(a: Aggregate, breakdown: Record<string, number> | undefined, computedAt: string): Record<string, unknown> {301 return {302 facilityCount: a.facilities,303 operationalCount: a.operational,304 constructionCount: a.construction,305 plannedCount: a.planned,306 knownMw: a.knownMw || null,307 constructionMw: a.constructionMw || null,308 plannedMw: a.plannedMw || null,309 hyperscaleCount: a.hyperscale,310 aiCount: a.ai,311 operatorCount: a.operators,312 countryCount: a.countries,313 metroCount: a.metros,314 cloudRegionCount: a.cloudRegions,315 projectCount: a.projects,316 projectPlannedMw: a.projectPlannedMw || null,317 projectConstructionMw: a.projectConstructionMw || null,318 expansionMw: a.expansionMw || null,319 aiConfirmedCount: a.aiConfirmed,320 ixpCount: a.ixps,321 openedLast3Years: a.openedRecent,322 mwCoverage: a.coverage,323 statusBreakdown: breakdown ?? {},324 computedAt,325 };326}327328/** Refresh `stats` jsonb on countries, metros and operators (merges into existing keys, e.g. country indicators). */329export async function refreshStats(db: Db = getDb()): Promise<{ countries: number; metros: number; operators: number }> {330 const now = new Date().toISOString();331 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")]);332 await db.transaction(async (tx) => {333 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}`);334 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}`);335 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}`);336 });337 return { countries: countries.length, metros: metros.length, operators: operators.length };338}339