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-- §23: screenshots and logs, on an issue itself (entry_id NULL) or on one of its replies.97CREATE TABLE IF NOT EXISTS issue_files (98	id         INTEGER PRIMARY KEY,99	issue_id   INTEGER NOT NULL REFERENCES issues (id),100	entry_id   INTEGER REFERENCES issue_entries (id),101	name       TEXT NOT NULL,102	type       TEXT NOT NULL,103	size       INTEGER NOT NULL,104	data       BLOB NOT NULL,105	ip_hash    TEXT NOT NULL DEFAULT '',106	created_at INTEGER NOT NULL107);108CREATE INDEX IF NOT EXISTS issue_files_issue ON issue_files (issue_id, id);109CREATE INDEX IF NOT EXISTS issue_files_ip ON issue_files (ip_hash, created_at);110-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.111CREATE TABLE IF NOT EXISTS sessions (112	token_hash TEXT PRIMARY KEY,113	expires_at INTEGER NOT NULL114);115-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.116CREATE TABLE IF NOT EXISTS repo_text (117	repo        TEXT PRIMARY KEY,118	description TEXT NOT NULL DEFAULT '',119	details     TEXT NOT NULL DEFAULT ''120);121CREATE TABLE IF NOT EXISTS connect_codes (122	code_hash   TEXT PRIMARY KEY,123	machine     TEXT NOT NULL,124	created_at  INTEGER NOT NULL,125	expires_at  INTEGER NOT NULL,126	used_at     INTEGER127);128-- §24: what Kepr tells the operator in the console, newest 200 kept.129CREATE TABLE IF NOT EXISTS notifications (130	id         INTEGER PRIMARY KEY,131	kind       TEXT NOT NULL,132	text       TEXT NOT NULL,133	href       TEXT NOT NULL,134	created_at INTEGER NOT NULL,135	read       INTEGER NOT NULL DEFAULT 0136);137-- §24: emails on their way out. An empty recipient is the operator, kepr.toml's address, sent the138-- subject and the link; an issue's follower (§23) is sent the body, unsubscribe as its header.139CREATE TABLE IF NOT EXISTS email_queue (140	id          INTEGER PRIMARY KEY,141	subject     TEXT NOT NULL,142	href        TEXT NOT NULL,143	recipient   TEXT NOT NULL DEFAULT '',144	body        TEXT NOT NULL DEFAULT '',145	unsubscribe TEXT NOT NULL DEFAULT '',146	status      TEXT NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'sent', 'failed')),147	attempts    INTEGER NOT NULL DEFAULT 0,148	next_at     INTEGER NOT NULL,149	last_error  TEXT NOT NULL DEFAULT '',150	created_at  INTEGER NOT NULL,151	sent_at     INTEGER152);153-- §23: addresses following an issue by email; nothing is sent to one until it is confirmed.154CREATE TABLE IF NOT EXISTS issue_followers (155	id         INTEGER PRIMARY KEY,156	issue_id   INTEGER NOT NULL REFERENCES issues (id),157	email      TEXT NOT NULL,158	confirmed  INTEGER NOT NULL DEFAULT 0,159	ip_hash    TEXT NOT NULL DEFAULT '',160	created_at INTEGER NOT NULL,161	UNIQUE (issue_id, email)162);163CREATE INDEX IF NOT EXISTS issue_followers_ip ON issue_followers (ip_hash, created_at);164-- §23: follow requests as they arrive. A follower's stop link deletes their row above, so counting165-- only those rows would hand the hour's allowance back every time someone stopped the emails.166CREATE TABLE IF NOT EXISTS issue_follow_requests (167	ip_hash    TEXT NOT NULL,168	created_at INTEGER NOT NULL169);170CREATE INDEX IF NOT EXISTS issue_follow_requests_ip171	ON issue_follow_requests (ip_hash, created_at);172-- A repo renamed from the console (June, 2026-10-01): its old pages redirect to the new name.173CREATE TABLE IF NOT EXISTS renamed (174	old TEXT PRIMARY KEY,175	new TEXT NOT NULL176);177`;178179/** What raises a notification (§24); each can be switched off. */180export const NOTIFICATION_KINDS = [181	"issue_filed",182	"issue_followup",183	"sync_failed",184	"backup_failed",185] as const;186export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];187const NOTIFICATIONS_KEPT = 200;188189// Columns added after the first kepr.db was made; SQLite has no ADD COLUMN IF NOT EXISTS.190const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [191	["repos", "languages", "TEXT NOT NULL DEFAULT ''"],192	["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],193	["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],194	["issue_entries", "name", "TEXT NOT NULL DEFAULT ''"],195	["issue_entries", "ip_hash", "TEXT NOT NULL DEFAULT ''"],196	["issue_entries", "reply_to", "INTEGER"],197	["repos", "hidden_paths", "TEXT NOT NULL DEFAULT ''"],198	["repos", "hidden_handed", "TEXT"],199	["email_queue", "recipient", "TEXT NOT NULL DEFAULT ''"],200	["email_queue", "body", "TEXT NOT NULL DEFAULT ''"],201	["email_queue", "unsubscribe", "TEXT NOT NULL DEFAULT ''"],202];203// Columns an older kepr.db still has; card order and install lines were removed (June, 2026-09-30).204const DROPPED_COLUMNS: [table: string, column: string][] = [205	["repos", "sort_order"],206	["repos", "install1"],207	["repos", "install2"],208	["repos", "install3"],209	// The private follow-up link, replaced by public replies (June, 2026-10-08).210	["issues", "token_hash"],211];212213/** `description` and `details` are the operator's Markdown, or '' for the README's. */214export type RepoSettings = {215	name: string;216	category: string;217	homepage: string;218	/** The operator's shown languages (§24), or none for the detected one. */219	languages: string[];220	/** Filing is paused (§23); the list, threads and follow-ups carry on. */221	issuesPaused: boolean;222	description: string;223	details: string;224};225226export type RepoText = "description" | "details";227228type RepoRow = {229	name: string;230	category: string;231	homepage: string;232	languages: string;233	issues_paused: number;234	description: string | null;235	details: string | null;236};237238export function openKeprDb(dataDir: string): Database {239	mkdirSync(dataDir, { recursive: true });240	const db = new Database(join(dataDir, "kepr.db"), { strict: true });241	db.run("PRAGMA journal_mode = WAL");242	db.run("PRAGMA busy_timeout = 5000");243	db.run(SCHEMA);244	// Every name below comes from the two lists above, never from input.245	const has = (table: string, column: string) =>246		db247			.query<{ name: string }, []>(`PRAGMA table_info(${table})`)248			.all()249			.some((existing) => existing.name === column);250	const named = has("issue_entries", "name");251	for (const [table, column, definition] of ADDED_COLUMNS) {252		if (!has(table, column)) db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);253	}254	// Follow-ups came only from an issue's reporter, so they keep the name the issue shows.255	if (!named) {256		db.run(`UPDATE issue_entries SET name = (257	SELECT reporter_name FROM issues WHERE issues.id = issue_entries.issue_id258) WHERE author = 'reporter'`);259	}260	for (const [table, column] of DROPPED_COLUMNS) {261		if (has(table, column)) db.run(`ALTER TABLE ${table} DROP COLUMN ${column}`);262	}263	db.run("CREATE INDEX IF NOT EXISTS issue_entries_ip ON issue_entries (ip_hash, created_at)");264	return db;265}266267export function setting(db: Database, key: string): string | null {268	const row = db269		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")270		.get(key);271	return row?.value ?? null;272}273274/** The name a repo was renamed to, following later renames, or null. */275export function renamedTo(db: Database, name: string): string | null {276	const query = db.query<{ new: string }, [string]>("SELECT new FROM renamed WHERE old = ?");277	let to: string | null = null;278	for (let row = query.get(name), hops = 0; row && hops < 5; row = query.get(row.new), hops++) {279		to = row.new;280	}281	return to;282}283284export function isRegistered(db: Database, name: string): boolean {285	return db.query("SELECT 1 FROM repos WHERE name = ?").get(name) !== null;286}287288/** Every repo starts private (§20); registering a known repo changes nothing. */289export function registerRepo(db: Database, name: string): void {290	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);291}292293/**294 * Tells the operator, unless that kind is switched off, and queues an email when that kind is295 * emailed. The newest 200 notifications are kept.296 */297export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {298	const now = Math.floor(Date.now() / 1000);299	// Immediate: it reads before writing, and the sync's thread writes kepr.db too.300	db.transaction(() => {301		if (!setting(db, `notify_off_${kind}`)) {302			db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(303				kind,304				text,305				href,306				now,307			);308			db.query(309				`DELETE FROM notifications310				WHERE id NOT IN (SELECT id FROM notifications ORDER BY id DESC LIMIT ?)`,311			).run(NOTIFICATIONS_KEPT);312		}313		if (setting(db, `email_on_${kind}`)) queueEmail(db, { subject: text, href });314	}).immediate();315}316317export type Email = {318	subject: string;319	href: string;320	to?: string;321	text?: string;322	unsubscribe?: string;323};324325/**326 * Queues an email (§24): to the operator, or to `to` with `text` as its body. Only the newest 200327 * are kept, and never by dropping one still waiting to go.328 */329export function queueEmail(db: Database, email: Email): void {330	const now = Math.floor(Date.now() / 1000);331	db.query(332		`INSERT INTO email_queue (subject, href, recipient, body, unsubscribe, next_at, created_at)333		VALUES (?, ?, ?, ?, ?, ?, ?)`,334	).run(335		email.subject,336		email.href,337		email.to ?? "",338		email.text ?? "",339		email.unsubscribe ?? "",340		now,341		now,342	);343	db.query(344		`DELETE FROM email_queue345		WHERE status != 'waiting' AND id NOT IN (SELECT id FROM email_queue ORDER BY id DESC LIMIT ?)`,346	).run(NOTIFICATIONS_KEPT);347}348349/** Records a sync's outcome; a repo that starts failing tells the operator, once. */350export function recordSync(db: Database, name: string, at: number, error: string | null): void {351	const before = db352		.query<{ last_sync_error: string | null }, [string]>(353			"SELECT last_sync_error FROM repos WHERE name = ?",354		)355		.get(name);356	if (error && !before?.last_sync_error) {357		notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);358	}359	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(360		at,361		error,362		name,363	);364}365366export function publicRepoNames(db: Database): string[] {367	return db368		.query<{ name: string }, []>("SELECT name FROM repos WHERE public = 1 ORDER BY name")369		.all()370		.map((row) => row.name);371}372373/** The settings of a public repo, or null when it is private or unknown: both must look missing. */374export function publicRepo(db: Database, name: string): RepoSettings | null {375	const row = db376		.query<RepoRow, [string]>(377			`SELECT name, category, homepage, languages, issues_paused, description, details378			FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name379			WHERE name = ? AND public = 1`,380		)381		.get(name);382	if (!row) return null;383	return {384		name: row.name,385		category: row.category,386		homepage: row.homepage,387		languages: row.languages === "" ? [] : row.languages.split(", "),388		issuesPaused: row.issues_paused === 1,389		description: row.description ?? "",390		details: row.details ?? "",391	};392}393394/**395 * What a sync removes from a repo: the paths the operator hid from it (June, 2026-09-30) and the396 * names ignored in every repo (June, 2026-10-01), both one per line in kepr.db.397 */398export function hiddenPaths(db: Database, repo: string): string[] {399	const row = db400		.query<{ hidden_paths: string }, [string]>("SELECT hidden_paths FROM repos WHERE name = ?")401		.get(repo);402	const paths = row?.hidden_paths ? row.hidden_paths.split("\n") : [];403	return [...paths, ...ignoredNames(db).map((name) => `${ANY_DEPTH}${name}`)];404}405406/** Keeps a piece of Kepr's own state in settings, never shown as a setting; null removes it. */407export function keepState(db: Database, key: string, value: string | null): void {408	if (value === null) db.query("DELETE FROM settings WHERE key = ?").run(key);409	else db.query("INSERT OR REPLACE INTO settings (key, value) VALUES (?, ?)").run(key, value);410}411412/** A repo's hidden paths unless they are known to be the list last handed to Koh, else null. */413export function hiddenToHand(db: Database, repo: string): string | null {414	const row = db415		.query<{ hidden_paths: string }, [string]>(416			`SELECT hidden_paths FROM repos417			WHERE name = ?418			AND (hidden_handed IS NULL OR hidden_paths != hidden_handed)`,419		)420		.get(repo);421	return row?.hidden_paths ?? null;422}423424/** Records the list in the bucket, or null while a write is under way and its outcome unknown. */425export function recordHiddenHanded(db: Database, repo: string, list: string | null): void {426	db.query("UPDATE repos SET hidden_handed = ? WHERE name = ?").run(list, repo);427}428429/** Names of files and folders hidden at any depth in every repo. */430export function ignoredNames(db: Database): string[] {431	return setting(db, "ignored")?.split("\n").filter(Boolean) ?? [];432}433434/** A repo's own texts, public or not; '' where the README speaks. */435export function repoText(db: Database, repo: string): Record<RepoText, string> {436	return (437		db438			.query<Record<RepoText, string>, [string]>(439				"SELECT description, details FROM repo_text WHERE repo = ?",440			)441			.get(repo) ?? { description: "", details: "" }442	);443}444445/** Sets one of a repo's texts; '' hands it back to the README. */446export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {447	db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);448	// `field` is one of two column names, never input.449	db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);450}