spb/datacenterindex

1/**2 * Global infrastructure pulse — deterministic counts over a time window, computed from events and entity dates.3 * Nothing here is scored or weighted: every figure is a count or a sum of published MW figures.4 */5import type { Pulse } from "@dci/core";6import { pg, eventCols, eventJoins, projectLive, type Fragment, type Sql } from "../lib/sql.js";7import { int, num, reqStr, str, type Row } from "../lib/rows.js";8import { round2 } from "../lib/dto.js";9import { toEventDtos } from "./events.js";1011export type PulseWindow = Pulse["window"];12const WINDOW_INTERVAL: Record<PulseWindow, string> = { "24h": "24 hours", "7d": "7 days", "30d": "30 days" };1314export const PULSE_METHODOLOGY = "Deterministic counts over the window: events with review_status ≠ rejected; projects by created_at / construction_started_on; facilities by opened_on / first_seen; MW figures are sums of published site-scoped values on the records (no estimates). operatorsNewMarkets = operators whose first facility or project in a metro (or country when no metro) falls inside the window and who had none there before. Hidden (false-positive) and merged projects are excluded.";1516function partialDate(sql: Sql, col: Fragment, fallback: Fragment): Fragment {17  return sql`(case when ${col} ~ '^\\d{4}-\\d{2}-\\d{2}' then ${col}::date when ${col} ~ '^\\d{4}-\\d{2}$' then (${col} || '-01')::date when ${col} ~ '^\\d{4}$' then (${col} || '-01-01')::date else ${fallback} end)`;18}1920export async function pulse(window: PulseWindow = "24h"): Promise<Pulse> {21  const sql = pg();22  const since = sql`(now() - ${WINDOW_INTERVAL[window]}::interval)`;23  const sinceDate = sql`(now() - ${WINDOW_INTERVAL[window]}::interval)::date`;24  const conDate = partialDate(sql, sql`p.construction_started_on`, sql`null::date`);25  const openedDate = partialDate(sql, sql`f.opened_on`, sql`null::date`);26  const fFirst = sql`coalesce(${partialDate(sql, sql`f.opened_on`, sql`null::date`)}, f.first_seen::date)`;27  const pFirst = partialDate(sql, sql`p.announced_on`, sql`p.created_at::date`);28  const [ev, pr, fa, markets, major, byCountry, byOperator, sinceRow] = await Promise.all([29    sql<Row[]>`select count(*)::int as total,30        count(*) filter (where e.event_type = 'facility_opened')::int as opened_ev,31        count(*) filter (where e.event_type = 'cloud_region_announced')::int as cloud_ann,32        count(*) filter (where e.event_type = 'power_agreement')::int as power,33        count(*) filter (where e.event_type in ('grid_constraint', 'utility_event'))::int as grid,34        count(*) filter (where e.event_type = 'acquisition')::int as acq,35        count(*) filter (where e.event_type = 'investment_announced')::int as fin,36        count(*) filter (where e.event_type = 'facility_discovered')::int as discovered,37        count(*) filter (where e.event_type in ('capacity_changed', 'planned_capacity_changed'))::int as cap,38        count(distinct e.project_id) filter (where e.project_id is not null and (e.event_type = 'construction_started' or (e.event_type = 'project_status_changed' and e.new_value::text ilike '%under_construction%')))::int as con_ev39      from events e where e.review_status <> 'rejected' and e.detected_at >= ${since}`,40    sql<Row[]>`select41        count(*) filter (where p.created_at >= ${since})::int as new_n, sum(p.planned_mw) filter (where p.created_at >= ${since})::float as new_mw,42        count(*) filter (where ${conDate} >= ${sinceDate})::int as con_n, sum(p.planned_mw) filter (where ${conDate} >= ${sinceDate})::float as con_mw43      from projects p where ${projectLive(sql)}`,44    sql<Row[]>`select count(*) filter (where ${openedDate} >= ${sinceDate} and f.status in ('operational', 'partially_operational', 'expansion'))::int as opened, count(*) filter (where f.first_seen >= ${since})::int as indexed from facilities f where f.merged_into is null`,45    sql<Row[]>`46      with entries as (47        select f.operator_id, f.metro_id, f.country_iso2, ${fFirst} as d from facilities f where f.merged_into is null and f.operator_id is not null and (f.metro_id is not null or f.country_iso2 is not null)48        union all49        select p.operator_id, p.metro_id, p.country_iso2, ${pFirst} from projects p where ${projectLive(sql)} and p.operator_id is not null and (p.metro_id is not null or p.country_iso2 is not null)50      ), firsts as (51        select operator_id, metro_id, country_iso2, min(d) as first_d from entries group by 1, 2, 352      )53      select o.id, o.slug, o.name, m.id as met_id, m.slug as met_slug, m.name as met_name, fs.country_iso2, fs.first_d54      from firsts fs join operators o on o.id = fs.operator_id left join metros m on m.id = fs.metro_id55      where fs.first_d >= ${sinceDate}56        and not exists (select 1 from firsts f2 where f2.operator_id = fs.operator_id and f2.first_d < ${sinceDate} and (f2.metro_id is not distinct from fs.metro_id) and (fs.metro_id is not null or f2.country_iso2 is not distinct from fs.country_iso2))57      order by fs.first_d desc, o.name limit 25`,58    sql<Row[]>`select ${eventCols(sql)} from events e ${eventJoins(sql)} where e.review_status <> 'rejected' and e.detected_at >= ${since} and e.significance >= 75 order by e.significance desc, e.detected_at desc limit 15`,59    sql<Row[]>`select c.iso2, c.name, c.slug, coalesce(ev.n, 0) as events, coalesce(pj.n, 0) as new_projects60      from countries c61      left join (select country_iso2, count(*)::int as n from events where review_status <> 'rejected' and detected_at >= ${since} and country_iso2 is not null group by 1) ev on ev.country_iso2 = c.iso262      left join (select p.country_iso2, count(*)::int as n from projects p where ${projectLive(sql)} and p.created_at >= ${since} and p.country_iso2 is not null group by 1) pj on pj.country_iso2 = c.iso263      where coalesce(ev.n, 0) > 0 or coalesce(pj.n, 0) > 0 order by events desc, new_projects desc, c.name limit 15`,64    sql<Row[]>`select o.id, o.slug, o.name, coalesce(ev.n, 0) as events, coalesce(pj.n, 0) as new_projects65      from operators o66      left join (select operator_id, count(*)::int as n from events where review_status <> 'rejected' and detected_at >= ${since} and operator_id is not null group by 1) ev on ev.operator_id = o.id67      left join (select p.operator_id, count(*)::int as n from projects p where ${projectLive(sql)} and p.created_at >= ${since} and p.operator_id is not null group by 1) pj on pj.operator_id = o.id68      where coalesce(ev.n, 0) > 0 or coalesce(pj.n, 0) > 0 order by events desc, new_projects desc, o.name limit 15`,69    sql<Row[]>`select ${since} as since`,70  ]);71  const e = ev[0] ?? {}, p = pr[0] ?? {}, f = fa[0] ?? {};72  return {73    window,74    since: new Date(String(sinceRow[0]?.since ?? Date.now())).toISOString(),75    newProjects: int(p.new_n),76    projectsEnteredConstruction: Math.max(int(p.con_n), int(e.con_ev)),77    facilitiesOpened: Math.max(int(f.opened), int(e.opened_ev)),78    newlyAnnouncedMw: round2(num(p.new_mw)),79    constructionStartedMw: round2(num(p.con_mw)),80    operatorsNewMarkets: markets.map((r) => ({ operator: { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) }, market: r.met_id ? { id: reqStr(r.met_id), slug: reqStr(r.met_slug), name: reqStr(r.met_name) } : null, countryIso2: str(r.country_iso2) })),81    cloudRegionsAnnounced: int(e.cloud_ann),82    powerAgreements: int(e.power),83    gridConstraintEvents: int(e.grid),84    acquisitions: int(e.acq),85    financingEvents: int(e.fin),86    newFacilitiesIndexed: Math.max(int(f.indexed), int(e.discovered)),87    capacityChanges: int(e.cap),88    eventsTotal: int(e.total),89    majorEvents: await toEventDtos(major),90    byCountry: byCountry.map((r) => ({ iso2: reqStr(r.iso2), name: reqStr(r.name), slug: reqStr(r.slug), events: int(r.events), newProjects: int(r.new_projects) })),91    byOperator: byOperator.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), events: int(r.events), newProjects: int(r.new_projects) })),92  };93}94