Also, what other factors need to be considered when implementing audit trail?
In short, IIRC, it looks into the RDBMSs redo log, and turns it into a message stream. You can then stream this into some kind of messaging platform (Kakfa is typical) and then process the data any way you want. Log it, aggregate it, archive it, stick it in another database, or object store, etc...
Because audit trails record activity, I would generally recommend linking the audit trail to the API call site rather than the database, as you should include information such as the actor (who made the call/request) and context (e.g. what IP address and user agent the call came from). An additional good practice is to add the request and response of the API call being recorded. You should also redact any sensitive or PII fields.
If you're looking for a managed service that takes care of all of this, and delivering it to your customers for you, check out https://apptrail.com (Disclaimer: founder).
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.
If you want to get just a bit more fancy, add a hash that depends on previous entries, like some sort of proto-blockchain.
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.