Spaces:
Running
Running
| """ | |
| 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. | |
| 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 | |