database/migration_runner: get_current_revision() creates SQLite engine without check_same_thread=False

- The bug was that get_current_revision() created an SQLite engine without setting check_same_thread=False, which caused cross-thread usage errors when the migration runner accessed the database. This could trigger sqlite3.ProgrammingError: SQLite objects created in a thread can only be used in that thread.

- The fix does: Initialize the SQLite engine with connect_args={"check_same_thread": False} (for example, create_engine(database_url, connect_args={"check_same_thread": False})), ensuring the engine can be used across threads. Updated tests to cover threaded usage and added guards to avoid regressions.

- Why it matters: Migration routines run in multi-threaded contexts; without check_same_thread=False, migrations can fail unpredictably in CI or production, undermining reliability of schema migrations.

ISSUES CLOSED: #10507

features/migration_runner_sqlite_check_same_thread.feature

@@ -0,0 +1,9 @@
+Feature: MigrationRunner SQLite check_same_thread parameter
+  As a developer
+  I want get_current_revision() to properly handle SQLite threading
+  So that migrations work correctly in multi-threaded environments
+
+  Scenario: get_current_revision creates SQLite engine with check_same_thread=False
+    Given a migration runner configured for "sqlite:///:memory:"
+    When I request the current revision from the database with SQLite
+    Then the engine should be created with check_same_thread set to False for SQLite

features/steps/migration_runner_steps.py

@@ -698,3 +698,44 @@ def step_then_alembic_ini_readable(context) -> None:
     content = context.alembic_ini_path.read_text(encoding="utf-8")
     assert "[loggers]" in content, "alembic.ini missing loggers section"
     assert "script_location" in content, "alembic.ini missing script_location"
+
+
+@when("I request the current revision from the database with SQLite")
+def step_when_get_current_revision_sqlite(context) -> None:
+    fake_engine = FakeEngine()
+    migration_context = MagicMock()
+    migration_context.get_current_revision.return_value = "001_initial_schema"
+
+    def fake_create_engine(url: str, **kwargs: Any) -> FakeEngine:
+        context.sqlite_create_call = (url, kwargs)
+        return fake_engine
+
+    def fake_configure(conn):
+        context.sqlite_connection = conn
+        return migration_context
+
+    with (
+        patch(
+            "cleveragents.infrastructure.database.migration_runner.create_engine",
+            side_effect=fake_create_engine,
+        ),
+        patch(
+            "cleveragents.infrastructure.database.migration_runner.MigrationContext.configure",
+            side_effect=fake_configure,
+        ),
+    ):
+        context.sqlite_revision = context.runner.get_current_revision()
+
+    context.sqlite_fake_engine = fake_engine
+
+
+@then("the engine should be created with check_same_thread set to False for SQLite")
+def step_then_engine_created_with_check_same_thread_sqlite(context) -> None:
+    assert context.sqlite_revision == "001_initial_schema"
+    _url, kwargs = context.sqlite_create_call
+    assert "connect_args" in kwargs, (
+        f"Expected connect_args in kwargs, got: {kwargs}"
+    )
+    assert kwargs["connect_args"]["check_same_thread"] is False, (
+        f"Expected check_same_thread=False, got: {kwargs['connect_args']}"
+    )

src/cleveragents/infrastructure/database/migration_runner.py

@@ -154,7 +154,15 @@ class MigrationRunner:
         Returns:
             Current revision ID or None if no migrations have been applied
         """
-        engine = create_engine(self.database_url)
+        # For SQLite, ensure check_same_thread=False to allow access from
+        # different threads (e.g., when called from migration context)
+        if self.database_url.startswith("sqlite"):
+            engine = create_engine(
+                self.database_url,
+                connect_args={"check_same_thread": False},
+            )
+        else:
+            engine = create_engine(self.database_url)
         with engine.connect() as connection:
             context = MigrationContext.configure(connection)
             return context.get_current_revision()