In my opinion the best puzzle and best one off I came up was based on magic system from classic cRPG Dungeon Master II: The Legend of Skullkeep (probably it was the same in DM I). In that game each spell is cast by selecting runes: one for Source (e.g. Fire), one for Form (e.g. Ball), one for Power (Weak to Strong). Or something similar.
This was in a home brew simplified DnD.
So the story was:
NPC told them to meet in a cave that leads to an old temple where an evil ritual is taking place.
Game started in the cave where it seems the NPC is killed or kidnaped. They found his journal that says to stop the ritual you have to light candles placed on runes according to a prayer. But the page containing the runes is missing, so is most of the candles.
While traversing the dungeon the players could find clues to figure out the runes.
The clues were:
Several burning candles with symbols on them, when snuffed a Mephit would pop out and attack them.
The symbols where:
[Fire][Water][Monster] for Steam Mephit
[Water][Earth][Monster] for Mud Mephit
To make it simpler I did announce what type of Mephit it was. Of course let the players note the symbols on piece of paper.
An altar that drains blood which had the [Blood][Pull] runes.
We had a mage and cleric in the party, so for some of the spells they knew, I wrote them the matching combination of runes. Firebolt would be something like [Fire][Send].
I made it sure that if they found all the clues, then they would be able to correctly match words from the prayers to runes.
They had two chances to found a scroll with the runes decrypted, but one was after a locked door and a big fight (they failed to open the lock) the other in a room with magical darkness guarded by shadows (they decided not to go there).
So the adventure concludes with a battle with cultists, after which I gave them a limited time to solve the puzzle and stop the ritual, which now could go on without the cultists.
On the altar they could see the runes inscribed, all they had to do was to place Candles on the correct ones. Don’t remember the prayer now, but it was something along the lines “[Water] is [Life] and the source….”. There was a total of 5 runes hidden in the prayer, so as an extra DM d**l move I gave them 6 candles. They’ve spent more time thinking about the additional candle than on the other five. Be ready to come up with a funny/deadly effect if they prepare wrong runes.
On a side note, the same as you I spent too much time thinking if puzzle/traps make sense in such a place. What I’ve noticed is my players don’t care, they even have more fun when there is a lot of funny/deadly things happening.