I was on a "issue" thread in another forum section, and someone mentioned mods, and a site, Skyrim Nexus. I hopped over and some of what I saw was useful and pretty cool even to the casual observer. Problem is, I don't think I have ever modded, or even added a mod, fix, or player made workaround to a game. So Where the heck do I start? What program or utility do I use to open or add them to a game?
How do I know if a site is "safe", because the AV program might say it isn't when it is in fact?

The Skyrim Nexus is 'safe'. Use at your leisure.

Each mod explains how to install/uninstall in the description of the mod. Simply follow those step and fire up your game. Most mods are simply download this zip file, extract/copy files into some folder on your PC, and start game. Each mod also has a discussion or comments section so you can get feedback on how others' have liked the mod, if it worked, etc.

If any mod is overwriting existing files (99% won't) you should backup the overwritten files in case you want to remove the mod.

Right now, the full modding tools (Creation Kit) is not released so mods are limited in what they can do and how you set them up. Just make sure to read the instructions for each one. Down the road there will likely be a mod manager, and tools that automatically keep your mods ticking just nicely.