Foreign Keys To Add

# Foreign Keys To Add ###### tags: `Caseflow Data` Ticket [#14732](https://github.com/department-of-veterans-affairs/caseflow/issues/14732) Why do this? See https://thoughtbot.com/blog/referential-integrity-with-foreign-keys Additional benefits: - [Caseflow Database Schema Documentation · department-of-veterans-affairs/caseflow Wiki (github.com)](https://github.com/department-of-veterans-affairs/caseflow/wiki/Caseflow-Database-Schema-Documentation) can be automatically updated and will be more consistent with Rails' associations. - This will enable a self-service approach for the Reporting Team to inspect the DB themselves. - Other DB analysis tools can leverage the foreign keys. - Enforces referential integrity of rspecs and data in UAT (i.e., more realistic data) How? - Initially using `gem 'immigrant'` - Then using `Class#reflect_on_all_associations` Why so many phases? Validating the foreign key (prescribed in a migration) may take some time, and we don't want it to fail and prevent deployment. * Data backups: JSON files at [s3://dsva-appeals-caseflow-prod/orphaned\_db\_records/](s3://dsva-appeals-caseflow-prod/orphaned_db_records/) directory in S3 * Communicated with DevOps: https://dsva.slack.com/archives/CAM9FJ85P/p1617400012239200 ## :heavy_check_mark: Phase 1: Users (March 22nd) :::spoiler ```ruby= add_foreign_key "cavc_remands", "users", column: "created_by_id" add_foreign_key "cavc_remands", "users", column: "updated_by_id" add_foreign_key "virtual_hearings", "users", column: "created_by_id" add_foreign_key "tasks", "users", column: "assigned_by_id" add_foreign_key "tasks", "users", column: "cancelled_by_id" add_foreign_key "distributions", "users", column: "judge_id" add_foreign_key "hearings", "users", column: "judge_id" add_foreign_key "hearing_days", "users", column: "judge_id" add_foreign_key "sent_hearing_email_events", "users", column: "sent_by_id" add_foreign_key "judge_case_reviews", "users", column: "attorney_id" add_foreign_key "judge_case_reviews", "users", column: "judge_id" ``` Check in prod to make sure the foreign key constraints will be valid: ```ruby= CavcRemand.includes(:created_by).where.not(created_by_id: nil).select{|cr| cr.created_by == nil}.count => 0 CavcRemand.includes(:updated_by).where.not(updated_by_id: nil).select{|cr| cr.updated_by == nil}.count => 0 VirtualHearing.includes(:created_by).where.not(created_by_id: nil).select{|cr| cr.created_by == nil}.count => 0 Task.includes(:assigned_by).where.not(assigned_by_id: nil).select{|cr| cr.assigned_by == nil}.count => 0 Task.includes(:cancelled_by).where.not(cancelled_by_id: nil).select{|cr| cr.cancelled_by == nil}.count => 0 Distribution.includes(:judge).where.not(judge_id: nil).select{|cr| cr.judge == nil}.count => 0 Hearing.includes(:judge).where.not(judge_id: nil).select{|cr| cr.judge == nil}.count => 0 HearingDay.includes(:judge).where.not(judge_id: nil).select{|cr| cr.judge == nil}.count => 0 SentHearingEmailEvent.includes(:sent_by).where.not(sent_by_id: nil).select{|cr| cr.sent_by == nil}.count => 0 JudgeCaseReview.includes(:attorney).where.not(attorney_id: nil).select{|cr| cr.attorney == nil}.count => 0 JudgeCaseReview.includes(:judge).where.not(judge_id: nil).select{|cr| cr.judge == nil}.count => 0 ``` ::: PR [Add missing foreign keys to the users table (Phase 1)](https://github.com/department-of-veterans-affairs/caseflow/pull/16034#) #16034 ## :heavy_check_mark: Phase 2: Organizations and more Users (Apr 13th) :::spoiler ```ruby= add_foreign_key "ihp_drafts", "organizations" add_foreign_key "organizations_users", "organizations" add_foreign_key "vso_configs", "organizations" add_foreign_key "job_notes", "users" add_foreign_key "messages", "users" ``` ```ruby= IhpDraft.includes(:organization).where.not(organization_id: nil).select{|r| r.organization == nil}.count =>0 ou=OrganizationsUser.includes(:organization).where.not(organization_id: nil).select{|r| r.organization == nil}; ou.count =>45 org_ids = ou.map(&:organization_id).uniq => [130, 139, 408, 163, 171, 190, 412, 320] => [130, 139, 408, 163, 171, 183, 190, 212, 412, 320] # Apr 12th org_ids.count => 10 # Check inactive orgs ou2=Organization.unscoped.find(org_ids).count => 10 ou.map(&:user).uniq.count =>45 ou.map(&:user).map{|u| [u.id, u.station_id, u.css_id, u.status, u.last_login_at]} ou.map{|r| [r.id, r.created_at, r.updated_at]}.sort_by{|r| r[2]} OrganizationsUser.includes(:user).where.not(user_id: nil).select{|r| r.user == nil}.count => 0 vc=VsoConfig.includes(:organization).where.not(organization_id: nil).select{|r| r.organization == nil}; vc.count =>3 org_ids=vc.map(&:organization_id).uniq => [237, 242, 259] # All VCs created 05 Mar 2019 orgs2=Organization.unscoped.where(id: org_ids) => [] VsoConfig.count => 82 # https://github.com/department-of-veterans-affairs/caseflow/blob/ded8b6ad58a5f034ecd1f3e6b0b551746f02d975/app/models/organizations/field_vso.rb#L15 # VsoConfig belongs only to FieldVso organizations. # Since the FieldVso records no longer exists, these VsoConfig are useless and can be deleted: vcs=VsoConfig.where(id: vc.pluck(:id)) vcs.destroy_all # Save filename="/tmp/VsoConfig_attribs.json" file_contents = JSON.pretty_generate vc.map{|r| r.attributes} File.open(filename.to_s, "w") { |file| file.puts file_contents } JobNote.includes(:user).where.not(user_id: nil).select{|r| r.user == nil}.count =>0 Message.includes(:user).where.not(user_id: nil).select{|r| r.user == nil}.count =>0 ``` ::: [Add missing foreign keys to Organization and User tables (Phase 2)](https://github.com/department-of-veterans-affairs/caseflow/pull/16114#) #16114 ## :heavy_check_mark: Phase 3: Tasks, DistributedCases :::spoiler ```ruby= add_foreign_key "task_timers", "tasks" add_foreign_key "tasks", "tasks", column: "parent_id" add_foreign_key "hearing_task_associations", "tasks", column: "hearing_task_id" # Refers to the tasks table for AMA appeals, but something like `4107503-2021-05-31` for legacy appeals: add_foreign_key "judge_case_reviews", "tasks" add_foreign_key "distributed_cases", "tasks" add_foreign_key "distributed_cases", "distributions" ``` ```ruby= tt=TaskTimer.includes(:task).where.not(task_id: nil).select{|r| r.task == nil}; tt.count =>7 tt.pluck(:id, :task_id, :processed_at, :error) => [[37, 50355, Fri, 15 Feb 2019 00:30:07 EST -05:00, nil], [38, 50357, Fri, 15 Feb 2019 00:30:08 EST -05:00, nil], [23, 50207, Fri, 15 Feb 2019 10:08:55 EST -05:00, nil], [56, 50567, Fri, 22 Feb 2019 00:30:05 EST -05:00, nil], [62, 50661, Wed, 27 Feb 2019 00:30:04 EST -05:00, nil], [32, 50339, Fri, 15 Feb 2019 00:30:03 EST -05:00, nil], [68, 50724, Thu, 28 Feb 2019 00:30:04 EST -05:00, nil]] # All are processed. Can be deleted. filename="/tmp/TaskTimer_attribs.json" file_contents = JSON.pretty_generate tt.map{|r| r.attributes}; File.open(filename.to_s, "w") { |file| file.puts file_contents } tt_ids = tt.pluck(:id) => [37, 38, 23, 56, 62, 32, 68] TaskTimer.where(id: tt_ids).destroy_all tp_ids=Task.includes(:parent).where.not(parent_id: nil).select{|r| r.parent == nil}.pluck(:id); # Takes a good while tp_ids.count => 0 hta_ids=HearingTaskAssociation.includes(:hearing_task).where.not(hearing_task_id: nil).select{|r| r.hearing_task == nil}.pluck(:id); hta_ids.count => 9 hta_ids.sort => [85, 254, 1082, 1688, 2587, 2947, 3900, 16697, 17900] htas=HearingTaskAssociation.where(id: hta_ids) #-- hta_ids2=HearingTaskAssociation.includes(:hearing).where.not(hearing_id: nil).select{|r| r.hearing == nil}.pluck(:id); hta_ids2.count => 1 hta_ids2 => [1082] #-- filename="/tmp/HearingTaskAssociation_attribs.json" file_contents = JSON.pretty_generate htas.map{|r| r.attributes}; File.open(filename.to_s, "w") { |file| file.puts file_contents } htas.delete_all dc_ids=DistributedCase.includes(:task).where.not(task_id: nil).select{|r| r.task == nil}.pluck(:id); dc_ids.count => 4 dc_ids => [1629, 99374, 109126, 28633] dcs=DistributedCase.where(id: dc_ids) dcs.pluck(:id, :case_id, :task_id) => [[1629, "34b45d8c-fe16-4c6e-b594-e0bc9e34a0d9", 75071], [28633, "967ddcf7-ff07-4ba6-b75b-e00755a37ddd-attempt1", 220040], [99374, "114a1577-1bce-4f64-9fc7-a5cec8f31057", 608057], [109126, "b0b12766-045e-4edd-82c9-8fcdfbcb08ee", 678322]] # Appeals no longer exist for these 3 dcs.map{|dc| Appeal.find_by_uuid(dc.case_id.sub("-attempt1","")).nil?} => [true, false, true, true] dc=DistributedCase.find(28633) => #<DistributedCase:0x00007f9427e4bd18 id: 28633, distribution_id: 1506, case_id: "967ddcf7-ff07-4ba6-b75b-e00755a37ddd-attempt1", docket: "hearing", priority: false, genpop: true, genpop_query: "any", docket_index: nil, ready_at: Thu, 30 May 2019 09:04:29 EDT -04:00, task_id: 220040, created_at: nil, updated_at: Fri, 26 Jun 2020 10:55:10 EDT -04:00> DistributedCase.find(1629).update_attribute('task_id', nil) DistributedCase.find(99374).update_attribute('task_id', nil) DistributedCase.find(109126).update_attribute('task_id', nil) # Since case_id is not a valid reference, task is not useful DistributedCase.find(28633).update_attribute('task_id', nil) dc_ids2=DistributedCase.includes(:distribution).where.not(distribution_id: nil).select{|r| r.distribution == nil}.pluck(:id); dc_ids2.count => 0 ``` ::: [Add missing foreign keys to Task table (Phase 3)](https://github.com/department-of-veterans-affairs/caseflow/pull/16120#) #16120 ## :heavy_check_mark: Phase 4: cavc_remands :::spoiler ```ruby= add_foreign_key "cavc_remands", "appeals", column: "remand_appeal_id" add_foreign_key "cavc_remands", "appeals", column: "source_appeal_id" ``` ```ruby= CavcRemand.includes(:remand_appeal).where.not(remand_appeal_id: nil).select{|r| r.remand_appeal == nil}.count =>0 CavcRemand.includes(:source_appeal).where.not(source_appeal_id: nil).select{|r| r.source_appeal == nil}.count =>0 ``` ::: [Add foreign keys for cavc_remands table (Phase 4)](https://github.com/department-of-veterans-affairs/caseflow/pull/16137#) #16137 ## :heavy_check_mark: Phase 5b: Hearings-related :::spoiler ```ruby= add_foreign_key "hearing_issue_notes", "request_issues" add_foreign_key "hearing_issue_notes", "hearings" # See AppealStreamSnapshot -- add_foreign_key "hearing_appeal_stream_snapshots", "legacy_hearings", column: "hearing_id" add_foreign_key "hearings", "hearing_days" add_foreign_key "transcriptions", "hearings" add_foreign_key "virtual_hearing_establishments", "virtual_hearings" ``` ```ruby= hins=HearingIssueNote.includes(:request_issue).where.not(request_issue_id: nil).select{|r| r.request_issue == nil}; hins.count => 1 # May 20th hinhs=HearingIssueNote.includes(:hearing).where.not(hearing_id: nil).select{|r| r.hearing == nil}; hinhs.count => 1 # May 20th [hins.first.id, hinhs.first.id] => [148, 148] # Save filename="/tmp/HearingIssueNote_attribs.json" file_contents = JSON.pretty_generate hins.map{|r| r.attributes}; File.open(filename.to_s, "w") { |file| file.puts file_contents } # Delete hins.first.delete hhds=Hearing.includes(:hearing_day).where.not(hearing_day_id: nil).select{|r| r.hearing_day == nil}; hhds.count => 0 ths=Transcription.includes(:hearing).where.not(hearing_id: nil).select{|r| r.hearing == nil}; ths.count => 0 vhes=VirtualHearingEstablishment.includes(:virtual_hearing).where.not(virtual_hearing_id: nil).select{|r| r.virtual_hearing == nil}; vhes.count => 0 ``` ::: [Add foreign keys for hearings tables (Phase 5b)](https://github.com/department-of-veterans-affairs/caseflow/pull/16270#) #16270 ## :heavy_check_mark: Phase 5c: Appeals, LegacyAppeals :::spoiler ```ruby= add_foreign_key "dispatch_tasks", "legacy_appeals", column: "appeal_id" add_foreign_key "legacy_hearings", "legacy_appeals", column: "appeal_id" add_foreign_key "hearings", "appeals", column: "appeal_id" ``` ```ruby= dts=Dispatch::Task.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}; dts.count # takes 20 seconds => 5 dts.pluck(:id, :type, :appeal_id, :aasm_state, :completion_status, :completed_at) => [[1139, "EstablishClaim", 21568, "completed", "invalidated", nil], [1741, "EstablishClaim", 23716, "completed", "expired", nil], [14935, "EstablishClaim", 24336, "completed", "invalidated", nil], [44536, "EstablishClaim", 145063, "completed", "invalidated", nil], [83981, "EstablishClaim", 357772, "completed", "routed_to_arc", Thu, 04 Oct 2018 10:08:14 EDT -04:00]] # Backup filename="/tmp/Dispatch_Task_attribs.json" file_contents = JSON.pretty_generate dts.map{|r| r.attributes}; File.open(filename.to_s, "w") { |file| file.puts file_contents } # Deleting dts.map(&:delete) lhs=LegacyHearing.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}; lhs.count => 15 => 14 # Apr 13th filename="/tmp/LegacyHearing_attribs.json" file_contents = JSON.pretty_generate lhs.map{|r| r.attributes}; File.open(filename.to_s, "w") { |file| file.puts file_contents } lh_ids=LegacyHearing.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.pluck :id => [10942, 12633, 13925, 18661, 20198, 26147, 38200, 40532, 45040, 47428, 51277, 102441, 103706, 150368] lh_ids.count => 14 LegacyHearing.where(id: lh_ids).delete_all => 14 hs=Hearing.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}; hs.count => 0 ``` ::: * [Asked Tango about LegacyHearing records](https://dsva.slack.com/archives/C3EAF3Q15/p1618257067291600) * [Asked Tango about LegacyHearing records in UAT](https://dsva.slack.com/archives/C3EAF3Q15/p1621451741031900) [Add foreign keys to appeals and legacy_appeals table (Phase 5c)](https://github.com/department-of-veterans-affairs/caseflow/pull/16263#) #16263 ## :heavy_check_mark: Phase 7: RequestIssues, DecisionIssues, EPEs :::spoiler ```ruby= add_foreign_key "legacy_issue_optins", "request_issues" add_foreign_key "legacy_issues", "request_issues" add_foreign_key "request_decision_issues", "request_issues" add_foreign_key "request_decision_issues", "decision_issues" add_foreign_key "request_issues", "request_issues", column: "corrected_by_request_issue_id" add_foreign_key "request_issues", "request_issues", column: "ineligible_due_to_id" ``` ```ruby= lio=LegacyIssueOptin.includes(:request_issue).where.not(request_issue_id: nil).select{|r| r.request_issue == nil}; lio.count => 0 LegacyIssue.includes(:request_issue).where.not(request_issue_id: nil).select{|r| r.request_issue == nil}.pluck(:id) => [] # To prevent PG::QueryCanceled: ERROR: canceling statement due to statement timeout ActiveRecord::Base.connection.execute "SET statement_timeout = 1800000" # Don't show extremely long SQL statement ActiveRecord::Base.logger.level = :warn RequestDecisionIssue.includes(:request_issue).where.not(request_issue_id: nil).select{|r| r.request_issue == nil}.pluck(:id) => [] RequestDecisionIssue.includes(:decision_issue).where.not(decision_issue_id: nil).select{|r| r.decision_issue == nil}.pluck(:id) => [] RequestIssue.includes(:correction_request_issue).where.not(corrected_by_request_issue_id: nil).select{|r| r.correction_request_issue == nil}.pluck(:id) => [] RequestIssue.includes(:ineligible_due_to).where.not(ineligible_due_to_id: nil).select{|r| r.ineligible_due_to == nil}.pluck(:id) => [2799, 2800, 3194, 3195, 3196, 3197, 3198, 3199, 3200, 3201, 10300, 43613, 43614] # Sally says these can be deleted: https://dsva.slack.com/archives/C54QCEHAL/p1618360956115700?thread_ts=1618357083.114200&cid=C54QCEHAL # Wednesday, April 28 => [2799, 2800, 3194, 3195, 3196, 3197, 3198, 3199, 3200, 3201, 10300, 43613, 43614] # Tuesday, May 4 - ineligible_due_to_id has been removed from all records above => [] ``` ::: [Add foreign keys for request-issues-related tables (Phase 7)](https://github.com/department-of-veterans-affairs/caseflow/pull/16277#) #16277 ## :heavy_check_mark: Phase 7b: Request Issues - Missing DecisionIssues and EPEs :::spoiler ```ruby= add_foreign_key "request_issues", "decision_issues", column: "contested_decision_issue_id" add_foreign_key "request_issues", "end_product_establishments" RequestIssue.includes(:contested_decision_issue).where.not(contested_decision_issue_id: nil).select{|r| r.contested_decision_issue == nil}.pluck(:id) => [972638, 1932358, 5183, 972639, 1591821, 1758654, 1758655] # Sally fixed a few # Wednesday, April 28 => [5183, 972639, 1758655, 972638, 1932358, 1591821, 1758654] # 2 more have popped up: ACTION: identify root cause; if it's in our code, we need to fix it before deploying FK # Tuesday, June 1 => [] # Caused by manual updates made outside of Casflow RequestIssue.includes(:end_product_establishment).where.not(end_product_establishment_id: nil).select{|r| r.end_product_establishment == nil}.pluck(:id) # Long runtime ... [766681, 767166, 767171, 767172, 767534, 767540, 767541, 768069, 780817, 780820, 780949, 780950, 780951, 780955, 780956, 780957, 780958, 780959, 780960, 780961, 780963, 780964, 780965, 780966, 780967, 780968, 780969, 780974, 780975, 782647, 784263, 784266, 784336, 784339, 784907, 784993, 785162, 785239, 767542, 780952, 780962, 780954] # Wednesday, April 28 [766681, 767166, 767171, 767172, 767534, 767540, 767541, 768069, 780817, 780820, 780949, 780950, 780951, 780955, 780956, 780957, 780958, 780959, 780960, 780961, 780963, 780964, 780965, 780966, 780967, 780968, 780969, 780974, 780975, 782647, 784263, 784266, 784336, 784339, 784907, 784993, 785162, 785239, 767542, 780952, 780962, 780954] # Will: almost positive these can be deleted; hasn't happened in 16 months # We can create a separate PR for this one FK, in case we need to revert # Ask Sally: If we added a FK, what would break? # ACTION: why is this happening? # Tuesday, June 1 => [] # Caused by bug, fix has been made ``` ::: [Add Decision Issue and End Product Establishment FK to Request Issues table (Phase 7b)](https://github.com/department-of-veterans-affairs/caseflow/pull/16304) #16304 ## :heavy_check_mark: Phase 7c: EndProductCodeUpdate :::spoiler ```ruby= add_foreign_key "end_product_code_updates", "end_product_establishments" ``` ```ruby= EndProductCodeUpdate.unscoped.includes(:end_product_establishment).where.not(end_product_establishment_id: nil).select{|r| r.end_product_establishment == nil}.pluck(:id) => [20717, 21139, 21654, 42451, 23784] # Wednesday, April 28 => [20717, 21139, 21654, 42451, 23784] # Wednesday, June 2 => [] # Safe to delete. Useless without an existing End Product Establishment EndProductCodeUpdate.count => 25443 # Wednesday, April 28 => 25872 # Asked Foxtrot: https://dsva.slack.com/archives/C54QCEHAL/p1618405353117800 ``` ::: [Add foreign key to EndProductCodeUpdate table (Phase 7c)](https://github.com/department-of-veterans-affairs/caseflow/pull/16313#) #16313 ## :sleuth_or_spy: Phase I: Remaining DecisionIssues :::spoiler ```ruby= add_foreign_key "remand_reasons", "decision_issues" ``` ```ruby= # To prevent PG::QueryCanceled: ERROR: canceling statement due to statement timeout ActiveRecord::Base.connection.execute "SET statement_timeout = 1800000" # Don't show extremely long SQL statement ActiveRecord::Base.logger.level = :warn RemandReason.includes(:decision_issue).where.not(decision_issue_id: nil).select{|r| r.decision_issue == nil}.pluck(:id).count => 35994 # Yikes! # Wednesday, April 28 => 36831 RemandReason.count => 67118 # Wednesday, April 28 => 68593 ``` ::: Fixes requested for UAT: * https://dsva.slack.com/archives/C54QCEHAL/p1618326023091300 * https://dsva.slack.com/archives/C54QCEHAL/p1618326540095500 ## :heavy_check_mark: Phase 8: DocketTracer, NonAvailability, UserQuota :::spoiler ```ruby= add_foreign_key "docket_tracers", "docket_snapshots" add_foreign_key "non_availabilities", "schedule_periods" add_foreign_key "user_quotas", "team_quotas" add_foreign_key "ramp_election_rollbacks", "ramp_elections" ``` ```ruby= DocketTracer.unscoped.includes(:docket_snapshot).where.not(docket_snapshot_id: nil).select{|r| r.docket_snapshot == nil}.pluck(:id) => [] #Prod => [] #Preprod => [] #UAT NonAvailability.unscoped.includes(:schedule_period).where.not(schedule_period_id: nil).select{|r| r.schedule_period == nil}.pluck(:id) => [] #Prod => [] #Preprod => [] #UAT UserQuota.unscoped.includes(:team_quota).where.not(team_quota_id: nil).select{|r| r.team_quota == nil}.pluck(:id) => [] #Prod => [] #Preprod => [] #UAT RampElectionRollback.unscoped.includes(:ramp_election).where.not(ramp_election_id: nil).select{|r| r.ramp_election == nil}.pluck(:id) => [5, 8, 17, 21, 22, 35, 45, 48] #Prod => [] #Preprod => [1, 2, 6, 3, 4, 5] #UAT # All records in Prod were created in 2018 and appeals that were rolled back have since been opted in and closed. Safe to delete # All records in UAT were created in 2018 and do not correspond to an existing appeal. Safe to delete ``` ::: [Add Foreign Keys to DocketTracer, NonAvailability, UserQuota, and RampElectionRollback tables (Phase 8)](https://github.com/department-of-veterans-affairs/caseflow/pull/16327#) #16327 ## Phase D: Documents :::spoiler Caution: large tables ```ruby= add_foreign_key "document_views", "documents" add_foreign_key "documents_tags", "documents" add_foreign_key "documents_tags", "tags" ``` ```ruby= DocumentView.unscoped.includes(:document).where.not(document_id: nil).select{|r| r.document == nil}.pluck(:id) DocumentTag.unscoped.includes(:document).where.not(document_id: nil).select{|r| r.document == nil}.pluck(:id) DocumentTag.unscoped.includes(:tag).where.not(tag_id: nil).select{|r| r.tag == nil}.pluck(:id) ``` ::: ## :heavy_check_mark: Phase 9: remaining Appeals, LegacyAppeals :::spoiler ```ruby= # ignore polymorphic: add_foreign_key "special_issue_lists", "appeals" # ignore polymorphic: add_foreign_key "vbms_uploaded_documents", "appeals" add_foreign_key "hearing_appeal_stream_snapshots", "legacy_appeals", column: "appeal_id" add_foreign_key "hearing_appeal_stream_snapshots", "legacy_hearings", column: "hearing_id" ``` ```ruby= SpecialIssueList.count => 210796 sil_ids=SpecialIssueList.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.pluck(:id) sil_ids.count # takes about 10 seconds => 128 sil_ids.min => 1 sil_ids.max => 130 sil = SpecialIssueList.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}; # Backup records filename="/tmp/SpecialIssueList_attribs.json" file_contents = JSON.pretty_generate sil.map{|r| r.attributes}; File.open(filename.to_s, "w") { |file| file.puts file_contents } # Can we fix them? sil.pluck(:id, :appeal_id, :appeal_type, :created_at, :updated_at) sil.pluck(:id, :appeal_id, :appeal_type).min => [1, 154, nil] sil.pluck(:id, :appeal_id, :appeal_type).max => [130, 162, nil] SpecialIssueList.where(id: [131..150]).pluck(:id, :appeal_id, :appeal_type) # appeal_ids are high and are for LegacyAppeals SpecialIssueList.where(id: [131..150]).pluck(:appeal_id, :appeal_type).min => [5779, "LegacyAppeal"] SpecialIssueList.where(id: [131..150]).pluck(:appeal_id, :appeal_type).max => [488997, "LegacyAppeal"] # I'm guessing the problematic sils are AMA appeals # Find the first sils with AMA appeals SpecialIssueList.where(id: [150..500], appeal_type: "Appeal").pluck(:appeal_id, :appeal_type).min => [564, "Appeal"] # Check if existing SIL maps to any of the appeal_id sil_appeal_ids = sil.map(&:appeal_id) sil_appeal_ids.count => 128 SpecialIssueList.where(appeal_id: sil_appeal_ids, appeal_type: "Appeal") => [] SpecialIssueList.where(appeal_id: sil_appeal_ids, appeal_type: "LegacyAppeal").count => 43 # SILs for legacies already exist, so let's set appeal_type to "Appeal" sil.map{|s| s.update(appeal_type: "Appeal")} sil.select{|s| s.reload.appeal == nil} => [] sil.map{|s| [s.id, s.attributes.select{|k,v| v==true}]} sil.select{|s| s.attributes.select{|k,v| v==true}.size>0}.map(&:id).count => 50 # Only 50 have some true boolean; the 78 others don't have useful info # ALL FIXED VbmsUploadedDocument.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.count => 21 vdoc=VbmsUploadedDocument.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil} vdoc.count => 102 # April 29 => 145 # May 20 # Backup filename="/tmp/VbmsUploadedDocument_attribs.json" file_contents = JSON.pretty_generate vdoc.map{|r| r.attributes}; File.open(filename.to_s, "w") { |file| file.puts file_contents } vdoc.pluck(:id, :document_type, :error, :processed_at, :appeal_id, :appeal_type) p=vdoc.partition{|v| v.appeal_type=="Appeal"} p.map(&:count) => [2, 100] # 2 are AMA; the rest are legacies # The purpose of the record is to track document uploads to VBMS and is not used afterwards. vdoc.pluck(:processed_at, :id, :document_type, :error, :appeal_id, :appeal_type).max => [Mon, 19 Apr 2021 07:26:23 EDT -04:00, 95438, "AMA Notification Letter", nil, 1391381, "LegacyAppeal"] # Should investigate why these are going missing. # Waiting to delete them? VbmsUploadedDocument.where(id: vdoc.pluck(:id)).delete_all ass=AppealStreamSnapshot.unscoped.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil} ass.count => 12 assh=AppealStreamSnapshot.unscoped.includes(:hearing).where.not(hearing_id: nil).select{|r| r.hearing == nil} assh.count => 6 assh.count => 15 # Apr 29th # Asked Tango: https://dsva.slack.com/archives/C3EAF3Q15/p1619720929484500 # Save ass2=ass+assh ass2.count => 27 filename="/tmp/AppealStreamSnapshot_attribs.json" file_contents = JSON.pretty_generate ass2.map{|r| r.attributes}; File.open(filename.to_s, "w") { |file| file.puts file_contents } # These always refer to LegacyAppeals (and LegacyHearings), which may be associated with deleted cases in VACOLS # Adding a FK for this may present errors for the user! # Can be deleted b/c they'll be recreated: https://dsva.slack.com/archives/C3EAF3Q15/p1619800095001300?thread_ts=1619720929.484500&cid=C3EAF3Q15 ``` ::: * [Asked Tango about AppealStreamSnapshot](https://dsva.slack.com/archives/C3EAF3Q15/p1619720929484500) ## :heavy_check_mark: Phase 10: Board Grant Effectuations :::spoiler ```ruby= add_foreign_key "board_grant_effectuations", "appeals" add_foreign_key "board_grant_effectuations", "decision_documents" add_foreign_key "board_grant_effectuations", "end_product_establishments" add_foreign_key "board_grant_effectuations", "decision_issues", column: "granted_decision_issue_id" BoardGrantEffectuation.unscoped.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.count => 0 #Prod => 0 #UAT => 0 #Preprod BoardGrantEffectuation.unscoped.includes(:decision_document).where.not(decision_document_id: nil).select{|r| r.decision_document == nil}.count => 0 #Prod => 0 #UAT => 0 #Preprod BoardGrantEffectuation.unscoped.includes(:end_product_establishment).where.not(end_product_establishment_id: nil).select{|r| r.end_product_establishment == nil}.count => 0 #Prod => 0 #UAT => 0 #Preprod BoardGrantEffectuation.unscoped.includes(:granted_decision_issue).where.not(granted_decision_issue_id: nil).select{|r| r.granted_decision_issue == nil}.count => 0 #Prod => 0 #UAT => 0 #Preprod ``` ::: ## :sleuth_or_spy: Phase 11: Attorney Case Reviews :::spoiler ```ruby= add_foreign_key "attorney_case_reviews", "users", column: "attorney_id" add_foreign_key "attorney_case_reviews", "users", column: "reviewing_judge_id" AttorneyCaseReview.unscoped.includes(:attorney).where.not(attorney_id: nil).select{|r| r.attorney == nil}.count => 0 #Prod => 0 #Preprod => 0 #UAT AttorneyCaseReview.unscoped.includes(:reviewing_judge).where.not(reviewing_judge_id: nil).select{|r| r.reviewing_judge == nil}.count => 0 #Prod => 0 #Preprod => 0 #UAT ``` ::: ## :sleuth_or_spy: Phase 12 - Next up - Provided by Immigrant :::spoiler ```ruby= # Add FK to clumn file_number in veterans table add_foreign_key "available_hearing_locations", "veterans", column_id: "veteran_file_number" AvailableHearingLocations.unscoped.includes(:veteran).where.not(veteran_file_number: nil).select{|r| r.veteran == nil}.count => 429,482 #Prod. All records. Appears to be an issue with the ActiveRecord relationship => 0 #Preprod. No records => 244 #UAT. All records # Add FK to legacy_appeals add_foreign_key "worksheet_issues", "appeals" WorksheetIssue.unscoped.includes(:appeal).where.not(appeal_id: nil).select{|r| r.appeal == nil}.count => 65 #Prod => 23 #Preprod => 3,716 #UAT # Add FK to dispatch_tasks table (not the tasks table) add_foreign_key "claim_establishments", "tasks" ClaimEstablishment.unscoped.includes(:task).where.not(task_id: nil).select{|r| r.task == nil}.count => 7 #Prod => 924 #Preprod => 450,231 #UAT # Investigate these next and add foreign key if possible. add_foreign_key "advance_on_docket_motions", "person_id" add_foreign_key "ramp_issues", "source_issue_id" add_foreign_key "remand_reasons", "decision_issue_id" add_foreign_key "certification_cancellations", "certification_id" # The documents table is extremely large -- investigate these later add_foreign_key "annotations", "document_id" add_foreign_key "document_views", "document_id" add_foreign_key "documents_tags", "document_id" add_foreign_key "documents_tags", "tag_id" add_foreign_key "sent_hearing_email_events", "hearing_id" add_foreign_key "hearing_email_recipients", "hearing_id" add_foreign_key "special_issue_lists", "appeal_id" add_foreign_key "tasks", "appeal_id" add_foreign_key "vbms_uploaded_documents", "appeal_id" add_foreign_key "judge_case_reviews", "task_id" add_foreign_key "attorney_case_reviews", "task_id" # Don't need FKs on a cache table add_foreign_key "cached_appeal_attributes", "appeal_id" add_foreign_key "cached_appeal_attributes", "vacols_id" ``` ```ruby= ``` ::: ## IP Demo * List benefits, esp. Veteran * Count of all FK added * Count of FK skipped and describe why * (If we come across this:) Don't want to cause UI/worflow error * Too many records, e.g. Document, DocumentView, etc. * polymorphic associations * see `config/initializers/immigrant.rb` for reasons * Remind folks about new PR item that suggests foreign keys (using the `immigrant` gem) * Next actions * Create job to check for orphaned polymorphic associations * Regenerated automated schema documentation * Consider adding FKs for eFolder Express? ## Notes DONE: PR #16333: Add `bundle exec rake immigrant:check_keys` to Makefile and PR template TODO: document notes from https://github.com/department-of-veterans-affairs/caseflow/pull/15383#issuecomment-705173743 TODO: incorporate polymorphic and other references in auto-generated schema documentation (by manually adding associations to Jailer) - Task.appeal_id - DistributedCase.case_id - SpecialIssueList.appeal_id - VbmsUploadedDocument.appeal_id Others: run `make check-fks`; see `immigrant.rb` and notes in `lib/helpers/association_wrapper.rb` ``` WARNING: Skipping claimants.participant_id: it has multiple associations referencing different keys/tables. WARNING: Skipping sent_hearing_email_events.hearing_id: it has multiple associations referencing different keys/tables. WARNING: Skipping worksheet_issues.appeal_id: it has multiple associations referencing different keys/tables. WARNING: Skipping hearings.appeal_id: it has multiple associations referencing different keys/tables. ``` TODO: ensure `belongs_to` has corresponding `has_one` - [ruby on rails - What's the difference between belongs\_to and has\_one? - Stack Overflow](https://stackoverflow.com/questions/3808926/whats-the-difference-between-belongs-to-and-has-one) TODO: Add FKs for eFolder Express?

Syntax	Example	Reference
# Header	Header	基本排版
- Unordered List	Unordered List
1. Ordered List	Ordered List
- [ ] Todo List	Todo List
> Blockquote	Blockquote
Bold font	Bold font
Italics font	Italics font
~~Strikethrough~~	~~Strikethrough~~
19^th^	19^th
H~2~O	H₂O
++Inserted text++	Inserted text
==Marked text==	Marked text
[link text](https:// "title")	Link
![image alt](https:// "title")	Image
`Code`	`Code`	在筆記中貼入程式碼
```javascript var i = 0; ```	`var i = 0;`
:smile:		Emoji list
{%youtube youtube_id %}	Externals
$L^aT_eX$	L^aT_eX
:::info This is a alert area. :::	This is a alert area.