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.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	UNIQUE (repo, number)71);72CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);73-- §23: addresses blocked from filing, replying and following, by the hash the rate limit keeps,74-- with the issue they were blocked from.75CREATE TABLE IF NOT EXISTS blocked_reporters (76	id         INTEGER PRIMARY KEY,77	ip_hash    TEXT NOT NULL UNIQUE,78	repo       TEXT NOT NULL,79	number     INTEGER NOT NULL,80	blocked_at INTEGER NOT NULL81);82-- The thread after the first post. 'reporter' is anyone from the public, the issue's own reporter83-- or not, under the name they chose; reply_to is the reply this one answers.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	name       TEXT NOT NULL DEFAULT '',89	body       TEXT NOT NULL,90	created_at INTEGER NOT NULL,91	hidden     INTEGER NOT NULL DEFAULT 0,92	ip_hash    TEXT NOT NULL DEFAULT '',93	reply_to   INTEGER94);95CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);96-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.97CREATE TABLE IF NOT EXISTS sessions (98	token_hash TEXT PRIMARY KEY,99	expires_at INTEGER NOT NULL100);101-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.102CREATE TABLE IF NOT EXISTS repo_text (103	repo        TEXT PRIMARY KEY,104	description TEXT NOT NULL DEFAULT '',105	details     TEXT NOT NULL DEFAULT ''106);107CREATE TABLE IF NOT EXISTS connect_codes (108	code_hash   TEXT PRIMARY KEY,109	machine     TEXT NOT NULL,110	created_at  INTEGER NOT NULL,111	expires_at  INTEGER NOT NULL,112	used_at     INTEGER113);114-- §24: what Kepr tells the operator in the console, newest 200 kept.115CREATE TABLE IF NOT EXISTS notifications (116	id         INTEGER PRIMARY KEY,117	kind       TEXT NOT NULL,118	text       TEXT NOT NULL,119	href       TEXT NOT NULL,120	created_at INTEGER NOT NULL,121	read       INTEGER NOT NULL DEFAULT 0122);123-- §24: notifications on their way to the operator's inbox; the address is kepr.toml's.124CREATE TABLE IF NOT EXISTS email_queue (125	id         INTEGER PRIMARY KEY,126	subject    TEXT NOT NULL,127	href       TEXT NOT NULL,128	status     TEXT NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'sent', 'failed')),129	attempts   INTEGER NOT NULL DEFAULT 0,130	next_at    INTEGER NOT NULL,131	last_error TEXT NOT NULL DEFAULT '',132	created_at INTEGER NOT NULL,133	sent_at    INTEGER134);135-- A repo renamed from the console (June, 2026-10-01): its old pages redirect to the new name.136CREATE TABLE IF NOT EXISTS renamed (137	old TEXT PRIMARY KEY,138	new TEXT NOT NULL139);140`;141142/** What raises a notification (§24); each can be switched off. */143export const NOTIFICATION_KINDS = [144	"issue_filed",145	"issue_followup",146	"sync_failed",147	"backup_failed",148] as const;149export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];150const NOTIFICATIONS_KEPT = 200;151152// Columns added after the first kepr.db was made; SQLite has no ADD COLUMN IF NOT EXISTS.153const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [154	["repos", "languages", "TEXT NOT NULL DEFAULT ''"],155	["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],156	["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],157	["issue_entries", "name", "TEXT NOT NULL DEFAULT ''"],158	["issue_entries", "ip_hash", "TEXT NOT NULL DEFAULT ''"],159	["issue_entries", "reply_to", "INTEGER"],160	["repos", "hidden_paths", "TEXT NOT NULL DEFAULT ''"],161	["repos", "hidden_handed", "TEXT"],162];163// Columns an older kepr.db still has; card order and install lines were removed (June, 2026-09-30).164const DROPPED_COLUMNS: [table: string, column: string][] = [165	["repos", "sort_order"],166	["repos", "install1"],167	["repos", "install2"],168	["repos", "install3"],169	// The private follow-up link, replaced by public replies (June, 2026-10-08).170	["issues", "token_hash"],171];172173/** `description` and `details` are the operator's Markdown, or '' for the README's. */174export type RepoSettings = {175	name: string;176	category: string;177	homepage: string;178	/** The operator's shown languages (§24), or none for the detected one. */179	languages: string[];180	/** Filing is paused (§23); the list, threads and follow-ups carry on. */181	issuesPaused: boolean;182	description: string;183	details: string;184};185186export type RepoText = "description" | "details";187188type RepoRow = {189	name: string;190	category: string;191	homepage: string;192	languages: string;193	issues_paused: number;194	description: string | null;195	details: string | null;196};197198export function openKeprDb(dataDir: string): Database {199	mkdirSync(dataDir, { recursive: true });200	const db = new Database(join(dataDir, "kepr.db"), { strict: true });201	db.run("PRAGMA journal_mode = WAL");202	db.run("PRAGMA busy_timeout = 5000");203	db.run(SCHEMA);204	// Every name below comes from the two lists above, never from input.205	const has = (table: string, column: string) =>206		db207			.query<{ name: string }, []>(`PRAGMA table_info(${table})`)208			.all()209			.some((existing) => existing.name === column);210	const named = has("issue_entries", "name");211	for (const [table, column, definition] of ADDED_COLUMNS) {212		if (!has(table, column)) db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);213	}214	// Follow-ups came only from an issue's reporter, so they keep the name the issue shows.215	if (!named) {216		db.run(`UPDATE issue_entries SET name = (217	SELECT reporter_name FROM issues WHERE issues.id = issue_entries.issue_id218) WHERE author = 'reporter'`);219	}220	for (const [table, column] of DROPPED_COLUMNS) {221		if (has(table, column)) db.run(`ALTER TABLE ${table} DROP COLUMN ${column}`);222	}223	db.run("CREATE INDEX IF NOT EXISTS issue_entries_ip ON issue_entries (ip_hash, created_at)");224	return db;225}226227export function setting(db: Database, key: string): string | null {228	const row = db229		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")230		.get(key);231	return row?.value ?? null;232}233234/** The name a repo was renamed to, following later renames, or null. */235export function renamedTo(db: Database, name: string): string | null {236	const query = db.query<{ new: string }, [string]>("SELECT new FROM renamed WHERE old = ?");237	let to: string | null = null;238	for (let row = query.get(name), hops = 0; row && hops < 5; row = query.get(row.new), hops++) {239		to = row.new;240	}241	return to;242}243244export function isRegistered(db: Database, name: string): boolean {245	return db.query("SELECT 1 FROM repos WHERE name = ?").get(name) !== null;246}247248/** Every repo starts private (§20); registering a known repo changes nothing. */249export function registerRepo(db: Database, name: string): void {250	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);251}252253/**254 * Tells the operator, unless that kind is switched off, and queues an email when that kind is255 * emailed. The newest 200 of each are kept.256 */257export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {258	const now = Math.floor(Date.now() / 1000);259	// Immediate: it reads before writing, and the sync's thread writes kepr.db too.260	db.transaction(() => {261		if (!setting(db, `notify_off_${kind}`)) {262			db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(263				kind,264				text,265				href,266				now,267			);268		}269		if (setting(db, `email_on_${kind}`)) {270			db.query(271				"INSERT INTO email_queue (subject, href, next_at, created_at) VALUES (?, ?, ?, ?)",272			).run(text, href, now, now);273		}274		for (const table of ["notifications", "email_queue"]) {275			// `table` is one of the two names above, never input.276			db.query(277				`DELETE FROM ${table}278				WHERE id NOT IN (SELECT id FROM ${table} ORDER BY id DESC LIMIT ?)`,279			).run(NOTIFICATIONS_KEPT);280		}281	}).immediate();282}283284/** Records a sync's outcome; a repo that starts failing tells the operator, once. */285export function recordSync(db: Database, name: string, at: number, error: string | null): void {286	const before = db287		.query<{ last_sync_error: string | null }, [string]>(288			"SELECT last_sync_error FROM repos WHERE name = ?",289		)290		.get(name);291	if (error && !before?.last_sync_error) {292		notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);293	}294	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(295		at,296		error,297		name,298	);299}300301export function publicRepoNames(db: Database): string[] {302	return db303		.query<{ name: string }, []>("SELECT name FROM repos WHERE public = 1 ORDER BY name")304		.all()305		.map((row) => row.name);306}307308/** The settings of a public repo, or null when it is private or unknown: both must look missing. */309export function publicRepo(db: Database, name: string): RepoSettings | null {310	const row = db311		.query<RepoRow, [string]>(312			`SELECT name, category, homepage, languages, issues_paused, description, details313			FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name314			WHERE name = ? AND public = 1`,315		)316		.get(name);317	if (!row) return null;318	return {319		name: row.name,320		category: row.category,321		homepage: row.homepage,322		languages: row.languages === "" ? [] : row.languages.split(", "),323		issuesPaused: row.issues_paused === 1,324		description: row.description ?? "",325		details: row.details ?? "",326	};327}328329/**330 * What a sync removes from a repo: the paths the operator hid from it (June, 2026-09-30) and the331 * names ignored in every repo (June, 2026-10-01), both one per line in kepr.db.332 */333export function hiddenPaths(db: Database, repo: string): string[] {334	const row = db335		.query<{ hidden_paths: string }, [string]>("SELECT hidden_paths FROM repos WHERE name = ?")336		.get(repo);337	const paths = row?.hidden_paths ? row.hidden_paths.split("\n") : [];338	return [...paths, ...ignoredNames(db).map((name) => `${ANY_DEPTH}${name}`)];339}340341/** Keeps a piece of Kepr's own state in settings, never shown as a setting; null removes it. */342export function keepState(db: Database, key: string, value: string | null): void {343	if (value === null) db.query("DELETE FROM settings WHERE key = ?").run(key);344	else db.query("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)").run(key, value);345}346347/** A repo's hidden paths unless they are known to be the list last handed to Koh, else null. */348export function hiddenToHand(db: Database, repo: string): string | null {349	const row = db350		.query<{ hidden_paths: string }, [string]>(351			`SELECT hidden_paths FROM repos352			WHERE name = ?353			AND (hidden_handed IS NULL OR hidden_paths != hidden_handed)`,354		)355		.get(repo);356	return row?.hidden_paths ?? null;357}358359/** Records the list in the bucket, or null while a write is under way and its outcome unknown. */360export function recordHiddenHanded(db: Database, repo: string, list: string | null): void {361	db.query("UPDATE repos SET hidden_handed = ? WHERE name = ?").run(list, repo);362}363364/** Names of files and folders hidden at any depth in every repo. */365export function ignoredNames(db: Database): string[] {366	return setting(db, "ignored")?.split("\n").filter(Boolean) ?? [];367}368369/** A repo's own texts, public or not; '' where the README speaks. */370export function repoText(db: Database, repo: string): Record<RepoText, string> {371	return (372		db373			.query<Record<RepoText, string>, [string]>(374				"SELECT description, details FROM repo_text WHERE repo = ?",375			)376			.get(repo) ?? { description: "", details: "" }377	);378}379380/** Sets one of a repo's texts; '' hands it back to the README. */381export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {382	db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);383	// `field` is one of two column names, never input.384	db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);385}