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 languages TEXT NOT NULL DEFAULT '',17 issues_paused INTEGER NOT NULL DEFAULT 0,18 hidden_paths TEXT NOT NULL DEFAULT '',19 last_sync_at INTEGER,20 last_sync_error TEXT21);22-- §24: the admin console hides a version or replaces its notes; NULL notes keep the record's.23CREATE TABLE IF NOT EXISTS release_settings (24 repo TEXT NOT NULL,25 version TEXT NOT NULL,26 hidden INTEGER NOT NULL DEFAULT 0,27 notes TEXT,28 PRIMARY KEY (repo, version)29);30-- §22.2: one count per release file; installs are Nix tarball fetches, per repo.31CREATE TABLE IF NOT EXISTS downloads (32 repo TEXT NOT NULL,33 version TEXT NOT NULL,34 platform TEXT NOT NULL,35 count INTEGER NOT NULL DEFAULT 0,36 PRIMARY KEY (repo, version, platform)37);38-- §24's Downloads: the same downloads per repo and UTC day (YYYY-MM-DD), the newest 400 days.39CREATE TABLE IF NOT EXISTS download_days (40 repo TEXT NOT NULL,41 day TEXT NOT NULL,42 count INTEGER NOT NULL DEFAULT 0,43 PRIMARY KEY (repo, day)44);45CREATE TABLE IF NOT EXISTS installs (46 repo TEXT PRIMARY KEY,47 count INTEGER NOT NULL DEFAULT 048);49-- §23. Numbers are per repo and set explicitly, so Kepr 2's carry over on import. The contact50-- email is the operator's alone; token_hash is the follow-up link's SHA-256 (NULL for imports).51CREATE TABLE IF NOT EXISTS issues (52 id INTEGER PRIMARY KEY,53 repo TEXT NOT NULL,54 number INTEGER NOT NULL,55 title TEXT NOT NULL,56 body TEXT NOT NULL,57 type TEXT NOT NULL CHECK (type IN ('bug', 'idea', 'question')),58 version TEXT NOT NULL DEFAULT '',59 reporter_name TEXT NOT NULL DEFAULT '',60 contact_email TEXT NOT NULL DEFAULT '',61 filed_at INTEGER NOT NULL,62 status TEXT NOT NULL DEFAULT 'open' CHECK (status IN ('open', 'closed')),63 closed_at INTEGER,64 fixed_in TEXT NOT NULL DEFAULT '',65 hidden INTEGER NOT NULL DEFAULT 0,66 ip_hash TEXT NOT NULL DEFAULT '',67 token_hash TEXT,68 UNIQUE (repo, number)69);70CREATE INDEX IF NOT EXISTS issues_ip ON issues (ip_hash, filed_at);71-- The thread after the first post: the reporter's follow-ups and the operator's replies.72-- §23: addresses blocked from filing and following up, by the hash the rate limit keeps, with the73-- issue they were blocked from.74CREATE TABLE IF NOT EXISTS blocked_reporters (75 id INTEGER PRIMARY KEY,76 ip_hash TEXT NOT NULL UNIQUE,77 repo TEXT NOT NULL,78 number INTEGER NOT NULL,79 blocked_at INTEGER NOT NULL80);81CREATE TABLE IF NOT EXISTS issue_entries (82 id INTEGER PRIMARY KEY,83 issue_id INTEGER NOT NULL REFERENCES issues (id),84 author TEXT NOT NULL CHECK (author IN ('reporter', 'operator')),85 body TEXT NOT NULL,86 created_at INTEGER NOT NULL,87 hidden INTEGER NOT NULL DEFAULT 088);89CREATE INDEX IF NOT EXISTS issue_entries_issue ON issue_entries (issue_id, id);90-- §24: admin sessions, revocable; only the SHA-256 of the cookie's token is kept.91CREATE TABLE IF NOT EXISTS sessions (92 token_hash TEXT PRIMARY KEY,93 expires_at INTEGER NOT NULL94);95-- June's own words for a repo, edited on its page when signed in; '' falls back to the README.96CREATE TABLE IF NOT EXISTS repo_text (97 repo TEXT PRIMARY KEY,98 description TEXT NOT NULL DEFAULT '',99 details TEXT NOT NULL DEFAULT ''100);101CREATE TABLE IF NOT EXISTS connect_codes (102 code_hash TEXT PRIMARY KEY,103 machine TEXT NOT NULL,104 created_at INTEGER NOT NULL,105 expires_at INTEGER NOT NULL,106 used_at INTEGER107);108-- §24: what Kepr tells the operator in the console, newest 200 kept.109CREATE TABLE IF NOT EXISTS notifications (110 id INTEGER PRIMARY KEY,111 kind TEXT NOT NULL,112 text TEXT NOT NULL,113 href TEXT NOT NULL,114 created_at INTEGER NOT NULL,115 read INTEGER NOT NULL DEFAULT 0116);117-- §24: notifications on their way to the operator's inbox; the address is kepr.toml's.118CREATE TABLE IF NOT EXISTS email_queue (119 id INTEGER PRIMARY KEY,120 subject TEXT NOT NULL,121 href TEXT NOT NULL,122 status TEXT NOT NULL DEFAULT 'waiting' CHECK (status IN ('waiting', 'sent', 'failed')),123 attempts INTEGER NOT NULL DEFAULT 0,124 next_at INTEGER NOT NULL,125 last_error TEXT NOT NULL DEFAULT '',126 created_at INTEGER NOT NULL,127 sent_at INTEGER128);129`;130131132export const NOTIFICATION_KINDS = [133 "issue_filed",134 "issue_followup",135 "sync_failed",136 "backup_failed",137] as const;138export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];139const NOTIFICATIONS_KEPT = 200;140141142const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [143 ["repos", "languages", "TEXT NOT NULL DEFAULT ''"],144 ["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],145 ["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],146 ["repos", "hidden_paths", "TEXT NOT NULL DEFAULT ''"],147];148149const DROPPED_COLUMNS: [table: string, column: string][] = [150 ["repos", "sort_order"],151 ["repos", "install1"],152 ["repos", "install2"],153 ["repos", "install3"],154];155156157export type RepoSettings = {158 name: string;159 category: string;160 homepage: string;161 162 languages: string[];163 164 issuesPaused: boolean;165 description: string;166 details: string;167};168169export type RepoText = "description" | "details";170171type RepoRow = {172 name: string;173 category: string;174 homepage: string;175 languages: string;176 issues_paused: number;177 description: string | null;178 details: string | null;179};180181export function openKeprDb(dataDir: string): Database {182 mkdirSync(dataDir, { recursive: true });183 const db = new Database(join(dataDir, "kepr.db"), { strict: true });184 db.run("PRAGMA journal_mode = WAL");185 db.run("PRAGMA busy_timeout = 5000");186 db.run(SCHEMA);187 188 const has = (table: string, column: string) =>189 db190 .query<{ name: string }, []>(`PRAGMA table_info(${table})`)191 .all()192 .some((existing) => existing.name === column);193 for (const [table, column, definition] of ADDED_COLUMNS) {194 if (!has(table, column)) db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);195 }196 for (const [table, column] of DROPPED_COLUMNS) {197 if (has(table, column)) db.run(`ALTER TABLE ${table} DROP COLUMN ${column}`);198 }199 return db;200}201202export function setting(db: Database, key: string): string | null {203 const row = db204 .query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")205 .get(key);206 return row?.value ?? null;207}208209210export function registerRepo(db: Database, name: string): void {211 db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);212}213214215216217218export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {219 const now = Math.floor(Date.now() / 1000);220 db.transaction(() => {221 if (!setting(db, `notify_off_${kind}`)) {222 db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(223 kind,224 text,225 href,226 now,227 );228 }229 if (setting(db, `email_on_${kind}`)) {230 db.query(231 "INSERT INTO email_queue (subject, href, next_at, created_at) VALUES (?, ?, ?, ?)",232 ).run(text, href, now, now);233 }234 for (const table of ["notifications", "email_queue"]) {235 236 db.query(237 `DELETE FROM ${table}238 WHERE id NOT IN (SELECT id FROM ${table} ORDER BY id DESC LIMIT ?)`,239 ).run(NOTIFICATIONS_KEPT);240 }241 })();242}243244245export function recordSync(db: Database, name: string, at: number, error: string | null): void {246 const before = db247 .query<{ last_sync_error: string | null }, [string]>(248 "SELECT last_sync_error FROM repos WHERE name = ?",249 )250 .get(name);251 if (error && !before?.last_sync_error) {252 notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);253 }254 db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(255 at,256 error,257 name,258 );259}260261export function publicRepoNames(db: Database): string[] {262 return db263 .query<{ name: string }, []>("SELECT name FROM repos WHERE public = 1 ORDER BY name")264 .all()265 .map((row) => row.name);266}267268269export function publicRepo(db: Database, name: string): RepoSettings | null {270 const row = db271 .query<RepoRow, [string]>(272 `SELECT name, category, homepage, languages, issues_paused, description, details273 FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name274 WHERE name = ? AND public = 1`,275 )276 .get(name);277 if (!row) return null;278 return {279 name: row.name,280 category: row.category,281 homepage: row.homepage,282 languages: row.languages === "" ? [] : row.languages.split(", "),283 issuesPaused: row.issues_paused === 1,284 description: row.description ?? "",285 details: row.details ?? "",286 };287}288289290export function hiddenPaths(db: Database, repo: string): string[] {291 const row = db292 .query<{ hidden_paths: string }, [string]>("SELECT hidden_paths FROM repos WHERE name = ?")293 .get(repo);294 return row?.hidden_paths ? row.hidden_paths.split("\n") : [];295}296297298export function repoText(db: Database, repo: string): Record<RepoText, string> {299 return (300 db301 .query<Record<RepoText, string>, [string]>(302 "SELECT description, details FROM repo_text WHERE repo = ?",303 )304 .get(repo) ?? { description: "", details: "" }305 );306}307308309export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {310 db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);311 312 db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);313}