spb/datacenterindex

1/**2 * /connectivity — IXPs, cloud regions, carrier hotels and per-metro interconnection density. Cable landing stations3 * have no connector yet and are returned empty with a note.4 */5import type { ConnectivityOverview } from "@dci/core";6import { pg, cloudRegionCols, facilityJoins, facilitySummaryCols } from "../lib/sql.js";7import { int, reqStr, type Row } from "../lib/rows.js";8import { cloudRegionSummary, facilitySummary, ixpSummary } from "../lib/dto.js";910export const CONNECTIVITY_NOTE = "IXP facility / operator counts come from published IXP↔facility links (facility_ixps); IXPs have no coordinates of their own and are placed at their metro reference point on maps. Carrier hotels = facility_type carrier_hotel or ≥ 20 carriers published on the facility. Submarine cable landing stations are empty until a licensed connector exists — never inferred. Cloud regions are associated with a market, not hosted at a specific facility unless a source says so.";1112export async function connectivityOverview(): Promise<ConnectivityOverview> {13  const sql = pg();14  const [ixps, cloudRegions, carrierHotels, byMetro] = await Promise.all([15    sql<Row[]>`select x.id, x.slug, x.name, x.name_long, x.city, x.country_iso2, x.website, x.network_count,16        (select count(*)::int from facility_ixps fx where fx.ixp_id = x.id) as facility_count,17        (select count(distinct f.operator_id)::int from facility_ixps fx join facilities f on f.id = fx.facility_id where fx.ixp_id = x.id and f.operator_id is not null) as operator_count,18        m.id as met_id, m.slug as met_slug, m.name as met_name, m.lat, m.lng19      from ixps x left join metros m on m.id = x.metro_id20      order by x.network_count desc nulls last, facility_count desc, x.name limit 100`,21    sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.status <> 'retired' order by pr.name, r.country_iso2, r.code limit 2000`,22    sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and (f.facility_type = 'carrier_hotel' or coalesce(f.carriers_count, 0) >= 20) order by f.carriers_count desc nulls last, f.completeness desc, f.name limit 50`,23    sql<Row[]>`select * from (select m.id, m.slug, m.name, m.country_iso2,24        (select count(*)::int from ixps x where x.metro_id = m.id) as ixps,25        (select count(*)::int from cloud_regions r where r.metro_id = m.id and r.status <> 'retired') as cloud_regions,26        (select count(*)::int from facilities f where f.metro_id = m.id and f.merged_into is null and (coalesce(f.carriers_count, 0) > 0 or exists (select 1 from facility_tenants t where t.facility_id = f.id and t.role in ('carrier', 'network')))) as facilities_with_carriers,27        (select count(*)::int from facilities f where f.metro_id = m.id and f.merged_into is null) as facilities28      from metros m29      where exists (select 1 from ixps x where x.metro_id = m.id) or exists (select 1 from cloud_regions r where r.metro_id = m.id) or exists (select 1 from facilities f where f.metro_id = m.id and f.merged_into is null and (coalesce(f.carriers_count, 0) > 0 or exists (select 1 from facility_tenants t where t.facility_id = f.id)))) t order by t.ixps + t.cloud_regions + t.facilities_with_carriers desc, t.facilities desc limit 50`,30  ]);31  return {32    ixps: ixps.map((r) => ({ ...ixpSummary(r), operatorCount: int(r.operator_count) })),33    cloudRegions: cloudRegions.map(cloudRegionSummary),34    carrierHotels: carrierHotels.map(facilitySummary),35    landingStations: [],36    byMetro: byMetro.map((r) => ({ id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), countryIso2: reqStr(r.country_iso2), ixps: int(r.ixps), cloudRegions: int(r.cloud_regions), facilitiesWithCarriers: int(r.facilities_with_carriers), facilities: int(r.facilities) })),37    note: CONNECTIVITY_NOTE,38  };39}40