spb/datacenterindex

1import type { Dashboard, DashboardStats, GridConstraintDTO, MarketMomentum, ProjectSummary, RankingRow } from "@dci/core";2import { pg, yearExpr, projectJoins, projectSummaryCols, projectLive, facilityView, knownMwAgg, countedAgg, eventCols, eventJoins, gridConstraintCols, gridConstraintJoins, AI_LEVELS, POWER_EVENT_TYPES, GRID_EVENT_TYPES } from "../lib/sql.js";3import { int, iso, num, reqStr, str, type Row } from "../lib/rows.js";4import { asStatus, asType, gridConstraintDto, gridConstraintFromEvent, projectSummary, round2, share } from "../lib/dto.js";5import { hrefFor } from "../lib/resolve.js";6import { latestEvents, toEventDtos } from "./events.js";7import { recentlyVerifiedFacilities } from "./facilities.js";8import { topRows } from "./rankings.js";9import { pulse } from "./pulse.js";10import { coverageRow, dimAggCols, momentum, scopeFor } from "../lib/pipeline.js";1112async function liveTop(dim: "country" | "metro" | "operator", limit: number): Promise<RankingRow[]> {13  const sql = pg();14  const known = knownMwAgg(sql), counted = countedAgg(sql), hasMw = sql`(coalesce(f.it_capacity_mw, f.total_power_mw, f.planned_power_mw) is not null or f.covered_by_parent)`;15  const rows = dim === "country"16    ? await sql<Row[]>`select c.iso2 as id, c.slug, c.name, c.iso2 as country_iso2, count(*) filter (where ${counted})::int as n, sum(${known}) filter (where f.status in ('operational','partially_operational','expansion'))::float as mw, count(*) filter (where ${counted} and ${hasMw})::int as with_mw from ${facilityView(sql)} f join countries c on c.iso2 = f.country_iso2 group by 1, 2, 3, 4 order by n desc limit ${limit}`17    : dim === "metro"18      ? await sql<Row[]>`select m.id, m.slug, m.name, m.country_iso2, count(*) filter (where ${counted})::int as n, sum(${known}) filter (where f.status in ('operational','partially_operational','expansion'))::float as mw, count(*) filter (where ${counted} and ${hasMw})::int as with_mw from ${facilityView(sql)} f join metros m on m.id = f.metro_id group by 1, 2, 3, 4 order by n desc limit ${limit}`19      : await sql<Row[]>`select o.id, o.slug, o.name, o.hq_country_iso2 as country_iso2, count(*) filter (where ${counted})::int as n, sum(${known}) filter (where f.status in ('operational','partially_operational','expansion'))::float as mw, count(*) filter (where ${counted} and ${hasMw})::int as with_mw from ${facilityView(sql)} f join operators o on o.id = f.operator_id group by 1, 2, 3, 4 order by n desc limit ${limit}`;20  return rows.map((r, i) => ({ rank: i + 1, id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), href: hrefFor(dim, reqStr(r.slug)), value: int(r.n), secondary: round2(num(r.mw)), countryIso2: str(r.country_iso2), coverage: share(int(r.with_mw), int(r.n)) }));21}2223async function fastestGrowingMetros(limit = 8): Promise<Dashboard["fastestGrowingMetros"]> {24  const sql = pg();25  const since = sql`(current_date - interval '12 months')`;26  const pd = (col: string) => sql`(case when p.${sql(col)} ~ '^\\d{4}-\\d{2}-\\d{2}' then p.${sql(col)}::date when p.${sql(col)} ~ '^\\d{4}-\\d{2}$' then (p.${sql(col)} || '-01')::date when p.${sql(col)} ~ '^\\d{4}$' then (p.${sql(col)} || '-01-01')::date else null end)`;27  const rows = await sql<Row[]>`28    with pa as (select p.metro_id, count(*) filter (where coalesce(${pd("announced_on")}, p.created_at::date) >= ${since})::int as announced, count(*) filter (where ${pd("construction_started_on")} >= ${since})::int as construction from projects p where ${projectLive(sql)} and p.metro_id is not null group by 1),29    fo as (select f.metro_id, count(*)::int as opened from facilities f where f.merged_into is null and f.metro_id is not null and f.status in ('operational','partially_operational','expansion') and (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) >= ${since} group by 1)30    select m.id, m.slug, m.name, m.country_iso2, coalesce(pa.announced, 0) + coalesce(pa.construction, 0) + coalesce(fo.opened, 0) as score31    from metros m left join pa on pa.metro_id = m.id left join fo on fo.metro_id = m.id32    where coalesce(pa.announced, 0) + coalesce(pa.construction, 0) + coalesce(fo.opened, 0) > 033    order by score desc, m.name limit ${limit}`;34  return Promise.all(rows.map(async (r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), countryIso2: reqStr(r.country_iso2), momentum: (await momentum(scopeFor(sql, { metroId: reqStr(r.id) }))) as MarketMomentum })));35}3637async function operatorExpansion(limit = 10): Promise<Dashboard["operatorExpansion"]> {38  const sql = pg();39  const since = sql`(current_date - interval '12 months')`;40  const rows = await sql<Row[]>`41    with entries as (42      select f.operator_id, f.metro_id, f.country_iso2, coalesce(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, f.first_seen::date) as d43      from facilities f where f.merged_into is null and f.operator_id is not null44      union all45      select p.operator_id, p.metro_id, p.country_iso2, 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 end46      from projects p where ${projectLive(sql)} and p.operator_id is not null47    ),48    fc as (select operator_id, country_iso2, min(d) as first_d from entries where country_iso2 is not null group by 1, 2),49    fm as (select operator_id, metro_id, min(d) as first_d from entries where metro_id is not null group by 1, 2),50    agg as (51      select o.id, o.slug, o.name,52        (select array_agg(fc.country_iso2 order by fc.first_d desc) from fc where fc.operator_id = o.id and fc.first_d >= ${since}) as new_countries,53        (select array_agg(m.name order by fm.first_d desc) from fm join metros m on m.id = fm.metro_id where fm.operator_id = o.id and fm.first_d >= ${since}) as new_metros,54        (select count(*)::int from projects p where ${projectLive(sql)} and p.operator_id = o.id and coalesce(case when p.announced_on ~ '^\\d{4}' then (left(p.announced_on, 4) || '-01-01')::date else null end, p.created_at::date) >= ${since}) as projects12m,55        (select count(*) from fc where fc.operator_id = o.id and fc.first_d < ${since}) as had_before56      from operators o57    )58    select * from agg where (coalesce(array_length(new_countries, 1), 0) > 0 or coalesce(array_length(new_metros, 1), 0) > 0) and had_before > 059    order by coalesce(array_length(new_countries, 1), 0) + coalesce(array_length(new_metros, 1), 0) desc, projects12m desc, name limit ${limit}`;60  return rows.map((r) => ({ operator: { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) }, newCountries: ((r.new_countries as string[] | null) ?? []).map(String), newMetros: ((r.new_metros as string[] | null) ?? []).map(String), projects12m: int(r.projects12m) }));61}6263async function globalGridConstraints(limit = 10): Promise<GridConstraintDTO[]> {64  const sql = pg();65  const [rows, ev] = await Promise.all([66    sql<Row[]>`select ${gridConstraintCols(sql)} from grid_constraints g ${gridConstraintJoins(sql)} order by g.created_at desc limit ${limit}`,67    sql<Row[]>`select ${eventCols(sql)} from events e ${eventJoins(sql)} where e.event_type = any(${GRID_EVENT_TYPES}) and e.review_status <> 'rejected' order by e.detected_at desc limit ${limit}`,68  ]);69  const out = rows.map(gridConstraintDto);70  const seen = new Set(out.map((g) => g.eventId).filter(Boolean));71  for (const e of await toEventDtos(ev)) { if (seen.has(e.id)) continue; seen.add(e.id); out.push(gridConstraintFromEvent(e)); }72  return out.slice(0, limit);73}7475export async function dashboard(): Promise<Dashboard> {76  const sql = pg();77  const known = knownMwAgg(sql);78  const [facStats, countsRow, evRow, crawlRow, capSeries, capFallback, projStatus, typeRows, pipeline, aiRow, aiRecent, latest, newProjects, recentlyVerified, rkCountries, rkMetros, rkOperators, pulseData, growing, major, expansion, powerRows, gridConstraints, coverage, ingRow] = await Promise.all([79    sql<Row[]>`select ${dimAggCols(sql)}, count(distinct f.country_iso2)::int as countries, count(distinct f.metro_id)::int as metros from ${facilityView(sql)} f`,80    sql<Row[]>`81      select (select count(*)::int from cloud_regions where status <> 'retired') as cloud_regions, (select count(*)::int from ixps) as ixps, (select count(*)::int from projects p where ${projectLive(sql)}) as projects,82        (select count(*)::int from sources) as sources, (select count(*)::int from documents) as documents, (select count(*)::int from operators) as all_operators`,83    sql<Row[]>`select count(*) filter (where detected_at >= now() - interval '24 hours')::int as e24, count(*) filter (where detected_at >= now() - interval '7 days')::int as e7 from events where review_status <> 'rejected'`,84    sql<Row[]>`select max(finished_at) as last_finished, max(started_at) as last_started, count(*) filter (where started_at >= now() - interval '24 hours')::int as runs24 from connector_runs`,85    sql<Row[]>`select day, metric, value from daily_metrics where dim = 'global' and metric in ('facilities_total', 'known_mw') order by day`,86    sql<Row[]>`select ${yearExpr(sql, sql`f.opened_on`)} as year, count(*) filter (where ${countedAgg(sql)})::int as n, sum(${known})::float as mw from ${facilityView(sql)} f where f.opened_on ~ '^\\d{4}' group by 1 order by 1`,87    sql<Row[]>`select p.status, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p where ${projectLive(sql)} group by p.status order by n desc`,88    sql<Row[]>`select facility_type, count(*)::int as n from facilities where merged_into is null group by facility_type order by n desc`,89    sql<Row[]>`select left(p.expected_opening, 4) as year, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p where ${projectLive(sql)} and p.expected_opening ~ '^\\d{4}' and p.status not in ('cancelled', 'closed') group by 1 order by 1`,90    sql<Row[]>`select (select count(*)::int from ${facilityView(sql)} f where ${countedAgg(sql)} and (f.ai_evidence = any(${AI_LEVELS}) or f.is_ai or f.facility_type = 'ai')) as facilities, (select count(*)::int from projects p where ${projectLive(sql)} and (p.ai_evidence = any(${AI_LEVELS}) or p.is_ai)) as projects, (select sum(p.planned_mw)::float from projects p where ${projectLive(sql)} and (p.ai_evidence = any(${AI_LEVELS}) or p.is_ai) and p.status not in ('cancelled')) as planned_mw`,91    sql<Row[]>`select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)} where ${projectLive(sql)} and (p.ai_evidence = any(${AI_LEVELS}) or p.is_ai) order by p.last_update desc limit 6`,92    latestEvents(20),93    sql<Row[]>`select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)} where ${projectLive(sql)} order by p.created_at desc limit 10`,94    recentlyVerifiedFacilities(10),95    topRows(["countries_by_facilities", "countries_by_known_mw", "countries_facilities", "top_countries"], "countries", 10),96    topRows(["metros_by_facilities", "metros_by_known_mw", "metros_facilities", "top_metros"], "metros", 10),97    topRows(["operators_by_facilities", "operators_by_known_mw", "operators_facilities", "top_operators"], "operators", 10),98    pulse("24h"),99    fastestGrowingMetros(8),100    sql<Row[]>`select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)} where ${projectLive(sql)} and p.planned_mw >= 100 and p.status not in ('cancelled', 'closed') order by p.planned_mw desc, p.last_update desc limit 12`,101    operatorExpansion(10),102    sql<Row[]>`select ${eventCols(sql)} from events e ${eventJoins(sql)} where e.event_type = any(${POWER_EVENT_TYPES}) and e.review_status <> 'rejected' order by e.detected_at desc limit 10`,103    globalGridConstraints(10),104    coverageRow("global", "Global", "global", scopeFor(sql, {})),105    sql<Row[]>`select (select count(*)::int from documents where last_fetched >= now() - interval '24 hours') as docs24, (select count(*)::int from connectors where enabled and not paused) as total, (select count(*)::int from connectors where enabled and not paused and health = 'ok') as healthy`,106  ]);107  const fs = facStats[0] ?? {};108  const cn = countsRow[0] ?? {};109  const facilities = int(fs.facilities);110  const stats: DashboardStats = {111    facilities,112    operational: int(fs.operational),113    underConstruction: int(fs.construction),114    planned: int(fs.planned),115    knownOperationalMw: round2(num(fs.known_mw)) ?? 0,116    constructionMw: round2(num(fs.construction_mw)) ?? 0,117    plannedMw: round2(num(fs.planned_mw)) ?? 0,118    mwCoverage: share(int(fs.with_mw), facilities),119    countries: int(fs.countries),120    metros: int(fs.metros),121    operators: int(fs.operators) || int(cn.all_operators),122    cloudRegions: int(cn.cloud_regions),123    ixps: int(cn.ixps),124    projects: int(cn.projects),125    events24h: int(evRow[0]?.e24),126    events7d: int(evRow[0]?.e7),127    sources: int(cn.sources),128    documents: int(cn.documents),129    lastCrawlAt: iso(crawlRow[0]?.last_finished) ?? iso(crawlRow[0]?.last_started),130    generatedAt: new Date().toISOString(),131  };132133  // capacity over time: daily_metrics (global) by year-end, else facilities by opened_on year134  let capacityOverTime: Dashboard["capacityOverTime"] = [];135  if (capSeries.length) {136    const byYear = new Map<number, { facilities: number; knownMw: number | null }>();137    for (const r of capSeries) {138      const year = Number(String(r.day).slice(0, 4));139      if (!year) continue;140      const cur = byYear.get(year) ?? { facilities: 0, knownMw: null };141      if (r.metric === "facilities_total") cur.facilities = int(r.value);142      if (r.metric === "known_mw") cur.knownMw = num(r.value);143      byYear.set(year, cur); // last day of the year wins (rows ordered by day)144    }145    capacityOverTime = [...byYear.entries()].sort((a, b) => a[0] - b[0]).map(([year, v]) => ({ year, facilities: v.facilities, knownMw: v.knownMw, cumulativeMw: v.knownMw }));146  }147  if (!capacityOverTime.length) {148    let cumF = 0, cumMw = 0, saw = false;149    capacityOverTime = capFallback.map((r) => { cumF += int(r.n); const m = num(r.mw); if (m != null) { cumMw += m; saw = true; } return { year: int(r.year), facilities: cumF, knownMw: round2(m), cumulativeMw: saw ? Math.round(cumMw * 100) / 100 : null }; }).filter((x) => x.year > 0);150  }151152  const ai = aiRow[0] ?? {};153  const ing = ingRow[0] ?? {};154  return {155    stats,156    capacityOverTime,157    projectsByStatus: projStatus.map((r) => ({ status: asStatus(r.status), count: int(r.n), mw: round2(num(r.mw)) })),158    topCountries: rkCountries ?? (await liveTop("country", 10)),159    topMetros: rkMetros ?? (await liveTop("metro", 10)),160    topOperators: rkOperators ?? (await liveTop("operator", 10)),161    typeBreakdown: typeRows.map((r) => ({ type: asType(r.facility_type), count: int(r.n) })),162    pipelineByYear: pipeline.map((r) => ({ year: reqStr(r.year), count: int(r.n), mw: round2(num(r.mw)) })),163    aiExpansion: { facilities: int(ai.facilities), projects: int(ai.projects), plannedMw: round2(num(ai.planned_mw)), recent: aiRecent.map(projectSummary) as ProjectSummary[] },164    latestEvents: latest,165    newProjects: newProjects.map(projectSummary),166    recentlyVerified,167    pulse: pulseData,168    fastestGrowingMetros: growing,169    majorProjects: major.map(projectSummary),170    operatorExpansion: expansion,171    powerEvents: await toEventDtos(powerRows),172    gridConstraints,173    coverage,174    ingestion: { lastRunAt: iso(crawlRow[0]?.last_started), runs24h: int(crawlRow[0]?.runs24), documents24h: int(ing.docs24), events24h: int(evRow[0]?.e24), connectorsHealthy: int(ing.healthy), connectorsTotal: int(ing.total) },175  };176}177