Using webdev endpoints the database store procedures

Ignition
1

Hello. I am looking for some suggestions with the following architecture.

The end goal is to have many edge gateway connected to the equipment. These edge gateways will read all the tag values and prep it all together to send the data to a SQL server. The SQL server has a bunch of store procedures/each with input params ready to be called.

On the edge gateways, one tag triggers and the values held in many of the tags are used as the input params to call a particular store procedure. The architecture is not limited to just edge gateways calling the store procedures. It could be other third party equipment that have a computer and need to insert or get data from the same DB.

Since all the edge gateways/or other equipment need to have a door to the SQL server to call the store procs, we are setting many Gateways behind a load balancer , all with the webdev module exposing an endpoint that expects a payload with the store proc to call and the necessary input params. Once the payload is received , a script preps the store proc call and sends it out to the DB and returns the response back to the webdev session.

I think this is a very standard use for the webdev module, however I am looking to cover all the possible things to go wrong.

Concerns for Post:

1-If DB not available, what's the best way to buffer the incoming calls? Buffer the data to an sqlite to hold it temporarily until the connection to the DB is restored and then to call the DB?

2-Each webdev session must return a response to the client. If the response has to wait on the DB to provide a successful/failed transaction, might the thread pool get exhausted from other incoming calls that remain open cause the db has not responded yet?

3-Is it better to immediately respond to the client(close the session with immediate response) , load the payload data to the buffer sqlite and later prep the buffered data to call the stored proc?

Concerns for Get.

1- Is enforcing a short time out to the store proc call enough to prevent having too many sessions and running out of threads?

What are your suggestions to make sure the ignition gateways are not the bottleneck in the flow of data?

2

Bruh, you don't have to roll your own here. You just need edge sync services:

3

This, honestly, sounds like an implementation that Ignition is not the best fit for.

Providing a REST API that buffers incoming responses into a local cache and then submits them to a DB is something you/LLMs can easily glue together from off the shelf components. This doesn't fit Ignition's strengths well.

4

Thanks for the feedback. This does not apply to my question. I am not referring to the historians.

5

Thanks for your response. I agree with you in the sense that there are better tools for this particular request. If we ignore this for a second. Do you think that the event streams module can help me do what I need without reinventing the wheel? As far as I know , after an http source request , it closes the session, plus it already has some buffering included so I might be able to make something work with that. Once the DB responds, I can use the handler to write back the result to a tag from one of the edge gateways assuming I receive that tag path in the payload from the http source. Do you any other ideas?