Tips for Using Spies in Mid-to-Late Game
You need to get your +espionage bonus up and those missions will have decent success rates.
Remember ambassadors and the empire leader can get espionage skills as well.
Best strategy is to find a pirate base and send all your ambassadors who aren’t really needed elsewhere there.
Then have your spies start low-risk missions against those pirates.
To get a leader with +espionage, you just need to dismiss your current one a month or two before the espionage missions end (dismissing leaders is BAD under some governments). You’ll either get a leader with less than 4 skills and they’ll pick up espionage, or you’ll have a chance to get a leader with it whenever a mission completes.
I really don’t care for the spy mechanics as they are now in the game. As you stated, they are pretty useless by mid game. Part of this is because spies apparently get no experience thwarting incoming attempts. Only gain experience doing other missions. Once you’re best spies are captured, which will eventually happen, your noob spies are just hanging out with little or no experience gain.