Really, you're pretty limited in the kinds of things you can do. As you've found out, you (still) cannot add/modify building types (although you can mod the
cost of labs or temples for a nation and pick which forts a particular nation gets.)
RE: adding a "Roads" site - there is no current way to mod a spell to add a magic site. Furthermore, there is a hard limit of 4 sites / province, so adding sites for infrastructure would be problematic.
RE: modding a spell to require gold to cast - not as such. However, expending gems has a "cost" associated. Also, if the spell summons a unit, it can have an upkeep cost (so while you don't spend gold up front, it can require gold in maintenance over time.)
As far as the province level effects that
units can provide, you can:
- Add/consume extra supplies (either directly, or via nature paths)
- Modify scales (only via copystatting, so you're limited to luck/misfortune and magic, AFAIK)
- Generate units (domsummon or summon commands)
- Provide patrol bonus (unit would need to be set to patrol manually; for example, see the Watcher summon)
- Generate gems
- Nullify bad events (fortuneteller ability)
- Promote good events (requires copystatting, so you're limited to either very small or very large values)
- Spread dominion like a prophet (copystat the Juggernaut)
- Reduce dominion (copystat something with heretic tag)
- Spread disease/kill population (reaper tag)
- Cause/reduce unrest
- Reduce (devour) population
- Cause insanity to other units (copystat void spectre}
There may be more.