As a DVR/HTPC or just a media playback device?
If the latter just use a WDTV HD live type device you can playbock files off your server or whatever.
If for recording, any lightweight system will handle that the real money cvomes in on the card you use for TV input.
You can do cablecard tuners or simple clearqam (very cheap but not able to record anything but free to air content) So for Fox, ABC CBS and TNT type channels, you are good to go.
If you live near a large city you can get an antenna and use ATSC signals to pick up your local HD TV stations and get higher quality than you would over Cable but with possiblity of distance/EMI or inclement weather disrupting yoursignal. This has the benefits though of higher quality and no monthly service charge from the cable company. (Or having to fight with them to rent a cable card and them telling you they dont do that even though they do they just dont know they do).
Ceton makes some nice cablecard tuners up to 4 channels simultaneously I think and prices have really dropped as well.
there are also prebuilt options but they are not HTPCs exactly...media playback or PVR/DVR type things like the homerun HD