Caseflow Schema CSV #

Column	Type	Required	Index	Description
appeal_id	integer FK		x	The ID of the appeal this motion is associated with
appeal_type	string		x	The type of appeal this motion is associated with
created_at	datetime ∗	x
granted	boolean		x	Whether VLJ has determined that there is sufficient cause to fast-track an appeal, i.e. grant or deny the motion to AOD.
id	integer (8) PK	x
person_id	integer (8) FK		x	Appellant ID
reason	string			VLJ's rationale for their decision on motion to AOD.
updated_at	datetime ∗	x	x
user_id	integer (8) FK		x

'allocations' table

Hearing Day Requests for each Regional Office used for calculation and confirmation of the Build Hearings Schedule Algorithm

Column	Type	Required	Index	Description
allocated_days	float ∗	x		Number of Video or Central Hearing Days Requested by the Regional Office
allocated_days_without_room	float			Number of Hearing Days Allocated with no Rooms
created_at	datetime ∗	x		Standard created_at/updated_at timestamps
first_slot_time	string (5)			The first time slot available for this allocation; interpreted as the local time at Central office or the RO
id	integer (8) PK	x
number_of_slots	integer			The number of time slots possible for this allocation
regional_office	string ∗	x		Key of the Regional Office Requesting Hearing Days
schedule_period_id	integer (8) ∗ FK	x	x	Hearings Schedule Period to which this request belongs
slot_length_minutes	integer			The length in minutes of each time slot for this allocation
updated_at	datetime ∗	x	x	Standard created_at/updated_at timestamps

'annotations' table

Column	Type	Required	Index
comment	string ∗	x
created_at	datetime
document_id	integer ∗ FK	x	x
id	integer PK	x
page	integer
relevant_date	date
updated_at	datetime
user_id	integer FK		x
x	integer
y	integer

'api_keys' table

Column	Type	Required	Index
consumer_name	string ∗	x	x
created_at	datetime
id	integer PK	x
key_digest	string ∗	x	x
updated_at	datetime		x

'api_views' table

Column	Type	Required
api_key_id	integer FK
created_at	datetime
id	integer PK	x
source	string
updated_at	datetime
vbms_id	string

'appeals' table

Decision reviews intaken for AMA appeals to the board (also known as a notice of disagreement).

Column	Type	Required	Index	Description
aod_based_on_age	boolean		x	If true, appeal is advance-on-docket due to claimant's age.
changed_hearing_request_type	string			The new hearing type preference for an appellant that needs a hearing scheduled
closest_regional_office	string			The code for the regional office closest to the Veteran on the appeal.
created_at	datetime
docket_range_date	date			Date that appeal was added to hearing docket range.
docket_type	string ∗	x	x	The docket type selected by the Veteran on their appeal form, which can be hearing, evidence submission, or direct review.
established_at	datetime		x	Timestamp for when the appeal has successfully been intaken into Caseflow by the user.
establishment_attempted_at	datetime			Timestamp for when the appeal's establishment was last attempted.
establishment_canceled_at	datetime			Timestamp when job was abandoned
establishment_error	string			The error message if attempting to establish the appeal resulted in an error. This gets cleared once the establishment is successful.
establishment_last_submitted_at	datetime			Timestamp for when the the job is eligible to run (can be reset to restart the job).
establishment_processed_at	datetime			Timestamp for when the establishment has succeeded in processing.
establishment_submitted_at	datetime			Timestamp for when the the intake was submitted for asynchronous processing.
filed_by_va_gov	boolean			Indicates whether or not this form came from VA.gov
homelessness	boolean ∗	x		Indicates whether or not a veteran is experiencing homelessness
id	integer (8) PK	x
legacy_opt_in_approved	boolean			Indicates whether a Veteran opted to withdraw matching issues from the legacy process. If there is a matching legacy issue and it is not withdrawn then it is ineligible for the decision review.
original_hearing_request_type	string			The hearing type preference for an appellant before any changes were made in Caseflow
poa_participant_id	string			Used to identify the power of attorney (POA) at the time the appeal was dispatched to BVA. Sometimes the POA changes in BGS after the fact, and BGS only returns the current representative.
receipt_date	date ∗	x		Receipt date of the appeal form. Used to determine which issues are within the timeliness window to be appealed. Only issues decided prior to the receipt date will show up as contestable issues.
stream_docket_number	string			Multiple appeals with the same docket number indicate separate appeal streams, mimicking the structure of legacy appeals.
stream_type	string			When multiple appeals have the same docket number, they are differentiated by appeal stream type, depending on the work being done on each appeal.
target_decision_date	date			If the appeal docket is direct review, this sets the target decision date for the appeal, which is one year after the receipt date.
updated_at	datetime		x
uuid	uuid ∗	x	x	The universally unique identifier for the appeal, which can be used to navigate to appeals/appeal_uuid. This allows a single ID to determine an appeal whether it is a legacy appeal or an AMA appeal.
veteran_file_number	string ∗	x	x	PII. The VBA corporate file number of the Veteran for this review. There can sometimes be more than one file number per Veteran.
veteran_is_not_claimant	boolean			Selected by the user during intake, indicates whether the Veteran is the claimant, or if the claimant is someone else such as a dependent. Must be TRUE if Veteran is deceased.

'appeal_series' table

Column	Type	Required	Index
created_at	datetime
id	integer PK	x
incomplete	boolean
merged_appeal_count	integer
updated_at	datetime		x

'appeal_states' table

Column	Type	Required	Index	Description
appeal_cancelled	boolean ∗	x		When true, appeal's root task is cancelled
appeal_docketed	boolean ∗	x		When true, appeal has been docketed
appeal_id	integer (8) ∗ FK	x	x	AMA or Legacy Appeal ID
appeal_type	string ∗	x	x	Appeal Type (Appeal or LegacyAppeal)
created_at	datetime ∗	x
created_by_id	integer (8) ∗ FK	x		User id of the user that inserted the record
decision_mailed	boolean ∗	x		When true, appeal has decision mail request complete
hearing_postponed	boolean ∗	x		When true, appeal has hearing postponed and no hearings scheduled
hearing_scheduled	boolean ∗	x		When true, appeal has at least one hearing scheduled
hearing_withdrawn	boolean ∗	x		When true, appeal has hearing withdrawn and no hearings scheduled
id	integer (8) PK	x
privacy_act_complete	boolean ∗	x		When true, appeal has a privacy act request completed
privacy_act_pending	boolean ∗	x		When true, appeal has a privacy act request still open
scheduled_in_error	boolean ∗	x		When true, hearing was scheduled in error and none scheduled
updated_at	datetime
updated_by_id	integer (8) FK			User id of the last user that updated the record
vso_ihp_complete	boolean ∗	x		When true, appeal has a VSO IHP request completed
vso_ihp_pending	boolean ∗	x		When true, appeal has a VSO IHP request pending

'hearing_appeal_stream_snapshots' table

Column	Type	Required	Index	Description
appeal_id	integer FK		x	LegacyAppeal ID; use as FK to legacy_appeals
created_at	datetime ∗	x		Automatic timestamp of when snapshot was created
hearing_id	integer FK		x	LegacyHearing ID; use as FK to legacy_hearings
updated_at	datetime		x	Automatic timestamp of when snapshot was updated

'appeal_views' table

Column	Type	Required	Index
appeal_id	integer ∗ FK	x	x
appeal_type	string ∗	x	x
created_at	datetime ∗	x
id	integer PK	x
last_viewed_at	datetime
updated_at	datetime ∗	x
user_id	integer ∗ FK	x	x

'appellant_substitutions' table

Store appellant substitution form data

Column	Type	Required	Index	Description
claimant_type	string ∗	x		Claimant type of substitute; needed to create Claimant record
created_at	datetime ∗	x		Standard created_at/updated_at timestamps
created_by_id	integer (8) ∗ FK	x		User that created this record
id	integer (8) PK	x
poa_participant_id	string			Identifier of the appellant's POA, if they have a CorpDB participant_id. Null if the substitute appellant has no POA.
selected_task_ids	integer (8) ∗	x		User-selected task ids from source appeal
source_appeal_id	integer (8) ∗ FK	x	x	The relevant source appeal for this substitution
substitute_participant_id	string ∗	x		Participant ID of substitute appellant
substitution_date	date ∗	x		Date of substitution
target_appeal_id	integer (8) ∗ FK	x	x	The new appeal resulting from this substitution
task_params	jsonb ∗	x		JSON hash to hold parameters for new tasks, such as an EvidenceSubmissionWindowTask's end-hold date, with keys from selected_task_ids
updated_at	datetime ∗	x		Standard created_at/updated_at timestamps

'appellant_substitution_histories' table

Column	Type	Required	Index	Description
appellant_substitution_id	integer (8) FK		x	Appellant substitution id of the last user that updated the CAVC record
created_at	datetime ∗	x
created_by_id	integer (8) FK			Current user who created Appellant substitution
current_appellant_substitute_participant_id	string FK			Current Appellant Substitute participant Id
current_appellant_veteran_participant_id	string FK			Current Appellant Veteran participant Id
id	integer (8) PK	x
original_appellant_substitute_participant_id	string FK			Original Appellant Substitute participant Id
original_appellant_veteran_participant_id	string FK			Original Appeallant Veteran Participant Id
substitution_date	date			Timestamp of substitution granted date
updated_at	datetime ∗	x

'attorney_case_reviews' table

Column	Type	Required	Index	Description
appeal_id	integer (8) FK		x	The ID of the appeal this case review is associated with
appeal_type	string		x	The type of appeal this case review is associated with
attorney_id	integer FK
created_at	datetime ∗	x
document_id	string ∗	x
document_type	string ∗	x
id	integer PK	x
note	text
overtime	boolean
reviewing_judge_id	integer FK
task_id	string ∗ FK	x	x	Refers to the tasks table for AMA appeals, but uses syntax `<vacols_id>-YYYY-MM-DD` for legacy appeals
untimely_evidence	boolean
updated_at	datetime ∗	x	x
work_product	string ∗	x

'available_hearing_locations' table

Column	Type	Required	Index	Description
address	string			Full address of the location
appeal_id	integer FK		x	Appeal/LegacyAppeal ID; use as FK to appeals/legacy_appeals
appeal_type	string		x	'Appeal' or 'LegacyAppeal'
city	string			i.e 'New York', 'Houston', etc
classification	string			The classification for location; i.e 'Regional Benefit Office', 'VA Medical Center (VAMC)', etc
created_at	datetime ∗	x		Automatic timestamp of when hearing location was created
distance	float			Distance between appellant's location and the hearing location
facility_id	string			Id associated with the facility; i.e 'vba_313', 'vba_354a', 'vba_317', etc
facility_type	string			The type of facility; i.e, 'va_benefits_facility', 'va_health_facility', 'vet_center', etc
id	integer (8) PK	x
name	string			Name of location; i.e 'Chicago Regional Benefit Office', 'Jennings VA Clinic', etc
state	string			State in abbreviated form; i.e 'NY', 'CA', etc
updated_at	datetime ∗	x	x	Automatic timestamp of when hearing location was updated
veteran_file_number	string		x	PII. The VBA corporate file number of the Veteran for the appeal
zip_code	string

'batch_processes' table

A generalized table for batching and processing records within caseflow

Column	Type	Required	Index	Description
batch_id	uuid PK FK	x
batch_type	string ∗	x	x	Indicates what type of record is being batched
created_at	datetime ∗	x		Date and Time that batch was created.
ended_at	datetime			The date/time that the batch finsished processing
records_attempted	integer			The number of records in the batch attempting to be processed
records_completed	integer			The number of records in the batch that completed processing successfully
records_failed	integer		x	The number of records in the batch that failed processing
started_at	datetime			The date/time that the batch began processing
state	string ∗	x	x	The state that the batch is currently in. PRE_PROCESSING, PROCESSING, PROCESSED
updated_at	datetime ∗	x		Date and Time that batch was last updated.

'bgs_attorneys' table

Cache of unique BGS attorney data — used for adding claimants to cases pulled from POA data

Column	Type	Required	Index	Description
created_at	datetime ∗	x	x	Standard created_at/updated_at timestamps
id	integer (8) PK	x
last_synced_at	datetime		x	The last time BGS was checked
name	string ∗	x	x	Name
participant_id	string ∗	x	x	Participant ID
record_type	string ∗	x		Known types: POA State Organization, POA National Organization, POA Attorney, POA Agent, POA Local/Regional Organization
updated_at	datetime ∗	x	x	Standard created_at/updated_at timestamps

'bgs_power_of_attorneys' table

Power of Attorney (POA) cached from BGS

Column	Type	Required	Index	Description
authzn_change_clmant_addrs_ind	string			Authorization for POA to change claimant address
authzn_poa_access_ind	string			Authorization for POA access
claimant_participant_id	string ∗	x	x	Claimant participant ID -- use as FK to claimants
created_at	datetime ∗	x	x	Standard created_at/updated_at timestamps
file_number	string		x	PII. Claimant file number
id	integer (8) PK	x
last_synced_at	datetime		x	The last time BGS was checked
legacy_poa_cd	string			Legacy POA code
poa_participant_id	string ∗	x	x	POA participant ID -- use as FK to people
representative_name	string ∗	x	x	POA name
representative_type	string ∗	x	x	POA type
updated_at	datetime ∗	x	x	Standard created_at/updated_at timestamps

'board_grant_effectuations' table

Represents the work item of updating records in response to a granted issue on a Board appeal. Some are represented as contentions on an EP in VBMS. Others are tracked via Caseflow tasks.

Column	Type	Required	Index	Description
appeal_id	integer (8) ∗ FK	x	x	The ID of the appeal containing the granted issue being effectuated.
contention_reference_id	string		x	The ID of the contention created in VBMS. Indicates successful creation of the contention. If the EP has been rated, this contention could have been connected to a rating issue. That connection is used to map the rating issue back to the decision issue.
created_at	datetime
decision_document_id	integer (8) FK		x	The ID of the decision document which triggered this effectuation.
decision_sync_attempted_at	datetime			When the EP is cleared, an asyncronous job attempts to map the resulting rating issue back to the decision issue. Timestamp representing the time the job was last attempted.
decision_sync_canceled_at	datetime			Timestamp when job was abandoned
decision_sync_error	string			Async job processing last error message. See description for decision_sync_attempted_at for the decision sync job description.
decision_sync_last_submitted_at	datetime			Timestamp for when the the job is eligible to run (can be reset to restart the job).
decision_sync_processed_at	datetime			Async job processing completed timestamp. See description for decision_sync_attempted_at for the decision sync job description.
decision_sync_submitted_at	datetime			Async job processing start timestamp. See description for decision_sync_attempted_at for the decision sync job description.
end_product_establishment_id	integer (8) FK		x	The ID of the end product establishment created for this board grant effectuation.
granted_decision_issue_id	integer (8) ∗ FK	x	x	The ID of the granted decision issue.
id	integer (8) PK	x
last_submitted_at	datetime			Async job processing most recent start timestamp
updated_at	datetime		x

'cached_appeal_attributes' table

Column	Type	Index	Description
appeal_id	integer	x
appeal_type	string	x
case_type	string	x	The case type, i.e. original, post remand, CAVC remand, etc
closest_regional_office_city	string	x	Closest regional office to the veteran
closest_regional_office_key	string	x	Closest regional office to the veteran in 4 character key
created_at	datetime
docket_number	string
docket_type	string	x
former_travel	boolean	x	Determines if the hearing type was formerly travel board; only applicable to Legacy appeals
hearing_request_type	string (10)	x	Stores hearing type requested by appellant; could be one of nil, 'Video', 'Central', 'Travel', or 'Virtual'
is_aod	boolean	x	Whether the case is Advanced on Docket
issue_count	integer		Number of issues on the appeal.
issue_types	string		A string delimited list of nonrating issue categories on the appeal.
power_of_attorney_name	string	x	'Firstname Lastname' of power of attorney
suggested_hearing_location	string	x	Suggested hearing location in 'City, State (Facility Type)' format
updated_at	datetime	x
vacols_id	string	x
veteran_name	string	x	'LastName, FirstName' of the veteran

'cached_user_attributes' table

VACOLS cached staff table attributes

Column	Type	Required	Index	Description
created_at	datetime ∗	x
sactive	string ∗	x
sattyid	string
sdomainid	string PK	x	x
slogid	string ∗	x
smemgrp	string (8)
snamef	string			User's First Name in VACOLS
snamel	string			User's Last Name in VACOLS
stafkey	string ∗	x
stitle	string (16)
svlj	string
updated_at	datetime ∗	x	x

'caseflow_stuck_records' table

This is a polymorphic table consisting of records that have repeatedly errored out of the syncing process. Currently, the only records on this table come from the PriorityEndProductSyncQueue table.

Column	Type	Required	Index	Description
determined_stuck_at	datetime ∗	x		The date/time at which the record in question was determined to be stuck.
error_messages	string			Array of Error Message(s) containing Batch ID and specific error if a failure occurs
id	integer (8) PK	x
remediated	boolean ∗	x		Reflects if the stuck record has been reviewed and fixed
remediation_notes	text			Brief description of the encountered issue and remediation strategy
stuck_record_id	integer (8) ∗ FK	x	x	The id / primary key of the stuck record and the type / where the record came from
stuck_record_type	string ∗	x	x
updated_at	datetime			The time an update occurred on the record

'cavc_dashboards' table

Column	Type	Required	Index	Description
board_decision_date	date ∗	x		The decision date of the source appeal
board_docket_number	string ∗	x		The docket number of the source appeal
cavc_decision_date	date ∗	x		The decision date from the CAVC board
cavc_docket_number	string ∗	x		The docket number assigned by the CAVC board
cavc_remand_id	integer (8) FK		x	ID of the associated CAVC Remand
created_at	datetime ∗	x
created_by_id	integer (8) FK			The ID for the user that created the record
id	integer (8) PK	x
joint_motion_for_remand	boolean			Whether the CAVC appeal is JMR/JMPR or not
updated_at	datetime ∗	x
updated_by_id	integer (8) FK			The ID for the user that most recently changed the record

'cavc_dashboard_dispositions' table

Column	Type	Required	Index	Description
cavc_dashboard_id	integer (8) FK		x	ID of the associated CAVC Dashboard
cavc_dashboard_issue_id	integer (8) FK
created_at	datetime ∗	x
created_by_id	integer (8) FK			The ID for the user that created the record
disposition	string ∗	x		The disposition of the issue
id	integer (8) PK	x
request_issue_id	integer (8) FK			ID for a request issue that was filed with the CAVC Remand
updated_at	datetime ∗	x
updated_by_id	integer (8) FK			The ID for the user that most recently changed the record

'cavc_dashboard_issues' table

Column	Type	Required	Index	Description
benefit_type	string
cavc_dashboard_id	integer (8) FK		x	ID of the associated CAVC Dashboard
created_at	datetime
created_by_id	integer (8) FK
id	integer (8) PK	x
issue_category	string
issue_description	string
updated_at	datetime
updated_by_id	integer (8) FK

'cavc_decision_reasons' table

Column	Type	Required	Description
basis_for_selection_category	string		The category that the decision reason belongs to. Optional.
created_at	datetime
decision_reason	string		The reason for the CAVC decision
id	integer (8) PK	x
order	integer		The order that the reasons should display in the UI. Child reasons will be ordered under their parent.
parent_decision_reason_id	integer		Associates a child decision reason to its parent in this table

'cavc_dispositions_to_reasons' table

Column	Type	Required	Index	Description
cavc_dashboard_disposition_id	integer (8) FK		x	ID of the associated CAVC Dashboard Disposition
cavc_decision_reason_id	integer (8) FK		x	ID of the associated CAVC Decision Reason
created_at	datetime ∗	x
created_by_id	integer (8) FK			The ID for the user that created the record
id	integer (8) PK	x
updated_at	datetime ∗	x
updated_by_id	integer (8) FK			The ID for the user that most recently changed the record

'cavc_reasons_to_bases' table

Column	Type	Required	Index	Description
cavc_dispositions_to_reason_id	integer (8) FK		x	ID of the associated CAVC Decision Reason
cavc_selection_basis_id	integer (8) FK		x	ID of the associated CAVC Basis for Selection
created_at	datetime ∗	x
created_by_id	integer (8) FK			The ID for the user that created the record
id	integer (8) PK	x
updated_at	datetime ∗	x
updated_by_id	integer (8) FK			The ID for the user that most recently changed the record

'cavc_remands' table

Column	Type	Required	Index	Description
cavc_decision_type	string ∗	x		CAVC decision type. Expecting 'remand', 'straight_reversal', or 'death_dismissal'
cavc_docket_number	string ∗	x		Docket number of the CAVC judgement
cavc_judge_full_name	string ∗	x		CAVC judge that passed the judgement on the remand
created_at	datetime ∗	x		Default timestamps
created_by_id	integer (8) ∗ FK	x		User that created this record
decision_date	date ∗	x		Date CAVC issued a decision, according to the CAVC
decision_issue_ids	integer (8) ∗	x		Decision issues being remanded; IDs refer to decision_issues table. For a JMR, all decision issues on the previous appeal will be remanded. For a JMPR, only some
federal_circuit	boolean			Whether the case has been appealed to the US Court of Appeals for the Federal Circuit
id	integer (8) PK	x
instructions	string ∗	x		Instructions and context provided upon creation of the remand record
judgement_date	date ∗	x		Date CAVC issued a judgement, according to the CAVC
mandate_date	date ∗	x		Date that CAVC reported the mandate was given
remand_appeal_id	integer (8) FK		x	Appeal created by this CAVC Remand
remand_subtype	string ∗	x		Type of remand. If the cavc_decision_type is 'remand', expecting one of 'jmp', 'jmpr', or 'mdr'. Otherwise, this can be null.
represented_by_attorney	boolean ∗	x		Whether or not the appellant was represented by an attorney
source_appeal_id	integer (8) ∗ FK	x	x	Appeal that CAVC has remanded
updated_at	datetime ∗	x		Default timestamps
updated_by_id	integer (8) FK			User that updated this record. For MDR remands, judgement and mandate dates will be added after the record is first created.

'cavc_remands_appellant_substitutions' table

Column	Type	Required	Index	Description
appellant_substitution_id	integer (8) FK		x	Appellant Substitution this is tied to
cavc_remand_id	integer (8) FK		x	Cavc Remand this is tied to
created_at	datetime ∗	x
created_by_id	integer (8)			Current user who created substitution
id	integer (8) PK	x
is_appellant_substituted	boolean			Y/N Boolean for active substitution
participant_id	string		x	Claimant Participant Id
remand_source	string			Source of Remand - From Add or Edit
substitute_participant_id	string		x	Appellant Substitute participant Id
substitution_date	date			Timestamp of substitution
updated_at	datetime ∗	x
updated_by_id	integer (8)			Current user who updated substitution

'cavc_selection_bases' table

Column	Type	Required
basis_for_selection	string
category	string
created_at	datetime
created_by	integer (8)
id	integer (8) PK	x
updated_at	datetime
updated_by	integer (8)

'certifications' table

Column	Type	Required	Index
already_certified	boolean
bgs_rep_address_line_1	string
bgs_rep_address_line_2	string
bgs_rep_address_line_3	string
bgs_rep_city	string
bgs_rep_country	string
bgs_rep_state	string
bgs_rep_zip	string
bgs_representative_name	string
bgs_representative_type	string
certification_date	string
certifying_office	string
certifying_official_name	string
certifying_official_title	string
certifying_username	string
completed_at	datetime
created_at	datetime ∗	x
form8_started_at	datetime
form9_matching_at	datetime
form9_type	string
hearing_change_doc_found_in_vbms	boolean
hearing_preference	string
id	integer PK	x
loading_data	boolean
loading_data_failed	boolean
nod_matching_at	datetime
poa_correct_in_bgs	boolean
poa_correct_in_vacols	boolean
poa_matches	boolean
representative_name	string
representative_type	string
soc_matching_at	datetime
ssocs_matching_at	datetime
ssocs_required	boolean
updated_at	datetime ∗	x	x
user_id	integer		x
v2	boolean
vacols_data_missing	boolean
vacols_hearing_preference	string
vacols_id	string
vacols_representative_name	string
vacols_representative_type	string

'certification_cancellations' table

Column	Type	Required	Index
cancellation_reason	string ∗	x
certification_id	integer ∗ FK	x	x
created_at	datetime
email	string ∗	x
id	integer PK	x
other_reason	string
updated_at	datetime		x

'claim_establishments' table

Column	Type	Required	Index	Description
created_at	datetime ∗	x
decision_type	integer
email_recipient	string
email_ro_id	string
ep_code	string
id	integer PK	x
outcoding_date	datetime
task_id	integer FK		x	references dispatch_tasks
updated_at	datetime ∗	x	x

'claimants' table

This table bridges decision reviews to participants when the participant is listed as a claimant on the decision review. A participant can be a claimant on multiple decision reviews.

Column	Type	Required	Unique	Index	Description
created_at	datetime
decision_review_id	integer (8) ∗ FK	x		x	The ID of the decision review the claimant is on.
decision_review_type	string ∗	x		x	The type of decision review the claimant is on.
id	integer (8) PK	x
notes	text				This is a notes field for adding claimant not listed and any supplementary information outside of unlisted claimant.
participant_id	string ∗ U	x	x	x	The participant ID of the claimant.
payee_code	string				The payee_code for the claimant, if applicable. payee_code is required when the claim is processed in VBMS.
type	string				The class name for the single table inheritance type of Claimant, for example VeteranClaimant, DependentClaimant, AttorneyClaimant, OtherClaimant, or HealthcareProviderClaimant.
updated_at	datetime			x

'claims_folder_searches' table

Column	Type	Required	Index
appeal_id	integer FK		x
appeal_type	string ∗	x	x
created_at	datetime
id	integer PK	x
query	string
updated_at	datetime		x
user_id	integer FK		x

'conference_links' table

Column	Type	Required	Index	Description
alias	string			Alias of the conference
alias_with_host	string			Alieas of the conference for the host
conference_deleted	boolean ∗	x		Flag to represent if a con ference has been deleted
conference_id	integer			Id of the conference
created_at	datetime ∗	x		Date and Time of creation
created_by_id	integer (8) ∗ FK	x	x	User id of the user who created the record. FK on User table
guest_hearing_link	string			Guest link for hearing daily docket.
guest_pin_long	string			Pin provided for the guest, allowing them entry into the video conference.
hearing_day_id	integer (8) ∗ FK	x	x	The associated hearing day id
host_link	string			Conference link generated from external conference service
host_pin	integer			Pin for the host of the conference to get into the conference
host_pin_long	string (8)			Generated host pin stored as a string
id	integer (8) PK	x
updated_at	datetime			Date and Time record was last updated
updated_by_id	integer (8) FK		x	user id of the user to last update the record. FK on the User table

'decision_documents' table

Column	Type	Required	Index	Description
appeal_id	integer (8) ∗ FK	x	x
appeal_type	string
attempted_at	datetime			When the job ran
canceled_at	datetime			Timestamp when job was abandoned
citation_number	string ∗	x	x	Unique identifier for decision document
created_at	datetime ∗	x
decision_date	date ∗	x
document_series_reference_id	string			UUID that is provided by eFolder that represents the group of documentsthis document belongs to. Think of a series as a stack of versions.
document_version_reference_id	string			UUID that is provided by eFolder that represents the specific version of the document.
error	string			Message captured from a failed attempt
id	integer (8) PK	x
last_submitted_at	datetime			When the job is eligible to run (can be reset to restart the job)
processed_at	datetime			When the job has concluded
redacted_document_location	string ∗	x
submitted_at	datetime			When the job first became eligible to run
updated_at	datetime ∗	x	x
uploaded_to_vbms_at	datetime			When document was successfully uploaded to VBMS

'decision_issues' table

Issues that represent a decision made on a decision review.

Column	Type	Required	Index	Description
benefit_type	string			Classification of the benefit being decided on. Maps 1 to 1 to VA lines of business, and typically used to know which line of business the decision correlates to.
caseflow_decision_date	date			This is a decision date for decision issues where decisions are entered in Caseflow, such as for appeals or for decision reviews with a business line that is not processed in VBMS.
created_at	datetime			Automatic timestamp when row was created.
decision_review_id	integer FK		x	ID of the decision review the decision was made on.
decision_review_type	string		x	Type of the decision review the decision was made on.
decision_text	string			If decision resulted in a change to a rating, the rating issue's decision text.
deleted_at	datetime		x
description	string			Optional description that the user can input for decisions made in Caseflow.
diagnostic_code	string			If a decision resulted in a rating, this is the rating issue's diagnostic code.
disposition	string ∗	x	x	The disposition for a decision issue. Dispositions made in Caseflow and dispositions made in VBMS can have different values.
end_product_last_action_date	date ∗	x		After an end product gets synced with a status of CLR (cleared), the end product's last_action_date is saved on any decision issues that are created as a result. This is used as a proxy for decision date for non-rating issues that are processed in VBMS because they don't have a rating profile date, and the exact decision date is not available.
id	integer (8) PK	x
participant_id	string ∗	x		The Veteran's participant id.
percent_number	string			percent_number from RatingIssue (prcntNo from Rating Profile)
rating_issue_reference_id	string		x	Identifies the specific issue on the rating that resulted from the decision issue (a rating issue can be connected to multiple contentions).
rating_profile_date	datetime			The profile date of the rating that a decision issue resulted in (if applicable). The profile_date is used as an identifier for the rating, and is the date that most closely maps to what the Veteran writes down as the decision date.
rating_promulgation_date	datetime			The promulgation date of the rating that a decision issue resulted in (if applicable). It is used for calculating whether a decision issue is within the timeliness window to be appealed or get a higher level review.
subject_text	text			subject_text from RatingIssue (subjctTxt from Rating Profile)
updated_at	datetime		x

'dispatch_tasks' table

Column	Type	Required	Index	Description
aasm_state	string		x	Current task state: unprepared, unassigned, assigned, started, reviewed, completed
appeal_id	integer ∗ FK	x
assigned_at	datetime
comment	string
completed_at	datetime
completion_status	integer
created_at	datetime ∗	x
id	integer PK	x
lock_version	integer
outgoing_reference_id	string
prepared_at	datetime
started_at	datetime
type	string ∗	x
updated_at	datetime ∗	x	x
user_id	integer FK		x

'distributed_cases' table

Column	Type	Required	Index
case_id	string ∗	x	x
created_at	datetime
distribution_id	integer FK		x
docket	string ∗	x
docket_index	integer ∗	x
genpop	boolean
genpop_query	string ∗	x
id	integer (8) PK	x
priority	boolean
ready_at	datetime ∗	x
task_id	integer ∗ FK	x
updated_at	datetime		x

'distributions' table

Column	Type	Required	Index	Description
completed_at	datetime
created_at	datetime ∗	x
errored_at	datetime			when the Distribution job suffered an error
id	integer (8) PK	x
judge_id	integer FK
priority_push	boolean			Whether or not this distribution is a priority-appeals-only push to judges via a weekly job (not manually requested)
started_at	datetime			when the Distribution job commenced
statistics	json
status	string
updated_at	datetime ∗	x	x

'docket_snapshots' table

Column	Type	Required	Index
created_at	datetime
docket_count	integer
id	integer PK	x
latest_docket_month	date
updated_at	datetime		x

'docket_switches' table

Stores the disposition and associated data for Docket Switch motions.

Column	Type	Required	Index	Description
created_at	datetime ∗	x	x	Standard created_at/updated_at timestamps
disposition	string ∗	x		Possible options are granted, partially_granted, and denied
docket_type	string			The new docket
granted_request_issue_ids	integer			When a docket switch is partially granted, this includes an array of the appeal's request issue IDs that were selected for the new docket. For full grant, this includes all prior request issue IDs.
id	integer (8) PK	x
new_docket_stream_id	integer (8) FK		x	References the new appeal stream with the updated docket; initially null until created by workflow
old_docket_stream_id	integer (8) ∗ FK	x	x	References the original appeal stream with old docket
receipt_date	datetime ∗	x		Date the board receives the NOD with request for docket switch; entered by user performing docket switch
task_id	integer (8) ∗ FK	x	x	The task that triggered the switch
updated_at	datetime ∗	x		Standard created_at/updated_at timestamps

'docket_tracers' table

Column	Type	Required	Index
ahead_and_ready_count	integer
ahead_count	integer
created_at	datetime
docket_snapshot_id	integer FK		x
id	integer PK	x
month	date		x
updated_at	datetime		x

'documents' table

Column	Type	Required	Index	Description
category_medical	boolean
category_other	boolean
category_procedural	boolean
created_at	datetime
description	string
file_number	string		x	PII
id	integer PK	x
previous_document_version_id	integer
received_at	date
series_id	string		x
type	string
updated_at	datetime
upload_date	date
vbms_document_id	string ∗	x	x

'document_views' table

Column	Type	Required	Index
created_at	datetime
document_id	integer ∗ FK	x	x
first_viewed_at	datetime
id	integer PK	x
updated_at	datetime
user_id	integer ∗ FK	x	x

'documents_tags' table

Column	Type	Required	Index
created_at	datetime
document_id	integer ∗ FK	x	x
id	integer PK	x
tag_id	integer ∗ FK	x	x
updated_at	datetime

'end_product_code_updates' table

Caseflow establishes end products in VBMS with specific end product codes. If that code is changed outside of Caseflow, that is tracked here.

Column	Type	Required	Index	Description
code	string ∗	x		The new end product code, if it has changed since last checked.
created_at	datetime ∗	x
end_product_establishment_id	integer (8) ∗ FK	x	x
id	integer (8) PK	x
updated_at	datetime ∗	x	x

'end_product_establishments' table

Represents end products that have been, or need to be established by Caseflow. Used to track the status of those end products as they are processed in VBMS and/or SHARE.

Column	Type	Required	Index	Description
benefit_type_code	string			1 if the Veteran is alive, and 2 if the Veteran is deceased. Not to be confused with benefit_type, which is unrelated.
claim_date	date			The claim_date for end product established.
claimant_participant_id	string			The participant ID of the claimant submitted on the end product.
code	string			The end product code, which determines the type of end product that is established. For example, it can contain information about whether it is rating, nonrating, compensation, pension, created automatically due to a Duty to Assist Error, and more.
committed_at	datetime			Timestamp indicating other actions performed as part of a larger atomic operation containing the end product establishment, such as creating contentions, are also complete.
created_at	datetime
development_item_reference_id	string			When a Veteran requests an informal conference with their higher level review, a tracked item is created. This stores the ID of the of the tracked item, it is also used to indicate the success of creating the tracked item.
doc_reference_id	string			When a Veteran requests an informal conference, a claimant letter is generated. This stores the document ID of the claimant letter, and is also used to track the success of creating the claimant letter.
established_at	datetime			Timestamp for when the end product was established.
id	integer (8) PK	x
last_synced_at	datetime			The time that the status of the end product was last synced with BGS. The end product is synced until it is canceled or cleared, meaning it is no longer active.
limited_poa_access	boolean			Indicates whether the limited Power of Attorney has access to view documents
limited_poa_code	string			The limited Power of Attorney code, which indicates whether the claim has a POA specifically for this claim, which can be different than the Veteran's POA
modifier	string			The end product modifier. For higher level reviews, the modifiers range from 030-039. For supplemental claims, they range from 040-049. The same modifier cannot be used twice for an active end product per Veteran. Once an end product is no longer active, the modifier can be used again.
payee_code	string ∗	x		The payee_code of the claimant submitted for this end product.
reference_id	string		x	The claim_id of the end product, which is stored after the end product is successfully established in VBMS.
source_id	integer (8) ∗ FK	x	x	The ID of the source that resulted in this end product establishment.
source_type	string ∗	x	x	The type of source that resulted in this end product establishment.
station	string			The station ID of the end product's station.
synced_status	string			The status of the end product, which is synced by a job. Once and end product is cleared (CLR) or canceled (CAN) the status is final and the end product will not continue being synced.
updated_at	datetime		x
user_id	integer FK		x	The ID of the user who performed the decision review intake.
veteran_file_number	string ∗	x	x	PII. The file number of the Veteran submitted when establishing the end product.

'end_product_updates' table

Updates the claim label for end products established from Caseflow

Column	Type	Required	Index	Description
active_request_issue_ids	integer (8) ∗	x		A list of active request issue IDs when a user has finished editing a decision review. Used to keep track of which request issues may have been impacted by the update.
created_at	datetime ∗	x
end_product_establishment_id	integer (8) ∗ FK	x	x	The end product establishment id used to track the end product being updated.
error	string			The error message captured from BGS if the end product update failed.
id	integer (8) PK	x
new_code	string			The new end product code the user wants to update to.
original_code	string			The original end product code before the update was submitted.
original_decision_review_id	integer (8)		x	The original decision review that this end product update belongs to; has a non-nil value only if a new decision_review was created.
original_decision_review_type	string		x	The original decision review type that this end product update belongs to
status	string			Status after an attempt to update the end product; expected values: 'success', 'error', ...
updated_at	datetime ∗	x
user_id	integer (8) ∗ FK	x	x	The ID of the user who makes an end product update.

'form8s' table

Column	Type	Required	Index	Description
_initial_appellant_name	string
_initial_appellant_relationship	string
_initial_hearing_requested	string
_initial_increased_rating_notification_date	date
_initial_insurance_loan_number	string
_initial_other_notification_date	date
_initial_representative_name	string
_initial_representative_type	string
_initial_service_connection_notification_date	date
_initial_soc_date	date
_initial_ssoc_required	string
_initial_veteran_name	string
agent_accredited	string
appellant_name	string
appellant_relationship	string
certification_date	date
certification_id	integer		x
certifying_office	string
certifying_official_name	string
certifying_official_title	string
certifying_official_title_specify_other	string
certifying_username	string
contested_claims_procedures_applicable	string
contested_claims_requirements_followed	string
created_at	datetime ∗	x
file_number	string			PII
form9_date	date
form_646_not_of_record_explanation	string
form_646_of_record	string
hearing_held	string
hearing_preference	string
hearing_requested	string
hearing_requested_explanation	string
hearing_transcript_on_file	string
id	integer PK	x
increased_rating_for	text
increased_rating_notification_date	date
insurance_loan_number	string
nod_date	date
other_for	text
other_notification_date	date
power_of_attorney	string
power_of_attorney_file	string
record_cf_or_xcf	string
record_clinical_rec	string
record_dental_f	string
record_dep_ed_f	string
record_hospital_cor	string
record_inactive_cf	string
record_insurance_f	string
record_loan_guar_f	string
record_other	string
record_other_explanation	text
record_outpatient_f	string
record_r_and_e_f	string
record_slides	string
record_tissue_blocks	string
record_training_sub_f	string
record_x_rays	string
remarks	text
representative_name	string
representative_type	string
representative_type_specify_other	string
service_connection_for	text
service_connection_notification_date	date
soc_date	date
ssoc_date_1	date
ssoc_date_2	date
ssoc_date_3	date
ssoc_required	string
updated_at	datetime ∗	x
vacols_id	string
veteran_name	string

'global_admin_logins' table

Column	Type	Required	Index
admin_css_id	string
created_at	datetime
id	integer PK	x
target_css_id	string
target_station_id	string
updated_at	datetime		x

'hearings' table

Column	Type	Required	Index	Description
appeal_id	integer ∗ FK	x		Appeal ID; use as FK to appeals
bva_poc	string			Hearing coordinator full name
created_at	datetime			Automatic timestamp when row was created.
created_by_id	integer (8) FK		x	The ID of the user who created the Hearing
disposition	string		x	Hearing disposition; can be one of: 'held', 'postponed', 'no_show', or 'cancelled'
evidence_window_waived	boolean			Determines whether the veteran/appelant has wavied the 90 day evidence hold
hearing_day_id	integer ∗ FK	x		HearingDay ID; use as FK to HearingDays
id	integer (8) PK	x
judge_id	integer FK			User ID of judge who will hold the hearing
military_service	string			Periods and circumstances of military service
notes	string			Any notes taken prior or post hearing
prepped	boolean			Determines whether the judge has checked the hearing as prepped
representative_name	string			Name of Appellant's representative if applicable
room	string			The room at BVA where the hearing will take place; ported from associated HearingDay
scheduled_time	time ∗	x		Date and Time when hearing will take place
summary	text			Summary of hearing
transcript_requested	boolean			Determines whether the veteran/appellant has requested the hearing transcription
transcript_sent_date	date			Date of when the hearing transcription was sent to the Veteran/Appellant
updated_at	datetime		x	Timestamp when record was last updated.
updated_by_id	integer (8) FK		x	The ID of the user who most recently updated the Hearing
uuid	uuid ∗	x	x
witness	string			Witness/Observer present during hearing

'hearing_days' table

Column	Type	Required	Index	Description
bva_poc	string			Hearing coordinator full name
created_at	datetime ∗	x		Automatic timestamp of when hearing day was created
created_by_id	integer (8) ∗ FK	x	x	The ID of the user who created the Hearing Day
deleted_at	datetime		x	Automatic timestamp of when hearing day was deleted
first_slot_time	string (5)			The first time slot available; interpreted as the local time at Central office or the RO
id	integer (8) PK	x
judge_id	integer FK			User ID of judge who is assigned to the hearing day
lock	boolean			Determines if the hearing day is locked and can't be edited
notes	text			Any notes about hearing day
number_of_slots	integer			The number of time slots possible for this day
regional_office	string			Regional office key associated with hearing day
request_type	string ∗	x		Hearing request types for all associated hearings; can be one of: 'T', 'C' or 'V'
room	string			The room at BVA where the hearing will take place
scheduled_for	date ∗	x		The date when all associated hearings will take place
slot_length_minutes	integer			The length in minutes of each time slot for this day
updated_at	datetime ∗	x	x	Automatic timestamp of when hearing day was updated
updated_by_id	integer (8) ∗ FK	x	x	The ID of the user who most recently updated the Hearing Day

'hearing_email_recipients' table

Recipients of hearings-related emails

Column	Type	Required	Index	Description
appeal_id	integer (8) FK		x	The ID of the appeal this email recipient is associated with
appeal_type	string		x	The type of appeal this email recipient is associated with
created_at	datetime ∗	x
email_address	string ∗	x		PII. The recipient's email address
email_sent	boolean ∗	x		Indicates if a notification email was sent to the recipient.
hearing_id	integer (8) FK		x	Associated hearing
hearing_type	string		x
id	integer (8) PK	x
timezone	string (50)			The recipient's timezone
type	string			The subclass name (i.e. AppellantHearingEmailRecipient)
updated_at	datetime ∗	x

'hearing_issue_notes' table

Column	Type	Required	Index
allow	boolean
created_at	datetime
deny	boolean
dismiss	boolean
hearing_id	integer (8) ∗ FK	x	x
id	integer (8) PK	x
remand	boolean
reopen	boolean
request_issue_id	integer (8) ∗ FK	x	x
updated_at	datetime		x
worksheet_notes	string

'hearing_locations' table

Column	Type	Required	Index	Description
address	string			Full address of the location
city	string			i.e 'New York', 'Houston', etc
classification	string			The classification for location; i.e 'Regional Benefit Office', 'VA Medical Center (VAMC)', etc
created_at	datetime ∗	x		Automatic timestamp of when hearing location was created
distance	float			Distance between appellant's location and the hearing location
facility_id	string ∗	x		Id associated with the facility; i.e 'vba_313', 'vba_354a', 'vba_317', etc
facility_type	string			The type of facility; i.e, 'va_benefits_facility', 'va_health_facility', 'vet_center', etc
hearing_id	integer FK		x	Hearing/LegacyHearing ID; use as FK to hearings/legacy_hearings
hearing_type	string		x	'Hearing' or 'LegacyHearing'
id	integer (8) PK	x
name	string			Name of location; i.e 'Chicago Regional Benefit Office', 'Jennings VA Clinic', etc
state	string			State in abbreviated form; i.e 'NY', 'CA', etc
updated_at	datetime ∗	x	x	Automatic timestamp of when hearing location was updated
zip_code	string

'hearing_task_associations' table

Column	Type	Required	Index	Description
created_at	datetime			Automatic timestamp of when association was created
hearing_id	integer (8) ∗ FK	x	x	Hearing/LegacyHearing ID; use as FK to hearings/legacy_hearings
hearing_task_id	integer (8) ∗ FK	x	x	associated HearingTask ID; use as fk to tasks
hearing_type	string ∗	x	x	'Hearing' or 'LegacyHearing'
id	integer (8) PK	x
updated_at	datetime		x	Automatic timestamp of when association was updated

'hearing_views' table

Column	Type	Required	Index	Description
created_at	datetime			Automatic timestamp of when hearing view was created
hearing_id	integer ∗ FK	x	x	Hearing/LegacyHearing ID; use as FK to hearings/legacy_hearings
hearing_type	string		x	'Hearing' or 'LegacyHearing'
id	integer PK	x
updated_at	datetime			Automatic timestamp of when hearing view was updated
user_id	integer ∗ FK	x	x	User ID; use as FK to users

'higher_level_reviews' table

Intake data for Higher Level Reviews.

Column	Type	Required	Index	Description
benefit_type	string ∗	x		The benefit type selected by the Veteran on their form, also known as a Line of Business.
created_at	datetime
establishment_attempted_at	datetime			Timestamp for the most recent attempt at establishing a claim.
establishment_canceled_at	datetime			Timestamp when job was abandoned
establishment_error	string			The error captured for the most recent attempt at establishing a claim if it failed. This is removed once establishing the claim succeeds.
establishment_last_submitted_at	datetime			Timestamp for the latest attempt at establishing the End Products for the Decision Review.
establishment_processed_at	datetime			Timestamp for when the End Product Establishments for the Decision Review successfully finished processing.
establishment_submitted_at	datetime			Timestamp for when the Higher Level Review was submitted by a Claims Assistant. This adds the End Product Establishment to a job to finish processing asynchronously.
filed_by_va_gov	boolean			Indicates whether or not this form came from VA.gov
id	integer (8) PK	x
informal_conference	boolean			Indicates whether a Veteran selected on their Higher Level Review form to have an informal conference. This creates a claimant letter and a tracked item in BGS.
legacy_opt_in_approved	boolean			Indicates whether a Veteran opted to withdraw their Higher Level Review request issues from the legacy system if a matching issue is found. If there is a matching legacy issue and it is not withdrawn, then that issue is ineligible to be a new request issue and a contention will not be created for it.
receipt_date	date ∗	x		The date that the Higher Level Review form was received by central mail. This is used to determine which issues are eligible to be appealed based on timeliness. Only issues decided prior to the receipt date will show up as contestable issues. It is also the claim date for any associated end products that are established.
same_office	boolean			Whether the Veteran wants their issues to be reviewed by the same office where they were previously reviewed. This creates a special issue on all of the contentions created on this Higher Level Review.
updated_at	datetime		x
uuid	uuid ∗	x	x	The universally unique identifier for the Higher Level Review. Can be used to link to the claim after it is completed.
veteran_file_number	string ∗	x	x	PII. The file number of the Veteran that the Higher Level Review is for.
veteran_is_not_claimant	boolean			Indicates whether the Veteran is the claimant on the Higher Level Review form, or if the claimant is someone else like a spouse or a child. Must be TRUE if the Veteran is deceased.

'ihp_drafts' table

Column	Type	Required	Index	Description
appeal_id	integer ∗ FK	x	x	Appeal id the IHP was written for
appeal_type	string ∗	x	x	Type of appeal the IHP was written for
created_at	datetime ∗	x		Default created_at/updated_at timestamps
id	integer (8) PK	x
organization_id	integer ∗ FK	x	x	IHP-writing VSO that drafted the IHP
path	string ∗	x		Path to the IHP in the VA V: drive
updated_at	datetime ∗	x		Default created_at/updated_at timestamps

'intakes' table

Represents the intake of an form or request made by a veteran.

Column	Type	Required	Index	Description
cancel_other	string			Notes added if a user canceled an intake for any reason other than the stock set of options.
cancel_reason	string			The reason the intake was canceled. Could have been manually canceled by a user, or automatic.
completed_at	datetime			Timestamp for when the intake was completed, whether it was successful or not.
completion_started_at	datetime			Timestamp for when the user submitted the intake to be completed.
completion_status	string			Indicates whether the intake was successful, or was closed by being canceled, expired, or due to an error.
created_at	datetime
detail_id	integer FK		x	The ID of the record created as a result of the intake.
detail_type	string		x	The type of the record created as a result of the intake.
error_code	string			If the intake was unsuccessful due to a set of known errors, the error code is stored here. An error is also stored here for RAMP elections that are connected to an active end product, even though the intake is a success.
id	integer PK	x
started_at	datetime			Timestamp for when the intake was created, which happens when a user successfully searches for a Veteran.
type	string		x	The class name of the intake.
updated_at	datetime		x
user_id	integer ∗ FK	x	x	The ID of the user who created the intake.
veteran_file_number	string		x	PII. The VBA corporate file number of the Veteran for this review. There can sometimes be more than one file number per Veteran.
veteran_id	integer (8) FK		x	The ID of the veteran record associated with this intake

'job_notes' table

Column	Type	Required	Index	Description
created_at	datetime ∗	x		Default created_at/updated_at
id	integer (8) PK	x
job_id	integer (8) ∗ FK	x	x	The job to which the note applies
job_type	string ∗	x	x
note	text ∗	x		The note
send_to_intake_user	boolean			Should the note trigger a message to the job intake user
updated_at	datetime ∗	x	x	Default created_at/updated_at
user_id	integer (8) ∗ FK	x	x	The user who created the note

'judge_case_reviews' table

Column	Type	Required	Index	Description
appeal_id	integer (8) FK		x	The ID of the appeal this case review is associated with
appeal_type	string		x	The type of appeal this case review is associated with
areas_for_improvement	text
attorney_id	integer FK
comment	text
complexity	string ∗	x
created_at	datetime ∗	x
factors_not_considered	text
id	integer (8) PK	x
judge_id	integer FK
location	string ∗	x
one_touch_initiative	boolean
positive_feedback	text
quality	string ∗	x
task_id	string ∗ FK	x		Refers to the tasks table for AMA appeals, but uses syntax `<vacols_id>-YYYY-MM-DD` for legacy appeals
timeliness	string			Documents if the drafted decision by an attorney was provided on a timely or untimely manner.
updated_at	datetime ∗	x	x

'legacy_appeals' table

Column	Type	Required	Index	Description
appeal_series_id	integer (8) FK		x
changed_hearing_request_type	string			The new hearing type preference for an appellant that needs a hearing scheduled
closest_regional_office	string
contaminated_water_at_camp_lejeune	boolean
created_at	datetime
dic_death_or_accrued_benefits_united_states	boolean
dispatched_to_station	string
education_gi_bill_dependents_educational_assistance_scholars	boolean
foreign_claim_compensation_claims_dual_claims_appeals	boolean
foreign_pension_dic_all_other_foreign_countries	boolean
foreign_pension_dic_mexico_central_and_south_america_caribb	boolean
hearing_including_travel_board_video_conference	boolean
home_loan_guaranty	boolean
id	integer (8) PK	x
incarcerated_veterans	boolean
insurance	boolean
issues_pulled	boolean
manlincon_compliance	boolean
mustard_gas	boolean
national_cemetery_administration	boolean
nonrating_issue	boolean
original_hearing_request_type	string			The hearing type preference for an appellant before any changes were made in Caseflow
pension_united_states	boolean
private_attorney_or_agent	boolean
radiation	boolean
rice_compliance	boolean
spina_bifida	boolean
updated_at	datetime		x
us_territory_claim_american_samoa_guam_northern_mariana_isla	boolean
us_territory_claim_philippines	boolean
us_territory_claim_puerto_rico_and_virgin_islands	boolean
vacols_id	string ∗	x	x
vamc	boolean
vbms_id	string
vocational_rehab	boolean
waiver_of_overpayment	boolean

'legacy_hearings' table

Column	Type	Required	Index	Description
appeal_id	integer FK			LegacyAppeal ID; use as FK to legacy_appeals
created_at	datetime			Automatic timestamp when row was created.
created_by_id	integer (8) FK		x	The ID of the user who created the Legacy Hearing
hearing_day_id	integer (8)		x	The hearing day the hearing will take place on
id	integer (8) PK	x
military_service	string			Periods and circumstances of military service
original_vacols_request_type	string			The original request type of the hearing in VACOLS, before it was changed to Virtual
prepped	boolean			Determines whether the judge has checked the hearing as prepped
summary	text			Summary of hearing
updated_at	datetime		x	Timestamp when record was last updated.
updated_by_id	integer (8) FK		x	The ID of the user who most recently updated the Legacy Hearing
user_id	integer FK		x	User ID of judge who will hold the hearing
vacols_id	string ∗	x	x	Corresponds to VACOLS’ hearsched.hearing_pkseq
witness	string			Witness/Observer present during hearing

'legacy_issues' table

On an AMA decision review, when a veteran requests to review an issue that is already being contested on a legacy appeal, the legacy issue is connected to the request issue. If the veteran also chooses to opt their legacy issues into AMA and the issue is eligible to be transferred to AMA, the issues are closed in VACOLS through a legacy issue opt-in. This table stores the legacy issues connected to each request issue, and the record for opting them into AMA (if applicable).

Column	Type	Required	Index	Description
created_at	datetime ∗	x		Default created_at/updated_at
id	integer (8) PK	x
request_issue_id	integer (8) ∗ FK	x	x	The request issue the legacy issue is being connected to.
updated_at	datetime ∗	x		Default created_at/updated_at
vacols_id	string ∗	x		The VACOLS ID of the legacy appeal that the legacy issue is part of.
vacols_sequence_id	integer ∗	x		The sequence ID of the legacy issue on the legacy appeal. The vacols_id and vacols_sequence_id form a composite key to identify a specific legacy issue.

'legacy_issue_optins' table

When a VACOLS issue from a legacy appeal is opted-in to AMA, this table keeps track of the related request_issue, and the status of processing the opt-in, or rollback if the request issue is removed from a Decision Review.

Column	Type	Required	Index	Description
created_at	datetime ∗	x		When a Request Issue is connected to a VACOLS issue on a legacy appeal, and the Veteran has agreed to withdraw their legacy appeals, a legacy_issue_optin is created at the time the Decision Review is successfully intaken. This is used to indicate that the legacy issue should subsequently be opted into AMA in VACOLS.
error	string
folder_decision_date	date			Decision date on case record folder
id	integer (8) PK	x
legacy_issue_id	integer (8) FK		x	The legacy issue being opted in, which connects to the request issue
optin_processed_at	datetime			The timestamp for when the opt-in was successfully processed, meaning it was updated in VACOLS as opted into AMA.
original_disposition_code	string			The original disposition code of the VACOLS issue being opted in. Stored in case the opt-in is rolled back.
original_disposition_date	date			The original disposition date of the VACOLS issue being opted in. Stored in case the opt-in is rolled back.
original_legacy_appeal_decision_date	date			The original disposition date of a legacy appeal being opted in
original_legacy_appeal_disposition_code	string			The original disposition code of legacy appeal being opted in
request_issue_id	integer (8) ∗ FK	x	x	The request issue connected to the legacy VACOLS issue that has been opted in.
rollback_created_at	datetime			Timestamp for when the connected request issue is removed from a Decision Review during edit, indicating that the opt-in needs to be rolled back.
rollback_processed_at	datetime			Timestamp for when a rolled back opt-in has successfully finished being rolled back.
updated_at	datetime ∗	x	x	Automatically populated when the record is updated.

'membership_requests' table

Column	Type	Required	Unique	Index	Description
created_at	datetime ∗	x
decided_at	datetime				The date and time when the deider user made a decision about the membership request
decider_id	integer (8) FK				The user who decides the status of the membership request
id	integer (8) PK	x
note	string				A note that provides additional context from the requestor about their request for access to the organization
organization_id	integer (8) FK			x	The organization that the membership request is asking to join
requestor_id	integer (8) FK			x	The User that is requesting access to the organization
status	string ∗ U	x	x	x	The status of the membership request at any given point of time
updated_at	datetime ∗	x

'messages' table

Column	Type	Required	Index	Description
created_at	datetime ∗	x
detail_id	integer FK		x	ID of the related object
detail_type	string		x	Model name of the related object
id	integer (8) PK	x
message_type	string			The type of event that caused this message to be created
read_at	datetime			When the message was read
text	string			The message
updated_at	datetime ∗	x	x
user_id	integer ∗ FK	x		The user for whom the message is intended

'metrics' table

Column	Type	Required	Index	Description
additional_info	json			additional data to store for the metric
app_name	string ∗	x	x	Application name: caseflow or efolder
created_at	datetime ∗	x
duration	float			Time in milliseconds from start to end
end	datetime			When metric recording stopped
id	integer (8) PK	x
metric_attributes	json			Store attributes relevant to the metric: OS, browser, etc
metric_class	string ∗	x		Class of metric, use reflection to find value to populate this
metric_group	string ∗	x		Metric group: service, etc
metric_message	string ∗	x		Message or log for metric
metric_name	string ∗	x	x	Name of metric
metric_product	string ∗	x	x	Where in application: Queue, Hearings, Intake, VHA, etc
metric_type	string ∗	x		Type of metric: ERROR, LOG, PERFORMANCE, etc
relevant_tables_info	json			Store information to tie metric to database table(s)
sent_to	string		x	Which system metric was sent to: Datadog, Rails Console, Javascript Console, etc
sent_to_info	json			Additional information for which system metric was sent to
start	datetime			When metric recording started
updated_at	datetime ∗	x
user_id	integer (8) ∗ FK	x	x	The ID of the user who generated metric.
uuid	uuid ∗	x		Unique ID for the metric, can be used to search within various systems for the logging

'mpi_update_person_events' table

Column	Type	Required	Index	Description
api_key_id	integer (8) ∗ FK	x	x	API Key used to initiate the event
completed_at	datetime			Timestamp of when update was completed, regardless of success or failure
created_at	datetime			Timestamp of when update was initiated
id	integer (8) PK	x
info	json			Additional information about the update
update_type	string ∗	x		Type or Result of update

'nod_date_updates' table

Tracks changes to an AMA appeal's receipt date (aka, NOD date)

Column	Type	Required	Index	Description
appeal_id	integer (8) ∗ FK	x	x	Appeal for which the NOD date is being edited
change_reason	string ∗	x		Reason for change: entry_error or new_info
created_at	datetime ∗	x		Default created_at/updated_at timestamps
id	integer (8) PK	x
new_date	date ∗	x		Date after update
old_date	date ∗	x		Date before update
updated_at	datetime ∗	x		Default created_at/updated_at timestamps
user_id	integer (8) ∗ FK	x	x	User that updated the NOD date

'non_availabilities' table

Column	Type	Required	Index
created_at	datetime ∗	x
date	date
id	integer (8) PK	x
object_identifier	string ∗	x
schedule_period_id	integer (8) ∗ FK	x	x
type	string ∗	x
updated_at	datetime ∗	x	x

'notifications' table

Column	Type	Required	Index	Description
appeals_id	string ∗	x	x	ID of the Appeal
appeals_type	string ∗	x	x	Type of Appeal
created_at	datetime			Timestamp of when Noticiation was Created
email_enabled	boolean ∗	x
email_notification_content	string			Full Email Text Content of Notification
email_notification_external_id	string		x	VA Notify Notification Id for the email notification send through their API
email_notification_status	string		x	Status of the Email Notification
event_date	date ∗	x		Date of Event
event_type	string ∗	x		Type of Event
id	integer (8) PK	x
notification_content	text			Full Text Content of Notification
notification_type	string ∗	x		Type of Notification that was created
notified_at	datetime			Time Notification was created
participant_id	string		x	ID of Participant
recipient_email	string			Participant's Email Address
recipient_phone_number	string			Participants Phone Number
sms_notification_content	string			Full SMS Text Content of Notification
sms_notification_external_id	string		x	VA Notify Notification Id for the sms notification send through their API
sms_notification_status	string		x	Status of SMS/Text Notification
updated_at	datetime			TImestamp of when Notification was Updated

'notification_events' table

Type of Event

Column	Type	Required	Description
email_template_id	uuid ∗	x	Staging Email Template UUID
event_type	string PK	x
sms_template_id	uuid ∗	x	Staging SMS Template UUID

'organizations' table

Column	Type	Required	Unique	Index	Description
accepts_priority_pushed_cases	boolean			x	Whether a JudgeTeam currently accepts distribution of automatically pushed priority cases
ama_only_push	boolean				whether a JudgeTeam should only get AMA appeals during the PushPriorityAppealsToJudgesJob
ama_only_request	boolean				whether a JudgeTeam should only get AMA appeals when requesting more cases
created_at	datetime
id	integer (8) PK	x
name	string ∗	x
participant_id	string U		x	x	Organizations BGS partipant id
role	string				Role users in organization must have, if present
status	string			x	Whether organization is active, inactive, or in some other Status.
status_updated_at	datetime				Track when organization status last changed.
type	string				Single table inheritance
updated_at	datetime			x
url	string ∗ U	x	x	x	Unique portion of the organization queue url

'organizations_users' table

Column	Type	Required	Index
admin	boolean
created_at	datetime
id	integer (8) PK	x
organization_id	integer FK		x
updated_at	datetime		x
user_id	integer FK		x

'people' table

Column	Type	Required	Index	Description
created_at	datetime ∗	x
date_of_birth	date			PII
email_address	string			PII. Person email address, cached from BGS
first_name	string			PII. Person first name, cached from BGS
id	integer (8) PK	x
last_name	string			PII. Person last name, cached from BGS
middle_name	string			PII. Person middle name, cached from BGS
name_suffix	string			PII. Person name suffix, cached from BGS
participant_id	string ∗	x	x
ssn	string		x	PII. Person Social Security Number, cached from BGS
updated_at	datetime ∗	x	x

'post_decision_motions' table

Stores the disposition and associated task of post-decisional motions handled by the Litigation Support Team: Motion for Reconsideration, Motion to Vacate, and Clear and Unmistakeable Error.

Column	Type	Required	Index	Description
appeal_id	integer (8) FK
created_at	datetime ∗	x
disposition	string ∗	x		Possible options are Grant, Deny, Withdraw, and Dismiss
id	integer (8) PK	x
task_id	integer (8) FK		x
updated_at	datetime ∗	x	x
vacate_type	string			Granted motion to vacate can be Straight Vacate, Vacate and Readjudication, or Vacate and De Novo.
vacated_decision_issue_ids	integer			When a motion to vacate is partially granted, this includes an array of the appeal's decision issue IDs that were chosen for vacatur in this post-decision motion. For full grant, this includes all prior decision issue IDs.

'priority_end_product_sync_queue' table

Queue of End Product Establishments that need to sync with VBMS

Column	Type	Required	Index	Description
batch_id	uuid FK		x	A unique UUID for the batch the record is executed with
created_at	datetime ∗	x		Date and Time the record was inserted into the queue
end_product_establishment_id	integer ∗ FK	x	x	ID of end_product_establishment record to be synced
error_messages	string			Array of Error Message(s) containing Batch ID and specific error if a failure occurs
id	integer (8) PK	x
last_batched_at	datetime		x	Date and Time the record was last batched
status	string ∗	x	x	A status to indicate what state the record is in such as PROCESSING and PROCESSED
updated_at	datetime ∗	x		Date and Time the record was last updated.

'ramp_closed_appeals' table

Keeps track of legacy appeals that are closed or partially closed in VACOLS due to being transitioned to a RAMP election. This data can be used to rollback the RAMP Election if needed.

Column	Type	Required	Index	Description
closed_on	datetime			The datetime that the legacy appeal was closed in VACOLS and opted into RAMP.
created_at	datetime
id	integer PK	x
nod_date	date			The date when the Veteran filed a Notice of Disagreement for the original claims decision in the legacy system.
partial_closure_issue_sequence_ids	string			If the entire legacy appeal could not be closed and moved to the RAMP Election, the VACOLS sequence IDs of issues on the legacy appeal which were closed are stored here, indicating that it was a partial closure.
ramp_election_id	integer FK			The ID of the RAMP election that closed the legacy appeal.
updated_at	datetime		x
vacols_id	string ∗	x		The VACOLS BFKEY of the legacy appeal that has been closed and opted into RAMP.

'ramp_elections' table

Intake data for RAMP elections.

Column	Type	Required	Index	Description
created_at	datetime
established_at	datetime			Timestamp for when the review successfully established, including any related actions such as establishing a claim in VBMS if applicable.
id	integer PK	x
notice_date	date			The date that the Veteran was notified of their option to opt their legacy appeals into RAMP.
option_selected	string ∗	x		Indicates whether the Veteran selected for their RAMP election to be processed as a higher level review (with or without a hearing), a supplemental claim, or a board appeal.
receipt_date	date ∗	x		The date that the RAMP form was received by central mail.
updated_at	datetime		x
veteran_file_number	string ∗	x	x	PII. The VBA corporate file number of the Veteran for this review. There can sometimes be more than one file number per Veteran.

'ramp_election_rollbacks' table

If a RAMP election needs to get rolled back, for example if the EP is canceled, it is tracked here. Also any VACOLS issues that were closed in the legacy system and opted into RAMP are re-opened in the legacy system.

Column	Type	Required	Index	Description
created_at	datetime ∗	x		Timestamp for when the rollback was created.
id	integer (8) PK	x
ramp_election_id	integer (8) FK		x	The ID of the RAMP Election being rolled back.
reason	string ∗	x		The reason for rolling back the RAMP Election. Rollbacks happen automatically for canceled RAMP Election End Products, but can also happen for other reason such as by request.
reopened_vacols_ids	string			The IDs of any legacy appeals which were reopened as a result of rolling back the RAMP Election, corresponding to the VACOLS BFKEY.
updated_at	datetime ∗	x	x	Timestamp for when the rollback was last updated.
user_id	integer (8) FK		x	The user who created the RAMP Election rollback, typically a system user.

'ramp_issues' table

Issues added to an end product as contentions for RAMP reviews. For RAMP elections, these are created in VBMS after the end product is established and updated in Caseflow when the end product is synced. For RAMP refilings, these are selected from the RAMP election's issues and added to the RAMP refiling end product that is established.

Column	Type	Required	Index	Description
contention_reference_id	string			The ID of the contention created in VBMS that corresponds to the RAMP issue.
created_at	datetime
description	string ∗	x		The description of the contention in VBMS.
id	integer PK	x
review_id	integer ∗ FK	x	x	The ID of the RAMP election or RAMP refiling for this issue.
review_type	string ∗	x	x	The type of RAMP review the issue is on, indicating whether this is a RAMP election issue or a RAMP refiling issue.
source_issue_id	integer FK			If a RAMP election issue added to a RAMP refiling, it is the source issue for the corresponding RAMP refiling issue.
updated_at	datetime		x

'ramp_refilings' table

Intake data for RAMP refilings, also known as RAMP selection.

Column	Type	Required	Index	Description
appeal_docket	string ∗	x		When the RAMP refiling option selected is appeal, they can select hearing, direct review or evidence submission as the appeal docket.
created_at	datetime
established_at	datetime			Timestamp for when the review successfully established, including any related actions such as establishing a claim in VBMS if applicable.
establishment_processed_at	datetime			Timestamp for when the end product establishments for the RAMP review finished processing.
establishment_submitted_at	datetime			Timestamp for when an intake for a review was submitted by the user.
has_ineligible_issue	boolean			Selected by the user during intake, indicates whether the Veteran listed ineligible issues on their refiling.
id	integer PK	x
option_selected	string ∗	x		Which lane the RAMP refiling is for, between appeal, higher level review, and supplemental claim.
receipt_date	date ∗	x		Receipt date of the RAMP form.
updated_at	datetime		x
veteran_file_number	string ∗	x	x	PII. The VBA corporate file number of the Veteran for this review. There can sometimes be more than one file number per Veteran.

'record_synced_by_jobs' table

Column	Type	Required	Index
created_at	datetime
error	string
id	integer (8) PK	x
processed_at	datetime
record_id	integer (8) FK		x
record_type	string		x
sync_job_name	string
updated_at	datetime		x

'remand_reasons' table

Column	Type	Required	Index
code	string
created_at	datetime ∗	x
decision_issue_id	integer FK		x
id	integer (8) PK	x
post_aoj	boolean
updated_at	datetime ∗	x	x

'request_decision_issues' table

Join table for the has and belongs to many to many relationship between request issues and decision issues.

Column	Type	Required	Index	Description
created_at	datetime ∗	x		Automatic timestamp when row was created.
decision_issue_id	integer FK		x	The ID of the decision issue.
deleted_at	datetime		x
id	integer (8) PK	x
request_issue_id	integer FK		x	The ID of the request issue.
updated_at	datetime ∗	x	x	Automatically populated when the record is updated.

'request_issues' table

Each Request Issue represents the Veteran's response to a Rating Issue. Request Issues come in three flavors: rating, nonrating, and unidentified. They are attached to a Decision Review and (for those that track contentions) an End Product Establishment. A Request Issue can contest a rating issue, a decision issue, or a nonrating issue without a decision issue.

Column	Type	Required	Index	Description
benefit_type	string ∗	x		The Line of Business the issue is connected with.
closed_at	datetime		x	Timestamp when the request issue was closed. The reason it was closed is in closed_status.
closed_status	string			Indicates whether the request issue is closed, for example if it was removed from a Decision Review, the associated End Product got canceled, the Decision Review was withdrawn.
contention_reference_id	integer		x	The ID of the contention created on the End Product for this request issue. This is populated after the contention is created in VBMS.
contention_removed_at	datetime			When a request issue is removed from a Decision Review during an edit, if it has a contention in VBMS that is also removed. This field indicates when the contention has successfully been removed in VBMS.
contention_updated_at	datetime			Timestamp indicating when a contention was successfully updated in VBMS.
contested_decision_issue_id	integer FK		x	The ID of the decision issue that this request issue contests. A Request issue will contest either a rating issue or a decision issue
contested_issue_description	string			Description of the contested rating or decision issue. Will be either a rating issue's decision text or a decision issue's description.
contested_rating_decision_reference_id	string		x	The BGS id for contested rating decisions. These may not have corresponding contested_rating_issue_reference_id values.
contested_rating_issue_diagnostic_code	string			If the contested issue is a rating issue, this is the rating issue's diagnostic code. Will be nil if this request issue contests a decision issue.
contested_rating_issue_profile_date	string			If the contested issue is a rating issue, this is the rating issue's profile date. Will be nil if this request issue contests a decision issue.
contested_rating_issue_reference_id	string		x	If the contested issue is a rating issue, this is the rating issue's reference id. Will be nil if this request issue contests a decision issue.
corrected_by_request_issue_id	integer FK			If this request issue has been corrected, the ID of the new correction request issue. This is needed for EP 930.
correction_type	string			EP 930 correction type. Allowed values: control, local_quality_error, national_quality_error where 'control' is a regular correction, 'local_quality_error' was found after the fact by a local quality review team, and 'national_quality_error' was similarly found by a national quality review team. This is needed for EP 930.
covid_timeliness_exempt	boolean			If a veteran requests a timeliness exemption that is related to COVID-19, this is captured when adding a Request Issue and available for reporting.
created_at	datetime			Automatic timestamp when row was created
decision_date	date			Either the rating issue's promulgation date, the decision issue's approx decision date or the decision date entered by the user (for nonrating and unidentified issues)
decision_review_id	integer (8) FK		x	ID of the decision review that this request issue belongs to
decision_review_type	string		x	Class name of the decision review that this request issue belongs to
decision_sync_attempted_at	datetime			Async job processing last attempted timestamp
decision_sync_canceled_at	datetime			Timestamp when job was abandoned
decision_sync_error	string			Async job processing last error message
decision_sync_last_submitted_at	datetime			Async job processing most recent start timestamp
decision_sync_processed_at	datetime			Async job processing completed timestamp
decision_sync_submitted_at	datetime			Async job processing start timestamp
edited_description	string			The edited description for the contested issue, optionally entered by the user.
end_product_establishment_id	integer FK		x	The ID of the End Product Establishment created for this request issue.
id	integer (8) PK	x
ineligible_due_to_id	integer (8) FK		x	If a request issue is ineligible due to another request issue, for example that issue is already being actively reviewed, then the ID of the other request issue is stored here.
ineligible_reason	string		x	The reason for a Request Issue being ineligible. If a Request Issue has an ineligible_reason, it is still captured, but it will not get a contention in VBMS or a decision.
is_predocket_needed	boolean			Indicates whether or not an issue has been selected to go to the pre-docket queue opposed to normal docketing.
is_unidentified	boolean			Indicates whether a Request Issue is unidentified, meaning it wasn't found in the list of contestable issues, and is not a new nonrating issue. Contentions for unidentified issues are created on a rating End Product if processed in VBMS but without the issue description, and someone is required to edit it in Caseflow before proceeding with the decision.
nonrating_issue_category	string			The category selected for nonrating request issues. These vary by business line.
nonrating_issue_description	string			The user entered description if the issue is a nonrating issue
notes	text			Notes added by the Claims Assistant when adding request issues. This may be used to capture handwritten notes on the form, or other comments the CA wants to capture.
ramp_claim_id	string			If a rating issue was created as a result of an issue intaken for a RAMP Review, it will be connected to the former RAMP issue by its End Product's claim ID.
rating_issue_associated_at	datetime			Timestamp when a contention and its contested rating issue are associated in VBMS.
split_issue_status	string			If a request issue is part of a split, on_hold status applies to the original request issues while active are request issues on splitted appeals
type	string			Determines whether the issue is a rating issue or a nonrating issue
unidentified_issue_text	string			User entered description if the request issue is neither a rating or a nonrating issue
untimely_exemption	boolean			If the contested issue's decision date was more than a year before the receipt date, it is considered untimely (unless it is a Supplemental Claim). However, an exemption to the timeliness can be requested. If so, it is indicated here.
untimely_exemption_notes	text			Notes related to the untimeliness exemption requested.
updated_at	datetime		x	Automatic timestamp whenever the record changes.
vacols_id	string			The vacols_id of the legacy appeal that had an issue found to match the request issue.
vacols_sequence_id	integer			The vacols_sequence_id, for the specific issue on the legacy appeal which the Claims Assistant determined to match the request issue on the Decision Review. A combination of the vacols_id (for the legacy appeal), and vacols_sequence_id (for which issue on the legacy appeal), is required to identify the issue being opted-in.
verified_unidentified_issue	boolean			A verified unidentified issue allows an issue whose rating data is missing to be intaken as a regular rating issue. In order to be marked as verified, a VSR needs to confirm that they were able to find the record of the decision for the issue.
veteran_participant_id	string		x	The veteran participant ID. This should be unique in upstream systems and used in the future to reconcile duplicates.

'request_issues_updates' table

Keeps track of edits to request issues on a decision review that happen after the initial intake, such as removing and adding issues. When the decision review is processed in VBMS, this also tracks whether adding or removing contentions in VBMS for the update has succeeded.

Column	Type	Required	Index	Description
after_request_issue_ids	integer ∗	x		An array of the active request issue IDs after a user has finished editing a decision review. Used with before_request_issue_ids to determine appropriate actions (such as which contentions need to be added).
attempted_at	datetime			Timestamp for when the request issue update processing was last attempted.
before_request_issue_ids	integer ∗	x		An array of the active request issue IDs previously on the decision review before this editing session. Used with after_request_issue_ids to determine appropriate actions (such as which contentions need to be removed).
canceled_at	datetime			Timestamp when job was abandoned
corrected_request_issue_ids	integer			An array of the request issue IDs that were corrected during this request issues update.
created_at	datetime			Timestamp when record was initially created
edited_request_issue_ids	integer			An array of the request issue IDs that were edited during this request issues update
error	string			The error message if the last attempt at processing the request issues update was not successful.
id	integer (8) PK	x
last_submitted_at	datetime			Timestamp for when the processing for the request issues update was last submitted. Used to determine how long to continue retrying the processing job. Can be reset to allow for additional retries.
processed_at	datetime			Timestamp for when the request issue update successfully completed processing.
review_id	integer (8) ∗ FK	x	x	The ID of the decision review edited.
review_type	string ∗	x	x	The type of the decision review edited.
submitted_at	datetime			Timestamp when the request issues update was originally submitted.
updated_at	datetime		x	Timestamp when record was last updated.
user_id	integer (8) ∗ FK	x	x	The ID of the user who edited the decision review.
withdrawn_request_issue_ids	integer			An array of the request issue IDs that were withdrawn during this request issues update.

'schedule_periods' table

Column	Type	Required	Index
created_at	datetime ∗	x
end_date	date ∗	x
file_name	string ∗	x
finalized	boolean
id	integer (8) PK	x
start_date	date ∗	x
type	string ∗	x
updated_at	datetime ∗	x	x
user_id	integer (8) ∗ FK	x	x

'sent_hearing_admin_email_events' table

Column	Type	Required	Description
created_at	datetime ∗	x
external_message_id	string		The ID returned by the GovDelivery API when we send an email.
id	integer (8) PK	x
sent_hearing_email_event_id	integer (8) FK		Associated sent hearing email event.
updated_at	datetime ∗	x

'sent_hearing_email_events' table

Events related to hearings notification emails

Column	Type	Required	Index	Description
email_address	string			Address the email was sent to
email_recipient_id	integer (8) FK			Associated HearingEmailRecipient
email_type	string			The type of email sent: cancellation, confirmation, updated_time_confirmation
external_message_id	string			The ID returned by the GovDelivery API when we send an email
hearing_id	integer (8) ∗ FK	x	x	Associated hearing
hearing_type	string ∗	x	x	'Hearing' or 'LegacyHearing'
id	integer (8) PK	x
recipient_role	string			The role of the recipient: veteran, representative, judge
send_successful	boolean			This column keeps track of whether the email was sent or not
send_successful_checked_at	datetime			The date the status was last checked/updated in the GovDelivery API
sent_at	datetime ∗	x		The date and time the email was sent
sent_by_id	integer (8) ∗ FK	x	x	User who initiated sending the email

'special_issue_lists' table

Associates special issues to an AMA or legacy appeal for Caseflow Queue. Caseflow Dispatch uses special issues stored in legacy_appeals. They are intentionally disconnected.

Column	Type	Required	Index	Description
appeal_id	integer (8) FK		x	The ID of the appeal associated with this record
appeal_type	string		x	The type of appeal associated with this record
blue_water	boolean			Blue Water
burn_pit	boolean			Burn Pit
contaminated_water_at_camp_lejeune	boolean
created_at	datetime
dic_death_or_accrued_benefits_united_states	boolean
education_gi_bill_dependents_educational_assistance_scholars	boolean
foreign_claim_compensation_claims_dual_claims_appeals	boolean
foreign_pension_dic_all_other_foreign_countries	boolean
foreign_pension_dic_mexico_central_and_south_america_caribb	boolean
hearing_including_travel_board_video_conference	boolean
home_loan_guaranty	boolean
id	integer (8) PK	x
incarcerated_veterans	boolean
insurance	boolean
manlincon_compliance	boolean
military_sexual_trauma	boolean			Military Sexual Trauma (MST)
mustard_gas	boolean
national_cemetery_administration	boolean
no_special_issues	boolean			Affirmative no special issues, added belatedly
nonrating_issue	boolean
pension_united_states	boolean
private_attorney_or_agent	boolean
radiation	boolean
rice_compliance	boolean
spina_bifida	boolean
updated_at	datetime		x
us_court_of_appeals_for_veterans_claims	boolean			US Court of Appeals for Veterans Claims (CAVC)
us_territory_claim_american_samoa_guam_northern_mariana_isla	boolean
us_territory_claim_philippines	boolean
us_territory_claim_puerto_rico_and_virgin_islands	boolean
vamc	boolean
vocational_rehab	boolean
waiver_of_overpayment	boolean

'split_correlation_tables' table

Associates the request issues of the split appeal to the original appeal.

Column	Type	Required	Description
appeal_id	integer		The new ID of the split appeal associated with this record.
appeal_type	string ∗	x	The type of appeal that the split appeal was orginally motioned as (i.e. DR,ES,SC).
appeal_uuid	uuid ∗	x	The universally unique identifier for the appeal, which allows a single ID to determine an appeal for Caseflow splitted appeals.
created_at	datetime ∗	x	The datetime when the split appeal was created
created_by_id	integer ∗	x	The user css_id that created the split appeal.
id	integer (8) PK	x
original_appeal_id	integer ∗	x	The original appeal id from where the split appeal appeal_id was created from.
original_appeal_uuid	string ∗	x	The original source appeal uuid from where the split was generated from.
original_request_issue_id	integer ∗	x	The original request issue id and the corresponding request issue id created from the split appeal process.
relationship_type	string		The new split_appeal relationship type created from the split and maybe used for future correlations.
split_other_reason	string		The other reason for splitting the appeal from comment section.
split_reason	string		Reason for splitting the appeal from drop menu.
split_request_issue_id	integer ∗	x	The original request issue id and the corresponding request issue id created from the split appeal process.
updated_at	datetime		The datetime when the split appeal was updated at.
updated_by_id	integer		The user css_id who most recently updated the split appeal workflow.
working_split_status	string ∗	x	The work flow status of the split appeal (i.e. on_hold, in_progress, cancelled, completed).

'supplemental_claims' table

Intake data for Supplemental Claims.

Column	Type	Required	Index	Description
benefit_type	string ∗	x		The benefit type selected by the Veteran on their form, also known as a Line of Business.
created_at	datetime
decision_review_remanded_id	integer (8) FK		x	If an Appeal or Higher Level Review decision is remanded, including Duty to Assist errors, it automatically generates a new Supplemental Claim. If this Supplemental Claim was generated, then the ID of the original Decision Review with the remanded decision is stored here.
decision_review_remanded_type	string		x	The type of the Decision Review remanded if applicable, used with decision_review_remanded_id to as a composite key to identify the remanded Decision Review.
establishment_attempted_at	datetime			Timestamp for the most recent attempt at establishing a claim.
establishment_canceled_at	datetime			Timestamp when job was abandoned
establishment_error	string			The error captured for the most recent attempt at establishing a claim if it failed. This is removed once establishing the claim succeeds.
establishment_last_submitted_at	datetime			Timestamp for the latest attempt at establishing the End Products for the Decision Review.
establishment_processed_at	datetime			Timestamp for when the End Product Establishments for the Decision Review successfully finished processing.
establishment_submitted_at	datetime			Timestamp for when the Supplemental Claim was submitted by a Claims Assistant. This adds the End Product Establishment to a job to finish processing asynchronously.
filed_by_va_gov	boolean			Indicates whether or not this form came from VA.gov
id	integer (8) PK	x
legacy_opt_in_approved	boolean			Indicates whether a Veteran opted to withdraw their Supplemental Claim request issues from the legacy system if a matching issue is found. If there is a matching legacy issue and it is not withdrawn, then that issue is ineligible to be a new request issue and a contention will not be created for it.
receipt_date	date ∗	x		The date that the Supplemental Claim form was received by central mail. Only issues decided prior to the receipt date will show up as contestable issues. It is also the claim date for any associated end products that are established. Supplemental Claims do not have the same timeliness restriction on contestable issues as Appeals and Higher Level Reviews.
updated_at	datetime		x
uuid	uuid ∗	x	x	The universally unique identifier for the Supplemental Claim. Can be used to link to the claim after it is completed.
veteran_file_number	string ∗	x	x	PII. The file number of the Veteran that the Supplemental Claim is for.
veteran_is_not_claimant	boolean			Indicates whether the Veteran is the claimant on the Supplemental Claim form, or if the claimant is someone else like a spouse or a child. Must be TRUE if the Veteran is deceased.

'system_admin_events' table

Column	Type	Required	Index	Description
completed_at	datetime			Timestamp of when event was completed without error
created_at	datetime			Timestamp of when event was initiated
errored_at	datetime			Timestamp of when event failed due to error
event_type	string ∗	x		Type of event
id	integer (8) PK	x
info	json			Additional information about the event
updated_at	datetime			Timestamp of when event was last updated
user_id	integer (8) ∗ FK	x	x	User who initiated the event

'tags' table

Column	Type	Required	Index
created_at	datetime ∗	x
id	integer PK	x
text	string ∗	x	x
updated_at	datetime ∗	x	x

'tasks' table

Column	Type	Required	Index	Description
appeal_id	integer ∗ FK	x	x
appeal_type	string ∗	x	x
assigned_at	datetime
assigned_by_id	integer FK
assigned_to_id	integer ∗ FK	x	x
assigned_to_type	string ∗	x	x
cancellation_reason	string		x	Reason for latest cancellation status
cancelled_by_id	integer FK			ID of user that cancelled the task. Backfilled from versions table. Can be nil if task was cancelled before this column was added or if there is no user logged in when the task is cancelled
closed_at	datetime
completed_by_id	integer FK			ID of user that marked task complete
created_at	datetime ∗	x
id	integer (8) PK	x
instructions	text
parent_id	integer FK		x
placed_on_hold_at	datetime
started_at	datetime
status	string ∗	x	x
type	string ∗	x	x
updated_at	datetime ∗	x	x

'task_timers' table

A task timer allows an associated task's (like EvidenceSubmissionWindowTask and TimedHoldTask) `when_timer_ends` method to be run asynchronously after timer expires.

Column	Type	Required	Index	Description
attempted_at	datetime			Async timestamp for most recent attempt to run Task#when_timer_ends.
canceled_at	datetime			Timestamp when job was abandoned. Associated task is typically cancelled.
created_at	datetime ∗	x		Automatic timestamp for record creation.
error	string			Async any error message from most recent failed attempt to run.
id	integer (8) PK	x
last_submitted_at	datetime			Async timestamp for most recent job start. Initially set to when timer should expire (Task#timer_ends_at).
processed_at	datetime			Async timestamp for when the job completes successfully. Associated task's method Task#when_timer_ends ran successfully.
submitted_at	datetime			Async timestamp for initial job start.
task_id	integer (8) ∗ FK	x	x	ID of the associated Task to be run.
updated_at	datetime ∗	x	x	Automatic timestmap for record update.

'team_quotas' table

Column	Type	Required	Index
created_at	datetime ∗	x
date	date ∗	x	x
id	integer PK	x
task_type	string ∗	x	x
updated_at	datetime ∗	x	x
user_count	integer

'transcriptions' table

Column	Type	Required	Index	Description
created_at	datetime			Automatic timestamp of when transcription was created
expected_return_date	date			Expected date when transcription would be returned by the transcriber
hearing_id	integer (8) FK		x	Hearing ID; use as FK to hearings
id	integer (8) PK	x
problem_notice_sent_date	date			Date when notice of problem with recording was sent to appellant
problem_type	string			Any problem with hearing recording; could be one of: 'No audio', 'Poor Audio Quality', 'Incomplete Hearing' or 'Other (see notes)'
requested_remedy	string			Any remedy requested by the apellant for the recording problem; could be one of: 'Proceed without transcript', 'Proceed with partial transcript' or 'New hearing'
sent_to_transcriber_date	date			Date when the recording was sent to transcriber
task_number	string			Number associated with transcription
transcriber	string			Contractor who will transcribe the recording; i.e, 'Genesis Government Solutions, Inc.', 'Jamison Professional Services', etc
updated_at	datetime		x	Automatic timestamp of when transcription was updated
uploaded_to_vbms_date	date			Date when the hearing transcription was uploaded to VBMS

'unrecognized_appellants' table

Unrecognized non-veteran appellants

Column	Type	Required	Index	Description
claimant_id	integer (8) ∗ FK	x	x	The OtherClaimant or HealthcareProviderClaimant record associating this appellant to a DecisionReview.
created_at	datetime ∗	x
created_by_id	integer (8) ∗ FK	x		The user that created this version of the unrecognized appellant
current_version_id	integer (8) FK			The current version for this unrecognized appellant
id	integer (8) PK	x
poa_participant_id	string			Identifier of the appellant's POA, if they have a CorpDB participant_id
relationship	string ∗	x		Relationship to veteran. Allowed values: attorney, child, spouse, other, or healthcare_provider.
unrecognized_party_detail_id	integer (8) FK		x	Contact details
unrecognized_power_of_attorney_id	integer (8) FK		x	Appellant's POA, if they aren't in CorpDB.
updated_at	datetime ∗	x

'unrecognized_party_details' table

For an appellant or POA, name and contact details for an unrecognized person or organization

Column	Type	Required	Description
address_line_1	string ∗	x	PII
address_line_2	string		PII
address_line_3	string		PII
city	string ∗	x
country	string ∗	x
created_at	datetime ∗	x
date_of_birth	date		PII
ein	string		PII. Employer Identification Number
email_address	string		PII
id	integer (8) PK	x
last_name	string		PII
middle_name	string		PII
name	string ∗	x	PII. Name of organization, or first name or mononym of person
party_type	string ∗	x	The type of this party. Allowed values: individual, organization
phone_number	string		PII
ssn	string		PII. Social Security Number
state	string ∗	x
suffix	string		PII
updated_at	datetime ∗	x
zip	string ∗	x

'users' table

Authenticated Caseflow users

Column	Type	Required	Index	Description
created_at	datetime
css_id	string ∗	x	x
efolder_documents_fetched_at	datetime			Date when efolder documents were cached in s3 for this user
email	string
full_name	string
id	integer PK	x
last_login_at	datetime			The last time the user-agent (browser) provided session credentials; see User.from_session for precision
roles	string
selected_regional_office	string
station_id	string ∗	x
status	string		x	Whether or not the user is an active user of caseflow
status_updated_at	datetime			When the user's status was last updated
updated_at	datetime		x

'user_quotas' table

Column	Type	Required	Index
created_at	datetime ∗	x
id	integer PK	x
locked_task_count	integer
team_quota_id	integer ∗ FK	x	x
updated_at	datetime ∗	x	x
user_id	integer ∗ FK	x	x

'vbms_communication_packages' table

Column	Type	Required	Index	Description
comm_package_name	string ∗	x
copies	integer (8) ∗	x
created_at	datetime ∗	x
created_by_id	integer (8)		x
document_mailable_via_pacman_id	integer (8) FK		x
document_mailable_via_pacman_type	string		x
file_number	string ∗	x		number associated with the documents.
id	integer (8) PK	x
status	string
updated_at	datetime ∗	x
updated_by_id	integer (8)		x
uuid	string			UUID of the communication package in Package Manager (Pacman)

'vbms_distributions' table

Column	Type	Required	Index	Description
claimant_station_of_jurisdiction	string ∗	x		Can't be null if [recipient_type] is ro-colocated.
created_at	datetime ∗	x
created_by_id	integer (8)		x
first_name	string ∗	x		recipient's first name. If Type is [person] then it cant be null.
id	integer (8) PK	x
last_name	string ∗	x		recipient's last name. If Type is [person] then it cant be null.
middle_name	string			recipient's middle name.
name	string ∗	x		should only be used for non-person entity names. Not null if [recipient_type] is organization, ro-colocated, or System.
participant_id	string			recipient's participant id.
poa_code	string ∗	x		Can't be null if [recipient_type] is ro-colocated. The recipients POA code
recipient_type	string ∗	x		Must be one of [person, organization, ro-colocated, System].
updated_at	datetime ∗	x
updated_by_id	integer (8)		x
uuid	string			UUID of the distrubtion in Package Manager (Pacman)
vbms_communication_package_id	integer (8) FK		x

'vbms_distribution_destinations' table

Column	Type	Required	Index	Description
address_line_1	string ∗	x		PII. If destination_type is domestic, international, or military then Must not be null.
address_line_2	string ∗	x		PII. If treatLine2AsAddressee is [true] then must not be null
address_line_3	string ∗	x		PII. If treatLine3AsAddressee is [true] then must not be null
address_line_4	string			PII.
address_line_5	string			PII.
address_line_6	string			PII.
city	string ∗	x		PII. If type is [domestic, international, military] then Must not be null
country_code	string ∗	x		Must be exactly two-letter ISO 3166 code.
country_name	string ∗	x
created_at	datetime ∗	x
created_by_id	integer (8)		x
destination_type	string ∗	x		Must be 'domesticAddress', 'internationalAddress', 'militaryAddress', 'derived', 'email', or 'sms'. Cannot be 'physicalAddress'.
id	integer (8) PK	x
postal_code	string ∗	x
state	string ∗	x		PII. Must be exactly two-letter ISO 3166-2 code. If destination_type is domestic or military then Must not be null
treat_line_2_as_addressee	boolean
treat_line_3_as_addressee	boolean			If true, treatLine2AsAddressee must also be true
updated_at	datetime ∗	x
updated_by_id	integer (8)		x
vbms_distribution_id	integer (8) FK		x

'vbms_uploaded_documents' table

Column	Type	Required	Index	Description
appeal_id	integer (8) FK		x	Appeal/LegacyAppeal ID; use as FK to appeals/legacy_appeals
appeal_type	string		x	'Appeal' or 'LegacyAppeal'
attempted_at	datetime
canceled_at	datetime			Timestamp when job was abandoned
created_at	datetime ∗	x
document_name	string
document_series_reference_id	string			UUID that is provided by eFolder that represents the group of documentsthis document belongs to. Think of a series as a stack of versions.
document_subject	string
document_type	string ∗	x
document_version_reference_id	string			UUID that is provided by eFolder that represents the specific version of the document.
error	string
id	integer (8) PK	x
last_submitted_at	datetime
processed_at	datetime
submitted_at	datetime
updated_at	datetime ∗	x	x
uploaded_to_vbms_at	datetime
veteran_file_number	string		x

'veterans' table

Column	Type	Required	Index	Description
bgs_last_synced_at	datetime			The last time cached BGS attributes were synced
closest_regional_office	string
created_at	datetime
date_of_death	date			Date of Death reported by BGS, cached locally
date_of_death_reported_at	datetime			The datetime that date_of_death last changed for veteran.
file_number	string ∗	x	x	PII. Veteran's file_number
first_name	string ∗	x		PII. Veteran's first name
id	integer (8) PK	x
last_name	string ∗	x		PII. Veteran's last name
middle_name	string			PII. Veteran's middle name
name_suffix	string
participant_id	string		x
ssn	string		x	PII. The cached Social Security Number
updated_at	datetime		x

'virtual_hearings' table

Column	Type	Required	Index	Description
alias	string		x	Alias for conference in Pexip
alias_with_host	string			Alias for conference in pexip with client_host
appellant_email	string			Appellant's email address
appellant_email_sent	boolean ∗	x		Determines whether or not a notification email was sent to the appellant
appellant_reminder_sent_at	datetime			The datetime the last reminder email was sent to the appellant.
appellant_tz	string (50)			Stores appellant timezone
conference_deleted	boolean ∗	x		Whether or not the conference was deleted from Pexip
conference_id	integer		x	ID of conference from Pexip
created_at	datetime ∗	x		Automatic timestamp of when virtual hearing was created
created_by_id	integer (8) ∗ FK	x	x	User who created the virtual hearing
guest_hearing_link	string			Link used by appellants and/or representatives to join virtual hearing conference
guest_pin	integer			PIN number for guests of Pexip conference
guest_pin_long	string (11)			Change the guest pin to store a longer pin with the # sign trailing
hearing_id	integer (8) FK		x	Associated hearing
hearing_type	string		x	'Hearing' or 'LegacyHearing'
host_hearing_link	string			Link used by judges to join virtual hearing conference
host_pin	integer			PIN number for host of Pexip conference
host_pin_long	string (8)			Change the host pin to store a longer pin with the # sign trailing
id	integer (8) PK	x
judge_email	string			Judge's email address
judge_email_sent	boolean ∗	x		Whether or not a notification email was sent to the judge
representative_email	string			Veteran's representative's email address
representative_email_sent	boolean ∗	x		Whether or not a notification email was sent to the veteran's representative
representative_reminder_sent_at	datetime			The datetime the last reminder email was sent to the representative.
representative_tz	string (50)			Stores representative timezone
request_cancelled	boolean			Determines whether the user has cancelled the virtual hearing request
updated_at	datetime ∗	x	x	Automatic timestamp of when virtual hearing was updated
updated_by_id	integer (8) FK		x	The ID of the user who most recently updated the virtual hearing

'virtual_hearing_establishments' table

Column	Type	Required	Index	Description
attempted_at	datetime			Async timestamp for most recent attempt to run.
canceled_at	datetime			Timestamp when job was abandoned.
created_at	datetime ∗	x		Automatic timestamp when row was created.
error	string			Async any error message from most recent failed attempt to run.
id	integer (8) PK	x
last_submitted_at	datetime			Async timestamp for most recent job start.
processed_at	datetime			Timestamp for when the virtual hearing was successfully processed.
submitted_at	datetime			Async timestamp for initial job start.
updated_at	datetime ∗	x		Timestamp when record was last updated.
virtual_hearing_id	integer (8) ∗ FK	x	x	Virtual Hearing the conference is being established for.

'vso_configs' table

Column	Type	Required	Index
created_at	datetime ∗	x
id	integer (8) PK	x
ihp_dockets	string
organization_id	integer FK		x
updated_at	datetime ∗	x	x

'work_modes' table

Captures user's current work mode for appeals being worked

Column	Type	Required	Index	Description
appeal_id	integer ∗ FK	x	x	Appeal ID -- use as FK to AMA appeals and legacy appeals
appeal_type	string ∗	x	x	Whether appeal_id is for AMA or legacy appeals
created_at	datetime ∗	x		Standard created_at/updated_at timestamps
id	integer (8) PK	x
overtime	boolean			Whether the appeal is currently marked as being worked as overtime
updated_at	datetime ∗	x		Standard created_at/updated_at timestamps

'worksheet_issues' table

Column	Type	Required	Index
allow	boolean
appeal_id	integer FK		x
created_at	datetime
deleted_at	datetime		x
deny	boolean
description	string
dismiss	boolean
disposition	string
from_vacols	boolean
id	integer PK	x
notes	string
omo	boolean
remand	boolean
reopen	boolean
updated_at	datetime		x
vacols_sequence_id	string ∗	x