What I'm currently doing could be called compute-fetch-store: the compute part is done entirely in the database with SQL views stacked one on top of the other. Then the program just fetches the result of the last view and stores it where it needs to be stored.
Stacked views are sometimes considered an anti-pattern, but I really like them because they're purely functional, have no side-effects whatsoever and cannot break (they either work or they don't, but they can't start breaking in the future). And they're also stateless: they present a holistic view of the data that avoids iterations and changes how you think about it. (Data is never really 'transformed', it's simply 'viewed' from a different perspective.)
Not saying that's the only way, or the best way, or even a good way! But it works for me.
I think it would apply well to the example: you could have a view, or a series of views, that compute balance top-ups based on a series of criteria; then the program would read that view and send email without doing any new calculation.
This.
In-RDBMS computation specified in declarative language with generic, protocol/technology specific adapters handling communication with external systems.
Treating RDBMS as a computing platform (and not merely as dumb data storage) makes systems simple and robust. Model your input as base relations (normalized to 5NF) and output as views.
Incremental computing engines such as https://github.com/feldera/feldera go even further with base relations not being persistent/stored.
Ha! I don't yet know much about 'incremental computing engines' but Feldera seem to be something I need. Because at some point I inevitably have to create materialized views to speed up some parts of the pipeline. Materialized views are of course a side effect and can become mildly dangerous if you're not careful to destroy/recreate them in time.
I was trying to think of a way to "only update new or changed rows" but it's not trivial. But Feldera seems to do exactly that. So thanks!