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`;131132133export const NOTIFICATION_KINDS = [134 "issue_filed",135 "issue_followup",136 "sync_failed",137 "backup_failed",138] as const;139export type NotificationKind = (typeof NOTIFICATION_KINDS)[number];140const NOTIFICATIONS_KEPT = 200;141142143const ADDED_COLUMNS: [table: string, column: string, definition: string][] = [144 ["repos", "languages", "TEXT NOT NULL DEFAULT ''"],145 ["repos", "issues_paused", "INTEGER NOT NULL DEFAULT 0"],146 ["issue_entries", "hidden", "INTEGER NOT NULL DEFAULT 0"],147 ["repos", "hidden_paths", "TEXT NOT NULL DEFAULT ''"],148];149150const DROPPED_COLUMNS: [table: string, column: string][] = [151 ["repos", "sort_order"],152 ["repos", "install1"],153 ["repos", "install2"],154 ["repos", "install3"],155];156157158export type RepoSettings = {159 name: string;160 category: string;161 homepage: string;162 163 languages: string[];164 165 issuesPaused: boolean;166 description: string;167 details: string;168};169170export type RepoText = "description" | "details";171172type RepoRow = {173 name: string;174 category: string;175 homepage: string;176 languages: string;177 issues_paused: number;178 description: string | null;179 details: string | null;180};181182export function openKeprDb(dataDir: string): Database {183 mkdirSync(dataDir, { recursive: true });184 const db = new Database(join(dataDir, "kepr.db"), { strict: true });185 db.run("PRAGMA journal_mode = WAL");186 db.run("PRAGMA busy_timeout = 5000");187 db.run(SCHEMA);188 189 const has = (table: string, column: string) =>190 db191 .query<{ name: string }, []>(`PRAGMA table_info(${table})`)192 .all()193 .some((existing) => existing.name === column);194 for (const [table, column, definition] of ADDED_COLUMNS) {195 if (!has(table, column)) db.run(`ALTER TABLE ${table} ADD COLUMN ${column} ${definition}`);196 }197 for (const [table, column] of DROPPED_COLUMNS) {198 if (has(table, column)) db.run(`ALTER TABLE ${table} DROP COLUMN ${column}`);199 }200 return db;201}202203export function setting(db: Database, key: string): string | null {204 const row = db205 .query<{ value: string }, [string]>("SELECT value FROM settings WHERE key = ?")206 .get(key);207 return row?.value ?? null;208}209210211export function registerRepo(db: Database, name: string): void {212 db.query("INSERT OR IGNORE INTO repos (name) VALUES (?)").run(name);213}214215216217218219export function notify(db: Database, kind: NotificationKind, text: string, href: string): void {220 const now = Math.floor(Date.now() / 1000);221 db.transaction(() => {222 if (!setting(db, `notify_off_${kind}`)) {223 db.query("INSERT INTO notifications (kind, text, href, created_at) VALUES (?, ?, ?, ?)").run(224 kind,225 text,226 href,227 now,228 );229 }230 if (setting(db, `email_on_${kind}`)) {231 db.query(232 "INSERT INTO email_queue (subject, href, next_at, created_at) VALUES (?, ?, ?, ?)",233 ).run(text, href, now, now);234 }235 for (const table of ["notifications", "email_queue"]) {236 237 db.query(238 `DELETE FROM ${table}239 WHERE id NOT IN (SELECT id FROM ${table} ORDER BY id DESC LIMIT ?)`,240 ).run(NOTIFICATIONS_KEPT);241 }242 })();243}244245246export function recordSync(db: Database, name: string, at: number, error: string | null): void {247 const before = db248 .query<{ last_sync_error: string | null }, [string]>(249 "SELECT last_sync_error FROM repos WHERE name = ?",250 )251 .get(name);252 if (error && !before?.last_sync_error) {253 notify(db, "sync_failed", `Sync failed for ${name}: ${error}`, `/admin/repos?repo=${name}`);254 }255 db.query("UPDATE repos SET last_sync_at = ?, last_sync_error = ? WHERE name = ?").run(256 at,257 error,258 name,259 );260}261262export function publicRepoNames(db: Database): string[] {263 return db264 .query<{ name: string }, []>("SELECT name FROM repos WHERE public = 1 ORDER BY name")265 .all()266 .map((row) => row.name);267}268269270export function publicRepo(db: Database, name: string): RepoSettings | null {271 const row = db272 .query<RepoRow, [string]>(273 `SELECT name, category, homepage, languages, issues_paused, description, details274 FROM repos LEFT JOIN repo_text ON repo_text.repo = repos.name275 WHERE name = ? AND public = 1`,276 )277 .get(name);278 if (!row) return null;279 return {280 name: row.name,281 category: row.category,282 homepage: row.homepage,283 languages: row.languages === "" ? [] : row.languages.split(", "),284 issuesPaused: row.issues_paused === 1,285 description: row.description ?? "",286 details: row.details ?? "",287 };288}289290291292293294export function hiddenPaths(db: Database, repo: string): string[] {295 const row = db296 .query<{ hidden_paths: string }, [string]>("SELECT hidden_paths FROM repos WHERE name = ?")297 .get(repo);298 const paths = row?.hidden_paths ? row.hidden_paths.split("\n") : [];299 return [...paths, ...ignoredNames(db).map((name) => `${ANY_DEPTH}${name}`)];300}301302303export function ignoredNames(db: Database): string[] {304 return setting(db, "ignored")?.split("\n").filter(Boolean) ?? [];305}306307308export function repoText(db: Database, repo: string): Record<RepoText, string> {309 return (310 db311 .query<Record<RepoText, string>, [string]>(312 "SELECT description, details FROM repo_text WHERE repo = ?",313 )314 .get(repo) ?? { description: "", details: "" }315 );316}317318319export function setRepoText(db: Database, repo: string, field: RepoText, value: string): void {320 db.query("INSERT OR IGNORE INTO repo_text (repo) VALUES (?)").run(repo);321 322 db.query(`UPDATE repo_text SET ${field} = ? WHERE repo = ?`).run(value, repo);323}