The following is a list of mods recommendable or at least of interest to just about everyone. Some of the credit here goes to modder Flash, who created the excellent Flash and FCR mods.
Update 5/18/2016: A more comprehensive and current list of mods can be found here, although it contains no descriptions. I may update the list below at some point to include universally recommended mods from this list with descriptions and current links.
An excellent overhaul mod pack which packs tweaks (skip intro movie, hide HUD), new features (visible wounds), and lots more. Includes options for harder difficulties. Fully customizable. Highly recommended even for first playthroughs.
Matchstickman says: "It was included as part of the additional content on the Enhanced Edition (Plantium Edition) DVDs. That should give you some idea of what CDPR thought of it!"
Full Combat Rebalance
Overhauls the combat to make it more deep and difficult and more faithful to The Witcher novels. Be warned: it's MUCH more difficult than vanilla, especially on Insane; be prepared to die a lot, at least in the first chapter. ONLY recommended if you've played the game already. Balance issues may occur, at least on Hard/Insane -- a new version is expected to arrive eventually which should fix them. Recommended for use on a new game only. Includes all features from Flash mod.
An aesthetic mod which adds scabbards (sheaths) to your outfit for carrying swords. Clipping issues may occur. Do not use with Texturen mod as serious performance issues occur.
GOG version installation advice
Sharpens the textures of most in-game objects, terrain, etc. 1.4GB file. May impact performance and/or cause stuttering; system requirements are increased a fair amount (min. dual core CPU). Do not use with Scabbard mod as it results in serious performance drop.
Hi-res character models
Designed to work with the Texturen mod above. Significantly enhances NPC textures.
TW1's quicksaves do not overwrite each other, so the savegame folder can get very, very big. This utility makes it easy to manage/delete the saves. Includes a variety of functions and options.
Stuff that makes Geralt's life easier
For those that don't mind bending the rules a bit, this mod adds three quest items to your inventory which let you meditate anywhere, turn on and off night vision at any time, and access storage from anywhere. Not compatible with Scabbard mod, though you may be able to make it compatible with fiddling (see link at bottom of post). Read the readme for proper installation. Thanks to Lexino for the mirror.
Extreme Immersion mod
Removes much of the HUD for elements already communicated in the game itself for maximum immersion, realism, and difficulty. Not recommended for a first playthrough. Recommended for use with 'Visible Wounds' option in Flash mod.
The Enhanced Edition of The Witcher contains several fan-made adventures, but there are plenty more. Below are those highly recommended or known to be widely enjoyed. I've not played any myself, so if anyone has any great experiences to share, please do and I'll add the adventure. Note adventures do not contain any voice acting.
Medical Problems I
An extensive adventure from Corylea containing 12 quests, 45 NPCs, 18 areas, 40 monsters, and more, lasting anywhere from 3-16 hours. In this adventure you must help Shani fight a new unknown sickness. Comes with an install manual, hint sheet, list of cool things you shouldn't miss, and walkthrough.
Medical Problems II - The Witcher Disease
The sequel, naturally. This time Geralt must save his comrades from a grave illness created specifically to overcome witchers' immunity to disease. Much like TW1 and TW2, it's recommended to play the first adventure beforehand, but not required or essential. This adventure lasts roughly five hours. Comes with an install manual, hint sheet, adventure file, and walkthrough.
Reviews of all fan-made adventures
ModDB page (mods & adventures)
If there's anything highly recommendable I've missed, please let me know and I'll be happy to add it.