I have good news and bad news. The good news is that I went quite deep down this rabbit hole while working on
Re-integrate NNlibCUDA as a package extension by ToucheSir · Pull Request #445 · FluxML/NNlib.jl · GitHub. The bad news is that it ended up at Package extensions for Julia < 1.9 - #17 by ToucheSir. TL;DR there isn’t much interest in having the Requires.jl fallback precompile.
It appears to me that part of the grand bargain of package extensions was that migrating from Requires.jl would at least be latency-neutral because the problems with precompilation were already present in the existing codebase. Adding new code under a @require block breaks that agreement, which is why I gave up on the aforementioned NNlib PR as-is. Perhaps StaticArrayInterface was not aware of this caveat when they added the Requires path?