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.

33.4 KB
Raw Blame
"""Tests for database/session_context.py.

Pre-FastAPI-migration this file used `flask.Flask().test_request_context()`
to drive session_context's Flask-aware code paths. Those are gone —
session_context resolves the user via contextvars + thread-local sessions
now — so the tests drive get_user_db_session / with_user_database directly
with mocked password resolution.
"""

import pytest
from unittest.mock import Mock, patch


@pytest.fixture
def app():
    """No-op placeholder.

    Pre-migration this returned a Flask app whose ``test_request_context()``
    drove session_context's (now-removed) Flask paths. The tests no longer
    use it; kept as a no-op so their signatures stay stable.
    """
    return


class TestDatabaseSessionError:
    """Tests for DatabaseSessionError exception."""

    def test_exception_can_be_raised(self):
        """Test that DatabaseSessionError can be raised."""
        from local_deep_research.database.session_context import (
            DatabaseSessionError,
        )

        with pytest.raises(DatabaseSessionError):
            raise DatabaseSessionError("Test error")

    def test_exception_message(self):
        """Test that exception preserves message."""
        from local_deep_research.database.session_context import (
            DatabaseSessionError,
        )

        try:
            raise DatabaseSessionError("Custom error message")
        except DatabaseSessionError as e:
            assert str(e) == "Custom error message"


class TestGetUserDbSession:
    """Tests for get_user_db_session context manager."""

    def test_raises_when_no_username_provided(self, app):
        """Test that error is raised when no username available."""
        from local_deep_research.database.session_context import (
            get_user_db_session,
            DatabaseSessionError,
        )

        # No username in session
        with pytest.raises(DatabaseSessionError, match="No authenticated user"):
            with get_user_db_session():
                pass

    def test_uses_provided_username(self, app):
        """Test that explicitly provided username is used."""
        from local_deep_research.database.session_context import (
            get_user_db_session,
        )

        with patch(
            "local_deep_research.database.session_context.db_manager"
        ) as mock_db:
            mock_db.has_encryption = False

            with patch(
                "local_deep_research.database.thread_local_session.get_metrics_session"
            ) as mock_get_session:
                mock_session = Mock()
                mock_get_session.return_value = mock_session

                with get_user_db_session(
                    username="testuser", password="testpass"
                ) as session:
                    assert session is mock_session

    def test_raises_when_encrypted_db_requires_password(self, app):
        """Test error when encrypted DB accessed without password."""
        from local_deep_research.database.session_context import (
            get_user_db_session,
            DatabaseSessionError,
        )

        with patch(
            "local_deep_research.database.session_context.db_manager"
        ) as mock_db:
            mock_db.has_encryption = True
            mock_db.connections = {}

            with patch(
                "local_deep_research.database.session_context.get_search_context"
            ) as mock_ctx:
                mock_ctx.return_value = None

                with patch(
                    "local_deep_research.database.session_passwords.session_password_store"
                ) as mock_store:
                    mock_store.get_session_password.return_value = None
                    mock_store.get_any_session_password.return_value = None

                    with pytest.raises(
                        DatabaseSessionError, match="requires password"
                    ):
                        with get_user_db_session(username="testuser"):
                            pass


class TestWithUserDatabase:
    """Tests for with_user_database decorator."""

    def test_decorator_injects_db_session(self, app):
        """Test that decorator injects db_session as first argument."""
        from local_deep_research.database.session_context import (
            with_user_database,
        )

        @with_user_database
        def test_func(db_session, arg1, arg2):
            return (db_session, arg1, arg2)

        with patch(
            "local_deep_research.database.session_context.get_user_db_session"
        ) as mock_ctx:
            mock_session = Mock()
            mock_ctx.return_value.__enter__ = Mock(return_value=mock_session)
            mock_ctx.return_value.__exit__ = Mock(return_value=False)

            result = test_func("value1", "value2")
            assert result == (mock_session, "value1", "value2")

    def test_decorator_passes_kwargs(self, app):
        """Test that decorator passes keyword arguments."""
        from local_deep_research.database.session_context import (
            with_user_database,
        )

        @with_user_database
        def test_func(db_session, key1=None, key2=None):
            return {"session": db_session, "key1": key1, "key2": key2}

        with patch(
            "local_deep_research.database.session_context.get_user_db_session"
        ) as mock_ctx:
            mock_session = Mock()
            mock_ctx.return_value.__enter__ = Mock(return_value=mock_session)
            mock_ctx.return_value.__exit__ = Mock(return_value=False)

            result = test_func(key1="a", key2="b")
            assert result["key1"] == "a"
            assert result["key2"] == "b"

    def test_decorator_extracts_special_kwargs(self, app):
        """Test that _username and _password are extracted from kwargs."""
        from local_deep_research.database.session_context import (
            with_user_database,
        )

        @with_user_database
        def test_func(db_session):
            return db_session

        with patch(
            "local_deep_research.database.session_context.get_user_db_session"
        ) as mock_ctx:
            mock_session = Mock()
            mock_ctx.return_value.__enter__ = Mock(return_value=mock_session)
            mock_ctx.return_value.__exit__ = Mock(return_value=False)

            test_func(_username="custom_user", _password="custom_pass")

            # Verify get_user_db_session was called with the custom credentials
            mock_ctx.assert_called_once_with("custom_user", "custom_pass")


class TestDatabaseAccessMixin:
    """Tests for DatabaseAccessMixin class."""

    def test_get_db_session_raises_deprecation_warning(self, app):
        """Test that get_db_session raises DeprecationWarning.

        The method was deprecated because it returned a closed session
        (context manager exits before returning).
        """
        from local_deep_research.database.session_context import (
            DatabaseAccessMixin,
        )
        import pytest

        class TestService(DatabaseAccessMixin):
            pass

        service = TestService()

        with pytest.raises(DeprecationWarning) as exc_info:
            service.get_db_session()

        assert "deprecated" in str(exc_info.value).lower()
        assert "get_user_db_session" in str(exc_info.value)


class TestUnencryptedDbPlaceholder:
    """Tests for UNENCRYPTED_DB_PLACEHOLDER constant."""

    def test_placeholder_value(self):
        """Test the placeholder constant value."""
        from local_deep_research.database.session_context import (
            UNENCRYPTED_DB_PLACEHOLDER,
        )

        assert UNENCRYPTED_DB_PLACEHOLDER == "unencrypted-mode"
        assert isinstance(UNENCRYPTED_DB_PLACEHOLDER, str)


class TestSafeRollback:
    """Tests for the safe_rollback helper introduced for issue #3827."""

    def test_calls_session_rollback(self):
        from local_deep_research.database.session_context import (
            safe_rollback,
        )

        session = Mock()
        safe_rollback(session, "ctx")
        session.rollback.assert_called_once()

    def test_swallows_rollback_exception(self):
        """Rollback errors must not propagate — call sites are exception
        handlers themselves and a raise here would mask the original error.
        """
        from local_deep_research.database.session_context import (
            safe_rollback,
        )

        session = Mock()
        session.rollback.side_effect = RuntimeError("simulated rollback fail")
        # Must NOT raise.
        safe_rollback(session, "ctx")
        session.rollback.assert_called_once()

    def test_works_without_context_argument(self):
        from local_deep_research.database.session_context import (
            safe_rollback,
        )

        session = Mock()
        safe_rollback(session)
        session.rollback.assert_called_once()

    def test_provisioning_state_invalid_request_is_treated_as_noop(self):
        """When the per-user QueuePool is mid-acquire on another thread,
        ``session.rollback()`` raises
        ``InvalidRequestError("This session is provisioning a new connection;
        concurrent operations are not permitted")``. That secondary error
        used to cascade back into the original caller and pollute the logs
        with a misleading "Failed to rollback session" line. ``safe_rollback``
        now treats that exact subclass of failure as a benign no-op (logged
        at DEBUG so the production stderr stream stays clean) and the call
        must NOT raise.
        """
        from sqlalchemy.exc import InvalidRequestError

        from local_deep_research.database.session_context import (
            safe_rollback,
        )

        session = Mock()
        session.rollback.side_effect = InvalidRequestError(
            "This session is provisioning a new connection; concurrent "
            "operations are not permitted"
        )

        # Must NOT raise — original caller is itself in an except handler.
        safe_rollback(session, "context_manager")

        session.rollback.assert_called_once()

    def test_provisioning_state_debug_logged(self):
        """The provisioning no-op emits a DEBUG line so a forensic analyst
        with ``LDR_APP_DEBUG=true`` can see *why* a recovery rollback was
        skipped, but the production stderr stream stays at WARNING+.
        """
        from sqlalchemy.exc import InvalidRequestError

        from local_deep_research.database.session_context import (
            safe_rollback,
        )
        import local_deep_research.database.session_context as sc_mod

        session = Mock()
        session.rollback.side_effect = InvalidRequestError(
            "This session is provisioning a new connection"
        )

        with patch.object(sc_mod.logger, "debug") as mock_debug:
            with patch.object(sc_mod.logger, "exception") as mock_exception:
                with patch.object(sc_mod.logger, "warning") as mock_warning:
                    safe_rollback(session, "context_manager")

        # DEBUG line carries the caller-supplied context so a tail of
        # skipped rollbacks can be attributed to the call site.
        debug_msg = mock_debug.call_args[0][0]
        assert "resetting broken session" in debug_msg
        assert "context_manager" in debug_msg

        # And the loud failure paths are NOT used for this benign case.
        mock_exception.assert_not_called()
        mock_warning.assert_not_called()

    def test_unrelated_sqlalchemy_error_still_logs_at_exception(self):
        """Only the provisioning/concurrency state is treated as a no-op.
        Other ``SQLAlchemyError`` subclasses (e.g. ``OperationalError``)
        still hit the loud ``logger.exception`` path so persistent DB
        issues stay visible at ERROR level on stderr."""
        from sqlalchemy.exc import OperationalError

        from local_deep_research.database.session_context import (
            safe_rollback,
        )
        import local_deep_research.database.session_context as sc_mod

        session = Mock()
        session.rollback.side_effect = OperationalError(
            "stmt", {}, Exception("database is locked")
        )

        with patch.object(sc_mod.logger, "exception") as mock_exception:
            safe_rollback(session, "context_manager")

        mock_exception.assert_called_once()
        # The error log line keeps the call-site context for forensics.
        msg = mock_exception.call_args[0][0]
        assert "context_manager" in msg
        assert "Failed to rollback session" in msg

    def test_safe_rollback_handles_none_session(self):
        """Passing None as session should return early without error."""
        from local_deep_research.database.session_context import safe_rollback

        # Must NOT raise AttributeError
        safe_rollback(None, "none_test")

    def test_interface_error_invalidates_session_and_resets_cache(self):
        """When ``session.rollback()`` raises ``InterfaceError`` (the
        ``Cursor needed to be reset because of commit/rollback …`` family),
        the session is structurally unusable — a future query on it would
        just re-raise. ``safe_rollback`` should treat the InterfaceError
        the same as the provisioning race: log at DEBUG and clear the
        thread-local cache so the next caller on this thread gets a
        fresh session from the ``QueuePool``.
        """
        from sqlalchemy.exc import InterfaceError

        from local_deep_research.database.session_context import (
            safe_rollback,
        )
        from local_deep_research.database.thread_local_session import (
            thread_session_manager,
        )

        # Pretend ``session`` IS the current thread's cached session —
        # the realistic case from ``get_user_db_session``.
        session = Mock()
        # ``InterfaceError`` is a ``DBAPIError`` subclass; the
        # (statement, params, orig) signature is the SQLAlchemy 2.x
        # way to construct it without going through a real driver.
        session.rollback.side_effect = InterfaceError(
            "Cursor needed to be reset because of commit/rollback and can "
            "no longer be fetched from",
            None,
            Exception("reset"),
        )
        thread_session_manager._local.session = session
        thread_session_manager._local.username = "testuser"
        try:
            safe_rollback(session, "library_search")
            # Must NOT raise — the caller is in an except handler.
            # And the thread-local cache must be cleared so the next
            # ``get_user_db_session`` on this thread gets a fresh
            # session from the QueuePool.
            assert thread_session_manager._local.session is None
            assert thread_session_manager._local.username is None
        finally:
            # Make sure a failing assertion above doesn't leak the
            # mock into the next test.
            thread_session_manager._local.session = None
            thread_session_manager._local.username = None

    def test_interface_error_reset_failure_does_not_mask_original(self):
        """If the thread-local cache reset itself raises (e.g. the
        session's ``close()`` blows up because the connection is gone),
        ``safe_rollback`` must still return cleanly — call sites are
        themselves in except handlers and a raise here would mask the
        original failure.
        """
        from sqlalchemy.exc import InterfaceError

        from local_deep_research.database.session_context import (
            safe_rollback,
        )
        import local_deep_research.database.session_context as sc_mod

        session = Mock()
        session.rollback.side_effect = InterfaceError(
            "Cursor needed to be reset", None, Exception("reset")
        )
        # Force ``reset_session_if_matches`` to raise — the rollback
        # path must still swallow it.
        with patch.object(
            sc_mod.thread_session_manager,
            "reset_session_if_matches",
            side_effect=RuntimeError("reset boom"),
        ):
            with patch.object(sc_mod.logger, "debug") as mock_debug:
                # Must NOT raise.
                safe_rollback(session, "library_search")

        # A DEBUG line still records the recovery attempt so a forensic
        # analyst with ``LDR_APP_DEBUG=true`` sees it.
        debug_msgs = [c.args[0] for c in mock_debug.call_args_list]
        assert any("reset_session_if_matches raised" in m for m in debug_msgs)

    def test_interface_error_message_only_also_resets(self):
        """Some SQLAlchemy builds wrap ``InterfaceError`` in a parent
        class whose ``str()`` still contains the canonical
        ``Cursor needed to be reset`` substring. The detection must
        match on the message as well as on the type, so an exotic
        subclass doesn't slip through into the loud path.
        """
        from local_deep_research.database.session_context import (
            safe_rollback,
        )
        from local_deep_research.database.thread_local_session import (
            thread_session_manager,
        )
        import local_deep_research.database.session_context as sc_mod

        session = Mock()
        # A bare ``SQLAlchemyError`` (not InterfaceError) but with the
        # canonical message — the message-based check should still fire.
        from sqlalchemy.exc import SQLAlchemyError

        session.rollback.side_effect = SQLAlchemyError(
            "Cursor needed to be reset because of commit/rollback"
        )
        thread_session_manager._local.session = session
        try:
            with patch.object(sc_mod.logger, "exception") as mock_exception:
                safe_rollback(session, "library_search")

            # Loud ``logger.exception`` is NOT used for this benign
            # broken-state case.
            mock_exception.assert_not_called()
            # And the cache was cleared.
            assert thread_session_manager._local.session is None
        finally:
            thread_session_manager._local.session = None

    def test_unrelated_interface_error_still_logs_at_exception(self):
        """InterfaceError without 'Cursor needed to be reset' (e.g. driver-level
        connection failures) should NOT be quietly swallowed into the DEBUG path;
        they must log at ERROR via logger.exception.
        """
        from sqlalchemy.exc import InterfaceError

        from local_deep_research.database.session_context import (
            safe_rollback,
        )
        import local_deep_research.database.session_context as sc_mod

        session = Mock()
        session.rollback.side_effect = InterfaceError(
            "connection already closed", None, Exception("closed")
        )

        with patch.object(sc_mod.logger, "exception") as mock_exception:
            safe_rollback(session, "test_context")

        mock_exception.assert_called_once()
        msg = mock_exception.call_args[0][0]
        assert "test_context" in msg
        assert "Failed to rollback session" in msg

    def test_reset_session_if_matches_only_clears_cached_session(self):
        """The ``reset_session_if_matches`` helper is identity-checked:
        passing a session that is NOT the current thread's cached one
        must not touch the cache. This protects callers that hand
        ``safe_rollback`` a session borrowed from another context
        (e.g. ``g.db_session`` or a worker thread's local) — a wrong
        identity match would silently clear someone else's cache and
        hide the original failure under a different stack trace.
        """
        from local_deep_research.database.thread_local_session import (
            thread_session_manager,
        )

        cached = Mock(name="cached")
        other = Mock(name="other")
        thread_session_manager._local.session = cached
        thread_session_manager._local.username = "alice"
        try:
            # Pass the WRONG session: must be a no-op.
            result = thread_session_manager.reset_session_if_matches(other)
            assert result is False
            # The cache is untouched — alice's session is still there.
            assert thread_session_manager._local.session is cached
            assert thread_session_manager._local.username == "alice"

            # Pass the RIGHT session: must clear and return True.
            result = thread_session_manager.reset_session_if_matches(cached)
            assert result is True
            assert thread_session_manager._local.session is None
        finally:
            thread_session_manager._local.session = None
            thread_session_manager._local.username = None

    def test_reset_session_if_matches_with_none_session_is_noop(self):
        """``reset_session_if_matches(None)`` is a defensive no-op so
        call sites can hand the helper a possibly-None session without
        a separate guard.
        """
        from local_deep_research.database.thread_local_session import (
            thread_session_manager,
        )

        # No session cached → no-op.
        assert thread_session_manager.reset_session_if_matches(None) is False

        # Session cached but identity doesn't match → still no-op.
        cached = Mock()
        thread_session_manager._local.session = cached
        try:
            assert (
                thread_session_manager.reset_session_if_matches(None) is False
            )
            assert thread_session_manager._local.session is cached
        finally:
            thread_session_manager._local.session = None


class TestSessionContextRollbackOnExit:
    """Tests for automatic safe_rollback on outermost scope exit to prevent
    QueuePool connection leaks.
    """

    def test_outermost_scope_rolls_back_if_in_transaction(self):
        from local_deep_research.database.session_context import (
            get_user_db_session,
        )
        from local_deep_research.database.thread_local_session import (
            thread_session_manager,
        )

        mock_session = Mock()
        mock_session.in_transaction.return_value = True

        with patch(
            "local_deep_research.database.session_context.db_manager"
        ) as mock_db:
            mock_db.has_encryption = False
            with patch(
                "local_deep_research.database.thread_local_session.get_metrics_session",
                return_value=mock_session,
            ):
                with patch(
                    "local_deep_research.database.session_context.safe_rollback"
                ) as mock_rollback:
                    with get_user_db_session(username="testuser"):
                        assert thread_session_manager.in_scope()
                    assert not thread_session_manager.in_scope()
                    mock_rollback.assert_called_once_with(
                        mock_session, "get_user_db_session exit"
                    )

    def test_outermost_scope_skips_rollback_if_not_in_transaction(self):
        from local_deep_research.database.session_context import (
            get_user_db_session,
        )

        mock_session = Mock()
        mock_session.in_transaction.return_value = False

        with patch(
            "local_deep_research.database.session_context.db_manager"
        ) as mock_db:
            mock_db.has_encryption = False
            with patch(
                "local_deep_research.database.thread_local_session.get_metrics_session",
                return_value=mock_session,
            ):
                with patch(
                    "local_deep_research.database.session_context.safe_rollback"
                ) as mock_rollback:
                    with get_user_db_session(username="testuser"):
                        pass
                    mock_rollback.assert_not_called()

    def test_nested_scope_does_not_rollback_on_inner_exit(self):
        from local_deep_research.database.session_context import (
            get_user_db_session,
        )
        from local_deep_research.database.thread_local_session import (
            thread_session_manager,
        )

        mock_session = Mock()
        mock_session.in_transaction.return_value = True

        with patch(
            "local_deep_research.database.session_context.db_manager"
        ) as mock_db:
            mock_db.has_encryption = False
            with patch(
                "local_deep_research.database.thread_local_session.get_metrics_session",
                return_value=mock_session,
            ):
                with patch(
                    "local_deep_research.database.session_context.safe_rollback"
                ) as mock_rollback:
                    with get_user_db_session(username="testuser"):
                        assert (
                            getattr(
                                thread_session_manager._local, "scope_depth", 0
                            )
                            == 1
                        )
                        with get_user_db_session(username="testuser"):
                            assert (
                                getattr(
                                    thread_session_manager._local,
                                    "scope_depth",
                                    0,
                                )
                                == 2
                            )
                        # Inner exit: should NOT have rolled back yet
                        mock_rollback.assert_not_called()
                        assert thread_session_manager.in_scope()
                    # Outer exit: should roll back
                    mock_rollback.assert_called_once_with(
                        mock_session, "get_user_db_session exit"
                    )

    def test_outermost_scope_swallows_in_transaction_missing_attribute(self):
        from local_deep_research.database.session_context import (
            get_user_db_session,
        )

        session_without_attr = object()  # No in_transaction attribute

        with patch(
            "local_deep_research.database.session_context.db_manager"
        ) as mock_db:
            mock_db.has_encryption = False
            with patch(
                "local_deep_research.database.thread_local_session.get_metrics_session",
                return_value=session_without_attr,
            ):
                with patch(
                    "local_deep_research.database.session_context.safe_rollback"
                ) as mock_rollback:
                    with get_user_db_session(username="testuser"):
                        pass
                    mock_rollback.assert_not_called()

    def test_outermost_scope_swallows_in_transaction_exception(self):
        from local_deep_research.database.session_context import (
            get_user_db_session,
        )

        mock_session = Mock()
        mock_session.in_transaction.side_effect = RuntimeError("Broken session")

        with patch(
            "local_deep_research.database.session_context.db_manager"
        ) as mock_db:
            mock_db.has_encryption = False
            with patch(
                "local_deep_research.database.thread_local_session.get_metrics_session",
                return_value=mock_session,
            ):
                # Must exit cleanly without re-raising RuntimeError
                with get_user_db_session(username="testuser"):
                    pass

    def test_real_engine_queuepool_connection_released_on_exit(self, tmp_path):
        """End-to-end integration test with real SQLAlchemy engine & QueuePool.
        Verifies that after querying SQLite in get_user_db_session, exiting the
        context returns the checked-out connection to QueuePool (checkedout() == 0).
        """
        from sqlalchemy import create_engine, text
        from sqlalchemy.orm import sessionmaker
        from sqlalchemy.pool import QueuePool
        from local_deep_research.database.session_context import (
            get_user_db_session,
        )
        from local_deep_research.database.thread_local_session import (
            cleanup_current_thread,
        )

        db_file = tmp_path / "test_pool.db"
        engine = create_engine(
            f"sqlite:///{db_file}",
            poolclass=QueuePool,
            pool_size=5,
            max_overflow=5,
            pool_timeout=5.0,
        )
        SessionLocal = sessionmaker(bind=engine)
        real_session = SessionLocal()

        try:
            with patch(
                "local_deep_research.database.session_context.db_manager"
            ) as mock_db:
                mock_db.has_encryption = False
                with patch(
                    "local_deep_research.database.thread_local_session.get_metrics_session",
                    return_value=real_session,
                ):
                    with get_user_db_session(username="real_user") as s:
                        s.execute(text("SELECT 1"))
                        # Connection is checked out from QueuePool
                        assert engine.pool.checkedout() == 1
                        assert s.in_transaction()

                    # On exit from get_user_db_session, connection must be returned
                    assert engine.pool.checkedout() == 0
                    assert not real_session.in_transaction()
        finally:
            real_session.close()
            engine.dispose()
            cleanup_current_thread()

    def test_multi_thread_pool_exhaustion_prevented(self, tmp_path):
        """Threads running queries through get_user_db_session do not
        exhaust a small QueuePool because connections are returned when the
        outermost scope ends — not pinned for the thread's lifetime.

        Engages the production thread-local session machinery (the
        engagement shape carried over from #6387's
        test_user_db_session_release.py): ``db_manager`` is swapped for a
        stand-in backed by a real QueuePool engine in BOTH
        ``session_context`` and ``thread_local_session``, and nothing is
        patched around ``get_metrics_session`` — the thread-local
        manager's session reuse and scope-depth handling run for real.
        The old shape built its own engine + SessionLocal and patched
        get_metrics_session, so the machinery this docstring names was
        never reached; with per-task cleanup_current_thread() it also
        masked the release-at-exit guarantee entirely.

        The barrier keeps every thread ALIVE after its ``with`` block
        ends, so scope-exit is the only path that can return the
        connection: 10 live threads hold sessions but the pool of 5 must
        show zero checked-out connections.
        """
        import threading
        from sqlalchemy import create_engine, text
        from sqlalchemy.orm import sessionmaker
        from sqlalchemy.pool import QueuePool
        from local_deep_research.database import (
            session_context,
            thread_local_session,
        )
        from local_deep_research.database.session_context import (
            get_user_db_session,
        )

        db_file = tmp_path / "test_threaded_pool.db"
        engine = create_engine(
            f"sqlite:///{db_file}",
            poolclass=QueuePool,
            pool_size=3,
            max_overflow=2,  # Max total 5 connections — half the threads
            pool_timeout=3.0,
            # The per-user QueuePool shares connections across worker
            # threads; default SQLite (check_same_thread=True) rejects that.
            connect_args={"check_same_thread": False},
        )

        class _FakeDBManager:
            """Stand-in for encrypted_db.db_manager backed by a real
            QueuePool engine — the two methods the session machinery
            actually calls."""

            def __init__(self, engine):
                self._sessionmaker = sessionmaker(bind=engine)
                self.has_encryption = False

            def open_user_database(self, username, password):
                return engine

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

        fake_db = _FakeDBManager(engine)

        n = 10  # live threads on a pool of max 5 connections
        errors = []
        barrier = threading.Barrier(n + 1)
        release = threading.Event()

        def worker(i):
            try:
                with get_user_db_session(username=f"user_{i}") as sess:
                    res = sess.execute(text("SELECT 1")).scalar()
                    assert res == 1
            except Exception as exc:  # noqa: BLE001
                errors.append(exc)
            barrier.wait(timeout=30)
            release.wait(timeout=30)

        try:
            with (
                patch.object(session_context, "db_manager", fake_db),
                patch.object(thread_local_session, "db_manager", fake_db),
            ):
                threads = [
                    threading.Thread(target=worker, args=(i,), daemon=True)
                    for i in range(n)
                ]
                for t in threads:
                    t.start()
                barrier.wait(timeout=30)
                # Every block has ended but every thread still lives: the
                # connections must already be back in the pool.
                checked_out = engine.pool.checkedout()
                release.set()
                for t in threads:
                    t.join(timeout=30)
            assert not errors, f"worker errors: {errors}"
            assert checked_out == 0, (
                f"connections still checked out after every scope ended: "
                f"{checked_out} — the thread-local session is pinning its "
                f"QueuePool connection past scope exit"
            )
        finally:
            engine.dispose()