I'd like to tune the game's existing traits so they have a much bigger impact on gameplay and make it far harder to build relationships if two Sims don't click. For example, instead of getting a small, almost insignificant moodlet (+1 Tense I think it was?) when they haven't used their mental skills for a while, Genius Sims would get a massive negative moodlet, something like Sad that greys out several social interactions, and by massive I mean +100 massive, not something that you can drown out with positive buffs because they're in a pretty room and they've had tea. The only way to stop their misery would be to make them do something mentally stimulating. I'd also like to tune many of the interactions - to build on the Genius example, they'd be far more likely to react negatively if someone talks to them about things they don't find interesting, getting a +3 or so Bored moodlet and decreasing the relationship bar. How doable would this be? I've read the official guides and it looks like I could mess about with the weights to do this, but every example I can find is for creating new traits, not overriding existing ones.

This is doable, and fairly easy. You'd just use any override tutorial as your starting point for getting the files into a package, but you're right that there is no tutorial for this. You should probably look at other override mods to see what changes they did.