[Audit] Add schema.sql<->Alembic parity gate; close gin-index/embedding/nullability drift

[stranske]

Why

Prod Postgres bootstraps from schema.sql (compose initdb mount); CI/tests bootstrap from alembic upgrade head on SQLite. There is no gate comparing the two, so CI validates a schema that differs from production. Verified divergences at e5764a5:

• idx_news_items_topics_gin exists in schema.sql:160 but is absent from every Alembic migration (0 hits) — topic-array query support present in compose Postgres, missing on Alembic-bootstrapped Postgres.
• documents.embedding is added Postgres-only in alembic/versions/001_canonical_schema.py:170 (ALTER TABLE documents ADD COLUMN ... vector(384)); the SQLite Alembic path lacks it and embeddings.py patches it at runtime (see related [Audit] Remove Postgres DDL from store_document runtime path #1281) — so Alembic-SQLite schema ≠ schema.sql.
• chat_feedback.created_at: schema.sql declares it NOT NULL DEFAULT now(), but alembic/versions/009_chat_feedback.py:30 sets nullable=True.

Scope

Add a parity gate that fails CI when schema.sql and the Alembic-produced schema diverge on Postgres, and close the verified gaps above.

Non-Goals

• Do NOT remove dialect-legitimate differences (e.g. GIN/vector are Postgres-only; SQLite equivalents may be skipped — but the gate must record them as intentional).
• Do NOT change the existing scripts/check_dialect_portability.py token gate; this is a complementary structural parity check.

Tasks

• Add a parity check (script or test) that bootstraps an ephemeral Postgres two ways — schema.sql vs alembic upgrade head — and diffs tables/columns/indexes/nullability, failing on undocumented divergence.
• Add idx_news_items_topics_gin via a dialect-guarded Alembic migration (Postgres postgresql_using='gin'; skip/equivalent on SQLite).
• Set created_at nullable=False in alembic/versions/009_chat_feedback.py:30 to match schema.sql.

Acceptance Criteria

• New parity gate passes and fails if a column/index/nullability is present in one bootstrap path but not the other (without an allowlist entry).
• SELECT 1 FROM pg_indexes WHERE indexname='idx_news_items_topics_gin' returns a row on both bootstrap paths.
• Deliberate-break demonstration: drop the gin index from schema.sql (or revert the migration) → parity gate fails; restore → passes.

Implementation Notes

Audit baseline: main @ e5764a5 on 2026-06-28. All three divergences source-verified. This is the LMS-class "SQLite-CI vs Postgres-prod" risk: tests pass on a schema prod doesn't run.

[agents-workflows-bot]

✅ Capability Check: Agent can proceed

Recommendation: PROCEED

✅ Actionable Tasks (3):

• Add a parity check (script or test) that bootstraps an ephemeral Postgres two ways — schema.sql vs alembic upgrade head — and diffs tables/columns/indexes/nullability, failing on undocumented divergence.
• Add idx_news_items_topics_gin via a dialect-guarded Alembic migration (Postgres postgresql_using='gin'; skip/equivalent on SQLite).
• Set created_at nullable=False in alembic/versions/009_chat_feedback.py:30 to match schema.sql.

⚠️ Partial Tasks (3):

• Acceptance criterion: New parity gate passes and fails if a column/index/nullability is present in one bootstrap path but not the other (without an allowlist entry).
  • Limitation: Agent can implement the gate and add automated tests or a runnable script to verify pass/fail behavior, but cannot guarantee execution in CI if workflow changes are required because .github/workflows/*.yml is protected.
• Acceptance criterion: SELECT 1 FROM pg_indexes WHERE indexname='idx_news_items_topics_gin' returns a row on both bootstrap paths.
  • Limitation: Agent can add the migration, update schema/bootstrap artifacts, and add tests/assertions for both paths, but cannot independently verify against an external Postgres service requiring credentials. Verification must rely on repo-local test infrastructure or existing CI environment.
• Acceptance criterion: Deliberate-break demonstration: drop the gin index from schema.sql (or revert the migration) → parity gate fails; restore → passes.
  • Limitation: Agent can create a test that simulates divergence and proves the parity gate detects it, but should not leave the repository in a deliberately broken state. If the request expects a manual before/after demonstration in CI history, a human may need to review or run it.

👤 Human Actions Needed:

• Confirm whether the repository already has a usable local/CI Postgres mechanism for ephemeral database tests; if not, a human may need to provide the expected execution environment.
• If the parity gate must run automatically in CI and no existing workflow already covers it, a human must wire it into CI because workflow files are protected.
• Review any intentional divergence allowlist semantics if undocumented schema differences exist and require product/maintainer approval.

---

Auto-generated by capability check

[stranske]

Reopening for verifier sequencing: PR #1332 has merged and now carries verify:compare, but this source issue should remain open until the durable verifier/provider comparison report is PASS and no disposition debt remains.

[stranske]

Closer verifier disposition for merged PR #1332: treating the provider-comparison CONCERNS result as a false positive against current main, not opening a follow-up PR.

Evidence checked live after merge:

• PR Add schema SQL and Alembic parity gate #1332 is merged at 019b2d9e1b8fd0055df458bfb4eee22f10892be9 and carries verify:compare.
• The actual Add schema SQL and Alembic parity gate #1332 diff is schema-parity scoped: Alembic migrations, schema.sql, and schema/bootstrap tests. It does not include the unrelated rate-limit/numeric/alert/search/ETL files named by the verifier report.
• gh pr checks 1332 shows CodeRabbit, Gate, Python 3.12, Python 3.13, lint-ruff, schema-verifier, and verifier / Run post-merge verifier passed.
• Current main has idx_news_items_topics_gin in canonical schema and migration paths with CREATE INDEX IF NOT EXISTS, including 010_schema_sql_parity_fixes.py for already-migrated databases.
• Current main has chat_feedback.created_at as non-null/defaulted in the canonical schema and Alembic path.
• Current tests/test_schema_postgres_bootstrap.py compares schema.sql and Alembic bootstraps across tables, columns/nullability/defaults, indexes, constraints, and materialized views, and reports exact only-in-one-side differences.

No remaining review, verifier, or bounded follow-up debt remains for #1308.

[stranske]

Closing after evidence-backed verifier disposition on merged PR #1332; no remaining follow-up debt.