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	last_sync_at    INTEGER,19	last_sync_error TEXT20);21-- §24: the admin console hides a version or replaces its notes; NULL notes keep the record's.22CREATE TABLE IF NOT EXISTS release_settings (23	repo    TEXT NOT NULL,24	version TEXT NOT NULL,25	hidden  INTEGER NOT NULL DEFAULT 0,26	notes   TEXT,27	PRIMARY KEY (repo, version)28);29-- §22.2: one count per release file; installs are Nix tarball fetches, per repo.30CREATE TABLE IF NOT EXISTS downloads (31	repo     TEXT NOT NULL,32	version  TEXT NOT NULL,33	platform TEXT NOT NULL,34	count    INTEGER NOT NULL DEFAULT 0,35	PRIMARY KEY (repo, version, platform)36);37CREATE TABLE IF NOT EXISTS installs (38	repo  TEXT PRIMARY KEY,39	count INTEGER NOT NULL DEFAULT 040);41-- §23. Numbers are per repo and set explicitly, so Kepr 2's carry over on import. The contact42-- email is the operator's alone; token_hash is the follow-up link's SHA-256 (NULL for imports).43CREATE TABLE IF NOT EXISTS issues (44	id            INTEGER PRIMARY KEY,45	repo          TEXT NOT NULL,46	number        INTEGER NOT NULL,47	title         TEXT NOT NULL,48	body          TEXT NOT NULL,49	type          TEXT NOT NULL CHECK (type IN ('bug', 'idea', 'question')),50	version       TEXT NOT NULL DEFAULT '',51	reporter_name TEXT NOT NULL DEFAULT '',52	contact_email TEXT NOT NULL DEFAULT '',53	filed_at      INTEGER NOT NULL,54	status        TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),55	closed_at     INTEGER,56	fixed_in      TEXT NOT NULL DEFAULT '',57	hidden        INTEGER NOT NULL DEFAULT 0,58	ip_hash       TEXT NOT NULL DEFAULT '',59	token_hash    TEXT,60	UNIQUE (repo, number)61);62CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);63-- The thread after the first post: the reporter's follow-ups and the operator's replies.64CREATE TABLE IF NOT EXISTS issue_entries (65	id         INTEGER PRIMARY KEY,66	issue_id   INTEGER NOT NULL REFERENCES issues (id),67	author     TEXT NOT NULL CHECK (author IN ('reporter', 'operator')),68	body       TEXT NOT NULL,69	created_at INTEGER NOT NULL,70	hidden     INTEGER NOT NULL DEFAULT 071);72CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);73-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.74CREATE TABLE IF NOT EXISTS sessions (75	token_hash TEXT PRIMARY KEY,76	expires_at INTEGER NOT NULL77);78-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.79CREATE TABLE IF NOT EXISTS repo_text (80	repo        TEXT PRIMARY KEY,81	description TEXT NOT NULL DEFAULT '',82	details     TEXT NOT NULL DEFAULT ''83);84CREATE TABLE IF NOT EXISTS connect_codes (85	code_hash   TEXT PRIMARY KEY,86	machine     TEXT NOT NULL,87	created_at  INTEGER NOT NULL,88	expires_at  INTEGER NOT NULL,89	used_at     INTEGER90);91-- §24: what Kepr tells the operator in the console, newest 200 kept.92CREATE TABLE IF NOT EXISTS notifications (93	id         INTEGER PRIMARY KEY,94	kind       TEXT NOT NULL,95	text       TEXT NOT NULL,96	href       TEXT NOT NULL,97	created_at INTEGER NOT NULL,98	read       INTEGER NOT NULL DEFAULT 099);100-- §24: notifications on their way to the operator's inbox; the address is kepr.toml's.101CREATE TABLE IF NOT EXISTS email_queue (102	id         INTEGER PRIMARY KEY,103	subject    TEXT NOT NULL,104	href       TEXT NOT NULL,105	status     TEXT NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'sent', 'failed')),106	attempts   INTEGER NOT NULL DEFAULT 0,107	next_at    INTEGER NOT NULL,108	last_error TEXT NOT NULL DEFAULT '',109	created_at INTEGER NOT NULL,110	sent_at    INTEGER111);112`;113114/** What raises a notification (§24); each can be switched off. */115export const NOTIFICATION_KINDS = [116	"issue_filed",117	"issue_followup",118	"sync_failed",119	"backup_failed",120] as const;121export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];122const NOTIFICATIONS_KEPT = 200;123124// Columns added after the first kepr.db was made; SQLite has no ADD COLUMN IF NOT EXISTS.125const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [126	["repos", "languages", "TEXT NOT NULL DEFAULT ''"],127	["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],128	["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],129];130// Columns an older kepr.db still has; card order and install lines were removed (June, 2026-09-30).131const DROPPED_COLUMNS: [table: string, column: string][] = [132	["repos", "sort_order"],133	["repos", "install1"],134	["repos", "install2"],135	["repos", "install3"],136];137138/** `description` and `details` are the operator's Markdown, or '' for the README's. */139export type RepoSettings = {140	name: string;141	category: string;142	homepage: string;143	/** The operator's shown languages (§24), or none for the detected one. */144	languages: string[];145	/** Filing is paused (§23); the list, threads and follow-ups carry on. */146	issuesPaused: boolean;147	description: string;148	details: string;149};150151export type RepoText = "description" | "details";152153type RepoRow = {154	name: string;155	category: string;156	homepage: string;157	languages: string;158	issues_paused: number;159	description: string | null;160	details: string | null;161};162163export function openKeprDb(dataDir: string): Database {164	mkdirSync(dataDir, { recursive: true });165	const db = new Database(join(dataDir, "kepr.db"), { strict: true });166	db.run("PRAGMA journal_mode = WAL");167	db.run("PRAGMA busy_timeout = 5000");168	db.run(SCHEMA);169	// Every name below comes from the two lists above, never from input.170	const has = (table: string, column: string) =>171		db172			.query<{ name: string }, []>(`PRAGMA table_info(${table})`)173			.all()174			.some((existing) => existing.name === column);175	for (const [table, column, definition] of ADDED_COLUMNS) {176		if (!has(table, column)) db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);177	}178	for (const [table, column] of DROPPED_COLUMNS) {179		if (has(table, column)) db.run(`ALTER TABLE ${table} DROP COLUMN ${column}`);180	}181	return db;182}183184export function setting(db: Database, key: string): string | null {185	const row = db186		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")187		.get(key);188	return row?.value ?? null;189}190191/** Every repo starts private (§20); registering a known repo changes nothing. */192export function registerRepo(db: Database, name: string): void {193	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);194}195196/**197 * Tells the operator, unless that kind is switched off, and queues an email when that kind is198 * emailed. The newest 200 of each are kept.199 */200export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {201	const now = Math.floor(Date.now() / 1000);202	db.transaction(() => {203		if (!setting(db, `notify_off_${kind}`)) {204			db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(205				kind,206				text,207				href,208				now,209			);210		}211		if (setting(db, `email_on_${kind}`)) {212			db.query(213				"INSERT INTO email_queue (subject, href, next_at, created_at) VALUES (?, ?, ?, ?)",214			).run(text, href, now, now);215		}216		for (const table of ["notifications", "email_queue"]) {217			// `table` is one of the two names above, never input.218			db.query(219				`DELETE FROM ${table}220				WHERE id NOT IN (SELECT id FROM ${table} ORDER BY id DESC LIMIT ?)`,221			).run(NOTIFICATIONS_KEPT);222		}223	})();224}225226/** Records a sync's outcome; a repo that starts failing tells the operator, once. */227export function recordSync(db: Database, name: string, at: number, error: string | null): void {228	const before = db229		.query<{ last_sync_error: string | null }, [string]>(230			"SELECT last_sync_error FROM repos WHERE name = ?",231		)232		.get(name);233	if (error && !before?.last_sync_error) {234		notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);235	}236	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(237		at,238		error,239		name,240	);241}242243export function publicRepoNames(db: Database): string[] {244	return db245		.query<{ name: string }, []>("SELECT name FROM repos WHERE public = 1 ORDER BY name")246		.all()247		.map((row) => row.name);248}249250/** The settings of a public repo, or null when it is private or unknown: both must look missing. */251export function publicRepo(db: Database, name: string): RepoSettings | null {252	const row = db253		.query<RepoRow, [string]>(254			`SELECT name, category, homepage, languages, issues_paused, description, details255			FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name256			WHERE name = ? AND public = 1`,257		)258		.get(name);259	if (!row) return null;260	return {261		name: row.name,262		category: row.category,263		homepage: row.homepage,264		languages: row.languages === "" ? [] : row.languages.split(", "),265		issuesPaused: row.issues_paused === 1,266		description: row.description ?? "",267		details: row.details ?? "",268	};269}270271/** A repo's own texts, public or not; '' where the README speaks. */272export function repoText(db: Database, repo: string): Record<RepoText, string> {273	return (274		db275			.query<Record<RepoText, string>, [string]>(276				"SELECT description, details FROM repo_text WHERE repo = ?",277			)278			.get(repo) ?? { description: "", details: "" }279	);280}281282/** Sets one of a repo's texts; '' hands it back to the README. */283export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {284	db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);285	// `field` is one of two column names, never input.286	db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);287}