Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
#!/usr/bin/env bun
/**
* Captures a schema-history fixture: the schema and rows of a brain created by
* an older gbrain release, as gzipped plain SQL, so the schema-history upgrade tests
* can upgrade it with today's code (#7645: a v265 brain could not reach v266).
*
* git worktree add --detach /tmp/gbrain-v270 <release commit>
* ln -s "$PWD/node_modules" /tmp/gbrain-v270/node_modules
* bun scripts/capture-schema-history-fixture.ts --tree /tmp/gbrain-v270 --engine pglite
* DATABASE_URL=postgresql://... bun scripts/capture-schema-history-fixture.ts --tree /tmp/gbrain-v270 --engine postgres
*
* The release's own engine runs initSchema (bootstrap, schema blob and every
* migration of that release) on an empty database. The dump is then restored
* into a second empty database through today's engine connect, and the script
* refuses to write the fixture unless the restored catalog, function bodies and
* rows equal the original's. Generated secrets are blanked before the dump.
*/
import { mkdirSync, writeFileSync } from 'node:fs';
import { dirname, join, resolve } from 'node:path';
import { randomUUID } from 'node:crypto';
import postgres from '#postgres';
import { PGLiteEngine } from '../src/core/pglite-engine.ts';
import { LEGACY_EMBEDDING_CONFIG } from '../test/helpers/legacy-embedding-config.ts';
import { diffCatalogSnapshots, formatCatalogDiffForFailure, snapshotCatalog, type CatalogQueryFn } from '../test/helpers/schema-diff.ts';
import { FUNCTION_BODIES_SQL } from '../test/helpers/schema-catalog.ts';
import { sameUpToFlattenedBooleanChains } from '../test/helpers/schema-history.ts';
type Query = (sql: string) => Promise<Array<Record<string, any>>>;
type Engine = 'pglite' | 'postgres';
const NOT_EXTENSION_MEMBER = (classid: string, objid: string) =>
`NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.classid = '${classid}'::regclass AND d.objid = ${objid} AND d.deptype = 'e')`;
const ident = (name: string) => '"' + name.replace(/"/g, '""') + '"';
/** Plain SQL that recreates the public schema and its rows, in pg_dump's dependency order. */
export async function dumpBrainSchema(query: Query, version: number): Promise<string> {
const out: string[] = [
`-- gbrain schema-history fixture: a fresh brain at schema v${version}.`,
'-- Generated by scripts/capture-schema-history-fixture.ts; do not edit.',
'SET check_function_bodies = false;',
];
for (const r of await query(`SELECT extname FROM pg_extension WHERE extname <> 'plpgsql' ORDER BY extname`)) {
out.push(`CREATE EXTENSION IF NOT EXISTS ${ident(r.extname)};`);
}
const sequences = await query(`
SELECT c.relname AS name, format_type(s.seqtypid, NULL) AS type, s.seqstart AS start, s.seqincrement AS inc,
s.seqmin AS min, s.seqmax AS max, s.seqcache AS cache, s.seqcycle AS cycle,
(SELECT t.relname || '.' || a.attname FROM pg_depend d JOIN pg_class t ON t.oid = d.refobjid
JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE d.classid = 'pg_class'::regclass AND d.objid = c.oid AND d.deptype = 'a') AS owned_by
FROM pg_sequence s JOIN pg_class c ON c.oid = s.seqrelid JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public'
AND NOT EXISTS (SELECT 1 FROM pg_depend d WHERE d.classid = 'pg_class'::regclass AND d.objid = c.oid AND d.deptype = 'i')
AND ${NOT_EXTENSION_MEMBER('pg_class', 'c.oid')}
ORDER BY c.oid`);
for (const s of sequences) {
out.push(`CREATE SEQUENCE ${ident(s.name)} AS ${s.type} INCREMENT BY ${s.inc} MINVALUE ${s.min} MAXVALUE ${s.max} START WITH ${s.start} CACHE ${s.cache}${s.cycle ? ' CYCLE' : ''};`);
}
for (const f of await query(`
SELECT pg_get_functiondef(p.oid) AS def FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public' AND p.prokind IN ('f', 'p') AND ${NOT_EXTENSION_MEMBER('pg_proc', 'p.oid')}
ORDER BY p.oid`)) {
out.push(`${String(f.def).trimEnd()};`);
}
const columns = await query(`
SELECT c.relname AS table_name, c.relpersistence AS persistence,
a.attname AS name, format_type(a.atttypid, a.atttypmod) AS type, a.attnotnull AS not_null,
a.attidentity AS identity, a.attgenerated AS generated, pg_get_expr(ad.adbin, ad.adrelid) AS default_expr,
CASE WHEN a.attcollation <> ty.typcollation THEN (SELECT collname FROM pg_collation WHERE oid = a.attcollation) END AS collation
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_attribute a ON a.attrelid = c.oid AND a.attnum > 0 AND NOT a.attisdropped
LEFT JOIN pg_type ty ON ty.oid = a.atttypid
LEFT JOIN pg_attrdef ad ON ad.adrelid = c.oid AND ad.adnum = a.attnum
WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p') AND ${NOT_EXTENSION_MEMBER('pg_class', 'c.oid')}
ORDER BY c.oid, a.attnum`);
const tables = new Map<string, { persistence: string; cols: string[]; insertable: string[] }>();
for (const c of columns) {
let t = tables.get(c.table_name);
if (!t) tables.set(c.table_name, t = { persistence: c.persistence, cols: [], insertable: [] });
if (c.name == null) continue;
let col = `${ident(c.name)} ${c.type}`;
if (c.collation) col += ` COLLATE ${ident(c.collation)}`;
if (c.generated === 's') col += ` GENERATED ALWAYS AS (${c.default_expr}) STORED`;
else if (c.identity === 'a' || c.identity === 'd') col += ` GENERATED ${c.identity === 'a' ? 'ALWAYS' : 'BY DEFAULT'} AS IDENTITY`;
else if (c.default_expr != null) col += ` DEFAULT ${c.default_expr}`;
if (c.not_null) col += ' NOT NULL';
t.cols.push(col);
if (c.generated !== 's') t.insertable.push(ident(c.name));
}
for (const [name, t] of tables) {
out.push(`CREATE ${t.persistence === 'u' ? 'UNLOGGED ' : ''}TABLE ${ident(name)} (\n ${t.cols.join(',\n ')}\n);`);
}
for (const s of sequences) {
if (!s.owned_by) continue;
const [table, column] = String(s.owned_by).split('.');
out.push(`ALTER SEQUENCE ${ident(s.name)} OWNED BY ${ident(table)}.${ident(column)};`);
}
for (const [name, t] of tables) {
const [{ rows }] = await query(`SELECT COALESCE(json_agg(x), '[]'::json)::text AS rows FROM ${ident(name)} x`);
if (rows === '[]' || t.insertable.length === 0) continue;
const tag = '$gbrain_fixture$';
if (String(rows).includes(tag)) throw new Error(`row data of ${name} contains ${tag}`);
out.push(`INSERT INTO ${ident(name)} (${t.insertable.join(', ')}) OVERRIDING SYSTEM VALUE\n SELECT ${t.insertable.join(', ')} FROM json_populate_recordset(NULL::${ident(name)}, ${tag}${rows}${tag});`);
}
for (const s of await sequencePositions(query)) {
const target = s.table_name && s.column_name ? `pg_get_serial_sequence('${ident(s.table_name)}', '${s.column_name}')` : `'${ident(s.name)}'`;
out.push(`SELECT setval(${target}, ${s.last_value}, ${s.is_called ? 'true' : 'false'});`);
}
const constraints = await query(`
SELECT t.relname AS table_name, con.conname AS name, con.contype AS type, pg_get_constraintdef(con.oid) AS def
FROM pg_constraint con JOIN pg_class t ON t.oid = con.conrelid JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public' AND con.contype IN ('p', 'u', 'c', 'x', 'f') AND ${NOT_EXTENSION_MEMBER('pg_class', 't.oid')}
ORDER BY con.oid`);
for (const c of constraints.filter((c) => c.type !== 'f')) {
out.push(`ALTER TABLE ${ident(c.table_name)} ADD CONSTRAINT ${ident(c.name)} ${c.def};`);
}
for (const i of await query(`
SELECT pg_get_indexdef(idx.indexrelid) AS def FROM pg_index idx
JOIN pg_class t ON t.oid = idx.indrelid JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public' AND idx.indisvalid AND ${NOT_EXTENSION_MEMBER('pg_class', 't.oid')}
AND NOT EXISTS (SELECT 1 FROM pg_constraint con WHERE con.conindid = idx.indexrelid AND con.contype IN ('p', 'u', 'x'))
ORDER BY idx.indexrelid`)) {
out.push(`${i.def};`);
}
for (const c of constraints.filter((c) => c.type === 'f')) {
out.push(`ALTER TABLE ${ident(c.table_name)} ADD CONSTRAINT ${ident(c.name)} ${c.def};`);
}
for (const v of await query(`
SELECT c.relname AS name, c.relkind AS kind, pg_get_viewdef(c.oid) AS def,
COALESCE(array_to_string(c.reloptions, ', '), '') AS options
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('v', 'm') AND ${NOT_EXTENSION_MEMBER('pg_class', 'c.oid')}
ORDER BY c.oid`)) {
const options = v.options ? ` WITH (${v.options})` : '';
out.push(`CREATE ${v.kind === 'm' ? 'MATERIALIZED ' : ''}VIEW ${ident(v.name)}${options} AS\n${String(v.def).trim().replace(/;$/, '')};`);
}
for (const t of await query(`
SELECT pg_get_triggerdef(tg.oid) AS def, tg.tgenabled AS enabled, tg.tgname AS name, t.relname AS table_name
FROM pg_trigger tg JOIN pg_class t ON t.oid = tg.tgrelid JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public' AND NOT tg.tgisinternal ORDER BY tg.oid`)) {
out.push(`${t.def};`);
if (t.enabled === 'D') out.push(`ALTER TABLE ${ident(t.table_name)} DISABLE TRIGGER ${ident(t.name)};`);
}
for (const r of await query(`
SELECT c.relname AS name, c.relrowsecurity AS rls, c.relforcerowsecurity AS force
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p') AND (c.relrowsecurity OR c.relforcerowsecurity)
ORDER BY c.oid`)) {
if (r.rls) out.push(`ALTER TABLE ${ident(r.name)} ENABLE ROW LEVEL SECURITY;`);
if (r.force) out.push(`ALTER TABLE ${ident(r.name)} FORCE ROW LEVEL SECURITY;`);
}
for (const p of await query(`
SELECT tablename AS table_name, policyname AS name, permissive, array_to_string(roles, ', ') AS roles,
cmd, qual, with_check FROM pg_policies WHERE schemaname = 'public' ORDER BY tablename, policyname`)) {
const roles = String(p.roles).split(', ').map((r) => (r === 'public' ? 'PUBLIC' : ident(r))).join(', ');
let sql = `CREATE POLICY ${ident(p.name)} ON ${ident(p.table_name)} AS ${p.permissive} FOR ${p.cmd} TO ${roles}`;
if (p.qual != null) sql += ` USING (${p.qual})`;
if (p.with_check != null) sql += ` WITH CHECK (${p.with_check})`;
out.push(`${sql};`);
}
for (const e of await query(`
SELECT e.evtname AS name, e.evtevent AS event, e.evtfoid::regproc::text AS fn, e.evtenabled AS enabled,
COALESCE(array_to_string(e.evttags, ''', '''), '') AS tags
FROM pg_event_trigger e WHERE ${NOT_EXTENSION_MEMBER('pg_event_trigger', 'e.oid')} ORDER BY e.oid`)) {
const when = e.tags ? ` WHEN TAG IN ('${e.tags}')` : '';
out.push(`CREATE EVENT TRIGGER ${ident(e.name)} ON ${e.event}${when} EXECUTE FUNCTION ${e.fn}();`);
if (e.enabled === 'D') out.push(`ALTER EVENT TRIGGER ${ident(e.name)} DISABLE;`);
}
out.push('RESET check_function_bodies;');
return out.join('\n\n') + '\n';
}
/** Every public sequence with its position, keyed by the column that owns it (serial or identity) or by its own name. */
async function sequencePositions(query: Query): Promise<Array<{ name: string; table_name: string | null; column_name: string | null; last_value: string; is_called: boolean }>> {
const out = [];
for (const s of await query(`
SELECT c.relname AS name, t.relname AS table_name, a.attname AS column_name
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
LEFT JOIN pg_depend d ON d.classid = 'pg_class'::regclass AND d.objid = c.oid AND d.deptype IN ('a', 'i')
LEFT JOIN pg_class t ON t.oid = d.refobjid
LEFT JOIN pg_attribute a ON a.attrelid = d.refobjid AND a.attnum = d.refobjsubid
WHERE n.nspname = 'public' AND c.relkind = 'S' AND ${NOT_EXTENSION_MEMBER('pg_class', 'c.oid')}
ORDER BY c.oid`)) {
const [{ last_value, is_called }] = await query(`SELECT last_value::text AS last_value, is_called FROM ${ident(s.name)}`);
out.push({ name: s.name, table_name: s.table_name ?? null, column_name: s.column_name ?? null, last_value, is_called: Boolean(is_called) });
}
return out;
}
/** Blank every secret a fresh brain generates (e.g. shared_skill_state.token_secret) so no fixture carries one. */
async function blankSecrets(query: Query): Promise<void> {
for (const c of await query(`
SELECT table_name, column_name FROM information_schema.columns
WHERE table_schema = 'public' AND column_name LIKE '%secret%' AND data_type IN ('text', 'character varying')
AND table_name IN (SELECT table_name FROM information_schema.tables WHERE table_schema = 'public' AND table_type = 'BASE TABLE')
ORDER BY 1, 2`)) {
await query(`UPDATE ${ident(c.table_name)} SET ${ident(c.column_name)} = repeat('0', length(${ident(c.column_name)})) WHERE ${ident(c.column_name)} IS NOT NULL`);
}
}
/** Every public table's rows, ordered, for the round-trip check. */
async function tableRows(query: Query): Promise<Record<string, string>> {
const out: Record<string, string> = {};
for (const t of await query(`
SELECT c.relname AS name FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p') AND ${NOT_EXTENSION_MEMBER('pg_class', 'c.oid')} ORDER BY 1`)) {
const [{ rows }] = await query(`SELECT COALESCE(json_agg(x ORDER BY x::text), '[]'::json)::text AS rows FROM ${ident(t.name)} x`);
out[t.name] = rows;
}
return out;
}
async function fidelity(original: Query, restored: Query): Promise<string[]> {
const problems: string[] = [];
const catalogDiff = diffCatalogSnapshots(await snapshotCatalog(original as CatalogQueryFn), await snapshotCatalog(restored as CatalogQueryFn), { compareOrdinals: true })
.filter((d) => !(d.section === 'triggers' && d.kind === 'changed' && sameUpToFlattenedBooleanChains(d.expected, d.actual)));
if (catalogDiff.length > 0) problems.push(formatCatalogDiffForFailure(catalogDiff));
const [a, b] = [await original(FUNCTION_BODIES_SQL), await restored(FUNCTION_BODIES_SQL)];
if (JSON.stringify(a) !== JSON.stringify(b)) problems.push('function bodies differ');
const positions = async (q: Query) => JSON.stringify((await sequencePositions(q)).map(({ table_name, column_name, last_value, is_called }) => ({ table_name, column_name, last_value, is_called })));
if (await positions(original) !== await positions(restored)) problems.push('sequence positions differ');
const [ra, rb] = [await tableRows(original), await tableRows(restored)];
for (const name of new Set([...Object.keys(ra), ...Object.keys(rb)])) {
if (ra[name] !== rb[name]) problems.push(`rows of ${name} differ`);
}
return problems;
}
function parseArgs(argv: string[]): { tree: string; engine: Engine; out?: string } {
const get = (flag: string) => { const i = argv.indexOf(flag); return i >= 0 ? argv[i + 1] : undefined; };
const tree = get('--tree');
const engine = get('--engine');
if (!tree || (engine !== 'pglite' && engine !== 'postgres')) {
throw new Error('Usage: capture-schema-history-fixture.ts --tree <release worktree> --engine pglite|postgres [--out <file>]');
}
return { tree: resolve(tree), engine, out: get('--out') };
}
function withDatabase(url: string, name: string): string {
const u = new URL(url);
u.pathname = `/${name}`;
return u.toString();
}
async function main(): Promise<void> {
const { tree, engine, out } = parseArgs(process.argv.slice(2));
delete process.env.GBRAIN_PGLITE_SNAPSHOT;
const { LATEST_SCHEMA_VERSION: version } = await import(join(tree, 'src/core/schema-migrations/latest.generated.ts'));
const gateway = await import(join(tree, 'src/core/ai/gateway.ts'));
gateway.configureGateway({ ...LEGACY_EMBEDDING_CONFIG, env: { ...process.env } });
const file = out ?? join('test/fixtures/schema-history', engine, `v${version}.sql.gz`);
let dump: string;
let problems: string[];
if (engine === 'pglite') {
const { PGLiteEngine: ReleaseEngine } = await import(join(tree, 'src/core/pglite-engine.ts'));
const release = new ReleaseEngine();
const restored = new PGLiteEngine();
try {
await release.connect({});
await release.initSchema();
const original: Query = async (sql) => (await release.db.query(sql)).rows;
await blankSecrets(original);
dump = await dumpBrainSchema(original, version);
await restored.connect({});
await restored.db.exec(dump);
problems = await fidelity(original, async (sql) => (await restored.db.query(sql)).rows as Array<Record<string, any>>);
} finally {
await release.disconnect();
await restored.disconnect();
}
} else {
const admin = process.env.DATABASE_URL;
if (!admin) throw new Error('--engine postgres needs DATABASE_URL (a disposable test server)');
const suffix = randomUUID().replace(/-/g, '');
const names = [`gbrain_fixture_release_${suffix}`, `gbrain_fixture_restored_${suffix}`];
const adminSql = postgres(admin, { max: 1, prepare: false, onnotice: () => {} });
const { PostgresEngine: ReleaseEngine } = await import(join(tree, 'src/core/postgres-engine.ts'));
const release = new ReleaseEngine();
let restored: ReturnType<typeof postgres> | undefined;
try {
for (const name of names) await adminSql.unsafe(`CREATE DATABASE ${name}`);
await release.connect({ database_url: withDatabase(admin, names[0]), poolSize: 2 });
await release.initSchema();
const original: Query = async (sql) => await release.sql.unsafe(sql);
await blankSecrets(original);
dump = await dumpBrainSchema(original, version);
restored = postgres(withDatabase(admin, names[1]), { max: 1, prepare: false, onnotice: () => {} });
await restored.unsafe(dump);
problems = await fidelity(original, async (sql) => await restored!.unsafe(sql));
} finally {
await release.disconnect();
await restored?.end({ timeout: 5 });
for (const name of names) await adminSql.unsafe(`DROP DATABASE IF EXISTS ${name} WITH (FORCE)`);
await adminSql.end({ timeout: 5 });
}
}
if (problems.length > 0) throw new Error(`restored v${version} ${engine} fixture differs from the release's brain:\n${problems.join('\n')}`);
mkdirSync(dirname(file), { recursive: true });
writeFileSync(file, Bun.gzipSync(Buffer.from(dump), { level: 9 }));
process.stdout.write(`wrote ${file} (schema v${version}, ${engine}, ${dump.length} bytes)\n`);
}
if (import.meta.main) {
main().then(() => process.exit(0), (err) => {
process.stderr.write(`${err instanceof Error ? err.stack ?? err.message : String(err)}\n`);
process.exit(1);
});
}