Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
/**
* Schema parity helpers for the v0.26.3 drift gate (issue #588).
*
* Strategy: snapshot `information_schema.columns` from a freshly-initialised
* engine (PGLite or Postgres), then diff. The original v0.26.3 plan compared
* raw `src/schema.sql` against raw `src/core/pglite-schema.ts`; codex review
* showed those files are intentionally divergent today (PGLite reaches its
* end-state via PGLITE_SCHEMA_SQL + migrations, not the raw blob alone).
* Comparing post-`initSchema()` end-states is what production actually runs,
* so it's what we test.
*
* The pure functions in this file have no engine dependency. The E2E test at
* `test/e2e/schema-drift.test.ts` wires them up to real engines; the unit
* tests in `test/helpers/schema-diff.test.ts` exercise them with synthetic
* snapshots (including the D3 negative case for the v0.26.1 token_ttl bug).
*/
import { createHash } from 'node:crypto';
export interface ColumnInfo {
dataType: string;
udtName: string;
isNullable: boolean;
columnDefault: string | null;
}
export type SchemaSnapshot = Map<string, Map<string, ColumnInfo>>;
export interface SchemaDiff {
tablesMissingInPGLite: string[];
tablesUnexpectedlyInPGLite: string[];
columnsMissingInPGLite: Array<{ table: string; columns: string[] }>;
columnsMissingInPostgres: Array<{ table: string; columns: string[] }>;
typeMismatches: Array<{
table: string;
column: string;
pg: ColumnInfo;
pglite: ColumnInfo;
reason: 'udt_name' | 'is_nullable' | 'column_default';
}>;
}
export interface SnapshotQueryRow {
table_name: string;
column_name: string;
data_type: string;
udt_name: string;
is_nullable: string;
column_default: string | null;
}
export type SnapshotQueryFn = (sql: string) => Promise<SnapshotQueryRow[]>;
const SNAPSHOT_SQL = `
SELECT
table_name,
column_name,
data_type,
udt_name,
is_nullable,
column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position
`;
// ─── v0.34 D7 — index parity ──────────────────────────────────────────
// snapshotIndexes captures index name + table + column-list + uniqueness +
// partial-predicate so the schema-drift E2E test catches missing or
// shape-mismatched indexes between Postgres and PGLite. Without this,
// hot-path indexes (e.g. v0.34 W4-5's partial composite + composite) can
// silently degrade from index-only-scan to Cartesian on 96K-chunk brains
// while the column-level drift test stays green.
export interface IndexInfo {
indexName: string;
tableName: string;
/** Column list as comma-joined names (case-preserved). */
columns: string;
isUnique: boolean;
isPartial: boolean;
}
export type IndexSnapshot = Map<string, IndexInfo>; // keyed by indexName
export interface IndexSnapshotRow {
index_name: string;
table_name: string;
columns: string;
is_unique: boolean;
is_partial: boolean;
}
export type IndexSnapshotQueryFn = (sql: string) => Promise<IndexSnapshotRow[]>;
// pg_index + pg_class + pg_attribute + pg_namespace — covers both Postgres
// and PGLite (both expose the standard pg_catalog views).
const INDEX_SNAPSHOT_SQL = `
SELECT
i.relname AS index_name,
t.relname AS table_name,
pg_get_indexdef(idx.indexrelid) AS columns,
idx.indisunique AS is_unique,
(pg_get_indexdef(idx.indexrelid) ILIKE '%WHERE%') AS is_partial
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
JOIN pg_class t ON t.oid = idx.indrelid
JOIN pg_namespace ns ON ns.oid = t.relnamespace
WHERE ns.nspname = 'public'
AND NOT idx.indisprimary
ORDER BY t.relname, i.relname
`;
/**
* v0.34 D7 — Pull an IndexSnapshot from any engine that exposes a SQL
* query callback. Caller adapts the native shape to `IndexSnapshotQueryFn`.
*/
export async function snapshotIndexes(query: IndexSnapshotQueryFn): Promise<IndexSnapshot> {
const rows = await query(INDEX_SNAPSHOT_SQL);
const snap: IndexSnapshot = new Map();
for (const row of rows) {
snap.set(row.index_name, {
indexName: row.index_name,
tableName: row.table_name,
columns: row.columns,
isUnique: row.is_unique === true || (row.is_unique as unknown) === 'true' || (row.is_unique as unknown) === 't',
isPartial: row.is_partial === true || (row.is_partial as unknown) === 'true' || (row.is_partial as unknown) === 't',
});
}
return snap;
}
export interface IndexDiff {
/** Indexes present in Postgres but missing from PGLite. */
pgOnly: IndexInfo[];
/** Indexes present in PGLite but missing from Postgres. */
pgliteOnly: IndexInfo[];
/** Indexes present on both sides with mismatched shape. */
mismatched: Array<{ pg: IndexInfo; pglite: IndexInfo; reason: string }>;
}
export function diffIndexSnapshots(
pg: IndexSnapshot,
pglite: IndexSnapshot,
opts: { allowlist?: string[] } = {},
): IndexDiff {
const allow = new Set(opts.allowlist ?? []);
const out: IndexDiff = { pgOnly: [], pgliteOnly: [], mismatched: [] };
for (const [name, info] of pg) {
if (allow.has(name)) continue;
const other = pglite.get(name);
if (!other) {
out.pgOnly.push(info);
continue;
}
// Shape compare. Index definitions render slightly differently across
// engines for the WHERE clause; normalize whitespace before comparing.
const normPg = info.columns.replace(/\s+/g, ' ').trim().toLowerCase();
const normPl = other.columns.replace(/\s+/g, ' ').trim().toLowerCase();
if (normPg !== normPl) {
out.mismatched.push({ pg: info, pglite: other, reason: 'definition_mismatch' });
continue;
}
if (info.isUnique !== other.isUnique) {
out.mismatched.push({ pg: info, pglite: other, reason: 'uniqueness_mismatch' });
}
if (info.isPartial !== other.isPartial) {
out.mismatched.push({ pg: info, pglite: other, reason: 'partial_mismatch' });
}
}
for (const [name, info] of pglite) {
if (allow.has(name)) continue;
if (!pg.has(name)) out.pgliteOnly.push(info);
}
return out;
}
export function isCleanIndexDiff(diff: IndexDiff): boolean {
return diff.pgOnly.length === 0 && diff.pgliteOnly.length === 0 && diff.mismatched.length === 0;
}
export function formatIndexDiffForFailure(diff: IndexDiff): string {
const lines: string[] = [];
if (diff.pgOnly.length > 0) {
lines.push(`Indexes in Postgres but MISSING in PGLite (the PGLite bootstrap is generated from src/schema.sql by bun run build:schema; check the PGLite rules in scripts/build-schema.ts):`);
for (const i of diff.pgOnly) {
lines.push(` - ${i.indexName} on ${i.tableName}: ${i.columns}`);
}
}
if (diff.pgliteOnly.length > 0) {
lines.push(`Indexes in PGLite but MISSING in Postgres (mirror in src/schema.sql or migrate.ts):`);
for (const i of diff.pgliteOnly) {
lines.push(` - ${i.indexName} on ${i.tableName}: ${i.columns}`);
}
}
if (diff.mismatched.length > 0) {
lines.push(`Indexes present on both sides but with shape drift:`);
for (const m of diff.mismatched) {
lines.push(` - ${m.pg.indexName} (${m.reason}):`);
lines.push(` PG: ${m.pg.columns}`);
lines.push(` PGLite: ${m.pglite.columns}`);
}
}
return lines.join('\n');
}
/**
* Pull a SchemaSnapshot from any engine that exposes a SQL query callback.
* Caller adapts the engine's native query shape to `SnapshotQueryFn` (PGLite
* returns `{rows}`, postgres.js returns the array directly).
*/
export async function snapshotSchema(query: SnapshotQueryFn): Promise<SchemaSnapshot> {
const rows = await query(SNAPSHOT_SQL);
const snap: SchemaSnapshot = new Map();
for (const row of rows) {
let cols = snap.get(row.table_name);
if (!cols) {
cols = new Map();
snap.set(row.table_name, cols);
}
cols.set(row.column_name, {
dataType: row.data_type,
udtName: row.udt_name,
isNullable: row.is_nullable === 'YES',
columnDefault: row.column_default ?? null,
});
}
return snap;
}
/**
* Defaults to be normalised before comparison. Postgres and PGLite render
* `gen_random_uuid()` consistently as of pgvector pgvector:pg16 + PGLite ≥0.2,
* but they sometimes differ on NULL representation and on type-cast
* formatting (`'value'::text` vs `'value'`). We collapse the obvious ones.
*/
function normaliseDefault(d: string | null): string | null {
if (d === null) return null;
// Order matters: collapse whitespace FIRST so the trailing-type-cast strip
// matches at end-of-string regardless of trailing spaces.
let normalised = d.trim().replace(/\s+/g, ' ');
// Strip trailing type casts like ::text, ::jsonb, ::uuid — PGLite sometimes
// omits them and Postgres sometimes includes them for string defaults.
normalised = normalised.replace(/::[a-z_][a-z0-9_]*(\[\])?$/i, '');
return normalised;
}
/**
* Compare two snapshots and produce a structured diff. Tables on the
* allowlist are excluded entirely from the comparison (intentional
* Postgres-only tables).
*/
export function diffSnapshots(
pg: SchemaSnapshot,
pglite: SchemaSnapshot,
opts: { allowlistPgOnlyTables: string[]; allowlistPgliteOnlyTables?: string[] },
): SchemaDiff {
const allowlist = new Set(opts.allowlistPgOnlyTables);
const pgliteOnly = new Set(opts.allowlistPgliteOnlyTables ?? []);
const diff: SchemaDiff = {
tablesMissingInPGLite: [],
tablesUnexpectedlyInPGLite: [],
columnsMissingInPGLite: [],
columnsMissingInPostgres: [],
typeMismatches: [],
};
for (const [table, pgCols] of pg) {
if (allowlist.has(table)) continue;
const pgliteCols = pglite.get(table);
if (!pgliteCols) {
diff.tablesMissingInPGLite.push(table);
continue;
}
const missingInPGLite: string[] = [];
for (const [col, pgInfo] of pgCols) {
const pgliteInfo = pgliteCols.get(col);
if (!pgliteInfo) {
missingInPGLite.push(col);
continue;
}
// udt_name is the canonical type identity (catches `_text` vs `_int4`,
// vector dimensions, etc.). data_type is the human-readable category.
if (pgInfo.udtName !== pgliteInfo.udtName) {
diff.typeMismatches.push({ table, column: col, pg: pgInfo, pglite: pgliteInfo, reason: 'udt_name' });
continue;
}
if (pgInfo.isNullable !== pgliteInfo.isNullable) {
diff.typeMismatches.push({ table, column: col, pg: pgInfo, pglite: pgliteInfo, reason: 'is_nullable' });
continue;
}
if (normaliseDefault(pgInfo.columnDefault) !== normaliseDefault(pgliteInfo.columnDefault)) {
diff.typeMismatches.push({ table, column: col, pg: pgInfo, pglite: pgliteInfo, reason: 'column_default' });
}
}
if (missingInPGLite.length > 0) {
diff.columnsMissingInPGLite.push({ table, columns: missingInPGLite });
}
}
// PGLite-only tables are suspicious but not auto-fail. Surface them so a
// reviewer can decide.
for (const [table, pgliteCols] of pglite) {
if (!pg.has(table)) {
if (!pgliteOnly.has(table)) diff.tablesUnexpectedlyInPGLite.push(table);
continue;
}
if (allowlist.has(table)) continue;
const pgCols = pg.get(table)!;
const missingInPostgres: string[] = [];
for (const col of pgliteCols.keys()) {
if (!pgCols.has(col)) missingInPostgres.push(col);
}
if (missingInPostgres.length > 0) {
diff.columnsMissingInPostgres.push({ table, columns: missingInPostgres });
}
}
return diff;
}
/**
* Build the failure message used in test assertions. Names every issue with
* a copy-paste-ready hint so a future contributor can paste the fix straight
* into src/schema.sql / a TS schema fragment (then `bun run build:schema`), a
* PGLite capability rule in scripts/build-schema.ts, or a migration sqlFor.pglite branch.
*/
export function formatDiffForFailure(diff: SchemaDiff): string {
const lines: string[] = [];
if (diff.tablesMissingInPGLite.length > 0) {
lines.push('Tables present on Postgres but missing from PGLite end-state:');
for (const t of diff.tablesMissingInPGLite) {
lines.push(` - ${t}`);
lines.push(` Hint: add CREATE TABLE for "${t}" to src/schema.sql (or its TS schema fragment) and run bun run build:schema; if it is dropped for PGLite by a rule in scripts/build-schema.ts, fix the rule, or add it to the allowlist if intentionally Postgres-only.`);
}
}
if (diff.columnsMissingInPGLite.length > 0) {
lines.push('Columns missing from PGLite end-state:');
for (const { table, columns } of diff.columnsMissingInPGLite) {
for (const col of columns) {
lines.push(` - ${table}.${col}`);
lines.push(` Hint: add "${col}" to the ${table} CREATE TABLE in src/schema.sql (or its TS schema fragment) and run bun run build:schema (check the PGLite rules in scripts/build-schema.ts), or add a sqlFor.pglite branch in the relevant migration.`);
}
}
}
if (diff.columnsMissingInPostgres.length > 0) {
lines.push('Columns present on PGLite but missing from Postgres:');
for (const { table, columns } of diff.columnsMissingInPostgres) {
for (const col of columns) {
lines.push(` - ${table}.${col}`);
lines.push(` Hint: either add "${col}" to ${table} in src/schema.sql + the migrations chain, or drop it for PGLite with a rule in scripts/build-schema.ts (then bun run build:schema).`);
}
}
}
if (diff.typeMismatches.length > 0) {
lines.push('Type / nullability / default mismatches:');
for (const m of diff.typeMismatches) {
lines.push(` - ${m.table}.${m.column} (${m.reason})`);
if (m.reason === 'udt_name') {
lines.push(` pg=${m.pg.dataType}/${m.pg.udtName} pglite=${m.pglite.dataType}/${m.pglite.udtName}`);
} else if (m.reason === 'is_nullable') {
lines.push(` pg.isNullable=${m.pg.isNullable} pglite.isNullable=${m.pglite.isNullable}`);
} else {
lines.push(` pg.default=${JSON.stringify(m.pg.columnDefault)} pglite.default=${JSON.stringify(m.pglite.columnDefault)}`);
}
}
}
if (diff.tablesUnexpectedlyInPGLite.length > 0) {
lines.push('Tables in PGLite that have no Postgres counterpart (suspicious — verify intentional):');
for (const t of diff.tablesUnexpectedlyInPGLite) {
lines.push(` - ${t}`);
}
}
if (lines.length === 0) return 'no diff';
return lines.join('\n');
}
/**
* Returns true when the diff has zero issues.
*/
export function isCleanDiff(diff: SchemaDiff): boolean {
return diff.tablesMissingInPGLite.length === 0
&& diff.columnsMissingInPGLite.length === 0
&& diff.columnsMissingInPostgres.length === 0
&& diff.typeMismatches.length === 0
&& diff.tablesUnexpectedlyInPGLite.length === 0;
}
// ─── Refactor wave 1 E4 — catalog-level snapshot ──────────────────────
// snapshotSchema/snapshotIndexes above answer "do both engines have the same
// columns and index names?". The catalog snapshot below answers "is the
// schema end state byte-identical to the one master produced?": it pins
// column ordinals, defaults, full index definitions, constraints (incl. CHECK
// text), triggers, function signatures + body hashes, views, RLS policies,
// grants, RLS flags, sequences and extensions. It is engine-agnostic: the
// caller adapts PGLite (`{rows}`) or postgres.js (array) to CatalogQueryFn,
// and the result is plain sorted data suitable for a golden file.
//
// Extension-owned objects (pgvector / pg_trgm / pgcrypto functions installed
// into `public`) are excluded via pg_depend deptype 'e': they belong to the
// extension version, not to gbrain's schema.
//
// Role names are environment-dependent (the connecting superuser is
// `postgres` locally but may differ elsewhere), so the connecting role is
// replaced with CATALOG_CURRENT_ROLE in grants and policies.
export const CATALOG_CURRENT_ROLE = '<current_role>';
export interface CatalogColumn {
name: string;
ordinal: number;
dataType: string;
udtName: string;
isNullable: boolean;
columnDefault: string | null;
}
export interface CatalogTable {
name: string;
/** pg_class.relkind: r = table, p = partitioned table. */
relkind: string;
rowSecurity: boolean;
forceRowSecurity: boolean;
/** Ordered by ordinal position. */
columns: CatalogColumn[];
}
export interface CatalogIndex { name: string; table: string; definition: string }
export interface CatalogConstraint { name: string; table: string; type: string; definition: string }
export interface CatalogTrigger { name: string; table: string; definition: string }
export interface CatalogFunction {
name: string;
identityArguments: string;
result: string;
kind: string;
language: string;
volatility: string;
securityDefiner: boolean;
config: string[];
bodySha256: string;
}
export interface CatalogView { name: string; kind: string; definitionSha256: string }
export interface CatalogPolicy {
table: string;
name: string;
permissive: string;
roles: string[];
command: string;
using: string | null;
withCheck: string | null;
}
/** Privileges of one (table, grantor, grantee, grantable) tuple, sorted. */
export interface CatalogGrant { table: string; grantor: string; grantee: string; grantable: boolean; privileges: string[] }
export interface CatalogSequence {
name: string;
dataType: string;
start: string;
min: string;
max: string;
increment: string;
cycle: boolean;
cache: string;
ownedBy: string | null;
}
export interface CatalogSnapshot {
tables: CatalogTable[];
/** Columns of views (information_schema.columns covers both). */
viewColumns: Array<{ view: string; columns: CatalogColumn[] }>;
indexes: CatalogIndex[];
constraints: CatalogConstraint[];
triggers: CatalogTrigger[];
functions: CatalogFunction[];
views: CatalogView[];
policies: CatalogPolicy[];
grants: CatalogGrant[];
sequences: CatalogSequence[];
extensions: string[];
}
export type CatalogQueryFn = (sql: string) => Promise<Array<Record<string, unknown>>>;
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')`;
/** One SQL statement per catalog section. Exported so fakes can route on it. */
export const CATALOG_QUERIES = {
currentRole: `SELECT current_user::text AS role`,
relations: `
SELECT c.relname::text AS name, c.relkind::text AS relkind,
c.relrowsecurity AS row_security, c.relforcerowsecurity AS force_row_security
FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
WHERE n.nspname = 'public' AND c.relkind IN ('r', 'p', 'v', 'm', 'f')
AND ${NOT_EXTENSION_MEMBER('pg_class', 'c.oid')}
ORDER BY c.relname`,
columns: `
SELECT table_name::text AS table_name, column_name::text AS column_name,
ordinal_position::int AS ordinal_position, data_type::text AS data_type,
udt_name::text AS udt_name, is_nullable::text AS is_nullable,
column_default::text AS column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position`,
indexes: `
SELECT i.relname::text AS name, t.relname::text AS table_name,
pg_get_indexdef(idx.indexrelid) AS definition
FROM pg_index idx
JOIN pg_class i ON i.oid = idx.indexrelid
JOIN pg_class t ON t.oid = idx.indrelid
JOIN pg_namespace n ON n.oid = t.relnamespace
WHERE n.nspname = 'public' AND ${NOT_EXTENSION_MEMBER('pg_class', 't.oid')}
ORDER BY t.relname, i.relname`,
constraints: `
SELECT con.conname::text AS name, COALESCE(t.relname::text, '') AS table_name,
con.contype::text AS type, pg_get_constraintdef(con.oid) AS definition
FROM pg_constraint con
JOIN pg_namespace n ON n.oid = con.connamespace
LEFT JOIN pg_class t ON t.oid = con.conrelid
WHERE n.nspname = 'public' AND ${NOT_EXTENSION_MEMBER('pg_constraint', 'con.oid')}
ORDER BY 2, 1`,
triggers: `
SELECT tg.tgname::text AS name, t.relname::text AS table_name,
pg_get_triggerdef(tg.oid) AS definition
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 t.relname, tg.tgname`,
functions: `
SELECT p.proname::text AS name,
pg_get_function_identity_arguments(p.oid) AS identity_arguments,
COALESCE(pg_get_function_result(p.oid), '') AS result,
p.prokind::text AS kind, l.lanname::text AS language,
p.provolatile::text AS volatility, p.prosecdef AS security_definer,
COALESCE(array_to_string(p.proconfig, E'\\n'), '') AS config,
COALESCE(p.prosrc, '') AS body
FROM pg_proc p
JOIN pg_namespace n ON n.oid = p.pronamespace
JOIN pg_language l ON l.oid = p.prolang
WHERE n.nspname = 'public' AND ${NOT_EXTENSION_MEMBER('pg_proc', 'p.oid')}
ORDER BY 1, 2`,
views: `
SELECT c.relname::text AS name, c.relkind::text AS kind,
pg_get_viewdef(c.oid) AS definition
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 1`,
policies: `
SELECT tablename::text AS table_name, policyname::text AS name,
permissive::text AS permissive, array_to_string(roles, E'\\n') AS roles,
cmd::text AS command, qual::text AS using_expr, with_check::text AS with_check
FROM pg_policies
WHERE schemaname = 'public'
ORDER BY 1, 2`,
grants: `
SELECT table_name::text AS table_name, grantor::text AS grantor, grantee::text AS grantee,
privilege_type::text AS privilege, is_grantable::text AS is_grantable
FROM information_schema.table_privileges
WHERE table_schema = 'public'
ORDER BY 1, 3, 4, 2`,
sequences: `
SELECT s.sequencename::text AS name, s.data_type::text AS data_type,
s.start_value::text AS start_value, s.min_value::text AS min_value,
s.max_value::text AS max_value, s.increment_by::text AS increment_by,
s.cycle AS cycle, s.cache_size::text AS cache_size,
(SELECT t.relname::text || '.' || a.attname::text
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.refclassid = 'pg_class'::regclass AND d.deptype IN ('a', 'i')
LIMIT 1) AS owned_by
FROM pg_sequences s
JOIN pg_namespace n ON n.nspname = s.schemaname
JOIN pg_class c ON c.relnamespace = n.oid AND c.relname = s.sequencename
WHERE s.schemaname = 'public'
ORDER BY 1`,
extensions: `SELECT extname::text AS name FROM pg_extension ORDER BY 1`,
} as const;
export type CatalogSection = keyof typeof CATALOG_QUERIES;
export type RawCatalogRows = Record<CatalogSection, Array<Record<string, unknown>>>;
function catalogBool(v: unknown): boolean {
return v === true || v === 't' || v === 'true' || v === 'YES';
}
function catalogText(v: unknown): string {
return v === null || v === undefined ? '' : String(v);
}
function catalogNullableText(v: unknown): string | null {
return v === null || v === undefined ? null : String(v);
}
function catalogLines(v: unknown): string[] {
const s = catalogText(v);
return s === '' ? [] : s.split('\n');
}
function catalogSha256(text: string): string {
return createHash('sha256').update(text).digest('hex');
}
function byKeys<T>(...keys: Array<(x: T) => string>) {
return (a: T, b: T) => {
for (const k of keys) {
const x = k(a);
const y = k(b);
if (x !== y) return x < y ? -1 : 1;
}
return 0;
};
}
function groupCatalogGrants(rows: Array<Record<string, unknown>>, role: (name: string) => string): CatalogGrant[] {
const groups = new Map<string, CatalogGrant>();
for (const r of rows) {
const g = {
table: catalogText(r.table_name),
grantor: role(catalogText(r.grantor)),
grantee: role(catalogText(r.grantee)),
grantable: catalogBool(r.is_grantable),
};
const key = JSON.stringify(g);
const entry = groups.get(key) ?? { ...g, privileges: [] };
entry.privileges.push(catalogText(r.privilege));
groups.set(key, entry);
}
return [...groups.values()]
.map((g) => ({ ...g, privileges: [...g.privileges].sort() }))
.sort(byKeys((g) => g.table, (g) => g.grantee, (g) => g.grantor, (g) => String(g.grantable)));
}
/**
* Pure: turn raw catalog rows (one array per CATALOG_QUERIES section) into a
* sorted, role-normalized CatalogSnapshot. Sorting happens here with a
* byte-order comparator, so the result does not depend on the server's
* collation or on the order rows came back in.
*/
export function buildCatalogSnapshot(raw: RawCatalogRows): CatalogSnapshot {
const currentRole = catalogText(raw.currentRole[0]?.role);
const role = (name: string) => (currentRole !== '' && name === currentRole ? CATALOG_CURRENT_ROLE : name);
const relkinds = new Map<string, Record<string, unknown>>();
for (const r of raw.relations) relkinds.set(catalogText(r.name), r);
const columnsByRelation = new Map<string, CatalogColumn[]>();
for (const r of raw.columns) {
const table = catalogText(r.table_name);
const list = columnsByRelation.get(table) ?? [];
list.push({
name: catalogText(r.column_name),
ordinal: Number(r.ordinal_position),
dataType: catalogText(r.data_type),
udtName: catalogText(r.udt_name),
isNullable: catalogBool(r.is_nullable),
columnDefault: catalogNullableText(r.column_default),
});
columnsByRelation.set(table, list);
}
const orderedColumns = (name: string) =>
[...(columnsByRelation.get(name) ?? [])].sort((a, b) => a.ordinal - b.ordinal);
const tables: CatalogTable[] = [];
const viewColumns: CatalogSnapshot['viewColumns'] = [];
for (const [name, r] of relkinds) {
const relkind = catalogText(r.relkind);
if (relkind === 'v' || relkind === 'm') {
viewColumns.push({ view: name, columns: orderedColumns(name) });
continue;
}
tables.push({
name,
relkind,
rowSecurity: catalogBool(r.row_security),
forceRowSecurity: catalogBool(r.force_row_security),
columns: orderedColumns(name),
});
}
tables.sort(byKeys((t) => t.name));
viewColumns.sort(byKeys((v) => v.view));
return {
tables,
viewColumns,
indexes: raw.indexes
.map((r) => ({ name: catalogText(r.name), table: catalogText(r.table_name), definition: catalogText(r.definition) }))
.sort(byKeys((i) => i.table, (i) => i.name)),
constraints: raw.constraints
.map((r) => ({
name: catalogText(r.name),
table: catalogText(r.table_name),
type: catalogText(r.type),
definition: catalogText(r.definition),
}))
.sort(byKeys((c) => c.table, (c) => c.name, (c) => c.definition)),
triggers: raw.triggers
.map((r) => ({ name: catalogText(r.name), table: catalogText(r.table_name), definition: catalogText(r.definition) }))
.sort(byKeys((t) => t.table, (t) => t.name)),
functions: raw.functions
.map((r) => ({
name: catalogText(r.name),
identityArguments: catalogText(r.identity_arguments),
result: catalogText(r.result),
kind: catalogText(r.kind),
language: catalogText(r.language),
volatility: catalogText(r.volatility),
securityDefiner: catalogBool(r.security_definer),
config: catalogLines(r.config),
bodySha256: catalogSha256(catalogText(r.body)),
}))
.sort(byKeys((f) => f.name, (f) => f.identityArguments)),
views: raw.views
.map((r) => ({ name: catalogText(r.name), kind: catalogText(r.kind), definitionSha256: catalogSha256(catalogText(r.definition)) }))
.sort(byKeys((v) => v.name)),
policies: raw.policies
.map((r) => ({
table: catalogText(r.table_name),
name: catalogText(r.name),
permissive: catalogText(r.permissive),
roles: catalogLines(r.roles).map(role).sort(),
command: catalogText(r.command),
using: catalogNullableText(r.using_expr),
withCheck: catalogNullableText(r.with_check),
}))
.sort(byKeys((p) => p.table, (p) => p.name)),
grants: groupCatalogGrants(raw.grants, role),
sequences: raw.sequences
.map((r) => ({
name: catalogText(r.name),
dataType: catalogText(r.data_type),
start: catalogText(r.start_value),
min: catalogText(r.min_value),
max: catalogText(r.max_value),
increment: catalogText(r.increment_by),
cycle: catalogBool(r.cycle),
cache: catalogText(r.cache_size),
ownedBy: catalogNullableText(r.owned_by),
}))
.sort(byKeys((s) => s.name)),
extensions: raw.extensions.map((r) => catalogText(r.name)).sort(),
};
}
/** Run every CATALOG_QUERIES section through `query` and build the snapshot. */
export async function snapshotCatalog(query: CatalogQueryFn): Promise<CatalogSnapshot> {
const raw = {} as RawCatalogRows;
for (const section of Object.keys(CATALOG_QUERIES) as CatalogSection[]) {
raw[section] = await query(CATALOG_QUERIES[section]);
}
return buildCatalogSnapshot(raw);
}
export interface CatalogDiffEntry {
section: keyof CatalogSnapshot;
key: string;
kind: 'missing_in_actual' | 'unexpected_in_actual' | 'changed';
expected?: unknown;
actual?: unknown;
}
function catalogEntries(snap: CatalogSnapshot, section: keyof CatalogSnapshot, compareOrdinals: boolean): Map<string, unknown> {
const out = new Map<string, unknown>();
const put = (key: string, value: unknown) => out.set(key, value);
switch (section) {
case 'tables':
for (const t of snap.tables) {
put(`${t.name}`, { relkind: t.relkind, rowSecurity: t.rowSecurity, forceRowSecurity: t.forceRowSecurity });
for (const c of t.columns) {
const { ordinal, ...rest } = c;
put(`${t.name}.${c.name}`, compareOrdinals ? c : rest);
}
}
return out;
case 'viewColumns':
for (const v of snap.viewColumns) {
for (const c of v.columns) {
const { ordinal, ...rest } = c;
put(`${v.view}.${c.name}`, compareOrdinals ? c : rest);
}
}
return out;
case 'indexes': for (const x of snap.indexes) put(x.name, x); return out;
case 'constraints': for (const x of snap.constraints) put(`${x.table}.${x.name}`, x); return out;
case 'triggers': for (const x of snap.triggers) put(`${x.table}.${x.name}`, x); return out;
case 'functions': for (const x of snap.functions) put(`${x.name}(${x.identityArguments})`, x); return out;
case 'views': for (const x of snap.views) put(x.name, x); return out;
case 'policies': for (const x of snap.policies) put(`${x.table}.${x.name}`, x); return out;
case 'grants': for (const x of snap.grants) put(`${x.table}:${x.grantee}:${x.grantor}:${x.grantable}`, x); return out;
case 'sequences': for (const x of snap.sequences) put(x.name, x); return out;
case 'extensions': for (const x of snap.extensions) put(x, x); return out;
}
}
/**
* Structured diff of two catalog snapshots. Columns are keyed by name; with
* `compareOrdinals: true` (PGLite master-vs-branch, T-G13) a column whose
* ordinal position moved is reported as changed. Cross-engine comparisons
* leave it false, matching the name-based diffSnapshots contract.
*/
export function diffCatalogSnapshots(
expected: CatalogSnapshot,
actual: CatalogSnapshot,
opts: { compareOrdinals?: boolean } = {},
): CatalogDiffEntry[] {
const compareOrdinals = opts.compareOrdinals ?? false;
const out: CatalogDiffEntry[] = [];
const sections = Object.keys(expected) as Array<keyof CatalogSnapshot>;
for (const section of sections) {
const e = catalogEntries(expected, section, compareOrdinals);
const a = catalogEntries(actual, section, compareOrdinals);
for (const [key, value] of e) {
if (!a.has(key)) out.push({ section, key, kind: 'missing_in_actual', expected: value });
else if (JSON.stringify(value) !== JSON.stringify(a.get(key))) {
out.push({ section, key, kind: 'changed', expected: value, actual: a.get(key) });
}
}
for (const [key, value] of a) {
if (!e.has(key)) out.push({ section, key, kind: 'unexpected_in_actual', actual: value });
}
}
return out;
}
export function formatCatalogDiffForFailure(diff: CatalogDiffEntry[]): string {
if (diff.length === 0) return 'no diff';
return diff
.map((d) => {
if (d.kind === 'changed') {
return ` - [${d.section}] ${d.key} changed\n expected: ${JSON.stringify(d.expected)}\n actual: ${JSON.stringify(d.actual)}`;
}
return ` - [${d.section}] ${d.key} ${d.kind === 'missing_in_actual' ? 'MISSING' : 'UNEXPECTED'}`;
})
.join('\n');
}