FTA to RST schema import - rework to update rows in existing database

[marcellmueller]

Describe the task
Currently our import from the FTA schema to our RST schema is very basic and only works on a freshly cleared database since we run flyway_clean every time we deploy to dev. We need to look at solutions on how to update this since while we could clear the prod database each time for the MVP public site at least - this would mean a couple minutes downtime each deploy, and more downtime if something went wrong.

Longer term we may need to figure out how to only update rows if data has been updated, though that depends on our strategy for migrating users/rec techs from using FTA to using our admin site.

• For the purpose of the MVP we would likely use some sort of 'on conflict update instead of insert'.
• This import script is located in migrations/fta/sql/V1.0.2__fta_to_rst.sql
• For testing this we can run our updated script against the current database in the RDS query editor

Acceptance Criteria

• Test RST migration is stable and not cleared on every deploy
• Every import statement is updated
• History is tracked in history tables
• Basic documentation on how to track history on creation of a new table is added
• Follow up and verify this is working - check history tables over the coming days for changes (if there are any, need to confirm)

[BAAlexK]

can maybe do automation on migration - need to research and do some testing
consider switching to prisma (not currently using for migrations) from flyaway

[airliah]

@marcellmueller anything that affects what users see for this ticket, that I can review?

[marcellmueller]

Not at all sorry 😅

This won't affect much of anything until we either go to prod or stop clearing the test database every deployment. After we deploy a database that we don't clear everytime (prod) then we can see historical records of any FTA data that was updated since our last import.