Luigit
repositories / pi-ext

pi-ext

bugabingas pi extensions

owned by admin

extensions/memory/src/store.ts

Raw
import {
	closeSync,
	mkdirSync,
	openSync,
	readdirSync,
	statSync,
	unlinkSync,
} from "node:fs";
import { stat, statfs } from "node:fs/promises";
import { basename, dirname, join } from "node:path";
import { DatabaseSync } from "node:sqlite";
import {
	APPLICATION_ID,
	GUIDE_SEED,
	JOURNAL_MAX_AGE_DAYS,
	JOURNAL_MAX_ROWS,
	SCHEMA_DOC_SEED,
	SCHEMA_VERSION,
	UNDO_MAX_AGE_DAYS,
	UNDO_MAX_BYTES,
	UNDO_MAX_SNAPSHOTS,
} from "./guide.ts";

export type JournalMode = "read" | "write" | "ddl" | "undo" | "fix";
export type JournalEntry = {
	ts: string;
	actor: "agent" | "human";
	mode: JournalMode;
	readonly: boolean;
	sql: string;
	params: string;
	rowsReturned: number;
	rowsWritten: number;
	spilledTo: string | null;
	durationMs: number;
	ok: boolean;
	error: string | null;
	tablesTouched: string;
	schemaChanged: boolean;
	attachedEscaped: boolean;
	undoPath: string | null;
};
/** Raw _journal row as read back from SQLite; flag columns arrive as 0/1. */
export type JournalRow = {
	seq: number;
	ts: string;
	actor: "agent" | "human";
	mode: JournalMode;
	readonly: number;
	sql: string;
	params: string;
	rowsReturned: number;
	rowsWritten: number;
	spilledTo: string | null;
	durationMs: number;
	ok: number;
	error: string | null;
	tablesTouched: string;
	schemaChanged: number;
	attachedEscaped: number;
	undoPath: string | null;
	undone: number;
};

export class V1DatabaseError extends Error {
	constructor(message: string) {
		super(message);
		this.name = "V1DatabaseError";
	}
}

export class MemoryStore {
	private db: DatabaseSync;
	readonly path: string;
	private constructor(path: string, db: DatabaseSync) {
		this.path = path;
		this.db = db;
	}

	static async open(path: string): Promise<MemoryStore> {
		mkdirSync(dirname(path), { recursive: true, mode: 0o700 });
		// Owner-private database file, including SQLite's derived WAL files.
		closeSync(openSync(path, "a", 0o600));
		const db = new DatabaseSync(path);
		const store = new MemoryStore(path, db);
		try {
			store.bootstrap();
			return store;
		} catch (error) {
			db.close();
			throw error;
		}
	}

	close(): void {
		this.db.close();
	}

	private bootstrap(): void {
		this.db.exec(
			"PRAGMA busy_timeout=2000; PRAGMA synchronous=FULL; PRAGMA foreign_keys=OFF;",
		);
		const { user_version: version } = this.db
			.prepare("PRAGMA user_version")
			.get() as { user_version: number };
		const { application_id: applicationId } = this.db
			.prepare("PRAGMA application_id")
			.get() as { application_id: number };
		const existing = this.db
			.prepare("SELECT name FROM sqlite_master LIMIT 1")
			.get();
		if (version === 0) {
			if (existing || applicationId !== 0)
				throw new Error(
					"Not an extension-owned memory database; choose a separate file via memory.databasePath",
				);
			this.initialize();
		} else if (version === SCHEMA_VERSION && applicationId === APPLICATION_ID) {
			// Current format.
		} else if (version === 1 && applicationId === APPLICATION_ID) {
			throw new V1DatabaseError(
				"This is a v1 (review-gated) memory database. v2 starts fresh without compatibility: delete or rename the file, or point memory.databasePath at a new one.",
			);
		} else if (version > SCHEMA_VERSION) {
			throw new Error(
				"Memory database schema is newer than this extension; update pi-ext",
			);
		} else {
			throw new Error(
				"Not an extension-owned memory database; choose a separate file via memory.databasePath",
			);
		}
		this.db.exec("PRAGMA journal_mode=WAL");
	}

	private initialize(): void {
		this.db.exec(`
			CREATE TABLE _guide (
				seq INTEGER PRIMARY KEY,
				topic TEXT NOT NULL UNIQUE,
				body TEXT NOT NULL,
				author TEXT NOT NULL
			) STRICT;
			CREATE TABLE _schema_docs (
				"table" TEXT PRIMARY KEY,
				purpose TEXT NOT NULL,
				columns TEXT NOT NULL,
				kind TEXT NOT NULL,
				author TEXT NOT NULL
			) STRICT;
			CREATE TABLE _journal (
				seq INTEGER PRIMARY KEY,
				ts TEXT NOT NULL,
				actor TEXT NOT NULL CHECK(actor IN ('agent','human')),
				mode TEXT NOT NULL CHECK(mode IN ('read','write','ddl','undo','fix')),
				readonly INTEGER NOT NULL CHECK(readonly IN (0,1)),
				sql TEXT NOT NULL,
				params TEXT NOT NULL DEFAULT '[]',
				rowsReturned INTEGER NOT NULL DEFAULT 0,
				rowsWritten INTEGER NOT NULL DEFAULT 0,
				spilledTo TEXT,
				durationMs INTEGER NOT NULL DEFAULT 0,
				ok INTEGER NOT NULL CHECK(ok IN (0,1)),
				error TEXT,
				tablesTouched TEXT NOT NULL DEFAULT '[]',
				schemaChanged INTEGER NOT NULL DEFAULT 0,
				attachedEscaped INTEGER NOT NULL DEFAULT 0,
				undoPath TEXT,
				undone INTEGER NOT NULL DEFAULT 0
			) STRICT;
			CREATE INDEX _journal_ts ON _journal(ts, seq);
			CREATE TABLE _stats_samples (
				ts TEXT NOT NULL,
				name TEXT NOT NULL,
				type TEXT NOT NULL CHECK(type IN ('table','index','schema')),
				bytes INTEGER NOT NULL,
				pages INTEGER NOT NULL,
				rows INTEGER
			) STRICT;
			CREATE INDEX _stats_samples_ts ON _stats_samples(ts);
			CREATE TABLE _health_runs (
				ts TEXT NOT NULL,
				verdict TEXT NOT NULL CHECK(verdict IN ('ok','warning','error')),
				checks TEXT NOT NULL
			) STRICT;
		`);
		const insertGuide = this.db.prepare(
			"INSERT OR IGNORE INTO _guide(topic, body, author) VALUES(?,?,'system')",
		);
		for (const row of GUIDE_SEED) insertGuide.run(row.topic, row.body);
		const insertDoc = this.db.prepare(
			'INSERT OR IGNORE INTO _schema_docs("table", purpose, columns, kind, author) VALUES(?,?,?,?,?)',
		);
		for (const row of SCHEMA_DOC_SEED)
			insertDoc.run(row.table, row.purpose, row.columns, row.kind, "system");
		this.db.prepare(`PRAGMA application_id=${APPLICATION_ID}`).run();
		this.db.prepare(`PRAGMA user_version=${SCHEMA_VERSION}`).run();
	}

	userTables(): string[] {
		const rows = this.db
			.prepare(
				"SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%' ORDER BY name",
			)
			.all() as Array<{ name: string }>;
		return rows.map((row) => row.name);
	}

	virtualTables(): string[] {
		const rows = this.db
			.prepare(
				"SELECT name FROM sqlite_master WHERE type='table' AND sql LIKE 'CREATE VIRTUAL TABLE%'",
			)
			.all() as Array<{ name: string }>;
		return rows.map((row) => row.name);
	}

	knownTables(): string[] {
		return this.userTables().filter((name) => !name.startsWith("_"));
	}

	// --- journal ---

	appendJournal(entry: JournalEntry): number {
		const result = this.db
			.prepare(
				`INSERT INTO _journal(ts,actor,mode,readonly,sql,params,rowsReturned,rowsWritten,spilledTo,durationMs,ok,error,tablesTouched,schemaChanged,attachedEscaped,undoPath,undone)
				 VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,0)`,
			)
			.run(
				entry.ts,
				entry.actor,
				entry.mode,
				entry.readonly ? 1 : 0,
				entry.sql,
				entry.params,
				entry.rowsReturned,
				entry.rowsWritten,
				entry.spilledTo,
				entry.durationMs,
				entry.ok ? 1 : 0,
				entry.error,
				entry.tablesTouched,
				entry.schemaChanged ? 1 : 0,
				entry.attachedEscaped ? 1 : 0,
				entry.undoPath,
			);
		return Number(result.lastInsertRowid);
	}

	recentJournal(limit: number): JournalRow[] {
		return this.db
			.prepare("SELECT * FROM _journal ORDER BY seq DESC LIMIT ?")
			.all(limit) as JournalRow[];
	}

	markUndone(seq: number): void {
		this.db.prepare("UPDATE _journal SET undone=1 WHERE seq=?").run(seq);
	}

	pruneRetention(now: Date): { journal: number; snapshots: number } {
		const cutoff = new Date(
			now.getTime() - JOURNAL_MAX_AGE_DAYS * 86_400_000,
		).toISOString();
		const ageRemoved = Number(
			this.db.prepare("DELETE FROM _journal WHERE ts < ?").run(cutoff).changes,
		);
		const overflowRemoved = Number(
			this.db
				.prepare(
					"DELETE FROM _journal WHERE seq NOT IN (SELECT seq FROM _journal ORDER BY seq DESC LIMIT ?)",
				)
				.run(JOURNAL_MAX_ROWS).changes,
		);
		return {
			journal: ageRemoved + overflowRemoved,
			snapshots: this.pruneSnapshots(now),
		};
	}

	// --- undo snapshots ---

	undoDir(): string {
		return join(dirname(this.path), `.${basename(this.path)}-undo`);
	}

	undoCapacity(): { allowed: boolean; reason?: string; bytes: number } {
		const bytes = statSync(this.path, { throwIfNoEntry: false })?.size ?? 0;
		if (bytes > UNDO_MAX_BYTES)
			return {
				allowed: false,
				reason: `database exceeds the ${Math.round(UNDO_MAX_BYTES / 1024 / 1024)} MB undo snapshot budget; writes still journal, but cannot be undone`,
				bytes,
			};
		return { allowed: true, bytes };
	}

	/** Capture a pre-write snapshot via VACUUM INTO; bounds are enforced here. */
	snapshotForUndo(): string | null {
		if (!this.undoCapacity().allowed) return null;
		mkdirSync(this.undoDir(), { recursive: true, mode: 0o700 });
		const path = join(
			this.undoDir(),
			`${Date.now()}-${Math.random().toString(36).slice(2, 8)}.sqlite`,
		);
		this.db.prepare(`VACUUM INTO '${path.replaceAll("'", "''")}'`).run();
		this.pruneSnapshots(new Date());
		return path;
	}

	pruneSnapshots(now: Date): number {
		let names: string[];
		try {
			names = readdirSync(this.undoDir());
		} catch {
			return 0;
		}
		const cutoff = now.getTime() - UNDO_MAX_AGE_DAYS * 86_400_000;
		const snapshots = names
			.map((name) => {
				const full = join(this.undoDir(), name);
				const info = statSync(full, { throwIfNoEntry: false });
				return { full, mtime: info?.mtimeMs ?? 0 };
			})
			.sort((a, b) => b.mtime - a.mtime);
		let removed = 0;
		for (const [index, snapshot] of snapshots.entries()) {
			if (snapshot.mtime < cutoff || index >= UNDO_MAX_SNAPSHOTS) {
				try {
					unlinkSync(snapshot.full);
					removed++;
				} catch {
					// A missing or locked snapshot file simply survives this pass.
				}
			}
		}
		return removed;
	}

	/**
	 * Restore user objects to a snapshot's state inside one transaction.
	 *
	 * ATTACH inside a transaction is legal (SQLite >= 3.21); the snapshot file
	 * uses a rollback journal, so the multi-database transaction commits
	 * atomically with a super-journal. DETACH runs after COMMIT: an attached
	 * btree with an open transaction cannot detach, so detaching first would
	 * always fail. Extension-owned tables (underscore prefix) are never
	 * touched; user tables are brought back to the snapshot state, including
	 * dropping post-snapshot creations and restoring FTS5 virtual tables by
	 * rebuild.
	 */
	restoreSnapshot(snapshotPath: string): {
		restored: string[];
		failed: string[];
	} {
		statSync(snapshotPath);
		const restored: string[] = [];
		const failed: string[] = [];
		const quote = (name: string) => `"${name.replaceAll('"', '""')}"`;
		this.db.exec("BEGIN IMMEDIATE");
		try {
			this.db.prepare(`ATTACH ? AS _undo_snap`).run(snapshotPath);
			const objects = (source: string) =>
				this.db
					.prepare(
						`SELECT type, name, sql FROM ${source}.sqlite_master WHERE sql IS NOT NULL AND name NOT LIKE 'sqlite\\_%' ESCAPE '\\'`,
					)
					.all() as Array<{ type: string; name: string; sql: string }>;
			const RESERVED = /^_/u;
			const mainObjects = objects("main");
			const snapObjects = objects("_undo_snap");
			const isVirtual = (sql: string) => /create\s+virtual\s+table/iu.test(sql);
			const vtabNames = snapObjects
				.filter((object) => object.type === "table" && isVirtual(object.sql))
				.map((object) => object.name);
			const isShadow = (name: string) =>
				vtabNames.some((vtab) => name.startsWith(`${vtab}_`));
			const snapTables = new Set(
				snapObjects
					.filter((object) => object.type === "table")
					.map((object) => object.name),
			);
			// Drop main-only user tables; post-snapshot creations disappear.
			for (const object of mainObjects) {
				if (
					object.type === "table" &&
					!snapTables.has(object.name) &&
					!RESERVED.test(object.name)
				)
					this.db
						.prepare(`DROP TABLE IF EXISTS main.${quote(object.name)}`)
						.run();
			}
			// Drop main-only triggers/indexes/views; post-snapshot scaffolding goes.
			for (const object of mainObjects) {
				if (
					object.type !== "table" &&
					!RESERVED.test(object.name) &&
					!snapObjects.some((snap) => snap.name === object.name)
				)
					this.db
						.prepare(
							`DROP ${object.type.toUpperCase()} IF EXISTS main.${quote(object.name)}`,
						)
						.run();
			}
			// Recreate snapshot tables in main and copy their rows.
			for (const object of snapObjects) {
				if (object.type !== "table" || RESERVED.test(object.name)) continue;
				if (isShadow(object.name)) continue;
				const target = `main.${quote(object.name)}`;
				const source = `_undo_snap.${quote(object.name)}`;
				if (isVirtual(object.sql) || !this.tableExists(object.name)) {
					this.db.prepare(`DROP TABLE IF EXISTS ${target}`).run();
					this.db.exec(object.sql);
					this.db.exec(`INSERT INTO ${target} SELECT * FROM ${source}`);
				} else {
					this.db.prepare(`DELETE FROM ${target}`).run();
					this.db.exec(`INSERT INTO ${target} SELECT * FROM ${source}`);
				}
				restored.push(object.name);
			}
			// Recreate snapshot triggers/indexes/views that are missing.
			for (const object of snapObjects) {
				if (object.type === "table" || RESERVED.test(object.name)) continue;
				if (!this.objectExists(object.name)) {
					this.db.exec(object.sql);
					restored.push(object.name);
				}
			}
			this.db.exec("COMMIT");
		} catch (error) {
			this.db.exec("ROLLBACK");
			failed.push(error instanceof Error ? error.message : String(error));
		} finally {
			try {
				this.db.exec("DETACH _undo_snap");
			} catch {
				// Rollback path may leave the attach pending only until rollback ends.
			}
		}
		return { restored, failed };
	}

	private tableExists(name: string): boolean {
		return (
			this.db
				.prepare("SELECT 1 FROM sqlite_master WHERE type='table' AND name=?")
				.get(name) !== undefined
		);
	}

	private objectExists(name: string): boolean {
		return (
			this.db.prepare("SELECT 1 FROM sqlite_master WHERE name=?").get(name) !==
			undefined
		);
	}

	// --- stats ---

	sampleSizes(): void {
		const now = new Date().toISOString();
		const virtual = this.virtualTables();
		const objects = this.db
			.prepare(
				"SELECT name, COALESCE(SUM(pgsize),0) AS bytes, COUNT(*) AS pages FROM dbstat GROUP BY name ORDER BY name",
			)
			.all() as Array<{ name: string; bytes: number; pages: number }>;
		const types = objectTypes(this.db);
		const insert = this.db.prepare(
			"INSERT INTO _stats_samples(ts,name,type,bytes,pages,rows) VALUES(?,?,?,?,?,?)",
		);
		for (const object of objects) {
			const type = classifyObject(object.name, types);
			// Row counts are cheap only for small ordinary tables; FTS shadows,
			// extension tables, and indexes are skipped.
			let rows: number | null = null;
			if (
				type === "table" &&
				object.pages <= 256 &&
				!object.name.startsWith("_") &&
				!virtual.includes(object.name) &&
				!virtual.some((vtab) => object.name.startsWith(`${vtab}_`))
			)
				rows = rowCount(this.db, object.name);
			insert.run(now, object.name, type, object.bytes, object.pages, rows);
		}
	}

	growth(): Array<{ ts: string; bytes: number }> {
		return this.db
			.prepare(
				"SELECT ts, SUM(bytes) AS bytes FROM _stats_samples GROUP BY ts ORDER BY ts DESC LIMIT 30",
			)
			.all() as Array<{ ts: string; bytes: number }>;
	}

	// --- viewer aggregations ---

	usageTableRows(): UsageTableRow[] {
		const activity = new Map<
			string,
			{ reads: number; writes: number; lastRead: string; lastWrite: string }
		>();
		const bump = (name: string, read: boolean, ts: string) => {
			const entry = activity.get(name) ?? {
				reads: 0,
				writes: 0,
				lastRead: "",
				lastWrite: "",
			};
			if (read) {
				entry.reads++;
				if (!entry.lastRead) entry.lastRead = ts;
			} else {
				entry.writes++;
				if (!entry.lastWrite) entry.lastWrite = ts;
			}
			activity.set(name, entry);
		};
		const entries = this.db
			.prepare(
				"SELECT ts, mode, readonly, tablesTouched FROM _journal WHERE ok=1 ORDER BY ts ASC LIMIT ?",
			)
			.all(JOURNAL_SCAN_LIMIT) as Array<{
			ts: string;
			mode: string;
			readonly: number;
			tablesTouched: string;
		}>;
		for (const entry of entries) {
			let touched: string[] = [];
			try {
				touched = JSON.parse(entry.tablesTouched) as string[];
			} catch {
				touched = [];
			}
			const read = entry.readonly === 1 || entry.mode === "read";
			for (const name of touched) bump(name, read, entry.ts);
		}
		const bytesAndRows = new Map<
			string,
			{ bytes: number; rows: number | null }
		>();
		const objects = this.db
			.prepare(
				"SELECT name, COALESCE(SUM(pgsize),0) AS bytes, COUNT(*) AS pages FROM dbstat GROUP BY name",
			)
			.all() as Array<{ name: string; bytes: number; pages: number }>;
		const virtual = this.virtualTables();
		const types = objectTypes(this.db);
		for (const object of objects) {
			if (classifyObject(object.name, types) !== "table") continue;
			let rows: number | null = null;
			if (
				object.pages <= 256 &&
				!object.name.startsWith("_") &&
				!virtual.includes(object.name) &&
				!virtual.some((vtab) => object.name.startsWith(`${vtab}_`))
			)
				rows = rowCount(this.db, object.name);
			bytesAndRows.set(object.name, { bytes: object.bytes, rows });
		}
		const names = new Set([...activity.keys(), ...bytesAndRows.keys()]);
		const tableRows: UsageTableRow[] = [];
		for (const name of names) {
			const stats = activity.get(name);
			const size = bytesAndRows.get(name);
			const reads = stats?.reads ?? 0;
			const writes = stats?.writes ?? 0;
			tableRows.push({
				name,
				bytes: size?.bytes ?? 0,
				rows: size?.rows ?? null,
				reads,
				writes,
				lastRead: stats?.lastRead ?? "",
				lastWrite: stats?.lastWrite ?? "",
				flag:
					reads === 0 && writes === 0
						? "DEAD"
						: reads === 0
							? "ZOMBIE"
							: reads >= 10 && reads >= writes * 2
								? "HOT"
								: "",
			});
		}
		return tableRows.sort(
			(a, b) =>
				b.reads + b.writes - (a.reads + a.writes) ||
				a.name.localeCompare(b.name),
		);
	}

	callsPerDay(days = 14): Array<{ day: string; calls: number }> {
		const cutoff = new Date(Date.now() - days * 86_400_000)
			.toISOString()
			.slice(0, 10);
		return this.db
			.prepare(
				"SELECT substr(ts,1,10) AS day, COUNT(*) AS calls FROM _journal WHERE ts >= ? GROUP BY day ORDER BY day",
			)
			.all(cutoff) as Array<{ day: string; calls: number }>;
	}

	journalTotals(days = 14): {
		calls: number;
		errors: number;
		timeouts: number;
		busy: number;
		spills: number;
		undos: number;
	} {
		const cutoff = new Date(Date.now() - days * 86_400_000).toISOString();
		const row = this.db
			.prepare(
				`SELECT COUNT(*) AS calls,
						SUM(ok=0) AS errors,
						SUM(ok=0 AND error LIKE '%was killed%') AS timeouts,
						SUM(ok=0 AND (error LIKE '%locked%' OR error LIKE '%busy%')) AS busy,
						SUM(spilledTo IS NOT NULL) AS spills,
						SUM(mode='undo') AS undos
					FROM _journal WHERE ts >= ?`,
			)
			.get(cutoff) as {
			calls: number | null;
			errors: number | null;
			timeouts: number | null;
			busy: number | null;
			spills: number | null;
			undos: number | null;
		};
		return {
			calls: Number(row.calls ?? 0),
			errors: Number(row.errors ?? 0),
			timeouts: Number(row.timeouts ?? 0),
			busy: Number(row.busy ?? 0),
			spills: Number(row.spills ?? 0),
			undos: Number(row.undos ?? 0),
		};
	}

	sizeOverview(): SizeOverview {
		const pageCount = Number(
			(this.db.prepare("PRAGMA page_count").get() as { page_count: number })
				.page_count,
		);
		const freelist = Number(
			(
				this.db.prepare("PRAGMA freelist_count").get() as {
					freelist_count: number;
				}
			).freelist_count,
		);
		const types = objectTypes(this.db);
		const owners = indexOwners(this.db);
		const virtual = new Set(this.virtualTables());
		const raw = this.db
			.prepare(
				"SELECT name, COALESCE(SUM(pgsize),0) AS bytes, COUNT(*) AS pages FROM dbstat GROUP BY name ORDER BY bytes DESC",
			)
			.all() as Array<{ name: string; bytes: number; pages: number }>;
		const objects = raw.map((object) => {
			const type = classifyObject(object.name, types);
			let rows: number | null = null;
			if (
				type === "table" &&
				object.pages <= 256 &&
				!object.name.startsWith("_") &&
				!virtual.has(object.name)
			)
				rows = rowCount(this.db, object.name);
			return {
				name:
					type === "index"
						? `${owners.get(object.name) ?? "?"} · ${object.name}`
						: object.name,
				type,
				bytes: object.bytes,
				pages: object.pages,
				rows,
			};
		});
		return {
			totalBytes: objects.reduce((sum, object) => sum + object.bytes, 0),
			pageCount,
			freelist,
			dataBytes: objects
				.filter((object) => object.type !== "index")
				.reduce((sum, object) => sum + object.bytes, 0),
			indexBytes: objects
				.filter((object) => object.type === "index")
				.reduce((sum, object) => sum + object.bytes, 0),
			objects,
		};
	}

	async healthChecks(force = false): Promise<HealthReport> {
		const checks: HealthCheck[] = [];
		let verdict: "ok" | "warning" | "error" = "ok";
		const raise = (status: "warn" | "error") => {
			if (status === "error") verdict = "error";
			else if (verdict === "ok") verdict = "warning";
		};
		const cached = this.recentHealthRun();
		let integrity = cached;
		if (force || !cached || Date.now() - Date.parse(cached.ts) > 300_000) {
			const result = this.db.prepare("PRAGMA quick_check").get() as {
				quick_check: string;
			};
			integrity = {
				ts: new Date().toISOString(),
				verdict: result.quick_check === "ok" ? "ok" : "error",
				checks: result.quick_check,
			};
			this.db
				.prepare("INSERT INTO _health_runs(ts,verdict,checks) VALUES(?,?,?)")
				.run(integrity.ts, integrity.verdict, integrity.checks);
		}
		const report = integrity ?? {
			ts: new Date().toISOString(),
			verdict: "error",
			checks: "unavailable",
		};
		checks.push(
			report.verdict === "ok"
				? { label: `integrity ${report.checks}`, status: "ok" }
				: {
						label: `integrity: ${report.checks}`,
						status: "error",
						hint: "stop writing; investigate before further use",
					},
		);
		if (report.verdict !== "ok") raise("error");
		const { application_id: applicationId } = this.db
			.prepare("PRAGMA application_id")
			.get() as { application_id: number };
		const { user_version: version } = this.db
			.prepare("PRAGMA user_version")
			.get() as { user_version: number };
		const formatIntact =
			applicationId === APPLICATION_ID && version === SCHEMA_VERSION;
		checks.push(
			formatIntact
				? { label: "format PIME · v2 intact", status: "ok" }
				: {
						label: `unexpected format (id=${applicationId}, v${version})`,
						status: "error",
					},
		);
		if (!formatIntact) raise("error");
		const wal = await stat(`${this.path}-wal`).then(
			(info) => info.size,
			() => 0,
		);
		checks.push(
			wal <= 8 * 1024 * 1024
				? { label: `WAL ${formatBytes(wal)} · checkpointed`, status: "ok" }
				: {
						label: `WAL ${formatBytes(wal)}`,
						status: "warn",
						hint: "c run checkpoint(TRUNCATE)",
					},
		);
		if (wal > 8 * 1024 * 1024) raise("warn");
		const size = this.sizeOverview();
		const freelistRatio =
			size.pageCount > 0 ? size.freelist / size.pageCount : 0;
		checks.push(
			freelistRatio <= 0.05
				? { label: "freelist 0% · no dead pages", status: "ok" }
				: {
						label: `freelist ${Math.round(freelistRatio * 100)}% ≈ ${formatBytes(Math.round((size.freelist / Math.max(1, size.pageCount)) * size.totalBytes))}`,
						status: "warn",
						hint: "v run VACUUM",
					},
		);
		if (freelistRatio > 0.05) raise("warn");
		const totals = this.journalTotals(1);
		checks.push({
			label: `24h: ${totals.calls} calls · ${totals.errors} errors · ${totals.busy} busy · ${totals.timeouts} timeouts`,
			status:
				totals.errors === 0 ? "ok" : totals.errors <= 2 ? "warn" : "error",
			hint:
				totals.errors > 0
					? "see Journal tab; repeated no-such-table → read _schema_docs"
					: undefined,
		});
		if (totals.errors > 2) raise("error");
		else if (totals.errors > 0) raise("warn");
		try {
			const info = await statfs(dirname(this.path));
			const free = Number(info.bavail) * Number(info.bsize);
			checks.push(
				free >= 200 * 1024 * 1024
					? {
							label: `disk ${Math.round(free / 1024 / 1024 / 1024)} GB free · db ${formatBytes(size.totalBytes)}`,
							status: "ok",
						}
					: {
							label: `disk only ${formatBytes(free)} free`,
							status: "warn",
							hint: "archive or export what you no longer need",
						},
			);
			if (free < 200 * 1024 * 1024) raise("warn");
		} catch {
			// statfs can be unavailable on exotic mounts; skip silently.
		}
		return { verdict, checks };
	}

	private recentHealthRun():
		| { ts: string; verdict: string; checks: string }
		| undefined {
		return this.db
			.prepare(
				"SELECT ts, verdict, checks FROM _health_runs ORDER BY ts DESC LIMIT 1",
			)
			.get() as { ts: string; verdict: string; checks: string } | undefined;
	}
}

function rowCount(db: DatabaseSync, name: string): number {
	return Number(
		(
			db
				.prepare(`SELECT COUNT(*) AS n FROM "${name.replaceAll('"', '""')}"`)
				.get() as {
				n: number;
			}
		).n,
	);
}

export type UsageTableRow = {
	name: string;
	bytes: number;
	rows: number | null;
	reads: number;
	writes: number;
	lastRead: string;
	lastWrite: string;
	flag: "" | "HOT" | "ZOMBIE" | "DEAD";
};

export type SizeOverview = {
	totalBytes: number;
	pageCount: number;
	freelist: number;
	dataBytes: number;
	indexBytes: number;
	objects: Array<{
		name: string;
		type: string;
		bytes: number;
		pages: number;
		rows: number | null;
	}>;
};

export type HealthCheck = {
	label: string;
	status: "ok" | "warn" | "error";
	hint?: string | undefined;
};

export type HealthReport = {
	verdict: "ok" | "warning" | "error";
	checks: HealthCheck[];
};

const JOURNAL_SCAN_LIMIT = 800;

function objectTypes(db: DatabaseSync): Map<string, string> {
	return new Map(
		(
			db.prepare("SELECT name, type FROM sqlite_master").all() as Array<{
				name: string;
				type: string;
			}>
		).map((row) => [row.name, row.type]),
	);
}

/**
 * Classify a dbstat object. Autoindexes are absent from the filtered name
 * maps, so anything unknown counts as an index: size attribution works, row
 * counting must never touch it.
 */
function classifyObject(
	name: string,
	types: Map<string, string>,
): "schema" | "table" | "index" | "other" {
	if (name === "sqlite_schema") return "schema";
	const type = types.get(name);
	if (type === "table") return "table";
	if (type === "view" || type === "trigger") return "other";
	return "index";
}

/** Map index objects to their owning table: autoindex prefixes or CREATE ... ON. */
function indexOwners(db: DatabaseSync): Map<string, string> {
	const owners = new Map<string, string>();
	const rows = db
		.prepare("SELECT name, tbl_name FROM sqlite_master WHERE type='index'")
		.all() as Array<{ name: string; tbl_name: string }>;
	for (const row of rows) owners.set(row.name, row.tbl_name);
	return owners;
}

function formatBytes(bytes: number): string {
	if (bytes >= 1024 * 1024 * 1024)
		return `${(bytes / 1024 / 1024 / 1024).toFixed(1)} GB`;
	if (bytes >= 1024 * 1024) return `${(bytes / 1024 / 1024).toFixed(1)} MB`;
	if (bytes >= 1024) return `${Math.round(bytes / 1024)} KiB`;
	return `${bytes} B`;
}

export async function databaseFileBytes(path: string): Promise<number> {
	const info = await stat(path);
	return info.size;
}