Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
28.4 KB
Raw Blame
/**
 * The vector-search statement, built once for both engines, the doctor
 * `vector_plan` check and the plan-proof E2E so the SQL they run or EXPLAIN
 * can never drift (#5824).
 *
 * Content freshness (`embedded_text_hash = md5(chunk_text)`) is an expression
 * Postgres has no statistics for. Inside the HNSW candidate CTE the planner
 * estimates it at 0.5% selectivity, expects to walk most of the index for
 * `ORDER BY embedding <=> $q LIMIT n`, and picks a sequential scan that hits
 * the vector deadline on large brains. On an HNSW-indexed column the
 * candidate CTE therefore carries only the well-estimated model check
 * (relaxed variant); freshness is projected as `hash_current` and filtered in
 * `scored`, so a stale-text chunk still never reaches results. The guarded
 * variant (freshness inside the CTE) serves non-indexed columns, the legacy
 * guard rollback, the exact fallback and the `hasMore` exhaustion witness.
 *
 * `candidate_pool` counts raw candidate rows (a full raw window escalates, a
 * short one means the index ran dry); `eligible_pool` counts the fresh ones
 * and is what `hasMore` compares against its guarded count.
 *
 * `indexWalkSql` (relaxed variant only) runs first. Even with freshness out
 * of the CTE, the joined statement's plan sits on a cost knife edge: TOASTed
 * vectors make a sequential scan look cheap, the HNSW estimate grows with
 * `hnsw.ef_search` (the window), and the private-page rule's correlated
 * subplans price every page lookup so high that the planner filters all
 * pages up front. A 25k-chunk brain flips to a full scan, sort and
 * nested-loop join at a 50-row limit or on any remote read. The index walk
 * orders `content_chunks` alone (`ann`, chunk-level filters only, twice the
 * window, scaled by 1/share for a source scope), then joins pages and
 * sources by key and applies every page filter to those rows, keeping the
 * nearest `innerLimit` eligible ones: the same set the joined statement
 * selects whenever that many survive. Callers run it with sorting disabled so
 * the HNSW scan is the only ordered path, accept it when its window is full,
 * widen it when a full window's pages fall short of the limit
 * (searchIndexWalk), and otherwise run the joined statement as before, whose plans stay with the
 * planner for selective filters, with materialization off
 * (JOINED_STATEMENT_SETTINGS). A type or date filter skips the walk: it is
 * an explicit narrowing that usually leaves the window short, so the walk
 * would only add its cost. So does a source scope whose share of pages
 * (`pages.source_id` planner statistics, `scope.share`) is below
 * INDEX_WALK_MIN_SCOPE_SHARE: the nearest chunks overall rarely fill its
 * window.
 *
 * `scopeScanSql` serves a source scope under SCOPE_SCAN_MAX_SHARE of pages
 * whose estimated chunk count (`scope.chunks`, SCOPE_CHUNKS_SQL's sampled
 * count) is at most SCOPE_SCAN_MAX_CHUNKS: an exact
 * distance scan over the scope's chunks with every filter applied, ordering
 * only chunk ids (`+ 0` keeps the index out), then the window joins back for
 * its columns. The eligible page ids come first as `= ANY(ARRAY(...))`, so
 * chunks are always reached through `idx_chunks_page`: joined inline, the
 * private-page rule's estimate flipped the scan to a sequential scan of every
 * chunk with a nested-loop join filter and ran the rule once per chunk
 * (1.1 s for a 40k-chunk scope at 50k pages, 9-22 s for a 74k-chunk one). It is complete by construction, where a filtered HNSW scan of
 * a mid-share scope returned a fraction of the true neighbours, and it costs
 * a few microseconds per scope chunk. Up to SCOPE_SCAN_FIRST_MAX_CHUNKS it
 * replaces the walk; above that the walk runs first (fast when the scope's
 * chunks spread through the space) and the scan answers when the walk comes
 * back short, as it does for a scope clustered away from the query. Callers
 * accept the scan when it fills the limit or its window is short (the scope
 * ran out of eligible chunks).
 */
import type { SearchOpts } from '../types.ts';
import { hnswIndexExpected } from '../vector-index.ts';
import { unverifiedExtractionFragment } from '../extraction-review.ts';
import { buildVectorCastFragment, normalizeEngineColumn } from './embedding-column.ts';
import { frontmatterWhereClauses } from './frontmatter-where.ts';
import { resolveBoostMap, resolveHardExcludes } from './source-boost.ts';
import { buildBestPerPagePoolCte, buildHardExcludeClause, buildSourceFactorCase, buildVisibilityClause } from './sql-ranking.ts';
import { chunkGenerationProbe } from './safe-chunks.ts';

export const VECTOR_EXTENSION_VERSION_SQL = `SELECT extversion FROM pg_extension WHERE extname = 'vector'`;

export interface VectorSearchStatementInput {
  dialect: 'postgres' | 'pglite';
  embedding: Float32Array;
  limit: number;
  offset: number;
  opts?: SearchOpts;
  /**
   * Estimated share of pages and chunks the source scope holds
   * (`vectorScopeLoader`): below INDEX_WALK_MIN_SCOPE_SHARE the walk is
   * omitted; at most SCOPE_SCAN_MAX_CHUNKS adds the scope scan.
   */
  scope?: VectorScope;
  /** chunkManifestReads; unset reads the manifest. */
  chunkManifest?: boolean;
}

export interface VectorSearchStatement {
  /** ANN statement; `params[innerLimitIdx]` is the candidate window. */
  sql: string;
  /** Exact scan with the full guard (`+ 0` disables the index); bind `null` at innerLimitIdx. */
  exactSql: string;
  /** Guarded eligibility witness; bind `[...params.slice(0, innerLimitIdx), pool + 1]`. */
  hasMoreSql: string;
  params: unknown[];
  innerLimitIdx: number;
  innerLimit: number;
  indexed: boolean;
  /** True when freshness moved out of the candidate CTE (indexed `embedding`, legacy guard off). */
  relaxed: boolean;
  /** Index-walk statement (relaxed variant without a type or date filter), same parameters as `sql`; trusted when `candidate_pool` reaches the window. */
  indexWalkSql?: string;
  /** Raw rows the walk orders per window slot: INDEX_WALK_OVERFETCH, scaled by 1/share for a source scope. */
  indexWalkOverfetch: number;
  /** Exact scan over a small source scope's chunks (relaxed variant), same parameters as `sql`; runs after the walk when both exist. */
  scopeScanSql?: string;
}

/**
 * The page columns the index walk's candidates, page filters and visibility
 * read, probed by key per ANN row. Named, never `*`: the body columns stay out
 * of the probe (a segmented page has no body string).
 */
export const INDEX_WALK_PAGE_COLUMNS = ['id', 'slug', 'source_id', 'title', 'type', 'frontmatter', 'deleted_at', 'updated_at', 'effective_date',
  'effective_date_source', 'trust_tier', 'body_generation', 'chunker_version', 'knowledge_revision', 'text_projection_revision', 'page_readers', 'folder_rule_ids'] as const;

/** Raw rows the index walk orders per window slot, so page filters can drop some and still fill the window. */
export const INDEX_WALK_OVERFETCH = 2;

/**
 * Session settings for the index-walk attempt: with sorting disabled, the
 * HNSW scan is the only path that delivers `ann` in distance order.
 */
export const INDEX_WALK_SETTINGS: Readonly<Record<string, string>> = { enable_sort: 'off' };

/**
 * Session settings for the ANN attempts of a joined statement (`sql`) that
 * backs an index walk, run when the walk times out or comes back short. The
 * private-page rule's correlated subplans price each page lookup high and its
 * row estimate low, so under the HNSW scan the planner preferred to filter
 * every page once, materialize the survivors and rescan them for each
 * candidate chunk, a join on `p.id = cc.page_id` run as a filter: about
 * 12.7M comparisons and 3 GB of temp reads (the materialized pages pass
 * work_mem) for a 250-row window over 51k pages, 2.2 s warm and 3-4 s under
 * load. With materialization off, the same HNSW scan drives the same filters
 * through a `pages_pkey` probe per candidate (10 ms). Materialize nodes
 * Postgres needs for correctness stay. Statements without a walk (a type or
 * date filter, a small source scope) keep their plans: there the planner may
 * prefer an exact scope-first scan once the materialized join is ruled out,
 * which orders a different, exact window.
 */
export const JOINED_STATEMENT_SETTINGS: Readonly<Record<string, string>> = { enable_material: 'off' };

/** Source scopes holding a smaller share of pages skip the index walk. */
export const INDEX_WALK_MIN_SCOPE_SHARE = 0.04;

/**
 * Source scopes under SCOPE_SCAN_MAX_SHARE of pages get the exact scope scan
 * up to SCOPE_SCAN_MAX_CHUNKS (counted, SCOPE_CHUNKS_SQL): first when at most
 * SCOPE_SCAN_FIRST_MAX_CHUNKS, otherwise after the walk comes back short.
 * The scan costs about 5 µs per scope chunk (75k chunks: 340 ms, 157k:
 * 760 ms on a 50k-page brain), so the 120k cap keeps the fallback near
 * 600 ms, under the joined statement's 820 ms on a clustered 75k-chunk scope
 * and well inside the attempt's 2 s budget. The cap was 60k when the count
 * was the page share of chunk reltuples, which put that 75k-chunk scope at
 * 39k.
 */
export const SCOPE_SCAN_MAX_SHARE = 0.3;
export const SCOPE_SCAN_FIRST_MAX_CHUNKS = 25_000;
export const SCOPE_SCAN_MAX_CHUNKS = 120_000;

/** Walk overfetch for a scope holding `share` of pages: the window keeps about INDEX_WALK_OVERFETCH in-scope rows per slot. */
export function indexWalkOverfetch(share: number | undefined): number {
  return share === undefined || share >= 1 ? INDEX_WALK_OVERFETCH : Math.ceil(INDEX_WALK_OVERFETCH / Math.max(share, INDEX_WALK_MIN_SCOPE_SHARE));
}

/** Planner statistics for `pages.source_id`; PGLite analyzes `pages` itself (planner-stats.ts), Postgres through autovacuum. */
export const PAGE_SOURCE_STATS_SQL = `SELECT most_common_vals::text::text[] AS sources, most_common_freqs AS freqs, n_distinct, null_frac,
    (SELECT reltuples FROM pg_class WHERE oid = 'pages'::regclass) AS reltuples,
    (SELECT reltuples FROM pg_class WHERE oid = 'content_chunks'::regclass) AS chunk_reltuples
  FROM pg_stats WHERE schemaname = current_schema() AND tablename = 'pages' AND attname = 'source_id'`;

export interface PageSourceStats {
  sources: string[] | null;
  freqs: number[] | null;
  n_distinct: number;
  null_frac: number;
  reltuples: number;
  /** `content_chunks` reltuples; -1 or absent when never analyzed. */
  chunk_reltuples?: number;
}

export interface VectorScope {
  share: number;
  /**
   * Estimated chunks in the scope: SCOPE_CHUNKS_SQL's count for a scope under
   * SCOPE_SCAN_MAX_SHARE, else the share times `content_chunks` reltuples;
   * absent without either.
   */
  chunks?: number;
}

/** Pages of a source scope whose chunks SCOPE_CHUNKS_SQL counts; a smaller scope has every page counted. */
export const SCOPE_CHUNK_SAMPLE_PAGES = 400;

/**
 * Chunks in a source scope (`$1`, source ids): its live pages, and the chunks
 * of a hash-stride sample of about SCOPE_CHUNK_SAMPLE_PAGES of them through
 * `idx_chunks_page`. Page share alone undercounts a source of long pages (a
 * sessions-like source holds 9.4 chunks per page against a brain average of
 * 5), which sent a 74k-chunk scope down the path sized for 39k. The sample is
 * deterministic (`hashint4`, so interleaved page ids do not alias a stride)
 * and costs one pass over the scope's page ids plus ~400 index probes.
 */
// chunk-visibility-ok: planner size estimate; staged rows only nudge the sample count
export const SCOPE_CHUNKS_SQL = `WITH scope_pages AS MATERIALIZED (
    SELECT id FROM pages WHERE source_id = ANY($1::text[]) AND deleted_at IS NULL),
  total AS (SELECT count(*)::int AS pages FROM scope_pages),
  sample AS (SELECT id FROM scope_pages, total WHERE hashint4(id) % GREATEST(1, total.pages / ${SCOPE_CHUNK_SAMPLE_PAGES}) = 0)
  SELECT (SELECT pages FROM total) AS pages, (SELECT count(*)::int FROM sample) AS sampled,
    (SELECT count(*)::int FROM content_chunks WHERE page_id = ANY(ARRAY(SELECT id FROM sample))) AS sample_chunks`;

export interface ScopeChunkCount {
  pages: number;
  sampled: number;
  sample_chunks: number;
}

/**
 * Share of pages the source scope holds, or undefined without a scope or statistics. The float4
 * `most_common_freqs` of a scope covering every source sum to 0.99999997, which widened the walk by
 * half (#6728), so the sum is rounded to SCOPE_SHARE_PRECISION and capped at 1.
 */
export function sourceScopeShare(stats: PageSourceStats | undefined, opts?: SearchOpts): number | undefined {
  const scope = scopeSourceIds(opts);
  if (!scope || !stats) return undefined;
  const sources = stats.sources ?? [];
  const freqs = (stats.freqs ?? []).map(Number);
  const distinct = Number(stats.n_distinct) < 0 ? -Number(stats.n_distinct) * Number(stats.reltuples) : Number(stats.n_distinct);
  const unlisted = Math.max(0, 1 - Number(stats.null_frac) - freqs.reduce((sum, freq) => sum + freq, 0)) / Math.max(1, distinct - sources.length);
  const share = scope.reduce((sum, id) => sum + (sources.includes(id) ? freqs[sources.indexOf(id)]! : unlisted), 0);
  return Math.min(1, Math.round(share * SCOPE_SHARE_PRECISION) / SCOPE_SHARE_PRECISION);
}

const SCOPE_SHARE_PRECISION = 1e6;

function scopeSourceIds(opts?: SearchOpts): string[] | undefined {
  const scope = opts?.sourceIds?.length ? opts.sourceIds : opts?.sourceId ? [opts.sourceId] : undefined;
  return scope && [...new Set(scope)];
}

/**
 * Share of pages and estimated chunks the source scope holds, or undefined
 * without a scope or statistics. A SCOPE_CHUNKS_SQL count, when given and
 * sampled, replaces the share-of-reltuples chunk estimate.
 */
export function sourceScope(stats: PageSourceStats | undefined, opts?: SearchOpts, counted?: ScopeChunkCount): VectorScope | undefined {
  const share = sourceScopeShare(stats, opts);
  if (share === undefined) return undefined;
  const sampled = Number(counted?.sampled ?? 0);
  if (sampled > 0) return { share, chunks: Math.round((Number(counted!.pages) * Number(counted!.sample_chunks)) / sampled) };
  const chunks = Number(stats?.chunk_reltuples ?? -1);
  return chunks > 0 ? { share, chunks: share * chunks } : { share };
}

/**
 * The engines' scope lookup, only for source-scoped searches:
 * PAGE_SOURCE_STATS_SQL through `load` at most once a minute, and for a scope
 * under SCOPE_SCAN_MAX_SHARE of pages (the only scopes whose chunk count picks
 * a path) SCOPE_CHUNKS_SQL through `count`, refreshed at most once a minute
 * per scope. The count never blocks a search: until a scope's first count
 * lands, and while a refresh runs, the search routes on the last count or the
 * share estimate. (`count` runs on the engine's own pool, so a search inside
 * a caller's transaction on a one-connection pool would otherwise wait on
 * itself.) The estimate only picks the walk, the scope scan or the joined
 * statement, each of which falls back to the joined statement when it comes
 * back short, so stale or missing statistics, or a failed count, cost speed
 * at most.
 */
export function vectorScopeLoader(
  load: () => Promise<PageSourceStats[]>,
  count?: (sourceIds: string[]) => Promise<ScopeChunkCount[]>,
): (opts?: SearchOpts) => Promise<VectorScope | undefined> {
  let cached: { at: number; stats: Promise<PageSourceStats | undefined> } | undefined;
  const counts = new Map<string, { at: number; value?: ScopeChunkCount }>();
  return async opts => {
    const ids = scopeSourceIds(opts);
    if (!ids) return undefined;
    if (!cached || performance.now() - cached.at > 60_000) cached = { at: performance.now(), stats: load().then(rows => rows[0], () => undefined) };
    const stats = await cached.stats;
    const share = sourceScopeShare(stats, opts);
    if (!count || share === undefined || share >= SCOPE_SCAN_MAX_SHARE) return sourceScope(stats, opts);
    const key = [...ids].sort().join('\u0000');
    const entry = counts.get(key);
    if (!entry || performance.now() - entry.at > 60_000) {
      const next: { at: number; value?: ScopeChunkCount } = { at: performance.now(), value: entry?.value };
      counts.set(key, next);
      count(ids).then(rows => { if (rows[0]) next.value = rows[0]; }, () => { /* keep the last count or the share estimate */ });
    }
    return sourceScope(stats, opts, counts.get(key)!.value);
  };
}

/**
 * The statement in both chunkManifestReads forms, each built on first use. The forms differ only in SQL text, so
 * either one answers for the shape (windows, params, walk and scope-scan presence). A Postgres search attempt picks
 * its form with the probe it runs inside its own transaction, after the settings, so the attempt's statement_timeout
 * bounds the probe as well (a probe before the transaction waited on a content_chunks lock with no bound).
 */
export function vectorStatementForms(build: (chunkManifest: boolean) => VectorSearchStatement): (chunkManifest: boolean) => VectorSearchStatement {
  const forms = new Map<boolean, VectorSearchStatement>();
  return chunkManifest => {
    let stmt = forms.get(chunkManifest);
    if (!stmt) forms.set(chunkManifest, stmt = build(chunkManifest));
    return stmt;
  };
}

export function buildVectorSearchStatement(input: VectorSearchStatementInput): VectorSearchStatement {
  const { dialect, limit, offset, opts } = input;
  const resolvedCol = normalizeEngineColumn(opts?.embeddingColumn);
  const indexed = hnswIndexExpected(resolvedCol.type, resolvedCol.dimensions);
  // innerLimit scales with offset to preserve the pagination contract.
  const innerLimit = offset + Math.max(limit * 5, 100);
  // issue #160: the guard predicate is projected as `unverified_stub` so
  // unverified auto-extracted stubs get factor 1.0 inside the re-rank.
  const sourceFactorCaseOnSlug = buildSourceFactorCase('slug', opts?.source_boosts ?? resolveBoostMap(), opts?.detail, 'unverified_stub');
  const hardExcludeClause = buildHardExcludeClause('p.slug', resolveHardExcludes(opts?.exclude_slug_prefixes, opts?.include_slug_prefixes));

  const params: unknown[] = ['[' + Array.from(input.embedding).join(',') + ']'];
  const bind = (value: unknown) => { params.push(value); return `$${params.length}`; };
  const filters: string[] = [];
  if (opts?.detail === 'low') filters.push(`AND cc.chunk_source = 'compiled_truth'`);
  if (opts?.type) filters.push(`AND p.type = ${bind(opts.type)}`);
  // v0.33: multi-type filter for whoknows, AND-applied with `type`.
  if (opts?.types && opts.types.length > 0) filters.push(`AND p.type = ANY(${bind(opts.types)}::text[])`);
  filters.push(...frontmatterWhereClauses('p', opts?.where, bind));
  if (opts?.exclude_slugs?.length) filters.push(`AND p.slug != ALL(${bind(opts.exclude_slugs)}::text[])`);
  if (opts?.language) filters.push(`AND cc.language = ${bind(opts.language)}`);
  if (opts?.symbolKind) filters.push(`AND cc.symbol_type = ${bind(opts.symbolKind)}`);
  // v0.29.1: since/until filter by effective date, with import-time fallback.
  // Spelled per column rather than as COALESCE(effective_date, updated_at,
  // created_at) <op> $n: same rows, but each arm has column statistics. The
  // planner estimates a since+until pair on the COALESCE at 0.5% and drops
  // the HNSW index exactly as it did for the freshness guard (#5824).
  const dateBound = (op: string, value: string) => {
    const bound = `${bind(value)}::text::timestamptz`;
    return `AND (p.effective_date ${op} ${bound} OR (p.effective_date IS NULL AND (p.updated_at ${op} ${bound} OR (p.updated_at IS NULL AND p.created_at ${op} ${bound}))))`;
  };
  if (opts?.afterDate) filters.push(dateBound(opts.afterDateInclusive ? '>=' : '>', opts.afterDate));
  if (opts?.beforeDate) filters.push(dateBound(opts.beforeDateInclusive ? '<=' : '<', opts.beforeDate));
  // v0.34.1 (#861): source isolation in the INNER CTE narrows the HNSW
  // candidate set before re-rank. Array form wins over scalar.
  if (opts?.sourceIds && opts.sourceIds.length > 0) filters.push(`AND p.source_id = ANY(${bind(opts.sourceIds)}::text[])`);
  else if (opts?.sourceId) filters.push(`AND p.source_id = ${bind(opts.sourceId)}`);
  // The index walk applies chunk-level filters while it orders the index and page-level ones after the key joins.
  const chunkFilters = filters.filter(sql => sql.startsWith('AND cc.'));
  const pageFilters = filters.filter(sql => !sql.startsWith('AND cc.'));

  let modelParam: string | undefined;
  if (resolvedCol.name === 'embedding') modelParam = bind(resolvedCol.embeddingModel || null);
  const relaxed = indexed && modelParam !== undefined && opts?.vectorLegacyGuard !== true;
  // A type or date filter, or a source scope with a small share of pages, is
  // the caller narrowing the search; the walk would usually come back short,
  // so those keep the joined statement alone.
  const share = input.scope?.share, chunks = input.scope?.chunks;
  const narrowed = !!(opts?.type || opts?.types?.length || opts?.where || opts?.afterDate || opts?.beforeDate)
    || (share !== undefined && share < INDEX_WALK_MIN_SCOPE_SHARE);
  const scopeScan = relaxed && share !== undefined && share < SCOPE_SCAN_MAX_SHARE && chunks !== undefined && chunks <= SCOPE_SCAN_MAX_CHUNKS;
  const walk = relaxed && !narrowed && !(scopeScan && chunks! <= SCOPE_SCAN_FIRST_MAX_CHUNKS);
  const overfetch = indexWalkOverfetch(share);
  const preMigration = modelParam ? `(${modelParam}::text IS NULL AND NOT EXISTS(SELECT 1 FROM config WHERE key='embedding_migration.state'))` : '';
  const hashCurrent = `(cc.embedded_text_hash=md5(cc.chunk_text) OR cc.embedded_text_hash IS NULL)`;
  const guardedGeneration = modelParam ? `AND ((cc.model=${modelParam} AND ${hashCurrent})
        OR ${preMigration})` : '';
  const relaxedGeneration = modelParam ? `AND (cc.model=${modelParam} OR ${preMigration})` : '';

  const innerLimitParam = bind(innerLimit);
  const innerLimitIdx = params.length - 1;
  const limitParam = bind(limit);
  const offsetParam = bind(offset);

  // v0.36 Phase 3: 'embedding_multimodal' carries text and image content, so
  // the column itself is the discriminator; the others filter by modality.
  const { col, castSql } = buildVectorCastFragment(resolvedCol);
  const modalityFilter = resolvedCol.name === 'embedding_image' ? `AND cc.modality = 'image'`
    : resolvedCol.name === 'embedding_multimodal' ? '' : `AND cc.modality = 'text'`;
  // v0.26.5: visibility in the inner CTE so hidden rows never take candidate slots.
  const visibilityClause = buildVisibilityClause('p', 's', { ...opts, chunkAlias: 'cc' });
  const pageVisibilityClause = buildVisibilityClause('p', 's', opts);
  const candidateFrom = (generation: string) => `FROM content_chunks cc
        JOIN pages p ON p.id = cc.page_id
        JOIN sources s ON s.id = p.source_id
        WHERE cc.${col} IS NOT NULL ${modalityFilter}
          ${filters.join('\n          ')}
          ${generation}
          ${hardExcludeClause}
          ${visibilityClause}`;
  const freshFilter = `${modelParam}::text IS NULL OR hash_current`;

  const candidateColumns = `p.slug, p.id as page_id, p.title, p.type, p.source_id,${dialect === 'pglite' ? ' p.updated_at,' : ''}
          p.effective_date, p.effective_date_source,
          CASE WHEN NULLIF(regexp_replace(p.frontmatter->>'message_id', '^[[:space:]]+|[[:space:]]+$', '', 'g'), '') IS NOT NULL
            THEN p.frontmatter->>'message_id' END AS message_id, p.frontmatter->>'thread_id' AS thread_id,
          CASE WHEN NULLIF(regexp_replace(p.frontmatter->>'message_id', '^[[:space:]]+|[[:space:]]+$', '', 'g'), '') IS NOT NULL
            THEN NULLIF(p.frontmatter->>'subject', '') END AS source_subject,
          cc.id as chunk_id, cc.chunk_index, cc.chunk_text, cc.chunk_source,
          (${unverifiedExtractionFragment('p')}) AS unverified_stub,`;

  const joinedCandidates = (exact: boolean) => {
    const relax = relaxed && !exact;
    return `
      WITH hnsw_candidates AS (
        SELECT
          ${candidateColumns}${relax ? `
          ${hashCurrent} AS hash_current,` : ''}
          1 - (cc.${col} <=> ${castSql}) AS raw_score
        ${candidateFrom(relax ? relaxedGeneration : guardedGeneration)}
        ORDER BY ${exact ? '(' : ''}cc.${col} <=> ${castSql}${exact ? ') + 0' : ''}
        LIMIT ${innerLimitParam}
      ),`;
  };

  const indexWalkCandidates = `
      WITH ann AS MATERIALIZED (
        SELECT cc.id, cc.${col} <=> ${castSql} AS distance
        FROM content_chunks cc
        WHERE cc.${col} IS NOT NULL ${modalityFilter}
          ${chunkFilters.join('\n          ')}
          ${relaxedGeneration}
        ORDER BY cc.${col} <=> ${castSql}
        LIMIT ${innerLimitParam}::int * ${overfetch}
      ),
      hnsw_candidates AS (
        SELECT
          ${candidateColumns}
          ${hashCurrent} AS hash_current,
          1 - ann.distance AS raw_score
        FROM ann
        -- Key probes behind OFFSET 0 fences: one chunk and one page lookup per
        -- ANN row, so the plan is bounded by the window whatever the scope
        -- size or page-filter estimate. A plain join let the planner
        -- materialize the whole scope's chunk-page join under the ANN loop
        -- and rescan it per row, spilling past work_mem on large scopes (#6728).
        CROSS JOIN LATERAL (SELECT * FROM content_chunks c WHERE c.id = ann.id OFFSET 0) cc
        CROSS JOIN LATERAL (SELECT ${INDEX_WALK_PAGE_COLUMNS.map(c => `pg.${c}`).join(', ')} FROM pages pg WHERE pg.id = cc.page_id OFFSET 0) p
        CROSS JOIN LATERAL (SELECT src.archived FROM sources src WHERE src.id = p.source_id OFFSET 0) s
        WHERE true
          ${pageFilters.join('\n          ')}
          ${hardExcludeClause}
          ${visibilityClause}
        ORDER BY ann.distance, ann.id
        LIMIT ${innerLimitParam}::int
      ),`;

  const scopeScanCandidates = `
      WITH scope_scan AS MATERIALIZED (
        SELECT cc.id
        FROM content_chunks cc
        WHERE cc.page_id = ANY(ARRAY(
            SELECT p.id
            FROM pages p
            JOIN sources s ON s.id = p.source_id
            WHERE true
              ${pageFilters.join('\n              ')}
              ${hardExcludeClause}
              ${pageVisibilityClause}
          ))
          AND ${chunkGenerationProbe('cc', input.chunkManifest ?? true)}
          AND cc.${col} IS NOT NULL ${modalityFilter}
          ${chunkFilters.join('\n          ')}
          ${relaxedGeneration}
        ORDER BY (cc.${col} <=> ${castSql}) + 0, cc.id
        LIMIT ${innerLimitParam}
      ),
      hnsw_candidates AS (
        SELECT
          ${candidateColumns}
          ${hashCurrent} AS hash_current,
          1 - (cc.${col} <=> ${castSql}) AS raw_score
        FROM scope_scan
        JOIN content_chunks cc ON cc.id = scope_scan.id
        JOIN pages p ON p.id = cc.page_id
      ),`;

  const statement = (candidates: string, relax: boolean) => `${candidates}
      -- score computed as a select-list expr (NOT in the inner ORDER BY, which
      -- must stay pure-distance so the HNSW index is usable).
      scored AS (
        SELECT *, raw_score * ${sourceFactorCaseOnSlug} AS score
        FROM hnsw_candidates${relax ? `
        WHERE ${freshFilter}` : ''}
      ),
      -- Collapse to the best chunk PER PAGE over the full candidate set before
      -- the user LIMIT (shared with the keyword arm in both engines).
      ${buildBestPerPagePoolCte('scored')},
      page_results AS (
      SELECT
        bpp.slug, bpp.page_id, bpp.title, bpp.type, bpp.source_id,
        bpp.effective_date, bpp.effective_date_source,
        bpp.message_id, bpp.thread_id, bpp.source_subject,
        bpp.chunk_id, bpp.chunk_index, bpp.chunk_text, bpp.chunk_source,
        bpp.score,
        ${dialect === 'pglite' ? `CASE WHEN bpp.updated_at < (
          SELECT MAX(te.created_at) FROM timeline_entries te WHERE te.page_id = bpp.page_id
        ) THEN true ELSE false END` : 'false'} AS stale
      FROM best_per_page bpp
      -- v0.41.13: stable tiebreaker for tied scores (planner-independent order).
      ORDER BY bpp.score DESC, bpp.page_id ASC, bpp.chunk_id ASC
      LIMIT ${limitParam}
      OFFSET ${offsetParam}
      )
      SELECT page_results.*, pool.candidate_pool, pool.eligible_pool
      FROM (SELECT count(*)::int AS candidate_pool, ${relax ? `(count(*) FILTER (WHERE ${freshFilter}))::int` : 'count(*)::int'} AS eligible_pool
        FROM hnsw_candidates) pool
      LEFT JOIN page_results ON true
      ORDER BY score DESC NULLS LAST, page_id ASC, chunk_id ASC
    `;

  return {
    sql: statement(joinedCandidates(false), relaxed),
    exactSql: statement(joinedCandidates(true), false),
    hasMoreSql: `SELECT count(*)::int AS eligible FROM (
            SELECT 1 ${candidateFrom(guardedGeneration)} AND $1::text IS NOT NULL LIMIT $${innerLimitIdx + 1}
          ) eligible`,
    params,
    innerLimitIdx,
    innerLimit,
    indexed,
    relaxed,
    indexWalkOverfetch: overfetch,
    ...(walk ? { indexWalkSql: statement(indexWalkCandidates, true) } : {}),
    ...(scopeScan ? { scopeScanSql: statement(scopeScanCandidates, true) } : {}),
  };
}