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	// Immediate: it reads the next number before writing, and the sync's thread writes kepr.db too.53	const number = db54		.transaction(() => {55			const row = db56				.query<{ next: number }, [string]>(57					`SELECT COALESCE(MAX(number), 0) + 1 AS next58FROM issues59WHERE repo = ?`,60				)61				.get(issue.repo) as { next: number };62			db.query(63				`INSERT INTO issues (64repo, number, title, body, type, version, reporter_name, contact_email,65filed_at, ip_hash, token_hash66) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,67			).run(68				issue.repo,69				row.next,70				issue.title,71				issue.body,72				issue.type,73				issue.version,74				issue.reporterName,75				issue.contactEmail,76				issue.filedAt,77				issue.ipHash,78				hashToken(token),79			);80			return row.next;81		})82		.immediate();83	return { number, token };84}8586/** Issues filed from one hashed IP since a time, hidden ones included: spam stays counted. */87export function filingsSince(db: Database, ipHash: string, since: number): number {88	const row = db89		.query<{ n: number }, [string, number]>(90			"SELECT COUNT(*) AS n FROM issues WHERE ip_hash = ? AND filed_at > ?",91		)92		.get(ipHash, since);93	return row?.n ?? 0;94}9596/** A repo's visible issues with one status, newest first. */97export function listIssues(db: Database, repo: string, status: "open" | "closed"): Issue[] {98	return db99		.query<Issue, [string, string]>(100			`SELECT ${PUBLIC_COLUMNS}101FROM issues102WHERE repo = ? AND status = ? AND hidden = 0103ORDER BY number DESC`,104		)105		.all(repo, status);106}107108export function issueCounts(db: Database, repo: string): { open: number; closed: number } {109	const rows = db110		.query<{ status: "open" | "closed"; n: number }, [string]>(111			`SELECT status, COUNT(*) AS n112FROM issues113WHERE repo = ? AND hidden = 0114GROUP BY status`,115		)116		.all(repo);117	const count = (status: string) => rows.find((row) => row.status === status)?.n ?? 0;118	return { open: count("open"), closed: count("closed") };119}120121/** A visible issue by its number; a hidden one is as missing as an unknown one. */122export function findIssue(db: Database, repo: string, number: number): Issue | null {123	return db124		.query<Issue, [string, number]>(125			`SELECT ${PUBLIC_COLUMNS}126FROM issues127WHERE repo = ? AND number = ? AND hidden = 0`,128		)129		.get(repo, number);130}131132/** An issue's visible follow-ups and replies, oldest first. */133export function threadEntries(db: Database, issueId: number): Entry[] {134	return operatorEntries(db, issueId).filter((entry) => !entry.hidden);135}136137/** Every entry in an issue's thread, hidden ones included, for the console. */138export function operatorEntries(db: Database, issueId: number): Entry[] {139	return db140		.query<Entry, [number]>(141			`SELECT id, author, body, created_at, hidden142FROM issue_entries143WHERE issue_id = ?144ORDER BY id`,145		)146		.all(issueId);147}148149/** Hides or shows one entry of one issue; false when the issue has no such entry. */150export function setEntryHidden(151	db: Database,152	issueId: number,153	entryId: number,154	hidden: boolean,155): boolean {156	return (157		db158			.query("UPDATE issue_entries SET hidden = ? WHERE id = ? AND issue_id = ?")159			.run(hidden ? 1 : 0, entryId, issueId).changes === 1160	);161}162163export function addEntry(164	db: Database,165	issueId: number,166	author: Entry["author"],167	body: string,168	at: number,169): number {170	return Number(171		db172			.query("INSERT INTO issue_entries (issue_id, author, body, created_at) VALUES (?, ?, ?, ?)")173			.run(issueId, author, body, at).lastInsertRowid,174	);175}176177/** Issues anyone can see, across every public repo: the profile's count (§21.2). */178export function publicIssueCount(db: Database): number {179	return (180		db181			.query<{ n: number }, []>(182				`SELECT COUNT(*) AS n183FROM issues i184JOIN repos r ON r.name = i.repo185WHERE r.public = 1 AND i.hidden = 0`,186			)187			.get()?.n ?? 0188	);189}190191/** An issue as the operator sees it (§24): hidden or not, with the reporter's contact detail. */192export type OperatorIssue = Issue & {193	repo: string;194	hidden: number;195	contact_email: string;196	/** 1 when the reporter spoke last: the filing, or their newest visible follow-up (§24). */197	awaiting: number;198};199export type OperatorFilter = "open" | "awaiting" | "closed" | "hidden";200201const REPORTER_SPOKE_LAST = `COALESCE((202	SELECT author FROM issue_entries e203	WHERE e.issue_id = issues.id AND e.hidden = 0204	ORDER BY e.id DESC205	LIMIT 1206), 'reporter') = 'reporter'`;207const OPERATOR_COLUMNS = `${PUBLIC_COLUMNS}, repo, hidden, contact_email,208${REPORTER_SPOKE_LAST} AS awaiting`;209const OPERATOR_WHERE: Record<OperatorFilter, string> = {210	open: "hidden = 0 AND status = 'open'",211	awaiting: `hidden = 0 AND status = 'open' AND ${REPORTER_SPOKE_LAST}`,212	closed: "hidden = 0 AND status = 'closed'",213	hidden: "hidden = 1",214};215216/** Every repo's issues under one console tab, private repos' included, newest first. */217export function operatorIssues(db: Database, filter: OperatorFilter): OperatorIssue[] {218	return db219		.query<OperatorIssue, []>(220			`SELECT ${OPERATOR_COLUMNS}221FROM issues222WHERE ${OPERATOR_WHERE[filter]}223ORDER BY filed_at DESC, id DESC`,224		)225		.all();226}227228export function operatorIssue(db: Database, repo: string, number: number): OperatorIssue | null {229	return db230		.query<OperatorIssue, [string, number]>(231			`SELECT ${OPERATOR_COLUMNS}232FROM issues233WHERE repo = ? AND number = ?`,234		)235		.get(repo, number);236}237238/** Closes an issue, optionally "fixed in" a save (§23); reopening clears both. */239export function setClosed(db: Database, id: number, closedAt: number | null, fixedIn = ""): void {240	db.query("UPDATE issues SET status = ?, closed_at = ?, fixed_in = ? WHERE id = ?").run(241		closedAt === null ? "open" : "closed",242		closedAt,243		fixedIn,244		id,245	);246}247248export function setHidden(db: Database, id: number, hidden: boolean): void {249	db.query("UPDATE issues SET hidden = ? WHERE id = ?").run(hidden ? 1 : 0, id);250}251252export function setType(db: Database, id: number, type: IssueType): void {253	db.query("UPDATE issues SET type = ? WHERE id = ?").run(type, id);254}255256// A list of issue ids bound as one JSON parameter, so the statement stays fixed whatever its length.257const IDS = "id IN (SELECT value FROM json_each(?))";258259/** How many of these ids are issues; the console refuses a bulk action naming one that isn't. */260export function countIssues(db: Database, ids: number[]): number {261	return (262		db263			.query<{ n: number }, [string]>(`SELECT COUNT(*) AS n FROM issues WHERE ${IDS}`)264			.get(JSON.stringify(ids))?.n ?? 0265	);266}267268/**269 * Closes or reopens many issues at once (§24). Only those not already so change, so a closed270 * issue keeps its time and "fixed in" save.271 */272export function setManyClosed(db: Database, ids: number[], closedAt: number | null): void {273	db.query(274		`UPDATE issues275		SET status = ?1, closed_at = ?2, fixed_in = ''276		WHERE id IN (SELECT value FROM json_each(?3)) AND status != ?1`,277	).run(closedAt === null ? "open" : "closed", closedAt, JSON.stringify(ids));278}279280export function setManyHidden(db: Database, ids: number[], hidden: boolean): void {281	db.query(`UPDATE issues SET hidden = ? WHERE ${IDS}`).run(hidden ? 1 : 0, JSON.stringify(ids));282}283284export type BlockedReporter = { id: number; repo: string; number: number; blocked_at: number };285286/**287 * Blocks the address an issue was filed from (§23); the issue itself is untouched. Returns the288 * block's id. An issue with no address (imported from Kepr 2) blocks nothing and returns null.289 */290export function blockReporter(db: Database, issueId: number, now: number): number | null {291	return (292		db293			.query<{ id: number }, [number, number]>(294				`INSERT INTO blocked_reporters (ip_hash, repo, number, blocked_at)295				SELECT ip_hash, repo, number, ?296				FROM issues297				WHERE id = ? AND ip_hash != ''298				ON CONFLICT (ip_hash) DO UPDATE SET ip_hash = excluded.ip_hash299				RETURNING id`,300			)301			.get(now, issueId)?.id ?? null302	);303}304305/** Lifts a block; false when there was none with that id. */306export function unblockReporter(db: Database, id: number): boolean {307	return db.query("DELETE FROM blocked_reporters WHERE id = ?").run(id).changes > 0;308}309310export function isBlocked(db: Database, ipHash: string): boolean {311	return db.query("SELECT 1 FROM blocked_reporters WHERE ip_hash = ?").get(ipHash) !== null;312}313314/** Whether the address an issue came from is blocked. */315export function reporterBlocked(db: Database, issueId: number): boolean {316	return (317		db318			.query(319				`SELECT 1320				FROM blocked_reporters b321				JOIN issues i ON i.ip_hash = b.ip_hash322				WHERE i.id = ?`,323			)324			.get(issueId) !== null325	);326}327328export function blockedReporters(db: Database): BlockedReporter[] {329	return db330		.query<BlockedReporter, []>(331			"SELECT id, repo, number, blocked_at FROM blocked_reporters ORDER BY blocked_at DESC, id DESC",332		)333		.all();334}