spb/datacenterindex

1/** Smaller public repositories: time series, sources, news, sitemap feeds. */2import type { SourceRef } from "@dci/core";3import { pg, page, likePattern, type Fragment } from "../lib/sql.js";4import { int, iso, json, num, reqIso, reqStr, str, strArray, type Row } from "../lib/rows.js";5import { sourceRef } from "../lib/dto.js";67export async function timeseries(metric: string, dim: string, days: number): Promise<Array<{ day: string; value: number }>> {8  const sql = pg();9  const rows = await sql<Row[]>`select day::text as day, value from daily_metrics where metric = ${metric} and dim = ${dim} and day >= (current_date - ${days}::int) order by day`;10  return rows.map((r) => ({ day: reqStr(r.day), value: num(r.value) ?? 0 }));11}1213export async function availableMetrics(): Promise<Array<{ metric: string; dims: number; from: string | null; to: string | null }>> {14  const sql = pg();15  const rows = await sql<Row[]>`select metric, count(distinct dim)::int as dims, min(day)::text as from_day, max(day)::text as to_day from daily_metrics group by metric order by metric`;16  return rows.map((r) => ({ metric: reqStr(r.metric), dims: int(r.dims), from: str(r.from_day), to: str(r.to_day) }));17}1819export interface SourceWithCounts extends SourceRef { connectorId: string; priority: number; robotsAllowed: boolean | null; notes: string | null; documents: number; provenanceRows: number; facilities: number; lastDocumentAt: string | null; updatedAt: string }2021function sourceWithCounts(r: Row): SourceWithCounts {22  return { ...sourceRef(r), connectorId: reqStr(r.connector_id), priority: int(r.priority, 3), robotsAllowed: r.robots_allowed == null ? null : r.robots_allowed === true, notes: str(r.notes), documents: int(r.documents), provenanceRows: int(r.provenance_rows), facilities: int(r.facilities), lastDocumentAt: iso(r.last_document_at), updatedAt: reqIso(r.updated_at) };23}2425const SOURCE_SELECT = (sql: ReturnType<typeof pg>) => sql`26  select s.*, coalesce(d.n, 0) as documents, d.last_document_at, coalesce(p.n, 0) as provenance_rows, coalesce(p.facilities, 0) as facilities27  from sources s28  left join (select source_id, count(*)::int as n, max(last_fetched) as last_document_at from documents group by source_id) d on d.source_id = s.id29  left join (select source_id, count(*)::int as n, count(distinct entity_id) filter (where entity_type = 'facility')::int as facilities from provenance where is_current group by source_id) p on p.source_id = s.id`;3031export async function listSources(): Promise<SourceWithCounts[]> {32  const sql = pg();33  const rows = await sql<Row[]>`${SOURCE_SELECT(sql)} order by coalesce(p.n, 0) desc, s.name`;34  return rows.map(sourceWithCounts);35}3637export async function getSource(id: string): Promise<(SourceWithCounts & { byPageType: Array<{ pageType: string; count: number }>; byEntityType: Array<{ entityType: string; count: number }> }) | null> {38  const sql = pg();39  const rows = await sql<Row[]>`${SOURCE_SELECT(sql)} where s.id = ${id}`;40  if (!rows[0]) return null;41  const [pt, et] = await Promise.all([42    sql<Row[]>`select page_type, count(*)::int as n from documents where source_id = ${id} group by page_type order by n desc`,43    sql<Row[]>`select entity_type, count(*)::int as n from provenance where source_id = ${id} and is_current group by entity_type order by n desc`,44  ]);45  return { ...sourceWithCounts(rows[0]), byPageType: pt.map((r) => ({ pageType: reqStr(r.page_type), count: int(r.n) })), byEntityType: et.map((r) => ({ entityType: reqStr(r.entity_type), count: int(r.n) })) };46}4748export interface NewsItemDTO {49  id: string; title: string; url: string; publishedAt: string | null; summary: string | null; pageType: string; eventType: string | null; mw: number | null; significance: number;50  source: { id: string; name: string; kind: string } | null; countryIso2s: string[]; operators: Array<{ id: string; slug: string; name: string }>; projectId: string | null; mentions: Record<string, unknown>;51}5253export async function listNews(f: { country?: string; operator?: string; since?: string; q?: string; page?: number; per_page?: number }): Promise<{ items: NewsItemDTO[]; total: number; page: number; perPage: number }> {54  const sql = pg();55  const pg_ = page(f.page, f.per_page, 100, 30);56  const c: Fragment[] = [sql`true`];57  if (f.country) c.push(sql`${f.country.toUpperCase()} = any(n.country_iso2s)`);58  if (f.operator) c.push(sql`exists (select 1 from operators o where o.id = any(n.operator_ids) and (o.slug = ${f.operator} or o.id = ${f.operator}))`);59  if (f.since) c.push(sql`coalesce(n.published_at, n.created_at) >= ${f.since}::timestamptz`);60  if (f.q) c.push(sql`(n.title ilike ${likePattern(f.q)} or n.summary ilike ${likePattern(f.q)})`);61  const rows = await sql<Row[]>`62    select n.*, s.name as source_name, s.kind as source_kind,63      (select coalesce(jsonb_agg(jsonb_build_object('id', o.id, 'slug', o.slug, 'name', o.name)), '[]'::jsonb) from operators o where o.id = any(n.operator_ids)) as ops,64      count(*) over() as total65    from news_items n left join sources s on s.id = n.source_id66    where ${c.reduce<Fragment>((a, x) => sql`${a} and ${x}`, sql`true`)}67    order by coalesce(n.published_at, n.created_at) desc limit ${pg_.perPage} offset ${pg_.offset}`;68  return {69    items: rows.map((r) => ({ id: reqStr(r.id), title: reqStr(r.title), url: reqStr(r.url), publishedAt: iso(r.published_at), summary: str(r.summary), pageType: reqStr(r.page_type, "unknown"), eventType: str(r.event_type), mw: num(r.mw), significance: int(r.significance, 20), source: r.source_id ? { id: reqStr(r.source_id), name: reqStr(r.source_name, reqStr(r.source_id)), kind: reqStr(r.source_kind, "news") } : null, countryIso2s: strArray(r.country_iso2s), operators: json<Array<{ id: string; slug: string; name: string }>>(r.ops, []), projectId: str(r.project_id), mentions: json<Record<string, unknown>>(r.mentions, {}) })),70    total: rows.length ? int(rows[0]!.total) : 0,71    page: pg_.page,72    perPage: pg_.perPage,73  };74}7576export const SITEMAP_KINDS = ["facilities", "operators", "countries", "metros", "projects", "cloud-regions"] as const;77export type SitemapKind = (typeof SITEMAP_KINDS)[number];78export const SITEMAP_PAGE = 5000;7980export async function sitemap(kind: SitemapKind, pageNo: number): Promise<{ items: Array<{ slug: string; updatedAt: string }>; total: number; page: number; perPage: number }> {81  const sql = pg();82  const offset = Math.max(0, pageNo - 1) * SITEMAP_PAGE;83  const table = kind === "cloud-regions" ? "cloud_regions" : kind;84  // facilities: merged duplicates hidden; projects: false positives (hidden) and merged duplicates never listed85  const where = kind === "facilities" ? sql`where merged_into is null` : kind === "projects" ? sql`where hidden = false and merged_into is null` : sql``;86  const rows = await sql<Row[]>`select slug, updated_at, count(*) over() as total from ${sql(table)} ${where} order by slug limit ${SITEMAP_PAGE} offset ${offset}`;87  const total = rows.length ? int(rows[0]!.total) : int((await sql<Row[]>`select count(*)::int as n from ${sql(table)} ${where}`)[0]?.n);88  return { items: rows.map((r) => ({ slug: reqStr(r.slug), updatedAt: reqIso(r.updated_at) })), total, page: pageNo, perPage: SITEMAP_PAGE };89}90