In addition to the IoT events themselves, I want to log the mutations in the metadata of each device.
Why?
My question is, what are the best practices regarding tracking this metadata
Do the simplest thing allowed while ensuring you retain all of the data and an associated timestamp or version.
I've done a little research into history tables and (bi-)temporal tables,
This seems like overkill.
and I'm little lost based on all of the options available. In short, I want some kind of history table of mutations in the devices table so that I can see when the metadata is inserted/updated/deleted.
So create one, then populates its contents using insert/update/delete triggers on the observed table.
I'm also hosting this server on a managed Azure instance, which is somewhat limited in the number and kinds of extensions available.