I worry that whatever you choose, your son will be dissatisfied. Maybe your kid is different, but most the kids I've known wanted a pet they could bond with. They've wanted a pet that was interested in what the kid does, that silently listened to the kid expressing their issues, and that offered things to do, like: "feed me", "pet me", or "play!"
Rabbits might work, but they might be too interested in one another. Even if they weren't forbidden, you'd run into the same issue with social birds like cockatiels. The only pets I can think of that give kids the attention a kid craves are the animals that have lived with humans for hundred if not thousands of years. Basically: dogs and cats. After that you get into cows, pigs, chickens, goats, and the like. Goats are wicked smart, but the males stink to high heaven unless neutered young. If you raise geese from goslings, they will love you forever and ask for cuddles whenever they see you -- but they have to stay outside and do make a mess. Someone else might correct me, but I doubt you want a pig -- partially just because they end up getting so big (I'm told there's no such thing as a teacup pig).
If the only issue with dogs is allergies, you could consider a low-allergenic breed. Dogs are simply the ideal pet for people with the time and space for them. They've lived with humans nearly forever and do all the stuff a young boy wants from a pet. Cats require less attention and can be caring and affectionate if they want to, but that depends on the particular cat. I've had turtles, ferrets, parrots, fish, and lots of other animals as pets and only the parrots and ferrets held a candle to dogs and cats. Fish-wise, I found the most exciting to be South American Cichlids -- but mostly because I had a Green Terror who fell in love with a Blue Acara overnight such that the two of them tried to kill everything in the tank in the span of 10 hours. I got them their own tank where they raised many babies.
Really good input here, and I suspect you're right on the disappointment factor. At least with fish, care is somewhat easy if you don't get crazy spp, so I'm not stuck with a high maintenance pet he didn't want in the first place if things go south.
A low allergenic dog would probably thrill your kid, and even if it didn't, I'm betting the adults in the house would quickly fall in love. I'd avoid Schnauzers because they are harder to train (they're smart enough, but will test whether they should actually listen to you or not -- whereas Basenjis are hard to train because they aren't that bright). Wheatens and other similar terriers are likely to have similar stubbornness issues. Maybe an Irish Water Dog or Lagotto Romagnolo? I don't know those breeds but I see them listed as low-allergy. I hate to link to reddit, but their LR sub says they really are pretty safe.
Do guinea pigs or rabbits qualify as "rodent"? Most of the people from the ew-faction have a specific dislike for long, naked tails, which neither of those two have. Worth a look, at the very least.
If you do decide on either of those, please get minimum 2, they will die if kept in solitude.
I had two adults, they were near silent. Though they were left to roam the house at all times, in the end I never really knew where they spent most of the night. Their cage was in my bedroom though, and that was the only place where they got food.
Rabbits and guinea pigs can be fun, I'd toss out chinchillas as another interesting option though those might be harder to find.
How do the wife and kiddo feel about live feeding? Plenty of reptiles need things like mealworms and crickets in addition to their flakes or pellets. Turtles, at least types you'll find in pet stores, will typically get live feeder fish in addition to their pellets as well.
i was going to suggest guinea pigs, too! they might be too "rodent" for wife, but honestly they're a lot more like smaller, low-maintenance rabbits. they have personalities for sure, and as long as you handle them from a young age, they can be picked up and hang out on your lap while you watch tv or whatever.
Sphinx cat. No dander to upset the allergies and they're kind of high maintenence which would be excellent for teaching hygiene to the kiddo. Like you actually have to bathe them. Too many boys grow up avoiding showers and baths.
Other than that though they're generally very chill animals. As much as cats can be chill. Not recommended for an indoor/outdoor animal as excess sun can cause this breed problems.