owned this note
owned this note
Published
Linked with GitHub
# 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?