Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
74.3 KB
Raw Blame
/**
 * Content chunks: one SQL implementation for both engines (refactor wave 1,
 * W1-extended). Statement text is PostgresEngine's master text (SQL-text
 * golden `sql-text/chunks.json`); PGLite runs the same statements
 * (docs/designs/refactor-wave-1/w1-inventory.md).
 *
 * The engine resolves everything this module must not: the source scope
 * (`sourceIds` > `sourceId` > 'default'), the registry-ACTIVE embedding
 * column for the stale / invalidate / read plane (passed as its bare name and
 * quoted here), the RLS scope transaction, `upsertChunks`'s retry + transaction
 * wrapper, the page-state guards (`lockPageKeys` / `readPageSnapshot`, passed
 * in as `ChunkPageGuards`) and the invalidations' `engine.transaction()`.
 * Reads that ran inside `withScopedReadTransaction` on master take
 * `ScopedRead`; the rest take `LegacyUnscopedRead` (EO4 inventory).
 *
 * Driver paths follow master per statement: tagged templates -> `run`
 * (prepared, extended protocol), direct `conn.unsafe` -> `unsafe`, engine
 * `executeRaw` -> `executeRaw`.
 */
import type { PageKey, PageSnapshot, PageSnapshotOptions } from '../page-state/types.ts';
import { assertPageRevision } from '../page-state/types.ts';
import type { BrainEngine } from '../engine.ts';
import type { EmbeddingState, EmbeddingStateCounts, EmbeddingStateRow } from '../embedding-state.ts';
import type { Chunk, ChunkInput, ChunklessPageRow, ResolvedColumn, StaleChunkRow } from '../types.ts';
import { rowToChunk, validateSlug } from '../utils.ts';
import { createHash } from 'node:crypto';
import { jsonRowBatches, sanitizeText } from '../batch-rows.ts';
import {
  normalizeEngineColumn,
  vectorCastSuffix,
  resolveWriteColumnFromConfigRows,
  quoteIdentifier,
  COLUMN_NAME_REGEX,
  EmbeddingColumnNotRegisteredError,
} from '../search/embedding-column.ts';
import { chunkDocumentOrder, chunkGenerationFilter, chunkManifestJoin, chunkVisibleFilter, chunkWriteInvalidations, movableChunkSql, requiresSafeChunks, safeChunksFilter, servedTextProjectionFilter } from '../search/safe-chunks.ts';
import { privatePagesFilterForCaller } from '../search/private-visibility.ts';
import { currentSpaceChunkPredicate, splitEmbeddingSignature, lockEmbeddingSources } from '../embedding-invalidation.ts';
import { EMBED_SKIP_FILTER_FRAGMENT } from '../embed-skip.ts';
import { QUARANTINE_FILTER_FRAGMENT } from '../quarantine.ts';
import { pageBudgetLimitedFilter } from '../page-size-policy.ts';
import { SAFE_FENCE_CHUNKER_VERSION } from '../search/safe-chunks.ts';
import { mapChunkWindowRows, type ChunkWindowOpts, type ChunkWindowPage, type ChunkWindowRequest } from '../search/chunk-windows.ts';
import type { SqlExecutor } from './executor.ts';
import type { LegacyUnscopedRead, ScopedRead } from './brands.ts';
import { joinFragments, renderFragment, sqlFragment, trustedSql, type SqlFragment } from './fragment.ts';
import { pipelined } from '../page-state/transactions.ts';
import { compileRowNormalizer } from './normalize.ts';
import { sealPageTextProjection } from '../page-state/text-seal.ts';

/** The engine's page-state guards, run on the same (transaction) engine. */
export interface ChunkPageGuards {
  lockPageKeys(keys: readonly PageKey[]): Promise<void>;
  readPageSnapshot(slug: string, opts?: PageSnapshotOptions): Promise<PageSnapshot | null>;
  /** #5984: shares a config read across one page transaction (the engine's `transactionMemo`). */
  memo?<T>(key: string, read: () => Promise<T>): Promise<T>;
}

/**
 * The engine's `transaction()`. The invalidations run their source lock and
 * UPDATE through the transaction clone's own `executeRaw`, exactly as master
 * did (raw gauge, and the clone's `executeRaw` is the seam callers observe).
 */
export type ChunkTransactionRunner = <T>(fn: (tx: Pick<BrainEngine, 'executeRaw'>) => Promise<T>) => Promise<T>;

type StaleChunkOpts = { sourceId?: string; signature?: string; includeNullSignature?: boolean };

/** Held or capped large pages (page-embedding-budget.ts) leave the embedding queue while the hold applies. */
const budgetLimited = trustedSql(pageBudgetLimitedFilter('p'));

export async function upsertChunksOnce(
  exec: SqlExecutor,
  guards: ChunkPageGuards,
  slug: string,
  chunks: ChunkInput[],
  opts?: { sourceId?: string; embeddingColumn?: ResolvedColumn; expectedRevision?: string; sealChunkerVersion?: number; pageId?: number; deferSeal?: true },
): Promise<void> {
    const memo = guards.memo ?? (<T>(_key: string, read: () => Promise<T>) => read());
    // Normalize the same way putPage does — pages.slug is stored lowercased,
    // so a raw mixed-case slug here would miss the row it just wrote (#430).
    slug = validateSlug(slug);
    // Compare and persist the same canonical body bytes. JSONB rejects raw
    // NUL/lone surrogates before the INSERT can sanitize them; identity fields
    // remain untouched so malformed identifiers still reject the transaction.
    chunks = chunks.map(chunk => ({ ...chunk, chunk_text: sanitizeText(chunk.chunk_text) }));
    const sourceId = opts?.sourceId ?? 'default';
    await guards.lockPageKeys([{ sourceId, slug }]);
    if (opts?.expectedRevision !== undefined) assertPageRevision(
      await guards.readPageSnapshot(slug, { sourceId }), { expectedRevision: opts.expectedRevision });

    // Source-scope the page-id lookup. Without this filter, multi-source
    // brains where the slug exists in 2+ sources return >1 row and the
    // chunk replacement targets the wrong page (or fans out across pages).
    // #5984: `sealChunkerVersion` is a complete replacement: the caller deleted
    // every chunk of the page earlier in this transaction, so nothing stale can
    // remain, and the seal (which also locks the page row and yields its id)
    // commits only together with the insert below.
    // #5984: a seal whose caller knows the page id (`pageId`, its own write of the page in
    // this transaction) is sent in one pipeline with the insert and checked after both ran.
    const seal = opts?.sealChunkerVersion;
    // The config reads the insert below needs go out with the page lookup (one round trip on Postgres).
    let gatewayModel: string | null = null;
    try {
      // Keep the gateway lazy so module-load failure remains inside this soft
      // fallback boundary; eager evaluation would bypass the config-row fallback.
      const gw = await import('../ai/gateway.ts'); // engine-dynamic-import-ok
      gatewayModel = gw.getEmbeddingModelProvenance();
    } catch {}
    const writesRows = chunks.length > 0;
    // #6712 L6: the column and model keys are one statement (a round trip where pipelining is off).
    const configRows = writesRows && (!opts?.embeddingColumn || !gatewayModel) ? memo('config:embedding-write', async () =>
      (await exec.run<{ key: string; value: string }>(sqlFragment`SELECT key, value FROM config WHERE key IN ('search_embedding_column', 'embedding_columns', 'embedding_model')`)).rows)
      .then(rows => rows, () => null) : undefined;
    const columnRows = opts?.embeddingColumn ? undefined : configRows;
    const modelRows = gatewayModel ? undefined : configRows?.then(rows => rows && rows.filter(row => row.key === 'embedding_model'));
    const sealed = seal !== undefined && opts?.pageId !== undefined && chunks.length > 0 ? opts.pageId : undefined;
    const sealPage = () => exec.run<{ id: number }>(sqlFragment`UPDATE pages SET chunker_version = ${seal} WHERE slug = ${slug} AND source_id = ${sourceId} RETURNING id`);
    const pages = sealed !== undefined ? [{ id: sealed }] : (await (seal === undefined
      ? exec.run<{ id: number }>(sqlFragment`SELECT id FROM pages WHERE slug = ${slug} AND source_id = ${sourceId} FOR UPDATE`)
      : sealPage())).rows;
    if (pages.length === 0) throw new Error(`Page not found: ${slug} (source=${sourceId})`);
    const pageId = pages[0].id;

    if (seal === undefined) {
      // A fragment write cannot certify the full-body fence boundary. Import seals
      // only after its complete replacement succeeds in the same transaction.
      for (const invalidation of chunkWriteInvalidations(pageId, chunks)) {
        if ((await exec.unsafe(invalidation.sql, invalidation.params)).rows.length > 0) break;
      }
      if (chunks.length === 0) {
        await exec.run(sqlFragment`WITH gone AS (DELETE FROM content_chunks WHERE page_id = ${pageId} RETURNING page_id)
          UPDATE pages SET text_projection_revision = NULL WHERE id = (SELECT page_id FROM gone LIMIT 1) AND text_projection_revision IS NOT NULL`);
        return;
      }
    } else if (chunks.length === 0) return;

    // Batch upsert: build a single multi-row INSERT ON CONFLICT statement.
    // v0.19.0: includes language/symbol_name/symbol_type/start_line/end_line
    // so code chunks carry tree-sitter metadata into the DB. Markdown chunks
    // pass NULL for all five.
    // v0.20.0 Cathedral II Layer 6: adds parent_symbol_path / doc_comment /
    // symbol_name_qualified so nested-chunk emission (A3) can round-trip
    // scope metadata through upserts.
    // v0.27.1 (Phase 8): added `modality` + `embedding_image` to the column
    // list. Image chunks pass embedding=null + embedding_image=Float32Array.
    //
    // #1262: the text-embedding column is registry-resolved, not the literal
    // `embedding`. A caller-resolved descriptor wins; otherwise the DB-plane
    // registry rows route the write to the SAME active column the read side
    // searches (a Voyage-routed brain must not fail every write with a
    // dimension mismatch against the legacy 1536d column). Config-table read
    // failure (pre-v36 brain mid-migration) falls back to the legacy column;
    // an unregistered `search_embedding_column` throws the resolver's loud
    // paste-ready hint.
    // Resolution MUST stay on this executor's handle: callers (import-file)
    // invoke this inside their own transaction — re-entering the engine's
    // public surface via resolveActiveEmbeddingColumnFromEngine deadlocks
    // the connection path (PGLite's single connection). Same rows, same pure
    // resolver, no re-entrancy.
    let writeCol: ResolvedColumn;
    if (opts?.embeddingColumn) {
      writeCol = normalizeEngineColumn(opts.embeddingColumn);
    } else {
      let searchEmbeddingColumn: string | null = null;
      let embeddingColumnsJson: string | null = null;
      // A null read (config table unreadable) leaves the legacy column via the resolver default.
      for (const r of await columnRows ?? []) {
        if (r.key === 'search_embedding_column') searchEmbeddingColumn = r.value;
        else if (r.key === 'embedding_columns') embeddingColumnsJson = r.value;
      }
      writeCol = resolveWriteColumnFromConfigRows({ searchEmbeddingColumn, embeddingColumnsJson });
    }
    const writeColId = quoteIdentifier(writeCol.name);
    const writeCast = vectorCastSuffix(writeCol);
    const col = trustedSql(writeColId);
    // Prune once, before any insert batch: a later batch never deletes an earlier one.
    if (seal === undefined) await moveDisplacedChunksAndPrune(exec, pageId, chunks, writeColId);

    // #4246: embedded_text_hash records md5(chunk_text) AT EMBED TIME so a
    // later text rewrite that keeps the vector is detectable as content
    // drift (invalidateContentDriftEmbeddings). NULL when no embedding lands.
    const cols = `(page_id, chunk_index, chunk_text, chunk_source, ${writeColId}, model, token_count, embedded_at, embedded_text_hash, embedding_input_hash, language, symbol_name, symbol_type, start_line, end_line, parent_symbol_path, doc_comment, symbol_name_qualified, modality, embedding_image, embedding_pending_since, projected_byte_start, projected_byte_end)`;
    let resolvedModel: string | null = gatewayModel;
    if (!resolvedModel) resolvedModel = (await modelRows)?.[0]?.value ?? null;
    resolvedModel = writeCol.embeddingModel || resolvedModel;
    if (!resolvedModel && chunks.some(chunk => chunk.embedding && !chunk.model)) {
      throw new Error('Embedding model provenance is unknown. Supply an explicit chunk model or run gbrain migrate embeddings --status before an explicit migration.');
    }
    if (!resolvedModel) resolvedModel = 'unconfigured';

    // #5984: one statement text per brain whatever the chunk count (each batch binds as one
    // JSON document), so a prepared connection pays no describe round trip per page.
    // A3: rows are encoded one bounded batch at a time (jsonRowBatches), never the whole page.
    const batches = jsonRowBatches(chunks, chunk => {
      const embedding = chunk.embedding ? '[' + Array.from(chunk.embedding).join(',') + ']' : null;
      return {
        chunk_index: chunk.chunk_index,
        // Already normalized before the seal snapshot. Both storage and the
        // embedded_text_hash input must use those same canonical bytes.
        chunk_text: chunk.chunk_text,
        chunk_source: chunk.chunk_source ?? null,
        embedding,
        model: chunk.model || resolvedModel,
        token_count: chunk.token_count || null,
        // #5553: embedding-input provenance travels only with the vector it describes.
        embedding_input_hash: embedding ? chunk.embedding_input_hash ?? null : null,
        language: chunk.language || null,
        symbol_name: chunk.symbol_name || null,
        symbol_type: chunk.symbol_type || null,
        start_line: chunk.start_line ?? null,
        end_line: chunk.end_line ?? null,
        parent_symbol_path: chunk.parent_symbol_path && chunk.parent_symbol_path.length > 0 ? chunk.parent_symbol_path : null,
        doc_comment: chunk.doc_comment || null,
        symbol_name_qualified: chunk.symbol_name_qualified || null,
        modality: chunk.modality ?? 'text',
        embedding_image: chunk.embedding_image ? '[' + Array.from(chunk.embedding_image).join(',') + ']' : null,
        projected_byte_start: chunk.projected_byte_start ?? null,
        projected_byte_end: chunk.projected_byte_end ?? null,
      };
    });

    // Single statement upsert: preserves existing embeddings via COALESCE when new value is NULL.
    // CONSISTENCY: when chunk_text changes and no new embedding is supplied, BOTH embedding AND
    // embedded_at must reset to NULL so 'embed --stale' correctly picks up the row for re-embedding.
    // Without this, embedded_at lies (says "embedded" while embedding=NULL), and any staleness
    // predicate on embedded_at would silently skip the row. This is why the egress fix predicates
    // on 'embedding IS NULL' rather than `embedded_at IS NULL` — and it's why we now keep both
    // columns honest at write time.
    //
    // v0.40.3.0 D24 NULL→non-NULL race fix (TODOS.md v0.35.x item).
    // Two writers racing on the same chunk (e.g., autopilot sync + manual
    // 'embed --stale' + contextual reindex) previously raced last-write-wins
    // via `COALESCE(EXCLUDED.embedding, content_chunks.embedding)`. With
    // per-chunk Haiku synopsis the cost of an overwrite jumped from
    // ~$0.000001 to ~$0.0003. New rule for the text-unchanged branch:
    //   - existing is NULL → take new (cold path, no race)
    //   - new is fresher (embedded_at > existing.embedded_at) → take new
    //   - otherwise → keep existing (slower writer with stale embedding loses)
    // Pinned by test/e2e/concurrent-embed-race.test.ts.
    //
    // Code-chunk metadata columns (language / symbol_name / symbol_type / line range /
    // parent_symbol_path / doc_comment / symbol_name_qualified) follow the SAME chunk_text-gated
    // CASE pattern as `embedding` (#769). Re-chunk (chunk_text changed) trusts EXCLUDED outright;
    // pure re-embed (chunk_text unchanged) COALESCEs so a caller that only carries embedding
    // doesn't clobber metadata to NULL. Without this, every embed --stale pass nuked code-def's
    // primary index for thousands of chunks at once.
    //
    // #3461: `model` mirrors the `embedding` CASE branch-for-branch — the label must
    // describe whichever vector WINS the upsert. The old COALESCE(EXCLUDED.model, …)
    // relabeled preserved (older-model) vectors with the current gateway model on every
    // partial re-embed, corrupting provenance without changing the vector.
    //
    // #4246: embedded_text_hash is md5(chunk_text) in SQL (not JS) so stamp + drift
    // comparison share ONE md5 implementation; it, embedded_at and the input hash
    // are set only when the row carries a vector.
    //
    // embedding_pending_since follows the vector that wins: NULL when one
    // lands, kept while the row stays without one, now() when this write
    // NULLs it (doctor ages the embedding backlog from it).
    const insertBatch = (json: string) => renderFragment(sqlFragment`INSERT INTO content_chunks ${trustedSql(cols)}
       SELECT ${pageId}::int, c.chunk_index, c.chunk_text, c.chunk_source, c.embedding${trustedSql(writeCast)}, c.model, c.token_count,
         CASE WHEN c.embedding IS NULL THEN NULL ELSE now() END,
         CASE WHEN c.embedding IS NULL THEN NULL ELSE md5(c.chunk_text) END,
         c.embedding_input_hash, c.language, c.symbol_name, c.symbol_type, c.start_line, c.end_line,
         c.parent_symbol_path, c.doc_comment, c.symbol_name_qualified, c.modality, c.embedding_image::vector,
         CASE WHEN c.embedding IS NULL THEN now() ELSE NULL END,
         c.projected_byte_start, c.projected_byte_end
       FROM jsonb_to_recordset(${json}::text::jsonb) AS c(chunk_index int, chunk_text text, chunk_source text,
         embedding text, model text, token_count int, embedding_input_hash text, language text, symbol_name text, symbol_type text,
         start_line int, end_line int, parent_symbol_path text[], doc_comment text, symbol_name_qualified text, modality text, embedding_image text,
         projected_byte_start bigint, projected_byte_end bigint)
       ON CONFLICT (page_id, chunk_index) DO UPDATE SET
         chunk_text = EXCLUDED.chunk_text,
         chunk_source = EXCLUDED.chunk_source,
         ${col} = CASE
           WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.${col}
           WHEN content_chunks.${col} IS NULL THEN EXCLUDED.${col}
           WHEN EXCLUDED.embedded_at IS NOT NULL
                AND (content_chunks.embedded_at IS NULL OR EXCLUDED.embedded_at > content_chunks.embedded_at)
                THEN EXCLUDED.${col}
           ELSE content_chunks.${col}
         END,
         model = CASE
           WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.model
           WHEN content_chunks.${col} IS NULL THEN EXCLUDED.model
           WHEN EXCLUDED.embedded_at IS NOT NULL
                AND (content_chunks.embedded_at IS NULL OR EXCLUDED.embedded_at > content_chunks.embedded_at)
                THEN EXCLUDED.model
           ELSE content_chunks.model
         END,
         token_count = EXCLUDED.token_count,
         embedded_at = CASE
           WHEN EXCLUDED.chunk_text != content_chunks.chunk_text AND EXCLUDED.${col} IS NULL THEN NULL
           WHEN content_chunks.${col} IS NULL AND EXCLUDED.${col} IS NOT NULL THEN EXCLUDED.embedded_at
           WHEN EXCLUDED.embedded_at IS NOT NULL
                AND (content_chunks.embedded_at IS NULL OR EXCLUDED.embedded_at > content_chunks.embedded_at)
                THEN EXCLUDED.embedded_at
           ELSE content_chunks.embedded_at
         END,
         embedded_text_hash = CASE
           WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.embedded_text_hash
           WHEN content_chunks.${col} IS NULL THEN EXCLUDED.embedded_text_hash
           WHEN EXCLUDED.embedded_at IS NOT NULL
                AND (content_chunks.embedded_at IS NULL OR EXCLUDED.embedded_at > content_chunks.embedded_at)
                THEN EXCLUDED.embedded_text_hash
           ELSE content_chunks.embedded_text_hash
         END,
         embedding_input_hash = CASE
           WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.embedding_input_hash
           WHEN content_chunks.${col} IS NULL THEN EXCLUDED.embedding_input_hash
           WHEN EXCLUDED.embedded_at IS NOT NULL
                AND (content_chunks.embedded_at IS NULL OR EXCLUDED.embedded_at > content_chunks.embedded_at)
                THEN EXCLUDED.embedding_input_hash
           ELSE content_chunks.embedding_input_hash
         END,
         language = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.language ELSE COALESCE(EXCLUDED.language, content_chunks.language) END,
         symbol_name = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.symbol_name ELSE COALESCE(EXCLUDED.symbol_name, content_chunks.symbol_name) END,
         symbol_type = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.symbol_type ELSE COALESCE(EXCLUDED.symbol_type, content_chunks.symbol_type) END,
         start_line = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.start_line ELSE COALESCE(EXCLUDED.start_line, content_chunks.start_line) END,
         end_line = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.end_line ELSE COALESCE(EXCLUDED.end_line, content_chunks.end_line) END,
         parent_symbol_path = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.parent_symbol_path ELSE COALESCE(EXCLUDED.parent_symbol_path, content_chunks.parent_symbol_path) END,
         doc_comment = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.doc_comment ELSE COALESCE(EXCLUDED.doc_comment, content_chunks.doc_comment) END,
         symbol_name_qualified = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.symbol_name_qualified ELSE COALESCE(EXCLUDED.symbol_name_qualified, content_chunks.symbol_name_qualified) END,
         modality = EXCLUDED.modality,
         projected_byte_start = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.projected_byte_start ELSE COALESCE(EXCLUDED.projected_byte_start, content_chunks.projected_byte_start) END,
         projected_byte_end = CASE WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN EXCLUDED.projected_byte_end ELSE COALESCE(EXCLUDED.projected_byte_end, content_chunks.projected_byte_end) END,
         embedding_image = COALESCE(EXCLUDED.embedding_image, content_chunks.embedding_image),
         embedding_pending_since = CASE
           WHEN EXCLUDED.${col} IS NOT NULL THEN NULL
           WHEN content_chunks.${col} IS NULL THEN COALESCE(content_chunks.embedding_pending_since, now())
           WHEN EXCLUDED.chunk_text != content_chunks.chunk_text THEN now()
           ELSE content_chunks.embedding_pending_since
         END`);
    const write = (json: string) => { const { text, params } = insertBatch(json); return exec.query(text, params); };
    // Every batch runs in the caller's transaction, so a failure in any batch rolls back the whole replacement.
    const first = batches.next().value as string;
    if (sealed === undefined || opts?.deferSeal) await write(first);
    else {
      const [{ rows }] = await pipelined({ kind: exec.dialect }, [sealPage, () => write(first)]) as [{ rows: Array<{ id: number }> }];
      if (rows.length === 0) throw new Error(`Page not found: ${slug} (source=${sourceId})`);
      if (Number(rows[0]!.id) !== Number(sealed)) throw new Error(`Page ${slug} (source=${sourceId}) is not the page this transaction wrote`);
    }
    for (const json of batches) await write(json);
  }

/**
 * A4: content-keyed reuse for a write that replaces a page's chunks in place
 * (the rebuild paths), then the one prune of the write. A stored row follows
 * its exact chunk (source and text), not its index: each movable row whose
 * chunk the new set holds at another index, where that index does not already
 * hold it, moves there with its id, vector, metadata, timestamps and #5553
 * input provenance, so a top insert keeps the vectors (and code edges) of
 * shifted chunks instead of re-embedding them. A legacy vector without a text
 * hash never moves. Rows park at negative indices, rows that
 * left the set or sit on a move target are deleted, and the parked rows land;
 * the insert batches then find their text unchanged and keep the vector under
 * the D24 rule. Validity against the page context stays with the caller
 * (installPageProjection nulls vectors whose recorded input no longer matches
 * before it writes; the recorded input does not depend on the index).
 */
async function moveDisplacedChunksAndPrune(exec: SqlExecutor, pageId: number, chunks: ChunkInput[], writeColId: string): Promise<void> {
  const newIndices = chunks.map(c => c.chunk_index);
  // chunk-visibility-ok: inline projection write path (upsertChunks); segmented pages stage rows through stageSegmentChunks
  const { rows } = await exec.run<{ id: number; chunk_index: number; chunk_source: string | null; text_md5: string; movable: boolean }>(sqlFragment`
    SELECT id, chunk_index, chunk_source, md5(chunk_text) AS text_md5, ${trustedSql(movableChunkSql(writeColId))} AS movable
    FROM content_chunks WHERE page_id = ${pageId} ORDER BY chunk_index`);
  const key = (source: string | null | undefined, md5: string) => JSON.stringify([source ?? null, md5]);
  const incoming = new Map(rows.some(r => r.movable) ? chunks.map(c => [c.chunk_index, key(c.chunk_source, createHash('md5').update(c.chunk_text).digest('hex'))]) : []);
  const candidates = new Map<string, Array<{ id: number }>>();
  for (const row of rows.filter(r => r.movable)) {
    const own = key(row.chunk_source, row.text_md5);
    if (incoming.get(Number(row.chunk_index)) === own) continue;
    const queue = candidates.get(own);
    if (queue) queue.push({ id: Number(row.id) });
    else candidates.set(own, [{ id: Number(row.id) }]);
  }
  const held = new Map(rows.map(row => [Number(row.chunk_index), key(row.chunk_source, row.text_md5)]));
  const ids: number[] = [];
  const targets: number[] = [];
  for (const [index, wanted] of incoming) {
    if (held.get(index) === wanted) continue;
    const row = candidates.get(wanted)?.shift();
    if (row) { ids.push(row.id); targets.push(index); }
  }
  if (ids.length === 0) {
    await exec.run(sqlFragment`DELETE FROM content_chunks WHERE page_id = ${pageId} AND chunk_index != ALL(${newIndices})`);
    return;
  }
  await exec.run(sqlFragment`UPDATE content_chunks c SET chunk_index = -1 - m.target
    FROM unnest(${ids}::int[], ${targets}::int[]) AS m(id, target) WHERE c.page_id = ${pageId} AND c.id = m.id`);
  await exec.run(sqlFragment`DELETE FROM content_chunks WHERE page_id = ${pageId} AND chunk_index >= 0
    AND (chunk_index != ALL(${newIndices}) OR chunk_index = ANY(${targets}::int[]))`);
  await exec.run(sqlFragment`UPDATE content_chunks SET chunk_index = -1 - chunk_index WHERE page_id = ${pageId} AND chunk_index < 0`);
}

/** The by-slug chunk read predicate shared by getChunks and getChunkPage: scope, privacy, sealed text and live body generation, safe chunks. */
function chunkReadWhere(
  slug: string,
  scope: { sourceIds?: string[]; sourceId: string },
  opts?: { excludePrivate?: boolean; requireSafeChunks?: boolean; includeUnsealed?: boolean; chunkManifest?: boolean },
): SqlFragment {
  const scopeSql = scope.sourceIds
    ? sqlFragment`p.source_id = ANY(${scope.sourceIds}::text[])`
    : sqlFragment`p.source_id = ${scope.sourceId}`;
  return sqlFragment`p.slug = ${slug} AND ${scopeSql}
          ${opts?.excludePrivate ? trustedSql(`AND ${privatePagesFilterForCaller('p')}`) : sqlFragment``}
          AND ${trustedSql(opts?.includeUnsealed ? chunkGenerationFilter('p', 'cc', opts.chunkManifest ?? true) : chunkVisibleFilter('p', 'cc'))}
          ${requiresSafeChunks(opts) ? trustedSql(`AND ${safeChunksFilter('p')}`) : sqlFragment``}`;
}

export async function getChunks(
  exec: ScopedRead,
  column: string,
  slug: string,
  scope: { sourceIds?: string[]; sourceId: string },
  opts?: { includeEmbedding?: boolean; excludePrivate?: boolean; requireSafeChunks?: boolean; includeUnsealed?: boolean; chunkManifest?: boolean; indexRange?: { from: number; to: number; ordinal?: boolean } },
): Promise<Chunk[]> {
      const colId = trustedSql(quoteIdentifier(column));
      const includeEmbedding = opts?.includeEmbedding === true;
      // #2544: explicit non-vector column list — most callers discard
      // embeddings, so `cc.*` shipped every vector over the wire only to be
      // thrown away. `includeEmbedding` adds it back for the callers that
      // consume it (embed-reuse.ts); it selects the registry-ACTIVE column
      // (aliased AS embedding) so a reused vector always matches the column
      // upsertChunks writes.
      // embedding_is_null: boolean truth of the stored vector (a schema
      // rebuild NULLs vectors without touching embedded_at). S2: it reports
      // the registry-ACTIVE column's truth — `embed <page>` filters on it.
      const embedCol = includeEmbedding ? sqlFragment`, cc.${colId} AS embedding` : sqlFragment``;
      const columns = sqlFragment`cc.id, cc.page_id, cc.chunk_index, cc.chunk_text, cc.chunk_source,
               cc.model, cc.token_count, cc.embedded_at, cc.language,
               cc.symbol_name, cc.symbol_type, cc.start_line, cc.end_line,
               cc.parent_symbol_path, cc.doc_comment, cc.symbol_name_qualified, cc.modality,
               cc.projected_byte_start, cc.projected_byte_end,
               (cc.${colId} IS NULL) AS embedding_is_null
               ${embedCol}`;
      const from = sqlFragment`FROM content_chunks cc
        JOIN pages p ON p.id = cc.page_id
        ${trustedSql(chunkManifestJoin('p', 'cc', 'm', opts?.chunkManifest ?? true))}
        WHERE ${chunkReadWhere(slug, scope, opts)}`;
      const order = trustedSql(chunkDocumentOrder('cc', 'm'));
      const range = opts?.indexRange;
      if (range?.ordinal) {
        const { rows } = await exec.run(sqlFragment`
          SELECT * FROM (
            SELECT ${columns},
                   (row_number() OVER (PARTITION BY cc.page_id ORDER BY ${order}) - 1)::int AS page_position,
                   row_number() OVER (ORDER BY ${order}) AS document_position
            ${from}
          ) r
          WHERE r.page_position >= ${range.from} AND r.page_position < ${range.to}
          ORDER BY r.document_position
        `);
        return rows.map((r) => rowToChunk(r, includeEmbedding));
      }
      const { rows } = await exec.run(sqlFragment`
        SELECT ${columns}
        ${from}
          ${range ? sqlFragment`AND cc.chunk_index >= ${range.from} AND cc.chunk_index < ${range.to}` : sqlFragment``}
        ORDER BY ${order}
      `);
      return rows.map((r) => rowToChunk(r, includeEmbedding));
  }

/**
 * One page of a page's chunks for get_chunks: the chunks whose chunk_index
 * falls in [fromIndex, fromIndex + limit) under the same predicate getChunks
 * applies, plus the count of every chunk that predicate admits and the
 * highest chunk_index, so the caller knows whether more remain. A range,
 * not a row LIMIT, so a slug that matches one page in each of two federated
 * sources never splits a chunk_index between two calls. A segmented page's
 * chunk_index is an insertion sequence, so its range is over positions in
 * document order instead (`fromIndex` and `max_chunk_index` are positions).
 */
export async function getChunkPage(
  exec: ScopedRead,
  column: string,
  slug: string,
  scope: { sourceIds?: string[]; sourceId: string },
  opts: { fromIndex: number; limit: number; excludePrivate?: boolean; requireSafeChunks?: boolean; chunkManifest?: boolean },
): Promise<{ chunks: Chunk[]; total_chunks: number; max_chunk_index: number | null }> {
      const { rows } = await exec.run<{ total: number | string; max_index: number | null; segmented: boolean | null; max_page_chunks: number | string | null }>(sqlFragment`
        SELECT COALESCE(sum(t.n), 0) AS total, max(t.max_index) AS max_index, bool_or(t.segmented) AS segmented, max(t.n) AS max_page_chunks
        FROM (
          SELECT count(*) AS n, max(cc.chunk_index) AS max_index, bool_or(cc.body_generation IS NOT NULL) AS segmented
          FROM content_chunks cc
          JOIN pages p ON p.id = cc.page_id
          WHERE ${chunkReadWhere(slug, scope, opts)}
          GROUP BY cc.page_id
        ) t
      `);
      const segmented = rows[0]?.segmented === true;
      const chunks = await getChunks(exec, column, slug, scope, { ...opts, indexRange: { from: opts.fromIndex, to: opts.fromIndex + opts.limit, ordinal: segmented } });
      const maxIndex = segmented ? Number(rows[0]?.max_page_chunks) - 1 : rows[0]?.max_index == null ? null : Number(rows[0].max_index);
      return { chunks, total_chunks: Number(rows[0]?.total ?? 0), max_chunk_index: maxIndex };
  }

/**
 * Stale-chunk WHERE clause over the registry-ACTIVE embedding column
 * (`cc."<name>"`, S2 unification — a registry-routed brain's staleness
 * lives in the active column, never the literal legacy `cc.embedding`).
 * embed_skip always excluded. `signature` widens "stale" to include
 * embedding_signature drift (NULL grandfathered). `includeNullSignature`
 * (#3391) lifts the grandfather clause so pre-stamp pages count as stale
 * too (provider-migration paths). Shared by countStaleChunks +
 * sumStaleChunkChars so they can't drift. #8811: `arm` narrows it to one of
 * its two disjoint halves, an unembedded chunk or an embedded chunk of a page
 * whose signature drifted.
 */
function staleChunkWhere(column: string, opts?: StaleChunkOpts, arm?: 'unembedded' | 'drifted'): SqlFragment {
  const staleColRef = trustedSql(`cc.${quoteIdentifier(column)}`);
  const conds: SqlFragment[] = [sqlFragment`p.deleted_at IS NULL`, sqlFragment`${trustedSql(chunkGenerationFilter('p', 'cc'))}`];
  if (arm === 'unembedded') {
    conds.push(sqlFragment`${staleColRef} IS NULL`);
  } else if (arm === 'drifted') {
    conds.push(
      opts?.includeNullSignature
        ? sqlFragment`${staleColRef} IS NOT NULL AND (p.embedding_signature IS NULL OR p.embedding_signature <> ${opts.signature})`
        : sqlFragment`${staleColRef} IS NOT NULL AND p.embedding_signature <> ${opts?.signature}`,
    );
  } else if (opts?.signature !== undefined) {
    conds.push(
      opts.includeNullSignature
        ? sqlFragment`(${staleColRef} IS NULL OR p.embedding_signature IS NULL OR p.embedding_signature <> ${opts.signature})`
        : sqlFragment`(${staleColRef} IS NULL OR (p.embedding_signature IS NOT NULL AND p.embedding_signature <> ${opts.signature}))`,
    );
  } else {
    conds.push(sqlFragment`${staleColRef} IS NULL`);
  }
  conds.push(sqlFragment`NOT (COALESCE(p.frontmatter, '{}'::jsonb) ? 'embed_skip')`);
  // Large pages held for approval or at their spend cap (page-embedding-budget.ts) leave the queue until it changes or the page is no longer large.
  conds.push(sqlFragment`NOT ${budgetLimited}`);
  if (opts?.sourceId !== undefined) conds.push(sqlFragment`p.source_id = ${opts.sourceId}`);
  return joinFragments(conds, ' AND ');
}

export async function countStaleChunks(exec: ScopedRead, column: string, opts?: StaleChunkOpts): Promise<number> {
    // Always JOIN pages so the embed_skip + signature predicates apply.
    // D7: source_id scoping. v0.41.31: optional signature widens staleness
    // to embedding_signature drift (NULL grandfathered unless
    // includeNullSignature, #3391).
    const { text, params } = renderFragment(sqlFragment`SELECT count(*)::int AS count
           FROM content_chunks cc
           JOIN pages p ON p.id = cc.page_id
          WHERE ${staleChunkWhere(column, opts)}`);
    const { rows } = await exec.unsafe<{ count?: number }>(text, params);
    return Number(rows[0]?.count ?? 0);
  }

export async function sumStaleChunkChars(exec: LegacyUnscopedRead, column: string, opts?: StaleChunkOpts): Promise<number> {
    // Sibling of countStaleChunks: same stale predicate, summing chunk_text
    // length for the sync cost preview. ::bigint guards int4 overflow.
    // #8811: with a signature the predicate ORs a chunk column with a page
    // column, which no index serves, so every content_chunks row was read on
    // each sync. Its two disjoint halves are summed apart instead: unembedded
    // chunks through the partial stale index, drifted pages' chunks by page.
    const stale = (arm?: 'unembedded' | 'drifted') => sqlFragment`SELECT LENGTH(cc.chunk_text) AS chars
         FROM content_chunks cc
         JOIN pages p ON p.id = cc.page_id
        WHERE ${staleChunkWhere(column, opts, arm)}`;
    const { text, params } = renderFragment(sqlFragment`SELECT COALESCE(SUM(chars), 0)::bigint AS chars FROM (${
      opts?.signature === undefined ? stale() : sqlFragment`${stale('unembedded')} UNION ALL ${stale('drifted')}`}) stale`);
    const { rows } = await exec.unsafe<{ chars?: number | string }>(text, params);
    return Number(rows[0]?.chars ?? 0);
  }

export async function setPageEmbeddingSignature(exec: SqlExecutor, slug: string, opts: { sourceId?: string; signature: string }): Promise<void> {
    await exec.run(sqlFragment`
      UPDATE pages SET embedding_signature = ${opts.signature}
      WHERE slug = ${slug} AND source_id = ${opts.sourceId ?? 'default'}
    `);
  }

/**
 * `currentSpaceChunkPredicate` (embedding-invalidation.ts) as a fragment: built from its text, with values bound in
 * place of its `$model` / `$dims` parameters, so the two cannot drift. `test/engine-sql-chunks.test.ts` pins them.
 */
export function currentSpaceChunkFragment(column: string, model: string, dims: number | null): SqlFragment {
  const strings = currentSpaceChunkPredicate(quoteIdentifier(column), 9001, 9002).split(/\$900[12]/);
  return sqlFragment(Object.assign([...strings], { raw: [...strings] }), model, dims);
}

export async function invalidateStaleSignatureEmbeddings(
  inTransaction: ChunkTransactionRunner,
  column: string,
  opts: { signature: string; sourceId?: string; includeNullSignature?: boolean },
): Promise<number> {
    const colId = trustedSql(quoteIdentifier(column));
    const { model, dims } = splitEmbeddingSignature(opts.signature);
    const srcClause = opts.sourceId !== undefined ? sqlFragment` AND p.source_id = ${opts.sourceId}` : sqlFragment``;
    const sigClause = opts.includeNullSignature
      ? sqlFragment`(p.embedding_signature IS NULL OR p.embedding_signature <> ${opts.signature})`
      : sqlFragment`p.embedding_signature IS NOT NULL
          AND p.embedding_signature <> ${opts.signature}`;
    return inTransaction(async tx => {
      const sources = await lockEmbeddingSources(tx, opts.sourceId);
      const { text, params } = renderFragment(sqlFragment`UPDATE content_chunks cc
            SET ${colId} = NULL, embedded_at = NULL, embedding_pending_since = now()
           FROM pages p
          WHERE cc.page_id = p.id
            AND p.deleted_at IS NULL
            AND p.source_id=ANY(${sources}::text[])
            AND EXISTS (SELECT 1 FROM sources s WHERE s.id=p.source_id AND NOT s.archived)
            AND cc.${colId} IS NOT NULL
            AND NOT ${currentSpaceChunkFragment(column, model, dims)}
            AND ${sigClause}${srcClause}
          RETURNING cc.page_id`);
      return (await tx.executeRaw(text, params)).length;
    });
  }

/** Chunk rows one content-drift batch examines; each batch is one short statement. */
export const CONTENT_DRIFT_BATCH_ROWS = 5_000;

export interface ContentDriftBatch {
  /** Candidate rows the batch examined (at most `limit`). */
  checked: number;
  /** Rows whose vector it invalidated. */
  drifted: number;
  /** Highest chunk id it examined; null when no row lies past `afterChunkId`. */
  lastChunkId: number | null;
}

/**
 * #4246 / #7117: one bounded step of the content-drift sweep. NULL the
 * embeddings whose stored embed-time hash no longer matches md5(chunk_text)
 * (the vector was computed from a PREVIOUS content revision), among the
 * `limit` chunk rows with the lowest ids above `afterChunkId`. The candidate
 * set is bounded by the primary key before any hash is computed, so a batch
 * costs the same on a healthy corpus as on a drifted one. Feeds the
 * NULL-embedding cursor (mirrors the signature invalidation above).
 * GRANDFATHER: NULL hash (pre-v133 rows) untouched so upgrades don't trigger
 * a corpus-wide re-embed spike. embed_skip pages excluded: the stale
 * selectors can't re-embed them, so NULLing would strand them. S2: keyed on
 * the registry-ACTIVE column (the caller resolves it with the loud resolver).
 * The cursor and limit are inlined, not bound, so a prepared statement never
 * switches to a generic plan that scans the whole table (migration-preludes.ts).
 */
export async function invalidateContentDriftBatch(
  inTransaction: ChunkTransactionRunner,
  column: string,
  opts: { afterChunkId: number; limit: number; sourceId?: string },
): Promise<ContentDriftBatch> {
    const after = Math.trunc(opts.afterChunkId);
    const limit = Math.trunc(opts.limit);
    if (!Number.isFinite(after) || !Number.isFinite(limit) || after < 0 || limit < 1) {
      throw new Error(`invalid content-drift batch window: after ${opts.afterChunkId}, limit ${opts.limit}`);
    }
    const colId = trustedSql(quoteIdentifier(column));
    const window = trustedSql(`id > ${after} ORDER BY id LIMIT ${limit}`);
    const srcClause = opts.sourceId !== undefined ? sqlFragment` AND p.source_id = ${opts.sourceId}` : sqlFragment``;
    return inTransaction(async tx => {
      const sources = await lockEmbeddingSources(tx, opts.sourceId);
      // chunk-visibility-ok: the drift sweep visits every chunk row by id; the UPDATE's predicates decide eligibility
      const { text, params } = renderFragment(sqlFragment`WITH b AS (SELECT id FROM content_chunks WHERE ${window}),
             w AS (UPDATE content_chunks cc
            SET ${colId} = NULL, embedded_at = NULL, embedded_text_hash = NULL, embedding_pending_since = now()
           FROM b, pages p
          WHERE cc.id = b.id
            AND cc.page_id = p.id
            AND p.source_id=ANY(${sources}::text[])
            AND EXISTS (SELECT 1 FROM sources s WHERE s.id=p.source_id AND NOT s.archived)
            AND p.deleted_at IS NULL
            AND cc.${colId} IS NOT NULL
            AND cc.embedded_text_hash IS NOT NULL
            AND cc.embedded_text_hash <> md5(cc.chunk_text)
            AND NOT (COALESCE(p.frontmatter, '{}'::jsonb) ? 'embed_skip') AND NOT ${budgetLimited}${srcClause}
          RETURNING 1)
        SELECT (SELECT max(id) FROM b) AS last_id, (SELECT count(*) FROM b)::int AS checked, (SELECT count(*) FROM w)::int AS drifted`);
      const [row] = await tx.executeRaw<{ last_id: number | string | null; checked: number | string; drifted: number | string }>(text, params);
      return {
        checked: Number(row?.checked ?? 0),
        drifted: Number(row?.drifted ?? 0),
        lastChunkId: row?.last_id == null ? null : Number(row.last_id),
      };
    });
  }

/**
 * A whole content-drift pass with no saved cursor, as consecutive bounded
 * batches (`invalidateContentDriftBatch`), each in its own transaction from
 * `inTransaction`; a batch shorter than its limit ends the pass. Returns the
 * chunk count invalidated. The embed paths run the
 * resumable, budgeted pass in content-drift-sweep.ts instead.
 */
export async function invalidateContentDriftEmbeddings(
  inTransaction: ChunkTransactionRunner,
  column: string,
  opts?: { sourceId?: string },
): Promise<number> {
    let afterChunkId = 0;
    let drifted = 0;
    for (;;) {
      const batch = await invalidateContentDriftBatch(inTransaction, column, { ...opts, afterChunkId, limit: CONTENT_DRIFT_BATCH_ROWS });
      drifted += batch.drifted;
      if (batch.lastChunkId === null || batch.checked < CONTENT_DRIFT_BATCH_ROWS) return drifted;
      afterChunkId = batch.lastChunkId;
    }
  }

export async function listStaleChunks(exec: ScopedRead, column: string, opts?: {
  batchSize?: number;
  afterPageId?: number;
  afterChunkIndex?: number;
  sourceId?: string;
  orderBy?: 'page_id' | 'updated_desc';
  afterUpdatedAt?: string | null;
}): Promise<StaleChunkRow[]> {
    const limit = opts?.batchSize ?? 2000;
    const afterPid = opts?.afterPageId ?? 0;
    const afterIdx = opts?.afterChunkIndex ?? -1;
    const orderBy = opts?.orderBy ?? 'page_id';
    // S2: stale = NULL in the registry-ACTIVE column (resolved by the engine
    // BEFORE the scoped transaction, falling back to legacy on a broken registry).
    const staleColId = trustedSql(quoteIdentifier(column));
    const read = async (fragment: SqlFragment) => (await exec.run(fragment)).rows as unknown as StaleChunkRow[];
    const generation = trustedSql(chunkGenerationFilter('p', 'cc'));

      // v0.41.18.0 (A13, codex #9): --priority recent path. Composite cursor
      // (updated_at DESC NULLS LAST, page_id ASC, chunk_index ASC). Backed by
      // idx_pages_updated_at_desc + content_chunks_stale_idx partial.
      if (orderBy === 'updated_desc') {
        const afterUpdated = opts?.afterUpdatedAt ?? null;
        const isFirstPage = afterUpdated === null && afterPid === 0;
        if (opts?.sourceId === undefined) {
          return isFirstPage ? read(sqlFragment`
            SELECT p.slug, cc.chunk_index, cc.chunk_text, cc.chunk_source,
                   cc.model, cc.token_count, p.source_id, cc.page_id,
                   p.updated_at
            FROM content_chunks cc
            JOIN pages p ON p.id = cc.page_id
            WHERE cc.${staleColId} IS NULL AND p.deleted_at IS NULL
              AND NOT (COALESCE(p.frontmatter, '{}'::jsonb) ? 'embed_skip') AND NOT ${budgetLimited}
              AND ${generation}
            ORDER BY p.updated_at DESC NULLS LAST, p.id ASC, cc.chunk_index ASC
            LIMIT ${limit}
          `) : read(sqlFragment`
            SELECT p.slug, cc.chunk_index, cc.chunk_text, cc.chunk_source,
                   cc.model, cc.token_count, p.source_id, cc.page_id,
                   p.updated_at
            FROM content_chunks cc
            JOIN pages p ON p.id = cc.page_id
            WHERE cc.${staleColId} IS NULL AND p.deleted_at IS NULL
              AND NOT (COALESCE(p.frontmatter, '{}'::jsonb) ? 'embed_skip') AND NOT ${budgetLimited}
              AND ${generation}
              AND (
                p.updated_at < ${afterUpdated}::timestamptz
                OR (p.updated_at = ${afterUpdated}::timestamptz AND p.id > ${afterPid})
                OR (p.updated_at = ${afterUpdated}::timestamptz AND p.id = ${afterPid} AND cc.chunk_index > ${afterIdx})
              )
            ORDER BY p.updated_at DESC NULLS LAST, p.id ASC, cc.chunk_index ASC
            LIMIT ${limit}
          `);
        }
        return isFirstPage ? read(sqlFragment`
          SELECT p.slug, cc.chunk_index, cc.chunk_text, cc.chunk_source,
                 cc.model, cc.token_count, p.source_id, cc.page_id,
                 p.updated_at
          FROM content_chunks cc
          JOIN pages p ON p.id = cc.page_id
          WHERE cc.${staleColId} IS NULL AND p.deleted_at IS NULL
            AND p.source_id = ${opts.sourceId}
            AND NOT (COALESCE(p.frontmatter, '{}'::jsonb) ? 'embed_skip') AND NOT ${budgetLimited}
              AND ${generation}
          ORDER BY p.updated_at DESC NULLS LAST, p.id ASC, cc.chunk_index ASC
          LIMIT ${limit}
        `) : read(sqlFragment`
          SELECT p.slug, cc.chunk_index, cc.chunk_text, cc.chunk_source,
                 cc.model, cc.token_count, p.source_id, cc.page_id,
                 p.updated_at
          FROM content_chunks cc
          JOIN pages p ON p.id = cc.page_id
          WHERE cc.${staleColId} IS NULL AND p.deleted_at IS NULL
            AND p.source_id = ${opts.sourceId}
            AND NOT (COALESCE(p.frontmatter, '{}'::jsonb) ? 'embed_skip') AND NOT ${budgetLimited}
              AND ${generation}
            AND (
              p.updated_at < ${afterUpdated}::timestamptz
              OR (p.updated_at = ${afterUpdated}::timestamptz AND p.id > ${afterPid})
              OR (p.updated_at = ${afterUpdated}::timestamptz AND p.id = ${afterPid} AND cc.chunk_index > ${afterIdx})
            )
          ORDER BY p.updated_at DESC NULLS LAST, p.id ASC, cc.chunk_index ASC
          LIMIT ${limit}
        `);
      }
      // orderBy === 'page_id' — legacy stable cursor.
      if (opts?.sourceId === undefined) {
        return read(sqlFragment`
          SELECT p.slug, cc.chunk_index, cc.chunk_text, cc.chunk_source,
                 cc.model, cc.token_count, p.source_id, cc.page_id
          FROM content_chunks cc
          JOIN pages p ON p.id = cc.page_id
          WHERE cc.${staleColId} IS NULL AND p.deleted_at IS NULL
            AND NOT (COALESCE(p.frontmatter, '{}'::jsonb) ? 'embed_skip') AND NOT ${budgetLimited}
              AND ${generation}
            AND (cc.page_id, cc.chunk_index) > (${afterPid}, ${afterIdx})
          ORDER BY cc.page_id, cc.chunk_index
          LIMIT ${limit}
        `);
      }
      return read(sqlFragment`
        SELECT p.slug, cc.chunk_index, cc.chunk_text, cc.chunk_source,
               cc.model, cc.token_count, p.source_id, cc.page_id
        FROM content_chunks cc
        JOIN pages p ON p.id = cc.page_id
        WHERE cc.${staleColId} IS NULL AND p.deleted_at IS NULL
          AND p.source_id = ${opts.sourceId}
          AND NOT (COALESCE(p.frontmatter, '{}'::jsonb) ? 'embed_skip') AND NOT ${budgetLimited}
              AND ${generation}
          AND (cc.page_id, cc.chunk_index) > (${afterPid}, ${afterIdx})
        ORDER BY cc.page_id, cc.chunk_index
        LIMIT ${limit}
      `);
  }

/**
 * Shared chunkless-page-with-content predicate. Excludes quarantined pages,
 * which are chunkless by design, and pages whose current revision's text
 * projection is sealed (#8473): the writer chunked that revision to nothing,
 * as for a body holding only a protected takes fence, so a heal cannot
 * produce a chunk. Deleting a page's chunks clears its seal (deleteChunks, an
 * empty fragment replacement), so a page emptied after sealing stays healable. An embed_skip page is not excluded: every write keeps
 * its chunks for keyword search (#6722), so one with none (an older gbrain's
 * size skip, or a backfill rewrite that never landed) is drift the safety net
 * chunks; its chunks get no vectors (#7174).
 */
function chunklessPagesWhere(opts?: { sourceId?: string }): SqlFragment {
  const conds: SqlFragment[] = [
    sqlFragment`p.deleted_at IS NULL`,
    // healChunklessPages chunks BOTH compiled_truth and timeline (mirrors
    // embedPage) — a timeline-only page (rare but schema-legal) has
    // something to heal even with compiled_truth = ''. A segmented page's
    // body columns are NULL; its body_bytes says it has one.
    sqlFragment`(p.compiled_truth <> '' OR p.timeline <> '' OR (p.body_storage = 'segmented' AND p.body_bytes > 0))`,
    sqlFragment`${trustedSql(QUARANTINE_FILTER_FRAGMENT)}`,
    sqlFragment`(p.text_projection_revision IS DISTINCT FROM p.knowledge_revision OR COALESCE(p.frontmatter, '{}'::jsonb) ? 'embed_skip')`,
    sqlFragment`NOT EXISTS (SELECT 1 FROM content_chunks cc WHERE cc.page_id = p.id)`,
  ];
  if (opts?.sourceId) conds.push(sqlFragment`p.source_id = ${opts.sourceId}`);
  return joinFragments(conds, ' AND ');
}

export async function countChunklessPagesWithContent(exec: ScopedRead, opts?: { sourceId?: string }): Promise<number> {
    const { text, params } = renderFragment(sqlFragment`SELECT count(*)::int AS count FROM pages p WHERE ${chunklessPagesWhere(opts)}`);
    const { rows } = await exec.unsafe<{ count?: number }>(text, params);
    return Number(rows[0]?.count ?? 0);
  }

export async function listChunklessPagesWithContent(exec: ScopedRead, opts?: {
  batchSize?: number;
  afterPageId?: number;
  sourceId?: string;
}): Promise<ChunklessPageRow[]> {
    const afterClause = opts?.afterPageId != null ? sqlFragment` AND p.id > ${opts.afterPageId}` : sqlFragment``;
    // Small default (unlike the 2000-row chunk-metadata cursors elsewhere):
    // each row here carries a FULL page body. See engine.ts docstring.
    const limit = opts?.batchSize ?? 50;
    const { text, params } = renderFragment(sqlFragment`SELECT p.id, p.slug, p.source_id, p.compiled_truth, p.timeline
           FROM pages p
          WHERE ${chunklessPagesWhere(opts)}${afterClause}
          ORDER BY p.id
          LIMIT ${limit}`);
    const { rows } = await exec.unsafe(text, params);
    return rows.map(r => ({
      id: r.id as number,
      slug: r.slug as string,
      source_id: (r.source_id as string | undefined) ?? 'default',
      compiled_truth: (r.compiled_truth as string | null) ?? '',
      timeline: (r.timeline as string | null) ?? '',
    }));
  }

/** Every embedding state, in report order (re-exported by src/core/embedding-state.ts). */
export const EMBEDDING_STATES: readonly EmbeddingState[] = ['embedded', 'pending', 'keyword_only', 'quarantined', 'empty', 'deleted_page_chunks', 'chunkless_healable'];

/**
 * #6696 B3 embedding-state classifier (contract in src/core/embedding-state.ts).
 * A chunk's state, by precedence: soft-deleted page, quarantined page, a
 * vector in the ACTIVE column, the embed job's own predicate
 * (`staleChunkWhere`, so `pending` can never drift from what `embed --stale`
 * selects), else a vector-less chunk on an embed_skip page.
 */
function chunkEmbeddingState(column: string): SqlFragment {
  const colRef = trustedSql(`cc.${quoteIdentifier(column)}`);
  return sqlFragment`CASE
      WHEN p.deleted_at IS NOT NULL THEN 'deleted_page_chunks'
      WHEN NOT ${trustedSql(QUARANTINE_FILTER_FRAGMENT)} THEN 'quarantined'
      WHEN ${colRef} IS NOT NULL THEN 'embedded'
      WHEN ${staleChunkWhere(column)} THEN 'pending'
      ELSE 'keyword_only' END`;
}

/** A live page with no chunk row: quarantined, healable with a body (`chunklessPagesWhere`, embed_skip included: #7174), else empty (no body, or a sealed projection with no chunks: #8473). */
function chunklessPageState(): SqlFragment {
  return sqlFragment`CASE
      WHEN NOT ${trustedSql(QUARANTINE_FILTER_FRAGMENT)} THEN 'quarantined'
      WHEN ${chunklessPagesWhere()} THEN 'chunkless_healable'
      ELSE 'empty' END`;
}

function embeddingStateScope(sourceIds?: string[]): SqlFragment {
  return sourceIds ? sqlFragment` AND p.source_id = ANY(${sourceIds}::text[])` : sqlFragment``;
}

const NO_CHUNK_ROWS = sqlFragment`NOT EXISTS (SELECT 1 FROM content_chunks cc WHERE cc.page_id = p.id)`;
const MARKER_BYTES = sqlFragment`CASE WHEN jsonb_typeof(p.frontmatter -> 'embed_skip' -> 'bytes') = 'number'
      THEN (p.frontmatter -> 'embed_skip' ->> 'bytes')::numeric::bigint END`;

const normalizeStateCount = compileRowNormalizer<{ state: EmbeddingState; n: number; eligible: number; overdue: number; unstamped: number; unstamped_min: Date | null }>(
  { n: 'bigint', eligible: 'bigint', overdue: 'bigint', unstamped: 'bigint', unstamped_min: 'date' });
const normalizeEmbedSkip = compileRowNormalizer<{ pages: number; marker_bytes: number; chunkless: number }>({ pages: 'bigint', marker_bytes: 'bigint', chunkless: 'bigint' });

/**
 * Counts per embedding state. `full`: two exact aggregates (every chunk, every
 * chunkless live page). `ops`: only index-backed reads, and `basis: 'unknown'`
 * when no valid partial index on content_chunks has the predicate
 * `<active column> IS NULL` (never a slow scan): the vector-less chunks through
 * that index, quarantined and embed_skip pages through the frontmatter GIN
 * index, deleted pages through the purge index, and `embedded` as the
 * planner's row estimate; `empty` and `chunkless_healable` need an anti-join
 * over every live page and are omitted.
 */
export async function readEmbeddingStateCounts(exec: ScopedRead, column: string,
  opts: { mode: 'ops' | 'full'; sourceIds?: string[]; purgeWindowHours: number }): Promise<EmbeddingStateCounts> {
  const scope = embeddingStateScope(opts.sourceIds);
  const colRef = trustedSql(`cc.${quoteIdentifier(column)}`);
  let coveringIndex: string | null = null;
  if (opts.mode === 'ops') {
    const { rows } = await exec.run<{ name: string }>(sqlFragment`
      SELECT ic.relname AS name
        FROM pg_index i
        JOIN pg_class ic ON ic.oid = i.indexrelid
        JOIN pg_class t ON t.oid = i.indrelid
        JOIN pg_namespace n ON n.oid = t.relnamespace
       WHERE t.relname = 'content_chunks' AND n.nspname = current_schema()
         AND i.indisvalid AND i.indpred IS NOT NULL
         AND pg_get_expr(i.indpred, i.indrelid) = '(' || quote_ident(${column}) || ' IS NULL)'
       ORDER BY ic.relname LIMIT 1`);
    coveringIndex = rows[0]?.name ?? null;
    if (!coveringIndex) {
      return { active_column: column, basis: 'unknown', covering_index: null, counts: {}, omitted: [], eligible_embedded: null,
        deleted_overdue: 0, embed_skip_pages: 0, embed_skip_marker_bytes: 0, unstamped_pending: 0, unstamped_created_at_min: null };
    }
  }
  const counts: Partial<Record<EmbeddingState, number>> = {};
  const add = (state: EmbeddingState, n: number) => { counts[state] = (counts[state] ?? 0) + n; };
  const overdue = sqlFragment`(p.deleted_at < now() - make_interval(hours => ${opts.purgeWindowHours}::int))`;
  const aggregate = (where: SqlFragment) => exec.run(sqlFragment`
      SELECT s.state, count(*)::bigint AS n, count(*) FILTER (WHERE s.eligible)::bigint AS eligible,
             count(*) FILTER (WHERE s.overdue)::bigint AS overdue,
             count(*) FILTER (WHERE s.unstamped)::bigint AS unstamped,
             min(s.created_at) FILTER (WHERE s.unstamped) AS unstamped_min
        FROM (SELECT ${chunkEmbeddingState(column)} AS state, ${trustedSql(EMBED_SKIP_FILTER_FRAGMENT)} AS eligible,
                     COALESCE(${overdue}, false) AS overdue, cc.embedding_pending_since IS NULL AS unstamped, cc.created_at
                FROM content_chunks cc JOIN pages p ON p.id = cc.page_id
               WHERE ${where} AND ${trustedSql(chunkGenerationFilter('p', 'cc'))}${scope}) s
       GROUP BY s.state`);
  const chunkRows = (await aggregate(opts.mode === 'ops' ? sqlFragment`${colRef} IS NULL` : sqlFragment`TRUE`)).rows.map(row => normalizeStateCount(row));
  let pendingUnstamped = 0;
  let unstampedMin: Date | null = null;
  let eligibleEmbedded = 0;
  let deletedOverdue = 0;
  for (const row of chunkRows) {
    if (row.state === 'pending') { pendingUnstamped = row.unstamped; unstampedMin = row.unstamped_min; }
    if (row.state === 'embedded') eligibleEmbedded = row.eligible;
    if (opts.mode === 'ops' && (row.state === 'quarantined' || row.state === 'deleted_page_chunks')) continue;
    if (row.state === 'deleted_page_chunks') deletedOverdue = row.overdue;
    add(row.state, row.n);
  }
  const embedSkip = normalizeEmbedSkip((await exec.run(sqlFragment`
      SELECT count(*)::bigint AS pages, COALESCE(sum(${MARKER_BYTES}), 0)::bigint AS marker_bytes,
             count(*) FILTER (WHERE ${NO_CHUNK_ROWS})::bigint AS chunkless
        FROM pages p
       WHERE p.frontmatter ? 'embed_skip' AND p.deleted_at IS NULL AND ${trustedSql(QUARANTINE_FILTER_FRAGMENT)}${scope}`)).rows[0] ?? {});
  const base = { active_column: column, covering_index: coveringIndex, embed_skip_pages: embedSkip.pages ?? 0, embed_skip_marker_bytes: embedSkip.marker_bytes ?? 0,
    unstamped_pending: pendingUnstamped, unstamped_created_at_min: unstampedMin };
  if (opts.mode === 'full') {
    const { rows } = await exec.run(sqlFragment`
      SELECT s.state, count(*)::bigint AS n
        FROM (SELECT ${chunklessPageState()} AS state FROM pages p WHERE p.deleted_at IS NULL AND ${NO_CHUNK_ROWS}${scope}) s
       GROUP BY s.state`);
    for (const row of rows) add(row.state as EmbeddingState, Number(row.n));
    for (const state of EMBEDDING_STATES) counts[state] ??= 0;
    return { ...base, basis: 'exact', counts, omitted: [], eligible_embedded: eligibleEmbedded, deleted_overdue: deletedOverdue };
  }
  const quarantined = await exec.run(sqlFragment`
      SELECT COALESCE(sum(GREATEST((SELECT count(*) FROM content_chunks cc WHERE cc.page_id = p.id), 1)), 0)::bigint AS n
        FROM pages p
       WHERE p.frontmatter ? 'quarantine' AND p.deleted_at IS NULL${scope}`);
  counts.quarantined = Number(quarantined.rows[0]?.n ?? 0);
  const deleted = await exec.run(sqlFragment`
      SELECT count(*)::bigint AS n, count(*) FILTER (WHERE ${overdue})::bigint AS overdue
        FROM pages p JOIN content_chunks cc ON cc.page_id = p.id
       WHERE p.deleted_at IS NOT NULL${scope}`);
  counts.deleted_page_chunks = Number(deleted.rows[0]?.n ?? 0);
  const plan = await exec.run<{ 'QUERY PLAN': unknown }>(sqlFragment`
      EXPLAIN (FORMAT JSON) SELECT 1 FROM content_chunks cc JOIN pages p ON p.id = cc.page_id
       WHERE ${colRef} IS NOT NULL AND p.deleted_at IS NULL AND ${trustedSql(QUARANTINE_FILTER_FRAGMENT)}${scope}`);
  counts.embedded = plannerRows(plan.rows[0]?.['QUERY PLAN']);
  for (const state of ['pending', 'keyword_only'] as const) counts[state] ??= 0;
  return { ...base, basis: 'estimated', counts, omitted: ['empty', 'chunkless_healable'], eligible_embedded: null,
    deleted_overdue: Number(deleted.rows[0]?.overdue ?? 0) };
}

/** `EXPLAIN (FORMAT JSON)`'s top-level row estimate (postgres.js parses json; PGLite may hand back text). */
function plannerRows(plan: unknown): number {
  const parsed = typeof plan === 'string' ? JSON.parse(plan) as unknown : plan;
  const rows = Array.isArray(parsed) ? (parsed[0] as { Plan?: { 'Plan Rows'?: unknown } } | undefined)?.Plan?.['Plan Rows'] : undefined;
  return typeof rows === 'number' && Number.isFinite(rows) ? Math.max(0, Math.round(rows)) : 0;
}

const normalizeStateRow = compileRowNormalizer<EmbeddingStateRow>({ chunks: 'bigint', bytes: 'bigint', marker_bytes: 'bigint', deleted_at: 'date' });

/**
 * One row per (page, state) in `states`, ordered (state, source_id, slug):
 * a page whose chunks sit in two states (some embedded, some pending) yields
 * two rows; a page with no chunk row yields one row with `chunks: 0` and its
 * body bytes. Exact (full scan); the caller passes `limit` (one more than it
 * prints, to report completeness).
 */
export async function listEmbeddingStateRows(exec: ScopedRead, column: string,
  opts: { sourceIds?: string[]; slugs?: string[]; states: readonly EmbeddingState[]; limit: number; purgeWindowHours: number }): Promise<EmbeddingStateRow[]> {
  const scope = opts.slugs ? sqlFragment`${embeddingStateScope(opts.sourceIds)} AND p.slug = ANY(${opts.slugs}::text[])` : embeddingStateScope(opts.sourceIds);
  const { rows } = await exec.run(sqlFragment`
    SELECT r.slug, r.source_id, r.state, r.chunks, r.bytes, r.embed_skip_reason, r.marker_bytes, r.quarantine_reason, r.deleted_at, r.overdue
      FROM (
        SELECT p.slug, p.source_id, ${chunkEmbeddingState(column)} AS state, count(*)::bigint AS chunks,
               COALESCE(sum(octet_length(cc.chunk_text)), 0)::bigint AS bytes,
               p.frontmatter -> 'embed_skip' ->> 'reason' AS embed_skip_reason, ${MARKER_BYTES} AS marker_bytes,
               p.frontmatter -> 'quarantine' ->> 'reason' AS quarantine_reason, p.deleted_at,
               COALESCE(p.deleted_at < now() - make_interval(hours => ${opts.purgeWindowHours}::int), false) AS overdue
          FROM content_chunks cc JOIN pages p ON p.id = cc.page_id
         WHERE ${trustedSql(chunkGenerationFilter('p', 'cc'))}${scope}
         GROUP BY p.id, 3
        UNION ALL
        SELECT p.slug, p.source_id, ${chunklessPageState()}, 0::bigint,
               (octet_length(p.compiled_truth) + octet_length(COALESCE(p.timeline, '')))::bigint,
               p.frontmatter -> 'embed_skip' ->> 'reason', ${MARKER_BYTES},
               p.frontmatter -> 'quarantine' ->> 'reason', p.deleted_at, false
          FROM pages p
         WHERE p.deleted_at IS NULL AND ${NO_CHUNK_ROWS}${scope}
      ) r
     WHERE r.state = ANY(${[...opts.states]}::text[])
     ORDER BY r.state, r.source_id, r.slug
     LIMIT ${opts.limit}`);
  return rows.map(row => normalizeStateRow(row));
}

/** The transaction engine's own steps `sealChunks` runs (its page guard, chunk delete and sealing chunk insert). */
export interface ChunkSealSteps extends ChunkPageGuards, Pick<BrainEngine, 'executeRaw'> {
  deleteChunks(slug: string, opts: { sourceId: string }): Promise<void>;
  upsertChunks(slug: string, chunks: ChunkInput[], opts: { sourceId: string; sealChunkerVersion: number }): Promise<void>;
}

/**
 * BrainEngine.sealChunks, run inside the caller's page transaction: replace a page's chunks and seal its text
 * projection at its current revision, the projection write `installPageProjection` makes with `seal: true`
 * (page-state/projections.ts) without its embedding-context conflict checks. It refuses a missing or deleted page,
 * a page in an archived source, a segmented page (its projection is built from its segments) and a stale
 * `expectedRevision`. The text seal is `sealPageTextProjection` (page-state/text-seal.ts).
 */
export async function sealChunks(exec: SqlExecutor, steps: ChunkSealSteps, slug: string, chunks: ChunkInput[],
  opts?: { sourceId?: string; expectedRevision?: string }): Promise<void> {
  slug = validateSlug(slug);
  const sourceId = opts?.sourceId ?? 'default';
  await steps.lockPageKeys([{ sourceId, slug }]);
  const current = await steps.readPageSnapshot(slug, { sourceId, requireLiveSource: true });
  if (!current || current.page.deleted_at) throw new Error(`Page not found: ${slug} (source=${sourceId})`);
  if (current.page.body_storage === 'segmented') throw new Error(`sealChunks refuses segmented page ${slug} (source=${sourceId}): its projection is built from its segments`);
  if (opts?.expectedRevision !== undefined) assertPageRevision(current, { expectedRevision: opts.expectedRevision });
  await steps.deleteChunks(slug, { sourceId });
  await steps.upsertChunks(slug, chunks, { sourceId, sealChunkerVersion: SAFE_FENCE_CHUNKER_VERSION });
  await sealPageTextProjection(steps, slug, sourceId, current);
  await exec.run(sqlFragment`DELETE FROM page_projection_jobs
    WHERE source_incarnation = ${current.sourceIncarnation}::uuid AND slug = ${slug} AND revision = ${current.revision}::uuid`);
}

export async function deleteChunks(exec: SqlExecutor, slug: string, opts?: { sourceId?: string }): Promise<void> {
    const sourceId = opts?.sourceId ?? 'default';
    // Source-qualify the page-id subquery; slugs are only unique per source.
    // Removing chunks voids the text projection seal that certified them; a
    // writer that reinstalls the projection seals it again (#8502). The page
    // row is locked before any chunk row (the DELETE needs its id), the order
    // every writer takes them in, so this never deadlocks against one.
    await exec.run(sqlFragment`
      WITH page AS (SELECT id FROM pages WHERE slug = ${slug} AND source_id = ${sourceId} FOR UPDATE),
      gone AS (
        DELETE FROM content_chunks
        WHERE page_id = (SELECT id FROM page)
        RETURNING page_id
      )
      UPDATE pages SET text_projection_revision = NULL
      WHERE id = (SELECT page_id FROM gone LIMIT 1) AND text_projection_revision IS NOT NULL
    `);
  }

/**
 * Decodes pgvector's binary send format (`vector_send`): int16 dimensions,
 * int16 unused, then big-endian float4 values. Both drivers return the bytea
 * as a Uint8Array (postgres.js a Buffer). The float4 values are the stored
 * ones, bit for bit the Float32Array the text literal parses to
 * (`test/engine-sql-chunks.test.ts` pins it on a real engine); a malformed
 * value decodes to null and the row is skipped.
 */
export function decodeVectorSend(value: unknown): Float32Array | null {
  if (!(value instanceof Uint8Array) || value.byteLength < 4) return null;
  const view = new DataView(value.buffer, value.byteOffset, value.byteLength);
  const dimensions = view.getInt16(0);
  if (value.byteLength !== 4 + 4 * dimensions) return null;
  const embedding = new Float32Array(dimensions);
  for (let i = 0; i < dimensions; i++) embedding[i] = view.getFloat32(4 + 4 * i);
  return embedding;
}

export async function getEmbeddingsByChunkIds(exec: LegacyUnscopedRead, ids: number[], column: string): Promise<Map<number, Float32Array>> {
    if (ids.length === 0) return new Map();
    // v0.36 (D9): column parameter used by hybrid.cosineReScore so
    // rescoring rehydrates from the active column's embedding space,
    // not always 'embedding'. Engine has no resolver access; the
    // caller must pass a known column name. Identifier-quoted (D12
    // defense layer 2) plus a strict regex check (D12 defense layer 1)
    // so even a misconfigured caller can't smuggle a SQL fragment.
    if (!COLUMN_NAME_REGEX.test(column)) {
      throw new EmbeddingColumnNotRegisteredError(column, []);
    }
    // Binary transfer (`vector_send`, halfvec cast exactly to vector): the
    // text literal cost about 3x more in vector_out formatting, wire bytes and
    // parsing than the TOAST read itself, for the same float4 values.
    const quotedCol = trustedSql(quoteIdentifier(column));
    const { text, params } = renderFragment(sqlFragment`
      SELECT cc.id, vector_send(cc.${quotedCol}::vector) AS embedding FROM content_chunks cc JOIN pages p ON p.id=cc.page_id
      WHERE cc.id = ANY(${ids}::int[]) AND cc.${quotedCol} IS NOT NULL AND ${trustedSql(chunkVisibleFilter('p', 'cc'))}
    `);
    const { rows } = await exec.unsafe(text, params);
    const result = new Map<number, Float32Array>();
    for (const row of rows) {
      const embedding = decodeVectorSend(row.embedding);
      if (embedding) result.set(row.id as number, embedding);
    }
    return result;
  }

/**
 * The text-space cosine re-score in SQL (`search/cosine-rescore.ts`): one
 * `(id, cosine)` row per hydrated chunk, the rows `getEmbeddingsByChunkIds`
 * returns (same active column, halfvec read exactly as vector, same
 * visibility). A hydrated vector whose dimensions differ from the query's,
 * or a zero-norm vector or query, scores 0 without reaching the operator, as
 * `cosineSimilarity` does; an absent or invisible chunk returns no row.
 */
export function cosineByChunkIdsStatement(ids: number[], column: string, query: Float32Array): { text: string; params: unknown[] } {
  if (!COLUMN_NAME_REGEX.test(column)) throw new EmbeddingColumnNotRegisteredError(column, []);
  const col = trustedSql(`cc.${quoteIdentifier(column)}`);
  const q = `[${Array.from(query).join(',')}]`;
  return renderFragment(sqlFragment`
    SELECT cc.id, CASE WHEN vector_dims(${col}::vector) <> ${query.length}::int OR vector_norm(${col}::vector) = 0 OR vector_norm(${q}::text::vector) = 0
                       THEN 0::float8 ELSE 1 - (${col}::vector <=> ${q}::text::vector) END AS cosine
      FROM content_chunks cc JOIN pages p ON p.id = cc.page_id
     WHERE cc.id = ANY(${ids}::int[]) AND ${col} IS NOT NULL AND ${trustedSql(chunkVisibleFilter('p', 'cc'))}
  `);
}

/**
 * Evidence delivery: ONE batched read of chunk_index windows, keyed by
 * page_id. Every requested page is re-authorized with the search legs'
 * visibility predicate (source scope, deleted, current text projection,
 * archived source, quarantine, private pages, safe chunks); pages that fail
 * are absent. Page rows of inline pages carry the raw body columns (the
 * assembler sanitizes them whole before slicing); a segmented page's row says
 * `segmented` and carries no body, and its chunk rows carry their occurrence
 * and segment-relative projected span, the references the assembler reads
 * windows around (page-body/read-range.ts). Chunk rows (sealed pages only)
 * anchor the hits. Page rows and chunk
 * rows come back in one UNION so the row cap never hides a page's
 * authorization.
 */
export async function getChunkWindows(exec: ScopedRead, requests: ChunkWindowRequest[], opts: ChunkWindowOpts, chunkManifest = true): Promise<ChunkWindowPage[]> {
  if (requests.length === 0) return [];
  const maxRows = Math.max(0, Math.floor(opts.maxRows));
  const scopeSql = opts.sourceIds && opts.sourceIds.length > 0
    ? sqlFragment`AND p.source_id = ANY(${opts.sourceIds}::text[])`
    : opts.sourceId ? sqlFragment`AND p.source_id = ${opts.sourceId}` : sqlFragment``;
  const privateSql = opts.excludePrivate ? trustedSql(`AND ${privatePagesFilterForCaller('p')}`) : sqlFragment``;
  const safeSql = requiresSafeChunks(opts) ? trustedSql(`AND ${safeChunksFilter('p')}`) : sqlFragment``;
  const { rows } = await exec.run(sqlFragment`
    WITH req AS (
      SELECT * FROM unnest(${requests.map(r => r.page_id)}::int[], ${requests.map(r => r.from_index)}::int[],
                           ${requests.map(r => r.to_index)}::int[], ${requests.map(r => r.priority)}::int[]) AS r(page_id, lo, hi, prio)
    ), auth AS (
      SELECT p.id, p.slug, p.source_id, p.type, p.knowledge_revision::text AS revision, p.compiled_truth, p.timeline, p.body_storage, p.body_generation,
             p.body_storage = 'segmented' AS segmented, (COALESCE(p.chunker_version, 0) >= ${SAFE_FENCE_CHUNKER_VERSION}) AS sealed,
             (SELECT MIN(req.prio) FROM req WHERE req.page_id = p.id) AS prio
        FROM pages p
        JOIN sources s ON s.id = p.source_id
       WHERE p.id = ANY(${[...new Set(requests.map(r => r.page_id))]}::int[]) ${scopeSql}
         AND p.deleted_at IS NULL AND ${trustedSql(servedTextProjectionFilter('p'))} AND NOT s.archived
         AND ${trustedSql(QUARANTINE_FILTER_FRAGMENT)} ${privateSql} ${safeSql}
    ), chunk_rows AS (
      SELECT cc.page_id, cc.id AS chunk_id, cc.chunk_index, cc.chunk_text, cc.chunk_source, cc.projected_byte_start, cc.projected_byte_end, cc.occurrence_id, a.prio
        FROM auth a
        JOIN content_chunks cc ON cc.page_id = a.id
       WHERE a.sealed AND ${trustedSql(chunkGenerationFilter('a', 'cc', chunkManifest))}
         AND cc.chunk_source = ANY(${opts.chunkSources}::text[])
         AND COALESCE(cc.modality, 'text') = 'text'
         AND EXISTS (SELECT 1 FROM req r WHERE r.page_id = cc.page_id AND cc.chunk_index BETWEEN r.lo AND r.hi)
       ORDER BY a.prio, cc.page_id, cc.chunk_index
       LIMIT ${maxRows + 1}
    )
    SELECT 'page' AS kind, a.id AS page_id, a.slug, a.source_id, a.type, a.revision, a.sealed, a.segmented, a.prio,
           (SELECT MAX(c2.chunk_index) FROM content_chunks c2 WHERE c2.page_id = a.id AND ${trustedSql(chunkGenerationFilter('a', 'c2', chunkManifest))}) AS max_chunk_index,
           a.compiled_truth, a.timeline,
           NULL::int AS chunk_id, NULL::int AS chunk_index, NULL::text AS chunk_text, NULL::text AS chunk_source,
           NULL::bigint AS projected_byte_start, NULL::bigint AS projected_byte_end, NULL::bigint AS occurrence_id
      FROM auth a
    UNION ALL
    SELECT 'chunk' AS kind, c.page_id, NULL, NULL, NULL, NULL, NULL, NULL, c.prio,
           NULL, NULL, NULL, c.chunk_id, c.chunk_index, c.chunk_text, c.chunk_source, c.projected_byte_start, c.projected_byte_end, c.occurrence_id
      FROM chunk_rows c
  `);
  return mapChunkWindowRows(rows, maxRows);
}

export async function getChunksWithEmbeddings(exec: LegacyUnscopedRead, slug: string, opts?: { sourceId?: string; includeUnsealed?: boolean }): Promise<Chunk[]> {
    const sourceId = opts?.sourceId;
    const projection = trustedSql(opts?.includeUnsealed ? chunkGenerationFilter('p', 'cc') : chunkVisibleFilter('p', 'cc'));
    const { rows } = sourceId
      ? await exec.run(sqlFragment`
          SELECT cc.* FROM content_chunks cc
          JOIN pages p ON p.id = cc.page_id
          WHERE ${projection} AND p.slug = ${slug} AND p.source_id = ${sourceId}
          ORDER BY cc.chunk_index
        `)
      : await exec.run(sqlFragment`
          SELECT cc.* FROM content_chunks cc
          JOIN pages p ON p.id = cc.page_id
          WHERE ${projection} AND p.slug = ${slug}
          ORDER BY cc.chunk_index
        `);
    return rows.map((r) => rowToChunk(r, true));
  }