MQTT counting number of messages

A beginner here.

I've read through forums and the web but only able to understand persistence of MQTT at broker level, but what about persistence at device level?
How does MQTT deal with that? Do I need to build it myself?

Context - I need to count the number and frequency of people passing through a door. Every time someone passes through the device, it sends a message to a server and stores in a database.
But what if there is no connection between the server and device?
How can I store all the messages at device level till it is sent to the server.
The problem here is not just sending the updated payload but also the time and counting the number of messages.

I am assuming (correct me if I am wrong) that you are running node-red on one machine (machine A for example) and the MQTT broker and database are on machine B, and you are worried about what happens if A cannot access B for some reason.
I have solved such problems by not writing directly to the broker but writing initially to a local data store. I used an sqlite database but with persistent context now available in node red that that might be a simpler solution. One could have an array in persistent context and every time a new record becomes available then push the new item to the array. Then have a separate flow that watches the persistent array and if there is anything in it, and there is good access to machine B, then it sends the data across to machine B, and once it knows it has gone then it removes that record from the array. Thus in normal operation and record will get added to the array, then immediately gets picked up, sent to B and removed from the array. If there is no connection then records build up in the array and then when the connection is restored the records are sent across.

I was thinking along the lines of Colin, but he was faster in typing the answer.

If you need the exact count of persons in the central database, then you need to store everything on the counter side and send it when the connection is up. The central database needs to confirm the successful receipt. You most probably do not need to acknowledge the confirmation. MQTT does not help here.
There are many concepts out there in the IT world on how to do this.

Urs.

1 Like

@Colin @Urs-Eppenberger Thanks for your responses.
Actually I'm trying to count and get data from a few esp-8266s to a central server which runs nodered and stores the data in a database.
What I get from your responses is that MQTT natively will not help in syncing data for the above requirement and that "persistence" will have to be programmed into the code.

You need to be timestamping your measurements at the device end if you ever plan to go into a store and forward mode.

A case you will have to think about is what if one of the esp8266's looses contact with the server (for what ever reason). The code on it will have to keep count of the door openings until it re establishes connection.

Can you just confirm what circumstance you are worried about? Is it losing connection so that the esp devices cannot send data to the central server? If so then what happens at the esp device when it loses the connection will be down to the software running there. Logically there is no way you can do anything about it at the node-red end, whatever information you need to send to the server when the connection recovers must be stored in the esp device until it can send it.

@role2mail, I have had a some experience doing embedded systems in the field that do much the same as what you're describing. You can use SPIFFS on the esp8266's to create a file system in flash memory. You can then use a library like ArduinoJSON to store your timestamped events in an onboard JSON object as they occur. When you have a re-connection to your mqtt broker, you can then publish the entire current json object, have node-red pick it up from the mqtt broker, as I'm assuming it's subscribed to the appropriate topic, and then parse the json object any way you like in node-red and populate your database.

Cheers
Brian