This repository has been archived on 2026-10-19. You can view files and clone it. You cannot open issues or pull requests or push a commit.
Files
DB-cleanup/merge-output/lopp-pr234/build.ts
StellarCrow 41766c68a2 chore: final state before the move to gart/gart-databases
Commits the work done since 2026-10-01 that was still local: the four-step
label and flag passes, the triage and recheck files, the v1 exports of
2026-10-06 and 2026-10-07, the split dossiers, and the Lopp PR 234 batch
(scripts, agent prompts, reader and checker files, the flag re-read of
2026-10-17, the review workbook and the import set of the 57 cases added
to stats2 as 506 to 562). Raw HTML and node_modules stay out (.gitignore).

The stats2 material in use was copied to gart/gart-databases (stats2/) on
2026-10-17; README-START-HERE.md now says so. This repo is closed.
2026-10-17 10:47:37 +02:00

207 lines
18 KiB
TypeScript

// Builds the deliverables from the readers' and checkers' files:
// sources/<key>_<victim>.txt one dossier per entry: proposed record, labels, summary, details,
// open questions, second reader, then the kept article text
// review/lopp-pr234-review-<date>.xlsx Sofia's review workbook (Cases, Duplicates, Open points, Second reader)
// review/lopp-pr234-cases-<date>.csv the Cases sheet as CSV
// bun build.ts
import ExcelJS from "exceljs";
import { createHash } from "crypto";
import { existsSync, mkdirSync, readFileSync, readdirSync, rmSync, writeFileSync } from "fs";
import { join } from "path";
import { FLAG_FIELDS, loadCase, pageParas, parseKeep } from "./lib.ts";
const HERE = import.meta.dir;
const TODAY = new Date().toLocaleDateString("sv");
const J = (p: string) => JSON.parse(readFileSync(join(HERE, p), "utf8"));
const entries: any[] = J("work/entries.json");
const fetched: Record<string, any[]> = J("work/fetched.json");
const validation: Record<string, string[]> = existsSync(join(HERE, "work/validation.json")) ? J("work/validation.json") : {};
for (const d of ["sources", "review"]) mkdirSync(join(HERE, d), { recursive: true });
for (const f of readdirSync(join(HERE, "sources"))) rmSync(join(HERE, "sources", f));
const STEPS = ["targeting", "targeting_accuracy", "who_was_attacked", "approach", "attack", "outcome", "obtained"];
const FLAGS = ["detention", "violence_torture", "drugs_alcohol", "weapons", "theft", "life_taken"];
const FIELDS = ["venue_of_control", "held_duration", "arrests"];
const slug = (s: string) => String(s ?? "").normalize("NFD").replace(/[̀-ͯ]/g, "").replace(/[^A-Za-z0-9]+/g, "-").replace(/^-|-$/g, "").slice(0, 40) || "Unidentified";
const list = (x: unknown) => [].concat((x as any) ?? []).join("; ");
const money = (v: any, k: string) => [v[`${k}_amount`] != null ? `${v[`${k}_amount`]} ${v[`${k}_currency`] ?? ""}`.trim() : "", v[`${k}_usd_stated`] != null ? `(USD ${v[`${k}_usd_stated`]} stated)` : ""].filter(Boolean).join(" ");
const people = (v: any) => (v.people ?? []).map((r: any) => `${r.role}: ${r.at_least ? "at least " : ""}${r.people}${r.detail ? ` (${r.detail})` : ""}`).join("; ");
const wrap = (s: string, w = 100, ind = " ") => {
const out: string[] = [];
for (const para of String(s ?? "").split(/\n+/)) {
let line = "";
for (const word of para.split(/\s+/)) {
if ((line + " " + word).trim().length > w) (out.push(ind + line.trim()), (line = word));
else line += " " + word;
}
out.push(ind + line.trim());
}
return out.join("\n");
};
const RULE = "=".repeat(100), THIN = "-".repeat(100);
// entries already imported into stats2: import/stats2-import-lopp-*/keys.json (Lopp key -> case id)
const imported: Record<string, number> = {};
for (const d of existsSync(join(HERE, "import")) ? readdirSync(join(HERE, "import")) : []) {
const f = join(HERE, "import", d, "keys.json");
if (existsSync(f)) Object.assign(imported, JSON.parse(readFileSync(f, "utf8")));
}
const rows: any[] = [];
const dups: any[] = [];
const opens: any[] = [];
const second: any[] = [];
for (const e of entries) {
const outF = join(HERE, "work/out", `${e.key}.json`);
if (!existsSync(outF)) { rows.push({ key: e.key, status: "NOT READ", lopp_description: e.lopp_label, lopp_date: e.date, lopp_victim: e.victim, lopp_location: e.location }); continue; }
const o = loadCase(e.key);
const c = existsSync(join(HERE, "work/check", `${e.key}.json`)) ? J(`work/check/${e.key}.json`) : null;
if (o.values && !Object.keys(o.values).length) o.values = null;
const v = o.values ?? {};
// text fields and page cuts the rewrite pass corrected are no longer open rejects
const fixFile = join(HERE, "work/fix", `${e.key}.json`);
const fix = existsSync(fixFile) ? J(`work/fix/${e.key}.json`) : null;
const rewritten = new Set(Object.keys(fix?.values ?? {}));
// a page fix that restores the evidence also settles a text field the fix names as kept
const pagesFixedEarly = (fix?.pages ?? []).length > 0 && (fix.pages as any[]).every((p) => (o.pages ?? []).some((x: any) => x.file === p.file));
const keptNote = (fix?.kept_as_is ?? []).join(" ");
for (const t of ["summary", "description", "case_details"]) if (pagesFixedEarly && keptNote.includes(t)) rewritten.add(t);
// the flags and obtained were re-read on 2026-10-17 under the owner's definitions; the second reader's verdicts on the old values no longer apply
// only the fields the re-read file actually set; a field it left out keeps its old verdict
const reread: Set<string> = o.reread_fields ?? new Set<string>();
if (reread.size && reread.size < FLAG_FIELDS.length + 1) validation[e.key] = [...(validation[e.key] ?? []), `re-read covers only ${[...reread].join(", ")}`];
const rejected = c ? Object.entries(c.verdicts ?? {}).filter(([k, x]) => x === "reject" && !rewritten.has(k) && !reread.has(k)).map(([k]) => k) : [];
// a page fix counts only when every page it names is one of the case pages
const pagesFixed = (fix?.pages ?? []).length > 0 && (fix.pages as any[]).every((p) => (o.pages ?? []).some((x: any) => x.file === p.file));
const pagesOpen = c?.pages_verdict === "reject" && !pagesFixed && !(fix?.kept_as_is ?? []).some((x: string) => /^pages/i.test(x));
if ((fix?.pages ?? []).length && !pagesFixed) validation[e.key] = [...(validation[e.key] ?? []), "a page fix names a page the case does not have"];
const checkerLine = !c ? "not checked" : [
c.status_verdict === "reject" ? `status rejected: ${c.status_notes}` : "",
pagesOpen ? `pages rejected: ${c.pages_notes}` : "",
...rejected.map((k) => `${k}: ${c.reasons?.[k] ?? ""}${c.alternatives?.[k] != null ? ` -> ${list(c.alternatives[k])}` : ""}`),
...(o.fixed ?? []).map((x: string) => `rewritten after the second reader: ${x}`),
].filter(Boolean).join("\n") || "all confirmed";
const fileName = `${e.key}_${slug(v.victim || e.victim)}.txt`;
// kept article text, page by page
const articles: string[] = [];
let nArt = 0;
for (const pg of o.pages ?? []) {
const nums = parseKeep(String(pg.keep ?? "")) ?? [];
if (!nums.length) continue;
const paras = pageParas(pg.file);
const text = nums.map((n) => paras.get(n)).filter(Boolean).join("\n\n");
const g = (fetched[e.key] ?? []).find((x: any) => pg.file.endsWith(`${e.key}-${(fetched[e.key] ?? []).indexOf(x) + 1}.txt`));
const head = readFileSync(join(HERE, pg.file), "utf8").split("\n").slice(0, 7);
const field = (name: string) => head.find((l) => l.startsWith(name))?.slice(11).trim() ?? "";
const url = field("URL");
nArt++;
articles.push([THIN, `FETCHED ARTICLE ${nArt}`, ` Source ${(() => { try { return new URL(url).host.replace(/^www\./, ""); } catch { return ""; } })()}`, ` URL ${url}`,
` Published ${g?.published || field("Published") || "(not given)"}`,
` Retrieved ${g?.retrieved || field("Retrieved") || TODAY} via ${field("Via") || g?.via}; page furniture cut by paragraph range (kept ${pg.keep} of ${paras.size}), article text unchanged`,
` Language as published (kept in the original language, not translated)`,
` Length ${text.length.toLocaleString("en")} chars`,
` Integrity sha256 ${createHash("sha256").update(text).digest("hex")}`, THIN, text, ""].join("\n"));
}
const record = o.values ? [
["date", v.date], ["victim", v.victim], ["location", v.location], ["country", v.country], ["description", v.description],
...FLAGS.map((f) => [f, String(v[f] ?? "") === "" ? "(unknown)" : v[f]]), ...FIELDS.map((f) => [f, v[f] || "(not stated)"]),
["demanded", [money(v, "demanded"), v.demanded_as_stated].filter(Boolean).join(" | ")], ["taken", [money(v, "taken"), v.taken_as_stated].filter(Boolean).join(" | ")],
["notes", o.notes],
].map(([k, x]) => ` ${String(k).padEnd(20)}${String(x ?? "")}`).join("\n") : " (no values: duplicate of an existing case)";
const txt = [
RULE, `LOPP ${e.key} | ${v.victim || e.victim} | ${v.date || e.date} | ${v.country || e.location.split(",").pop().trim()}`, RULE, "",
`STATUS ${o.status}${o.duplicate_of != null ? ` of ${o.duplicate_of}` : ""}${o.duplicate_evidence ? `: ${o.duplicate_evidence}` : ""}${o.scope_reason ? `: ${o.scope_reason}` : ""}`, "",
`PROPOSED RECORD [new case from Lopp's list, PR 234; read by two readers; flags and obtained re-read 2026-10-17 under the owner's definitions (empty = unknown); no operator has reviewed it; no stats2 id yet]`, record, "",
...((o.changes ?? []).length ? ["CHANGES OF 2026-10-17", ...o.changes.map((x: string) => ` ${x}`), ...(o.flags_notes ? [` re-read note: ${o.flags_notes}`] : []), ""] : []),
...(o.values ? ["FOUR-STEP LABELS", ...STEPS.map((s) => ` ${s.padEnd(20)}${list(v[s])}`), "", "PEOPLE ATTACKED", ` ${people(v) || "(none)"}`, "",
"PUBLIC SUMMARY [plain English, the text the site shows]", wrap(v.summary), "",
"CASE DETAILS [internal evidence record, quotations in the original language]", wrap(v.case_details), ""] : []),
"OPEN QUESTIONS FOR THE OWNER", ...((o.open_points ?? []).length ? o.open_points.map((q: any, i: number) => ` ${i + 1}. (${q.field}) ${q.detail}\n set to: ${q.first_value} | alternative: ${q.second_value}\n evidence: ${q.quote}`) : [" (none)"]), "",
"SECOND READER", wrap(checkerLine), "",
"LOPP'S ENTRY", ` ${e.date} | ${e.victim} | ${e.location}`, wrap(e.lopp_label), ...(o.lopp_differences ?? []).map((d: string) => ` differs: ${d}`), "",
"LINKED SOURCES", ...[...(v.sources ?? []), ...(o.new_sources_for_existing ?? [])].map((u: string, i: number) => ` ${i + 1}. ${u}`), "",
...articles,
].join("\n");
writeFileSync(join(HERE, "sources", fileName), txt);
const row: any = {
key: e.key, status: imported[e.key] ? "imported" : o.status, stats2_id: imported[e.key] ?? "", duplicate_of: o.duplicate_of ?? "", second_reader: checkerLine, rejected_count: rejected.length + (c?.status_verdict === "reject" ? 1 : 0) + (pagesOpen ? 1 : 0),
mechanical_problems: (validation[e.key] ?? []).join("\n"),
changes_2026_10_17: [...(o.changes ?? []), ...(o.flags_notes ? [`re-read note: ${o.flags_notes}`] : [])].join("\n"),
flags_reread: reread.size === FLAG_FIELDS.length + 1 ? "yes" : reread.size ? "partly" : "no",
date: v.date, victim: v.victim, location: v.location, country: v.country, description: v.description, lopp_description: e.lopp_label, summary: v.summary,
...Object.fromEntries(FLAGS.map((f) => [f, v[f]])), ...Object.fromEntries(FIELDS.map((f) => [f, v[f]])),
demanded_amount: v.demanded_amount, demanded_currency: v.demanded_currency, demanded_usd_stated: v.demanded_usd_stated, demanded_as_stated: v.demanded_as_stated,
taken_amount: v.taken_amount, taken_currency: v.taken_currency, taken_usd_stated: v.taken_usd_stated, taken_as_stated: v.taken_as_stated,
...Object.fromEntries(STEPS.map((s) => [s, list(v[s])])), people: people(v),
open_points: (o.open_points ?? []).map((q: any) => `${q.field}: ${q.detail}`).join("\n"),
sources: (v.sources ?? []).join("\n"), lopp_differences: (o.lopp_differences ?? []).join("\n"), notes: o.notes, case_details: v.case_details,
lopp_date: e.date, lopp_victim: e.victim, lopp_location: e.location, dossier: `lopp-pr234/sources/${fileName}`,
operator_decision: "", operator_comment: "",
};
rows.push(row);
if (o.status === "duplicate") dups.push({ key: e.key, duplicate_of: o.duplicate_of, evidence: o.duplicate_evidence, new_sources_for_existing: (o.new_sources_for_existing ?? []).join("\n"), second_reader: c ? `${c.status_verdict}${c.status_notes ? `: ${c.status_notes}` : ""}` : "not checked", lopp_line: e.lopp_label, operator_decision: "", operator_comment: "" });
for (const q of o.open_points ?? []) opens.push({ key: e.key, victim: v.victim, field: q.field, detail: q.detail, set_to: q.first_value, alternative: q.second_value, quote: q.quote, operator_decision: "" });
if (c?.status_verdict === "reject") second.push({ key: e.key, field: "status", reader_value: o.status, reason: c.status_notes ?? "", checker_alternative: "", operator_decision: "" });
if (pagesOpen) second.push({ key: e.key, field: "pages", reader_value: (o.pages ?? []).map((p: any) => `${p.file.split("/").pop()}: ${p.keep}`).join("; "), reason: c.pages_notes ?? "", checker_alternative: "", operator_decision: "" });
if (c) for (const k of rejected) second.push({ key: e.key, field: k, reader_value: list(v[k] ?? ""), reason: c.reasons?.[k] ?? "", checker_alternative: list(c.alternatives?.[k] ?? ""), operator_decision: "" });
}
const order = { new: 0, "out of scope": 1, "no source": 2, duplicate: 3, imported: 4, "NOT READ": 5 } as Record<string, number>;
rows.sort((a, b) => (order[a.status] ?? 9) - (order[b.status] ?? 9) || a.key.localeCompare(b.key));
// on the Cases sheet every column Gart wrote carries a G_ prefix; Lopp's and Sofia's columns keep theirs
const ours = (k: string) => (/^(lopp_|operator_)/.test(k) && k !== "lopp_differences" ? k : `G_${k}`);
const wb = new ExcelJS.Workbook();
const sheet = (name: string, data: any[], header: (k: string) => string = (k) => k) => {
const ws = wb.addWorksheet(name, { views: [{ state: "frozen", ySplit: 1, xSplit: 1 }] });
const cols = data.length ? Object.keys(data.find((r) => r.status !== "NOT READ") ?? data[0]) : ["(empty)"];
ws.columns = cols.map((k) => ({ header: header(k), key: k, width: ["summary", "description", "case_details", "notes", "second_reader", "open_points", "detail", "quote", "reason", "evidence", "lopp_line", "lopp_description", "changes_2026_10_17"].includes(k) ? 60 : 18 }));
for (const r of data) ws.addRow(r);
ws.getRow(1).font = { bold: true };
ws.eachRow((row, i) => { if (i > 1) row.alignment = { vertical: "top", wrapText: true }; });
ws.autoFilter = { from: { row: 1, column: 1 }, to: { row: 1, column: cols.length } };
};
// Read me: what the sheets hold and the rules of 2026-10-16 and 2026-10-17 the values follow
const readme = wb.addWorksheet("Read me");
readme.columns = [{ header: "", key: "a", width: 34 }, { header: "", key: "b", width: 120 }];
const README: [string, string][] = [
["Lopp PR 234: review workbook", `Built ${TODAY}. 340 entries from Jameson Lopp's list (PR 234). Every value is a proposal until an operator decides; difficult or unclear rows go to the owner.`],
["", ""],
["Sheets", ""],
["Imported", "Rows with status imported are in stats2 already (column G_stats2_id); no review needed. Imported 2026-10-17: the entries with no open point, no second-reader reject and no validator problem."],
["Cases", "One row per entry. Columns starting with G_ are Gart's proposal; lopp_ columns are Lopp's own entry; operator_decision and operator_comment are for the operator."],
["Duplicates", "Entries that are already a stats2 case, with any new source links for that case."],
["Open points", "Questions the readers could not settle by a rule or ruling."],
["Second reader rejects", "Values the second reader disputed. Verdicts on the flags and on obtained are left out: those were re-read on 2026-10-17."],
["", ""],
["Changes of 2026-10-17", "Column G_changes_2026_10_17 lists every value that changed on the row, old -> new, and why."],
["", ""],
["Rules the values follow", ""],
["Flags (owner, 2026-10-16)", "yes = it occurred; no = it surely did not occur (the source says so, or nobody was present); empty = unknown, the information is not available; unconfirmed = alleged, charged or disputed, not established. A flag the source is silent on is empty, never no. violence_torture can also be threat only."],
["Weapons (owner, 2026-10-16)", "Any object used as a weapon counts (a car driven at a person, a scarf used to strangle). Restraints are not weapons; a weapon found later and never shown is no."],
["Obtained (owner, 2026-10-17)", "Unknown = the sources do not say what, if anything, was taken. Unconfirmed = a taking alleged or charged and not established. The label Unknown is not yet in the live database; the SQL that adds it is in stats2/export/flags-silence-2026-10-16/."],
["Scope", "In: physical violence, detention or a credible threat, with a crypto nexus: crypto the target, crypto demanded as ransom, or the victim targeted for perceived holdings (research paper, February 2026). Crypto must be explicit: demanded, stolen, targeted or the motive (Sofi's handbook). Entries moved on 2026-10-17 say so in G_changes_2026_10_17."],
["No source", "Entries whose articles could not be read rest on Lopp's line. Each needs an operator's decision; none is dropped in advance."],
["Roles (owner, 2026-10-16)", "Operators decide everything the rules cover and escalate only difficult or unclear points to the owner."],
["Full rules", "gart-io/databases/stats2/DB-GUIDE.md; label tests in DB-cleanup/merge-output/LABEL-RULES.md; flag tests in FLAG-RULES.md."],
];
for (const [a, b] of README) readme.addRow({ a, b });
readme.getColumn(1).font = { bold: true };
readme.eachRow((row) => { row.alignment = { vertical: "top", wrapText: true }; });
sheet("Cases", rows, ours);
sheet("Duplicates", dups);
sheet("Open points", opens);
sheet("Second reader rejects", second);
const xlsx = join(HERE, "review", `lopp-pr234-review-${TODAY}.xlsx`);
await wb.xlsx.writeFile(xlsx);
const cols = Object.keys(rows.find((r) => r.status !== "NOT READ") ?? rows[0]);
const q = (x: unknown) => `"${String(x ?? "").replace(/"/g, '""')}"`;
writeFileSync(join(HERE, "review", `lopp-pr234-cases-${TODAY}.csv`), [cols.map(ours).join(","), ...rows.map((r) => cols.map((k) => q(r[k])).join(","))].join("\n") + "\n");
const count = (s: string) => rows.filter((r) => r.status === s).length;
console.log(`rows ${rows.length}: imported ${count("imported")}, new ${count("new")}, duplicate ${count("duplicate")}, out of scope ${count("out of scope")}, no source ${count("no source")}, not read ${count("NOT READ")}; open points ${opens.length}; second-reader rejects ${second.length}`);
console.log(`wrote ${xlsx}`);