Architecture for alerts system based on API - would this be too slow a design?

Question

(Hi I’m new here - I believe this question suits here from the guide saying “ architecture, and design” - however if there is a better place, just pop me a comment. As there’s no actual code it doesn’t suit stack overflow )

I am designing a simple site and app where people can create alerts for product stock availability and prices.

So users would create an alert for let’s say a lawnmower at $125.

There would be many products at many prices from a few different vendor APIs.

The hard bit is the backend. I’d have to loop through a unique list of all products submitted, query APIs for their price, put that in another table of product-to-price, then loop through the entire user table again checking that current price against the alert price the user has set.

But the alert needs to be real-time as users bid on products, yet that script could take minutes to run with just a few users. I’m hoping for many users with many notifications.

Any ideas on a better architecture? Open to tech, ideally JS and AWS but am open to change.

Answer 1

Based on the information, here is the system design I would start with:

Asynchronous background job

1. At pre-determined intervals, poll the vendor API's - being respectful of rate limits.
2. Add/update those products with the vendor data, e.g. VendorID, Price, etc., into a database such as Postgres.
3. Add a dedicated secondary read-only follower DB that replicates data from the primary configured with PG bouncer to pool connections. This is because you mentioned that you would like the responses as real-time as possible - one way to "speed up" requests would be to ensure that the service has dedicated access to that database, and other services can't take up resources. This model is a general approach when connecting third-party analytics systems or tools that need to be read from your database.

Synchronous API

1. When a request comes in that can tolerate the possibility of stale data between polling intervals (e.g. alerts) of prices serve the data from the secondary DB
2. When a request comes in that requires the most up-to-date information, issue a request to the backend to refresh that specific product from the vendor API so you can return the most up to date version of the data. In this scenario, you could then display a loading interface back to the user while you make the request.

Considerations

When building a system that polls and stores data from an external source, you need to consider the following very closely:

1. You can't guarantee the availability of vendor API's - how does your application handle this?
2. How tolerant would your systems be to the stale data - from the second the response is returned from the vendor API, that data should be considered stale, and you should treat it as such internally.
3. How can you optimise your schema design and SQL queries - using things like Aggregate Functions to perform as much computation on the database as possible (they are incredibly good at that).
4. You might not need a full-blown DBMS such as Postgress, and maybe something more straightforward like SQLite might be more appropriate as it's incredibly fast for both reads & writes.

I'm happy to debate and discuss this further - let me know your thoughts (the good and bad)!