Good Spell Systems...
Let Magic Just Work - No saves, no chance to fail. But punishing hubris can be fun, but not so often or catastrophic that it keeps players from using it. (Attention From the Sources of Power)
Let Magic Users Use Magic - They should have a decently varied repertoire, and have access to most if not all of it at a given time. But requiring time or resources to be spent in order to do so can be fun. (I've got just the spell! ...where was that book...)
Let Magic Bend The Rules - Let the player interpret the spell to have an effect suitable to the situation, instead of spells being specific keys for specific locks. But a framework for resolving this can be helpful.(Freebooters on the Frontier/Maze Rats style freeform magic)
Let Magic Be Big - Magic should be impressive (Magic is Stunning), and occasionally extremely powerful. (Ultimate Spellcasting)
That said, if dungeon crawling, spacial exploration, and/or resource management is a central challenge of your playstyle, don't let magic trivialize those challenges. Be up front about this and gain consensus on what the game is about. Then, either replace the problematic spell/item, or let them use it but reveal a big downside or side effect, or tie the effect to a limited resource itself.
Good Spells are...
Evocative and Flavorful (interesting details) but not Prescriptive (narrowing use cases)
Versatile or Broadly Interpretable (if using FotF style freeform spellcasting) and ripe for "Hijinks" as Scrap would say, but not Redundant due to overlap of effect with other spells
Powerful and Awe Inspiring, not Underwhelming (though subtlety can be part of the effect)
Effective - it just works, rather than a chance for a fizzling null result - reconsider target saving to resist
Has a non-trivial inconvenience, downside, or is double-edged
Has Qualitative effect, not just a Quantitative stat bonus/penalty - changes the world, not numbers
and...
(If you want to share it with others) Doesn’t Worldbuild, making it difficult to justify within an arbitrary setting
(If gradual mastery of magic is important) Scales with caster power
(If casters can choose their souls) Is not immediately useful in many cases, making it an obvious choice over other spells
(If resource management is a part of your game) does not just do something that a mundane object could do
(If using character classes) does not obviate the usefulness of a character class
Attention From the Sources of Power
Each magic came to mortals' ken via secret Sources of Power beyond the Gods' own.
Each time a spell from a certain source is used an additional time per (month), there is a chance that the caster will gain the attention of this Source. Roll 1D6. If it is below the number of times it has been used in the past (month?), the caster gains the attention of the source, rolling below:
1: Caster's Soul becomes bound to the Source. The Source makes itself known to the caster and urges them to act for them at inopportune moments. Beneficial divine magic has no effect on them. On death, the most recent Source binding the soul takes full possession of them.
2: The caster is Touched by the Source, gaining a detrimental aspect or mutation, repelling any mild or middling beneficial divine effects, until treated via strong divine magic or sacrifice and renouncement of arcane arts
3: An additional, dangerous, unintended effect related to the Source manifests.
4: The effect is twisted beyond the caster's intention.
5: ?The spell gains a tier of power, possibly beyond intention.
6: The caster gains a glimpse of a new form of the Source's magic. Every round that passes, they forget it on a 1-in-6. Otherwise, if they begin scribing it into their spellbook, completing on a 1-in-6 per Turn of undisturbed scribing thereafter. On completion, they have learned a new spell.
I've got just the spell! ...where was that book...
Magicworkers can cast non-memorized spells from their repertoire, but there is only a 1-in-6 chance of doing so each round (in combat) or Turn (outside of combat, where they don't feel as rushed) while they rifle through their collection and flip through pages.
This also causes the loss of a spell slot (or a memorized spell of the player's choice).
This ability is not improved with level, as the size of their corpus grows along with any increased skill in searching.
Ultimate Spellcasting
Competent magicworkers can choose to pour everything they have into an ultimate casting of a certain spell
its effect is magnified, or permanent, or tweaked to have a certain desired effect
it takes a bit more time to cast, but not hugely
it has no chance to fail
but the caster permanently loses the ability to cast that spell again, even in its original form
and, it might permanently sap some energy from the caster, in the form of HD and/or a reduced stat
This may be performed only once every 5th(?) level.
