store.ts

1import type { Database } from "bun:sqlite";2import { createHash, randomBytes } from "node:crypto";34export const ISSUE_TYPES = ["bug", "idea", "question"] as const;5export type IssueType = (typeof ISSUE_TYPES)[number];67export 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};2021export type Entry = {22	id: number;23	author: "reporter" | "operator";24	body: string;25	created_at: number;26};2728export 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};3940// Everything the public may see; the contact email and IP hash stay out of every public read.41const PUBLIC_COLUMNS =42	"id, number, title, body, type, version, reporter_name, filed_at, status, fixed_in, token_hash";4344export function hashToken(token: string): string {45	return createHash("sha256").update(token).digest("hex");46}4748/** Files an issue under the repo's next number and returns it with its follow-up token (§23). */49export 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 next55FROM issues56WHERE repo = ?`,57			)58			.get(issue.repo) as { next: number };59		db.query(60			`INSERT INTO issues (61repo, number, title, body, type, version, reporter_name, contact_email,62filed_at, ip_hash, token_hash63) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,64		).run(65			issue.repo,66			row.next,67			issue.title,68			issue.body,69			issue.type,70			issue.version,71			issue.reporterName,72			issue.contactEmail,73			issue.filedAt,74			issue.ipHash,75			hashToken(token),76		);77		return row.next;78	})();79	return { number, token };80}8182/** Issues filed from one hashed IP since a time, hidden ones included: spam stays counted. */83export function filingsSince(db: Database, ipHash: string, since: number): number {84	const row = db85		.query<{ n: number }, [string, number]>(86			"SELECT COUNT(*) AS n FROM issues WHERE ip_hash = ? AND filed_at > ?",87		)88		.get(ipHash, since);89	return row?.n ?? 0;90}9192/** A repo's visible issues with one status, newest first. */93export function listIssues(db: Database, repo: string, status: "open" | "closed"): Issue[] {94	return db95		.query<Issue, [string, string]>(96			`SELECT ${PUBLIC_COLUMNS}97FROM issues98WHERE repo = ? AND status = ? AND hidden = 099ORDER BY number DESC`,100		)101		.all(repo, status);102}103104export function issueCounts(db: Database, repo: string): { open: number; closed: number } {105	const rows = db106		.query<{ status: "open" | "closed"; n: number }, [string]>(107			`SELECT status, COUNT(*) AS n108FROM issues109WHERE repo = ? AND hidden = 0110GROUP BY status`,111		)112		.all(repo);113	const count = (status: string) => rows.find((row) => row.status === status)?.n ?? 0;114	return { open: count("open"), closed: count("closed") };115}116117/** A visible issue by its number; a hidden one is as missing as an unknown one. */118export function findIssue(db: Database, repo: string, number: number): Issue | null {119	return db120		.query<Issue, [string, number]>(121			`SELECT ${PUBLIC_COLUMNS}122FROM issues123WHERE repo = ? AND number = ? AND hidden = 0`,124		)125		.get(repo, number);126}127128export function threadEntries(db: Database, issueId: number): Entry[] {129	return db130		.query<Entry, [number]>(131			"SELECT id, author, body, created_at FROM issue_entries WHERE issue_id = ? ORDER BY id",132		)133		.all(issueId);134}135136export function addEntry(137	db: Database,138	issueId: number,139	author: Entry["author"],140	body: string,141	at: number,142): number {143	return Number(144		db145			.query("INSERT INTO issue_entries (issue_id, author, body, created_at) VALUES (?, ?, ?, ?)")146			.run(issueId, author, body, at).lastInsertRowid,147	);148}149150/** Issues anyone can see, across every public repo: the profile's count (§21.2). */151export function publicIssueCount(db: Database): number {152	return (153		db154			.query<{ n: number }, []>(155				`SELECT COUNT(*) AS n156FROM issues i157JOIN repos r ON r.name = i.repo158WHERE r.public = 1 AND i.hidden = 0`,159			)160			.get()?.n ?? 0161	);162}163164/** An issue as the operator sees it (§24): hidden or not, with the reporter's contact detail. */165export type OperatorIssue = Issue & { repo: string; hidden: number; contact_email: string };166export type OperatorFilter = "open" | "closed" | "hidden";167168const OPERATOR_COLUMNS = `${PUBLIC_COLUMNS}, repo, hidden, contact_email`;169170/** Every repo's issues, private repos' included: open or closed ones not hidden, or the hidden. */171export function operatorIssues(db: Database, filter: OperatorFilter): OperatorIssue[] {172	const where = filter === "hidden" ? "hidden = 1" : "hidden = 0 AND status = ?";173	const query = db.query<OperatorIssue, string[]>(174		`SELECT ${OPERATOR_COLUMNS}175FROM issues176WHERE ${where}177ORDER BY filed_at DESC, id DESC`,178	);179	return filter === "hidden" ? query.all() : query.all(filter);180}181182export function operatorIssue(db: Database, repo: string, number: number): OperatorIssue | null {183	return db184		.query<OperatorIssue, [string, number]>(185			`SELECT ${OPERATOR_COLUMNS}186FROM issues187WHERE repo = ? AND number = ?`,188		)189		.get(repo, number);190}191192/** Closes an issue, optionally "fixed in" a save (§23); reopening clears both. */193export function setClosed(db: Database, id: number, closedAt: number | null, fixedIn = ""): void {194	db.query("UPDATE issues SET status = ?, closed_at = ?, fixed_in = ? WHERE id = ?").run(195		closedAt === null ? "open" : "closed",196		closedAt,197		fixedIn,198		id,199	);200}201202export function setHidden(db: Database, id: number, hidden: boolean): void {203	db.query("UPDATE issues SET hidden = ? WHERE id = ?").run(hidden ? 1 : 0, id);204}205206export function setType(db: Database, id: number, type: IssueType): void {207	db.query("UPDATE issues SET type = ? WHERE id = ?").run(type, id);208}