app/database.py

"""
Database Client Setup
=====================

What:
    Creates the shared Supabase client and exposes a small accessor function
    for the rest of the backend.

How:
    Environment variables are loaded once at import time, then used to build
    a single Supabase client. Other modules call `get_db()` instead of
    importing the client directly so access stays consistent.

What it does:
    - Loads `SUPABASE_URL` and `SUPABASE_KEY` from the environment
    - Creates the shared Supabase client
    - Exposes `get_db()` as the standard database client accessor

What it does NOT do:
    - Does not define repositories or services
    - Does not contain business logic
    - Does not execute domain queries itself
"""
import os
import threading
from dotenv import load_dotenv
from httpx import Client as HttpxClient
from supabase import create_client, Client
from supabase.lib.client_options import SyncClientOptions

load_dotenv()

url: str = (os.environ.get("SUPABASE_URL") or "").strip()
key: str = (
    os.environ.get("SUPABASE_KEY")
    or os.environ.get("SUPABASE_SERVICE_ROLE_KEY")
    or ""
).strip()


def create_db_client() -> Client:
    """Create a fresh Supabase client using the backend service key."""
    if not url:
        raise RuntimeError("SUPABASE_URL is required.")
    if not key:
        raise RuntimeError("SUPABASE_KEY or SUPABASE_SERVICE_ROLE_KEY is required.")

    # Ignore machine-level proxy env vars for Supabase calls.
    # This project has been run in environments where HTTP(S)_PROXY points to
    # a dead local proxy, which breaks every database request with WinError 10061.
    http_client = HttpxClient(trust_env=False)
    options = SyncClientOptions(httpx_client=http_client)
    return create_client(url, key, options=options)


# Expose a default instance for tests or single-threaded use cases
supabase: Client = create_db_client()

# Thread-local storage to prevent httpx.Client from being shared across FastAPI worker threads.
# Sharing a single httpx.Client concurrently causes "Server disconnected" and RemoteProtocolError.
_local = threading.local()


def get_db() -> Client:
    """
    Factory function that returns a thread-local Supabase client.
    Use this instead of importing `supabase` directly to avoid
    concurrent connection pool errors.
    """
    if not hasattr(_local, "client"):
        _local.client = create_db_client()
    return _local.client