db.ts

1import { Database } from "bun:sqlite";2import { mkdirSync } from "node:fs";3import { join } from "node:path";4import { ANY_DEPTH } from "../mirror/store.ts";56// §19: only what the pages, the sync loop and issues use so far; later phases add their tables.7const SCHEMA = `8CREATE TABLE IF NOT EXISTS settings (9	key   TEXT PRIMARY KEY,10	value TEXT NOT NULL11);12CREATE TABLE IF NOT EXISTS repos (13	name            TEXT PRIMARY KEY,14	public          INTEGER NOT NULL DEFAULT 0,15	category        TEXT NOT NULL DEFAULT '',16	homepage        TEXT NOT NULL DEFAULT '',17	languages       TEXT NOT NULL DEFAULT '',18	issues_paused   INTEGER NOT NULL DEFAULT 0,19	hidden_paths    TEXT NOT NULL DEFAULT '',20	-- The hidden list last written to .kepr/hidden/<repo>; NULL while unknown.21	hidden_handed   TEXT,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);41-- §24's Downloads: the same downloads per repo and UTC day (YYYY-MM-DD), the newest 400 days.42CREATE TABLE IF NOT EXISTS download_days (43	repo  TEXT NOT NULL,44	day   TEXT NOT NULL,45	count INTEGER NOT NULL DEFAULT 0,46	PRIMARY KEY (repo, day)47);48CREATE TABLE IF NOT EXISTS installs (49	repo  TEXT PRIMARY KEY,50	count INTEGER NOT NULL DEFAULT 051);52-- §23. Numbers are per repo and set explicitly, so Kepr 2's carry over on import. The contact53-- email is the operator's alone; token_hash is the follow-up link's SHA-256 (NULL for imports).54CREATE TABLE IF NOT EXISTS issues (55	id            INTEGER PRIMARY KEY,56	repo          TEXT NOT NULL,57	number        INTEGER NOT NULL,58	title         TEXT NOT NULL,59	body          TEXT NOT NULL,60	type          TEXT NOT NULL CHECK (type IN ('bug', 'idea', 'question')),61	version       TEXT NOT NULL DEFAULT '',62	reporter_name TEXT NOT NULL DEFAULT '',63	contact_email TEXT NOT NULL DEFAULT '',64	filed_at      INTEGER NOT NULL,65	status        TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),66	closed_at     INTEGER,67	fixed_in      TEXT NOT NULL DEFAULT '',68	hidden        INTEGER NOT NULL DEFAULT 0,69	ip_hash       TEXT NOT NULL DEFAULT '',70	token_hash    TEXT,71	UNIQUE (repo, number)72);73CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);74-- The thread after the first post: the reporter's follow-ups and the operator's replies.75-- §23: addresses blocked from filing and following up, by the hash the rate limit keeps, with the76-- issue they were blocked from.77CREATE TABLE IF NOT EXISTS blocked_reporters (78	id         INTEGER PRIMARY KEY,79	ip_hash    TEXT NOT NULL UNIQUE,80	repo       TEXT NOT NULL,81	number     INTEGER NOT NULL,82	blocked_at INTEGER NOT NULL83);84CREATE TABLE IF NOT EXISTS issue_entries (85	id         INTEGER PRIMARY KEY,86	issue_id   INTEGER NOT NULL REFERENCES issues (id),87	author     TEXT NOT NULL CHECK (author IN ('reporter', 'operator')),88	body       TEXT NOT NULL,89	created_at INTEGER NOT NULL,90	hidden     INTEGER NOT NULL DEFAULT 091);92CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);93-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.94CREATE TABLE IF NOT EXISTS sessions (95	token_hash TEXT PRIMARY KEY,96	expires_at INTEGER NOT NULL97);98-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.99CREATE TABLE IF NOT EXISTS repo_text (100	repo        TEXT PRIMARY KEY,101	description TEXT NOT NULL DEFAULT '',102	details     TEXT NOT NULL DEFAULT ''103);104CREATE TABLE IF NOT EXISTS connect_codes (105	code_hash   TEXT PRIMARY KEY,106	machine     TEXT NOT NULL,107	created_at  INTEGER NOT NULL,108	expires_at  INTEGER NOT NULL,109	used_at     INTEGER110);111-- §24: what Kepr tells the operator in the console, newest 200 kept.112CREATE TABLE IF NOT EXISTS notifications (113	id         INTEGER PRIMARY KEY,114	kind       TEXT NOT NULL,115	text       TEXT NOT NULL,116	href       TEXT NOT NULL,117	created_at INTEGER NOT NULL,118	read       INTEGER NOT NULL DEFAULT 0119);120-- §24: notifications on their way to the operator's inbox; the address is kepr.toml's.121CREATE TABLE IF NOT EXISTS email_queue (122	id         INTEGER PRIMARY KEY,123	subject    TEXT NOT NULL,124	href       TEXT NOT NULL,125	status     TEXT NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'sent', 'failed')),126	attempts   INTEGER NOT NULL DEFAULT 0,127	next_at    INTEGER NOT NULL,128	last_error TEXT NOT NULL DEFAULT '',129	created_at INTEGER NOT NULL,130	sent_at    INTEGER131);132-- A repo renamed from the console (June, 2026-10-01): its old pages redirect to the new name.133CREATE TABLE IF NOT EXISTS renamed (134	old TEXT PRIMARY KEY,135	new TEXT NOT NULL136);137`;138139/** What raises a notification (§24); each can be switched off. */140export const NOTIFICATION_KINDS = [141	"issue_filed",142	"issue_followup",143	"sync_failed",144	"backup_failed",145] as const;146export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];147const NOTIFICATIONS_KEPT = 200;148149// Columns added after the first kepr.db was made; SQLite has no ADD COLUMN IF NOT EXISTS.150const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [151	["repos", "languages", "TEXT NOT NULL DEFAULT ''"],152	["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],153	["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],154	["repos", "hidden_paths", "TEXT NOT NULL DEFAULT ''"],155	["repos", "hidden_handed", "TEXT"],156];157// Columns an older kepr.db still has; card order and install lines were removed (June, 2026-09-30).158const DROPPED_COLUMNS: [table: string, column: string][] = [159	["repos", "sort_order"],160	["repos", "install1"],161	["repos", "install2"],162	["repos", "install3"],163];164165/** `description` and `details` are the operator's Markdown, or '' for the README's. */166export type RepoSettings = {167	name: string;168	category: string;169	homepage: string;170	/** The operator's shown languages (§24), or none for the detected one. */171	languages: string[];172	/** Filing is paused (§23); the list, threads and follow-ups carry on. */173	issuesPaused: boolean;174	description: string;175	details: string;176};177178export type RepoText = "description" | "details";179180type RepoRow = {181	name: string;182	category: string;183	homepage: string;184	languages: string;185	issues_paused: number;186	description: string | null;187	details: string | null;188};189190export function openKeprDb(dataDir: string): Database {191	mkdirSync(dataDir, { recursive: true });192	const db = new Database(join(dataDir, "kepr.db"), { strict: true });193	db.run("PRAGMA journal_mode = WAL");194	db.run("PRAGMA busy_timeout = 5000");195	db.run(SCHEMA);196	// Every name below comes from the two lists above, never from input.197	const has = (table: string, column: string) =>198		db199			.query<{ name: string }, []>(`PRAGMA table_info(${table})`)200			.all()201			.some((existing) => existing.name === column);202	for (const [table, column, definition] of ADDED_COLUMNS) {203		if (!has(table, column)) db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);204	}205	for (const [table, column] of DROPPED_COLUMNS) {206		if (has(table, column)) db.run(`ALTER TABLE ${table} DROP COLUMN ${column}`);207	}208	return db;209}210211export function setting(db: Database, key: string): string | null {212	const row = db213		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")214		.get(key);215	return row?.value ?? null;216}217218/** The name a repo was renamed to, following later renames, or null. */219export function renamedTo(db: Database, name: string): string | null {220	const query = db.query<{ new: string }, [string]>("SELECT new FROM renamed WHERE old = ?");221	let to: string | null = null;222	for (let row = query.get(name), hops = 0; row && hops < 5; row = query.get(row.new), hops++) {223		to = row.new;224	}225	return to;226}227228/** Every repo starts private (§20); registering a known repo changes nothing. */229export function registerRepo(db: Database, name: string): void {230	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);231}232233/**234 * Tells the operator, unless that kind is switched off, and queues an email when that kind is235 * emailed. The newest 200 of each are kept.236 */237export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {238	const now = Math.floor(Date.now() / 1000);239	db.transaction(() => {240		if (!setting(db, `notify_off_${kind}`)) {241			db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(242				kind,243				text,244				href,245				now,246			);247		}248		if (setting(db, `email_on_${kind}`)) {249			db.query(250				"INSERT INTO email_queue (subject, href, next_at, created_at) VALUES (?, ?, ?, ?)",251			).run(text, href, now, now);252		}253		for (const table of ["notifications", "email_queue"]) {254			// `table` is one of the two names above, never input.255			db.query(256				`DELETE FROM ${table}257				WHERE id NOT IN (SELECT id FROM ${table} ORDER BY id DESC LIMIT ?)`,258			).run(NOTIFICATIONS_KEPT);259		}260	})();261}262263/** Records a sync's outcome; a repo that starts failing tells the operator, once. */264export function recordSync(db: Database, name: string, at: number, error: string | null): void {265	const before = db266		.query<{ last_sync_error: string | null }, [string]>(267			"SELECT last_sync_error FROM repos WHERE name = ?",268		)269		.get(name);270	if (error && !before?.last_sync_error) {271		notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);272	}273	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(274		at,275		error,276		name,277	);278}279280export function publicRepoNames(db: Database): string[] {281	return db282		.query<{ name: string }, []>("SELECT name FROM repos WHERE public = 1 ORDER BY name")283		.all()284		.map((row) => row.name);285}286287/** The settings of a public repo, or null when it is private or unknown: both must look missing. */288export function publicRepo(db: Database, name: string): RepoSettings | null {289	const row = db290		.query<RepoRow, [string]>(291			`SELECT name, category, homepage, languages, issues_paused, description, details292			FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name293			WHERE name = ? AND public = 1`,294		)295		.get(name);296	if (!row) return null;297	return {298		name: row.name,299		category: row.category,300		homepage: row.homepage,301		languages: row.languages === "" ? [] : row.languages.split(", "),302		issuesPaused: row.issues_paused === 1,303		description: row.description ?? "",304		details: row.details ?? "",305	};306}307308/**309 * What a sync removes from a repo: the paths the operator hid from it (June, 2026-09-30) and the310 * names ignored in every repo (June, 2026-10-01), both one per line in kepr.db.311 */312export function hiddenPaths(db: Database, repo: string): string[] {313	const row = db314		.query<{ hidden_paths: string }, [string]>("SELECT hidden_paths FROM repos WHERE name = ?")315		.get(repo);316	const paths = row?.hidden_paths ? row.hidden_paths.split("\n") : [];317	return [...paths, ...ignoredNames(db).map((name) => `${ANY_DEPTH}${name}`)];318}319320/** Keeps a piece of Kepr's own state in settings, never shown as a setting; null removes it. */321export function keepState(db: Database, key: string, value: string | null): void {322	if (value === null) db.query("DELETE FROM settings WHERE key = ?").run(key);323	else db.query("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)").run(key, value);324}325326/** A repo's hidden paths unless they are known to be the list last handed to Koh, else null. */327export function hiddenToHand(db: Database, repo: string): string | null {328	const row = db329		.query<{ hidden_paths: string }, [string]>(330			`SELECT hidden_paths FROM repos331			WHERE name = ?332			AND (hidden_handed IS NULL OR hidden_paths != hidden_handed)`,333		)334		.get(repo);335	return row?.hidden_paths ?? null;336}337338/** Records the list in the bucket, or null while a write is under way and its outcome unknown. */339export function recordHiddenHanded(db: Database, repo: string, list: string | null): void {340	db.query("UPDATE repos SET hidden_handed = ? WHERE name = ?").run(list, repo);341}342343/** Names of files and folders hidden at any depth in every repo. */344export function ignoredNames(db: Database): string[] {345	return setting(db, "ignored")?.split("\n").filter(Boolean) ?? [];346}347348/** A repo's own texts, public or not; '' where the README speaks. */349export function repoText(db: Database, repo: string): Record<RepoText, string> {350	return (351		db352			.query<Record<RepoText, string>, [string]>(353				"SELECT description, details FROM repo_text WHERE repo = ?",354			)355			.get(repo) ?? { description: "", details: "" }356	);357}358359/** Sets one of a repo's texts; '' hands it back to the README. */360export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {361	db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);362	// `field` is one of two column names, never input.363	db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);364}