repositories / pi-ext
pi-ext
bugabingas pi extensions
owned by admin
extensions/devdocs/database-worker.ts
Rawimport { createHash } from "node:crypto";
import { chmodSync, lstatSync, mkdirSync } from "node:fs";
import { dirname } from "node:path";
import { DatabaseSync } from "node:sqlite";
import type { Document } from "./src/types.ts";
// All synchronous SQLite work is confined to this child, never Pi's event loop.
const APP_ID = 0x50444443;
let db: DatabaseSync | undefined;
function resetOwnedCache(connection: DatabaseSync): void {
const id = Number(
connection.prepare("PRAGMA application_id").get()?.["application_id"],
);
if (id !== APP_ID) throw new Error("database is not a devdocs cache");
connection.exec(
"BEGIN IMMEDIATE; DROP TABLE IF EXISTS section_fts; DROP TABLE IF EXISTS sections; DROP TABLE IF EXISTS docs; DROP TABLE IF EXISTS leases; DROP TABLE IF EXISTS lookups; DROP TABLE IF EXISTS accounting; COMMIT;",
);
}
function database(path: string): DatabaseSync {
if (db) return db;
mkdirSync(dirname(path), { recursive: true, mode: 0o700 });
const info = lstatSync(path, { throwIfNoEntry: false });
if (info && (!info.isFile() || info.isSymbolicLink()))
throw new Error("unsafe database file");
const next = new DatabaseSync(path);
try {
next.exec("PRAGMA busy_timeout=3000; PRAGMA foreign_keys=ON;");
const id = Number(
next.prepare("PRAGMA application_id").get()?.["application_id"],
);
const tables = Number(
next
.prepare("SELECT count(*) AS n FROM sqlite_master WHERE type='table'")
.get()?.["n"],
);
if ((id !== 0 && id !== APP_ID) || (id === 0 && tables !== 0))
throw new Error("database is not a devdocs cache");
// Rollback journaling avoids depending on bundled SQLite WAL bug-fix levels.
const pageSize = Number(
next.prepare("PRAGMA page_size").get()?.["page_size"],
);
const pages = Number(
next.prepare("PRAGMA page_count").get()?.["page_count"],
);
if (
!Number.isSafeInteger(pageSize) ||
pageSize < 512 ||
pages * pageSize > 1073741824
)
throw new Error("capacity: physical database exceeds 1 GiB bound");
next.exec(
`PRAGMA journal_mode=DELETE; PRAGMA temp_store=MEMORY; PRAGMA max_page_count=${Math.floor(1073741824 / pageSize)}; PRAGMA auto_vacuum=INCREMENTAL;`,
);
const version = Number(
next.prepare("PRAGMA user_version").get()?.["user_version"],
);
if (
id === APP_ID &&
(version !== 2 ||
next.prepare("PRAGMA quick_check(1)").get()?.["quick_check"] !== "ok")
)
resetOwnedCache(next);
next.exec(`
BEGIN IMMEDIATE;
PRAGMA application_id=${APP_ID}; PRAGMA user_version=2;
CREATE TABLE IF NOT EXISTS docs(revision TEXT PRIMARY KEY, key TEXT NOT NULL, source TEXT NOT NULL, target TEXT NOT NULL, version TEXT, origin TEXT NOT NULL, candidate TEXT NOT NULL, attribution TEXT NOT NULL, validator TEXT, published INTEGER NOT NULL, checked INTEGER NOT NULL, accessed INTEGER NOT NULL, bytes INTEGER NOT NULL, current INTEGER NOT NULL);
CREATE UNIQUE INDEX IF NOT EXISTS heads ON docs(key) WHERE current=1;
CREATE INDEX IF NOT EXISTS lru ON docs(accessed, revision);
CREATE TABLE IF NOT EXISTS sections(id INTEGER PRIMARY KEY, revision TEXT NOT NULL REFERENCES docs(revision) ON DELETE CASCADE, ordinal INTEGER NOT NULL, heading TEXT NOT NULL, body TEXT NOT NULL);
CREATE INDEX IF NOT EXISTS section_revision ON sections(revision,ordinal);
CREATE VIRTUAL TABLE IF NOT EXISTS section_fts USING fts5(heading, body, content='sections', content_rowid='id');
CREATE TRIGGER IF NOT EXISTS section_insert AFTER INSERT ON sections BEGIN INSERT INTO section_fts(rowid,heading,body) VALUES(new.id,new.heading,new.body); END;
CREATE TRIGGER IF NOT EXISTS section_delete AFTER DELETE ON sections BEGIN INSERT INTO section_fts(section_fts,rowid,heading,body) VALUES('delete',old.id,old.heading,old.body); END;
CREATE TABLE IF NOT EXISTS leases(key TEXT PRIMARY KEY, owner TEXT NOT NULL, expires INTEGER NOT NULL);
CREATE INDEX IF NOT EXISTS lease_expiry ON leases(expires);
CREATE TABLE IF NOT EXISTS lookups(key TEXT PRIMARY KEY, candidates TEXT NOT NULL, checked INTEGER NOT NULL, bytes INTEGER NOT NULL);
CREATE INDEX IF NOT EXISTS lookup_lru ON lookups(checked,key);
CREATE TABLE IF NOT EXISTS accounting(id INTEGER PRIMARY KEY CHECK(id=1), bytes INTEGER NOT NULL);
INSERT OR IGNORE INTO accounting VALUES(1,0);
CREATE TRIGGER IF NOT EXISTS docs_insert AFTER INSERT ON docs BEGIN UPDATE accounting SET bytes=bytes+new.bytes WHERE id=1; END;
CREATE TRIGGER IF NOT EXISTS docs_delete AFTER DELETE ON docs BEGIN UPDATE accounting SET bytes=bytes-old.bytes WHERE id=1; END;
CREATE TRIGGER IF NOT EXISTS lookup_insert AFTER INSERT ON lookups BEGIN UPDATE accounting SET bytes=bytes+new.bytes WHERE id=1; END;
CREATE TRIGGER IF NOT EXISTS lookup_delete AFTER DELETE ON lookups BEGIN UPDATE accounting SET bytes=bytes-old.bytes WHERE id=1; END;
COMMIT;
`);
chmodSync(path, 0o600);
db = next;
return next;
} catch (error) {
next.close();
throw error;
}
}
function metadata(value: string): unknown {
try {
return JSON.parse(value);
} catch {
throw new Error("corrupt cache metadata");
}
}
function sha(value: string): string {
return createHash("sha256").update(value).digest("hex");
}
function run(
path: string,
operation: string,
a: Record<string, unknown>,
): unknown {
const d = database(path);
const now = Number(a["now"] ?? Date.now());
switch (operation) {
case "limit": {
const capacity = Number(a["capacity"]);
d.exec("BEGIN IMMEDIATE");
try {
let count = 0;
while (
Number(
d.prepare("SELECT bytes FROM accounting WHERE id=1").get()?.[
"bytes"
],
) > capacity
) {
if (++count > 1000)
throw new Error(
"capacity: cache reduction exceeds bounded eviction batch",
);
const lookup = d
.prepare("SELECT key FROM lookups ORDER BY checked,key LIMIT 1")
.get();
if (lookup)
d.prepare("DELETE FROM lookups WHERE key=?").run(
String(lookup["key"]),
);
else {
const doc = d
.prepare(
"SELECT revision FROM docs WHERE key NOT IN (SELECT key FROM leases WHERE expires>?) ORDER BY accessed,revision LIMIT 1",
)
.get(now);
if (!doc)
throw new Error(
"capacity: active refresh protects cache content",
);
d.prepare("DELETE FROM docs WHERE revision=?").run(
String(doc["revision"]),
);
}
}
d.exec("COMMIT");
return true;
} catch (error) {
d.exec("ROLLBACK");
throw error;
}
}
case "status":
return d
.prepare(
"SELECT bytes,(SELECT count(*) FROM docs) AS revisions,(SELECT count(*) FROM leases WHERE expires>?) AS refreshing FROM accounting WHERE id=1",
)
.get(now);
case "head":
return (
d
.prepare(
"SELECT revision,validator,checked FROM docs WHERE key=? AND current=1",
)
.get(String(a["key"])) ?? null
);
case "discovery": {
const row = d
.prepare("SELECT candidates,checked FROM lookups WHERE key=?")
.get(String(a["key"]));
if (!row) return null;
const candidates = metadata(String(row["candidates"]));
if (
!Array.isArray(candidates) ||
candidates.length > 20 ||
candidates.some(
(candidate) =>
!candidate ||
typeof candidate !== "object" ||
["id", "target", "source", "origin", "scope"].some(
(key) => typeof candidate[key] !== "string",
),
)
)
throw new Error("corrupt cache metadata");
return { candidates, checked: Number(row["checked"]) };
}
case "remember": {
const key = String(a["key"]);
let candidates = a["candidates"];
if (a["merge"]) {
const prior = d
.prepare("SELECT candidates FROM lookups WHERE key=?")
.get(key);
const previous = prior ? metadata(String(prior["candidates"])) : [];
if (!Array.isArray(previous) || !Array.isArray(candidates))
throw new Error("corrupt cache metadata");
const unique = new Map<string, unknown>();
for (const candidate of [...candidates, ...previous]) {
if (
!candidate ||
typeof candidate !== "object" ||
!("id" in candidate) ||
!("origin" in candidate) ||
!("scope" in candidate)
)
throw new Error("corrupt cache metadata");
const identity = JSON.stringify([
candidate.id,
candidate.origin,
candidate.scope,
]);
if (!unique.has(identity)) unique.set(identity, candidate);
}
candidates = [...unique.values()].slice(0, 20);
}
const content = JSON.stringify(candidates);
const bytes = Buffer.byteLength(content) * 2 + 256;
if (bytes > Number(a["capacity"]) / 4)
throw new Error("capacity: discovery too large");
d.exec("BEGIN IMMEDIATE");
try {
d.prepare("DELETE FROM lookups WHERE key=?").run(key);
d.prepare("INSERT INTO lookups VALUES(?,?,?,?)").run(
key,
content,
now,
bytes,
);
d.prepare(
"DELETE FROM lookups WHERE key IN (SELECT key FROM lookups ORDER BY checked DESC,key LIMIT -1 OFFSET 1000)",
).run();
let victims = 0;
while (
Number(
d.prepare("SELECT bytes FROM accounting WHERE id=1").get()?.[
"bytes"
],
) > Number(a["capacity"])
) {
const victim = d
.prepare(
"SELECT key FROM lookups WHERE key<>? ORDER BY checked,key LIMIT 1",
)
.get(key);
if (!victim || ++victims > 1000)
throw new Error("capacity: discovery cannot fit");
d.prepare("DELETE FROM lookups WHERE key=?").run(
String(victim["key"]),
);
}
d.exec("COMMIT");
return true;
} catch (error) {
d.exec("ROLLBACK");
throw error;
}
}
case "lease": {
d.prepare("DELETE FROM leases WHERE expires<=?").run(now);
d.prepare(
"INSERT INTO leases VALUES(?,?,?) ON CONFLICT(key) DO UPDATE SET owner=excluded.owner,expires=excluded.expires WHERE leases.expires<=?",
).run(String(a["key"]), String(a["owner"]), now + 60000, now);
return (
d
.prepare("SELECT owner FROM leases WHERE key=?")
.get(String(a["key"]))?.["owner"] === a["owner"]
);
}
case "release":
d.prepare("DELETE FROM leases WHERE key=? AND owner=?").run(
String(a["key"]),
String(a["owner"]),
);
return true;
case "validate": {
const result = d
.prepare(
"UPDATE docs SET checked=? WHERE key=? AND revision=? AND current=1 AND EXISTS(SELECT 1 FROM leases WHERE key=? AND owner=? AND expires>?)",
)
.run(
now,
String(a["key"]),
String(a["revision"]),
String(a["key"]),
String(a["owner"]),
now,
);
if (Number(result.changes) !== 1)
throw new Error("expired refresh lease");
return true;
}
case "put": {
const doc = a["document"] as Document;
const key = String(a["key"]);
const revision = sha(
JSON.stringify([key, doc.sections, doc.attribution]),
);
// Logical accounting charges UTF-8 payload plus a conservative equal-size FTS allowance.
const bytes =
Buffer.byteLength(JSON.stringify(doc)) * 2 + doc.sections.length * 256;
const capacity = Number(a["capacity"]);
if (bytes > capacity)
throw new Error("capacity: document exceeds cache limit");
d.exec("BEGIN IMMEDIATE");
try {
if (
a["owner"] &&
!d
.prepare(
"SELECT key FROM leases WHERE key=? AND owner=? AND expires>?",
)
.get(key, String(a["owner"]), now)
)
throw new Error("expired refresh lease");
if (
d.prepare("SELECT revision FROM docs WHERE revision=?").get(revision)
) {
d.prepare("UPDATE docs SET current=0 WHERE key=?").run(key);
d.prepare(
"UPDATE docs SET current=1,checked=?,accessed=?,validator=? WHERE revision=?",
).run(now, now, doc.validator, revision);
} else {
let count = 0;
while (
Number(
d.prepare("SELECT bytes FROM accounting WHERE id=1").get()?.[
"bytes"
],
) +
bytes >
capacity
) {
const victim = d
.prepare(
"SELECT revision FROM docs WHERE (key<>? OR current=0) AND (current=0 OR key NOT IN (SELECT key FROM leases WHERE expires>?)) ORDER BY accessed,revision LIMIT 1",
)
.get(key, now);
if (!victim || ++count > 1000)
throw new Error("capacity: cannot replace protected revision");
d.prepare("DELETE FROM docs WHERE revision=?").run(
String(victim["revision"]),
);
}
d.prepare("UPDATE docs SET current=0 WHERE key=?").run(key);
d.prepare("INSERT INTO docs VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,1)").run(
revision,
key,
doc.candidate.source,
doc.candidate.target,
doc.candidate.version,
doc.candidate.origin,
JSON.stringify(doc.candidate),
doc.attribution,
doc.validator,
now,
now,
now,
bytes,
);
const insert = d.prepare(
"INSERT INTO sections(revision,ordinal,heading,body) VALUES(?,?,?,?)",
);
doc.sections.forEach((s, i) =>
insert.run(revision, i, s.heading, s.body),
);
}
d.exec("COMMIT");
return revision;
} catch (error) {
d.exec("ROLLBACK");
throw error;
}
}
case "search": {
const terms =
String(a["query"])
.match(/[\p{L}\p{N}_-]+/gu)
?.slice(0, 20) ?? [];
if (!terms.length) return [];
const expression = terms
.map((t) => '"' + t.replaceAll('"', '""') + '"')
.join(" OR ");
const rows = d
.prepare(
`SELECT s.revision,s.ordinal,s.heading,substr(s.body,1,160) AS snippet,d.target,d.version,d.source,d.origin,d.checked FROM section_fts JOIN sections s ON s.id=section_fts.rowid JOIN docs d ON d.revision=s.revision WHERE section_fts MATCH ? AND d.key=? AND d.current=1 ORDER BY bm25(section_fts,8,1),s.ordinal LIMIT ?`,
)
.all(expression, String(a["key"]), Number(a["limit"]));
if (rows.length)
d.prepare("UPDATE docs SET accessed=? WHERE key=? AND current=1").run(
now,
String(a["key"]),
);
return rows;
}
case "readMany": {
const refs = a["refs"] as {
revision: string;
ordinal: number;
offset?: number;
}[];
if (!Array.isArray(refs) || refs.length > 20)
throw new Error("invalid read set");
d.exec("BEGIN IMMEDIATE");
try {
let bytes = 0;
const rows = refs.map((ref) => {
const row = d
.prepare(
"SELECT s.revision,s.ordinal,s.heading,substr(s.body,?,?) AS body,length(s.body) AS totalLength,d.target,d.version,d.source,d.origin,d.checked AS checkedAt,d.checked,d.candidate,d.attribution FROM sections s JOIN docs d ON d.revision=s.revision WHERE s.revision=? AND s.ordinal=?",
)
.get(
(ref.offset ?? 0) + 1,
Math.min(Number(a["budget"] ?? 64000), 64000),
ref.revision,
ref.ordinal,
);
if (!row) return null;
bytes += Buffer.byteLength(String(row["body"]));
if (bytes > 8 * 1024 * 1024)
throw new Error("read set exceeds bounds");
const candidate = metadata(String(row["candidate"])) as {
scope?: unknown;
requestedVersion?: unknown;
};
if (
!candidate ||
typeof candidate.scope !== "string" ||
(candidate.requestedVersion !== undefined &&
candidate.requestedVersion !== null &&
typeof candidate.requestedVersion !== "string")
)
throw new Error("corrupt cache metadata");
d.prepare("UPDATE docs SET accessed=? WHERE revision=?").run(
now,
ref.revision,
);
return {
...row,
candidate: undefined,
scope: candidate.scope,
requestedVersion: candidate.requestedVersion ?? null,
checkedAt: new Date(Number(row["checkedAt"])).toISOString(),
freshness:
now - Number(row["checked"]) >= Number(a["ttlMs"] ?? 86400000)
? "stale"
: "fresh",
};
});
d.exec("COMMIT");
return rows;
} catch (error) {
d.exec("ROLLBACK");
throw error;
}
}
default:
throw new Error("unknown database operation");
}
}
process.on(
"message",
(message: {
id: number;
path: string;
operation: string;
args: Record<string, unknown>;
}) => {
try {
process.send?.({
id: message.id,
value: run(message.path, message.operation, message.args),
});
} catch (error) {
const text = error instanceof Error ? error.message : "database failure";
if (db && /malformed|corrupt|no such table|no such column/i.test(text)) {
try {
resetOwnedCache(db);
db.close();
db = undefined;
process.send?.({
id: message.id,
value: run(message.path, message.operation, message.args),
});
return;
} catch {
/* Refuse unidentifiable or unrecoverable files; never replace arbitrary user data. */
}
}
const errorCode = /capacity/.test(text)
? "capacity: cache cannot admit document"
: /not a devdocs|unsafe/.test(text)
? "database refused: not a safe devdocs store"
: /lease/.test(text)
? "refresh lease expired"
: "database operation failed; verify path, permissions, space, and cache integrity";
process.send?.({ id: message.id, error: errorCode });
}
},
);
process.on("disconnect", () => {
db?.close();
process.exit(0);
});