3.6's are known for oil consumption and have timing chain issues, 3.2's need the timing belt done every 100K and are known for thermostats, and as far as tranny's they're hit and miss. Some members have issues, but alot of members don't. Just depends on your driving habits and maintenance.
I have a 2004 CTS with 120,000 on it, other than worn plugs at 97,000 miles, the engine has been fine and uses no oil. Always Mobil 1 Synthetic changed at 50% oil life.
The timing chain issue was on later years because GM changed the chain and gears to a quieter pitch to reduce noise and screwed it up.
Tune up you have to remove the upper portion of the intake, 6 bolts and lots of electrical connectors and a few hoses to pull the coil packs out to change the plugs. The front differential mount always goes bad, if you push and pull up and down on the front of the differental where the driveshaft goes in it shouldn't move up and down. I replaced mine with an aftermarket high performance urethane bushing. The third brake light over the license plate always goes bad on these too, the ballast for the neon tubes rusts out. New the part lists for $500 for the entire light, replace with LEDs retrofit much cheaper, also aftermarket.
Of course, how it was driven and maintained, etc, has the biggest impact. Definitely have a mechanic inspect the vehicle before purchasing it. It'll be money well spent.
first things first, when buying a used car make sure there are no dead hookers in the trunk.
we will need to know if its a 3.2 or 3.6l engine. the 2004's had 2 completely different engine designs.
the 3.2 origanated form the cadillac catera. although the catara had a bad name, the 3.2 had almost all the bugs worked out of it and came with larger bore than the catera.
only a few smaller issues with the 3.2 like servicing the belt is a pain, spark plugs and thermostat are not as easy to change as s honda and the valve covers leak oil. if the car wont start its probably the crank position sensor.
the 3.6's have more power but keep in mind the 2004's were the guinea pigs for this motor. gm has made tweaks to it over the years, and it is currently thier most popular engine in a slightly different variant.
you have to watch out for timing chain issues, stalling caused by thottle body issues and oil consumption.
all cts years are plagued with complaints of a loud diff. putting in LSD additive, and high grade diff fluid (i use royal purple) keeps them running better.
seeing that you ride a harley, you are probably no stranger to a tool box and oil leaks, so you should be fine.
I ride a harley as well, but its not even broken in yet.
I have a 2004 v6 with 112k and when I brought it the 3rd light right away wasn't working replaced with led strips also I heard the front bushings are known to go on these cars replaced. Runs fine now, just have a small oil leak which I can't seem to locate where it is coming from , took it to 2 mechanics and they can't seem to locate it either. But it leaves a small spot the size of a nickel every time I leave it in the driveway.