Roll out plan for referral details

[tomsmyers]

Context

We are working on a set of changes which allows referral details (maximum enforceable days, completion deadline, further information) to be updated in sent referrals. The updates must be fully traceable, i.e. who made the update, why the update was made, when the update was made, and what was changed.

The solution we have agreed upon to facilitate this behaviour is a seperate table (referral_details) with the following layout:

Any changes to the referral details (for sent referrals) add a new row to this table. The history can be traced by stepping back via superseded_by_ids. The latest can be efficiently lookup by select * from referral_details where referral_id = :referral_id and superseded_by_id is null.

The roll out

We have to be careful when rolling out this new data structure to ensure:

• no locking behaviour in the tables during update which may cause performance issues to the production service
• all existing deployed code continues to work at all times (no references to tables which are no longer in use etc)
• no data loss for existing referrals (both sent _and _draft).

The following approach will be used (where each number indicates a seperate deployment):

1. Start populating the new table for newly created referrals - this is basically running the new table in parallel, with the data duplicated, but the referral table holding the data used to populate API responses (so not change there).
2. Backfill the new table with existing referrals that have no already populated the new table. Once this is done the table is fully "live", i.e. it contains the up to date referral details for all referrals.
3. Now we can switch the data used to populate the responses over to the new table, and stop popuating the existing fields in the referral table. We can split this step into 2 if we want to.
4. Deprecate the old fields in the referral table.

[kth13]

@tomsmyers ,While doing the backfilling, I believe we will do a force update to mitigate the duplicate rows already created in both the tables?

[sldblog]

Perhaps "Backfill the new table with missing older referrals"? We'll only have to backfill the ones that weren't added since by the new code.

[tomsmyers]

great to call this out explicitly. I agree with david - we will have conditions in the update SQL to only created records for referrals that not already have them

[kth13]

That's good. Thanks David and Tom

[sldblog]

3. Now we can switch the data used to populate the responses over to the new table, and deprecate (as well as stop popuating) the existing fields

At this point there should be

• Old code pods reading "old" table for API and writing "old" and "new" tables
• New code pods reading "new" table for API and and writing only "new" table

New pods would execute the deprecating migrations while "old code" is still running

So we won't be able to do the rename/deprecation of fields in a migration as long as "old" tables are read in any way -- perhaps as a 4th step? (Which can go immediately after)

[tomsmyers]

have updated the plan to reflect this. thanks.

[tomsmyers]

step 1:

• Write referral details to the new update-able table alongside the existing referral table #891

step 2:

• backfill referral_details table with existing referral details (which don't already have data in the new table) #901

@kth13 @sldblog this sql handles the potential duplication using left join x ... where x is null

[sldblog]

step 3:

• use the new referral details table to populate referral DTOs #910

[sldblog]

This is now live.