Evaluate Liquibase migration files structure

[GilmoreA6]

Spike Goal

Determine whether or not we need to refactor how the liquibase changelog .yml files are structured due to current difficulty with changes to FK constrained items.

Timebox

Time allocated (2 FTE days)

Notes

• As part of Rename sentSubmissionId and receivedSubmissionId to inboundMessageId and outboundMessageId #1404 it was determined that changing the variable names "sentSubmissionId" and "receivedSubmissionId" to "inboundMessageId" and "outboundMessageId" required a corresponding change to the metadata table column names referencing those items. Relevant column names are "received_message_id" and "sent_message_id" created in changeSet 2 of metadata.yml

• The message_link table has a column named" message_id" which references column "received_message_Id" of the metadata table. This column would also have to be changed if the metadata column names are changed.

• In order to change the column name in the message link table we would need to first drop the message_link constraint on the metadata table, then change the column names on the metadata table, then change the referenced column name in the message_link table an re-add the constraint.

-The above is difficult to accomplish with our current changelog structure as we would have to change the convention we are using in order to apply the migrations in the correct order (ex: we would need to add a changeset that changed the message_link table in the metadata.yml file). This would then make applying the same migrations to a new clean database not work correctly.

-Options:
1.) remove the message link table entirely as proposed in #1422
2.) Completely refactor the migrations to follow the Liquibase release-oriented structure https://docs.liquibase.com/start/design-liquibase-project.html

[halprin]

I want to first question the assumption that we need to rename the message_id column in the message_link table. Are you sure we need to rename this? It wasn't named received_message_id or anything like that previously, so does it really need to be renamed to inbound_message_id? If we don't need to rename the column in the message_link table, then this problem goes away I think.

I know that @jcrichlake had some thoughts on how to organize our migrations back in the day, so I am very curious to hear his thoughts.

If we need to rename, I'm in favor of refactoring the migrations because I rather keep the message_link table instead of folding it into the existing metadata table.

[jcrichlake]

I think reorganizing our migration steps is fine. I think we wanted to keep things organized by entity so that we can find any bad updates more easily.

I would not recommend that we remove the message link table as that can impact the normalization of the data