Thanks for the report!
This situation should be handled more gracefully by ggplot and thus requires a fix.
Essentially, because the limits include 0, the axis assumed 0.1 is the smallest number to label via the negative.small argument.
However this is still bigger than your largest number, so it gets the order wrong.
In the meantime, you can set the negative.small argument to a smaller number, for which I recommend a number smaller than your smallest non-zero data.

library(ggplot2)

data.frame(
  "Y_val" = c(0, 1e-6, 1e-5, 1e-4),
  "X_val" = c(10, 20, 30, 40)
) |>
  ggplot(
    aes(
      x = X_val,
      y = Y_val
    )
  ) + 
  geom_point() + 
  scale_y_continuous(
    transform = scales::pseudo_log_trans(sigma = 1e-7, base = 10),
    guide = guide_axis_logticks(negative.small = 1e-7)
  ) 

^{Created on 2024-09-25 with reprex v2.1.1}