db.py

"""Postgres access (Supabase). One pooled connection source for the app."""

from contextlib import contextmanager

import psycopg
from pgvector.psycopg import register_vector
from psycopg_pool import ConnectionPool

from config import get_settings

_pool: ConnectionPool | None = None


def get_pool() -> ConnectionPool:
    global _pool
    if _pool is None:
        settings = get_settings()
        if not settings.database_url:
            raise RuntimeError("DATABASE_URL missing in backend/.env")
        _pool = ConnectionPool(
            settings.database_url,
            min_size=0,
            max_size=4,
            kwargs={"connect_timeout": 20},
            configure=register_vector,
            open=True,
        )
    return _pool


@contextmanager
def connection():
    with get_pool().connection() as conn:
        yield conn


def query(sql: str, params=None) -> list[tuple]:
    with connection() as conn:
        return conn.execute(sql, params).fetchall()