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	languages       TEXT NOT NULL DEFAULT '',21	issues_paused   INTEGER NOT NULL DEFAULT 0,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);41CREATE TABLE IF NOT EXISTS installs (42	repo  TEXT PRIMARY KEY,43	count INTEGER NOT NULL DEFAULT 044);45-- §23. Numbers are per repo and set explicitly, so Kepr 2's carry over on import. The contact46-- email is the operator's alone; token_hash is the follow-up link's SHA-256 (NULL for imports).47CREATE TABLE IF NOT EXISTS issues (48	id            INTEGER PRIMARY KEY,49	repo          TEXT NOT NULL,50	number        INTEGER NOT NULL,51	title         TEXT NOT NULL,52	body          TEXT NOT NULL,53	type          TEXT NOT NULL CHECK (type IN ('bug', 'idea', 'question')),54	version       TEXT NOT NULL DEFAULT '',55	reporter_name TEXT NOT NULL DEFAULT '',56	contact_email TEXT NOT NULL DEFAULT '',57	filed_at      INTEGER NOT NULL,58	status        TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),59	closed_at     INTEGER,60	fixed_in      TEXT NOT NULL DEFAULT '',61	hidden        INTEGER NOT NULL DEFAULT 0,62	ip_hash       TEXT NOT NULL DEFAULT '',63	token_hash    TEXT,64	UNIQUE (repo, number)65);66CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);67-- The thread after the first post: the reporter's follow-ups and the operator's replies.68CREATE TABLE IF NOT EXISTS issue_entries (69	id         INTEGER PRIMARY KEY,70	issue_id   INTEGER NOT NULL REFERENCES issues (id),71	author     TEXT NOT NULL CHECK (author IN ('reporter', 'operator')),72	body       TEXT NOT NULL,73	created_at INTEGER NOT NULL,74	hidden     INTEGER NOT NULL DEFAULT 075);76CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);77-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.78CREATE TABLE IF NOT EXISTS sessions (79	token_hash TEXT PRIMARY KEY,80	expires_at INTEGER NOT NULL81);82-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.83CREATE TABLE IF NOT EXISTS repo_text (84	repo        TEXT PRIMARY KEY,85	description TEXT NOT NULL DEFAULT '',86	details     TEXT NOT NULL DEFAULT ''87);88CREATE TABLE IF NOT EXISTS connect_codes (89	code_hash   TEXT PRIMARY KEY,90	machine     TEXT NOT NULL,91	created_at  INTEGER NOT NULL,92	expires_at  INTEGER NOT NULL,93	used_at     INTEGER94);95`;9697// Columns added after the first kepr.db was made; SQLite has no ADD COLUMN IF NOT EXISTS.98const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [99	["repos", "languages", "TEXT NOT NULL DEFAULT ''"],100	["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],101	["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],102];103104/** `description` and `details` are the operator's Markdown, or '' for the README's. */105export type RepoSettings = {106	name: string;107	category: string;108	homepage: string;109	installLines: string[];110	/** The operator's shown languages (§24), or none for the detected one. */111	languages: string[];112	/** Filing is paused (§23); the list, threads and follow-ups carry on. */113	issuesPaused: boolean;114	description: string;115	details: string;116};117118export type RepoText = "description" | "details";119120type RepoRow = {121	name: string;122	category: string;123	homepage: string;124	install1: string;125	install2: string;126	install3: string;127	languages: string;128	issues_paused: number;129	description: string | null;130	details: string | null;131};132133export function openKeprDb(dataDir: string): Database {134	mkdirSync(dataDir, { recursive: true });135	const db = new Database(join(dataDir, "kepr.db"), { strict: true });136	db.run("PRAGMA journal_mode = WAL");137	db.run("PRAGMA busy_timeout = 5000");138	db.run(SCHEMA);139	for (const [table, column, definition] of ADDED_COLUMNS) {140		// Both names come from the list above, never from input.141		const columns = db.query<{ name: string }, []>(`PRAGMA table_info(${table})`).all();142		if (!columns.some((existing) => existing.name === column)) {143			db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);144		}145	}146	return db;147}148149export function setting(db: Database, key: string): string | null {150	const row = db151		.query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")152		.get(key);153	return row?.value ?? null;154}155156/** Every repo starts private (§20); registering a known repo changes nothing. */157export function registerRepo(db: Database, name: string): void {158	db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);159}160161export function recordSync(db: Database, name: string, at: number, error: string | null): void {162	db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(163		at,164		error,165		name,166	);167}168169export function publicRepoNames(db: Database): string[] {170	return db171		.query<{ name: string }, []>(172			"SELECT name FROM repos WHERE public = 1 ORDER BY sort_order, name",173		)174		.all()175		.map((row) => row.name);176}177178/** The settings of a public repo, or null when it is private or unknown: both must look missing. */179export function publicRepo(db: Database, name: string): RepoSettings | null {180	const row = db181		.query<RepoRow, [string]>(182			`SELECT name, category, homepage, install1, install2, install3, languages, issues_paused,183			description, details184			FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name185			WHERE name = ? AND public = 1`,186		)187		.get(name);188	if (!row) return null;189	const installLines = [row.install1, row.install2, row.install3].filter((line) => line !== "");190	return {191		name: row.name,192		category: row.category,193		homepage: row.homepage,194		installLines,195		languages: row.languages === "" ? [] : row.languages.split(", "),196		issuesPaused: row.issues_paused === 1,197		description: row.description ?? "",198		details: row.details ?? "",199	};200}201202/** A repo's own texts, public or not; '' where the README speaks. */203export function repoText(db: Database, repo: string): Record<RepoText, string> {204	return (205		db206			.query<Record<RepoText, string>, [string]>(207				"SELECT description, details FROM repo_text WHERE repo = ?",208			)209			.get(repo) ?? { description: "", details: "" }210	);211}212213/** Sets one of a repo's texts; '' hands it back to the README. */214export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {215	db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);216	// `field` is one of two column names, never input.217	db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);218}