// --------------------------------------------------------------------------- // pg_parity — prove the SQLite and Postgres engines return IDENTICAL data for // every read/query DAL method. The PG data was migrated from this SQLite, so a // correct adapter must make each query indistinguishable across engines. // // # dump one engine's answers, then the other, then diff: // DB_ENGINE=sqlite bun scripts/pg_parity.ts dump /tmp/parity_sqlite.json // DB_ENGINE=pg bun scripts/pg_parity.ts dump /tmp/parity_pg.json // bun scripts/pg_parity.ts compare /tmp/parity_sqlite.json /tmp/parity_pg.json // // Args for per-id methods are drawn from the live DB via deterministic // `ORDER BY ` queries, so BOTH runs probe the exact same rows. Comparison is // data-parity (order-insensitive set equality) with a separate order-diff note, // since query ORDER BY ties may legitimately differ but the DATA must not. // --------------------------------------------------------------------------- import { sql } from "drizzle-orm"; import { db as drizzleDb } from "../control-server/src/db_client"; import * as db from "../control-server/src/db"; import * as lab from "../control-server/src/lab_db"; import * as life from "../control-server/src/lifecycle_db"; import * as evid from "../control-server/src/evidmon_db"; import * as watch from "../control-server/src/watchers"; // ── canonical form ───────────────────────────────────────────────────────── // undefined→null (JS artifact, not a data difference); Map→sorted entries; // object keys sorted; arrays sorted by element canonical-string for SET equality. function canonOrdered(v: unknown): unknown { if (v === undefined) return null; if (v === null || typeof v !== "object") return v; if (v instanceof Map) { return { __map: [...v.entries()].map(([k, val]) => [k, canonOrdered(val)]).sort((a, b) => String(a[0]).localeCompare(String(b[0]))) }; } if (Array.isArray(v)) return v.map(canonOrdered); const o = v as Record; const out: Record = {}; for (const k of Object.keys(o).sort()) out[k] = canonOrdered(o[k]); return out; } function canonSorted(v: unknown): unknown { if (v === undefined) return null; if (v === null || typeof v !== "object") return v; if (v instanceof Map) return canonOrdered(v); if (Array.isArray(v)) { const mapped = v.map(canonSorted); return [...mapped].sort((a, b) => JSON.stringify(a).localeCompare(JSON.stringify(b))); } const o = v as Record; const out: Record = {}; for (const k of Object.keys(o).sort()) out[k] = canonSorted(o[k]); return out; } async function ids(query: string): Promise[]> { const res = await drizzleDb.execute(sql.raw(query)); return (Array.isArray(res) ? res : ((res as { rows?: unknown[] }).rows ?? [])) as Record[]; } async function buildCases() { // deterministic argument samples (same rows on both engines) // PG is snake_case; alias back to the camelCase keys buildCases reads (quoted // aliases preserve case). SQLite golden was dumped with camelCase columns. const taskRows = await ids(`SELECT id, meego_id AS "meegoId" FROM tasks ORDER BY id LIMIT 8`); const featRows = await ids("SELECT id FROM features ORDER BY id LIMIT 8"); const secRows = await ids("SELECT id FROM security ORDER BY id LIMIT 8"); const nodeRows = await ids("SELECT id FROM nodes ORDER BY id"); const promptRows = await ids("SELECT id FROM readable_prompts ORDER BY id LIMIT 8"); const runRows = await ids(`SELECT id, target_type AS "targetType", target_id AS "targetId", batch_id AS "batchId" FROM readable_report_runs ORDER BY id LIMIT 8`); const batchRows = await ids("SELECT id FROM readable_batch_jobs ORDER BY id"); const srcRows = await ids(`SELECT target_type AS "targetType", target_id AS "targetId" FROM readable_sources ORDER BY target_type, target_id LIMIT 8`); const emRows = await ids(`SELECT DISTINCT owner_kind AS "ownerKind", owner_id AS "ownerId" FROM evidence_media ORDER BY owner_kind, owner_id LIMIT 8`); const evRows = await ids(`SELECT DISTINCT target_type AS "targetType", target_id AS "targetId" FROM evidence_capture_events ORDER BY target_type, target_id LIMIT 8`); const txRows = await ids(`SELECT DISTINCT kind, target_id AS "targetId" FROM task_transitions ORDER BY kind, target_id LIMIT 8`); const wkeys = (await ids("SELECT wkey FROM watcher_names ORDER BY wkey LIMIT 12")).map((r) => r.wkey as string); const larkIds = (await ids(`SELECT lark_user_id AS "larkUserId" FROM lark_users ORDER BY lark_user_id`)).map((r) => r.larkUserId as string); const setKeys = (await ids("SELECT key FROM settings ORDER BY key")).map((r) => r.key as string); const frRows = await ids(`SELECT task_id AS "taskId", round FROM fix_report_versions ORDER BY task_id, round LIMIT 8`); const cases: { name: string; run: () => Promise }[] = []; const add = (name: string, run: () => Promise) => cases.push({ name, run }); const each = (rows: T[], name: string, run: (r: T) => Promise) => rows.forEach((r, i) => add(`${name}[${i}]`, () => run(r))); // ── db.ts collections ── add("listTasks", () => db.listTasks()); add("listFeatures", () => db.listFeatures()); add("listSecurity", () => db.listSecurity()); add("listNodes", () => db.listNodes()); add("listAllClusters", () => db.listAllClusters()); add("getWebhookUrls", () => db.getWebhookUrls()); add("nextAwaitingFixTask", () => db.nextAwaitingFixTask()); // ── db.ts per-id ── each(taskRows, "getTask", (r) => db.getTask(r.id as string)); each(taskRows, "getTaskByMeegoId", (r) => db.getTaskByMeegoId(r.meegoId as string)); each(taskRows, "getTaskReadableReport", (r) => db.getTaskReadableReport(r.id as string)); each(taskRows, "getTaskCreatorEmail", (r) => db.getTaskCreatorEmail(r.id as string)); each(taskRows, "getFixReportVersions", (r) => db.getFixReportVersions(r.id as string)); each(frRows, "hasFixReportVersion", (r) => db.hasFixReportVersion(r.taskId as string, Number(r.round))); each(featRows, "getFeature", (r) => db.getFeature(r.id as string)); each(featRows, "getFeatureReadableReport", (r) => db.getFeatureReadableReport(r.id as string)); each(featRows, "getFeatureCreatorEmail", (r) => db.getFeatureCreatorEmail(r.id as string)); each(secRows, "getSecurity", (r) => db.getSecurity(r.id as string)); each(secRows, "getSecurityReadableReport", (r) => db.getSecurityReadableReport(r.id as string)); each(secRows, "getSecurityCreatorEmail", (r) => db.getSecurityCreatorEmail(r.id as string)); each(nodeRows, "getNode", (r) => db.getNode(r.id as string)); each(nodeRows, "getNodeClusters", (r) => db.getNodeClusters(r.id as string)); each(nodeRows, "countEvalOnNode", (r) => db.countEvalOnNode(r.id as string)); each(nodeRows, "countFixOnNode", (r) => db.countFixOnNode(r.id as string)); each(emRows, "getEvidenceMedia", (r) => db.getEvidenceMedia(r.ownerKind as never, r.ownerId as string)); each(emRows, "getEvidenceMediaMap", (r) => db.getEvidenceMediaMap(r.ownerKind as never, r.ownerId as string)); each(larkIds.map((x) => ({ x })), "getLarkUser", (r) => db.getLarkUser(r.x)); each(setKeys.map((x) => ({ x })), "getSetting", (r) => db.getSetting(r.x, null)); each(wkeys.map((x) => ({ x })), "getWatcherName", (r) => db.getWatcherName(r.x)); add("getWatcherNames(all)", () => db.getWatcherNames(wkeys)); // ── lab_db ── add("listPrompts", () => lab.listPrompts(false)); add("listPrompts(incl archived)", () => lab.listPrompts(true)); add("currentReadablePrompt", () => lab.currentReadablePrompt()); add("listBatchJobs", () => lab.listBatchJobs(50)); add("hasRunningBatchJob", () => lab.hasRunningBatchJob()); add("readLabMembers", () => lab.readLabMembers()); each(promptRows, "getPrompt", (r) => lab.getPrompt(r.id as string)); each(runRows, "getRun", (r) => lab.getRun(r.id as string)); each(runRows.filter((r) => r.batchId), "listRunsByBatch", (r) => lab.listRunsByBatch(r.batchId as string)); each(batchRows, "getBatchJob", (r) => lab.getBatchJob(r.id as string)); each(runRows, "listRunsForTarget", (r) => lab.listRunsForTarget(r.targetType as never, r.targetId as string)); each(srcRows, "getSource", (r) => lab.getSource(r.targetType as never, r.targetId as string)); add("isLabMember(sample)", async () => ({ a: await lab.isLabMember("jingyuan.yi"), b: await lab.isLabMember("nope-xyz") })); // ── lifecycle_db ── add("selectTransitions()", () => life.selectTransitions({})); add("selectTransitions(bug)", () => life.selectTransitions({ kind: "bug" })); add("listTargetSummaries()", () => life.listTargetSummaries({})); each(txRows, "transitionsForTarget", (r) => life.transitionsForTarget(r.kind as never, r.targetId as string)); each(txRows, "titleOf", (r) => life.titleOf(r.kind as never, r.targetId as string)); // ── evidmon_db ── add("selectEvents()", () => evid.selectEvents({})); add("listTargetsWithEvents", () => evid.listTargetsWithEvents(0, 200)); each(evRows, "eventsForTarget", (r) => evid.eventsForTarget(r.targetType as never, r.targetId as string)); // ── watchers (DB-backed resolution) ── add("resolveWatcherLabels", () => watch.resolveWatcherLabels(wkeys)); add("dedupeWatchers", () => watch.dedupeWatchers(wkeys)); return cases; } async function dump(outPath: string) { const cases = await buildCases(); const out: Record = {}; for (const c of cases) { try { out[c.name] = await c.run(); } catch (e) { out[c.name] = { __error: String((e as Error).message || e) }; } } await Bun.write(outPath, JSON.stringify({ engine: "pg", count: cases.length, results: out }, null, 2)); console.log(`[parity] engine=pg (drizzle): dumped ${cases.length} methods → ${outPath}`); } async function compare(aPath: string, bPath: string) { const A = JSON.parse(await Bun.file(aPath).text()); const B = JSON.parse(await Bun.file(bPath).text()); const names = [...new Set([...Object.keys(A.results), ...Object.keys(B.results)])].sort(); let dataDiff = 0, orderDiff = 0, ok = 0, errs = 0; console.log(`[parity] comparing ${A.engine} vs ${B.engine} over ${names.length} methods\n`); for (const n of names) { const a = A.results[n], b = B.results[n]; const aErr = a && typeof a === "object" && "__error" in a; const bErr = b && typeof b === "object" && "__error" in b; if (aErr || bErr) { if (JSON.stringify(a) === JSON.stringify(b)) { errs++; continue; } // both errored same → ignore console.log(` ✗ ${n} ERROR mismatch\n ${A.engine}: ${JSON.stringify(a)}\n ${B.engine}: ${JSON.stringify(b)}`); dataDiff++; continue; } const sortedA = JSON.stringify(canonSorted(a)), sortedB = JSON.stringify(canonSorted(b)); if (sortedA !== sortedB) { dataDiff++; // find a compact hint of where they differ const sa = sortedA.length, sb = sortedB.length; let i = 0; while (i < Math.min(sa, sb) && sortedA[i] === sortedB[i]) i++; console.log(` ✗ ${n} DATA differs (lenA=${sa} lenB=${sb}) near:\n ${A.engine}: …${sortedA.slice(Math.max(0, i - 20), i + 80)}…\n ${B.engine}: …${sortedB.slice(Math.max(0, i - 20), i + 80)}…`); continue; } if (JSON.stringify(canonOrdered(a)) !== JSON.stringify(canonOrdered(b))) { orderDiff++; console.log(` ⚠ ${n} same data, DIFFERENT ORDER`); continue; } ok++; } console.log(`\n[parity] ${ok} identical | ${orderDiff} order-only | ${dataDiff} DATA-DIFF | ${errs} (both-errored)`); process.exit(dataDiff === 0 ? 0 : 1); } const [mode, p1, p2] = process.argv.slice(2); if (mode === "dump" && p1) await dump(p1); else if (mode === "compare" && p1 && p2) await compare(p1, p2); else { console.error("usage: pg_parity.ts dump | compare "); process.exit(2); }