Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
12.6 KB
Raw Blame
/**
 * #5686 upgrade: re-key connector checkpoints through retained receipts, and
 * clean up checkpoint rows no source can load.
 *
 * Old checkpoint keys hashed the raw `sources.config`, which the cycle stamp
 * rewrote after every run, so they cannot be recomputed. The newest committed
 * checkpoint receipt per source incarnation still names the key it saved, so
 * its row is copied to the stable key (`INSERT … ON CONFLICT DO NOTHING`,
 * never overwriting a newer row). A source whose newest receipt was compacted
 * or is missing re-walks its window once; it never falls back to an older
 * receipt. Every statement runs on its own (transaction-mode PgBouncer), ages
 * use the database clock, and a rerun changes nothing.
 *
 * Orphans: `managed-connector` rows no source can load, and retry pointers
 * whose checkpoint no source loads, are excluded from the 7-day checkpoint
 * purge, so every cycle since the stamp was added left one behind. Cleanup
 * keeps rows touched after it started and rows referenced by a non-terminal
 * request or a recorded pending set. `gbrain repair connector-checkpoints` and
 * the doctor count reuse the same predicate with a 7-day age floor, because an
 * older binary may still be using an old key during a mixed-version window.
 */
import type { BrainEngine } from '../engine.ts';
import { CONNECTOR_KINDS_SQL, connectorCheckpointKeys, connectorIdentity, type ConnectorKind } from './connector-identity.ts';
import { CONNECTOR_STATE_OP, connectorStateKey, emptyConnectorState, type ConnectorState } from './connector-state.ts';

type Sql = Pick<BrainEngine, 'executeRaw' | 'transaction' | 'kind'>;
/** The database time migration 176 first ran; classifies retired-format connector intents. */
export const CONNECTOR_MIGRATION_OP = 'managed-connector-migration';
export async function readConnectorV2Cutoff(engine: Pick<BrainEngine, 'executeRaw'>): Promise<string | null> {
  const [row] = await engine.executeRaw<{ cutoff: string | null }>("SELECT completed_keys->0->>'cutoff' AS cutoff FROM op_checkpoints WHERE op=$1 AND fingerprint='v2-cutoff'", [CONNECTOR_MIGRATION_OP]);
  return row?.cutoff ?? null;
}

interface SourceRow { id: string; incarnation: string; local_path: string | null; config: Record<string, unknown> | string | null; archived: boolean }

export interface LoadableConnectorKey { id: string; incarnation: string; archived: boolean; alias: boolean }

/**
 * Checkpoint keys of every registered connector source (archived ones included: a restore must still find its
 * cursor): each source's stable key, plus its alias keys (`alias: true`, #6561) so rows an earlier release wrote
 * under them are never orphans while the source exists.
 */
export async function loadableConnectorKeys(engine: Pick<BrainEngine, 'executeRaw'>): Promise<Map<string, LoadableConnectorKey>> {
  const rows = await engine.executeRaw<SourceRow>(`SELECT id,incarnation::text,local_path,config,archived FROM sources WHERE config->>'kind' IN (${CONNECTOR_KINDS_SQL})`);
  const keys = new Map<string, LoadableConnectorKey>();
  for (const row of rows) {
    const config = typeof row.config === 'string' ? JSON.parse(row.config) as Record<string, unknown> : row.config ?? {};
    const [stable, ...aliases] = connectorCheckpointKeys(row.id, row.incarnation, connectorIdentity(config.kind as ConnectorKind, config, row.local_path));
    const source = { id: row.id, incarnation: row.incarnation, archived: row.archived === true };
    keys.set(stable!, { ...source, alias: false });
    for (const alias of aliases) keys.set(alias, { ...source, alias: true });
  }
  return keys;
}

export interface OrphanCheckpointRow { op: 'managed-connector' | 'managed-connector-retry'; fingerprint: string; updated_at: string }

/** The orphan test for op_checkpoints row `c`; binds loadable keys, before, min age days and the state op from parameter `$first` on. */
const orphanPredicate = (first: number) => {
  const [k, b, d, o] = [first, first + 1, first + 2, first + 3].map(n => `$${n}`);
  return `(
      ((c.op='managed-connector' AND NOT (c.fingerprint = ANY(${k}::text[])))
        OR (c.op='managed-connector-retry' AND NOT (COALESCE(c.completed_keys->0->>'checkpointKey','') = ANY(${k}::text[]))))
      AND NOT EXISTS (SELECT 1 FROM persistence_requests r
        WHERE (r.state IN ('queued','deferred','running','recovering') OR r.recovery IS NOT NULL)
          AND (r.intent->>'kind' LIKE 'connector\\_v2\\_%' OR r.intent->>'kind' LIKE 'managed\\_connector\\_%')
          AND (r.intent->>'checkpointKey' = c.fingerprint OR r.intent->>'checkpointKey' = c.completed_keys->0->>'checkpointKey'
            OR r.request_id::text = c.completed_keys->0->>'requestId'))
      AND NOT EXISTS (SELECT 1 FROM op_checkpoints s
        CROSS JOIN LATERAL jsonb_array_elements(COALESCE(s.completed_keys->0->'pending','[]'::jsonb)) p
        JOIN persistence_requests r ON r.request_id::text = p->>'requestId'
        WHERE s.op=${o} AND (r.intent->>'checkpointKey' = c.fingerprint OR r.intent->>'checkpointKey' = c.completed_keys->0->>'checkpointKey'
          OR r.request_id::text = c.completed_keys->0->>'requestId'))
      AND (${b}::text IS NULL OR c.updated_at <= ${b}::text::timestamptz)
      AND (${d}::integer IS NULL OR c.updated_at < now() - make_interval(days => ${d}::integer)))`;
};

/**
 * Checkpoint and retry rows no source can load. `before` bounds `updated_at`
 * (the migration start); `minAgeDays` adds the doctor/repair age floor.
 */
export async function orphanConnectorCheckpoints(engine: Pick<BrainEngine, 'executeRaw'>, opts: { before?: string; minAgeDays?: number; limit?: number } = {}): Promise<OrphanCheckpointRow[]> {
  const loadable = [...(await loadableConnectorKeys(engine)).keys()];
  return engine.executeRaw<OrphanCheckpointRow>(`SELECT c.op, c.fingerprint, c.updated_at::text AS updated_at FROM op_checkpoints c
    WHERE c.op IN ('managed-connector','managed-connector-retry') AND ${orphanPredicate(2)}
    ORDER BY c.op, c.fingerprint LIMIT $1`, [opts.limit ?? 100_000, loadable, opts.before ?? null, opts.minAgeDays ?? null, CONNECTOR_STATE_OP]);
}

/** Checkpoint and retry rows kept only because their key is a source's #6561 alias key (the repair preview lists them). */
export async function aliasProtectedConnectorCheckpoints(engine: Pick<BrainEngine, 'executeRaw'>): Promise<number> {
  const aliases = [...(await loadableConnectorKeys(engine))].filter(([, source]) => source.alias).map(([key]) => key);
  if (!aliases.length) return 0;
  const [row] = await engine.executeRaw<{ n: number | string }>(`SELECT count(*)::int AS n FROM op_checkpoints c
    WHERE (c.op='managed-connector' AND c.fingerprint = ANY($1::text[]))
       OR (c.op='managed-connector-retry' AND c.completed_keys->0->>'checkpointKey' = ANY($1::text[]))`, [aliases]);
  return Number(row?.n ?? 0);
}

/**
 * Deletes the given orphans. Each DELETE re-applies the whole orphan predicate
 * in the same statement, so a row loaded, touched or newly referenced since
 * planning survives.
 */
export async function deleteOrphanConnectorCheckpoints(engine: Pick<BrainEngine, 'executeRaw' | 'transaction'>, rows: OrphanCheckpointRow[], opts: { before?: string; minAgeDays?: number } = {}): Promise<{ checkpoints: number; retries: number }> {
  const removed = { checkpoints: 0, retries: 0 };
  if (!rows.length) return removed;
  // Connector source rows stay share-locked while loadability is recomputed and rows are deleted, so a config change cannot make a deleted key current.
  await engine.transaction(async tx => {
    await tx.executeRaw(`SELECT id FROM sources WHERE config->>'kind' IN (${CONNECTOR_KINDS_SQL}) ORDER BY id FOR SHARE`);
    const loadable = [...(await loadableConnectorKeys(tx)).keys()];
    for (const row of rows) {
      const deleted = await tx.executeRaw<{ op: string }>(`DELETE FROM op_checkpoints c WHERE c.op=$1 AND c.fingerprint=$2 AND c.updated_at=$3::text::timestamptz
        AND ${orphanPredicate(4)} RETURNING c.op`, [row.op, row.fingerprint, row.updated_at, loadable, opts.before ?? null, opts.minAgeDays ?? null, CONNECTOR_STATE_OP]);
      if (!deleted.length) continue;
      if (row.op === 'managed-connector') removed.checkpoints++; else removed.retries++;
    }
  });
  return removed;
}

export interface ConnectorMigrationReport {
  rekeyed: Array<{ source_id: string; resumed_from: string }>;
  rewalking: string[];
  removed: { checkpoints: number; retries: number };
}

export async function migrateConnectorCheckpoints(engine: Sql, log: (line: string) => void = line => process.stderr.write(`${line}\n`)): Promise<ConnectorMigrationReport> {
  const [clock] = await engine.executeRaw<{ now: string }>('SELECT now()::text AS now');
  const start = clock.now;
  await engine.executeRaw("INSERT INTO op_checkpoints(op,fingerprint,completed_keys) VALUES($1,'v2-cutoff',$2::text::jsonb) ON CONFLICT (op,fingerprint) DO NOTHING",
    [CONNECTOR_MIGRATION_OP, JSON.stringify([{ cutoff: start }])]);
  // One statement, bounded by its own timeout; no long transaction around the scan.
  const receipts = await engine.transaction(async tx => {
    await tx.executeRaw("SELECT set_config('statement_timeout','120s',true)");
    return tx.executeRaw<{ source_id: string; source_incarnation: string; kind: string | null; checkpoint_key: string | null; compacted: boolean; committed_at: string }>(`
      SELECT DISTINCT ON (source_id, source_incarnation) source_id, source_incarnation::text, intent->>'kind' AS kind, intent->>'checkpointKey' AS checkpoint_key,
             COALESCE(compacted,false) AS compacted, COALESCE(completed_at,updated_at)::text AS committed_at
        FROM persistence_requests
       -- Compaction nulls the intent but keeps the slug, so a compacted newest receipt is still found (and re-walks).
       WHERE state='committed' AND slug='__managed_connector_checkpoint__' AND operation='submit_job'
       ORDER BY source_id, source_incarnation, sequence DESC`);
  });
  const newest = new Map(receipts.map(row => [`${row.source_id}\u0000${row.source_incarnation}`, row]));
  const report: ConnectorMigrationReport = { rekeyed: [], rewalking: [], removed: { checkpoints: 0, retries: 0 } };
  // Archived sources are migrated too: archive and restore keep the incarnation, so a restore resumes its cursor.
  for (const [key, source] of await loadableConnectorKeys(engine)) {
    if (source.alias) continue;
    const receipt = newest.get(`${source.id}\u0000${source.incarnation}`);
    // No managed checkpoint yet, or already saved by this release under its stable key.
    if (!receipt || receipt.kind === 'connector_v2_checkpoint') continue;
    let recovery: Pick<ConnectorState, 'upgrade_recovery' | 'resumed_from'> = { upgrade_recovery: 'rewalking_once', resumed_from: null };
    if (!receipt.compacted && receipt.checkpoint_key) {
      await engine.executeRaw(`INSERT INTO op_checkpoints(op,fingerprint,completed_keys)
        SELECT 'managed-connector',$1,completed_keys FROM op_checkpoints WHERE op='managed-connector' AND fingerprint=$2
        ON CONFLICT (op,fingerprint) DO NOTHING`, [key, receipt.checkpoint_key]);
      const [copied] = await engine.executeRaw<{ fingerprint: string }>("SELECT fingerprint FROM op_checkpoints WHERE op='managed-connector' AND fingerprint=$1", [key]);
      if (copied) recovery = { upgrade_recovery: 'resumed', resumed_from: receipt.committed_at };
    }
    const seeded: ConnectorState = { ...emptyConnectorState(), ...recovery };
    const inserted = await engine.executeRaw<{ op: string }>(`INSERT INTO op_checkpoints(op,fingerprint,completed_keys) VALUES($1,$2,$3::text::jsonb)
      ON CONFLICT (op,fingerprint) DO NOTHING RETURNING op`, [CONNECTOR_STATE_OP, connectorStateKey(source.id, source.incarnation), JSON.stringify([seeded])]);
    if (!inserted.length) continue;
    if (recovery.upgrade_recovery === 'resumed') {
      report.rekeyed.push({ source_id: source.id, resumed_from: recovery.resumed_from! });
      log(`  ${source.id}: resumed from pre-upgrade checkpoint of ${recovery.resumed_from}`);
      log(`    content selection since then is unverified; to re-walk: gbrain sync --source ${source.id} --reset-checkpoint`);
    } else report.rewalking.push(source.id);
  }
  report.removed = await deleteOrphanConnectorCheckpoints(engine, await orphanConnectorCheckpoints(engine, { before: start }), { before: start });
  if (report.rekeyed.length || report.rewalking.length || report.removed.checkpoints || report.removed.retries) {
    log(`  connector checkpoints: ${report.rekeyed.length} source(s) re-keyed, ${report.rewalking.length} will re-walk once `
      + `(the first post-upgrade admission spike is expected and is not #5470 churn); removed ${report.removed.checkpoints} orphan checkpoint row(s) `
      + `and ${report.removed.retries} orphan retry pointer(s).`);
  }
  return report;
}