fix: post-migration schema verification with self-healing

PgBouncer transaction-mode poolers can silently swallow ALTER TABLE statements: the SQL doesn't error, but the column never gets created. The migration system increments the schema version counter anyway, so gbrain thinks it's on the latest version but the actual table is missing columns. This caused production embed failures when the embed handler tried to INSERT into columns that didn't exist. Add verifySchema() that runs after all migrations complete: 1. Parses CREATE TABLE + ALTER TABLE ADD COLUMN from schema-embedded.ts 2. Queries information_schema.columns for actual DB state 3. Diffs expected vs actual columns 4. Self-heals missing columns via ALTER TABLE ADD COLUMN IF NOT EXISTS 5. Throws with actionable diagnostics if self-heal fails Called from PostgresEngine.initSchema() after runMigrations(). PGLite skipped (in-process, no PgBouncer).
fix: pass sourceId in cycle sync phase to prevent full reimport (#475 )
2026-08-14 17:02:19 +00:00 · 2026-04-28 04:46:38 +00:00 · 2026-04-27 16:56:07 -07:00
12 changed files with 619 additions and 7 deletions
@@ -2,6 +2,97 @@

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

+## [0.22.6] - 2026-04-28
+
+### Schema verification after migrations
+
+- Post-migration schema verification catches columns that were defined in migrations but silently failed to create (common with PgBouncer transaction-mode poolers).
+- Self-healing: automatically adds missing columns via ALTER TABLE when detected.
+- Prevents the "column X does not exist" embed failures that occur when schema version is ahead of actual table state.
+
+## [0.22.5] - 2026-04-27
+
+## **Autopilot stops re-importing your whole brain when a commit gets garbage-collected.**
+## **Cycle reads the per-source `sources.last_commit` anchor instead of the drift-prone global key.**
+
+`gbrain dream` and the `autopilot-cycle` worker were calling `performSync()` without `sourceId`, so sync read the global `config.sync.last_commit` key. When that commit gets GC'd from git history (a force push, a squash, an `--amend` chain), `git cat-file -t <anchor>` fails, sync concludes "force push happened," and triggers a full reimport of every page. On a 78K-page brain that's ~30 minutes per cycle, the autopilot job hits its timeout, dead-letters, and the next cron tick does it again. Production OpenClaw deployment hit exactly this pattern: every cycle ran the full reimport while the per-source `sources.last_commit` (`00a62e50`) was a valid HEAD ancestor the entire time.
+
+v0.22.5 threads `sourceId` through the cycle. `runPhaseSync()` now resolves the brain directory against the `sources` table (`SELECT id FROM sources WHERE local_path = $1`) and passes the result to `performSync()`. When a source row matches, sync reads `sources.last_commit` (per-source, always written back on every successful sync). When no row matches (pre-v0.18 brain or never-registered path), it falls through to the global key ... fully backward compatible. Six new regression tests pin the resolver behavior, including the table-missing fallback for old brains and the empty-string-id defensive case.
+
+### The numbers that matter
+
+Production behavior on a 78,797-page brain:
+
+| Metric | Pre-v0.22.5 (master) | v0.22.5 | Δ |
+|---|---|---|---|
+| Autopilot cycle wall time (steady state) | 30+ min (then timeout) | <1 sec | -1800x |
+| Files re-imported per cycle (steady state) | 78,797 | 0 | -78,797 |
+| `autopilot-cycle` jobs hitting `max_stalled` | every cycle | 0 | -100% |
+| Cycle phases that consult per-source anchor | 0 | 1 (sync) | +1 |
+| New regression tests in `test/core/cycle.test.ts` | n/a | 6 | +6 |
+
+Resolver behavior matrix (every row covered by a test):
+
+| Scenario | sourceId passed | Anchor read from | Backward compatible |
+|---|---|---|---|
+| Sources row matches `brainDir` (current install) | `"default"` | `sources.last_commit` ✅ | Yes |
+| No sources row (pre-v0.18 brain) | `undefined` | `config.sync.last_commit` | Yes |
+| `sources` table doesn't exist (very old brain) | `undefined` (catch) | `config.sync.last_commit` | Yes |
+| Multiple rows share a `local_path` (no UNIQUE) | one of the matching ids (non-deterministic) | the matched row's anchor | Yes |
+| Empty-string id row | `""` (defensive ... won't happen in practice) | empty-string source row | Yes |
+
+### What this means for builders
+
+If your brain has been silently doing a full reimport every autopilot cycle, `gbrain upgrade` plus your next cycle will fix it ... no manual action needed. The fix is mechanical and idempotent. If you've been running with the operational band-aid that copied the per-source anchor to the global key every 5 minutes (the pre-PR workaround), you can take it out after upgrading. Two follow-ups are filed for v0.23: a `UNIQUE` index on `sources.local_path` so duplicate-path resolution is deterministic, and narrowing the resolver's bare `catch` to PostgreSQL's `42P01` (undefined_table) so real DB errors don't get silently swallowed into the global-fallback path.
+
+## To take advantage of v0.22.5
+
+`gbrain upgrade` runs `gbrain post-upgrade` which runs `gbrain apply-migrations`. v0.22.5 has no schema migration ... the fix is pure code, no data backfill ... so the upgrade itself is the entire action.
+
+1. **Upgrade:**
+   ```bash
+   gbrain upgrade
+   ```
+
+2. **Verify the next autopilot cycle is fast.** Either let `gbrain autopilot` tick naturally, or run one cycle directly:
+   ```bash
+   gbrain dream --phase sync --json | jq '.phases[] | select(.phase == "sync")'
+   ```
+   On a brain with a registered source, the sync phase should report incremental status (`up_to_date` or a small added/modified count) and complete in seconds. If it reports thousands of files added/modified on a brain you haven't actually changed, file an issue ... the resolver isn't matching your `brainDir` to a `sources.local_path` (likely a path-normalization mismatch ... see TODO 1 below).
+
+3. **Optional ... confirm the resolver matched.** The `sources` row used by `gbrain dream` should match your brain directory exactly:
+   ```bash
+   gbrain query 'SELECT id, local_path FROM sources' --json
+   ```
+   If the path stored in `sources.local_path` differs from the directory `gbrain dream --dir <path>` is invoked with (trailing slash, symlink resolution), v0.22.5 will fall back to the legacy global-key path silently for that source. A future v0.23 fix will normalize both sides; for now you can re-register the source with the canonical absolute path.
+
+4. **If any step fails or the numbers look wrong,** file an issue: https://github.com/garrytan/gbrain/issues with:
+   - output of `gbrain doctor`
+   - contents of `~/.gbrain/upgrade-errors.jsonl` if it exists
+   - which step broke
+
+   This feedback loop is how the gbrain maintainers find fragile upgrade paths. Thank you.
+
+### Itemized changes
+
+**Hotfix.** `src/core/cycle.ts` ... new `resolveSourceForDir(engine, brainDir)` helper queries `SELECT id FROM sources WHERE local_path = $1 LIMIT 1`. `runPhaseSync()` calls it before `performSync()` and threads the result as `sourceId`. Bare `try/catch` swallows missing-table errors so pre-v0.18 brains keep working unchanged. 26 new lines, one file. The fix funnels into the existing `readSyncAnchor()` branching at `src/commands/sync.ts:174-188`, which already chose between per-source and global anchors when given a `sourceId`; the cycle just wasn't passing one.
+
+**Tests.** 6 new test cases in `test/core/cycle.test.ts` covering every branch of the resolver:
+- **Test 1** ... seeded `sources` row → `performSync` receives matching `sourceId`.
+- **Test 2** ... no row → `sourceId=undefined`, falls through to global key.
+- **Test 3** ... different `brainDir` than registered source → undefined (no cross-match).
+- **Test 4** ... `sources` table missing (very old brain) → catch returns undefined, sync still runs. Uses a fresh `PGLiteEngine` (not the shared one) because `initSchema()` only re-runs PENDING migrations; `DROP TABLE` on the shared engine would have left it permanently degraded for every subsequent test in the file. Codex review caught this landmine.
+- **Test 5** ... duplicate `local_path` rows → resolver returns one of the matching ids (non-deterministic; the SQL has no `ORDER BY`). Documents the contract for the v0.23 UNIQUE-constraint follow-up.
+- **Test 6** ... empty-string id row → resolver propagates `""` (defensive case Codex flagged ... PK prevents NULL but `''` can be inserted).
+
+The `performSync` mock in `test/core/cycle.test.ts:50-65` was extended to capture `sourceId` alongside the existing `dryRun / noPull / noExtract` opts. The new `describe` block runs after the existing 22 tests; the shared PGLite engine cleanup pattern (`DELETE FROM sources` in `beforeEach`) keeps state from leaking between tests.
+
+### For contributors
+
+When threading new options through `runCycle → runPhaseSync → performSync`, extend the `syncCalls` capture shape in `test/core/cycle.test.ts:20` and add per-option assertions to the existing `describe('runCycle — dryRun propagates...')` and `describe('runCycle — phase selection')` blocks. The `cycle.test.ts` shared-engine pattern is fast (~1.4s for 28 tests on PGLite in-memory) but `initSchema()` only runs PENDING migrations ... if your test needs to mutate the schema mid-suite (DROP TABLE, ALTER, etc.), spin up a fresh `PGLiteEngine` and dispose in `finally` instead of touching the shared engine. The v0.22.5 test 4 is the canonical example.
+
+The bare `catch` in `resolveSourceForDir` is intentional for v0.22.5 because narrowing to a PG-specific error code (`error.code === '42P01'`) requires engine-aware error introspection that the existing PGLite engine doesn't expose uniformly with postgres-engine. v0.23 will add a small `isMissingRelationError(error, engine.kind)` helper to `src/core/utils.ts` and the resolver will rethrow everything else.
+
 ## [0.22.4] - 2026-04-26

 ## **Frontmatter-guard ships. Broken brain pages can't hide.**
@@ -101,7 +101,7 @@ strict behavior when unset.
 - `src/core/migrate.ts` — schema-migration runner. Owns the `MIGRATIONS` array (source of truth for schema DDL). v0.14.2 extended the `Migration` interface with `sqlFor?: { postgres?, pglite? }` (engine-specific SQL overrides `sql`) and `transaction?: boolean` (set to false for `CREATE INDEX CONCURRENTLY`, which Postgres refuses inside a transaction; ignored on PGLite since it has no concurrent writers). Migration v14 (fix wave) uses a handler branching on `engine.kind` to run CONCURRENTLY on Postgres (with a pre-drop of any invalid remnant via `pg_index.indisvalid`) and plain `CREATE INDEX` on PGLite. v15 bumps `minion_jobs.max_stalled` default 1→5 and backfills existing non-terminal rows.
 - `src/core/progress.ts` — Shared bulk-action progress reporter. Writes to stderr. Modes: `auto` (TTY: `\r`-rewriting; non-TTY: plain lines), `human`, `json` (JSONL), `quiet`. Rate-gated by `minIntervalMs` and `minItems`. `startHeartbeat(reporter, note)` helper for single long queries. `child()` composes phase paths. Singleton SIGINT/SIGTERM coordinator emits `abort` events for every live phase. EPIPE defense on both sync throws and stream `'error'` events. Zero dependencies. Introduced in v0.15.2.
 - `src/core/cli-options.ts` — Global CLI flag parser. `parseGlobalFlags(argv)` returns `{cliOpts, rest}` with `--quiet` / `--progress-json` / `--progress-interval=<ms>` stripped. `getCliOptions()` / `setCliOptions()` expose a module-level singleton so commands reach the resolved flags without parameter threading. `cliOptsToProgressOptions()` maps to reporter options. `childGlobalFlags()` returns the flag suffix to append to `execSync('gbrain ...')` calls in migration orchestrators. `OperationContext.cliOpts` extends shared-op dispatch for MCP callers.
- `src/core/cycle.ts` — v0.17 brain maintenance cycle primitive. `runCycle(engine: BrainEngine | null, opts: CycleOpts): Promise<CycleReport>` composes 6 phases in semantically-driven order (lint → backlinks → sync → extract → embed → orphans). Three callers: `gbrain dream` CLI, `gbrain autopilot` daemon's inline path, and the Minions `autopilot-cycle` handler (`src/commands/jobs.ts`). One source of truth for what the brain does overnight. Coordination via `gbrain_cycle_locks` DB table (TTL-based; works through PgBouncer transaction pooling, unlike session-scoped `pg_try_advisory_lock`) + `~/.gbrain/cycle.lock` file lock with PID-liveness for PGLite / engine=null mode. `CycleReport.schema_version: "1"` is the stable agent-consumable shape. `PhaseResult.error: { class, code, message, hint?, docs_url? }` is Stripe-API-tier structured failure info. `yieldBetweenPhases` hook awaited between every phase — Minions handler uses this to renew its job lock and prevent v0.14 stall-death regression. Engine nullable: filesystem phases (lint, backlinks) run without DB; DB phases skip with `status: "skipped", reason: "no_database"`. Lock-skip: read-only phase selections (`--phase orphans`) bypass the cycle lock. v0.22.1 (#403): `CycleOpts.signal?: AbortSignal` propagates the worker's abort signal; `checkAborted()` fires between every phase and throws if the signal is aborted (cooperative — can't interrupt a phase mid-execution). v0.22.1 (#417): `runPhaseSync` returns `pagesAffected` via `SyncPhaseResult`; `runCycle` captures it and threads to `runPhaseExtract` as the 4th arg, enabling incremental extract on the cycle path. v0.22.1 (Codex F2): `runPhaseSync` takes `willRunExtractPhase: boolean` and sets `noExtract: phases.includes('extract')` so `gbrain dream --phase sync` doesn't silently lose extraction.
+- `src/core/cycle.ts` — v0.17 brain maintenance cycle primitive. `runCycle(engine: BrainEngine | null, opts: CycleOpts): Promise<CycleReport>` composes 6 phases in semantically-driven order (lint → backlinks → sync → extract → embed → orphans). Three callers: `gbrain dream` CLI, `gbrain autopilot` daemon's inline path, and the Minions `autopilot-cycle` handler (`src/commands/jobs.ts`). One source of truth for what the brain does overnight. Coordination via `gbrain_cycle_locks` DB table (TTL-based; works through PgBouncer transaction pooling, unlike session-scoped `pg_try_advisory_lock`) + `~/.gbrain/cycle.lock` file lock with PID-liveness for PGLite / engine=null mode. `CycleReport.schema_version: "1"` is the stable agent-consumable shape. `PhaseResult.error: { class, code, message, hint?, docs_url? }` is Stripe-API-tier structured failure info. `yieldBetweenPhases` hook awaited between every phase — Minions handler uses this to renew its job lock and prevent v0.14 stall-death regression. Engine nullable: filesystem phases (lint, backlinks) run without DB; DB phases skip with `status: "skipped", reason: "no_database"`. Lock-skip: read-only phase selections (`--phase orphans`) bypass the cycle lock. v0.22.1 (#403): `CycleOpts.signal?: AbortSignal` propagates the worker's abort signal; `checkAborted()` fires between every phase and throws if the signal is aborted (cooperative — can't interrupt a phase mid-execution). v0.22.1 (#417): `runPhaseSync` returns `pagesAffected` via `SyncPhaseResult`; `runCycle` captures it and threads to `runPhaseExtract` as the 4th arg, enabling incremental extract on the cycle path. v0.22.1 (Codex F2): `runPhaseSync` takes `willRunExtractPhase: boolean` and sets `noExtract: phases.includes('extract')` so `gbrain dream --phase sync` doesn't silently lose extraction. v0.22.5 (#475): new `resolveSourceForDir(engine, brainDir)` helper queries `SELECT id FROM sources WHERE local_path = $1 LIMIT 1`; `runPhaseSync` threads result as `sourceId` to `performSync()` so sync reads the per-source `sources.last_commit` anchor instead of the drift-prone global `config.sync.last_commit` key. Bare try/catch lets pre-v0.18 brains fall through to the global key. Closes the prod hang where every autopilot cycle ran a 30-min full reimport because the global anchor commit had been GC'd from git history.
 - `src/commands/dream.ts` — v0.17 `gbrain dream` CLI. ~80-line thin alias over `runCycle`. brainDir resolution requires explicit `--dir` OR `sync.repo_path` config (no more walk-up-cwd-for-.git footgun). Flags: `--dry-run`, `--json`, `--phase <name>`, `--pull`, `--dir <path>`. Exit code 1 on status=failed (partial/warn not fatal — don't page on warnings).
 - `scripts/check-progress-to-stdout.sh` — CI guard against regressing to `\r`-on-stdout progress. Wired into `bun run test` via `scripts/check-progress-to-stdout.sh && bun test` in package.json.
 - `docs/progress-events.md` — Canonical JSON event schema reference. Stable from v0.15.2, additive only.
@@ -1 +1 @@
-0.22.4
+0.22.5
@@ -180,7 +180,7 @@ strict behavior when unset.
 - `src/core/migrate.ts` — schema-migration runner. Owns the `MIGRATIONS` array (source of truth for schema DDL). v0.14.2 extended the `Migration` interface with `sqlFor?: { postgres?, pglite? }` (engine-specific SQL overrides `sql`) and `transaction?: boolean` (set to false for `CREATE INDEX CONCURRENTLY`, which Postgres refuses inside a transaction; ignored on PGLite since it has no concurrent writers). Migration v14 (fix wave) uses a handler branching on `engine.kind` to run CONCURRENTLY on Postgres (with a pre-drop of any invalid remnant via `pg_index.indisvalid`) and plain `CREATE INDEX` on PGLite. v15 bumps `minion_jobs.max_stalled` default 1→5 and backfills existing non-terminal rows.
 - `src/core/progress.ts` — Shared bulk-action progress reporter. Writes to stderr. Modes: `auto` (TTY: `\r`-rewriting; non-TTY: plain lines), `human`, `json` (JSONL), `quiet`. Rate-gated by `minIntervalMs` and `minItems`. `startHeartbeat(reporter, note)` helper for single long queries. `child()` composes phase paths. Singleton SIGINT/SIGTERM coordinator emits `abort` events for every live phase. EPIPE defense on both sync throws and stream `'error'` events. Zero dependencies. Introduced in v0.15.2.
 - `src/core/cli-options.ts` — Global CLI flag parser. `parseGlobalFlags(argv)` returns `{cliOpts, rest}` with `--quiet` / `--progress-json` / `--progress-interval=<ms>` stripped. `getCliOptions()` / `setCliOptions()` expose a module-level singleton so commands reach the resolved flags without parameter threading. `cliOptsToProgressOptions()` maps to reporter options. `childGlobalFlags()` returns the flag suffix to append to `execSync('gbrain ...')` calls in migration orchestrators. `OperationContext.cliOpts` extends shared-op dispatch for MCP callers.
- `src/core/cycle.ts` — v0.17 brain maintenance cycle primitive. `runCycle(engine: BrainEngine | null, opts: CycleOpts): Promise<CycleReport>` composes 6 phases in semantically-driven order (lint → backlinks → sync → extract → embed → orphans). Three callers: `gbrain dream` CLI, `gbrain autopilot` daemon's inline path, and the Minions `autopilot-cycle` handler (`src/commands/jobs.ts`). One source of truth for what the brain does overnight. Coordination via `gbrain_cycle_locks` DB table (TTL-based; works through PgBouncer transaction pooling, unlike session-scoped `pg_try_advisory_lock`) + `~/.gbrain/cycle.lock` file lock with PID-liveness for PGLite / engine=null mode. `CycleReport.schema_version: "1"` is the stable agent-consumable shape. `PhaseResult.error: { class, code, message, hint?, docs_url? }` is Stripe-API-tier structured failure info. `yieldBetweenPhases` hook awaited between every phase — Minions handler uses this to renew its job lock and prevent v0.14 stall-death regression. Engine nullable: filesystem phases (lint, backlinks) run without DB; DB phases skip with `status: "skipped", reason: "no_database"`. Lock-skip: read-only phase selections (`--phase orphans`) bypass the cycle lock. v0.22.1 (#403): `CycleOpts.signal?: AbortSignal` propagates the worker's abort signal; `checkAborted()` fires between every phase and throws if the signal is aborted (cooperative — can't interrupt a phase mid-execution). v0.22.1 (#417): `runPhaseSync` returns `pagesAffected` via `SyncPhaseResult`; `runCycle` captures it and threads to `runPhaseExtract` as the 4th arg, enabling incremental extract on the cycle path. v0.22.1 (Codex F2): `runPhaseSync` takes `willRunExtractPhase: boolean` and sets `noExtract: phases.includes('extract')` so `gbrain dream --phase sync` doesn't silently lose extraction.
+- `src/core/cycle.ts` — v0.17 brain maintenance cycle primitive. `runCycle(engine: BrainEngine | null, opts: CycleOpts): Promise<CycleReport>` composes 6 phases in semantically-driven order (lint → backlinks → sync → extract → embed → orphans). Three callers: `gbrain dream` CLI, `gbrain autopilot` daemon's inline path, and the Minions `autopilot-cycle` handler (`src/commands/jobs.ts`). One source of truth for what the brain does overnight. Coordination via `gbrain_cycle_locks` DB table (TTL-based; works through PgBouncer transaction pooling, unlike session-scoped `pg_try_advisory_lock`) + `~/.gbrain/cycle.lock` file lock with PID-liveness for PGLite / engine=null mode. `CycleReport.schema_version: "1"` is the stable agent-consumable shape. `PhaseResult.error: { class, code, message, hint?, docs_url? }` is Stripe-API-tier structured failure info. `yieldBetweenPhases` hook awaited between every phase — Minions handler uses this to renew its job lock and prevent v0.14 stall-death regression. Engine nullable: filesystem phases (lint, backlinks) run without DB; DB phases skip with `status: "skipped", reason: "no_database"`. Lock-skip: read-only phase selections (`--phase orphans`) bypass the cycle lock. v0.22.1 (#403): `CycleOpts.signal?: AbortSignal` propagates the worker's abort signal; `checkAborted()` fires between every phase and throws if the signal is aborted (cooperative — can't interrupt a phase mid-execution). v0.22.1 (#417): `runPhaseSync` returns `pagesAffected` via `SyncPhaseResult`; `runCycle` captures it and threads to `runPhaseExtract` as the 4th arg, enabling incremental extract on the cycle path. v0.22.1 (Codex F2): `runPhaseSync` takes `willRunExtractPhase: boolean` and sets `noExtract: phases.includes('extract')` so `gbrain dream --phase sync` doesn't silently lose extraction. v0.22.5 (#475): new `resolveSourceForDir(engine, brainDir)` helper queries `SELECT id FROM sources WHERE local_path = $1 LIMIT 1`; `runPhaseSync` threads result as `sourceId` to `performSync()` so sync reads the per-source `sources.last_commit` anchor instead of the drift-prone global `config.sync.last_commit` key. Bare try/catch lets pre-v0.18 brains fall through to the global key. Closes the prod hang where every autopilot cycle ran a 30-min full reimport because the global anchor commit had been GC'd from git history.
 - `src/commands/dream.ts` — v0.17 `gbrain dream` CLI. ~80-line thin alias over `runCycle`. brainDir resolution requires explicit `--dir` OR `sync.repo_path` config (no more walk-up-cwd-for-.git footgun). Flags: `--dry-run`, `--json`, `--phase <name>`, `--pull`, `--dir <path>`. Exit code 1 on status=failed (partial/warn not fatal — don't page on warnings).
 - `scripts/check-progress-to-stdout.sh` — CI guard against regressing to `\r`-on-stdout progress. Wired into `bun run test` via `scripts/check-progress-to-stdout.sh && bun test` in package.json.
 - `docs/progress-events.md` — Canonical JSON event schema reference. Stable from v0.15.2, additive only.
@@ -1,6 +1,6 @@
 {
  "name": "gbrain",
-  "version": "0.22.4",
+  "version": "0.22.6",
  "description": "Postgres-native personal knowledge brain with hybrid RAG search",
  "type": "module",
  "main": "src/core/index.ts",
@@ -15,6 +15,11 @@
 # the natural per-file test time of 5-10s.
 #
 # Exits non-zero on the first failing file so CI fails fast.
+#
+# `--timeout=60000` matches the unit test suite. Bun's default is 5s,
+# which is too tight for setupDB's TRUNCATE CASCADE on ~30 tables on
+# CI runners under load (one CI flake observed on PR #475 hitting
+# exactly 5000.09ms in the Tags beforeAll).

 set -euo pipefail

@@ -30,7 +35,7 @@ for f in test/e2e/*.test.ts; do
  name=$(basename "$f")
  echo ""
  echo "=== $name ==="
-  if output=$(bun test "$f" 2>&1); then
+  if output=$(bun test --timeout=60000 "$f" 2>&1); then
    pass_files=$((pass_files + 1))
    # Extract pass/fail counts from bun's summary (e.g., "123 pass")
    p=$(echo "$output" | grep -oE '[0-9]+ pass' | tail -1 | grep -oE '[0-9]+' || echo 0)
@@ -444,6 +444,27 @@ interface SyncPhaseResult extends PhaseResult {
  pagesAffected?: string[];
 }

+/**
+ * Resolve the source id for a brain directory by looking up the sources
+ * table. Returns undefined when no registered source matches (falls back
+ * to pre-v0.18 global config.sync.* keys).
+ */
+async function resolveSourceForDir(
+  engine: BrainEngine,
+  brainDir: string,
+): Promise<string | undefined> {
+  try {
+    const rows = await engine.executeRaw<{ id: string }>(
+      `SELECT id FROM sources WHERE local_path = $1 LIMIT 1`,
+      [brainDir],
+    );
+    return rows[0]?.id;
+  } catch {
+    // sources table might not exist on very old brains — fall through.
+    return undefined;
+  }
+}
+
 async function runPhaseSync(
  engine: BrainEngine,
  brainDir: string,
@@ -453,8 +474,13 @@ async function runPhaseSync(
 ): Promise<SyncPhaseResult> {
  try {
    const { performSync } = await import('../commands/sync.ts');
+    // Resolve the per-source id so sync reads source-scoped last_commit
+    // instead of the global config key. The global key can drift out of
+    // git history (force push, GC) causing a full reimport of all files.
+    const sourceId = await resolveSourceForDir(engine, brainDir);
    const result = await performSync(engine, {
      repoPath: brainDir,
+      sourceId,
      dryRun,
      noPull: !pull,
      noEmbed: true,                       // embed is a separate phase
@@ -2,6 +2,7 @@ import postgres from 'postgres';
 import { GBrainError, type EngineConfig } from './types.ts';
 import { SCHEMA_SQL } from './schema-embedded.ts';
 import type { BrainEngine } from './engine.ts';
+import { verifySchema } from './schema-verify.ts';

 let sql: ReturnType<typeof postgres> | null = null;
 let connectedUrl: string | null = null;
@@ -237,6 +238,8 @@ export async function initSchema(): Promise<void> {
  }
 }

+export { verifySchema } from './schema-verify.ts';
+
 export async function withTransaction<T>(fn: (tx: ReturnType<typeof postgres>) => Promise<T>): Promise<T> {
  const conn = getConnection();
  return conn.begin(async (tx) => {
@@ -3,6 +3,7 @@ import type { BrainEngine, LinkBatchInput, TimelineBatchInput, ReservedConnectio
 import { MAX_SEARCH_LIMIT, clampSearchLimit } from './engine.ts';
 import { runMigrations } from './migrate.ts';
 import { SCHEMA_SQL } from './schema-embedded.ts';
+import { verifySchema } from './schema-verify.ts';
 import type {
  Page, PageInput, PageFilters, PageType,
  Chunk, ChunkInput, StaleChunkRow,
@@ -108,6 +109,14 @@ export class PostgresEngine implements BrainEngine {
      if (applied > 0) {
        console.log(`  ${applied} migration(s) applied`);
      }
+
+      // Post-migration schema verification: catches columns that migrations
+      // defined but PgBouncer transaction-mode silently failed to create.
+      // Self-heals missing columns via ALTER TABLE ADD COLUMN IF NOT EXISTS.
+      const verify = await verifySchema(this);
+      if (verify.healed.length > 0) {
+        console.log(`  Schema verify: self-healed ${verify.healed.length} missing column(s)`);
+      }
    } finally {
      await conn`SELECT pg_advisory_unlock(42)`;
    }
@@ -0,0 +1,282 @@
+/**
+ * Post-migration schema verification with self-healing.
+ *
+ * PgBouncer transaction-mode poolers can silently swallow ALTER TABLE
+ * statements: the SQL doesn't error, but the column never gets created.
+ * The migration system increments the schema version counter anyway, so
+ * gbrain thinks it's on v29 but the actual table is missing columns.
+ *
+ * This module parses the canonical CREATE TABLE definitions in
+ * schema-embedded.ts and diffs them against information_schema.columns.
+ * Missing columns are self-healed via ALTER TABLE ADD COLUMN IF NOT EXISTS.
+ *
+ * Called at the end of initSchema(), after all migrations complete.
+ */
+
+import { SCHEMA_SQL } from './schema-embedded.ts';
+import type { BrainEngine } from './engine.ts';
+
+/** A column expected to exist in the database. */
+export interface ExpectedColumn {
+  table: string;
+  column: string;
+  /** The full column definition (type + constraints) from the CREATE TABLE. */
+  definition: string;
+}
+
+/**
+ * Parse CREATE TABLE statements from SCHEMA_SQL to extract expected columns.
+ *
+ * This is a best-effort parser that handles the gbrain schema conventions:
+ * - Standard column definitions with types and constraints
+ * - Skips CONSTRAINT lines, CHECK lines, and UNIQUE lines
+ * - Handles multi-line definitions
+ *
+ * Returns only tables and columns — not constraints, indexes, or triggers.
+ */
+export function parseExpectedColumns(): ExpectedColumn[] {
+  const results: ExpectedColumn[] = [];
+
+  // Match CREATE TABLE IF NOT EXISTS <name> ( ... );
+  const tableRegex = /CREATE\s+TABLE\s+IF\s+NOT\s+EXISTS\s+(\w+)\s*\(([\s\S]*?)\);/gi;
+
+  const SQL_KEYWORDS = new Set(['constraint', 'unique', 'check', 'primary', 'foreign', 'exclude']);
+
+  function processLine(tableName: string, line: string) {
+    line = line.trim().replace(/,\s*$/, '');
+    if (!line) return;
+
+    // Skip CONSTRAINT, UNIQUE, CHECK, PRIMARY KEY lines
+    if (/^\s*(CONSTRAINT|UNIQUE|CHECK|PRIMARY\s+KEY)/i.test(line)) return;
+
+    const colMatch = line.match(/^\s*(\w+)\s+(.+)$/);
+    if (colMatch) {
+      const colName = colMatch[1].toLowerCase();
+      if (SQL_KEYWORDS.has(colName)) return;
+
+      results.push({
+        table: tableName,
+        column: colName,
+        definition: colMatch[2].trim(),
+      });
+    }
+  }
+
+  let match: RegExpExecArray | null;
+  while ((match = tableRegex.exec(SCHEMA_SQL)) !== null) {
+    const tableName = match[1];
+    const body = match[2];
+
+    const lines = body.split('\n');
+    let currentLine = '';
+
+    for (const rawLine of lines) {
+      const trimmed = rawLine.trim();
+
+      // Skip empty lines and comments
+      if (!trimmed || trimmed.startsWith('--')) {
+        // If we have accumulated content and hit a blank/comment line,
+        // the accumulated content is a complete line
+        if (currentLine.trim()) {
+          processLine(tableName, currentLine);
+          currentLine = '';
+        }
+        continue;
+      }
+
+      currentLine += ' ' + trimmed;
+
+      // If line ends with comma, it's a complete column definition
+      if (trimmed.endsWith(',')) {
+        processLine(tableName, currentLine);
+        currentLine = '';
+      }
+    }
+
+    // Handle any remaining accumulated line (last column before closing paren)
+    if (currentLine.trim()) {
+      processLine(tableName, currentLine);
+    }
+  }
+
+  // Also parse ALTER TABLE ... ADD COLUMN IF NOT EXISTS statements.
+  // These are used for columns added outside CREATE TABLE blocks
+  // (e.g., pages.search_vector, files.source_id).
+  const alterRegex = /ALTER\s+TABLE\s+(\w+)\s+ADD\s+COLUMN\s+IF\s+NOT\s+EXISTS\s+(\w+)\s+([^;,]+)/gi;
+  let alterMatch: RegExpExecArray | null;
+  const seen = new Set(results.map(r => `${r.table}.${r.column}`));
+  while ((alterMatch = alterRegex.exec(SCHEMA_SQL)) !== null) {
+    const table = alterMatch[1];
+    const column = alterMatch[2].toLowerCase();
+    const definition = alterMatch[3].trim().replace(/,\s*$/, '');
+    const key = `${table}.${column}`;
+    if (!seen.has(key)) {
+      seen.add(key);
+      results.push({ table, column, definition });
+    }
+  }
+
+  return results;
+}
+
+/**
+ * Build a simplified type expression suitable for ALTER TABLE ADD COLUMN.
+ *
+ * Strips inline REFERENCES, CHECK, UNIQUE, and complex constraints that
+ * can't be used in ADD COLUMN IF NOT EXISTS. Preserves NOT NULL, DEFAULT,
+ * and the base type.
+ */
+export function simplifyColumnDef(definition: string): string {
+  let def = definition;
+
+  // Remove REFERENCES ... (with optional ON DELETE/UPDATE clauses)
+  def = def.replace(/REFERENCES\s+\w+\([^)]*\)(\s+ON\s+(DELETE|UPDATE)\s+\w+(\s+\w+)?)*\s*/gi, '');
+
+  // Remove CHECK constraints (handle nested parens)
+  def = def.replace(/CHECK\s*\((?:[^()]*|\([^()]*\))*\)/gi, '');
+
+  // Remove inline UNIQUE
+  def = def.replace(/\bUNIQUE\b/gi, '');
+
+  // Remove trailing commas and whitespace
+  def = def.replace(/,\s*$/, '').trim();
+
+  // Collapse multiple spaces
+  def = def.replace(/\s+/g, ' ').trim();
+
+  return def;
+}
+
+/**
+ * Query the database for actual columns in the public schema.
+ * Returns a Set of "table.column" strings for fast lookup.
+ */
+async function getActualColumns(engine: BrainEngine): Promise<Set<string>> {
+  const rows = await engine.executeRaw<{ table_name: string; column_name: string }>(
+    `SELECT table_name, column_name
+     FROM information_schema.columns
+     WHERE table_schema = 'public'`
+  );
+  const set = new Set<string>();
+  for (const row of rows) {
+    set.add(`${row.table_name}.${row.column_name}`);
+  }
+  return set;
+}
+
+/**
+ * Get the set of tables that actually exist in the database.
+ */
+async function getActualTables(engine: BrainEngine): Promise<Set<string>> {
+  const rows = await engine.executeRaw<{ table_name: string }>(
+    `SELECT table_name
+     FROM information_schema.tables
+     WHERE table_schema = 'public' AND table_type = 'BASE TABLE'`
+  );
+  return new Set(rows.map(r => r.table_name));
+}
+
+export interface VerifyResult {
+  /** Total columns checked */
+  checked: number;
+  /** Columns that were missing */
+  missing: Array<{ table: string; column: string }>;
+  /** Columns successfully self-healed */
+  healed: Array<{ table: string; column: string }>;
+  /** Columns that failed to self-heal */
+  failed: Array<{ table: string; column: string; error: string }>;
+}
+
+/**
+ * Verify that every column defined in schema-embedded.ts actually exists
+ * in the database. Self-heals missing columns via ALTER TABLE ADD COLUMN.
+ *
+ * Should be called after initSchema() + runMigrations() complete.
+ *
+ * @returns VerifyResult with details of what was checked and fixed.
+ * @throws Error if any columns could not be healed (after attempting all).
+ */
+export async function verifySchema(engine: BrainEngine): Promise<VerifyResult> {
+  const expected = parseExpectedColumns();
+  const actualColumns = await getActualColumns(engine);
+  const actualTables = await getActualTables(engine);
+
+  const result: VerifyResult = {
+    checked: 0,
+    missing: [],
+    healed: [],
+    failed: [],
+  };
+
+  // Group expected columns by table for cleaner logging
+  for (const col of expected) {
+    // Skip tables that don't exist yet — they'll be created by schema.sql
+    // on the next initSchema() call. We only verify columns on tables that
+    // DO exist (the failure mode is: table exists, migration ran, but ALTER
+    // TABLE silently failed).
+    if (!actualTables.has(col.table)) {
+      continue;
+    }
+
+    result.checked++;
+
+    const key = `${col.table}.${col.column}`;
+    if (!actualColumns.has(key)) {
+      result.missing.push({ table: col.table, column: col.column });
+    }
+  }
+
+  if (result.missing.length === 0) {
+    return result;
+  }
+
+  // Log missing columns
+  console.warn(`\n⚠️  Schema verification found ${result.missing.length} missing column(s):`);
+  for (const m of result.missing) {
+    console.warn(`  ${m.table}.${m.column}`);
+  }
+  console.warn('  Attempting self-heal via ALTER TABLE ADD COLUMN...\n');
+
+  // Build a map from table.column -> definition for self-healing
+  const defMap = new Map<string, string>();
+  for (const col of expected) {
+    defMap.set(`${col.table}.${col.column}`, col.definition);
+  }
+
+  // Attempt to add each missing column
+  for (const m of result.missing) {
+    const rawDef = defMap.get(`${m.table}.${m.column}`);
+    if (!rawDef) {
+      result.failed.push({ ...m, error: 'No definition found in schema' });
+      continue;
+    }
+
+    const simpleDef = simplifyColumnDef(rawDef);
+
+    try {
+      const sql = `ALTER TABLE ${m.table} ADD COLUMN IF NOT EXISTS ${m.column} ${simpleDef}`;
+      await engine.runMigration(0, sql);
+      result.healed.push({ table: m.table, column: m.column });
+      console.log(`  ✓ Added ${m.table}.${m.column}`);
+    } catch (err: unknown) {
+      const msg = err instanceof Error ? err.message : String(err);
+      result.failed.push({ ...m, error: msg });
+      console.error(`  ✗ Failed to add ${m.table}.${m.column}: ${msg}`);
+    }
+  }
+
+  if (result.healed.length > 0) {
+    console.log(`\n  Schema self-heal: ${result.healed.length}/${result.missing.length} column(s) recovered.`);
+  }
+
+  if (result.failed.length > 0) {
+    const failList = result.failed.map(f => `${f.table}.${f.column}: ${f.error}`).join('\n  ');
+    throw new Error(
+      `Schema verification failed: ${result.failed.length} column(s) could not be added:\n  ${failList}\n` +
+      'This usually means PgBouncer transaction-mode silently dropped ALTER TABLE statements.\n' +
+      'Fix: connect directly to Postgres (not through PgBouncer) and run: gbrain apply-migrations --yes'
+    );
+  }
+
+  return result;
+}
@@ -17,7 +17,7 @@ import { existsSync, unlinkSync } from 'fs';

 let lintCalls: Array<{ target: string; fix: boolean; dryRun: boolean | undefined }> = [];
 let backlinksCalls: Array<{ action: string; dir: string; dryRun: boolean | undefined }> = [];
-let syncCalls: Array<{ dryRun: boolean | undefined; noPull: boolean | undefined; noExtract: boolean | undefined }> = [];
+let syncCalls: Array<{ dryRun: boolean | undefined; noPull: boolean | undefined; noExtract: boolean | undefined; sourceId: string | undefined }> = [];
 let extractCalls: Array<{ mode: string; dir: string; slugs: string[] | undefined }> = [];
 let embedCalls: Array<{ stale: boolean | undefined; dryRun: boolean | undefined }> = [];
 let orphansCalls: number = 0;
@@ -49,7 +49,7 @@ mock.module('../../src/commands/backlinks.ts', () => ({
 // Mock sync
 mock.module('../../src/commands/sync.ts', () => ({
  performSync: async (_engine: any, opts: any) => {
-    syncCalls.push({ dryRun: opts.dryRun, noPull: opts.noPull, noExtract: opts.noExtract });
+    syncCalls.push({ dryRun: opts.dryRun, noPull: opts.noPull, noExtract: opts.noExtract, sourceId: opts.sourceId });
    return {
      status: opts.dryRun ? 'dry_run' : 'synced',
      fromCommit: 'abcd',
@@ -452,3 +452,91 @@ describe('runCycle — Codex F2: noExtract is gated on whether extract phase run
    expect(extractCalls.length).toBe(0); // extract phase did NOT run
  });
 });
+
+// ─── sourceId resolution (regression #475) ─────────────────────────
+//
+// Production OpenClaw deployment hit a 30+ min hang on every autopilot
+// cycle because runPhaseSync was calling performSync without sourceId,
+// so sync read the global config.sync.last_commit key (which had drifted
+// out of git history after a force-push GC'd the commit). The per-source
+// sources.last_commit anchor was valid the entire time. PR #475 added
+// resolveSourceForDir() so the cycle reads the per-source anchor instead.
+//
+// These tests pin the resolver -> performSync(opts.sourceId) plumbing.
+
+describe('runCycle — sourceId resolution (regression #475)', () => {
+  beforeEach(async () => {
+    await truncateCycleLocks(sharedEngine);
+    await (sharedEngine as any).db.query('DELETE FROM sources');
+  });
+
+  test('seeded sources row → performSync receives matching sourceId', async () => {
+    await (sharedEngine as any).db.query(
+      `INSERT INTO sources (id, name, local_path) VALUES ($1, $2, $3)`,
+      ['default', 'default', '/tmp/brain-475-a'],
+    );
+    await runCycle(sharedEngine, { brainDir: '/tmp/brain-475-a' });
+    expect(syncCalls.at(-1)?.sourceId).toBe('default');
+  });
+
+  test('no matching sources row → performSync receives sourceId=undefined', async () => {
+    await runCycle(sharedEngine, { brainDir: '/tmp/brain-475-b' });
+    expect(syncCalls.at(-1)?.sourceId).toBeUndefined();
+  });
+
+  test('different brainDir than registered source → undefined (no cross-match)', async () => {
+    await (sharedEngine as any).db.query(
+      `INSERT INTO sources (id, name, local_path) VALUES ($1, $2, $3)`,
+      ['other', 'other', '/some/other/brain'],
+    );
+    await runCycle(sharedEngine, { brainDir: '/tmp/brain-475-c' });
+    expect(syncCalls.at(-1)?.sourceId).toBeUndefined();
+  });
+
+  test('sources table missing (very old brain) → catch returns undefined, sync still runs', async () => {
+    // CRITICAL: do NOT DROP TABLE on the shared engine. initSchema() only
+    // re-runs PENDING migrations; once schema_version is at latest, the
+    // v20 migration that creates `sources` will not re-execute. Use a
+    // fresh one-shot engine so the shared engine isn't degraded for
+    // every later test in this file.
+    const fresh = new PGLiteEngine();
+    await fresh.connect({});
+    await fresh.initSchema();
+    await (fresh as any).db.query('DROP TABLE IF EXISTS sources CASCADE');
+    try {
+      await runCycle(fresh, { brainDir: '/tmp/brain-475-d' });
+      expect(syncCalls.at(-1)?.sourceId).toBeUndefined();
+    } finally {
+      await fresh.disconnect();
+    }
+  });
+
+  test('multiple rows with same local_path → resolver returns one matching id (non-deterministic)', async () => {
+    // Schema has no UNIQUE on local_path; SQL has no ORDER BY. Either id
+    // is acceptable; the contract is "any matching id, never null when
+    // matches exist." This test pins behavior so the follow-up
+    // UNIQUE-constraint TODO has a regression target.
+    await (sharedEngine as any).db.query(
+      `INSERT INTO sources (id, name, local_path) VALUES
+        ('first', 'first', '/tmp/brain-475-e'),
+        ('second', 'second', '/tmp/brain-475-e')`,
+    );
+    await runCycle(sharedEngine, { brainDir: '/tmp/brain-475-e' });
+    const sourceId = syncCalls.at(-1)?.sourceId;
+    expect(sourceId).toBeDefined();
+    expect(['first', 'second']).toContain(sourceId as string);
+  });
+
+  test('empty-string id row → resolver propagates as "" (defensive)', async () => {
+    // Schema has id as PRIMARY KEY (NOT NULL), so NULL id can't happen.
+    // Empty string CAN be inserted, and the resolver's `rows[0]?.id`
+    // would treat any falsy id as "no source" via the optional chain.
+    // This test pins the current behavior (we DO pass '' through to
+    // performSync) so a future refactor doesn't silently regress it.
+    await (sharedEngine as any).db.query(
+      `INSERT INTO sources (id, name, local_path) VALUES ('', 'empty', '/tmp/brain-475-f')`,
+    );
+    await runCycle(sharedEngine, { brainDir: '/tmp/brain-475-f' });
+    expect(syncCalls.at(-1)?.sourceId).toBe('');
+  });
+});
@@ -0,0 +1,108 @@
+import { describe, it, expect } from 'bun:test';
+import { parseExpectedColumns, simplifyColumnDef } from '../src/core/schema-verify.ts';
+
+describe('parseExpectedColumns', () => {
+  it('extracts columns from all major tables', () => {
+    const columns = parseExpectedColumns();
+
+    // Should find columns from known tables
+    const tables = new Set(columns.map(c => c.table));
+    expect(tables.has('pages')).toBe(true);
+    expect(tables.has('content_chunks')).toBe(true);
+    expect(tables.has('links')).toBe(true);
+    expect(tables.has('sources')).toBe(true);
+    expect(tables.has('minion_jobs')).toBe(true);
+    expect(tables.has('files')).toBe(true);
+
+    // Should find specific columns that have historically been missed by PgBouncer
+    const columnKeys = new Set(columns.map(c => `${c.table}.${c.column}`));
+    expect(columnKeys.has('content_chunks.symbol_type')).toBe(true);
+    expect(columnKeys.has('content_chunks.start_line')).toBe(true);
+    expect(columnKeys.has('content_chunks.end_line')).toBe(true);
+    expect(columnKeys.has('content_chunks.parent_symbol_path')).toBe(true);
+    expect(columnKeys.has('content_chunks.doc_comment')).toBe(true);
+    expect(columnKeys.has('content_chunks.symbol_name_qualified')).toBe(true);
+    expect(columnKeys.has('content_chunks.search_vector')).toBe(true);
+
+    // pages columns
+    expect(columnKeys.has('pages.slug')).toBe(true);
+    expect(columnKeys.has('pages.source_id')).toBe(true);
+    expect(columnKeys.has('pages.page_kind')).toBe(true);
+    expect(columnKeys.has('pages.search_vector')).toBe(true);
+
+    // sources columns
+    expect(columnKeys.has('sources.chunker_version')).toBe(true);
+  });
+
+  it('does not include CONSTRAINT lines as columns', () => {
+    const columns = parseExpectedColumns();
+    const colNames = columns.map(c => c.column);
+
+    // These are constraint names, not column names
+    expect(colNames).not.toContain('constraint');
+    expect(colNames).not.toContain('unique');
+    expect(colNames).not.toContain('check');
+    expect(colNames).not.toContain('primary');
+    expect(colNames).not.toContain('foreign');
+  });
+
+  it('returns non-empty definitions for all columns', () => {
+    const columns = parseExpectedColumns();
+    for (const col of columns) {
+      expect(col.definition.length).toBeGreaterThan(0);
+    }
+  });
+});
+
+describe('simplifyColumnDef', () => {
+  it('strips REFERENCES clauses', () => {
+    const result = simplifyColumnDef(
+      "TEXT NOT NULL DEFAULT 'default' REFERENCES sources(id) ON DELETE CASCADE"
+    );
+    expect(result).toBe("TEXT NOT NULL DEFAULT 'default'");
+  });
+
+  it('strips CHECK constraints', () => {
+    const result = simplifyColumnDef(
+      "TEXT NOT NULL DEFAULT 'markdown' CHECK (page_kind IN ('markdown','code'))"
+    );
+    expect(result).toBe("TEXT NOT NULL DEFAULT 'markdown'");
+  });
+
+  it('preserves simple type + NOT NULL + DEFAULT', () => {
+    const result = simplifyColumnDef("INTEGER NOT NULL DEFAULT 0");
+    expect(result).toBe("INTEGER NOT NULL DEFAULT 0");
+  });
+
+  it('strips UNIQUE keyword', () => {
+    const result = simplifyColumnDef("TEXT NOT NULL UNIQUE");
+    expect(result).toBe("TEXT NOT NULL");
+  });
+
+  it('handles complex REFERENCES with ON DELETE and ON UPDATE', () => {
+    const result = simplifyColumnDef(
+      "INTEGER NOT NULL REFERENCES pages(id) ON DELETE CASCADE"
+    );
+    expect(result).toBe("INTEGER NOT NULL");
+  });
+
+  it('handles bare type', () => {
+    const result = simplifyColumnDef("TEXT");
+    expect(result).toBe("TEXT");
+  });
+
+  it('handles vector type', () => {
+    const result = simplifyColumnDef("vector(1536)");
+    expect(result).toBe("vector(1536)");
+  });
+
+  it('handles TSVECTOR type', () => {
+    const result = simplifyColumnDef("TSVECTOR");
+    expect(result).toBe("TSVECTOR");
+  });
+
+  it('handles array types', () => {
+    const result = simplifyColumnDef("TEXT[]");
+    expect(result).toBe("TEXT[]");
+  });
+});
@@ -1 +1 @@
 .22.4
 .22.5