app/db/session.py

"""SQLAlchemy engine, session factory, and an explicit schema-creation helper.

The engine is built from `settings.DATABASE_URL` (`app.config`, already
fail-loud-validated at import time) -- no credentials are hardcoded or
logged anywhere in this module. Connection-failure messages name the
database host/port/name being targeted but intentionally never include
`DB_PASSWORD` or the raw `DATABASE_URL`.

`create_all()` is NOT called at import time or anywhere else in this module
automatically. Table creation is a deliberate, explicit step gated on human
confirmation of `migrations/schema.sql` (DB-02) -- importing this module
must never mutate the database.
"""

from __future__ import annotations

from collections.abc import Generator
from contextlib import contextmanager

from sqlalchemy import create_engine
from sqlalchemy.engine import Engine
from sqlalchemy.exc import SQLAlchemyError
from sqlalchemy.orm import Session, sessionmaker

from app.config import settings
from app.db.models import Base

engine: Engine = create_engine(settings.DATABASE_URL, pool_pre_ping=True, future=True)

SessionLocal = sessionmaker(bind=engine, autoflush=False, autocommit=False, future=True)


def get_session() -> Generator[Session, None, None]:
    """FastAPI-dependency-style session generator.

    Usage: `db: Session = Depends(get_session)` in a router. Closes the
    session after the request regardless of outcome.
    """
    session = SessionLocal()
    try:
        yield session
    finally:
        session.close()


@contextmanager
def session_scope() -> Generator[Session, None, None]:
    """Context-manager session for scripts (training/, one-off maintenance)
    that run outside FastAPI's dependency-injection system. Commits on
    success, rolls back on any exception, always closes.
    """
    session = SessionLocal()
    try:
        yield session
        session.commit()
    except Exception:
        session.rollback()
        raise
    finally:
        session.close()


def create_all() -> None:
    """Create every table defined on `Base.metadata` against the configured
    MySQL database.

    This is the explicit, human-approved migration step for phase 01-02
    (DB-02): only invoke this after `migrations/schema.sql` has been
    reviewed and confirmed by the user. Never called automatically by
    importing this module.

    Raises `RuntimeError` with a clear, credential-free message if the
    database is unreachable or rejects the DDL -- the password is never
    included, only the host/port/database name being targeted.
    """
    try:
        Base.metadata.create_all(bind=engine)
    except SQLAlchemyError as exc:
        raise RuntimeError(
            "Could not create the database schema: the configured MySQL "
            f"database (host={settings.DB_HOST}, port={settings.DB_PORT}, "
            f"db={settings.DB_NAME}) is unreachable or rejected the "
            "operation. Check DB_HOST/DB_PORT/DB_NAME/DB_USER/DB_PASSWORD "
            "in .env -- credential values are intentionally omitted from "
            "this message."
        ) from exc