Problem

PgBouncer transaction-mode poolers can silently swallow ALTER TABLE statements during migrations. The SQL executes without error, but the column never materializes in the database. The migration system increments the schema version counter anyway, creating a dangerous desync: the application thinks it's on v29 but the actual table is missing columns.

This caused production embed failures when the embed handler tried to INSERT into symbol_type, start_line, end_line, parent_symbol_path columns that didn't exist on the content_chunks table.

Error Log

PostgresError: column "symbol_type" of relation "content_chunks" does not exist
    at ErrorResponse (.../postgres.js)
    at handle (.../postgres.js)

The schema version in config read v29 (latest), but information_schema.columns showed the v26/v27 columns (symbol_type, start_line, end_line, parent_symbol_path, doc_comment, symbol_name_qualified, search_vector) were absent from the actual table. The migrations had "succeeded" — PgBouncer acknowledged every statement — but ALTER TABLE was a no-op behind the pooler.

What We Tried

1. Re-running gbrain apply-migrations — no-op because schema version already at v29
2. Manually running the ALTER TABLE statements against direct Postgres (not PgBouncer) — worked, confirming the pooler was the issue
3. Considered adding a migration that re-runs the ALTERs — but this doesn't prevent future occurrences

Solution

Add a verifySchema() function (src/core/schema-verify.ts) that runs AFTER all migrations complete in PostgresEngine.initSchema(). It:

1. Parses the canonical schema from schema-embedded.ts — both CREATE TABLE blocks and ALTER TABLE ADD COLUMN IF NOT EXISTS statements
2. Queries information_schema.columns for the actual database state
3. Diffs expected vs actual columns (skipping tables that don't exist yet)
4. Self-heals missing columns via ALTER TABLE ADD COLUMN IF NOT EXISTS with simplified definitions (strips FKs, CHECKs, UNIQUE that can't go in ADD COLUMN)
5. Throws with actionable diagnostics if any column can't be healed

Behavior matrix

Files changed

• src/core/schema-verify.ts (new) — parseExpectedColumns(), simplifyColumnDef(), verifySchema()
• src/core/postgres-engine.ts — calls verifySchema(this) at end of initSchema()
• src/core/db.ts — re-exports verifySchema for backward-compat module-level callers
• test/schema-verify.test.ts (new) — 12 tests covering parser + simplifier
• package.json — version bump to 0.22.6
• CHANGELOG.md — release notes

Testing

• 12 unit tests covering schema parser and definition simplifier
• Parser correctly extracts all columns from all tables including the production-failing ones (symbol_type, start_line, end_line, parent_symbol_path, search_vector)
• Typecheck passes clean
• simplifyColumnDef correctly handles: REFERENCES with ON DELETE/UPDATE, nested CHECK constraints, UNIQUE, vector types, array types, TSVECTOR

^{Need help on this PR? Tag @codesmith with what you need.}

• Let Codesmith autofix CI failures and bot reviews