MQTT and Transaction Groups

I’d like to only use transaction groups to store real-time and historical data in wide tables. The data is written to PostgreSQL With TimescaleDB and served via GraphQL API to various web services. We are currently sending MQTT data from numerous Ignition edge devices and also need to insert any data that comes from the “store and forward” on the edge system. I understand that this works when using the data historian, however, the transaction group is just the current value from the edge node provider. Does anyone have any ideas on how to accomplish this?

Individual items from MQTT have their own timestamps. Converting to wide tables would need a way to collect the items with “close” timestamps to generate a wide row. How do you do that, and deal with missing items? And deal with double entries within a given wide row’s “window”? There’s no generic solution. Largely due to the the fact one’s answers to my questions would vary from site to site and even from table to table within a site.

I don’t doubt that it can be done (I’m a fan of wide tables), but it will be utterly custom.