11–18 of 18 posts
INSERT only, no UPDATEs. Queries return most recent record. Periodically groom older entries.
Wouldn’t grooming older entries eliminate the trail?
INSERT only, no UPDATEs. Queries return most recent record. Periodically groom older entries.
You need to have a trigger that keeps an "up-to-date" view of only the latest version -- or alternatively, have a trigger that keeps the "insert only" table based on one you treat as usual (delete/insert/update)
INSERT only, no UPDATEs. Queries return most recent record. Periodically groom older entries.
This works well for the audit part - have done this myself more than once - but makes it hard to impossible to get good query performance for some kinds of queries. You need to have a trigger that keeps an "up-to-date" view of only the latest version -- or alternatively, have a trigger that keeps the "insert only" table based on one you treat as usual (delete/insert/update)
We stored medical records. Datafeeds were very chatty. Updates would arrive out of order. Resolving "single best record" (aka "source of truth") was trivial via queries. The norm seems to be to adjudicate as data comes in. So my solution really upset a lot of people.
To your point, I did have to change the schema for scripts and allergies. To get the indices arranged just right so that GROUP BY queries were performant.
Further: UPDATE only strategy enables temporal testing; just specify time range. Saves a lot of time, effort. Versus norm of reloading the data from scratch every test run.
Earlier quoted context omitted.
Wouldn’t grooming older entries eliminate the trail?
You generally dump them into a data warehouse after a certain amount of time so they are not in the live database anymore. You still have the records and can still reference them for reports and compliance reasons.
Earlier quoted context omitted.
Wouldn’t grooming older entries eliminate the trail?
You generally dump them into a data warehouse after a certain amount of time so they are not in the live database anymore. You still have the records and can still reference them for reports and compliance reasons.
The App Trail comment provides a good example of the necessary user context you may want. Additionally, you may also need to require verification that activity was logged, for example for a transaction to complete you want redundancies to ensure the logging occurred correctly.
Essentially it boils down to recreating user authentication and authorisation type functionality, but for all of your middleware pieces and components. You can capture the logs into a single database, do some hashing, and maybe include signatures from devices and users and you should have pretty coverage.
Earlier quoted context omitted.
How would CDC include information like the user making the change?
A CDC alone wouldn't. You would need to track and store that metadata in an additional database along with transaction ID, so it can be joined with the raw change stream to form a complete audit log.