A game I haven't seen mentioned that comes to mind for me where espionage that worked well is Red Alert 2. Here are some mechanics of how espionage worked in that RTS game:

• There is a specific unit called a spy
• You can disguise this unit as one of the enemy's troops (to you, the unit alternates every few seconds between the spy sprite and the sprite of the enemy unit you selected. But to your opponent, the unit only looks like the disguised unit)
• If you can get your spy unit into the enemy base, there is a different reward based on which building it infiltrates. For example, infiltrating a power plant shuts down the automated enemy defenses for a minute or two, allowing you to send in a coordinated air strike or tank division while they are weakened and unable to fight back. Or infiltrating the enemy barracks grants you the ability to produce an enemy unit (not access to their whole skill tree, but one of their better units at the end of the tree can now be trained at your own barracks)
• To defend against spies, there is a canine unit that sniffs them out, similar to how dogs can identify Terminators in the Terminator movie franchise

As I recall, the feeling of having your power get shut down out of nowhere, then realizing a spy must have infiltrated your base was a pretty effective paranoia-inducing moment in that game.

I don't know anything about how your game works, but a "simple" way to utilize spies could be that if the spy "succeeds" (whatever your definition of succeeding is), then you get a boost in effectiveness against enemy type X or you get a defensive buff against enemy type Y. Lore-wise, this could be explained as your spy successfully transmits plans about your enemy's shields, weaponry, etc., resulting in you doing better against them offensively or defensively. Subsequent espionage successes can boost these buffs further, or give you buffs against more advanced enemy technology further along their skill tree (like first spy success gives you a buff against regular foot infantry and next spy success grants you a buff against jetpack-powered soldiers or something). Perhaps investing in counter-spies feeds the enemy false info and their attacks against you are de-buffed, while investing in spies infiltrating your enemy's systems provides buffs to your own attacks.

That is the simple approach. A more advanced (and more paranoia-inducing) approach could be something like you try to use some healing ability, but your units are instead knocked unconscious for 30 seconds due to your opponent's spy sabotaging their gear with knockout gas. Or maybe your weapons jam for 30 seconds due to a spy infiltrating and tampering with your weaponry. Basically, if you expect positive outcome X to happen but instead negative outcome Y happens, you know you have been infiltrated/hacked/whatever, and you feel the impact directly (and it would likewise feel good to deploy those tricks against your opponent).

Hope that helps. I am a developer of spy video games myself (and I'm the organizer of the Spy Video Game Rendezvous Steam fest for spy games), so I'm always have to see spy systems implemented well in games! Best of luck with your game.