Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
#!/usr/bin/env bun
/**
* Schema text generator (refactor wave 1, W2): one copy of schema DDL.
*
* Canonical sources (docs/ENGINES.md "Canonical schema sources"):
* - src/schema.sql: canonical for every table except the TS fragment tables;
* - the TS fragment modules in FRAGMENTS below: canonical for their tables
* (evaluated here under Bun, so the text is exactly what migrations use).
* Everything else is generated, in this fixed order (`bun run build:schema`):
* 1. evaluate the fragment modules;
* 2. rewrite the `BEGIN/END GENERATED from <path>` regions of src/schema.sql;
* 3. write src/core/schema-embedded.generated.ts (the Postgres blob);
* 4. write src/core/pglite-schema.generated.ts, the PGLite bootstrap TEMPLATE:
* schema.sql statements + fragment text, transformed by the explicit
* PGLite capability rules below, with the __EMBEDDING_DIMS__ /
* __EMBEDDING_MODEL__ placeholders and the runtime policy hooks
* (applyChunkEmbeddingIndexPolicy, applyFtsLanguagePolicy) left to
* getPGLiteSchema(dims, model) at runtime.
* Unknown statement kinds, unclassified DO blocks and rules that no longer
* match exit non-zero.
*
* Usage:
* bun run build:schema # write all outputs in place
* bun scripts/build-schema.ts --out-dir <dir> # write outputs under <dir>/<repo path> (freshness guard)
*/
import { createHash } from 'node:crypto';
import { mkdirSync, readFileSync, writeFileSync } from 'node:fs';
import { dirname, join, resolve } from 'node:path';
import { SOURCE_INGESTION_RECEIPTS_SCHEMA_SQL } from '../src/core/company-brain/receipt-schema.ts';
import { FTS_INPUT_FUNCTION_SQL } from '../src/core/fts-language.ts';
import { PAGE_RETRIEVALS_FORGET_FUNCTION_SQL } from '../src/core/derived-state-maintainers.ts';
import { FACT_WITHDRAWAL_SCHEMA_STATEMENTS } from '../src/core/facts/withdrawal-schema.ts';
import { GRANT_AUDIT_SCHEMA_SQL } from '../src/core/grants/schema.ts';
import { LEASE_TOKEN_SCHEMA_SQL } from '../src/core/lease-schema.ts';
import { LINK_TEMPORAL_SCHEMA_SQL } from '../src/core/link-temporal-schema.ts';
import { PAGE_BODY_RLS_SQL, PAGE_BODY_SCHEMA_SQL, PAGE_GENERATION_BUMP_FN_SQL, PAGE_SEGMENT_DERIVATIONS_SQL } from '../src/core/page-body/schema.ts';
import { PAGE_PROJECTION_SCHEMA_SQL } from '../src/core/page-state/projection-schema.ts';
import { PAGE_STATE_SCHEMA_SQL } from '../src/core/page-state/schema.ts';
import { POSTGRES_CONCURRENT_PERSISTENCE_INDEXES, PERSISTENCE_SCHEMA_STATEMENTS } from '../src/core/persistence/schema.ts';
import { PERSISTENCE_TOPOLOGY_SCHEMA_SQL } from '../src/core/persistence/topology-schema.ts';
import { WORKTREE_REFRESH_SCHEMA_SQL } from '../src/core/persistence/worktree-refresh-schema.ts';
import { SHARED_SKILLS_SCHEMA_SQL } from '../src/core/shared-skills/schema-all.ts';
import { DECIDE_SCHEMA_SQL } from '../src/core/ai/decide/schema.ts';
import { FACT_RELINK_SCHEMA_SQL } from '../src/core/facts/relink-schema.ts';
import { PINNED_QUESTIONS_SCHEMA_SQL } from '../src/core/questions/schema.ts';
import { CORE_EDIT_NOTICES_SCHEMA_SQL } from '../src/core/core-memory-schema.ts';
import { FACT_PURGE_LOOKUP_INDEX_SQL, MEMORY_PURGE_SCHEMA_SQL } from '../src/core/facts/purge-schema.ts';
import { WRITE_GATE_SCHEMA_SQL } from '../src/core/write-gate-schema.ts';
import { BELIEF_LEDGER_SCHEMA_SQL } from '../src/core/belief/schema.ts';
import { GIT_CANONICAL_SCHEMA_SQL } from '../src/core/persistence/git-canonical-schema.ts';
import { FUNCTION_SEARCH_PATH_HARDENING_SQL } from '../src/core/function-search-path.ts';
const REPO = resolve(import.meta.dir, '..');
export const SCHEMA_SQL_PATH = 'src/schema.sql';
export const EMBEDDED_PATH = 'src/core/schema-embedded.generated.ts';
export const PGLITE_PATH = 'src/core/pglite-schema.generated.ts';
const SEE = 'See: docs/ENGINES.md#canonical-schema-sources';
export class SchemaBuildError extends Error {}
function fail(what: string, why: string, fix: string): never {
throw new SchemaBuildError(`FAIL: ${what}\nWhy: ${why}\nFix: ${fix}\n${SEE}`);
}
// ---------------------------------------------------------------------------
// 1. Fragments: TS modules canonical for their tables.
// ---------------------------------------------------------------------------
export interface Fragment {
/** Banner label: `<source path> (<expression>)`. */
source: string;
expr: string;
/** Text of the src/schema.sql region (the Postgres blob). */
postgres: string;
/** Text spliced into the PGLite template. */
pglite: string;
}
const persistenceSql = (statements: readonly string[]) => `${statements.join(';\n')};`;
export const FRAGMENTS: readonly Fragment[] = [
{ source: 'src/core/page-body/schema.ts', expr: 'PAGE_GENERATION_BUMP_FN_SQL', postgres: PAGE_GENERATION_BUMP_FN_SQL, pglite: PAGE_GENERATION_BUMP_FN_SQL },
{ source: 'src/core/fts-language.ts', expr: 'FTS_INPUT_FUNCTION_SQL', postgres: FTS_INPUT_FUNCTION_SQL, pglite: FTS_INPUT_FUNCTION_SQL },
{ source: 'src/core/derived-state-maintainers.ts', expr: 'PAGE_RETRIEVALS_FORGET_FUNCTION_SQL', postgres: PAGE_RETRIEVALS_FORGET_FUNCTION_SQL, pglite: PAGE_RETRIEVALS_FORGET_FUNCTION_SQL },
{ source: 'src/core/grants/schema.ts', expr: 'GRANT_AUDIT_SCHEMA_SQL', postgres: GRANT_AUDIT_SCHEMA_SQL, pglite: GRANT_AUDIT_SCHEMA_SQL },
{
source: 'src/core/facts/withdrawal-schema.ts',
expr: 'FACT_WITHDRAWAL_SCHEMA_STATEMENTS[0]',
postgres: `${FACT_WITHDRAWAL_SCHEMA_STATEMENTS[0]};`,
pglite: `${FACT_WITHDRAWAL_SCHEMA_STATEMENTS[0]};`,
},
{ source: 'src/core/lease-schema.ts', expr: 'LEASE_TOKEN_SCHEMA_SQL', postgres: LEASE_TOKEN_SCHEMA_SQL, pglite: LEASE_TOKEN_SCHEMA_SQL },
{ source: 'src/core/page-body/schema.ts', expr: 'PAGE_BODY_SCHEMA_SQL', postgres: PAGE_BODY_SCHEMA_SQL, pglite: PAGE_BODY_SCHEMA_SQL },
{ source: 'src/core/page-body/schema.ts', expr: 'PAGE_SEGMENT_DERIVATIONS_SQL', postgres: PAGE_SEGMENT_DERIVATIONS_SQL, pglite: PAGE_SEGMENT_DERIVATIONS_SQL },
// Capability rule: PGLite has no role system and enables RLS on no core table, so the segment tables' RLS block is Postgres-only.
{ source: 'src/core/page-body/schema.ts', expr: 'PAGE_BODY_RLS_SQL', postgres: PAGE_BODY_RLS_SQL, pglite: '' },
{ source: 'src/core/page-state/schema.ts', expr: 'PAGE_STATE_SCHEMA_SQL', postgres: PAGE_STATE_SCHEMA_SQL, pglite: PAGE_STATE_SCHEMA_SQL },
{
source: 'src/core/persistence/schema.ts',
expr: 'PERSISTENCE_SCHEMA_STATEMENTS',
// Capability rule: Postgres builds persistence_requests_database_pending
// (v165) and the #5762 sync-run indexes CONCURRENTLY in migrations (never
// inside the blob); PGLite has no concurrent builds, so its bootstrap
// creates them inline.
postgres: persistenceSql(PERSISTENCE_SCHEMA_STATEMENTS.filter((s) => !POSTGRES_CONCURRENT_PERSISTENCE_INDEXES.has(s))),
pglite: persistenceSql(PERSISTENCE_SCHEMA_STATEMENTS),
},
{ source: 'src/core/persistence/git-canonical-schema.ts', expr: 'GIT_CANONICAL_SCHEMA_SQL', postgres: GIT_CANONICAL_SCHEMA_SQL, pglite: GIT_CANONICAL_SCHEMA_SQL },
{ source: 'src/core/page-state/projection-schema.ts', expr: 'PAGE_PROJECTION_SCHEMA_SQL', postgres: PAGE_PROJECTION_SCHEMA_SQL, pglite: PAGE_PROJECTION_SCHEMA_SQL },
{ source: 'src/core/persistence/topology-schema.ts', expr: 'PERSISTENCE_TOPOLOGY_SCHEMA_SQL', postgres: PERSISTENCE_TOPOLOGY_SCHEMA_SQL, pglite: PERSISTENCE_TOPOLOGY_SCHEMA_SQL },
{ source: 'src/core/persistence/worktree-refresh-schema.ts', expr: 'WORKTREE_REFRESH_SCHEMA_SQL', postgres: WORKTREE_REFRESH_SCHEMA_SQL, pglite: WORKTREE_REFRESH_SCHEMA_SQL },
{ source: 'src/core/company-brain/receipt-schema.ts', expr: 'SOURCE_INGESTION_RECEIPTS_SCHEMA_SQL', postgres: SOURCE_INGESTION_RECEIPTS_SCHEMA_SQL, pglite: SOURCE_INGESTION_RECEIPTS_SCHEMA_SQL },
{ source: 'src/core/shared-skills/schema-all.ts', expr: 'SHARED_SKILLS_SCHEMA_SQL', postgres: SHARED_SKILLS_SCHEMA_SQL, pglite: SHARED_SKILLS_SCHEMA_SQL },
{ source: 'src/core/ai/decide/schema.ts', expr: 'DECIDE_SCHEMA_SQL', postgres: DECIDE_SCHEMA_SQL, pglite: DECIDE_SCHEMA_SQL },
{ source: 'src/core/facts/relink-schema.ts', expr: 'FACT_RELINK_SCHEMA_SQL', postgres: FACT_RELINK_SCHEMA_SQL, pglite: FACT_RELINK_SCHEMA_SQL },
{ source: 'src/core/link-temporal-schema.ts', expr: 'LINK_TEMPORAL_SCHEMA_SQL', postgres: LINK_TEMPORAL_SCHEMA_SQL, pglite: LINK_TEMPORAL_SCHEMA_SQL },
{ source: 'src/core/core-memory-schema.ts', expr: 'CORE_EDIT_NOTICES_SCHEMA_SQL', postgres: CORE_EDIT_NOTICES_SCHEMA_SQL, pglite: CORE_EDIT_NOTICES_SCHEMA_SQL },
{ source: 'src/core/facts/purge-schema.ts', expr: 'MEMORY_PURGE_SCHEMA_SQL', postgres: MEMORY_PURGE_SCHEMA_SQL, pglite: MEMORY_PURGE_SCHEMA_SQL },
{ source: 'src/core/facts/purge-schema.ts', expr: 'FACT_PURGE_LOOKUP_INDEX_SQL', postgres: FACT_PURGE_LOOKUP_INDEX_SQL, pglite: FACT_PURGE_LOOKUP_INDEX_SQL },
{ source: 'src/core/write-gate-schema.ts', expr: 'WRITE_GATE_SCHEMA_SQL', postgres: WRITE_GATE_SCHEMA_SQL, pglite: WRITE_GATE_SCHEMA_SQL },
{ source: 'src/core/questions/schema.ts', expr: 'PINNED_QUESTIONS_SCHEMA_SQL', postgres: PINNED_QUESTIONS_SCHEMA_SQL, pglite: PINNED_QUESTIONS_SCHEMA_SQL },
{ source: 'src/core/belief/schema.ts', expr: 'BELIEF_LEDGER_SCHEMA_SQL', postgres: BELIEF_LEDGER_SCHEMA_SQL, pglite: BELIEF_LEDGER_SCHEMA_SQL },
// Its region must stay the last statement of src/schema.sql (regions render where their markers sit, not in this
// array's order): a replay's CREATE OR REPLACE resets each function's search_path, so pg_temp is appended after every one (#7632).
{ source: 'src/core/function-search-path.ts', expr: 'FUNCTION_SEARCH_PATH_HARDENING_SQL', postgres: FUNCTION_SEARCH_PATH_HARDENING_SQL, pglite: FUNCTION_SEARCH_PATH_HARDENING_SQL },
];
const fragmentLabel = (f: Fragment) => `${f.source} (${f.expr})`;
const regionBody = (text: string) => `${text.replace(/^\s*\n/, '').replace(/\s+$/, '')}\n`;
// ---------------------------------------------------------------------------
// SQL statement splitting (comments, quotes, dollar quotes aware).
// ---------------------------------------------------------------------------
export interface Stmt {
/** Full text including leading comments/whitespace since the previous statement. */
text: string;
/** Comment-stripped, whitespace-collapsed text. */
norm: string;
line: number;
}
export function splitSql(src: string, firstLine = 1): Stmt[] {
const out: Stmt[] = [];
let i = 0;
let start = 0;
const n = src.length;
const push = (end: number) => {
const text = src.slice(start, end);
const lead = text.length - text.trimStart().length;
out.push({ text, norm: normalizeSql(text), line: firstLine + src.slice(0, start + lead).split('\n').length - 1 });
start = end;
};
while (i < n) {
const c = src[i]!;
if (c === '-' && src[i + 1] === '-') {
const e = src.indexOf('\n', i);
i = e < 0 ? n : e + 1;
} else if (c === '/' && src[i + 1] === '*') {
i = src.indexOf('*/', i + 2) + 2;
} else if (c === "'" || c === '"') {
i++;
while (i < n && !(src[i] === c && src[i + 1] !== c)) i += src[i] === c ? 2 : 1;
i++;
} else if (c === '$') {
const m = /^\$([A-Za-z_][A-Za-z0-9_]*)?\$/.exec(src.slice(i, i + 64));
if (m) {
const e = src.indexOf(m[0], i + m[0].length);
if (e < 0) throw new SchemaBuildError(`unterminated dollar quote ${m[0]} at offset ${i}`);
i = e + m[0].length;
} else {
i++;
}
} else if (c === ';') {
i++;
push(i);
} else {
i++;
}
}
if (src.slice(start).trim()) push(n);
else if (start < n) out.push({ text: src.slice(start), norm: '', line: firstLine });
return out;
}
export function normalizeSql(s: string): string {
let r = '';
let i = 0;
while (i < s.length) {
const c = s[i]!;
if (c === '-' && s[i + 1] === '-') {
const e = s.indexOf('\n', i);
i = e < 0 ? s.length : e;
} else if (c === "'") {
const st = i++;
while (i < s.length && !(s[i] === "'" && s[i + 1] !== "'")) i += s[i] === "'" ? 2 : 1;
i++;
r += s.slice(st, i);
} else if (c === '$' && /^\$([A-Za-z_][A-Za-z0-9_]*)?\$/.test(s.slice(i, i + 64))) {
const tag = /^\$([A-Za-z_][A-Za-z0-9_]*)?\$/.exec(s.slice(i, i + 64))![0];
const e = s.indexOf(tag, i + tag.length) + tag.length;
r += s.slice(i, e);
i = e;
} else {
r += c;
i++;
}
}
return r.replace(/\s+/g, ' ').trim();
}
/**
* Drop `--` comments outside string and dollar quotes, and the lines they
* leave empty. schema.sql's comments document the Postgres text; the PGLite
* template carries none, so no comment text (with its stray parentheses) can
* reach structural parsers such as test/schema-bootstrap-coverage.test.ts.
*/
export function stripSqlComments(s: string): string {
let r = '';
let i = 0;
while (i < s.length) {
const c = s[i]!;
if (c === '-' && s[i + 1] === '-') {
const e = s.indexOf('\n', i);
const lineStart = r.lastIndexOf('\n') + 1;
if (/^[ \t]*$/.test(r.slice(lineStart))) {
r = r.slice(0, lineStart);
i = e < 0 ? s.length : e + 1;
} else {
r = r.replace(/[ \t]+$/, '');
i = e < 0 ? s.length : e;
}
} else if (c === "'") {
const st = i++;
while (i < s.length && !(s[i] === "'" && s[i + 1] !== "'")) i += s[i] === "'" ? 2 : 1;
i++;
r += s.slice(st, i);
} else if (c === '$' && /^\$([A-Za-z_][A-Za-z0-9_]*)?\$/.test(s.slice(i, i + 64))) {
const tag = /^\$([A-Za-z_][A-Za-z0-9_]*)?\$/.exec(s.slice(i, i + 64))![0];
const e = s.indexOf(tag, i + tag.length) + tag.length;
r += s.slice(i, e);
i = e;
} else {
r += c;
i++;
}
}
return r;
}
// ---------------------------------------------------------------------------
// 2. src/schema.sql generated regions.
// ---------------------------------------------------------------------------
const BEGIN_RE = /^-- BEGIN GENERATED from (\S+) \(([^)]+)\)\..*$/;
const END_RE = /^-- END GENERATED from (\S+) \(([^)]+)\)$/;
export function beginMarker(f: Fragment): string {
return `-- BEGIN GENERATED from ${fragmentLabel(f)}. Edit that file, then run: bun run build:schema`;
}
export function endMarker(f: Fragment): string {
return `-- END GENERATED from ${fragmentLabel(f)}`;
}
export type SchemaPart = { kind: 'sql'; text: string; line: number } | { kind: 'region'; fragment: Fragment; line: number };
/** Split schema.sql into hand-written SQL runs and generated regions. */
export function parseSchemaSql(text: string): SchemaPart[] {
const lines = text.split('\n');
const parts: SchemaPart[] = [];
const seen = new Set<Fragment>();
let buf: string[] = [];
let bufLine = 1;
for (let i = 0; i < lines.length; i++) {
const b = BEGIN_RE.exec(lines[i]!);
if (END_RE.test(lines[i]!)) fail(`${SCHEMA_SQL_PATH}:${i + 1} END GENERATED marker without a BEGIN`, 'regions are delimited by a BEGIN/END pair.', 'restore the matching BEGIN marker (or regenerate from git).');
if (!b) {
if (buf.length === 0) bufLine = i + 1;
buf.push(lines[i]!);
continue;
}
const fragment = FRAGMENTS.find((f) => f.source === b[1] && f.expr === b[2]);
if (!fragment) fail(`${SCHEMA_SQL_PATH}:${i + 1} names an unknown fragment ${b[1]} (${b[2]})`, 'every generated region must map to an entry of FRAGMENTS in scripts/build-schema.ts.', 'fix the marker or add the fragment to FRAGMENTS.');
if (seen.has(fragment)) fail(`${SCHEMA_SQL_PATH}:${i + 1} has a second region for ${fragmentLabel(fragment)}`, 'each fragment is generated into exactly one region.', 'delete the duplicate region.');
seen.add(fragment);
let j = i + 1;
while (j < lines.length && !END_RE.test(lines[j]!)) {
if (BEGIN_RE.test(lines[j]!)) fail(`${SCHEMA_SQL_PATH}:${j + 1} nested BEGIN GENERATED marker`, 'regions cannot nest.', 'close the previous region first.');
j++;
}
const end = END_RE.exec(lines[j] ?? '');
if (!end || end[1] !== b[1] || end[2] !== b[2]) fail(`${SCHEMA_SQL_PATH}:${i + 1} region for ${fragmentLabel(fragment)} has no matching END marker`, 'an unclosed region would swallow hand-written SQL.', `add \`${endMarker(fragment)}\`.`);
parts.push({ kind: 'sql', text: buf.join('\n'), line: bufLine });
buf = [];
parts.push({ kind: 'region', fragment, line: i + 1 });
i = j;
bufLine = j + 2;
}
parts.push({ kind: 'sql', text: buf.join('\n'), line: bufLine });
for (const f of FRAGMENTS) {
if (!seen.has(f)) fail(`${SCHEMA_SQL_PATH} has no generated region for ${fragmentLabel(f)}`, 'the fragment tables must reach the Postgres blob through schema.sql.', `add \`${beginMarker(f)}\` / \`${endMarker(f)}\` where the tables belong, then run: bun run build:schema`);
}
return parts;
}
export function renderSchemaSql(current: string): string {
return parseSchemaSql(current)
.map((p) => (p.kind === 'sql' ? p.text : `${beginMarker(p.fragment)}\n${regionBody(p.fragment.postgres)}${endMarker(p.fragment)}`))
.join('\n');
}
// ---------------------------------------------------------------------------
// 3. schema-embedded.generated.ts
// ---------------------------------------------------------------------------
const escapeTemplate = (s: string) => s.replace(/\\/g, '\\\\').replace(/`/g, '\\`').replace(/\$/g, '\\$');
export function renderEmbedded(schemaSql: string): string {
return `// AUTO-GENERATED — do not edit. Run: bun run build:schema\n// Source: ${SCHEMA_SQL_PATH}\n\nexport const SCHEMA_SQL = \`\n${escapeTemplate(schemaSql)}\`;\n`;
}
// ---------------------------------------------------------------------------
// 4. PGLite template: capability rules.
// ---------------------------------------------------------------------------
/** Statement identity used by the rules, e.g. `table:pages`, `index:idx_x`, `do:<sha12>`. */
export function identify(s: Stmt): string {
const t = s.norm;
let m: RegExpExecArray | null;
if ((m = /^CREATE TABLE IF NOT EXISTS (\w+)/i.exec(t))) return `table:${m[1]}`;
if ((m = /^CREATE (?:UNIQUE )?INDEX (?:CONCURRENTLY )?IF NOT EXISTS (\w+)/i.exec(t))) return `index:${m[1]}`;
if ((m = /^CREATE OR REPLACE FUNCTION (\w+)/i.exec(t))) return `function:${m[1]}`;
if ((m = /^DROP FUNCTION IF EXISTS (\w+)/i.exec(t))) return `drop-function:${m[1]}`;
if ((m = /^DROP TRIGGER IF EXISTS (\w+) ON (\w+)/i.exec(t))) return `drop-trigger:${m[2]}.${m[1]}`;
if ((m = /^CREATE TRIGGER (\w+) .*? ON (\w+) /i.exec(t))) return `trigger:${m[2]}.${m[1]}`;
if ((m = /^ALTER TABLE (\w+) ADD COLUMN IF NOT EXISTS (\w+)/i.exec(t))) return `add-column:${m[1]}.${m[2]}`;
if ((m = /^ALTER TABLE (\w+) DROP COLUMN IF EXISTS (\w+)/i.exec(t))) return `drop-column:${m[1]}.${m[2]}`;
if ((m = /^ALTER TABLE (\w+) ALTER COLUMN (\w+) SET STORAGE/i.exec(t))) return `set-storage:${m[1]}.${m[2]}`;
if ((m = /^CREATE EXTENSION IF NOT EXISTS (\w+)/i.exec(t))) return `extension:${m[1]}`;
if ((m = /^CREATE SEQUENCE IF NOT EXISTS (\w+)/i.exec(t))) return `sequence:${m[1]}`;
if ((m = /^SELECT setval\('(\w+)'/i.exec(t))) return `setval:${m[1]}`;
if ((m = /^INSERT INTO (\w+)/i.exec(t))) return `insert:${m[1]}`;
if (/^DO \$/i.test(t)) return `do:${createHash('sha256').update(t).digest('hex').slice(0, 12)}`;
if (t === ';') return 'empty';
return `unknown:${t.slice(0, 60)}`;
}
type Rewrite = (text: string) => string;
interface Rule {
id: string;
reason: string;
action: 'drop' | Rewrite;
}
function replaceOnce(from: string | RegExp, to: string): Rewrite {
return (text) => {
// nosemgrep: javascript.lang.security.audit.detect-non-literal-regexp.detect-non-literal-regexp -- `from` is a constant capability-rule pattern defined in this script; the copy only forces the global flag for counting
const hits = typeof from === 'string' ? text.split(from).length - 1 : (text.match(new RegExp(from.source, `${from.flags.replace('g', '')}g`)) ?? []).length;
if (hits !== 1) throw new SchemaBuildError(`PGLite rule expected exactly one match of ${String(from)}, found ${hits}`);
return text.replace(from, to);
};
}
function columnLineIndex(lines: string[], column: string): number {
// nosemgrep: javascript.lang.security.audit.detect-non-literal-regexp.detect-non-literal-regexp -- `column` is a constant column name from this script's capability rules, never external input
const idx = lines.map((l, i) => (new RegExp(`^\\s+${column}\\s`).test(l) ? i : -1)).filter((i) => i >= 0);
if (idx.length !== 1) throw new SchemaBuildError(`PGLite rule expected one column line for ${column}, found ${idx.length}`);
return idx[0]!;
}
function omitColumns(...columns: string[]): Rewrite {
return (text) => {
const lines = text.split('\n');
for (const c of columns) lines.splice(columnLineIndex(lines, c), 1);
return lines.join('\n');
};
}
function moveColumnsAfter(anchor: string, ...columns: string[]): Rewrite {
return (text) => {
const lines = text.split('\n');
const moved = columns.map((c) => lines.splice(columnLineIndex(lines, c), 1)[0]!);
lines.splice(columnLineIndex(lines, anchor) + 1, 0, ...moved);
return lines.join('\n');
};
}
const compose = (...fns: Rewrite[]): Rewrite => (text) => fns.reduce((t, f) => f(t), text);
const POSTGRES_ONLY = 'Postgres-only by design; not part of the PGLite bootstrap';
const MIGRATION_TIMING = 'PGLite gets it later from its migration, so fresh PGLite brains keep master\'s column/object order';
/**
* Explicit PGLite capability rules, keyed by statement identity. A statement
* with no rule is portable and copied verbatim. Every rule must match.
*/
export const PGLITE_RULES: readonly Rule[] = [
{ id: 'extension:pgcrypto', reason: 'PGLite ships no pgcrypto; gen_random_uuid() is core', action: 'drop' },
{ id: 'table:sources', reason: `sources.chunker_version: ${MIGRATION_TIMING}`, action: omitColumns('chunker_version') },
{ id: 'add-column:sources.upstream_checked_at', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'add-column:sources.upstream_commit', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'add-column:sources.upstream_behind', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'index:pages_generation_idx', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'index:idx_pages_updated_at_desc', reason: MIGRATION_TIMING, action: 'drop' },
{
id: 'table:content_chunks',
reason: `embedding dims/model are runtime placeholders; code-symbol and search_vector columns: ${MIGRATION_TIMING}`,
action: compose(
replaceOnce(/vector\(1536\)/, 'vector(__EMBEDDING_DIMS__)'),
replaceOnce("DEFAULT 'text-embedding-3-large'", "DEFAULT '__EMBEDDING_MODEL__'"),
omitColumns('parent_symbol_path', 'doc_comment', 'symbol_name_qualified', 'search_vector'),
),
},
{ id: 'index:idx_chunks_search_vector', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'index:idx_chunks_symbol_qualified', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'function:update_chunk_search_vector', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'drop-trigger:content_chunks.chunk_search_vector_trigger', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'trigger:content_chunks.chunk_search_vector_trigger', reason: MIGRATION_TIMING, action: 'drop' },
...['code_edges_chunk', 'code_edges_symbol'].flatMap((t): Rule[] => [{ id: `table:${t}`, reason: MIGRATION_TIMING, action: 'drop' }]),
...['idx_code_edges_chunk_from', 'idx_code_edges_chunk_to', 'idx_code_edges_chunk_to_symbol', 'idx_code_edges_chunk_from_symbol',
'idx_code_edges_symbol_from', 'idx_code_edges_symbol_to', 'idx_code_edges_symbol_from_symbol']
.map((i): Rule => ({ id: `index:${i}`, reason: MIGRATION_TIMING, action: 'drop' })),
{
id: 'insert:config',
reason: 'PGLite records engine=pglite; the embedding model/dims are runtime placeholders',
action: compose(
replaceOnce("('version', '1'),\n", "('version', '1'),\n ('engine', 'pglite'),\n"),
replaceOnce("('embedding_model', 'text-embedding-3-large')", "('embedding_model', '__EMBEDDING_MODEL__')"),
replaceOnce("('embedding_dimensions', '1536')", "('embedding_dimensions', '__EMBEDDING_DIMS__')"),
),
},
{ id: 'drop-column:files.storage_url', reason: `${POSTGRES_ONLY} (legacy Supabase Storage column cleanup)`, action: 'drop' },
{ id: 'table:file_migration_ledger', reason: `${POSTGRES_ONLY} (Supabase Storage migration ledger)`, action: 'drop' },
{ id: 'index:idx_file_migration_ledger_status', reason: POSTGRES_ONLY, action: 'drop' },
{
id: 'function:update_page_search_vector',
reason: 'the PGLite blob body has no comment lines; prosrc is pinned by the E4 blob catalog and the upgrade-replay golden',
action: (text) => text.replace(/\n[ \t]+--[^\n]*(?=\n)/g, ''),
},
{ id: 'table:minion_jobs', reason: 'master\'s PGLite column order (result/progress/error_text/stacktrace after the private-queue columns)', action: moveColumnsAfter('private_queue_lease_until', 'result', 'progress', 'error_text', 'stacktrace') },
{ id: 'set-storage:minion_attachments.content', reason: `${POSTGRES_ONLY} (TOAST storage tuning)`, action: 'drop' },
{ id: 'table:dream_verdicts', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'index:dream_verdicts_expires_idx', reason: MIGRATION_TIMING, action: 'drop' },
{ id: 'function:notify_minion_job_change', reason: `${POSTGRES_ONLY} (LISTEN/NOTIFY wakeups need a server)`, action: 'drop' },
{ id: 'drop-trigger:minion_jobs.minion_job_notify', reason: POSTGRES_ONLY, action: 'drop' },
{ id: 'trigger:minion_jobs.minion_job_notify', reason: POSTGRES_ONLY, action: 'drop' },
];
/** DO blocks outside fragment regions are opaque, so each is classified by content hash. */
export const PGLITE_DO_BLOCKS: Readonly<Record<string, { keep: boolean; reason: string }>> = {
'146e456b365a': { keep: false, reason: 'schema.sql RLS enablement block (#5227 conditional enables): PGLite has no role system (the shared-skills fragment keeps its own block)' },
};
interface Addition {
after: string;
reason: string;
sql: string;
}
/** PGLite-only statements (Postgres gets these objects from migrations). */
export const PGLITE_ADDITIONS: readonly Addition[] = [
{
after: 'index:idx_links_origin',
reason: 'page_links alias view is part of the PGLite bootstrap; Postgres gets it from a migration',
sql: `-- v0.38: page_links is the alias the engine queries use (pglite-engine.ts +
-- postgres-engine.ts both JOIN page_links pl ON pl.to_page_id = p.id). The
-- alias predates the table-name standardization; the canonical table is
-- links. Brainstorm domain-bank connection_count tiebreaker and the
-- doctor link-density score read through this view.
--
-- The projection is intentionally NARROW (id, from_page_id, to_page_id only).
-- Engine queries only reference pl.id (via COUNT(*)) and pl.to_page_id.
-- Including link_source / origin_page_id / etc. in the view would couple
-- the alias to columns that didn't exist in pre-v0.13 brains AND would
-- block ALTER TABLE DROP COLUMN on those columns during upgrades.
CREATE OR REPLACE VIEW page_links AS
SELECT id, from_page_id, to_page_id FROM links;`,
},
{
after: 'drop-function:update_page_search_vector_from_timeline',
reason: 'slug_aliases / page_aliases are bootstrap tables on PGLite (before the managed-writer guard loop); Postgres gets them from migrations',
sql: `-- v0.42 type-unification (T1, plan D1+D11+D17): slug_aliases backs the
-- concept-redirect → alias-table migration. Wikilinks like
-- [[old-redirect-slug]] resolve to canonical via engine.resolveSlugWithAlias
-- short-circuit. Source-scoped throughout (codex F12: dangling_aliases
-- doctor check joins on (source_id, alias_slug)).
CREATE TABLE IF NOT EXISTS slug_aliases (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
alias_slug TEXT NOT NULL,
canonical_slug TEXT NOT NULL,
notes TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT slug_aliases_no_self CHECK (alias_slug <> canonical_slug),
CONSTRAINT slug_aliases_uniq UNIQUE (source_id, alias_slug)
);
CREATE INDEX IF NOT EXISTS slug_aliases_canonical_idx
ON slug_aliases (source_id, canonical_slug);
-- T3 retrieval-cathedral (retrieval-maxpool incident): free-text alias
-- resolution for SEARCH. Distinct from slug_aliases (slug->slug wikilink
-- redirect): page_aliases maps a normalized free-text name ("hall of light",
-- "明堂") to a canonical slug so a query that is a chosen name surfaces the
-- page. alias_norm is normalizeAlias() output; the (source_id, alias_norm,
-- slug) triple is unique so re-ingest is idempotent without blocking a second
-- page claiming the same alias (collisions reported + resolved at query time).
CREATE TABLE IF NOT EXISTS page_aliases (
id BIGSERIAL PRIMARY KEY,
source_id TEXT NOT NULL,
alias_norm TEXT NOT NULL,
slug TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
CONSTRAINT page_aliases_uniq UNIQUE (source_id, alias_norm, slug)
);
CREATE INDEX IF NOT EXISTS page_aliases_lookup_idx
ON page_aliases (source_id, alias_norm);
CREATE INDEX IF NOT EXISTS page_aliases_slug_idx
ON page_aliases (source_id, slug);`,
},
];
export function renderPgliteTemplateSql(schemaSql: string): string {
const out: string[] = [];
const usedRules = new Set<string>();
const usedDo = new Set<string>();
const usedAdditions = new Set<Addition>();
for (const part of parseSchemaSql(schemaSql)) {
if (part.kind === 'region') {
out.push(`\n${regionBody(part.fragment.pglite)}`);
continue;
}
for (const s of splitSql(part.text, part.line)) {
if (!s.norm) {
out.push(s.text);
continue;
}
const id = identify(s);
if (id.startsWith('unknown:')) {
fail(`${SCHEMA_SQL_PATH}:${s.line} has a statement the PGLite generator does not recognize: ${s.norm.slice(0, 80)}`,
'the PGLite bootstrap is derived statement by statement; an unknown construct may be Postgres-only.',
'teach identify() in scripts/build-schema.ts the statement kind and add a PGLITE_RULES entry if PGLite needs it changed or dropped.');
}
let text = s.text;
if (id.startsWith('do:')) {
const cls = PGLITE_DO_BLOCKS[id.slice(3)];
if (!cls) {
fail(`${SCHEMA_SQL_PATH}:${s.line} DO block ${id.slice(3)} is not classified for PGLite`,
'DO blocks are opaque (roles, RLS, catalog probes); each must be explicitly kept or dropped for PGLite.',
`add '${id.slice(3)}': { keep: <true|false>, reason: '...' } to PGLITE_DO_BLOCKS in scripts/build-schema.ts.`);
}
usedDo.add(id.slice(3));
if (!cls.keep) continue;
}
const rule = PGLITE_RULES.find((r) => r.id === id);
if (rule) {
if (usedRules.has(rule.id)) fail(`${SCHEMA_SQL_PATH}:${s.line} matches PGLite rule ${rule.id} a second time`, 'rules are keyed by statement identity and must match exactly one statement.', 'make the identity unique or split the rule.');
usedRules.add(rule.id);
if (rule.action === 'drop') continue;
text = rule.action(text);
}
out.push(stripSqlComments(text));
for (const a of PGLITE_ADDITIONS.filter((x) => x.after === id)) {
usedAdditions.add(a);
out.push(`\n${stripSqlComments(a.sql).replace(/\s+$/, '')}\n`);
}
}
}
const stale = [
...PGLITE_RULES.filter((r) => !usedRules.has(r.id)).map((r) => `rule ${r.id}`),
...Object.keys(PGLITE_DO_BLOCKS).filter((k) => !usedDo.has(k)).map((k) => `DO block ${k}`),
...PGLITE_ADDITIONS.filter((a) => !usedAdditions.has(a)).map((a) => `addition anchored after ${a.after}`),
];
if (stale.length > 0) {
fail(`PGLite capability rules no longer match ${SCHEMA_SQL_PATH}: ${stale.join(', ')}`,
'a rule whose statement changed or disappeared would silently stop applying.',
'update or delete the rule in scripts/build-schema.ts to match the new schema.sql text.');
}
return `\n-- GBrain PGLite schema (local embedded Postgres), generated from ${SCHEMA_SQL_PATH}\n-- and the TS schema fragments by scripts/build-schema.ts.\n${out.join('')}\n`;
}
export function renderPgliteModule(schemaSql: string): string {
return `// AUTO-GENERATED — do not edit. Run: bun run build:schema
// Sources: ${SCHEMA_SQL_PATH} + the TS fragment modules, transformed by the PGLite
// capability rules in scripts/build-schema.ts (docs/ENGINES.md#canonical-schema-sources).
// A template: __EMBEDDING_DIMS__ / __EMBEDDING_MODEL__ and the chunk-index and
// FTS-language policies are applied at runtime by getPGLiteSchema(dims, model).
import { applyChunkEmbeddingIndexPolicy } from './vector-index.ts';
import { applyFtsLanguagePolicy } from './fts-language.ts';
import { DEFAULT_EMBEDDING_MODEL, DEFAULT_EMBEDDING_DIMENSIONS } from './ai/defaults.ts';
export const PGLITE_SCHEMA_SQL_TEMPLATE = \`${escapeTemplate(renderPgliteTemplateSql(schemaSql))}\`;
export function getPGLiteSchema(
dims: number = DEFAULT_EMBEDDING_DIMENSIONS,
model: string = DEFAULT_EMBEDDING_MODEL,
): string {
const parsedDims = Number(dims);
if (!Number.isInteger(parsedDims) || parsedDims <= 0) {
throw new Error(\`Invalid embedding dimensions: \${dims}\`);
}
const sanitizedModel = String(model).replace(/'/g, "''");
return applyFtsLanguagePolicy(applyChunkEmbeddingIndexPolicy(PGLITE_SCHEMA_SQL_TEMPLATE, parsedDims))
.replace(/__EMBEDDING_DIMS__/g, String(parsedDims))
.replace(/__EMBEDDING_MODEL__/g, sanitizedModel);
}
/** Back-compat: pre-computed default-1536 schema for existing callers. */
export const PGLITE_SCHEMA_SQL = getPGLiteSchema();
`;
}
// ---------------------------------------------------------------------------
// Chain
// ---------------------------------------------------------------------------
export interface BuildOutputs {
[repoPath: string]: string;
}
/** Run the whole chain in fixed order from the committed schema.sql. */
export function buildAll(schemaSqlIn = readFileSync(join(REPO, SCHEMA_SQL_PATH), 'utf8')): BuildOutputs {
const schemaSql = renderSchemaSql(schemaSqlIn);
return {
[SCHEMA_SQL_PATH]: schemaSql,
[EMBEDDED_PATH]: renderEmbedded(schemaSql),
[PGLITE_PATH]: renderPgliteModule(schemaSql),
};
}
function main(argv: string[]): number {
let outDir = REPO;
for (let i = 0; i < argv.length; i++) {
if (argv[i] === '--out-dir') outDir = resolve(argv[++i]!);
else {
console.error(`unknown argument ${argv[i]}`);
return 2;
}
}
let outputs: BuildOutputs;
try {
outputs = buildAll();
} catch (e) {
if (e instanceof SchemaBuildError) {
console.error(e.message);
return 1;
}
throw e;
}
for (const [path, text] of Object.entries(outputs)) {
const target = join(outDir, path);
mkdirSync(dirname(target), { recursive: true });
writeFileSync(target, text);
if (outDir === REPO) console.log(`Generated ${path}`);
}
return 0;
}
if (import.meta.main) process.exit(main(process.argv.slice(2)));