Skip to content

Garry's Opinionated OpenClaw/Hermes Agent Brain

TypeScript
42.7 KB
Raw Blame
/**
 * Links: one SQL implementation for both engines (refactor wave 1,
 * W1-extended). Statement text is PostgresEngine's master text (SQL-text
 * golden `sql-text/links.json`); PGLite runs the same statements
 * (docs/designs/refactor-wave-1/w1-inventory.md, "links").
 *
 * The JSONB batch writes keep master's executeRaw path (executeRawJsonb over
 * the executor); the engine re-resolves the executor on each batchRetry
 * attempt. `getLinks` / `getBacklinks` / `listLinkSources` ran inside
 * `withScopedReadTransaction` on master and take `ScopedRead`; the other reads
 * take `LegacyUnscopedRead` (EO4 inventory). `replaceDerivedLinks` already
 * shares `derived-links.ts`.
 */
import type { LinkBatchInput, TraverseGraphOpts } from '../engine.ts';
import type { Link, GraphNode, GraphPath } from '../types.ts';
import { executeRawJsonb } from '../sql-query.ts';
import { sanitizeForJsonb, buildLinkRows } from '../batch-rows.ts';
import { privatePagesFilterForCaller, privateLinkOriginFilterForCaller } from '../search/private-visibility.ts';
import { TRAVERSE_PATH_ROW_CAP, TRAVERSE_WALK_ROW_CAP } from '../engine-constants.ts';
import { PageMissingError } from '../engine-errors.ts';
import { QUARANTINE_FILTER_FRAGMENT } from '../quarantine.ts';
import type { SqlExecutor } from './executor.ts';
import type { LegacyUnscopedRead, ScopedRead } from './brands.ts';
import { sqlFragment, trustedSql, type SqlFragment } from './fragment.ts';
import { refreshRelationships, type RelationshipKey } from '../link-relationships.ts';
import { isTemporalLinkType, relationshipFilterSql, temporalLinkJoinSql, TEMPORAL_LINK_SELECT_SQL, annotateTemporalRow } from '../link-validity.ts';
import type { LinkReadScope } from '../link-validity.ts';

/** Bounded link reads annotate rows with relationship status (never filter) when a temporal read is requested. */
function finishTemporal(rows: unknown[], opts?: LinkReadScope): Link[] {
  const t = opts?.page?.count ? undefined : opts?.temporal;
  return (t ? rows.map(r => annotateTemporalRow(r as Link, t.asOf)) : rows) as Link[];
}

/** Run one link read; a counting page (#7075) runs it as count(*) and returns one row carrying `link_total`. */
async function runLinkRead(exec: ScopedRead, opts: LinkReadScope | undefined, statement: SqlFragment): Promise<unknown[]> {
  if (!opts?.page?.count) return (await exec.run(statement)).rows;
  return (await exec.run(sqlFragment`SELECT count(*)::int AS link_total FROM (${statement}) counted`)).rows;
}

/** WHERE fragment that keeps only edges matching the caller's temporal policy (walks and fanout). */
function temporalWalkFilter(alias: string, opts?: LinkReadScope) {
  return opts?.temporal ? trustedSql(`AND ${relationshipFilterSql(alias, { ...opts.temporal, excludePrivate: opts.excludePrivate })}`) : sqlFragment``;
}

const ORIGIN_COLUMNS = { l: 'l.origin_page_id', l2: 'l2.origin_page_id' } as const;

/**
 * Keeps an edge only while its origin page (the page that authored it) is live
 * and, under a source scope, inside that scope. An edge with no origin page
 * passes.
 */
function liveOriginFilter(link: keyof typeof ORIGIN_COLUMNS, scope?: { sourceId?: string; sourceIds?: string[] }) {
  const inScope = scope?.sourceIds?.length
    ? sqlFragment` AND lo.source_id = ANY(${scope.sourceIds}::text[])`
    : scope?.sourceId ? sqlFragment` AND lo.source_id = ${scope.sourceId}` : sqlFragment``;
  const col = trustedSql(ORIGIN_COLUMNS[link]);
  return sqlFragment`AND (${col} IS NULL OR EXISTS (SELECT 1 FROM pages lo WHERE lo.id = ${col} AND lo.deleted_at IS NULL${inScope}))`;
}

/**
 * SQL pieces of an opt-in keyset page (LinkReadScope.page, #6876). Without a
 * page every piece renders master's statement text, so internal callers read
 * every row. With one, link_type, the keyset bounds and the temporal policy
 * filter in SQL before LIMIT, and each row carries `link_id`.
 */
function linkPageSql(opts: LinkReadScope | undefined, near: 'from' | 'to') {
  const page = opts?.page;
  if (!page) return { context: sqlFragment`l.context`, id: sqlFragment``, where: sqlFragment``, limit: sqlFragment`` };
  const chars = page.contextChars;
  const context = chars === undefined
    ? sqlFragment`l.context`
    : sqlFragment`CASE WHEN char_length(l.context) > ${chars} THEN left(l.context, ${chars - 1}) || '…' ELSE l.context END AS context`;
  const rel = page.relationship;
  const relationship = !rel
    ? sqlFragment``
    : rel.keep === 'match'
      ? sqlFragment` ${temporalWalkFilter('l', { temporal: rel.policy, excludePrivate: opts?.excludePrivate })}`
      : sqlFragment` ${trustedSql(`AND NOT (${relationshipFilterSql('l', { ...rel.policy, excludePrivate: opts?.excludePrivate })})`)}`;
  const linkType = page.linkType !== undefined ? sqlFragment` AND l.link_type = ${page.linkType}` : sqlFragment``;
  const ft = page.fromType;
  const fromType = !ft
    ? sqlFragment``
    : sqlFragment` AND (f.type = ${ft.type}${ft.type === 'untyped' ? sqlFragment` OR f.type IS NULL OR f.type = ''` : sqlFragment``}
        OR EXISTS (SELECT 1 FROM unnest(${ft.aliasSources}::text[], ${ft.aliases}::text[]) AS k(s, t) WHERE k.s = f.source_id AND k.t = f.type))`;
  // A paged read pinned to one source inside a grant (an identity member's
  // read) keeps only that source's page, so a same-slug page elsewhere in the
  // grant never takes the member's LIMIT rows.
  const pinned = opts?.sourceIds?.length && opts.sourceId !== undefined
    ? (near === 'from' ? sqlFragment` AND f.source_id = ${opts.sourceId}` : sqlFragment` AND t.source_id = ${opts.sourceId}`)
    : sqlFragment``;
  const after = page.afterId !== undefined ? sqlFragment` AND l.id > ${page.afterId}` : sqlFragment``;
  const through = page.throughId !== undefined ? sqlFragment` AND l.id <= ${page.throughId}` : sqlFragment``;
  return {
    context,
    id: sqlFragment`, l.id AS link_id`,
    where: sqlFragment`${linkType}${fromType}${pinned}${after}${through}${relationship}`,
    limit: page.limit !== undefined ? sqlFragment` LIMIT ${page.limit}` : sqlFragment``,
  };
}

export async function addLink(
  exec: SqlExecutor,
    from: string,
    to: string,
    context?: string,
    linkType?: string,
    linkSource?: string,
    originSlug?: string,
    originField?: string,
    opts?: { fromSourceId?: string; toSourceId?: string; originSourceId?: string },
  ): Promise<void> {
    const fromSrc = opts?.fromSourceId ?? 'default';
    const toSrc = opts?.toSourceId ?? 'default';
    const originSrc = opts?.originSourceId ?? 'default';

    // Default link_source to 'markdown' for back-compat with pre-v0.13 callers.
    const src = linkSource ?? 'markdown';
    // #4109: resolve both required endpoints and upsert from ONE statement
    // snapshot — a separate pre-check raced concurrent hard deletes (a delete
    // winning between check and insert made a zero-row upsert look like
    // success). The lookups stay source-qualified per endpoint (JOIN-on-
    // (slug, source_id), not the pre-v0.18 cross-product that fanned out
    // across sources containing either slug). FOR KEY SHARE makes a
    // concurrent hard delete linearize around the mutation: a delete that
    // wins first is observed as a missing endpoint; a mutation that wins
    // first holds the referenced rows through the insert instead of leaking
    // a raw FK violation.
    const [result] = (await exec.run(sqlFragment`
      WITH endpoint_state AS (
        SELECT
          (SELECT id FROM pages WHERE slug = ${from} AND source_id = ${fromSrc} FOR KEY SHARE) AS from_id,
          (SELECT id FROM pages WHERE slug = ${to} AND source_id = ${toSrc} FOR KEY SHARE) AS to_id,
          (SELECT id FROM pages WHERE slug = ${originSlug ?? null} AND source_id = ${originSrc} FOR KEY SHARE) AS origin_id
      ), upserted AS (
        INSERT INTO links (from_page_id, to_page_id, link_type, context, link_source, origin_page_id, origin_field)
        SELECT s.from_id, s.to_id, ${linkType || ''}, ${sanitizeForJsonb(context || '')}, ${src}, s.origin_id, ${originField ?? null}
        FROM endpoint_state s
        WHERE s.from_id IS NOT NULL AND s.to_id IS NOT NULL
        ON CONFLICT (from_page_id, to_page_id, link_type, link_source, origin_page_id) DO UPDATE SET
          context = EXCLUDED.context,
          origin_field = EXCLUDED.origin_field
        RETURNING 1
      )
      SELECT
        endpoint_state.from_id IS NOT NULL AS from_exists,
        endpoint_state.to_id IS NOT NULL AS to_exists,
        endpoint_state.from_id, endpoint_state.to_id
      FROM endpoint_state
    `)).rows as Array<{ from_exists: boolean; to_exists: boolean; from_id: number | null; to_id: number | null }>;
    if (!result?.from_exists) throw new PageMissingError('addLink', 'from', from, fromSrc);
    if (!result.to_exists) throw new PageMissingError('addLink', 'to', to, toSrc);
    if (isTemporalLinkType(linkType)) {
      await refreshRelationships(exec, [{ from_page_id: Number(result.from_id), to_page_id: Number(result.to_id), link_type: linkType! }]);
    }
  }


/** One code path a markdown page cites (`src/core/sync.ts:42`), with the slug of its code page. */
export interface CodeReference { codeSlug: string; path: string; line?: number }

/**
 * The doc↔impl edges for every code path one markdown page cites, in one
 * statement (#6712 L6; the import paths wrote two savepointed addLink calls per
 * reference). Each reference writes what the addLink pair wrote: page→code
 * `documents` (context `cited at <path>:<line>`, or the path) and code→page
 * `documented_by` (context the path), both `markdown` edges whose origin is the
 * page, field `compiled_truth`. Endpoints resolve source-qualified in the
 * page's source under FOR KEY SHARE in the same statement snapshot; an edge
 * whose endpoint is missing is skipped alone (a code page not imported yet);
 * an existing edge takes the new context and origin_field; a repeated edge
 * keeps its last occurrence, as sequential upserts would. Returns the edges
 * written (inserted or updated).
 */
export async function addCodeReferenceLinks(exec: Pick<SqlExecutor, 'executeRaw'>, slug: string, sourceId: string, refs: CodeReference[]): Promise<number> {
  const edges = new Map<string, { from_slug: string; to_slug: string; link_type: string; context: string }>();
  for (const ref of refs) {
    for (const [from, to, linkType, context] of [
      [slug, ref.codeSlug, 'documents', ref.line ? `cited at ${ref.path}:${ref.line}` : ref.path],
      [ref.codeSlug, slug, 'documented_by', ref.path],
    ] as const) {
      const key = JSON.stringify([from, to, linkType]);
      edges.delete(key);
      edges.set(key, { from_slug: from, to_slug: to, link_type: linkType, context: sanitizeForJsonb(context) });
    }
  }
  if (!edges.size) return 0;
  const rows = [...edges.values()].map((edge, ord) => ({ ...edge, ord }));
  const written = await executeRawJsonb<RelationshipKey>(exec,
    `WITH v AS (
       SELECT * FROM jsonb_to_recordset(($3::jsonb)->'rows') AS v(ord int, from_slug text, to_slug text, link_type text, context text)
     ), endpoints AS (
       SELECT p.id, p.slug FROM pages p
       WHERE p.source_id = $1 AND p.slug IN (SELECT from_slug FROM v UNION SELECT to_slug FROM v)
       ORDER BY p.id
       FOR KEY SHARE
     )
     INSERT INTO links (from_page_id, to_page_id, link_type, context, link_source, origin_page_id, origin_field)
     SELECT f.id, t.id, v.link_type, v.context, 'markdown', o.id, 'compiled_truth'
     FROM v
     JOIN endpoints f ON f.slug = v.from_slug
     JOIN endpoints t ON t.slug = v.to_slug
     LEFT JOIN endpoints o ON o.slug = $2::text
     ORDER BY v.ord
     ON CONFLICT (from_page_id, to_page_id, link_type, link_source, origin_page_id) DO UPDATE SET
       context = EXCLUDED.context,
       origin_field = EXCLUDED.origin_field
     RETURNING from_page_id, to_page_id, link_type`,
    [sourceId, slug],
    [{ rows }],
  );
  await refreshRelationships(exec, written);
  return written.length;
}

export async function addLinksBatch(exec: SqlExecutor, links: LinkBatchInput[]): Promise<number> {
    // #1861: pass the batch as one JSONB document via jsonb_to_recordset instead
    // of N parallel unnest(${arr}::text[]). The old text[] array-literal path
    // crashed Postgres ("malformed array literal") on free-text context strings
    // (calendar/Zoom lines with commas, quotes, braces, em-dashes); JSONB encodes
    // arbitrary text safely and dodges the 65535-param cap. Binding goes through
    // executeRawJsonb (the audited cross-engine JSONB contract) with an OBJECT
    // wrapper { rows } — a bare top-level array through postgres.js would re-enter
    // the same array serializer this fix exists to avoid. Row construction +
    // NUL-stripping + exact defaulting live in buildLinkRows (shared with PGLite).
    const rows = buildLinkRows(links);
    const insert = (target: SqlExecutor) => executeRawJsonb<RelationshipKey>(
      target,
      `INSERT INTO links (from_page_id, to_page_id, link_type, context, link_source, link_kind, origin_page_id, origin_field, resolution_type)
       SELECT f.id, t.id, v.link_type, v.context, v.link_source, v.link_kind, o.id, v.origin_field, v.resolution_type
       FROM jsonb_to_recordset(($1::jsonb)->'rows') AS v(
         from_slug text, to_slug text, link_type text, context text, link_source text,
         origin_slug text, origin_field text, from_source_id text, to_source_id text,
         origin_source_id text, link_kind text, resolution_type text
       )
       JOIN pages f ON f.slug = v.from_slug AND f.source_id = v.from_source_id
       JOIN pages t ON t.slug = v.to_slug AND t.source_id = v.to_source_id
       LEFT JOIN pages o ON o.slug = v.origin_slug AND o.source_id = v.origin_source_id
       ON CONFLICT (from_page_id, to_page_id, link_type, link_source, origin_page_id) DO NOTHING
       RETURNING from_page_id, to_page_id, link_type`,
      [],
      [{ rows }],
    );
    // Temporal relation types also refresh their relationship state in the
    // same transaction; batches of plain references skip it entirely.
    if (!rows.some(r => isTemporalLinkType(r.link_type))) return (await insert(exec)).length;
    return exec.transaction(async tx => {
      const inserted = await insert(tx);
      await refreshRelationships(tx, inserted);
      return inserted.length;
    });
  }


  // #3674 — see BrainEngine.removeLinksByPagesAndSource JSDoc. Identical SQL
  // shape in PGLiteEngine (parity). JSONB recordset binding (never
  // JSON.stringify into ::jsonb — executeRawJsonb passes raw objects).
export async function removeLinksByPagesAndSource(
  exec: SqlExecutor,
    pages: Array<{ slug: string; source_id: string }>,
    opts: {
      linkSource: string;
      keepTypedNerPairs?: Array<{
        from_slug: string; from_source_id: string;
        to_slug: string; to_source_id: string;
      }>;
    },
  ): Promise<number> {
    if (pages.length === 0) return 0;
    const payload = { pages, keep: opts.keepTypedNerPairs ?? [] };
    const rows = await executeRawJsonb(
      exec,
      `WITH scope AS (
         SELECT f.id AS from_id
         FROM jsonb_to_recordset(($2::jsonb)->'pages') AS p(slug text, source_id text)
         JOIN pages f ON f.slug = p.slug AND f.source_id = p.source_id
       ),
       keep AS (
         SELECT f.id AS from_id, t.id AS to_id
         FROM jsonb_to_recordset(($2::jsonb)->'keep') AS k(
           from_slug text, from_source_id text, to_slug text, to_source_id text
         )
         JOIN pages f ON f.slug = k.from_slug AND f.source_id = k.from_source_id
         JOIN pages t ON t.slug = k.to_slug AND t.source_id = k.to_source_id
       )
       DELETE FROM links l
       USING scope s
       WHERE l.from_page_id = s.from_id
         AND l.link_source = $1
         AND NOT (
           COALESCE(l.link_kind, '') = 'typed_ner'
           AND EXISTS (
             SELECT 1 FROM keep k
             WHERE k.from_id = l.from_page_id AND k.to_id = l.to_page_id
           )
         )
       RETURNING l.from_page_id, l.to_page_id, l.link_type`,
      [opts.linkSource],
      [payload],
    ) as RelationshipKey[];
    await refreshRelationships(exec, rows);
    return rows.length;
  }


export async function removeLink(
  exec: SqlExecutor,
    from: string,
    to: string,
    linkType?: string,
    linkSource?: string,
    opts?: { fromSourceId?: string; toSourceId?: string },
  ): Promise<number> {
    const fromSrc = opts?.fromSourceId ?? 'default';
    const toSrc = opts?.toSourceId ?? 'default';
    // Build up filters dynamically. linkType + linkSource are independent
    // optional constraints; all four combinations are valid. Each branch's
    // page-id subquery is source-qualified so multi-source brains don't
    // delete the wrong (from, to) pair.
    // #4527: RETURNING 1 so the caller learns how many edges actually died —
    // a zero-match delete must be distinguishable from a real removal.
    if (linkType !== undefined && linkSource !== undefined) {
      const rows = (await exec.run(sqlFragment`
        DELETE FROM links
        WHERE from_page_id = (SELECT id FROM pages WHERE slug = ${from} AND source_id = ${fromSrc})
          AND to_page_id = (SELECT id FROM pages WHERE slug = ${to} AND source_id = ${toSrc})
          AND link_type = ${linkType}
          AND link_source IS NOT DISTINCT FROM ${linkSource}
        RETURNING from_page_id, to_page_id, link_type
      `)).rows as unknown as RelationshipKey[];
      await refreshRelationships(exec, rows);
      return rows.length;
    } else if (linkType !== undefined) {
      const rows = (await exec.run(sqlFragment`
        DELETE FROM links
        WHERE from_page_id = (SELECT id FROM pages WHERE slug = ${from} AND source_id = ${fromSrc})
          AND to_page_id = (SELECT id FROM pages WHERE slug = ${to} AND source_id = ${toSrc})
          AND link_type = ${linkType}
        RETURNING from_page_id, to_page_id, link_type
      `)).rows as unknown as RelationshipKey[];
      await refreshRelationships(exec, rows);
      return rows.length;
    } else if (linkSource !== undefined) {
      const rows = (await exec.run(sqlFragment`
        DELETE FROM links
        WHERE from_page_id = (SELECT id FROM pages WHERE slug = ${from} AND source_id = ${fromSrc})
          AND to_page_id = (SELECT id FROM pages WHERE slug = ${to} AND source_id = ${toSrc})
          AND link_source IS NOT DISTINCT FROM ${linkSource}
        RETURNING from_page_id, to_page_id, link_type
      `)).rows as unknown as RelationshipKey[];
      await refreshRelationships(exec, rows);
      return rows.length;
    } else {
      const rows = (await exec.run(sqlFragment`
        DELETE FROM links
        WHERE from_page_id = (SELECT id FROM pages WHERE slug = ${from} AND source_id = ${fromSrc})
          AND to_page_id = (SELECT id FROM pages WHERE slug = ${to} AND source_id = ${toSrc})
        RETURNING from_page_id, to_page_id, link_type
      `)).rows as unknown as RelationshipKey[];
      await refreshRelationships(exec, rows);
      return rows.length;
    }
  }


export async function getLinks(exec: ScopedRead, slug: string, opts?: LinkReadScope): Promise<Link[]> {
    const privacy = opts?.excludePrivate ? `AND ${privatePagesFilterForCaller('f')} AND ${privatePagesFilterForCaller('t')} AND ${privateLinkOriginFilterForCaller('l')}` : '';
    const tSel = opts?.temporal ? TEMPORAL_LINK_SELECT_SQL : '';
    const tJoin = opts?.temporal ? temporalLinkJoinSql('l', opts.excludePrivate) : '';
    const finish = (rows: unknown[]) => finishTemporal(rows, opts);
    const page = linkPageSql(opts, 'from');
      // #2200: federated grant scopes ALL THREE page endpoints — from, to, AND
      // the origin (the page that authored the edge, surfaced as origin_slug):
      // an edge whose origin is outside the grant or deleted is dropped with
      // its context and origin_field. Remote MCP clients always land here.
      if (opts?.sourceIds && opts.sourceIds.length > 0) {
        const ids = opts.sourceIds;
        const rows = await runLinkRead(exec, opts, sqlFragment`
          SELECT f.slug as from_slug, f.source_id as from_source_id,
                 t.slug as to_slug, t.source_id as to_source_id,
                 l.link_type, ${page.context}, l.link_source,
                 o.slug as origin_slug, o.source_id as origin_source_id,
                 l.origin_field ${trustedSql(tSel)}${page.id}
          FROM links l
          JOIN pages f ON f.id = l.from_page_id
          JOIN pages t ON t.id = l.to_page_id
          LEFT JOIN pages o ON o.id = l.origin_page_id ${trustedSql(tJoin)}
          WHERE f.slug = ${slug} AND f.source_id = ANY(${ids}::text[]) AND t.source_id = ANY(${ids}::text[])
            AND f.deleted_at IS NULL AND t.deleted_at IS NULL ${liveOriginFilter('l', { sourceIds: ids })} ${trustedSql(privacy)}${page.where}
          ORDER BY l.id${page.limit}
        `);
        return finish(rows);
      }
      // v0.31.8 (D16) + #2200: the federated arm above is the first branch; the
      // two below preserve pre-v0.31.8 semantics. Without opts.sourceId, no
      // source filter (cross-source view for internal callers). With
      // opts.sourceId, scope the from-page lookup.
      // #3754: all three arms filter soft-deleted endpoints (f/t deleted_at IS
      // NULL) and soft-deleted origins so links to/from or authored by
      // soft-deleted pages stop voting in the graph, matching
      // orphans/get/list/search visibility.
      if (opts?.sourceId) {
        const rows = await runLinkRead(exec, opts, sqlFragment`
          SELECT f.slug as from_slug, f.source_id as from_source_id,
                 t.slug as to_slug, t.source_id as to_source_id,
                 l.link_type, ${page.context}, l.link_source,
                 o.slug as origin_slug, o.source_id as origin_source_id,
                 l.origin_field ${trustedSql(tSel)}${page.id}
          FROM links l
          JOIN pages f ON f.id = l.from_page_id
          JOIN pages t ON t.id = l.to_page_id
          LEFT JOIN pages o ON o.id = l.origin_page_id ${trustedSql(tJoin)}
          WHERE f.slug = ${slug} AND f.source_id = ${opts.sourceId}
            AND f.deleted_at IS NULL AND t.deleted_at IS NULL ${liveOriginFilter('l')} ${trustedSql(privacy)}${page.where}
          ORDER BY l.id${page.limit}
        `);
        return finish(rows);
      }
      const rows = await runLinkRead(exec, opts, sqlFragment`
        SELECT f.slug as from_slug, f.source_id as from_source_id,
               t.slug as to_slug, t.source_id as to_source_id,
               l.link_type, ${page.context}, l.link_source,
               o.slug as origin_slug, o.source_id as origin_source_id,
               l.origin_field ${trustedSql(tSel)}${page.id}
        FROM links l
        JOIN pages f ON f.id = l.from_page_id
        JOIN pages t ON t.id = l.to_page_id
        LEFT JOIN pages o ON o.id = l.origin_page_id ${trustedSql(tJoin)}
        WHERE f.slug = ${slug}
          AND f.deleted_at IS NULL AND t.deleted_at IS NULL ${liveOriginFilter('l')} ${trustedSql(privacy)}${page.where}
        ORDER BY l.id${page.limit}
      `);
      return finish(rows);
  }


export async function getBacklinks(exec: ScopedRead, slug: string, opts?: LinkReadScope): Promise<Link[]> {
    const privacy = opts?.excludePrivate ? `AND ${privatePagesFilterForCaller('f')} AND ${privatePagesFilterForCaller('t')} AND ${privateLinkOriginFilterForCaller('l')}` : '';
    const tSel = opts?.temporal ? TEMPORAL_LINK_SELECT_SQL : '';
    const tJoin = opts?.temporal ? temporalLinkJoinSql('l', opts.excludePrivate) : '';
    const finish = (rows: unknown[]) => finishTemporal(rows, opts);
    const page = linkPageSql(opts, 'to');
      // #2200: federated grant scopes all three endpoints (mirrors getLinks) —
      // the referrer (from), the queried page (to), AND the origin — so neither
      // a foreign referrer nor a foreign origin slug is disclosed to the caller.
      if (opts?.sourceIds && opts.sourceIds.length > 0) {
        const ids = opts.sourceIds;
        const rows = await runLinkRead(exec, opts, sqlFragment`
          SELECT f.slug as from_slug, f.source_id as from_source_id,
                 t.slug as to_slug, t.source_id as to_source_id,
                 l.link_type, ${page.context}, l.link_source,
                 o.slug as origin_slug, o.source_id as origin_source_id,
                 l.origin_field ${trustedSql(tSel)}${page.id}
          FROM links l
          JOIN pages f ON f.id = l.from_page_id
          JOIN pages t ON t.id = l.to_page_id
          LEFT JOIN pages o ON o.id = l.origin_page_id ${trustedSql(tJoin)}
          WHERE t.slug = ${slug} AND t.source_id = ANY(${ids}::text[]) AND f.source_id = ANY(${ids}::text[])
            AND f.deleted_at IS NULL AND t.deleted_at IS NULL ${liveOriginFilter('l', { sourceIds: ids })} ${trustedSql(privacy)}${page.where}
          ORDER BY l.id${page.limit}
        `);
        return finish(rows);
      }
      // v0.31.8 (D16) + #2200: federated arm above is first; two below mirror getLinks
      // (incl. the #3754 soft-delete endpoint filter on all three arms).
      if (opts?.sourceId) {
        const rows = await runLinkRead(exec, opts, sqlFragment`
          SELECT f.slug as from_slug, f.source_id as from_source_id,
                 t.slug as to_slug, t.source_id as to_source_id,
                 l.link_type, ${page.context}, l.link_source,
                 o.slug as origin_slug, o.source_id as origin_source_id,
                 l.origin_field ${trustedSql(tSel)}${page.id}
          FROM links l
          JOIN pages f ON f.id = l.from_page_id
          JOIN pages t ON t.id = l.to_page_id
          LEFT JOIN pages o ON o.id = l.origin_page_id ${trustedSql(tJoin)}
          WHERE t.slug = ${slug} AND t.source_id = ${opts.sourceId}
            AND f.deleted_at IS NULL AND t.deleted_at IS NULL ${liveOriginFilter('l')} ${trustedSql(privacy)}${page.where}
          ORDER BY l.id${page.limit}
        `);
        return finish(rows);
      }
      const rows = await runLinkRead(exec, opts, sqlFragment`
        SELECT f.slug as from_slug, f.source_id as from_source_id,
               t.slug as to_slug, t.source_id as to_source_id,
               l.link_type, ${page.context}, l.link_source,
               o.slug as origin_slug, o.source_id as origin_source_id,
               l.origin_field ${trustedSql(tSel)}${page.id}
        FROM links l
        JOIN pages f ON f.id = l.from_page_id
        JOIN pages t ON t.id = l.to_page_id
        LEFT JOIN pages o ON o.id = l.origin_page_id ${trustedSql(tJoin)}
        WHERE t.slug = ${slug}
          AND f.deleted_at IS NULL AND t.deleted_at IS NULL ${liveOriginFilter('l')} ${trustedSql(privacy)}${page.where}
        ORDER BY l.id${page.limit}
      `);
      return finish(rows);
  }


export async function listLinkSources(
  exec: ScopedRead,
    opts?: { sourceId?: string; sourceIds?: string[] },
  ): Promise<{ link_source: string | null; count: number }[]> {
      // v114 (#1941): distinct provenances + counts for `gbrain link-sources`.
      // A scoped read counts an edge only when its from page, its to page and
      // its origin page (when it has one, and it is live) are all in scope.
      // Federated {sourceIds} takes precedence over scalar {sourceId}; neither = unscoped.
      const sourceCondition =
        opts?.sourceIds && opts.sourceIds.length > 0
          ? sqlFragment`WHERE f.source_id = ANY(${opts.sourceIds}::text[]) AND t.source_id = ANY(${opts.sourceIds}::text[]) ${liveOriginFilter('l', opts)}`
          : opts?.sourceId
            ? sqlFragment`WHERE f.source_id = ${opts.sourceId} AND t.source_id = ${opts.sourceId} ${liveOriginFilter('l', opts)}`
            : sqlFragment``;
      const rows = (await exec.run(sqlFragment`
        SELECT l.link_source, COUNT(*)::int AS count
        FROM links l
        JOIN pages f ON f.id = l.from_page_id
        JOIN pages t ON t.id = l.to_page_id
        ${sourceCondition}
        GROUP BY l.link_source
        ORDER BY count DESC, l.link_source ASC NULLS LAST
      `)).rows;
      return rows as unknown as { link_source: string | null; count: number }[];
  }


export async function findOrphanPages(exec: LegacyUnscopedRead, opts?: {
    sourceId?: string;
    sourceIds?: string[];
    excludePrivate?: boolean;
    mode?: 'inbound' | 'islanded';
    scopeInbound?: boolean;
  }): Promise<Array<{ slug: string; title: string; domain: string | null; type?: string | null; quarantined?: boolean; source_id?: string }>> {
    // Soft-delete filter on BOTH sides:
    //   - candidate: p.deleted_at IS NULL — soft-deleted pages aren't orphan candidates
    //   - link source: src.deleted_at IS NULL — links FROM soft-deleted pages don't count as inbound
    // Without the link-source filter, a live page can hide from orphan results purely
    // because a soft-deleted page links to it. v0.26.5 invariant; codex C11.
    //
    // v0.41.29.0: scope ONLY the candidate side (`p.source_id`) when opts.sourceId
    // is set. The inbound-link NOT EXISTS deliberately counts links from ANY source
    // for the local operator: a page in source X linked FROM source Y is reachable,
    // so NOT an orphan of X. Do NOT add `src.source_id = p.source_id` here — that
    // would be the stricter intra-source-only definition we deliberately reject.
    // `scopeInbound` (remote callers) counts only links whose other endpoint and live origin are inside the same scope.
    const sourceFilter =
      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``;
    // #4524: default mode 'islanded' — identical predicate to getHealth's
    // orphan_pages (no live inbound AND no live outbound; outbound counts
    // only when its TARGET page is live, per gbrain#4153 endpoint liveness).
    // mode 'inbound' preserves the legacy no-inbound-only view.
    const outboundFilter =
      (opts?.mode ?? 'islanded') === 'islanded'
        ? sqlFragment`AND NOT EXISTS (
            SELECT 1
            FROM links l
            JOIN pages tgt ON tgt.id = l.to_page_id
            WHERE l.from_page_id = p.id
              AND tgt.deleted_at IS NULL ${opts?.scopeInbound ? sourceScope('tgt', opts) : sqlFragment``} ${opts?.scopeInbound ? liveOriginFilter('l', opts) : sqlFragment``} ${opts?.excludePrivate ? trustedSql(`AND ${privatePagesFilterForCaller('tgt')} AND ${privateLinkOriginFilterForCaller('l')}`) : sqlFragment``}
          )`
        : sqlFragment``;
    const rows = (await exec.run(sqlFragment`
      SELECT
        p.slug,
        COALESCE(p.title, p.slug) AS title,
        p.frontmatter->>'domain' AS domain,
        p.type,
        (NOT ${trustedSql(QUARANTINE_FILTER_FRAGMENT)}) AS quarantined,
        p.source_id
      FROM pages p
      WHERE p.deleted_at IS NULL ${opts?.excludePrivate ? trustedSql(`AND ${privatePagesFilterForCaller('p')}`) : sqlFragment``}
        ${sourceFilter}
        AND NOT EXISTS (
          SELECT 1
          FROM links l
          JOIN pages src ON src.id = l.from_page_id
          WHERE l.to_page_id = p.id
            AND src.deleted_at IS NULL ${opts?.scopeInbound ? sourceScope('src', opts) : sqlFragment``} ${opts?.scopeInbound ? liveOriginFilter('l', opts) : sqlFragment``} ${opts?.excludePrivate ? trustedSql(`AND ${privatePagesFilterForCaller('src')} AND ${privateLinkOriginFilterForCaller('l')}`) : sqlFragment``}
        )
        ${outboundFilter}
      ORDER BY p.slug
    `)).rows;
    return rows as unknown as Array<{ slug: string; title: string; domain: string | null; type?: string | null; quarantined?: boolean; source_id?: string }>;
  }


/**
 * Breadth-first walk shared by `traverseGraph` and `traversePathsDetailed`.
 *
 * Each recursive row is one whole BFS layer: its depth, its frontier and every
 * page visited so far. A layer expands the frontier through one indexed
 * `links` join with the caller's per-step filters (soft delete, privacy,
 * source scope, link type, temporal policy), drops already-visited pages with a
 * hash anti-join, and keeps at most `layerCap` new pages and never more than
 * TRAVERSE_WALK_ROW_CAP + 1 visited pages in total, in (slug, id) order. Every
 * page therefore appears once, at its shortest distance from the seed, and the
 * work is bounded by the pages and edges touched instead of by the number of
 * simple paths (the old per-path CTE took 22 s on a 12-page clique at depth 5).
 *
 * Emits two CTEs: `bfs` and `walk(id, depth, pos)`, where `pos` is the page's
 * position in visit order. A walk with more than TRAVERSE_WALK_ROW_CAP rows was
 * truncated; callers keep `pos <= TRAVERSE_WALK_ROW_CAP`.
 */
function bfsWalkCtes(w: {
  seed: SqlFragment;
  maxLayer: number;
  direction: 'in' | 'out' | 'both';
  step: SqlFragment;
  layerCap?: number;
}) {
  const out = sqlFragment`SELECT p2.id, p2.slug FROM unnest(bfs.frontier) f(id)
          JOIN links l ON l.from_page_id = f.id
          JOIN pages p2 ON p2.id = l.to_page_id
          WHERE ${w.step}`;
  const into = sqlFragment`SELECT p2.id, p2.slug FROM unnest(bfs.frontier) f(id)
          JOIN links l ON l.to_page_id = f.id
          JOIN pages p2 ON p2.id = l.from_page_id
          WHERE ${w.step}`;
  const neighbours = w.direction === 'out' ? out : w.direction === 'in' ? into : sqlFragment`${out} UNION ALL ${into}`;
  const budget = sqlFragment`greatest(0, ${TRAVERSE_WALK_ROW_CAP + 1}::int - cardinality(bfs.visited))`;
  const limit = w.layerCap !== undefined && w.layerCap > 0 ? sqlFragment`least(${w.layerCap}::int, ${budget})` : budget;
  return sqlFragment`bfs(depth, frontier, visited) AS (
        SELECT 0, array_agg(p.id ORDER BY p.slug, p.id), array_agg(p.id ORDER BY p.slug, p.id)
        FROM pages p WHERE ${w.seed}
        HAVING count(*) > 0
        UNION ALL
        SELECT bfs.depth + 1, n.next, bfs.visited || n.next
        FROM bfs CROSS JOIN LATERAL (
          SELECT array_agg(c.id ORDER BY c.slug, c.id) AS next
          FROM (
            SELECT DISTINCT nb.id, nb.slug FROM (${neighbours}) nb
            WHERE NOT EXISTS (SELECT 1 FROM unnest(bfs.visited) v(id) WHERE v.id = nb.id)
            ORDER BY nb.slug, nb.id
            LIMIT ${limit}
          ) c
        ) n
        WHERE bfs.depth < ${w.maxLayer}::int AND n.next IS NOT NULL
      ),
      walk AS (
        SELECT u.id, b.depth, row_number() OVER (ORDER BY b.depth, u.ord) AS pos
        FROM bfs b CROSS JOIN LATERAL unnest(b.frontier) WITH ORDINALITY u(id, ord)
      )`;
}

const SCOPED_SOURCE_COLUMNS = { src: 'src.source_id', tgt: 'tgt.source_id', p: 'p.source_id', p2: 'p2.source_id', p3: 'p3.source_id', pf: 'pf.source_id', pt: 'pt.source_id' } as const;

function sourceScope(alias: keyof typeof SCOPED_SOURCE_COLUMNS, opts?: { sourceId?: string; sourceIds?: string[] }) {
  const column = trustedSql(SCOPED_SOURCE_COLUMNS[alias]);
  if (opts?.sourceIds && opts.sourceIds.length > 0) return sqlFragment`AND ${column} = ANY(${opts.sourceIds}::text[])`;
  if (opts?.sourceId) return sqlFragment`AND ${column} = ${opts.sourceId}`;
  return sqlFragment``;
}

export async function traverseGraph(
  exec: LegacyUnscopedRead,
    slug: string,
    depth: number = 5,
    opts?: TraverseGraphOpts,
  ): Promise<GraphNode[]> {
    const privacy = (page: string, link?: string) => opts?.excludePrivate
      ? trustedSql(`AND ${privatePagesFilterForCaller(page)}${link ? ` AND ${privateLinkOriginFilterForCaller(link)}` : ''}`) : sqlFragment``;
    // v0.34.1 (#861 — P0 leak seal): visited pages are scoped to the caller's
    // source(s) at the seed and at every step, and the per-node `links`
    // aggregation only includes edges to in-scope pages.
    // T8 (v0.36+): `frontierCap` bounds the new pages each BFS layer adds,
    // chosen in (slug, id) order.
    const rows = (await exec.run(sqlFragment`
      WITH RECURSIVE ${bfsWalkCtes({
        seed: sqlFragment`p.slug = ${slug} AND p.deleted_at IS NULL ${privacy('p')} ${sourceScope('p', opts)}`,
        maxLayer: depth,
        direction: 'out',
        step: sqlFragment`p2.deleted_at IS NULL ${privacy('p2', 'l')} ${sourceScope('p2', opts)} ${liveOriginFilter('l', opts)} ${temporalWalkFilter('l', opts)}`,
        layerCap: opts?.frontierCap,
      })}
      SELECT g.slug, g.source_id, g.title, g.type, g.depth,
        coalesce(
          -- jsonb_agg(DISTINCT ...) collapses duplicate (to_slug, link_type)
          -- edges that originate from different provenance (markdown body
          -- vs frontmatter vs auto-extracted). The underlying links table
          -- preserves every row with its origin_page_id / link_source —
          -- the dedup is presentation-only for the legacy traverseGraph
          -- aggregation. See plan Bug 6/10.
          (SELECT jsonb_agg(DISTINCT jsonb_build_object('to_slug', p3.slug, 'link_type', l2.link_type))
           FROM links l2
           JOIN pages p3 ON p3.id = l2.to_page_id
           WHERE l2.from_page_id = g.id AND p3.deleted_at IS NULL ${privacy('p3', 'l2')} ${sourceScope('p3', opts)} ${liveOriginFilter('l2', opts)} ${temporalWalkFilter('l2', opts)}),
          '[]'::jsonb
        ) as links
      FROM (SELECT p.id, p.slug, p.source_id, p.title, p.type, w.depth
            FROM walk w JOIN pages p ON p.id = w.id
            WHERE w.pos <= ${TRAVERSE_WALK_ROW_CAP}::int) g
      ORDER BY g.depth, g.slug, g.source_id
    `)).rows;

    return rows.map((r: Record<string, unknown>) => ({
      slug: r.slug as string,
      source_id: r.source_id as string,
      title: r.title as string,
      type: r.type as string,
      depth: Number(r.depth),
      links: (typeof r.links === 'string' ? JSON.parse(r.links) : r.links) as { to_slug: string; link_type: string }[],
    }));
  }


export async function traversePathsDetailed(
  exec: LegacyUnscopedRead,
    slug: string,
    opts?: LinkReadScope & { depth?: number; linkType?: string; direction?: 'in' | 'out' | 'both' },
  ): Promise<{ paths: GraphPath[]; truncated: boolean }> {
    const privacy = (page: string, link?: string) => opts?.excludePrivate
      ? trustedSql(`AND ${privatePagesFilterForCaller(page)}${link ? ` AND ${privateLinkOriginFilterForCaller(link)}` : ''}`) : sqlFragment``;
    const depth = opts?.depth ?? 5;
    const direction = opts?.direction ?? 'out';
    const linkType = opts?.linkType ?? null;
    const linkTypeFilter = linkType !== null ? sqlFragment`AND l.link_type = ${linkType}` : sqlFragment``;
    // v0.34.1 (#861 — P0 leak seal) and #3754: source scope, soft delete,
    // privacy, link type and temporal policy apply at the seed, at every BFS
    // step and on both endpoints of every returned edge, so an out-of-scope,
    // private or deleted page neither anchors, relays nor terminates a path;
    // an edge whose origin page is deleted or out of scope is not walked.
    // The walk visits pages at depth < `depth`; each qualifying edge is
    // returned once, at one past the depth of its shallower visited endpoint
    // on the walked side ('both' counts either endpoint).
    const walk = bfsWalkCtes({
      seed: sqlFragment`p.slug = ${slug} AND p.deleted_at IS NULL ${privacy('p')} ${sourceScope('p', opts)}`,
      maxLayer: depth - 1,
      direction,
      step: sqlFragment`p2.deleted_at IS NULL ${privacy('p2', 'l')} ${linkTypeFilter} ${temporalWalkFilter('l', opts)} ${sourceScope('p2', opts)} ${liveOriginFilter('l', opts)}`,
    });
    const visited = sqlFragment`nodes AS (SELECT p.id, p.slug, p.source_id, w.depth FROM walk w JOIN pages p ON p.id = w.id WHERE w.pos <= ${TRAVERSE_WALK_ROW_CAP}::int)`;
    const walkTruncated = sqlFragment`(SELECT count(*) FROM walk) > ${TRAVERSE_WALK_ROW_CAP}::int AS walk_truncated`;
    const edges = direction === 'out'
      ? sqlFragment`w.id as from_id, w.slug as from_slug, w.source_id as from_source_id,
               p2.id as to_id, p2.slug as to_slug, p2.source_id as to_source_id,
               l.link_type, l.context, w.depth + 1 as depth
        FROM nodes w
        JOIN links l ON l.from_page_id = w.id
        JOIN pages p2 ON p2.id = l.to_page_id
        WHERE w.depth < ${depth}
          AND p2.deleted_at IS NULL ${privacy('p2', 'l')}
          ${linkTypeFilter} ${temporalWalkFilter('l', opts)}
          ${sourceScope('p2', opts)} ${liveOriginFilter('l', opts)}`
      : direction === 'in'
        ? sqlFragment`p2.id as from_id, p2.slug as from_slug, p2.source_id as from_source_id,
               w.id as to_id, w.slug as to_slug, w.source_id as to_source_id,
               l.link_type, l.context, w.depth + 1 as depth
        FROM nodes w
        JOIN links l ON l.to_page_id = w.id
        JOIN pages p2 ON p2.id = l.from_page_id
        WHERE w.depth < ${depth}
          AND p2.deleted_at IS NULL ${privacy('p2', 'l')}
          ${linkTypeFilter} ${temporalWalkFilter('l', opts)}
          ${sourceScope('p2', opts)} ${liveOriginFilter('l', opts)}`
        : sqlFragment`pf.id as from_id, pf.slug as from_slug, pf.source_id as from_source_id,
               pt.id as to_id, pt.slug as to_slug, pt.source_id as to_source_id,
               l.link_type, l.context, e.depth + 1 as depth
        FROM (
          SELECT l.id AS link_id, wf.depth FROM nodes wf
          JOIN links l ON l.from_page_id = wf.id
          LEFT JOIN nodes wt ON wt.id = l.to_page_id
          WHERE wt.id IS NULL OR wf.depth <= wt.depth
          UNION ALL
          SELECT l.id, wt.depth FROM nodes wt
          JOIN links l ON l.to_page_id = wt.id
          LEFT JOIN nodes wf ON wf.id = l.from_page_id
          WHERE wf.id IS NULL OR wt.depth < wf.depth
        ) e
        JOIN links l ON l.id = e.link_id
        JOIN pages pf ON pf.id = l.from_page_id
        JOIN pages pt ON pt.id = l.to_page_id
        WHERE e.depth < ${depth}
          AND pf.deleted_at IS NULL ${privacy('pf', 'l')}
          AND pt.deleted_at IS NULL ${privacy('pt')}
          ${linkTypeFilter} ${temporalWalkFilter('l', opts)}
          ${sourceScope('pf', opts)}
          ${sourceScope('pt', opts)} ${liveOriginFilter('l', opts)}`;
    const rows = (await exec.run(sqlFragment`
      WITH RECURSIVE ${walk}, ${visited}
      SELECT ${walkTruncated}, ${edges}
      ORDER BY depth, from_slug, to_slug, from_source_id, to_source_id
      LIMIT ${TRAVERSE_PATH_ROW_CAP + 1}
    `)).rows;

    // Row cap: the LIMIT above fetched CAP + 1 rows; the probe row only tells
    // us the walk overflowed and is dropped with everything past the cap.
    const truncated = rows.length > TRAVERSE_PATH_ROW_CAP || (rows as Array<{ walk_truncated?: boolean }>).some((r) => r.walk_truncated === true);
    const bounded = (truncated ? rows.slice(0, TRAVERSE_PATH_ROW_CAP) : rows) as Record<string, unknown>[];
    // Duplicate link rows (same edge, different provenance) collapse here.
    // Keyed on page ids, so the same slug in two sources stays two edges.
    const seen = new Set<string>();
    const result: GraphPath[] = [];
    for (const r of bounded) {
      const key = `${r.from_id}|${r.to_id}|${r.link_type}|${r.depth}`;
      if (seen.has(key)) continue;
      seen.add(key);
      result.push({
        from_slug: r.from_slug as string,
        from_source_id: r.from_source_id as string,
        to_slug: r.to_slug as string,
        to_source_id: r.to_source_id as string,
        link_type: r.link_type as string,
        context: (r.context as string) || '',
        depth: Number(r.depth),
      });
    }
    return { paths: result, truncated };
  }