spb/datacenterindex
Public
HTML 53.9%
TypeScript 44.5%
JavaScript 0.6%
SQL 0.5%
1/**2 * Entity match workbench: pending candidates with BOTH facility rows side by side (name, operator, city, address,3 * coordinates + distance, facility codes, external ids, source counts) and the matcher's score / reasons.4 * Decisions: approve (merge), reject (keep separate), related-campus (candidate is a building of the matched campus),5 * defer.6 */7import type { FastifyInstance } from "fastify";8import { z } from "zod";9import { extractFacilityCodes } from "@dci/core";10import { envelope, notFound, parseQuery, parseBody, badRequest } from "../../lib/http.js";11import { intParam, pageParam, strParam } from "../../lib/params.js";12import { pg, page } from "../../lib/sql.js";13import { int, iso, json, num, record, reqStr, str, strArray, type Row } from "../../lib/rows.js";14import { facilitiesByIds } from "../../repositories/facilities.js";15import { ensureManualSource, mergeFacility, setFacilityParent } from "../../repositories/admin/merge.js";16import { invalidate } from "../../cache.js";1718const STATUSES = ["pending", "approved", "rejected", "related_campus", "deferred", "auto_merged", "auto_created", "all"] as const;1920function matchDto(r: Row): Record<string, unknown> {21 return { id: reqStr(r.id), connectorId: reqStr(r.connector_id), candidateKey: reqStr(r.candidate_key), candidate: json(r.candidate, {}), matchedFacilityId: str(r.matched_facility_id), score: num(r.score), reasons: strArray(r.reasons), status: reqStr(r.status), decidedBy: str(r.decided_by), decidedAt: iso(r.decided_at), createdAt: iso(r.created_at), candidateFacilityId: str(r.candidate_facility_id) };22}2324interface Side { id: string; slug: string; name: string; operator: string | null; city: string | null; address: string | null; lat: number | null; lng: number | null; codes: string[]; externalIds: Record<string, string | number>; sourceCount: number; status: string; recordScope: string; parentFacilityId: string | null }2526function side(r: Row): Side {27 return { id: reqStr(r.id), slug: reqStr(r.slug), name: reqStr(r.name), operator: str(r.op_name), city: str(r.city), address: str(r.address), lat: num(r.lat), lng: num(r.lng), codes: extractFacilityCodes(reqStr(r.name)), externalIds: record(r.external_ids), sourceCount: int(r.source_count), status: reqStr(r.status), recordScope: reqStr(r.record_scope, "facility"), parentFacilityId: str(r.parent_facility_id) };28}2930function haversineKm(a: Side, b: Side): number | null {31 if (a.lat == null || a.lng == null || b.lat == null || b.lng == null) return null;32 const R = 6371.0088, toRad = (d: number) => (d * Math.PI) / 180;33 const dLat = toRad(b.lat - a.lat), dLng = toRad(b.lng - a.lng);34 const h = Math.sin(dLat / 2) ** 2 + Math.cos(toRad(a.lat)) * Math.cos(toRad(b.lat)) * Math.sin(dLng / 2) ** 2;35 return Math.round(2 * R * Math.asin(Math.sqrt(Math.min(1, h))) * 1000) / 1000;36}3738/** The facility created from the candidate (entity key or candidate.createdFacilityId) — for a pending row. */39function candidateFacilityId(r: Row): string | null {40 return str(r.candidate_facility_id) ?? str((json<Record<string, unknown>>(r.candidate, {}) as Record<string, unknown>).createdFacilityId);41}4243export async function matchAdminRoutes(app: FastifyInstance): Promise<void> {44 app.get("/matches", { schema: { summary: "Entity match queue (?status=pending|approved|rejected|related_campus|deferred|auto_merged|auto_created|all&connector=) with both facility rows (pair: name, operator, city, address, coords + distance, codes, external ids, source counts), score and reasons" } }, async (req) => {45 const q = parseQuery(z.object({ status: z.preprocess((v) => (v === "" || v == null ? undefined : v), z.enum(STATUSES).optional()), connector: strParam, page: pageParam, per_page: intParam }), req.query);46 const sql = pg();47 const pg_ = page(q.page, q.per_page, 200, 50);48 const status = q.status ?? "pending";49 const rows = await sql<Row[]>`50 select m.*, coalesce(k.entity_id, m.candidate->>'createdFacilityId') as candidate_facility_id, count(*) over() as total51 from entity_matches m52 left join entity_keys k on k.key = m.candidate_key and k.entity_type = 'facility'53 where ${status === "all" ? sql`true` : sql`m.status = ${status}`} and ${q.connector ? sql`m.connector_id = ${q.connector}` : sql`true`}54 order by m.score desc, m.created_at desc limit ${pg_.perPage} offset ${pg_.offset}`;55 const ids = [...new Set(rows.flatMap((r) => [str(r.matched_facility_id), candidateFacilityId(r)]).filter((x): x is string => Boolean(x)))];56 const [facs, sides] = await Promise.all([57 facilitiesByIds(ids),58 ids.length ? sql<Row[]>`select f.id, f.slug, f.name, f.city, f.address, f.lat, f.lng, f.external_ids, f.source_count, f.status, f.record_scope, f.parent_facility_id, o.name as op_name from facilities f left join operators o on o.id = f.operator_id where f.id = any(${ids})` : Promise.resolve([] as Row[]),59 ]);60 const by = new Map(facs.map((f) => [f.id, f]));61 const sideBy = new Map(sides.map((r) => [reqStr(r.id), side(r)]));62 const items = rows.map((r) => {63 const matchedId = str(r.matched_facility_id);64 const candId = candidateFacilityId(r);65 const cand = json<Record<string, unknown>>(r.candidate, {});66 const a = candId ? sideBy.get(candId) ?? null : null;67 const b = matchedId ? sideBy.get(matchedId) ?? null : null;68 // when the candidate was never persisted as a facility, describe it from the candidate payload itself69 const candidateSide: Partial<Side> | null = a ?? (Object.keys(cand).length ? { id: "", slug: "", name: str(cand.name) ?? "", operator: str(cand.operatorName ?? cand.operator), city: str(cand.city), address: str(cand.address), lat: num((cand.geo as Record<string, unknown> | undefined)?.lat ?? cand.lat), lng: num((cand.geo as Record<string, unknown> | undefined)?.lng ?? cand.lng), codes: extractFacilityCodes(str(cand.name) ?? ""), externalIds: record(cand.externalIds), sourceCount: 0, status: str(cand.status) ?? "unknown", recordScope: "facility", parentFacilityId: null } : null);70 const distanceKm = candidateSide && b && candidateSide.lat != null && candidateSide.lng != null ? haversineKm(candidateSide as Side, b) : null;71 return {72 ...matchDto(r),73 candidateFacilityId: candId,74 matched: matchedId ? by.get(matchedId) ?? null : null,75 candidateFacility: candId ? by.get(candId) ?? null : null,76 pair: { candidate: candidateSide, matched: b, distanceKm, sameCodes: candidateSide && b ? candidateSide.codes!.filter((c) => b.codes.includes(c)) : [] },77 };78 });79 return envelope(items, { total: rows.length ? int(rows[0]!.total) : 0, page: pg_.page, perPage: pg_.perPage });80 });8182 async function pendingMatch(id: string): Promise<Row> {83 const sql = pg();84 const rows = await sql<Row[]>`select m.*, coalesce(k.entity_id, m.candidate->>'createdFacilityId') as candidate_facility_id from entity_matches m left join entity_keys k on k.key = m.candidate_key and k.entity_type = 'facility' where m.id = ${id}`;85 const m = rows[0];86 if (!m) throw notFound("match");87 if (m.status !== "pending" && m.status !== "deferred") throw badRequest(`match is already ${String(m.status)}`);88 return m;89 }9091 app.post("/matches/:id/approve", { schema: { summary: "Approve: point the candidate key at the matched facility and merge any separately-created facility into it" } }, async (req) => {92 const { id } = req.params as { id: string };93 const sql = pg();94 const m = await pendingMatch(id);95 const matched = str(m.matched_facility_id);96 if (!matched) throw badRequest("match has no matched_facility_id");97 const target = await sql<Row[]>`select id, merged_into from facilities where id = ${matched}`;98 if (!target[0]) throw badRequest("matched facility no longer exists");99 const survivor = str(target[0].merged_into) ?? matched;100 await ensureManualSource();101 const key = reqStr(m.candidate_key);102 const existing = await sql<Row[]>`select entity_id from entity_keys where key = ${key} and entity_type = 'facility'`;103 const separate = str(existing[0]?.entity_id) ?? candidateFacilityId(m);104 let merge: Awaited<ReturnType<typeof mergeFacility>> | null = null;105 if (separate && separate !== survivor) merge = await mergeFacility(separate, survivor);106 await sql`insert into entity_keys (key, entity_type, entity_id, connector_id) values (${key}, 'facility', ${survivor}, ${reqStr(m.connector_id)}) on conflict (key, entity_type) do update set entity_id = ${survivor}`;107 await sql`update entity_matches set status = 'approved', decided_by = 'admin', decided_at = now() where id = ${id}`;108 await invalidate("/datacenters");109 return envelope({ id, status: "approved", facilityId: survivor, candidateKey: key, merged: merge });110 });111112 app.post("/matches/:id/reject", { schema: { summary: "Reject: keep the candidate as a separate facility" } }, async (req) => {113 const { id } = req.params as { id: string };114 const sql = pg();115 const rows = await sql<Row[]>`update entity_matches set status = 'rejected', decided_by = 'admin', decided_at = now() where id = ${id} and status in ('pending', 'deferred') returning id`;116 if (!rows[0]) {117 const exists = await sql`select status from entity_matches where id = ${id}`;118 if (!exists.length) throw notFound("match");119 throw badRequest(`match is already ${String(exists[0]!.status)}`);120 }121 return envelope({ id, status: "rejected" });122 });123124 app.post("/matches/:id/related-campus", { schema: { summary: "Related campus: the candidate facility is a BUILDING of the matched campus (parent_facility_id set, record_scope building / campus); both rows stay, aggregates never count both" } }, async (req) => {125 const { id } = req.params as { id: string };126 const sql = pg();127 const m = await pendingMatch(id);128 const matched = str(m.matched_facility_id);129 const building = candidateFacilityId(m);130 if (!matched) throw badRequest("match has no matched_facility_id");131 if (!building) throw badRequest("the candidate was never persisted as a facility — approve or reject instead");132 const parentRow = (await sql<Row[]>`select id, merged_into from facilities where id = ${matched}`)[0];133 if (!parentRow) throw badRequest("matched facility no longer exists");134 const campus = str(parentRow.merged_into) ?? matched;135 const res = await setFacilityParent(building, campus);136 await sql`update entity_matches set status = 'related_campus', decided_by = 'admin', decided_at = now() where id = ${id}`;137 await Promise.all([invalidate("/datacenters"), invalidate("/dashboard"), invalidate("/map")]);138 return envelope({ id, status: "related_campus", building, campus, containment: res });139 });140141 app.post("/matches/:id/defer", { schema: { summary: "Defer the decision (status deferred, keeps the row in the workbench under ?status=deferred)" } }, async (req) => {142 const { id } = req.params as { id: string };143 const sql = pg();144 const rows = await sql<Row[]>`update entity_matches set status = 'deferred', decided_by = 'admin', decided_at = now() where id = ${id} and status = 'pending' returning id`;145 if (!rows[0]) {146 const exists = await sql`select status from entity_matches where id = ${id}`;147 if (!exists.length) throw notFound("match");148 throw badRequest(`match is already ${String(exists[0]!.status)}`);149 }150 return envelope({ id, status: "deferred" });151 });152}153