On that note, how aggressive should I be S-modding ships?
Depends on the ship in question and your other story point usage. Early smods on frigates is a nice way to get some bonus exp and if you scrap them later, you get 100% total refund. Now, you always end up with 100% even on capitals but you're unlikely to scrap capitals and they don't give any initial bonus exp while frigates give 75% iiirc. If you find yourself using story points on other stuff that gives bonus exp, this is less of a reason to smod aggressively.
Logistic ship smods are always good investment, especially on the biggest ones, since you will likely be using them the entire playthrough. As soon as you get an atlas or prometheus, smodding their respective cargo hullmods is a good idea.
If you find you lose ships often, smodding is one of the ways to secure "almost always recoverable" for that ship (the other ways being bulkheads, hull restoration skill, or having an officer on board). Just having an smod on the ship works.
Of course, an smodded ship is also just better than one that isn't, which means you can take bigger fights. Bigger fights means more exp which means more story points for more smods.
Also if you really hate hyperspace storms (and other terrain CR damage like pulsars), smod solar shielding. Completely blocks storm damage if it hits that ship. Fully shielded fleet can just ignore storm damage and sit in blackholes.