SPIKE | Investigate a strategy for logging/saving writes to VACOLS and other integrations to maintain data integrity

ghost commented 7 years ago

Connects:

Problem

There is a possibility of data corruption when we deploy something buggy. This data corruption can propagate quickly down the Appeal stream and affect real lives.

We don't know what was in VACOLS before we wrote to it
We don't keep a permanent-enough track of exactly what we wrote
We don't keep track of when we start writing new data. We have this manual entry but that's kinda lame: https://github.com/department-of-veterans-affairs/caseflow/blob/master/docs/data-changelog.md

We need a strategy to monitor what we write that is better than what we currently have (Rails.logger).

Considerations

Time series DB?
Speed? Adding a read for every write is expensive, yes.
@cmgiven any thoughts?

NickHeiner commented 7 years ago

Proposed solution from our discussion in #2621: create a table that provides a complete audit log of all writes to VACOLS. Columns could include:

Timestamp
App doing the write (eg Reader, Certification, Dispatch, Hearings Prep)
Optionally another string label for the app to document why the write is happening, if it's otherwise ambiguous.
Previous value
Next value

We should create a method that all code uses to write to VACOLS. This method will both do the write and create a row in the audit table. No code should write to VACOLS outside of this method.

We may need to be careful about transactions, rollbacks, and failed writes. We don't want to write to the audit table, have the VACOLS write fail, and then have erroneous data in the audit table.

The goal is that with this table, we can have complete data recovery from any corrupt data that Caseflow writes. It should also aid in debugging Caseflow.

NickHeiner commented 7 years ago

Closing in favor of #2681.

department-of-veterans-affairs / caseflow