CREATE PUBLICATION news_item FOR TABLE news WHERE (topic IS "AAPL");
In the app (Django or whatever) after inserting news item, call a service (async) that matches users, sends alerts
I’d create a message queue table that would be inserted with jobs according to changes in other tables. Makes it easier to handle the message queue asynchronously.
Doing it in application side in a reliable way needs some kind of a distributed transaction mechanism which might not be feasible. I don’t want to fire an insert and then fail to send an alert and find myself within an if statement thinking “what the fuck do I do now?”
You could insert the job on the application code too. I just like triggers and abuse them which might not be a good idea in all cases.
Depends on the requirements though.
The business logic is clear to read, easy to change.
Ive enjoyed writing complex SQL, but when it comes to app development I want my engineers to just write python. Triggers are mostly invisible in the codebase and I would end up having to do all the maintenance myself.
There is of course more than one way to skin a cat, but I want our codebase to have just one single way.
On top of that, triggers are hard to test.