Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
14.2 KB
Raw Blame
/**
 * E2E schema drift gate (issue #588, v0.26.3).
 *
 * Spins up a fresh PGLite instance and a fresh Postgres database, runs each
 * engine's `initSchema()` end-to-end (bootstrap + schema replay + migrations),
 * snapshots `information_schema.columns` from both, then diffs the snapshots
 * via the pure helper in `test/helpers/schema-diff.ts`.
 *
 * Catches the v0.26.1 bug class: someone adds columns to one engine path
 * (raw schema.sql, raw pglite-schema.ts, or a sqlFor branch in a migration)
 * but forgets the other side. Both engines must produce the same end-state.
 *
 * Out of scope: detecting "manual ALTER TABLE on production Postgres that
 * never made it into source files" (the actual v0.26.1 trigger). That
 * requires comparing prod's information_schema against source — a separate
 * `gbrain doctor --schema-audit` mechanism deferred to v0.26.4.
 *
 * Skips gracefully when DATABASE_URL is unset (matches the existing E2E
 * pattern in test/e2e/postgres-bootstrap.test.ts and test/e2e/postgres-jsonb.test.ts).
 *
 * Run: DATABASE_URL=postgresql://... bun test test/e2e/schema-drift.test.ts
 *  Or: bun run ci:local  (the full Docker-backed gate)
 */

import { describe, test, expect, beforeAll, afterAll } from 'bun:test';
import { PGLiteEngine } from '../../src/core/pglite-engine.ts';
import { PostgresEngine } from '../../src/core/postgres-engine.ts';
import {
  type SchemaSnapshot,
  type SnapshotQueryRow,
  type IndexSnapshot,
  type IndexSnapshotRow,
  snapshotSchema,
  diffSnapshots,
  formatDiffForFailure,
  isCleanDiff,
  snapshotIndexes,
  diffIndexSnapshots,
  isCleanIndexDiff,
  formatIndexDiffForFailure,
} from '../helpers/schema-diff.ts';

const DATABASE_URL = process.env.DATABASE_URL;
const skip = !DATABASE_URL;

if (skip) {
  console.log('Skipping E2E schema drift gate (DATABASE_URL not set)');
}

// Tier 3 opt-out: this file constructs a fresh in-memory PGLite to compare
// against fresh Postgres. If GBRAIN_PGLITE_SNAPSHOT is set (ci:local sets it
// for unit shards), PGLite would boot post-initSchema with a snapshot — fine
// for the comparison, but we want the canonical path here.
delete process.env.GBRAIN_PGLITE_SNAPSHOT;

/**
 * Tables that exist in src/schema.sql but are intentionally absent from
 * src/core/pglite-schema.ts (and from the migrations chain on the PGLite
 * side). Whenever something is added to this list, add an inline reason.
 *
 * v0.27.1: `files` removed from this list — multimodal ingestion needed
 * binary-asset metadata on PGLite, and migration v36 adds the table on
 * the PGLite side mirroring the Postgres v0.18 shape verbatim. Now a
 * parity-required table on both engines.
 */
const PG_ONLY_TABLES = [
  // file_migration_ledger drives the v0.18 storage-object rewrite on
  // Postgres. PGLite never had blob storage so the ledger has no consumer.
  'file_migration_ledger',
];

/**
 * Index names that exist on exactly one engine on purpose. Same discipline as
 * PG_ONLY_TABLES: every entry carries an inline reason, and the defensive test
 * below fails if an entry stops being one-sided (a stale allowlist row would
 * silently shadow future drift).
 */
const INDEX_ALLOWLIST: string[] = [
  // Lives on file_migration_ledger, which is on PG_ONLY_TABLES above (PGLite
  // never had blob storage, so neither the table nor its index exist there).
  'idx_file_migration_ledger_status',
  // Migration 76 creates this partial GIN on minion_jobs.data on Postgres only
  // (sqlFor.pglite is deliberately empty): PGLite's single-host doctor audit
  // query is fine with a sequential scan, per the migration's own comment.
  'minion_jobs_doctor_run_id_idx',
];

describe.skipIf(skip)('schema drift: PGLite ↔ Postgres post-initSchema parity (E2E)', () => {
  let pglite: PGLiteEngine;
  let pg: PostgresEngine;
  let pgliteSnap: SchemaSnapshot;
  let pgSnap: SchemaSnapshot;
  let pgliteIdxSnap: IndexSnapshot;
  let pgIdxSnap: IndexSnapshot;

  beforeAll(async () => {
    // PGLite side: in-memory, run the canonical initSchema.
    pglite = new PGLiteEngine();
    await pglite.connect({});
    await pglite.initSchema();

    // Postgres side: ensure the test database is FRESH before initSchema.
    // v0.37.2.0 fix: previously the test trusted the caller to pass a fresh
    // DATABASE_URL, but `gbrain doctor` (used by the CLAUDE.md E2E bootstrap
    // ritual) populates `content_chunks.model DEFAULT` from the configured
    // gateway model. On a re-run, `CREATE TABLE IF NOT EXISTS` is a no-op so
    // the stale default sticks while PGLite (always fresh-in-memory) gets the
    // engine fallback. That produced a phantom drift unrelated to schema
    // parity.
    //
    // SAFETY GATE (codex P0, tightened in v0.37.2.0): DROP SCHEMA public CASCADE is
    // destructive. The db name MUST always look test-shaped — no env-var override
    // bypasses that floor. GBRAIN_TEST_DB=1 only relaxes the localhost requirement
    // so CI environments where the host is a service name (e.g. "postgres") can
    // still reset. If the db name doesn't match the test pattern, nothing nukes it.
    pg = new PostgresEngine();
    await pg.connect({ database_url: DATABASE_URL! });
    const pgConnPre = (pg as any).sql;

    const url = new URL(DATABASE_URL!);
    const dbName = url.pathname.replace(/^\//, '');
    const host = url.hostname;
    const isLocalhost = host === 'localhost' || host === '127.0.0.1' || host.endsWith('.local');
    // db-name pattern is the floor: gbrain_test, *_test, test_*, *_e2e.
    // Required REGARDLESS of any override — a production db named "production_data"
    // cannot be reset even with GBRAIN_TEST_DB=1.
    const looksLikeTestDb = /^(gbrain_test|.*_test|test_.*|.*_e2e)$/i.test(dbName);
    const ciOptIn = process.env.GBRAIN_TEST_DB === '1';
    // resetAllowed semantics: db name is test-shaped AND (localhost OR ci-opt-in).
    // Neither host nor env-var alone is sufficient.
    const resetAllowed = looksLikeTestDb && (isLocalhost || ciOptIn);

    if (resetAllowed) {
      await pgConnPre.unsafe('DROP SCHEMA IF EXISTS public CASCADE; CREATE SCHEMA public;');
    } else {
      // Surface a loud, paste-ready hint. The test will still try initSchema;
      // if the caller already had a fresh DB the parity check passes anyway.
      const reason = !looksLikeTestDb
        ? `db name "${dbName}" doesn't match the test pattern (gbrain_test, *_test, test_*, *_e2e). ` +
          `GBRAIN_TEST_DB=1 does NOT override this — db name is the hard floor.`
        : `host="${host}" is non-local AND GBRAIN_TEST_DB=1 is not set. ` +
          `Set GBRAIN_TEST_DB=1 to allow non-local hosts (e.g. CI service names) — ` +
          `but only when the db name is already test-shaped.`;
      console.warn(`[schema-drift] Skipping DROP SCHEMA — ${reason}`);
    }

    await pg.initSchema();

    // Snapshot both. PGLite returns `{rows}`, postgres.js returns the array.
    const pgliteDb = (pglite as any).db;
    pgliteSnap = await snapshotSchema(async (sql) => {
      const r = await pgliteDb.query(sql);
      return r.rows as SnapshotQueryRow[];
    });

    const pgConn = (pg as any).sql;
    pgSnap = await snapshotSchema(async (sql) => {
      const r = await pgConn.unsafe(sql);
      return r as unknown as SnapshotQueryRow[];
    });

    // Index snapshots (plan D1): same engines, pg_catalog-based helper query.
    pgliteIdxSnap = await snapshotIndexes(async (sql) => {
      const r = await pgliteDb.query(sql);
      return r.rows as IndexSnapshotRow[];
    });
    pgIdxSnap = await snapshotIndexes(async (sql) => {
      const r = await pgConn.unsafe(sql);
      return r as unknown as IndexSnapshotRow[];
    });
  }, 60_000);

  afterAll(async () => {
    if (pglite) await pglite.disconnect();
    if (pg) await pg.disconnect();
  }, 30_000);

  test('post-initSchema schemas are equivalent (modulo allowlist)', () => {
    // F4b planner-stats accounting is PGLite-only by design (Postgres has autovacuum).
    const diff = diffSnapshots(pgSnap, pgliteSnap, { allowlistPgOnlyTables: PG_ONLY_TABLES, allowlistPgliteOnlyTables: ['planner_stats_deltas', 'planner_stats_state'] });
    if (!isCleanDiff(diff)) {
      throw new Error(`Schema drift detected:\n${formatDiffForFailure(diff)}`);
    }
    expect(isCleanDiff(diff)).toBe(true);
  });

  // Sentinel cases. Each is the v0.26.1 bug class for one specific table.
  // Failing here gives a tighter blame message than the global parity test.
  for (const sentinel of ['oauth_clients', 'mcp_request_log', 'access_tokens', 'eval_candidates']) {
    test(`regression #588: ${sentinel} columns match across engines`, () => {
      const pgCols = pgSnap.get(sentinel);
      const pgliteCols = pgliteSnap.get(sentinel);
      expect(pgCols, `${sentinel} missing from Postgres post-initSchema`).toBeDefined();
      expect(pgliteCols, `${sentinel} missing from PGLite post-initSchema`).toBeDefined();
      const diff = diffSnapshots(
        new Map([[sentinel, pgCols!]]),
        new Map([[sentinel, pgliteCols!]]),
        { allowlistPgOnlyTables: [] },
      );
      if (!isCleanDiff(diff)) {
        throw new Error(`Drift on ${sentinel}:\n${formatDiffForFailure(diff)}`);
      }
    });
  }

  test('Postgres-only tables on the allowlist are still absent from PGLite', () => {
    // Defensive: if someone adds `files` to PGLite without removing it from
    // the allowlist, we want to know — the allowlist would silently shadow
    // a real divergence in coverage policy.
    for (const t of PG_ONLY_TABLES) {
      expect(pgSnap.has(t), `${t} should be in Postgres schema`).toBe(true);
      expect(pgliteSnap.has(t), `${t} unexpectedly added to PGLite — remove from allowlist`).toBe(false);
    }
  });

  // ─── plan D1 — index parity ─────────────────────────────────────────
  // The column-level drift gate above stays green when an index lands on one
  // engine but not the other (the v0.34 W4-5 class: hot-path composite index
  // silently missing on one side). snapshotIndexes/diffIndexSnapshots have
  // existed in test/helpers/schema-diff.ts since v0.34 D7 but were never wired
  // to real engines until now.
  describe('index parity: PGLite ↔ Postgres post-initSchema (plan D1)', () => {
    test('post-initSchema index sets are equivalent (modulo allowlist)', () => {
      const diff = diffIndexSnapshots(pgIdxSnap, pgliteIdxSnap, { allowlist: INDEX_ALLOWLIST });
      if (!isCleanIndexDiff(diff)) {
        throw new Error(`Index drift detected:\n${formatIndexDiffForFailure(diff)}`);
      }
      expect(isCleanIndexDiff(diff)).toBe(true);
    });

    test('allowlisted indexes are still one-sided (Postgres-only)', () => {
      // Defensive mirror of the PG_ONLY_TABLES test: a stale allowlist entry
      // would silently shadow real drift on that index forever.
      for (const name of INDEX_ALLOWLIST) {
        expect(pgIdxSnap.has(name), `${name} should exist on Postgres`).toBe(true);
        expect(pgliteIdxSnap.has(name), `${name} unexpectedly added to PGLite — remove from INDEX_ALLOWLIST`).toBe(false);
      }
    });

    // The three ON-CONFLICT-load-bearing unique indexes. Each backs an
    // `ON CONFLICT` target in a write path; if one goes missing (or loses its
    // predicate/expression), those writes start throwing 42P10 at runtime.
    // Assert them by their REAL names with a direct pg_indexes query per
    // engine so a failure names the exact engine and the exact index.
    const ON_CONFLICT_INDEXES: Array<{
      name: string;
      table: string;
      /** Substrings that must appear in the whitespace-normalized, lowercased indexdef. */
      defMust: string[];
    }> = [
      {
        // take-proposal idempotency (migration 125 / schema.sql): unique
        // EXPRESSION index — per-claim via md5(claim_text), no WHERE clause.
        name: 'take_proposals_idempotency_idx',
        table: 'take_proposals',
        defMust: [
          'create unique index',
          '(source_id, page_slug, content_hash, prompt_version, md5(claim_text))',
        ],
      },
      {
        // Life Chronicle event-projection dedup: partial unique on
        // (event_page_id, date) WHERE event_page_id IS NOT NULL.
        name: 'idx_timeline_event_dedup',
        table: 'timeline_entries',
        defMust: [
          'create unique index',
          '(event_page_id, date)',
          'where (event_page_id is not null)',
        ],
      },
      {
        // Facts ontology dedup (migration 188): partial unique keyed on the
        // deterministic value_hash plus the stint's valid_from, scoped
        // WHERE dimension IS NOT NULL.
        name: 'idx_facts_ontology_stint_dedup',
        table: 'facts',
        defMust: [
          'create unique index',
          '(source_id, entity_slug, dimension, value_hash, source_markdown_slug, valid_from)',
          'where (dimension is not null)',
        ],
      },
    ];

    /** Match the diff helper's normalization: collapse whitespace, lowercase. */
    const norm = (s: string) => s.replace(/\s+/g, ' ').trim().toLowerCase();

    for (const idx of ON_CONFLICT_INDEXES) {
      test(`ON-CONFLICT index ${idx.name} exists with the right shape on both engines`, async () => {
        const sql = `
          SELECT indexname, tablename, indexdef
          FROM pg_indexes
          WHERE schemaname = 'public' AND indexname = '${idx.name}'
        `;
        const pgliteDb = (pglite as any).db;
        const pgConn = (pg as any).sql;
        const engines: Array<{ label: string; rows: Array<{ indexname: string; tablename: string; indexdef: string }> }> = [
          { label: 'Postgres', rows: (await pgConn.unsafe(sql)) as any },
          { label: 'PGLite', rows: (await pgliteDb.query(sql)).rows as any },
        ];
        for (const { label, rows } of engines) {
          expect(rows.length, `${idx.name} missing from ${label} pg_indexes`).toBe(1);
          const row = rows[0]!;
          expect(row.tablename, `${idx.name} on wrong table on ${label}`).toBe(idx.table);
          const def = norm(row.indexdef);
          for (const must of idx.defMust) {
            if (!def.includes(must)) {
              throw new Error(
                `${label}: ${idx.name} indexdef is missing "${must}".\n  Actual: ${row.indexdef}`,
              );
            }
          }
        }
      });
    }
  });
});