Skip to content

~95% on SimpleQA (e.g. Qwen3.6-27B on a 3090). Supports all local and cloud LLMs (llama.cpp, Ollama, Google, ...). 10+ search engines - arXiv, PubMed, your private documents. Everything Local & Encrypted.

39.5 KB
Raw Blame
"""Database session lifecycle under ASGI.

Flask tore the request's DB session down in ``teardown_appcontext``,
which ran **on the request-serving thread** — the same thread that had
opened the session and stored it in a ``threading.local()``. FastAPI's
equivalent hook is ``DatabaseMiddleware.__call__``'s ``finally``, which
is ``async`` and therefore always runs on the **event-loop thread**,
while every ``def`` route handler runs on a pooled AnyIO worker. A
teardown on thread A cannot reach thread A' s local storage. That single
structural difference is the source of every property pinned here.

What each section establishes, and what breaks if it regresses:

1. **Thread affinity of the session cache.** ``_get_cached_user_session``
   is keyed ``(thread_uuid, username, namespace)``. The thread component
   is not an isolation mechanism — cross-user isolation comes from
   ``username`` being in the key, i.e. from the decorator sitting *below*
   username resolution. The thread component exists because a SQLAlchemy
   ``Session`` is not thread-safe and ``db_manager.get_session`` does
   nothing to scope one per thread. Losing it hands one ``Session`` to
   two concurrent AnyIO workers: concurrent identity-map mutation.

2. **Where request cleanup runs.** ASGI middleware still tears down on the
   loop thread, but every synchronous endpoint is wrapped by
   ``WorkerCleanupAPIRoute``.  Its ``finally`` runs on the handler worker and
   closes that worker's thread-local sessions before the response returns.

3. **Synchronous response bodies need a per-iteration boundary.** Starlette
   consumes them on AnyIO workers only after the endpoint returns. The
   production response wrapper cleans the worker after each iterator step;
   the regression test first demonstrates the leak with Starlette's bare
   response against the same real one-connection pool.

4. **Explicit async offloads need the same boundary.** ``run_db_sync``'s
   ``finally`` runs *on* the executor worker, so DB work launched from an
   async endpoint receives the same guarantee as a sync route. Verified with
   real sessions and pool accounting against a bare ``asyncio.to_thread``
   positive control on the same pinned worker thread.

5. **Connections must come back to the pool.** A per-user engine is a
   ``QueuePool(POOL_SIZE=20, MAX_OVERFLOW=40, pool_timeout=10)``. A
   session that is never closed pins its connection for as long as
   something references the session.  The request wrapper is what prevents
   uvicorn's long-lived workers from retaining those references.

6. **LRU eviction while a session is live.** Eviction cannot close a session
   because its caller may still be using it. ``get_db_session`` therefore
   keeps an owner-thread registry outside the LRU, and worker cleanup closes
   every acquired session even when its cache entry has already been evicted.

Everything here binds real production objects — the real
``thread_specific_cache``-decorated ``_get_cached_user_session``, the
real ``ThreadLocalSessionManager``, the real ``DatabaseMiddleware``, the
real ``run_db_sync``. The only substitution is ``db_manager``: a fake
that hands out **real** ``sqlalchemy.orm.Session`` objects bound to
**real** ``QueuePool`` SQLite engines, so session identity, ``close()``
and ``pool.checkedout()`` are all genuine. Only the SQLCipher key
derivation is removed.
"""

from __future__ import annotations

import asyncio
import gc
import threading
import uuid
from concurrent.futures import ThreadPoolExecutor
from contextlib import contextmanager
from unittest.mock import MagicMock, patch

import anyio.to_thread
import httpx
import pytest
from cachetools import LRUCache, cached
from fastapi import FastAPI
from sqlalchemy import create_engine, text
from sqlalchemy.exc import TimeoutError as SATimeoutError
from sqlalchemy.orm import Session, sessionmaker
from sqlalchemy.pool import QueuePool
from starlette.responses import StreamingResponse

from local_deep_research.database import thread_local_session as tls
from local_deep_research.utilities import db_utils
from local_deep_research.utilities.request_context import request_user
from local_deep_research.web.dependencies.threadpool import (
    WorkerCleanupAPIRoute,
    WorkerCleanupStreamingResponse,
    run_db_sync,
)
from local_deep_research.web.fastapi_app import DatabaseMiddleware

# Give-up deadline for every blocking rendezvous below. Never measured,
# never asserted on: it is either reached instantly (property holds) or
# burned in full (property is broken and the test fails on the barrier).
TRIP_TIMEOUT = 10.0

# The one timeout that is *expected* to be paid in full, in the pool
# exhaustion control. Kept short for that reason.
POOL_TIMEOUT = 0.5


# ---------------------------------------------------------------------------
# Harness
# ---------------------------------------------------------------------------


class _TrackingSession(Session):
    """A real ``Session`` that records whether it was closed.

    Subclassing rather than mocking keeps ``execute``, autobegin,
    ``rollback`` and pool checkout/checkin completely real — the
    behaviours the pool assertions depend on.
    """

    def __init__(self, *args, **kwargs):
        super().__init__(*args, **kwargs)
        self.close_calls = 0

    def close(self):
        self.close_calls += 1
        super().close()


class _FakeDBManager:
    """Stand-in for ``encrypted_db.db_manager``.

    Implements only the three methods the code under test calls, with
    real SQLAlchemy behind each. Every returned object is a real
    ``Session`` on a real ``QueuePool`` engine; only SQLCipher's PBKDF2
    open is removed, because it costs hundreds of milliseconds and has
    nothing to do with session *lifetime*.
    """

    def __init__(self, engines):
        self.connections = dict(engines)
        self._makers = {
            user: sessionmaker(bind=engine, class_=_TrackingSession)
            for user, engine in engines.items()
        }

    def _new(self, username):
        maker = self._makers.get(username)
        if maker is None:
            return None
        # Deliberately keeps NO registry of what it handed out: a list
        # here would keep every session reachable and silently defeat
        # the garbage-collection measurement in the last test.
        return maker()

    # db_utils._get_cached_user_session
    def get_session(self, username):
        return self._new(username)

    # ThreadLocalSessionManager.get_session
    def open_user_database(self, username, password):
        return self.connections.get(username)

    def create_thread_safe_session_for_metrics(self, username, password):
        return self._new(username)


def _make_engine(tmp_path, name, *, size=20, overflow=40, timeout=10):
    return create_engine(
        f"sqlite:///{tmp_path / (name + '.db')}",
        poolclass=QueuePool,
        pool_size=size,
        max_overflow=overflow,
        pool_timeout=timeout,
    )


@pytest.fixture
def engines(tmp_path):
    """One real per-user QueuePool engine for alice and bob."""
    made = {user: _make_engine(tmp_path, user) for user in ("alice", "bob")}
    yield made
    for engine in made.values():
        engine.dispose()


@pytest.fixture
def fake_db_manager(engines, monkeypatch):
    """Point both production db_manager references at the fake."""
    fake = _FakeDBManager(engines)
    monkeypatch.setattr(db_utils, "db_manager", fake)
    monkeypatch.setattr(tls, "db_manager", fake)
    return fake


@pytest.fixture(autouse=True)
def _isolate_session_state():
    """Keep the two process-global caches from leaking between tests.

    ``_get_cached_user_session``'s LRUCache and the global
    ``thread_session_manager`` both outlive a single test, and several
    tests here deliberately fill them.
    """

    def _reset():
        db_utils._get_cached_user_session.cache_clear()
        tls.cleanup_current_thread()
        with tls.thread_session_manager._lock:
            tls.thread_session_manager._thread_credentials.clear()
        gc.collect()

    _reset()
    yield
    _reset()


@contextmanager
def _pin_single_anyio_worker():
    """Force Starlette's ``def``-handler offload onto ONE worker thread.

    With a single token the limiter admits one handler at a time, and
    anyio's ``idle_workers.pop()`` hands back the most recently released
    worker — so consecutive requests provably share a thread instead of
    racing for one. Every test using this asserts that premise before
    asserting anything else.
    """
    limiter = anyio.to_thread.current_default_thread_limiter()
    original = limiter.total_tokens
    limiter.total_tokens = 1
    try:
        yield
    finally:
        limiter.total_tokens = original


@contextmanager
def _pin_single_default_executor(loop):
    """Same idea for ``asyncio.to_thread`` / ``run_db_sync``.

    Those do NOT use the AnyIO limiter (they use the loop's default
    ThreadPoolExecutor), so they need their own pin.
    """
    single = ThreadPoolExecutor(max_workers=1, thread_name_prefix="pinned")
    previous = getattr(loop, "_default_executor", None)
    loop.set_default_executor(single)
    try:
        yield
    finally:
        loop.set_default_executor(previous or ThreadPoolExecutor())
        single.shutdown(wait=True)


class _SessionInjector:
    """Stands in for ``SessionMiddleware``, which needs a signed cookie.

    ``DatabaseMiddleware``'s whole contract with the layer above it is
    ``scope["session"]`` being a dict, so filling it from a header drives
    the real middleware without a login round trip. Ordering matches
    production: session outside, database inside.
    """

    def __init__(self, app, sessions):
        self.app = app
        self.sessions = sessions

    async def __call__(self, scope, receive, send):
        if scope["type"] == "http":
            headers = dict(scope["headers"])
            user = headers.get(b"x-test-user", b"").decode()
            scope["session"] = (
                {"username": user, "session_id": self.sessions[user]}
                if user
                else {}
            )
        await self.app(scope, receive, send)


@pytest.fixture
def server_sessions():
    """Real server-side sessions, so ``_enforce_session_revocation``
    inside ``DatabaseMiddleware`` stays on its real code path instead of
    clearing the injected session dict."""
    from local_deep_research.web.auth.session_manager import session_manager

    made = {u: session_manager.create_session(u) for u in ("alice", "bob")}
    yield made
    for session_id in made.values():
        session_manager.destroy_session(session_id)


def _stack(handler, server_sessions, path="/probe"):
    app = FastAPI()
    app.router.route_class = WorkerCleanupAPIRoute
    app.get(path)(handler)
    return _SessionInjector(DatabaseMiddleware(app), server_sessions)


async def _get(stack, path, user):
    transport = httpx.ASGITransport(app=stack)
    async with httpx.AsyncClient(
        transport=transport, base_url="http://testserver"
    ) as client:
        return await client.get(path, headers={"x-test-user": user})


# ---------------------------------------------------------------------------
# 1. Thread affinity of the cached Session
# ---------------------------------------------------------------------------


def test_one_user_gets_a_separate_session_per_worker_thread(fake_db_manager):
    """A ``Session`` is not thread-safe, so two AnyIO workers serving the
    same user concurrently must each get their OWN session object.

    This is what the ``thread_id`` component of
    ``thread_specific_cache``'s key buys — and it is a *different*
    property from cross-user isolation, which comes from ``username``
    being in the key. The two are routinely conflated; they are pinned
    separately (see the control immediately below, and
    ``tests/security/test_cross_user_isolation_invariants.py`` for the
    username half).

    Both threads are held at a barrier so they are genuinely inside the
    accessor at the same time, which is the situation the key defends.
    """
    namespace = uuid.uuid4().hex
    barrier = threading.Barrier(2, timeout=TRIP_TIMEOUT)
    results: dict[int, tuple] = {}

    def _worker(slot):
        barrier.wait()
        first = db_utils._get_cached_user_session("alice", namespace)
        second = db_utils._get_cached_user_session("alice", namespace)
        results[slot] = (threading.get_ident(), first, second)

    with ThreadPoolExecutor(max_workers=2) as pool:
        for future in [pool.submit(_worker, i) for i in (0, 1)]:
            future.result(timeout=TRIP_TIMEOUT)

    (ident_a, first_a, second_a) = results[0]
    (ident_b, first_b, second_b) = results[1]

    assert ident_a != ident_b, (
        "premise: the two calls must land on different OS threads, "
        "otherwise this test says nothing about thread affinity"
    )
    assert first_a is not first_b, (
        "two concurrent worker threads were handed the SAME SQLAlchemy "
        "Session for one user. A Session is documented as not "
        "thread-safe: concurrent identity-map mutation and autoflush on "
        "one instance is the failure the thread key exists to prevent."
    )
    assert first_a is second_a and first_b is second_b, (
        "within one thread the accessor must return the cached session; "
        "opening a fresh SQLCipher-backed session per call is the cost "
        "this cache exists to avoid"
    )


def test_a_thread_unkeyed_cache_would_share_one_session_across_workers(
    engines,
):
    """CONTROL for the test above.

    Same factory, same two-thread harness, but wrapped in a plain
    ``cachetools.cached`` — i.e. the same cache with the ``thread_id``
    component of the key removed and nothing else changed. If this does
    NOT produce a shared session, the harness above cannot detect the
    loss of thread affinity and its clean result is meaningless.
    """
    maker = sessionmaker(bind=engines["alice"], class_=_TrackingSession)

    @cached(cache=LRUCache(maxsize=10), lock=threading.RLock())
    def _unkeyed(username):
        return maker()

    barrier = threading.Barrier(2, timeout=TRIP_TIMEOUT)
    seen: dict[int, tuple] = {}

    def _worker(slot):
        barrier.wait()
        seen[slot] = (threading.get_ident(), _unkeyed("alice"))

    try:
        with ThreadPoolExecutor(max_workers=2) as pool:
            for future in [pool.submit(_worker, i) for i in (0, 1)]:
                future.result(timeout=TRIP_TIMEOUT)

        assert seen[0][0] != seen[1][0], "premise: two distinct threads"
        assert seen[0][1] is seen[1][1], (
            "dropping the thread component from the key MUST hand one "
            "Session to both threads. It did not, so the sibling test's "
            "'sessions are distinct' assertion proves nothing."
        )
    finally:
        _unkeyed.cache_clear()
        for session in {id(v[1]): v[1] for v in seen.values()}.values():
            session.close()


def test_one_worker_serving_two_users_never_reuses_the_first_session(
    fake_db_manager,
):
    """The cache sits BELOW username resolution, so one pooled worker
    serving alice then bob then alice again gets alice's session, bob's
    session, alice's session — never a carry-over.

    This drives the public entry point ``get_db_session()`` with no
    username argument, so the contextvar resolution that
    ``DatabaseMiddleware`` performs per request is part of what is
    exercised, not stubbed out. It runs off the main thread because that
    is where sync route handlers run, and because ``get_db_session``
    has a distinct code path there (the background-thread guard).
    """
    namespace = uuid.uuid4().hex
    out = {}

    def _serve_three_requests():
        out["thread"] = threading.get_ident()
        with request_user("alice", "sid-a"):
            out["alice1"] = db_utils.get_db_session(namespace)
        with request_user("bob", "sid-b"):
            out["bob"] = db_utils.get_db_session(namespace)
        with request_user("alice", "sid-a"):
            out["alice2"] = db_utils.get_db_session(namespace)

    with ThreadPoolExecutor(max_workers=1) as pool:
        pool.submit(_serve_three_requests).result(timeout=TRIP_TIMEOUT)

    assert out["thread"] != threading.get_ident(), (
        "premise: this must exercise the worker-thread path, not "
        "MainThread's deprecated no-username branch"
    )
    assert out["bob"] is not out["alice1"], (
        "SECURITY: the worker handed bob the session it had opened for "
        "alice. This is what caching ABOVE username resolution does."
    )
    assert out["alice2"] is out["alice1"], (
        "alice's second request on the same worker must hit her own "
        "cache entry; if it does not, bob's request evicted or "
        "overwrote it and the key is not (thread, username, namespace)"
    )
    assert out["alice1"].bind is fake_db_manager.connections["alice"]
    assert out["bob"].bind is fake_db_manager.connections["bob"]


# ---------------------------------------------------------------------------
# 2. Where DatabaseMiddleware's cleanup can reach
# ---------------------------------------------------------------------------


@pytest.mark.asyncio
async def test_sync_route_cleanup_runs_on_the_handler_worker(
    server_sessions, monkeypatch
):
    """The route wrapper restores same-thread request cleanup.

    ``DatabaseMiddleware`` still cleans the event-loop thread, which cannot
    reach a sync handler's ``threading.local``. ``WorkerCleanupAPIRoute`` adds
    the missing cleanup on the handler worker. All calls are observed so a
    future refactor cannot accidentally move the only effective cleanup back
    to the loop thread.

    PREMISE UPDATE (PR #6207, follow-up to #6095): ``DatabaseMiddleware``
    now offloads ``ensure_user_database`` through ``run_db_sync`` (instead
    of a bare ``asyncio.to_thread`` that left the executor thread's
    thread-local session with no cleanup boundary at all — the leak
    #6207 closes). ``run_db_sync``'s own ``finally`` unconditionally calls
    ``cleanup_current_thread()`` on whichever asyncio default-executor
    thread it ran on, BEFORE the request is dispatched to the inner app.
    That executor pool is a structurally different pool from both AnyIO's
    worker pool (``_pin_single_anyio_worker`` only pins the latter) and the
    event-loop thread, so this now observes THREE distinct-thread cleanup
    calls instead of two: the run_db_sync offload thread, then the sync
    handler's owner-worker thread, then the middleware's loop thread.
    """
    leftover = MagicMock(name="session_opened_by_the_handler")
    handler_thread = {}

    def probe():
        handler_thread["ident"] = threading.get_ident()
        # Exactly what ThreadLocalSessionManager.get_session stores.
        tls.thread_session_manager._local.session = leftover
        tls.thread_session_manager._local.username = "alice"
        return {"ok": True}

    cleanup_threads: list[int] = []
    real_cleanup = tls.cleanup_current_thread

    def _spy():
        cleanup_threads.append(threading.get_ident())
        real_cleanup()

    monkeypatch.setattr(tls, "cleanup_current_thread", _spy)

    with patch(
        "local_deep_research.web.dependencies.auth.ensure_user_database",
        lambda request: None,
    ):
        with _pin_single_anyio_worker():
            response = await _get(
                _stack(probe, server_sessions), "/probe", "alice"
            )

            assert response.status_code == 200, response.text
            assert response.json() == {"ok": True}
            assert handler_thread, "the handler never ran"

            loop_thread = threading.get_ident()
            assert handler_thread["ident"] != loop_thread, (
                "premise: a sync `def` handler must be offloaded to an "
                "AnyIO worker. If Starlette ever stopped offloading, this "
                "whole hazard would disappear and this test must be "
                "rewritten rather than deleted."
            )
            assert len(cleanup_threads) == 3, (
                "expected the run_db_sync offload of ensure_user_database, "
                "then owner-worker cleanup, then middleware's loop-thread "
                f"cleanup; saw {cleanup_threads}"
            )
            offload_thread, owner_thread, seen_loop_thread = cleanup_threads
            assert (owner_thread, seen_loop_thread) == (
                handler_thread["ident"],
                loop_thread,
            ), (
                "expected owner-worker cleanup followed by middleware's "
                f"loop-thread cleanup; saw {cleanup_threads}"
            )
            assert offload_thread not in (
                handler_thread["ident"],
                loop_thread,
            ), (
                "the leading entry is run_db_sync's cleanup of the "
                "ensure_user_database offload, which runs on asyncio's "
                "default ThreadPoolExecutor -- a different pool from both "
                "AnyIO's worker pool (the handler thread) and the event "
                f"loop thread, so it must be a third distinct thread; saw "
                f"{cleanup_threads}"
            )
            assert leftover.close.call_count == 1, (
                "the synchronous endpoint returned without closing its "
                "worker-owned session"
            )
            assert leftover.rollback.call_count == 1


@pytest.mark.asyncio
async def test_the_next_request_on_that_worker_starts_without_a_session(
    server_sessions, fake_db_manager, engines
):
    """Consecutive requests on one worker do not retain a DB connection.

    Two requests, one pinned worker, real ``ThreadLocalSessionManager``
    and real sessions. Request 1 opens one; request 2 reports what it
    finds on entry, before touching anything.

    This is not a hypothetical: under ``workers=1`` that worker serves
    every user in turn, which is why
    ``ThreadLocalSessionManager.get_session``'s username re-validation
    is load-bearing rather than defensive.
    """
    observed: list = []

    def probe():
        found = tls.thread_session_manager.get_current_session()
        # close_calls is sampled HERE, not after the test's own
        # teardown: the claim is that the next request finds a live
        # handle, which stops being observable once anything closes it.
        observed.append(
            (
                threading.get_ident(),
                found,
                None if found is None else found.close_calls,
            )
        )
        session = tls.thread_session_manager.get_session("alice", "pw")
        session.execute(text("select 1"))
        return {"ok": True}

    stack = _stack(probe, server_sessions)
    with patch(
        "local_deep_research.web.dependencies.auth.ensure_user_database",
        lambda request: None,
    ):
        with _pin_single_anyio_worker():
            first = await _get(stack, "/probe", "alice")
            second = await _get(stack, "/probe", "alice")

    assert first.status_code == 200 and second.status_code == 200
    assert len(observed) == 2
    (
        (thread_one, on_entry_one, _),
        (
            thread_two,
            on_entry_two,
            closes_on_entry_two,
        ),
    ) = observed

    assert thread_one == thread_two, (
        "premise: both requests must land on the same pinned worker, "
        f"got {thread_one} and {thread_two}"
    )
    assert on_entry_one is None, (
        "premise: the worker must start this test with no session, "
        "otherwise the second observation proves nothing"
    )
    assert on_entry_two is None, (
        "the second request inherited the first request's worker-local session"
    )
    assert closes_on_entry_two is None
    assert engines["alice"].pool.checkedout() == 0, (
        "the request completed but its connection remained checked out"
    )


# ---------------------------------------------------------------------------
# 3. Synchronous stream bodies need an owner-worker cleanup per iterator step
# ---------------------------------------------------------------------------


@pytest.mark.asyncio
async def test_stream_body_cleanup_runs_on_the_iteration_worker(
    server_sessions, fake_db_manager, engines
):
    """A sync stream receives a cleanup boundary for every ``next()``.

    A synchronous endpoint returns its ``StreamingResponse`` before Starlette
    starts consuming the body.  The endpoint cleanup therefore runs too early
    to see a session opened by the body iterator.  The raw response below is a
    positive control reproducing that leak; the production response wrapper
    must close the same real SQLAlchemy session and return its connection to
    the real QueuePool before the completed response reaches the caller.
    """
    pool = engines["alice"].pool
    manager = tls.thread_session_manager
    body_threads: dict[str, int] = {}
    opened: dict[str, _TrackingSession] = {}

    def body(label):
        body_threads[label] = threading.get_ident()
        session = manager.get_session("alice", "pw")
        assert isinstance(session, _TrackingSession)
        opened[label] = session
        session.execute(text("select 1"))
        yield b"ok"

    def raw_stream():
        return StreamingResponse(body("raw"))

    def protected_stream():
        return WorkerCleanupStreamingResponse(body("protected"))

    def peek():
        return threading.get_ident(), manager.get_current_session()

    with patch(
        "local_deep_research.web.dependencies.auth.ensure_user_database",
        lambda request: None,
    ):
        with _pin_single_anyio_worker():
            raw_response = await _get(
                _stack(raw_stream, server_sessions), "/probe", "alice"
            )
            raw_peek_thread, raw_seen = await anyio.to_thread.run_sync(peek)
            raw_checked_out = pool.checkedout()

            # Reset the positive control on its owning worker before running
            # the protected half on that same deterministic worker.
            await anyio.to_thread.run_sync(tls.cleanup_current_thread)

            protected_response = await _get(
                _stack(protected_stream, server_sessions),
                "/probe",
                "alice",
            )
            (
                protected_peek_thread,
                protected_seen,
            ) = await anyio.to_thread.run_sync(peek)
            protected_checked_out = pool.checkedout()

    assert raw_response.status_code == 200
    assert raw_response.content == b"ok"
    assert raw_peek_thread == body_threads["raw"], (
        "premise: the positive-control observation must reuse the worker "
        "that advanced the raw stream"
    )
    assert raw_seen is opened["raw"]
    assert raw_checked_out == 1, (
        "positive control: a bare synchronous StreamingResponse body must "
        "leave its connection checked out, or this test cannot detect the bug"
    )

    assert protected_response.status_code == 200
    assert protected_response.content == b"ok"
    assert protected_peek_thread == body_threads["protected"], (
        "premise: the protected observation must reuse the worker that "
        "advanced the stream"
    )
    assert protected_seen is None, (
        "the next task inherited the completed stream's thread-local session"
    )
    assert opened["protected"].close_calls == 1
    assert protected_checked_out == 0, (
        "the stream completed but its connection remained checked out"
    )


# ---------------------------------------------------------------------------
# 4. Explicit asyncio offloads use the same owner-worker cleanup
# ---------------------------------------------------------------------------


@pytest.mark.asyncio
async def test_run_db_sync_closes_the_workers_session_and_frees_the_conn(
    fake_db_manager, engines
):
    """``run_db_sync``'s ``finally`` runs ON the worker, so it can do
    what the middleware cannot.

    Both halves run on one pinned executor thread, so "the next task on
    the same worker" is deterministic rather than a race. The bare
    ``asyncio.to_thread`` half is the positive control: it must leak, or
    the ``run_db_sync`` half is not evidence of a fix.

    Pool accounting is real, so this also pins the consequence that
    matters operationally — a leaked session holds a connection out of a
    ``QueuePool`` sized ``POOL_SIZE + MAX_OVERFLOW``.
    """
    pool = engines["alice"].pool
    manager = tls.thread_session_manager

    def _open():
        session = manager.get_session("alice", "pw")
        session.execute(text("select 1"))
        return threading.get_ident(), session

    def _peek():
        return threading.get_ident(), manager.get_current_session()

    loop = asyncio.get_running_loop()
    with _pin_single_default_executor(loop):
        assert pool.checkedout() == 0, "premise: pool starts idle"

        raw_thread, raw_session = await asyncio.to_thread(_open)
        raw_peek_thread, raw_seen = await asyncio.to_thread(_peek)
        raw_checked_out = pool.checkedout()

        # Clear by hand so the two halves are independent.
        await asyncio.to_thread(tls.cleanup_current_thread)

        safe_thread, safe_session = await run_db_sync(_open)
        safe_peek_thread, safe_seen = await run_db_sync(_peek)
        safe_checked_out = pool.checkedout()

    threads = {raw_thread, raw_peek_thread, safe_thread, safe_peek_thread}
    assert len(threads) == 1, (
        "premise: the single-worker executor did not pin every task to "
        f"one thread ({threads}), so neither half tests reuse"
    )

    assert raw_seen is raw_session, (
        "positive control: a bare asyncio.to_thread task MUST leave its "
        f"DB session on the pooled worker (saw {raw_seen!r}), otherwise "
        "the run_db_sync assertion below proves nothing"
    )
    assert raw_checked_out == 1, (
        "positive control: the leaked session must still be holding its "
        f"pooled connection; checkedout() was {raw_checked_out}"
    )

    assert safe_seen is None, (
        "run_db_sync must clear the worker's thread-local DB session "
        f"before returning; the next task on that thread saw {safe_seen!r}"
    )
    assert safe_session.close_calls == 1, (
        "the session must be CLOSED, not merely unlinked — dropping the "
        "reference leaves the connection checked out until the garbage "
        "collector happens to run"
    )
    assert safe_checked_out == 0, (
        "run_db_sync must return the worker's connection to the "
        f"QueuePool; {safe_checked_out} were still checked out"
    )


# ---------------------------------------------------------------------------
# 5. Connections must come back to the pool
# ---------------------------------------------------------------------------


def _hold_a_session(manager, engine_user, ready, release, errors, *, clean):
    """One "request" on a thread that then stays alive, as a uvicorn
    AnyIO worker does."""
    try:
        session = manager.get_session(engine_user, "pw")
        session.execute(text("select 1"))
    except Exception as exc:  # noqa: BLE001 - reported, not swallowed
        errors.append(exc)
    finally:
        if clean:
            manager.cleanup_thread()
        ready.release()
    release.wait(TRIP_TIMEOUT)


def _run_holders(manager, count, *, clean):
    """Start ``count`` holder threads, wait for all of them to have
    finished their "request", and return whatever went wrong.

    The threads are still parked (and so still holding their
    thread-locals) when this returns, which is the whole point: the
    caller inspects the pool while they are alive.
    """
    ready = threading.Semaphore(0)
    release = threading.Event()
    errors: list[Exception] = []
    threads = [
        threading.Thread(
            target=_hold_a_session,
            args=(manager, "alice", ready, release, errors),
            kwargs={"clean": clean},
            daemon=True,
        )
        for _ in range(count)
    ]
    for thread in threads:
        thread.start()
    for _ in threads:
        assert ready.acquire(timeout=TRIP_TIMEOUT), (
            "a holder thread never reported in"
        )
    return errors, release, threads


def _release(release, threads):
    release.set()
    for thread in threads:
        thread.join(timeout=TRIP_TIMEOUT)


def test_uncleaned_sessions_on_live_workers_exhaust_the_pool(
    tmp_path, monkeypatch
):
    """MECHANISM CONTROL for the pool-exhaustion symptom.

    Each "request" lands on a thread that afterwards stays alive — the
    uvicorn worker model. Its thread-local session keeps a reference, so
    the garbage collector never gets a chance to hand the connection
    back either. With no per-request cleanup reachable from the
    middleware, capacity is consumed once per worker and never
    returned; past ``pool_size + max_overflow`` the next checkout blocks
    for ``pool_timeout`` and then raises.

    Scaled down (2 slots, 4 requests) so it is deterministic and cheap;
    production is 20 + 40 and a 10s timeout, which is the reported
    "stalls 10 seconds, then 500".
    """
    engine = _make_engine(
        tmp_path, "alice", size=1, overflow=1, timeout=POOL_TIMEOUT
    )
    monkeypatch.setattr(tls, "db_manager", _FakeDBManager({"alice": engine}))
    manager = tls.ThreadLocalSessionManager()
    errors, release, threads = _run_holders(manager, 4, clean=False)
    try:
        assert engine.pool.checkedout() == 2, (
            "premise: both pool slots must be held by the two threads "
            f"that got in first; checkedout() was {engine.pool.checkedout()}"
        )
        assert len(errors) == 2, (
            "expected the 3rd and 4th requests to be refused once the "
            f"2-slot pool was pinned; got {len(errors)} error(s): {errors}"
        )
        assert all(isinstance(e, SATimeoutError) for e in errors), (
            f"expected QueuePool timeouts, got {errors}"
        )
        assert "QueuePool limit of size 1 overflow 1" in str(errors[0])
    finally:
        _release(release, threads)
        manager.cleanup_all()
        engine.dispose()


def test_cleanup_on_the_worker_returns_every_connection_to_the_pool(
    tmp_path, monkeypatch
):
    """The same four requests on the same 2-slot pool, with the cleanup
    running ON each worker, all succeed and leave the pool idle.

    Paired with the control above this isolates the variable: the only
    difference between the two is where the cleanup runs, so the pool
    exhaustion is attributable to cleanup reachability and nothing else.
    """
    engine = _make_engine(
        tmp_path, "alice", size=1, overflow=1, timeout=POOL_TIMEOUT
    )
    monkeypatch.setattr(tls, "db_manager", _FakeDBManager({"alice": engine}))
    manager = tls.ThreadLocalSessionManager()
    errors, release, threads = _run_holders(manager, 4, clean=True)
    try:
        assert errors == [], (
            "with per-worker cleanup all four requests must fit through a "
            f"2-slot pool; got {errors}"
        )
        assert engine.pool.checkedout() == 0, (
            "every connection must be back in the pool once each worker "
            f"has cleaned up; {engine.pool.checkedout()} still checked out"
        )
    finally:
        _release(release, threads)
        manager.cleanup_all()
        engine.dispose()


# ---------------------------------------------------------------------------
# 6. Owner tracking closes sessions that the LRU can no longer see
# ---------------------------------------------------------------------------


def _fill_cache(namespace_prefix, count):
    """Open ``count`` distinct cache entries on this thread and query
    each, so every one holds a pooled connection."""
    opened = []
    for index in range(count):
        session = db_utils._get_cached_user_session(
            "alice", f"{namespace_prefix}{index}"
        )
        session.execute(text("select 1"))
        opened.append(session)
    return opened


def test_cache_eviction_does_not_close_a_potentially_active_session(
    fake_db_manager,
):
    """Eviction detaches the cache entry but cannot end its caller's use.

    The owner-thread registry tested below, rather than an unsafe cross-thread
    eviction callback, closes it at the worker boundary.
    """
    cache = db_utils._get_cached_user_session.cache
    maxsize = cache.maxsize
    prefix = uuid.uuid4().hex
    opened = _fill_cache(prefix, maxsize + 2)

    evicted = opened[0]
    assert evicted is not opened[-1], "premise: distinct sessions"
    try:
        assert evicted.close_calls == 0
    finally:
        for session in opened:
            session.close()


def test_cached_sessions_pin_connections_until_the_collector_runs(
    fake_db_manager, engines
):
    """Mechanism control: the bare private cache cannot reclaim evictions.

    An evicted ``Session`` is unreachable but sits in a reference cycle,
    so CPython's refcounting never frees it; the pooled connection stays
    checked out until ``gc`` collects that cycle. With the collector
    disabled the count climbs past what the cache can even hold, and
    ``gc.collect()`` is what brings it down to ``maxsize``.

    Production calls use ``get_db_session`` rather than this private helper;
    the paired test below proves its owner registry closes all of these at
    the worker boundary. Keeping this control demonstrates why the registry
    is necessary even though the LRU itself contains only ``maxsize`` entries.
    """
    pool = engines["alice"].pool
    cache = db_utils._get_cached_user_session.cache
    maxsize = cache.maxsize
    total = maxsize + 5
    prefix = uuid.uuid4().hex

    gc_was_enabled = gc.isenabled()
    gc.disable()
    opened = []
    try:
        opened = _fill_cache(prefix, total)
        del opened[:]
        held_without_gc = pool.checkedout()
        assert len(cache) == maxsize, (
            f"premise: the cache must be full at {maxsize} entries, "
            f"holding {len(cache)}"
        )
        assert held_without_gc == total, (
            "with the collector off, every session ever "
            "created should still hold its connection — including the "
            f"{total - maxsize} the cache already evicted. Expected "
            f"{total} checked out, saw {held_without_gc}"
        )
        gc.collect()
        assert pool.checkedout() == maxsize, (
            "after a collection only the sessions the cache still holds "
            f"should be checked out; {pool.checkedout()} of {total} were"
        )
    finally:
        db_utils._get_cached_user_session.cache_clear()
        if gc_was_enabled:
            gc.enable()
        gc.collect()


def test_worker_cleanup_closes_even_sessions_evicted_from_the_cache(
    fake_db_manager, engines
):
    """The public accessor tracks sessions beyond the LRU's reach.

    More namespaces than the cache can hold forces eviction.  At the worker
    boundary every acquired session must still be closed without relying on
    garbage collection, otherwise a burst can exhaust the production pool
    before the collector runs.
    """
    pool = engines["alice"].pool
    cache = db_utils._get_cached_user_session.cache
    total = cache.maxsize + 5
    prefix = uuid.uuid4().hex

    gc_was_enabled = gc.isenabled()
    gc.disable()
    try:
        sessions = [
            db_utils.get_db_session(f"{prefix}{index}", username="alice")
            for index in range(total)
        ]
        for session in sessions:
            session.execute(text("select 1"))

        assert pool.checkedout() == total, "premise: every session checked out"
        closed = db_utils.cleanup_cached_user_sessions_current_thread()

        assert closed == total
        assert all(session.close_calls == 1 for session in sessions)
        assert pool.checkedout() == 0
        assert not [
            key
            for key in cache
            if key[0]
            == getattr(db_utils.g_thread_local_store, "thread_id", None)
        ]
    finally:
        if gc_was_enabled:
            gc.enable()
        gc.collect()