spb/datacenterindex

1/**2 * Likely-duplicate facilities still stored as separate records, scored with the production matcher.3 *4 *   set -a; source .env; set +a5 *   node node_modules/tsx/dist/cli.mjs scripts/quality-dedupe.ts            # report only6 *   node node_modules/tsx/dist/cli.mjs scripts/quality-dedupe.ts --apply    # merge pairs scoring ≥ 0.92 (mergeFacilities)7 *   … --min 0.6 --limit 200                                                 # report threshold / max rows printed8 *9 * Candidate pairs: same operator + same country with name similarity ≥ 0.85, or any two live facilities less than10 * 300 m apart. Each pair is scored in both directions with `scoreFacilityMatch`; the best score decides.11 * `--apply` folds the younger / less-sourced record into the older one for scores ≥ AUTO_MERGE_THRESHOLD (0.92),12 * writing an `entity_matches` row (status approved, decided_by quality-dedupe) for audit. Nothing below 0.92 is touched.13 */14import { closeDb, getDb, sql, textArray } from "@dci/db";15import { haversineKm, newId } from "@dci/core";16import { AUTO_MERGE_THRESHOLD, decide, nameSimilarity, scoreFacilityMatch, type FacilityCandidate } from "../apps/worker/src/ingest/match.js";17import { mergeFacilities } from "../apps/worker/src/ingest/facilities.js";1819const args = process.argv.slice(2);20const APPLY = args.includes("--apply");21const num = (flag: string, def: number) => { const i = args.indexOf(flag); return i >= 0 && args[i + 1] ? Number(args[i + 1]) : def; };22const MIN_REPORT = num("--min", 0.6);23const LIMIT = num("--limit", 400);2425interface Row extends FacilityCandidate { sourceCount: number; createdAt: string; confidence: string }2627async function loadFacilities(): Promise<Row[]> {28  const rows = await getDb().execute(sql`29    select f.id, f.name, f.normalized_name, f.operator_id, o.name as operator_name, f.country_iso2, f.city, f.address, f.lat, f.lng, f.geo_precision, f.external_ids,30      f.source_count, f.created_at, f.confidence,31      (select coalesce(array_agg(alias), '{}'::text[]) from facility_aliases a where a.facility_id = f.id) as aliases32    from facilities f left join operators o on o.id = f.operator_id where f.merged_into is null`);33  return rows.map((r) => ({34    id: String(r.id), name: String(r.name), normalizedName: String(r.normalized_name), aliases: Array.isArray(r.aliases) ? (r.aliases as string[]) : [],35    operatorId: r.operator_id == null ? null : String(r.operator_id), operatorName: r.operator_name == null ? null : String(r.operator_name),36    countryIso2: r.country_iso2 == null ? null : String(r.country_iso2), city: r.city == null ? null : String(r.city), address: r.address == null ? null : String(r.address),37    lat: r.lat == null ? null : Number(r.lat), lng: r.lng == null ? null : Number(r.lng), geoPrecision: r.geo_precision == null ? null : String(r.geo_precision),38    externalIds: (r.external_ids as Record<string, string | number>) ?? null, sourceCount: Number(r.source_count ?? 0), createdAt: String(r.created_at), confidence: String(r.confidence),39  }));40}4142function probeOf(f: Row) {43  return { name: f.name, aliases: f.aliases, operatorId: f.operatorId, operatorName: f.operatorName, countryIso2: f.countryIso2, city: f.city, address: f.address, lat: f.lat, lng: f.lng, geoPrecision: (f.geoPrecision ?? null) as never };44}4546async function main(): Promise<void> {47  const all = await loadFacilities();48  console.log(`live facilities: ${all.length}`);49  const pairs = new Map<string, [Row, Row, string]>();50  const key = (a: Row, b: Row) => (a.id < b.id ? `${a.id}|${b.id}` : `${b.id}|${a.id}`);5152  // (a) same operator + same country, name similarity ≥ 0.8553  const byOpCountry = new Map<string, Row[]>();54  for (const f of all) if (f.operatorId && f.countryIso2) { const k = `${f.operatorId}|${f.countryIso2}`; (byOpCountry.get(k) ?? byOpCountry.set(k, []).get(k)!).push(f); }55  for (const group of byOpCountry.values()) {56    if (group.length < 2 || group.length > 600) continue;57    for (let i = 0; i < group.length; i++) for (let j = i + 1; j < group.length; j++) {58      const a = group[i]!, b = group[j]!;59      const n = nameSimilarity(probeOf(a), b);60      if (n.score >= 0.85 && !n.siblings) pairs.set(key(a, b), [a, b, `name:${n.score.toFixed(2)}`]);61    }62  }63  // (b) any two facilities < 300 m apart (bucketed by ~1 km cells)64  const cells = new Map<string, Row[]>();65  const cell = (lat: number, lng: number) => `${Math.floor(lat * 100)}|${Math.floor(lng * 100)}`;66  for (const f of all) if (f.lat != null && f.lng != null) { const k = cell(f.lat, f.lng); (cells.get(k) ?? cells.set(k, []).get(k)!).push(f); }67  for (const f of all) {68    if (f.lat == null || f.lng == null) continue;69    for (const dl of [-1, 0, 1]) for (const dg of [-1, 0, 1]) {70      const bucket = cells.get(`${Math.floor(f.lat * 100) + dl}|${Math.floor(f.lng * 100) + dg}`);71      if (!bucket) continue;72      for (const g of bucket) {73        if (g.id <= f.id) continue;74        const d = haversineKm(f.lat, f.lng, g.lat!, g.lng!);75        if (d < 0.3 && !pairs.has(key(f, g))) pairs.set(key(f, g), [f, g, `distance:${(d * 1000).toFixed(0)}m`]);76      }77    }78  }79  console.log(`candidate pairs: ${pairs.size}`);8081  const scored: Array<{ a: Row; b: Row; why: string; score: number; reasons: string[] }> = [];82  for (const [a, b, why] of pairs.values()) {83    const m1 = scoreFacilityMatch(probeOf(a), b);84    const m2 = scoreFacilityMatch(probeOf(b), a);85    const m = m1.score >= m2.score ? m1 : m2;86    if (m.score >= MIN_REPORT) scored.push({ a, b, why, score: m.score, reasons: m.reasons });87  }88  scored.sort((x, y) => y.score - x.score);89  const mergeable = scored.filter((s) => s.score >= AUTO_MERGE_THRESHOLD);90  const pending = scored.filter((s) => s.score < AUTO_MERGE_THRESHOLD);91  console.log(`likely duplicates (score ≥ ${MIN_REPORT}): ${scored.length} — auto-mergeable (≥ ${AUTO_MERGE_THRESHOLD}): ${mergeable.length}, review (${MIN_REPORT}–${AUTO_MERGE_THRESHOLD}): ${pending.length}\n`);92  for (const s of scored.slice(0, LIMIT)) console.log(`${s.score.toFixed(3)} ${decide(s.score).padEnd(7)} | ${s.a.name} [${s.a.operatorName ?? "-"}, ${s.a.countryIso2 ?? "-"}] ⇄ ${s.b.name} [${s.b.operatorName ?? "-"}, ${s.b.countryIso2 ?? "-"}] | ${s.why} | ${s.reasons.join(",")}`);9394  if (!APPLY) { console.log(`\n(dry run — pass --apply to merge the ${mergeable.length} pairs scoring ≥ ${AUTO_MERGE_THRESHOLD})`); return; }95  const gone = new Set<string>();96  let merged = 0;97  for (const s of mergeable) {98    if (gone.has(s.a.id) || gone.has(s.b.id)) continue;99    // survivor: more sources, then better confidence, then older100    const rank = (f: Row) => [f.sourceCount, ["verified", "high", "moderate", "estimated", "unverified"].indexOf(f.confidence) * -1, -Date.parse(f.createdAt)] as const;101    const [into, from] = rank(s.a) >= rank(s.b) ? [s.a, s.b] : [s.b, s.a];102    await getDb().transaction(async (tx) => {103      await tx.execute(sql`insert into entity_matches (id, connector_id, candidate_key, candidate, matched_facility_id, score, reasons, status, decided_by, decided_at)104        values (${newId("match")}, 'quality-dedupe', ${`quality-dedupe:${from.id}`}, ${JSON.stringify({ createdFacilityId: from.id, name: from.name, operatorName: from.operatorName, countryIso2: from.countryIso2, why: s.why })}::jsonb, ${into.id}, ${s.score}, ${textArray(s.reasons)}::text[], 'approved', 'quality-dedupe', now())`);105      await mergeFacilities(tx, from.id, into.id, "quality-dedupe");106    });107    gone.add(from.id);108    merged++;109    console.log(`merged ${from.name} (${from.id}) → ${into.name} (${into.id}) @ ${s.score}`);110  }111  console.log(`\nmerged ${merged} facilities`);112}113114main().catch((e) => { console.error(e); process.exitCode = 1; }).finally(() => closeDb());115