🧭 Epic - MariaDB Support (alongside PostgreSQL & SQLite)

This feature validates that SQLAlchemy and Alembic migrations are implemented correctly, and provides an additional baseline to compare performance with.

🧭 Type of Feature

• Database portability
• DevOps / CI
• Documentation

🙋‍♂️ User Stories

🔧 Task Breakdown

1. SQLAlchemy & Drivers

   • Pin mariadb>=1.1.4 (connector) in requirements.in.
   • Update config.py to accept DB_DRIVER env var default → mariadb+mariadbconnector.

2. Alembic Dialect Support

   • Add MariaDB branch in alembic/env.py (engine.dialect.name == "mariadb").
   • Replace postgresql.UUID → String(36) if dialect is MariaDB.
   • Migrate JSONB → JSON type; fallback to TEXT for 10.3-.

3. Schema Fix-ups

   • Shorten FK names (<= 64 chars) via naming_convention for MariaDB.
   • Replace ARRAY columns with comma-separated VARCHAR or link-table.

4. Test Matrix

   • GitHub Actions matrix { DB: sqlite, postgres, mariadb }.
   • GitHub Service container: mariadb:10.6, env MARIADB_ROOT_PASSWORD: test.
   • Parametrise pytest fixture to spin engine per DB.

5. Docker Compose

   • docker-compose.mariadb.yml adds:

     mariadb:
       image: mariadb:10.6
       environment:
         MARIADB_ROOT_PASSWORD: gateway
       ports: ["3306:3306"]

   • Gateway service gets DB_URL: mariadb+mariadbconnector://root:gateway@mariadb:3306/gateway.

6. Helm Chart

   • values.yaml →

     db:
       type: mariadb  # postgres|sqlite|mariadb
       host: mariadb
       port: 3306
       user: gateway
       password: gateway

   • Conditional template renders driverClassName, liveness query.

7. Documentation

   • “Supported Databases” page: sample URLs, limitations (no partial JSONPath index, FK length).
   • README quick-start snippet for Compose & Helm.

8. Migration Guide

   • Doc section: “Migrating Postgres → MariaDB” (export/import with mysqldump caveats).

9. Observability

   • DB metrics labels include engine="mariadb".

10. Release Notes & Changelog

    • Note new driver dependency and env vars.

📓 Implementation Notes / Caveats

• JSON – MariaDB ≥10.5 stores JSON as LONGTEXT with type validation; no JSONB indexing.
• UUIDs – stored as CHAR(36); minor perf hit vs native uuid in Postgres.
• Transactions – default engine InnoDB; tests enforce READ COMMITTED isolation.
• Max key length – 3072 bytes (utf8mb4) ⇒ watch composite indexes.

🔄 Alternatives Considered

✅ Definition of Done

• All unit & integration tests pass on sqlite-latest, postgres-16, mariadb-10.6 in CI.
• docker compose -f docker-compose.mariadb.yml up starts without manual tweaks.
• Helm chart with db.type=mariadb deploys successfully on kind.