This looks excellent so far.
I have some suggestions for improvements:
1: Show the username of the person who uploaded the mod/asset. This makes it possible for people to be properly credited (especially for those who upload art assets), and it also helps to avoid some potential confusion.
2: Create a comment system. Being able to have small, on-site conversations would prevent the need for running back to the forum each time a user wants to tell somebody something.
3: Original size front-page previews. It's hard to tell what I'm looking at when there's just a big pixelated mess popping up. This deals mostly with art assets.
4: On the art/mod submission page, you might want to change the background image of the field headers (Name, Description, Image, etc) to something a little more simplistic. At first glance, they don't look like headers, but rather text entry fields. A simple blue horizontal bar would go a long way towards making the page look cleaner.
5: Being able to include multiple images. Sometimes one image just doesn't tell a whole mod's story. You could have the user select the main image they want used for the thumbnail, and then allow them to upload two or three more, so people get a better idea as to what exactly it is they're downloading.
6: Use the mod's/asset's alternative text on the front page slideshow instead of the full description. Would make things a lot cleaner, and make more sense as well. You also might want to consider showing the name of the mod/asset that is being shown, because it isn't always obvious from the description.
7: Not necessary, but it would be cool to be able to embed youtube videos into the mod descriptions (If you use the above suggestion, that is). Just something to consider if it's not too much of a pain, because videos are a hundred times better than just a few screenshots.
Also, a bug: The "Forum" and "Blog" links on the front page don't appear as links--clicking on them takes you to the appropriate page, but the text isn't highlighted, nor does the cursor change when you move your mouse over them.
Overall though, this is an excellent start. I can't wait to see this in a completed state
Keep up the great work.