Tasha's Cauldron of Everything fixed the problem with magic stone, sort of.
You have made all the relevant observations about magic stone. It is good until it falls behind other cantrips at 5th level. So if you are planning long term, even though it is good for a little while, it becomes a waste of space later, so isn't even worth taking when it is usable. Tasha's Cauldron of Everything introduced some optional class features to the Druid and Warlock that make it less of a trap choice in the early game.
At 4th level, Warlocks get the Eldritch Versatiliy feature:
Whenever you reach a level in this class that grants the Ability Score Improvement feature, you can do one of the following, representing a change of focus in your occult studies:
- Replace one cantrip you learned from this class’s Pact Magic feature with another cantrip from the warlock spell list.
And druids get Cantrip Versatility:
Whenever you reach a level in this class that grants the Ability Score Improvement feature, you can replace one cantrip you learned from this class’s Spellcasting feature with another cantrip from the druid spell list.
Artificers have this feature built into the class already, so no optional features required. Your intuition is correct - it can see some use in the early game, but it gets bad later. If the DM is cool with the optional rule from Tasha's, just use it while it's useful, then swap it out later.
Niche uses: not worth the cantrip slot
There are still some situations where you might find yourself saying "magic stone would be useful right about now", but they are going to be pretty few and far between.
- Your martial comrades have been relieved of their weapons.
In this case, if for some reason one of your non-caster party members needs a weapon of some sort, magic stone is better than punching in melee and better than nothing at range. So you might find yourself thinking "magic stone would be nice right now", but you will quickly be reminded how bad it is when you use the rest of your cantrips far more often.
- You want to look like you're throwing pebbles while actually hurting someone.
Remember when you were a kid, and you had the brilliant idea of putting a rock inside a snow ball? That's magic stone. You can look like you're throwing mostly harmless pebbles while actually packing something of a wallop, relatively speaking. Again, not worth the cantrip slot, unless you're into this kind of mischief.