I explicitly dislike the idea of using Git as the backing store. Forget the fact that Git is not native on Windows and is immensely bloated; the actual .git folder is a mess for backup systems when working locally compared to a single database file.
Sure but there is a big difference between being stored once (modulo backups) on a central server, and every developer needing to download all the resources for every issue and the entire wiki in order to work on the code at all. It works fine for sqlite, because they only have a handful of developers, so it's not a big deal for them each to have their own local copy of everything. But having to download GBs of issue and wiki data in order to make a pull request (however that would work with fossil) or otherwise contribute is a significant barrier to entry.
* Changes being tied to a commit (akin to git notes) so issues could have different status and comments on different branches. No overall view of the project.
* Using their own branches to separate from code state, which made a huge mess when looking at your branches or git history.
* Using a separate space (like git notes does, I forget what this is called), which isn't included in the standard push/pull.
* All of the above being distributed, issues in your checkout could be very different than someone else's, and now you have to merge those too.
* No non-developer UI for project managers to see or comment on issues.
It just... never was something majority actually want so they didn't really get any traction.
Issues wise you also get few nasty cases where you really do not want to keep it with project, like having clients send a bunch of screenshots or even videos of triggering some bugs can grow storage pretty quickly... and while extra few GBs on a file server isn't a big deal, keeping it with code repo just so someone can look at tickets locally is PITA, and you quickly get into "let's not use it, it just makes everything complicated and everyone repo bloated".