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}