chore(dev): detect and resolve local dev database schema drift at startup

[parthrohit22]

Problem

In development, AUTO_CREATE_TABLES=true uses SQLAlchemy create_all, which creates missing tables but never alters existing ones and never advances the Alembic alembic_version stamp. After a schema-changing merge, an existing dev DB silently drifts and requests fail at runtime with opaque 500s.

Evidence (reproduced 2026-07-25)

After migration 0007_remove_data_source merged, POST /repositories/github returned 500 NOT NULL constraint failed: repositories.data_source. The DB's alembic_version was stamped at 0005 while the 0006 analysis_jobs table physically existed (create_all built it without stamping), so a plain alembic upgrade head also crashed with table analysis_jobs already exists. Recovery required alembic stamp 0006 then alembic upgrade head.

Proposed behaviour

• On startup in development/test, compare the DB's current Alembic revision to head (use Alembic ScriptDirectory/MigrationContext APIs, do not shell out).
• If behind head: either auto-upgrade head (logging what ran) or fail fast with an actionable message naming current rev, head rev, and the exact command. Justify the chosen approach in the PR.
• Tolerate the create_all-without-stamp state: detect via a cheap physical probe (inspect for a table from a later migration) and, if drift is detected, print the stamp-then-upgrade recovery rather than crashing into "table already exists".
• After create_all on a fresh DB, ALSO stamp alembic_version to head so the drift check stays truthful going forward.
• Production/staging unchanged: no auto-migration outside development/test; Compose (AUTO_CREATE_TABLES=false) unaffected. Dialect-agnostic (SQLite + PostgreSQL).

Acceptance criteria

• Fresh DB with AUTO_CREATE_TABLES starts and is stamped at head.
• Behind-head DB auto-upgrades or fails fast per chosen approach.
• Stamp-mismatch/physical-drift case yields an actionable error, no crash loop.
• Backend tests cover all three cases; apps/backend/README.md documents behaviour + manual recovery.

Priority

P1 - High (bites every contributor with an existing dev DB after every future migration).

[parthrohit22]

Duplicate of #166, which was already resolved and merged via PR #167 (app/core/schema_sync.py — dev schema-drift detection at startup). Closing as a duplicate; #167 delivers exactly this scope. Filed in error during a batch without checking existing closed issues first.