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	hidden: number;27};2829export type NewIssue = {30	repo: string;31	title: string;32	body: string;33	type: IssueType;34	version: string;35	reporterName: string;36	contactEmail: string;37	ipHash: string;38	filedAt: number;39};4041// Everything the public may see; the contact email and IP hash stay out of every public read.42const PUBLIC_COLUMNS =43	"id, number, title, body, type, version, reporter_name, filed_at, status, fixed_in, token_hash";4445export function hashToken(token: string): string {46	return createHash("sha256").update(token).digest("hex");47}4849/** Files an issue under the repo's next number and returns it with its follow-up token (§23). */50export function fileIssue(db: Database, issue: NewIssue): { number: number; token: string } {51	const token = randomBytes(32).toString("hex");52	const number = db.transaction(() => {53		const row = db54			.query<{ next: number }, [string]>(55				`SELECT COALESCE(MAX(number), 0) + 1 AS next56FROM issues57WHERE repo = ?`,58			)59			.get(issue.repo) as { next: number };60		db.query(61			`INSERT INTO issues (62repo, number, title, body, type, version, reporter_name, contact_email,63filed_at, ip_hash, token_hash64) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,65		).run(66			issue.repo,67			row.next,68			issue.title,69			issue.body,70			issue.type,71			issue.version,72			issue.reporterName,73			issue.contactEmail,74			issue.filedAt,75			issue.ipHash,76			hashToken(token),77		);78		return row.next;79	})();80	return { number, token };81}8283/** Issues filed from one hashed IP since a time, hidden ones included: spam stays counted. */84export function filingsSince(db: Database, ipHash: string, since: number): number {85	const row = db86		.query<{ n: number }, [string, number]>(87			"SELECT COUNT(*) AS n FROM issues WHERE ip_hash = ? AND filed_at > ?",88		)89		.get(ipHash, since);90	return row?.n ?? 0;91}9293/** A repo's visible issues with one status, newest first. */94export function listIssues(db: Database, repo: string, status: "open" | "closed"): Issue[] {95	return db96		.query<Issue, [string, string]>(97			`SELECT ${PUBLIC_COLUMNS}98FROM issues99WHERE repo = ? AND status = ? AND hidden = 0100ORDER BY number DESC`,101		)102		.all(repo, status);103}104105export function issueCounts(db: Database, repo: string): { open: number; closed: number } {106	const rows = db107		.query<{ status: "open" | "closed"; n: number }, [string]>(108			`SELECT status, COUNT(*) AS n109FROM issues110WHERE repo = ? AND hidden = 0111GROUP BY status`,112		)113		.all(repo);114	const count = (status: string) => rows.find((row) => row.status === status)?.n ?? 0;115	return { open: count("open"), closed: count("closed") };116}117118/** A visible issue by its number; a hidden one is as missing as an unknown one. */119export function findIssue(db: Database, repo: string, number: number): Issue | null {120	return db121		.query<Issue, [string, number]>(122			`SELECT ${PUBLIC_COLUMNS}123FROM issues124WHERE repo = ? AND number = ? AND hidden = 0`,125		)126		.get(repo, number);127}128129/** An issue's visible follow-ups and replies, oldest first. */130export function threadEntries(db: Database, issueId: number): Entry[] {131	return operatorEntries(db, issueId).filter((entry) => !entry.hidden);132}133134/** Every entry in an issue's thread, hidden ones included, for the console. */135export function operatorEntries(db: Database, issueId: number): Entry[] {136	return db137		.query<Entry, [number]>(138			`SELECT id, author, body, created_at, hidden139FROM issue_entries140WHERE issue_id = ?141ORDER BY id`,142		)143		.all(issueId);144}145146/** Hides or shows one entry of one issue; false when the issue has no such entry. */147export function setEntryHidden(148	db: Database,149	issueId: number,150	entryId: number,151	hidden: boolean,152): boolean {153	return (154		db155			.query("UPDATE issue_entries SET hidden = ? WHERE id = ? AND issue_id = ?")156			.run(hidden ? 1 : 0, entryId, issueId).changes === 1157	);158}159160export function addEntry(161	db: Database,162	issueId: number,163	author: Entry["author"],164	body: string,165	at: number,166): number {167	return Number(168		db169			.query("INSERT INTO issue_entries (issue_id, author, body, created_at) VALUES (?, ?, ?, ?)")170			.run(issueId, author, body, at).lastInsertRowid,171	);172}173174/** Issues anyone can see, across every public repo: the profile's count (§21.2). */175export function publicIssueCount(db: Database): number {176	return (177		db178			.query<{ n: number }, []>(179				`SELECT COUNT(*) AS n180FROM issues i181JOIN repos r ON r.name = i.repo182WHERE r.public = 1 AND i.hidden = 0`,183			)184			.get()?.n ?? 0185	);186}187188/** An issue as the operator sees it (§24): hidden or not, with the reporter's contact detail. */189export type OperatorIssue = Issue & {190	repo: string;191	hidden: number;192	contact_email: string;193	/** 1 when the reporter spoke last: the filing, or their newest visible follow-up (§24). */194	awaiting: number;195};196export type OperatorFilter = "open" | "awaiting" | "closed" | "hidden";197198const REPORTER_SPOKE_LAST = `COALESCE((199	SELECT author FROM issue_entries e200	WHERE e.issue_id = issues.id AND e.hidden = 0201	ORDER BY e.id DESC202	LIMIT 1203), 'reporter') = 'reporter'`;204const OPERATOR_COLUMNS = `${PUBLIC_COLUMNS}, repo, hidden, contact_email,205${REPORTER_SPOKE_LAST} AS awaiting`;206const OPERATOR_WHERE: Record<OperatorFilter, string> = {207	open: "hidden = 0 AND status = 'open'",208	awaiting: `hidden = 0 AND status = 'open' AND ${REPORTER_SPOKE_LAST}`,209	closed: "hidden = 0 AND status = 'closed'",210	hidden: "hidden = 1",211};212213/** Every repo's issues under one console tab, private repos' included, newest first. */214export function operatorIssues(db: Database, filter: OperatorFilter): OperatorIssue[] {215	return db216		.query<OperatorIssue, []>(217			`SELECT ${OPERATOR_COLUMNS}218FROM issues219WHERE ${OPERATOR_WHERE[filter]}220ORDER BY filed_at DESC, id DESC`,221		)222		.all();223}224225export function operatorIssue(db: Database, repo: string, number: number): OperatorIssue | null {226	return db227		.query<OperatorIssue, [string, number]>(228			`SELECT ${OPERATOR_COLUMNS}229FROM issues230WHERE repo = ? AND number = ?`,231		)232		.get(repo, number);233}234235/** Closes an issue, optionally "fixed in" a save (§23); reopening clears both. */236export function setClosed(db: Database, id: number, closedAt: number | null, fixedIn = ""): void {237	db.query("UPDATE issues SET status = ?, closed_at = ?, fixed_in = ? WHERE id = ?").run(238		closedAt === null ? "open" : "closed",239		closedAt,240		fixedIn,241		id,242	);243}244245export function setHidden(db: Database, id: number, hidden: boolean): void {246	db.query("UPDATE issues SET hidden = ? WHERE id = ?").run(hidden ? 1 : 0, id);247}248249export function setType(db: Database, id: number, type: IssueType): void {250	db.query("UPDATE issues SET type = ? WHERE id = ?").run(type, id);251}252253// A list of issue ids bound as one JSON parameter, so the statement stays fixed whatever its length.254const IDS = "id IN (SELECT value FROM json_each(?))";255256/** How many of these ids are issues; the console refuses a bulk action naming one that isn't. */257export function countIssues(db: Database, ids: number[]): number {258	return (259		db260			.query<{ n: number }, [string]>(`SELECT COUNT(*) AS n FROM issues WHERE ${IDS}`)261			.get(JSON.stringify(ids))?.n ?? 0262	);263}264265/**266 * Closes or reopens many issues at once (§24). Only those not already so change, so a closed267 * issue keeps its time and "fixed in" save.268 */269export function setManyClosed(db: Database, ids: number[], closedAt: number | null): void {270	db.query(271		`UPDATE issues272		SET status = ?1, closed_at = ?2, fixed_in = ''273		WHERE id IN (SELECT value FROM json_each(?3)) AND status != ?1`,274	).run(closedAt === null ? "open" : "closed", closedAt, JSON.stringify(ids));275}276277export function setManyHidden(db: Database, ids: number[], hidden: boolean): void {278	db.query(`UPDATE issues SET hidden = ? WHERE ${IDS}`).run(hidden ? 1 : 0, JSON.stringify(ids));279}280281export type BlockedReporter = { id: number; repo: string; number: number; blocked_at: number };282283/**284 * Blocks the address an issue was filed from (§23); the issue itself is untouched. Returns the285 * block's id. An issue with no address (imported from Kepr 2) blocks nothing and returns null.286 */287export function blockReporter(db: Database, issueId: number, now: number): number | null {288	return (289		db290			.query<{ id: number }, [number, number]>(291				`INSERT INTO blocked_reporters (ip_hash, repo, number, blocked_at)292				SELECT ip_hash, repo, number, ?293				FROM issues294				WHERE id = ? AND ip_hash != ''295				ON CONFLICT (ip_hash) DO UPDATE SET ip_hash = excluded.ip_hash296				RETURNING id`,297			)298			.get(now, issueId)?.id ?? null299	);300}301302/** Lifts a block; false when there was none with that id. */303export function unblockReporter(db: Database, id: number): boolean {304	return db.query("DELETE FROM blocked_reporters WHERE id = ?").run(id).changes > 0;305}306307export function isBlocked(db: Database, ipHash: string): boolean {308	return db.query("SELECT 1 FROM blocked_reporters WHERE ip_hash = ?").get(ipHash) !== null;309}310311/** Whether the address an issue came from is blocked. */312export function reporterBlocked(db: Database, issueId: number): boolean {313	return (314		db315			.query(316				`SELECT 1317				FROM blocked_reporters b318				JOIN issues i ON i.ip_hash = b.ip_hash319				WHERE i.id = ?`,320			)321			.get(issueId) !== null322	);323}324325export function blockedReporters(db: Database): BlockedReporter[] {326	return db327		.query<BlockedReporter, []>(328			"SELECT id, repo, number, blocked_at FROM blocked_reporters ORDER BY blocked_at DESC, id DESC",329		)330		.all();331}