The only mods I know that change actual gameplay quite dramatically are sacrificial mods, which some I don't think could be discussed here. But he has a great zombie mod if you're into that stuff and his road to fame mod is the best friend of get famous. Really enhances the pack. You should check them all out to see if he has some you're interested in. And on his website he goes into great detail of what each one does and what it will add. I like that.
Other than that I agree. Most just help you use imagination like pose player (one of my favorites)
Personally what I'm always hunting for is anything that adds more interactions and animations.
Like raaven (I think her name is in patreon) that collab with someone and made a kitchen set for kids and toddlers to actually use.
Oh that reminds me of a mod that adds a lot to my gameplay. zulfina kids can mod. With this kids can garden, cook, use sim ray, use magic, do yoga, ride bikes and a few other things.
Also she has one that adds doctor interactions so kids can play doctor. Or as adults you can check temperature and a few other things I can't think of because I have too much lol
I also believe zero has mods that you might be interested in. Many are just mods that fix tiny annoyances but she has some like become a hybrid vamp and spellcaster, become a traditional witch where you can summon sprites and stuff I can't remember now because it's too much. She does a lot of occult enhancing. One of her mods give vampires more powers like become immune to sunlight for 3 days or wherever, or see your reflection in the mirror etc.
Thepancake1 has a mod that adds slow dancing, (sacrificial too) dance on counter, see saws for kids, kiss a sim from behind while they are cooking, read book on bed and stuff like that.
Another modder I can't think of the name for added a rocking horse for kids and toddlers as well as functional bunk beds.
Meaningful stories is a must to keep those emotions balanced. But personality wise I tried them all and haven't found any that I thought enhanced personality if that's what your seeking.
And there is another mod that unlocks all interactions from all careers because I don't play careers I would never get to use them without it.
Triplis has a mod to give kids 6 new traits. Love the energized trait. You can make the kid eat candy, run around, fight self and a few others lol