Make Alembic the canonical SQLite migration path

[amjith]

Goal

Make database schema changes predictable during VM deploys by moving schema ownership to Alembic and making deployment/startup run the migration path explicitly.

The repo already has Alembic scaffolding (alembic.ini, migrations/env.py, and migrations/versions/0001 through 0007), but the current runtime path still relies on custom startup repair code:

• symphony-dbcli init-db calls Store.init() and create_model_tables(...).
• FastAPI startup also calls Store.init() and create_model_tables(...).
• Store.init() creates raw-SQL legacy tables and runs _migrate(...) for additive column repairs.
• create_model_tables(...) calls SQLAlchemy Base.metadata.create_all(...) and then repair_sqlite_model_tables(...).
• scripts/provision-exedev-vm.sh runs uv run symphony-dbcli --profile prod init-db before installing/restarting the service, but the generated systemd unit does not run migrations on every restart.

We should make init-db the single blessed migration command, backed by Alembic, and then have the VM service run it safely before serve starts.

Non-goals

• Do not rewrite all Store data access to SQLAlchemy ORM as part of this issue.
• Do not remove the existing SQLite DB or require manual data export/import.
• Do not change the configured DB location.
• Do not mix unrelated dashboard/runtime behavior changes into the migration work.

Phase 1: Schema inventory and migration plan

Produce a short implementation note or PR description that maps every existing schema owner to the future Alembic owner.

Inventory these areas:

• Raw SQLite tables in src/symphony_dbcli/store.py SCHEMA.
• Legacy repairs in src/symphony_dbcli/store.py _migrate(...).
• SQLAlchemy models in src/symphony_dbcli/models.py.
• SQLAlchemy repair entries in _SQLITE_COLUMN_REPAIRS.
• Existing Alembic migrations in migrations/versions.
• The init-db path in src/symphony_dbcli/cli.py.
• The FastAPI startup path in src/symphony_dbcli/web/app.py.
• The generated systemd unit in scripts/provision-exedev-vm.sh.

Progress checkpoint:

• A reviewer can tell which tables are already covered by Alembic and which tables still depend on Store.init() / repair logic.
• The plan states whether each existing custom repair will be kept temporarily, moved into Alembic, or retired.

Phase 2: Make Alembic runnable from the app config

Make Alembic use the same database path as symphony-dbcli --profile ... init-db.

Expected work:

• Add a small internal migration runner or helper that resolves WorkflowConfig.database.path and runs alembic upgrade head against that SQLite DB.
• Avoid requiring operators to manually construct SYMPHONY_DATABASE_URL.
• Keep this helper usable from tests without shelling out when practical.
• Ensure the migration command is idempotent.

Progress checkpoint:

• A test can run Alembic against a temporary empty SQLite DB using the same config path resolution that production uses.
• Running the migration command twice succeeds without changing data or failing.

Phase 3: Move SQLAlchemy-managed table changes fully under Alembic

Bring the SQLAlchemy model tables into a migration-first workflow.

Expected work:

• Verify the current Alembic head creates/updates the SQLAlchemy-managed tables to match the current models.
• Move any active _SQLITE_COLUMN_REPAIRS entries for model tables into Alembic revisions where they are still needed.
• Add tests that compare the schema from a fresh Alembic upgrade with the expected model columns/indexes.
• Decide how to handle existing DBs that may already have columns added by repair code before Alembic knows about them.

Progress checkpoint:

• Fresh DB + alembic upgrade head yields the expected source/work-item/chat schema.
• Existing DBs with previously repaired columns can still upgrade cleanly.

Phase 4: Cover legacy Store tables with handwritten Alembic migrations

Alembic does not require ORM models, so keep Store as the data access layer for now and add handwritten migrations for its raw-SQL tables.

Expected work:

• Create Alembic revisions for the legacy tables currently created by Store.SCHEMA.
• Create Alembic revisions for the additive repairs currently in Store._migrate(...).
• Preserve existing data when upgrading a populated DB.
• Add a test fixture for an older/minimal DB and verify upgrade to head adds missing columns.

Progress checkpoint:

• A fresh DB migrated with Alembic has both the legacy Store tables and SQLAlchemy-managed tables.
• A legacy DB missing columns such as worker heartbeat/deadline or PR cleanup columns upgrades to head successfully.
• No broad Store ORM rewrite is required.

Phase 5: Make init-db the migration facade

Change symphony-dbcli init-db so operators use one command for schema setup and upgrade.

Expected work:

• Have init-db run Alembic migrations to head.
• Keep any required transitional compatibility behavior only where necessary and clearly mark it as temporary.
• Ensure workflow version loading still happens after schema is ready.
• Return clear errors if migration fails.

Progress checkpoint:

• uv run symphony-dbcli --profile prod init-db upgrades the configured DB to the latest schema.
• The command remains safe to run repeatedly.
• Tests cover empty DB, current DB, and at least one older DB shape.

Phase 6: Update VM provisioning and systemd startup behavior

Make deploy/restart apply migrations before the app starts.

Expected work:

• Update scripts/provision-exedev-vm.sh so the generated systemd unit runs the migration facade before serve.
• Prefer ExecStartPre=<uv> run symphony-dbcli --profile prod init-db if it behaves cleanly under systemd.
• Keep ExecStart=<uv> run symphony-dbcli --profile prod serve --no-reload focused on serving/runtime work.
• Make failure behavior obvious: if migration fails, the service should not start with a mismatched schema.

Progress checkpoint:

• Re-running the provisioning script installs a unit that migrates on start/restart.
• systemctl restart symphony-dbcli.service applies pending migrations before launching the app.
• Failed migrations leave useful logs in journalctl.

Phase 7: Retire duplicate custom repair paths

After Alembic owns the schema, remove or narrow the old repair mechanisms.

Expected work:

• Remove redundant _migrate(...) column additions that Alembic now owns, or keep only a guarded compatibility shim with a documented removal plan.
• Remove redundant _SQLITE_COLUMN_REPAIRS entries where Alembic now handles the change.
• Avoid Base.metadata.create_all(...) silently papering over missing migrations in production paths, unless intentionally kept for tests only.
• Update docs so future schema changes require Alembic migrations.

Progress checkpoint:

• New DB columns are introduced through Alembic revisions, not hidden startup repair lists.
• Documentation tells future contributors where migrations live and how to test them.
• Quality gates pass: uv run ruff check ., uv run ruff format --check ., uv run mypy, and uv run pytest.

Suggested validation matrix

• Empty SQLite DB -> init-db -> app starts.
• Existing local DB -> init-db -> app starts and data remains readable.
• Older schema fixture -> init-db -> missing columns added and data preserved.
• VM-style path/profile -> init-db -> service start succeeds.
• Running init-db twice is a no-op.
• Migration failure prevents service start and logs a clear error.

Open questions

• Should FastAPI startup keep running migrations directly, or should production rely on ExecStartPre and fail fast if schema is stale?
• Should legacy Store tables be represented as SQLAlchemy Core Table metadata for schema clarity, without rewriting Store methods?
• Do we want a one-time Alembic stamp path for existing databases that have already been repaired by startup code?