Used it on and off mainly to check it out, but always in a personal/experimental capacity. Never managed to convince any teams to give it a try, mostly because git don't tend to get in the way, so hard to justify to learn something completely new.
I really enjoy how local-first it is, as someone who sometimes work without internet connection. That the data around "work" is part of the SCM as well, not just the code, makes a lot of sense to me at a high-level, and many times I wish git worked the same...
I mean, git is just as "local-first" (a git repo is just a directory after all), and the standard git-toolchain includes a server, so...
But yeah, fossil is interesting, and it's a crying shame its not more well known, for the exact reasons you point out.