As part of our "decomposition" efforts we need to remove all foreign keys that are cross-database. We are going to replace them all with ["loose foreign keys"](https://docs.gitlab.com/ee/development/database/loose_foreign_keys.html). See issues in epic for the remaining foreign keys to be removed. The issues were generated by the following script: <details><summary>find_fks.rb</summary> ```rb db_data_sources = ApplicationRecord.connection.data_sources def fks_query(table) <<~SQL SELECT tc.table_schema, tc.constraint_name, tc.table_name, kcu.column_name, ccu.table_schema AS foreign_table_schema, ccu.table_name AS foreign_table_name, ccu.column_name AS foreign_column_name, rc.delete_rule FROM information_schema.table_constraints AS tc JOIN information_schema.key_column_usage AS kcu ON tc.constraint_name = kcu.constraint_name AND tc.table_schema = kcu.table_schema JOIN information_schema.constraint_column_usage AS ccu ON ccu.constraint_name = tc.constraint_name AND ccu.table_schema = tc.table_schema JOIN information_schema.referential_constraints as rc ON tc.constraint_name = rc.constraint_name WHERE tc.constraint_type = 'FOREIGN KEY' AND tc.table_name='#{table}'; SQL end def is_cross_db?(fk_record) Gitlab::Database::GitlabSchema.table_schemas([fk_record['table_name'], fk_record['foreign_table_name']]).many? end fks_by_parent = {} db_data_sources.each do |table| ApplicationRecord.connection.execute(fks_query(table)).each do |row| if is_cross_db?(row) fks_by_parent[row['foreign_table_name']] ||= [] fks_by_parent[row['foreign_table_name']] << row end end end fks_by_parent.each do |parent, foreign_keys| markdown = "## #{parent} is referenced as a foreign key from other #{foreign_keys.count} other tables in a different database" markdown << "\n" markdown << "\n" markdown << "The intention of this issue is to determine any risks when changing all of the below foreign keys referencing #{parent} to become [Loose foreign keys](https://docs.gitlab.com/ee/development/database/loose_foreign_keys.html). Loose foreign keys are cleaned up asynchronously which means when we perform a `DELETE FROM #{parent}` we will queue a cleanup task that will be executed in ~5 mins to cleanup all of the rows that reference this record. This can mean there are sometimes strange UX bugs where they may see a link to something that no longer exists and clicking it may give an error or it may mean there is a background job that runs and errors due to an invalid foreign key or it may mean a 500 when rendering a certain page containing any of the below tables. Some more details about risks and mitigations are being documented in https://gitlab.com/gitlab-org/gitlab/-/merge_requests/76626 ." markdown << "\n" markdown << "\n" markdown << 'The ~"group::sharding" will be responsible for actually doing the work to convert the foreign keys to loose foreign keys but will likely need help from impacted teams to asses the risk and potentially determine or implement appropriate mitigation for any risks. Sometimes mitigation may mean refactoring code slightly to avoid errors or explicitly handling certain riskier cases by checking if there is a pending deletion for a record. It is also possible that we can optimize the 5 minute latency for cleanup in certain cases determined to be urgent.' markdown << "\n" markdown << "\n" foreign_keys.each do |foreign_key| markdown << "1. [ ] `#{foreign_key['table_name']}.#{foreign_key['column_name']}` -> `#{foreign_key['foreign_table_name']}.#{foreign_key['foreign_column_name']}` - `ON DELETE #{foreign_key['delete_rule']}`" markdown << "\n" markdown << " 1. [ ] Best team to review (check off when reviewed): " markdown << "\n" markdown << " 1. [ ] MR: `<LINK>`" markdown << "\n" markdown << " 1. [ ] No way for user to access once parent is deleted. Please explain: `<DETAIL>`" markdown << "\n" markdown << " 1. [ ] Possible to access once parent deleted but low user impact. Please explain: `<DETAIL>`" markdown << "\n" markdown << " 1. [ ] Possible Sidekiq workers that may load directly and possibly lead to exceptions. Please explain: `<DETAIL>`" markdown << "\n" markdown << " 1. [ ] Possible user impact to be evaluated or mitigated. Please explain: `<DETAIL>`" markdown << "\n" end markdown << "\n" markdown << '/epic https://gitlab.com/groups/gitlab-org/-/epics/7249' markdown << "\n" markdown << '/label ~"group::sharding" ~"sharding-blocker::phase4" ~"sharding-blocker" ~"sharding::active" ~"workflow::ready for development"' markdown << "\n" markdown << '/milestone %"14.7"' markdown << "\n" File.open(Rails.root.join("crossfks/#{parent}.md"), 'w') { |f| f.write(markdown) } end ``` </details> ## Request for domain experts **What would happen if we didn't have these foreign keys in place and orphaned records are left behind?** Loose foreign keys are **asynchronous with around 5 minute latency** so we should understand the worst case scenario for orphaned records. All issues in this epic have lists of table names and group labels have been added next to each table. If you see your group name next to a table then you can think about the above question and check off any items that apply. We don't need you to make any MR changes. We just need your feedback and we'll do the rest.