I'm all for anything that gives religion more of a punch past "This God Cursed This Guy (Who profaned his temple) with some Werechinchilla disease" or something like that.
Agreed. I like the idea of gods playing a more active role besides just handing out curses during worldgen. Holy wars sound interesting, but that's already sort-of handled by the ethics system.
Maybe make it so that if a deity is pleased with your fortress (nice temple, lots of worshipers, regular sacrifices?) they will bless your fortress in some way. Maybe give all of their worshipers a happy thought, give a useful mood to one of your dwarves, or a gift your fortress with an artifact weapon. A pissed-off deity could curse your fortress - an unhappy thought for all their worshipers, causing an undead outbreak, driving a random dwarf insane, or cursing a random dwarf (players will probably weaponize this, but that's part of the fun).
Make some artifacts 'divine' in nature. When a dwarf enters a fey, fell, or possessed mood there is a chance that the inspiration is from a deity instead of a random spirit or their own drunken brain. The artifact produced will be appropriate to the deity in some way - incorporating an image of the deity, made out of a material the deity favors, recording an event the deity was responsible for (like cursing someone), etc. Deities get angry if anything bad happens to a divine artifact.
It would also be neat if deities could make demands, perhaps by possessing one of their worshipers. "Deity McBig, god of magma, speaks through Urist McWorshiper, demanding that a great temple be built in his name!" They could demand a temple, demand a better-quality temple, demand an appropriate sacrifice, etc. Meet the demand and the deity becomes happier and more likely to bless your fortress. Fail and the deity curses your fortress. This could be especially fun if a god of death takes an interest in your fortress and starts demanding the sacrifice of sentient beings! I like to imagine players deliberately provoking a war with another civ to ensure a steady supply of sacrifices - or maybe just using an altar as an 'immigration services' department. Or maybe even deliberately ignoring demands for the sake of performing science on the inevitable curse victims.