Create job to check for users that cannot be found in VACOLS

[yoomlam]

Resulting from #14307 investigation for stuck appeals.

Description

Create job to check for users that cannot be found in VACOLS (possibly due to a change made in VACOLS)?

Something like this:

AttorneyTask.includes(:assigned_to).open.where(appeal_type: :LegacyAppeal).map(&:assigned_to).uniq.select{|atty| 
 UserRepository.user_info_from_vacols(atty.css_id).nil?  
}
=> []

Occurs rarely but will stall appeal processing.

Acceptance criteria

• regularly running job that reports on assignee users (users on judge teams) that cannot be found in VACOLS
• regularly running job that reports on legacy case attorneys that cannot be found in caseflow

Background/context/resources

#14307 comment A

Technical notes

• Can also do additional checks on atty, e.g., is atty part of active JudgeTeam?
  • Response: not being part of an active JudgeTeam doesn't cause the appeal to be stuck.

[lomky]

what is this chart?

1 | |
2 | ||||||||
3 | 
5 | 
8 | 

Make sure the error message is actionable!

New job vs stuck appeals checker?

if new job and query is quick, daily.

Probably StuckAppealsChecker tho

Why 1?

Why 2?

• New query on stuck appeals checker

[hschallhorn]

Another issue:
Active legacy appeals where the demdusr on the associated vacols record does not map to a caseflow user because they have no sdomainid. Linky

legacy_appeal_vacols_ids = LegacyAppeal.pluck(:vacols_id)
demdusr_slogids = legacy_appeal_vacols_ids.in_groups_of(1000, false).flat_map do |batch_vacols_ids|
  active_case_vacols_ids = VACOLS::Case.where(bfkey: batch_vacols_ids).where.not(bfmpro: "HIS").pluck(:bfkey)
  VACOLS::Decass.where(defolder: active_case_vacols_ids).pluck(:demdusr)
end.uniq
# SLOWWWW, needs refining

assigners_with_no_sdomainid = VACOLS::Staff.where(slogid: demdusr_slogids, sdomainid: nil).pluck(:slogid)
=> ["SDURKIN", "MSABLSKY", "AJMULLEN", "RSULIVAN", "SLWILKNS", "JMCDNALD", "SUSNTOTH", "JJMARTIN", "DWEISS", "MGALGHER", "GEGUIDO", "RPELTIER", "RWSCHOLZ"]

vacols_css_ids = VACOLS::Staff.where(slogid: demdusr_slogids).where.not(sdomainid: nil).pluck(:sdomainid)
assigners_with_no_caseflow_user = vacols_css_ids - User.pluck(:css_id)
=> 0

[hschallhorn]

There are no AttorneyTasks on legacy appeals in caseflow.
When a judge tries to assign a legacy appeal to an attorney, we first show them their team, then all attorneys from vacols. This error came from on of the caseflow users on a judge team not being associated with a vacols record. This check should ensure all attorneys on a judge team have an associated vacols record that shows that they are an attorney.

judge_team_atty_css_ids = OrganizationsUser.includes(:user).where(organization: JudgeTeam.active, admin: false).pluck("users.css_id")
vacols_atty_css_ids = VACOLS::Staff.where.not(sattyid: nil).pluck(:sdomainid)
css_ids_not_in_vacols = judge_team_atty_css_ids - vacols_atty_css_ids
=> ["VBASAJVALENN", "VBAPHOTHOMPM"]

# Double check these would throw the error in the sentry alert (User#vacols_uniq_id.nil? is true)
css_ids_not_in_vacols.map { |css_id| User.find_by(css_id: css_id).vacols_uniq_id }.uniq
=> [nil]

Note that I also found a few attys on judge teams that were marked inactive
or are judges in vacols. Unsure if that is an issue.

# the attorney scope filters out inactive users
vacols_atty_css_ids = VACOLS::Staff.attorney.pluck(:sdomainid)
css_ids_not_in_vacols = judge_team_atty_css_ids - vacols_atty_css_ids
css_ids_not_in_vacols.count
=> 30
VACOLS::Staff.where(sdomainid: css_ids_not_in_vacols).group(:sactive).count
=> {"I"=>19, "A"=>8}
VACOLS::Staff.where(sdomainid: css_ids_not_in_vacols).group(:svlj).count
=> {nil=>18, "J"=>8, "A"=>1}

[hschallhorn]

@yoomlam What do you think the best format for this is? This all seems like non caseflow errors (teams not up to date, invalid vacols records, etc.) If this is a job, we would need to pass along the bad info to the board to fix. Is a job report the best format for the bad data report?

[yoomlam]

@hschallhorn I'm not sure of the kind of answer you want, so here goes...

If you can create dashboard items like the "Inactive users" ones at the top of https://query.prod.appeals.va.gov/dashboard/15, then the query results would be more current than a daily job. If the Board makes a fix, the results would should up in the dashboard faster. Plus, the SQL query can be reused in other tools in the future.

A job report is acceptable as well. The Rails code would be more widely understandable in case we need to update it.

In either case, Ann-Marie could grab the result and send to the Board.

The format itself would contain the users and the associated case IDs.

[yoomlam]

Note that I also found a few attys on judge teams that were marked inactive
or are judges in vacols. Unsure if that is an issue.

If you can report on inactive VACOLS::Staff that have open assignments, then that would cover the VACOLS Inactive users, who are not checked in the charts at the top of https://query.prod.appeals.va.gov/dashboard/15.

[hschallhorn]

Starting a dashboard here: https://query.prod.appeals.va.gov/dashboard/17

EDIT: We don't exactly have access to redshift right now. Will need to make this a job.

[yoomlam]

Using Redshift is now and will be acceptable.

I don't think you need the "Number of active cases with assigner with no associated caseflow user" 102 count in the dashboard since that is provided at the bottom of the table:

I think removing it will reduce the number of queries that Metabase makes as well.

[hschallhorn]

@araposo-tistatech I created a dashboard for user records that will result in caseflow errors.
https://query.prod.appeals.va.gov/dashboard/17
How to read this dashboard:

Number of assigners of active cases with no associated caseflow user

This is the number of vacols staff records that are invalid. For any legacy case these users have created a decision for, if the judge tries to send the case to dispatch, they will receive a "Record is invalid: Attorney can't be blank".

This can be rectified by the board updating the demdusr (modifying user) of the associated decass (DAS) record to the uniq vacols id of an attorney that exists in caseflow. The next box in the dashboad provides the cases these invalid user records affect. On occasion, this has been caused by a duplicate vacols staff record. The demdusr is associated with a now retired vacols staff record, even though the real life person is in caseflow and has an updated staff record in vacols. (see below example)

Example Batteam Request: https://dsva.slack.com/archives/CHX8FMP28/p1603819414394600
Example solution: https://dsva.slack.com/archives/CJL810329/p1603825257039300?thread_ts=1603822444.031300&cid=CJL810329

Assigners of active cases with no associated caseflow user

This is the mapping the users above to the cases that will fail if a judge tries to send them to dispatch. The number of rows is the number of cases affected by these user records

• regularly running job that reports on legacy case attorneys that cannot be found in caseflow

Users on a judge team with no corresponding vacols user

These are users that are on judge teams in caseflow that do not have a corresponding vacols staff record. This will result in a VACOLS::Case::InvalidLocationError if the judge tries to assign a legacy appeal to this user.

This can be rectified by ensuring there is a vacols staff record associated with the caseflow user css_id.

• regularly running job that reports on assignee users (users on judge teams) that cannot be found in VACOLS

[yoomlam]

ACs met. This can be closed.