grain.social/grain-support-agent
grain's support agent: triages mentions and reports, investigates bugs, proposes fixes
0
0
import { constants, DatabaseSync } from "node:sqlite";
// Read-only access to grain-serve's index, for the investigation agent.
//
// The connection is opened read-only and query-only, so SQLite itself refuses
// writes. What may be *read* is enforced by SQLite's authorizer, which sees
// every table and column a statement reads as it is compiled, however the
// statement names them: through quoting, aliases, subqueries, CTEs or
// EXPLAIN. A statement that reads a hidden table fails to compile, and so
// never runs. The private tables hold credentials (OAuth keys and sessions,
// push tokens) or what an account keeps to itself; the moderation tables hold
// reports and classifier verdicts, child-safety ones among them, which a model
// reads only through the brief (child-safety.ts).
export const PRIVATE_TABLES = new Set([
"oauth_key",
"web_session",
"carried_cookie_key",
"push_token",
"preference",
"notification_pref",
"notification_seen",
"mute",
"story_view",
"space_invite",
"space_support",
]);
/** Reports and what the classifiers said: a model reads them only as the brief shows them. */
export const MODERATION_TABLES = new Set(["report", "classification"]);
/**
* Tables that hold other tables' data outside their rows, private tables' with
* the rest: the file's raw pages, and the index samples ANALYZE may keep.
*/
const BEYOND_ROWS = new Set(["sqlite_dbpage", "dbstat", "sqlite_stat3", "sqlite_stat4"]);
/** Functions that reach past this one database. */
const FORBIDDEN_FUNCTIONS = new Set(["load_extension", "fts3_tokenizer"]);
export const MAX_ROWS = 200;
const MAX_CELL = 2000;
/** Whether a table is closed to the agent: private, or moderation. */
export const isHidden = (table: string) => PRIVATE_TABLES.has(table.toLowerCase()) || MODERATION_TABLES.has(table.toLowerCase());
/** Returns why a statement is refused before it is compiled, or undefined when it may be. */
export function refuse(sql: string): string | undefined {
const trimmed = sql.trim().replace(/;\s*$/, "");
if (!trimmed) return "empty statement";
if (trimmed.includes(";")) return "one statement at a time";
if (!/^(select|with|explain)\b/i.test(trimmed)) return "only SELECT, WITH and EXPLAIN are allowed";
return undefined;
}
/**
* The authorizer: a statement may select, read what is not private, call
* functions that stay in this database, recurse, and use the table-valued
* pragmas, which SQLite offers only for pragmas without side effects. Anything
* else fails to compile.
*/
function authorize(action: number, arg1: string | null, arg2: string | null): number {
switch (action) {
case constants.SQLITE_SELECT:
case constants.SQLITE_RECURSIVE:
return constants.SQLITE_OK;
case constants.SQLITE_READ: {
const table = (arg1 ?? "").toLowerCase();
return isHidden(table) || BEYOND_ROWS.has(table) ? constants.SQLITE_DENY : constants.SQLITE_OK;
}
case constants.SQLITE_FUNCTION:
return FORBIDDEN_FUNCTIONS.has((arg2 ?? "").toLowerCase()) ? constants.SQLITE_DENY : constants.SQLITE_OK;
case constants.SQLITE_PRAGMA:
// pragma_table_info('<table>') and its like name a table as the argument.
return arg2 && isHidden(arg2) ? constants.SQLITE_DENY : constants.SQLITE_OK;
default:
return constants.SQLITE_DENY;
}
}
let db: DatabaseSync | undefined;
export function open(path: string): DatabaseSync {
if (!db) {
db = new DatabaseSync(path, { readOnly: true });
// grain-serve holds the write lock while indexing; wait rather than fail.
db.exec("PRAGMA busy_timeout = 10000; PRAGMA query_only = 1;");
db.setAuthorizer(authorize);
}
return db;
}
function clip(value: unknown): unknown {
if (typeof value === "string" && value.length > MAX_CELL) {
return `${value.slice(0, MAX_CELL)}… (${value.length} chars)`;
}
if (value instanceof Uint8Array) return `<${value.length} bytes>`;
if (typeof value === "bigint") return value.toString();
return value;
}
export function query(path: string, sql: string): { rows: Record<string, unknown>[]; truncated: boolean } {
const why = refuse(sql);
if (why) throw new Error(why);
const rows: Record<string, unknown>[] = [];
let truncated = false;
try {
// A table-valued pragma is authorized as it first steps, not as it compiles.
for (const row of open(path).prepare(sql).iterate()) {
if (rows.length >= MAX_ROWS) {
truncated = true;
break;
}
rows.push(Object.fromEntries(Object.entries(row).map(([k, v]) => [k, clip(v)])));
}
} catch (err) {
const message = err instanceof Error ? err.message : String(err);
// SQLite says what it refused, not why.
if (/not authorized|prohibited/.test(message)) {
throw new Error(
`${message}: the private tables (${[...PRIVATE_TABLES].join(", ")}), the moderation tables (${[...MODERATION_TABLES].join(", ")}) and the file's raw pages cannot be read`,
);
}
throw err;
}
return { rows, truncated };
}