app/database.py

"""
database.py — SQLAlchemy engine and session factory.

Two backends, selected purely by the DATABASE_URL scheme:

  postgresql+psycopg://...  Neon serverless Postgres. Real network latency and
                        Neon's autosuspend-on-idle behaviour on the compute, so
                        pool_pre_ping + pool_recycle matter -- see _make_engine()
                        below.
  sqlite:///...         Local/offline fallback for when the Neon connection
                        string is not configured. The SQLite file is EPHEMERAL
                        wherever the deploy target's disk is (e.g. a Hugging
                        Face Space container): it does not survive a restart or
                        rebuild.

The engine is built lazily. Alembic's env.py and the contract checker import
Base without wanting a connection, and a module-level engine would make those
fail at import time on a machine with no .env.
"""

from __future__ import annotations

from collections.abc import Iterator

from sqlalchemy import Engine, create_engine, event, text
from sqlalchemy.orm import DeclarativeBase, Session, sessionmaker

from app.config import settings


class Base(DeclarativeBase):
    pass


_engine: Engine | None = None
_session_factory: sessionmaker[Session] | None = None


def _ensure_sqlite_dir(url: str) -> None:
    """
    SQLite refuses to open a file whose parent directory is missing.

    urlparse().path keeps every slash after the "sqlite://" scheme separator,
    so "sqlite:///data/outreach.db" (relative, 3 slashes total) parses to
    "/data/outreach.db" and "sqlite:////app/data/outreach.db" (absolute, 4
    slashes) parses to "//app/data/outreach.db" -- in both cases stripping
    exactly the first character recovers the real path ("data/outreach.db"
    relative, "/app/data/outreach.db" absolute).
    """
    from pathlib import Path
    from urllib.parse import urlparse

    raw = urlparse(url).path
    if not raw or raw in (":memory:", "/:memory:"):
        return
    Path(raw[1:]).parent.mkdir(parents=True, exist_ok=True)


def _make_engine() -> Engine:
    url = settings.sqlalchemy_url
    if not url:
        raise RuntimeError(
            "No database configured. Set DATABASE_URL in outreach-backend/.env"
        )

    is_sqlite = url.startswith("sqlite")

    if is_sqlite:
        _ensure_sqlite_dir(url)

        # Fallback for when DATABASE_URL is not configured. SQLite keeps the
        # service usable in that case.
        #
        # pool_size/max_overflow are QueuePool-specific and not guaranteed valid
        # for whatever pool class SQLAlchemy picks for a file-based sqlite URL,
        # so they are only passed on the Postgres path below.
        #
        # check_same_thread=False: FastAPI's sync endpoints and the job
        # ThreadPoolExecutor each open sessions from their own thread; SQLite's
        # per-connection default would reject that.
        engine = create_engine(
            url,
            pool_pre_ping=True,
            future=True,
            connect_args={"check_same_thread": False},
        )

        # SQLite ignores FK constraints unless told otherwise per-connection.
        # reveal_requests has no ORM relationship (no cascade="delete-orphan"),
        # so without this pragma a deleted lead would leave its reveal rows
        # orphaned instead of the ON DELETE CASCADE the schema declares.
        @event.listens_for(engine, "connect")
        def _enable_sqlite_fk(dbapi_connection, _record):
            cursor = dbapi_connection.cursor()
            cursor.execute("PRAGMA foreign_keys=ON")
            cursor.close()

        return engine

    # DATABASE_URL may be Neon's pooled endpoint (host "ep-xxx-pooler...", PgBouncer in transaction mode) or the
    # direct one -- nothing to change here either way. Neon's pooler supports protocol-level prepared
    # statements (PgBouncer >= 1.22), which is what psycopg uses; this app takes no session-level state
    # (no SET, advisory locks or LISTEN), so it is transaction-pooling safe. Alembic is the one caller that
    # must NOT use the pooled URL -- see alembic/env.py and Settings.sqlalchemy_direct_url.
    #
    # pool_pre_ping matters here specifically because Neon's default (non-pooled)
    # compute autosuspends after 5 minutes idle; the next checkout after a
    # suspend would otherwise hand out a dead connection. pool_recycle keeps
    # connections from going stale in the pool between requests.
    #
    # pool_size/max_overflow history (2026-10-19): a burst of unconditional per-card
    # GET /leads/{id}/contacts calls on Pipeline/Contacts/Today (ContactsPanel fetched
    # on mount with no gate) exhausted this pool live (QueuePool limit of size 5
    # overflow 5 reached). Briefly raised to 20/30 as a stop-gap, then reverted back
    # here once the real fix landed -- ContactsPanel is click-gated again
    # (components/enrichment/contacts-panel.tsx), so no page fires more than one of
    # these at a time anymore and 5+5 is back to being enough headroom.
    return create_engine(
        url,
        pool_pre_ping=True,
        pool_recycle=280,
        pool_size=5,
        max_overflow=5,
        future=True,
    )


def get_engine() -> Engine:
    global _engine
    if _engine is None:
        _engine = _make_engine()
    return _engine


def SessionLocal() -> Session:  # noqa: N802 — kept callable-shaped, as before
    global _session_factory
    if _session_factory is None:
        _session_factory = sessionmaker(
            bind=get_engine(), autoflush=False, expire_on_commit=False, future=True
        )
    return _session_factory()


def get_db() -> Iterator[Session]:
    """FastAPI dependency — one session per request."""
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()


def ping() -> bool:
    with get_engine().connect() as conn:
        conn.execute(text("SELECT 1"))
    return True