Platinum, here is the lowdown:
The Turbo CD should at least come with a System 2.0 card. This allows regular CD games.
There is an upgrade card called the Super System card (System 3.0). This allows "Super CD" games. This is as far as US games ever got.
The CD games are region free, so they are import friendly!
In Japan, there was an "Arcade Card", which was the final upgrade. Only a few games were released. Unless you are a crazy collector who likes dropping money on $300 shoot 'em ups and cheap 16-bit Neo Geo ports, then don't bother with it.
I definitely recommend getting a Super CD (System 3.0) card. Some of these Super CD games are among the best 16-bit games there are. Examples include Castlevania Dracula X: Rondo of Blood, Lords of Thunder and Gate of Thunder. Unfortunately, as others have said, getting a US Super CD card is not easy/cheap. It might actually be easier to get a Japanese PC Engine (that is the name of the Turbografx-16 in Japan) Super CD system.
There is a reliability issue with the Turbografx-CD unit. One of the internal plastic gears becomes brittle over time and can literally shatter. If that happens, the CD unit is toast. Getting a replacement gear is VERY difficult. I do know of someone (over at the Pcenginfx.com forums) that can fix them, but he still has to look for the correct parts (only found in a specific mini cassette player, I think).
Due to the difficulty of getting a Super CD card, the reliability issue with the CD unit and the fact that CD-games are region free, a lot of people keep their Turbografx-16 for US games and also import a PC Engine Duo for playing CD games and Japanese import HuCards/Turbochips on. If you get serious about the TG-16, this is the best way to go. There are three versions of the PC Engine Duo. All are good, but the original Duo sometimes has sound issues (which ARE fixable without too much difficulty, unlike the original CD unit...).
So, the best route, IF you cannot find a decently priced US Super CD card, is to import a PC Engine Duo. Then you have access to ALL TG-16 and PC Engine games (except for a few Arcade card games, unless you buy that of course) and won't have to worry about the thing breaking down beyond reasonable repair. Getting a US Duo is also an option, but it is actually more expensive than importing a PC Engine Duo. Your call, there.
As for modding the system to get s-video or SCART, I think Tomaitheous might be able to help you. I cannot personally vouch for him, though. I don't know him except through reading his posts. I can personally vouch for some of the members at Pcenginefx, if you want a mod. Just PM me if you want a name.
Last Alert is definitely a fun game. I compare it to Ikari Warriors or the overhead levels from Super Contra, except it is more mission based. It would be worth buying if you weren't already getting it.