Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
31.5 KB
Raw Blame
/**
 * `gbrain trust backfill` (#5575: A8, CEO-17, DX-5): classifies rows written
 * before trust tiers existed, from deterministic signals only. Rows with no
 * signal stay `unknown`; content_chunks are never touched (chunk tier is its
 * page's at read time); page_versions keep `unknown` (ENG-10).
 *
 * Signals, combined as the minimum (least trusted) of every one that applies:
 * - the row's source is a connector source (`sources.config.kind` google or
 *   github): external_untrusted (connector free text is attacker-controllable;
 *   legacy rows cannot tell structured fields apart);
 * - pages.source_kind: webhook / file-watcher / inbox-folder / cron-scheduler
 *   (ingestion captures) external_untrusted; mcp:* / put_page / capture-cli
 *   agent_written;
 * - frontmatter transcript_import, provenance: auto-extracted, dream_generated,
 *   extracted_by: extract_atoms-* (legacy atoms, #6533): agent_written (model-
 *   or session-derived);
 * - on a claimed source, a Life Chronicle event (`captured_via`
 *   life-chronicle:auto, #9284) is also capped at the tier of its depth page
 *   (`event.depth`, same source): that page's stored tier and its own
 *   frontmatter caps;
 * - facts.source lane tags (the facts backstop, conversation-facts and
 *   extract_facts lanes) and takes.source take_proposals#: agent_written;
 * - post-v193 rows through write_request_id -> persistence_requests (CEO-17):
 *   connector jobs external_untrusted, remote callers agent_written, managed
 *   sync/import/reconcile/file-repair of an owner source operator_curated,
 *   plain local put_page/capture/remember/takes/timeline writes and model
 *   maintenance agent_written, anything else no signal;
 * - facts on a page's fence, takes and timeline rows also take their page's
 *   tier when the page has one (taint from the page they live on).
 *
 * The highest tier the backfill can assign is operator_curated; it never
 * assigns user_confirmed (CEO-10, enforced again by the tier trigger).
 *
 * `dryRun` is strictly read-only: SELECTs inside a READ ONLY transaction, no
 * checkpoint, no config. It works on a brain whose schema predates the trust
 * columns (or attribution, or provenance columns) by classifying from the
 * columns that exist, and reports which signal families were available.
 * Applying runs in bounded batches (resumable cursor in op_checkpoints, the
 * resumePageRevisionBackfill precedent) under the backfill setting, inside
 * withCoordinatedWrite for each batch's sources on a managed brain.
 */
import { createHash } from 'node:crypto';
import type { BrainEngine } from '../engine.ts';
import { FACTS_BACKSTOP_SOURCES } from '../facts/capture-sources.ts';
import { opError } from '../ops/contract.ts';
import { CONNECTOR_SOURCE_KINDS } from '../persistence/connector-identity.ts';
import { withCoordinatedWrite, withTrustBackfill } from '../persistence/context.ts';
import { propagateTierLowering } from './propagation.ts';
import { appendBulkRetier } from '../belief/retier.ts';
import { markBeliefCommits } from '../belief/append.ts';
import { maintenanceAttribution } from '../persistence/attribution.ts';
import { readManagedMode } from '../persistence/managed-mode.ts';
import { edgeTrimSql } from '../edge-whitespace.ts';
import { CHRONICLE_CAPTURED_VIA, EXTRACT_ATOMS_EXTRACTOR_PREFIX, frontmatterTrustCapsSql } from './channel.ts';
import { claimedSourceSql, legacyRowSql, notConnectorSourceSql, readLegacyCeilings, type LegacyCeilings } from './claim-state.ts';
import { TRUST_BACKFILL_COMPLETED_KEY, type TrustTable } from './schema.ts';
import { TRUST_TIER_RANK, TRUST_TIERS, isTrustTier, trustRankSql, trustTierFromRankSql, type TrustTier } from './tier.ts';

export { TRUST_BACKFILL_COMPLETED_KEY };
export const TRUST_BACKFILL_COMMAND = 'gbrain trust backfill --resume';
const CHECKPOINT_OP = 'trust-backfill';
/** The claim lift's own cursor, so it never resumes (or clears) a full backfill's. */
const CLAIM_CHECKPOINT_OP = 'trust-claim';
const CHECKPOINT_KEY = 'v1';
const DEFAULT_BATCH = 2000;
/** Pages first: facts, takes and timeline rows take their page's classified tier. */
const BACKFILL_ORDER: readonly TrustTable[] = ['pages', 'facts', 'takes', 'timeline_entries'];

export interface TrustBackfillOptions {
  dryRun?: boolean;
  resume?: boolean;
  batchSize?: number;
  log?: (line: string) => void;
  /**
   * Only rows of these sources (the claim lift, trust/claim.ts): its own
   * resumable cursor, `write_origin.channel` `trust_claim`, and no global
   * backfill completion stamp.
   */
  sources?: readonly string[];
  /** Dry run only: project these sources as if claimed. */
  assumeClaimed?: readonly string[];
}
export type TierCounts = Record<TrustTier, number>;
export interface TrustBackfillTableReport {
  table: TrustTable;
  rows: number;
  /** Stored tiers now (absent on a brain without the trust columns). */
  current?: TierCounts;
  /** Tiers after a backfill: stored non-unknown tiers kept, unknown rows classified. */
  projected: TierCounts;
  /** Apply only: rows this run moved off `unknown`. */
  updated?: number;
}
export interface TrustBackfillReport {
  mode: 'dry_run' | 'apply';
  schema: 'trust_columns' | 'pre_trust';
  status: 'complete' | 'partial';
  tables: TrustBackfillTableReport[];
  rows: number;
  projected: TierCounts;
  /** Rows at agent_written or below (agent_written, unknown, external_untrusted): the CEO-25 review-burden gauge. */
  at_or_below_agent_written: number;
  at_or_below_agent_written_pct: number;
  unknown_pct: number;
  /** Signal families the schema could read. */
  signals: string[];
  resume_command?: string;
}

const zero = (): TierCounts => Object.fromEntries(TRUST_TIERS.map(tier => [tier, 0])) as TierCounts;
const pct = (n: number, d: number) => (d === 0 ? 0 : Math.round((n / d) * 10_000) / 100);
const quoted = (values: readonly string[]) => values.map(v => `'${v.replace(/'/g, "''")}'`).join(',');
const rank = (tier: TrustTier) => TRUST_TIER_RANK[tier];

type Columns = { has(table: string, column: string): boolean; table(table: string): boolean };

async function readColumns(engine: BrainEngine): Promise<Columns> {
  const rows = await engine.executeRaw<{ table_name: string; column_name: string }>(
    `SELECT table_name, column_name FROM information_schema.columns
      WHERE table_schema = current_schema() AND table_name = ANY($1::text[])`,
    [['pages', 'facts', 'takes', 'timeline_entries', 'sources', 'persistence_requests']]);
  const set = new Set(rows.map(r => `${r.table_name}.${r.column_name}`));
  const tables = new Set(rows.map(r => r.table_name));
  return { has: (table, column) => set.has(`${table}.${column}`), table: name => tables.has(name) };
}

/** Builds the rank expressions for one schema shape. Every term yields a rank or NULL (no signal). */
function classifier(cols: Columns, assumeClaimed: readonly string[] = [], ceilings: LegacyCeilings = null, relower = false) {
  const signals = new Set<string>();
  /** The source aliased `s` is claimed (or projected as claimed): its rows with no lowering signal are operator_curated. */
  const claimed = (s: string) => {
    if (!cols.has('sources', 'config')) return null;
    signals.add('claimed_source');
    return assumeClaimed.length
      ? `(${claimedSourceSql(s)} OR (${s}.id IN (${quoted(assumeClaimed)}) AND ${notConnectorSourceSql(s)}))`
      : claimedSourceSql(s);
  };
  /** A claim lifts only legacy rows (claim-state.ts): `row` is the classified row's alias in `table`. */
  const claimTerm = (s: string, table: TrustTable, row: string) => {
    const c = claimed(s);
    return c ? `CASE WHEN ${c} AND ${legacyRowSql(table, row, ceilings)} THEN ${rank('operator_curated')} END` : null;
  };
  const connector = (sourceAlias: string) => {
    if (!cols.has('sources', 'config')) return null;
    signals.add('connector_source');
    return `CASE WHEN ${sourceAlias}.config->>'kind' IN (${quoted(CONNECTOR_SOURCE_KINDS)}) THEN ${rank('external_untrusted')} END`;
  };
  const requests = cols.has('persistence_requests', 'operation') && cols.has('persistence_requests', 'intent') && cols.has('persistence_requests', 'authority');
  const request = (r: string) => {
    if (!requests) return null;
    signals.add('write_request');
    const kind = `${r}.intent->>'kind'`;
    return `CASE WHEN ${r}.id IS NULL THEN NULL
      WHEN ${kind} LIKE 'connector\\_v2\\_%' OR ${kind} LIKE 'managed\\_connector\\_%' THEN ${rank('external_untrusted')}
      WHEN ${r}.authority->>'remote' = 'true' THEN ${rank('agent_written')}
      WHEN ${kind} LIKE 'managed\\_sync\\_%' OR ${kind} IN ('managed_file_import','canonical_reconcile','managed_file_repair') THEN ${rank('operator_curated')}
      WHEN ${kind} LIKE 'managed\\_facts\\_%' OR ${kind} LIKE 'managed\\_atom\\_%' OR ${kind} LIKE 'managed\\_maintenance\\_%' THEN ${rank('agent_written')}
      WHEN ${r}.intent IS NOT NULL AND ${kind} IS NULL AND ${r}.operation IN ('put_page','capture','remember','edit_page','append_page','delete_page','restore_page',
        'revert_version','add_tag','remove_tag','add_timeline_entry','add_link','remove_link','takes_add','takes_update','takes_supersede','takes_resolve','takes_remove') THEN ${rank('agent_written')}
      END`;
  };
  const requestJoin = (table: string, column: string, alias: string, row: string) =>
    requests && cols.has(table, column) ? `LEFT JOIN persistence_requests ${alias} ON ${alias}.id = ${row}.${column}` : '';
  const pageTerms = (p: string, s: string, r: string): string[] => {
    const terms: string[] = [];
    const c = connector(s); if (c) terms.push(c);
    if (cols.has('pages', 'source_kind')) {
      signals.add('pages_source_kind');
      terms.push(`CASE WHEN ${p}.source_kind IN ('webhook','file-watcher','inbox-folder','cron-scheduler') THEN ${rank('external_untrusted')}
        WHEN ${p}.source_kind LIKE 'mcp:%' OR ${p}.source_kind IN ('put_page','capture-cli') THEN ${rank('agent_written')} END`);
    }
    if (cols.has('pages', 'frontmatter')) {
      signals.add('frontmatter_provenance');
      terms.push(`CASE WHEN ${p}.frontmatter ? 'transcript_import' OR ${p}.frontmatter->>'provenance' = 'auto-extracted'
        OR ${p}.frontmatter->>'dream_generated' = 'true'
        OR (CASE WHEN jsonb_typeof(${p}.frontmatter->'extracted_by') = 'string' THEN ${edgeTrimSql(`${p}.frontmatter->>'extracted_by'`)} END)
          LIKE '${EXTRACT_ATOMS_EXTRACTOR_PREFIX.replace(/_/g, '\\_')}%' THEN ${rank('agent_written')} END`);
    }
    if (cols.has('pages', 'revision_write_request_id')) { const q = request(r); if (q) terms.push(q); }
    return terms;
  };
  const least = (terms: string[]) => (terms.length ? `LEAST(${terms.join(', ')})` : 'NULL::int');
  /**
   * #9284: a legacy Life Chronicle event is model-extracted from its depth page (`event.depth`, a live page in the
   * same source), so it ranks no higher than that page: its stored tier and frontmatter caps when classified, else
   * what the same statement classifies it as (its frontmatter caps and page signals: source kind, connector source,
   * write request), since an UPDATE reads the depth page as it was before. A missing depth page adds no cap (the
   * event's own agent_written cap still applies).
   */
  const chronicleDepthCap = (p: string, s: string) => {
    const d = 'chronicle_depth', dr = 'chronicle_depth_r';
    const depthCaps = least(frontmatterTrustCapsSql(`${d}.frontmatter`));
    const projected = least([...frontmatterTrustCapsSql(`${d}.frontmatter`), ...pageTerms(d, s, dr)]);
    return `CASE WHEN ${p}.type = 'event' AND jsonb_typeof(${p}.frontmatter->'captured_via') = 'string'
        AND ${edgeTrimSql(`${p}.frontmatter->>'captured_via'`)} = '${CHRONICLE_CAPTURED_VIA}'
        AND jsonb_typeof(${p}.frontmatter->'event') = 'object' AND jsonb_typeof(${p}.frontmatter->'event'->'depth') = 'string'
      THEN (SELECT ${cols.has('pages', 'trust_tier') ? `CASE WHEN ${d}.trust_tier <> 'unknown' THEN LEAST(${trustRankSql(`${d}.trust_tier`)}, ${depthCaps}) ELSE ${projected} END` : projected}
        FROM pages ${d} ${requestJoin('pages', 'revision_write_request_id', dr, d)} WHERE ${d}.source_id = ${p}.source_id AND ${d}.slug = ${p}.frontmatter->'event'->>'depth'${cols.has('pages', 'deleted_at') ? ` AND ${d}.deleted_at IS NULL` : ''}
        LIMIT 1) END`;
  };
  /**
   * A page's rule: its signals, and on a claimed source operator_curated lowered by every frontmatter signal
   * an owner sync would apply (`frontmatterTrustCaps`), so a claim never lifts a captured, clipped, imported
   * or marked page above what it says it is, nor a Life Chronicle event above its depth page.
   */
  const pageRule = (p: string, s: string, r: string) => {
    const terms = pageTerms(p, s, r);
    const c = claimed(s);
    if (!c) return least(terms);
    const caps = cols.has('pages', 'frontmatter') ? [...frontmatterTrustCapsSql(`${p}.frontmatter`), chronicleDepthCap(p, s)] : [];
    return `CASE WHEN ${c} AND ${legacyRowSql('pages', p, ceilings)} THEN ${least([String(rank('operator_curated')), ...caps, ...terms])} ELSE ${least(terms)} END`;
  };
  const hasTrust = cols.has('pages', 'trust_tier');
  /**
   * The tier a row's page contributes: its stored tier when classified, else the page's own signals. In the
   * relower pass a page the claim lifted contributes the tier it is about to be lowered to, so the read-only
   * preview and the apply (which lowers pages first) report the same child rows (#6533).
   */
  const pageTaint = (p: string, s: string, r: string) => {
    const rule = pageRule(p, s, r);
    const stored = trustRankSql(`${p}.trust_tier`);
    const classified = relower ? `CASE WHEN ${p}.write_origin->>'channel' = 'trust_claim' THEN LEAST(${stored}, COALESCE(${rule}, ${stored})) ELSE ${stored} END` : stored;
    return hasTrust ? `CASE WHEN ${p}.id IS NULL THEN NULL WHEN ${p}.trust_tier <> 'unknown' THEN ${classified} ELSE ${rule} END` : rule;
  };
  const pageJoins = (p: string, s: string, r: string, on: string) =>
    `LEFT JOIN pages ${p} ON ${on} ${cols.has('sources', 'config') ? `LEFT JOIN sources ${s} ON ${s}.id = ${p}.source_id` : ''} ${requestJoin('pages', 'revision_write_request_id', r, p)}`;

  /** FROM clause and rule-rank expression per table; `x` is the row alias, `src` the expression naming its source. */
  const forTable = (table: TrustTable): { from: string; rule: string; src: string } => {
    if (table === 'pages') {
      return { from: `pages x ${cols.has('sources', 'config') ? 'LEFT JOIN sources s ON s.id = x.source_id' : ''} ${requestJoin('pages', 'revision_write_request_id', 'r', 'x')}`,
        rule: pageRule('x', 's', 'r'), src: 'x.source_id' };
    }
    if (table === 'facts') {
      const terms: string[] = [];
      const c = connector('s'); if (c) terms.push(c);
      const own = claimTerm('s', 'facts', 'x'); if (own) terms.push(own);
      if (cols.has('facts', 'source')) {
        signals.add('facts_source_lane');
        terms.push(`CASE WHEN x.source IN (${quoted(FACTS_BACKSTOP_SOURCES)}) OR x.source LIKE 'cli:extract-conversation-facts%' THEN ${rank('agent_written')} END`);
      }
      if (cols.has('facts', 'write_request_id')) { const q = request('r'); if (q) terms.push(q); }
      const fenced = cols.has('facts', 'source_markdown_slug');
      if (fenced) terms.push(pageTaint('pg', 'ps', 'pr'));
      return {
        from: `facts x ${cols.has('sources', 'config') ? 'LEFT JOIN sources s ON s.id = x.source_id' : ''} ${requestJoin('facts', 'write_request_id', 'r', 'x')}
          ${fenced ? pageJoins('pg', 'ps', 'pr', 'pg.source_id = x.source_id AND pg.slug = x.source_markdown_slug') : ''}`,
        rule: least(terms), src: 'x.source_id',
      };
    }
    const terms: string[] = [pageTaint('pg', 'ps', 'pr')];
    if (cols.has('sources', 'config')) terms.push(`CASE WHEN ps.config->>'kind' IN (${quoted(CONNECTOR_SOURCE_KINDS)}) THEN ${rank('external_untrusted')} END`);
    const own = claimTerm('ps', table, 'x'); if (own) terms.push(own);
    if (table === 'takes' && cols.has('takes', 'source')) {
      signals.add('takes_source');
      terms.push(`CASE WHEN x.source LIKE 'take\\_proposals#%' THEN ${rank('agent_written')} END`);
    }
    if (cols.has(table, 'write_request_id')) { const q = request('r'); if (q) terms.push(q); }
    return { from: `${table} x ${pageJoins('pg', 'ps', 'pr', 'pg.id = x.page_id')} ${requestJoin(table, 'write_request_id', 'r', 'x')}`,
      rule: least(terms), src: 'pg.source_id' };
  };
  return { forTable, signals, hasTrust };
}

async function readCursor(engine: BrainEngine, op = CHECKPOINT_OP, key = CHECKPOINT_KEY): Promise<Partial<Record<TrustTable, number>>> {
  const [row] = await engine.executeRaw<{ keys: unknown }>(
    'SELECT completed_keys AS keys FROM op_checkpoints WHERE op = $1 AND fingerprint = $2', [op, key]);
  const keys = typeof row?.keys === 'string' ? JSON.parse(row.keys) : row?.keys;
  return Array.isArray(keys) && keys[0] && typeof keys[0] === 'object' ? keys[0] as Partial<Record<TrustTable, number>> : {};
}
async function saveCursor(engine: BrainEngine, cursor: Partial<Record<TrustTable, number>>, op = CHECKPOINT_OP, key = CHECKPOINT_KEY): Promise<void> {
  await engine.executeRaw(`INSERT INTO op_checkpoints(op, fingerprint, completed_keys) VALUES ($1, $2, $3::text::jsonb)
    ON CONFLICT (op, fingerprint) DO UPDATE SET completed_keys = EXCLUDED.completed_keys, updated_at = now()`,
  [op, key, JSON.stringify([cursor])]);
}

/** A claim lift's cursor key: one per set of sources, so lifting another set never skips its rows. */
const claimCursorKey = (sources: readonly string[]) => createHash('sha256').update([...sources].sort().join('\n')).digest('hex').slice(0, 16);

function summarize(mode: TrustBackfillReport['mode'], schema: TrustBackfillReport['schema'], tables: TrustBackfillTableReport[],
  signals: Set<string>, status: TrustBackfillReport['status']): TrustBackfillReport {
  const projected = zero();
  for (const t of tables) for (const tier of TRUST_TIERS) projected[tier] += t.projected[tier];
  const rows = tables.reduce((n, t) => n + t.rows, 0);
  const low = projected.agent_written + projected.unknown + projected.external_untrusted;
  return {
    mode, schema, status, tables, rows, projected,
    at_or_below_agent_written: low, at_or_below_agent_written_pct: pct(low, rows), unknown_pct: pct(projected.unknown, rows),
    signals: [...signals].sort(),
    ...(status === 'partial' ? { resume_command: TRUST_BACKFILL_COMMAND } : {}),
  };
}

/** The read-only projection: counts by table x tier, in keyset batches inside one READ ONLY transaction. */
async function dryRun(engine: BrainEngine, batch: number, opts: Pick<TrustBackfillOptions, 'sources' | 'assumeClaimed'> = {}): Promise<TrustBackfillReport> {
  return engine.transaction(async tx => {
    await tx.executeRaw('SET TRANSACTION READ ONLY');
    const cols = await readColumns(tx);
    const ceilings = cols.has('sources', 'config') ? await readLegacyCeilings(tx).catch(() => null) : null;
    const { forTable, signals, hasTrust } = classifier(cols, opts.assumeClaimed, ceilings);
    const tables: TrustBackfillTableReport[] = [];
    for (const table of BACKFILL_ORDER) {
      if (!cols.table(table)) continue;
      const { from, rule, src } = forTable(table);
      const only = opts.sources ? ` AND ${src} IN (${quoted(opts.sources)})` : '';
      const ruleTier = trustTierFromRankSql(rule);
      const report: TrustBackfillTableReport = { table, rows: 0, ...(hasTrust ? { current: zero() } : {}), projected: zero() };
      for (let after = 0; ;) {
        const rows = await tx.executeRaw<{ last: number | string; n: number | string; current: string | null; tier: string }>(
          `SELECT max(id) AS last, count(*) AS n, current, tier FROM (
             SELECT x.id, ${hasTrust ? 'x.trust_tier' : 'NULL::text'} AS current,
                    ${hasTrust ? `CASE WHEN x.trust_tier <> 'unknown' THEN x.trust_tier ELSE ${ruleTier} END` : ruleTier} AS tier
               FROM ${from} WHERE x.id > $1${only} ORDER BY x.id LIMIT $2) b
           GROUP BY current, tier`, [after, batch]);
        if (rows.length === 0) break;
        for (const row of rows) {
          const n = Number(row.n);
          report.rows += n;
          report.projected[isTrustTier(row.tier) ? row.tier : 'unknown'] += n;
          if (report.current && isTrustTier(row.current)) report.current[row.current] += n;
          after = Math.max(after, Number(row.last));
        }
      }
      tables.push(report);
    }
    return summarize('dry_run', hasTrust ? 'trust_columns' : 'pre_trust', tables, signals, 'complete');
  });
}

export async function runTrustBackfill(engine: BrainEngine, opts: TrustBackfillOptions = {}): Promise<TrustBackfillReport> {
  const batch = opts.batchSize ?? DEFAULT_BATCH;
  if (opts.dryRun) return dryRun(engine, batch, opts);
  if (opts.sources && opts.sources.length === 0) return summarize('apply', 'trust_columns', [], new Set(), 'complete');
  const log = opts.log ?? ((line: string) => process.stderr.write(`${line}\n`));
  const cols = await readColumns(engine);
  if (!cols.has('pages', 'trust_tier')) {
    throw opError('migrations_pending', 'This brain has no trust_tier columns yet, so there is nothing to backfill.',
      'Apply the pending schema migrations on the brain host, then run the backfill. gbrain trust backfill --dry-run previews it without them.', {
        fix: { argv: ['gbrain', 'apply-migrations', '--yes'], consent: [], actor: 'agent', requires_exclusive: true,
          why: 'Applies the pending schema migrations, including the trust tier columns.', verify: { argv: ['gbrain', 'doctor', '--json'] } },
      });
  }
  const { forTable, signals } = classifier(cols, [], await readLegacyCeilings(engine).catch(() => null));
  const managedBrain = await readManagedMode(engine);
  const attribution = await maintenanceAttribution(engine);
  const scoped = opts.sources !== undefined;
  const op = scoped ? CLAIM_CHECKPOINT_OP : CHECKPOINT_OP;
  const key = scoped ? claimCursorKey(opts.sources!) : CHECKPOINT_KEY;
  const origin = JSON.stringify({ channel: scoped ? 'trust_claim' : 'trust_backfill' });
  const cursor = opts.resume || scoped ? await readCursor(engine, op, key) : {};
  const tables: TrustBackfillTableReport[] = [];
  for (const table of BACKFILL_ORDER) {
    const { from, rule, src } = forTable(table);
    let updated = 0;
    for (let after = cursor[table] ?? 0; ;) {
      const ids = (await engine.executeRaw<{ id: number | string }>(scoped
        ? `SELECT x.id FROM ${from} WHERE x.id > $1 AND x.trust_tier = 'unknown' AND ${src} = ANY($3::text[]) ORDER BY x.id LIMIT $2`
        : `SELECT id FROM ${table} WHERE id > $1 AND trust_tier = 'unknown' ORDER BY id LIMIT $2`, scoped ? [after, batch, [...opts.sources!]] : [after, batch])).map(r => Number(r.id));
      if (ids.length === 0) break;
      const sources = (await engine.executeRaw<{ source_id: string | null }>(
        `SELECT DISTINCT ${src} AS source_id FROM ${from} WHERE x.id = ANY($1::bigint[])`, [ids])).map(r => r.source_id).filter((s): s is string => !!s);
      const publications: string[] = [];
      // W1 #6783 §6.4: each batch's changed subjects are recorded as one summary trust.retiered per stream plus a manifest row each.
      const apply = (tx: BrainEngine) => withTrustBackfill(tx, async () => {
        const changed = await tx.executeRaw<{ id: number; tier: string }>(
          `WITH b AS (SELECT x.id, ${trustTierFromRankSql(rule)} AS tier FROM ${from} WHERE x.id = ANY($1::bigint[]))
           UPDATE ${table} t SET trust_tier = b.tier, write_origin = COALESCE(t.write_origin, $2::text::jsonb)
             FROM b WHERE t.id = b.id AND t.trust_tier = 'unknown' AND b.tier <> 'unknown' RETURNING t.id, b.tier`, [ids, origin]);
        const publication = await appendBulkRetier(tx, changed.map(r => ({ table, id: Number(r.id), from: 'unknown', to: r.tier })), scoped ? 'trust_claim' : 'trust_backfill');
        if (publication) publications.push(publication);
        return changed.length;
      });
      updated += await engine.transaction(tx => managedBrain && sources.length
        ? withCoordinatedWrite(tx, sources, () => apply(tx), attribution)
        : apply(tx));
      if (publications.length) await markBeliefCommits(engine, publications).catch(() => undefined);
      after = ids[ids.length - 1]!;
      cursor[table] = after;
      await saveCursor(engine, cursor, op, key);
      log(`[trust ${scoped ? 'claim' : 'backfill'}] ${table}: ${updated} row(s) classified, through id ${after}`);
    }
    tables.push({ table, rows: 0, projected: zero(), updated });
  }
  await engine.executeRaw('DELETE FROM op_checkpoints WHERE op = $1 AND fingerprint = $2', [op, key]);
  if (scoped) return summarize('apply', 'trust_columns', tables, signals, 'complete');
  await engine.setConfig(TRUST_BACKFILL_COMPLETED_KEY, new Date().toISOString());
  const counts = await readTrustTierCounts(engine);
  for (const t of tables) {
    const c = counts.find(row => row.table === t.table);
    if (c) { t.rows = c.rows; t.projected = c.counts; t.current = c.counts; }
  }
  return summarize('apply', 'trust_columns', tables, signals, 'complete');
}

export const TRUST_RELOWER_COMMAND = ['gbrain', 'trust', 'claim-sources', '--relower'] as const;

export interface TrustRelowerReport {
  mode: 'dry_run' | 'apply';
  /** Rows the claim lifted that their own signals now place lower, per table and target tier. */
  tables: Array<{ table: TrustTable; rows: number; to: TierCounts }>;
  rows: number;
  /** Up to 20 affected pages, for the preview. */
  sample: Array<{ source_id: string; slug: string; from: TrustTier; to: TrustTier }>;
  /** Sources holding at least one of those rows. */
  sources: string[];
}

/**
 * #6533 repair, lower only: rows a claim lifted (`write_origin.channel` `trust_claim`) whose own page rule
 * now ranks below their stored tier (legacy extract_atoms atoms the claim made "your notes" before
 * `extracted_by` was a signal) are lowered to that rule, pages first so facts, takes and timeline rows on a
 * lowered page follow it. Each lowered row's `write_origin.channel` becomes `trust_relower`, so a rerun finds
 * nothing. Nothing is ever raised, and rows a person or a later write classified are not touched. The dry
 * run is read-only and reports the same rows the apply lowers. #9284: named `sources` also cover a
 * non-connector source whose claim `sources set-trust` ended, classified as if still claimed, so the rows that
 * claim lifted can still be lowered; without `sources` only claimed sources are considered.
 */
export async function relowerClaimedRows(engine: BrainEngine, opts: { dryRun?: boolean; sources?: readonly string[] } = {}): Promise<TrustRelowerReport> {
  const cols = await readColumns(engine);
  const mode: TrustRelowerReport['mode'] = opts.dryRun ? 'dry_run' : 'apply';
  if (!cols.has('pages', 'trust_tier') || !cols.has('pages', 'write_origin') || !cols.has('sources', 'config')) return { mode, tables: [], rows: 0, sample: [], sources: [] };
  const targets = opts.sources
    ? await engine.executeRaw<{ id: string; claimed: boolean }>(`SELECT s.id, ${claimedSourceSql('s')} AS claimed FROM sources s
        WHERE s.id = ANY($1::text[]) AND ${notConnectorSourceSql('s')} ORDER BY s.id`, [[...opts.sources]])
    : await engine.executeRaw<{ id: string; claimed: boolean }>(`SELECT s.id, true AS claimed FROM sources s WHERE ${claimedSourceSql('s')} ORDER BY s.id`);
  const claimed = targets.map(r => r.id);
  const ended = targets.filter(r => !r.claimed).map(r => r.id);
  const { forTable } = classifier(cols, ended, await readLegacyCeilings(engine).catch(() => null), true);
  const report: TrustRelowerReport = { mode, tables: [], rows: 0, sample: [], sources: [] };
  const affected = new Set<string>();
  if (claimed.length === 0) return report;
  const managedBrain = opts.dryRun ? false : await readManagedMode(engine);
  const attribution = opts.dryRun ? null : await maintenanceAttribution(engine);
  const origin = JSON.stringify({ channel: 'trust_relower' });
  const lowered: Array<{ table: TrustTable; id: number }> = [];
  for (const table of BACKFILL_ORDER) {
    if (!cols.table(table) || !cols.has(table, 'write_origin')) continue;
    const { from, rule, src } = forTable(table);
    const candidates = `SELECT x.id, ${src} AS source_id, ${table === 'pages' ? 'x.slug' : 'NULL::text'} AS slug, x.trust_tier AS from_tier,
        ${trustTierFromRankSql(rule)} AS to_tier
      FROM ${from}
     WHERE x.write_origin->>'channel' = 'trust_claim' AND ${src} = ANY($1::text[])
       AND (${rule}) IS NOT NULL AND (${rule}) < ${trustRankSql('x.trust_tier')}`;
    const rows = await engine.executeRaw<{ id: number | string; source_id: string; slug: string | null; from_tier: string; to_tier: string }>(
      `${candidates} ORDER BY x.id`, [claimed]);
    const to = zero();
    for (const row of rows) {
      const target = isTrustTier(row.to_tier) ? row.to_tier : 'unknown';
      to[target]++;
      affected.add(row.source_id);
      if (row.slug !== null && report.sample.length < 20 && isTrustTier(row.from_tier)) {
        report.sample.push({ source_id: row.source_id, slug: row.slug, from: row.from_tier, to: target });
      }
    }
    report.tables.push({ table, rows: rows.length, to });
    report.rows += rows.length;
    if (opts.dryRun || rows.length === 0) continue;
    const ids = rows.map(r => Number(r.id));
    const sources = [...new Set(rows.map(r => r.source_id))];
    const publications: string[] = [];
    const apply = async (tx: BrainEngine) => {
      const changed = await withTrustBackfill(tx, () => tx.executeRaw<{ id: number; from_tier: string; to_tier: string }>(
        `WITH b AS (${candidates} AND x.id = ANY($2::bigint[]))
         UPDATE ${table} t SET trust_tier = b.to_tier, write_origin = $3::text::jsonb FROM b WHERE t.id = b.id RETURNING t.id, b.from_tier, b.to_tier`, [claimed, ids, origin]));
      const publication = await appendBulkRetier(tx, changed.map(r => ({ table, id: Number(r.id), from: r.from_tier, to: r.to_tier })), 'trust_relower');
      if (publication) publications.push(publication);
      return changed.map(row => ({ table, id: Number(row.id) }));
    };
    lowered.push(...await engine.transaction(tx => managedBrain ? withCoordinatedWrite(tx, sources, () => apply(tx), attribution!) : apply(tx)));
    if (publications.length) await markBeliefCommits(engine, publications).catch(() => undefined);
  }
  report.sources = [...affected].sort();
  // W1 #6783 §12 (b): after every table is relowered (the dry run reports exactly these rows), what was derived from them follows.
  if (lowered.length) await engine.transaction(tx => managedBrain
    ? withCoordinatedWrite(tx, claimed, () => propagateTierLowering(tx, lowered), attribution!) : propagateTierLowering(tx, lowered));
  return report;
}

/** Doctor: the stored backfill state (an interrupted run's cursor, and when a complete run finished). */
export async function readTrustBackfillState(engine: BrainEngine): Promise<{ interrupted: boolean; completedAt: string | null }> {
  const [row] = await engine.executeRaw<{ n: number | string }>(
    'SELECT count(*) AS n FROM op_checkpoints WHERE op = $1 AND fingerprint = $2', [CHECKPOINT_OP, CHECKPOINT_KEY]);
  return { interrupted: Number(row?.n ?? 0) > 0, completedAt: await engine.getConfig(TRUST_BACKFILL_COMPLETED_KEY) };
}

export interface TrustTierTableCounts { table: TrustTable; rows: number; counts: TierCounts }

/** Stored tier counts per table (doctor `trust_tiers`). Throws on a brain without the columns. */
export async function readTrustTierCounts(engine: BrainEngine): Promise<TrustTierTableCounts[]> {
  const out: TrustTierTableCounts[] = [];
  for (const table of BACKFILL_ORDER) {
    const rows = await engine.executeRaw<{ tier: string; n: number | string }>(`SELECT trust_tier AS tier, count(*) AS n FROM ${table} GROUP BY trust_tier`);
    const counts = zero();
    for (const row of rows) counts[isTrustTier(row.tier) ? row.tier : 'unknown'] += Number(row.n);
    out.push({ table, rows: rows.reduce((n, r) => n + Number(r.n), 0), counts });
  }
  return out;
}