1import { Database } from "bun:sqlite";2import { mkdirSync } from "node:fs";3import { join } from "node:path";4import { ANY_DEPTH } from "../mirror/store.ts";567const 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 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);39-- §24's Downloads: the same downloads per repo and UTC day (YYYY-MM-DD), the newest 400 days.40CREATE TABLE IF NOT EXISTS download_days (41 repo TEXT NOT NULL,42 day TEXT NOT NULL,43 count INTEGER NOT NULL DEFAULT 0,44 PRIMARY KEY (repo, day)45);46CREATE TABLE IF NOT EXISTS installs (47 repo TEXT PRIMARY KEY,48 count INTEGER NOT NULL DEFAULT 049);50-- §23. Numbers are per repo and set explicitly, so Kepr 2's carry over on import. The contact51-- email is the operator's alone; token_hash is the follow-up link's SHA-256 (NULL for imports).52CREATE TABLE IF NOT EXISTS issues (53 id INTEGER PRIMARY KEY,54 repo TEXT NOT NULL,55 number INTEGER NOT NULL,56 title TEXT NOT NULL,57 body TEXT NOT NULL,58 type TEXT NOT NULL CHECK (type IN ('bug', 'idea', 'question')),59 version TEXT NOT NULL DEFAULT '',60 reporter_name TEXT NOT NULL DEFAULT '',61 contact_email TEXT NOT NULL DEFAULT '',62 filed_at INTEGER NOT NULL,63 status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),64 closed_at INTEGER,65 fixed_in TEXT NOT NULL DEFAULT '',66 hidden INTEGER NOT NULL DEFAULT 0,67 ip_hash TEXT NOT NULL DEFAULT '',68 token_hash TEXT,69 UNIQUE (repo, number)70);71CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);72-- The thread after the first post: the reporter's follow-ups and the operator's replies.73-- §23: addresses blocked from filing and following up, by the hash the rate limit keeps, with the74-- 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);82CREATE TABLE IF NOT EXISTS issue_entries (83 id INTEGER PRIMARY KEY,84 issue_id INTEGER NOT NULL REFERENCES issues (id),85 author TEXT NOT NULL CHECK (author IN ('reporter', 'operator')),86 body TEXT NOT NULL,87 created_at INTEGER NOT NULL,88 hidden INTEGER NOT NULL DEFAULT 089);90CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);91-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.92CREATE TABLE IF NOT EXISTS sessions (93 token_hash TEXT PRIMARY KEY,94 expires_at INTEGER NOT NULL95);96-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.97CREATE TABLE IF NOT EXISTS repo_text (98 repo TEXT PRIMARY KEY,99 description TEXT NOT NULL DEFAULT '',100 details TEXT NOT NULL DEFAULT ''101);102CREATE TABLE IF NOT EXISTS connect_codes (103 code_hash TEXT PRIMARY KEY,104 machine TEXT NOT NULL,105 created_at INTEGER NOT NULL,106 expires_at INTEGER NOT NULL,107 used_at INTEGER108);109-- §24: what Kepr tells the operator in the console, newest 200 kept.110CREATE TABLE IF NOT EXISTS notifications (111 id INTEGER PRIMARY KEY,112 kind TEXT NOT NULL,113 text TEXT NOT NULL,114 href TEXT NOT NULL,115 created_at INTEGER NOT NULL,116 read INTEGER NOT NULL DEFAULT 0117);118-- §24: notifications on their way to the operator's inbox; the address is kepr.toml's.119CREATE TABLE IF NOT EXISTS email_queue (120 id INTEGER PRIMARY KEY,121 subject TEXT NOT NULL,122 href TEXT NOT NULL,123 status TEXT NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'sent', 'failed')),124 attempts INTEGER NOT NULL DEFAULT 0,125 next_at INTEGER NOT NULL,126 last_error TEXT NOT NULL DEFAULT '',127 created_at INTEGER NOT NULL,128 sent_at INTEGER129);130-- A repo renamed from the console (June, 2026-10-01): its old pages redirect to the new name.131CREATE TABLE IF NOT EXISTS renamed (132 old TEXT PRIMARY KEY,133 new TEXT NOT NULL134);135`;136137138export const NOTIFICATION_KINDS = [139 "issue_filed",140 "issue_followup",141 "sync_failed",142 "backup_failed",143] as const;144export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];145const NOTIFICATIONS_KEPT = 200;146147148const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [149 ["repos", "languages", "TEXT NOT NULL DEFAULT ''"],150 ["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],151 ["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],152 ["repos", "hidden_paths", "TEXT NOT NULL DEFAULT ''"],153];154155const DROPPED_COLUMNS: [table: string, column: string][] = [156 ["repos", "sort_order"],157 ["repos", "install1"],158 ["repos", "install2"],159 ["repos", "install3"],160];161162163export type RepoSettings = {164 name: string;165 category: string;166 homepage: string;167 168 languages: string[];169 170 issuesPaused: boolean;171 description: string;172 details: string;173};174175export type RepoText = "description" | "details";176177type RepoRow = {178 name: string;179 category: string;180 homepage: string;181 languages: string;182 issues_paused: number;183 description: string | null;184 details: string | null;185};186187export function openKeprDb(dataDir: string): Database {188 mkdirSync(dataDir, { recursive: true });189 const db = new Database(join(dataDir, "kepr.db"), { strict: true });190 db.run("PRAGMA journal_mode = WAL");191 db.run("PRAGMA busy_timeout = 5000");192 db.run(SCHEMA);193 194 const has = (table: string, column: string) =>195 db196 .query<{ name: string }, []>(`PRAGMA table_info(${table})`)197 .all()198 .some((existing) => existing.name === column);199 for (const [table, column, definition] of ADDED_COLUMNS) {200 if (!has(table, column)) db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);201 }202 for (const [table, column] of DROPPED_COLUMNS) {203 if (has(table, column)) db.run(`ALTER TABLE ${table} DROP COLUMN ${column}`);204 }205 return db;206}207208export function setting(db: Database, key: string): string | null {209 const row = db210 .query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")211 .get(key);212 return row?.value ?? null;213}214215216export function renamedTo(db: Database, name: string): string | null {217 const query = db.query<{ new: string }, [string]>("SELECT new FROM renamed WHERE old = ?");218 let to: string | null = null;219 for (let row = query.get(name), hops = 0; row && hops < 5; row = query.get(row.new), hops++) {220 to = row.new;221 }222 return to;223}224225226export function registerRepo(db: Database, name: string): void {227 db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);228}229230231232233234export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {235 const now = Math.floor(Date.now() / 1000);236 db.transaction(() => {237 if (!setting(db, `notify_off_${kind}`)) {238 db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(239 kind,240 text,241 href,242 now,243 );244 }245 if (setting(db, `email_on_${kind}`)) {246 db.query(247 "INSERT INTO email_queue (subject, href, next_at, created_at) VALUES (?, ?, ?, ?)",248 ).run(text, href, now, now);249 }250 for (const table of ["notifications", "email_queue"]) {251 252 db.query(253 `DELETE FROM ${table}254 WHERE id NOT IN (SELECT id FROM ${table} ORDER BY id DESC LIMIT ?)`,255 ).run(NOTIFICATIONS_KEPT);256 }257 })();258}259260261export function recordSync(db: Database, name: string, at: number, error: string | null): void {262 const before = db263 .query<{ last_sync_error: string | null }, [string]>(264 "SELECT last_sync_error FROM repos WHERE name = ?",265 )266 .get(name);267 if (error && !before?.last_sync_error) {268 notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);269 }270 db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(271 at,272 error,273 name,274 );275}276277export function publicRepoNames(db: Database): string[] {278 return db279 .query<{ name: string }, []>("SELECT name FROM repos WHERE public = 1 ORDER BY name")280 .all()281 .map((row) => row.name);282}283284285export function publicRepo(db: Database, name: string): RepoSettings | null {286 const row = db287 .query<RepoRow, [string]>(288 `SELECT name, category, homepage, languages, issues_paused, description, details289 FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name290 WHERE name = ? AND public = 1`,291 )292 .get(name);293 if (!row) return null;294 return {295 name: row.name,296 category: row.category,297 homepage: row.homepage,298 languages: row.languages === "" ? [] : row.languages.split(", "),299 issuesPaused: row.issues_paused === 1,300 description: row.description ?? "",301 details: row.details ?? "",302 };303}304305306307308309export function hiddenPaths(db: Database, repo: string): string[] {310 const row = db311 .query<{ hidden_paths: string }, [string]>("SELECT hidden_paths FROM repos WHERE name = ?")312 .get(repo);313 const paths = row?.hidden_paths ? row.hidden_paths.split("\n") : [];314 return [...paths, ...ignoredNames(db).map((name) => `${ANY_DEPTH}${name}`)];315}316317318export function ignoredNames(db: Database): string[] {319 return setting(db, "ignored")?.split("\n").filter(Boolean) ?? [];320}321322323export function repoText(db: Database, repo: string): Record<RepoText, string> {324 return (325 db326 .query<Record<RepoText, string>, [string]>(327 "SELECT description, details FROM repo_text WHERE repo = ?",328 )329 .get(repo) ?? { description: "", details: "" }330 );331}332333334export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {335 db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);336 337 db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);338}