Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
67.0 KB
Raw Blame
/**
 * Pages: one SQL implementation for both engines (refactor wave 1,
 * W1-extended). Statement text is PostgresEngine's master text (SQL-text
 * golden `sql-text/pages.json`); PGLite runs the same statements
 * (docs/designs/refactor-wave-1/w1-inventory.md, "pages").
 *
 * The engines keep page-state orchestration: `putPage`'s transaction,
 * page-key lock and revision check, `updateSlug` / `setPageAliases`'
 * transactions, and `getPage` / `readPageSnapshot` (SQL in
 * `page-state/snapshot.ts`). Reads that ran inside `withScopedReadTransaction`
 * on master take `ScopedRead` (or a `ScopedReadRunner` when an early return
 * must stay outside the transaction); every other read takes
 * `LegacyUnscopedRead` (EO4 inventory).
 */
import type { BrainEngine } from '../engine.ts';
import type { DomainBankSampleOpts, CorpusSampleOpts, DomainBankRow } from '../types.ts';
import type { Page, PageInput, PageFilters, StalePageRow } from '../types.ts';
import type { GetVersionsOpts, PageVersionRows } from '../page-state/version-types.ts';
import { PAGE_SORT_SQL } from '../types.ts';
import type { PageWriteOptions } from '../page-state/types.ts';
import { moveSlugBindings, recordRenameAlias } from '../page-state/rename-alias.ts';
import { segmentedRevertRefusal } from '../page-state/versions.ts';
import { sanitizeText } from '../batch-rows.ts';
import { SAFE_FENCE_CHUNKER_VERSION, bodyWriteChunkVersion } from '../search/safe-chunks.ts';
import { privatePagesFilterFragment, privateSnapshotFilterFragment, privatePagesFilterForCaller, privacyForCaller, callerReadersAllowedSql, currentCallerAccess } from '../search/private-visibility.ts';
import { validDateSql } from '../valid-time.ts';
import { quarantineFilterFragment } from '../quarantine.ts';
import { rederiveHiddenSql } from '../eligibility/sql.ts';
import { validateSlug, contentHash, isBlankBody, rowToPage, rowToStalePage, isMissingSchemaObject, warnOncePerProcess } from '../utils.ts';
import { DELETE_BATCH_SIZE } from '../engine-constants.ts';
import { derivedOriginPurgeHold, OWED_PURGE_OP } from '../persistence/derived-origin-hold.ts';
import { jsonbParam, type SqlExecutor } from './executor.ts';
import type { LegacyUnscopedRead, ScopedRead } from './brands.ts';
import type { ScopedReadRunner } from './cjk-search.ts';
import { compileRowNormalizer } from './normalize.ts';
import { stagingNeedsTextSql } from '../page-body/schema.ts';
import { atomRetirementPurgeHold } from '../persistence/atom-retirement-hold.ts';
import { derivedRetirementPurgeHold } from '../persistence/derived-retirement-hold.ts';
import { createPageVersion } from '../page-state/versions.ts';
import { renderFragment, sqlFragment, trustedSql, type SqlFragment } from './fragment.ts';
import { frontmatterWhereFragment } from '../search/frontmatter-where.ts';
import { quoteIdentifier, resolveWriteColumnFromConfigRows } from '../search/embedding-column.ts';
import { pickCanonicalOrigin } from '../persistence/origin-guard.ts';
import { scrubPurgedSlugs } from '../feedback/store.ts';
import { scrubPurgedNameStores } from '../persistence/purge-name-stores.ts';
import { scrubPurgedSlugHolds, scrubPurgedSlugStores } from '../persistence/purge-slug-stores.ts';
import { expireGonePageFacts } from '../facts/gone-page-facts.ts';
import { guestSourceExclusionSql } from '../guests/derived.ts';

/**
 * PGLite can return zero rows from `INSERT ... ON CONFLICT DO UPDATE ...
 * RETURNING` in no-op/trigger edge cases; its engine passes a re-read here.
 * Postgres passes none (it always returns the row).
 */
export type RereadPage = (slug: string, sourceId: string) => Promise<Page | null>;

export async function findDuplicatePage(
  exec: ScopedRead,
  sourceId: string,
  opts: { hash: string; frontmatterId?: string | null; excludeSlug?: string },
): Promise<{ slug: string; id: number } | null> {
    const fmId = opts.frontmatterId ?? null;
    const excludeSlug = opts.excludeSlug ?? null;
      const { rows } = await exec.run<{ id: number | string; slug: string }>(sqlFragment`
        SELECT id, slug FROM pages
        WHERE source_id = ${sourceId}
          AND deleted_at IS NULL
          AND (content_hash = ${opts.hash} OR (frontmatter->>'id' = ${fmId} AND ${fmId}::text IS NOT NULL))
          AND (${excludeSlug}::text IS NULL OR slug <> ${excludeSlug})
        ORDER BY (frontmatter->>'id' IS NOT DISTINCT FROM ${fmId}::text) DESC, id
        LIMIT 1
      `);
      if (rows.length === 0) return null;
      const r = rows[0];
      return { slug: r.slug, id: Number(r.id) };
  }

/**
 * GBRA-75 wave 10: `findDuplicatePage` for several inputs of one source in one statement. Entry i is the row the
 * single call returns for `inputs[i]`: the same predicate, the same order and the first row per input.
 */
export async function findDuplicatePages(
  exec: ScopedRead,
  sourceId: string,
  inputs: ReadonlyArray<{ hash: string; frontmatterId?: string | null; excludeSlug?: string }>,
): Promise<Array<{ slug: string; id: number } | null>> {
  if (!inputs.length) return [];
  const { rows } = await exec.run<{ n: number | string; id: number | string; slug: string }>(sqlFragment`
    SELECT DISTINCT ON (k.n) k.n, p.id, p.slug
    FROM jsonb_to_recordset(${JSON.stringify(inputs.map((input, i) => ({ n: i + 1, hash: input.hash, frontmatter_id: input.frontmatterId ?? null,
      exclude_slug: input.excludeSlug ?? null })))}::text::jsonb) AS k(n int, hash text, frontmatter_id text, exclude_slug text)
    JOIN pages p ON p.source_id = ${sourceId}
      AND p.deleted_at IS NULL
      AND (p.content_hash = k.hash OR (p.frontmatter->>'id' = k.frontmatter_id AND k.frontmatter_id IS NOT NULL))
      AND (k.exclude_slug IS NULL OR p.slug <> k.exclude_slug)
    ORDER BY k.n, (p.frontmatter->>'id' IS NOT DISTINCT FROM k.frontmatter_id) DESC, p.id
  `);
  const found: Array<{ slug: string; id: number } | null> = inputs.map(() => null);
  for (const row of rows) found[Number(row.n) - 1] = { slug: row.slug, id: Number(row.id) };
  return found;
}

export async function putPage(
  exec: SqlExecutor,
  slug: string,
  page: PageInput,
  opts?: PageWriteOptions,
  rereadOnEmptyReturning?: RereadPage,
): Promise<Page> {
    slug = validateSlug(slug);
    const hash = page.content_hash || contentHash(page);
    const frontmatter = page.frontmatter || {};
    const sourceId = opts?.sourceId ?? 'default';

    // Data-loss guard: a page edit is a read-modify-write; if the read returned
    // empty, the modify lands on nothing and this upsert would blank the body
    // over real content (ON CONFLICT sets compiled_truth = EXCLUDED.* flat).
    // Only fires when the incoming body is itself blank, so the common
    // non-empty write pays no extra query. Deletes use deletePage; a deliberate
    // clear passes allowEmptyOverwrite. See isBlankBody. A segmented page's
    // body columns are NULL, so its size is body_bytes.
    if (isBlankBody(page.compiled_truth) && !opts?.allowEmptyOverwrite) {
      const { rows: prior } = await exec.run<{ compiled_truth: string | null; body_storage: string | null; body_bytes: unknown }>(sqlFragment`
        SELECT compiled_truth, body_storage, body_bytes FROM pages
        WHERE source_id = ${sourceId} AND slug = ${slug} AND deleted_at IS NULL
        LIMIT 1`);
      const segmented = prior[0]?.body_storage === 'segmented';
      if (prior[0] && (segmented || !isBlankBody(prior[0].compiled_truth))) {
        throw new Error(
          `putPage: refusing to overwrite non-empty page '${slug}' ` +
            `(${segmented ? `${Number(prior[0].body_bytes)} bytes, segmented` : `${prior[0].compiled_truth!.length} chars`}) with an empty body — ` +
            `likely a read-modify-write that read empty. Pass ` +
            `{ allowEmptyOverwrite: true } to force, or deletePage to remove it.`,
        );
      }
    }

    // v0.18.0 Step 5+: source_id is in the INSERT column list so multi-source
    // callers land on the (source_id, slug) row they intend; omitting it let
    // the schema DEFAULT 'default' fabricate a duplicate at (default, slug).
    // ON CONFLICT target is (source_id, slug); global UNIQUE(slug) dropped in v17.
    const pageKind = page.page_kind || 'markdown';
    // v0.29.1 — effective_date / effective_date_source / import_filename are
    // additive opt-in inputs from the importer (computeEffectiveDate). When
    // omitted, the ON CONFLICT path preserves any existing value via
    // COALESCE(EXCLUDED.x, pages.x) so a putPage that doesn't know about
    // these columns (auto-link, code reindex, etc.) doesn't blank them out.
    const effectiveDate = page.effective_date ?? null;
    const effectiveDateSource = page.effective_date_source ?? null;
    const importFilename = page.import_filename ?? null;
    // v0.32.7 CJK wave: chunker_version + source_path columns.
    // Only an import transaction may seal a fully sanitized chunk replacement.
    const chunkerVersion = Math.min(page.chunker_version ?? 0, SAFE_FENCE_CHUNKER_VERSION - 1);
    const sourcePath = page.source_path ?? null;
    // v0.39.3.0 provenance write-through (WARN-8 + CV12). Server stamps
    // `ingested_at = now()` ONLY when any provenance is being written —
    // null `source_kind` / `source_uri` / `ingested_via` means no provenance
    // write fired this call, and COALESCE-preserve UPDATE keeps the prior
    // first-write timestamp intact (audit trail survives routine edits).
    const sourceKind = page.source_kind ?? null;
    const sourceUri = page.source_uri ?? null;
    const ingestedVia = page.ingested_via ?? null;
    const ingestedAt = (sourceKind || sourceUri || ingestedVia) ? new Date() : null;
    // #5984: the contextual retrieval stamp, when the caller passes it, rides in this statement.
    const cr = opts?.contextualRetrieval && opts.contextualRetrieval.mode !== 'none' ? opts.contextualRetrieval : null;
    const crColumns = cr ? sqlFragment`, contextual_retrieval_mode, corpus_generation` : sqlFragment``;
    const crValues = cr ? sqlFragment`, ${cr.mode}, ${cr.corpusGeneration}` : sqlFragment``;
    const crSet = cr ? sqlFragment`
        contextual_retrieval_mode = EXCLUDED.contextual_retrieval_mode,
        corpus_generation     = EXCLUDED.corpus_generation,` : sqlFragment``;
    // origin-guard-ok: both engines' putPage run assertOriginUnclaimed in this transaction first
    const { rows } = await exec.run(sqlFragment`
      INSERT INTO pages (source_id, slug, type, page_kind, title, compiled_truth, timeline, frontmatter, content_hash, updated_at, effective_date, effective_date_source, import_filename, chunker_version, source_path, source_kind, source_uri, ingested_via, ingested_at${crColumns})
      VALUES (${sourceId}, ${slug}, ${page.type}, ${pageKind}, ${sanitizeText(page.title)}, ${sanitizeText(page.compiled_truth)}, ${sanitizeText(page.timeline || '')}, ${jsonbParam(frontmatter)}, ${hash}, now(), ${effectiveDate}, ${effectiveDateSource}, ${importFilename}, ${chunkerVersion}::smallint, ${sourcePath}, ${sourceKind}, ${sourceUri}, ${ingestedVia}, ${ingestedAt}${crValues})
      ON CONFLICT (source_id, slug) DO UPDATE SET${crSet}
        type = EXCLUDED.type,
        page_kind = EXCLUDED.page_kind,
        title = EXCLUDED.title,
        compiled_truth = EXCLUDED.compiled_truth,
        timeline = EXCLUDED.timeline,
        frontmatter = EXCLUDED.frontmatter,
        content_hash = EXCLUDED.content_hash,
        updated_at = now(),
        deleted_at = NULL,
        effective_date        = COALESCE(EXCLUDED.effective_date,        pages.effective_date),
        effective_date_source = COALESCE(EXCLUDED.effective_date_source, pages.effective_date_source),
        import_filename       = COALESCE(EXCLUDED.import_filename,       pages.import_filename),
        chunker_version       = ${trustedSql(bodyWriteChunkVersion('EXCLUDED.compiled_truth', 'EXCLUDED.timeline'))},
        source_path           = COALESCE(EXCLUDED.source_path,           pages.source_path),
        database_only_reason  = CASE WHEN EXCLUDED.source_path IS NOT NULL AND pages.database_only_reason IN ('detached', 'origin_twin', 'mirror_read_only') THEN NULL ELSE pages.database_only_reason END,
        source_kind           = COALESCE(EXCLUDED.source_kind,           pages.source_kind),
        source_uri            = COALESCE(EXCLUDED.source_uri,            pages.source_uri),
        ingested_via          = COALESCE(EXCLUDED.ingested_via,          pages.ingested_via),
        ingested_at           = COALESCE(EXCLUDED.ingested_at,           pages.ingested_at)
      RETURNING knowledge_revision, text_projection_revision, id, source_id, slug, type, title, compiled_truth, timeline, frontmatter, content_hash, created_at, updated_at, effective_date, effective_date_source, import_filename, source_kind, source_uri, ingested_via, ingested_at
    `);
    if (rows.length === 0 && rereadOnEmptyReturning) {
      // The row WAS written; re-read instead of crashing in rowToPage(undefined).
      const reread = await rereadOnEmptyReturning(slug, sourceId);
      if (reread) return reread;
      throw new Error(`putPage: RETURNING produced no row for ${sourceId}/${slug}`);
    }
    return rowToPage(rows[0]);
  }

export async function deletePage(exec: SqlExecutor, slug: string, opts?: { sourceId?: string }): Promise<void> {
    const sourceId = opts?.sourceId ?? 'default';
    await exec.run(sqlFragment`DELETE FROM pages WHERE slug = ${slug} AND source_id = ${sourceId}`);
  }

/**
 * v0.41.19.0 — batch delete primitive. See BrainEngine.deletePages JSDoc.
 * Caller is responsible for chunking input to <= DELETE_BATCH_SIZE.
 * RETURNING slug projects the actually-deleted set so the caller can filter
 * pagesAffected. #9068: the rows that name a deleted page outside any foreign
 * key (retrieval logs and ratings, the volunteer log, the gazetteer, captured
 * eval slugs; #9146: the slug-keyed stores and the slug's write-gate holds) go
 * in the same transaction, after the page row: a feedback write
 * holding the row commits first and is scrubbed, and a later one finds no page.
 */
export async function deletePages(exec: SqlExecutor, slugs: string[], opts: { sourceId: string }): Promise<string[]> {
    if (slugs.length === 0) return [];
    if (slugs.length > DELETE_BATCH_SIZE) {
      throw new Error(
        `deletePages: input size ${slugs.length} exceeds DELETE_BATCH_SIZE=${DELETE_BATCH_SIZE}. Caller must chunk.`,
      );
    }
    return exec.transaction(async (tx) => {
      const { rows } = await tx.run<{ slug: string }>(sqlFragment`
        DELETE FROM pages
         WHERE slug = ANY(${slugs}::text[]) AND source_id = ${opts.sourceId}
        RETURNING slug
      `);
      const deleted = rows.map(r => r.slug);
      await scrubPurgedSlugs(tx, opts.sourceId, deleted);
      await scrubPurgedNameStores(tx, opts.sourceId, deleted);
      await scrubPurgedSlugStores(tx, opts.sourceId, deleted);
      await scrubPurgedSlugHolds(tx, opts.sourceId, deleted.map(slug => ({ slug })));
      return deleted;
    });
  }

/**
 * v0.41.19.0 — batch path → slug resolution. See BrainEngine.resolveSlugsByPaths
 * JSDoc. Single SQL round-trip. #6948: a path's live pages win over its tombstones;
 * several pages resolve to the canonical one (`pickCanonicalOrigin`), and a path whose
 * pages are ambiguous is left out (never an arbitrary twin).
 */
export async function resolveSlugsByPaths(
  exec: LegacyUnscopedRead,
  paths: string[],
  opts: { sourceId: string },
): Promise<Map<string, string>> {
    if (paths.length === 0) return new Map();
    if (paths.length > DELETE_BATCH_SIZE) {
      throw new Error(
        `resolveSlugsByPaths: input size ${paths.length} exceeds DELETE_BATCH_SIZE=${DELETE_BATCH_SIZE}. Caller must chunk.`,
      );
    }
    const { rows } = await exec.run<{ slug: string; source_path: string; live: boolean }>(sqlFragment`
      SELECT slug, source_path, deleted_at IS NULL AS live
        FROM pages
       WHERE source_path = ANY(${paths}::text[]) AND source_id = ${opts.sourceId}
       ORDER BY source_path, slug
    `);
    const m = new Map<string, string>();
    for (const path of new Set(rows.map(r => r.source_path))) {
      const claimants = rows.filter(r => r.source_path === path);
      const live = claimants.filter(r => r.live);
      const picked = pickCanonicalOrigin(live.length ? live : claimants, path);
      if (picked && !('ambiguous' in picked)) m.set(path, picked.slug);
    }
    return m;
  }

export async function softDeletePage(exec: SqlExecutor, slug: string, opts?: { sourceId?: string }): Promise<{ slug: string } | null> {
    const sourceId = opts?.sourceId;
    // Idempotent-as-null contract: only flip rows that are currently active.
    // RETURNING projects the slug so we can tell hit-vs-miss without a probe.
    const sourceCondition = sourceId ? sqlFragment`AND source_id = ${sourceId}` : sqlFragment``;
    const { rows } = await exec.run<{ slug: string }>(sqlFragment`
      UPDATE pages SET deleted_at = now()
      WHERE slug = ${slug} AND deleted_at IS NULL ${sourceCondition}
      RETURNING slug
    `);
    if (rows.length === 0) return null;
    return { slug: rows[0].slug };
  }

/**
 * #4587 — batch soft-delete primitive. See BrainEngine.softDeletePages
 * JSDoc. Mirrors deletePages' shape (empty-array early-return, batch-size
 * throw, RETURNING slug) with softDeletePage's `deleted_at IS NULL`
 * idempotency predicate. Nothing cascades — the 72h purge phase owns the
 * eventual hard delete.
 */
export async function softDeletePages(exec: SqlExecutor, slugs: string[], opts: { sourceId: string }): Promise<string[]> {
    if (slugs.length === 0) return [];
    if (slugs.length > DELETE_BATCH_SIZE) {
      throw new Error(
        `softDeletePages: input size ${slugs.length} exceeds DELETE_BATCH_SIZE=${DELETE_BATCH_SIZE}. Caller must chunk.`,
      );
    }
    const { rows } = await exec.run<{ slug: string }>(sqlFragment`
      UPDATE pages SET deleted_at = now()
       WHERE slug = ANY(${slugs}::text[]) AND source_id = ${opts.sourceId} AND deleted_at IS NULL
      RETURNING slug
    `);
    return rows.map(r => r.slug);
  }

export async function restorePage(exec: SqlExecutor, slug: string, opts?: { sourceId?: string }): Promise<boolean> {
    const sourceId = opts?.sourceId;
    const sourceCondition = sourceId ? sqlFragment`AND source_id = ${sourceId}` : sqlFragment``;
    // GST-2766: a restored page owes no purge; its owed-purge record goes in the same statement.
    const { rows } = await exec.run(sqlFragment`
      WITH restored AS (
        UPDATE pages SET deleted_at = NULL, updated_at = now()
        WHERE slug = ${slug} AND deleted_at IS NOT NULL ${sourceCondition}
        RETURNING id, slug
      ), owed AS (
        DELETE FROM op_checkpoints WHERE op = ${OWED_PURGE_OP} AND fingerprint IN (SELECT id::text FROM restored)
      )
      SELECT slug FROM restored
    `);
    return rows.length > 0;
  }

export async function purgeDeletedPages(
  exec: SqlExecutor,
  olderThanHours: number,
  opts?: { dryRun?: boolean; excludeIds?: number[] },
): Promise<{ slugs: string[]; count: number; pages?: { slug: string; source_id: string; deleted_at: Date }[] }> {
    // Clamp to non-negative integer; runaway purge protection. The DELETE
    // cascades through content_chunks, page_links, chunk_relations via FKs.
    const hours = Math.max(0, Math.floor(olderThanHours));
    // GBRA-130 A-49: tombstones the unmanaged belief-ledger expiry could not delete (or parked) stay for their own retry.
    const excluded = opts?.excludeIds?.length ? sqlFragment`
          AND NOT (pages.id = ANY(${opts.excludeIds}::bigint[]))` : sqlFragment``;
    const excludedManifest = opts?.excludeIds?.length ? sqlFragment` AND NOT (p.id = ANY(${opts.excludeIds}::bigint[]))` : sqlFragment``;
    if (opts?.dryRun) {
      // SAME WHERE predicate as the DELETE below (same cutoff arithmetic,
      // same DB now() clock source) — only the verb differs, so preview and
      // purge agree modulo rows crossing the cutoff between statements.
      const { rows } = await exec.run<{ slug: string; source_id: string; deleted_at: Date | string }>(sqlFragment`
        SELECT slug, source_id, deleted_at FROM pages
        WHERE deleted_at IS NOT NULL
          AND deleted_at < now() - (${hours} || ' hours')::interval
          AND NOT EXISTS (SELECT 1 FROM op_checkpoints dg WHERE dg.op = 'sync-identity-downgrade' AND dg.fingerprint = pages.id::text
            AND dg.completed_keys->0->>'disposition' IS NULL AND pages.deleted_at = (dg.completed_keys->0->>'old_deleted_at')::timestamptz)
          AND ${trustedSql(atomRetirementPurgeHold('pages'))}
          AND ${trustedSql(derivedRetirementPurgeHold('pages'))}
          AND ${trustedSql(derivedOriginPurgeHold('pages'))}${excluded}
        ORDER BY deleted_at ASC, slug ASC
      `);
      const pages = rows.map((r) => ({
        slug: r.slug,
        source_id: r.source_id,
        deleted_at: r.deleted_at instanceof Date ? r.deleted_at : new Date(r.deleted_at),
      }));
      return { slugs: pages.map((p) => p.slug), count: pages.length, pages };
    }
    // Wave B (spec 11): segment texts only the purged pages' manifests reference go in the same purge, not by later GC.
    return exec.transaction(async (tx) => {
      const { rows: texts } = await tx.run<{ source_id: string; sha: string }>(sqlFragment`
        SELECT DISTINCT m.source_id, encode(m.sha256, 'hex') AS sha FROM page_body_manifest m JOIN pages p ON p.id = m.page_id
        WHERE p.deleted_at IS NOT NULL AND p.deleted_at < now() - (${hours} || ' hours')::interval${excludedManifest}`);
      const { rows } = await tx.run<{ slug: string; source_id: string; deleted_at: string }>(sqlFragment`
        DELETE FROM pages
        WHERE deleted_at IS NOT NULL
          AND deleted_at < now() - (${hours} || ' hours')::interval
          -- #6729: the old page of an open rename-identity downgrade waits for its disposition (identityDowngradePurgeHold).
          AND NOT EXISTS (SELECT 1 FROM op_checkpoints dg WHERE dg.op = 'sync-identity-downgrade' AND dg.fingerprint = pages.id::text
            AND dg.completed_keys->0->>'disposition' IS NULL AND pages.deleted_at = (dg.completed_keys->0->>'old_deleted_at')::timestamptz)
          -- #7013: a restorable re-extraction retirement waits out its hold (atomRetirementPurgeHold).
          AND ${trustedSql(atomRetirementPurgeHold('pages'))}
          -- GBRA-161: every derived retirement waits out its hold (derivedRetirementPurgeHold).
          AND ${trustedSql(derivedRetirementPurgeHold('pages'))}
          -- GST-2689: an origin whose derived pages could not be labelled private stays parked (derivedOriginPurgeHold).
          AND ${trustedSql(derivedOriginPurgeHold('pages'))}${excluded}
        RETURNING slug, source_id, deleted_at::text AS deleted_at
      `);
      await expireGonePageFacts(tx, rows);
      if (rows.length) await tx.run(sqlFragment`DELETE FROM page_body_stagings s USING unnest(${rows.map(r => r.source_id)}::text[], ${rows.map(r => r.slug)}::text[]) AS d(source_id, slug)
        WHERE s.source_id = d.source_id AND s.slug = d.slug`);
      if (texts.length) await tx.run(sqlFragment`DELETE FROM page_segment_texts t
        USING unnest(${texts.map(r => r.source_id)}::text[], ${texts.map(r => r.sha)}::text[]) AS d(source_id, sha)
        WHERE t.source_id = d.source_id AND t.sha256 = decode(d.sha, 'hex')
          AND NOT EXISTS (SELECT 1 FROM page_body_manifest m WHERE m.source_id = t.source_id AND m.sha256 = t.sha256)
          AND NOT ${trustedSql(stagingNeedsTextSql('t'))}`);
      const bySource = new Map<string, Array<{ slug: string; deletedAt: string }>>();
      for (const r of rows) bySource.set(r.source_id, [...bySource.get(r.source_id) ?? [], { slug: r.slug, deletedAt: r.deleted_at }]);
      for (const [sourceId, purged] of bySource) {
        const slugs = purged.map(p => p.slug);
        await scrubPurgedSlugs(tx, sourceId, slugs);
        await scrubPurgedNameStores(tx, sourceId, slugs);
        await scrubPurgedSlugStores(tx, sourceId, slugs);
        await scrubPurgedSlugHolds(tx, sourceId, purged);
      }
      const slugs = rows.map((r) => r.slug);
      return { slugs, count: slugs.length };
    });
  }

export async function refreshPageBody(
  exec: SqlExecutor,
  slug: string,
  sourceId: string,
  compiledTruth: string,
  timeline: string,
  contentHash: string,
): Promise<void> {
    // Narrow UPDATE — leaves frontmatter, type, chunks, links, embeddings,
    // tags, takes untouched. Skips soft-deleted rows so a redirect retry
    // can't accidentally reanimate the body of a deleted canonical.
    // The spliced chunker-version expression reads the first two binds
    // ($1 compiled_truth, $2 timeline), so they stay first.
    await exec.run(sqlFragment`
      UPDATE pages
      SET compiled_truth = ${compiledTruth},
          timeline = ${timeline},
          content_hash = ${contentHash},
          chunker_version = ${trustedSql(bodyWriteChunkVersion('$1', '$2'))},
          updated_at = now()
      WHERE source_id = ${sourceId}
        AND slug = ${slug}
        AND deleted_at IS NULL
    `);
  }

export async function updatePageContextualRetrievalState(
  exec: SqlExecutor,
  slug: string,
  sourceId: string,
  mode: string,
  corpusGeneration: string | null,
  memo: <T>(key: string, read: () => Promise<T>) => Promise<T> = (_key, read) => read(),
): Promise<void> {
    if (mode === 'none') {
      // GBRA-156: the same read, under the same memo key, as the chunk write of this page transaction (chunks.ts).
      const config = await 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);
      const column = resolveWriteColumnFromConfigRows({
        searchEmbeddingColumn: config.find(row => row.key === 'search_embedding_column')?.value ?? null,
        embeddingColumnsJson: config.find(row => row.key === 'embedding_columns')?.value ?? null,
      });
      const vector = trustedSql(quoteIdentifier(column.name));
      await exec.run(sqlFragment`
        WITH previous AS MATERIALIZED (
          SELECT id, contextual_retrieval_mode AS old_mode,
            COALESCE(frontmatter, '{}'::jsonb) ? 'embed_skip' AS skipped
          FROM pages WHERE source_id=${sourceId} AND slug=${slug} AND deleted_at IS NULL
          FOR UPDATE
        ), changed AS (
          UPDATE pages p SET contextual_retrieval_mode=${mode}, corpus_generation=${corpusGeneration},
            updated_at=now(), embedding_signature=CASE
              WHEN previous.old_mode IN ('title','per_chunk_synopsis') AND NOT previous.skipped
              THEN NULL ELSE p.embedding_signature END
          FROM previous WHERE p.id=previous.id
          RETURNING p.id, previous.old_mode, previous.skipped
        )
        UPDATE content_chunks cc SET ${vector}=NULL, embedded_at=NULL,
          embedded_text_hash=NULL, embedding_input_hash=NULL, embedding_pending_since=now()
        FROM changed WHERE cc.page_id=changed.id
          AND changed.old_mode IN ('title','per_chunk_synopsis') AND NOT changed.skipped
          AND cc.${vector} IS NOT NULL`);
      return;
    }
    // Narrow UPDATE — bumps updated_at as a side effect so the autopilot
    // sweep doesn't think the page hasn't changed since last touch. Skips
    // soft-deleted rows. corpus_generation nullable (caller passes NULL
    // for the 'none' tier path).
    await exec.run(sqlFragment`
      UPDATE pages
      SET contextual_retrieval_mode = ${mode},
          corpus_generation = ${corpusGeneration},
          updated_at = now()
      WHERE source_id = ${sourceId}
        AND slug = ${slug}
        AND deleted_at IS NULL
    `);
  }

export async function listPages(exec: ScopedRead, filters?: PageFilters): Promise<Page[]> {
    const limit = filters?.limit || 100;
    const offset = filters?.offset || 0;
    const updatedAfter = filters?.updated_after;

    const typeCondition = filters?.type ? sqlFragment`AND p.type = ${filters.type}` : sqlFragment``;
    const tagJoin = filters?.tag ? sqlFragment`JOIN tags t ON t.page_id = p.id` : sqlFragment``;
    const tagCondition = filters?.tag ? sqlFragment`AND t.tag = ${filters.tag}` : sqlFragment``;
    // v0.45.7 keyset (updated_at, slug) supersedes updated_after when set.
    const keyset = filters?.updatedAfterKeyset;
    const updatedCondition = keyset
      // Exact only when the cursor carries the column's microseconds: callers
      // resume from `Page.updated_at_iso` (projected below), never from a JS
      // Date, which would re-select every row in the last row's millisecond.
      // `::text::timestamptz`: a bare `::timestamptz` param is typed by the
      // postgres.js driver, which serializes strings through a JS Date and
      // truncates the cursor to milliseconds (re-selecting the whole
      // millisecond; a >limit cluster inside one millisecond never drains).
      ? sqlFragment`AND (p.updated_at > ${keyset.updatedAt}::text::timestamptz OR (p.updated_at = ${keyset.updatedAt}::text::timestamptz AND p.slug > ${keyset.slug}))`
      : updatedAfter
        ? sqlFragment`AND p.updated_at > ${updatedAfter}::text::timestamptz`
        : sqlFragment``;
    // slugPrefix uses the (source_id, slug) UNIQUE btree index for range scans.
    // Escape LIKE metacharacters so the user prefix is treated as a literal.
    const slugPrefix = filters?.slugPrefix;
    const slugCondition = slugPrefix
      ? sqlFragment`AND p.slug LIKE ${slugPrefix.replace(/[\\%_]/g, (c) => '\\' + c) + '%'} ESCAPE '\\'`
      : sqlFragment``;
    // Search fallbacks must exclude before LIMIT: post-filtering can lose the
    // entire evidence floor when newer excluded pages occupy every slot.
    const excludedSlugs = filters?.excludeSlugPrefixes?.filter(prefix => prefix.length > 0) ?? [];
    const excludedSlugCondition = excludedSlugs.length
      ? sqlFragment` AND NOT EXISTS (SELECT 1 FROM unnest(${excludedSlugs}::text[]) AS excluded(prefix) WHERE starts_with(p.slug, excluded.prefix))`
      : sqlFragment``;
    // #6500 slug keyset (single-source contract, see PageFilters.slugAfter).
    const slugAfterCondition = filters?.slugAfter !== undefined
      ? sqlFragment`AND p.slug > ${filters.slugAfter}`
      : sqlFragment``;
    // v0.31.12 + v0.34.1 (#876, D9): scope to a single source OR an array
    // of sources. When BOTH are set, the array wins (federated semantics
    // subsume the scalar case). When neither is set, no filter applies.
    const sourceCondition = filters?.sourceIds && filters.sourceIds.length > 0
      ? sqlFragment`AND p.source_id = ANY(${filters.sourceIds}::text[])`
      : filters?.sourceId
        ? sqlFragment`AND p.source_id = ${filters.sourceId}`
        : sqlFragment``;
    // v0.26.5: hide soft-deleted by default; opt in via filters.includeDeleted.
    const deletedCondition = filters?.includeDeleted === true
      ? sqlFragment``
      : sqlFragment`AND p.deleted_at IS NULL`;
    // #4352: untrusted-caller private-page filter (see PageFilters.excludePrivate).
    const privateCondition = filters?.excludePrivate === true
      ? trustedSql(`AND ${privatePagesFilterForCaller('p')}`)
      : sqlFragment``;
    const effectiveAfterCondition = filters?.effective_after
      ? sqlFragment`AND p.effective_date >= ${filters.effective_after}::timestamptz`
      : sqlFragment``;
    const effectiveBeforeCondition = filters?.effective_before
      ? sqlFragment`AND p.effective_date <= ${filters.effective_before}::timestamptz`
      : sqlFragment``;
    const whereCondition = frontmatterWhereFragment(filters?.where);

    // v0.29: ORDER BY threading via PAGE_SORT_SQL whitelist (no SQL injection).
    const sortKey = filters?.sort && PAGE_SORT_SQL[filters.sort] ? filters.sort : 'updated_desc';
    const orderBy = trustedSql(PAGE_SORT_SQL[sortKey]);

    const columns = filters?.listColumnsOnly === true
      ? sqlFragment`p.id, p.source_id, p.slug, p.type, p.page_kind, p.title, p.created_at, p.updated_at, p.deleted_at,
          p.effective_date, p.effective_date_source, p.body_storage, p.body_bytes, ''::text AS compiled_truth, ''::text AS timeline, '{}'::jsonb AS frontmatter`
      : sqlFragment`p.*`;
      const { rows } = await exec.run(sqlFragment`
        SELECT ${columns}, to_char(p.updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS updated_at_iso FROM pages p
        ${tagJoin}
        WHERE 1=1 ${typeCondition} ${tagCondition} ${updatedCondition} ${slugCondition}${excludedSlugCondition} ${slugAfterCondition} ${sourceCondition} ${deletedCondition} ${privateCondition} ${effectiveAfterCondition} ${effectiveBeforeCondition}${whereCondition}
        ORDER BY ${orderBy} LIMIT ${limit} OFFSET ${offset}
      `);
      return rows.map(rowToPage);
  }

export async function getAllSlugs(exec: ScopedRead, opts?: { sourceId?: string }): Promise<Set<string>> {
      // v0.31.8 (D12): two-branch. With opts.sourceId return only that
      // source's slugs (reconcileLinks); without, the brain-wide union.
      if (opts?.sourceId) {
        const { rows } = await exec.run<{ slug: string }>(sqlFragment`SELECT slug FROM pages WHERE source_id = ${opts.sourceId}`);
        return new Set(rows.map((r) => r.slug));
      }
      const { rows } = await exec.run<{ slug: string }>(sqlFragment`SELECT slug FROM pages`);
      return new Set(rows.map((r) => r.slug));
  }

export async function listAllPageRefs(exec: LegacyUnscopedRead): Promise<Array<{ slug: string; source_id: string; updated_at: Date }>> {
    // v0.32.8: cross-source page enumeration. ORDER BY (source_id, slug) for
    // deterministic iteration (F11) — same-slug-different-source pages stay
    // grouped predictably. WHERE deleted_at IS NULL matches default getPage
    // visibility semantics (v0.26.5). #4304: updated_at projected so --since
    // walks can filter refs before any full-page fetch.
    const { rows } = await exec.run<{ slug: string; source_id: string; updated_at: string | Date }>(sqlFragment`
      SELECT slug, source_id, updated_at FROM pages
      WHERE deleted_at IS NULL
      ORDER BY source_id, slug
    `);
    return rows.map((r) => ({
      slug: r.slug,
      source_id: r.source_id,
      updated_at: r.updated_at instanceof Date ? r.updated_at : new Date(r.updated_at),
    }));
  }

/** PGLite returned `last_retrieved_at` as text on some paths; Postgres as `Date`. */
const toDomainBankRow = ((normalize) => (raw: Record<string, unknown>): DomainBankRow => {
  const r = normalize(raw);
  return {
    slug: r.slug as string,
    source_id: r.source_id as string,
    prefix: r.prefix as string | null,
    page_id: Number(r.page_id),
    title: r.title as string | null,
    compiled_truth: (r.compiled_truth as string | null) ?? '',
    connection_count: Number(r.connection_count),
    last_retrieved_at: r.last_retrieved_at as Date | null,
    representative_chunk_id: r.representative_chunk_id == null ? null : Number(r.representative_chunk_id),
  };
})(compileRowNormalizer<Record<string, unknown>>({ last_retrieved_at: 'date' }));

// v0.37.0 — domain-bank sampling (D14 + D5 + D10).
//
// `listPrefixSampledPages`: one page per prefix, tiebroken by inbound-link
// count (connection_count via LEFT JOIN to page_links). Stale-bias optional
// for LSD mode (D5). Source-scoped (D5). Excludes close-set slugs, and (like
// listCorpusSample) segmented pages, whose body column is NULL.
//
// Ranking inside each prefix partition:
//   1. stale_score DESC (when staleBias) — never-retrieved beats >90d-stale beats fresh
//   2. connection_count DESC — structural-centrality tiebreaker (D10)
//   3. slug ASC — deterministic for tests
export async function listPrefixSampledPages(scoped: ScopedReadRunner, opts: DomainBankSampleOpts): Promise<DomainBankRow[]> {
    if (opts.prefixes.length === 0) return [];
    const exclude = opts.excludeSlugs ?? [];
    const staleBias = opts.staleBias === true;
    const staleThreshold = opts.staleThresholdDays ?? 90;
    // Source scoping (D5, codex r2 #2 — federated array wins over scalar).
    const sourceIds = opts.sourceIds ?? null;
    const sourceId = opts.sourceId ?? null;
    return scoped(async (exec) => {
      const { rows } = await exec.run(sqlFragment`
      WITH prefix_pages AS (
        SELECT
          p.id AS page_id,
          p.slug,
          p.source_id,
          p.title,
          p.compiled_truth,
          GREATEST(p.last_retrieved_at, r.last_retrieved_at) AS last_retrieved_at,
          substring(p.slug from '^[^/]+/[^/]+') AS prefix,
          COUNT(pl.id) AS connection_count
        FROM pages p
        LEFT JOIN page_retrievals r ON r.page_id = p.id
        LEFT JOIN page_links pl ON pl.to_page_id = p.id
        WHERE p.deleted_at IS NULL AND p.body_storage = 'inline'
          AND substring(p.slug from '^[^/]+/[^/]+') = ANY(${opts.prefixes}::text[])
          AND (cardinality(${exclude}::text[]) = 0 OR NOT (p.slug = ANY(${exclude}::text[])))
          AND (
            (${sourceIds}::text[] IS NOT NULL AND p.source_id = ANY(${sourceIds}::text[]))
            OR (${sourceIds}::text[] IS NULL AND ${sourceId}::text IS NOT NULL AND p.source_id = ${sourceId})
            OR (${sourceIds}::text[] IS NULL AND ${sourceId}::text IS NULL)
          )
        GROUP BY p.id, p.slug, p.source_id, p.title, p.compiled_truth, p.last_retrieved_at, r.last_retrieved_at
      ),
      ranked AS (
        SELECT
          pp.*,
          (CASE WHEN ${staleBias}::boolean THEN
            CASE
              WHEN pp.last_retrieved_at IS NULL THEN 2
              WHEN pp.last_retrieved_at < NOW() - (${staleThreshold}::int * INTERVAL '1 day') THEN 1
              ELSE 0
            END
          ELSE 0
          END) AS stale_score,
          ROW_NUMBER() OVER (
            PARTITION BY pp.prefix
            ORDER BY
              (CASE WHEN ${staleBias}::boolean THEN
                CASE
                  WHEN pp.last_retrieved_at IS NULL THEN 2
                  WHEN pp.last_retrieved_at < NOW() - (${staleThreshold}::int * INTERVAL '1 day') THEN 1
                  ELSE 0
                END
              ELSE 0
              END) DESC,
              pp.connection_count DESC,
              pp.slug ASC
          ) AS rn
        FROM prefix_pages pp
      ),
      with_chunk AS (
        SELECT
          r.*,
          (
            SELECT cc.id FROM content_chunks cc
            WHERE cc.page_id = r.page_id AND cc.embedding IS NOT NULL
            ORDER BY cc.chunk_index ASC
            LIMIT 1
          ) AS representative_chunk_id
        FROM ranked r
        WHERE r.rn = 1
      )
      SELECT page_id, slug, source_id, title, compiled_truth, last_retrieved_at,
             prefix, connection_count, representative_chunk_id
      FROM with_chunk
      ORDER BY prefix
    `);
      return rows.map(toDomainBankRow);
    });
  }

// v0.37.0 — corpus-sampling fallback when prefix-stratified can't fill M.
// Deterministic with opts.seed (setseed before SELECT on the same executor;
// PostgresEngine's runner pins one connection with alwaysTransaction when
// seeded); random otherwise.
export async function listCorpusSample(scoped: ScopedReadRunner, opts: CorpusSampleOpts): Promise<DomainBankRow[]> {
    if (opts.n <= 0) return [];
    const exclude = opts.excludeSlugs ?? [];
    const sourceIds = opts.sourceIds ?? null;
    const sourceId = opts.sourceId ?? null;
    return scoped(async (exec) => {
      if (typeof opts.seed === 'number') {
        // Clamp to [-1, 1] required by setseed.
        const clamped = Math.max(-1, Math.min(1, opts.seed));
        await exec.run(sqlFragment`SELECT setseed(${clamped}::float8)`);
      }
      const { rows } = await exec.run(sqlFragment`
      WITH sampled AS (
        SELECT
          p.id AS page_id,
          p.slug,
          p.source_id,
          p.title,
          p.compiled_truth,
          GREATEST(p.last_retrieved_at, r.last_retrieved_at) AS last_retrieved_at,
          substring(p.slug from '^[^/]+/[^/]+') AS prefix,
          (SELECT COUNT(*) FROM page_links pl WHERE pl.to_page_id = p.id) AS connection_count
        FROM pages p
        LEFT JOIN page_retrievals r ON r.page_id = p.id
        WHERE p.deleted_at IS NULL AND p.body_storage = 'inline'
          AND (cardinality(${exclude}::text[]) = 0 OR NOT (p.slug = ANY(${exclude}::text[])))
          AND (
            (${sourceIds}::text[] IS NOT NULL AND p.source_id = ANY(${sourceIds}::text[]))
            OR (${sourceIds}::text[] IS NULL AND ${sourceId}::text IS NOT NULL AND p.source_id = ${sourceId})
            OR (${sourceIds}::text[] IS NULL AND ${sourceId}::text IS NULL)
          )
        ORDER BY RANDOM()
        LIMIT ${opts.n}
      )
      SELECT
        s.*,
        (
          SELECT cc.id FROM content_chunks cc
          WHERE cc.page_id = s.page_id AND cc.embedding IS NOT NULL
          ORDER BY cc.chunk_index ASC
          LIMIT 1
        ) AS representative_chunk_id
      FROM sampled s
    `);
      return rows.map(toDomainBankRow);
    });
  }

export async function resolveSlugs(
  exec: LegacyUnscopedRead,
  partial: string,
  opts?: { sourceId?: string; sourceIds?: string[]; excludePrivate?: boolean },
): Promise<string[]> {
    // v0.41.13 #1436: source scope. When neither opt is set the resolver
    // stays unscoped for back-compat with internal callers. The
    // `deleted_at IS NULL` filter excludes soft-deleted rows (v0.26.5) from
    // fuzzy candidates — they're not legitimate match targets for a remote
    // `get_page`.
    const sources = opts?.sourceIds?.length ? opts.sourceIds : null;
    const scalar = opts?.sourceId ?? null;
    const privacy = opts?.excludePrivate ? trustedSql(` AND ${privatePagesFilterForCaller('pages')}`) : sqlFragment``;
    const scopeFragment = sources
      ? sqlFragment` AND source_id = ANY(${sources}::text[])`
      : scalar
        ? sqlFragment` AND source_id = ${scalar}`
        : sqlFragment``;

    // Try exact match first
    const exact = await exec.run<{ slug: string }>(sqlFragment`SELECT slug FROM pages WHERE slug = ${partial} AND deleted_at IS NULL${scopeFragment}${privacy}`);
    if (exact.rows.length > 0) return [exact.rows[0].slug];

    // Fuzzy match via pg_trgm
    const fuzzy = await exec.run<{ slug: string }>(sqlFragment`
      SELECT slug, similarity(title, ${partial}) AS sim
      FROM pages
      WHERE deleted_at IS NULL AND (title % ${partial} OR slug ILIKE ${'%' + partial + '%'})${scopeFragment}${privacy}
      ORDER BY sim DESC
      LIMIT 5
    `);
    return fuzzy.rows.map((r) => r.slug);
  }

// ── v0.42.7 (#1696): link/timeline extraction freshness watermark ──

/**
 * Origins of wanted links whose target now exists: a live page with the wanted
 * slug (or, for a bare-name reference, the wanted basename) updated after the
 * origin last resolved it. Their next extraction creates the edge
 * (src/core/wanted-links.ts).
 */
/**
 * #9295: `producers` limits the wanted rows to the producers the caller's sweep re-resolves. A body-only sweep cannot
 * settle a frontmatter row, so counting one made the page stale after every sweep; the row stays unchecked and a
 * frontmatter sweep still finds it.
 */
function wantedOriginIsStale(producers?: readonly string[]) {
  const producer = producers ? sqlFragment` AND w.producer = ANY(${[...producers]}::text[])` : sqlFragment``;
  return sqlFragment`id IN (SELECT w.origin_page_id FROM wanted_links w
      JOIN pages t ON t.source_id = w.target_source_id AND t.slug = w.target_ref AND t.deleted_at IS NULL
      WHERE t.updated_at > w.checked_at${producer}
    UNION SELECT w.origin_page_id FROM wanted_links w
      JOIN pages t ON t.source_id = w.target_source_id AND regexp_replace(t.slug, '^.*/', '') = w.target_ref AND t.deleted_at IS NULL
      WHERE w.ref_kind = 'name' AND t.updated_at > w.checked_at${producer})`;
}

/**
 * GBRA-130 A-1: a page whose link extraction is held: its failure record is `held` at the page's current knowledge
 * revision and at the extractor generation `versionTs`. An edit or a generation change releases it.
 */
function linkExtractionHeld(versionTs?: string) {
  const generation = versionTs ? sqlFragment` AND links_extract_failure->>'generation' = ${versionTs}` : sqlFragment``;
  return sqlFragment`COALESCE(links_extract_failure->>'state' = 'held' AND links_extract_failure->>'revision' = knowledge_revision::text${generation}, false)`;
}

type StaleOpts = { sourceId?: string; versionTs?: string; attendance?: 'exclude' | 'blocked'; held?: 'exclude' | 'only'; wantedProducers?: readonly string[]; guests?: boolean };

/**
 * Shared stale-for-extraction predicate. `attendance` narrows it by the #5761
 * marker: a page is attendance-blocked while its marker equals its current
 * knowledge revision. Extraction itself never passes it, so it keeps
 * reconsidering blocked pages. `held` (GBRA-130 A-1) excludes or selects pages
 * whose link extraction is held (linkExtractionHeld); managed extraction excludes them.
 */
function stalePagesWhere(opts?: StaleOpts) {
  const wanted = wantedOriginIsStale(opts?.wantedProducers);
  const version = opts?.versionTs
    ? sqlFragment`(links_extracted_at IS NULL OR links_extracted_at < ${opts.versionTs}::timestamptz OR updated_at > links_extracted_at OR ${wanted})`
    : sqlFragment`(links_extracted_at IS NULL OR updated_at > links_extracted_at OR ${wanted})`;
  const source = opts?.sourceId ? sqlFragment` AND source_id = ${opts.sourceId}` : sqlFragment``;
  const attendance = opts?.attendance === 'exclude'
    ? sqlFragment` AND links_attendance_blocked_revision IS DISTINCT FROM knowledge_revision`
    : opts?.attendance === 'blocked' ? sqlFragment` AND links_attendance_blocked_revision = knowledge_revision` : sqlFragment``;
  const held = opts?.held === 'exclude' ? sqlFragment` AND NOT ${linkExtractionHeld(opts.versionTs)}`
    : opts?.held === 'only' ? sqlFragment` AND ${linkExtractionHeld(opts.versionTs)}` : sqlFragment``;
  // GBRA-504: a guest page is never extracted (guests/derived.ts), so it is never stale; `guests: false` is the shape before the guest-notes migration.
  const guests = opts?.guests === false ? sqlFragment`` : sqlFragment` AND ${trustedSql(guestSourceExclusionSql('extract', 'pages'))}`;
  // A quarantined page is hidden from search; it is neither re-extracted nor counted as stale.
  return sqlFragment`deleted_at IS NULL AND ${trustedSql(quarantineFilterFragment('pages'))} AND ${version}${source}${attendance}${held}${guests}`;
}

/** The pages (unaliased `pages` rows) link extraction still owes work: stale, not attendance-blocked, not held (rank_entities' remote coverage). */
export function staleForExtractionSql(versionTs: string): SqlFragment {
  return stalePagesWhere({ versionTs, attendance: 'exclude', held: 'exclude' });
}

/**
 * #6803 (D1): the live page count, the stale count without attendance-blocked pages and the attendance-blocked count
 * in one statement, so doctor's `links_extraction_lag` and the sync nudge share one predicate and one threshold input.
 * `attendance: false` is the pre-v180 shape (no marker column: nothing is attendance-blocked). `held` (GBRA-130 A-1) also
 * excludes held pages from the stale count and counts them (`held: false` is the pre-v258 shape: nothing is held).
 */
export function extractionLagStatement(opts: { sourceId?: string; versionTs: string; attendance: boolean; held?: boolean; guests?: boolean; wantedProducers?: readonly string[] }): { text: string; params: unknown[] } {
  const source = opts.sourceId ? sqlFragment` AND source_id = ${opts.sourceId}` : sqlFragment``;
  const base = { versionTs: opts.versionTs, wantedProducers: opts.wantedProducers, guests: opts.guests };
  const stale = stalePagesWhere({ ...base, ...(opts.attendance ? { attendance: 'exclude' as const } : {}), ...(opts.held ? { held: 'exclude' as const } : {}) });
  const blocked = opts.attendance ? sqlFragment`count(*) FILTER (WHERE ${stalePagesWhere({ ...base, attendance: 'blocked' })})::int` : sqlFragment`0`;
  const held = opts.held ? sqlFragment`count(*) FILTER (WHERE ${stalePagesWhere({ ...base, held: 'only' })})::int` : sqlFragment`0`;
  return renderFragment(sqlFragment`SELECT count(*)::int AS total, count(*) FILTER (WHERE ${stale})::int AS stale, ${blocked} AS attendance_blocked, ${held} AS held
    FROM pages WHERE deleted_at IS NULL AND ${trustedSql(quarantineFilterFragment('pages'))}${source}`);
}

/** GBRA-130 A-1: up to `limit` held pages with their failure code, oldest page first, for doctor's message. */
export function heldLinkExtractionStatement(opts: { sourceId?: string; versionTs: string; limit: number }): { text: string; params: unknown[] } {
  return renderFragment(sqlFragment`SELECT slug, source_id, links_extract_failure->>'code' AS code FROM pages
    WHERE ${stalePagesWhere({ sourceId: opts.sourceId, versionTs: opts.versionTs, held: 'only', guests: false })} ORDER BY id LIMIT ${opts.limit}`);
}

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

export async function listStalePagesForExtraction(exec: ScopedRead, opts: {
  batchSize: number;
  afterPageId?: number;
  sourceId?: string;
  versionTs?: string;
  held?: 'exclude';
  wantedProducers?: readonly string[];
}): Promise<StalePageRow[]> {
    const afterClause = opts.afterPageId != null ? sqlFragment` AND id > ${opts.afterPageId}` : sqlFragment``;
    // #1768: project a deterministic full-µs UTC string alongside updated_at.
    // to_char (not ::text — DateStyle-fragile) so extractStaleFromDB can stamp
    // links_extracted_at = the exact updated_at and the staleness predicate clears.
    // A segmented page is listed like an inline one (count and list share
    // stalePagesWhere): its body columns are NULL, so every extractor reads
    // its snapshot and, for a segmented page, the body block by block.
    const { text, params } = renderFragment(sqlFragment`SELECT id, slug, source_id, type, title, compiled_truth, timeline, frontmatter, updated_at,
                to_char(updated_at AT TIME ZONE 'UTC', 'YYYY-MM-DD"T"HH24:MI:SS.US"Z"') AS updated_at_iso
           FROM pages
           WHERE ${stalePagesWhere(opts)}${afterClause}
           ORDER BY id
           LIMIT ${opts.batchSize}`);
    const { rows } = await exec.unsafe(text, params);
    return rows.map(rowToStalePage);
  }

export async function markPagesExtractedBatch(
  exec: SqlExecutor,
  refs: Array<{ slug: string; source_id: string; extractedAt?: string }>,
  defaultExtractedAt: string,
): Promise<number> {
    if (refs.length === 0) return 0;
    const slugs = refs.map(r => r.slug);
    const srcs = refs.map(r => r.source_id);
    // Per-ref timestamp (D4 race fix): extract --stale passes each row's read
    // updated_at; sites that omit it fall back to defaultExtractedAt.
    const tss = refs.map(r => r.extractedAt ?? defaultExtractedAt);
    // #3957: the stamped-row count is observable so callers (stampExtracted)
    // can surface a wrong-source shortfall instead of claiming success while
    // every ref missed.
    // #5761: a stamped page's links were published, so its attendance marker (and GBRA-130's failure record) clears with the watermark.
    const result = await exec.run(sqlFragment`
      UPDATE pages p SET links_extracted_at = v.ts::timestamptz,
        links_attendance_blocked_revision = NULL, links_attendance_blocked_at = NULL, links_extract_failure = NULL
      FROM unnest(${slugs}::text[], ${srcs}::text[], ${tss}::text[]) AS v(slug, source_id, ts)
      WHERE p.slug = v.slug AND p.source_id = v.source_id
    `);
    return result.affectedRows;
  }

/**
 * #5761: record that extraction left a page stale only because an attendee did
 * not resolve. Written only while the captured knowledge revision is still the
 * page's current one, so a concurrent edit is never marked.
 */
export async function markPagesAttendanceBlocked(
  exec: SqlExecutor,
  refs: Array<{ slug: string; source_id: string; revision: string }>,
): Promise<number> {
    if (refs.length === 0) return 0;
    const result = await exec.run(sqlFragment`
      UPDATE pages p SET links_attendance_blocked_revision = v.revision::uuid, links_attendance_blocked_at = now()
      FROM unnest(${refs.map(r => r.slug)}::text[], ${refs.map(r => r.source_id)}::text[], ${refs.map(r => r.revision)}::text[])
        AS v(slug, source_id, revision)
      WHERE p.slug = v.slug AND p.source_id = v.source_id AND p.knowledge_revision = v.revision::uuid AND p.deleted_at IS NULL
    `);
    return result.affectedRows;
  }

/**
 * #6454: live pages whose stored frontmatter still carries one of the parser's
 * reserved top-level keys (`type`, `title`, `tags`, `slug`; older imports kept
 * them). Not drift since `comparableFrontmatter` (markdown.ts), but hygiene
 * `gbrain repair frontmatter-keys` sweeps on consent. Counts per source for
 * the doctor check; the listing pages by id for the preview-bound sweep.
 */
export const RESERVED_FRONTMATTER_KEYS_SQL = ['type', 'title', 'tags', 'slug'];
const normalizeStaleKeyCount = compileRowNormalizer<{ source_id: string; n: number }>({ n: 'bigint' });
export async function countStaleFrontmatterKeyPages(exec: ScopedRead, opts: { sourceIds: string[] }): Promise<Array<{ source_id: string; n: number }>> {
    if (opts.sourceIds.length === 0) return [];
    const { rows } = await exec.run<{ source_id: string; n: number | string }>(sqlFragment`
      SELECT source_id, count(*)::bigint AS n FROM pages
      WHERE source_id = ANY(${opts.sourceIds}::text[]) AND deleted_at IS NULL AND frontmatter ?| ${RESERVED_FRONTMATTER_KEYS_SQL}::text[]
      GROUP BY source_id ORDER BY source_id
    `);
    return rows.map(row => normalizeStaleKeyCount(row));
  }
export async function listStaleFrontmatterKeyPages(exec: ScopedRead, opts: { sourceId: string; afterId: number; limit: number }):
  Promise<Array<{ id: number; slug: string; keys: string[] }>> {
    const { rows } = await exec.run<{ id: number | string; slug: string; keys: string[] }>(sqlFragment`
      SELECT id, slug, ARRAY(SELECT k FROM unnest(${RESERVED_FRONTMATTER_KEYS_SQL}::text[]) AS k WHERE frontmatter ? k ORDER BY k) AS keys
      FROM pages
      WHERE source_id = ${opts.sourceId} AND deleted_at IS NULL AND frontmatter ?| ${RESERVED_FRONTMATTER_KEYS_SQL}::text[] AND id > ${opts.afterId}
      ORDER BY id LIMIT ${opts.limit}
    `);
    return rows.map(row => ({ id: Number(row.id), slug: row.slug, keys: row.keys }));
  }
/** Strips the reserved keys from the named live pages of one source; the revision trigger advances each changed row. */
export async function stripStaleFrontmatterKeys(exec: SqlExecutor, opts: { sourceId: string; ids: number[] }): Promise<number> {
    if (opts.ids.length === 0) return 0;
    const result = await exec.run(sqlFragment`
      UPDATE pages SET frontmatter = frontmatter - ${RESERVED_FRONTMATTER_KEYS_SQL}::text[]
      WHERE source_id = ${opts.sourceId} AND id = ANY(${opts.ids}::int[]) AND deleted_at IS NULL AND frontmatter ?| ${RESERVED_FRONTMATTER_KEYS_SQL}::text[]
    `);
    return result.affectedRows;
  }

export async function findByTitleFuzzy(
  exec: LegacyUnscopedRead,
  name: string,
  dirPrefix?: string,
  minSimilarity: number = 0.55,
  sourceId?: string,
): Promise<{ slug: string; similarity: number } | null> {
    // Use `%` so the existing idx_pages_trgm GIN index can prune candidates
    // when the requested threshold is at least pg_trgm's default 0.3. Below
    // that, retain the exact comparison path so low-threshold callers do not
    // lose valid matches. Keep the explicit comparison in both paths as the
    // result contract.
    //
    // Tie-breaker: sort by slug after similarity so re-runs return the
    // same winner when multiple pages score equally (prevents churn
    // in put_page auto-link reconciliation).
    //
    // `sourceId` + `deleted_at IS NULL` mirror the filters `tryFuzzyMatch`
    // in `src/core/entities/resolve.ts` got via #1436 (v0.41.13.0). Without
    // them, fuzzy resolution could suggest cross-source slugs that the
    // caller then silently drops at the FK filter in
    // `operations.ts:reconcileLinks` (the `allSlugs` filter) — making it
    // look like the match failed when in fact it picked the wrong page.
    const prefixPattern = dirPrefix ? `${dirPrefix}/%` : '%';
    const trgmPrefilter = minSimilarity >= 0.3
      ? sqlFragment`title % ${name} AND`
      : sqlFragment``;
    const { rows } = sourceId
      ? await exec.run<{ slug: string; sim: number }>(sqlFragment`
          SELECT slug, similarity(title, ${name}) AS sim
          FROM pages
          WHERE ${trgmPrefilter} similarity(title, ${name}) >= ${minSimilarity}
            AND slug LIKE ${prefixPattern}
            AND source_id = ${sourceId}
            AND deleted_at IS NULL
          ORDER BY sim DESC, slug ASC
          LIMIT 1
        `)
      : await exec.run<{ slug: string; sim: number }>(sqlFragment`
          SELECT slug, similarity(title, ${name}) AS sim
          FROM pages
          WHERE ${trgmPrefilter} similarity(title, ${name}) >= ${minSimilarity}
            AND slug LIKE ${prefixPattern}
          ORDER BY sim DESC, slug ASC
          LIMIT 1
        `);
    if (rows.length === 0) return null;
    return { slug: rows[0].slug, similarity: rows[0].sim };
  }

export async function getPageTimestamps(exec: LegacyUnscopedRead, slugs: string[]): Promise<Map<string, Date>> {
    if (slugs.length === 0) return new Map();
    const { rows } = await exec.run<{ slug: string; ts: string | Date }>(sqlFragment`
      SELECT slug, COALESCE(updated_at, created_at) as ts
      FROM pages WHERE slug = ANY(${slugs}::text[])
    `);
    return new Map(rows.map(r => [r.slug, new Date(r.ts as string)]));
  }

/**
 * `get_versions` columns. Explicit, so the write attribution columns on
 * page_versions (who wrote and who archived each snapshot) never reach a
 * plain `read` caller; trusted and admin callers get them through
 * `get_write_attribution`'s resolver in `ops/attribution.ts`.
 */
const PAGE_VERSION_COLUMNS = trustedSql('pv.id, pv.page_id, pv.compiled_truth, pv.frontmatter, pv.snapshot_at, pv.knowledge_revision, '
  + 'pv.timeline, pv.title, pv.type, pv.tags, pv.is_deleted, pv.source_path');
const PAGE_VERSION_METADATA_COLUMNS = trustedSql('pv.id, pv.page_id, pv.frontmatter, pv.snapshot_at, pv.knowledge_revision, '
  + 'pv.title, pv.type, pv.tags, pv.is_deleted, pv.source_path');
/** Wave B: the reference columns of a segmented version, kept only on segmented rows so inline rows stay as they were. */
const PAGE_VERSION_SEGMENTED_COLUMNS = trustedSql('pv.body_storage, pv.body_generation, pv.body_bytes, pv.content_hash');

/**
 * GBRA-217: a snapshot's `replaced_because` as a reader may see it. A trusted reader gets the stored reason. A reader
 * held to world-visible pages (`excludePrivate`) gets it only when the snapshot's successor is world-visible too (the
 * next snapshot of the page, or the live page, which the caller's own page predicate checks) and the successor was not
 * pruned (`successor_pruned`: its visibility can no longer be checked, so the reason is withheld). The successor is
 * read over every snapshot of the page, never only the rows the reader may see. Shared by get_versions and get_page as_of.
 * GBRA-286: a successor snapshot that names readers the caller does not meet (GBRA-218 readable_by) withholds it too.
 */
export function replacedBecauseSql(pv: string, excludePrivate: boolean): string {
  if (!excludePrivate) return `${pv}.change_reason`;
  return `CASE WHEN ${pv}.change_reason IS NULL OR COALESCE(${pv}.successor_pruned, false) THEN NULL
    WHEN COALESCE((SELECT ${privateSnapshotFilterFragment('rb_next')} AND ${callerReadersAllowedSql('rb_next', 'snapshot')} FROM page_versions rb_next
      WHERE rb_next.page_id = ${pv}.page_id AND rb_next.id > ${pv}.id ORDER BY rb_next.id LIMIT 1), true) THEN ${pv}.change_reason END`;
}

/** GBRA-217: each version's parsed valid time (the shared normaliser), so a timeline reads in one get_versions call. */
const VERSION_VALID_TIME_COLUMNS = `${validDateSql("pv.frontmatter->>'valid_from'")} AS valid_from, ${validDateSql("pv.frontmatter->>'valid_until'")} AS valid_until`;

/**
 * Newest first (`pv.id` breaks snapshot_at ties). Scope, privacy and
 * re-derivation (a page a purge hid) predicates sit in WHERE, so `limit`
 * bounds only rows the caller may read; `includeBody: false` never selects
 * the body columns.
 */
export async function getVersions<B extends boolean = true>(
  exec: LegacyUnscopedRead,
  slug: string,
  opts?: GetVersionsOpts<B>,
): Promise<PageVersionRows<B>> {
    const privacy = opts?.excludePrivate
      ? trustedSql(`AND ${privatePagesFilterForCaller('p')} AND ${callerReadersAllowedSql('pv', 'snapshot')} AND ${privacyForCaller('p', privateSnapshotFilterFragment('pv'))}${currentCallerAccess().hidePrivate ? " AND NOT COALESCE(pv.frontmatter ? 'pinned_question', false)" : ''}`) : sqlFragment``;
    const scope = 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 columns = opts?.includeBody === false ? PAGE_VERSION_METADATA_COLUMNS : PAGE_VERSION_COLUMNS;
    const limit = opts?.limit !== undefined ? sqlFragment`LIMIT ${opts.limit}` : sqlFragment``;
    const { rows } = await exec.run(sqlFragment`
      SELECT ${columns}, ${PAGE_VERSION_SEGMENTED_COLUMNS}, ${trustedSql(replacedBecauseSql('pv', opts?.excludePrivate === true))} AS replaced_because,
        ${trustedSql(VERSION_VALID_TIME_COLUMNS)} FROM page_versions pv
      JOIN pages p ON p.id = pv.page_id
      WHERE p.slug = ${slug} ${scope}
        ${privacy} AND ${trustedSql(rederiveHiddenSql('pages', 'p'))}
      ORDER BY pv.snapshot_at DESC, pv.id DESC
      ${limit}
    `);
    return rows.map(row => {
      const { body_storage, body_generation, body_bytes, content_hash, ...version } = row as Record<string, unknown>;
      return body_storage === 'segmented'
        ? { ...version, body_storage, body_generation: Number(body_generation), body_bytes: Number(body_bytes), content_hash }
        : version;
    }) as PageVersionRows<B>;
  }

export async function revertToVersion(
  exec: SqlExecutor,
  slug: string,
  versionId: number,
  opts?: { sourceId?: string },
): Promise<void> {
    // Wave B: a segmented page or version is refused before anything is written (segment writer: PR 3).
    const scope = opts?.sourceId ? sqlFragment`AND p.source_id = ${opts.sourceId}` : sqlFragment``;
    const { rows: segmented } = await exec.run(sqlFragment`
      SELECT 1 FROM pages p JOIN page_versions pv ON pv.page_id = p.id
      WHERE p.slug = ${slug} ${scope} AND pv.id = ${versionId}
        AND (p.body_storage = 'segmented' OR pv.body_storage = 'segmented')
    `);
    if (segmented.length > 0) throw segmentedRevertRefusal(slug, versionId);
    // v0.31.8 (D12): two-branch. With opts.sourceId, scope BOTH the page lookup
    // AND the version reference. Without it, multi-source brains can revert
    // the wrong same-slug page.
    if (opts?.sourceId) {
      await exec.run(sqlFragment`
        UPDATE pages SET
          compiled_truth = pv.compiled_truth,
          frontmatter = pv.frontmatter,
          chunker_version = ${trustedSql(bodyWriteChunkVersion('pv.compiled_truth', 'pages.timeline'))},
          updated_at = now()
        FROM page_versions pv
        WHERE pages.slug = ${slug} AND pages.source_id = ${opts.sourceId}
              AND pv.id = ${versionId} AND pv.page_id = pages.id
      `);
      return;
    }
    await exec.run(sqlFragment`
      UPDATE pages SET
        compiled_truth = pv.compiled_truth,
        frontmatter = pv.frontmatter,
          chunker_version = ${trustedSql(bodyWriteChunkVersion('pv.compiled_truth', 'pages.timeline'))},
        updated_at = now()
      FROM page_versions pv
      WHERE pages.slug = ${slug} AND pv.id = ${versionId} AND pv.page_id = pages.id
    `);
  }

/**
 * The rename statement plus its slug alias and bindings, run on the
 * transaction the engine opened (`exec` is that transaction's executor,
 * `tx` its engine clone). Source-qualified so a rename in source A doesn't
 * sweep up same-slug rows in sources B/C/D. The move advances the page's
 * knowledge revision, so it first versions the page as it stood: page-at-time
 * (belief ledger W2) reads the body before the rename from that preimage.
 */
export async function updateSlug(exec: SqlExecutor, tx: BrainEngine, oldSlug: string, newSlug: string, sourceId: string): Promise<number> {
      const preimage = await tx.readPageSnapshot(oldSlug, { sourceId, includeDeleted: true });
      if (preimage) await createPageVersion(tx, oldSlug, sourceId, preimage);
      // origin-guard-ok: the moved row keeps its own origin and liveness, so it adds no claimant (#6948); callers that record a new path bind it with bindOrigin
      const moved = await exec.executeRaw(
        `UPDATE pages SET slug = $1, updated_at = now() WHERE slug = $2 AND source_id = $3 RETURNING id`,
        [newSlug, oldSlug, sourceId],
      );
      if (moved.length > 0) {
        await recordRenameAlias(tx, sourceId, oldSlug, newSlug);
        await moveSlugBindings(tx, sourceId, oldSlug, newSlug);
      }
      // #3056: rows moved — a zero-row UPDATE does not throw, so the count is
      // the only way callers can see the no-op.
      return moved.length;
  }

/**
 * Replace a page's frontmatter alias set under its page-key lock, inside the
 * engine's transaction. Derived rows (`origin` declared/subject, written by
 * the mention pass) are left alone.
 */
export async function setPageAliases(
  exec: SqlExecutor,
  tx: Pick<BrainEngine, 'lockPageKeys'>,
  slug: string,
  sourceId: string,
  aliasNorms: string[],
): Promise<void> {
    const uniq = Array.from(new Set(aliasNorms.filter(a => a.length > 0)));
      await tx.lockPageKeys([{ sourceId, slug }]);
      await exec.executeRaw("DELETE FROM page_aliases WHERE source_id=$1 AND slug=$2 AND origin='frontmatter'", [sourceId, slug]);
      if (!uniq.length) return;
      await exec.executeRaw(`INSERT INTO page_aliases (source_id,alias_norm,slug)
        SELECT $1,a,$2 FROM unnest($3::text[]) AS a ON CONFLICT DO NOTHING`, [sourceId, slug, uniq]);
  }

export async function resolveSlugWithAliasDetailed(
  exec: LegacyUnscopedRead,
  slug: string,
  sourceOrSources: string | readonly string[],
  opts?: { excludePrivate?: boolean },
): Promise<{ canonical_slug: string; source_id: string } | null> {
    const sources = Array.isArray(sourceOrSources) ? sourceOrSources : [sourceOrSources];
    if (sources.length === 0) return null;
    const privacy = opts?.excludePrivate ? trustedSql(`AND EXISTS (SELECT 1 FROM pages p WHERE p.slug = slug_aliases.canonical_slug AND p.source_id = slug_aliases.source_id AND p.deleted_at IS NULL AND ${privatePagesFilterForCaller('p')})`) : sqlFragment``;
    try {
      const { rows } = await exec.run<{ canonical_slug: string; source_id: string }>(sqlFragment`
        SELECT canonical_slug, source_id
        FROM slug_aliases
        WHERE alias_slug = ${slug}
          AND source_id = ANY(${sources}::text[]) ${privacy}
        ORDER BY array_position(${sources}::text[], source_id), id
      `);
      if (rows.length === 0) return null;
      if (rows.length > 1) {
        warnOncePerProcess(
          `resolveSlugWithAlias:multi_match:${slug}`,
          `[resolveSlugWithAlias] multi_match: alias '${slug}' exists in ${rows.length} sources; returning first by sourceOrSources order.`,
        );
      }
      return { canonical_slug: rows[0].canonical_slug, source_id: rows[0].source_id };
    } catch (e) {
      // Pre-v105 brain: slug_aliases table doesn't exist yet. Defense-in-depth
      // per the engine interface contract.
      if (isMissingSchemaObject(e, 'table', 'slug_aliases')) return null;
      throw e;
    }
  }