spb/datacenterindex

1/**2 * Admin data-quality reads: QualityOverview (largest values, review queue, counters) and the quality_flags list.3 * Every "largest" query is a plain ORDER BY on published figures — the point is to surface outliers for review.4 */5import type { QualityFlagDTO, QualityOverview } from "@dci/core";6import { pg, facilityView, pipelineMwAgg, projectLive, andAll, page, PLANNED_SET, CONSTRUCTION_SET, type Fragment } from "../../lib/sql.js";7import { int, iso, num, reqStr, str, type Row } from "../../lib/rows.js";8import { qualityFlagDto } from "../../lib/dto.js";9import { entityKey, resolveEntityRefs } from "../../lib/resolve.js";1011const CODE_LABELS: Record<string, string> = {12  mw_single_site_gt_1000: "Single site > 1 000 MW",13  mw_building_gt_500: "One building > 500 MW",14  mw_market_statistic: "MW figure is a market statistic",15  mw_change_5x: "MW figure changed > 5×",16  mw_money_collision: "MW / money figure collision",17  mw_semantics_default: "MW semantics defaulted",18  mw_utility_not_it: "Utility / grid MW (not IT load)",19  mw_project_gt_2000: "Project > 2 000 MW",20  mw_density_implausible: "Implausible power density",21  mw_invalid: "Invalid MW figure",22  inv_single_site_gt_50b: "Single site investment > $50B",23  inv_gt_500b: "Investment > $500B (industry statistic)",24  inv_change_5x: "Investment changed > 5×",25  inv_money_mw_collision: "Investment / MW figure collision",26  inv_invalid: "Invalid investment figure",27  project_false_positive_candidate: "Project false-positive candidate",28  project_title_like_name: "Project named after a headline",29  project_no_location: "Project without a location",30  project_unknown_scope: "Project figure with unknown scope",31  facility_no_country: "Facility without a country",32  operator_bad_slug: "Operator with a broken slug",33  claim_rejected_backing_value: "Rejected claim was backing the displayed value",34};3536export function flagLabel(code: string): string {37  if (CODE_LABELS[code]) return CODE_LABELS[code]!;38  if (code.startsWith("scope_")) return `Figure scope: ${code.slice(6).replace(/_/g, " ")}`;39  if (code.startsWith("inv_scope_")) return `Investment scope: ${code.slice(10).replace(/_/g, " ")}`;40  if (code.startsWith("inv_")) return `Investment: ${code.slice(4).replace(/_/g, " ")}`;41  if (code.startsWith("duplicate")) return "Possible duplicate";42  return code.replace(/_/g, " ").replace(/^\w/, (c) => c.toUpperCase());43}4445function named(r: Row): { id: string; slug: string; name: string } {46  return { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name) };47}4849/** Attach {slug,name} refs to quality flag rows (one lookup per entity type). */50export async function flagsWithRefs(rows: Row[]): Promise<QualityFlagDTO[]> {51  const refs = await resolveEntityRefs(rows.map((r) => ({ type: reqStr(r.entity_type), id: str(r.entity_id) })));52  return rows.map((r) => qualityFlagDto(r, refs.get(entityKey(r.entity_type, r.entity_id)) ?? null));53}5455export async function qualityOverview(): Promise<QualityOverview> {56  const sql = pg();57  const live = projectLive(sql);58  const [counts, sev, codes, largeOps, largeCon, largePrj, largeInv, opPipe, metPipe, queue, alerts] = await Promise.all([59    sql<Row[]>`select60        (select count(*) from quality_flags where status = 'open')::int as open_flags,61        (select count(*) from entity_matches where status = 'pending')::int + (select count(*) from quality_flags where status = 'open' and code like 'duplicate%')::int as possible_duplicates,62        (select count(*) from quality_flags where status = 'open' and code like 'mw\\_%')::int as suspicious_mw,63        (select count(*) from quality_flags where status = 'open' and code like 'inv\\_%')::int as suspicious_inv,64        (select count(*) from quality_flags where status = 'open' and code = 'project_false_positive_candidate')::int as project_fp,65        (select count(*) from claims where status = 'unscoped')::int as unscoped_claims,66        (select count(*) from claims where status = 'review')::int as claims_review,67        (select count(*) from projects p where ${live} and p.planned_mw >= 500 and (coalesce(p.evidence_level, 'none') <> 'strong' or p.confidence in ('moderate', 'estimated', 'unverified')))::int as unverified_large,68        (select count(*) from facilities where merged_into is null and country_iso2 is null)::int as fac_no_country,69        (select count(*) from projects p where ${live} and p.facility_id is null)::int as prj_no_facility,70        (select count(*) from operators o where not exists (select 1 from facilities f where (f.operator_id = o.id or f.owner_id = o.id) and f.merged_into is null)71            and not exists (select 1 from projects p where p.operator_id = o.id and ${live})72            and not exists (select 1 from cloud_regions r where r.provider_id = o.id)73            and not exists (select 1 from facility_tenants t where t.operator_id = o.id))::int as orphan_operators`,74    sql<Row[]>`select severity, count(*)::int as n from quality_flags where status = 'open' group by 1`,75    sql<Row[]>`select code, count(*)::int as n from quality_flags where status = 'open' group by 1 order by n desc`,76    sql<Row[]>`select id, slug, name, coalesce(it_capacity_mw, total_power_mw) as mw, capacity_scope as scope, capacity_semantics as semantics from facilities where merged_into is null and status in ('operational', 'partially_operational', 'expansion') and coalesce(it_capacity_mw, total_power_mw) is not null order by 4 desc limit 15`,77    sql<Row[]>`select id, slug, name, coalesce(planned_power_mw, it_capacity_mw, total_power_mw) as mw from facilities where merged_into is null and status = 'under_construction' and coalesce(planned_power_mw, it_capacity_mw, total_power_mw) is not null order by 4 desc limit 15`,78    sql<Row[]>`select p.id, p.slug, p.name, p.planned_mw as mw, p.capacity_scope as scope from projects p where ${live} and p.planned_mw is not null order by p.planned_mw desc limit 15`,79    sql<Row[]>`select p.id, p.slug, p.name, p.investment_usd as inv, p.investment_scope as scope from projects p where ${live} and p.investment_usd is not null order by p.investment_usd desc limit 15`,80    sql<Row[]>`select o.id, o.slug, o.name,81        coalesce((select sum(${pipelineMwAgg(sql)}) from ${facilityView(sql)} f where f.operator_id = o.id and f.status = any(${[...PLANNED_SET, ...CONSTRUCTION_SET]})), 0)82        + coalesce((select sum(p.planned_mw) from projects p where p.operator_id = o.id and ${live} and p.status = any(${[...PLANNED_SET, ...CONSTRUCTION_SET]})), 0) as mw83      from operators o order by mw desc nulls last limit 15`,84    sql<Row[]>`select m.id, m.slug, m.name,85        coalesce((select sum(${pipelineMwAgg(sql)}) from ${facilityView(sql)} f where f.metro_id = m.id and f.status = any(${[...PLANNED_SET, ...CONSTRUCTION_SET]})), 0)86        + coalesce((select sum(p.planned_mw) from projects p where p.metro_id = m.id and ${live} and p.status = any(${[...PLANNED_SET, ...CONSTRUCTION_SET]})), 0) as mw87      from metros m order by mw desc nulls last limit 15`,88    sql<Row[]>`select * from quality_flags where status = 'open' order by priority desc, created_at desc limit 50`,89    sql<Row[]>`select id, level, component, message, created_at from system_alerts where resolved_at is null order by created_at desc limit 20`,90  ]);91  const c = counts[0] ?? {};92  const bySeverity: Record<string, number> = {};93  for (const r of sev) bySeverity[reqStr(r.severity)] = int(r.n);94  return {95    openFlags: int(c.open_flags),96    bySeverity,97    byCode: codes.map((r) => ({ code: reqStr(r.code), count: int(r.n), label: flagLabel(reqStr(r.code)) })),98    possibleDuplicates: int(c.possible_duplicates),99    suspiciousMw: int(c.suspicious_mw),100    suspiciousInvestment: int(c.suspicious_inv),101    projectFalsePositiveCandidates: int(c.project_fp),102    unscopedClaims: int(c.unscoped_claims),103    claimsInReview: int(c.claims_review),104    unverifiedLargeProjects: int(c.unverified_large),105    facilitiesMissingCountry: int(c.fac_no_country),106    projectsWithoutFacility: int(c.prj_no_facility),107    orphanOperators: int(c.orphan_operators),108    largest: {109      operationalFacilityMw: largeOps.map((r) => ({ ...named(r), mw: num(r.mw) ?? 0, scope: str(r.scope), semantics: str(r.semantics) })),110      constructionFacilityMw: largeCon.map((r) => ({ ...named(r), mw: num(r.mw) ?? 0 })),111      projectMw: largePrj.map((r) => ({ ...named(r), mw: num(r.mw) ?? 0, scope: str(r.scope) })),112      investment: largeInv.map((r) => ({ ...named(r), investmentUsd: num(r.inv) ?? 0, scope: str(r.scope) })),113      operatorPipelineMw: opPipe.filter((r) => (num(r.mw) ?? 0) > 0).map((r) => ({ ...named(r), mw: Math.round((num(r.mw) ?? 0) * 100) / 100 })),114      metroPipelineMw: metPipe.filter((r) => (num(r.mw) ?? 0) > 0).map((r) => ({ ...named(r), mw: Math.round((num(r.mw) ?? 0) * 100) / 100 })),115    },116    reviewQueue: await flagsWithRefs(queue),117    alerts: alerts.map((a) => ({ id: reqStr(a.id), level: reqStr(a.level), component: reqStr(a.component), message: reqStr(a.message), createdAt: iso(a.created_at) ?? "" })),118  };119}120121export interface FlagFilters { status?: string; code?: string; entityType?: string; minPriority?: number; page?: number; perPage?: number }122123export async function listFlags(f: FlagFilters): Promise<{ items: QualityFlagDTO[]; total: number; page: number; perPage: number }> {124  const sql = pg();125  const pg_ = page(f.page, f.perPage, 200, 50);126  const c: Fragment[] = [];127  if (f.status && f.status !== "all") c.push(sql`q.status = ${f.status}`);128  if (f.code) c.push(sql`q.code = ${f.code}`);129  if (f.entityType) c.push(sql`q.entity_type = ${f.entityType}`);130  if (f.minPriority != null) c.push(sql`q.priority >= ${f.minPriority}`);131  const rows = await sql<Row[]>`select q.*, count(*) over() as total from quality_flags q where ${andAll(sql, c)} order by q.priority desc, q.created_at desc limit ${pg_.perPage} offset ${pg_.offset}`;132  return { items: await flagsWithRefs(rows), total: rows.length ? int(rows[0]!.total) : 0, page: pg_.page, perPage: pg_.perPage };133}134135export async function setFlagStatus(id: string, status: "resolved" | "dismissed", resolution: string | null): Promise<QualityFlagDTO | null> {136  const sql = pg();137  const rows = await sql<Row[]>`update quality_flags set status = ${status}, resolution = ${resolution}, resolved_by = 'admin', resolved_at = now(), updated_at = now() where id = ${id} returning *`;138  if (!rows[0]) return null;139  return (await flagsWithRefs(rows))[0] ?? null;140}141