store.ts

1import type { Database } from "bun:sqlite";23export const ISSUE_TYPES = ["bug", "idea", "question"] as const;4export type IssueType = (typeof ISSUE_TYPES)[number];56export type Issue = {7	id: number;8	number: number;9	title: string;10	body: string;11	type: IssueType;12	version: string;13	reporter_name: string;14	filed_at: number;15	status: "open" | "closed";16	fixed_in: string;17};1819/** A row of the public list: the issue and its visible replies. */20export type ListedIssue = Issue & { replies: number };2122/** One reply. "reporter" is anyone from the public, under the name they chose ('' is anonymous). */23export type Entry = {24	id: number;25	author: "reporter" | "operator";26	name: string;27	body: string;28	created_at: number;29	hidden: number;30	reply_to: number | null;31};3233export type NewEntry = {34	author: Entry["author"];35	body: string;36	at: number;37	name?: string;38	ipHash?: string;39	replyTo?: number | null;40};4142export type NewIssue = {43	repo: string;44	title: string;45	body: string;46	type: IssueType;47	version: string;48	reporterName: string;49	contactEmail: string;50	ipHash: string;51	filedAt: number;52};5354// Everything the public may see; the contact email and IP hash stay out of every public read.55const PUBLIC_COLUMNS =56	"id, number, title, body, type, version, reporter_name, filed_at, status, fixed_in";5758/** Files an issue under the repo's next number and returns that number (§23). */59export function fileIssue(db: Database, issue: NewIssue): number {60	// Immediate: it reads the next number before writing, and the sync's thread writes kepr.db too.61	return db62		.transaction(() => {63			const row = db64				.query<{ next: number }, [string]>(65					`SELECT COALESCE(MAX(number), 0) + 1 AS next66FROM issues67WHERE repo = ?`,68				)69				.get(issue.repo) as { next: number };70			db.query(71				`INSERT INTO issues (72repo, number, title, body, type, version, reporter_name, contact_email, filed_at, ip_hash73) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,74			).run(75				issue.repo,76				row.next,77				issue.title,78				issue.body,79				issue.type,80				issue.version,81				issue.reporterName,82				issue.contactEmail,83				issue.filedAt,84				issue.ipHash,85			);86			return row.next;87		})88		.immediate();89}9091/** Issues filed from one hashed IP since a time, hidden ones included: spam stays counted. */92export function filingsSince(db: Database, ipHash: string, since: number): number {93	const row = db94		.query<{ n: number }, [string, number]>(95			"SELECT COUNT(*) AS n FROM issues WHERE ip_hash = ? AND filed_at > ?",96		)97		.get(ipHash, since);98	return row?.n ?? 0;99}100101/** Replies posted from one hashed IP since a time, hidden ones included. */102export function repliesSince(db: Database, ipHash: string, since: number): number {103	const row = db104		.query<{ n: number }, [string, number]>(105			"SELECT COUNT(*) AS n FROM issue_entries WHERE ip_hash = ? AND created_at > ?",106		)107		.get(ipHash, since);108	return row?.n ?? 0;109}110111/** A repo's visible issues with one status, newest first, each counting its visible replies. */112export function listIssues(db: Database, repo: string, status: "open" | "closed"): ListedIssue[] {113	return db114		.query<ListedIssue, [string, string]>(115			`SELECT ${PUBLIC_COLUMNS}, (116	SELECT COUNT(*) FROM issue_entries e WHERE e.issue_id = issues.id AND e.hidden = 0117) AS replies118FROM issues119WHERE repo = ? AND status = ? AND hidden = 0120ORDER BY number DESC`,121		)122		.all(repo, status);123}124125export function issueCounts(db: Database, repo: string): { open: number; closed: number } {126	const rows = db127		.query<{ status: "open" | "closed"; n: number }, [string]>(128			`SELECT status, COUNT(*) AS n129FROM issues130WHERE repo = ? AND hidden = 0131GROUP BY status`,132		)133		.all(repo);134	const count = (status: string) => rows.find((row) => row.status === status)?.n ?? 0;135	return { open: count("open"), closed: count("closed") };136}137138/** A visible issue by its number; a hidden one is as missing as an unknown one. */139export function findIssue(db: Database, repo: string, number: number): Issue | null {140	return db141		.query<Issue, [string, number]>(142			`SELECT ${PUBLIC_COLUMNS}143FROM issues144WHERE repo = ? AND number = ? AND hidden = 0`,145		)146		.get(repo, number);147}148149/** An issue's visible replies, oldest first. */150export function threadEntries(db: Database, issueId: number): Entry[] {151	return operatorEntries(db, issueId).filter((entry) => !entry.hidden);152}153154/** Every reply in an issue's thread, hidden ones included, for the console. */155export function operatorEntries(db: Database, issueId: number): Entry[] {156	return db157		.query<Entry, [number]>(158			`SELECT id, author, name, body, created_at, hidden, reply_to159FROM issue_entries160WHERE issue_id = ?161ORDER BY id`,162		)163		.all(issueId);164}165166/** Hides or shows one entry of one issue; false when the issue has no such entry. */167export function setEntryHidden(168	db: Database,169	issueId: number,170	entryId: number,171	hidden: boolean,172): boolean {173	return (174		db175			.query("UPDATE issue_entries SET hidden = ? WHERE id = ? AND issue_id = ?")176			.run(hidden ? 1 : 0, entryId, issueId).changes === 1177	);178}179180export function addEntry(db: Database, issueId: number, entry: NewEntry): number {181	return Number(182		db183			.query(184				`INSERT INTO issue_entries (issue_id, author, name, body, created_at, ip_hash, reply_to)185VALUES (?, ?, ?, ?, ?, ?, ?)`,186			)187			.run(188				issueId,189				entry.author,190				entry.name ?? "",191				entry.body,192				entry.at,193				entry.ipHash ?? "",194				entry.replyTo ?? null,195			).lastInsertRowid,196	);197}198199/** Issues anyone can see, across every public repo: the profile's count (§21.2). */200export function publicIssueCount(db: Database): number {201	return (202		db203			.query<{ n: number }, []>(204				`SELECT COUNT(*) AS n205FROM issues i206JOIN repos r ON r.name = i.repo207WHERE r.public = 1 AND i.hidden = 0`,208			)209			.get()?.n ?? 0210	);211}212213/** An issue as the operator sees it (§24): hidden or not, with the reporter's contact detail. */214export type OperatorIssue = Issue & {215	repo: string;216	hidden: number;217	contact_email: string;218	/** 1 when the reporter spoke last: the filing, or their newest visible follow-up (§24). */219	awaiting: number;220};221export type OperatorFilter = "open" | "awaiting" | "closed" | "hidden";222223const REPORTER_SPOKE_LAST = `COALESCE((224	SELECT author FROM issue_entries e225	WHERE e.issue_id = issues.id AND e.hidden = 0226	ORDER BY e.id DESC227	LIMIT 1228), 'reporter') = 'reporter'`;229const OPERATOR_COLUMNS = `${PUBLIC_COLUMNS}, repo, hidden, contact_email,230${REPORTER_SPOKE_LAST} AS awaiting`;231const OPERATOR_WHERE: Record<OperatorFilter, string> = {232	open: "hidden = 0 AND status = 'open'",233	awaiting: `hidden = 0 AND status = 'open' AND ${REPORTER_SPOKE_LAST}`,234	closed: "hidden = 0 AND status = 'closed'",235	hidden: "hidden = 1",236};237238/** Every repo's issues under one console tab, private repos' included, newest first. */239export function operatorIssues(db: Database, filter: OperatorFilter): OperatorIssue[] {240	return db241		.query<OperatorIssue, []>(242			`SELECT ${OPERATOR_COLUMNS}243FROM issues244WHERE ${OPERATOR_WHERE[filter]}245ORDER BY filed_at DESC, id DESC`,246		)247		.all();248}249250export function operatorIssue(db: Database, repo: string, number: number): OperatorIssue | null {251	return db252		.query<OperatorIssue, [string, number]>(253			`SELECT ${OPERATOR_COLUMNS}254FROM issues255WHERE repo = ? AND number = ?`,256		)257		.get(repo, number);258}259260/** Closes an issue, optionally "fixed in" a save (§23); reopening clears both. */261export function setClosed(db: Database, id: number, closedAt: number | null, fixedIn = ""): void {262	db.query("UPDATE issues SET status = ?, closed_at = ?, fixed_in = ? WHERE id = ?").run(263		closedAt === null ? "open" : "closed",264		closedAt,265		fixedIn,266		id,267	);268}269270export function setHidden(db: Database, id: number, hidden: boolean): void {271	db.query("UPDATE issues SET hidden = ? WHERE id = ?").run(hidden ? 1 : 0, id);272}273274export function setType(db: Database, id: number, type: IssueType): void {275	db.query("UPDATE issues SET type = ? WHERE id = ?").run(type, id);276}277278// A list of issue ids bound as one JSON parameter, so the statement stays fixed whatever its length.279const IDS = "id IN (SELECT value FROM json_each(?))";280281/** How many of these ids are issues; the console refuses a bulk action naming one that isn't. */282export function countIssues(db: Database, ids: number[]): number {283	return (284		db285			.query<{ n: number }, [string]>(`SELECT COUNT(*) AS n FROM issues WHERE ${IDS}`)286			.get(JSON.stringify(ids))?.n ?? 0287	);288}289290/**291 * Closes or reopens many issues at once (§24). Only those not already so change, so a closed292 * issue keeps its time and "fixed in" save.293 */294export function setManyClosed(db: Database, ids: number[], closedAt: number | null): void {295	db.query(296		`UPDATE issues297		SET status = ?1, closed_at = ?2, fixed_in = ''298		WHERE id IN (SELECT value FROM json_each(?3)) AND status != ?1`,299	).run(closedAt === null ? "open" : "closed", closedAt, JSON.stringify(ids));300}301302export function setManyHidden(db: Database, ids: number[], hidden: boolean): void {303	db.query(`UPDATE issues SET hidden = ? WHERE ${IDS}`).run(hidden ? 1 : 0, JSON.stringify(ids));304}305306export type BlockedReporter = { id: number; repo: string; number: number; blocked_at: number };307308/**309 * Blocks the address an issue was filed from, or one of its replies was posted from (§23); the310 * posts themselves are untouched. Returns the block's id. A post with no address (imported from311 * Kepr 2, or the operator's) blocks nothing and returns null.312 */313export function blockReporter(314	db: Database,315	issueId: number,316	now: number,317	entryId: number | null = null,318): number | null {319	return (320		db321			.query<{ id: number }, [number | null, number, number | null, number]>(322				`INSERT INTO blocked_reporters (ip_hash, repo, number, blocked_at)323				SELECT hash, repo, number, blocked_at FROM (324					SELECT CASE WHEN ?1 IS NULL THEN i.ip_hash ELSE e.ip_hash END AS hash,325					i.repo, i.number, ?2 AS blocked_at326					FROM issues i327					LEFT JOIN issue_entries e ON e.id = ?3 AND e.issue_id = i.id328					WHERE i.id = ?4329				)330				WHERE hash != ''331				ON CONFLICT (ip_hash) DO UPDATE SET ip_hash = excluded.ip_hash332				RETURNING id`,333			)334			.get(entryId, now, entryId, issueId)?.id ?? null335	);336}337338/** Lifts a block; false when there was none with that id. */339export function unblockReporter(db: Database, id: number): boolean {340	return db.query("DELETE FROM blocked_reporters WHERE id = ?").run(id).changes > 0;341}342343export function isBlocked(db: Database, ipHash: string): boolean {344	return db.query("SELECT 1 FROM blocked_reporters WHERE ip_hash = ?").get(ipHash) !== null;345}346347/** Whether the address an issue came from is blocked. */348export function reporterBlocked(db: Database, issueId: number): boolean {349	return (350		db351			.query(352				`SELECT 1353				FROM blocked_reporters b354				JOIN issues i ON i.ip_hash = b.ip_hash355				WHERE i.id = ?`,356			)357			.get(issueId) !== null358	);359}360361export function blockedReporters(db: Database): BlockedReporter[] {362	return db363		.query<BlockedReporter, []>(364			"SELECT id, repo, number, blocked_at FROM blocked_reporters ORDER BY blocked_at DESC, id DESC",365		)366		.all();367}