Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
/**
* E2E test for PostgresEngine forward-reference bootstrap.
*
* Codex caught that `test/e2e/helpers.ts:74` uses the standalone
* `db.initSchema()` from `src/core/db.ts`, which only runs SCHEMA_SQL and
* never calls runMigrations(). A test using that helper would NOT exercise
* `PostgresEngine.initSchema()`'s reordered path, producing false-positive
* coverage. This test deliberately bypasses the standard helper and
* instantiates `PostgresEngine` directly, calling `engine.initSchema()` so
* the bootstrap → SCHEMA_SQL → runMigrations sequence runs end-to-end.
*
* Covers issues #366, #375, #378 — Postgres-side wedges where pre-v0.18
* brains crashed on `column "source_id" does not exist`.
*
* NOTE: snapshot-based historical state simulation is out of scope for this
* wave (would require maintaining historical schema dumps). The test
* mutates a fresh-LATEST brain to a pre-v0.18 shape; codex flagged this as
* approximate. Acceptable here because the bootstrap's contract is narrow:
* "given a brain that lacks the specific forward-references, initSchema
* produces a brain at LATEST." The test exercises exactly that contract.
*
* Run: DATABASE_URL=postgresql://... bun run test:e2e test/e2e/postgres-bootstrap.test.ts
*/
import { describe, test, expect, beforeAll, afterAll } from 'bun:test';
import { PostgresEngine } from '../../src/core/postgres-engine.ts';
import { LATEST_VERSION } from '../../src/core/migrate.ts';
import { readFactsEmbeddingDim } from '../../src/core/embedding-dim-check.ts';
import { isolatedPersistencePostgres } from '../helpers/persistence-postgres.ts';
import { applyPostgresForwardReferenceBootstrap } from '../../src/core/engine-sql/bootstrap.ts';
const DATABASE_URL = process.env.DATABASE_URL;
const skip = !DATABASE_URL;
describe.skipIf(skip)('PostgresEngine forward-reference bootstrap (E2E)', () => {
let engine: PostgresEngine;
let close: (() => Promise<void>) | undefined;
beforeAll(async () => {
const fixture = await isolatedPersistencePostgres(DATABASE_URL!, 'module');
engine = fixture.engine;
close = fixture.close;
}, 30_000);
afterAll(async () => {
await close?.();
});
test('fact embedding identity bootstrap repairs old and partial schemas without inventing provenance', async () => {
await engine.initSchema();
const { dims } = await readFactsEmbeddingDim(engine);
const fact = await engine.insertFact({ fact: 'Synthetic bootstrap claim', source: 'synthetic', embedding: new Float32Array(dims!).fill(0.1) }, { source_id: 'default' });
const original = await engine.executeRaw('SELECT id,fact,embedding::text,embedded_at FROM facts WHERE id=$1', [fact.id]);
expect(original[0].embedding).not.toBeNull();
for (const missing of [['embedding_model', 'embedded_text_hash'], ['embedding_model'], ['embedded_text_hash']]) {
const conn = await (engine as any).sql.reserve();
try {
await conn.unsafe('SELECT pg_advisory_lock(42)');
await conn.unsafe("UPDATE facts SET embedding_model='synthetic:original',embedded_text_hash='synthetic-preserved-hash' WHERE id=$1", [fact.id]);
for (const column of missing) await conn.unsafe(`ALTER TABLE facts DROP COLUMN ${column}`);
await conn.unsafe("UPDATE config SET value='165' WHERE key='version'");
await applyPostgresForwardReferenceBootstrap(conn);
await applyPostgresForwardReferenceBootstrap(conn);
expect((await conn.unsafe("SELECT value FROM config WHERE key='version'"))[0].value).toBe('165');
expect(await conn.unsafe(`SELECT column_name,data_type,is_nullable,column_default FROM information_schema.columns
WHERE table_schema=current_schema() AND table_name='facts' AND column_name IN ('embedding_model','embedded_text_hash') ORDER BY column_name`)).toEqual([
{ column_name: 'embedded_text_hash', data_type: 'text', is_nullable: 'YES', column_default: null },
{ column_name: 'embedding_model', data_type: 'text', is_nullable: 'YES', column_default: null },
]);
} finally {
await conn.unsafe('SELECT pg_advisory_unlock(42)');
conn.release();
}
const identity = [{
embedding_model: missing.includes('embedding_model') ? null : 'synthetic:original',
embedded_text_hash: missing.includes('embedded_text_hash') ? null : 'synthetic-preserved-hash',
}];
expect(await engine.executeRaw('SELECT embedding_model,embedded_text_hash FROM facts WHERE id=$1', [fact.id])).toEqual(identity);
for (const column of missing) await engine.executeRaw(`ALTER TABLE facts DROP COLUMN ${column}`);
await engine.initSchema();
await engine.initSchema();
expect(await engine.getConfig('version')).toBe(String(LATEST_VERSION));
expect(await engine.executeRaw('SELECT embedding_model,embedded_text_hash FROM facts WHERE id=$1', [fact.id])).toEqual(identity);
expect(await engine.executeRaw('SELECT id,fact,embedding::text,embedded_at FROM facts WHERE id=$1', [fact.id])).toEqual(original);
}
}, 60_000);
test('grant bootstrap repairs a partial installation without changing existing client policy', async () => {
await engine.initSchema();
const conn = await (engine as any).sql.reserve();
try {
await conn.unsafe('SELECT pg_advisory_lock(42)');
await conn.unsafe(`
INSERT INTO oauth_clients (client_id, client_name, scope, grant_revision)
VALUES ('fixture-bootstrap-client', 'fixture client', 'read', 4);
ALTER TABLE oauth_clients DROP COLUMN grant_profile;
ALTER TABLE oauth_clients DROP COLUMN allowed_operations;
`);
await expect((async () => { await conn.unsafe('SELECT grant_profile FROM oauth_clients LIMIT 1'); })())
.rejects.toThrow('does not exist');
await applyPostgresForwardReferenceBootstrap(conn);
await applyPostgresForwardReferenceBootstrap(conn);
expect(await conn.unsafe(`
SELECT scope, grant_revision, grant_profile, allowed_operations
FROM oauth_clients WHERE client_id = 'fixture-bootstrap-client'
`)).toEqual([{ scope: 'read', grant_revision: 4, grant_profile: null, allowed_operations: null }]);
} finally {
await applyPostgresForwardReferenceBootstrap(conn);
await conn.unsafe("DELETE FROM oauth_clients WHERE client_id = 'fixture-bootstrap-client'");
await conn.unsafe('SELECT pg_advisory_unlock(42)');
conn.release();
}
}, 30_000);
test('queue bootstrap preserves historical NULL authority and repairs either missing protocol column', async () => {
await engine.initSchema();
const conn = await (engine as any).sql.reserve();
try {
await conn.unsafe('SELECT pg_advisory_lock(42)');
await conn.unsafe(`
TRUNCATE minion_jobs RESTART IDENTITY CASCADE;
DROP TRIGGER IF EXISTS minion_queue_protocol ON minion_jobs;
ALTER TABLE minion_jobs DROP COLUMN submission_authority;
ALTER TABLE minion_jobs DROP COLUMN claim_generation;
INSERT INTO minion_jobs (name, status, data, attempts_made, attempts_started, delay_until)
VALUES ('sync', 'delayed', '{"sourceId":"default"}', 1, 2, '2026-09-01T00:00:00Z'),
('lint', 'completed', '{}', 0, 1, NULL);
`);
const snapshotSql = 'SELECT id, name, status, data, attempts_made, attempts_started, delay_until FROM minion_jobs ORDER BY id';
const original = await conn.unsafe(snapshotSql);
await applyPostgresForwardReferenceBootstrap(conn);
await applyPostgresForwardReferenceBootstrap(conn);
expect(await conn.unsafe(snapshotSql)).toEqual(original);
expect(await conn.unsafe('SELECT submission_authority, claim_generation::int FROM minion_jobs ORDER BY id'))
.toEqual([{ submission_authority: null, claim_generation: 0 }, { submission_authority: null, claim_generation: 0 }]);
await conn.unsafe(`ALTER TABLE minion_jobs DROP COLUMN submission_authority;
UPDATE minion_jobs SET claim_generation = 7 WHERE name = 'lint';`);
await applyPostgresForwardReferenceBootstrap(conn);
expect(await conn.unsafe("SELECT submission_authority, claim_generation::int FROM minion_jobs WHERE name = 'lint'"))
.toEqual([{ submission_authority: null, claim_generation: 7 }]);
await conn.unsafe(`ALTER TABLE minion_jobs DROP COLUMN claim_generation;
UPDATE minion_jobs SET submission_authority = '{"version":1,"kind":"application"}' WHERE name = 'lint';`);
await applyPostgresForwardReferenceBootstrap(conn);
expect(await conn.unsafe("SELECT submission_authority, claim_generation::int FROM minion_jobs WHERE name = 'lint'"))
.toEqual([{ submission_authority: { version: 1, kind: 'application' }, claim_generation: 0 }]);
} finally {
await conn.unsafe('SELECT pg_advisory_unlock(42)');
conn.release();
}
await engine.initSchema();
}, 30_000);
test('PostgresEngine.initSchema applies bootstrap → SCHEMA_SQL → migrations on pre-v0.18 brain', async () => {
// First call: bring the test DB to LATEST shape so we have something to mutate.
await engine.initSchema();
// Clear data from prior tests in the suite. Adding a UNIQUE(slug)
// constraint below would fail if multi-source fixtures left rows with
// duplicate slugs across sources (which is valid under the composite
// UNIQUE this test is undoing).
const conn = (engine as any).sql;
await conn.unsafe(`TRUNCATE pages, content_chunks, links, tags, raw_data, timeline_entries, page_versions, ingest_log RESTART IDENTITY CASCADE`);
// Mutate to pre-v0.18 shape: drop source_id and the sources table.
// The advisory lock is released between initSchema calls, so this
// direct DDL won't deadlock.
await conn.unsafe(`
ALTER TABLE pages DROP CONSTRAINT IF EXISTS pages_source_slug_key;
ALTER TABLE pages ADD CONSTRAINT pages_slug_key UNIQUE (slug);
DROP INDEX IF EXISTS idx_pages_source_id;
ALTER TABLE pages DROP COLUMN IF EXISTS source_id CASCADE;
DROP TABLE IF EXISTS sources CASCADE;
`);
await engine.setConfig('version', '20');
// The path under test: full PostgresEngine.initSchema() including the
// bootstrap call, SCHEMA_SQL replay, and runMigrations chain.
await engine.initSchema();
expect(await engine.getConfig('version')).toBe(String(LATEST_VERSION));
// Verify the forward-referenced column exists after upgrade.
const colCheck = await conn`
SELECT column_name FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'pages'
AND column_name = 'source_id'
`;
expect(colCheck).toHaveLength(1);
// Verify the default source row was seeded.
const srcCheck = await conn`SELECT id FROM sources WHERE id = 'default'`;
expect(srcCheck).toHaveLength(1);
});
test('PostgresEngine.initSchema is idempotent on a brain already at LATEST', async () => {
// Fresh-LATEST brain. Calling initSchema again must not error and must
// not regress the version.
await engine.initSchema();
expect(await engine.getConfig('version')).toBe(String(LATEST_VERSION));
});
test('pre-v121 timeline shape converges to full final shape on REAL Postgres (#2626 wedge class)', async () => {
// The v121 wedge was Postgres-visible in production (blob CREATE INDEX
// on a column migration v121 hadn't added yet); the PGLite twins live in
// test/bootstrap.test.ts. Rewind schema AND the version counter to the
// wedged cohort's true state, then assert full initSchema convergence:
// column + FK + BOTH partial indexes, ledger at LATEST.
await engine.initSchema();
const conn = (engine as any).sql;
await conn.unsafe(`
DROP INDEX IF EXISTS idx_timeline_event_dedup;
DROP INDEX IF EXISTS idx_timeline_event_page;
ALTER TABLE timeline_entries DROP CONSTRAINT IF EXISTS timeline_entries_event_page_id_fkey;
ALTER TABLE timeline_entries DROP COLUMN IF EXISTS event_page_id;
`);
await engine.setConfig('version', '120');
await engine.initSchema();
expect(await engine.getConfig('version')).toBe(String(LATEST_VERSION));
const col = await conn`
SELECT column_name FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'timeline_entries' AND column_name = 'event_page_id'
`;
expect(col).toHaveLength(1);
const fk = await conn`
SELECT conname FROM pg_constraint WHERE conname = 'timeline_entries_event_page_id_fkey'
`;
expect(fk).toHaveLength(1);
const idx = await conn`
SELECT indexname FROM pg_indexes
WHERE tablename = 'timeline_entries'
AND indexname IN ('idx_timeline_event_page', 'idx_timeline_event_dedup')
`;
expect(idx).toHaveLength(2);
}, 60_000);
test('pre-v143 dream_verdicts shape (#4657 wedge class) converges on REAL Postgres', async () => {
// #4657: blob CREATE INDEX dream_verdicts_expires_idx references
// expires_at, added only by migration v143 — every Postgres brain at
// schema v30-v142 wedged on connect. Rewind schema AND version to the
// wedged cohort's state, seed a row so v143's judged_at-derived backfill
// is exercised, then assert full initSchema convergence.
await engine.initSchema();
const conn = (engine as any).sql;
await conn.unsafe(`
DROP INDEX IF EXISTS dream_verdicts_expires_idx;
ALTER TABLE dream_verdicts DROP COLUMN IF EXISTS expires_at;
DELETE FROM dream_verdicts WHERE file_path = '/tmp/i4657.jsonl';
INSERT INTO dream_verdicts (file_path, content_hash, worth_processing, judged_at)
VALUES ('/tmp/i4657.jsonl', 'i4657hash', true, now() - interval '10 days');
`);
await engine.setConfig('version', '142');
await engine.initSchema();
expect(await engine.getConfig('version')).toBe(String(LATEST_VERSION));
// Column converged: NOT NULL with the 30-day default restored by v143.
const col = await conn`
SELECT is_nullable, column_default FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'dream_verdicts' AND column_name = 'expires_at'
`;
expect(col).toHaveLength(1);
expect(col[0].is_nullable).toBe('NO');
expect(String(col[0].column_default)).toContain('30 days');
// Pre-TTL row backfilled from judged_at, not stamped with a fresh 30 days.
const row = await conn`
SELECT (expires_at = judged_at + interval '30 days') AS backfilled
FROM dream_verdicts WHERE file_path = '/tmp/i4657.jsonl'
`;
expect(row).toHaveLength(1);
expect(row[0].backfilled).toBe(true);
// Index restored.
const idx = await conn`
SELECT indexname FROM pg_indexes
WHERE tablename = 'dream_verdicts' AND indexname = 'dream_verdicts_expires_idx'
`;
expect(idx).toHaveLength(1);
// Clean up the seeded row so it doesn't leak into other suites sharing
// the DATABASE_URL database (its expires_at sits ~20 days in the future).
await conn.unsafe(`DELETE FROM dream_verdicts WHERE file_path = '/tmp/i4657.jsonl'`);
}, 60_000);
test('pre-v7 minion_jobs shape (scanner-sweep wedge class) converges on REAL Postgres', async () => {
await engine.initSchema();
const conn = (engine as any).sql;
await conn.unsafe(`
DROP INDEX IF EXISTS idx_minion_jobs_timeout;
DROP INDEX IF EXISTS uniq_minion_jobs_idempotency;
ALTER TABLE minion_jobs DROP COLUMN IF EXISTS timeout_at;
ALTER TABLE minion_jobs DROP COLUMN IF EXISTS idempotency_key;
`);
await engine.initSchema();
const cols = await conn`
SELECT column_name FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'minion_jobs'
AND column_name IN ('timeout_at', 'idempotency_key')
`;
expect(cols).toHaveLength(2);
const idx = await conn`
SELECT indexname FROM pg_indexes
WHERE tablename = 'minion_jobs'
AND indexname IN ('idx_minion_jobs_timeout', 'uniq_minion_jobs_idempotency')
`;
expect(idx).toHaveLength(2);
}, 60_000);
test('pre-v136 minion_jobs private-queue shape (dream-inline lifecycle) converges on REAL Postgres', async () => {
// v0.46.25 (#4332): the private-queue owner/lease columns are migration-
// added AND referenced by the blob partial indexes — the same wedge class
// as v121 and pre-v7 above. Strip all three columns + both indexes, then
// assert the bootstrap → SCHEMA_SQL replay re-adds every piece.
await engine.initSchema();
const conn = (engine as any).sql;
await conn.unsafe(`
DROP INDEX IF EXISTS idx_minion_jobs_private_queue_recovery;
DROP INDEX IF EXISTS idx_minion_jobs_private_queue_owner;
ALTER TABLE minion_jobs DROP COLUMN IF EXISTS private_queue_owner_job_id;
ALTER TABLE minion_jobs DROP COLUMN IF EXISTS private_queue_owner_token;
ALTER TABLE minion_jobs DROP COLUMN IF EXISTS private_queue_lease_until;
`);
await engine.initSchema();
const cols = await conn`
SELECT column_name FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'minion_jobs'
AND column_name IN ('private_queue_owner_job_id', 'private_queue_owner_token', 'private_queue_lease_until')
`;
expect(cols).toHaveLength(3);
const idx = await conn`
SELECT indexname, indexdef FROM pg_indexes
WHERE tablename = 'minion_jobs'
AND indexname IN ('idx_minion_jobs_private_queue_recovery', 'idx_minion_jobs_private_queue_owner')
`;
expect(idx).toHaveLength(2);
// The recovery index must come back PARTIAL — the dream-inline predicate
// is what keeps the startup recovery scan off the general job table.
const recovery = idx.find(
(r: { indexname: string; indexdef: string }) => r.indexname === 'idx_minion_jobs_private_queue_recovery',
);
expect(recovery?.indexdef).toContain('dream-inline-');
}, 60_000);
test('token-only-missing minion_jobs is repaired by the pq_token probe on REAL Postgres (749a7dcb)', async () => {
// Partial-upgrade shape: ONLY private_queue_owner_token is missing.
// Neither blob index references the token, so SCHEMA_SQL replay cannot
// crash on it, and the ledger is already at LATEST so runMigrations won't
// re-run v136 — the ONLY repair path is the minion_jobs_pq_token_exists
// probe (749a7dcb) triggering the bootstrap's three-column ALTER block.
await engine.initSchema();
const conn = (engine as any).sql;
await conn.unsafe(`
ALTER TABLE minion_jobs DROP COLUMN IF EXISTS private_queue_owner_token;
`);
await engine.initSchema();
const cols = await conn`
SELECT column_name FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'minion_jobs'
AND column_name = 'private_queue_owner_token'
`;
expect(cols).toHaveLength(1);
}, 60_000);
test('standalone db.initSchema straddles an old-shaped brain via the shared bootstrap (#4477)', async () => {
// src/core/db.ts's module-level initSchema (used by test/e2e/helpers.ts
// and legacy callers) replays SCHEMA_SQL directly. Pre-fix it ran NO
// forward-reference bootstrap, so a brain whose pages table predates a
// blob-indexed column (here: deleted_at ← pages_deleted_at_purge_idx)
// wedged on the blob's CREATE INDEX. It now shares
// applyPostgresForwardReferenceBootstrap with PostgresEngine.initSchema.
await engine.initSchema();
const conn = (engine as any).sql;
await conn.unsafe(`
DROP INDEX IF EXISTS pages_deleted_at_purge_idx;
ALTER TABLE pages DROP COLUMN IF EXISTS deleted_at CASCADE;
`);
// The engine connected in module-singleton style (PostgresEngine.connect
// delegates to db.connect), so db.initSchema() runs on the SAME pool —
// exactly how test/e2e/helpers.ts drives it. Do NOT db.disconnect()
// here: that would tear down the shared singleton under the engine.
const db = await import('../../src/core/db.ts');
// The path under test: bootstrap → SCHEMA_SQL, no engine.initSchema.
await db.initSchema();
const col = await conn`
SELECT column_name FROM information_schema.columns
WHERE table_schema = current_schema()
AND table_name = 'pages' AND column_name = 'deleted_at'
`;
expect(col).toHaveLength(1);
const idx = await conn`
SELECT indexname FROM pg_indexes
WHERE tablename = 'pages' AND indexname = 'pages_deleted_at_purge_idx'
`;
expect(idx).toHaveLength(1);
// The CASCADE dropped the derivation-purge scope trigger with the column;
// the replay must recreate it once deleted_at exists again.
const trg = await conn`
SELECT tgname FROM pg_trigger
WHERE tgrelid = 'pages'::regclass AND tgname = 'pages_derivation_scope_delete'
`;
expect(trg).toHaveLength(1);
}, 60_000);
// Migration v120 — schema-lint hardening (#1647 / #171). Postgres-only
// assertions (security_invoker has no surface on embedded PGLite).
test('v120: page_links view runs with security_invoker=on (#1647b)', async () => {
await engine.initSchema();
const rows = await engine.executeRaw<{ reloptions: string[] | null }>(
`SELECT c.reloptions FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relname = 'page_links' AND c.relkind = 'v'`,
);
expect(rows.length).toBe(1);
expect(JSON.stringify(rows[0].reloptions ?? [])).toContain('security_invoker=on');
});
test('v120: trigger + event-trigger functions pin search_path, incl auto_enable_rls (#1647a/#171)', async () => {
await engine.initSchema();
const rows = await engine.executeRaw<{ proname: string; proconfig: unknown }>(
`SELECT p.proname, p.proconfig FROM pg_proc p JOIN pg_namespace n ON n.oid = p.pronamespace
WHERE n.nspname = 'public'
AND p.proname IN ('bump_page_generation_fn','bump_page_generation_clock_fn',
'update_chunk_search_vector','update_page_search_vector',
'notify_minion_job_change','auto_enable_rls')`,
);
expect(rows.length).toBeGreaterThanOrEqual(5);
for (const r of rows) {
expect(JSON.stringify(r.proconfig ?? [])).toContain('search_path=');
}
});
});