1import { Database } from "bun:sqlite";2import { mkdirSync } from "node:fs";3import { join } from "node:path";456const 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-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.80CREATE TABLE IF NOT EXISTS repo_text (81 repo TEXT PRIMARY KEY,82 description TEXT NOT NULL DEFAULT '',83 details TEXT NOT NULL DEFAULT ''84);85CREATE TABLE IF NOT EXISTS connect_codes (86 code_hash TEXT PRIMARY KEY,87 machine TEXT NOT NULL,88 created_at INTEGER NOT NULL,89 expires_at INTEGER NOT NULL,90 used_at INTEGER91);92`;939495export type RepoSettings = {96 name: string;97 category: string;98 homepage: string;99 installLines: string[];100 description: string;101 details: string;102};103104export type RepoText = "description" | "details";105106type RepoRow = {107 name: string;108 category: string;109 homepage: string;110 install1: string;111 install2: string;112 install3: string;113 description: string | null;114 details: string | null;115};116117export function openKeprDb(dataDir: string): Database {118 mkdirSync(dataDir, { recursive: true });119 const db = new Database(join(dataDir, "kepr.db"), { strict: true });120 db.run("PRAGMA journal_mode = WAL");121 db.run("PRAGMA busy_timeout = 5000");122 db.run(SCHEMA);123 return db;124}125126export function setting(db: Database, key: string): string | null {127 const row = db128 .query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")129 .get(key);130 return row?.value ?? null;131}132133134export function registerRepo(db: Database, name: string): void {135 db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);136}137138export function recordSync(db: Database, name: string, at: number, error: string | null): void {139 db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(140 at,141 error,142 name,143 );144}145146export function publicRepoNames(db: Database): string[] {147 return db148 .query<{ name: string }, []>(149 "SELECT name FROM repos WHERE public = 1 ORDER BY sort_order, name",150 )151 .all()152 .map((row) => row.name);153}154155156export function publicRepo(db: Database, name: string): RepoSettings | null {157 const row = db158 .query<RepoRow, [string]>(159 `SELECT name, category, homepage, install1, install2, install3, description, details160 FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name161 WHERE name = ? AND public = 1`,162 )163 .get(name);164 if (!row) return null;165 const installLines = [row.install1, row.install2, row.install3].filter((line) => line !== "");166 return {167 name: row.name,168 category: row.category,169 homepage: row.homepage,170 installLines,171 description: row.description ?? "",172 details: row.details ?? "",173 };174}175176177export function repoText(db: Database, repo: string): Record<RepoText, string> {178 return (179 db180 .query<Record<RepoText, string>, [string]>(181 "SELECT description, details FROM repo_text WHERE repo = ?",182 )183 .get(repo) ?? { description: "", details: "" }184 );185}186187188export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {189 db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);190 191 db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);192}