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 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}7778/** Issues filed from one hashed IP since a time, hidden ones included: spam stays counted. */79export 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}8788/** A repo's visible issues with one status, newest first. */89export 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}9798export 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}107108/** A visible issue by its number; a hidden one is as missing as an unknown one. */109export 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}116117export 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}124125export 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}138139/** Issues anyone can see, across every public repo: the profile's count (§21.2). */140export function publicIssueCount(db: Database): number {141	return (142		db143			.query<{ n: number }, []>(144				"SELECT COUNT(*) AS n FROM issues i JOIN repos r ON r.name = i.repo WHERE r.public = 1 AND i.hidden = 0",145			)146			.get()?.n ?? 0147	);148}149150/** An issue as the operator sees it (§24): hidden or not, with the reporter's contact detail. */151export type OperatorIssue = Issue & { repo: string; hidden: number; contact_email: string };152export type OperatorFilter = "open" | "closed" | "hidden";153154const OPERATOR_COLUMNS = `${PUBLIC_COLUMNS}, repo, hidden, contact_email`;155156/** Every repo's issues, private repos' included: open or closed ones not hidden, or the hidden. */157export function operatorIssues(db: Database, filter: OperatorFilter): OperatorIssue[] {158	const where = filter === "hidden" ? "hidden = 1" : "hidden = 0 AND status = ?";159	const query = db.query<OperatorIssue, string[]>(160		`SELECT ${OPERATOR_COLUMNS} FROM issues WHERE ${where} ORDER BY filed_at DESC, id DESC`,161	);162	return filter === "hidden" ? query.all() : query.all(filter);163}164165export function operatorIssue(db: Database, repo: string, number: number): OperatorIssue | null {166	return db167		.query<OperatorIssue, [string, number]>(168			`SELECT ${OPERATOR_COLUMNS} FROM issues WHERE repo = ? AND number = ?`,169		)170		.get(repo, number);171}172173/** Closes an issue, optionally "fixed in" a save (§23); reopening clears both. */174export function setClosed(db: Database, id: number, closedAt: number | null, fixedIn = ""): void {175	db.query("UPDATE issues SET status = ?, closed_at = ?, fixed_in = ? WHERE id = ?").run(176		closedAt === null ? "open" : "closed",177		closedAt,178		fixedIn,179		id,180	);181}182183export function setHidden(db: Database, id: number, hidden: boolean): void {184	db.query("UPDATE issues SET hidden = ? WHERE id = ?").run(hidden ? 1 : 0, id);185}186187export function setType(db: Database, id: number, type: IssueType): void {188	db.query("UPDATE issues SET type = ? WHERE id = ?").run(type, id);189}