Moderation is a good idea for just about anything.
Caffeine can help some people stay alert, can reduce headaches, and can even make breathing easier. It has found a wide variety of roles in the body. It's used in treatment of apnea, asthma, some pain relievers even include caffeine. For this I'd say it's good.
However, overconsumption of anything is bad. Drinking too much coffee can be dangerous for some people with immune disorders, heart disease, etc because they have trouble metabolizing it. Women, in late pregnancy and when ovulating, have been shown to be more effected by caffeine. People develop a sort of immunity to it, so to speak, just like anything else. But it is different for everyone, depending on their unique physiology and situation. And it can be addictive just like nicotine, which has been hypotheized to be healthy in moderation (nicotine, not smoking).