Technical Documentation of Virtual Hearing DB Schemas

[ferristseng]

Description

Create some technical documentation around the table schemas supporting virtual hearings for the TIB/B3 teams.

Would be good to have it next week by next Wednesday (4/29)

Acceptance criteria

N/A

Background/context/resources

Request:

https://dsva.slack.com/archives/C3EAF3Q15/p1587564256404000

Technical notes

N/A

[ferristseng]

Schemas

virtual_hearings table

alias (string), alias_with_host (string) - alias refers to an alias we supply to Pexip as a way of identifying the conference. This alias should always consist of 7 digits, and can be used to generate the other aliases we were instructed to create by the Pexip engineering team. This is generated before the conference is actually created in Pexip.

guest_pin (integer), host_pin (integer) - the pin numbers for the guest and host for joining the conference. These are generated at the same time alias is generated.

conference_deleted (boolean) - whether or not a conference has been deleted in Pexip. We only delete a conference after it has been held, or if it was canceled. Note: we keep the virtual hearings records in our database after they are deleted for historical purposes. A hearing could have multiple associated virtual hearing records, but there should only be one active at any given time.

conference_id (integer) - the ID of the conference after it was created in Pexip. Once this is set, we start sending confirmation emails to the veteran, POA, and judge. We consider a virtual hearing "active" if the conference id is set, and the hearing hasn't been canceled.

hearing_id (integer), hearing_type (string) - a foreign key that points to a record in the hearings table or legacy_hearings table. A hearing is a virtual hearing if it has an associated virtual hearing that hasn't been canceled. Currently, virtual hearings can only be created from existing Video hearings.

request_cancelled (boolean) - whether or not the request for a virtual hearing has been cancelled (this is different from the hearing being cancelled or postponed). If a virtual hearing was cancelled, that indicates the hearing should take place at the original location (e.g. at the original RO/hearing location for a video hearing). Once a virtual hearing request is cancelled, the conference is deleted in Pexip and notification emails are sent out to the veteran, POA, and judge.

*_email (string), *_email_sent (boolean) - these track the status of sending emails to the veteran, POA, and representative in Caseflow. Whenever the veteran, POA, or representative need to be notified of any changes to the virtual hearing (e.g. if the time of the hearing is modified), the the *_email_sent are all toggled back to false.

virtual_hearing_establishments table

The virtual_hearing_establishments table stores metadata around the job that is responsible for creating the conference in Pexip. Once a conference is successfully created, the processed_at field is updated with the date and time the job was completed.

sent_hearing_email_events table

The sent_hearing_email_events table provides a log of all emails we send to the veteran, POA, or judge.

email_address (string) - the recipient of the email.

email_type (string) - an identifier indicating what kind of email we sent to the user. The options are currently:

• cancellation
• confirmation
• updated_time_confirmation

external_message_id (string) - this is the ID we get back from GovDelivery, which is the service we use to send all of our emails. We are able to use this to check the status of the email (bounced or sent), since GovDelivery sends it asynchronously.

hearing_id (integer), hearing_type (string) - a foreign key that points to a record in the hearings table or legacy_hearings table. Currently, all emails we send are related to virtual hearings, so this table will include only emails for virtual hearings.

recipient_role (string) - the role of the recipient of the email. The options are currently:

• veteran
• representative
• judge

sent_at (datetime) - date and time the email was sent

[ferristseng]

This was handed over to the TIB/B3 team yesterday!