I have not played for a long time [;)] I am hardly a guru on the game, but I tend to manage just fine.
Automation is as you prefer. I have run constructors on auto apart from giving the odd job manually (just put auto again once you have given your order and it will do whatever it finds useful once your job is completed). I don't have issues apart from fuel issues, and once in a while I lack materials and order a manual mine somewhere. I would not recommend new users to mess with manual exploration and construction, it is better to get control of other areas (the fighting) first. And doing manual exploration is either time consuming or to efficient...
Long distances are generally not good, this also goes for colonies. Of course, you if it is a cluster/ring you may want a new colony as beachhead. Since constructors are on auto you don't need to worry about bases. The AI may build a remote mine, and my main issue with these is that they can be hard to protect against pirates. I sometimes demolish these and build other bases to cover the loss manually.
On new colonies I send a small fleet in advance. I book a mine on a gas source once it is colonized. I can live with the first space port taking an eternity and a half to construct, but you better leave protection in the system while it is built.
A large space port is expensive. If you need more shipyards, make your own modification of the current one with more constructors/shipyards (follow the ratio already there). You don't need more than a medium for a while if you start as a beginning, single colony. And the large one is expensive to maintain (you don't have excellent home world, do you? [:)]. Just make sure you don't remove the shipyards by accident by accepting manual upgrades. In general I can't remember much issues early on.
As for fuel. Make sure you designs have enough energy collectors to cover static energy usage, or they will drain fuel while idle. I think maybe a patch made this default for all races. In general, I have all military ships in fleets. The starting escort and frigates tend to be my only automated ships, and they die soon enough (after doing early fleet work). Once my empire is large and I am filthy rich, I may order 80 frigates and 80 destroyers at once to have some automated pirate protection.
As can be seen in my messy AARs, I tend to use raider and battle fleets (with maybe a larger home fleet). The raiders are typically 4 destroyers. I spread these around my empire to kill pirate raiders and pirate bases on manual orders. Home colony is a nice thing. Battle fleets are maybe 8 cruisers when they become available (and get capitals later), for anti pirate work I may also send a single (or two) ship from these, so I for instance don't need a raider fleet to protect my home grounds while the home fleet sits at the capital. This is in peace time. During war the raiders are... raiders. They hunt mining bases. The battle fleets hunt enemy fleets and settled systems (busting space ports is fun). A good battle fleet is large enough to bust a medium space port and the normal enemy fleets. If the enemy got a giant fleet, combine multiples in larger fleets. In most cases it is more handy to hit multiple targets at once. I wonder how the enemy feels when I dismantle the infrastructure (space port and mining bases) in 3 systems at once
I agree on the retrofit. Compare your current design to the latest. I usually wait until firepower/shields improve. When a good upgrade arrives, I do upgrade all, but not at once in order to have some ships ready. Notice that a fleet given the order to retrofit will head to its home colony. It is useful to keep this updated, and in general pay attention when you do retrofits...
I hate damaged ships and bases, I kill them. I did a few tries with stories to see how that played out, but it would not trigger, and the stupid debris fields annoy me to no end. And they take a long time to kill even with a proper fleet, lots of micro management in attacking everything.
If my frigate, escort, or explorer is damaged in far away lands, I scrap them. A spare constructor is a wasted constructor. If I need a constructor job done real fast, I grab the closest one, and either ignore its order, or queue it to repair whatever it was building (and then set auto afterwards).
Bases you find may be useful but is usually not. If it is a useful fuel source I may let them stay around. For instance, I may have gas mines in a gas cloud. After some pirate busting I inherit a pirate base in the same cloud (different location). If it is useful fuel, why bust it? It works, and it can even defend itself...
And guarding bases early on is indeed necessary, but I tend to do that with fleets. Never trust the AI to run a military ship
(After trying various automation, doing automated military and doing to rest my self remains. That could be interesting and nerve wracking).