I was highly opposed to using an external data store to manage meta-data around CA Agile Central records, but as I consider these requirements and questions, I'm beginning to lean towards an external storage option (e.g. Cassandra, MongoDB, etc.)
QUESTION ONE: Are the Historic Snapshots of Work Items Immutable?
We need to map the free-text explanations provided by the team member as to why a work item was blocked over to a smaller set of blocked-reason-categories (Ex: Blocked Explanation: "DEV4 was down again!" ==> Blocked Reason Category: "Environment Issue") in a custom field (or external data base). Aside: There is a second field, "Root Cause for Block", but let's keep it simpler for now and just discuss the "Blocked-Reason-Categories" field.
Many variant, free-text explanations may all map to the same basic category (e.g., environment issue, or alignment issue, etc., etc.).
This mapping exercise would not be done by the team member on the spot. It would be something someone else would do at a later date as they scrubbed over blocked work item history in an iteration, a release, or over any arbitrary time range..
If a work item is blocked once, we can come back and map that one incident with it's explanation to a single blocked-reason-category; however, if the work item was blocked, unblocked, and then blocked again, one or more times, then there will be potentially several incidents in the work item's *history*. Each incident in the work item's past will have it's own free-text blocked explanation that would need to be mapped to whatever category is indicated. Therefore, we need to go back and surgically update the Blocked-Reasons-Category field for historic snapshots, which I suspect are immutable (for good reason).
QUESTION TWO: Are Custom Fields Captured in Work Item State History Records?
If custom fields are not even captured in the state history snapshots of work items, then it's settled, we have to use an external storage solution for this augmentation.