feat(db): adopt Alembic for PostgreSQL migrations (retire SQLite-only bespoke runner)

[dolho]

Summary

Adopt Alembic as the migration system for the PostgreSQL backend, replacing the SQLite-only bespoke runner (db/migrations.py: PRAGMA table_info + INSERT OR IGNORE + raw ALTER) and the fresh-build-from-schema.py path (init_schema_postgres). This is the migration story for the Postgres-only future — SQLite is being dropped, so we do not invest in dual-backend Alembic; SQLite keeps its legacy bespoke path until it is removed.

Long-term goal: db/tables.py SQLAlchemy MetaData becomes the single source of truth, ending the current 3-way hand-sync (schema.py CREATE strings / tables.py Core handles / migrations.py ALTERs). Relates to #746 (collapse schema.py + migrations.py), #1160 (migration runner data-loss / no cross-process serialization), #721 (schema drift), #300 (the Core abstraction this builds on).

Motivation

• No in-place upgrade path for existing Postgres DBs today. init_schema_postgres only CREATE ... IF NOT EXISTS — a pre-existing PG database never picks up columns added later. Fresh-from-head only.
• The bespoke runner is SQLite-bound (PRAGMA, INSERT OR IGNORE, sqlite3.OperationalError), has a DROP-rebuild data-loss window and no cross-process serialization (fix(db): migration runner — DROP-rebuild data-loss window and no cross-process serialization #1160), and forces triple-maintenance per schema change.
• Alembic is SQLAlchemy-native: dialect-aware DDL, ordered revisions with down-migrations, autogenerate diffing live DB vs tables.py metadata, and a battle-tested version table + locking story.

Two migration domains (must preserve)

src/backend/enterprise/ is a separate (private) repo with its own migration system (backend/_migrations.py):

• Separate tracking table enterprise_schema_migrations; owns only enterprise_* tables; never ALTERs an OSS table; runs from register_enterprise() AFTER OSS init_database.
• Already mimics Alembic versions/ style (discover() + module-prefixed names like siem_0001_…).

Alembic adoption must keep the two domains isolated:

• OSS revisions in this (public) repo, version table alembic_version.
• Enterprise revisions in the private enterprise repo, separate version table (e.g. alembic_version_enterprise) / branch label, applied after OSS at startup. No enterprise DDL in this repo.

Acceptance Criteria (phased)

Phase 1 — OSS Alembic foundation (this repo)

• Add alembic dependency (backend image + scheduler image).
• alembic.ini + migrations/env.py wired to db/tables.py metadata as target_metadata; offline + online modes; URL from DATABASE_URL.
• Baseline revision capturing the full current head schema (so a fresh PG DB is built by alembic upgrade head, not init_schema_postgres).
• init_database() Postgres branch runs alembic upgrade head (OSS) instead of init_schema_postgres; SQLite branch unchanged (legacy bespoke path retained).
• Existing-PG-DB stamp path: alembic stamp a DB already at head-schema so it isn't rebuilt.
• Tests: env config loads; baseline upgrade head then downgrade base round-trips on a throwaway PG (CI dual-backend job / TEST_POSTGRES_URL).
• Docs: architecture.md migration section + docs/POSTGRESQL_SETUP.md.

Phase 2 — Enterprise domain (private repo, separate PR)

• Enterprise Alembic env with its own version table/branch, discovered + applied from register_enterprise() after OSS upgrade. Revisions live in the enterprise repo only.

Phase 3 — Single source of truth + SQLite retirement (later, with #746)

• New schema changes authored as Alembic autogenerate revisions from tables.py metadata.
• Retire schema.py TABLES fresh-build + db/migrations.py once SQLite is dropped; collapse to tables.py + Alembic.

Interim workaround (accepted)

During the transition, a schema change may need both an Alembic revision (Postgres) and a legacy db/migrations.py entry (SQLite) until SQLite is removed. Acceptable short-term; tracked for cleanup in Phase 3.

Technical Notes

• Build on the MetaData from db/tables.py introduced in Configurable database backend: SQLAlchemy Core abstraction (SQLite + PostgreSQL) #300 — it already lists every table.
• A few existing bespoke migrations do data backfills and SQLite table-rebuilds (fake column-drop); those are SQLite-only and do not need porting (SQLite is going away). The Alembic baseline is the head schema, not a replay of history.
• Multi-worker / multi-process startup: rely on Alembic's version table; add a Postgres advisory lock around upgrade if needed (addresses fix(db): migration runner — DROP-rebuild data-loss window and no cross-process serialization #1160's serialization gap).
• Cross-repo: enterprise changes ship in the private enterprise repo, not here.

[dolho]

Relates to #746 (single source of truth), #1160 (runner serialization/data-loss), #721 (schema drift), #300 (SQLAlchemy Core foundation). Direction: Postgres-only future — SQLite keeps its legacy bespoke path until removed.