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	languages       TEXT NOT NULL DEFAULT '',17	issues_paused   INTEGER NOT NULL DEFAULT 0,18	hidden_paths    TEXT NOT NULL DEFAULT '',19	last_sync_at    INTEGER,20	last_sync_error TEXT21);22-- §24: the admin console hides a version or replaces its notes; NULL notes keep the record's.23CREATE TABLE IF NOT EXISTS release_settings (24	repo    TEXT NOT NULL,25	version TEXT NOT NULL,26	hidden  INTEGER NOT NULL DEFAULT 0,27	notes   TEXT,28	PRIMARY KEY (repo, version)29);30-- §22.2: one count per release file; installs are Nix tarball fetches, per repo.31CREATE TABLE IF NOT EXISTS downloads (32	repo     TEXT NOT NULL,33	version  TEXT NOT NULL,34	platform TEXT NOT NULL,35	count    INTEGER NOT NULL DEFAULT 0,36	PRIMARY KEY (repo, version, platform)37);38-- §24's Downloads: the same downloads per repo and UTC day (YYYY-MM-DD), the newest 400 days.39CREATE TABLE IF NOT EXISTS download_days (40	repo  TEXT NOT NULL,41	day   TEXT NOT NULL,42	count INTEGER NOT NULL DEFAULT 0,43	PRIMARY KEY (repo, day)44);45CREATE TABLE IF NOT EXISTS installs (46	repo  TEXT PRIMARY KEY,47	count INTEGER NOT NULL DEFAULT 048);49-- §23. Numbers are per repo and set explicitly, so Kepr 2's carry over on import. The contact50-- email is the operator's alone; token_hash is the follow-up link's SHA-256 (NULL for imports).51CREATE TABLE IF NOT EXISTS issues (52	id            INTEGER PRIMARY KEY,53	repo          TEXT NOT NULL,54	number        INTEGER NOT NULL,55	title         TEXT NOT NULL,56	body          TEXT NOT NULL,57	type          TEXT NOT NULL CHECK (type IN ('bug', 'idea', 'question')),58	version       TEXT NOT NULL DEFAULT '',59	reporter_name TEXT NOT NULL DEFAULT '',60	contact_email TEXT NOT NULL DEFAULT '',61	filed_at      INTEGER NOT NULL,62	status        TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),63	closed_at     INTEGER,64	fixed_in      TEXT NOT NULL DEFAULT '',65	hidden        INTEGER NOT NULL DEFAULT 0,66	ip_hash       TEXT NOT NULL DEFAULT '',67	token_hash    TEXT,68	UNIQUE (repo, number)69);70CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);71-- The thread after the first post: the reporter's follow-ups and the operator's replies.72CREATE TABLE IF NOT EXISTS issue_entries (73	id         INTEGER PRIMARY KEY,74	issue_id   INTEGER NOT NULL REFERENCES issues (id),75	author     TEXT NOT NULL CHECK (author IN ('reporter', 'operator')),76	body       TEXT NOT NULL,77	created_at INTEGER NOT NULL,78	hidden     INTEGER NOT NULL DEFAULT 079);80CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);81-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.82CREATE TABLE IF NOT EXISTS sessions (83	token_hash TEXT PRIMARY KEY,84	expires_at INTEGER NOT NULL85);86-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.87CREATE TABLE IF NOT EXISTS repo_text (88	repo        TEXT PRIMARY KEY,89	description TEXT NOT NULL DEFAULT '',90	details     TEXT NOT NULL DEFAULT ''91);92CREATE TABLE IF NOT EXISTS connect_codes (93	code_hash   TEXT PRIMARY KEY,94	machine     TEXT NOT NULL,95	created_at  INTEGER NOT NULL,96	expires_at  INTEGER NOT NULL,97	used_at     INTEGER98);99-- §24: what Kepr tells the operator in the console, newest 200 kept.100CREATE TABLE IF NOT EXISTS notifications (101	id         INTEGER PRIMARY KEY,102	kind       TEXT NOT NULL,103	text       TEXT NOT NULL,104	href       TEXT NOT NULL,105	created_at INTEGER NOT NULL,106	read       INTEGER NOT NULL DEFAULT 0107);108-- §24: notifications on their way to the operator's inbox; the address is kepr.toml's.109CREATE TABLE IF NOT EXISTS email_queue (110	id         INTEGER PRIMARY KEY,111	subject    TEXT NOT NULL,112	href       TEXT NOT NULL,113	status     TEXT NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'sent', 'failed')),114	attempts   INTEGER NOT NULL DEFAULT 0,115	next_at    INTEGER NOT NULL,116	last_error TEXT NOT NULL DEFAULT '',117	created_at INTEGER NOT NULL,118	sent_at    INTEGER119);120`;121122/** What raises a notification (§24); each can be switched off. */123export const NOTIFICATION_KINDS = [124	"issue_filed",125	"issue_followup",126	"sync_failed",127	"backup_failed",128] as const;129export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];130const NOTIFICATIONS_KEPT = 200;131132// Columns added after the first kepr.db was made; SQLite has no ADD COLUMN IF NOT EXISTS.133const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [134	["repos", "languages", "TEXT NOT NULL DEFAULT ''"],135	["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],136	["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],137	["repos", "hidden_paths", "TEXT NOT NULL DEFAULT ''"],138];139// Columns an older kepr.db still has; card order and install lines were removed (June, 2026-09-30).140const DROPPED_COLUMNS: [table: string, column: string][] = [141	["repos", "sort_order"],142	["repos", "install1"],143	["repos", "install2"],144	["repos", "install3"],145];146147/** `description` and `details` are the operator's Markdown, or '' for the README's. */148export type RepoSettings = {149	name: string;150	category: string;151	homepage: string;152	/** The operator's shown languages (§24), or none for the detected one. */153	languages: string[];154	/** Filing is paused (§23); the list, threads and follow-ups carry on. */155	issuesPaused: boolean;156	description: string;157	details: string;158};159160export type RepoText = "description" | "details";161162type RepoRow = {163	name: string;164	category: string;165	homepage: string;166	languages: string;167	issues_paused: number;168	description: string | null;169	details: string | null;170};171172export function openKeprDb(dataDir: string): Database {173	mkdirSync(dataDir, { recursive: true });174	const db = new Database(join(dataDir, "kepr.db"), { strict: true });175	db.run("PRAGMA journal_mode = WAL");176	db.run("PRAGMA busy_timeout = 5000");177	db.run(SCHEMA);178	// Every name below comes from the two lists above, never from input.179	const has = (table: string, column: string) =>180		db181			.query<{ name: string }, []>(`PRAGMA table_info(${table})`)182			.all()183			.some((existing) => existing.name === column);184	for (const [table, column, definition] of ADDED_COLUMNS) {185		if (!has(table, column)) db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);186	}187	for (const [table, column] of DROPPED_COLUMNS) {188		if (has(table, column)) db.run(`ALTER TABLE ${table} DROP COLUMN ${column}`);189	}190	return db;191}192193export function setting(db: Database, key: string): string | null {194	const row = db195		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")196		.get(key);197	return row?.value ?? null;198}199200/** Every repo starts private (§20); registering a known repo changes nothing. */201export function registerRepo(db: Database, name: string): void {202	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);203}204205/**206 * Tells the operator, unless that kind is switched off, and queues an email when that kind is207 * emailed. The newest 200 of each are kept.208 */209export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {210	const now = Math.floor(Date.now() / 1000);211	db.transaction(() => {212		if (!setting(db, `notify_off_${kind}`)) {213			db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(214				kind,215				text,216				href,217				now,218			);219		}220		if (setting(db, `email_on_${kind}`)) {221			db.query(222				"INSERT INTO email_queue (subject, href, next_at, created_at) VALUES (?, ?, ?, ?)",223			).run(text, href, now, now);224		}225		for (const table of ["notifications", "email_queue"]) {226			// `table` is one of the two names above, never input.227			db.query(228				`DELETE FROM ${table}229				WHERE id NOT IN (SELECT id FROM ${table} ORDER BY id DESC LIMIT ?)`,230			).run(NOTIFICATIONS_KEPT);231		}232	})();233}234235/** Records a sync's outcome; a repo that starts failing tells the operator, once. */236export function recordSync(db: Database, name: string, at: number, error: string | null): void {237	const before = db238		.query<{ last_sync_error: string | null }, [string]>(239			"SELECT last_sync_error FROM repos WHERE name = ?",240		)241		.get(name);242	if (error && !before?.last_sync_error) {243		notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);244	}245	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(246		at,247		error,248		name,249	);250}251252export function publicRepoNames(db: Database): string[] {253	return db254		.query<{ name: string }, []>("SELECT name FROM repos WHERE public = 1 ORDER BY name")255		.all()256		.map((row) => row.name);257}258259/** The settings of a public repo, or null when it is private or unknown: both must look missing. */260export function publicRepo(db: Database, name: string): RepoSettings | null {261	const row = db262		.query<RepoRow, [string]>(263			`SELECT name, category, homepage, languages, issues_paused, description, details264			FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name265			WHERE name = ? AND public = 1`,266		)267		.get(name);268	if (!row) return null;269	return {270		name: row.name,271		category: row.category,272		homepage: row.homepage,273		languages: row.languages === "" ? [] : row.languages.split(", "),274		issuesPaused: row.issues_paused === 1,275		description: row.description ?? "",276		details: row.details ?? "",277	};278}279280/** The paths the operator hid from a repo (June, 2026-09-30), one per line in kepr.db. */281export function hiddenPaths(db: Database, repo: string): string[] {282	const row = db283		.query<{ hidden_paths: string }, [string]>("SELECT hidden_paths FROM repos WHERE name = ?")284		.get(repo);285	return row?.hidden_paths ? row.hidden_paths.split("\n") : [];286}287288/** A repo's own texts, public or not; '' where the README speaks. */289export function repoText(db: Database, repo: string): Record<RepoText, string> {290	return (291		db292			.query<Record<RepoText, string>, [string]>(293				"SELECT description, details FROM repo_text WHERE repo = ?",294			)295			.get(repo) ?? { description: "", details: "" }296	);297}298299/** Sets one of a repo's texts; '' hands it back to the README. */300export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {301	db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);302	// `field` is one of two column names, never input.303	db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);304}