Firstly, you don't -need- to add any mods. You can just play the official campaigns first.
The original NWN2 campaign... has its moments, though the first chapter is extremely long and tedious. But it's a good way to learn the game and pretty well written in general. (Although the story is very generic.)
The first Expansion, Mask of the Betrayer, is widely and rightly hailed as a masterpiece. It has a frustrating mechanic that you can mod out if it bothers you too much, but you really should play it. Although you shouldn't start with it. It's meant for a character that's finished the original campaign. You can start it with a new character instead, but because it's an epic level campaign it's highly advisable to learn the ropes in a simpler environment first.
The second expansion, Storm of Zehir, is a more old-school party-based map-exploring RPG. Fun and different, but not nearly as interesting story-wise as Mask. Or even the original campaign. It's a low(ish)-level campaign, but again not recommended to start with because it's pretty tough and you have to create a whole party that works well together.
The official stuff should keep you going for a -long- time. But if you want more, there are plenty of modules out there. I'm not a great expert on what the best are, though. Honestly, I never found the NWN2 modules to be nearly as good as the best NWN1 modules. NWN1 had a much bigger community that produced much more quality stuff.
Still, from the top of my head, I'd recommend:
*) The Maimed God's Saga, which came up in another thread here just earlier. A very atmospheric and moody adventure about a cleric. Well written and interesting if you like forgotten realms lore. Very low magic. Focussed more on story and RP than on actionI liked the earlier half better than the last act, but it's one of the best I've played either way.
*) The Conan Chronicles. A series of modules that's very well done and, as the name suggests, set in the Conan world and loosely based on those stories. A great place to explore if you want some non-Forgotten Realms type fantasy. Good mix of roleplaying and combat. Should keep you busy for a long time.
*) Tragedy in Tragidor. Silly title, but a lot of fun. One of the oldest NWN2 mods but still pretty good. A re-make of an NWN1 mod, actually. (Conan Chronicles are also an NWN1 remake, in part.) This is a more basic adventure. You have to help defend a village from an orc siege. More focus on combat, but also a good story and a good small-town atmosphere. I found it to be quite moving. Medium length.
There are also persistent worlds, which are like mini MMOs. But I never played those for NWN2, only on NWN1.
_______________________________________________________
Now, modifications. (In NWN parlance, they're typically called overrides or haks, depending on where they're used, to avoid confusing with modules/mods.)
First of: You don't -need- any. The base game works. Sort of. It's sluggish and the camera is horrible, but I never found a modification to fix this. But if you want tweaks, there's a lot out there.
*) Notable is the PRC pack. (Player's Resource Consortium, but also Prestige Class.) It adds a giant quantity of classes, feats and spells to the game. Mostly prestige classes, but also a few base-classes. Oh, and more playable races, too. It's needed for some Persistent Worlds, and can be made compatible with the original campaigns and most user modules. It can definitely shake things up if you've gotten bored with the base content.
But... there's already a -huge- amount of classes, races, feats and spells in the base game + expansions. I really recommend against installing this if you're new to the game. Also, the balance isn't always the best and sometimes it can mess up the scripting in modules. (Say a module checks what class you are to give you an item: a wizard gets a wand, a fighter gets a sword. But you're some PRC special Hexblade class that the designer didn't account for. What does he get? Module error.)
*) Less Obtrusive Spell Buffs.
http://neverwintervault.org/project/nwn2/other/visual-effect/less-obtrusive-spell-buffs This one is highly recommended! It stops your party from looking like a lit christmas tree when you cast a bunch of buffing spells. And since NWN is all about casting buffing spells to survive, this helps a lot.
*) Adonnay's Elven Weaponry override:
http://neverwintervault.org/project/nwn2/hakpak/original-hakpak/adonnays-elven-weaponry Very highly recommended! This makes the swords in NWN2 look less butt-ugly. In fact, they look pretty cool now. Axes and everything else still look terrible, but with this override swords no longer look like they're made of foot-thick plastic.
*) Tony_k's companion and monster AI.
http://neverwintervault.org/project/nwn2/script/companion-and-monster-ai-22 Gives you increased control over your party members' behaviour when they're running AI scripts, and makes monsters smarter. Makes the game much better.
Anyway, I think that should be enough to get you started. There's lots more out there, and my knowledge is actually a few years out of date, so it's quite possible there's awesome stuff out there I've never heard of. Still, have fun!
