spb/datacenterindex
Public
HTML 53.9%
TypeScript 44.5%
JavaScript 0.6%
SQL 0.5%
1/**2 * Containment-aware aggregates shared by operator / metro / country payloads, /compare and the dashboard:3 * PipelineBreakdown (facility lifecycle buckets + project stages), ExpansionVelocity, market concentration (HHI),4 * momentum components and the CoverageRow. Every MW figure is a sum of published site-scoped figures — no estimate.5 */6import type { CoverageRow, ExpansionVelocity, MarketConcentration, MarketMomentum, PipelineBreakdown } from "@dci/core";7import { pg, facilityView, knownMwAgg, pipelineMwAgg, countedAgg, hasMwAgg, projectLive, OPERATIONAL_SET, CONSTRUCTION_SET, PLANNED_SET, GRID_EVENT_TYPES, type Fragment, type Sql } from "./sql.js";8import { int, num, reqStr, str, type Row } from "./rows.js";9import { asStatus, round2, share } from "./dto.js";1011/** Scope of an aggregate: a condition on the facility view row `f` and on the project row `p`. */12export interface Scope { facility: Fragment; project: Fragment; event?: Fragment; cloud?: Fragment }1314export function scopeFor(sql: Sql, s: { operatorId?: string; metroId?: string; countryIso2?: string }): Scope {15 if (s.operatorId) return { facility: sql`(f.operator_id = ${s.operatorId} or f.owner_id = ${s.operatorId})`, project: sql`p.operator_id = ${s.operatorId}`, event: sql`e.operator_id = ${s.operatorId}`, cloud: sql`r.provider_id = ${s.operatorId}` };16 if (s.metroId) return { facility: sql`f.metro_id = ${s.metroId}`, project: sql`p.metro_id = ${s.metroId}`, event: sql`(e.metro_id = ${s.metroId} or (e.entity_type = 'facility' and e.entity_id in (select id from facilities where metro_id = ${s.metroId})))`, cloud: sql`r.metro_id = ${s.metroId}` };17 if (s.countryIso2) return { facility: sql`f.country_iso2 = ${s.countryIso2}`, project: sql`p.country_iso2 = ${s.countryIso2}`, event: sql`e.country_iso2 = ${s.countryIso2}`, cloud: sql`r.country_iso2 = ${s.countryIso2}` };18 return { facility: sql`true`, project: sql`true`, event: sql`true`, cloud: sql`true` };19}2021export async function pipelineBreakdown(scope: Scope): Promise<PipelineBreakdown> {22 const sql = pg();23 const known = knownMwAgg(sql), pipe = pipelineMwAgg(sql), counted = countedAgg(sql), hasMw = hasMwAgg(sql);24 const [fr, pr] = await Promise.all([25 sql<Row[]>`select26 count(*) filter (where ${counted})::int as total,27 count(*) filter (where ${counted} and ${hasMw})::int as with_mw,28 count(*) filter (where ${counted} and f.status = any(${OPERATIONAL_SET}))::int as op_n, sum(${known}) filter (where f.status = any(${OPERATIONAL_SET}))::float as op_mw,29 count(*) filter (where ${counted} and f.status = any(${CONSTRUCTION_SET}))::int as con_n, sum(${pipe}) filter (where f.status = any(${CONSTRUCTION_SET}))::float as con_mw,30 count(*) filter (where ${counted} and f.status in ('approved', 'permitting'))::int as app_n, sum(${pipe}) filter (where f.status in ('approved', 'permitting'))::float as app_mw,31 count(*) filter (where ${counted} and f.status in ('rumored', 'proposed', 'announced', 'delayed'))::int as ann_n, sum(${pipe}) filter (where f.status in ('rumored', 'proposed', 'announced', 'delayed'))::float as ann_mw32 from ${facilityView(sql)} f where ${scope.facility}`,33 sql<Row[]>`select p.status, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p where ${projectLive(sql)} and ${scope.project} group by p.status order by n desc`,34 ]);35 const r = fr[0] ?? {};36 const total = int(r.total);37 return {38 operational: { count: int(r.op_n), mw: round2(num(r.op_mw)) },39 construction: { count: int(r.con_n), mw: round2(num(r.con_mw)) },40 approved: { count: int(r.app_n), mw: round2(num(r.app_mw)) },41 announced: { count: int(r.ann_n), mw: round2(num(r.ann_mw)) },42 projects: pr.map((x) => ({ status: asStatus(x.status), count: int(x.n), mw: round2(num(x.mw)) })),43 mwCoverage: total ? share(int(r.with_mw), total) : 0,44 };45}4647const WINDOWS: Array<{ label: "12m" | "3y" | "5y"; months: number }> = [{ label: "12m", months: 12 }, { label: "3y", months: 36 }, { label: "5y", months: 60 }];4849/** Expansion velocity: new facilities (opened_on else first_seen), projects (announced_on else created_at), new countries / metros per window. */50export async function expansionVelocity(scope: Scope): Promise<ExpansionVelocity> {51 const sql = pg();52 const known = knownMwAgg(sql), counted = countedAgg(sql);53 const openedDate = sql`(case when f.opened_on ~ '^\\d{4}-\\d{2}-\\d{2}' then f.opened_on::date when f.opened_on ~ '^\\d{4}-\\d{2}$' then (f.opened_on || '-01')::date when f.opened_on ~ '^\\d{4}$' then (f.opened_on || '-01-01')::date else f.first_seen::date end)`;54 const annDate = sql`(case when p.announced_on ~ '^\\d{4}-\\d{2}-\\d{2}' then p.announced_on::date when p.announced_on ~ '^\\d{4}-\\d{2}$' then (p.announced_on || '-01')::date when p.announced_on ~ '^\\d{4}$' then (p.announced_on || '-01-01')::date else p.created_at::date end)`;55 const windows = await Promise.all(WINDOWS.map(async (w) => {56 const since = sql`(current_date - make_interval(months => ${w.months}))`;57 const [fr, pr, geo] = await Promise.all([58 sql<Row[]>`select count(*) filter (where ${counted})::int as n, sum(${known})::float as mw from ${facilityView(sql)} f where ${scope.facility} and ${openedDate} >= ${since}`,59 sql<Row[]>`select count(*)::int as n, sum(p.planned_mw)::float as mw from projects p where ${projectLive(sql)} and ${scope.project} and ${annDate} >= ${since}`,60 sql<Row[]>`with fx as (select f.country_iso2, f.metro_id, min(${openedDate}) as first_d from ${facilityView(sql)} f where ${scope.facility} group by 1, 2)61 select count(distinct country_iso2) filter (where country_iso2 is not null and first_d >= ${since} and not exists (select 1 from fx f2 where f2.country_iso2 = fx.country_iso2 and f2.first_d < ${since}))::int as countries,62 count(distinct metro_id) filter (where metro_id is not null and first_d >= ${since} and not exists (select 1 from fx f2 where f2.metro_id = fx.metro_id and f2.first_d < ${since}))::int as metros63 from fx`,64 ]);65 return { label: w.label, newFacilities: int(fr[0]?.n), newProjects: int(pr[0]?.n), newCountries: int(geo[0]?.countries), newMetros: int(geo[0]?.metros), openedMw: round2(num(fr[0]?.mw)), announcedMw: round2(num(pr[0]?.mw)) };66 }));67 const years = await sql<Row[]>`select extract(year from ${openedDate})::int as year, bool_and(f.opened_on ~ '^\\d{4}') as opened_basis,68 count(*) filter (where ${counted})::int as n, array_agg(distinct f.country_iso2) filter (where f.country_iso2 is not null) as countries, array_agg(distinct f.metro_id) filter (where f.metro_id is not null) as metros69 from ${facilityView(sql)} f where ${scope.facility} group by 1 order by 1`;70 const seenC = new Set<string>(), seenM = new Set<string>();71 let cum = 0;72 const countriesOverTime: ExpansionVelocity["countriesOverTime"] = [];73 for (const y of years) {74 const year = int(y.year);75 if (!year) continue;76 for (const c of (y.countries as string[] | null) ?? []) seenC.add(c);77 for (const m of (y.metros as string[] | null) ?? []) seenM.add(m);78 cum += int(y.n);79 countriesOverTime.push({ year, countries: seenC.size, metros: seenM.size, facilities: cum, basis: y.opened_basis === true ? "opened" : "first_seen" });80 }81 return { windows, countriesOverTime };82}8384export const CONCENTRATION_NOTE = "HHI = Σ (share × 100)² over operators, 0–10 000; 10 000 = one operator. Computed on counted facilities (campus rows with buildings excluded) and, separately, on known operational MW only when published figures exist — `coverage` is the share of facilities with a figure; treat the MW view as partial when coverage is low.";8586export async function concentration(scope: Scope): Promise<MarketConcentration> {87 const sql = pg();88 const known = knownMwAgg(sql), counted = countedAgg(sql), hasMw = hasMwAgg(sql);89 const rows = await sql<Row[]>`select o.id, o.slug, o.name, count(*) filter (where ${counted})::int as n, sum(${known}) filter (where f.status = any(${OPERATIONAL_SET}))::float as mw, count(*) filter (where ${counted} and ${hasMw})::int as with_mw90 from ${facilityView(sql)} f join operators o on o.id = f.operator_id where ${scope.facility} group by o.id, o.slug, o.name order by n desc, mw desc nulls last`;91 const totalN = rows.reduce((a, r) => a + int(r.n), 0);92 const totalCounted = totalN;93 const withMw = rows.reduce((a, r) => a + int(r.with_mw), 0);94 const totalMw = rows.reduce((a, r) => a + (num(r.mw) ?? 0), 0);95 const hhi = (shares: number[]) => Math.round(shares.reduce((a, s) => a + (s * 100) ** 2, 0));96 const fShares = rows.map((r) => (totalN ? int(r.n) / totalN : 0));97 const facilities = {98 hhi: hhi(fShares),99 top3Share: share(rows.slice(0, 3).reduce((a, r) => a + int(r.n), 0), totalN),100 top5Share: share(rows.slice(0, 5).reduce((a, r) => a + int(r.n), 0), totalN),101 top: rows.slice(0, 10).map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), count: int(r.n), share: share(int(r.n), totalN) })),102 };103 let knownMw: MarketConcentration["knownMw"] = null;104 if (totalMw > 0) {105 const byMw = rows.filter((r) => (num(r.mw) ?? 0) > 0).sort((a, b) => (num(b.mw) ?? 0) - (num(a.mw) ?? 0));106 knownMw = {107 hhi: hhi(byMw.map((r) => (num(r.mw) ?? 0) / totalMw)),108 top3Share: share(byMw.slice(0, 3).reduce((a, r) => a + (num(r.mw) ?? 0), 0), totalMw),109 top5Share: share(byMw.slice(0, 5).reduce((a, r) => a + (num(r.mw) ?? 0), 0), totalMw),110 coverage: share(withMw, totalCounted),111 top: byMw.slice(0, 10).map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), mw: round2(num(r.mw)) ?? 0, share: share(num(r.mw) ?? 0, totalMw) })),112 };113 }114 return { operatorCount: rows.length, facilities, knownMw, note: CONCENTRATION_NOTE };115}116117/** 12-month momentum components (never collapsed into a score). */118export async function momentum(scope: Scope): Promise<MarketMomentum> {119 const sql = pg();120 const since = sql`(current_date - interval '12 months')`;121 const annDate = sql`(case when p.announced_on ~ '^\\d{4}-\\d{2}-\\d{2}' then p.announced_on::date when p.announced_on ~ '^\\d{4}-\\d{2}$' then (p.announced_on || '-01')::date when p.announced_on ~ '^\\d{4}$' then (p.announced_on || '-01-01')::date else p.created_at::date end)`;122 const conDate = sql`(case when p.construction_started_on ~ '^\\d{4}-\\d{2}-\\d{2}' then p.construction_started_on::date when p.construction_started_on ~ '^\\d{4}-\\d{2}$' then (p.construction_started_on || '-01')::date when p.construction_started_on ~ '^\\d{4}$' then (p.construction_started_on || '-01-01')::date else null end)`;123 const openedDate = sql`(case when f.opened_on ~ '^\\d{4}-\\d{2}-\\d{2}' then f.opened_on::date when f.opened_on ~ '^\\d{4}-\\d{2}$' then (f.opened_on || '-01')::date when f.opened_on ~ '^\\d{4}$' then (f.opened_on || '-01-01')::date else null end)`;124 const [pa, pc, fo, ne, cr, ev] = await Promise.all([125 sql<Row[]>`select count(*)::int as n, sum(p.planned_mw)::float as mw from projects p where ${projectLive(sql)} and ${scope.project} and ${annDate} >= ${since}`,126 sql<Row[]>`select count(*)::int as n, sum(p.planned_mw)::float as mw from projects p where ${projectLive(sql)} and ${scope.project} and (${conDate} >= ${since} or (p.status = 'under_construction' and ${conDate} is null and exists (select 1 from events e where e.project_id = p.id and e.event_type in ('construction_started', 'project_status_changed') and e.new_value::text ilike '%under_construction%' and e.detected_at >= ${since})))`,127 sql<Row[]>`select count(*) filter (where ${countedAgg(sql)})::int as n from ${facilityView(sql)} f where ${scope.facility} and f.status = any(${OPERATIONAL_SET}) and ${openedDate} >= ${since}`,128 sql<Row[]>`with fx as (select f.operator_id, min(coalesce(${openedDate}, f.first_seen::date)) as first_d from ${facilityView(sql)} f where ${scope.facility} and f.operator_id is not null group by 1)129 select o.id, o.slug, o.name from fx join operators o on o.id = fx.operator_id where fx.first_d >= ${since} order by fx.first_d desc limit 20`,130 sql<Row[]>`select count(*)::int as n from cloud_regions r where ${scope.cloud ?? sql`true`} and ((r.launched_on ~ '^\\d{4}' and (case when r.launched_on ~ '^\\d{4}-\\d{2}-\\d{2}' then r.launched_on::date when r.launched_on ~ '^\\d{4}-\\d{2}$' then (r.launched_on || '-01')::date else (left(r.launched_on, 4) || '-01-01')::date end) >= ${since}) or r.created_at::date >= ${since})`,131 sql<Row[]>`select count(*)::int as total, count(*) filter (where e.event_type = any(${GRID_EVENT_TYPES}))::int as grid from events e where ${scope.event ?? sql`true`} and e.review_status <> 'rejected' and e.detected_at >= ${since}`,132 ]);133 return {134 window: "12m",135 projectsAnnounced: int(pa[0]?.n),136 projectsEnteredConstruction: int(pc[0]?.n),137 constructionMw: round2(num(pc[0]?.mw)),138 announcedMw: round2(num(pa[0]?.mw)),139 newEntrants: ne.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) })),140 facilitiesOpened: int(fo[0]?.n),141 cloudRegionsAdded: int(cr[0]?.n),142 gridEvents: int(ev[0]?.grid),143 eventsTotal: int(ev[0]?.total),144 };145}146147/** CoverageRow for a scope (containment-aware counts, share of facilities with each field known). */148export async function coverageRow(key: string, name: string, slug: string, scope: Scope): Promise<CoverageRow> {149 const sql = pg();150 const counted = countedAgg(sql), hasMw = hasMwAgg(sql);151 const [fr, pr] = await Promise.all([152 sql<Row[]>`select count(*) filter (where ${counted})::int as n,153 count(*) filter (where ${counted} and ${hasMw})::int as with_mw,154 count(*) filter (where ${counted} and f.operator_id is not null)::int as with_op,155 count(*) filter (where ${counted} and f.lat is not null and f.geo_precision in ('exact', 'parcel', 'street'))::int as precise,156 count(*) filter (where ${counted} and f.lat is not null)::int as any_loc,157 count(*) filter (where ${counted} and f.status <> 'unknown')::int as with_status,158 count(*) filter (where ${counted} and f.opened_on ~ '^\\d{4}')::int as with_open,159 count(*) filter (where ${counted} and f.source_count >= 2)::int as multi,160 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,161 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 in ('operator','government','filing','utility','cloud_provider','registry')))::int as primary_src162 from ${facilityView(sql)} f where ${scope.facility}`,163 sql<Row[]>`select count(*)::int as n, count(*) filter (where p.lat is not null)::int as with_loc from projects p where ${projectLive(sql)} and ${scope.project}`,164 ]);165 const r = fr[0] ?? {};166 const n = int(r.n);167 return {168 key, name, slug,169 facilities: n,170 capacityCoverage: share(int(r.with_mw), n),171 operatorCoverage: share(int(r.with_op), n),172 preciseLocationCoverage: share(int(r.precise), n),173 anyLocationCoverage: share(int(r.any_loc), n),174 statusCoverage: share(int(r.with_status), n),175 openingDateCoverage: share(int(r.with_open), n),176 multiSourceCoverage: share(int(r.multi), n),177 projects: int(pr[0]?.n),178 projectsWithLocation: int(pr[0]?.with_loc),179 connectivityCoverage: share(int(r.conn), n),180 primarySourceShare: share(int(r.primary_src), n),181 };182}183184/** Aggregates over the containment-aware view grouped by a dimension (for lists / dashboards). */185export interface DimAgg { id: string; slug: string; name: string; countryIso2: string | null; facilities: number; operational: number; construction: number; planned: number; knownMw: number | null; constructionMw: number | null; plannedMw: number | null; withMw: number; operators: number; ai: number; hyperscale: number; coverage: number }186187export function dimAggFromRow(r: Row): DimAgg {188 const n = int(r.facilities);189 return { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), countryIso2: str(r.country_iso2), facilities: n, operational: int(r.operational), construction: int(r.construction), planned: int(r.planned), knownMw: round2(num(r.known_mw)), constructionMw: round2(num(r.construction_mw)), plannedMw: round2(num(r.planned_mw)), withMw: int(r.with_mw), operators: int(r.operators), ai: int(r.ai), hyperscale: int(r.hyperscale), coverage: share(int(r.with_mw), n) };190}191192/** Column list for a containment-aware aggregate over facilityView `f` (pair with a group by on the dimension). */193export function dimAggCols(sql: Sql): Fragment {194 const known = knownMwAgg(sql), pipe = pipelineMwAgg(sql), counted = countedAgg(sql), hasMw = hasMwAgg(sql);195 return sql`196 count(f.id) filter (where ${counted})::int as facilities,197 count(f.id) filter (where ${counted} and f.status = any(${OPERATIONAL_SET}))::int as operational,198 count(f.id) filter (where ${counted} and f.status = any(${CONSTRUCTION_SET}))::int as construction,199 count(f.id) filter (where ${counted} and f.status = any(${PLANNED_SET}))::int as planned,200 sum(${known}) filter (where f.status = any(${OPERATIONAL_SET}))::float as known_mw,201 sum(${pipe}) filter (where f.status = any(${CONSTRUCTION_SET}))::float as construction_mw,202 sum(${pipe}) filter (where f.status = any(${PLANNED_SET}))::float as planned_mw,203 count(f.id) filter (where ${counted} and ${hasMw})::int as with_mw,204 count(distinct f.operator_id)::int as operators,205 count(f.id) filter (where ${counted} and (f.ai_evidence in ('confirmed', 'likely') or f.is_ai or f.facility_type = 'ai'))::int as ai,206 count(f.id) filter (where ${counted} and (f.is_hyperscale or f.facility_type = 'hyperscale'))::int as hyperscale`;207}208