add the issues and thread tables to kepr.db with per-repo numbers, hashed follow-up tokens and the data layer the public pages read (v3.0.27)

@@ -0,0 +1,137 @@1+import type { Database } from "bun:sqlite";2+import { createHash, randomBytes } from "node:crypto";3+4+export const ISSUE_TYPES = ["bug", "idea", "question"] as const;5+export type IssueType = (typeof ISSUE_TYPES)[number];6+7+export type Issue = {8+	id: number;9+	number: number;10+	title: string;11+	body: string;12+	type: IssueType;13+	version: string;14+	reporter_name: string;15+	filed_at: number;16+	status: "open" | "closed";17+	fixed_in: string;18+	token_hash: string | null;19+};20+21+export type Entry = {22+	id: number;23+	author: "reporter" | "operator";24+	body: string;25+	created_at: number;26+};27+28+export type NewIssue = {29+	repo: string;30+	title: string;31+	body: string;32+	type: IssueType;33+	version: string;34+	reporterName: string;35+	contactEmail: string;36+	ipHash: string;37+	filedAt: number;38+};39+40+// Everything the public may see; the contact email and IP hash stay out of every public read.41+const PUBLIC_COLUMNS =42+	"id, number, title, body, type, version, reporter_name, filed_at, status, fixed_in, token_hash";43+44+export function hashToken(token: string): string {45+	return createHash("sha256").update(token).digest("hex");46+}47+48+/** Files an issue under the repo's next number and returns it with its follow-up token (§23). */49+export function fileIssue(db: Database, issue: NewIssue): { number: number; token: string } {50+	const token = randomBytes(32).toString("hex");51+	const number = db.transaction(() => {52+		const row = db53+			.query<{ next: number }, [string]>(54+				"SELECT COALESCE(MAX(number), 0) + 1 AS next FROM issues WHERE repo = ?",55+			)56+			.get(issue.repo) as { next: number };57+		db.query(58+			`INSERT INTO issues (repo, number, title, body, type, version, reporter_name, contact_email,59+				filed_at, ip_hash, token_hash) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,60+		).run(61+			issue.repo,62+			row.next,63+			issue.title,64+			issue.body,65+			issue.type,66+			issue.version,67+			issue.reporterName,68+			issue.contactEmail,69+			issue.filedAt,70+			issue.ipHash,71+			hashToken(token),72+		);73+		return row.next;74+	})();75+	return { number, token };76+}77+78+/** Issues filed from one hashed IP since a time, hidden ones included: spam stays counted. */79+export function filingsSince(db: Database, ipHash: string, since: number): number {80+	const row = db81+		.query<{ n: number }, [string, number]>(82+			"SELECT COUNT(*) AS n FROM issues WHERE ip_hash = ? AND filed_at > ?",83+		)84+		.get(ipHash, since);85+	return row?.n ?? 0;86+}87+88+/** A repo's visible issues with one status, newest first. */89+export function listIssues(db: Database, repo: string, status: "open" | "closed"): Issue[] {90+	return db91+		.query<Issue, [string, string]>(92+			`SELECT ${PUBLIC_COLUMNS} FROM issues WHERE repo = ? AND status = ? AND hidden = 093+			ORDER BY number DESC`,94+		)95+		.all(repo, status);96+}97+98+export function issueCounts(db: Database, repo: string): { open: number; closed: number } {99+	const rows = db100+		.query<{ status: "open" | "closed"; n: number }, [string]>(101+			"SELECT status, COUNT(*) AS n FROM issues WHERE repo = ? AND hidden = 0 GROUP BY status",102+		)103+		.all(repo);104+	const count = (status: string) => rows.find((row) => row.status === status)?.n ?? 0;105+	return { open: count("open"), closed: count("closed") };106+}107+108+/** A visible issue by its number; a hidden one is as missing as an unknown one. */109+export function findIssue(db: Database, repo: string, number: number): Issue | null {110+	return db111+		.query<Issue, [string, number]>(112+			`SELECT ${PUBLIC_COLUMNS} FROM issues WHERE repo = ? AND number = ? AND hidden = 0`,113+		)114+		.get(repo, number);115+}116+117+export function threadEntries(db: Database, issueId: number): Entry[] {118+	return db119+		.query<Entry, [number]>(120+			"SELECT id, author, body, created_at FROM issue_entries WHERE issue_id = ? ORDER BY id",121+		)122+		.all(issueId);123+}124+125+export function addEntry(126+	db: Database,127+	issueId: number,128+	author: Entry["author"],129+	body: string,130+	at: number,131+): number {132+	return Number(133+		db134+			.query("INSERT INTO issue_entries (issue_id, author, body, created_at) VALUES (?, ?, ?, ?)")135+			.run(issueId, author, body, at).lastInsertRowid,136+	);137+}

@@ -0,0 +1,74 @@1+import { describe, expect, test } from "bun:test";2+import { mkdtemp } from "node:fs/promises";3+import { tmpdir } from "node:os";4+import { join } from "node:path";5+import {6+	addEntry,7+	fileIssue,8+	filingsSince,9+	findIssue,10+	hashToken,11+	issueCounts,12+	listIssues,13+	type NewIssue,14+	threadEntries,15+} from "../src/issues/store.ts";16+import { openKeprDb } from "../src/server/db.ts";17+18+const newIssue = (repo: string, title: string, filedAt = 1000): NewIssue => ({19+	repo,20+	title,21+	body: "It broke.",22+	type: "bug",23+	version: "1.0.0",24+	reporterName: "",25+	contactEmail: "[email protected]",26+	ipHash: "ip-a",27+	filedAt,28+});29+30+describe("issue data layer", async () => {31+	const db = openKeprDb(await mkdtemp(join(tmpdir(), "kepr-issues-")));32+33+	test("numbers are per repo and continue after an imported number", () => {34+		expect(fileIssue(db, newIssue("app", "one")).number).toBe(1);35+		expect(fileIssue(db, newIssue("app", "two")).number).toBe(2);36+		expect(fileIssue(db, newIssue("lib", "first")).number).toBe(1);37+		db.run(38+			"INSERT INTO issues (repo, number, title, body, type, filed_at) VALUES ('lib', 41, 'from kepr 2', 'x', 'idea', 5)",39+		);40+		expect(fileIssue(db, newIssue("lib", "next")).number).toBe(42);41+	});42+43+	test("only the token's hash is stored, and public reads leave out contact and IP", () => {44+		const { number, token } = fileIssue(db, newIssue("app", "secret"));45+		expect(token).toMatch(/^[0-9a-f]{64}$/);46+		const issue = findIssue(db, "app", number);47+		expect(issue?.token_hash).toBe(hashToken(token));48+		expect(JSON.stringify(issue)).not.toContain(token);49+		expect(Object.keys(issue ?? {})).not.toContain("contact_email");50+		expect(Object.keys(issue ?? {})).not.toContain("ip_hash");51+	});52+53+	test("hidden issues vanish from lists, counts and lookups but still count against the IP", () => {54+		const before = filingsSince(db, "ip-a", 0);55+		const { number } = fileIssue(db, newIssue("app", "spam"));56+		db.run("UPDATE issues SET hidden = 1 WHERE repo = 'app' AND number = ?", [number]);57+		expect(findIssue(db, "app", number)).toBeNull();58+		expect(listIssues(db, "app", "open").map((i) => i.title)).not.toContain("spam");59+		expect(issueCounts(db, "app")).toEqual({ open: 3, closed: 0 });60+		expect(filingsSince(db, "ip-a", 0)).toBe(before + 1);61+		expect(filingsSince(db, "ip-a", 1000)).toBe(0);62+	});63+64+	test("the thread keeps entries in order with their author kind", () => {65+		const issue = findIssue(db, "app", 1);66+		if (!issue) throw new Error("issue 1 missing");67+		addEntry(db, issue.id, "reporter", "More detail.", 2000);68+		addEntry(db, issue.id, "operator", "Thanks.", 3000);69+		expect(threadEntries(db, issue.id).map((e) => [e.author, e.body])).toEqual([70+			["reporter", "More detail."],71+			["operator", "Thanks."],72+		]);73+	});74+});