[mickeyp]

IMO, you should not avoid triggers if it helps prevent invariants in your database. That is what they are especially good at preventing.

You can instruct postgres to raise exceptions using the same error code that constraints use: that way your clients do not need to know the difference.

[jumski]

Good point! For the SQL functions I mentioned, I'm comfortable without triggers - all mutations go through functions (no direct table access), and only start_flow is user-fac ing.

That said, there ARE other places that would benefit from triggers (aggregate counts). I've avoided them because they're hot paths and I was worried about perf impact - relyi ng on pgTAP coverage instead.

Your defense-in-depth argument is solid though. I should revisit this and benchmark whether the safety is worth the perf cost. Something like RegreSQL would come in handy