Back to the original question, I think you're asking too much of one boat. Although, the Mad River canoe is pretty close to hitting it. It *will* do everything, just as an XR650L can do everything. Very good analogy. If you stick with the one-boat thing, that's a great way to go.
To stick with that idea, I really dislike SOT's, and many other of these recreational boats. They're a lot like an XL125. Too slow to go on the highway at all, and they pretty much suck in single track too. Fine for plonking around, and great for beginners, but you'll outgrow them pretty fast if you're serious about this at all. Don't get me wrong, better than nothing, and can be fun to use in certain situations. They are friendly, and allow some people to get out who wouldn't otherwise be able to. But if you actually want to run rapids, or actually go on a trip, they're completely useless.
My parents bought some 10 foot kayaks from Costco, and my goodness do they suck. They're so slow, and they are darn near impossible to paddle straight, and I'm a very experienced paddler. Frankly if I had to go somewhere on water, I think I'd rather swim.
My cousin bought a 12 foot kayak from a real boat store, and it's better. Nicely outfitted, and tracks straight as an arrow. But it's still slowish. The hull was designed for stability and straight tracking.
I've got a Dimension R5 kayak, an old design, but man, what a great boat. It really does everything. If you packed ultra-light, you could use it for a 1 week camping trip. I can sustain about 7kph IIRC all day long. And I've shot class 3 rapids on it. Too long to "play", but it can shoot rapids well.
We bought a Swift Temagami kevlar canoe last year. Really nice boat. 17.5 feet long, only 54 lbs, slightly wide for stability and cargo. Quick on the water, but not a racing canoe. Not suitable for whitewater at all though. I mean... *you could*, but it's fragile.
This is the family boat. It's HUGE, but light enough to throw over your head.
My wife and I did the Baron Canyon run last year, 14km round trip at a leisurely pace in about 4 hours, including a couple portages, 1 hour lunch stop, and 30 minutes sheltering from a storm. You are just not going to be able to do that on a SOT.
I've never seen a tandem kayak that could run rapids either.