spb/datacenterindex
Public
HTML 53.9%
TypeScript 44.5%
JavaScript 0.6%
SQL 0.5%
1import type { ClaimDTO, CloudRegionSummary, EntityHistory, EventDTO, FacilityDetail, FacilityFilters, FacilitySummary, GridConstraintDTO, ProvenanceDTO, SourceRef } from "@dci/core";2import { pg, andAll, facilityJoins, facilitySummaryCols, mwExpr, page, bboxAround, haversineExpr, yearExpr, likePattern, PIPELINE_SET, cloudRegionCols, gridConstraintCols, gridConstraintJoins, POWER_EVENT_TYPES, GRID_EVENT_TYPES, AI_LEVELS, type Fragment, type Sql } from "../lib/sql.js";3import { int, num, record, reqIso, reqStr, str, strArray, type Row } from "../lib/rows.js";4import { cloudRegionSummary, facilitySummary, gridConstraintDto, gridConstraintFromEvent, provenanceDto } from "../lib/dto.js";5import { csv } from "../lib/http.js";6import { findBySlugOrId } from "../lib/resolve.js";7import { nearby } from "../lib/nearby.js";8import { capacityHistory, claimsFor, dataQualityFor, entityHistory, eventsForSubject, provenanceAll } from "../lib/quality.js";9import { eventsForEntity, eventsWhere } from "./events.js";10import { projectsForFacility } from "./projects.js";11import { buildSourceHistory, documentVersionsFor, sourceIdsOf, sourceRefsFor } from "../lib/source-history.js";1213export type FacilitySort = NonNullable<FacilityFilters["sort"]>;1415export interface FacilityQuery extends Omit<FacilityFilters, "country" | "status" | "type" | "confidence"> {16 country?: string[];17 status?: string[];18 type?: string[];19 confidence?: string[];20 /** extra: restrict to ids */21 ids?: string[];22 /** extra: restrict to a metro id / country iso2 / operator id directly */23 metroId?: string;24 countryIso2?: string;25 operatorId?: string;26 /** extra: AI evidence levels (confirmed, likely, associated, unknown) */27 aiEvidence?: string[];28 /** extra: geo precision values */29 locationPrecision?: string[];30 /** extra: record scope (building | facility | campus) */31 recordScope?: string[];32}3334/** Translate raw FacilityFilters (csv strings) into a FacilityQuery. */35export function toFacilityQuery(f: FacilityFilters): FacilityQuery {36 return { ...f, country: csv(f.country).map((s) => s.toUpperCase()), status: csv(f.status), type: csv(f.type), confidence: csv(f.confidence) };37}3839/** WHERE fragment for a FacilityQuery (aliases f/o/m/c as in facilityJoins). */40export function facilityConds(sql: Sql, q: FacilityQuery): Fragment[] {41 const conds: Fragment[] = [sql`f.merged_into is null`];42 const mw = mwExpr(sql);43 if (q.q) {44 const text = q.q.trim();45 if (text) conds.push(sql`(f.search @@ plainto_tsquery('simple', ${text}) or f.name ilike ${likePattern(text)} or f.city ilike ${likePattern(text)} or o.name ilike ${likePattern(text)})`);46 }47 if (q.ids?.length) conds.push(sql`f.id = any(${q.ids})`);48 if (q.country?.length) conds.push(sql`f.country_iso2 = any(${q.country})`);49 if (q.countryIso2) conds.push(sql`f.country_iso2 = ${q.countryIso2}`);50 if (q.region) conds.push(sql`(c.region ilike ${q.region} or c.subregion ilike ${q.region} or f.region_name ilike ${q.region})`);51 if (q.metro) conds.push(sql`(m.slug = ${q.metro} or m.id = ${q.metro})`);52 if (q.metroId) conds.push(sql`f.metro_id = ${q.metroId}`);53 if (q.operator) conds.push(sql`(o.slug = ${q.operator} or o.id = ${q.operator} or f.owner_id in (select id from operators where slug = ${q.operator}))`);54 if (q.operatorId) conds.push(sql`(f.operator_id = ${q.operatorId} or f.owner_id = ${q.operatorId})`);55 if (q.status?.length) conds.push(sql`f.status = any(${q.status})`);56 if (q.type?.length) conds.push(sql`f.facility_type = any(${q.type})`);57 if (q.min_mw != null) conds.push(sql`${mw} >= ${q.min_mw}`);58 if (q.max_mw != null) conds.push(sql`${mw} <= ${q.max_mw}`);59 if (q.opened_from != null) conds.push(sql`${yearExpr(sql, sql`f.opened_on`)} >= ${q.opened_from}`);60 if (q.opened_to != null) conds.push(sql`${yearExpr(sql, sql`f.opened_on`)} <= ${q.opened_to}`);61 if (q.planned_from != null || q.planned_to != null) {62 const y = yearExpr(sql, sql`coalesce(f.opened_on, f.construction_started_on, f.announced_on)`);63 conds.push(sql`f.status = any(${PIPELINE_SET})`);64 if (q.planned_from != null) conds.push(sql`${y} >= ${q.planned_from}`);65 if (q.planned_to != null) conds.push(sql`${y} <= ${q.planned_to}`);66 }67 if (q.cloud) {68 conds.push(sql`(exists (select 1 from facility_tenants ft join operators p on p.id = ft.operator_id where ft.facility_id = f.id and ft.role = 'cloud' and (p.slug = ${q.cloud} or p.id = ${q.cloud}))69 or (f.metro_id is not null and exists (select 1 from cloud_regions r join operators p on p.id = r.provider_id where r.metro_id = f.metro_id and (p.slug = ${q.cloud} or p.id = ${q.cloud}))))`);70 }71 if (q.hyperscale === true) conds.push(sql`(f.is_hyperscale or f.facility_type = 'hyperscale')`);72 if (q.hyperscale === false) conds.push(sql`(not f.is_hyperscale and f.facility_type <> 'hyperscale')`);73 if (q.colocation === true) conds.push(sql`f.facility_type in ('colocation', 'carrier_hotel', 'wholesale')`);74 if (q.ai === true) conds.push(sql`(f.is_ai or f.facility_type = 'ai' or f.ai_evidence = any(${AI_LEVELS}))`);75 if (q.ai === false) conds.push(sql`(not f.is_ai and f.facility_type <> 'ai' and f.ai_evidence <> all(${AI_LEVELS}))`);76 if (q.aiEvidence?.length) conds.push(sql`f.ai_evidence = any(${q.aiEvidence})`);77 if (q.locationPrecision?.length) conds.push(sql`f.geo_precision = any(${q.locationPrecision})`);78 if (q.recordScope?.length) conds.push(sql`f.record_scope = any(${q.recordScope})`);79 if (q.renewable === true) conds.push(sql`f.renewable_claim is not null`);80 if (q.has_mw === true) conds.push(sql`${mw} is not null`);81 if (q.has_mw === false) conds.push(sql`${mw} is null`);82 if (q.has_geo === true) conds.push(sql`f.lat is not null and f.lng is not null`);83 if (q.has_geo === false) conds.push(sql`f.lat is null`);84 if (q.confidence?.length) conds.push(sql`f.confidence = any(${q.confidence})`);85 return conds;86}8788function orderBy(sql: Sql, sort: FacilitySort | undefined, order: "asc" | "desc" | undefined): Fragment {89 const dir = order ?? (sort === "name" ? "asc" : "desc");90 const asc = dir === "asc";91 switch (sort) {92 case "name": return asc ? sql`f.name asc, f.id asc` : sql`f.name desc, f.id desc`;93 case "mw": return asc ? sql`${mwExpr(sql)} asc nulls last, f.name asc` : sql`${mwExpr(sql)} desc nulls last, f.name asc`;94 case "opened": return asc ? sql`f.opened_on asc nulls last, f.name asc` : sql`f.opened_on desc nulls last, f.name asc`;95 case "completeness": return asc ? sql`f.completeness asc, f.name asc` : sql`f.completeness desc, f.name asc`;96 case "updated": default: return asc ? sql`f.updated_at asc, f.id asc` : sql`f.updated_at desc, f.id desc`;97 }98}99100export async function listFacilities(q: FacilityQuery): Promise<{ items: FacilitySummary[]; total: number; page: number; perPage: number }> {101 const sql = pg();102 const pg_ = page(q.page, q.per_page, 100, 24);103 const rows = await sql<Row[]>`104 select ${facilitySummaryCols(sql)}, count(*) over() as total105 from facilities f ${facilityJoins(sql)}106 where ${andAll(sql, facilityConds(sql, q))}107 order by ${orderBy(sql, q.sort, q.order)}108 limit ${pg_.perPage} offset ${pg_.offset}`;109 const total = rows.length ? int(rows[0]!.total) : pg_.page > 1 ? await countFacilities(q) : 0;110 return { items: rows.map(facilitySummary), total, page: pg_.page, perPage: pg_.perPage };111}112113export async function countFacilities(q: FacilityQuery): Promise<number> {114 const sql = pg();115 const rows = await sql<Row[]>`select count(*)::int as n from facilities f ${facilityJoins(sql)} where ${andAll(sql, facilityConds(sql, q))}`;116 return int(rows[0]?.n);117}118119export async function facilitiesByIds(ids: string[]): Promise<FacilitySummary[]> {120 if (!ids.length) return [];121 const sql = pg();122 const rows = await sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.id = any(${ids})`;123 const by = new Map(rows.map((r) => [String(r.id), facilitySummary(r)]));124 return ids.map((id) => by.get(id)).filter((x): x is FacilitySummary => Boolean(x));125}126127/** Facilities matching an arbitrary condition over `facilities f` (+ facilityJoins aliases), ordered by MW. */128export async function facilitiesWhere(cond: Fragment, limit = 20, orderBy?: Fragment): Promise<FacilitySummary[]> {129 const sql = pg();130 const rows = await sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.merged_into is null and (${cond}) order by ${orderBy ?? sql`${mwExpr(sql)} desc nulls last, f.name`} limit ${limit}`;131 return rows.map(facilitySummary);132}133134export async function recentlyVerifiedFacilities(limit = 10): Promise<FacilitySummary[]> {135 const sql = pg();136 const rows = await sql<Row[]>`137 select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)}138 where f.merged_into is null and f.last_verified is not null139 order by f.last_verified desc limit ${limit}`;140 return rows.map(facilitySummary);141}142143async function nearbyFacilities(id: string, lat: number, lng: number, km = 25, limit = 8): Promise<FacilitySummary[]> {144 const sql = pg();145 const b = bboxAround(lat, lng, km);146 const dist = haversineExpr(sql, lat, lng);147 const rows = await sql<Row[]>`148 select ${facilitySummaryCols(sql)}, ${dist} as distance_km149 from facilities f ${facilityJoins(sql)}150 where f.merged_into is null and f.id <> ${id} and f.lat between ${b.minLat} and ${b.maxLat} and f.lng between ${b.minLng} and ${b.maxLng} and ${dist} <= ${km}151 order by ${dist} asc limit ${limit}`;152 return rows.map(facilitySummary);153}154155async function cloudRegionsInMetro(metroId: string | null, limit = 12): Promise<CloudRegionSummary[]> {156 if (!metroId) return [];157 const sql = pg();158 const rows = await sql<Row[]>`select ${cloudRegionCols(sql)} from cloud_regions r join operators pr on pr.id = r.provider_id where r.metro_id = ${metroId} order by pr.name, r.code limit ${limit}`;159 return rows.map(cloudRegionSummary);160}161162export async function provenanceFor(entityType: string, entityId: string, limit = 400): Promise<ProvenanceDTO[]> {163 const sql = pg();164 const rows = await sql<Row[]>`165 select p.field, p.value, p.source_id, s.name as source_name, s.kind as source_kind, p.url, p.first_observed, p.last_observed, p.retrieved_at, p.confidence, p.is_estimate, p.method, p.is_winner, p.scope, p.run_id, p.document_id166 from provenance p left join sources s on s.id = p.source_id167 where p.entity_type = ${entityType} and p.entity_id = ${entityId} and p.is_current168 order by p.field, p.is_winner desc, p.last_observed desc limit ${limit}`;169 return rows.map(provenanceDto);170}171172/** Grid constraints (grid_constraints rows + grid / utility / power events) for a metro and/or country. */173export async function gridConstraintsFor(scope: { metroId?: string | null; countryIso2?: string | null }, limit = 20): Promise<GridConstraintDTO[]> {174 const sql = pg();175 const conds: Fragment[] = [];176 if (scope.metroId) conds.push(sql`g.metro_id = ${scope.metroId}`);177 if (scope.countryIso2 && !scope.metroId) conds.push(sql`g.country_iso2 = ${scope.countryIso2}`);178 if (!conds.length) return [];179 const [rows, events] = await Promise.all([180 sql<Row[]>`select ${gridConstraintCols(sql)} from grid_constraints g ${gridConstraintJoins(sql)} where ${conds.reduce<Fragment>((a, c) => sql`${a} or ${c}`, sql`false`)} order by g.effective_date desc nulls last, g.created_at desc limit ${limit}`,181 scope.metroId ? eventsWhere(sql`e.metro_id = ${scope.metroId} and e.event_type = any(${GRID_EVENT_TYPES})`, limit) : Promise.resolve([] as EventDTO[]),182 ]);183 const out = rows.map(gridConstraintDto);184 const seen = new Set(out.map((g) => g.eventId).filter(Boolean));185 for (const e of events) if (!seen.has(e.id)) out.push(gridConstraintFromEvent(e));186 return out.slice(0, limit);187}188189export const POWER_CONTEXT_NOTE = "utilityCapacityMw / gridConnectionMw are utility-side figures (power available or contracted from the grid), not IT load — never compare them with itCapacityMw. countryEnergy shows national grid averages (renewable share, generation), which do not describe this facility's contracted electricity or power purchase agreements. Grid constraints are public reports attached to the facility's market or country, not to the site itself.";190191/** Resolve a facility by slug or id, following merged_into to the survivor. */192export async function resolveFacilityRow(idOrSlug: string): Promise<Row | null> {193 const sql = pg();194 const base = await findBySlugOrId("facilities", idOrSlug);195 if (!base) return null;196 const mergedInto = str(base.merged_into);197 return mergedInto ? ((await sql<Row[]>`select * from facilities where id = ${mergedInto} limit 1`)[0] ?? base) : base;198}199200export async function getFacilityDetail(idOrSlug: string, opts: { radiusKm?: number } = {}): Promise<{ detail: FacilityDetail; sources: SourceRef[] } | null> {201 const sql = pg();202 const row = await resolveFacilityRow(idOrSlug);203 if (!row) return null;204 const id = String(row.id);205 const lat = num(row.lat), lng = num(row.lng);206 const metroId = str(row.metro_id);207 const countryIso2 = str(row.country_iso2);208 const operatorId = str(row.operator_id);209 const [sumRows, aliasRows, tenantRows, ixpRows, cloudRegions, nearbyRows, projects, provenance, events, versions, ownerRows, campusRows, buildingRows, roleRows, claims, nearbyInfra, powerEvents, gridConstraints, energyRows, dataQuality] = await Promise.all([210 sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.id = ${id}`,211 sql<Row[]>`select alias from facility_aliases where facility_id = ${id} order by alias`,212 sql<Row[]>`select t.role, t.asn, o.id, o.slug, o.name from facility_tenants t join operators o on o.id = t.operator_id where t.facility_id = ${id} order by o.name`,213 sql<Row[]>`select x.id, x.slug, x.name from facility_ixps fx join ixps x on x.id = fx.ixp_id where fx.facility_id = ${id} order by x.name`,214 cloudRegionsInMetro(metroId),215 lat != null && lng != null ? nearbyFacilities(id, lat, lng) : Promise.resolve([] as FacilitySummary[]),216 projectsForFacility(id, operatorId, metroId),217 provenanceFor("facility", id),218 eventsForEntity("facility", id, 50),219 documentVersionsFor("facility", id),220 row.owner_id ? sql<Row[]>`select id, slug, name from operators where id = ${String(row.owner_id)}` : Promise.resolve([] as Row[]),221 row.campus_id ? sql<Row[]>`select id, slug, name from campuses where id = ${String(row.campus_id)}` : Promise.resolve([] as Row[]),222 sql<Row[]>`select ${facilitySummaryCols(sql)} from facilities f ${facilityJoins(sql)} where f.parent_facility_id = ${id} and f.merged_into is null order by f.name`,223 sql<Row[]>`select o.id, o.slug, o.name, (o.id = ${str(row.developer_id)}) as is_developer, (o.id = ${str(row.landowner_id)}) as is_landowner from operators o where o.id in (${str(row.developer_id) ?? ""}, ${str(row.landowner_id) ?? ""})`,224 claimsFor("facility", id),225 lat != null && lng != null ? nearby({ lat, lng, radiusKm: opts.radiusKm ?? 25, excludeFacilityId: id, limitPerType: 50 }) : Promise.resolve(null),226 eventsWhere(sql`e.event_type = any(${POWER_EVENT_TYPES}) and ((e.entity_type = 'facility' and e.entity_id = ${id}) ${operatorId && metroId ? sql`or (e.operator_id = ${operatorId} and e.metro_id = ${metroId})` : sql``})`, 20),227 gridConstraintsFor({ metroId, countryIso2 }),228 countryIso2 ? sql<Row[]>`select renewable_share, electricity_twh, stats_year from countries where iso2 = ${countryIso2}` : Promise.resolve([] as Row[]),229 dataQualityFor("facility", id, int(row.completeness), str(row.last_verified)),230 ]);231 const summary = facilitySummary(sumRows[0] ?? row);232 const carriers = tenantRows.filter((t) => t.role !== "cloud").map((t) => ({ id: String(t.id), slug: String(t.slug), name: String(t.name), asn: num(t.asn) }));233 const cloudProviders = tenantRows.filter((t) => t.role === "cloud").map((t) => ({ id: String(t.id), slug: String(t.slug), name: String(t.name) }));234 const owner = ownerRows[0] ? { id: String(ownerRows[0].id), slug: String(ownerRows[0].slug), name: String(ownerRows[0].name) } : null;235 const campus = campusRows[0] ? { id: String(campusRows[0].id), slug: String(campusRows[0].slug), name: String(campusRows[0].name) } : null;236 const refOf = (r: Row | undefined) => (r ? { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) } : null);237 const developer = refOf(roleRows.find((r) => r.is_developer === true));238 const landowner = refOf(roleRows.find((r) => r.is_landowner === true));239 const energy = energyRows[0];240 const detail: FacilityDetail = {241 ...summary,242 buildings: buildingRows.map(facilitySummary),243 developer,244 landowner,245 tenants: tenantRows.map((t) => ({ id: String(t.id), slug: String(t.slug), name: String(t.name), role: reqStr(t.role, "tenant") })),246 claims,247 capacityHistory: capacityHistory(provenance, claims, events),248 nearbyInfrastructure: nearbyInfra,249 powerContext: {250 utilityCapacityMw: num(row.utility_capacity_mw),251 gridConnectionMw: num(row.grid_connection_mw),252 powerEvents,253 gridConstraints,254 countryEnergy: energy ? { renewableShare: num(energy.renewable_share), electricityTwh: num(energy.electricity_twh), statsYear: num(energy.stats_year) } : null,255 note: POWER_CONTEXT_NOTE,256 },257 dataQuality,258 aliases: aliasRows.map((a) => String(a.alias)),259 owner,260 campus,261 address: str(row.address),262 postalCode: str(row.postal_code),263 geoSource: str(row.geo_source),264 tier: str(row.tier),265 buildingSqm: num(row.building_sqm),266 siteAreaHa: num(row.site_area_ha),267 rackCount: num(row.rack_count),268 pue: num(row.pue),269 coolingType: str(row.cooling_type),270 renewableClaim: str(row.renewable_claim),271 constructionStartedOn: str(row.construction_started_on),272 announcedOn: str(row.announced_on),273 website: str(row.website),274 description: str(row.description),275 certifications: strArray(row.certifications),276 carriers,277 cloudProviders,278 ixps: ixpRows.map((x) => ({ id: String(x.id), slug: String(x.slug), name: String(x.name) })),279 cloudRegions,280 nearby: nearbyRows,281 projects,282 provenance,283 events,284 sourceHistory: buildSourceHistory(provenance, events, versions),285 externalIds: record(row.external_ids),286 firstSeen: reqIso(row.first_seen),287 updatedAt: reqIso(row.updated_at),288 };289 const sources = await sourceRefsFor(sourceIdsOf(provenance, events, versions, claims));290 return { detail, sources };291}292293/** /datacenters/:id/history */294export async function getFacilityHistory(idOrSlug: string): Promise<{ history: EntityHistory; sources: SourceRef[] } | null> {295 const row = await resolveFacilityRow(idOrSlug);296 if (!row) return null;297 const id = String(row.id);298 const [prov, claims, events] = await Promise.all([provenanceAll("facility", id), claimsFor("facility", id), eventsForSubject("facility", id)]);299 return { history: entityHistory("facility", id, prov, claims, events), sources: await sourceRefsFor(sourceIdsOf(prov, claims, events)) };300}301302/** /datacenters/:id/claims */303export async function getFacilityClaims(idOrSlug: string, opts: { status?: string[]; predicate?: string } = {}): Promise<{ id: string; claims: ClaimDTO[]; sources: SourceRef[] } | null> {304 const row = await resolveFacilityRow(idOrSlug);305 if (!row) return null;306 const id = String(row.id);307 const claims = await claimsFor("facility", id, opts);308 return { id, claims, sources: await sourceRefsFor(sourceIdsOf(claims)) };309}310311/** /datacenters/:id/provenance — every observation, current and superseded. */312export async function getFacilityProvenance(idOrSlug: string): Promise<{ id: string; provenance: ProvenanceDTO[]; current: number; sources: SourceRef[] } | null> {313 const row = await resolveFacilityRow(idOrSlug);314 if (!row) return null;315 const id = String(row.id);316 const provenance = await provenanceAll("facility", id);317 const sql = pg();318 const cur = await sql<Row[]>`select count(*)::int as n from provenance where entity_type = 'facility' and entity_id = ${id} and is_current`;319 return { id, provenance, current: int(cur[0]?.n), sources: await sourceRefsFor(sourceIdsOf(provenance)) };320}321322/** Distinct facility cities matching a text (for search). */323export async function searchCities(text: string, limit = 5): Promise<Array<{ city: string; countryIso2: string | null; count: number }>> {324 const sql = pg();325 const rows = await sql<Row[]>`326 select f.city, f.country_iso2, count(*)::int as n327 from facilities f328 where f.merged_into is null and f.city is not null and (f.city ilike ${likePattern(text)} or similarity(f.city, ${text}) > 0.45)329 group by f.city, f.country_iso2330 order by (lower(f.city) = lower(${text})) desc, n desc limit ${limit}`;331 return rows.map((r) => ({ city: String(r.city), countryIso2: str(r.country_iso2), count: int(r.n) }));332}333