grain.social/grain-support-agent
grain's support agent: triages mentions and reports, investigates bugs, proposes fixes
0
0
import { test } from "node:test";
import assert from "node:assert/strict";
import { DatabaseSync } from "node:sqlite";
import { mkdtempSync } from "node:fs";
import { tmpdir } from "node:os";
import { join } from "node:path";
import { isHidden, MODERATION_TABLES, PRIVATE_TABLES, query, refuse } from "../src/grain-db.ts";
// An index with a public table, an FTS table, and every private and moderation one, in WAL
// mode as grain-serve keeps it.
const path = join(mkdtempSync(join(tmpdir(), "grain-db-")), "index.db");
const w = new DatabaseSync(path);
w.exec(`
PRAGMA journal_mode = WAL;
CREATE TABLE photo (uri TEXT PRIMARY KEY, did TEXT);
WITH RECURSIVE c(n) AS (SELECT 1 UNION ALL SELECT n+1 FROM c WHERE n < 500)
INSERT INTO photo SELECT 'at://did:plc:a/social.grain.photo/' || n, 'did:plc:a' FROM c;
CREATE VIRTUAL TABLE profile_search USING fts5(handle);
INSERT INTO profile_search VALUES ('someone.bsky.social');
`);
for (const table of [...PRIVATE_TABLES, ...MODERATION_TABLES]) w.exec(`CREATE TABLE ${table} (secret TEXT); INSERT INTO ${table} VALUES ('hunter2');`);
w.close();
test("statements must be a single read", () => {
assert.equal(refuse("SELECT COUNT(*) FROM photo"), undefined);
assert.equal(refuse("with x as (select 1) select * from x;"), undefined);
for (const sql of ["", "DELETE FROM photo", "PRAGMA table_info(photo)", "SELECT 1; DROP TABLE x", "ATTACH '/etc/passwd' AS p", "VACUUM INTO '/tmp/copy.db'"]) {
assert.ok(refuse(sql), sql);
}
});
test("public tables read, through FTS and the table-valued pragmas too", () => {
assert.equal(query(path, "SELECT count(*) AS n FROM photo").rows[0].n, 500);
assert.equal(query(path, "SELECT handle FROM profile_search WHERE profile_search MATCH 'someone'").rows.length, 1);
assert.ok(query(path, "SELECT name FROM pragma_table_info('photo')").rows.length);
});
test("no statement reads a private or moderation table, however it names it", () => {
for (const table of [...PRIVATE_TABLES, ...MODERATION_TABLES]) {
for (const sql of [
`SELECT * FROM ${table}`,
`SELECT count(*) FROM "${table}"`,
`SELECT * FROM [${table.toUpperCase()}]`,
`SELECT * FROM main.\`${table}\``,
`SELECT * FROM photo WHERE did IN (SELECT secret FROM ${table})`,
`WITH x AS (SELECT * FROM ${table}) SELECT * FROM x`,
`SELECT * FROM photo p JOIN ${table} t ON 1`,
`EXPLAIN SELECT secret FROM ${table}`,
`SELECT name FROM pragma_table_info('${table}')`,
]) {
assert.throws(() => query(path, sql), /cannot be read/, sql);
}
}
// Nor the raw pages the file holds them in.
assert.throws(() => query(path, "SELECT * FROM dbstat"));
});
test("hidden tables are known by name, in any case", () => {
assert.ok(isHidden("oauth_key"));
assert.ok(isHidden("Web_Session"));
assert.equal(isHidden("photo"), false);
assert.equal(isHidden("profile"), false);
});
test("reports and classifier verdicts are closed to SQL: a model reads them only through the brief", () => {
assert.ok(isHidden("report"));
assert.ok(isHidden("Classification"));
for (const sql of [
"SELECT * FROM report",
"SELECT secret FROM classification",
"SELECT p.uri FROM photo p WHERE EXISTS (SELECT 1 FROM report r WHERE r.secret = p.did)",
"SELECT name FROM pragma_table_info('report')",
]) {
assert.throws(() => query(path, sql), /moderation tables \(report, classification\)/, sql);
}
});
test("query caps rows and cannot write", () => {
const { rows, truncated } = query(path, "SELECT uri FROM photo");
assert.equal(rows.length, 200);
assert.equal(truncated, true);
assert.throws(() => query(path, "WITH x AS (SELECT 1) INSERT INTO photo (uri) SELECT * FROM x"));
});