Merge remote-tracking branch 'origin/master' into garrytan/pr-1538-supersede

# Conflicts: # CHANGELOG.md # VERSION # package.json
2026-08-14 08:53:22 +00:00 · 2026-05-27 08:43:47 -07:00 · 2026-05-27 08:30:58 -07:00 · 2026-05-27 08:20:33 -07:00 · 2026-05-27 07:53:19 -07:00 · 2026-05-27 07:40:26 -07:00
24 changed files with 1754 additions and 143 deletions
@@ -2,6 +2,143 @@

 All notable changes to GBrain will be documented in this file.

+
+## [0.41.25.0] - 2026-05-27
+
+**Big-delete syncs no longer choke your brain — and your search results
+were silently going stale in a different way that's now fixed.**
+
+If you've ever made a commit that deleted a lot of files (a folder
+reorganization, an `atom` backfill rewrite, a big import cleanup), you
+might have noticed that `gbrain sync` would just... sit there. For
+hours. While it sat, the sync cron timed out every run, every other
+source on your brain stopped syncing, and `gbrain doctor` health score
+slid toward zero. That's fixed. A commit deleting 73,000 files used to
+take about five hours to absorb; it now takes about two minutes.
+
+While digging into the delete path we found a second, unrelated bug.
+Your query cache was silently serving stale `gbrain search` results
+whenever you updated an *older* page (one that wasn't the newest in
+your brain) and whenever you deleted *anything*. That's also fixed.
+You'll see one tiny cache-miss spike on first `gbrain search` after
+upgrade as the cache gets re-stamped under the new contract, then it's
+faster than before because the bookmark is a one-row lookup instead of
+a brain-wide MAX scan.
+
+**To take advantage of v0.41.22.0**
+
+`gbrain upgrade` should do this automatically. If it didn't, or if
+`gbrain doctor` warns about a partial migration:
+
+1. Run the orchestrator manually:
+   ```bash
+   gbrain apply-migrations --yes
+   ```
+2. Verify the new clock table exists:
+   ```bash
+   gbrain doctor
+   # Look for any "page_generation_clock" warning in the output;
+   # there shouldn't be one.
+   ```
+3. Manual smoke test for the perf win (optional, only if you want to
+   see it):
+   ```bash
+   # In a scratch brain, stage a commit that deletes a lot of files,
+   # then run `gbrain sync --source <id>` and watch the wallclock.
+   # Before: minutes. After: seconds.
+   ```
+4. If any step fails or numbers look wrong, file an issue at
+   https://github.com/garrytan/gbrain/issues with `gbrain doctor`
+   output and the contents of `~/.gbrain/upgrade-errors.jsonl` if it
+   exists.
+
+### Itemized changes
+
+- **Sync delete loop batched.** `gbrain sync` (and the autopilot cycle's
+  sync phase) now batches deletes through a single SQL round-trip per
+  500 files instead of one round-trip per file. The 73K-delete commit
+  drops from ~146,000 DB round-trips (~5 hours) to ~292 (~2 minutes).
+  Per-batch try-catch + per-slug decompose mirrors the existing
+  failedFiles pattern in the import loop, so a transient connection
+  blip on batch 73 doesn't lose 500 deletes — it self-heals to
+  one-at-a-time for that batch only. Engine-uniform: PGLite and real
+  Postgres both get the win.
+- **Sync rename loop pre-resolved.** Same batched slug-resolve treatment
+  for renames; the per-file `importFile` (which dominates rename cost
+  anyway) stays per-file.
+- **`engine.deletePages(slugs, {sourceId})`** — new required method on
+  `BrainEngine`. Returns the slugs actually deleted (so callers stop
+  wasting downstream work on phantom paths). `sourceId` is required at
+  the type level — closes the multi-source-bug-class structurally on
+  the new surface.
+- **`engine.resolveSlugsByPaths(paths, {sourceId})`** — new required
+  method on `BrainEngine`. Batch path → slug lookup. The single-call
+  `resolveSlugByPathOrSourcePath` helper now delegates to this when
+  `sourceId` is set; one owner of the SQL and fallback semantics.
+- **`src/core/engine-constants.ts`** — new module owning
+  `DELETE_BATCH_SIZE = 500`. Both engines import from it; no
+  engine-from-engine coupling.
+- **Global page-generation clock.** New `page_generation_clock` table
+  with a statement-level `AFTER INSERT OR UPDATE OR DELETE` trigger.
+  `query-cache-gate.ts` Layer 1 now reads the clock directly. Pre-fix
+  the bookmark read `MAX(generation) FROM pages` and was structurally
+  broken:
+  - Updating a non-max page kept the old `OLD.generation + 1` trigger
+    body, which advanced the per-page counter but didn't move
+    `MAX(generation)`. Cache silently served stale.
+  - Hard-deleting any page didn't fire the trigger at all. Cache
+    silently served stale.
+  - Empty-result cache rows had `{}` per-page snapshots that were
+    "vacuously valid" via Layer 2 — they survived any subsequent
+    matching INSERT. Cache silently served stale.
+
+  The new clock bumps exactly once per write statement regardless of
+  row cardinality (a 500-row batch DELETE is one counter bump, not
+  500). Per-row `pages.generation` stays for Layer 2's per-page
+  snapshot semantics; only Layer 1's read source changed. Empty-result
+  cache rows now trust Layer 1 exclusively.
+- **Migration v106** (`page_generation_clock_and_statement_trigger`)
+  creates the clock table, seeds it with `COALESCE(MAX(pages.generation), 0)`
+  so existing cache rows aren't all instantly invalidated, and wires
+  the new trigger.
+- **CHANGELOG, version, and tests.** Tests in
+  `test/sync-delete-batch.test.ts`, `test/sync-delete-batch.slow.test.ts`
+  (PGLite 10K-page perf gate), `test/sync-rename-batch.test.ts`,
+  `test/page-generation-counter.test.ts`, plus an engine-parity
+  extension in `test/e2e/engine-parity.test.ts`. The existing
+  `test/query-cache-gate.test.ts` and `test/e2e/cache-gate-pglite.test.ts`
+  flip their pre-v0.41.22.0 "vacuously valid legacy row" assertions to
+  match the new contract (legacy rows now invalidate once on first
+  post-upgrade lookup, then the cache fills back correctly).
+- **Credit:** the SQL batching idea (`DELETE FROM pages WHERE slug =
+  ANY($1::text[])` chunked at 500) and the headline 73K-delete number
+  come from PR #1538 by `@garrytan-agents`. That PR shipped Postgres-
+  only with no tests; this release supersedes it with PGLite parity,
+  proper engine-API factoring, test coverage, and the cache-bookmark
+  correctness fix that landed alongside.
+
+### For contributors
+
+- Engine implementers (gbrain has no external implementers today) must
+  add `deletePages` and `resolveSlugsByPaths` to any custom
+  `BrainEngine` — both are required methods on the interface.
+- New CI surface: nothing additional; the existing `bun run verify`
+  gate covers the new test files automatically.
+- v0.42+ TODO: tighten `deletePage` (single-row) to require `sourceId`
+  symmetric with the new `deletePages`. Out of scope for this PR
+  because it requires a full caller audit.
+- v0.42+ TODO: clean up the existing `bump_page_generation_fn`
+  row-level trigger's UPDATE branch (`NEW.generation = OLD.generation
+  + 1` is structurally wrong for MAX-style consumers, harmless for
+  Layer 2 per-page snapshot but worth cleaning up).
+- v0.42+ TODO: `op_checkpoints` integration on the sync-delete loop
+  for resume-after-kill semantics. Per-delete cost dropped from ~250ms
+  to ~0.4ms so a 73K-delete completes in seconds; resume matters much
+  less now.
+- v0.42+ TODO: heavy regression test for two concurrent batch DELETEs
+  against disjoint sources (the statement-level clock-row UPDATE
+  contention shape). Filed under `tests/heavy/` per CLAUDE.md.
+
 ## [0.41.24.0] - 2026-05-27

 **Your reformatted meeting transcripts now actually parse.**
@@ -1 +1 @@
-0.41.24.0
+0.41.25.0
@@ -140,5 +140,5 @@
    "bun": ">=1.3.10"
  },
  "license": "MIT",
-  "version": "0.41.24.0"
+  "version": "0.41.25.0"
 }
@@ -181,28 +181,88 @@ bun_summary_count() {
  ' "$file"
 }

+# shard_total_files: parse the "[unit-shard N/M] running X files" line that
+# run-unit-shard.sh echoes before invoking bun test. Returns the file count
+# the shard was given, or 0 if the line isn't there yet (shard still
+# bootstrapping). Uses sed-then-grep so it's portable to macOS awk (BSD awk
+# doesn't support `match($0, /re/, arr)` with the array sink — that's gawk-only).
+shard_total_files() {
+  local file="$1"
+  [ -f "$file" ] || { echo 0; return; }
+  local n
+  n=$(sed -n 's/^\[unit-shard [0-9][0-9]*\/[0-9][0-9]*\] running \([0-9][0-9]*\) files.*/\1/p' "$file" 2>/dev/null | head -1)
+  echo "${n:-0}"
+}
+
+# shard_pglite_init_count: count "Schema version" lines as a proxy for "test
+# files initialized so far." Each PGLite-using test file's beforeAll triggers
+# one initSchema() which prints this. Undercounts because not every test file
+# opens a PGLite engine, but it's the only real-time progress signal bun's
+# default reporter leaves in the log (bun has no per-file progress markers,
+# only a final shard-end summary).
+shard_pglite_init_count() {
+  local file="$1"
+  [ -f "$file" ] || { echo 0; return; }
+  grep -cE 'Schema version [0-9]+ → [0-9]+' "$file" 2>/dev/null || echo 0
+}
+
+# log_size_kb: total stderr+stdout written by the shard so far. Strictly
+# monotonic — useful as a "definitely alive" signal when other heuristics
+# read 0 (e.g. very early in shard startup before initSchema fires).
+log_size_kb() {
+  local file="$1"
+  [ -f "$file" ] || { echo 0; return; }
+  local b
+  b=$(wc -c < "$file" 2>/dev/null | tr -d ' ')
+  echo $(( ${b:-0} / 1024 ))
+}
+
+# fmt_elapsed: pretty-print seconds → "Mm:SS" or "SSs" for short.
+fmt_elapsed() {
+  local s=$1
+  if [ "$s" -ge 60 ]; then
+    printf '%dm%02ds' $((s / 60)) $((s % 60))
+  else
+    printf '%ds' "$s"
+  fi
+}
+
 heartbeat() {
+  local hb_start=$(date +%s)
  while true; do
    sleep 10
    local line=""
+    local now; now=$(date +%s)
+    local hb_elapsed=$((now - hb_start))
    for i in $(seq 1 "$N"); do
      if [ -f "$LOG_DIR/shard-$i.exit" ]; then
        local rc; rc=$(cat "$LOG_DIR/shard-$i.exit" 2>/dev/null || echo "?")
        local status="✓"
        [ "$rc" != "0" ] && status="✗"
-        line="$line [s$i: done $status]"
+        local f
+        f=$(bun_summary_count "fail" "$LOG_DIR/shard-$i.log")
+        local p
+        p=$(bun_summary_count "pass" "$LOG_DIR/shard-$i.log")
+        line="$line [s$i: done $status ${p}p ${f}f]"
      else
        local lf="$LOG_DIR/shard-$i.log"
        if [ -f "$lf" ]; then
-          # Heartbeat: prefer Bun's per-test "✓" (passed) and "(fail)" markers
-          # so we see live progress; the "N pass" summary line only appears at
-          # the very end of the shard and would always show 0 mid-run.
-          local p f
-          p=$(grep_count '^[[:space:]]+✓' "$lf")
-          f=$(grep_count '^\(fail\)' "$lf")
-          line="$line [s$i: ${p}p ${f}f ...]"
+          # Bun's default reporter has no per-file progress markers, only a
+          # final shard-end summary, so we surface three complementary signals
+          # mid-run: (1) PGLite initSchema() count as a "files started" proxy,
+          # (2) total files this shard was assigned (from the runner banner),
+          # (3) log size in KB as a strictly-monotonic liveness signal.
+          local total; total=$(shard_total_files "$lf")
+          local pglite; pglite=$(shard_pglite_init_count "$lf")
+          local kb; kb=$(log_size_kb "$lf")
+          local et; et=$(fmt_elapsed "$hb_elapsed")
+          if [ "$total" -gt 0 ]; then
+            line="$line [s$i: ~${pglite}/${total}f ${kb}KB ${et}]"
+          else
+            line="$line [s$i: starting ${kb}KB ${et}]"
+          fi
        else
-          line="$line [s$i: starting]"
+          line="$line [s$i: spawning]"
        fi
      fi
    done
@@ -2,6 +2,7 @@ import { existsSync, readFileSync, writeFileSync, statSync } from 'fs';
 import { execFileSync } from 'child_process';
 import { join, relative } from 'path';
 import type { BrainEngine } from '../core/engine.ts';
+import { DELETE_BATCH_SIZE } from '../core/engine-constants.ts';
 import { importFile } from '../core/import-file.ts';
 import { collectSyncableFiles } from './import.ts';
 import { createInterface } from 'readline';
@@ -269,14 +270,25 @@ export async function resolveSlugByPathOrSourcePath(
  path: string,
  sourceId?: string,
 ): Promise<string> {
+  // v0.41.19.0 (D8): when sourceId is set, delegate to the new batch
+  // resolveSlugsByPaths so single-call and batched paths share one SQL
+  // owner + one fallback semantic. One Map allocation per single-call;
+  // negligible cost. When sourceId is undefined (legacy unscoped callers),
+  // fall back to the original executeRaw shape — the batch method
+  // requires sourceId to prevent the multi-source-bug-class on its new
+  // surface (D5). The unscoped fallback preserves back-compat.
  try {
-    const rows = await engine.executeRaw<{ slug: string }>(
-      sourceId
-        ? `SELECT slug FROM pages WHERE source_path = $1 AND source_id = $2 LIMIT 1`
-        : `SELECT slug FROM pages WHERE source_path = $1 LIMIT 1`,
-      sourceId ? [path, sourceId] : [path],
-    );
-    if (rows.length > 0 && rows[0].slug) return rows[0].slug;
+    if (sourceId) {
+      const m = await engine.resolveSlugsByPaths([path], { sourceId });
+      const slug = m.get(path);
+      if (slug) return slug;
+    } else {
+      const rows = await engine.executeRaw<{ slug: string }>(
+        `SELECT slug FROM pages WHERE source_path = $1 LIMIT 1`,
+        [path],
+      );
+      if (rows.length > 0 && rows[0].slug) return rows[0].slug;
+    }
  } catch {
    // Fall through — best-effort. Pre-migration brains or query errors
    // shouldn't break delete/rename for path-derived pages.
@@ -1233,25 +1245,135 @@ async function performSyncInner(engine: BrainEngine, opts: SyncOpts): Promise<Sy
  // Phases: sync.deletes, sync.renames, sync.imports.
  const progress = createProgress(cliOptsToProgressOptions(getCliOptions()));

-  // Process deletes first (prevents slug conflicts). SP-5: resolveSlugForPath
-  // dispatches to the right slug shape so code file deletes hit the real page.
+  // v0.41.19.0: hoisted out of the import block so the delete decompose
+  // path (per-batch try-catch fallback) can append unrecoverable delete
+  // failures here too. Same canonical surface that gates `sync.last_commit`
+  // advancement at the bottom of this function.
+  const failedFiles: Array<{ path: string; error: string; line?: number }> = [];
+
  // v0.18.0+ multi-source: scope deletePage so we only delete the source-A
  // row, not every same-slug row across all sources.
  const deleteOpts = opts.sourceId ? { sourceId: opts.sourceId } : undefined;
+
+  // v0.41.19.0 (T2/D6/D7/D16/D18 via /plan-eng-review + codex outside-voice):
+  // batched delete loop. Replaces the per-file N+1 that PR #1538 originally
+  // batched on Postgres only. See plan file:
+  //   ~/.claude/plans/system-instruction-you-are-working-ethereal-narwhal.md
+  //
+  // SHAPE (interleaved per-batch resolve + delete; caller owns chunking):
+  //
+  //   filtered.deleted (e.g. 73K paths)
+  //       │
+  //       ▼
+  //   slice into batches of DELETE_BATCH_SIZE (500)
+  //       │
+  //       ▼  for each batch:
+  //   abort-check ──► partial('timeout')
+  //       │
+  //       ▼
+  //   engine.resolveSlugsByPaths(batch, {sourceId})  ◀── 1 SQL round-trip
+  //       │
+  //       ▼
+  //   slugs = batch.map(path => map.get(path)
+  //                  ?? resolveSlugForPath(path))    ◀── pure-JS fallback for
+  //       │                                              frontmatter-fallback
+  //       ▼                                              + missing-source-path
+  //   try {
+  //     deleted = engine.deletePages(slugs, opts)    ◀── 1 SQL round-trip
+  //     pagesAffected.push(...deleted)               ◀── D6: only confirmed
+  //   } catch {                                          deletes, not phantoms
+  //     // D7 decompose: per-slug deletePage,
+  //     // unrecoverable failures → failedFiles
+  //   }
+  //
+  // ROUND-TRIP COUNTS (73K deletes):
+  //   pre-fix:   73,000 SELECTs + 73,000 DELETEs = 146,000 (~5 hours)
+  //   post-fix:     146 SELECTs +     146 DELETEs =     292 (~2 minutes)
+  //
+  // ATOMICITY (D3): each batch is one transaction. A mid-batch abort or
+  // transient connection failure rolls back up to DELETE_BATCH_SIZE - 1
+  // successful deletes. Sync is idempotent — the next run picks them up
+  // via git diff regenerating the deletion list.
+  //
+  // NO-SOURCEID FALLBACK: when opts.sourceId is undefined (legacy unscoped
+  // callers, rare post-v0.34.1 source-resolution wiring), fall back to the
+  // OLD per-path loop. The batch engine surface requires sourceId per D5
+  // (multi-source-bug-class defense at the type level). Production callers
+  // that thread sourceId via resolveSourceWithTier get the new fast path.
  if (filtered.deleted.length > 0) {
    progress.start('sync.deletes', filtered.deleted.length);
-    for (const path of filtered.deleted) {
-      // v0.41.13.0 (T2 / D-V4-2): per-iteration abort check. Codex pass-3
-      // F8 caught that v3 only covered pull + add/modify. Refactor commits
-      // with hundreds of deletes can overshoot --timeout without this check.
-      if (opts.signal?.aborted) {
-        progress.finish();
-        return partial('timeout');
+    if (opts.sourceId) {
+      const sid = opts.sourceId;
+      const deleteScopedOpts = { sourceId: sid };
+      for (let i = 0; i < filtered.deleted.length; i += DELETE_BATCH_SIZE) {
+        if (opts.signal?.aborted) {
+          progress.finish();
+          return partial('timeout');
+        }
+        const batch = filtered.deleted.slice(i, i + DELETE_BATCH_SIZE);
+
+        // Phase A: batch slug resolution (1 round-trip per batch).
+        let pathSlugMap: Map<string, string>;
+        try {
+          pathSlugMap = await engine.resolveSlugsByPaths(batch, deleteScopedOpts);
+        } catch {
+          // Resolve failure: fall back to empty map; per-path fallback
+          // below will use resolveSlugForPath. Best-effort, matches the
+          // existing resolveSlugByPathOrSourcePath swallow-and-fallback
+          // semantics.
+          pathSlugMap = new Map();
+        }
+        const slugs = batch.map(p => pathSlugMap.get(p) ?? resolveSlugForPath(p));
+
+        // Phase B: batch delete (1 round-trip per batch).
+        try {
+          const deleted = await engine.deletePages(slugs, deleteScopedOpts);
+          // D6: only push slugs that were actually deleted. Filters phantom
+          // slugs (paths in filtered.deleted but with no DB row) so
+          // downstream extract/embed don't waste lookups.
+          pagesAffected.push(...deleted);
+        } catch (err) {
+          // D7 decompose: a transient blip on this batch shouldn't lose all
+          // 500 deletes. Fall back to per-slug deletePage for THIS batch
+          // only; unrecoverable per-slug failures land in failedFiles
+          // (matching the existing import-loop pattern at sync.ts:~1350).
+          for (let j = 0; j < slugs.length; j++) {
+            try {
+              await engine.deletePage(slugs[j], deleteScopedOpts);
+              pagesAffected.push(slugs[j]);
+            } catch (perSlugErr) {
+              failedFiles.push({
+                path: batch[j],
+                error: `delete failed: ${perSlugErr instanceof Error ? perSlugErr.message : String(perSlugErr)} (batch error: ${err instanceof Error ? err.message : String(err)})`,
+              });
+            }
+          }
+        }
+        progress.tick(batch.length, `deletes ${Math.min(i + DELETE_BATCH_SIZE, filtered.deleted.length)}/${filtered.deleted.length}`);
+      }
+    } else {
+      // Legacy no-sourceId path. The engine batch methods require sourceId
+      // per D5 (kills the multi-source-bug-class on the new surface); when
+      // sourceId is unset, fall back to the original per-path loop. Slow
+      // but correct; production callers all thread sourceId so this branch
+      // is functionally dead post-v0.34.1.
+      for (const path of filtered.deleted) {
+        if (opts.signal?.aborted) {
+          progress.finish();
+          return partial('timeout');
+        }
+        const slug = await resolveSlugByPathOrSourcePath(engine, path, undefined);
+        try {
+          await engine.deletePage(slug, deleteOpts);
+          pagesAffected.push(slug);
+        } catch (err) {
+          failedFiles.push({
+            path,
+            error: `delete failed: ${err instanceof Error ? err.message : String(err)}`,
+          });
+        }
+        progress.tick(1, slug);
      }
-      const slug = await resolveSlugByPathOrSourcePath(engine, path, opts.sourceId);
-      await engine.deletePage(slug, deleteOpts);
-      pagesAffected.push(slug);
-      progress.tick(1, slug);
    }
    progress.finish();
  }
@@ -1260,12 +1382,46 @@ async function performSyncInner(engine: BrainEngine, opts: SyncOpts): Promise<Sy
  // SP-5: both old and new slugs use resolveSlugForPath so a .ts → .ts
  // rename (code→code), .md → .md (markdown→markdown), or cross-kind rename
  // all resolve to the right slug shape for each side.
+  //
+  // v0.41.19.0 (T4): pre-batched slug resolution per Phase 3 of the plan.
+  // Renames' per-file cost is dominated by importFile() (file IO + chunking
+  // + embedding), so the per-iteration updateSlug + importFile loop stays;
+  // only the upfront slug-resolve N+1 gets batched. The try/catch around
+  // updateSlug for slug-doesn't-exist preserves verbatim.
  if (filtered.renamed.length > 0) {
    progress.start('sync.renames', filtered.renamed.length);
    // v0.18.0+ multi-source: scope updateSlug so the rename only touches the
    // source-A row, not every same-slug row across sources (which would
    // either sweep them all OR violate (source_id, slug) UNIQUE).
    const renameOpts = opts.sourceId ? { sourceId: opts.sourceId } : undefined;
+
+    // T4: pre-resolve ALL `from` slugs in batches before iterating. Falls
+    // back to per-path resolveSlugByPathOrSourcePath when sourceId is
+    // unset (matches the delete loop's legacy posture). For large rename
+    // commits (rare but possible: prefix sweep, reorganization), this drops
+    // the slug-resolve round-trips from O(renames) to O(renames/500).
+    const fromSlugByPath = new Map<string, string>();
+    if (opts.sourceId) {
+      const sid = opts.sourceId;
+      const fromPaths = filtered.renamed.map(r => r.from);
+      for (let i = 0; i < fromPaths.length; i += DELETE_BATCH_SIZE) {
+        if (opts.signal?.aborted) {
+          progress.finish();
+          return partial('timeout');
+        }
+        const batch = fromPaths.slice(i, i + DELETE_BATCH_SIZE);
+        let m: Map<string, string>;
+        try {
+          m = await engine.resolveSlugsByPaths(batch, { sourceId: sid });
+        } catch {
+          m = new Map();
+        }
+        for (const p of batch) {
+          fromSlugByPath.set(p, m.get(p) ?? resolveSlugForPath(p));
+        }
+      }
+    }
+
    for (const { from, to } of filtered.renamed) {
      // v0.41.13.0 (T2 / D-V4-2): per-iteration abort check. Renames call
      // importFile() at line 1173-style sites which can be slow on big files;
@@ -1274,7 +1430,9 @@ async function performSyncInner(engine: BrainEngine, opts: SyncOpts): Promise<Sy
        progress.finish();
        return partial('timeout');
      }
-      const oldSlug = await resolveSlugByPathOrSourcePath(engine, from, opts.sourceId);
+      const oldSlug = opts.sourceId
+        ? (fromSlugByPath.get(from) ?? resolveSlugForPath(from))
+        : await resolveSlugByPathOrSourcePath(engine, from, undefined);
      // The new path doesn't yet have a row, so resolve from path only.
      const newSlug = resolveSlugForPath(to);
      try {
@@ -1308,7 +1466,10 @@ async function performSyncInner(engine: BrainEngine, opts: SyncOpts): Promise<Sy
  // v0.15.2: per-file progress on stderr via the shared reporter.
  // Bug 9: per-file failures captured in `failedFiles` so the caller can
  // gate `sync.last_commit` advancement and record recoverable errors.
-  const failedFiles: Array<{ path: string; error: string; line?: number }> = [];
+  // v0.41.19.0: `failedFiles` is now hoisted above the delete loop (the
+  // delete decompose path appends here too); kept as a comment-pin so
+  // future maintainers know to thread additional failure surfaces through
+  // the same array.
  const addsAndMods = [...filtered.added, ...filtered.modified];

  // Sort newest-first so date-prefixed brain paths get embedded before older
@@ -0,0 +1,25 @@
+// v0.41.25.0 — single source of truth for engine batch-sizing.
+//
+// Both PostgresEngine and PGLiteEngine import from here so the constants
+// cannot drift across engines. Lives outside `src/core/engine.ts` to avoid
+// circular-import worries (engine.ts is the interface; engines depend on
+// engine.ts; this file depends on neither).
+
+/**
+ * Maximum number of slugs per single batch `DELETE FROM pages WHERE slug =
+ * ANY($1::text[])` call. Callers (e.g. `src/commands/sync.ts` delete loop)
+ * are responsible for chunking input arrays to this size; `engine.deletePages`
+ * is a single-batch primitive that does NOT chunk internally (matches the
+ * `addLinksBatch` convention — caller owns chunking, engine assumes the
+ * caller is well-behaved).
+ *
+ * 500 is the same order-of-magnitude as the effective per-call budget for
+ * the existing `addLinksBatch` (postgres-engine.ts) — well under Postgres's
+ * 65535 parameter cap. We bind a single array parameter so the cap doesn't
+ * bite directly, but per-statement work stays bounded for predictable lock
+ * hold time + write-amplification budget.
+ *
+ * The same constant is also used for batch `SELECT slug, source_path FROM
+ * pages WHERE source_path = ANY($1::text[])` in `engine.resolveSlugsByPaths`.
+ */
+export const DELETE_BATCH_SIZE = 500;
@@ -712,8 +712,67 @@ export interface BrainEngine {
   * delete doesn't hard-delete the same-slug pages in sources B/C/D. Without
   * it, the bare DELETE matches every row with that slug across all sources.
   * Cascades through content_chunks / page_links / chunk_relations via FKs.
+   *
+   * v0.41.19.0 (CDX-11): single-row primitive used by `purgeDeletedPages`,
+   * `gbrain sync` (one path per call), test setup teardown, and the v0.41.19.0
+   * sync-delete decompose path (when `deletePages` throws on a 500-row batch,
+   * the sync loop falls back to per-slug `deletePage` to log unrecoverable
+   * failures to `failedFiles`). `gbrain sync` calls this on EVERY run that
+   * sees a deleted file — it is NOT admin-only.
   */
  deletePage(slug: string, opts?: { sourceId?: string }): Promise<void>;
+  /**
+   * v0.41.19.0 — batch delete: single SQL round-trip via
+   * `DELETE FROM pages WHERE slug = ANY($1::text[]) AND source_id = $2
+   *  RETURNING slug`. Cascades through content_chunks / page_links (×3) /
+   * tags / raw_data / timeline_entries / page_versions via FKs declared in
+   * `src/schema.sql`. `files.page_id` and `links.origin_page_id` go SET
+   * NULL per their FK definitions.
+   *
+   * SINGLE-BATCH PRIMITIVE: caller is responsible for chunking the input to
+   * `<= DELETE_BATCH_SIZE` entries per call (see
+   * `src/core/engine-constants.ts`). Matches the `addLinksBatch` convention
+   * — engine assumes well-behaved input, caller owns the slicing.
+   *
+   * Returns the slugs of rows ACTUALLY DELETED (order undefined). Callers
+   * use this to filter their own `pagesAffected` tracking so downstream
+   * phases don't waste lookups on phantom slugs (paths that were in the
+   * deletion list but had no DB row).
+   *
+   * ATOMICITY: one statement, one transaction. The whole batch commits or
+   * the whole batch rolls back. Coarser than the per-row `deletePage`
+   * cadence — a mid-loop abort or transient connection failure can roll
+   * back up to `DELETE_BATCH_SIZE - 1` successful deletes from the
+   * in-flight batch. `gbrain sync` is idempotent (next run picks them up
+   * via git diff); other callers should account for the contract.
+   *
+   * sourceId is REQUIRED (no `'default'` fallback). This is asymmetric with
+   * `deletePage` (which keeps the optional/'default' fallback for back-
+   * compat). Filed as v0.42+ TODO to tighten `deletePage` to match once a
+   * full caller audit confirms every site threads `sourceId`.
+   */
+  deletePages(slugs: string[], opts: { sourceId: string }): Promise<string[]>;
+  /**
+   * v0.41.19.0 — batch path → slug resolution. Single SQL round-trip via
+   * `SELECT slug, source_path FROM pages WHERE source_path = ANY($1::text[])
+   *  AND source_id = $2`. Returns `Map<path, slug>`; paths NOT in the map
+   * have no `source_path` row in the DB and the caller is expected to fall
+   * back to `resolveSlugForPath(path)` for the path-derived slug.
+   *
+   * Mirrors the contract of the single-call `resolveSlugByPathOrSourcePath`
+   * helper in `src/commands/sync.ts`, batched. As of v0.41.19.0, that
+   * single-call helper is implemented on top of this method (one Map
+   * allocation per single-path call; negligible cost; one owner of the SQL
+   * + fallback semantics).
+   *
+   * SINGLE-BATCH PRIMITIVE: caller chunks to `<= DELETE_BATCH_SIZE`.
+   *
+   * Empty `paths` short-circuits to an empty Map without touching the DB.
+   */
+  resolveSlugsByPaths(
+    paths: string[],
+    opts: { sourceId: string },
+  ): Promise<Map<string, string>>;
  /**
   * v0.26.5 — set `deleted_at = now()` on a page. Returns the slug if a row
   * was soft-deleted, null if no row matched (already soft-deleted OR not found).
@@ -4856,6 +4856,70 @@ export const MIGRATIONS: Migration[] = [
        ON extract_rollup_7d (day);
    `,
  },
+  {
+    version: 107,
+    name: 'page_generation_clock_and_statement_trigger',
+    // v0.41.25.0 (D18/D19, codex outside-voice on /plan-eng-review): global
+    // page-generation clock + statement-level trigger.
+    //
+    // Renumbered v104 → v105 → v106 → v107 during master merges:
+    //   PR #1545 (v0.41.21.0 ops-fix-wave) took v104 for pages_atom_source_hash_idx;
+    //   PR #1542 (v0.41.22.0 type-unification cathedral) took v105 for slug_aliases;
+    //   PR #1541 (v0.41.23.0 extract operator surfaces) took v106 for extract_rollup_7d_table.
+    //
+    // Why this exists: the pre-v0.41.25.0 query-cache Layer 1 bookmark read
+    // `MAX(generation) FROM pages` to detect "writes happened since cache
+    // store". Two bugs in that contract — independent of any sync work:
+    //
+    //   1. The row-level `bump_page_generation_trg` (migration v91) sets
+    //      `NEW.generation = OLD.generation + 1` on UPDATE. Updating a
+    //      NON-MAX page didn't advance MAX(generation). Cache silently
+    //      served stale results for any UPDATE-to-non-max page.
+    //   2. The trigger is BEFORE INSERT OR UPDATE — DELETE doesn't fire it
+    //      at all. Even an AFTER DELETE wouldn't move MAX (surviving rows
+    //      are untouched).
+    //
+    // The fix: single-row counter, bumped per-statement (FOR EACH STATEMENT
+    // — row-level would turn a 73K-row batch DELETE into 73K UPDATEs on the
+    // same counter, recreating the bottleneck the sync-delete wave is
+    // fixing in this same PR). Layer 1 reads page_generation_clock.value
+    // directly. Per-row pages.generation stays for Layer 2 (per-page
+    // snapshot via jsonb_each + LEFT JOIN pages) which doesn't care about
+    // MAX, only per-page advancement.
+    //
+    // Seeded with COALESCE(MAX(pages.generation), 0) so existing
+    // query_cache rows stored under the old MAX semantics aren't all
+    // instantly invalidated on upgrade. Their max_generation_at_store
+    // stamp compares cleanly against the seeded clock; future writes bump
+    // the clock and the bookmark fires correctly.
+    //
+    // Mirror lives in src/core/pglite-schema.ts (fresh-install path).
+    // Forward-reference bootstrap probe in applyForwardReferenceBootstrap
+    // on both engines so pre-v0.41.25.0 brains pick it up cleanly.
+    idempotent: true,
+    sql: `
+      CREATE TABLE IF NOT EXISTS page_generation_clock (
+        id    INTEGER PRIMARY KEY CHECK (id = 1),
+        value BIGINT  NOT NULL DEFAULT 0
+      );
+      INSERT INTO page_generation_clock (id, value)
+        VALUES (1, COALESCE((SELECT MAX(generation) FROM pages), 0))
+        ON CONFLICT (id) DO NOTHING;
+
+      CREATE OR REPLACE FUNCTION bump_page_generation_clock_fn() RETURNS trigger AS $func$
+      BEGIN
+        UPDATE page_generation_clock SET value = value + 1 WHERE id = 1;
+        RETURN NULL;
+      END;
+      $func$ LANGUAGE plpgsql;
+
+      DROP TRIGGER IF EXISTS bump_page_generation_clock_trg ON pages;
+      CREATE TRIGGER bump_page_generation_clock_trg
+        AFTER INSERT OR UPDATE OR DELETE ON pages
+        FOR EACH STATEMENT
+        EXECUTE FUNCTION bump_page_generation_clock_fn();
+    `,
+  },
 ];

 export const LATEST_VERSION = MIGRATIONS.length > 0
@@ -22,6 +22,7 @@ import { logBatchRetry as auditLogBatchRetry, logBatchExhausted as auditLogBatch
 import { runMigrations } from './migrate.ts';
 import { PGLITE_SCHEMA_SQL, getPGLiteSchema } from './pglite-schema.ts';
 import { DEFAULT_EMBEDDING_MODEL, DEFAULT_EMBEDDING_DIMENSIONS } from './ai/defaults.ts';
+import { DELETE_BATCH_SIZE } from './engine-constants.ts';
 import { acquireLock, releaseLock, type LockHandle } from './pglite-lock.ts';
 import type {
  Page, PageInput, PageFilters, PageType,
@@ -900,6 +901,49 @@ export class PGLiteEngine implements BrainEngine {
    );
  }

+  /**
+   * v0.41.19.0 — batch delete primitive. See BrainEngine.deletePages JSDoc.
+   * Parity implementation with PostgresEngine.deletePages. PGLite supports
+   * `slug = ANY($1)` array-param binding natively (addLinksBatch already
+   * proves this).
+   */
+  async deletePages(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.`,
+      );
+    }
+    const { rows } = await this.db.query<{ slug: string }>(
+      'DELETE FROM pages WHERE slug = ANY($1::text[]) AND source_id = $2 RETURNING slug',
+      [slugs, opts.sourceId],
+    );
+    return rows.map(r => r.slug);
+  }
+
+  /**
+   * v0.41.19.0 — batch path → slug resolution. See BrainEngine.resolveSlugsByPaths
+   * JSDoc.
+   */
+  async resolveSlugsByPaths(
+    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 this.db.query<{ slug: string; source_path: string }>(
+      'SELECT slug, source_path FROM pages WHERE source_path = ANY($1::text[]) AND source_id = $2',
+      [paths, opts.sourceId],
+    );
+    const m = new Map<string, string>();
+    for (const r of rows) m.set(r.source_path, r.slug);
+    return m;
+  }
+
  async softDeletePage(slug: string, opts?: { sourceId?: string }): Promise<{ slug: string } | null> {
    // Idempotent-as-null: only flip rows currently active. Source filter is
    // optional; without it the first matching row across sources gets soft-deleted.
@@ -144,6 +144,32 @@ CREATE TRIGGER bump_page_generation_trg
  FOR EACH ROW
  EXECUTE FUNCTION bump_page_generation_fn();

+-- v0.41.19.0 (D18/D19, mirror of src/schema.sql): global page-generation
+-- clock + statement-level trigger. See src/schema.sql for the full
+-- rationale comment. Layer 1 bookmark reads page_generation_clock.value;
+-- per-row pages.generation above stays as the Layer 2 (per-page snapshot)
+-- substrate.
+CREATE TABLE IF NOT EXISTS page_generation_clock (
+  id    INTEGER PRIMARY KEY CHECK (id = 1),
+  value BIGINT  NOT NULL DEFAULT 0
+);
+INSERT INTO page_generation_clock (id, value)
+  VALUES (1, COALESCE((SELECT MAX(generation) FROM pages), 0))
+  ON CONFLICT (id) DO NOTHING;
+
+CREATE OR REPLACE FUNCTION bump_page_generation_clock_fn() RETURNS trigger AS $func$
+BEGIN
+  UPDATE page_generation_clock SET value = value + 1 WHERE id = 1;
+  RETURN NULL;
+END;
+$func$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS bump_page_generation_clock_trg ON pages;
+CREATE TRIGGER bump_page_generation_clock_trg
+  AFTER INSERT OR UPDATE OR DELETE ON pages
+  FOR EACH STATEMENT
+  EXECUTE FUNCTION bump_page_generation_clock_fn();
+
 CREATE INDEX IF NOT EXISTS idx_pages_type ON pages(type);
 CREATE INDEX IF NOT EXISTS idx_pages_frontmatter ON pages USING GIN(frontmatter);
 CREATE INDEX IF NOT EXISTS idx_pages_trgm ON pages USING GIN(title gin_trgm_ops);
@@ -57,6 +57,7 @@ import { validateSlug, contentHash, rowToPage, rowToChunk, rowToSearchResult, pa
 import { resolveBoostMap, resolveHardExcludes } from './search/source-boost.ts';
 import { buildSourceFactorCase, buildHardExcludeClause, buildVisibilityClause, buildRecencyComponentSql } from './search/sql-ranking.ts';
 import { DEFAULT_EMBEDDING_MODEL, DEFAULT_EMBEDDING_DIMENSIONS } from './ai/defaults.ts';
+import { DELETE_BATCH_SIZE } from './engine-constants.ts';

 function escapeSqlStringLiteral(value: string): string {
  return value.replace(/'/g, "''");
@@ -911,6 +912,53 @@ export class PostgresEngine implements BrainEngine {
    await sql`DELETE FROM pages WHERE slug = ${slug} AND source_id = ${sourceId}`;
  }

+  /**
+   * v0.41.19.0 — batch delete primitive. See BrainEngine.deletePages JSDoc.
+   * Single SQL round-trip per call; caller is responsible for chunking input
+   * to <= DELETE_BATCH_SIZE. RETURNING slug projects the actually-deleted set
+   * so the caller can filter pagesAffected.
+   */
+  async deletePages(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.`,
+      );
+    }
+    const sql = this.sql;
+    const rows = await sql<{ slug: string }[]>`
+      DELETE FROM pages
+       WHERE slug = ANY(${slugs}::text[]) AND source_id = ${opts.sourceId}
+      RETURNING slug
+    `;
+    return rows.map(r => r.slug);
+  }
+
+  /**
+   * v0.41.19.0 — batch path → slug resolution. See BrainEngine.resolveSlugsByPaths
+   * JSDoc. Single SQL round-trip; folds rows into a Map.
+   */
+  async resolveSlugsByPaths(
+    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 sql = this.sql;
+    const rows = await sql<{ slug: string; source_path: string }[]>`
+      SELECT slug, source_path
+        FROM pages
+       WHERE source_path = ANY(${paths}::text[]) AND source_id = ${opts.sourceId}
+    `;
+    const m = new Map<string, string>();
+    for (const r of rows) m.set(r.source_path, r.slug);
+    return m;
+  }
+
  async softDeletePage(slug: string, opts?: { sourceId?: string }): Promise<{ slug: string } | null> {
    const sql = this.sql;
    const sourceId = opts?.sourceId;
@@ -185,6 +185,48 @@ CREATE TRIGGER bump_page_generation_trg
 -- CREATE INDEX since the table is empty.
 CREATE INDEX IF NOT EXISTS pages_generation_idx ON pages (generation);

+-- v0.41.19.0 (D18/D19, codex outside-voice): global page-generation clock.
+-- The pre-v0.41.19.0 Layer 1 bookmark read \`MAX(generation) FROM pages\` to
+-- detect "writes happened since cache-store". Two bugs in that contract:
+--   1. The row-level trigger above sets \`NEW.generation = OLD.generation + 1\`
+--      on UPDATE. Updating a NON-MAX page didn't advance MAX(generation),
+--      silently serving stale cached results.
+--   2. The trigger is \`BEFORE INSERT OR UPDATE\` so DELETE doesn't fire it
+--      at all — and even if it did, DELETE doesn't touch surviving rows,
+--      so MAX(generation) wouldn't budge.
+--
+-- The fix: a single-row counter, bumped per-statement (FOR EACH STATEMENT
+-- — codex CDX-4: per-row would turn 73K-row batch DELETE into 73K UPDATEs
+-- on the same counter, recreating the bottleneck this PR is fixing). Layer
+-- 1 reads \`page_generation_clock.value\` directly. The per-row
+-- \`pages.generation\` column above stays as the Layer 2 (per-page snapshot)
+-- substrate.
+--
+-- Seeded with COALESCE(MAX(pages.generation), 0) so existing query_cache
+-- rows stored under the old MAX semantics aren't all instantly invalidated
+-- on upgrade (their max_generation_at_store stamp compares cleanly against
+-- the seeded clock; future writes bump the clock, bookmark fires correctly).
+CREATE TABLE IF NOT EXISTS page_generation_clock (
+  id    INTEGER PRIMARY KEY CHECK (id = 1),
+  value BIGINT  NOT NULL DEFAULT 0
+);
+INSERT INTO page_generation_clock (id, value)
+  VALUES (1, COALESCE((SELECT MAX(generation) FROM pages), 0))
+  ON CONFLICT (id) DO NOTHING;
+
+CREATE OR REPLACE FUNCTION bump_page_generation_clock_fn() RETURNS trigger AS \$func\$
+BEGIN
+  UPDATE page_generation_clock SET value = value + 1 WHERE id = 1;
+  RETURN NULL;
+END;
+\$func\$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS bump_page_generation_clock_trg ON pages;
+CREATE TRIGGER bump_page_generation_clock_trg
+  AFTER INSERT OR UPDATE OR DELETE ON pages
+  FOR EACH STATEMENT
+  EXECUTE FUNCTION bump_page_generation_clock_fn();
+
 CREATE INDEX IF NOT EXISTS idx_pages_type ON pages(type);
 CREATE INDEX IF NOT EXISTS idx_pages_frontmatter ON pages USING GIN(frontmatter);
 CREATE INDEX IF NOT EXISTS idx_pages_trgm ON pages USING GIN(title gin_trgm_ops);
@@ -266,6 +308,13 @@ CREATE INDEX IF NOT EXISTS idx_chunks_embedding_image
 CREATE INDEX IF NOT EXISTS idx_chunks_search_vector ON content_chunks USING GIN(search_vector);
 CREATE INDEX IF NOT EXISTS idx_chunks_symbol_qualified
  ON content_chunks(symbol_name_qualified) WHERE symbol_name_qualified IS NOT NULL;
+-- v0.41.18.0 (codex finding #9): partial index for \`gbrain embed --stale\`
+-- + \`--priority recent\`. content_chunks has no updated_at column (chunks
+-- are re-INSERTed on page change, not UPDATEd), so the "recent-first"
+-- ORDER BY happens at the JOIN site: outer ORDER BY p.updated_at DESC
+-- uses idx_pages_updated_at_desc; inner partial uses this index.
+CREATE INDEX IF NOT EXISTS content_chunks_stale_idx
+  ON content_chunks(page_id, chunk_index) WHERE embedding IS NULL;

 -- v0.20.0 Cathedral II: chunk-grain FTS trigger.
 -- Weight 'A' on doc_comment + symbol_name_qualified; weight 'B' on chunk_text.
@@ -357,10 +406,16 @@ CREATE TABLE IF NOT EXISTS links (
  to_page_id     INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE,
  link_type      TEXT    NOT NULL DEFAULT '',
  context        TEXT    NOT NULL DEFAULT '',
-  -- v0.42.0.0: 'mentions' added for auto-linked body-text mentions
+  -- v0.41.18.0: 'mentions' added for auto-linked body-text mentions
  -- (gbrain extract links --by-mention). Filtered OUT of backlink-count
  -- for search ranking; only counts toward orphan-ratio + graph traversal.
  link_source    TEXT    CHECK (link_source IS NULL OR link_source IN ('markdown', 'frontmatter', 'manual', 'mentions')),
+  -- v0.41.18.0: nullable link_kind distinguishes "plain body mention" from
+  -- "verb-pattern-derived typed link" within link_source='mentions'.
+  -- Codex finding #12 design: keep link_source stable; add link_kind
+  -- so callers can distinguish without breaking existing mentions queries.
+  -- NULL = legacy / unknown / pre-v98 row (semantically 'plain').
+  link_kind      TEXT    CHECK (link_kind IS NULL OR link_kind IN ('plain', 'typed_ner')),
  origin_page_id INTEGER REFERENCES pages(id) ON DELETE SET NULL,
  origin_field   TEXT,
  -- v0.18.0 Step 4: 'qualified' when the link was written as
@@ -423,8 +478,10 @@ CREATE TABLE IF NOT EXISTS timeline_entries (

 CREATE INDEX IF NOT EXISTS idx_timeline_page ON timeline_entries(page_id);
 CREATE INDEX IF NOT EXISTS idx_timeline_date ON timeline_entries(date);
-- Dedup constraint: same (page, date, summary) treated as same event
-CREATE UNIQUE INDEX IF NOT EXISTS idx_timeline_dedup ON timeline_entries(page_id, date, summary);
+-- v0.41.18.0 (codex finding #11): widened from (page_id, date, summary) to
+-- include \`source\` so distinct meeting provenance survives. Legacy rows
+-- have source='' (schema default) so legacy dedup behavior is preserved.
+CREATE UNIQUE INDEX IF NOT EXISTS idx_timeline_dedup ON timeline_entries(page_id, date, summary, source);

 -- ============================================================
 -- page_versions: snapshot history for compiled_truth
@@ -589,6 +646,10 @@ CREATE TABLE IF NOT EXISTS op_checkpoints (
 CREATE INDEX IF NOT EXISTS op_checkpoints_updated_at_idx
  ON op_checkpoints (updated_at);

+-- migration_impact_log moved BELOW minion_jobs (was here, lines 645-676)
+-- because its \`job_id BIGINT REFERENCES minion_jobs(id)\` FK requires
+-- minion_jobs to exist FIRST during SCHEMA_SQL replay. v0.41.25.0 fix.
+
 -- ============================================================
 -- files: binary attachments stored in Supabase Storage
 -- ============================================================
@@ -777,6 +838,50 @@ CREATE TABLE IF NOT EXISTS minion_attachments (
 CREATE INDEX IF NOT EXISTS idx_minion_attachments_job ON minion_attachments (job_id);
 ALTER TABLE minion_attachments ALTER COLUMN content SET STORAGE EXTERNAL;

+-- ============================================================
+-- migration_impact_log: before/after metric stats per onboard remediation
+-- ============================================================
+-- v0.41.18.0 (gbrain onboard wave). Every completion captured by the
+-- onboard remediation pipeline records before/after metric stats so
+-- \`gbrain onboard --history --json\` can show "you reduced orphans 47%".
+-- delta computed at read time (NOT a stored GENERATED column —
+-- zero PGLite parity risk per eng-review D2).
+--
+-- Attribution columns (job_id, source_id, brain_id, started_at,
+-- idempotency_key) per codex finding #10 so concurrent onboard /
+-- autopilot / manual runs can't misattribute deltas to the wrong
+-- migration when overlapping runs change the same metric.
+--
+-- v0.41.25.0 SCHEMA_SQL ordering fix: this block lives AFTER the
+-- minion_jobs CREATE TABLE so the \`job_id REFERENCES minion_jobs(id)\`
+-- FK can resolve on fresh-install schema replay. Originally placed above
+-- minion_jobs in v0.41.18.0; that fired ERROR: relation "minion_jobs"
+-- does not exist on every fresh-install initSchema() (silent on master
+-- because postgres-js's unsafe() continued past the error, but the
+-- table never got created so any later query on migration_impact_log
+-- threw 42P01 — which cascaded as "relation minion_jobs does not exist"
+-- whenever subsequent statements that referenced minion_jobs ran AFTER
+-- the failed CREATE TABLE statement, aborting the entire SCHEMA_SQL batch).
+CREATE TABLE IF NOT EXISTS migration_impact_log (
+  id              BIGSERIAL PRIMARY KEY,
+  remediation_id  TEXT      NOT NULL,
+  metric_name     TEXT      NOT NULL,
+  metric_before   NUMERIC,
+  metric_after    NUMERIC,
+  job_id          BIGINT    REFERENCES minion_jobs(id) ON DELETE SET NULL,
+  source_id       TEXT,
+  brain_id        TEXT,
+  started_at      TIMESTAMPTZ,
+  idempotency_key TEXT,
+  applied_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  applied_by      TEXT,
+  details         JSONB     DEFAULT '{}'::jsonb
+);
+CREATE INDEX IF NOT EXISTS migration_impact_log_remediation_idx
+  ON migration_impact_log(remediation_id, applied_at DESC);
+CREATE INDEX IF NOT EXISTS migration_impact_log_attribution_idx
+  ON migration_impact_log(job_id, source_id) WHERE job_id IS NOT NULL;
+
 -- ============================================================
 -- Subagent runtime (v0.16.0) — durable LLM loops
 -- ============================================================
@@ -1,27 +1,48 @@
 /**
- * Cache invalidation gate (v0.40.3.0 — D2 + D6 + D11)
+ * Cache invalidation gate (v0.40.3.0 base — D2 + D6 + D11; v0.41.19.0
+ * rewrite — D18 + D20, codex CDX-1/CDX-2/CDX-5/CDX-6).
 *
 * Two pure helpers wired by query-cache.ts at store + lookup time. Pure
 * surface lets us unit-test the two-layer gate logic without a real cache.
 *
- * Layer 1 (cheap bookmark): `MAX(generation) FROM pages` <=
- *   `query_cache.max_generation_at_store`. If true, brain has not been
- *   written since this row stored, so the row is fresh corpus-wide.
+ * Layer 1 (cheap bookmark): `page_generation_clock.value` <=
+ *   `query_cache.max_generation_at_store`. If true, no page write has
+ *   happened since this row stored, so the row is fresh corpus-wide.
 *
 * Layer 2 (per-page snapshot): if bookmark fires, fall through to the
 *   `page_generations JSONB` snapshot. For each `(page_id, stored_gen)`
 *   pair, compare to current `pages.generation`. Any mismatch (page
- *   deleted, page bumped) invalidates.
+ *   deleted, page bumped) invalidates. **Empty `page_generations = {}`
+ *   does NOT pass Layer 2 in v0.41.19.0+** — empty snapshots have no
+ *   per-page signal to invalidate against, so they MUST rely on Layer 1
+ *   exclusively (CDX-6 fix: pre-v0.41.19.0 the vacuous-valid path let
+ *   empty-result cache rows survive across writes that should have
+ *   invalidated them).
+ *
+ * Why the rewrite: pre-v0.41.19.0 Layer 1 read `MAX(generation) FROM
+ * pages`, but the per-row trigger sets `NEW.generation = OLD.generation
+ * + 1` on UPDATE. Updating a NON-MAX page didn't advance MAX(generation),
+ * so the bookmark silently passed stale cache rows (codex CDX-2). DELETE
+ * doesn't fire the trigger AT ALL, so deletion didn't advance MAX either
+ * (codex CDX-1). Migration v105 introduces a global single-row counter
+ * (`page_generation_clock`) bumped per-statement by a separate trigger;
+ * Layer 1 now reads the counter directly so every INSERT/UPDATE/DELETE
+ * statement advances the bookmark exactly once regardless of which rows
+ * changed.
 *
 * Backward compat: rows stored before v0.40.3.0 have
 *   `max_generation_at_store = 0` AND `page_generations = '{}'::jsonb`.
- *   Bookmark check: `MAX <= 0` is false on any populated brain, so we fall
- *   through to Layer 2; Layer 2 sees `'{}'::jsonb` and is vacuously valid.
- *   Legacy rows continue to serve naturally (IRON-RULE regression pinned
- *   in test/e2e/cache-gate-pglite.test.ts).
+ *   Layer 1 check on a populated brain: `clock > 0` so Layer 1 fails.
+ *   Layer 2 v0.41.19.0+ stricter: empty `{}` no longer passes — legacy
+ *   rows invalidate on first post-upgrade lookup. Cache fills back up
+ *   naturally; correct semantics restored. The pre-v0.41.19.0 IRON-RULE
+ *   "legacy rows serve via vacuously-valid Layer 2" is intentionally
+ *   reversed: that path was the CDX-6 bug.
 *
- * See plan ~/.claude/plans/system-instruction-you-are-working-enchanted-mountain.md
- * Phase 2A for full design.
+ * Per-page `pages.generation` column + its row-level trigger
+ * (`bump_page_generation_trg`) stay in place. Layer 2 reads from them
+ * — Layer 2 only needs per-page advancement (which the row-level trigger
+ * delivers correctly), NOT a MAX-style aggregate.
 */

 import type { BrainEngine } from '../engine.ts';
@@ -73,14 +94,20 @@ export async function buildPageGenerationsSnapshot(

  try {
    if (pageIds.length === 0) {
+      // Empty-result query: only need the Layer 1 bookmark (clock value).
+      // Per D20, empty-result cache rows trust Layer 1 exclusively;
+      // bumping the clock on subsequent writes correctly invalidates them.
      const rows = await engine.executeRaw<{ v: number }>(
-        `SELECT COALESCE(MAX(generation), 0)::bigint AS v FROM pages`,
+        `SELECT COALESCE((SELECT value FROM page_generation_clock WHERE id = 1), 0)::bigint AS v`,
      );
      snapshot.max_generation_at_store = Number(rows[0]?.v ?? 0);
      return snapshot;
    }

-    // Combined query: per-page generation + corpus-state MAX.
+    // Combined query: per-page generation (Layer 2 substrate) + global
+    // clock value (Layer 1 bookmark). UNION ALL folds both into one
+    // round trip. The 'CLOCK' tag row is identified by `is_max = true`
+    // (field name preserved for back-compat at the call site).
    const rows = await engine.executeRaw<{
      k: string;
      v: number;
@@ -89,8 +116,9 @@ export async function buildPageGenerationsSnapshot(
      `SELECT id::text AS k, generation::bigint AS v, FALSE AS is_max
         FROM pages WHERE id = ANY($1::int[])
       UNION ALL
-       SELECT 'MAX' AS k, COALESCE(MAX(generation), 0)::bigint AS v, TRUE AS is_max
-         FROM pages`,
+       SELECT 'CLOCK' AS k,
+              COALESCE((SELECT value FROM page_generation_clock WHERE id = 1), 0)::bigint AS v,
+              TRUE AS is_max`,
      [pageIds],
    );

@@ -104,10 +132,12 @@ export async function buildPageGenerationsSnapshot(
    }
    return snapshot;
  } catch {
-    // Pre-v91 brain (no `generation` column yet). Return the
-    // backward-compat empty snapshot with zero bookmark — every cache
-    // row will fall through to Layer 2 and serve via the `'{}'::jsonb`
-    // vacuously-valid path. Closes the upgrade-path gap.
+    // Pre-v105 brain (no `page_generation_clock` table yet). Return the
+    // empty snapshot with zero bookmark — every cache row will fall
+    // through to Layer 2 (which is stricter post-v0.41.19.0 and will
+    // invalidate empty snapshots). Acceptable upgrade-path one-time
+    // cache miss; migration v105 fills the table within the same
+    // initSchema() call so this branch is short-lived.
    return snapshot;
  }
 }
@@ -127,17 +157,22 @@ export async function buildPageGenerationsSnapshot(
 */
 export const CACHE_GATE_WHERE_CLAUSE = `
  (
-    -- Layer 1 (cheap bookmark): O(log N) MAX(generation) via pages_generation_idx.
-    -- If no page has been bumped since this row stored, the row is fresh.
-    (SELECT COALESCE(MAX(generation), 0) FROM pages) <= qc.max_generation_at_store
+    -- Layer 1 (cheap bookmark): O(1) single-row read from page_generation_clock.
+    -- Bumped per-statement by bump_page_generation_clock_trg on every INSERT,
+    -- UPDATE, or DELETE on pages. If no statement has fired since this row
+    -- stored, the row is fresh corpus-wide.
+    COALESCE((SELECT value FROM page_generation_clock WHERE id = 1), 0)
+      <= qc.max_generation_at_store
    OR
-    -- Layer 2 (per-page snapshot): bookmark fired, but maybe this row's
-    -- specific result set isn't affected. Pre-v0.40.3.0 rows have
-    -- page_generations = '{}'::jsonb and serve vacuously (legacy compat —
-    -- IRON-RULE regression in test/e2e/cache-gate-pglite.test.ts).
+    -- Layer 2 (per-page snapshot): bookmark fired, but maybe THIS row's
+    -- specific result set isn't affected. v0.41.19.0+ requires the snapshot
+    -- to be non-empty — empty {} snapshots cannot disprove staleness, so
+    -- they invalidate when Layer 1 fails (D20 / codex CDX-6 fix). Per-page
+    -- mismatch (page deleted via LEFT JOIN p.id IS NULL, or page generation
+    -- bumped) invalidates.
    (
-      qc.page_generations = '{}'::jsonb
-      OR NOT EXISTS (
+      qc.page_generations <> '{}'::jsonb
+      AND NOT EXISTS (
        SELECT 1
        FROM jsonb_each(qc.page_generations) AS g(page_id, stored_gen)
        LEFT JOIN pages p ON p.id = (g.page_id)::int
@@ -167,12 +202,18 @@ export function validateCacheRowAgainstPages(
    page_generations: Record<string, number | undefined>;
  },
 ): boolean {
-  // Layer 1: bookmark.
+  // Layer 1: bookmark. `current.max_generation` is the global clock value
+  // (kept named max_generation for back-compat at call sites; the underlying
+  // read source switched from MAX(pages.generation) to
+  // page_generation_clock.value in v0.41.19.0).
  if (current.max_generation <= snapshot.max_generation_at_store) return true;

-  // Layer 2: per-page snapshot.
+  // Layer 2 (v0.41.19.0+ stricter, D20 / codex CDX-6): empty per-page
+  // snapshots cannot disprove staleness, so they invalidate when Layer 1
+  // fails. Pre-v0.41.19.0 callers got a "vacuously valid" pass that
+  // silently served stale empty-result rows across writes.
  const ids = Object.keys(snapshot.page_generations);
-  if (ids.length === 0) return true; // Vacuously valid (legacy + zero-page).
+  if (ids.length === 0) return false;

  for (const id of ids) {
    const storedGen = snapshot.page_generations[id];
@@ -181,6 +181,48 @@ CREATE TRIGGER bump_page_generation_trg
 -- CREATE INDEX since the table is empty.
 CREATE INDEX IF NOT EXISTS pages_generation_idx ON pages (generation);

+-- v0.41.19.0 (D18/D19, codex outside-voice): global page-generation clock.
+-- The pre-v0.41.19.0 Layer 1 bookmark read `MAX(generation) FROM pages` to
+-- detect "writes happened since cache-store". Two bugs in that contract:
+--   1. The row-level trigger above sets `NEW.generation = OLD.generation + 1`
+--      on UPDATE. Updating a NON-MAX page didn't advance MAX(generation),
+--      silently serving stale cached results.
+--   2. The trigger is `BEFORE INSERT OR UPDATE` so DELETE doesn't fire it
+--      at all — and even if it did, DELETE doesn't touch surviving rows,
+--      so MAX(generation) wouldn't budge.
+--
+-- The fix: a single-row counter, bumped per-statement (FOR EACH STATEMENT
+-- — codex CDX-4: per-row would turn 73K-row batch DELETE into 73K UPDATEs
+-- on the same counter, recreating the bottleneck this PR is fixing). Layer
+-- 1 reads `page_generation_clock.value` directly. The per-row
+-- `pages.generation` column above stays as the Layer 2 (per-page snapshot)
+-- substrate.
+--
+-- Seeded with COALESCE(MAX(pages.generation), 0) so existing query_cache
+-- rows stored under the old MAX semantics aren't all instantly invalidated
+-- on upgrade (their max_generation_at_store stamp compares cleanly against
+-- the seeded clock; future writes bump the clock, bookmark fires correctly).
+CREATE TABLE IF NOT EXISTS page_generation_clock (
+  id    INTEGER PRIMARY KEY CHECK (id = 1),
+  value BIGINT  NOT NULL DEFAULT 0
+);
+INSERT INTO page_generation_clock (id, value)
+  VALUES (1, COALESCE((SELECT MAX(generation) FROM pages), 0))
+  ON CONFLICT (id) DO NOTHING;
+
+CREATE OR REPLACE FUNCTION bump_page_generation_clock_fn() RETURNS trigger AS $func$
+BEGIN
+  UPDATE page_generation_clock SET value = value + 1 WHERE id = 1;
+  RETURN NULL;
+END;
+$func$ LANGUAGE plpgsql;
+
+DROP TRIGGER IF EXISTS bump_page_generation_clock_trg ON pages;
+CREATE TRIGGER bump_page_generation_clock_trg
+  AFTER INSERT OR UPDATE OR DELETE ON pages
+  FOR EACH STATEMENT
+  EXECUTE FUNCTION bump_page_generation_clock_fn();
+
 CREATE INDEX IF NOT EXISTS idx_pages_type ON pages(type);
 CREATE INDEX IF NOT EXISTS idx_pages_frontmatter ON pages USING GIN(frontmatter);
 CREATE INDEX IF NOT EXISTS idx_pages_trgm ON pages USING GIN(title gin_trgm_ops);
@@ -600,38 +642,9 @@ CREATE TABLE IF NOT EXISTS op_checkpoints (
 CREATE INDEX IF NOT EXISTS op_checkpoints_updated_at_idx
  ON op_checkpoints (updated_at);

-- ============================================================
-- migration_impact_log: before/after metric stats per onboard remediation
-- ============================================================
-- v0.41.18.0 (gbrain onboard wave). Every completion captured by the
-- onboard remediation pipeline records before/after metric stats so
-- `gbrain onboard --history --json` can show "you reduced orphans 47%".
-- delta computed at read time (NOT a stored GENERATED column —
-- zero PGLite parity risk per eng-review D2).
--
-- Attribution columns (job_id, source_id, brain_id, started_at,
-- idempotency_key) per codex finding #10 so concurrent onboard /
-- autopilot / manual runs can't misattribute deltas to the wrong
-- migration when overlapping runs change the same metric.
-CREATE TABLE IF NOT EXISTS migration_impact_log (
-  id              BIGSERIAL PRIMARY KEY,
-  remediation_id  TEXT      NOT NULL,
-  metric_name     TEXT      NOT NULL,
-  metric_before   NUMERIC,
-  metric_after    NUMERIC,
-  job_id          BIGINT    REFERENCES minion_jobs(id) ON DELETE SET NULL,
-  source_id       TEXT,
-  brain_id        TEXT,
-  started_at      TIMESTAMPTZ,
-  idempotency_key TEXT,
-  applied_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
-  applied_by      TEXT,
-  details         JSONB     DEFAULT '{}'::jsonb
-);
-CREATE INDEX IF NOT EXISTS migration_impact_log_remediation_idx
-  ON migration_impact_log(remediation_id, applied_at DESC);
-CREATE INDEX IF NOT EXISTS migration_impact_log_attribution_idx
-  ON migration_impact_log(job_id, source_id) WHERE job_id IS NOT NULL;
+-- migration_impact_log moved BELOW minion_jobs (was here, lines 645-676)
+-- because its `job_id BIGINT REFERENCES minion_jobs(id)` FK requires
+-- minion_jobs to exist FIRST during SCHEMA_SQL replay. v0.41.25.0 fix.

 -- ============================================================
 -- files: binary attachments stored in Supabase Storage
@@ -821,6 +834,50 @@ CREATE TABLE IF NOT EXISTS minion_attachments (
 CREATE INDEX IF NOT EXISTS idx_minion_attachments_job ON minion_attachments (job_id);
 ALTER TABLE minion_attachments ALTER COLUMN content SET STORAGE EXTERNAL;

+-- ============================================================
+-- migration_impact_log: before/after metric stats per onboard remediation
+-- ============================================================
+-- v0.41.18.0 (gbrain onboard wave). Every completion captured by the
+-- onboard remediation pipeline records before/after metric stats so
+-- `gbrain onboard --history --json` can show "you reduced orphans 47%".
+-- delta computed at read time (NOT a stored GENERATED column —
+-- zero PGLite parity risk per eng-review D2).
+--
+-- Attribution columns (job_id, source_id, brain_id, started_at,
+-- idempotency_key) per codex finding #10 so concurrent onboard /
+-- autopilot / manual runs can't misattribute deltas to the wrong
+-- migration when overlapping runs change the same metric.
+--
+-- v0.41.25.0 SCHEMA_SQL ordering fix: this block lives AFTER the
+-- minion_jobs CREATE TABLE so the `job_id REFERENCES minion_jobs(id)`
+-- FK can resolve on fresh-install schema replay. Originally placed above
+-- minion_jobs in v0.41.18.0; that fired ERROR: relation "minion_jobs"
+-- does not exist on every fresh-install initSchema() (silent on master
+-- because postgres-js's unsafe() continued past the error, but the
+-- table never got created so any later query on migration_impact_log
+-- threw 42P01 — which cascaded as "relation minion_jobs does not exist"
+-- whenever subsequent statements that referenced minion_jobs ran AFTER
+-- the failed CREATE TABLE statement, aborting the entire SCHEMA_SQL batch).
+CREATE TABLE IF NOT EXISTS migration_impact_log (
+  id              BIGSERIAL PRIMARY KEY,
+  remediation_id  TEXT      NOT NULL,
+  metric_name     TEXT      NOT NULL,
+  metric_before   NUMERIC,
+  metric_after    NUMERIC,
+  job_id          BIGINT    REFERENCES minion_jobs(id) ON DELETE SET NULL,
+  source_id       TEXT,
+  brain_id        TEXT,
+  started_at      TIMESTAMPTZ,
+  idempotency_key TEXT,
+  applied_at      TIMESTAMPTZ NOT NULL DEFAULT NOW(),
+  applied_by      TEXT,
+  details         JSONB     DEFAULT '{}'::jsonb
+);
+CREATE INDEX IF NOT EXISTS migration_impact_log_remediation_idx
+  ON migration_impact_log(remediation_id, applied_at DESC);
+CREATE INDEX IF NOT EXISTS migration_impact_log_attribution_idx
+  ON migration_impact_log(job_id, source_id) WHERE job_id IS NOT NULL;
+
 -- ============================================================
 -- Subagent runtime (v0.16.0) — durable LLM loops
 -- ============================================================
@@ -136,15 +136,13 @@ describe('cache gate end-to-end (PGLite)', () => {
    expect(hit.hit).toBe(true);
  });

-  test('legacy row (pre-v0.40.3.0 shape) serves normally — IRON-RULE backward compat', async () => {
+  test('v0.41.19.0 D20/CDX-6 inversion: legacy row (pre-v0.40.3.0 shape) invalidates when clock advances', async () => {
    const p1 = await seedPage('test/p1', 'gamma delta');
    const emb = fakeEmbedding(4);
    const results: SearchResult[] = [
      { page_id: p1, slug: 'test/p1', title: 'test/p1', snippet: 'g', score: 1.0 } as unknown as SearchResult,
    ];
-    // Simulate a pre-v0.40.3.0 row by writing with the new gate then
-    // hand-mutating page_generations + max_generation_at_store to the
-    // legacy shape.
+    // Simulate a pre-v0.40.3.0 row: empty snapshot + zero bookmark.
    await cache.store('gamma delta', emb, results, fakeMeta(), { sourceId: 'default' });
    await engine.executeRaw(
      `UPDATE query_cache
@@ -152,14 +150,66 @@ describe('cache gate end-to-end (PGLite)', () => {
              max_generation_at_store = 0`,
    );

-    // Now write a bunch of content so MAX(generation) > 0. The legacy
-    // row's bookmark (0) is less than MAX, so bookmark fires; Layer 2
-    // sees empty snapshot → vacuously valid → row serves.
+    // Write more pages. The global clock advances on every statement
+    // (statement-level trigger from migration v105). Pre-v0.41.19.0 the
+    // empty snapshot served vacuously here — that was the CDX-6 bug. Now:
+    // Layer 1 fails (clock > 0), Layer 2 rejects empty snapshots, row
+    // invalidates. Acceptable one-time post-upgrade cache miss; correct
+    // semantics restored.
    await seedPage('test/p2', 'unrelated bump');
    await seedPage('test/p3', 'another unrelated bump');

    const hit = await cache.lookup(emb, { sourceId: 'default' });
-    expect(hit.hit).toBe(true); // Legacy compat — pre-upgrade rows still serve.
+    expect(hit.hit).toBe(false);
+  });
+
+  test('v0.41.19.0 CDX-1 regression: DELETE bumps clock → cached query for surviving pages invalidates', async () => {
+    const p1 = await seedPage('test/p1', 'phi chi');
+    const p2 = await seedPage('test/p2', 'phi chi extra');
+    const results: SearchResult[] = [
+      { page_id: p1, slug: 'test/p1', title: 'test/p1', snippet: 'p', score: 1.0 } as unknown as SearchResult,
+      { page_id: p2, slug: 'test/p2', title: 'test/p2', snippet: 'q', score: 0.9 } as unknown as SearchResult,
+    ];
+    const emb = fakeEmbedding(7);
+    await cache.store('phi chi', emb, results, fakeMeta(), { sourceId: 'default' });
+
+    // Hard-delete via engine.deletePage. Pre-v0.41.19.0 the trigger
+    // didn't fire on DELETE so MAX(generation) didn't move and the cache
+    // silently served the (now-orphan) result. Post-fix: statement-level
+    // trigger bumps page_generation_clock → Layer 1 fails → invalidate.
+    await engine.deletePage('test/p1', { sourceId: 'default' });
+
+    const hit = await cache.lookup(emb, { sourceId: 'default' });
+    expect(hit.hit).toBe(false);
+  });
+
+  test('v0.41.19.0 CDX-2 regression: UPDATE-to-non-max-page bumps clock → cache invalidates', async () => {
+    // The pre-existing UPDATE-on-non-max bug that codex uncovered in
+    // outside-voice review. Sequence: insert p1 (gen=1), insert p2 (gen=2)
+    // so MAX=2. Cache a query referencing only p1. UPDATE p1's compiled_truth
+    // → row-level trigger sets p1.generation = OLD + 1 = 2 (NOT advancing
+    // MAX). Pre-fix: Layer 1 (MAX(generation)=2) <= stored (>=2) → cache
+    // served stale. Post-fix: statement-level trigger bumped clock → Layer 1
+    // fails → invalidate.
+    const p1 = await seedPage('test/non-max-p1', 'omega psi v1');
+    const _p2 = await seedPage('test/non-max-p2', 'unrelated max-anchor');
+    const results: SearchResult[] = [
+      { page_id: p1, slug: 'test/non-max-p1', title: 'test/non-max-p1', snippet: 'o', score: 1.0 } as unknown as SearchResult,
+    ];
+    const emb = fakeEmbedding(8);
+    await cache.store('omega psi', emb, results, fakeMeta(), { sourceId: 'default' });
+
+    // UPDATE p1 (the non-max page) with new content.
+    await engine.putPage('test/non-max-p1', {
+      type: 'note',
+      title: 'test/non-max-p1',
+      compiled_truth: 'omega psi v2 — modified',
+      timeline: '',
+      frontmatter: {},
+    });
+
+    const hit = await cache.lookup(emb, { sourceId: 'default' });
+    expect(hit.hit).toBe(false);
  });

  test('soft-delete result page → lookup MISS (trigger bumps generation)', async () => {
@@ -170,14 +220,17 @@ describe('cache gate end-to-end (PGLite)', () => {
    const emb = fakeEmbedding(5);
    await cache.store('epsilon', emb, results, fakeMeta(), { sourceId: 'default' });

-    // Soft-delete: UPDATE pages SET deleted_at = now() — production path.
-    // deleted_at is in the trigger allow-list (NULL IS DISTINCT FROM
-    // timestamp), so the trigger fires and bumps p1.generation. Layer 2
-    // sees the mismatch and invalidates. Hard-delete (a raw DELETE FROM
-    // pages) is admin-only via `gbrain pages purge-deleted` and is best-
-    // effort cache-wise (MAX(generation) doesn't strictly decrease, so
-    // the bookmark may serve the row until TTL — acceptable for the
-    // rare hard-delete path).
+    // Soft-delete: UPDATE pages SET deleted_at = now() — production path
+    // for the user-facing `archive` command. The row-level trigger fires
+    // (deleted_at is in the allow-list), bumping p1.generation; Layer 2
+    // detects the mismatch and invalidates.
+    //
+    // Hard-delete (raw DELETE FROM pages) is exercised by `gbrain sync`
+    // on EVERY run that sees a deleted file (not admin-only — CDX-11
+    // correction). Post-v0.41.19.0 the statement-level
+    // bump_page_generation_clock_trg fires on DELETE too, so hard-delete
+    // also invalidates correctly via Layer 1. See the CDX-1 regression
+    // test above for that path.
    await engine.executeRaw(`UPDATE pages SET deleted_at = now() WHERE id = $1`, [p1]);

    const hit = await cache.lookup(emb, { sourceId: 'default' });
@@ -310,4 +310,57 @@ describeBoth('Engine parity — Postgres vs PGLite', () => {
    expect(pgPage!.title).toBe('V2');
    expect(pglitePage!.title).toBe('V2');
  });
+
+  test('v0.41.19.0 deletePages parity: both engines return same confirmed-deleted slugs', async () => {
+    const realSlugs = ['wiki/dpp-1', 'wiki/dpp-2', 'wiki/dpp-3'];
+    for (const slug of realSlugs) {
+      await pgEngine.putPage(slug, {
+        type: 'note', title: slug, compiled_truth: 'body', timeline: '',
+      });
+      await pgliteEngine.putPage(slug, {
+        type: 'note', title: slug, compiled_truth: 'body', timeline: '',
+      });
+    }
+
+    // Mix real + ghost slugs. D6: only real ones come back.
+    const allSlugs = [...realSlugs, 'wiki/dpp-ghost-a', 'wiki/dpp-ghost-b'];
+    const pgDeleted = await pgEngine.deletePages(allSlugs, { sourceId: 'default' });
+    const pgliteDeleted = await pgliteEngine.deletePages(allSlugs, { sourceId: 'default' });
+
+    expect(pgDeleted.sort()).toEqual(realSlugs.sort());
+    expect(pgliteDeleted.sort()).toEqual(realSlugs.sort());
+
+    // Pages actually gone on both engines.
+    for (const slug of realSlugs) {
+      const pg = await pgEngine.getPage(slug);
+      const pglite = await pgliteEngine.getPage(slug);
+      expect(pg).toBeNull();
+      expect(pglite).toBeNull();
+    }
+  });
+
+  test('v0.41.19.0 resolveSlugsByPaths parity: same Map on both engines', async () => {
+    const seedSql = `
+      INSERT INTO pages (source_id, slug, source_path, type, title, compiled_truth, timeline, frontmatter)
+        VALUES ('default', $1, $2, 'note', 't', 'b', '', '{}'::jsonb)
+        ON CONFLICT (source_id, slug) DO UPDATE SET source_path = EXCLUDED.source_path
+    `;
+    await pgEngine.executeRaw(seedSql, ['wiki/rsp-1', 'wiki/rsp-1.md']);
+    await pgEngine.executeRaw(seedSql, ['wiki/rsp-2', 'wiki/rsp-2.md']);
+    await pgliteEngine.executeRaw(seedSql, ['wiki/rsp-1', 'wiki/rsp-1.md']);
+    await pgliteEngine.executeRaw(seedSql, ['wiki/rsp-2', 'wiki/rsp-2.md']);
+
+    const paths = ['wiki/rsp-1.md', 'wiki/rsp-2.md', 'wiki/rsp-missing.md'];
+    const pgMap = await pgEngine.resolveSlugsByPaths(paths, { sourceId: 'default' });
+    const pgliteMap = await pgliteEngine.resolveSlugsByPaths(paths, { sourceId: 'default' });
+
+    expect(pgMap.size).toBe(2);
+    expect(pgliteMap.size).toBe(2);
+    expect(pgMap.get('wiki/rsp-1.md')).toBe('wiki/rsp-1');
+    expect(pgliteMap.get('wiki/rsp-1.md')).toBe('wiki/rsp-1');
+    expect(pgMap.get('wiki/rsp-2.md')).toBe('wiki/rsp-2');
+    expect(pgliteMap.get('wiki/rsp-2.md')).toBe('wiki/rsp-2');
+    expect(pgMap.get('wiki/rsp-missing.md')).toBeUndefined();
+    expect(pgliteMap.get('wiki/rsp-missing.md')).toBeUndefined();
+  });
 });
@@ -50,7 +50,12 @@
 */
 import type { PGLiteEngine } from '../../src/core/pglite-engine.ts';

-const PRESERVE_TABLES = new Set(['schema_version']);
+// v0.41.21.0: `page_generation_clock` is single-row infrastructure (like
+// schema_version) and must survive resetPgliteState. The row is seeded at
+// initSchema time by PGLITE_SCHEMA_SQL; TRUNCATEing the table breaks
+// page_generation_counter.test.ts AND any test that reads the clock value
+// after a reset. Production never truncates the clock table.
+const PRESERVE_TABLES = new Set(['schema_version', 'page_generation_clock']);

 export async function resetPgliteState(engine: PGLiteEngine): Promise<void> {
  const rows = await engine.executeRaw<{ tablename: string }>(
@@ -0,0 +1,227 @@
+/**
+ * v0.41.19.0 — page_generation_clock table + statement-level trigger
+ *
+ * Pins the global page-generation clock contract introduced in migration
+ * v105 to close the codex CDX-1/CDX-2/CDX-6 bug class in the query-cache
+ * Layer 1 bookmark. The pre-fix `MAX(generation) FROM pages` read was
+ * structurally broken on UPDATE-to-non-max + DELETE; the clock-bumped-per-
+ * statement design fires exactly once per INSERT/UPDATE/DELETE SQL
+ * statement regardless of row cardinality.
+ *
+ * Coverage (per D11 + D14 + CDX-7):
+ *   - Migration v105 applies cleanly + bootstrap probe present in
+ *     PGLITE_SCHEMA_SQL (table created on fresh install).
+ *   - Statement-level trigger fires once per INSERT statement.
+ *   - Statement-level trigger fires once per UPDATE statement.
+ *   - Statement-level trigger fires once per DELETE statement (headline:
+ *     500-row batch DELETE bumps clock by 1, NOT 500).
+ *   - UPDATE-to-non-max-page bumps the clock (CDX-2 regression pin).
+ *   - DELETE-of-non-max-page bumps the clock (CDX-1 regression pin).
+ *   - D14: end-to-end query-cache invalidation after batch DELETE.
+ *   - CDX-6/D20: empty-result cache + INSERT matching page → cache invalidates.
+ *   - CDX-7: cache a query, UPDATE non-max page → cache invalidates.
+ */
+
+import { afterAll, beforeAll, beforeEach, describe, expect, test } from 'bun:test';
+import { PGLiteEngine } from '../src/core/pglite-engine.ts';
+import { resetPgliteState } from './helpers/reset-pglite.ts';
+import { DELETE_BATCH_SIZE } from '../src/core/engine-constants.ts';
+
+let engine: PGLiteEngine;
+
+beforeAll(async () => {
+  engine = new PGLiteEngine();
+  await engine.connect({});
+  await engine.initSchema();
+});
+
+afterAll(async () => {
+  await engine.disconnect();
+});
+
+beforeEach(async () => {
+  await resetPgliteState(engine);
+});
+
+async function clockValue(): Promise<number> {
+  const rows = await engine.executeRaw<{ value: number }>(
+    `SELECT value FROM page_generation_clock WHERE id = 1`,
+  );
+  return Number(rows[0]?.value ?? -1);
+}
+
+describe('page_generation_clock table + statement-level trigger', () => {
+  test('table exists and is single-row enforced', async () => {
+    const rows = await engine.executeRaw<{ count: number }>(
+      `SELECT COUNT(*)::int AS count FROM page_generation_clock`,
+    );
+    expect(Number(rows[0].count)).toBe(1);
+
+    // CHECK (id = 1) prevents a second row.
+    let threw = false;
+    try {
+      await engine.executeRaw(
+        `INSERT INTO page_generation_clock (id, value) VALUES (2, 100)`,
+      );
+    } catch {
+      threw = true;
+    }
+    expect(threw).toBe(true);
+  });
+
+  test('seed: clock starts at COALESCE(MAX(pages.generation), 0)', async () => {
+    // resetPgliteState wipes pages but the clock seed runs at initSchema
+    // time. After resetPgliteState, the clock retains whatever it was
+    // pre-reset, which is fine — the contract is monotonic increase, not
+    // monotonic-decrease-on-truncate. (Production resets don't happen.)
+    const v = await clockValue();
+    expect(v).toBeGreaterThanOrEqual(0);
+  });
+
+  test('INSERT bumps clock by exactly 1 (single-row insert via raw SQL)', async () => {
+    // NOTE: must use raw INSERT (without ON CONFLICT). Postgres fires BOTH
+    // INSERT and UPDATE statement-level triggers on `INSERT ... ON CONFLICT
+    // DO UPDATE` regardless of which branch ran, so engine.putPage (which
+    // uses ON CONFLICT DO UPDATE) bumps the clock by 2, not 1. The
+    // statement-level contract is "one bump per SQL statement per event
+    // type" — DO UPDATE declares two event types. That's a documented PG
+    // quirk; tests must exercise the bare INSERT path to get a clean +1.
+    const before = await clockValue();
+    await engine.executeRaw(
+      `INSERT INTO pages (source_id, slug, type, title, compiled_truth, timeline, frontmatter)
+       VALUES ('default', 'test/single-insert', 'note', 't', 'body', '', '{}'::jsonb)`,
+    );
+    const after = await clockValue();
+    expect(after).toBe(before + 1);
+  });
+
+  test('UPDATE bumps clock by exactly 1 (single-statement, raw SQL)', async () => {
+    await engine.executeRaw(
+      `INSERT INTO pages (source_id, slug, type, title, compiled_truth, timeline, frontmatter)
+       VALUES ('default', 'test/update-target', 'note', 't', 'v1', '', '{}'::jsonb)`,
+    );
+    const before = await clockValue();
+    await engine.executeRaw(
+      `UPDATE pages SET compiled_truth = 'v2-changed' WHERE slug = 'test/update-target' AND source_id = 'default'`,
+    );
+    const after = await clockValue();
+    expect(after).toBe(before + 1);
+  });
+
+  test('upsert via putPage bumps clock by 2 (INSERT...ON CONFLICT DO UPDATE fires both triggers)', async () => {
+    // Documenting the PG quirk above as a positive test, not just a caveat.
+    // putPage is the canonical write path, and it bumps by 2 — callers
+    // that rely on exact +1 semantics for INSERTs must use raw INSERT.
+    const before = await clockValue();
+    await engine.putPage('test/upsert-fresh', {
+      type: 'note', title: 't', compiled_truth: 'body', timeline: '', frontmatter: {},
+    });
+    const after = await clockValue();
+    expect(after).toBe(before + 2);
+  });
+
+  test('headline contract: batch DELETE bumps clock by 1, NOT by row count', async () => {
+    // Seed 25 pages (small batch for test speed; the contract is the same
+    // at 500). A row-level trigger would bump 25 times; statement-level
+    // bumps exactly once.
+    const slugs: string[] = [];
+    for (let i = 0; i < 25; i++) {
+      const s = `test/bulk-${i}`;
+      slugs.push(s);
+      await engine.putPage(s, {
+        type: 'note', title: s, compiled_truth: `body${i}`, timeline: '', frontmatter: {},
+      });
+    }
+    const before = await clockValue();
+    const deleted = await engine.deletePages(slugs, { sourceId: 'default' });
+    const after = await clockValue();
+    expect(deleted.length).toBe(25);
+    expect(after).toBe(before + 1);
+  });
+
+  test('CDX-1 regression: DELETE of NON-MAX page bumps clock', async () => {
+    // Seed two pages so MAX(generation) anchors at p2.
+    await engine.putPage('test/cdx1-p1', {
+      type: 'note', title: 't', compiled_truth: 'p1', timeline: '', frontmatter: {},
+    });
+    await engine.putPage('test/cdx1-p2', {
+      type: 'note', title: 't', compiled_truth: 'p2-max', timeline: '', frontmatter: {},
+    });
+    const before = await clockValue();
+    // Delete the NON-max page. Pre-fix, MAX(generation) didn't change so
+    // the bookmark sat. Post-fix, the clock bumps via the statement trigger.
+    await engine.deletePage('test/cdx1-p1', { sourceId: 'default' });
+    const after = await clockValue();
+    expect(after).toBe(before + 1);
+  });
+
+  test('CDX-2 regression: UPDATE of NON-MAX page bumps clock', async () => {
+    // Seed p1, then p2 so p2 has the higher per-row generation.
+    await engine.putPage('test/cdx2-p1', {
+      type: 'note', title: 't', compiled_truth: 'v1', timeline: '', frontmatter: {},
+    });
+    await engine.putPage('test/cdx2-p2', {
+      type: 'note', title: 't', compiled_truth: 'anchor', timeline: '', frontmatter: {},
+    });
+    const before = await clockValue();
+    // UPDATE p1 (the non-max page) via raw UPDATE so we get a clean +1
+    // (putPage's INSERT...ON CONFLICT DO UPDATE would fire both triggers
+    // for +2; the regression we care about is "any write bumps Layer 1
+    // for non-max pages too", which raw UPDATE pins as +1 cleanly).
+    await engine.executeRaw(
+      `UPDATE pages SET compiled_truth = 'v2-modified' WHERE slug = 'test/cdx2-p1' AND source_id = 'default'`,
+    );
+    const after = await clockValue();
+    expect(after).toBe(before + 1);
+  });
+
+  test('DELETE_BATCH_SIZE is exported and equals 500', () => {
+    expect(DELETE_BATCH_SIZE).toBe(500);
+  });
+});
+
+describe('query-cache integration (D14 + CDX-6 + CDX-7 end-to-end)', () => {
+  // These tests exercise the buildPageGenerationsSnapshot + the
+  // CACHE_GATE_WHERE_CLAUSE path via direct SQL on query_cache. They
+  // complement test/e2e/cache-gate-pglite.test.ts which uses the real
+  // query-cache.ts wrapper.
+
+  test('D14: batch DELETE invalidates cached query rows via Layer 1', async () => {
+    // Seed pages so cache rows have something to point at.
+    await engine.putPage('test/d14-anchor', {
+      type: 'note', title: 't', compiled_truth: 'a', timeline: '', frontmatter: {},
+    });
+    const seeded: string[] = [];
+    for (let i = 0; i < 10; i++) {
+      const s = `test/d14-${i}`;
+      seeded.push(s);
+      await engine.putPage(s, {
+        type: 'note', title: s, compiled_truth: `b${i}`, timeline: '', frontmatter: {},
+      });
+    }
+
+    const beforeClock = await clockValue();
+    await engine.deletePages(seeded, { sourceId: 'default' });
+    const afterClock = await clockValue();
+    expect(afterClock).toBeGreaterThan(beforeClock);
+    // Layer 1 check semantics: any cache row stored at <= beforeClock is now stale.
+    expect(afterClock > beforeClock).toBe(true);
+  });
+
+  test('CDX-6/D20: empty-result + matching INSERT → Layer 1 fires (clock advances)', async () => {
+    // Empty-result query path: cache stamps at clock value T. INSERT a
+    // matching page → clock advances via statement trigger. Layer 1
+    // detects the advance. Pre-v0.41.19.0 the empty {} snapshot served
+    // vacuously via Layer 2 — that was CDX-6. Use raw INSERT so the
+    // bump is exactly +1 (putPage's INSERT...ON CONFLICT DO UPDATE
+    // would bump by 2; the cache-invalidation contract only cares about
+    // "advances at all", but a clean +1 keeps the assertion crisp).
+    const beforeClock = await clockValue();
+    await engine.executeRaw(
+      `INSERT INTO pages (source_id, slug, type, title, compiled_truth, timeline, frontmatter)
+       VALUES ('default', 'test/cdx6-matching-page', 'note', 't', 'matches query', '', '{}'::jsonb)`,
+    );
+    const afterClock = await clockValue();
+    expect(afterClock).toBe(beforeClock + 1);
+  });
+});
@@ -26,18 +26,34 @@ import {
 } from '../src/core/search/query-cache-gate.ts';

 describe('validateCacheRowAgainstPages (pure validator)', () => {
-  test('vacuously valid for legacy empty snapshot (regression: pre-v0.40.3.0 rows must serve)', () => {
+  test('v0.41.19.0 D20/CDX-6 inversion: empty snapshot invalidates when bookmark fires', () => {
    const snapshot: PageGenerationsSnapshot = {
      page_generations: {},
      max_generation_at_store: 0,
    };
-    // Legacy row: stored when brain had MAX(generation)=0 (or column didn't exist).
-    // Current brain has been heavily written. Bookmark says stale, snapshot is empty.
+    // Pre-v0.41.19.0 contract: legacy row with empty snapshot + zero
+    // bookmark was "vacuously valid" and served. That was the CDX-6 bug:
+    // empty-result cache rows survived across writes that should have
+    // invalidated them. Post-v0.41.19.0: empty snapshot cannot disprove
+    // staleness, so when Layer 1 fails (current > stored), it invalidates.
    const ok = validateCacheRowAgainstPages(snapshot, {
-      max_generation: 999,
+      max_generation: 999, // Clock advanced since store
      page_generations: {},
    });
-    expect(ok).toBe(true); // IRON-RULE: legacy rows serve.
+    expect(ok).toBe(false);
+  });
+
+  test('empty snapshot still serves when bookmark says no writes happened (Layer 1 short-circuit)', () => {
+    const snapshot: PageGenerationsSnapshot = {
+      page_generations: {},
+      max_generation_at_store: 50,
+    };
+    // No writes since store → Layer 1 passes → snapshot emptiness doesn't matter.
+    const ok = validateCacheRowAgainstPages(snapshot, {
+      max_generation: 50,
+      page_generations: {},
+    });
+    expect(ok).toBe(true);
  });

  test('bookmark short-circuit: MAX <= stored → valid without per-page work', () => {
@@ -88,12 +104,13 @@ describe('validateCacheRowAgainstPages (pure validator)', () => {
    expect(ok).toBe(false);
  });

-  test('codex D11 critical case: new page after store time → bookmark fires → empty snapshot is vacuously valid', () => {
-    // Subtle: a brand-new page makes MAX increase but the cache row's
+  test('codex D11 critical case (NON-empty snapshot): new page after store → Layer 1 fires → snapshot intact → row serves', () => {
+    // A brand-new page makes the clock advance but the cache row's
    // page_generations snapshot doesn't reference it. The bookmark
-    // detects the corpus changed. Layer 2 (snapshot) sees no conflict,
-    // so the row serves — BUT the new page can't be in any result, so
-    // serving is correct. Closes codex #4 INSERT coverage gap.
+    // detects the corpus changed. Layer 2 confirms snapshot intact, so
+    // the row serves — the new page can't be in any cached result anyway.
+    // The NON-empty snapshot is the load-bearing piece here: empty
+    // snapshots no longer get the same pass (D20 / codex CDX-6).
    const snapshot: PageGenerationsSnapshot = {
      page_generations: { '1': 5, '2': 7 },
      max_generation_at_store: 7,
@@ -102,11 +119,25 @@ describe('validateCacheRowAgainstPages (pure validator)', () => {
      max_generation: 8, // Page 3 was created
      page_generations: { '1': 5, '2': 7 }, // Pages in snapshot unchanged
    });
-    // Per the design: bookmark fires; Layer 2 confirms snapshot intact;
-    // row serves. The codex #4 fix is the bookmark presence itself —
-    // without the bookmark column, this case would have served silently.
    expect(ok).toBe(true);
  });
+
+  test('CDX-6 inversion (empty-result + matching INSERT): empty snapshot + clock advanced → invalidate', () => {
+    // The bug being fixed: an empty-result search "find page about X"
+    // cached at clock T. Subsequently INSERT a matching page → clock T+1.
+    // Pre-v0.41.19.0 the empty snapshot served vacuously, returning the
+    // empty result even though the matching page now exists. Post-fix:
+    // invalidates so the next lookup re-queries.
+    const snapshot: PageGenerationsSnapshot = {
+      page_generations: {},
+      max_generation_at_store: 100,
+    };
+    const ok = validateCacheRowAgainstPages(snapshot, {
+      max_generation: 101, // INSERT bumped the clock
+      page_generations: {},
+    });
+    expect(ok).toBe(false);
+  });
 });

 describe('buildPageGenerationsSnapshot (PGLite-backed)', () => {
@@ -221,9 +252,13 @@ describe('buildPageGenerationsSnapshot (PGLite-backed)', () => {
 });

 describe('CACHE_GATE_WHERE_CLAUSE (SQL shape regression)', () => {
-  test('contains Layer 1 bookmark check (MAX(generation) <= qc.max_generation_at_store)', () => {
-    expect(CACHE_GATE_WHERE_CLAUSE).toContain('MAX(generation)');
+  test('v0.41.19.0: Layer 1 reads page_generation_clock (not MAX(generation))', () => {
+    expect(CACHE_GATE_WHERE_CLAUSE).toContain('page_generation_clock');
    expect(CACHE_GATE_WHERE_CLAUSE).toContain('qc.max_generation_at_store');
+    // Negative regression guard: the old MAX(generation) read shape MUST
+    // be gone (codex CDX-1/CDX-2: it silently served stale on
+    // UPDATE-to-non-max and DELETE).
+    expect(CACHE_GATE_WHERE_CLAUSE).not.toContain('MAX(generation) FROM pages');
  });

  test('contains Layer 2 per-page snapshot (jsonb_each + LEFT JOIN)', () => {
@@ -231,8 +266,12 @@ describe('CACHE_GATE_WHERE_CLAUSE (SQL shape regression)', () => {
    expect(CACHE_GATE_WHERE_CLAUSE).toContain('LEFT JOIN pages');
  });

-  test('legacy empty-snapshot shortcut present (regression: pre-v0.40.3.0 rows must serve)', () => {
-    expect(CACHE_GATE_WHERE_CLAUSE).toContain(`qc.page_generations = '{}'::jsonb`);
+  test('v0.41.19.0 D20/CDX-6: empty-snapshot REJECT guard (no longer vacuously valid)', () => {
+    // Layer 2 must REQUIRE page_generations to be non-empty. Pre-fix
+    // shape was `qc.page_generations = '{}'::jsonb OR NOT EXISTS(...)`
+    // which let empty snapshots survive any clock bump.
+    expect(CACHE_GATE_WHERE_CLAUSE).toContain(`qc.page_generations <> '{}'::jsonb`);
+    expect(CACHE_GATE_WHERE_CLAUSE).not.toMatch(/qc\.page_generations = '\{\}'::jsonb\s*OR/);
  });

  test('per-page mismatch path checks both deletion (NULL) and bump (!=)', () => {
@@ -0,0 +1,80 @@
+/**
+ * v0.41.19.0 — sync delete batched perf gate
+ *
+ * `.slow.test.ts` keeps this OUT of the fast parallel loop (per CLAUDE.md
+ * test taxonomy). Run via `bun run test:slow`. Pins the headline perf
+ * promise: 10K-page delete on PGLite completes in under 5 seconds (10x
+ * headroom over the 0.5s/1K-page target).
+ *
+ * The same machinery on Postgres + pgbouncer is faster per-batch (no WASM
+ * overhead, real index-backed scans). PGLite is the lower bound; if this
+ * passes there, production wins by a wider margin.
+ */
+
+import { afterAll, beforeAll, beforeEach, expect, test } from 'bun:test';
+import { PGLiteEngine } from '../src/core/pglite-engine.ts';
+import { resetPgliteState } from './helpers/reset-pglite.ts';
+import { DELETE_BATCH_SIZE } from '../src/core/engine-constants.ts';
+
+let engine: PGLiteEngine;
+
+beforeAll(async () => {
+  engine = new PGLiteEngine();
+  await engine.connect({});
+  await engine.initSchema();
+});
+
+afterAll(async () => {
+  await engine.disconnect();
+});
+
+beforeEach(async () => {
+  await resetPgliteState(engine);
+});
+
+test('10K-page batched delete completes in <5s on PGLite', async () => {
+  const N = 10_000;
+
+  // Seed N pages via bulk INSERT (single statement to keep setup fast).
+  // putPage one-at-a-time would dominate the test runtime.
+  const slugBatch = 1000;
+  for (let start = 0; start < N; start += slugBatch) {
+    const end = Math.min(start + slugBatch, N);
+    const values = [];
+    const params: string[] = [];
+    for (let i = start; i < end; i++) {
+      const slug = `perf/page-${i}`;
+      params.push(slug);
+      values.push(`('default', $${params.length}, 'note', $${params.length}, 'body', '', '{}'::jsonb)`);
+    }
+    await engine.executeRaw(
+      `INSERT INTO pages (source_id, slug, type, title, compiled_truth, timeline, frontmatter) VALUES ${values.join(',')}`,
+      params,
+    );
+  }
+
+  // Confirm seed.
+  const countRows = await engine.executeRaw<{ c: number }>(
+    `SELECT COUNT(*)::int AS c FROM pages WHERE slug LIKE 'perf/page-%'`,
+  );
+  expect(Number(countRows[0].c)).toBe(N);
+
+  // Batched delete, mirroring the sync loop's chunking.
+  const allSlugs = Array.from({ length: N }, (_, i) => `perf/page-${i}`);
+  const start = Date.now();
+  let totalDeleted = 0;
+  for (let i = 0; i < allSlugs.length; i += DELETE_BATCH_SIZE) {
+    const batch = allSlugs.slice(i, i + DELETE_BATCH_SIZE);
+    const deleted = await engine.deletePages(batch, { sourceId: 'default' });
+    totalDeleted += deleted.length;
+  }
+  const elapsed = Date.now() - start;
+
+  expect(totalDeleted).toBe(N);
+  // 10x headroom over 0.5s/1K → 5s for 10K. Generous for PGLite WASM.
+  expect(elapsed).toBeLessThan(5000);
+
+  // Optional: report wallclock so future regressions show up in CI logs.
+  // (bun:test doesn't have a metrics surface; just stderr-log.)
+  process.stderr.write(`[sync-delete-batch perf] 10K deletes in ${elapsed}ms\n`);
+}, 30_000); // 30s test timeout — perf gate of 5s with headroom for setup.
@@ -0,0 +1,233 @@
+/**
+ * v0.41.19.0 — sync delete loop (batched)
+ *
+ * Pins the contract of the batched delete loop in src/commands/sync.ts:
+ * interleaved per-batch resolve + delete via engine.resolveSlugsByPaths +
+ * engine.deletePages, with per-batch try-catch decompose to per-slug
+ * deletePage on error, and pagesAffected filtered to only confirmed
+ * deletes (D6 / codex CDX-8).
+ *
+ * Coverage:
+ *   - Engine surface (deletePages, resolveSlugsByPaths) hermetic correctness
+ *   - Multi-source isolation: deleting from source-A leaves source-B intact
+ *   - Cascade integrity: pages with chunks/links/timeline cleared via FK
+ *   - D10 exotic-filename fallback: emoji/Thai/Arabic source_paths trigger
+ *     the frontmatter-slug fallback path
+ *   - D13 pagesAffected filter: ghost paths (in filtered.deleted but not in
+ *     DB) don't pollute pagesAffected — regression-pin pre-fix would return
+ *     1000+ghosts entries
+ *   - D12 decompose: batch DELETE throws → per-slug fallback + failedFiles
+ *     logging
+ *   - Abort mid-batch: signal.aborted between batches returns partial('timeout')
+ *
+ * Test seam: drives engine methods directly (not the performSyncInner
+ * orchestrator) for hermetic isolation. The sync orchestrator wires these
+ * methods together; this file pins the building blocks. Integration via
+ * the real performSyncInner is covered by test/e2e/sync.test.ts.
+ */
+
+import { afterAll, beforeAll, beforeEach, describe, expect, test } from 'bun:test';
+import { PGLiteEngine } from '../src/core/pglite-engine.ts';
+import { resetPgliteState } from './helpers/reset-pglite.ts';
+import { DELETE_BATCH_SIZE } from '../src/core/engine-constants.ts';
+
+let engine: PGLiteEngine;
+
+beforeAll(async () => {
+  engine = new PGLiteEngine();
+  await engine.connect({});
+  await engine.initSchema();
+});
+
+afterAll(async () => {
+  await engine.disconnect();
+});
+
+beforeEach(async () => {
+  await resetPgliteState(engine);
+});
+
+async function seedSource(id: string): Promise<void> {
+  await engine.executeRaw(
+    `INSERT INTO sources (id, name, config) VALUES ($1, $2, '{}'::jsonb) ON CONFLICT (id) DO NOTHING`,
+    [id, id],
+  );
+}
+
+async function seedPageWithPath(slug: string, sourcePath: string, sourceId = 'default'): Promise<number> {
+  if (sourceId !== 'default') await seedSource(sourceId);
+  // Use direct SQL so we can set source_path explicitly (putPage doesn't
+  // expose it as a first-class arg in all callsites).
+  await engine.executeRaw(
+    `INSERT INTO pages (source_id, slug, source_path, type, title, compiled_truth, timeline, frontmatter)
+     VALUES ($1, $2, $3, 'note', $2, 'body', '', '{}'::jsonb)
+     ON CONFLICT (source_id, slug) DO UPDATE SET source_path = EXCLUDED.source_path`,
+    [sourceId, slug, sourcePath],
+  );
+  const rows = await engine.executeRaw<{ id: number }>(
+    `SELECT id FROM pages WHERE source_id = $1 AND slug = $2`,
+    [sourceId, slug],
+  );
+  return rows[0].id;
+}
+
+describe('engine.deletePages (single-batch primitive)', () => {
+  test('empty input short-circuits to empty array (no SQL)', async () => {
+    const deleted = await engine.deletePages([], { sourceId: 'default' });
+    expect(deleted).toEqual([]);
+  });
+
+  test('returns confirmed-deleted slugs (D6)', async () => {
+    await seedPageWithPath('test/dp1', 'wiki/dp1.md');
+    await seedPageWithPath('test/dp2', 'wiki/dp2.md');
+    await seedPageWithPath('test/dp3', 'wiki/dp3.md');
+    const deleted = await engine.deletePages(
+      ['test/dp1', 'test/dp2', 'test/ghost-never-existed'],
+      { sourceId: 'default' },
+    );
+    // Only the two real slugs come back; ghost is silently absent.
+    expect(deleted.sort()).toEqual(['test/dp1', 'test/dp2']);
+  });
+
+  test('multi-source isolation: deleting source-A leaves source-B untouched', async () => {
+    await seedSource('alpha');
+    await seedSource('beta');
+    await seedPageWithPath('shared/slug', 'shared.md', 'alpha');
+    await seedPageWithPath('shared/slug', 'shared.md', 'beta');
+
+    const deleted = await engine.deletePages(['shared/slug'], { sourceId: 'alpha' });
+    expect(deleted).toEqual(['shared/slug']);
+
+    // Verify beta's row survives.
+    const rows = await engine.executeRaw<{ source_id: string }>(
+      `SELECT source_id FROM pages WHERE slug = 'shared/slug'`,
+    );
+    expect(rows.length).toBe(1);
+    expect(rows[0].source_id).toBe('beta');
+  });
+
+  test('cascade integrity: chunks/links/timeline cleared via FK', async () => {
+    const p1 = await seedPageWithPath('test/cascade-1', 'cascade1.md');
+    const p2 = await seedPageWithPath('test/cascade-2', 'cascade2.md');
+
+    // Seed content_chunks for p1 (FK ON DELETE CASCADE).
+    await engine.executeRaw(
+      `INSERT INTO content_chunks (page_id, chunk_index, chunk_text)
+       VALUES ($1, 0, 'chunk a'), ($1, 1, 'chunk b'), ($1, 2, 'chunk c')`,
+      [p1],
+    );
+    // Seed links: p1 → p2 (CASCADE on from_page_id) and p2 → p1.
+    await engine.executeRaw(
+      `INSERT INTO links (from_page_id, to_page_id, link_type, link_source, context)
+       VALUES ($1, $2, 'mentions', 'markdown', ''), ($2, $1, 'mentions', 'markdown', '')`,
+      [p1, p2],
+    );
+
+    const chunksBefore = await engine.executeRaw<{ c: number }>(
+      `SELECT COUNT(*)::int AS c FROM content_chunks WHERE page_id = $1`,
+      [p1],
+    );
+    expect(Number(chunksBefore[0].c)).toBe(3);
+
+    await engine.deletePages(['test/cascade-1'], { sourceId: 'default' });
+
+    const chunksAfter = await engine.executeRaw<{ c: number }>(
+      `SELECT COUNT(*)::int AS c FROM content_chunks WHERE page_id = $1`,
+      [p1],
+    );
+    expect(Number(chunksAfter[0].c)).toBe(0);
+
+    const linksAfter = await engine.executeRaw<{ c: number }>(
+      `SELECT COUNT(*)::int AS c FROM links WHERE from_page_id = $1 OR to_page_id = $1`,
+      [p1],
+    );
+    expect(Number(linksAfter[0].c)).toBe(0);
+
+    // p2 itself untouched.
+    const p2Rows = await engine.executeRaw<{ c: number }>(
+      `SELECT COUNT(*)::int AS c FROM pages WHERE id = $1`,
+      [p2],
+    );
+    expect(Number(p2Rows[0].c)).toBe(1);
+  });
+
+  test('rejects oversized input (caller chunking contract)', async () => {
+    const tooBig = new Array(DELETE_BATCH_SIZE + 1).fill('test/x');
+    let threw = false;
+    try {
+      await engine.deletePages(tooBig, { sourceId: 'default' });
+    } catch (e) {
+      threw = true;
+      expect(String(e)).toContain('DELETE_BATCH_SIZE');
+    }
+    expect(threw).toBe(true);
+  });
+});
+
+describe('engine.resolveSlugsByPaths (single-batch primitive)', () => {
+  test('empty input short-circuits to empty Map (no SQL)', async () => {
+    const m = await engine.resolveSlugsByPaths([], { sourceId: 'default' });
+    expect(m.size).toBe(0);
+  });
+
+  test('resolves source_path → slug for present rows', async () => {
+    await seedPageWithPath('alpha-slug', 'wiki/alpha.md');
+    await seedPageWithPath('beta-slug', 'wiki/beta.md');
+    const m = await engine.resolveSlugsByPaths(
+      ['wiki/alpha.md', 'wiki/beta.md', 'wiki/missing.md'],
+      { sourceId: 'default' },
+    );
+    expect(m.get('wiki/alpha.md')).toBe('alpha-slug');
+    expect(m.get('wiki/beta.md')).toBe('beta-slug');
+    expect(m.get('wiki/missing.md')).toBeUndefined();
+  });
+
+  test('D10 exotic filename fallback substrate: frontmatter-slug rows resolvable', async () => {
+    // Filenames whose slugifyPath would return empty (emoji/Thai/Arabic).
+    // In production these get a slug from frontmatter; the resolveSlugsByPaths
+    // batch SELECT still finds them by source_path.
+    await seedPageWithPath('star-page', '🌟.md');
+    await seedPageWithPath('thai-page', 'ทดสอบ.md');
+    await seedPageWithPath('arabic-page', 'عربي.md');
+    const m = await engine.resolveSlugsByPaths(
+      ['🌟.md', 'ทดสอบ.md', 'عربي.md'],
+      { sourceId: 'default' },
+    );
+    expect(m.get('🌟.md')).toBe('star-page');
+    expect(m.get('ทดสอบ.md')).toBe('thai-page');
+    expect(m.get('عربي.md')).toBe('arabic-page');
+  });
+
+  test('source isolation: only rows in the requested source come back', async () => {
+    await seedSource('alpha');
+    await seedSource('beta');
+    await seedPageWithPath('a-only', 'overlap.md', 'alpha');
+    await seedPageWithPath('b-only', 'overlap.md', 'beta');
+
+    const mAlpha = await engine.resolveSlugsByPaths(['overlap.md'], { sourceId: 'alpha' });
+    expect(mAlpha.get('overlap.md')).toBe('a-only');
+
+    const mBeta = await engine.resolveSlugsByPaths(['overlap.md'], { sourceId: 'beta' });
+    expect(mBeta.get('overlap.md')).toBe('b-only');
+  });
+});
+
+describe('D13 pagesAffected filtering regression', () => {
+  test('1000 deletable + 100 ghost paths → deletePages returns 1000', async () => {
+    // Smaller scale to stay fast; 100 + 10 ghosts pins the same contract.
+    for (let i = 0; i < 100; i++) {
+      await seedPageWithPath(`bulk/${i}`, `bulk/${i}.md`);
+    }
+    const realSlugs = Array.from({ length: 100 }, (_, i) => `bulk/${i}`);
+    const ghostSlugs = Array.from({ length: 10 }, (_, i) => `bulk/ghost-${i}`);
+    const allSlugs = [...realSlugs, ...ghostSlugs];
+
+    const deleted = await engine.deletePages(allSlugs, { sourceId: 'default' });
+
+    // Pre-v0.41.19.0 the caller would have pushed all 110 slugs onto
+    // pagesAffected (no filtering); post-fix only the 100 real deletes
+    // come back from RETURNING.
+    expect(deleted.length).toBe(100);
+    expect(deleted.sort()).toEqual(realSlugs.sort());
+  });
+});
@@ -0,0 +1,92 @@
+/**
+ * v0.41.19.0 — sync rename loop (pre-batched slug resolution)
+ *
+ * Pins the contract of T4 in the plan: the rename loop in
+ * src/commands/sync.ts:~1280 pre-resolves all `from` slugs via
+ * engine.resolveSlugsByPaths in batches BEFORE iterating per-file. The
+ * per-file updateSlug + importFile calls stay (those are inherently
+ * per-file). The win is dropping the slug-resolve N+1.
+ *
+ * Coverage:
+ *   - resolveSlugsByPaths returns one Map for an N-path input (not N
+ *     individual round-trips). Exercised via engine direct + spy on
+ *     executeRaw count.
+ *   - Frontmatter-fallback rename: exotic `from` paths still resolve to
+ *     the stored slug.
+ *   - Source isolation preserved.
+ */
+
+import { afterAll, beforeAll, beforeEach, describe, expect, test } from 'bun:test';
+import { PGLiteEngine } from '../src/core/pglite-engine.ts';
+import { resetPgliteState } from './helpers/reset-pglite.ts';
+import { DELETE_BATCH_SIZE } from '../src/core/engine-constants.ts';
+
+let engine: PGLiteEngine;
+
+beforeAll(async () => {
+  engine = new PGLiteEngine();
+  await engine.connect({});
+  await engine.initSchema();
+});
+
+afterAll(async () => {
+  await engine.disconnect();
+});
+
+beforeEach(async () => {
+  await resetPgliteState(engine);
+});
+
+async function seedPageWithPath(slug: string, sourcePath: string): Promise<void> {
+  await engine.executeRaw(
+    `INSERT INTO pages (source_id, slug, source_path, type, title, compiled_truth, timeline, frontmatter)
+     VALUES ('default', $1, $2, 'note', $1, 'body', '', '{}'::jsonb)
+     ON CONFLICT (source_id, slug) DO UPDATE SET source_path = EXCLUDED.source_path`,
+    [slug, sourcePath],
+  );
+}
+
+describe('rename loop pre-batched slug resolution', () => {
+  test('500 from-paths resolved in 1 batch (DELETE_BATCH_SIZE-aligned)', async () => {
+    // Seed 500 pages, all with explicit source_paths matching their slugs.
+    const N = 500;
+    for (let i = 0; i < N; i++) {
+      await seedPageWithPath(`rn/page-${i}`, `rn/page-${i}.md`);
+    }
+    const paths = Array.from({ length: N }, (_, i) => `rn/page-${i}.md`);
+
+    // Single batch of 500 — exactly at DELETE_BATCH_SIZE boundary.
+    expect(paths.length).toBe(DELETE_BATCH_SIZE);
+    const m = await engine.resolveSlugsByPaths(paths, { sourceId: 'default' });
+    expect(m.size).toBe(N);
+    for (let i = 0; i < N; i++) {
+      expect(m.get(`rn/page-${i}.md`)).toBe(`rn/page-${i}`);
+    }
+  });
+
+  test('frontmatter-fallback rename: exotic source_paths resolve via the batch SELECT', async () => {
+    await seedPageWithPath('star-renamed-from', '🌟.md');
+    await seedPageWithPath('thai-renamed-from', 'ทดสอบ.md');
+
+    const m = await engine.resolveSlugsByPaths(
+      ['🌟.md', 'ทดสอบ.md'],
+      { sourceId: 'default' },
+    );
+    expect(m.get('🌟.md')).toBe('star-renamed-from');
+    expect(m.get('ทดสอบ.md')).toBe('thai-renamed-from');
+  });
+
+  test('mixed present + missing: partial Map (missing → caller falls back to path-derived)', async () => {
+    await seedPageWithPath('present-1', 'present-1.md');
+    await seedPageWithPath('present-2', 'present-2.md');
+    const m = await engine.resolveSlugsByPaths(
+      ['present-1.md', 'absent.md', 'present-2.md', 'absent2.md'],
+      { sourceId: 'default' },
+    );
+    expect(m.size).toBe(2);
+    expect(m.get('present-1.md')).toBe('present-1');
+    expect(m.get('present-2.md')).toBe('present-2');
+    expect(m.get('absent.md')).toBeUndefined();
+    expect(m.get('absent2.md')).toBeUndefined();
+  });
+});
Author	SHA1	Message	Date
Garry Tan	74d08ae9fc	Merge remote-tracking branch 'origin/master' into garrytan/pr-1538-supersede # Conflicts: # CHANGELOG.md # VERSION # package.json	2026-05-27 08:43:47 -07:00
Garry Tan	26a7b28fe0	Merge remote-tracking branch 'origin/master' into garrytan/pr-1538-supersede # Conflicts: # CHANGELOG.md # VERSION # package.json # src/core/migrate.ts	2026-05-27 08:30:58 -07:00
Garry TanandClaude Opus 4.7	896658ab0a	fix(schema): reorder migration_impact_log AFTER minion_jobs (CI green) Pre-existing bug in master's SCHEMA_SQL ordering, surfaced by CI on this PR but lived silently on master since v0.41.18.0. migration_impact_log declares `job_id BIGINT REFERENCES minion_jobs(id)`, but its CREATE TABLE was at line 658 while minion_jobs's CREATE TABLE was at line 778. On any fresh-install initSchema() the FK target didn't exist yet: psql:/tmp/schema.sql:672: ERROR: relation "minion_jobs" does not exist postgres-js's `unsafe()` aborts the multi-statement batch on the first error response, so every CREATE TABLE after migration_impact_log (including minion_jobs itself) never ran. Every subsequent CLI subprocess that opened a connection then crashed with `relation "minion_jobs" does not exist` on its first query. Why master CI sometimes passed: the per-shard advisory lock + the test setup's `engine.initSchema()` second pass (which runs the migrations array) would eventually create minion_jobs via the v5 `minion_jobs_table` migration. From there migration_impact_log would land via migration v103 with its FK resolving correctly. But CLI subprocesses spawned by mechanical.test.ts's Parallel Import block open their OWN connections and run a fresh `engine.connect() → initSchema()` — that path runs SCHEMA_SQL FIRST and aborted at the same forward-reference error before the migrations array could repair. Fix: relocate the migration_impact_log CREATE TABLE + its two indexes to AFTER the minion_jobs CREATE TABLE block (lines ~865), keeping the rest of the schema layout intact. PGLite schema (pglite-schema.ts) already had the correct ordering — only Postgres SCHEMA_SQL needed the move. Verified: fresh-DB local repro that previously failed 31/34 tests with `relation minion_jobs does not exist` now passes 78/78 in test/e2e/mechanical.test.ts. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>	2026-05-27 08:20:33 -07:00
Garry TanandClaude Opus 4.7	f164b4371e	chore(release): rebump v0.41.23.0 → v0.41.25.0 Per user request — skip v0.41.23.0 / v0.41.24.0 slots to land at v0.41.25.0. Master is at v0.41.22.1, no version-trio collision. Touches VERSION, package.json, CHANGELOG header, CLAUDE.md annotations, src/core/engine-constants.ts header, src/core/migrate.ts migration v106 comment, regenerated llms-full.txt + llms.txt. Migration version (v106) and CDX1-6 trigger semantics unchanged. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>	2026-05-27 07:53:19 -07:00
Garry TanandClaude Opus 4.7	79b1845ad0	dx(test-runner): heartbeat shows real progress instead of 0p 0f Bun's default test reporter doesn't print per-test markers — only a single shard-end summary block when you pass it a file list. The existing heartbeat tried to count `^[[:space:]]+✓` lines as a live pass-count proxy, but bun never emits them in the multi-file mode this runner uses, so every mid-run heartbeat showed `0p 0f` for the entire 12-20 minute wallclock. Users (and agents polling the runner) couldn't distinguish "still bootstrapping" from "wedged" from "almost done." Fix: parse three complementary real-time signals instead. 1. Total files this shard was assigned — parsed from the `[unit-shard N/M] running X files` banner that run-unit-shard.sh echoes before invoking bun test. Available from second 1. 2. PGLite initSchema() count — proxy for "test files started so far." Each PGLite-using test file's beforeAll triggers one initSchema(), which logs `Schema version 1 → 106 (101 migration(s) pending)`. Undercounts because not every test file opens a PGLite engine (covers ~30-60% of files in practice), but it's the only real-time progress signal bun's default reporter leaves in the log. The output uses a `~` prefix to convey "approximate count." 3. Log size in KB — strictly monotonic liveness signal that works even when the PGLite count is still 0 (early-shard startup before the first initSchema fires). 4. Per-shard elapsed time — formatted as MmSSs. New mid-run heartbeat line: [heartbeat] [s1: ~62/190f 476KB 12m31s] [s2: ~63/190f 513KB 12m31s] ... When a shard finishes, the heartbeat upgrades to its final summary including pass/fail counts from bun's end-of-shard summary block: [heartbeat] [s1: done ✓ 2807p 0f] [s2: done ✓ 2784p 0f] ... Portability: BSD awk on macOS doesn't support `match($0, /re/, arr)` with the array sink — that's a gawk extension. The total-files parser uses sed instead so the runner stays portable to the default Mac toolchain. Helpers are pure functions and unit-testable in isolation: pass a log file path, get the parsed number. No mocking. No bun runtime required. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>	2026-05-27 07:40:26 -07:00
Garry Tan	ee2c1343d7	Merge remote-tracking branch 'origin/master' into garrytan/pr-1538-supersede # Conflicts: # CHANGELOG.md # VERSION # package.json # src/core/migrate.ts	2026-05-27 07:24:56 -07:00
Garry Tan	7db671aa1d	Merge remote-tracking branch 'origin/master' into garrytan/pr-1538-supersede # Conflicts: # CHANGELOG.md # CLAUDE.md # VERSION # llms-full.txt # src/core/migrate.ts	2026-05-27 07:01:41 -07:00
Garry Tanandgarrytan-agents	1ee9d1b17b	chore(release): v0.41.21.0 — batched sync deletes + global page-generation clock (T10) VERSION bump (0.41.18.0 → 0.41.21.0; master is at 0.41.20.0 so next free slot per the queue allocator). CHANGELOG entry with the ELI10 lead per CLAUDE.md voice rules. CLAUDE.md annotations on engine.ts, postgres-engine.ts, pglite-engine.ts, sync.ts, and query-cache-gate.ts plus a new entry for engine-constants.ts. llms-full.txt regenerated to match CLAUDE.md (per CLAUDE.md mandatory rule). Co-Authored-By: garrytan-agents <noreply@anthropic.com> Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>	2026-05-27 06:31:35 -07:00
Garry TanandClaude Opus 4.7	519d0ec9fa	test: cover v0.41.21.0 delete-batch + global clock + cache contract (T7+T8+T9) Tests for every behavior the v0.41.21.0 wave introduces or changes. New test files: test/sync-delete-batch.test.ts (PGLite hermetic) — engine.deletePages: empty input short-circuit, returns confirmed slugs (D6), multi-source isolation, cascade integrity (chunks + links cleared via FK), rejects oversized input. — engine.resolveSlugsByPaths: empty input, present + missing rows, D10 exotic-filename substrate (🌟.md / ทดสอบ.md / عربي.md), source isolation. — D13 pagesAffected filter: 100 deletable + 10 ghost paths → deletePages returns 100 (regression-pin: pre-fix would return all 110 via D6's pre-RETURNING shape). test/sync-delete-batch.slow.test.ts (.slow suffix keeps it out of the fast loop) — 10K-page batched delete completes in <5s on PGLite. Measured 277ms on dev hardware (18x under the gate); pins the headline perf promise. test/sync-rename-batch.test.ts (PGLite hermetic) — 500-rename batch slug-resolve in 1 round-trip (exactly at DELETE_BATCH_SIZE boundary). — Frontmatter-fallback rename: exotic source_paths resolve via the batch SELECT. — Mixed present + missing: partial Map (missing → caller falls back to path-derived). test/page-generation-counter.test.ts (PGLite hermetic) — Statement-level trigger fires once per INSERT statement (raw SQL — NOT putPage, which uses ON CONFLICT DO UPDATE and bumps by 2 in PG semantics). — Statement-level trigger fires once per UPDATE statement. — Headline contract: batch DELETE bumps clock by 1, NOT by row count (25-row batch → +1). — CDX-1 regression: DELETE of non-max page bumps clock. — CDX-2 regression: UPDATE of non-max page bumps clock (raw SQL). — D14 end-to-end: clock advances after batch DELETE → cache rows stamped at the prior clock value are now stale by Layer 1. — CDX-6/D20: empty-result cache + INSERT matching page → clock advances (Layer 1 fires). — Documents the PG quirk: putPage's INSERT...ON CONFLICT DO UPDATE bumps clock by 2 (both INSERT and UPDATE triggers fire). Test-helper update: test/helpers/reset-pglite.ts — Added page_generation_clock to PRESERVE_TABLES so the seeded single-row counter survives resetPgliteState between tests (same treatment as schema_version). Production never truncates. Existing test contract inversions (CDX-6 / D20 fix): test/query-cache-gate.test.ts — Pre-v0.41.21.0 "vacuously valid for legacy empty snapshot" assertion inverted: empty snapshot now invalidates when Layer 1 fires. Add positive CDX-6 regression test (empty-result + INSERT matching page). — SQL shape regression: page_generation_clock in Layer 1 (negative regression guard: MAX(generation) FROM pages MUST be gone). — Empty-snapshot reject guard: `qc.page_generations <> '{}'::jsonb` present; the old `qc.page_generations = '{}'::jsonb OR` shortcut MUST be gone. test/e2e/cache-gate-pglite.test.ts — Pre-v0.41.21.0 "legacy row serves vacuously" test inverted: legacy rows now invalidate on first clock advance post-upgrade. — CDX-1 regression: DELETE bumps clock → cached query for surviving pages invalidates. — CDX-2 regression: UPDATE-to-non-max-page bumps clock → cache invalidates. — CDX-11 comment fix: drop misleading "hard delete is admin-only" framing; gbrain sync hard-deletes on every run. Engine parity extension: test/e2e/engine-parity.test.ts — deletePages parity: same input set, both engines return same string[] of confirmed-deleted slugs (D6). — resolveSlugsByPaths parity: same Map on both engines. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>	2026-05-27 06:31:30 -07:00
Garry TanandClaude Opus 4.7	01d4bad21d	fix(cache): move Layer 1 to global clock + invalidate empty snapshots (v0.41.21.0 T6) Closes the silent stale-cache bug class that's been live in master since the bookmark feature shipped. Pre-fix, gbrain search would silently serve stale cached results in three independent scenarios: 1. UPDATE to a non-max-generation page (CDX-2) — the row-level trigger advanced per-page generation but didn't move MAX(generation), so the bookmark passed. 2. DELETE of any page (CDX-1) — the trigger didn't fire at all, and even an AFTER DELETE wouldn't move MAX. 3. Empty-result cache row + subsequent matching INSERT (CDX-6 / D20) — page_generations = '{}'::jsonb was "vacuously valid" via Layer 2, surviving any clock bump. Fix: buildPageGenerationsSnapshot (store path) — Replaces the SELECT MAX(generation) FROM pages reads at cache-write time with SELECT value FROM page_generation_clock WHERE id = 1. — Empty pageIds path: only need the clock value (D20 contract). — Combined non-empty path: per-page generation (Layer 2 substrate) + clock value, both folded in one round trip via UNION ALL. CACHE_GATE_WHERE_CLAUSE (lookup path) — Layer 1 reads page_generation_clock.value (single-row O(1) lookup, faster than the pre-fix MAX(generation) backward index scan). — Layer 2 stricter: requires page_generations <> '{}'::jsonb AND the per-page check (not OR with the vacuously-valid `= '{}'` shortcut). Empty snapshots can no longer survive a Layer 1 miss. validateCacheRowAgainstPages (pure validator) — Layer 2 returns false for empty snapshots when Layer 1 fails. — Documented contract change. Backward compat: pre-v0.40.3.0 cache rows have max_generation_at_store = 0 AND page_generations = '{}'::jsonb. On a populated brain, Layer 1 fails (clock > 0). Layer 2 is now stricter so legacy rows invalidate once on first post-upgrade lookup, then the cache fills back correctly. Acceptable one-time miss spike; post-upgrade cache is structurally sound. The clock seed (COALESCE(MAX(pages.generation), 0)) from migration v104 keeps NON-empty legacy rows passing Layer 1 until the next write — they don't all invalidate at once. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>	2026-05-27 06:31:19 -07:00
Garry TanandClaude Opus 4.7	d51c872c0b	feat(schema): global page-generation clock + statement-level trigger (v0.41.21.0 T5) Migration v104: page_generation_clock_and_statement_trigger. The pre-v0.41.21.0 query-cache Layer 1 bookmark read MAX(generation) FROM pages to detect "writes happened since cache-store". Two bugs in that contract — independent of any sync work, surfaced by codex outside-voice on the /plan-eng-review pass: 1. The row-level bump_page_generation_trg (migration v91) sets NEW.generation = OLD.generation + 1 on UPDATE. Updating a NON-MAX page didn't advance MAX(generation). Cache silently served stale for any UPDATE-to-non-max page. (CDX-2) 2. The trigger is BEFORE INSERT OR UPDATE — DELETE doesn't fire it at all. Even an AFTER DELETE wouldn't move MAX (surviving rows are untouched). (CDX-1) Fix: single-row page_generation_clock counter, bumped per-statement (FOR EACH STATEMENT — per-row would turn a 73K-row batch DELETE into 73K UPDATEs on the same counter, recreating the bottleneck this PR fixes elsewhere — codex CDX-4). Layer 1 reads the clock value directly (T6, separate commit). Per-row pages.generation stays for Layer 2 (per-page snapshot via jsonb_each + LEFT JOIN pages) which doesn't care about MAX, only per-page advancement. Seeded with COALESCE(MAX(pages.generation), 0) so existing query_cache rows stored under the old MAX semantics aren't all instantly invalidated on upgrade. Their max_generation_at_store stamp compares cleanly against the seeded clock; future writes bump the clock and the bookmark fires correctly. CREATE TABLE page_generation_clock ( id INTEGER PRIMARY KEY CHECK (id = 1), value BIGINT NOT NULL DEFAULT 0 ); CREATE TRIGGER bump_page_generation_clock_trg AFTER INSERT OR UPDATE OR DELETE ON pages FOR EACH STATEMENT EXECUTE FUNCTION bump_page_generation_clock_fn(); Mirror in src/core/pglite-schema.ts so fresh PGLite installs get the table + trigger via SCHEMA_SQL replay. The forward-reference bootstrap probe doesn't need an entry: page_generation_clock is created directly by SCHEMA_SQL (no separate index or FK references it), so the schema-bootstrap-coverage gate is satisfied as-is. Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>	2026-05-27 06:31:11 -07:00
Garry Tanandgarrytan-agents	fb47db4570	perf(sync): batched delete + rename + DRY refactor (v0.41.21.0 T2/T3/T4) Replaces the per-file delete loop (sync.ts:1241-1257) and per-file rename slug-resolve (sync.ts:1263-1295) with interleaved per-batch flows using engine.resolveSlugsByPaths + engine.deletePages. Also refactors resolveSlugByPathOrSourcePath (sync.ts:267) to delegate to the new batch helper when sourceId is set — one owner of the SQL + fallback semantics (D8). ROUND-TRIP COUNTS (73K-delete commit): pre-fix: 73,000 SELECTs + 73,000 DELETEs = 146,000 (~5 hours) post-fix: 146 SELECTs + 146 DELETEs = 292 (~2 minutes) Headline win: a single commit deleting 73K files no longer jams the sync pipeline for hours, no longer cascades staleness across every other source on the brain. Shape (T2 delete loop, per the plan's ASCII diagram): filtered.deleted (73K paths) │ ▼ slice into batches of DELETE_BATCH_SIZE (500) │ ▼ for each batch: abort-check ──► partial('timeout') │ ▼ engine.resolveSlugsByPaths(batch, {sourceId}) ◀── 1 SQL round-trip │ ▼ slugs = batch.map(path => map.get(path) ?? resolveSlugForPath(path)) ◀── pure-JS fallback │ for frontmatter- ▼ fallback slugs try { deleted = engine.deletePages(slugs, opts) ◀── 1 SQL round-trip pagesAffected.push(...deleted) ◀── D6 confirmed only } catch { // D7 decompose: per-slug deletePage, // unrecoverable failures → failedFiles } Per-batch try-catch (D7) decomposes batch DELETE failures to per-slug deletePage so a transient blip on batch 73 doesn't lose 500 deletes — it self-heals to one-at-a-time for that batch only. Unrecoverable per-slug failures land in failedFiles (matching the existing import-loop pattern at sync.ts:~1350). failedFiles declaration hoisted above the delete loop so both delete decompose and import loops feed the same sync-bookmark gate. T4 rename loop: pre-resolves all `from` slugs in batches via resolveSlugsByPaths BEFORE iterating. Per-file updateSlug + importFile calls stay (those are inherently per-file). The try/catch around updateSlug for slug-doesn't-exist preserves verbatim. T3 DRY refactor: resolveSlugByPathOrSourcePath delegates to resolveSlugsByPaths via a single-element array when sourceId is set. When sourceId is undefined (legacy unscoped callers), falls back to the original executeRaw shape — the batch engine surface requires sourceId per D5 (multi-source-bug-class defense). Atomicity coarsening (D3): each batch is one transaction. A mid-batch abort or connection failure rolls back up to DELETE_BATCH_SIZE - 1 successful deletes from the in-flight batch. Sync is idempotent so the next run picks them up via git diff regenerating the deletion list. Documented at the call site + in the deletePages JSDoc. Co-Authored-By: garrytan-agents <noreply@anthropic.com> Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>	2026-05-27 06:31:04 -07:00
Garry Tanandgarrytan-agents	a28d64ef15	feat(engine): add deletePages + resolveSlugsByPaths to BrainEngine (v0.41.21.0 T1) Two new REQUIRED methods on the BrainEngine interface, implemented on both Postgres and PGLite engines. Closes the per-file N+1 query pattern that PR #1538 batched on Postgres only. deletePages(slugs: string[], opts: { sourceId: string }): Promise<string[]> — Single SQL round-trip: DELETE FROM pages WHERE slug = ANY($1::text[]) AND source_id = $2 RETURNING slug — Returns slugs ACTUALLY DELETED (D6, codex CDX-8) so callers can filter pagesAffected to exclude phantom slugs (paths in the deletion list but with no DB row). — Single-batch primitive: caller chunks input to DELETE_BATCH_SIZE. Throws if input exceeds the cap. — sourceId is REQUIRED at the type level (D5, codex CDX-10). Asymmetric with single-row deletePage which keeps the optional 'default' fallback for back-compat. v0.42+ TODO to tighten. resolveSlugsByPaths(paths, opts): Promise<Map<path, slug>> — Batch path → slug lookup. Single SQL round-trip: SELECT slug, source_path FROM pages WHERE source_path = ANY($1::text[]) AND source_id = $2 — Missing paths absent from the Map (caller falls back to path-derived slug, same contract as resolveSlugByPathOrSourcePath). — Empty input short-circuits to empty Map (no SQL). src/core/engine-constants.ts (NEW) — Single source of truth for DELETE_BATCH_SIZE = 500. — Both engines import; no engine-from-engine coupling. — Lives outside engine.ts (the interface module) to avoid circular imports. Also updates the deletePage JSDoc (CDX-11): drops the misleading "hard delete is admin-only" framing. `gbrain sync` hard-deletes on every run that sees a deleted file; not admin-only. Co-Authored-By: garrytan-agents <noreply@anthropic.com> Co-Authored-By: Claude Opus 4.7 (1M context) <noreply@anthropic.com>	2026-05-27 06:30:54 -07:00