Having run with the modding community for a while, I can honestly tell you there really isn't much that you can do with a CFW PSP. While most people claim homebrew as the reason for installing it, piracy is usually the result. However, a list of things I did with my PSP include:
Cheat (Either CWCheat or nightPR)
Youtube
Cave Story
Wagic: The Homebrew
Emulators
EDIT: Custom UIs before Sony added it
There are other things, but either it's shady or I don't remember.
And while this isn't on the software side, I did hardware mods including sound-reactive LEDs, a custom paint job, LEDs in the shoulder buttons, and the Ring of Light mod.
www.acidmods.com can tell you more.
Also,
qj.net has a PSP section with the newest and most popular homebrew apps. Check it out.
