A few thoughts on reading through this, in no particular order:
1. If you're looking for more-than-binary roll outcomes, maybe take a glance at PF2 and their critical success-failure ideas. There, it seems any check has 4 possible outcomes; and while over-codifed (of course it is, it's PF after all!) the general idea is worth a look.
Something I do, very informally and always situation-dependent, is let the die roll not only set the succeed-fail outcome but the
degree of such. Let's use the sneak-past-the-dragon example, and say for these purposes that the outright pass-fail point is a natural 10 on the die. Well, if someone rolls 11 I'm going to narrate their sneaking as being barely good enough, and maybe even that the dragon noticed something amiss* but didn't specifically notice you. Meanwhile, rolling a 19 means you sail through, no problem. Ditto the other way - rolling 8 means your sneaking wasn't up to scratch and the dragon's now alert* to something, while rolling a 2 means you blew it and now there's a dragon in your face.
* - if I'm giving the dragon a perception check these middling rolls will inform how difficult/easy said perception roll will be.
2. For obscure knowledge checks I almost always give a roll (even if the odds are very poor), if only because we haven't roleplayed every minute of these characters' lives and who knows what obscure knowledge or trivia one or more of them might have picked up sometime in the past; be it during class training, or at grandma's knee, or while faffing around in the pub one night.
How I do this is that whoever asks gets a roll, then characters of class(es) that might know e.g. Clerics get a roll for anything religion-based, then I'll give one group roll for everyone else combined. And depending how obscure the sought information is, even a perfect roll might only get partial info.
3. The biggest problem with "make every roll meaningful" is this: players very quickly learn in the metagame that no roll means nothing to see here, even though the PCs have no way of knowing this, and this metagame aspect negatively affects play. Thus, I'm constantly throwing in 'null' rolls - an example of such might be searching a room for something that I-as-DM know isn't there - a good roll means they're also now sure it's not there, but a bad roll means they aren't sure if a) it's not there at all or b) it's there and they missed it.
4. Regarding "try until you succeed" situations: I always interpret a roll as being the absolute best shot you can give it, given the situation at hand. Thus, there's no such thing as 'take 20' or equivalent - if the DC of a lock, for example, is 17 and you've got all day to try and open it, if you roll a 15 you ain't opening that lock no matter how long you spend at it - this time, the lock wins. You don't get to keep rolling until you succeed.
Yes I know this goes against 3e-and-forward design. Tough.