Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
21.0 KB
Raw Blame
/**
 * Storage for use-attributed retrieval feedback (migration v207). One module
 * owns every statement against retrieval_events / retrieval_event_pages /
 * retrieval_event_links / retrieval_feedback / retrieval_weights; both engines
 * run the same SQL through `engine.executeRaw` with positional binds (no JSONB).
 */
import type { BrainEngine } from '../engine.ts';
import type { EvalCandidateInput } from '../types.ts';

export type FeedbackOp = 'query' | 'search' | 'think' | 'synthesize' | 'recall';
export type FeedbackSignal = 'explicit' | 'cited';
export type ElementKind = 'page' | 'link';

export const NEUTRAL_WEIGHT = 0.5;

export interface EventPage {
  source_id: string;
  slug: string;
  content_hash: string | null;
  rank: number;
  cited: boolean;
}

export interface EventLink {
  source_id: string;
  edge_key: string;
  to_slug: string;
}

export interface RetrievalEventInput {
  id: string;
  client_id: string;
  op: FeedbackOp;
  pages: EventPage[];
  links: EventLink[];
}

export interface StoredEvent {
  id: string;
  client_id: string;
  op: FeedbackOp;
  created_at: Date;
  pages: EventPage[];
  links: EventLink[];
}

export interface RatingTarget {
  kind: ElementKind;
  source_id: string;
  key: string;
  rating: number;
  content_hash: string | null;
}

export interface AppliedRating {
  kind: ElementKind;
  source_id: string;
  key: string;
  rating: number;
  weight_before: number;
  weight_after: number;
  newly_applied: boolean;
}

const pageRowExists = (source: string, slug: string) => `EXISTS (SELECT 1 FROM pages pg WHERE pg.source_id = ${source} AND pg.slug = ${slug} FOR KEY SHARE)`;
const edgeEndsExist = (source: string, key: string) =>
  `${pageRowExists(source, `split_part(${key}, '|', 1)`)} AND ${pageRowExists(source, `reverse(split_part(reverse(${key}), '|', 1))`)}`;

export async function insertRetrievalEvents(engine: BrainEngine, events: RetrievalEventInput[]): Promise<void> {
  if (events.length === 0) return;
  await engine.executeRaw(
    `INSERT INTO retrieval_events (id, client_id, op)
     SELECT * FROM unnest($1::text[], $2::text[], $3::text[])
     ON CONFLICT (id) DO NOTHING`,
    [events.map(e => e.id), events.map(e => e.client_id), events.map(e => e.op)],
  );
  const pages = events.flatMap(e => e.pages.map(p => ({ id: e.id, ...p })));
  if (pages.length > 0) {
    await engine.executeRaw(
      `INSERT INTO retrieval_event_pages (event_id, source_id, slug, content_hash, rank, cited)
       SELECT * FROM unnest($1::text[], $2::text[], $3::text[], $4::text[], $5::int[], $6::text[]::boolean[])
         AS t(event_id, source_id, slug, content_hash, rank, cited)
        WHERE ${pageRowExists('t.source_id', 't.slug')}
       ON CONFLICT DO NOTHING`,
      [
        pages.map(p => p.id), pages.map(p => p.source_id), pages.map(p => p.slug),
        pages.map(p => p.content_hash), pages.map(p => p.rank), pages.map(p => (p.cited ? 'true' : 'false')),
      ],
    );
  }
  const links = events.flatMap(e => e.links.map(l => ({ id: e.id, ...l })));
  if (links.length > 0) {
    await engine.executeRaw(
      `INSERT INTO retrieval_event_links (event_id, source_id, edge_key, to_slug)
       SELECT * FROM unnest($1::text[], $2::text[], $3::text[], $4::text[]) AS t(event_id, source_id, edge_key, to_slug)
        WHERE ${pageRowExists('t.source_id', 't.to_slug')} AND ${edgeEndsExist('t.source_id', 't.edge_key')}
       ON CONFLICT DO NOTHING`,
      [links.map(l => l.id), links.map(l => l.source_id), links.map(l => l.edge_key), links.map(l => l.to_slug)],
    );
  }
}

export async function getRetrievalEvent(engine: BrainEngine, id: string): Promise<StoredEvent | null> {
  const rows = await engine.executeRaw<{ id: string; client_id: string; op: FeedbackOp; created_at: Date | string }>(
    `SELECT id, client_id, op, created_at FROM retrieval_events WHERE id = $1`,
    [id],
  );
  const row = rows[0];
  if (!row) return null;
  const pages = await engine.executeRaw<{ source_id: string; slug: string; content_hash: string | null; rank: number | string; cited: boolean }>(
    `SELECT source_id, slug, content_hash, rank, cited FROM retrieval_event_pages WHERE event_id = $1 ORDER BY rank`,
    [id],
  );
  const links = await engine.executeRaw<EventLink>(
    `SELECT source_id, edge_key, to_slug FROM retrieval_event_links WHERE event_id = $1 ORDER BY source_id, edge_key, to_slug`,
    [id],
  );
  return {
    id: row.id,
    client_id: row.client_id,
    op: row.op,
    created_at: new Date(row.created_at),
    pages: pages.map(p => ({ ...p, rank: Number(p.rank) })),
    links,
  };
}

/** Current content_hash of each (source_id, slug); missing or deleted pages are absent. */
export async function currentPageHashes(
  engine: BrainEngine,
  refs: Array<{ source_id: string; slug: string }>,
): Promise<Map<string, string | null>> {
  const out = new Map<string, string | null>();
  if (refs.length === 0) return out;
  const rows = await engine.executeRaw<{ source_id: string; slug: string; content_hash: string | null }>(
    `SELECT p.source_id, p.slug, p.content_hash
       FROM pages p
       JOIN unnest($1::text[], $2::text[]) AS r(source_id, slug) ON p.source_id = r.source_id AND p.slug = r.slug
      WHERE p.deleted_at IS NULL`,
    [refs.map(r => r.source_id), refs.map(r => r.slug)],
  );
  for (const r of rows) out.set(`${r.source_id}:${r.slug}`, r.content_hash);
  return out;
}

/**
 * Apply one batch of ratings atomically. Each element's feedback row is
 * inserted first (idempotent on the primary key); only newly inserted rows
 * move the weight, inside the same statement, so concurrent raters are
 * ordered by the weight row's lock and a retry never compounds.
 */
export async function applyRatings(
  engine: BrainEngine,
  args: { eventId: string; clientId: string; signal: FeedbackSignal; alpha: number; targets: RatingTarget[] },
): Promise<AppliedRating[]> {
  const { eventId, clientId, signal, alpha } = args;
  const seen = new Set<string>();
  const targets = args.targets.filter(t => {
    const id = `${t.kind}|${t.source_id}|${t.key}`;
    if (seen.has(id)) return false;
    seen.add(id);
    return true;
  });
  if (targets.length === 0) return [];
  const kinds = targets.map(t => t.kind);
  const sources = targets.map(t => t.source_id);
  const keys = targets.map(t => t.key);
  const ratings = targets.map(t => t.rating);
  const hashes = targets.map(t => t.content_hash);
  const applied = await engine.executeRaw<{ element_kind: ElementKind; source_id: string; element_key: string; weight_after: number | string }>(
    `WITH input AS (
       SELECT * FROM unnest($1::text[], $2::text[], $3::text[], $4::int[], $5::text[])
         AS t(element_kind, source_id, element_key, rating, content_hash)
        WHERE CASE t.element_kind WHEN 'page' THEN ${pageRowExists('t.source_id', 't.element_key')}
                                  ELSE ${edgeEndsExist('t.source_id', 't.element_key')} END
     ),
     cur AS (
       SELECT i.*, COALESCE(w.weight, ${NEUTRAL_WEIGHT})::real AS w0,
              CASE WHEN w.weight IS NULL THEN ${NEUTRAL_WEIGHT}
                   WHEN i.element_kind = 'page' AND w.content_hash IS DISTINCT FROM i.content_hash
                     THEN ${NEUTRAL_WEIGHT} + (w.weight - ${NEUTRAL_WEIGHT}) / 2
                   ELSE w.weight END AS w_eff
         FROM input i
         LEFT JOIN retrieval_weights w
           ON w.source_id = i.source_id AND w.element_kind = i.element_kind AND w.element_key = i.element_key
     ),
     calc AS (
       SELECT c.*, LEAST(1, GREATEST(0, c.w_eff + $6::real * ((c.rating - 1) / 4.0 - c.w_eff)))::real AS w1
         FROM cur c
     ),
     ins AS (
       INSERT INTO retrieval_feedback
         (event_id, signal, element_kind, source_id, element_key, client_id, rating, weight_before, weight_after)
       SELECT $7, $8, element_kind, source_id, element_key, $9, rating, w_eff, w1 FROM calc
       ON CONFLICT DO NOTHING
       RETURNING element_kind, source_id, element_key, rating
     )
     INSERT INTO retrieval_weights AS rw (source_id, element_kind, element_key, weight, content_hash, updates, updated_at)
     SELECT ins.source_id, ins.element_kind, ins.element_key, calc.w1, calc.content_hash, 1, now()
       FROM ins JOIN calc ON calc.element_kind = ins.element_kind AND calc.source_id = ins.source_id AND calc.element_key = ins.element_key
     ON CONFLICT (source_id, element_kind, element_key) DO UPDATE SET
       weight = LEAST(1, GREATEST(0,
         (CASE WHEN rw.element_kind = 'page' AND rw.content_hash IS DISTINCT FROM EXCLUDED.content_hash
               THEN ${NEUTRAL_WEIGHT} + (rw.weight - ${NEUTRAL_WEIGHT}) / 2 ELSE rw.weight END)
         + $6::real * ((SELECT rating FROM ins i2 WHERE i2.source_id = EXCLUDED.source_id AND i2.element_kind = EXCLUDED.element_kind AND i2.element_key = EXCLUDED.element_key) - 1) / 4.0
         - $6::real * (CASE WHEN rw.element_kind = 'page' AND rw.content_hash IS DISTINCT FROM EXCLUDED.content_hash
               THEN ${NEUTRAL_WEIGHT} + (rw.weight - ${NEUTRAL_WEIGHT}) / 2 ELSE rw.weight END)))::real,
       content_hash = EXCLUDED.content_hash,
       updates = rw.updates + 1,
       updated_at = now()
     RETURNING rw.element_kind, rw.source_id, rw.element_key, rw.weight AS weight_after`,
    [kinds, sources, keys, ratings, hashes, alpha, eventId, signal, clientId],
  );
  const newly = new Set(applied.map(a => `${a.element_kind}|${a.source_id}|${a.element_key}`));
  const stored = await engine.executeRaw<{ element_kind: ElementKind; source_id: string; element_key: string; rating: number | string; weight_before: number | string; weight_after: number | string }>(
    `SELECT element_kind, source_id, element_key, rating, weight_before, weight_after
       FROM retrieval_feedback
      WHERE event_id = $1 AND signal = $2
        AND (element_kind, source_id, element_key) IN (
          SELECT * FROM unnest($3::text[], $4::text[], $5::text[]))`,
    [eventId, signal, kinds, sources, keys],
  );
  const finalWeights = new Map(applied.map(a => [`${a.element_kind}|${a.source_id}|${a.element_key}`, Number(a.weight_after)]));
  return stored.map(r => {
    const id = `${r.element_kind}|${r.source_id}|${r.element_key}`;
    return {
      kind: r.element_kind,
      source_id: r.source_id,
      key: r.element_key,
      rating: Number(r.rating),
      weight_before: Number(r.weight_before),
      weight_after: newly.has(id) ? (finalWeights.get(id) ?? Number(r.weight_after)) : Number(r.weight_after),
      newly_applied: newly.has(id),
    };
  });
}

/**
 * Learned weights for the given elements. A page edited since its weight was
 * learned reads at half its learned deviation from neutral; missing rows are
 * absent (callers treat them as neutral).
 */
export async function readWeights(
  engine: BrainEngine,
  kind: ElementKind,
  refs: Array<{ source_id: string; key: string }>,
): Promise<Map<string, number>> {
  const out = new Map<string, number>();
  if (refs.length === 0) return out;
  const pageJoin = kind === 'page'
    ? `LEFT JOIN pages p ON p.source_id = w.source_id AND p.slug = w.element_key AND p.deleted_at IS NULL`
    : '';
  const effective = kind === 'page'
    ? `CASE WHEN p.content_hash IS DISTINCT FROM w.content_hash
            THEN ${NEUTRAL_WEIGHT} + (w.weight - ${NEUTRAL_WEIGHT}) / 2 ELSE w.weight END`
    : 'w.weight';
  const rows = await engine.executeRaw<{ source_id: string; element_key: string; weight: number | string }>(
    `SELECT w.source_id, w.element_key, ${effective} AS weight
       FROM retrieval_weights w
       JOIN unnest($2::text[], $3::text[]) AS r(source_id, element_key)
         ON w.source_id = r.source_id AND w.element_key = r.element_key
       ${pageJoin}
      WHERE w.element_kind = $1`,
    [kind, refs.map(r => r.source_id), refs.map(r => r.key)],
  );
  for (const r of rows) out.set(`${r.source_id}:${r.element_key}`, Number(r.weight));
  return out;
}

export async function countRecentRatingCalls(engine: BrainEngine, clientId: string): Promise<number> {
  const rows = await engine.executeRaw<{ n: number | string }>(
    `SELECT count(DISTINCT (event_id, applied_at))::int AS n
       FROM retrieval_feedback
      WHERE client_id = $1 AND signal = 'explicit' AND applied_at > now() - interval '1 hour'`,
    [clientId],
  );
  return Number(rows[0]?.n ?? 0);
}

export async function pruneRetrievalFeedback(engine: BrainEngine, retentionDays: number): Promise<{ events: number; weights: number }> {
  const events = await engine.executeRaw<{ n: number | string }>(
    `WITH d AS (DELETE FROM retrieval_events WHERE created_at < now() - ($1::int * interval '1 day') RETURNING 1)
     SELECT count(*)::int AS n FROM d`,
    [retentionDays],
  );
  const weights = await engine.executeRaw<{ n: number | string }>(
    `WITH d AS (
       DELETE FROM retrieval_weights w
        WHERE (w.element_kind = 'page' AND NOT EXISTS (
                 SELECT 1 FROM pages p WHERE p.source_id = w.source_id AND p.slug = w.element_key))
           OR (w.element_kind = 'link' AND NOT EXISTS (
                 SELECT 1 FROM links l
                   JOIN pages fp ON fp.id = l.from_page_id
                   JOIN pages tp ON tp.id = l.to_page_id
                  WHERE fp.source_id = w.source_id
                    AND fp.slug || '|' || l.link_type || '|' || tp.slug = w.element_key))
       RETURNING 1)
     SELECT count(*)::int AS n FROM d`,
  );
  return { events: Number(events[0]?.n ?? 0), weights: Number(weights[0]?.n ?? 0) };
}

/**
 * #8821: a hard-deleted page's slug leaves its source's retrieval logs, ratings and weights (as a page key or either
 * end of a `from|type|to` edge key) and the captured eval candidates whose results came from that source. Runs inside
 * the purge's transaction; returns rows touched per store, keyed as the purge receipt names them.
 */
/** A captured slug is the purged page's unless another source the candidate covers still has a page by that name. */
const purgedCandidateSlug = (slug: string) =>
  `${slug} = ANY($2::text[]) AND NOT EXISTS (SELECT 1 FROM pages po WHERE po.slug = ${slug} AND po.source_id = ANY(eval_candidates.source_ids) AND po.source_id <> $1)`;

export async function scrubPurgedSlugs(
  engine: Pick<BrainEngine, 'executeRaw'>,
  sourceId: string,
  slugs: readonly string[],
): Promise<Record<string, number>> {
  if (!slugs.length) return {};
  const edgeNames = (key: string) => `EXISTS (SELECT 1 FROM unnest($2::text[]) AS s(slug) WHERE starts_with(${key}, s.slug || '|') OR right(${key}, length(s.slug) + 1) = '|' || s.slug)`;
  const count = async (sql: string) => (await engine.executeRaw(sql, [sourceId, [...slugs]])).length;
  return {
    retrieval_event_pages: await count('DELETE FROM retrieval_event_pages WHERE source_id = $1 AND slug = ANY($2::text[]) RETURNING 1'),
    retrieval_event_links: await count(`DELETE FROM retrieval_event_links WHERE source_id = $1 AND (to_slug = ANY($2::text[]) OR ${edgeNames('edge_key')}) RETURNING 1`),
    retrieval_feedback: await count(`DELETE FROM retrieval_feedback WHERE source_id = $1
      AND ((element_kind = 'page' AND element_key = ANY($2::text[])) OR (element_kind = 'link' AND ${edgeNames('element_key')})) RETURNING 1`),
    retrieval_weights: await count(`DELETE FROM retrieval_weights WHERE source_id = $1
      AND ((element_kind = 'page' AND element_key = ANY($2::text[])) OR (element_kind = 'link' AND ${edgeNames('element_key')})) RETURNING 1`),
    'eval_candidates.retrieved_slugs': await count(`UPDATE eval_candidates
      SET retrieved_slugs = ARRAY(SELECT s FROM unnest(retrieved_slugs) WITH ORDINALITY AS u(s, n) WHERE NOT (${purgedCandidateSlug('u.s')}) ORDER BY n)
      WHERE $1 = ANY(source_ids) AND EXISTS (SELECT 1 FROM unnest(retrieved_slugs) AS r(s) WHERE ${purgedCandidateSlug('r.s')}) RETURNING 1`),
  };
}

/**
 * #9068: insert one captured eval candidate with only the slugs a page of its sources (any source when it names none)
 * still holds, in retrieval order. Each probe takes the feedback writers' key-share lock on the page row, so a capture
 * deferred past its query's response either commits before a purge takes the row, and the purge's scrub strips it, or
 * waits for the purge and drops the slug. A facts-arm result (`facts/<id>`, facts-arm.ts) names a fact, not a page:
 * it stays while that fact row does. Both engines run this statement.
 */
export async function insertEvalCandidate(engine: Pick<BrainEngine, 'executeRaw'>, input: EvalCandidateInput): Promise<number> {
  const rows = await engine.executeRaw<{ id: number | string }>(
    `INSERT INTO eval_candidates (
       tool_name, query, retrieved_slugs, retrieved_chunk_ids, source_ids,
       expand_enabled, detail, detail_resolved, vector_enabled, expansion_applied,
       latency_ms, remote, job_id, subagent_id, embedding_column
     ) VALUES ($1, $2,
       ARRAY(SELECT u.s FROM unnest($3::text[]) WITH ORDINALITY AS u(s, n)
              WHERE EXISTS (SELECT 1 FROM pages pg WHERE pg.slug = u.s
                              AND (cardinality($5::text[]) = 0 OR pg.source_id = ANY($5::text[])) FOR KEY SHARE)
                 OR CASE WHEN u.s ~ '^facts/[0-9]{1,18}$' THEN EXISTS (SELECT 1 FROM facts f WHERE f.id = substr(u.s, 7)::bigint
                              AND (cardinality($5::text[]) = 0 OR f.source_id = ANY($5::text[]))) ELSE false END
              ORDER BY u.n),
       $4::int[], $5::text[], $6, $7, $8, $9, $10, $11, $12, $13, $14, $15)
     RETURNING id`,
    [input.tool_name, input.query, input.retrieved_slugs, input.retrieved_chunk_ids, input.source_ids, input.expand_enabled, input.detail,
      input.detail_resolved, input.vector_enabled, input.expansion_applied, input.latency_ms, input.remote, input.job_id, input.subagent_id,
      input.embedding_column ?? null],
  );
  return Number(rows[0]!.id);
}

export async function resetRetrievalWeights(
  engine: BrainEngine,
  opts: { sourceId?: string; slug?: string },
): Promise<number> {
  const rows = await engine.executeRaw<{ n: number | string }>(
    `WITH d AS (
       DELETE FROM retrieval_weights
        WHERE ($1::text IS NULL OR source_id = $1)
          AND ($2::text IS NULL OR (element_kind = 'page' AND element_key = $2)
                               OR (element_kind = 'link' AND (element_key LIKE $2 || '|%' OR element_key LIKE '%|' || $2)))
       RETURNING 1)
     SELECT count(*)::int AS n FROM d`,
    [opts.sourceId ?? null, opts.slug ?? null],
  );
  return Number(rows[0]?.n ?? 0);
}

export interface FeedbackStatusRow {
  events: number;
  ratings_explicit: number;
  ratings_cited: number;
  weights_total: number;
  weights_off_neutral: number;
  last_rating_at: Date | null;
  top_client_share_7d: number | null;
  lowest: Array<{ source_id: string; slug: string; weight: number; low_ratings: number }>;
  highest: Array<{ source_id: string; slug: string; weight: number }>;
}

export async function feedbackStatus(engine: BrainEngine): Promise<FeedbackStatusRow> {
  const [counts] = await engine.executeRaw<Record<string, number | string | Date | null>>(
    `SELECT
       (SELECT count(*)::int FROM retrieval_events) AS events,
       (SELECT count(*)::int FROM retrieval_feedback WHERE signal = 'explicit') AS ratings_explicit,
       (SELECT count(*)::int FROM retrieval_feedback WHERE signal = 'cited') AS ratings_cited,
       (SELECT count(*)::int FROM retrieval_weights) AS weights_total,
       (SELECT count(*)::int FROM retrieval_weights WHERE abs(weight - ${NEUTRAL_WEIGHT}) > 0.01) AS weights_off_neutral,
       (SELECT max(applied_at) FROM retrieval_feedback) AS last_rating_at,
       (SELECT max(n)::float / NULLIF(sum(n), 0) FROM (
          SELECT count(*) AS n FROM retrieval_feedback
           WHERE signal = 'explicit' AND applied_at > now() - interval '7 days'
           GROUP BY client_id) c) AS top_client_share_7d`,
  );
  const lowest = await engine.executeRaw<{ source_id: string; slug: string; weight: number | string; low_ratings: number | string }>(
    `SELECT w.source_id, w.element_key AS slug, w.weight,
            (SELECT count(*) FROM retrieval_feedback f
              WHERE f.element_kind = 'page' AND f.source_id = w.source_id
                AND f.element_key = w.element_key AND f.rating <= 2)::int AS low_ratings
       FROM retrieval_weights w
      WHERE w.element_kind = 'page' AND w.weight < ${NEUTRAL_WEIGHT}
      ORDER BY w.weight ASC, w.source_id, w.element_key LIMIT 10`,
  );
  const highest = await engine.executeRaw<{ source_id: string; slug: string; weight: number | string }>(
    `SELECT source_id, element_key AS slug, weight FROM retrieval_weights
      WHERE element_kind = 'page' AND weight > ${NEUTRAL_WEIGHT}
      ORDER BY weight DESC, source_id, element_key LIMIT 10`,
  );
  return {
    events: Number(counts?.events ?? 0),
    ratings_explicit: Number(counts?.ratings_explicit ?? 0),
    ratings_cited: Number(counts?.ratings_cited ?? 0),
    weights_total: Number(counts?.weights_total ?? 0),
    weights_off_neutral: Number(counts?.weights_off_neutral ?? 0),
    last_rating_at: counts?.last_rating_at ? new Date(counts.last_rating_at as string) : null,
    top_client_share_7d: counts?.top_client_share_7d == null ? null : Number(counts.top_client_share_7d),
    lowest: lowest.map(r => ({ ...r, weight: Number(r.weight), low_ratings: Number(r.low_ratings) })),
    highest: highest.map(r => ({ ...r, weight: Number(r.weight) })),
  };
}