Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
19.0 KB
Raw Blame
/**
 * Unit tests for `test/helpers/schema-diff.ts`.
 *
 * The pure diff function is the load-bearing piece of the v0.26.3 drift gate
 * (issue #588). If it has bugs, the gate is a paper gate. These tests use
 * synthetic snapshots so they run without a database, and they include the
 * D3 negative case: the v0.26.1 token_ttl regression that motivated the
 * issue in the first place.
 */

import { describe, test, expect } from 'bun:test';
import {
  type SchemaSnapshot,
  type ColumnInfo,
  diffSnapshots,
  formatDiffForFailure,
  isCleanDiff,
  snapshotSchema,
} from './schema-diff.ts';
import { createHash } from 'node:crypto';
import {
  type CatalogSnapshot,
  type RawCatalogRows,
  CATALOG_CURRENT_ROLE,
  CATALOG_QUERIES,
  buildCatalogSnapshot,
  diffCatalogSnapshots,
  formatCatalogDiffForFailure,
  snapshotCatalog,
} from './schema-diff.ts';

function col(partial: Partial<ColumnInfo> = {}): ColumnInfo {
  return {
    dataType: partial.dataType ?? 'text',
    udtName: partial.udtName ?? 'text',
    isNullable: partial.isNullable ?? true,
    columnDefault: partial.columnDefault ?? null,
  };
}

function makeSnap(tables: Record<string, Record<string, ColumnInfo>>): SchemaSnapshot {
  const snap: SchemaSnapshot = new Map();
  for (const [table, cols] of Object.entries(tables)) {
    const m = new Map<string, ColumnInfo>();
    for (const [name, info] of Object.entries(cols)) m.set(name, info);
    snap.set(table, m);
  }
  return snap;
}

const NO_ALLOWLIST = { allowlistPgOnlyTables: [] };

describe('diffSnapshots', () => {
  test('identical snapshots produce a clean diff', () => {
    const a = makeSnap({ pages: { id: col({ udtName: 'uuid' }), title: col() } });
    const b = makeSnap({ pages: { id: col({ udtName: 'uuid' }), title: col() } });
    const diff = diffSnapshots(a, b, NO_ALLOWLIST);
    expect(isCleanDiff(diff)).toBe(true);
  });

  test('column missing in PGLite reports the exact column name', () => {
    const pg = makeSnap({ oauth_clients: { client_id: col(), token_ttl: col({ udtName: 'int4' }) } });
    const pglite = makeSnap({ oauth_clients: { client_id: col() } });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(diff.columnsMissingInPGLite).toEqual([{ table: 'oauth_clients', columns: ['token_ttl'] }]);
    expect(diff.columnsMissingInPostgres).toEqual([]);
    expect(isCleanDiff(diff)).toBe(false);
  });

  test('column present only on PGLite reports columnsMissingInPostgres', () => {
    const pg = makeSnap({ pages: { id: col({ udtName: 'uuid' }) } });
    const pglite = makeSnap({ pages: { id: col({ udtName: 'uuid' }), pglite_only: col() } });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(diff.columnsMissingInPostgres).toEqual([{ table: 'pages', columns: ['pglite_only'] }]);
  });

  test('table missing in PGLite reports tablesMissingInPGLite', () => {
    const pg = makeSnap({ pages: {}, mcp_request_log: { id: col() } });
    const pglite = makeSnap({ pages: {} });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(diff.tablesMissingInPGLite).toEqual(['mcp_request_log']);
  });

  test('table on allowlist is excluded from tablesMissingInPGLite', () => {
    const pg = makeSnap({ files: { id: col() }, pages: { id: col() } });
    const pglite = makeSnap({ pages: { id: col() } });
    const diff = diffSnapshots(pg, pglite, { allowlistPgOnlyTables: ['files'] });
    expect(diff.tablesMissingInPGLite).toEqual([]);
    expect(isCleanDiff(diff)).toBe(true);
  });

  test('udt_name mismatch is reported as type mismatch', () => {
    // The exact codex-flagged regression: access_tokens.id is UUID on Postgres,
    // TEXT on PGLite.
    const pg = makeSnap({ access_tokens: { id: col({ dataType: 'uuid', udtName: 'uuid' }) } });
    const pglite = makeSnap({ access_tokens: { id: col({ dataType: 'text', udtName: 'text' }) } });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(diff.typeMismatches).toHaveLength(1);
    expect(diff.typeMismatches[0]).toMatchObject({
      table: 'access_tokens',
      column: 'id',
      reason: 'udt_name',
    });
  });

  test('array element type mismatch is caught via udt_name', () => {
    // information_schema reports `ARRAY` for both `text[]` and `int[]` in
    // data_type but distinguishes them via udt_name (`_text` vs `_int4`).
    // udt_name catches this; data_type alone would not.
    const pg = makeSnap({ t: { x: col({ dataType: 'ARRAY', udtName: '_text' }) } });
    const pglite = makeSnap({ t: { x: col({ dataType: 'ARRAY', udtName: '_int4' }) } });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(diff.typeMismatches).toHaveLength(1);
    expect(diff.typeMismatches[0].reason).toBe('udt_name');
  });

  test('nullable mismatch is reported when udt_name matches', () => {
    const pg = makeSnap({ t: { x: col({ udtName: 'text', isNullable: false }) } });
    const pglite = makeSnap({ t: { x: col({ udtName: 'text', isNullable: true }) } });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(diff.typeMismatches).toHaveLength(1);
    expect(diff.typeMismatches[0].reason).toBe('is_nullable');
  });

  test('default mismatch is reported when udt_name and nullable match', () => {
    const pg = makeSnap({ t: { x: col({ udtName: 'text', columnDefault: "'a'" }) } });
    const pglite = makeSnap({ t: { x: col({ udtName: 'text', columnDefault: "'b'" }) } });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(diff.typeMismatches).toHaveLength(1);
    expect(diff.typeMismatches[0].reason).toBe('column_default');
  });

  test('default normalisation: ::text type cast is stripped before comparing', () => {
    // Postgres often renders string defaults with `::text`, PGLite without.
    const pg = makeSnap({ t: { x: col({ udtName: 'text', columnDefault: "'hello'::text" }) } });
    const pglite = makeSnap({ t: { x: col({ udtName: 'text', columnDefault: "'hello'" }) } });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(isCleanDiff(diff)).toBe(true);
  });

  test('default normalisation: whitespace + ::jsonb cast collapsed', () => {
    const pg = makeSnap({ t: { meta: col({ udtName: 'jsonb', columnDefault: "  '{}'::jsonb  " }) } });
    const pglite = makeSnap({ t: { meta: col({ udtName: 'jsonb', columnDefault: "'{}'" }) } });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(isCleanDiff(diff)).toBe(true);
  });

  test('multiple issues across multiple tables are all reported', () => {
    const pg = makeSnap({
      a: { x: col({ udtName: 'int4' }), y: col({ udtName: 'text' }) },
      b: { z: col({ udtName: 'uuid' }) },
      c: { only_on_pg: col() },
    });
    const pglite = makeSnap({
      a: { x: col({ udtName: 'int8' }) }, // udt mismatch + missing y
      b: { z: col({ udtName: 'uuid' }), only_on_pglite: col() },
    });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    expect(diff.tablesMissingInPGLite).toEqual(['c']);
    expect(diff.columnsMissingInPGLite).toEqual([{ table: 'a', columns: ['y'] }]);
    expect(diff.columnsMissingInPostgres).toEqual([{ table: 'b', columns: ['only_on_pglite'] }]);
    expect(diff.typeMismatches).toHaveLength(1);
    expect(diff.typeMismatches[0]).toMatchObject({ table: 'a', column: 'x', reason: 'udt_name' });
  });
});

describe('formatDiffForFailure', () => {
  test('clean diff renders as "no diff"', () => {
    const diff = diffSnapshots(makeSnap({ pages: {} }), makeSnap({ pages: {} }), NO_ALLOWLIST);
    expect(formatDiffForFailure(diff)).toBe('no diff');
  });

  test('column missing names the column with a copy-paste hint', () => {
    const pg = makeSnap({ oauth_clients: { token_ttl: col({ udtName: 'int4' }) } });
    const pglite = makeSnap({ oauth_clients: {} });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    const out = formatDiffForFailure(diff);
    expect(out).toContain('oauth_clients.token_ttl');
    expect(out).toContain('src/schema.sql');
    expect(out).toContain('bun run build:schema');
  });

  test('udt mismatch shows both sides', () => {
    const pg = makeSnap({ access_tokens: { id: col({ dataType: 'uuid', udtName: 'uuid' }) } });
    const pglite = makeSnap({ access_tokens: { id: col({ dataType: 'text', udtName: 'text' }) } });
    const diff = diffSnapshots(pg, pglite, NO_ALLOWLIST);
    const out = formatDiffForFailure(diff);
    expect(out).toContain('access_tokens.id');
    expect(out).toContain('uuid');
    expect(out).toContain('text');
  });
});

describe('D3 regression: oauth_clients.token_ttl drift (the v0.26.1 incident)', () => {
  // The bug that motivated issue #588: prod Postgres got `token_ttl` and
  // `deleted_at` via manual ALTER TABLE; PGLite did not. All unit tests
  // broke with `column "token_ttl" does not exist`. If this gate had
  // existed at v0.26.1 it would have caught the divergence at PR time
  // (the prod ALTER and the PGLite update would have travelled together
  // through CI).
  test('drift gate would have caught the v0.26.1 token_ttl + deleted_at bug', () => {
    const pgSide = makeSnap({
      oauth_clients: {
        client_id: col({ udtName: 'text', isNullable: false }),
        token_ttl: col({ udtName: 'int4' }),
        deleted_at: col({ udtName: 'timestamptz' }),
      },
    });
    const pgliteSide = makeSnap({
      oauth_clients: {
        client_id: col({ udtName: 'text', isNullable: false }),
        // token_ttl + deleted_at MISSING — exactly the v0.26.1 state
      },
    });
    const diff = diffSnapshots(pgSide, pgliteSide, NO_ALLOWLIST);
    expect(diff.columnsMissingInPGLite).toHaveLength(1);
    expect(diff.columnsMissingInPGLite[0].table).toBe('oauth_clients');
    expect(diff.columnsMissingInPGLite[0].columns).toEqual(['token_ttl', 'deleted_at']);
    const message = formatDiffForFailure(diff);
    expect(message).toContain('token_ttl');
    expect(message).toContain('deleted_at');
    expect(message).toContain('oauth_clients');
  });
});

describe('snapshotSchema', () => {
  test('aggregates rows from the query callback into a Map<table, Map<column>>', async () => {
    const fakeRows = [
      { table_name: 'pages', column_name: 'id',    data_type: 'uuid', udt_name: 'uuid', is_nullable: 'NO',  column_default: 'gen_random_uuid()' },
      { table_name: 'pages', column_name: 'title', data_type: 'text', udt_name: 'text', is_nullable: 'YES', column_default: null },
      { table_name: 'tags',  column_name: 'tag',   data_type: 'text', udt_name: 'text', is_nullable: 'NO',  column_default: null },
    ];
    const snap = await snapshotSchema(async () => fakeRows);
    expect(snap.get('pages')!.get('id')).toEqual({
      dataType: 'uuid',
      udtName: 'uuid',
      isNullable: false,
      columnDefault: 'gen_random_uuid()',
    });
    expect(snap.get('pages')!.get('title')!.isNullable).toBe(true);
    expect(snap.get('tags')!.get('tag')!.columnDefault).toBeNull();
  });
});

// ─── Refactor wave 1 E4 / T-G13: catalog-level snapshot ───────────────

function rawCatalog(partial: Partial<RawCatalogRows> = {}): RawCatalogRows {
  return {
    currentRole: [{ role: 'ci_superuser' }],
    relations: [],
    columns: [],
    indexes: [],
    constraints: [],
    triggers: [],
    functions: [],
    views: [],
    policies: [],
    grants: [],
    sequences: [],
    extensions: [],
    ...partial,
  };
}

function column(table: string, name: string, ordinal: number, extra: Record<string, unknown> = {}) {
  return { table_name: table, column_name: name, ordinal_position: ordinal, data_type: 'text', udt_name: 'text', is_nullable: 'YES', column_default: null, ...extra };
}

describe('buildCatalogSnapshot', () => {
  test('orders tables by name and columns by ordinal, regardless of row order', () => {
    const snap = buildCatalogSnapshot(rawCatalog({
      relations: [
        { name: 'tags', relkind: 'r', row_security: false, force_row_security: false },
        { name: 'pages', relkind: 'r', row_security: 't', force_row_security: 'f' },
      ],
      columns: [column('pages', 'title', 2), column('tags', 'tag', 1), column('pages', 'id', 1, { is_nullable: 'NO', udt_name: 'int4' })],
    }));
    expect(snap.tables.map((t) => t.name)).toEqual(['pages', 'tags']);
    expect(snap.tables[0]).toEqual({
      name: 'pages',
      relkind: 'r',
      rowSecurity: true,
      forceRowSecurity: false,
      columns: [
        { name: 'id', ordinal: 1, dataType: 'text', udtName: 'int4', isNullable: false, columnDefault: null },
        { name: 'title', ordinal: 2, dataType: 'text', udtName: 'text', isNullable: true, columnDefault: null },
      ],
    });
  });

  test('view columns are reported under viewColumns, not tables', () => {
    const snap = buildCatalogSnapshot(rawCatalog({
      relations: [{ name: 'page_links', relkind: 'v', row_security: false, force_row_security: false }],
      columns: [column('page_links', 'slug', 1)],
      views: [{ name: 'page_links', kind: 'v', definition: ' SELECT 1;' }],
    }));
    expect(snap.tables).toEqual([]);
    expect(snap.viewColumns).toEqual([{ view: 'page_links', columns: [{ name: 'slug', ordinal: 1, dataType: 'text', udtName: 'text', isNullable: true, columnDefault: null }] }]);
    expect(snap.views).toEqual([{ name: 'page_links', kind: 'v', definitionSha256: createHash('sha256').update(' SELECT 1;').digest('hex') }]);
  });

  test('function bodies are pinned by sha256; config lines split; booleans coerced', () => {
    const body = 'BEGIN RETURN NEW; END;';
    const snap = buildCatalogSnapshot(rawCatalog({
      functions: [{
        name: 'touch', identity_arguments: '', result: 'trigger', kind: 'f', language: 'plpgsql',
        volatility: 'v', security_definer: 't', config: 'search_path=pg_catalog, public\nwork_mem=64kB', body,
      }],
    }));
    expect(snap.functions).toEqual([{
      name: 'touch', identityArguments: '', result: 'trigger', kind: 'f', language: 'plpgsql', volatility: 'v',
      securityDefiner: true, config: ['search_path=pg_catalog, public', 'work_mem=64kB'],
      bodySha256: createHash('sha256').update(body).digest('hex'),
    }]);
  });

  test('the connecting role is placeholdered in grants and policies; other roles are kept', () => {
    const snap = buildCatalogSnapshot(rawCatalog({
      grants: [
        { table_name: 'pages', grantor: 'ci_superuser', grantee: 'ci_superuser', privilege: 'SELECT', is_grantable: 'YES' },
        { table_name: 'pages', grantor: 'ci_superuser', grantee: 'ci_superuser', privilege: 'INSERT', is_grantable: 'YES' },
        { table_name: 'pages', grantor: 'ci_superuser', grantee: 'PUBLIC', privilege: 'SELECT', is_grantable: 'NO' },
      ],
      policies: [{ table_name: 'pages', name: 'p_read', permissive: 'PERMISSIVE', roles: 'ci_superuser\nreader_example', command: 'SELECT', using_expr: 'true', with_check: null }],
    }));
    expect(snap.grants).toEqual([
      { table: 'pages', grantor: CATALOG_CURRENT_ROLE, grantee: CATALOG_CURRENT_ROLE, grantable: true, privileges: ['INSERT', 'SELECT'] },
      { table: 'pages', grantor: CATALOG_CURRENT_ROLE, grantee: 'PUBLIC', grantable: false, privileges: ['SELECT'] },
    ]);
    expect(snap.policies[0].roles).toEqual([CATALOG_CURRENT_ROLE, 'reader_example']);
    expect(snap.policies[0].withCheck).toBeNull();
  });

  test('extensions and sequences are sorted; constraints keep CHECK text verbatim', () => {
    const snap = buildCatalogSnapshot(rawCatalog({
      extensions: [{ name: 'vector' }, { name: 'pg_trgm' }],
      sequences: [
        { name: 'z_seq', data_type: 'bigint', start_value: '1', min_value: '1', max_value: '9', increment_by: '1', cycle: false, cache_size: '1', owned_by: null },
        { name: 'a_seq', data_type: 'integer', start_value: '1', min_value: '1', max_value: '9', increment_by: '1', cycle: 't', cache_size: '1', owned_by: 'pages.id' },
      ],
      constraints: [{ name: 'pages_kind_check', table_name: 'pages', type: 'c', definition: "CHECK ((kind = ANY (ARRAY['a'::text, 'b'::text])))" }],
    }));
    expect(snap.extensions).toEqual(['pg_trgm', 'vector']);
    expect(snap.sequences.map((s) => [s.name, s.cycle, s.ownedBy])).toEqual([['a_seq', true, 'pages.id'], ['z_seq', false, null]]);
    expect(snap.constraints[0].definition).toBe("CHECK ((kind = ANY (ARRAY['a'::text, 'b'::text])))");
  });
});

describe('snapshotCatalog', () => {
  test('runs every catalog section query exactly once through the callback', async () => {
    const seen: string[] = [];
    const snap = await snapshotCatalog(async (sql) => {
      seen.push(sql);
      if (sql === CATALOG_QUERIES.currentRole) return [{ role: 'postgres' }];
      if (sql === CATALOG_QUERIES.extensions) return [{ name: 'plpgsql' }];
      return [];
    });
    expect(seen).toEqual(Object.values(CATALOG_QUERIES));
    expect(snap.extensions).toEqual(['plpgsql']);
  });

  test('queries exclude extension-owned objects and internal triggers', () => {
    expect(CATALOG_QUERIES.functions).toContain("d.deptype = 'e'");
    expect(CATALOG_QUERIES.relations).toContain("d.deptype = 'e'");
    expect(CATALOG_QUERIES.triggers).toContain('NOT tg.tgisinternal');
  });
});

describe('diffCatalogSnapshots (T-G13: ordinals only for same-engine comparisons)', () => {
  function snapWithColumns(order: Array<[string, number]>): CatalogSnapshot {
    return buildCatalogSnapshot(rawCatalog({
      relations: [{ name: 'pages', relkind: 'r', row_security: false, force_row_security: false }],
      columns: order.map(([name, ordinal]) => column('pages', name, ordinal)),
    }));
  }

  test('identical snapshots diff clean', () => {
    const a = snapWithColumns([['id', 1], ['title', 2]]);
    expect(diffCatalogSnapshots(a, snapWithColumns([['id', 1], ['title', 2]]), { compareOrdinals: true })).toEqual([]);
    expect(formatCatalogDiffForFailure([])).toBe('no diff');
  });

  test('a column reorder is invisible name-based and reported with compareOrdinals', () => {
    const master = snapWithColumns([['id', 1], ['title', 2]]);
    const branch = snapWithColumns([['title', 1], ['id', 2]]);
    expect(diffCatalogSnapshots(master, branch)).toEqual([]);
    const strict = diffCatalogSnapshots(master, branch, { compareOrdinals: true });
    expect(strict.map((d) => [d.section, d.key, d.kind])).toEqual([
      ['tables', 'pages.id', 'changed'],
      ['tables', 'pages.title', 'changed'],
    ]);
    expect(formatCatalogDiffForFailure(strict)).toContain('pages.id changed');
  });

  test('missing and unexpected objects are reported per section', () => {
    const a = buildCatalogSnapshot(rawCatalog({ indexes: [{ name: 'idx_a', table_name: 'pages', definition: 'CREATE INDEX idx_a ON public.pages (a)' }] }));
    const b = buildCatalogSnapshot(rawCatalog({ indexes: [{ name: 'idx_b', table_name: 'pages', definition: 'CREATE INDEX idx_b ON public.pages (b)' }] }));
    const diff = diffCatalogSnapshots(a, b);
    expect(diff.map((d) => [d.section, d.key, d.kind])).toEqual([
      ['indexes', 'idx_a', 'missing_in_actual'],
      ['indexes', 'idx_b', 'unexpected_in_actual'],
    ]);
  });

  test('existing cross-engine diffSnapshots stays name-based (column order ignored)', () => {
    const pg = makeSnap({ pages: { id: col({ udtName: 'int4' }), title: col() } });
    const pglite = makeSnap({ pages: { title: col(), id: col({ udtName: 'int4' }) } });
    expect(isCleanDiff(diffSnapshots(pg, pglite, NO_ALLOWLIST))).toBe(true);
  });
});