Can someone clarify how Spot Pass on the 3DS works?

SpotPass is the generic name for any passive Internet feature on the 3DS, just like StreetPass is the generic name for any passive local wireless feature. SpotPass features are indicated via a blue LED and blue dot on icons, like StreetPass features are indicated via a green LED and green dot on icons.

SpotPass works via scanning for accessible wireless routers when the 3DS is closed. If it finds one, it synchronizes data for any games you have SpotPass-enabled. For some games like Kid Icarus: Uprising this is basically just an "Internet StreetPass" but other games like Samurai Warriors Chronicles have used SpotPass to distribute free DLC to early purchasers. Non-games like Nintendo Video also use SpotPass to get updates.

Mario Kart 7 does add people to your StreetPass Mii Plaza population if you've played online with them, although as you noted you can't use them in Find Mii, nor Find Mii 2 unless you pay their hiring cost. I wouldn't actually call this SpotPass, but since it's a term Nintendo made up who knows if they want to call it that or not.

Nintendo has distributed other items for StreetPass Mii Plaza over SpotPass. A new Kid Icarus: Uprising puzzle panel, and a single piece of it, was sent to everyone on February 27th. And on the one-year anniversary of the 3DS's release, Satoru Iwata (in Europe and Japan) and Reggie Fils-Amie (in North America) will visit your plaza after arriving via SpotPass. They do have puzzle pieces and can be immediately used in Find Mii, and will give you the achievement for meeting a Special Mii.

It's much like the Wii Connect24. It allows your handheld device to be connected via the internet while it is on standby so your friends can connect, interact, and leave messages with out you having to be there.

SpotPass will allow your handheld device to connect to the Nintendo's servers (via your home Wifi network or an open network) and download game trailers, patches, new levels, add ons, demos, and updates (Nintendo has been using SpotPass to push updates since May). It also allows you to sync/transfer your old DSiWare games (if you have them) to your 3DS.

Spot Pass is basically content received through internet connection. For example, receiving new Nintendo videos, getting people to join on Mario Kart via Internet connection (as you said), and more.