import { spawn } from "node:child_process"; import { readdirSync, rmSync, statSync, unlinkSync, writeFileSync, } from "node:fs"; import { tmpdir } from "node:os"; import { join } from "node:path"; import { fileURLToPath } from "node:url"; import { DEFAULT_TIMEOUT_MS, MAX_INLINE_ROWS, MAX_SQL_LENGTH, MAX_TIMEOUT_MS, SPILL_MAX_AGE_HOURS, } from "./guide.ts"; import type { StatementKind } from "./sql.ts"; import { classify, guard, guidanceFor, referencedTables } from "./sql.ts"; import type { JournalEntry, JournalMode, JournalRow, MemoryStore, } from "./store.ts"; export type JsonCell = string | number | boolean | null; function cell(value: unknown): JsonCell { if (value instanceof Uint8Array) return ``; if (typeof value === "bigint") return value.toString(); if (value === null) return null; if (typeof value === "string") return value; if (typeof value === "number") return Number.isFinite(value) ? value : String(value); if (typeof value === "boolean") return value; return String(value); } export type SqlOutcome = { columns: string[]; rows: JsonCell[][]; rowCount: number; truncated: boolean; spillPath: string | null; changes: number; durationMs: number; tablesTouched: string[]; schemaChanged: boolean; attachedEscaped: boolean; journalSeq: number; }; export type RunOptions = { actor: "agent" | "human"; readonly?: boolean; params?: unknown[]; timeoutMs?: number; /** Explicit viewer journal modes; write/read/ddl derive from the SQL. */ mode?: "undo" | "fix"; /** Set false for internal calls that must not consume undo capacity. */ snapshot?: boolean; }; const SPILL_PREFIX = "pi-ext-memory-"; const JOURNAL_SCAN = 200; function workerPath(): string { // Dist packages emit extension chunks with .ts names but Node entries as // flat .js files; resolve the built worker first, then the source sibling. const built = fileURLToPath(new URL("./sql-worker.js", import.meta.url)); if (statSync(built, { throwIfNoEntry: false })) return built; return fileURLToPath(new URL("../sql-worker.ts", import.meta.url)); } type WorkerResponse = { ok: boolean; columns?: string[]; rows?: unknown[][]; rowCount?: number; truncated?: boolean; changes?: number; durationMs: number; attached: string[]; error?: string; }; function dispatchWorker( dbPath: string, sql: string, params: unknown[], readonly: boolean, timeoutMs: number, ): Promise { return new Promise((resolve, reject) => { // A child process, not a worker thread: SIGKILL is the only reliable way // to stop a statement stuck inside native SQLite code. let stdout = ""; let settled = false; const child = spawn(process.execPath, [ workerPath(), JSON.stringify({ dbPath, sql, params, readonly }), ]); const finish = (fn: () => void) => { if (settled) return; settled = true; clearTimeout(timer); child.kill("SIGKILL"); fn(); }; const timer = setTimeout(() => { finish(() => reject( new Error( `Statement exceeded ${timeoutMs} ms and was killed. Split it into smaller batches (bounded UPDATE/DELETE loops), or pass a larger timeoutMs (up to ${MAX_TIMEOUT_MS}). Long statements also hold the write lock and stall other sessions.`, ), ), ); }, timeoutMs); child.stdout?.on("data", (chunk: Buffer) => { stdout += chunk.toString(); }); child.on("error", (error) => { finish(() => reject(error)); }); child.on("exit", (code, signal) => { finish(() => { const line = stdout .split("\n") .find((entry) => entry.trim().startsWith("{")); if (line) { try { resolve(JSON.parse(line) as WorkerResponse); return; } catch { // Fall through to the process-level failure below. } } reject( new Error( `SQL worker failed (exit ${code ?? "null"}${signal ? `, signal ${signal}` : ""}) without a response`, ), ); }); }); }); } function spillRows(rows: unknown[][]): string { const path = join( tmpdir(), `${SPILL_PREFIX}${Date.now()}-${Math.random().toString(36).slice(2, 8)}.ndjson`, ); writeFileSync( path, rows.map((row) => JSON.stringify(row)).join("\n") + "\n", { mode: 0o600 }, ); return path; } export function pruneSpills(now: Date): number { let removed = 0; const cutoff = now.getTime() - SPILL_MAX_AGE_HOURS * 3_600_000; try { for (const name of readdirSync(tmpdir())) { if (!name.startsWith(SPILL_PREFIX) || !name.endsWith(".ndjson")) continue; const full = join(tmpdir(), name); const info = statSync(full, { throwIfNoEntry: false }); if (!info || info.mtimeMs < cutoff) { try { if (!info) unlinkSync(full); else rmSync(full, { force: true }); removed++; } catch { // A missing spill file survives this pass. } } } } catch { // Unreadable temp dir simply means nothing pruned. } return removed; } /** * Execute one SQL call end to end: classify, guard, snapshot for undo, * dispatch to the worker, journal the outcome. Throws guiding errors; the * journal records every attempt including denials and failures. */ export async function runSql( store: MemoryStore, sql: string, options: RunOptions, ): Promise { if (typeof sql !== "string" || !sql.trim()) throw new Error( "sql is required; write one SQLite statement (or several separated by ';')", ); if (sql.length > MAX_SQL_LENGTH) throw new Error( `sql exceeds ${MAX_SQL_LENGTH} characters; split the work into several calls`, ); const readonly = options.readonly ?? false; const kind: StatementKind = classify(sql); const mode: JournalMode = options.mode ?? (readonly ? "read" : kind === "ddl" ? "ddl" : "write"); const decision = guard(sql, kind); const known = store.knownTables(); const tables = referencedTables(sql, new Set(known)); const started = Date.now(); const base: JournalEntry = { ts: new Date().toISOString(), actor: options.actor, mode, readonly, sql, params: JSON.stringify(options.params ?? []), rowsReturned: 0, rowsWritten: 0, spilledTo: null, durationMs: 0, ok: false, error: null, tablesTouched: JSON.stringify(tables), schemaChanged: kind === "ddl", attachedEscaped: false, undoPath: null, }; if (!decision.ok) { base.durationMs = Date.now() - started; base.error = `${decision.reason}: ${decision.guidance}`; const seq = store.appendJournal(base); throw new Error( `${decision.reason}. ${decision.guidance} (journaled #${seq})`, ); } let undoPath: string | null = null; if (!readonly && options.snapshot !== false) { undoPath = store.snapshotForUndo(); base.undoPath = undoPath; } let response: WorkerResponse; try { response = await dispatchWorker( store.path, sql, options.params ?? [], readonly, options.timeoutMs ?? DEFAULT_TIMEOUT_MS, ); } catch (error) { base.durationMs = Date.now() - started; base.undoPath = undoPath; base.error = guidanceFor(error, known); const seq = store.appendJournal(base); const failure = new Error(`${base.error} (journaled #${seq})`); throw failure; } base.durationMs = response.durationMs; base.rowsReturned = response.rowCount ?? 0; base.rowsWritten = response.changes ?? 0; base.ok = response.ok; base.attachedEscaped = response.attached.length > 0; if (!response.ok) { base.error = guidanceFor( new Error(response.error ?? "unknown SQLite error"), known, ); const seq = store.appendJournal(base); throw new Error(`${base.error} (journaled #${seq})`); } if (base.attachedEscaped) base.error = `attached schemas detected post-exec: ${response.attached.join(", ")}; they were not journaled`; const truncated = response.truncated ?? false; const allRows = response.rows ?? []; if (truncated || allRows.length > MAX_INLINE_ROWS) base.spilledTo = spillRows(allRows); const seq = store.appendJournal(base); const rows = (response.rows ?? []).map((row) => row.map(cell)); return { columns: response.columns ?? [], rows: base.spilledTo ? rows.slice(0, MAX_INLINE_ROWS) : rows, rowCount: response.rowCount ?? 0, truncated: Boolean(base.spilledTo) || truncated, spillPath: base.spilledTo, changes: response.changes ?? 0, durationMs: response.durationMs, tablesTouched: tables, schemaChanged: kind === "ddl", attachedEscaped: base.attachedEscaped, journalSeq: seq, }; } /** Undo one journaled write/ddl entry by restoring its pre-write snapshot. */ export function undoEntry( store: MemoryStore, seq: number, ): { ok: boolean; restored: string[]; failed: string[] } { const entry = store .recentJournal(JOURNAL_SCAN) .find( (row: JournalRow) => row.seq === seq && row.ok === 1 && row.undone === 0 && (row.mode === "write" || row.mode === "ddl"), ); if (!entry) return { ok: false, restored: [], failed: [ `#${seq} has no live undo: only successful, not-yet-undone write/ddl entries can be undone, and the journal keeps ${JOURNAL_SCAN} recent entries`, ], }; if (!entry.undoPath) return { ok: false, restored: [], failed: [ "no snapshot exists for this entry (database exceeded the undo budget at write time)", ], }; const result = store.restoreSnapshot(entry.undoPath); const ok = result.failed.length === 0; store.markUndone(seq); store.appendJournal({ ts: new Date().toISOString(), actor: "human", mode: "undo", readonly: false, sql: `undo #${seq}: ${entry.sql.slice(0, 200)}`, params: "[]", rowsReturned: 0, rowsWritten: 0, spilledTo: null, durationMs: 0, ok, error: ok ? null : result.failed.join("; "), tablesTouched: entry.tablesTouched, schemaChanged: false, attachedEscaped: false, undoPath: null, }); store.pruneSnapshots(new Date()); return { ok, restored: result.restored, failed: result.failed }; }