db.ts

1import { Database } from "bun:sqlite";2import { mkdirSync } from "node:fs";3import { join } from "node:path";45// §19: only what the pages, the sync loop and issues use so far; later phases add their tables.6const SCHEMA = `7CREATE TABLE IF NOT EXISTS settings (8	key   TEXT PRIMARY KEY,9	value TEXT NOT NULL10);11CREATE TABLE IF NOT EXISTS repos (12	name            TEXT PRIMARY KEY,13	public          INTEGER NOT NULL DEFAULT 0,14	category        TEXT NOT NULL DEFAULT '',15	homepage        TEXT NOT NULL DEFAULT '',16	sort_order      INTEGER NOT NULL DEFAULT 0,17	install1        TEXT NOT NULL DEFAULT '',18	install2        TEXT NOT NULL DEFAULT '',19	install3        TEXT NOT NULL DEFAULT '',20	languages       TEXT NOT NULL DEFAULT '',21	issues_paused   INTEGER NOT NULL DEFAULT 0,22	last_sync_at    INTEGER,23	last_sync_error TEXT24);25-- §24: the admin console hides a version or replaces its notes; NULL notes keep the record's.26CREATE TABLE IF NOT EXISTS release_settings (27	repo    TEXT NOT NULL,28	version TEXT NOT NULL,29	hidden  INTEGER NOT NULL DEFAULT 0,30	notes   TEXT,31	PRIMARY KEY (repo, version)32);33-- §22.2: one count per release file; installs are Nix tarball fetches, per repo.34CREATE TABLE IF NOT EXISTS downloads (35	repo     TEXT NOT NULL,36	version  TEXT NOT NULL,37	platform TEXT NOT NULL,38	count    INTEGER NOT NULL DEFAULT 0,39	PRIMARY KEY (repo, version, platform)40);41CREATE TABLE IF NOT EXISTS installs (42	repo  TEXT PRIMARY KEY,43	count INTEGER NOT NULL DEFAULT 044);45-- §23. Numbers are per repo and set explicitly, so Kepr 2's carry over on import. The contact46-- email is the operator's alone; token_hash is the follow-up link's SHA-256 (NULL for imports).47CREATE TABLE IF NOT EXISTS issues (48	id            INTEGER PRIMARY KEY,49	repo          TEXT NOT NULL,50	number        INTEGER NOT NULL,51	title         TEXT NOT NULL,52	body          TEXT NOT NULL,53	type          TEXT NOT NULL CHECK (type IN ('bug', 'idea', 'question')),54	version       TEXT NOT NULL DEFAULT '',55	reporter_name TEXT NOT NULL DEFAULT '',56	contact_email TEXT NOT NULL DEFAULT '',57	filed_at      INTEGER NOT NULL,58	status        TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),59	closed_at     INTEGER,60	fixed_in      TEXT NOT NULL DEFAULT '',61	hidden        INTEGER NOT NULL DEFAULT 0,62	ip_hash       TEXT NOT NULL DEFAULT '',63	token_hash    TEXT,64	UNIQUE (repo, number)65);66CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);67-- The thread after the first post: the reporter's follow-ups and the operator's replies.68CREATE TABLE IF NOT EXISTS issue_entries (69	id         INTEGER PRIMARY KEY,70	issue_id   INTEGER NOT NULL REFERENCES issues (id),71	author     TEXT NOT NULL CHECK (author IN ('reporter', 'operator')),72	body       TEXT NOT NULL,73	created_at INTEGER NOT NULL,74	hidden     INTEGER NOT NULL DEFAULT 075);76CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);77-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.78CREATE TABLE IF NOT EXISTS sessions (79	token_hash TEXT PRIMARY KEY,80	expires_at INTEGER NOT NULL81);82-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.83CREATE TABLE IF NOT EXISTS repo_text (84	repo        TEXT PRIMARY KEY,85	description TEXT NOT NULL DEFAULT '',86	details     TEXT NOT NULL DEFAULT ''87);88CREATE TABLE IF NOT EXISTS connect_codes (89	code_hash   TEXT PRIMARY KEY,90	machine     TEXT NOT NULL,91	created_at  INTEGER NOT NULL,92	expires_at  INTEGER NOT NULL,93	used_at     INTEGER94);95-- §24: what Kepr tells the operator in the console, newest 200 kept.96CREATE TABLE IF NOT EXISTS notifications (97	id         INTEGER PRIMARY KEY,98	kind       TEXT NOT NULL,99	text       TEXT NOT NULL,100	href       TEXT NOT NULL,101	created_at INTEGER NOT NULL,102	read       INTEGER NOT NULL DEFAULT 0103);104-- §24: notifications on their way to the operator's inbox; the address is kepr.toml's.105CREATE TABLE IF NOT EXISTS email_queue (106	id         INTEGER PRIMARY KEY,107	subject    TEXT NOT NULL,108	href       TEXT NOT NULL,109	status     TEXT NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'sent', 'failed')),110	attempts   INTEGER NOT NULL DEFAULT 0,111	next_at    INTEGER NOT NULL,112	last_error TEXT NOT NULL DEFAULT '',113	created_at INTEGER NOT NULL,114	sent_at    INTEGER115);116`;117118/** What raises a notification (§24); each can be switched off. */119export const NOTIFICATION_KINDS = [120	"issue_filed",121	"issue_followup",122	"sync_failed",123	"backup_failed",124] as const;125export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];126const NOTIFICATIONS_KEPT = 200;127128// Columns added after the first kepr.db was made; SQLite has no ADD COLUMN IF NOT EXISTS.129const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [130	["repos", "languages", "TEXT NOT NULL DEFAULT ''"],131	["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],132	["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],133];134135/** `description` and `details` are the operator's Markdown, or '' for the README's. */136export type RepoSettings = {137	name: string;138	category: string;139	homepage: string;140	installLines: string[];141	/** The operator's shown languages (§24), or none for the detected one. */142	languages: string[];143	/** Filing is paused (§23); the list, threads and follow-ups carry on. */144	issuesPaused: boolean;145	description: string;146	details: string;147};148149export type RepoText = "description" | "details";150151type RepoRow = {152	name: string;153	category: string;154	homepage: string;155	install1: string;156	install2: string;157	install3: string;158	languages: string;159	issues_paused: number;160	description: string | null;161	details: string | null;162};163164export function openKeprDb(dataDir: string): Database {165	mkdirSync(dataDir, { recursive: true });166	const db = new Database(join(dataDir, "kepr.db"), { strict: true });167	db.run("PRAGMA journal_mode = WAL");168	db.run("PRAGMA busy_timeout = 5000");169	db.run(SCHEMA);170	for (const [table, column, definition] of ADDED_COLUMNS) {171		// Both names come from the list above, never from input.172		const columns = db.query<{ name: string }, []>(`PRAGMA table_info(${table})`).all();173		if (!columns.some((existing) => existing.name === column)) {174			db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);175		}176	}177	return db;178}179180export function setting(db: Database, key: string): string | null {181	const row = db182		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")183		.get(key);184	return row?.value ?? null;185}186187/** Every repo starts private (§20); registering a known repo changes nothing. */188export function registerRepo(db: Database, name: string): void {189	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);190}191192/**193 * Tells the operator, unless that kind is switched off, and queues an email when that kind is194 * emailed. The newest 200 of each are kept.195 */196export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {197	const now = Math.floor(Date.now() / 1000);198	db.transaction(() => {199		if (!setting(db, `notify_off_${kind}`)) {200			db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(201				kind,202				text,203				href,204				now,205			);206		}207		if (setting(db, `email_on_${kind}`)) {208			db.query(209				"INSERT INTO email_queue (subject, href, next_at, created_at) VALUES (?, ?, ?, ?)",210			).run(text, href, now, now);211		}212		for (const table of ["notifications", "email_queue"]) {213			// `table` is one of the two names above, never input.214			db.query(215				`DELETE FROM ${table}216				WHERE id NOT IN (SELECT id FROM ${table} ORDER BY id DESC LIMIT ?)`,217			).run(NOTIFICATIONS_KEPT);218		}219	})();220}221222/** Records a sync's outcome; a repo that starts failing tells the operator, once. */223export function recordSync(db: Database, name: string, at: number, error: string | null): void {224	const before = db225		.query<{ last_sync_error: string | null }, [string]>(226			"SELECT last_sync_error FROM repos WHERE name = ?",227		)228		.get(name);229	if (error && !before?.last_sync_error) {230		notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);231	}232	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(233		at,234		error,235		name,236	);237}238239export function publicRepoNames(db: Database): string[] {240	return db241		.query<{ name: string }, []>(242			"SELECT name FROM repos WHERE public = 1 ORDER BY sort_order, name",243		)244		.all()245		.map((row) => row.name);246}247248/** The settings of a public repo, or null when it is private or unknown: both must look missing. */249export function publicRepo(db: Database, name: string): RepoSettings | null {250	const row = db251		.query<RepoRow, [string]>(252			`SELECT name, category, homepage, install1, install2, install3, languages, issues_paused,253			description, details254			FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name255			WHERE name = ? AND public = 1`,256		)257		.get(name);258	if (!row) return null;259	const installLines = [row.install1, row.install2, row.install3].filter((line) => line !== "");260	return {261		name: row.name,262		category: row.category,263		homepage: row.homepage,264		installLines,265		languages: row.languages === "" ? [] : row.languages.split(", "),266		issuesPaused: row.issues_paused === 1,267		description: row.description ?? "",268		details: row.details ?? "",269	};270}271272/** A repo's own texts, public or not; '' where the README speaks. */273export function repoText(db: Database, repo: string): Record<RepoText, string> {274	return (275		db276			.query<Record<RepoText, string>, [string]>(277				"SELECT description, details FROM repo_text WHERE repo = ?",278			)279			.get(repo) ?? { description: "", details: "" }280	);281}282283/** Sets one of a repo's texts; '' hands it back to the README. */284export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {285	db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);286	// `field` is one of two column names, never input.287	db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);288}