Repository files, collaboration, releases and project activity.
archforge-external-status / src/database.jsmain
▤ src/database.js4966 bytes
49 lines · Select a line number for a permalink
1
'use strict';2
const fs=require('node:fs');const path=require('node:path');const {DatabaseSync}=require('node:sqlite');3
function openDatabase(file){if(file!==':memory:')fs.mkdirSync(path.dirname(file),{recursive:true,mode:0o750});const db=new DatabaseSync(file); db.exec('PRAGMA journal_mode=WAL; PRAGMA foreign_keys=ON; PRAGMA busy_timeout=5000;');init(db);return db;}4
function init(db){db.exec(`5
CREATE TABLE IF NOT EXISTS admin(id INTEGER PRIMARY KEY CHECK(id=1), username TEXT NOT NULL, password_hash TEXT NOT NULL, totp_encrypted TEXT NOT NULL);6
CREATE TABLE IF NOT EXISTS sessions(token_hash TEXT PRIMARY KEY, csrf TEXT NOT NULL, expires_at INTEGER NOT NULL, created_at INTEGER NOT NULL);7
CREATE TABLE IF NOT EXISTS services(id TEXT PRIMARY KEY, label TEXT NOT NULL, description TEXT NOT NULL, configured INTEGER NOT NULL DEFAULT 1, category TEXT NOT NULL DEFAULT 'Core', current_state TEXT NOT NULL DEFAULT 'unknown', failures INTEGER NOT NULL DEFAULT 0, successes INTEGER NOT NULL DEFAULT 0, last_checked INTEGER, last_latency INTEGER);8
CREATE TABLE IF NOT EXISTS samples(id INTEGER PRIMARY KEY AUTOINCREMENT, service_id TEXT NOT NULL REFERENCES services(id), checked_at INTEGER NOT NULL, success INTEGER NOT NULL, latency_ms INTEGER, warning INTEGER NOT NULL DEFAULT 0, detail TEXT NOT NULL DEFAULT '');9
CREATE INDEX IF NOT EXISTS idx_samples_service_date ON samples(service_id,checked_at);10
CREATE TABLE IF NOT EXISTS incidents(id INTEGER PRIMARY KEY AUTOINCREMENT, service_id TEXT REFERENCES services(id), title TEXT NOT NULL, description TEXT NOT NULL, state TEXT NOT NULL CHECK(state IN ('investigating','identified','monitoring','resolved')), severity TEXT NOT NULL CHECK(severity IN ('degraded','outage')), automatic INTEGER NOT NULL DEFAULT 0, started_at INTEGER NOT NULL, resolved_at INTEGER);11
CREATE INDEX IF NOT EXISTS idx_incidents_active ON incidents(resolved_at, started_at);12
CREATE TABLE IF NOT EXISTS incident_updates(id INTEGER PRIMARY KEY AUTOINCREMENT, incident_id INTEGER NOT NULL REFERENCES incidents(id) ON DELETE CASCADE, state TEXT NOT NULL, body TEXT NOT NULL, created_at INTEGER NOT NULL);13
CREATE TABLE IF NOT EXISTS maintenance(id INTEGER PRIMARY KEY AUTOINCREMENT, title TEXT NOT NULL, description TEXT NOT NULL, starts_at INTEGER NOT NULL, ends_at INTEGER NOT NULL, service_id TEXT REFERENCES services(id), created_at INTEGER NOT NULL);14
CREATE TABLE IF NOT EXISTS subscriptions(id INTEGER PRIMARY KEY AUTOINCREMENT, email TEXT NOT NULL UNIQUE COLLATE NOCASE, confirmed INTEGER NOT NULL DEFAULT 0, confirm_hash TEXT, confirm_until INTEGER, unsubscribe_hash TEXT NOT NULL, created_at INTEGER NOT NULL);15
CREATE TABLE IF NOT EXISTS messages(id INTEGER PRIMARY KEY AUTOINCREMENT, channel TEXT NOT NULL CHECK(channel IN ('discord','email')), destination TEXT NOT NULL, subject TEXT NOT NULL, body TEXT NOT NULL, created_at INTEGER NOT NULL, attempts INTEGER NOT NULL DEFAULT 0, next_at INTEGER NOT NULL, delivered_at INTEGER, last_error TEXT);16
CREATE INDEX IF NOT EXISTS idx_messages_queue ON messages(delivered_at,next_at);17
CREATE TABLE IF NOT EXISTS setting(key TEXT PRIMARY KEY,value TEXT NOT NULL);18
CREATE TABLE IF NOT EXISTS audit(id INTEGER PRIMARY KEY AUTOINCREMENT, event TEXT NOT NULL, created_at INTEGER NOT NULL);19
-- Canonical public brand labels stay correct even when an older process20
-- writes the original names into the same database. IDs stay stable.21
CREATE TRIGGER IF NOT EXISTS archforge_services_branding_insert22
AFTER INSERT ON services23
WHEN NEW.id IN ('git_ssh','git_http')24
BEGIN25
UPDATE services SET26
label=CASE NEW.id WHEN 'git_ssh' THEN 'ArchForge SSH' ELSE 'ArchForge HTTPS' END,27
category='ArchForge'28
WHERE id=NEW.id;29
END;30
CREATE TRIGGER IF NOT EXISTS archforge_services_branding_update31
AFTER UPDATE OF label,category ON services32
WHEN NEW.id IN ('git_ssh','git_http') AND33
(NEW.label != CASE NEW.id WHEN 'git_ssh' THEN 'ArchForge SSH' ELSE 'ArchForge HTTPS' END34
OR NEW.category != 'ArchForge')35
BEGIN36
UPDATE services SET37
label=CASE NEW.id WHEN 'git_ssh' THEN 'ArchForge SSH' ELSE 'ArchForge HTTPS' END,38
category='ArchForge'39
WHERE id=NEW.id;40
END;41
`);}42
function syncServices(db,services){const stmt=db.prepare('INSERT INTO services(id,label,description,configured,category) VALUES(?,?,?,?,?) ON CONFLICT(id) DO UPDATE SET label=excluded.label,description=excluded.description,configured=excluded.configured,category=excluded.category');for(const s of services)stmt.run(s.id,s.label,s.description,s.configured?1:0,s.category||'Core');}43
function rows(db,sql,...p){return db.prepare(sql).all(...p);}44
function one(db,sql,...p){return db.prepare(sql).get(...p);}45
function run(db,sql,...p){return db.prepare(sql).run(...p);}46
function transaction(db,fn){db.exec('BEGIN IMMEDIATE');try{const r=fn();db.exec('COMMIT');return r;}catch(e){db.exec('ROLLBACK');throw e;}}47
function now(){return Math.floor(Date.now()/1000);}48
module.exports={openDatabase,init,syncServices,rows,one,run,transaction,now};