Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
20.6 KB
Raw Blame
/**
 * Generic backfill runner — v0.30.1 (Fix 3).
 *
 * Generalizes the keyset+checkpoint+adaptive-batch pattern from
 * src/core/backfill-effective-date.ts so future backfills (embedding_voyage,
 * emotional_weight, etc.) reuse the proven pieces instead of cloning them.
 *
 * Codex T3 correction: writes go through engine.withReservedConnection so
 * BEGIN / SET LOCAL / UPDATE / COMMIT execute on the SAME backend. With
 * pooled engine.executeRaw, SET LOCAL evaporates between calls because
 * the next call can land on a different connection. Pinned backend +
 * SET LOCAL inside the same txn gives durable per-batch timeout semantics.
 *
 * Codex P2 / X4: backfills declare an optional `requiredIndex` (partial
 * index on the predicate column). On first run, the runner verifies the
 * index exists and creates it CONCURRENTLY if missing.
 */

import type { BrainEngine } from './engine.ts';
import { isStatementTimeoutError, isRetryableConnError } from './retry-matcher.ts';
// v0.41.18.0: swap inline setTimeout for shared abortableSleep so the sleep
// primitive is unified across the codebase. Backfill's outer-loop + batch-
// halving control flow stays intact (orthogonal to withRetry's per-call retry
// shape) — the unification is at the sleep primitive only, per codex H-6.
import { abortableSleep } from './retry.ts';
import { GBrainError } from './types.ts';
import { redactConnectionInfo } from './audit/redact-connection-info.ts';
import { classifyItemFailure, ItemRun } from './item-failure.ts';
import { OperationError } from './ops/contract.ts';
import { READ_WRITE_BEGIN } from './postgres-engine/read-write-transactions.ts';
import { heldUnchanged, noteSkip, parseSkipLedger, readSkipLedgerRaw, reportSkips, restoreSkipLedger, rowRevision, type BackfillSkip } from './backfill-skips.ts';

function brief(err: unknown): string {
  const code = (err as { code?: unknown } | null)?.code;
  const message = redactConnectionInfo(err instanceof Error ? err.message : String(err)).slice(0, 200);
  return typeof code === 'string' ? `${code} ${message}` : message;
}

/**
 * #5730: a failed batch whose ROLLBACK also failed. The reserved connection's
 * transaction state is unknown, so the run stops instead of retrying on it;
 * the driver discards the connection when it is released. Nothing from the
 * batch was committed and the checkpoint was not advanced.
 */
export class BackfillRollbackError extends GBrainError {
  readonly code = 'backfill_rollback_failed';
  constructor(name: string, batchError: unknown, rollbackError: unknown) {
    super(
      `Backfill ${name} stopped: ROLLBACK of a failed batch also failed`,
      `batch error: ${brief(batchError)}; rollback error: ${brief(rollbackError)}`,
      `gbrain backfill ${name} --resume`,
      'docs/ENGINES.md#backfill-rollback-failed',
    );
    this.name = 'BackfillRollbackError';
  }
}

/**
 * GBRA-130 A-47: the COMMIT of a batch was sent and failed without a server
 * verdict (a lost connection, no SQLSTATE of the row's own), so the batch may
 * or may not have committed. The run stops: it never bisects or skips an
 * undecided batch, and the checkpoint stays before it. A resumed run re-selects
 * the batch; rows that did commit no longer match and are not redone.
 */
export class BackfillCommitIndeterminateError extends GBrainError {
  readonly code = 'backfill_commit_indeterminate';
  constructor(name: string, commitError: unknown) {
    super(
      `Backfill ${name} stopped: a batch COMMIT was sent but its outcome is unknown`,
      `commit error: ${brief(commitError)}; the checkpoint stays before the batch`,
      `gbrain backfill ${name} --resume`,
      'docs/ENGINES.md#backfill-commit-indeterminate',
    );
    this.name = 'BackfillCommitIndeterminateError';
  }
}

export interface BackfillSpec<TRow = Record<string, unknown>> {
  /** Stable identifier — used in checkpoint key + CLI dispatch. */
  name: string;
  /** Postgres table name (used in keyset query). */
  table: string;
  /**
   * Primary-key column name. Keyset pagination uses `WHERE id > $lastId
   * ORDER BY id LIMIT $batchSize` — column name controls both ORDER BY
   * and the comparison. Defaults to 'id'.
   */
  idColumn?: string;
  /**
   * Columns to select for `compute()`. The id column is always included.
   */
  selectColumns: string[];
  /**
   * SQL fragment for `WHERE` (without `WHERE`). Names the un-backfilled rows.
   * E.g. "effective_date IS NULL" or "embedding_voyage IS NULL".
   */
  needsBackfill: string;
  /**
   * Compute updates for a batch of rows. Returns one entry per row that
   * needs updating; rows not present in the result are unchanged.
   */
  compute: (rows: TRow[], engine: BrainEngine) => Promise<Array<{ id: number; updates: Record<string, unknown> }>>;
  /**
   * Optional partial-index requirement (P2 / X4). Runner verifies/creates
   * the index CONCURRENTLY on first run. Skipped on PGLite (no CONCURRENTLY).
   */
  requiredIndex?: { name: string; sql: string };
  /** Estimate of rows-per-second for ETA reporting. Pure-display. */
  estimateRowsPerSecond?: number;
}

export interface BackfillRunOpts {
  /** Hard cap on total rows touched (testing). Undefined = no cap. */
  maxRows?: number;
  /** Initial batch size before adaptive halving. Default 1000. */
  batchSize?: number;
  /** Skip checkpoint, restart from id=0. Default false. */
  fresh?: boolean;
  /** Don't write; report what WOULD happen. Default false. */
  dryRun?: boolean;
  /** Per-batch progress callback. */
  onBatch?: (info: BackfillProgress) => void;
  /** Bail after N total errors. Default 200. */
  maxErrors?: number;
  /**
   * Per-batch statement timeout in seconds. Routed via SET LOCAL inside
   * the reserved-connection transaction. Default 600 (10min). Smaller
   * values fail fast; larger values let the runner do more work per batch.
   */
  perBatchTimeoutSec?: number;
}

export interface BackfillProgress {
  batch: number;
  rowsThisBatch: number;
  cumulative: number;
  lastId: number;
  errorsSeen: number;
  effectiveBatchSize: number;
}

export interface BackfillResult {
  examined: number;
  updated: number;
  errors: number;
  lastId: number;
  durationSec: number;
  /** True iff `maxRows` capped the run (more rows remain). */
  cappedByMaxRows: boolean;
  /** True iff `maxErrors` bailed the run. */
  cappedByErrors: boolean;
  /**
   * GBRA-130 A-47: every row recorded in `backfill.<name>.skipped` after this run (failing or held), with its code
   * and the command that retries it. A recorded row is behind the checkpoint and is retried by every later run.
   */
  skipped: BackfillSkip[];
}

const DEFAULT_BATCH_SIZE = 1000;
const DEFAULT_MAX_ERRORS = 200;
const DEFAULT_PER_BATCH_TIMEOUT_SEC = 600;
const MIN_BATCH_SIZE = 16;

/** Config-table key a backfill's keyset cursor persists under. Exported so other
 *  resumable keyset loops (reindex-search-vector) share the convention. */
export function checkpointKey(name: string): string {
  return `backfill.${name}.last_id`;
}

async function getCheckpoint(engine: BrainEngine, name: string, fresh: boolean): Promise<number> {
  if (fresh) return 0;
  try {
    const rows = await engine.executeRaw<{ value: string }>(
      `SELECT value FROM config WHERE key = $1 LIMIT 1`,
      [checkpointKey(name)],
    );
    if (rows.length === 0) return 0;
    const n = Number(rows[0].value);
    return Number.isFinite(n) && n >= 0 ? n : 0;
  } catch {
    return 0;
  }
}

async function setCheckpoint(engine: BrainEngine, name: string, lastId: number): Promise<void> {
  await engine.setConfig(checkpointKey(name), String(lastId));
}

/**
 * Verify or create the partial index a backfill declares. Postgres-only
 * (PGLite ignores CONCURRENTLY anyway, and partial-index is not always
 * supported). Returns false if the index is missing AND we couldn't create
 * it (caller decides whether to bail).
 */
export async function ensureBackfillIndex<TRow>(
  engine: BrainEngine,
  spec: BackfillSpec<TRow>,
): Promise<{ existed: boolean; created: boolean }> {
  if (engine.kind !== 'postgres' || !spec.requiredIndex) {
    return { existed: true, created: false };
  }
  const { name, sql } = spec.requiredIndex;
  try {
    const rows = await engine.executeRaw<{ exists: boolean }>(
      `SELECT EXISTS(SELECT 1 FROM pg_indexes WHERE indexname = $1) AS exists`,
      [name],
    );
    if (rows[0]?.exists) return { existed: true, created: false };
    // Create the index. CONCURRENTLY can't run inside a transaction, so we
    // route via the reserved connection and let the engine handle txn
    // semantics directly.
    await engine.withReservedConnection(async conn => {
      await conn.executeRaw(sql);
    }, { selfContained: true });
    return { existed: false, created: true };
  } catch (err) {
    process.stderr.write(`[backfill] index creation failed: ${(err as Error).message}; will continue without partial index\n`);
    return { existed: false, created: false };
  }
}

/** A COMMIT failure the server decided (a row's own SQLSTATE error): the batch rolled back, so it may be bisected. */
function decidedCommitFailure(err: unknown): boolean {
  const code = (err as { code?: unknown } | null)?.code;
  return typeof code === 'string' && /^[0-9A-Z]{5}$/.test(code) && classifyItemFailure(err).scope === 'item';
}

/** Failures of a batch transaction's BEGIN or SET LOCAL: never the rows' fault. */
const SETUP_FAILURES = new WeakSet<object>();

/** A failure of the batch's own rows (not a setup failure, a timeout, a dropped connection, a failed ROLLBACK or an undecided COMMIT): bisect it. */
function isolatable(err: unknown): boolean {
  if (err instanceof BackfillRollbackError || err instanceof BackfillCommitIndeterminateError) return false;
  if (typeof err === 'object' && err !== null && SETUP_FAILURES.has(err)) return false;
  if (isStatementTimeoutError(err) || isRetryableConnError(err)) return false;
  return classifyItemFailure(err).scope === 'item';
}

/**
 * Run a backfill end-to-end. Honors the checkpoint, halves on timeout,
 * reconnects on conn drop, bails on max errors.
 *
 * GBRA-130 A-47: a batch whose compute or write fails with an item-scoped
 * error is bisected down to single rows, each part computed and written in its
 * own transaction; a single failing row is recorded (backfill-skips.ts) and the
 * checkpoint moves past it. Recorded rows are retried first on every later run.
 * Inside a bisection any other failure stops the run with the checkpoint before
 * the batch. When most attempted rows fail (ItemRun's breaker), the run stops
 * `item_failures_systemic` and restores the skip record and checkpoint it began with.
 */
export async function runBackfill<TRow = Record<string, unknown>>(
  engine: BrainEngine,
  spec: BackfillSpec<TRow>,
  opts: BackfillRunOpts = {},
): Promise<BackfillResult> {
  const t0 = Date.now();
  const idCol = spec.idColumn ?? 'id';
  const cols = [idCol, ...spec.selectColumns.filter(c => c !== idCol)];
  const maxErrors = opts.maxErrors ?? DEFAULT_MAX_ERRORS;
  const perBatchTimeoutSec = opts.perBatchTimeoutSec ?? DEFAULT_PER_BATCH_TIMEOUT_SEC;
  const idOf = (row: TRow) => Number((row as Record<string, unknown>)[idCol]);

  // X4 / P2: verify/create the partial index up-front when declared.
  if (spec.requiredIndex) {
    await ensureBackfillIndex(engine, spec);
  }

  let batchSize = opts.batchSize ?? DEFAULT_BATCH_SIZE;
  const startCheckpoint = await getCheckpoint(engine, spec.name, false);
  let lastId = opts.fresh === true ? 0 : startCheckpoint;
  let examined = 0;
  let updated = 0;
  let errors = 0;
  let batchNum = 0;
  const startLedger = opts.dryRun ? null : await readSkipLedgerRaw(engine, spec.name);
  const ledger = parseSkipLedger(opts.fresh === true ? null : startLedger);
  const items = new ItemRun('backfill.rows');
  const { VERSION: producer } = await import('../version.ts');

  const saveLedger = async () => {
    const raw = JSON.stringify(ledger);
    if (!opts.dryRun && raw !== startLedger && (startLedger !== null || Object.keys(ledger.items).length > 0)) await engine.setConfig(`backfill.${spec.name}.skipped`, raw);
  };
  const finish = async (flags: { cappedByMaxRows: boolean; cappedByErrors: boolean }): Promise<BackfillResult> => {
    if (items.systemic()) throw items.systemic();
    await saveLedger();
    return { examined, updated, errors, lastId, durationSec: (Date.now() - t0) / 1000, ...flags, skipped: reportSkips(ledger, spec.name) };
  };

  // T3 fix: writes go through withReservedConnection so BEGIN / SET LOCAL
  // / UPDATE / COMMIT all happen on the same backend. Without this,
  // pooled executeRaw can land BEGIN on backend-A and UPDATE on backend-B
  // and SET LOCAL evaporates.
  const writeBatch = async (computedUpdates: Array<{ id: number; updates: Record<string, unknown> }>): Promise<number> => {
    if (opts.dryRun || computedUpdates.length === 0) return 0;
    return engine.withReservedConnection(async conn => {
      await conn.executeRaw(engine.kind === 'postgres' ? READ_WRITE_BEGIN : 'BEGIN');
      let batchUpdated = 0;
      let stage: 'setup' | 'update' | 'commit' = 'setup';
      try {
        if (engine.kind === 'postgres') {
          await conn.executeRaw(`SET LOCAL statement_timeout = '${perBatchTimeoutSec}s'`);
        }
        stage = 'update';
        for (const { id, updates } of computedUpdates) {
          const setClauses: string[] = [];
          const params: unknown[] = [id];
          let paramIdx = 2;
          for (const [col, val] of Object.entries(updates)) {
            setClauses.push(`${col} = $${paramIdx}`);
            params.push(val);
            paramIdx++;
          }
          if (setClauses.length === 0) continue;
          await conn.executeRaw(
            `UPDATE ${spec.table} SET ${setClauses.join(', ')} WHERE ${idCol} = $1`,
            params,
          );
          batchUpdated++;
        }
        stage = 'commit';
        await conn.executeRaw(`COMMIT`);
        return batchUpdated;
      } catch (err) {
        if (stage === 'setup' && typeof err === 'object' && err !== null) SETUP_FAILURES.add(err);
        if (stage === 'commit' && !decidedCommitFailure(err)) {
          await conn.executeRaw(`ROLLBACK`).catch(() => undefined);
          throw new BackfillCommitIndeterminateError(spec.name, err);
        }
        try {
          await conn.executeRaw(`ROLLBACK`);
        } catch (rollbackErr) {
          throw new BackfillRollbackError(spec.name, err, rollbackErr);
        }
        throw err;
      }
    }, { selfContained: true });
  };

  const now = () => new Date().toISOString();
  const attempt = async (rows: TRow[]): Promise<void> => {
    updated += await writeBatch(await spec.compute(rows, engine));
    for (const row of rows) { items.succeed(); delete ledger.items[String(idOf(row))]; }
  };
  /** Bisects rows that failed together with `first` down to single rows; returns how many rows it recorded as skipped. */
  const isolate = async (rows: TRow[], first: unknown): Promise<number> => {
    let skipped = 0;
    const visit = async (part: TRow[], error?: unknown): Promise<void> => {
      if (error === undefined) {
        try { await attempt(part); return; } catch (err) {
          if (!isolatable(err)) throw err;
          error = err;
        }
      }
      if (part.length === 1) {
        noteSkip(ledger, idOf(part[0]), rowRevision(part[0]), items.fail(String(idOf(part[0])), error), now(), producer);
        skipped++;
        return;
      }
      const mid = Math.ceil(part.length / 2);
      await visit(part.slice(0, mid));
      await visit(part.slice(mid));
    };
    await visit(rows, first);
    return skipped;
  };

  try {
    // Rows recorded by earlier runs that still need the backfill are retried one at a time first;
    // a held row is retried only once it or the gbrain version changed. Records of rows that landed or went away are dropped.
    const recorded = opts.dryRun ? [] : Object.keys(ledger.items).map(Number).filter(id => id <= lastId);
    for (let i = 0; i < recorded.length; i += 500) {
      const ids = recorded.slice(i, i + 500);
      const pending = await engine.executeRaw<TRow>(
        `SELECT ${cols.join(', ')} FROM ${spec.table} WHERE ${idCol} IN (${ids.map((_, n) => `$${n + 1}`).join(', ')}) AND (${spec.needsBackfill}) ORDER BY ${idCol}`, ids);
      const present = new Set(pending.map(idOf));
      for (const id of ids) if (!present.has(id)) delete ledger.items[String(id)];
      for (const row of pending) {
        if (heldUnchanged(ledger.items[String(idOf(row))], rowRevision(row), producer)) continue;
        examined++;
        try { await attempt([row]); } catch (err) {
          if (!isolatable(err)) throw err;
          errors += await isolate([row], err);
        }
      }
    }

    while (true) {
      const remaining = opts.maxRows ? Math.max(0, opts.maxRows - examined) : Number.POSITIVE_INFINITY;
      if (remaining <= 0) return await finish({ cappedByMaxRows: true, cappedByErrors: false });
      const effective = Math.min(batchSize, remaining);

      let rows: TRow[];
      try {
        rows = await engine.executeRaw<TRow>(
          `SELECT ${cols.join(', ')} FROM ${spec.table}
           WHERE ${idCol} > $1 AND (${spec.needsBackfill})
           ORDER BY ${idCol}
           LIMIT $2`,
          [lastId, effective],
        );
      } catch (err) {
        errors++;
        if (errors >= maxErrors) return await finish({ cappedByMaxRows: false, cappedByErrors: true });
        // Connection drop: brief sleep + retry the same window.
        if (isRetryableConnError(err)) {
          await abortableSleep(1000);
          continue;
        }
        throw err;
      }

      // No more rows match the predicate. Done.
      if (rows.length === 0) return await finish({ cappedByMaxRows: false, cappedByErrors: false });
      examined += rows.length;
      batchNum++;

      let computedUpdates: Array<{ id: number; updates: Record<string, unknown> }> | null = null;
      let skippedRows: number | null = null;
      try {
        computedUpdates = await spec.compute(rows, engine);
      } catch (err) {
        errors++;
        if (errors >= maxErrors) break;
        if (isStatementTimeoutError(err)) {
          batchSize = Math.max(MIN_BATCH_SIZE, Math.floor(batchSize / 2));
          process.stderr.write(`[backfill:${spec.name}] compute timeout; halving batch to ${batchSize}\n`);
          continue;
        }
        if (!isolatable(err)) throw err;
        skippedRows = await isolate(rows, err);
      }

      if (computedUpdates) {
        try {
          updated += await writeBatch(computedUpdates);
        } catch (err) {
          if (err instanceof BackfillRollbackError || err instanceof BackfillCommitIndeterminateError) throw err;
          errors++;
          if (errors >= maxErrors) break;
          if (isStatementTimeoutError(err)) {
            batchSize = Math.max(MIN_BATCH_SIZE, Math.floor(batchSize / 2));
            process.stderr.write(`[backfill:${spec.name}] write timeout; halving batch to ${batchSize}\n`);
            continue;
          }
          if (isRetryableConnError(err)) {
            await abortableSleep(1000);
            continue;
          }
          if (!isolatable(err)) throw err;
          skippedRows = await isolate(rows, err);
        }
      }
      if (skippedRows === null) for (const _ of rows) items.succeed();
      else {
        errors += skippedRows;
        // The skipped rows are recorded before the checkpoint moves past them.
        await saveLedger();
      }

      // Advance the checkpoint to the highest id we examined this batch.
      const lastBatchId = (rows[rows.length - 1] as Record<string, unknown>)[idCol];
      if (typeof lastBatchId === 'number') lastId = lastBatchId;
      if (!opts.dryRun) await setCheckpoint(engine, spec.name, lastId);

      opts.onBatch?.({
        batch: batchNum,
        rowsThisBatch: rows.length,
        cumulative: examined,
        lastId,
        errorsSeen: errors,
        effectiveBatchSize: effective,
      });
      if (errors >= maxErrors) break;
    }

    return await finish({ cappedByMaxRows: false, cappedByErrors: errors >= maxErrors });
  } catch (err) {
    // A systemic run records nothing: the skip record and the checkpoint go back to what the run began with.
    if (!opts.dryRun && err instanceof OperationError && err.code === 'item_failures_systemic') {
      await restoreSkipLedger(engine, spec.name, startLedger);
      if (startCheckpoint > 0) await setCheckpoint(engine, spec.name, startCheckpoint);
      else await clearBackfillCheckpoint(engine, spec.name);
    }
    throw err;
  }
}

/**
 * Clear the checkpoint for a backfill. Used by --fresh + after manual reset.
 */
export async function clearBackfillCheckpoint(engine: BrainEngine, name: string): Promise<void> {
  try {
    await engine.executeRaw(`DELETE FROM config WHERE key = $1`, [checkpointKey(name)]);
  } catch {
    /* best-effort */
  }
}