Add SQLite bootstrap schema drift checks

[cmsjade5000]

Goal

Reduce drift risk between the SQLite bootstrap/repair path and the canonical SQLAlchemy/Alembic schema.

Context

api/db.py manually bootstraps and repairs SQLite schema for local deployments while Alembic owns migrations. That is useful for the self-hosted app, but it creates a risk that fresh SQLite installs differ from the schema expected by tests, models, or future migrations.

Suggested Scope

• Add a test that creates a fresh SQLite database through the bootstrap path.
• Compare expected tables, key columns, indexes, constraints, and repair-created structures to SQLAlchemy metadata where practical.
• Document intentional SQLite-specific deviations.
• Keep repair-only behavior non-destructive.
• Consider a helper that makes future schema additions easier to test.

Files or Areas

• api/db.py
• alembic/versions/
• api/models/
• tests/test_db_sqlite_constraints.py
• tests/test_db_connection.py
• Optional new focused test file for bootstrap schema drift

Out of Scope

• Replacing Alembic
• Dropping or rewriting live databases
• Bulk migrations on the live SQLite database
• Expanding legacy ETL behavior

Acceptance Criteria

• Schema drift is detected by tests before deployment.
• Intentional SQLite-specific differences are documented in the test or nearby code.
• Fresh SQLite bootstrap behavior remains safe and non-destructive.
• Tests use temporary databases only.

Verification

• pytest tests/test_db_connection.py tests/test_db_sqlite_constraints.py
• Any new focused bootstrap drift test added by the PR

Contributor Notes

This is a good testing/database issue for a careful contributor. Please use temporary SQLite files only and do not touch ~/Library/Application Support/Vault966/data/vault.db or the repository vault.db symlink.

[cmsjade5000]

Tracking parent: #202

Tags/workstream: ops, database, testing.

Dependency note: Part of the release-safety track with #198 and #200. This can land independently, but it should inform #201 before larger schema/job/source-sync refactors proceed.