app/database.py

from sqlalchemy.ext.asyncio import create_async_engine, async_sessionmaker, AsyncSession
from sqlalchemy.orm import DeclarativeBase
from app.core.config import get_settings

settings = get_settings()

# asyncpg accepts a string SSL mode directly; omit the key entirely when
# SSL is disabled so that plain-TCP local containers keep working.
_connect_args: dict = {}
if settings.db_ssl_mode != "disable":
    _connect_args["ssl"] = settings.db_ssl_mode

engine = create_async_engine(
    settings.database_url,
    echo=settings.debug,
    pool_pre_ping=True,
    pool_size=settings.db_pool_size,
    max_overflow=settings.db_max_overflow,
    pool_timeout=settings.db_pool_timeout,
    pool_recycle=settings.db_pool_recycle,
    connect_args=_connect_args,
    # Snapshot isolation: PostgreSQL READ COMMITTED already prevents dirty
    # reads; REPEATABLE READ additionally prevents non-repeatable AND phantom
    # reads, so every request sees a single consistent snapshot. Write/write
    # conflicts surface as serialization failures (SQLSTATE 40001), which the
    # API maps to a retryable 409 (see app.main).
    isolation_level="REPEATABLE READ",
)

AsyncSessionLocal = async_sessionmaker(
    engine,
    class_=AsyncSession,
    expire_on_commit=False,
)


class Base(DeclarativeBase):
    pass


async def get_db() -> AsyncSession:
    async with AsyncSessionLocal() as session:
        try:
            yield session
        finally:
            await session.close()