In general, you only get personal batteries deeper into the game, meaning enemies are about to become trivial. The exception is space age, where you still don’t want to trivialize demolishers, but fighting pentapods DOES make sense. Being able to use a staff in your weapon slots (without ammo) would be very useful for exploring planets when you first drop. Point being, I expect most combat-centric items to already be falling off around that stage of the game (for vanilla, non spage).
If you want late-postgame relevance, then you will want to focus on utility-based magic. Waterfill/lavafill were just examples of the sort of effects I had in mind—not an explicit request for those things. There is a lot of potential to do wacky things.
If you want to focus on offense, the main restriction I would focus on is being able to start using magic even earlier into the game (eg right as you get green science—not after you already got blue science running). That is when non-automated offense is most critical, and offensive spells would have the most impact.
Alternatively, offense may make more sense in modpacks or settings with a ton of enemies, such as rampant/deathworld.
IMO, I would focus on the timing of providing offense at the right stage of a playthrough, as that is vastly more important than the actual details of the effect.