test(database): add failing test for get_current_revision() SQLite threading error

- Added features/tdd_migration_runner_sqlite_threading.feature — a Behave BDD feature file with a TDD test for the SQLite threading bug in MigrationRunner.get_current_revision().
- Added features/steps/tdd_migration_runner_sqlite_threading_steps.py — Step definitions for the TDD test.
- The test scenario creates a raw sqlite3 connection in the main thread (without check_same_thread=False), wraps it in a SQLAlchemy engine using a creator function, monkey-patches create_engine so get_current_revision() uses the main-thread engine, calls get_current_revision() from a background thread, and asserts that no exception is raised.
- The test currently fails with ProgrammingError about SQLite thread safety (proving the bug exists). The @tdd_expected_fail tag inverts the result to passed in CI.

ISSUES CLOSED: #10487

features/steps/tdd_migration_runner_sqlite_threading_steps.py

@@ -0,0 +1,134 @@
+"""Step definitions for TDD Issue #10487 — MigrationRunner SQLite threading error.
+
+These steps verify that ``MigrationRunner.get_current_revision()`` can be
+called safely from a background thread without raising a SQLite threading
+error.
+
+Currently FAILS because ``get_current_revision()`` creates a SQLite engine
+without ``connect_args={"check_same_thread": False}``.  When a connection
+created in the main thread is used from a background thread, SQLite raises:
+
+    ProgrammingError: SQLite objects created in a thread can only be used
+    in that same thread.
+
+Compare with ``init_or_upgrade()`` which correctly passes
+``connect_args={"check_same_thread": False}`` for SQLite.
+
+Bug: https://git.cleverthis.com/cleveragents/cleveragents-core/issues/10487
+"""
+
+from __future__ import annotations
+
+import shutil
+import sqlite3
+import tempfile
+import threading
+from pathlib import Path
+from unittest.mock import patch
+
+from behave import given, then, when
+from behave.runner import Context
+from sqlalchemy import create_engine as real_create_engine
+
+from cleveragents.infrastructure.database.migration_runner import MigrationRunner
+
+
+@given("a MigrationRunner configured with a temporary SQLite database")
+def step_migration_runner_with_temp_db(context: Context) -> None:
+    """Create a MigrationRunner pointing at a temporary SQLite database file."""
+    context.tdd_threading_tmpdir = tempfile.mkdtemp(
+        prefix="tdd_migration_runner_threading_10487_"
+    )
+    db_path = Path(context.tdd_threading_tmpdir) / "test.db"
+    context.tdd_threading_db_url = f"sqlite:///{db_path}"
+    context.tdd_threading_runner = MigrationRunner(context.tdd_threading_db_url)
+    context.tdd_threading_db_path = str(db_path)
+
+    def _cleanup() -> None:
+        shutil.rmtree(context.tdd_threading_tmpdir, ignore_errors=True)
+
+    context.add_cleanup(_cleanup)
+
+
+@given("the database has been initialized via init_or_upgrade")
+def step_database_initialized(context: Context) -> None:
+    """Initialize the database schema using init_or_upgrade."""
+    context.tdd_threading_runner.init_or_upgrade(require_confirmation=False)
+
+
+@when("I call get_current_revision from a background thread")
+def step_call_get_current_revision_from_thread(context: Context) -> None:
+    """Call get_current_revision() from a background thread and capture any errors.
+
+    To reproduce the SQLite threading bug, we create a raw sqlite3 connection
+    in the main thread (without ``check_same_thread=False``) and wrap it in
+    a SQLAlchemy engine.  We then monkey-patch ``create_engine`` so that
+    ``get_current_revision()`` reuses this main-thread engine when called
+    from the background thread.  When the background thread tries to use
+    the main-thread connection, SQLite raises ``ProgrammingError``.
+
+    This reproduces the bug: ``get_current_revision()`` calls
+    ``create_engine(self.database_url)`` without
+    ``connect_args={"check_same_thread": False}``.  With a shared
+    main-thread connection, SQLite rejects the cross-thread usage.
+    """
+    errors: list[Exception] = []
+
+    # Create a raw sqlite3 connection in the main thread WITHOUT
+    # check_same_thread=False.  This connection will be used by the
+    # background thread, triggering the SQLite threading error.
+    raw_main_thread_conn = sqlite3.connect(context.tdd_threading_db_path)
+
+    # Create a SQLAlchemy engine that wraps the raw connection.
+    # We use a creator function that always returns the same raw connection.
+    main_thread_engine = real_create_engine(
+        "sqlite://",
+        creator=lambda: raw_main_thread_conn,
+    )
+
+    def call_from_thread() -> None:
+        try:
+            # Patch create_engine so get_current_revision() uses the
+            # main-thread engine (simulating a shared/cached engine).
+            with patch(
+                "cleveragents.infrastructure.database.migration_runner.create_engine",
+                return_value=main_thread_engine,
+            ):
+                context.tdd_threading_runner.get_current_revision()
+        except Exception as exc:
+            errors.append(exc)
+
+    thread = threading.Thread(target=call_from_thread)
+    thread.start()
+    thread.join()
+
+    raw_main_thread_conn.close()
+    main_thread_engine.dispose()
+    context.tdd_threading_errors = errors
+
+
+@then(
+    "get_current_revision should not raise any exception from the background thread"
+)
+def step_no_exception_from_thread(context: Context) -> None:
+    """Assert that get_current_revision() raised no exception from the thread.
+
+    This assertion FAILS on the unfixed code because SQLite raises:
+        ProgrammingError: SQLite objects created in a thread can only be
+        used in that same thread.
+
+    The root cause is that ``get_current_revision()`` calls
+    ``create_engine(self.database_url)`` without
+    ``connect_args={"check_same_thread": False}``.  When the connection
+    pool returns a connection created in the main thread to a background
+    thread, SQLite rejects the cross-thread usage.
+
+    Once the fix is applied (adding ``connect_args={"check_same_thread": False}``
+    to the ``create_engine`` call in ``get_current_revision()``), this test
+    will pass and the ``@tdd_expected_fail`` tag should be removed.
+    """
+    errors: list[Exception] = context.tdd_threading_errors
+    assert not errors, (
+        f"get_current_revision() must not raise from a background thread; "
+        f"got: {errors!r}"
+    )

features/tdd_migration_runner_sqlite_threading.feature

@@ -0,0 +1,25 @@
+@tdd_issue @tdd_issue_10487
+Feature: TDD Issue #10487 — MigrationRunner.get_current_revision() SQLite threading error
+  As a developer
+  I want to verify that MigrationRunner.get_current_revision() works correctly
+  when called from a background thread
+  So that the SQLite threading bug is captured and will be caught by a regression test
+
+  ``MigrationRunner.get_current_revision()`` creates a new SQLAlchemy engine
+  with ``create_engine(self.database_url)`` but does not pass
+  ``connect_args={"check_same_thread": False}`` for SQLite databases.
+  When called from a thread other than the one that created the engine,
+  SQLite raises ``ProgrammingError: SQLite objects created in a thread can
+  only be used in that same thread``.
+
+  Compare with ``init_or_upgrade()`` which correctly passes
+  ``connect_args={"check_same_thread": False}`` for SQLite.
+
+  Bug: https://git.cleverthis.com/cleveragents/cleveragents-core/issues/10487
+
+  @tdd_issue @tdd_issue_10487 @tdd_expected_fail
+  Scenario: get_current_revision() called from background thread raises no error
+    Given a MigrationRunner configured with a temporary SQLite database
+    And the database has been initialized via init_or_upgrade
+    When I call get_current_revision from a background thread
+    Then get_current_revision should not raise any exception from the background thread