spb/datacenterindex
Public
HTML 53.9%
TypeScript 44.5%
JavaScript 0.6%
SQL 0.5%
1import type { ClaimDTO, EntityHistory, EventDTO, FacilityStatus, HistoryPoint, ProjectDetail, ProjectStageDTO, ProjectSummary, SourceRef } from "@dci/core";2import { PROJECT_STAGES, parsePartialDate, partialDateSortKey, projectTransition } from "@dci/core";3import { pg, andAll, projectJoins, projectSummaryCols, projectLive, page, likePattern, yearExpr, AI_LEVELS, type Fragment } from "../lib/sql.js";4import { int, num, reqStr, str, type Row } from "../lib/rows.js";5import { asStatus, projectSummary } from "../lib/dto.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 { eventsForProject } from "./events.js";10import { provenanceFor } from "./facilities.js";11import { buildSourceHistory, documentVersionsFor, sourceIdsOf, sourceRefsFor } from "../lib/source-history.js";1213export interface ProjectFilters {14 status?: string[];15 country?: string; // iso216 operator?: string; // slug or id17 metro?: string; // slug or id18 facilityId?: string;19 min_mw?: number;20 max_mw?: number;21 ai?: boolean;22 project_class?: string[];23 evidence_level?: string[];24 expected_from?: number;25 expected_to?: number;26 announced_since?: string;27 q?: string;28 sort?: "updated" | "mw" | "announced" | "opening";29 order?: "asc" | "desc";30 page?: number;31 per_page?: number;32}3334export function projectConds(f: ProjectFilters): Fragment[] {35 const sql = pg();36 const c: Fragment[] = [projectLive(sql)];37 if (f.status?.length) c.push(sql`p.status = any(${f.status})`);38 if (f.country) c.push(sql`p.country_iso2 = ${f.country.toUpperCase()}`);39 if (f.operator) c.push(sql`(o.slug = ${f.operator} or o.id = ${f.operator})`);40 if (f.metro) c.push(sql`(m.slug = ${f.metro} or m.id = ${f.metro})`);41 if (f.facilityId) c.push(sql`p.facility_id = ${f.facilityId}`);42 if (f.min_mw != null) c.push(sql`p.planned_mw >= ${f.min_mw}`);43 if (f.max_mw != null) c.push(sql`p.planned_mw <= ${f.max_mw}`);44 if (f.ai === true) c.push(sql`(p.is_ai or p.ai_evidence = any(${AI_LEVELS}))`);45 if (f.ai === false) c.push(sql`(not p.is_ai and p.ai_evidence <> all(${AI_LEVELS}))`);46 if (f.project_class?.length) c.push(sql`p.project_class = any(${f.project_class})`);47 if (f.evidence_level?.length) c.push(sql`p.evidence_level = any(${f.evidence_level})`);48 if (f.expected_from != null) c.push(sql`${yearExpr(sql, sql`p.expected_opening`)} >= ${f.expected_from}`);49 if (f.expected_to != null) c.push(sql`${yearExpr(sql, sql`p.expected_opening`)} <= ${f.expected_to}`);50 if (f.announced_since) c.push(sql`(p.announced_on >= ${f.announced_since} or (p.announced_on is null and p.created_at >= ${f.announced_since}::timestamptz))`);51 if (f.q) { const t = f.q.trim(); if (t) c.push(sql`(p.name ilike ${likePattern(t)} or similarity(p.name, ${t}) > 0.3 or o.name ilike ${likePattern(t)})`); }52 return c;53}5455function order(sort: ProjectFilters["sort"], dir: ProjectFilters["order"]): Fragment {56 const sql = pg();57 const asc = dir === "asc";58 switch (sort) {59 case "mw": return asc ? sql`p.planned_mw asc nulls last, p.name` : sql`p.planned_mw desc nulls last, p.name`;60 case "announced": return asc ? sql`p.announced_on asc nulls last, p.name` : sql`p.announced_on desc nulls last, p.name`;61 case "opening": return asc ? sql`p.expected_opening asc nulls last, p.name` : sql`p.expected_opening desc nulls last, p.name`;62 case "updated": default: return asc ? sql`p.last_update asc, p.id` : sql`p.last_update desc, p.id desc`;63 }64}6566export async function listProjects(f: ProjectFilters): Promise<{ items: ProjectSummary[]; total: number; page: number; perPage: number }> {67 const sql = pg();68 const pg_ = page(f.page, f.per_page, 100, 24);69 const rows = await sql<Row[]>`70 select ${projectSummaryCols(sql)}, count(*) over() as total71 from projects p ${projectJoins(sql)}72 where ${andAll(sql, projectConds(f))}73 order by ${order(f.sort, f.order)}74 limit ${pg_.perPage} offset ${pg_.offset}`;75 return { items: rows.map(projectSummary), total: rows.length ? int(rows[0]!.total) : 0, page: pg_.page, perPage: pg_.perPage };76}7778/** Projects linked to a facility: facility_id match, or same operator + metro. */79export async function projectsForFacility(facilityId: string, operatorId: string | null, metroId: string | null, limit = 12): Promise<ProjectSummary[]> {80 const sql = pg();81 const rows = await sql<Row[]>`82 select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)}83 where ${projectLive(sql)} and (p.facility_id = ${facilityId}84 ${operatorId && metroId ? sql`or (p.operator_id = ${operatorId} and p.metro_id = ${metroId})` : sql``})85 order by (p.facility_id = ${facilityId}) desc, p.last_update desc limit ${limit}`;86 return rows.map(projectSummary);87}8889/** Live projects matching a condition over `projects p` (+ projectJoins aliases o / pf / m). */90export async function projectsWhere(where: Fragment, limit = 10, orderBy?: Fragment): Promise<ProjectSummary[]> {91 const sql = pg();92 const rows = await sql<Row[]>`select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)} where ${projectLive(sql)} and (${where}) order by ${orderBy ?? sql`p.last_update desc`} limit ${limit}`;93 return rows.map(projectSummary);94}9596/** Resolve a project by slug or id; merged projects redirect to their survivor, hidden projects are never served. */97export async function resolveProjectRow(idOrSlug: string): Promise<Row | null> {98 const sql = pg();99 const base = await findBySlugOrId("projects", idOrSlug);100 if (!base) return null;101 let row = base;102 for (let hops = 0; hops < 5 && str(row.merged_into); hops++) {103 const next = (await sql<Row[]>`select * from projects where id = ${String(row.merged_into)} limit 1`)[0];104 if (!next) break;105 row = next;106 }107 if (row.hidden === true) return null;108 return row;109}110111// ─── lifecycle stages ──────────────────────────────────────────────────────────────────────────────112113type Stage = ProjectStageDTO["stage"];114const STAGE_COLUMN: Partial<Record<Stage, string>> = { announced: "announced_on", permitting: "permit_filed_on", approved: "approved_on", under_construction: "construction_started_on", operational: "opened_on" };115const TIMELINE_STAGE: Array<[RegExp, Stage]> = [116 [/cancel/i, "cancelled"], [/delay|on hold|paused/i, "delayed"], [/open|launch|live|commission|energi[sz]ed/i, "operational"], [/partial/i, "partially_operational"],117 [/construction|ground|topped/i, "under_construction"], [/approv|consent|permit(ted)?\b|green/i, "approved"], [/planning|filed|permit|zoning|application/i, "permitting"],118 [/announce|unveil|reveal|plan/i, "announced"], [/propos/i, "proposed"], [/rumo/i, "rumored"],119];120const EVENT_STAGE: Record<string, Stage> = { construction_started: "under_construction", planning_filed: "permitting", planning_approved: "approved", project_delayed: "delayed", project_cancelled: "cancelled", facility_opened: "operational", project_announced: "announced" };121122interface StageEvidence { date: string; url: string | null; sourceName: string | null; eventId: string | null }123124function isStage(s: unknown): s is Stage { return typeof s === "string" && (PROJECT_STAGES as readonly string[]).includes(s); }125126export function buildStages(row: Row, timeline: Array<{ date: string; type: string; url: string | null; sourceName: string | null }>, events: EventDTO[]): ProjectStageDTO[] {127 const current = asStatus(row.status);128 const ev = new Map<Stage, StageEvidence>();129 const put = (stage: Stage, e: StageEvidence) => {130 if (!e.date || !/^\d{4}/.test(e.date)) return;131 const prev = ev.get(stage);132 // earliest dated evidence wins; a dated column beats a detected_at fallback133 if (!prev || partialDateSortKey(e.date) < partialDateSortKey(prev.date)) ev.set(stage, e);134 };135 for (const [stage, col] of Object.entries(STAGE_COLUMN) as Array<[Stage, string]>) { const d = str(row[col]); if (d) put(stage, { date: d, url: str(row.source_url), sourceName: null, eventId: null }); }136 if (current === "operational" && !ev.has("operational") && str(row.expected_opening) && partialDateSortKey(str(row.expected_opening)) <= Date.now()) put("operational", { date: String(row.expected_opening), url: str(row.source_url), sourceName: null, eventId: null });137 for (const t of timeline) { const stage = TIMELINE_STAGE.find(([re]) => re.test(t.type))?.[1]; if (stage) put(stage, { date: t.date, url: t.url, sourceName: t.sourceName, eventId: null }); }138 for (const e of events) {139 let stage: Stage | undefined = EVENT_STAGE[e.eventType];140 if (e.eventType === "project_status_changed" || e.eventType === "status_changed") { const to = typeof e.newValue === "object" && e.newValue ? (e.newValue as Record<string, unknown>).status ?? (e.newValue as Record<string, unknown>).to : e.newValue; stage = isStage(to) ? to : undefined; }141 if (stage) put(stage, { date: e.effectiveDate ?? e.detectedAt.slice(0, 10), url: e.url || null, sourceName: e.sourceName, eventId: e.id });142 }143 // `expansion` is an operational site growing — it sits at the operational stage of the lifecycle view144 const currentStage: Stage | null = current === "expansion" ? "operational" : isStage(current) ? current : null;145 return PROJECT_STAGES.map((stage) => {146 const isCurrent = currentStage === stage;147 const side = stage === "delayed" || stage === "cancelled";148 // a stage is reached when it is the current one, when dated evidence placed the project there, or when the pipeline149 // moved forward past it (a project under construction was announced); side branches only when current or dated150 const forward = !side && currentStage != null && (currentStage === "delayed" || currentStage === "cancelled" ? ["rumored", "proposed", "announced"].includes(stage) : projectTransition(stage, currentStage) === "forward");151 const reached = isCurrent || ev.has(stage) || forward;152 const e = ev.get(stage);153 return { stage, date: e?.date ?? null, reached, current: isCurrent, url: e?.url ?? null, sourceName: e?.sourceName ?? null, eventId: e?.eventId ?? null };154 });155}156157function daysBetween(a: string | null, b: string | null): number | null {158 if (!a || !b) return null;159 const pa = parsePartialDate(a), pb = parsePartialDate(b);160 if (!pa || !pb) return null;161 const da = new Date(pa.length === 4 ? `${pa}-01-01` : pa.length === 7 ? `${pa}-01` : pa.slice(0, 10));162 const db = new Date(pb.length === 4 ? `${pb}-01-01` : pb.length === 7 ? `${pb}-01` : pb.slice(0, 10));163 if (Number.isNaN(da.getTime()) || Number.isNaN(db.getTime())) return null;164 return Math.round((db.getTime() - da.getTime()) / 86_400_000);165}166167export function velocityDays(stages: ProjectStageDTO[]): ProjectDetail["velocityDays"] {168 const d = (s: Stage) => stages.find((x) => x.stage === s)?.date ?? null;169 return {170 announcedToPermitting: daysBetween(d("announced"), d("permitting")),171 permittingToApproval: daysBetween(d("permitting"), d("approved")),172 approvalToConstruction: daysBetween(d("approved"), d("under_construction")),173 constructionToOpening: daysBetween(d("under_construction"), d("operational")),174 announcedToConstruction: daysBetween(d("announced"), d("under_construction")),175 };176}177178const COMPLETENESS_COLS = ["operator_id", "country_iso2", "lat", "planned_mw", "expected_opening", "announced_on", "description"];179export function projectCompleteness(row: Row): number {180 return Math.round((COMPLETENESS_COLS.filter((c) => row[c] != null && row[c] !== "").length / COMPLETENESS_COLS.length) * 100);181}182183function investmentPoints(claims: ClaimDTO[]): HistoryPoint[] {184 return claims.filter((c) => /usd$/.test(c.predicate) && c.status !== "rejected").map((c) => ({ date: c.publishedAt && /^\d{4}/.test(c.publishedAt) ? c.publishedAt : c.firstObserved, field: "investmentUsd", predicate: c.predicate, value: c.value ?? c.valueText, sourceId: c.sourceId, sourceName: c.sourceName, sourceKind: c.sourceKind, url: c.url, claimId: c.id, kind: "claim" as const }));185}186187export async function getProjectDetail(idOrSlug: string, opts: { radiusKm?: number } = {}): Promise<{ detail: ProjectDetail; sources: SourceRef[] } | null> {188 const sql = pg();189 const row = await resolveProjectRow(idOrSlug);190 if (!row) return null;191 const id = String(row.id);192 const lat = num(row.lat), lng = num(row.lng);193 const [sumRows, timelineRows, provenance, events, versions, campusRows, claims, nearbyInfra, dataQuality] = await Promise.all([194 sql<Row[]>`select ${projectSummaryCols(sql)} from projects p ${projectJoins(sql)} where p.id = ${id}`,195 sql<Row[]>`select t.event_date, t.event_type, t.description, t.url, s.name as source_name from project_timeline t left join sources s on s.id = t.source_id where t.project_id = ${id}`,196 provenanceFor("project", id),197 eventsForProject(id, 50),198 documentVersionsFor("project", id),199 row.campus_id ? sql<Row[]>`select id, slug, name from campuses where id = ${String(row.campus_id)}` : Promise.resolve([] as Row[]),200 claimsFor("project", id),201 lat != null && lng != null ? nearby({ lat, lng, radiusKm: opts.radiusKm ?? 25, excludeProjectId: id, limitPerType: 50 }) : Promise.resolve(null),202 dataQualityFor("project", id, projectCompleteness(row), null),203 ]);204 const summary = projectSummary(sumRows[0] ?? row);205 const timeline = timelineRows206 .map((t) => ({ date: reqStr(t.event_date), type: reqStr(t.event_type), description: reqStr(t.description), url: str(t.url), sourceName: str(t.source_name) }))207 .sort((a, b) => partialDateSortKey(a.date) - partialDateSortKey(b.date));208 const statusHistory = events209 .filter((e) => e.eventType === "project_status_changed" || e.eventType === "status_changed")210 .map((e) => ({ date: e.effectiveDate ?? e.detectedAt, from: e.oldValue != null ? asStatus(typeof e.oldValue === "object" ? (e.oldValue as Record<string, unknown>).status : e.oldValue) : null, to: asStatus(typeof e.newValue === "object" && e.newValue ? (e.newValue as Record<string, unknown>).status : e.newValue), url: e.url || null }))211 .sort((a, b) => (a.date < b.date ? -1 : a.date > b.date ? 1 : 0));212 const stages = buildStages(row, timeline, events);213 const relatedEvents = events.filter((e) => e.project && e.project.id === id && !(e.entityType === "project" && e.entityId === id));214 const history = [...capacityHistory(provenance, claims, events), ...investmentPoints(claims)].sort((a, b) => (a.date < b.date ? -1 : a.date > b.date ? 1 : 0));215 const detail: ProjectDetail = {216 ...summary,217 description: str(row.description),218 sourceUrl: str(row.source_url),219 campus: campusRows[0] ? { id: reqStr(campusRows[0].id), slug: reqStr(campusRows[0].slug), name: reqStr(campusRows[0].name) } : null,220 stages,221 velocityDays: velocityDays(stages),222 timeline,223 statusHistory,224 claims,225 history,226 nearbyInfrastructure: nearbyInfra,227 relatedEvents,228 dataQuality,229 provenance,230 events,231 sourceHistory: buildSourceHistory(provenance, events, versions),232 };233 const sources = await sourceRefsFor(sourceIdsOf(provenance, events, versions, claims));234 return { detail, sources };235}236237/** /projects/:slug/history */238export async function getProjectHistory(idOrSlug: string): Promise<{ history: EntityHistory; sources: SourceRef[] } | null> {239 const row = await resolveProjectRow(idOrSlug);240 if (!row) return null;241 const id = String(row.id);242 const [prov, claims, events] = await Promise.all([provenanceAll("project", id), claimsFor("project", id), eventsForSubject("project", id)]);243 return { history: entityHistory("project", id, prov, claims, events), sources: await sourceRefsFor(sourceIdsOf(prov, claims, events)) };244}245246/** /projects/:slug/claims */247export async function getProjectClaims(idOrSlug: string, opts: { status?: string[]; predicate?: string } = {}): Promise<{ id: string; claims: ClaimDTO[]; sources: SourceRef[] } | null> {248 const row = await resolveProjectRow(idOrSlug);249 if (!row) return null;250 const id = String(row.id);251 const claims = await claimsFor("project", id, opts);252 return { id, claims, sources: await sourceRefsFor(sourceIdsOf(claims)) };253}254255export interface PipelineAggregates {256 byStatus: Array<{ status: FacilityStatus; count: number; mw: number | null; investmentUsd: number | null }>;257 byYear: Array<{ year: string; count: number; mw: number | null }>;258 byCountry: Array<{ countryIso2: string; name: string | null; slug: string | null; count: number; mw: number | null }>;259 totals: { projects: number; plannedMw: number | null; investmentUsd: number | null; ai: number };260}261262export async function projectPipeline(): Promise<PipelineAggregates> {263 const sql = pg();264 const live = projectLive(sql);265 const [st, yr, co, tot] = await Promise.all([266 sql<Row[]>`select p.status, count(*)::int as n, sum(p.planned_mw)::float as mw, sum(p.investment_usd)::float as inv from projects p where ${live} group by p.status order by n desc`,267 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 ${live} and p.expected_opening ~ '^\\d{4}' and p.status not in ('cancelled','closed') group by 1 order by 1`,268 sql<Row[]>`select p.country_iso2, c.name, c.slug, count(*)::int as n, sum(p.planned_mw)::float as mw from projects p left join countries c on c.iso2 = p.country_iso2 where ${live} and p.country_iso2 is not null group by 1,2,3 order by n desc, mw desc nulls last limit 15`,269 sql<Row[]>`select count(*)::int as n, sum(p.planned_mw)::float as mw, sum(p.investment_usd)::float as inv, count(*) filter (where p.is_ai or p.ai_evidence = any(${AI_LEVELS}))::int as ai from projects p where ${live}`,270 ]);271 return {272 byStatus: st.map((r) => ({ status: asStatus(r.status), count: int(r.n), mw: num(r.mw), investmentUsd: num(r.inv) })),273 byYear: yr.map((r) => ({ year: reqStr(r.year), count: int(r.n), mw: num(r.mw) })),274 byCountry: co.map((r) => ({ countryIso2: reqStr(r.country_iso2), name: str(r.name), slug: str(r.slug), count: int(r.n), mw: num(r.mw) })),275 totals: { projects: int(tot[0]?.n), plannedMw: num(tot[0]?.mw), investmentUsd: num(tot[0]?.inv), ai: int(tot[0]?.ai) },276 };277}278