We want to track around 20-30 fields changes of case object. These fields changes can include the long text area field, multiselect picklist values, formula fields, and roll summary also. The reason I mentioned this is because of limitations around field history tracking. So we need some custom solution..
Now what we thought is to :
Create a custom object first named "Field Update Object".
Write code in case trigger handler saying whenever these specific field values changes, create a "Field Update Object" record. The code will include one text field which will hold combination of : transaction ID + CaseId. Reason of doing this is because unlike case history, I dont want to capture all the changes happening to one field. Ex: If subject of case is changed via user, then from trigger, then changed from flows..What field history will show is 3 records.
What i want to have is 1 "Field Update Object" record for 1 field change per case record. Which means if Subject is changed by user > then trigger > then flow, I would like to capture the Latest value (end value showing to user after transaction is committed) showing to the user rather than all the values keep getting change from trigger and flows. I will use case insert event to insert a "Field Update Object" record and then case update event if the transaction id + case id combo matches with the existing record..Reason -- It may happen that a trigger can run multiple times synchronously.
Then our batch will run after every 5 min to see all the records created in last 5 minutes in "Field Update Object" and create json to send to third party via Rest API.
Also, I think if we are executing a Async job from case trigger which is updating case, this will be a separate transaction, there is no way we can know the if the parent transaction which was a case trigger sync transaction has already created a record or not right?
Another approach could be: CDC and suscribing through trigger which will also dont have event delivery limit applied. But here formula field will not get capture. Similarly for async job also, 2 events of cdc will be fire if we are updating case again.
Really need help on this to have a better design. In Apex we dont have any method to know final values committed to database after multiple same trigger run.