I am going to supply a rough draft to workaround the async live view functions problem.
Hefty disclaimer: this is a hacky workaround and not an elegant solution. The need for this workaround should probably be interpreted as a sign to re-consider the entire approach.
With that said, I am doing this in one of my personal projects.
OK, here we go.
Make this new module:
defmodule MyAppWeb.Utils.LiveViewAsyncUtils do
  @moduledoc """
  Override the `assign_async` and `start_async`functions from `Phoenix.LiveView`
  We do this to allow checking out the `auto` mode Ecto Sandbox in tests.
  """
  alias Ecto.Adapters.SQL.Sandbox
  alias MyApp.Repo
  alias Phoenix.LiveView.Async
  def assign_async_aug(socket, key_or_keys, func, opts \\ []) do
    func_aug = build_augmented_func(func)
    # credo:disable-for-lines:1 MyApp.CustomCredo.Check.Design.NoLiveViewAsyncFunctions
    Async.assign_async(socket, key_or_keys, func_aug, opts)
  end
  def start_async_aug(socket, key_or_keys, func, opts \\ []) do
    func_aug = build_augmented_func(func)
    # credo:disable-for-lines:1 MyApp.CustomCredo.Check.Design.NoLiveViewAsyncFunctions
    Async.start_async(socket, key_or_keys, func_aug, opts)
  end
  defp build_augmented_func(func) do
    parent_pid = self()
    fn ->
      Sandbox.allow(Repo, parent_pid, self())
      func.()
    end
  end
end
then add this to html_helpers function in my_app_web.ex
  import MyAppWeb.Utils.LiveViewAsyncUtils
and then add this credo rule to .credo.exs
          {MyApp.CustomCredo.Check.Design.NoLiveViewAsyncFunctions},
and then add that new credo check somewhere in your project
defmodule MyApp.CustomCredo.Check.Design.NoLiveViewAsyncFunctions do
  @moduledoc false
  use Credo.Check,
    base_priority: :high,
    category: :design,
    explanations: [
      check: """
      This check ensures that `assign_async` and `start_async` from Phoenix.LiveView.Async
      are not used directly in the codebase.
      These functions are disallowed because they do not work correctly
      in async tests when Sandbox is in auto mode.
      """
    ]
  @doc false
  @impl true
  def run(%SourceFile{} = source_file, params) do
    issue_meta = IssueMeta.for(source_file, params)
    Credo.Code.prewalk(source_file, &traverse(&1, &2, issue_meta))
  end
  # Match full module path: Phoenix.LiveView.Async.assign_async/start_async
  defp traverse(
         {:., meta, [{:__aliases__, _, [Phoenix, LiveView, Async]}, func]} = ast,
         issues,
         issue_meta
       )
       when func in [:assign_async, :start_async] do
    {ast, [issue_for(issue_meta, func, meta[:line]) | issues]}
  end
  # Match aliased form: Async.assign_async/start_async
  defp traverse({:., meta, [{:__aliases__, _, [Async]}, func]} = ast, issues, issue_meta)
       when func in [:assign_async, :start_async] do
    {ast, [issue_for(issue_meta, func, meta[:line]) | issues]}
  end
  # Match direct function calls: assign_async/start_async (imported)
  defp traverse({func, meta, _args} = ast, issues, issue_meta)
       when func in [:assign_async, :start_async] do
    {ast, [issue_for(issue_meta, func, meta[:line]) | issues]}
  end
  defp traverse(ast, issues, _issue_meta) do
    {ast, issues}
  end
  defp issue_for(issue_meta, function_name, line_no) do
    format_issue(
      issue_meta,
      message:
        "Found disallowed call to Phoenix.LiveView.Async.#{function_name}/3. Use `MyAppWeb.Utils.LiveViewAsyncUtils.#{function_name}_aug/3` instead",
      trigger: "#{function_name}",
      line_no: line_no
    )
  end
end
and finally run mix credo to see what needs fixing.
I am not recommending that anybody do this. I only provide the information because I feel responsible for people who followed the OP.