Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
#!/usr/bin/env node
/**
* CI guard for the POSITIONAL jsonb double-encode footgun (#2339 / #2324 class).
*
* The legacy scripts/check-jsonb-pattern.sh only catches the template-tag form
* (`${JSON.stringify(x)}::jsonb`). It MISSES the positional-param form:
*
* engine.executeRaw(`... $3::jsonb ...`, [a, b, JSON.stringify(x)])
*
* Under postgres.js `.unsafe(sql, params)` a JS STRING bound to a `$N::jsonb`
* param double-encodes — the text→jsonb cast wraps the already-JSON string into a
* jsonb *string scalar*. PGLite parses it silently, so the bug is invisible in
* unit tests and only bites on real Postgres (it aborted every sync in #2339).
*
* This scanner flags any executeRaw / executeRawDirect / .unsafe(...) call whose
* balanced argument span contains BOTH a positional `$N::jsonb` cast
* (NOT `$N::text::jsonb`, NOT `$N::text[]`) AND a `JSON.stringify(` — the exact
* double-encode shape. It is heuristic by design (whole-span correlation); the
* real backstop is the DATABASE_URL-gated e2e parity test. Keep both.
*
* D3 extension: it ALSO flags UNCAST positional binds ($N with no cast at all)
* into known-JSONB columns fed by JSON.stringify. Postgres resolves an uncast
* param's type from its target column (Describe returns jsonb), so the driver
* re-serializes the already-stringified value — the same double-encode as
* `$N::jsonb`, with no cast token to grep for (the propose-takes
* dedup_against_fence_rows shape). Detection maps columns to binds via
* `INSERT INTO t (cols...) VALUES (...)` position and `col = $N` assignment,
* then verifies JSON.stringify feeds THAT param when the params argument is an
* array literal (falls back to whole-span correlation otherwise).
*
* Allowed forms (NOT flagged):
* - `$N::text::jsonb` + JSON.stringify (the fix: binds as text, cast parses it)
* - `$N::text[]` (the unnest path — arrays bind fine)
* - executeRawJsonb(...) (passes raw objects, not strings)
* - sql.json(x) (postgres.js native jsonb serializer)
* - a `jsonb-guard-ok` comment anywhere in the call span (explicit opt-out)
*
* Exit 0 = clean, 1 = violations found. Runs under node or bun.
*/
import { readdirSync, readFileSync, statSync } from 'node:fs';
import { join } from 'node:path';
// Default scan roots; overridable via argv so the guard's own test can point it
// at a fixture dir (e.g. `node check-jsonb-params.mjs /tmp/fixtures`).
const ROOTS = process.argv.slice(2).length > 0 ? process.argv.slice(2) : ['src', 'scripts'];
// executeRawDirect must precede executeRaw in the alternation so the longer name
// wins; executeRawJsonb is deliberately excluded (it passes objects). The
// optional `<...>` handles generic type args, e.g. `executeRaw<{ id: string }>(`.
//
// Only the postgres.js raw path is scanned (executeRaw/executeRawDirect/.unsafe).
// PGLite's native `this.db.query(...)` is intentionally NOT matched: its driver
// parses a text→jsonb cast natively, so the double-encode that bites postgres.js
// `.unsafe()` does not occur there (the `pglite-masks` invariant). The engine
// parity test pins that the resulting jsonb_typeof agrees across both engines.
const CALL_RE = /\b(executeRawDirect|executeRaw|unsafe)\s*(?:<[^>;]*>)?\s*\(/g;
/** Walk from the '(' at openIdx and return [start,end) of the balanced span,
* respecting strings, template literals, and comments. */
function findSpan(src, openIdx) {
let depth = 0;
let mode = 'code'; // code | line | block | sq | dq | tpl
for (let i = openIdx; i < src.length; i++) {
const c = src[i];
const n = src[i + 1];
if (mode === 'line') { if (c === '\n') mode = 'code'; continue; }
if (mode === 'block') { if (c === '*' && n === '/') { mode = 'code'; i++; } continue; }
if (mode === 'sq') { if (c === '\\') { i++; continue; } if (c === "'") mode = 'code'; continue; }
if (mode === 'dq') { if (c === '\\') { i++; continue; } if (c === '"') mode = 'code'; continue; }
if (mode === 'tpl') { if (c === '\\') { i++; continue; } if (c === '`') mode = 'code'; continue; }
// mode === 'code'
if (c === '/' && n === '/') { mode = 'line'; i++; continue; }
if (c === '/' && n === '*') { mode = 'block'; i++; continue; }
if (c === "'") { mode = 'sq'; continue; }
if (c === '"') { mode = 'dq'; continue; }
if (c === '`') { mode = 'tpl'; continue; }
if (c === '(') depth++;
else if (c === ')') { depth--; if (depth === 0) return [openIdx + 1, i]; }
}
return [openIdx + 1, src.length];
}
/** Blank out comments so a commented-out example doesn't trip the JSON.stringify probe. */
function stripComments(s) {
return s.replace(/\/\/[^\n]*/g, '').replace(/\/\*[\s\S]*?\*\//g, '');
}
// JSONB columns declared in src/core/schema-migrations/ (and schema.sql) that are written through the
// positional raw path. An uncast `$N` bound to one of these resolves to a
// jsonb-typed param server-side and double-encodes a JSON.stringify'd string
// exactly like `$N::jsonb` does. Keep in sync with the schema migrations when a new
// JSONB column gains a positional write site. Deliberately excludes
// short/ambiguous names (`config`, `data`, `meta`, `value_json`, ...) that
// also exist as non-JSONB columns or JS identifiers — those stay covered by
// the cast-form check + the e2e parity tests.
const KNOWN_JSONB_COLUMNS = [
'dedup_against_fence_rows',
'domain_scorecards',
'edge_metadata',
'dim_scores',
'source_tier_breakdown',
'standing_entities',
'surfaced_slugs',
'receipt_json',
'report_json',
'response_json',
'completed_keys',
];
/** Split a balanced span on top-level commas, respecting (), [], {}, strings,
* template literals, and comments. Mirrors findSpan's mode machine. */
function splitTopLevel(s) {
const parts = [];
let depth = 0;
let mode = 'code';
let start = 0;
for (let i = 0; i < s.length; i++) {
const c = s[i];
const n = s[i + 1];
if (mode === 'line') { if (c === '\n') mode = 'code'; continue; }
if (mode === 'block') { if (c === '*' && n === '/') { mode = 'code'; i++; } continue; }
if (mode === 'sq') { if (c === '\\') { i++; continue; } if (c === "'") mode = 'code'; continue; }
if (mode === 'dq') { if (c === '\\') { i++; continue; } if (c === '"') mode = 'code'; continue; }
if (mode === 'tpl') { if (c === '\\') { i++; continue; } if (c === '`') mode = 'code'; continue; }
if (c === '/' && n === '/') { mode = 'line'; i++; continue; }
if (c === '/' && n === '*') { mode = 'block'; i++; continue; }
if (c === "'") { mode = 'sq'; continue; }
if (c === '"') { mode = 'dq'; continue; }
if (c === '`') { mode = 'tpl'; continue; }
if (c === '(' || c === '[' || c === '{') depth++;
else if (c === ')' || c === ']' || c === '}') depth--;
else if (c === ',' && depth === 0) { parts.push(s.slice(start, i)); start = i + 1; }
}
parts.push(s.slice(start));
return parts;
}
/** Uncast `$N` binds into known-JSONB columns inside a call span.
* Returns [{ col, n }]. Two shapes: `col = $N` (UPDATE SET / WHERE) and
* positional mapping through `INSERT INTO t (cols...) VALUES (vals...)`. */
function findUncastJsonbColumnBinds(span) {
const found = [];
for (const col of KNOWN_JSONB_COLUMNS) {
const re = new RegExp('\\b' + col + '\\s*=\\s*\\$(\\d+)(?!\\s*::)', 'gi');
let m;
while ((m = re.exec(span))) found.push({ col, n: Number(m[1]) });
}
const insRe = /INSERT\s+INTO\s+\S+\s*\(([^)]*)\)\s*VALUES\s*\(/gi;
let mi;
while ((mi = insRe.exec(span))) {
const cols = mi[1].split(',').map((c) => c.trim().toLowerCase());
const [vs, ve] = findSpan(span, insRe.lastIndex - 1);
const values = splitTopLevel(span.slice(vs, ve)).map((v) => v.trim());
for (let i = 0; i < cols.length && i < values.length; i++) {
if (!KNOWN_JSONB_COLUMNS.includes(cols[i])) continue;
const bare = values[i].match(/^\$(\d+)$/);
if (bare) found.push({ col: cols[i], n: Number(bare[1]) });
}
}
return found;
}
/** Best-effort: the text of the Nth (1-based) positional param when the call's
* params argument is an array literal. Returns null when it isn't (variable
* reference, spread, ...) — caller falls back to whole-span correlation. */
function nthParamText(span, n) {
const args = splitTopLevel(span);
for (let a = 1; a < args.length; a++) {
const arg = args[a].trim();
if (!arg.startsWith('[')) continue;
const close = arg.lastIndexOf(']');
if (close === -1) return null;
const elems = splitTopLevel(arg.slice(1, close));
return elems[n - 1] ?? null;
}
return null;
}
const violations = [];
function scanFile(file) {
const src = readFileSync(file, 'utf8');
CALL_RE.lastIndex = 0;
let m;
while ((m = CALL_RE.exec(src))) {
const method = m[1];
const openIdx = m.index + m[0].length - 1; // index of the '('
const [s, e] = findSpan(src, openIdx);
const span = src.slice(s, e);
if (/jsonb-guard-ok/.test(span)) continue;
if (!/JSON\.stringify\s*\(/.test(stripComments(span))) continue;
const line = src.slice(0, s).split('\n').length;
// A positional `$N::jsonb` that is NOT `$N::text::jsonb`.
const jsonbRe = /\$\d+\s*::\s*jsonb\b/g;
let j;
let badText = '';
while ((j = jsonbRe.exec(span))) {
const pre = span.slice(Math.max(0, j.index - 12), j.index);
if (/::\s*text\s*$/.test(pre)) continue; // $N::text::jsonb is the fix — allowed
badText = j[0].replace(/\s+/g, '');
break;
}
if (badText) {
violations.push(
`${file}:${line} ${method}(...) binds JSON.stringify into ${badText} — use $N::text::jsonb or pass a raw object (executeRawJsonb / sql.json)`,
);
continue;
}
// D3 extension: uncast `$N` into a known-JSONB column. Postgres describes
// the param as jsonb from the target column, so the same double-encode
// fires with no `::jsonb` token in the SQL.
for (const bind of findUncastJsonbColumnBinds(span)) {
const nth = nthParamText(span, bind.n);
const feeds = nth === null ? true : /JSON\.stringify\s*\(/.test(stripComments(nth));
if (!feeds) continue;
violations.push(
`${file}:${line} ${method}(...) binds JSON.stringify into uncast $${bind.n} targeting JSONB column '${bind.col}' — the param resolves to jsonb server-side and double-encodes; use $${bind.n}::text::jsonb or pass a raw object (executeRawJsonb / sql.json)`,
);
break;
}
}
}
function walk(dir) {
let ents;
try { ents = readdirSync(dir); } catch { return; }
for (const ent of ents) {
if (ent === 'node_modules') continue;
const p = join(dir, ent);
const st = statSync(p);
if (st.isDirectory()) walk(p);
else if (p.endsWith('.ts') && !p.endsWith('.test.ts')) scanFile(p);
}
}
for (const root of ROOTS) walk(root);
if (violations.length) {
console.error('JSONB positional double-encode violations (#2339 class):\n');
for (const v of violations) console.error(' ' + v);
console.error(`\n${violations.length} violation(s). Fix: bind through $N::text::jsonb (keeping JSON.stringify), or pass a raw object via executeRawJsonb / sql.json. See docs/ENGINES.md.`);
process.exit(1);
}
console.log('check-jsonb-params: clean (no positional $N::jsonb + JSON.stringify double-encodes)');