Garry's Opinionated OpenClaw/Hermes Agent Brain
TypeScript
/**
* Connection Manager — route Postgres queries by query type (v0.30.1, Fix 1).
*
* Three pools, one decision: read() goes to the pooler (port 6543, fast,
* many connections); ddl() and bulk() go to a direct connection (port 5432,
* 30min statement_timeout, capped at 3 conns) so DDL doesn't time out on
* the Supabase pooler's 2-min statement_timeout.
*
* The connection-manager is the URL-routing layer. It layers on top of
* postgres.js's existing pool primitives + PostgresEngine.withReservedConnection.
*
* ┌─────────────────────────────┐
* │ GBRAIN_DATABASE_URL │ GBRAIN_DIRECT_DATABASE_URL (override)
* │ (pooler, port 6543) │
* └────────┬────────────────────┘
* │
* ▼ auto-detect Supabase
* ┌──────────────┐ ┌──────────────┐
* │ read pool │ │ direct pool │
* │ size 10 │ │ size 3 │
* │ prepare:no │ │ stmt 30min │
* │ stmt 5min │ │ idle 5min │
* └──────────────┘ │ mwm 256MB │
* └──────────────┘
*
* Architectural notes:
* - INSTANCE-owned (T5 / X1 amendment): each PostgresEngine constructs its
* own ConnectionManager. Worker engines (cycle, sync) inherit the parent's
* via constructor option `parent`. transaction() clones share the parent's.
* - Lazy direct pool init via cached Promise<Sql> (A1): concurrent first
* callers await the same Promise, so no double-init.
* - Kill-switch (F1): GBRAIN_DISABLE_DIRECT_POOL=1 falls back to single-pool
* legacy path. With parent set, inherit parent's kill-switch state (A2).
* - Audit (F8): every acquire/release/error logs to connection-events.jsonl.
* - Non-Supabase passthrough: if URL isn't a Supabase pooler and no
* GBRAIN_DIRECT_DATABASE_URL override, ddl()/bulk() share the read pool.
*/
import postgres from '#postgres'
import { traceSqlOptions } from './sql-trace.ts';
import { tlsRequiredDefaultError, withPgTls } from './pg-tls.ts';
import { gbrainApplicationName, readPoolPipelineOptions, transactionPipelineOptions, resolvePrepare, resolveSessionTimeouts, resolvePoolSize, resolveMaxLifetimeSeconds, resolveUrlConnectTimeout, resolveSharedTypes, resolveArrayTypes, endPoolBounded, poolHealthOptions, type PoolHealthHooks } from './db.ts';
import { redactPgUrl } from './url-redact.ts';
import { logConnectionEvent } from './connection-audit.ts';
import { directPoolProfile, type DirectPoolProfile } from './direct-pool-profile.ts';
import { deriveDirectUrl, deriveSessionPoolerUrl, isSupabasePoolerUrl } from './supabase-pooler-url.ts';
import { settingsConnectionOptions } from './embedded-engine.ts';
export { deriveDirectUrl, deriveSessionPoolerUrl, isSupabasePoolerUrl };
export type Sql = ReturnType<typeof postgres>;
export interface ConnectionManagerOpts {
/** Primary URL — usually the pooler (port 6543) on Supabase. */
url: string;
/**
* Override for the direct URL. When set, takes precedence over auto-derivation.
* Sourced from GBRAIN_DIRECT_DATABASE_URL or explicit caller config.
*/
directUrl?: string | null;
/**
* False for an engine opened from settings (engine-open.ts): the direct URL is `directUrl` alone (absent: none, or
* the route a known pooler URL derives) and GBRAIN_DIRECT_DATABASE_URL is never read.
*/
directUrlFromEnv?: boolean;
/** True for an engine opened from settings: the direct pool passes every connection field explicitly, never PG* env. */
settingsConnection?: boolean;
/**
* Inherit pools + kill-switch state from a parent manager (worker engines,
* transaction clones). When set, this manager is a thin reference holder
* and does NOT open its own pools.
*/
parent?: ConnectionManager;
/**
* Read pool size override (defaults to resolvePoolSize() — 10 normally).
*/
readPoolSize?: number;
/**
* Direct pool size override (defaults to GBRAIN_DIRECT_POOL_SIZE env or 3).
*/
directPoolSize?: number;
/**
* When true, the read pool is owned by some other code (e.g. db.ts:connect's
* module singleton). The connection manager will USE it via getReadPool but
* not call .end() on disconnect(). Default false (we own both pools).
*/
readPoolOwnedExternally?: boolean;
/** #5730: told when the driver discards a pooled connection left inside a transaction. */
onpoisoned?: (pool: 'read' | 'direct', status: string) => void;
/** #6383: the driver's stuck-connection and build-failure reports, per pool (`onpoisoned` stays separate for its callers). */
health?: (pool: 'read' | 'direct') => Omit<PoolHealthHooks, 'onpoisoned'>;
}
/** Default direct-pool size (P1 raised from 2 to 3). Override via env. */
export const DEFAULT_DIRECT_POOL_SIZE = 3;
/** DDL pool default statement_timeout (Fix 1). */
const DDL_STMT_TIMEOUT_MS = 30 * 60 * 1000; // 30min
/** DDL pool default idle-in-transaction timeout (Fix 1). */
const DDL_IDLE_TX_TIMEOUT_MS = 5 * 60 * 1000; // 5min
/** Bulk pool default maintenance_work_mem (P1). 256MB safe on Supabase. */
const BULK_MAINTENANCE_WORK_MEM = '256MB';
/** True when the URL targets Supavisor transaction mode (port 6543). */
function isTransactionPoolerUrl(url: string): boolean {
try {
return new URL(url.replace(/^postgres(ql)?:\/\//, 'http://')).port === '6543';
} catch {
return false;
}
}
/**
* Resolve the direct-pool URL from an explicit/env override + the primary URL.
*
* A direct override still pointing at the TRANSACTION-mode pooler (port 6543,
* usually a copy-paste of the primary URL) is a misconfiguration: the manager
* would believe DDL/bulk work runs on a long-timeout direct connection while
* still routing through Supavisor transaction mode (short timeouts, no
* prepared statements). Normalize it via deriveDirectUrl. Session-mode pooler
* URLs (pooler host, port 5432) pass through — they are a legitimate
* direct-ish target when the db.<ref> host is unreachable.
*/
export function normalizeDirectUrl(primaryUrl: string, override?: string | null): string | null {
const candidate = override ?? deriveDirectUrl(primaryUrl);
if (!candidate) return null;
if (!isTransactionPoolerUrl(candidate)) return candidate;
return deriveDirectUrl(candidate) ?? deriveDirectUrl(primaryUrl);
}
/**
* Error codes that mean "the direct host is unreachable from this network"
* (#1641). The auto-derived db.<ref>.supabase.co host is IPv6-only without
* the paid IPv4 add-on, so ENOTFOUND/ECONNREFUSED here is expected on
* IPv4-only networks — we fall back to the pooler instead of failing init.
*/
const NETWORK_UNREACHABLE_CODES = [
'ENOTFOUND', 'ECONNREFUSED', 'ENETUNREACH', 'EHOSTUNREACH',
'ETIMEDOUT', 'CONNECT_TIMEOUT',
];
/** True when err looks like a network-unreachable failure (not auth/SQL). */
export function isNetworkUnreachableError(err: unknown): boolean {
const code = (err as { code?: unknown } | null)?.code;
if (typeof code === 'string' && NETWORK_UNREACHABLE_CODES.includes(code)) return true;
const msg = err instanceof Error ? err.message : String(err);
return NETWORK_UNREACHABLE_CODES.some(c => msg.includes(c));
}
/**
* Read kill-switch state from env. Subordinate to parent manager's state
* when present (A2 inheritance).
*/
export function readKillSwitchEnv(): boolean {
return process.env.GBRAIN_DISABLE_DIRECT_POOL === '1' ||
process.env.GBRAIN_DISABLE_DIRECT_POOL === 'true';
}
/**
* Resolve direct pool size: explicit > env > the process profile's default
* (3 for a resident process, 1 otherwise).
*/
export function resolveDirectPoolSize(explicit?: number): number {
if (typeof explicit === 'number' && explicit > 0) return explicit;
const raw = process.env.GBRAIN_DIRECT_POOL_SIZE;
if (raw) {
const parsed = parseInt(raw, 10);
if (Number.isFinite(parsed) && parsed > 0 && parsed <= 20) return parsed;
}
return directPoolProfile() === 'resident' ? DEFAULT_DIRECT_POOL_SIZE : 1;
}
export class ConnectionManager {
private readonly opts: ConnectionManagerOpts;
private _readPool: Sql | null = null;
private _readPoolOwnedExternally: boolean;
private _directInit: Promise<Sql | null> | null = null;
private _directPool: Sql | null = null;
private _killSwitch: boolean;
private _directUrl: string | null;
private _isSupabase: boolean;
/** #1915: true when _directUrl was auto-derived (no operator override) —
* only then may the unreachable-host path retry the session pooler. */
private _directUrlAutoDerived = false;
/** #1915: single-flight guard for the session-pooler retry. */
private _sessionInit: Promise<Sql> | null = null;
private _sessionFallbackLogged = false;
/** #8478: bumped by disconnect() and retire(); a pool whose init began in an earlier epoch has no owner. */
private _epoch = 0;
constructor(opts: ConnectionManagerOpts) {
this.opts = opts;
this._readPoolOwnedExternally = opts.readPoolOwnedExternally === true;
// A2: kill-switch resolution. Parent overrides env when present.
if (opts.parent) {
this._killSwitch = opts.parent.isKillSwitchActive();
this._isSupabase = opts.parent.isSupabase();
this._directUrl = opts.parent.resolveDirectUrl();
this._directUrlAutoDerived = opts.parent.isDirectUrlAutoDerived();
this._readPool = opts.parent.peekReadPool();
this._readPoolOwnedExternally = true; // never end the parent's pool
} else {
this._killSwitch = readKillSwitchEnv();
this._isSupabase = isSupabasePoolerUrl(opts.url);
// Direct URL: explicit override > env > derive > null. Pooler-shaped
// overrides are normalized to a real direct host (or dropped).
const envOverride = opts.directUrlFromEnv === false ? undefined : process.env.GBRAIN_DIRECT_DATABASE_URL;
const override = opts.directUrl ?? envOverride;
this._directUrl = normalizeDirectUrl(opts.url, override);
// #1915: an operator override is respected as-is — never second-guessed
// with the session-pooler retry. Only auto-derived URLs may fall back.
this._directUrlAutoDerived = override == null;
}
}
/** Known poolers derive their route; other deployments require a valid explicit override. */
/** Statements route through the direct pool. A one-shot command keeps it for schema DDL only (`ddl()`). */
isDualPoolActive(): boolean {
return directPoolProfile() !== 'one_shot' && this.isDirectRouteConfigured();
}
private isDirectRouteConfigured(): boolean {
if (this._killSwitch || !this._directUrl) return false;
if (this._isSupabase) return true;
if (this._directUrlAutoDerived) return false;
try { return ['postgres:', 'postgresql:'].includes(new URL(this._directUrl).protocol); }
catch { return false; }
}
isSupabase(): boolean { return this._isSupabase; }
isKillSwitchActive(): boolean { return this._killSwitch; }
resolveDirectUrl(): string | null { return this._directUrl; }
isDirectUrlAutoDerived(): boolean { return this._directUrlAutoDerived; }
/**
* Internal: peek at the read pool without forcing init. Used by parent
* inheritance to share the same instance.
*/
peekReadPool(): Sql | null { return this._readPool; }
/**
* Set the read pool. Used by db.ts:connect or PostgresEngine.connect when
* they own the pool externally (and connection-manager is just routing).
*/
setReadPool(sql: Sql): void {
this._readPool = sql;
this._readPoolOwnedExternally = true;
}
/**
* Get or lazily create the read pool. Honors `readPoolOwnedExternally` so
* we don't double-create when db.ts:connect already owns the singleton.
*/
async getReadPool(): Promise<Sql> {
if (this._readPool) return this._readPool;
if (this._readPoolOwnedExternally) {
throw new Error('connection-manager: read pool marked as externally-owned but not provided');
}
const opts: Record<string, unknown> = {
max: resolvePoolSize(this.opts.readPoolSize),
idle_timeout: 20,
connect_timeout: resolveUrlConnectTimeout(this.opts.url),
// Explicit (matches the postgres.js implicit default; GBRAIN_POOL_MAX_LIFETIME_S overrides).
max_lifetime: resolveMaxLifetimeSeconds(),
types: { bigint: postgres.BigInt },
onnotice: process.env.GBRAIN_PG_NOTICES === '1' ? undefined : () => {},
shared_types: resolveSharedTypes(this.opts.url),
array_types: resolveArrayTypes(this.opts.url),
...readPoolPipelineOptions(this.opts.url),
...transactionPipelineOptions(this.opts.url),
};
const timeouts = resolveSessionTimeouts();
Object.assign(opts, poolHealthOptions({ ...this.opts.health?.('read'), onpoisoned: (status: string) => this.opts.onpoisoned?.('read', status) }, timeouts.statement_timeout));
if (Object.keys(timeouts).length > 0) opts.connection = timeouts;
const prepare = resolvePrepare(this.opts.url);
if (typeof prepare === 'boolean') opts.prepare = prepare;
this._readPool = postgres(this.opts.url, traceSqlOptions(withPgTls(this.opts.url, opts), 'read'));
logConnectionEvent({ pool: 'read', op: 'init' });
return this._readPool;
}
/**
* Acquire the read connection. Synchronous accessor — assumes read pool
* is already initialized (matches existing engine.sql semantics).
* Throws if pool not ready.
*/
read(): Sql {
if (!this._readPool) {
throw new Error('connection-manager: read pool not initialized; call getReadPool() first or set externally');
}
return this._readPool;
}
/**
* Acquire (and lazy-init) the direct DDL pool. The kill switch or an
* unconfigured direct route returns the read pool (single-pool fallback).
* A one-shot command still gets the direct pool here: schema DDL needs its
* session advisory lock and long statement timeout.
*
* A1: lazy init wraps in a cached Promise<Sql> so concurrent first-callers
* await the same init instead of racing two pool constructions.
*/
async ddl(): Promise<Sql> {
if (!this.isDirectRouteConfigured()) {
return this.getReadPool();
}
return this.getDirectPool();
}
/**
* Acquire the direct pool for a long-running BULK operation. Caller can
* override the per-op timeout via SET LOCAL inside a sql.begin block.
* Same pool as ddl(); the distinction is callsite intent (used by audit
* + caller-side timeout SET LOCAL).
*/
async bulk(_timeoutSeconds?: number): Promise<Sql> {
if (!this.isDualPoolActive()) {
return this.getReadPool();
}
return this.getDirectPool();
}
private async getDirectPool(): Promise<Sql> {
if (this._directPool) return this._directPool;
const epoch = this._epoch;
// A1: cache the Promise so concurrent first callers await the same init.
if (!this._directInit) {
this._directInit = this.initDirectPool().then(pool => this.adoptDirectPool(pool, epoch)).catch(err => {
// Reset cache on failure so next caller can retry.
if (this._epoch === epoch) this._directInit = null;
throw err;
});
}
let pool: Sql | null;
try {
pool = await this._directInit;
} catch (err) {
// #8478: a manager closed while this init was in flight starts no fallback.
if (this._epoch !== epoch) throw err;
// #1641: the derived direct host (db.<ref>.supabase.co) is IPv6-only
// without Supabase's IPv4 add-on. On IPv4-only networks the direct
// pool can never connect — permanently fall back to the read pool
// (self-activating kill-switch) instead of failing init/migrations.
// Non-network errors (auth, SQL) still throw: they mean misconfig,
// not unreachability.
if (isNetworkUnreachableError(err)) {
// #1915: before the permanent read-pool fallback, retry ONCE against
// the SESSION pooler (same host as the transaction pooler, port 5432).
// It is IPv4-reachable and honors the direct pool's startup-parameter
// timeouts, so DDL/bulk keep their 30min budget instead of dying on
// the transaction pooler's ~8s statement_timeout (cold-start init
// failures). Auto-derived URLs only — operator overrides are final.
if (this._directUrlAutoDerived) {
try {
// Single-flight FIRST, URL-equality guard second: caller 1 of a
// rejected _directInit mutates _directUrl to the session URL
// before starting the retry, so a concurrent caller 2 that
// checked `sessionUrl !== this._directUrl` first would see them
// equal, skip the in-flight retry entirely, and trip the
// read-pool kill-switch while caller 1's session pooler connects.
if (this._sessionInit) {
return await this._sessionInit;
}
const sessionUrl = deriveSessionPoolerUrl(this.opts.url);
if (sessionUrl && sessionUrl !== this._directUrl) {
const prevHost = this._directUrl ? this.hostOnly(this._directUrl) : 'unknown host';
this._directUrl = sessionUrl;
this._sessionInit = this.initDirectPool().then(p => this.adoptDirectPool(p, epoch));
const p = await this._sessionInit;
if (!this._sessionFallbackLogged) {
this._sessionFallbackLogged = true;
console.error(
`gbrain: direct connection to ${prevHost} unreachable; using the Supabase session pooler ` +
`(${this.hostOnly(sessionUrl)}) for DDL/bulk instead — long-timeout settings still apply. ` +
'Set GBRAIN_DIRECT_DATABASE_URL to override.',
);
}
return p;
}
} catch (sessionErr) {
if (this._epoch !== epoch) throw sessionErr;
// Session pooler also failed → fall through to the #1641
// read-pool kill-switch below (the original behavior).
}
}
const alreadyWarned = this._killSwitch;
this._killSwitch = true;
const msg = err instanceof Error ? err.message : String(err);
if (!alreadyWarned) console.error(
`gbrain: direct connection to ${this._directUrl ? this.hostOnly(this._directUrl) : 'unknown host'} unreachable (${msg}); ` +
'falling back to the pooler for DDL/bulk (long migrations may hit the pooler statement timeout). ' +
'Set GBRAIN_DIRECT_DATABASE_URL to a reachable direct URL (e.g. the Session pooler, port 5432) or enable the Supabase IPv4 add-on; ' +
'GBRAIN_DISABLE_DIRECT_POOL=1 silences this.',
);
return this.getReadPool();
}
throw err;
}
if (!pool) {
// Defensive — initDirectPool should have thrown.
throw new Error('connection-manager: direct pool init returned null');
}
return pool;
}
/** #8478: a pool that finished opening after disconnect() or retire() is ended here; nobody else holds it. */
private async adoptDirectPool(pool: Sql, epoch: number): Promise<Sql> {
if (this._epoch !== epoch) {
await endPoolBounded(pool);
throw Object.assign(new Error('connection-manager: the direct pool finished opening after this manager was closed'), { code: 'CONNECTION_ENDED' });
}
this._directPool = pool;
return pool;
}
private async initDirectPool(): Promise<Sql> {
if (!this._directUrl) {
throw new Error('connection-manager: cannot init direct pool — no direct URL');
}
const size = resolveDirectPoolSize(this.opts.directPoolSize);
const opts: Record<string, unknown> = {
max: size,
idle_timeout: 20,
connect_timeout: resolveUrlConnectTimeout(this._directUrl),
// Explicit (matches the postgres.js implicit default; GBRAIN_POOL_MAX_LIFETIME_S overrides).
max_lifetime: resolveMaxLifetimeSeconds(),
types: { bigint: postgres.BigInt },
// Always use prepared statements on the direct pool — no PgBouncer
// here, so the prepare-cache invalidation issue doesn't apply.
prepare: true,
onnotice: process.env.GBRAIN_PG_NOTICES === '1' ? undefined : () => {},
...poolHealthOptions({ ...this.opts.health?.('direct'), onpoisoned: (status: string) => this.opts.onpoisoned?.('direct', status) }, String(DDL_STMT_TIMEOUT_MS)),
shared_types: resolveSharedTypes(this._directUrl),
array_types: resolveArrayTypes(this._directUrl),
// Apply DDL session GUCs as connection startup parameters (durable
// through any intermediary pooling layer, same trick as
// resolveSessionTimeouts).
connection: {
statement_timeout: String(DDL_STMT_TIMEOUT_MS),
idle_in_transaction_session_timeout: String(DDL_IDLE_TX_TIMEOUT_MS),
maintenance_work_mem: BULK_MAINTENANCE_WORK_MEM,
application_name: gbrainApplicationName(),
},
};
const t0 = Date.now();
let pool: Sql | null = null;
let directOpts: Record<string, unknown> = opts;
try {
directOpts = this.opts.settingsConnection ? { ...opts, ...settingsConnectionOptions(this._directUrl) } : opts;
pool = postgres(this._directUrl, traceSqlOptions(withPgTls(this._directUrl, directOpts), 'direct'));
// Probe to validate connectivity early.
await pool`SELECT 1`;
logConnectionEvent({
pool: 'ddl',
op: 'init',
duration_ms: Date.now() - t0,
host: this._directUrl ? this.hostOnly(this._directUrl) : undefined,
});
return pool;
} catch (err) {
// Don't leak the failed pool's sockets/timers (#1641 fallback keeps
// the process running afterward).
if (pool) await endPoolBounded(pool);
logConnectionEvent({
pool: 'ddl',
op: 'error',
duration_ms: Date.now() - t0,
error: { message: err instanceof Error ? err.message : String(err) },
});
throw (this._directUrl ? await tlsRequiredDefaultError(this._directUrl, err, directOpts) : null) ?? err;
}
}
/**
* SELECT 1 latency probe on each pool. Used by doctor's connection_routing
* check + healthCheck() in the gateway.
*/
async healthCheck(): Promise<{ read: number | null; direct: number | null }> {
const result: { read: number | null; direct: number | null } = { read: null, direct: null };
try {
const t0 = Date.now();
const pool = await this.getReadPool();
await pool`SELECT 1`;
result.read = Date.now() - t0;
} catch { /* leave null */ }
if (this.isDualPoolActive()) {
try {
const t0 = Date.now();
const pool = await this.getDirectPool();
await pool`SELECT 1`;
result.direct = Date.now() - t0;
} catch { /* leave null */ }
}
return result;
}
/**
* Disconnect pools we own. Read pool stays alive if marked externally owned
* (db.ts singleton path). Direct pool is always ours.
*/
async disconnect(): Promise<void> {
// #1972: end both pools concurrently with a gbrain-owned hard bound, so the
// per-pool drains don't STACK (sequential 2s waits → ~4-6s once the
// instance/module pool is added downstream) and neither can hang teardown
// past the CLI's 10s force-exit. endPoolBounded never throws.
const ends: Promise<void>[] = [];
this._epoch++;
this._sessionInit = null;
this._directInit = null;
if (this._directPool) {
ends.push(endPoolBounded(this._directPool));
this._directPool = null;
}
if (this._readPool && !this._readPoolOwnedExternally) {
ends.push(endPoolBounded(this._readPool));
this._readPool = null;
}
await Promise.all(ends);
}
/** Ends a manager a reconnect superseded once its in-flight work finishes, with no hard bound (a long hold such as CREATE INDEX CONCURRENTLY completes). */
retire(): void {
const pools = [this._directPool, this._readPoolOwnedExternally ? null : this._readPool];
this._epoch++;
this._sessionInit = null;
this._directPool = null;
this._directInit = null;
this._readPool = null;
for (const pool of pools) pool?.end().catch(() => {});
}
/**
* Diagnostic snapshot for doctor / get_health surfaces.
*/
describeMode(): {
mode: 'split' | 'single (kill-switch)' | 'single (non-supabase)' | 'single (no-direct-url)' | 'single (one-shot command)';
direct_host?: string;
kill_switch_active: boolean;
direct_pool_size: number;
direct_profile: DirectPoolProfile;
} {
let mode: 'split' | 'single (kill-switch)' | 'single (non-supabase)' | 'single (no-direct-url)' | 'single (one-shot command)';
if (this.isDualPoolActive()) mode = 'split';
else if (this.isDirectRouteConfigured()) mode = 'single (one-shot command)';
else if (this._killSwitch && (this._isSupabase || this._directUrl)) mode = 'single (kill-switch)';
else if (!this._isSupabase) mode = 'single (non-supabase)';
else mode = 'single (no-direct-url)';
return {
mode,
direct_host: this._directUrl ? this.hostOnly(this._directUrl) : undefined,
kill_switch_active: this._killSwitch,
direct_pool_size: resolveDirectPoolSize(this.opts.directPoolSize),
direct_profile: directPoolProfile(),
};
}
private hostOnly(url: string): string {
// Redact creds first, then strip everything except host:port for doctor display.
const redacted = redactPgUrl(url);
try {
const parsed = new URL(redacted.replace(/^postgres(ql)?:\/\//, 'http://'));
return `${parsed.hostname}${parsed.port ? `:${parsed.port}` : ''}`;
} catch {
return redacted;
}
}
}