Complete Stage 7 API v1 SQLAlchemy and Alembic migration

[anth-volk]

Goal

Complete the Stage 7 migration of API v1 persistence to canonical SQLAlchemy 2 ORM and Alembic usage without changing the existing database schema or API v1 contracts.

Scope

• Map the existing API v1 schema with typed SQLAlchemy models.
• Pass caller-owned Sessions into persistence services and define transaction boundaries at application operations.
• Let SQLAlchemy own Engine pooling, Sessions, transactions, and JSON conversion.
• Add an autogenerated Alembic baseline and model-agnostic migration guidance.
• Remove all DAO, unit-of-work, session-manager, and raw database-facade compatibility layers.
• Preserve legacy API v1 response shapes only at route serialization boundaries while keeping native JSON objects internally.
• Provide an opt-in, read-only metadata comparison for explicitly supplied existing schemas.
• Verify application startup without MySQL DDL and process-owned resource cleanup.

Acceptance criteria

• Direct Session/model and API regression tests pass.
• Existing-schema comparison is read-only and reports metadata drift without mutation.
• The full API regression suite passes.
• AI guidance prohibits manually authored Alembic revision scripts.
• The local SQLite initializer is documented as temporary and tracked for replacement by a traditional cache in a later stage.