spb/datacenterindex

1/**2 * Facility merge: `dup` is folded into `into`. Aliases, provenance, tenants, IXPs, entity keys, projects and3 * events move to the survivor; the duplicate row stays with merged_into set (public endpoints hide it and4 * detail lookups follow the pointer).5 */6import { pg } from "../../lib/sql.js";7import { HttpError } from "../../lib/http.js";8import type { Row } from "../../lib/rows.js";910export interface MergeResult { into: string; merged: string; moved: Record<string, number> }1112export async function mergeFacility(dupId: string, intoId: string, decidedBy = "admin"): Promise<MergeResult> {13  if (dupId === intoId) throw new Error("cannot merge a facility into itself");14  const sql = pg();15  return sql.begin(async (tx) => {16    const both = await tx<Row[]>`select id, name, merged_into from facilities where id in (${dupId}, ${intoId})`;17    const dup = both.find((r) => r.id === dupId);18    const into = both.find((r) => r.id === intoId);19    if (!dup) throw new Error(`facility ${dupId} not found`);20    if (!into) throw new Error(`facility ${intoId} not found`);21    if (into.merged_into) throw new Error(`target ${intoId} is itself merged into ${String(into.merged_into)}`);22    const moved: Record<string, number> = {};23    const count = (r: { count: number }) => r.count;2425    // aliases (+ the duplicate's own name)26    moved.aliases = count(await tx`insert into facility_aliases (facility_id, alias, normalized, source_id) select ${intoId}, alias, normalized, source_id from facility_aliases where facility_id = ${dupId} on conflict do nothing`);27    await tx`insert into facility_aliases (facility_id, alias, normalized, source_id) select ${intoId}, name, normalized_name, null from facilities where id = ${dupId} on conflict do nothing`;28    await tx`delete from facility_aliases where facility_id = ${dupId}`;2930    // provenance: move rows that do not collide with the survivor's unique key, drop the rest31    moved.provenance = count(await tx`update provenance p set entity_id = ${intoId} where p.entity_type = 'facility' and p.entity_id = ${dupId} and not exists (select 1 from provenance q where q.entity_type = 'facility' and q.entity_id = ${intoId} and q.field = p.field and q.source_id = p.source_id and q.url = p.url)`);32    await tx`delete from provenance where entity_type = 'facility' and entity_id = ${dupId}`;3334    moved.tenants = count(await tx`insert into facility_tenants (facility_id, operator_id, role, asn, source_id) select ${intoId}, operator_id, role, asn, source_id from facility_tenants where facility_id = ${dupId} on conflict do nothing`);35    await tx`delete from facility_tenants where facility_id = ${dupId}`;36    moved.ixps = count(await tx`insert into facility_ixps (facility_id, ixp_id, source_id) select ${intoId}, ixp_id, source_id from facility_ixps where facility_id = ${dupId} on conflict do nothing`);37    await tx`delete from facility_ixps where facility_id = ${dupId}`;3839    moved.entityKeys = count(await tx`update entity_keys set entity_id = ${intoId} where entity_type = 'facility' and entity_id = ${dupId}`);40    moved.projects = count(await tx`update projects set facility_id = ${intoId} where facility_id = ${dupId}`);41    moved.events = count(await tx`update events set entity_id = ${intoId} where entity_type = 'facility' and entity_id = ${dupId}`);42    moved.documents = count(await tx`update documents set entity_refs = (select coalesce(jsonb_agg(distinct case when e->>'type' = 'facility' and e->>'id' = ${dupId} then jsonb_build_object('type', 'facility', 'id', ${intoId}::text) else e end), '[]'::jsonb) from jsonb_array_elements(entity_refs) e), updated_at = now() where entity_refs @> ${JSON.stringify([{ type: "facility", id: dupId }])}::jsonb`);43    await tx`update facilities set merged_into = ${intoId}, updated_at = now() where id = ${dupId}`;44    await tx`update facilities set source_count = (select count(distinct source_id) from provenance where entity_type = 'facility' and entity_id = ${intoId} and is_current), updated_at = now() where id = ${intoId}`;45    await tx`insert into events (id, entity_type, entity_id, event_type, detected_at, old_value, new_value, source_id, url, title, summary, significance, confidence, review_status, fingerprint)46      select ${"evt_" + Math.random().toString(36).slice(2, 14)}, 'facility', ${intoId}, 'facility_updated', now(), ${JSON.stringify({ mergedFacility: dupId, name: dup.name })}::jsonb, ${JSON.stringify({ into: intoId })}::jsonb, 'src_manual', '', ${"Duplicate merged: " + String(dup.name)}, ${"Merged by " + decidedBy}, 20, 'high', 'approved', ${"merge:" + dupId + ":" + intoId}47      where exists (select 1 from sources where id = 'src_manual') on conflict do nothing`;48    return { into: intoId, merged: dupId, moved };49  });50}5152export interface ParentResult { id: string; parentId: string | null; recordScope: string; parentRecordScope: string | null; changed: boolean }5354/**55 * Containment link: `childId` becomes a building of `parentId` (null detaches). Guards: parent exists, is not merged,56 * is not the child, and the parent's own chain never leads back to the child (no cycles, checked 5 levels up).57 * record_scope: child → building (or facility when detached), parent → campus (stays campus while it has children).58 */59export async function setFacilityParent(childIdOrSlug: string, parentIdOrSlug: string | null, decidedBy = "admin"): Promise<ParentResult> {60  const sql = pg();61  const child = (await sql<Row[]>`select id, slug, name, parent_facility_id, record_scope, merged_into from facilities where id = ${childIdOrSlug} or slug = ${childIdOrSlug} order by (id = ${childIdOrSlug}) desc limit 1`)[0];62  if (!child) throw new HttpError(404, "facility not found");63  const childId = String(child.id);64  let parentId: string | null = null;65  if (parentIdOrSlug != null) {66    const parent = (await sql<Row[]>`select id, merged_into from facilities where id = ${parentIdOrSlug} or slug = ${parentIdOrSlug} order by (id = ${parentIdOrSlug}) desc limit 1`)[0];67    if (!parent) throw new HttpError(400, "parent facility not found");68    if (parent.merged_into) throw new HttpError(400, `parent is merged into ${String(parent.merged_into)}`);69    parentId = String(parent.id);70    if (parentId === childId) throw new HttpError(400, "a facility cannot be its own parent");71    // cycle check: walk up from the parent72    let cur: string | null = parentId;73    for (let i = 0; i < 5 && cur; i++) {74      const up: Row | undefined = (await sql<Row[]>`select parent_facility_id from facilities where id = ${cur}`)[0];75      cur = up?.parent_facility_id == null ? null : String(up.parent_facility_id);76      if (cur === childId) throw new HttpError(400, "containment cycle: the parent is already contained by this facility");77    }78  }79  const previous = child.parent_facility_id == null ? null : String(child.parent_facility_id);80  const scope = parentId ? "building" : "facility";81  if (previous === parentId && String(child.record_scope) === scope) return { id: childId, parentId, recordScope: scope, parentRecordScope: null, changed: false };82  await ensureManualSource();83  const now = new Date().toISOString();84  const url = `https://www.datacenterindex.io/admin/facilities/${childId}`;85  let parentScope: string | null = null;86  await sql.begin(async (tx) => {87    await tx`update facilities set parent_facility_id = ${parentId}, record_scope = ${scope}, updated_at = now() where id = ${childId}`;88    for (const [field, value] of [["parentFacilityId", parentId], ["recordScope", scope]] as Array<[string, unknown]>) {89      await tx`insert into provenance (id, entity_type, entity_id, field, value, source_id, connector_id, document_id, url, first_observed, last_observed, retrieved_at, confidence, is_estimate, method, extractor_version, is_current, is_winner, note)90        values (${"prov_" + Math.random().toString(36).slice(2, 14)}, 'facility', ${childId}, ${field}, ${JSON.stringify(value)}::jsonb, 'src_manual', 'manual', null, ${url}, ${now}, ${now}, ${now}, 'high', false, 'manual', 'admin', true, true, ${"containment set by " + decidedBy})91        on conflict (entity_type, entity_id, field, source_id, url) do update set value = excluded.value, last_observed = excluded.last_observed, retrieved_at = excluded.retrieved_at, is_current = true, is_winner = true, note = excluded.note`;92    }93    if (parentId) {94      await tx`update facilities set record_scope = 'campus', updated_at = now() where id = ${parentId} and record_scope <> 'campus'`;95      parentScope = "campus";96    }97    // a former parent stays a campus only while it still has building rows98    if (previous && previous !== parentId) {99      const r = await tx`update facilities set record_scope = 'facility', updated_at = now() where id = ${previous} and record_scope = 'campus' and not exists (select 1 from facilities c where c.parent_facility_id = ${previous} and c.merged_into is null)`;100      if (r.count && !parentId) parentScope = "facility";101    }102  });103  return { id: childId, parentId, recordScope: scope, parentRecordScope: parentScope, changed: true };104}105106/** Ensure the manual-curation source exists (kind registry, "Manual curation"). */107export async function ensureManualSource(): Promise<void> {108  const sql = pg();109  await sql`insert into sources (id, connector_id, name, domain, kind, priority, url, license, attribution, notes) values ('src_manual', 'manual', 'Manual curation', 'datacenterindex.io', 'registry', 1, 'https://www.datacenterindex.io', 'internal', 'DataCenterIndex editorial team', 'Values entered or corrected by an administrator') on conflict (id) do nothing`;110}111