Look into effort to switch all primary key IDs to be UUIDs

[chouinar]

Summary

We generally prefer UUIDs for our primary keys as they are easier to work with (can generate them yourselves or let the DB without ever hitting a conflict). However the primary key on a lot of the data we're working with was integers when we imported it.

We want to look into what it would take to switch to fully using UUIDs for the primary keys.

A few notes:

• We'd still need to keep the integers as a sort of secondary unique key that we could look records up by for the purposes of connecting to the old system
• In some places like the GET /opportunity endpoint we'd need to allow either int or UUID

Questions to research:

• Is it possible to swap out the primary key of a table on the fly?
  • How would it work for foreign keys?
• Assuming some amount of downtime would be necessary, how long would it take to correct the data?
• Would it potentially be simpler to just make a new set of tables and later "swap" them in?

Idea:

• Create a table like opportunity_tmp from the existing opportunity table. Add a new UUID column.
• Repeat for all tables.
• Delete + rename every table all at once.

Acceptance criteria

• Scope out a plan

[chouinar]

There are 3 sets of IDs we would want to switch from integer to UUID:

• Agency tables
• Opportunity summary
• Opportunity

Agency table IDs are pretty self-contained and were something we created entirely. Worst case scenario for switching those would be deleting all of the agency data, changing the column types, and re-importing.

Opportunity summaries are also a new ID that didn't exist before, but there are a few tables that depend on them.

Opportunity ID is the most complex one because many, many tables connect to the opportunity via this AND we still need to keep the ID in some form so anyone who uses opportunity data in their system can connect to the opportunities they may already have. In any scenario, we should account for fixing this one last.

---

Actually doing the switch is tricky as we need to account for the primary key AND any foreign keys that point to the record.

I tried testing the following idea (using the agency tables):

1. Add future_whatever_id as a uuid to the table we want to replace the ID for, defaulting the value to a UUID (server_default is also needed)
2. Try to change the foreign key in a table that references this record to a UUID -> errors because you can't change int to uuid

To do this, approach we'd likely need several migrations, and the final switchover to the new UUID column as the primary key is probably more complicated than it is worth.

I think the "delete and remake" approach might just be simpler. All of our data is imported in the transformation process, so we just need to re-import things to make that work.