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 (§23). */59export function fileIssue(db: Database, issue: NewIssue): { id: number; number: 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			const { lastInsertRowid } = db71				.query(72					`INSERT INTO issues (73repo, number, title, body, type, version, reporter_name, contact_email, filed_at, ip_hash74) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?)`,75				)76				.run(77					issue.repo,78					row.next,79					issue.title,80					issue.body,81					issue.type,82					issue.version,83					issue.reporterName,84					issue.contactEmail,85					issue.filedAt,86					issue.ipHash,87				);88			return { id: Number(lastInsertRowid), number: row.next };89		})90		.immediate();91}9293/** Issues filed from one hashed IP since a time, hidden ones included: spam stays counted. */94export function filingsSince(db: Database, ipHash: string, since: number): number {95	const row = db96		.query<{ n: number }, [string, number]>(97			"SELECT COUNT(*) AS n FROM issues WHERE ip_hash = ? AND filed_at > ?",98		)99		.get(ipHash, since);100	return row?.n ?? 0;101}102103/** Replies posted from one hashed IP since a time, hidden ones included. */104export function repliesSince(db: Database, ipHash: string, since: number): number {105	const row = db106		.query<{ n: number }, [string, number]>(107			"SELECT COUNT(*) AS n FROM issue_entries WHERE ip_hash = ? AND created_at > ?",108		)109		.get(ipHash, since);110	return row?.n ?? 0;111}112113/** A repo's visible issues with one status, newest first, each counting its visible replies. */114export function listIssues(db: Database, repo: string, status: "open" | "closed"): ListedIssue[] {115	return db116		.query<ListedIssue, [string, string]>(117			`SELECT ${PUBLIC_COLUMNS}, (118	SELECT COUNT(*) FROM issue_entries e WHERE e.issue_id = issues.id AND e.hidden = 0119) AS replies120FROM issues121WHERE repo = ? AND status = ? AND hidden = 0122ORDER BY number DESC`,123		)124		.all(repo, status);125}126127export function issueCounts(db: Database, repo: string): { open: number; closed: number } {128	const rows = db129		.query<{ status: "open" | "closed"; n: number }, [string]>(130			`SELECT status, COUNT(*) AS n131FROM issues132WHERE repo = ? AND hidden = 0133GROUP BY status`,134		)135		.all(repo);136	const count = (status: string) => rows.find((row) => row.status === status)?.n ?? 0;137	return { open: count("open"), closed: count("closed") };138}139140/** A visible issue by its number; a hidden one is as missing as an unknown one. */141export function findIssue(db: Database, repo: string, number: number): Issue | null {142	return db143		.query<Issue, [string, number]>(144			`SELECT ${PUBLIC_COLUMNS}145FROM issues146WHERE repo = ? AND number = ? AND hidden = 0`,147		)148		.get(repo, number);149}150151/** An issue's visible replies, oldest first. */152export function threadEntries(db: Database, issueId: number): Entry[] {153	return operatorEntries(db, issueId).filter((entry) => !entry.hidden);154}155156/** Every reply in an issue's thread, hidden ones included, for the console. */157export function operatorEntries(db: Database, issueId: number): Entry[] {158	return db159		.query<Entry, [number]>(160			`SELECT id, author, name, body, created_at, hidden, reply_to161FROM issue_entries162WHERE issue_id = ?163ORDER BY id`,164		)165		.all(issueId);166}167168/** Hides or shows one entry of one issue; false when the issue has no such entry. */169export function setEntryHidden(170	db: Database,171	issueId: number,172	entryId: number,173	hidden: boolean,174): boolean {175	return (176		db177			.query("UPDATE issue_entries SET hidden = ? WHERE id = ? AND issue_id = ?")178			.run(hidden ? 1 : 0, entryId, issueId).changes === 1179	);180}181182export function addEntry(db: Database, issueId: number, entry: NewEntry): number {183	return Number(184		db185			.query(186				`INSERT INTO issue_entries (issue_id, author, name, body, created_at, ip_hash, reply_to)187VALUES (?, ?, ?, ?, ?, ?, ?)`,188			)189			.run(190				issueId,191				entry.author,192				entry.name ?? "",193				entry.body,194				entry.at,195				entry.ipHash ?? "",196				entry.replyTo ?? null,197			).lastInsertRowid,198	);199}200201/** Issues anyone can see, across every public repo: the profile's count (§21.2). */202export function publicIssueCount(db: Database): number {203	return (204		db205			.query<{ n: number }, []>(206				`SELECT COUNT(*) AS n207FROM issues i208JOIN repos r ON r.name = i.repo209WHERE r.public = 1 AND i.hidden = 0`,210			)211			.get()?.n ?? 0212	);213}214215/** An issue as the operator sees it (§24): hidden or not, with the reporter's contact detail. */216export type OperatorIssue = Issue & {217	repo: string;218	hidden: number;219	contact_email: string;220	/** 1 when the reporter spoke last: the filing, or their newest visible follow-up (§24). */221	awaiting: number;222};223export type OperatorFilter = "open" | "awaiting" | "closed" | "hidden";224225const REPORTER_SPOKE_LAST = `COALESCE((226	SELECT author FROM issue_entries e227	WHERE e.issue_id = issues.id AND e.hidden = 0228	ORDER BY e.id DESC229	LIMIT 1230), 'reporter') = 'reporter'`;231const OPERATOR_COLUMNS = `${PUBLIC_COLUMNS}, repo, hidden, contact_email,232${REPORTER_SPOKE_LAST} AS awaiting`;233const OPERATOR_WHERE: Record<OperatorFilter, string> = {234	open: "hidden = 0 AND status = 'open'",235	awaiting: `hidden = 0 AND status = 'open' AND ${REPORTER_SPOKE_LAST}`,236	closed: "hidden = 0 AND status = 'closed'",237	hidden: "hidden = 1",238};239240/** Every repo's issues under one console tab, private repos' included, newest first. */241export function operatorIssues(db: Database, filter: OperatorFilter): OperatorIssue[] {242	return db243		.query<OperatorIssue, []>(244			`SELECT ${OPERATOR_COLUMNS}245FROM issues246WHERE ${OPERATOR_WHERE[filter]}247ORDER BY filed_at DESC, id DESC`,248		)249		.all();250}251252export function operatorIssue(db: Database, repo: string, number: number): OperatorIssue | null {253	return db254		.query<OperatorIssue, [string, number]>(255			`SELECT ${OPERATOR_COLUMNS}256FROM issues257WHERE repo = ? AND number = ?`,258		)259		.get(repo, number);260}261262/** Closes an issue, optionally "fixed in" a save (§23); reopening clears both. */263export function setClosed(db: Database, id: number, closedAt: number | null, fixedIn = ""): void {264	db.query("UPDATE issues SET status = ?, closed_at = ?, fixed_in = ? WHERE id = ?").run(265		closedAt === null ? "open" : "closed",266		closedAt,267		fixedIn,268		id,269	);270}271272export function setHidden(db: Database, id: number, hidden: boolean): void {273	db.query("UPDATE issues SET hidden = ? WHERE id = ?").run(hidden ? 1 : 0, id);274}275276export function setType(db: Database, id: number, type: IssueType): void {277	db.query("UPDATE issues SET type = ? WHERE id = ?").run(type, id);278}279280// A list of issue ids bound as one JSON parameter, so the statement stays fixed whatever its length.281const IDS = "id IN (SELECT value FROM json_each(?))";282283/** How many of these ids are issues; the console refuses a bulk action naming one that isn't. */284export function countIssues(db: Database, ids: number[]): number {285	return (286		db287			.query<{ n: number }, [string]>(`SELECT COUNT(*) AS n FROM issues WHERE ${IDS}`)288			.get(JSON.stringify(ids))?.n ?? 0289	);290}291292/**293 * Closes or reopens many issues at once (§24). Only those not already so change, so a closed294 * issue keeps its time and "fixed in" save.295 */296export function setManyClosed(db: Database, ids: number[], closedAt: number | null): void {297	db.query(298		`UPDATE issues299		SET status = ?1, closed_at = ?2, fixed_in = ''300		WHERE id IN (SELECT value FROM json_each(?3)) AND status != ?1`,301	).run(closedAt === null ? "open" : "closed", closedAt, JSON.stringify(ids));302}303304export function setManyHidden(db: Database, ids: number[], hidden: boolean): void {305	db.query(`UPDATE issues SET hidden = ? WHERE ${IDS}`).run(hidden ? 1 : 0, JSON.stringify(ids));306}307308export type BlockedReporter = { id: number; repo: string; number: number; blocked_at: number };309310/**311 * Blocks the address an issue was filed from, or one of its replies was posted from (§23); the312 * posts themselves are untouched. Returns the block's id. A post with no address (imported from313 * Kepr 2, or the operator's) blocks nothing and returns null.314 */315export function blockReporter(316	db: Database,317	issueId: number,318	now: number,319	entryId: number | null = null,320): number | null {321	return (322		db323			.query<{ id: number }, [number | null, number, number | null, number]>(324				`INSERT INTO blocked_reporters (ip_hash, repo, number, blocked_at)325				SELECT hash, repo, number, blocked_at FROM (326					SELECT CASE WHEN ?1 IS NULL THEN i.ip_hash ELSE e.ip_hash END AS hash,327					i.repo, i.number, ?2 AS blocked_at328					FROM issues i329					LEFT JOIN issue_entries e ON e.id = ?3 AND e.issue_id = i.id330					WHERE i.id = ?4331				)332				WHERE hash != ''333				ON CONFLICT (ip_hash) DO UPDATE SET ip_hash = excluded.ip_hash334				RETURNING id`,335			)336			.get(entryId, now, entryId, issueId)?.id ?? null337	);338}339340/** Lifts a block; false when there was none with that id. */341export function unblockReporter(db: Database, id: number): boolean {342	return db.query("DELETE FROM blocked_reporters WHERE id = ?").run(id).changes > 0;343}344345export function isBlocked(db: Database, ipHash: string): boolean {346	return db.query("SELECT 1 FROM blocked_reporters WHERE ip_hash = ?").get(ipHash) !== null;347}348349/** Whether the address an issue came from is blocked. */350export function reporterBlocked(db: Database, issueId: number): boolean {351	return (352		db353			.query(354				`SELECT 1355				FROM blocked_reporters b356				JOIN issues i ON i.ip_hash = b.ip_hash357				WHERE i.id = ?`,358			)359			.get(issueId) !== null360	);361}362363export function blockedReporters(db: Database): BlockedReporter[] {364	return db365		.query<BlockedReporter, []>(366			"SELECT id, repo, number, blocked_at FROM blocked_reporters ORDER BY blocked_at DESC, id DESC",367		)368		.all();369}