I'm trying to come up with a scalable database design that supports automation/workflow.
In my current solution, I have a trigger model that requires a field, condition value, and action.
Field: Status <- Just a list of supported fields e.g. [Status, Vote]
Condition: 1 <- Can be a primary key or a quantity
Action: "Change value"
Triggers are attached to cards. As soon as a card changes status to 1 the trigger should execute the "Change value" action which changes a value on an arbitrary supported field. The action, in this case, needs information about which field to change and to what value. But let's say we also want to support another action e.g. "Notify users". This action requires information about which users to notify. Different type of data is needed depending on the action.
I'm also concerned about losing the referential integrity with a solution like this. If one would delete status with pk 1 the trigger itself wouldn't be deleted. I would have to manually delete it once the affected status is removed. Is it possible to have a flexible, scalable automation design without losing referential integrity?
Since I'm not a database designer this is a little bit outside of my comfort zone so any help that points me in the right direction would be greatly appreciated.