Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
18.3 KB
Raw Blame
#!/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);
  });
}