import { mkdirSync } from "node:fs";import { join, resolve } from "node:path"; import { createClient, type Client } from "@libsql/client";import { Effect } from "effect"; import { ScopeDbError, type ScopeDb, type ScopeDbHandle, type ScopeWriteEvent,} from "../seams/scope-db"; // ---------------------------------------------------------------------------// libSQL-backed ScopeDb (self-hosted). One SQLite file per scope under// `<root>/<scope>.db`. A control table `__versions` holds a monotonic counter// per user table; every write statement bumps the counters for the tables it// touched and emits a `ScopeWriteEvent` (the runtime forwards that to// LiveChannel). Scope isolation is a separate file per scope — there is no// cross-scope query path.// --------------------------------------------------------------------------- const VERSION_TABLE = "__scope_versions"; // Statements that mutate data. Determines whether a `sql` call bumps versions.const WRITE_RE = /^\s*(insert|update|delete|replace|create|drop|alter)\b/i; // Extract the table names a write statement targets (best-effort: covers the// shapes authored tools produce — INSERT INTO x, UPDATE x, DELETE FROM x,// CREATE TABLE x, REPLACE INTO x).const targetsOf = (sql: string): string[] => { const out = new Set<string>(); const patterns = [ /\binsert\s+(?:or\s+\w+\s+)?into\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi, /\breplace\s+into\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi, /\bupdate\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi, /\bdelete\s+from\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi, /\bcreate\s+table\s+(?:if\s+not\s+exists\s+)?["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi, /\bdrop\s+table\s+(?:if\s+exists\s+)?["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi, /\balter\s+table\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi, ]; for (const re of patterns) { let m: RegExpExecArray | null; while ((m = re.exec(sql)) !== null) { const name = m[1]; if (name && name !== VERSION_TABLE) out.add(name); } } return [...out];}; const toArgs = (values: readonly unknown[]): unknown[] => values.map((v) => (v === undefined ? null : v)); const makeHandle = ( scope: string, client: Client, emit: (event: ScopeWriteEvent) => void,): ScopeDbHandle => { const ensureVersionTable = async () => { await client.execute( `CREATE TABLE IF NOT EXISTS ${VERSION_TABLE} (name TEXT PRIMARY KEY, version INTEGER NOT NULL DEFAULT 0)`, ); }; const bump = async (tables: readonly string[]): Promise<{ table: string; version: number }[]> => { const bumped: { table: string; version: number }[] = []; for (const table of tables) { await client.execute({ sql: `INSERT INTO ${VERSION_TABLE} (name, version) VALUES (?, 1) ON CONFLICT(name) DO UPDATE SET version = version + 1`, args: [table], }); const row = await client.execute({ sql: `SELECT version FROM ${VERSION_TABLE} WHERE name = ?`, args: [table], }); bumped.push({ table, version: Number(row.rows[0]?.version ?? 0) }); } return bumped; }; const runStatement = <Row>( sql: string, args: unknown[], ): Effect.Effect<readonly Row[], ScopeDbError> => Effect.tryPromise({ try: async () => { await ensureVersionTable(); const result = await client.execute({ sql, args: args as never }); if (WRITE_RE.test(sql)) { const targets = targetsOf(sql); if (targets.length > 0) { const bumped = await bump(targets); if (bumped.length > 0) emit({ scope, tables: bumped }); } } // oxlint-disable-next-line executor/no-double-cast -- boundary: libSQL's driver `Row` type does not structurally overlap the caller's generic `Row`; the SQL is the schema contract return result.rows as unknown as readonly Row[]; }, catch: (cause) => new ScopeDbError({ message: `scope-db statement failed for scope ${scope}`, cause }), }); return { sql: <Row = Record<string, unknown>>(strings: TemplateStringsArray, ...values: unknown[]) => { const sql = strings.reduce((acc, part, i) => acc + part + (i < values.length ? "?" : ""), ""); return runStatement<Row>(sql, toArgs(values)); }, exec: <Row = Record<string, unknown>>(sql: string, params: readonly unknown[] = []) => runStatement<Row>(sql, toArgs(params)), tableVersion: (table: string) => Effect.tryPromise({ try: async () => { await ensureVersionTable(); const row = await client.execute({ sql: `SELECT version FROM ${VERSION_TABLE} WHERE name = ?`, args: [table], }); return Number(row.rows[0]?.version ?? 0); }, catch: (cause) => new ScopeDbError({ message: "tableVersion failed", cause }), }), versions: () => Effect.tryPromise({ try: async () => { await ensureVersionTable(); const rows = await client.execute(`SELECT name, version FROM ${VERSION_TABLE}`); const out = new Map<string, number>(); for (const r of rows.rows) out.set(String(r.name), Number(r.version)); return out as ReadonlyMap<string, number>; }, catch: (cause) => new ScopeDbError({ message: "versions failed", cause }), }), };}; export interface LibsqlScopeDbOptions { /** Directory holding one SQLite file per scope, or ":memory:" for tests. */ readonly root: string;} const toUrl = (path: string): string => (path === ":memory:" ? path : `file:${resolve(path)}`); // A reversible, collision-free filename key for a scope (Fix 9). A scope already// composed only of filename-safe characters is used verbatim (so distinct// filename-safe scopes stay distinct — "my-scope" and "my_scope" no longer// collide). Any scope containing a character outside that set is hex-encoded in// full under an `x-` prefix, which can never collide with a verbatim scope// (verbatim scopes never start with `x-` followed by only hex... unless they// literally are `x-<hex>`, so we also encode those). Two different scopes always// produce two different keys.const SAFE_SCOPE = /^[A-Za-z0-9._-]+$/;const HEX_PREFIXED = /^x-[0-9a-f]*$/;const scopeFileKey = (scope: string): string => { if (SAFE_SCOPE.test(scope) && !HEX_PREFIXED.test(scope)) return scope; const hex = Buffer.from(scope, "utf8").toString("hex"); return `x-${hex}`;}; export const makeLibsqlScopeDb = (options: LibsqlScopeDbOptions): ScopeDb => { const clients = new Map<string, Client>(); const listeners = new Set<(event: ScopeWriteEvent) => void>(); const emit = (event: ScopeWriteEvent) => { for (const listener of listeners) listener(event); }; const clientFor = (scope: string): Client => { let client = clients.get(scope); if (!client) { if (options.root === ":memory:") { client = createClient({ url: ":memory:" }); } else { mkdirSync(options.root, { recursive: true }); // Collision-free filename (Fix 9): the old `replace(/[^..]/g, "_")` // COLLAPSED distinct scopes to the same file ("my-scope" and "my_scope" // -> "my_scope.db"), so two scopes shared one database. Encode the raw // scope instead: keep the common safe-identifier case readable, and for // anything with a character outside `[A-Za-z0-9._-]` fall back to a // reversible hex encoding of the full raw scope. Distinct scopes always // map to distinct filenames. const safe = scopeFileKey(scope); client = createClient({ url: toUrl(join(options.root, `${safe}.db`)) }); } clients.set(scope, client); } return client; }; return { forScope: (scope) => Effect.try({ try: () => makeHandle(scope, clientFor(scope), emit), catch: (cause) => new ScopeDbError({ message: `failed to open scope db ${scope}`, cause }), }), onWrite: (listener) => { listeners.add(listener); return () => listeners.delete(listener); }, close: () => Effect.sync(() => { for (const client of clients.values()) client.close(); clients.clear(); listeners.clear(); }), };};
import { mkdirSync } from "node:fs";
import { join, resolve } from "node:path";
import { createClient, type Client } from "@libsql/client";
import { Effect } from "effect";
import {
ScopeDbError,
type ScopeDb,
type ScopeDbHandle,
type ScopeWriteEvent,
} from "../seams/scope-db";
// ---------------------------------------------------------------------------
// libSQL-backed ScopeDb (self-hosted). One SQLite file per scope under
// `<root>/<scope>.db`. A control table `__versions` holds a monotonic counter
// per user table; every write statement bumps the counters for the tables it
// touched and emits a `ScopeWriteEvent` (the runtime forwards that to
// LiveChannel). Scope isolation is a separate file per scope — there is no
// cross-scope query path.
// ---------------------------------------------------------------------------
const VERSION_TABLE = "__scope_versions";
// Statements that mutate data. Determines whether a `sql` call bumps versions.
const WRITE_RE = /^\s*(insert|update|delete|replace|create|drop|alter)\b/i;
// Extract the table names a write statement targets (best-effort: covers the
// shapes authored tools produce — INSERT INTO x, UPDATE x, DELETE FROM x,
// CREATE TABLE x, REPLACE INTO x).
const targetsOf = (sql: string): string[] => {
const out = new Set<string>();
const patterns = [
/\binsert\s+(?:or\s+\w+\s+)?into\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi,
/\breplace\s+into\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi,
/\bupdate\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi,
/\bdelete\s+from\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi,
/\bcreate\s+table\s+(?:if\s+not\s+exists\s+)?["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi,
/\bdrop\s+table\s+(?:if\s+exists\s+)?["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi,
/\balter\s+table\s+["'`]?([a-zA-Z_][a-zA-Z0-9_]*)["'`]?/gi,
];
for (const re of patterns) {
let m: RegExpExecArray | null;
while ((m = re.exec(sql)) !== null) {
const name = m[1];
if (name && name !== VERSION_TABLE) out.add(name);
}
}
return [...out];
};
const toArgs = (values: readonly unknown[]): unknown[] =>
values.map((v) => (v === undefined ? null : v));
const makeHandle = (
scope: string,
client: Client,
emit: (event: ScopeWriteEvent) => void,
): ScopeDbHandle => {
const ensureVersionTable = async () => {
await client.execute(
`CREATE TABLE IF NOT EXISTS ${VERSION_TABLE} (name TEXT PRIMARY KEY, version INTEGER NOT NULL DEFAULT 0)`,
);
};
const bump = async (tables: readonly string[]): Promise<{ table: string; version: number }[]> => {
const bumped: { table: string; version: number }[] = [];
for (const table of tables) {
await client.execute({
sql: `INSERT INTO ${VERSION_TABLE} (name, version) VALUES (?, 1)
ON CONFLICT(name) DO UPDATE SET version = version + 1`,
args: [table],
});
const row = await client.execute({
sql: `SELECT version FROM ${VERSION_TABLE} WHERE name = ?`,
args: [table],
});
bumped.push({ table, version: Number(row.rows[0]?.version ?? 0) });
}
return bumped;
};
const runStatement = <Row>(
sql: string,
args: unknown[],
): Effect.Effect<readonly Row[], ScopeDbError> =>
Effect.tryPromise({
try: async () => {
await ensureVersionTable();
const result = await client.execute({ sql, args: args as never });
if (WRITE_RE.test(sql)) {
const targets = targetsOf(sql);
if (targets.length > 0) {
const bumped = await bump(targets);
if (bumped.length > 0) emit({ scope, tables: bumped });
}
}
// oxlint-disable-next-line executor/no-double-cast -- boundary: libSQL's driver `Row` type does not structurally overlap the caller's generic `Row`; the SQL is the schema contract
return result.rows as unknown as readonly Row[];
},
catch: (cause) =>
new ScopeDbError({ message: `scope-db statement failed for scope ${scope}`, cause }),
});
return {
sql: <Row = Record<string, unknown>>(strings: TemplateStringsArray, ...values: unknown[]) => {
const sql = strings.reduce((acc, part, i) => acc + part + (i < values.length ? "?" : ""), "");
return runStatement<Row>(sql, toArgs(values));
},
exec: <Row = Record<string, unknown>>(sql: string, params: readonly unknown[] = []) =>
runStatement<Row>(sql, toArgs(params)),
tableVersion: (table: string) =>
Effect.tryPromise({
try: async () => {
await ensureVersionTable();
const row = await client.execute({
sql: `SELECT version FROM ${VERSION_TABLE} WHERE name = ?`,
args: [table],
});
return Number(row.rows[0]?.version ?? 0);
},
catch: (cause) => new ScopeDbError({ message: "tableVersion failed", cause }),
}),
versions: () =>
Effect.tryPromise({
try: async () => {
await ensureVersionTable();
const rows = await client.execute(`SELECT name, version FROM ${VERSION_TABLE}`);
const out = new Map<string, number>();
for (const r of rows.rows) out.set(String(r.name), Number(r.version));
return out as ReadonlyMap<string, number>;
},
catch: (cause) => new ScopeDbError({ message: "versions failed", cause }),
}),
};
};
export interface LibsqlScopeDbOptions {
/** Directory holding one SQLite file per scope, or ":memory:" for tests. */
readonly root: string;
}
const toUrl = (path: string): string => (path === ":memory:" ? path : `file:${resolve(path)}`);
// A reversible, collision-free filename key for a scope (Fix 9). A scope already
// composed only of filename-safe characters is used verbatim (so distinct
// filename-safe scopes stay distinct — "my-scope" and "my_scope" no longer
// collide). Any scope containing a character outside that set is hex-encoded in
// full under an `x-` prefix, which can never collide with a verbatim scope
// (verbatim scopes never start with `x-` followed by only hex... unless they
// literally are `x-<hex>`, so we also encode those). Two different scopes always
// produce two different keys.
const SAFE_SCOPE = /^[A-Za-z0-9._-]+$/;
const HEX_PREFIXED = /^x-[0-9a-f]*$/;
const scopeFileKey = (scope: string): string => {
if (SAFE_SCOPE.test(scope) && !HEX_PREFIXED.test(scope)) return scope;
const hex = Buffer.from(scope, "utf8").toString("hex");
return `x-${hex}`;
};
export const makeLibsqlScopeDb = (options: LibsqlScopeDbOptions): ScopeDb => {
const clients = new Map<string, Client>();
const listeners = new Set<(event: ScopeWriteEvent) => void>();
const emit = (event: ScopeWriteEvent) => {
for (const listener of listeners) listener(event);
};
const clientFor = (scope: string): Client => {
let client = clients.get(scope);
if (!client) {
if (options.root === ":memory:") {
client = createClient({ url: ":memory:" });
} else {
mkdirSync(options.root, { recursive: true });
// Collision-free filename (Fix 9): the old `replace(/[^..]/g, "_")`
// COLLAPSED distinct scopes to the same file ("my-scope" and "my_scope"
// -> "my_scope.db"), so two scopes shared one database. Encode the raw
// scope instead: keep the common safe-identifier case readable, and for
// anything with a character outside `[A-Za-z0-9._-]` fall back to a
// reversible hex encoding of the full raw scope. Distinct scopes always
// map to distinct filenames.
const safe = scopeFileKey(scope);
client = createClient({ url: toUrl(join(options.root, `${safe}.db`)) });
}
clients.set(scope, client);
}
return client;
};
return {
forScope: (scope) =>
Effect.try({
try: () => makeHandle(scope, clientFor(scope), emit),
catch: (cause) => new ScopeDbError({ message: `failed to open scope db ${scope}`, cause }),
}),
onWrite: (listener) => {
listeners.add(listener);
return () => listeners.delete(listener);
},
close: () =>
Effect.sync(() => {
for (const client of clients.values()) client.close();
clients.clear();
listeners.clear();
}),
};
};