spb/datacenterindex
Public
HTML 53.9%
TypeScript 44.5%
JavaScript 0.6%
SQL 0.5%
1import type { EventDTO, OperatorDetail, OperatorSummary, SourceRef } from "@dci/core";2import { pg, andAll, page, likePattern, facilityView, knownMwAgg, countedAgg, facilityJoins, facilitySummaryCols, mwExpr, cloudRegionCols, eventCols, eventJoins, projectLive, OPERATIONAL_SET, AI_LEVELS, CORPORATE_EVENT_TYPES, type Fragment, type Sql } from "../lib/sql.js";3import { bool, int, num, record, reqIso, reqStr, str, strArray, type Row } from "../lib/rows.js";4import { cloudRegionSummary, facilitySummary, round2, share, statusBreakdown } from "../lib/dto.js";5import { findBySlugOrId } from "../lib/resolve.js";6import { listFacilities, provenanceFor } from "./facilities.js";7import { projectsWhere } from "./projects.js";8import { eventsForOperator, toEventDtos } from "./events.js";9import { buildSourceHistory, documentVersionsFor, sourceIdsOf, sourceRefsFor } from "../lib/source-history.js";10import { dimAggCols, expansionVelocity, pipelineBreakdown, scopeFor } from "../lib/pipeline.js";11import { claimsFor, dataQualityFor } from "../lib/quality.js";1213export interface OperatorFilters { q?: string; kind?: string; country?: string; sort?: "facilities" | "name" | "mw"; order?: "asc" | "desc"; page?: number; per_page?: number }1415/**16 * OperatorSummary from a row carrying `stats` (worker-refreshed, containment-aware) and optional live aggregate17 * columns (facility_count, known_mw, …). Live columns win when present (they reflect the current database); the18 * stats jsonb fills what the live query did not compute.19 */20export function operatorSummary(r: Row): OperatorSummary {21 const stats = (r.stats as Record<string, unknown> | null) ?? {};22 const live = r.facility_count != null;23 const fc = live ? int(r.facility_count) : int(stats.facilityCount);24 const withMw = live ? int(r.with_mw) : null;25 return {26 id: reqStr(r.id),27 slug: reqStr(r.slug),28 name: reqStr(r.name),29 kind: (str(r.kind) as OperatorSummary["kind"]) ?? null,30 website: str(r.website),31 hqCountryIso2: str(r.hq_country_iso2),32 facilityCount: fc,33 countryCount: live ? int(r.country_count) : int(stats.countryCount),34 metroCount: live ? int(r.metro_count) : int(stats.metroCount),35 knownMw: live ? round2(num(r.known_mw)) : round2(num(stats.knownMw)),36 plannedMw: live ? round2(num(r.planned_mw)) : round2(num(stats.plannedMw)),37 constructionMw: live ? round2(num(r.construction_mw)) : round2(num(stats.constructionMw)),38 projectCount: r.project_count != null ? int(r.project_count) : int(stats.projectCount),39 projectPlannedMw: r.project_planned_mw != null ? round2(num(r.project_planned_mw)) : round2(num(stats.projectPlannedMw)),40 aiCount: live ? int(r.ai) : int(stats.aiCount),41 cloudRegionCount: r.cloud_region_count != null ? int(r.cloud_region_count) : int(stats.cloudRegionCount),42 mwCoverage: withMw != null ? (fc ? share(withMw, fc) : 0) : num(stats.mwCoverage),43 isCloudProvider: bool(r.is_cloud_provider),44 isCarrier: bool(r.is_carrier),45 };46}4748/** Containment-aware aggregate per operator over the facility view, optionally restricted to a country / metro. */49function aggFragment(sql: Sql, scope: { countryIso2?: string; metroId?: string } = {}): Fragment {50 const conds: Fragment[] = [sql`f.operator_id is not null`];51 if (scope.countryIso2) conds.push(sql`f.country_iso2 = ${scope.countryIso2}`);52 if (scope.metroId) conds.push(sql`f.metro_id = ${scope.metroId}`);53 return sql`(54 select f.operator_id, count(distinct f.country_iso2)::int as country_count, count(distinct f.metro_id)::int as metro_count, ${dimAggCols(sql)}55 from ${facilityView(sql)} f where ${andAll(sql, conds)} group by f.operator_id56 )`;57}5859function projectAgg(sql: Sql): Fragment {60 return sql`(select p.operator_id, count(*)::int as n, sum(p.planned_mw) filter (where p.status in ('rumored','proposed','announced','permitting','approved','delayed','under_construction'))::float as mw from projects p where ${projectLive(sql)} group by p.operator_id)`;61}6263const OPERATOR_COLS = (sql: Sql) => sql`64 o.id, o.slug, o.name, o.kind, o.website, o.hq_country_iso2, o.stats, o.is_cloud_provider, o.is_carrier,65 coalesce(a.facilities, 0) as facility_count, coalesce(a.country_count, 0) as country_count, coalesce(a.metro_count, 0) as metro_count,66 a.known_mw, a.planned_mw, a.construction_mw, coalesce(a.with_mw, 0) as with_mw, coalesce(a.ai, 0) as ai,67 coalesce(pc.n, 0) as project_count, pc.mw as project_planned_mw,68 (select count(*)::int from cloud_regions cr where cr.provider_id = o.id and cr.status <> 'retired') as cloud_region_count`;6970export async function listOperators(f: OperatorFilters): Promise<{ items: OperatorSummary[]; total: number; page: number; perPage: number }> {71 const sql = pg();72 const pg_ = page(f.page, f.per_page, 100, 50);73 const conds: Fragment[] = [sql`true`];74 if (f.q) conds.push(sql`(o.name ilike ${likePattern(f.q)} or similarity(o.name, ${f.q}) > 0.3 or exists (select 1 from unnest(o.aliases) a where a ilike ${likePattern(f.q)}))`);75 if (f.kind) conds.push(sql`o.kind = ${f.kind}`);76 if (f.country) { const iso = f.country.toUpperCase(); conds.push(sql`(o.hq_country_iso2 = ${iso} or exists (select 1 from facilities x where x.operator_id = o.id and x.country_iso2 = ${iso} and x.merged_into is null))`); }77 const asc = f.order === "asc";78 const order = f.sort === "name" ? (asc ? sql`o.name asc` : sql`o.name desc`) : f.sort === "mw" ? (asc ? sql`a.known_mw asc nulls last, o.name` : sql`a.known_mw desc nulls last, o.name`) : asc ? sql`coalesce(a.facilities, 0) asc, o.name` : sql`coalesce(a.facilities, 0) desc, o.name`;79 const rows = await sql<Row[]>`80 select ${OPERATOR_COLS(sql)}, count(*) over() as total81 from operators o82 left join ${aggFragment(sql)} a on a.operator_id = o.id83 left join ${projectAgg(sql)} pc on pc.operator_id = o.id84 where ${andAll(sql, conds)}85 order by ${order}86 limit ${pg_.perPage} offset ${pg_.offset}`;87 return { items: rows.map(operatorSummary), total: rows.length ? int(rows[0]!.total) : 0, page: pg_.page, perPage: pg_.perPage };88}8990/** Operators ranked by facilities inside a scope (country or metro) — containment-aware counts. */91export async function operatorsForScope(scope: { countryIso2?: string; metroId?: string }, limit = 10): Promise<OperatorSummary[]> {92 const sql = pg();93 const rows = await sql<Row[]>`94 select o.id, o.slug, o.name, o.kind, o.website, o.hq_country_iso2, '{}'::jsonb as stats, o.is_cloud_provider, o.is_carrier,95 a.facilities as facility_count, a.country_count, a.metro_count, a.known_mw, a.planned_mw, a.construction_mw, a.with_mw, a.ai, 0 as project_count, null::float as project_planned_mw, 0 as cloud_region_count96 from ${aggFragment(sql, scope)} a join operators o on o.id = a.operator_id97 where a.facilities > 098 order by a.facilities desc, a.known_mw desc nulls last, o.name limit ${limit}`;99 return rows.map(operatorSummary);100}101102/** OperatorSummary for one id (live aggregates). null when unknown. */103export async function operatorSummaryById(id: string): Promise<OperatorSummary | null> {104 const sql = pg();105 const rows = await sql<Row[]>`select ${OPERATOR_COLS(sql)} from operators o left join ${aggFragment(sql)} a on a.operator_id = o.id left join ${projectAgg(sql)} pc on pc.operator_id = o.id where o.id = ${id}`;106 return rows[0] ? operatorSummary(rows[0]) : null;107}108109/** Corporate events (acquisitions, financing, partnerships, executive changes…) linked to an operator. */110export async function corporateEventsForOperator(operatorId: string, limit = 30): Promise<EventDTO[]> {111 const sql = pg();112 const rows = await sql<Row[]>`113 select ${eventCols(sql)} from events e ${eventJoins(sql)}114 where (e.operator_id = ${operatorId} or (e.entity_type = 'operator' and e.entity_id = ${operatorId})) and e.event_type = any(${CORPORATE_EVENT_TYPES}) and e.review_status <> 'rejected'115 order by e.detected_at desc limit ${limit}`;116 return toEventDtos(rows);117}118119/** Metros first entered by the operator within the last `months` months (by earliest facility opened_on / first_seen or project announcement). */120export async function newMarkets(operatorId: string, months = 12): Promise<Array<{ id: string; slug: string; name: string }>> {121 const sql = pg();122 const rows = await sql<Row[]>`123 with fm as (124 select f.metro_id, min(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) as first_d125 from facilities f where f.merged_into is null and f.operator_id = ${operatorId} and f.metro_id is not null group by 1126 union all127 select p.metro_id, min(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)128 from projects p where ${projectLive(sql)} and p.operator_id = ${operatorId} and p.metro_id is not null group by 1129 ), first as (select metro_id, min(first_d) as first_d from fm group by 1)130 select m.id, m.slug, m.name from first join metros m on m.id = first.metro_id where first.first_d >= current_date - make_interval(months => ${months}) order by first.first_d desc limit 25`;131 return rows.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) }));132}133134export async function getOperatorDetail(idOrSlug: string, fPage = 1): Promise<{ detail: OperatorDetail; sources: SourceRef[] } | null> {135 const sql = pg();136 const row = await findBySlugOrId("operators", idOrSlug);137 if (!row) return null;138 const id = String(row.id);139 const scope = scopeFor(sql, { operatorId: id });140 const known = knownMwAgg(sql), counted = countedAgg(sql);141 const [sumRows, countryRows, metroRows, statusRows, facilities, projects, recentEvents, provenance, versions, parentRows, pipeline, velocity, aiRows, cloudRows, corporateEvents, claims] = await Promise.all([142 sql<Row[]>`select ${OPERATOR_COLS(sql)} from operators o left join ${aggFragment(sql)} a on a.operator_id = o.id left join ${projectAgg(sql)} pc on pc.operator_id = o.id where o.id = ${id}`,143 sql<Row[]>`144 select f.country_iso2 as iso2, c.name, c.slug, count(*) filter (where ${counted})::int as n, sum(${known}) filter (where f.status = any(${OPERATIONAL_SET}))::float as known_mw145 from ${facilityView(sql)} f left join countries c on c.iso2 = f.country_iso2146 where f.operator_id = ${id} and f.country_iso2 is not null147 group by 1, 2, 3 order by n desc, c.name`,148 sql<Row[]>`149 select m.id, m.slug, m.name, m.country_iso2, count(*) filter (where ${counted})::int as n, sum(${known}) filter (where f.status = any(${OPERATIONAL_SET}))::float as known_mw150 from ${facilityView(sql)} f join metros m on m.id = f.metro_id151 where f.operator_id = ${id} group by 1, 2, 3, 4 order by n desc, m.name limit 100`,152 sql<Row[]>`select status, count(*)::int as n from facilities where operator_id = ${id} and merged_into is null group by status`,153 listFacilities({ operatorId: id, page: fPage, per_page: 50, sort: "mw", order: "desc" }),154 projectsWhere(sql`${projectLive(sql)} and p.operator_id = ${id}`, 20),155 eventsForOperator(id, 20),156 provenanceFor("operator", id),157 documentVersionsFor("operator", id),158 row.parent_id ? sql<Row[]>`select id, slug, name from operators where id = ${String(row.parent_id)}` : Promise.resolve([] as Row[]),159 pipelineBreakdown(scope),160 expansionVelocity(scope),161 sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and (f.operator_id = ${id} or f.owner_id = ${id}) and f.ai_evidence = any(${AI_LEVELS}) order by ${mwExpr(sql)} desc nulls last, f.name limit 20`,162 sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.provider_id = ${id} order by r.country_iso2, r.code limit 500`,163 corporateEventsForOperator(id, 30),164 claimsFor("operator", id),165 ]);166 const summary = operatorSummary(sumRows[0] ?? row);167 const completeness = Math.round(([row.website, row.hq_country_iso2, row.kind, row.description].filter((v) => v != null && v !== "").length / 4) * 100);168 const dataQuality = await dataQualityFor("operator", id, completeness, str(row.updated_at));169 const counted_total = summary.facilityCount || countryRows.reduce((a, c) => a + int(c.n), 0);170 const detail: OperatorDetail = {171 ...summary,172 pipeline,173 velocity,174 topCountries: countryRows.slice(0, 10).map((c) => ({ iso2: reqStr(c.iso2), name: reqStr(c.name, reqStr(c.iso2)), slug: reqStr(c.slug, reqStr(c.iso2).toLowerCase()), facilityCount: int(c.n), knownMw: round2(num(c.known_mw)), share: share(int(c.n), counted_total) })),175 topMetros: metroRows.slice(0, 10).map((m) => ({ id: reqStr(m.id), slug: reqStr(m.slug), name: reqStr(m.name), countryIso2: reqStr(m.country_iso2), facilityCount: int(m.n), knownMw: round2(num(m.known_mw)), share: share(int(m.n), counted_total) })),176 aiFacilities: aiRows.map(facilitySummary),177 cloudRegions: cloudRows.map(cloudRegionSummary),178 corporateEvents,179 claims,180 dataQuality,181 aliases: strArray(row.aliases),182 description: str(row.description),183 parent: parentRows[0] ? { id: String(parentRows[0].id), slug: String(parentRows[0].slug), name: String(parentRows[0].name) } : null,184 countries: countryRows.map((c) => ({ iso2: reqStr(c.iso2), name: reqStr(c.name, reqStr(c.iso2)), slug: reqStr(c.slug, reqStr(c.iso2).toLowerCase()), facilityCount: int(c.n), knownMw: round2(num(c.known_mw)) })),185 metros: metroRows.map((m) => ({ id: reqStr(m.id), slug: reqStr(m.slug), name: reqStr(m.name), countryIso2: reqStr(m.country_iso2), facilityCount: int(m.n) })),186 statusBreakdown: statusBreakdown(statusRows),187 facilities: facilities.items,188 projects,189 recentEvents,190 sourceHistory: buildSourceHistory(provenance, recentEvents, versions),191 externalIds: record(row.external_ids),192 updatedAt: reqIso(row.updated_at),193 };194 const sources = await sourceRefsFor(sourceIdsOf(provenance, recentEvents, versions, claims, corporateEvents));195 return { detail, sources };196}197198/** Operator candidates for query interpretation (trigram). */199export async function operatorCandidates(q: string, limit = 5): Promise<Array<{ id: string; slug: string; name: string; similarity: number }>> {200 const sql = pg();201 // SQL does the cheap prefilter (trigram or substring); word-boundary verification happens here so short aliases202 // ("IO") never match inside unrelated words ("operatIOnal").203 const rows = await sql<Row[]>`204 select o.id, o.slug, o.name, o.aliases, similarity(o.name, ${q}) as sim205 from operators o206 where (o.name % ${q} and similarity(o.name, ${q}) >= 0.45) or position(lower(o.name) in lower(${q})) > 0207 or exists (select 1 from unnest(o.aliases) a where length(a) >= 3 and position(lower(a) in lower(${q})) > 0)208 order by sim desc, length(o.name) desc limit ${limit * 4}`;209 const esc = (x: string) => x.replace(/[.*+?^${}()|[\]\\]/g, "\\$&");210 const bounded = (needle: string) => new RegExp(`(^|[^\\p{L}\\p{N}])${esc(needle)}([^\\p{L}\\p{N}]|$)`, "iu").test(q);211 return rows212 .map((r) => {213 const name = reqStr(r.name);214 const aliases = Array.isArray(r.aliases) ? (r.aliases as string[]) : [];215 let similarity = num(r.sim) ?? 0;216 if (bounded(name)) similarity = Math.max(similarity, 0.95);217 else if (aliases.some((a) => a.length >= 3 && bounded(a))) similarity = Math.max(similarity, 0.9);218 return { id: reqStr(r.id), slug: reqStr(r.slug), name, similarity };219 })220 .filter((r) => r.similarity >= 0.45)221 .sort((a, b) => b.similarity - a.similarity || b.name.length - a.name.length)222 .slice(0, limit);223}224