Is this just for the official content (original campaign, Shadows of Undrentide & Hordes of the Underdark), or for user-created modules? I ask because many modules incorporate their own visual upgrades...

Well, there are basically two ways to go about it: haks and overrides; the latter being easier for newbies because you can just throw them into the override dir whereas haks have to be infused into modules using the toolset (CEP, Project Q etc).

This override upgrades creature models and textures:
http://neverwintervault.org/project/nwn1/hakpak/combined/creature-override-compilation

This hak adds ceilings:
http://neverwintervault.org/project/nwn1/hakpak/tileset/nwncq-project

I don't like the ceiling one much, but you can see how it looks in my Siege of Shadowdale recounting, that starts here:
http://lilura1.blogspot.com/2015/01/siege-of-shadowdale-part-i.html

For other overrides, you can read this post:
http://www.gog.com/forum/neverwinter_nights_series/texture_mods_etc/post7

I also recommend these, cuz they're wonderful and EZ to setup:
http://neverwintervault.org/project/nwn1/hakpak/original-hakpak/customize-character-override-hak-ccoh
http://neverwintervault.org/project/nwn1/hakpak/original-hakpak/alternate-combat-animations-pack-acp-v40

Hope that helps ya a lil' bit. Good luck, NWN mods and modules are a jungle.