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	sort_order      INTEGER NOT NULL DEFAULT 0,17	install1        TEXT NOT NULL DEFAULT '',18	install2        TEXT NOT NULL DEFAULT '',19	install3        TEXT NOT NULL DEFAULT '',20	last_sync_at    INTEGER,21	last_sync_error TEXT22);23-- §24: the admin console hides a version or replaces its notes; NULL notes keep the record's.24CREATE TABLE IF NOT EXISTS release_settings (25	repo    TEXT NOT NULL,26	version TEXT NOT NULL,27	hidden  INTEGER NOT NULL DEFAULT 0,28	notes   TEXT,29	PRIMARY KEY (repo, version)30);31-- §22.2: one count per release file; installs are Nix tarball fetches, per repo.32CREATE TABLE IF NOT EXISTS downloads (33	repo     TEXT NOT NULL,34	version  TEXT NOT NULL,35	platform TEXT NOT NULL,36	count    INTEGER NOT NULL DEFAULT 0,37	PRIMARY KEY (repo, version, platform)38);39CREATE TABLE IF NOT EXISTS installs (40	repo  TEXT PRIMARY KEY,41	count INTEGER NOT NULL DEFAULT 042);43-- §23. Numbers are per repo and set explicitly, so Kepr 2's carry over on import. The contact44-- email is the operator's alone; token_hash is the follow-up link's SHA-256 (NULL for imports).45CREATE TABLE IF NOT EXISTS issues (46	id            INTEGER PRIMARY KEY,47	repo          TEXT NOT NULL,48	number        INTEGER NOT NULL,49	title         TEXT NOT NULL,50	body          TEXT NOT NULL,51	type          TEXT NOT NULL CHECK (type IN ('bug', 'idea', 'question')),52	version       TEXT NOT NULL DEFAULT '',53	reporter_name TEXT NOT NULL DEFAULT '',54	contact_email TEXT NOT NULL DEFAULT '',55	filed_at      INTEGER NOT NULL,56	status        TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),57	closed_at     INTEGER,58	fixed_in      TEXT NOT NULL DEFAULT '',59	hidden        INTEGER NOT NULL DEFAULT 0,60	ip_hash       TEXT NOT NULL DEFAULT '',61	token_hash    TEXT,62	UNIQUE (repo, number)63);64CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);65-- The thread after the first post: the reporter's follow-ups and the operator's replies.66CREATE TABLE IF NOT EXISTS issue_entries (67	id         INTEGER PRIMARY KEY,68	issue_id   INTEGER NOT NULL REFERENCES issues (id),69	author     TEXT NOT NULL CHECK (author IN ('reporter', 'operator')),70	body       TEXT NOT NULL,71	created_at INTEGER NOT NULL72);73CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);74-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.75CREATE TABLE IF NOT EXISTS sessions (76	token_hash TEXT PRIMARY KEY,77	expires_at INTEGER NOT NULL78);79`;8081export type RepoSettings = {82	name: string;83	category: string;84	homepage: string;85	installLines: string[];86};8788type RepoRow = {89	name: string;90	category: string;91	homepage: string;92	install1: string;93	install2: string;94	install3: string;95};9697export function openKeprDb(dataDir: string): Database {98	mkdirSync(dataDir, { recursive: true });99	const db = new Database(join(dataDir, "kepr.db"), { strict: true });100	db.run("PRAGMA journal_mode = WAL");101	db.run("PRAGMA busy_timeout = 5000");102	db.run(SCHEMA);103	return db;104}105106export function setting(db: Database, key: string): string | null {107	const row = db108		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")109		.get(key);110	return row?.value ?? null;111}112113/** Every repo starts private (§20); registering a known repo changes nothing. */114export function registerRepo(db: Database, name: string): void {115	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);116}117118export function recordSync(db: Database, name: string, at: number, error: string | null): void {119	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(120		at,121		error,122		name,123	);124}125126export function publicRepoNames(db: Database): string[] {127	return db128		.query<{ name: string }, []>(129			"SELECT name FROM repos WHERE public = 1 ORDER BY sort_order, name",130		)131		.all()132		.map((row) => row.name);133}134135/** The settings of a public repo, or null when it is private or unknown: both must look missing. */136export function publicRepo(db: Database, name: string): RepoSettings | null {137	const row = db138		.query<RepoRow, [string]>(139			"SELECT name, category, homepage, install1, install2, install3 FROM repos WHERE name = ? AND public = 1",140		)141		.get(name);142	if (!row) return null;143	const installLines = [row.install1, row.install2, row.install3].filter((line) => line !== "");144	return { name: row.name, category: row.category, homepage: row.homepage, installLines };145}