Keep in mind that even magic energy should be coming from somewhere. If it’s purely from the individual person, over-casting could lead to something similar to low blood sugar as all the energy is consumed from your body; if it comes from your surroundings, over-casting might leave an area stripped of magic or polluted.

Why do you want magic? What do you need it for that can’t be achieved without it?

It’s hard to come up with ideas without any context, ESPECIALLY nitty gritty. So instead I’ll try to start with a few frameworks that could be built upon. Maybe my ideas are bad. Maybe they are interesting. Everyone can judge for themselves. But I’m going for framework and inspiration here of how to start. I hope someone finds it interesting enough to take a thread and run with it.

You could have a magic system that’s based around breaking current physics which might feel grounded (like you know internally that a magic alters quantum wave functions and influences physical outcomes as a result for instance, or just stick with the good ol’ air earth water fire that people are already familiar with and will understand immediately). That would put limits on what your magic could or couldn’t do and make it feel plausible or at least consistent. It would also be easier to set a power level to since it’s already by nature restricted.

You could also choose more narrative inspired system which might be less cohesive but allow for bigger moments with your characters. Just imagine a cinematic moment. Your protagonist is on their last legs. The BBEG has hordes of minions overrunning the towns and cities. One of the protagonists friends embraces them and as tears fall the BBEG prepares to annihilate them. Call back to a scene where you foreshadow this moment and as the protag remembers that scene they find within themselves the strength repel the BBEG. Or maybe they can teleport away with their friend, or maybe they sacrifice themselves to destroy the BBEG in a metaphysical explosion. The specifics of that would be yours to craft but the moment you design would inform the ‘magic’ required. To make a system like this I would imagine a few different impactful moments that would be super fun to have in the story and their magical effects. Make these just about the top end of what magic can do in your world and then start scaling down to a more reasonable level. And now start thinking about how people would use the scaled down stuff in their day to day lives.

It might be interesting to allow for the balancing and OP-ness of the system to be restricted by sacrifice to the characters. Maybe there’s no real limit to magic aside from how much of yourself you’re willing to give up. Perhaps giving your entire body and soul could cause a magical effect of a certain power level. Maybe if two people cast it together, sacrificing both of themselves it would be more. The good side could quickly go down a dark path trying to out muscle the BBEG.

You could also go the route of taking inspiration from RPG systems like Shadowrun, Dungeons and Dragons, Call of Cthulhu. I’m definitely NOT saying copy those systems, but you could take from DND the concept of doing a ritual each day to be able to have access to certain stuff. Or maybe you like the idea of casting spells taking a physical toll on the caster as it does in Shadowrun. Or maybe accessing the arcane drives one made like in CoC.

So sorry I don’t think I can get into nitty gritty details but hopefully something in this sparks some inspiration. And if not, at least it can rule some stuff out!

How many magic users do you expect there to be in a given area? 1 per nation means you could make them pretty strong and influential but their abilities are limited by their physical presence, so most people are relying on traditional methods to do things and the fact that the wizard can make water or food is pretty meaningless. You still have to farm.

You can also limit magic by making it cost something. Some fantasy makes it physically exhausting, costing rest and food. That can be fun because dramatic, high emotion situations inherently allow for more dramatic uses of magic. It can also cost physical components, stronger magics utilize more or rarer components. That limits how a society might use magic to replace mundane tasks. Medieval peasant labor is going to be cheaper than whatever costly magic can offer.

I’m not story writer man by any means but I always find it interesting when characters have the super powerful abilities but it’s limited in either scale or control or has some drawback

Code geass for example, the power to compel someone to do any command, drawback is he needs direct eye contact and can only use it once on a person

Or the ability to read minds but unable to turn it off making crowded places completely unbearable

Or the ability to make victims around you seem like time has stopped but it has a small area of effect, only changes the perception of time, and his heart stops when ever the ability is active

Hopefully this is some use to you

@Butterbee makes a good point that it’s hard to effectively brainstorm some nitty gritty without knowing the function being designed for, and @TheOneCurly makes a good point about magical rarity vs density in the population, since knowing that provides avenues to mitigate the occurrence of “blow up a city” displays; perhaps ability is common but strength or capability is rarer, or the casting of magic is detectable, discernable, and/or able to be countered.

making matter is way too energy intensive (except for light, otherwise it wouldn’t be visible at all.)

I immediately have a few thoughts and questions:

• Does the casting of the magic have verbal, material, or motion requirements? Or is the gathering of magical energies for a given effect visible to everyone, rather than only the gifted/trained? Many times the visual component as described in writing or portrayed in the case of visual media is a device meant to help the reader imagine the world or to let the viewer know that something is going on. What works best for your setting?

• How much of a distinction between “creating” and “converting” are you looking to explain? For example, one could “create” a rock from the air to laypeople but has really gathered surrounding particulate from the area and condensed it. There can be a distinction between what it’s rumored to be capable of and how it actually works.

• What are the costs of magic, and how does that implication impact society in your setting? With physical materials, high impact magic can have high costs for the material required - manipulating time requires wood from a stand of the oldest trees in the world, or a fireball of a given size requires a comparable diamond to convert the amount of heat and pressure it’s undergone.

• If it’s effort based, what is the toll on the body of the caster? Can they increase the effect of their magic by exertion and exhaust themselves into fugues or comas? Draw so much through themselves that they lose the ability altogether, or die? Use it for so long that their body itself begins to unravel from the strain?

I think, based on what story you’re aiming to tell, that you can set a limitation on what can be achieved through any number of factors. There can be an unlimited amount of usable magical energy (and potential devastation) and a perfectly normal reason that one can’t actually wield more than is appropriate for the moment of the story.