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 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-- §24: what Kepr tells the operator in the console, newest 200 kept.96CREATE TABLE IF NOT EXISTS notifications (97 id INTEGER PRIMARY KEY,98 kind TEXT NOT NULL,99 text TEXT NOT NULL,100 href TEXT NOT NULL,101 created_at INTEGER NOT NULL,102 read INTEGER NOT NULL DEFAULT 0103);104`;105106107export const NOTIFICATION_KINDS = [108 "issue_filed",109 "issue_followup",110 "sync_failed",111 "backup_failed",112] as const;113export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];114const NOTIFICATIONS_KEPT = 200;115116117const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [118 ["repos", "languages", "TEXT NOT NULL DEFAULT ''"],119 ["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],120 ["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],121];122123124export type RepoSettings = {125 name: string;126 category: string;127 homepage: string;128 installLines: string[];129 130 languages: string[];131 132 issuesPaused: boolean;133 description: string;134 details: string;135};136137export type RepoText = "description" | "details";138139type RepoRow = {140 name: string;141 category: string;142 homepage: string;143 install1: string;144 install2: string;145 install3: string;146 languages: string;147 issues_paused: number;148 description: string | null;149 details: string | null;150};151152export function openKeprDb(dataDir: string): Database {153 mkdirSync(dataDir, { recursive: true });154 const db = new Database(join(dataDir, "kepr.db"), { strict: true });155 db.run("PRAGMA journal_mode = WAL");156 db.run("PRAGMA busy_timeout = 5000");157 db.run(SCHEMA);158 for (const [table, column, definition] of ADDED_COLUMNS) {159 160 const columns = db.query<{ name: string }, []>(`PRAGMA table_info(${table})`).all();161 if (!columns.some((existing) => existing.name === column)) {162 db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);163 }164 }165 return db;166}167168export function setting(db: Database, key: string): string | null {169 const row = db170 .query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")171 .get(key);172 return row?.value ?? null;173}174175176export function registerRepo(db: Database, name: string): void {177 db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);178}179180181export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {182 if (setting(db, `notify_off_${kind}`)) return;183 db.transaction(() => {184 db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(185 kind,186 text,187 href,188 Math.floor(Date.now() / 1000),189 );190 db.query(191 `DELETE FROM notifications192 WHERE id NOT IN (SELECT id FROM notifications ORDER BY id DESC LIMIT ?)`,193 ).run(NOTIFICATIONS_KEPT);194 })();195}196197198export function recordSync(db: Database, name: string, at: number, error: string | null): void {199 const before = db200 .query<{ last_sync_error: string | null }, [string]>(201 "SELECT last_sync_error FROM repos WHERE name = ?",202 )203 .get(name);204 if (error && !before?.last_sync_error) {205 notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);206 }207 db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(208 at,209 error,210 name,211 );212}213214export function publicRepoNames(db: Database): string[] {215 return db216 .query<{ name: string }, []>(217 "SELECT name FROM repos WHERE public = 1 ORDER BY sort_order, name",218 )219 .all()220 .map((row) => row.name);221}222223224export function publicRepo(db: Database, name: string): RepoSettings | null {225 const row = db226 .query<RepoRow, [string]>(227 `SELECT name, category, homepage, install1, install2, install3, languages, issues_paused,228 description, details229 FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name230 WHERE name = ? AND public = 1`,231 )232 .get(name);233 if (!row) return null;234 const installLines = [row.install1, row.install2, row.install3].filter((line) => line !== "");235 return {236 name: row.name,237 category: row.category,238 homepage: row.homepage,239 installLines,240 languages: row.languages === "" ? [] : row.languages.split(", "),241 issuesPaused: row.issues_paused === 1,242 description: row.description ?? "",243 details: row.details ?? "",244 };245}246247248export function repoText(db: Database, repo: string): Record<RepoText, string> {249 return (250 db251 .query<Record<RepoText, string>, [string]>(252 "SELECT description, details FROM repo_text WHERE repo = ?",253 )254 .get(repo) ?? { description: "", details: "" }255 );256}257258259export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {260 db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);261 262 db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);263}