ETL Schema CSV #

Column	Type	Required	Index	Description
active_appeal	boolean ∗	x	x	Calculated based on BVA status
aod_due_to_dob	boolean		x	Calculated every day based on Claimant DOB
aod_granted	boolean ∗	x	x	advance_on_docket_motions.granted
aod_reason	string (50)			advance_on_docket_motions.reason
aod_user_id	integer (8)		x	advance_on_docket_motions.user_id
appeal_created_at	datetime ∗	x	x	appeals.created_at
appeal_id	integer (8) ∗ FK	x	x	ID of the Appeal
appeal_updated_at	datetime ∗	x	x	appeals.updated_at
claimant_dob	date		x	people.date_of_birth
claimant_first_name	string			people.first_name
claimant_id	integer (8)		x	claimants.id
claimant_last_name	string			people.last_name
claimant_middle_name	string			people.middle_name
claimant_name_suffix	string			people.name_suffix
claimant_participant_id	string (50)		x	claimants.participant_id
claimant_payee_code	string (20)			claimants.payee_code
claimant_person_id	integer (8)		x	people.id
closest_regional_office	string (20)			The code for the regional office closest to the Veteran on the appeal.
created_at	datetime ∗	x	x	Creation timestamp for the ETL record
decision_status_sort_key	integer ∗	x	x	Integer for sorting status in display order
docket_number	string (50) ∗	x		Docket number
docket_range_date	date			Date that appeal was added to hearing docket range.
docket_type	string (50)		x	Docket type
established_at	datetime ∗	x		Timestamp for when the appeal was intaken successfully
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.
poa_participant_id	string (20)		x	Used to identify the power of attorney (POA)
receipt_date	date		x	Receipt date of the NOD form
status	string (32) ∗	x	x	Calculated BVA status based on Tasks
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	x	Updated timestamp for the ETL record
uuid	uuid ∗	x	x	The universally unique identifier for the appeal
veteran_dob	date			people.date_of_birth
veteran_file_number	string (20) ∗	x	x	Veteran file number
veteran_first_name	string			veterans.first_name
veteran_id	integer (8) ∗	x	x	veterans.id
veteran_is_not_claimant	boolean		x	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.
veteran_last_name	string			veterans.last_name
veteran_middle_name	string			veterans.middle_name
veteran_name_suffix	string			veterans.name_suffix
veteran_participant_id	string (20)		x	veterans.participant_id

'attorney_case_reviews' table

Denormalized attorney_case_reviews

Column	Type	Required	Index	Description
appeal_id	integer (8) ∗	x	x	tasks.appeal_id
appeal_type	string ∗	x	x	tasks.appeal_type
attorney_css_id	string (50) ∗	x		users.css_id
attorney_full_name	string (255) ∗	x		users.full_name
attorney_id	integer (8) ∗	x	x	attorney_case_reviews.attorney_id
attorney_sattyid	string (20)			users.sattyid
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
document_reference_id	string (50)			attorney_case_reviews.document_id
document_type	string (20)		x	attorney_case_reviews.document_type
id	integer (8) PK	x
note	text			attorney_case_reviews.note
overtime	boolean			attorney_case_reviews.overtime
review_created_at	datetime ∗	x	x	attorney_case_reviews.created_at
review_id	integer (8) ∗	x	x	attorney_case_reviews.id
review_updated_at	datetime ∗	x	x	attorney_case_reviews.updated_at
reviewing_judge_css_id	string (50) ∗	x		users.css_id
reviewing_judge_full_name	string (255) ∗	x		users.full_name
reviewing_judge_id	integer (8) ∗	x	x	attorney_case_reviews.reviewing_judge_id
reviewing_judge_sattyid	string (20)			users.sattyid
task_id	string ∗	x	x	attorney_case_reviews.task_id
untimely_evidence	boolean			attorney_case_reviews.untimely_evidence
updated_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
vacols_id	string		x	Substring attorney_case_reviews.task_id for Legacy Appeals
work_product	string (20)			attorney_case_reviews.work_product

'etl_builds' table

ETL build metadata for each job

Column	Type	Required	Index	Description
comments	string			Ad hoc comments (e.g. error message)
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
finished_at	datetime		x	Build end time
id	integer (8) PK	x
started_at	datetime		x	Build start time (usually identical to created_at)
status	string		x	Enum value: running, complete, error
updated_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record

'etl_build_tables' table

ETL table metadata, one for each table per-build

Column	Type	Required	Index	Description
comments	string			Ad hoc comments (e.g. error message)
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
etl_build_id	integer (8) ∗ FK	x	x	PK of the etl_build
finished_at	datetime		x	Build end time
id	integer (8) PK	x
rows_deleted	integer (8)			Number of rows deleted
rows_inserted	integer (8)			Number of new rows
rows_rejected	integer (8)			Number of rows skipped
rows_updated	integer (8)			Number of rows changed
started_at	datetime		x	Build start time (usually identical to created_at)
status	string		x	Enum value: running, complete, error
table_name	string		x	Name of the ETL table
updated_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record

'decision_documents' table

Column	Type	Required	Index	Description
appeal_id	integer (8) ∗	x	x	Associated appeal
appeal_type	string ∗	x	x
attempted_at	datetime			When the job ran
attorney_case_review_id	integer (8) ∗	x	x	References associated attorney_case_review record
attorney_user_id	integer (8)		x	Id of the attorney user on the associated judge_case_review
canceled_at	datetime			Timestamp when the job was abandoned
citation_number	string ∗	x	x	Unique identifier for decision document
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
decision_date	date ∗	x	x
decision_document_created_at	datetime		x	decision_documents.created_at
decision_document_updated_at	datetime		x	decision_documents.updated_at
docket_number	string			from appeals.stream_docket_number
error	string			Message captured from a failed attempt
id	integer (8) PK	x
judge_case_review_id	integer (8) ∗	x	x	References associated judge_case_review record
judge_user_id	integer (8)		x	Id of the judge user on the associated judge_case_review
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	Default created_at/updated_at for the ETL record
uploaded_to_vbms_at	datetime			When document was successfully uploaded to VBMS

'decision_issues' table

Copy of decision_issues

Column	Type	Required	Index	Description
benefit_type	string (20)		x	decision_issues.benefit_type
caseflow_decision_date	date			decision_issues.caseflow_decision_date
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
decision_review_id	integer (8)		x	decision_issues.decision_review_id
decision_review_type	string (20)		x	decision_issues.decision_review_type
decision_text	string			decision_issues.decision_text
description	string			decision_issues.description
diagnostic_code	string (20)			decision_issues.diagnostic_code
disposition	string (50)		x	decision_issues.disposition
end_product_last_action_date	date			decision_issues.end_product_last_action_date
id	integer (8) PK	x
issue_created_at	datetime		x	decision_issues.created_at
issue_deleted_at	datetime		x	decision_issues.deleted_at
issue_updated_at	datetime		x	decision_issues.updated_at
participant_id	integer (8) ∗	x	x	decision_issues.participant_id
percent_number	string			percent_number from RatingIssue (prcntNo from Rating Profile)
rating_issue_reference_id	integer (8)		x	decision_issues.rating_issue_reference_id
rating_profile_date	datetime			decision_issues.rating_profile_date
rating_promulgation_date	datetime			decision_issues.rating_promulgation_date
subject_text	text			subject_text from RatingIssue (subjctTxt from Rating Profile)
updated_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record

'decision_reviews' table

Decision 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.
closest_regional_office	string		x	The code for the regional office closest to the Veteran on the appeal.
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
decision_review_created_at	datetime		x
decision_review_id	integer (8) ∗	x	x	ID of the Decision Review -- may be used as FK to decision_issues
decision_review_remanded_id	integer (8)		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.
decision_review_type	string ∗	x	x	The type of the Decision Review -- may be used as FK to decision_issues
decision_review_updated_at	datetime		x
docket_range_date	date		x	Date that appeal was added to hearing docket range.
docket_type	string		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_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.
id	integer (8) PK	x
informal_conference	boolean		x	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		x	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.
poa_participant_id	string		x	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	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		x	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.
stream_docket_number	string		x	Multiple appeals with the same docket number indicate separate appeal streams, mimicking the structure of legacy appeals.
stream_type	string		x	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		x	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	x	Default created_at/updated_at for the ETL record
uuid	uuid ∗	x	x	The universally unique identifier for the Decision Review
veteran_file_number	string ∗	x	x	The file number of the Veteran that the Decision Review is for.
veteran_is_not_claimant	boolean		x	Indicates whether the Veteran is the claimant on the Decision Review form, or if the claimant is someone else like a spouse or a child. Must be TRUE if the Veteran is deceased.

'hearings' table

Denormalized hearings

Column	Type	Required	Index	Description
appeal_id	integer ∗	x	x	ID of the associated Appeal
bva_poc	string			hearings.bva_poc
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
created_by_id	integer (8)			The ID of the user who created the Hearing
created_by_user_css_id	string (50)			users.css_id
created_by_user_full_name	string (255)			users.full_name
created_by_user_sattyid	string (20)			users.sattyid
disposition	string			hearings.disposition
evidence_window_waived	boolean			hearings.evidence_window_waived
hearing_created_at	datetime		x	hearings.created_at
hearing_day_bva_poc	string			hearing_days.bva_poc
hearing_day_created_at	datetime		x	hearing_days.created_at
hearing_day_created_by_id	integer (8)			The ID of the user who created the Hearing Day
hearing_day_created_by_user_css_id	string (50)			users.css_id
hearing_day_created_by_user_full_name	string (255)			users.full_name
hearing_day_created_by_user_sattyid	string (20)			users.sattyid
hearing_day_deleted_at	datetime		x	hearing_days.deleted_at
hearing_day_id	integer		x	hearings.hearing_day_id
hearing_day_judge_id	integer			hearing_days.judge_id
hearing_day_lock	boolean			hearing_days.lock
hearing_day_notes	text			hearing_days.notes
hearing_day_regional_office	string			hearing_days.regional_office
hearing_day_request_type	string			hearing_days.request_type
hearing_day_room	string			The room at BVA where the hearing will take place
hearing_day_scheduled_for	date			hearing_days.scheduled_for
hearing_day_updated_at	datetime		x	hearing_days.updated_at
hearing_day_updated_by_id	integer (8)			The ID of the user who most recently updated the Hearing Day
hearing_day_updated_by_user_css_id	string (50)			users.css_id
hearing_day_updated_by_user_full_name	string (255)			users.full_name
hearing_day_updated_by_user_sattyid	string (20)			users.sattyid
hearing_id	integer (8) ∗	x	x	ID of the Hearing
hearing_location_address	string			hearing_locations.address
hearing_location_city	string			hearing_locations.city
hearing_location_classification	string			hearing_locations.classification
hearing_location_created_at	datetime		x	hearing_locations.created_at
hearing_location_distance	float			hearing_locations.distance
hearing_location_facility_id	string			hearing_locations.facility_id
hearing_location_facility_type	string			hearing_locations.facility_type
hearing_location_id	integer (8)		x	hearing_locations.id
hearing_location_name	string			hearing_locations.name
hearing_location_state	string			hearing_locations.state
hearing_location_updated_at	datetime		x	hearing_locations.updated_at
hearing_location_zip_code	string			hearing_locations.zip_code
hearing_request_type	string ∗	x	x	Calculated based on virtual_hearings and hearing_day.request_type
hearing_updated_at	datetime		x	hearings.updated_at
id	integer (8) PK	x
judge_css_id	string			users.css_id
judge_full_name	string			users.full_name
judge_id	integer		x	hearings.judge_id
judge_sattyid	string			users.sattyid
military_service	string			hearings.military_service
notes	string			hearings.notes
prepped	boolean			hearings.prepped
representative_name	string			hearings.representative_name
room	string			hearings.room
scheduled_time	time			hearings.scheduled_time
summary	text			hearings.summary
transcript_requested	boolean			hearings.transcript_requested
transcript_sent_date	date			hearings.transcript_sent_date
type	string		x	Hearing (AMA) or LegacyHearing
updated_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
updated_by_id	integer (8)			The ID of the user who most recently updated the Hearing
updated_by_user_css_id	string (50)			users.css_id
updated_by_user_full_name	string (255)			users.full_name
updated_by_user_sattyid	string (20)			users.sattyid
uuid	uuid		x	Unique identifier for the Hearing
vacols_id	string		x	When type=LegacyHearing, this column points at the VACOLS case id
witness	string			hearings.witness

'judge_case_reviews' table

Column	Type	Required	Index	Description
actual_task_id	string FK		x	Substring from judge_case_reviews.task_id referring to the tasks table for AMA Appeals
appeal_id	integer (8) ∗	x	x	tasks.appeal_id
appeal_type	string ∗	x	x	tasks.appeal_type
areas_for_improvement	text
attorney_css_id	string (20) ∗	x		users.css_id
attorney_full_name	string (255) ∗	x		users.full_name
attorney_id	integer (8) ∗	x	x	judge_case_reviews.attorney_id; references users table
attorney_sattyid	string (20)			users.sattyid
comment	text			from judge
complexity	string
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
factors_not_considered	text
id	integer (8) PK	x
judge_css_id	string (20) ∗	x		users.css_id
judge_full_name	string (255) ∗	x		users.full_name
judge_id	integer		x	judge_case_reviews.judge_id; references users table
judge_sattyid	string (20)			users.sattyid
location	string
one_touch_initiative	boolean
original_task_id	string		x	judge_case_reviews.task_id; Refers to the tasks table for AMA appeals, but uses syntax `<vacols_id>-YYYY-MM-DD` for legacy appeals
positive_feedback	text
quality	string
review_created_at	datetime ∗	x	x	judge_case_reviews.created_at
review_id	integer (8) ∗	x	x	judge_case_reviews.id
review_updated_at	datetime ∗	x	x	judge_case_reviews.updated_at
updated_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
vacols_id	string		x	Substring from judge_case_reviews.task_id for Legacy Appeals

'organizations' table

Copy of Organizations table

Column	Type	Required	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		x
id	integer (8) PK	x
name	string
participant_id	string			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		x	Unique portion of the organization queue url

'organizations_users' table

Copy of OrganizationUsers table

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

'people' table

Copy of People table

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

'remand_reasons' table

Copy of remand_reasons

Column	Type	Required	Index	Description
code	string (30)		x	remand_reasons.code
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
decision_issue_id	integer		x	remand_reasons.decision_issue_id
id	integer (8) PK	x
post_aoj	boolean		x	remand_reasons.post_aoj
remand_reason_created_at	datetime		x	remand_reasons.created_at
remand_reason_updated_at	datetime		x	remand_reasons.updated_at
updated_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record

'tasks' table

Denormalized Tasks with User/Organization

Column	Type	Required	Index	Description
appeal_id	integer (8) ∗ FK	x	x	tasks.appeal_id
appeal_type	string ∗	x	x	tasks.appeal_type
assigned_at	datetime			tasks.assigned_at
assigned_by_id	integer (8)			tasks.assigned_by_id
assigned_by_user_css_id	string (50)			users.css_id
assigned_by_user_full_name	string (255)			users.full_name
assigned_by_user_sattyid	string (20)			users.sattyid
assigned_to_id	integer (8) ∗	x	x	tasks.assigned_to_id
assigned_to_org_name	string (255)			organizations.name
assigned_to_org_type	string (50)			organizations.type
assigned_to_type	string ∗	x	x	tasks.assigned_to_type
assigned_to_user_css_id	string (50)			users.css_id
assigned_to_user_full_name	string (255)			users.full_name
assigned_to_user_sattyid	string (20)			users.sattyid
closed_at	datetime			tasks.closed_at
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
id	integer (8) PK	x
instructions	text			tasks.instructions
parent_id	integer (8) FK		x	tasks.parent_id
placed_on_hold_at	datetime			tasks.placed_on_hold_at
started_at	datetime			tasks.started_at
task_created_at	datetime			tasks.created_at
task_id	integer (8) ∗	x	x	tasks.id
task_status	string (20) ∗	x	x	tasks.status
task_type	string (50) ∗	x	x	tasks.type
task_updated_at	datetime			tasks.updated_at
updated_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record

'users' table

Combined Caseflow/VACOLS user lookups

Column	Type	Required	Index	Description
created_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
css_id	string (50) ∗	x		CSEM (Active Directory) username
email	string (255)			CSEM email
full_name	string (255)			CSEM full name
id	integer (8) PK	x
last_login_at	datetime
roles	string
sactive	string (1)
sattyid	string (20)
selected_regional_office	string (255)			CSEM regional office
slogid	string (20)
smemgrp	string (8)			VACOLS cached_user_attributes.smemgrp
stafkey	string (20)
station_id	string (20) ∗	x		CSEM station
status	string (20)		x	Whether or not the user is an active user of caseflow
status_updated_at	datetime			When the user's status was last updated
stitle	string (16)			VACOLS cached_user_attributes.stitle
svlj	string (1)
updated_at	datetime ∗	x	x	Default created_at/updated_at for the ETL record
user_id	integer ∗	x	x	ID of the User