IOT data interpretation

Question

This is a more architectural question and I am hoping to get more ideas out of this thread. I have a computer vision model detecting daily life activities and sending data to the server every second. I want to show that data on the frontend but only after interpreting it in a comprehensible way.

So the process currently is:

1. Activities get detected
2. Some data is pushed to my API
3. Data is stored in the database

To interpret the data in comprehensible way we have to go back and look at previous rows to confirm what this data is. For example if the sleep was detected and is consistently detected for the past 1 hours then it was sleep otherwise it was not really sleep. To reach this result so far we have come up with a few architectural plan:

Option 1: Triggers - Upon inserting the data in the database we write a trigger that goes back and determine what kind of activity is this.

• Pros: We figure out the activity immediately and can make that data available to the customers really soon.
• Cons: Triggers usually slow things down. I am worried this will send the database performance or might result in deadlock somewhere. Given that we will be inserting data multiple times a second from different directions this can become a liability soon.

Option 2: Stored Procs - After database insert has happened a stored proc that contains all the logic separately goes through the data and classifies the activity.

• Pros: Processing happens immediately.
• Cons: Again any such functions on database can create overhead given the volume of data we will be dealing with.

Option 3: Add interpretation logic on the server - After data is push to the API before inserting we get all the previous rows and analyse that data on the server and only then insert it. So far this is looking the most viable option to me. I am using nodeJS so it seems easy enough to do that.

• Pros: No DB overhead, immediate, server can handle more complex logic
• Cons: It can possibly make the server slow if we do this on every request

Option 4: Have another service running that looks at the data every minute and classifies it and puts it in a separate table.

• Pros: No DB overhead, hopefully won't make the server slow.
• Cons: Not immediate

I am here to find out if there are more obvious ways to handle this sort of thing that I don't yet know about. If there are any obvious AWS services that I can use for this that would also be appreciated

Answer 1

I would ditch relational databases for this kind of work.

Take a look at Timeseries DBs, which are better suited to do timeseries (i.e. "event"-based) computations and aggregations. Even Elasticsearch could fit, as it has some timeseries-related functions.

Also, take a look at stream processing solutions, which is roughly what you described. Such as Apache's Spark, Storm, Flink, and whatever they got these days.

Also Kafka is a mix of both of the above, while having some pretty cool and unique features. It is simultaneously a sort-of timeseries DB, while having a really strong and simple stream processing library.