This page is a resource to understand Caseflow's database.

See Caseflow Data Model and Dictionary.

A participant ID is provided by BGS to uniquely identify (1) Claimants (who may be Veterans) and (2) POA entities (which can be an organization or individual). Several tables refer to the participant ID by different names, depending on who they are referencing.

A participant ID for a POA is usually named poa_participant_id, for example in the DB tables: appeals, appellant_substitutions, bgs_power_of_attorneys, and unrecognized_appellants. Some exceptions are the bgs_attorneys and organizations tables, which simply use participant_id. Note: For the organizations table, only organizations with type="Representative" have a non-null participant_id.

A participant ID for a claimant or veteran goes by several names, such as claimant_participant_id, veteran_participant_id, and substitute_participant_id. The claimants, veterans, people, and decision_issues tables simply use participant_id.

• Ever want to see the schema for a subset of tables? Paste excerpts from schema.rb and "Import" into http://dbdiagram.io/, then move the boxes around to your liking. Example diagram of Document-related tables and more in ticket #15510.
• Extending previous work on parent-child task type relationships (such as this for the DR docket) and playing with Jupyter notebook, here's an interactive graph showing parent-child task type relationships for all task trees in prod as of July 19, 2020.
• Some diagrams and documentation of Caseflow's DB schema created in Oct 2019.

CSV files of the DB schema:

• Caseflow DB
• Caseflow's ETL DB

The following subclass and ERD diagrams reflect entities in the Caseflow DB. You can also get the svg version by clicking on the diagrams below. The svg version of these diagrams facilitates text search (by opening the file in a web browser).

To Caseflow Engineers: These files are generated by running make docs and are saved in your local docs/schema directory (see PR #16371). In that directory, the svg version of these diagrams facilitates text search, and the dot version allows you to manipulate the diagrams (for example using online Graphviz tools).

To help understand Caseflow's schema, let's start with DB tables that store records (a.k.a. DB rows) representing different ActiveRecord classes. The following diagram shows ActiveRecord classes (and subclasses) associated with certain DB tables.

• Classes associated with the same DB table are indicated with the same color -- this color-coding is used in subsequent diagrams.
• These DB tables typically have a type column that indicates which ActiveRecord class is being represented by the record.
• Blue-outlined rectangular nodes typically indicate abstract classes; while ellipses indicate concrete classes. The names of concrete classes are used to populate the type column.
• Blue dotted edges/arrows indicate a subclass relationship.

Notable observations:

• The Appeal class is a subclass of DecisionReview. HigherLevelReview and SupplementalClaim are subclasses of ClaimReview, which is a subclass of DecisionReview. This class hierarchy is mimicked by DecisionReviewIntake, ClaimReviewIntake, HigherLevelReviewIntake and SupplementalClaimIntake. However, all *Intake records are stored in the intakes table, whereas Appeal, HigherLevelReview, and SupplementalClaim records are stored in separate appeals, higher_level_reviews, and supplemental_claims tables respectively.
• There are many Tasks and some are grouped under an abstract class. For example, the ColocatedTask abstract class groups many *ColocatedTasks. Similarly, there are abstract classes JudgeTask, AttorneyTask, MailTask, and HearingAdminActionTask.
• Vso, FieldVso, and PrivateBar are types of Representatives, which is a subclass of Organization.

caseflow-subclasses.dot

This next diagram is an Entity-Relationship Diagram (ERD), showing how ActiveRecord classes are associated with each other via some *_id column (e.g., foreign keys). The column name can be inferred from the edge label between two nodes. For example, a Hearing record is associated with a HearingDay via the hearings.hearing_day_id column -- in the DB, there is a foreign key constraint from the hearings table to the hearing_days table.

• To reduce clutter, some abstract classes are consolidated into blue-outlined rectangular nodes containing a title. For example, the node with title appeal_type refers to the Appeal and LegacyAppeal classes. In Rails, this is a polymorphic association -- hence a foreign key constraint cannot be added to the DB.
• Many classes are associated with the User class. To reduce clutter, a User class is not drawn in the diagram. Instead, this is indicated with the text "(assoc with User)" for relevant classes to avoid drawing too many overlapping edges.
• Additional color-coding has been applied to categorize certain nodes:
  • light blue: classes containing the string "Hearings" and classes related to hearings
  • red: classes related to completion of a significant step (e.g., ClaimEstablishment, JudgeCaseReview, DecisionDocument, DecisionIssue, BoardGrantEffectuation)
• Light gray rectangular nodes with 2 rectangular notches indicate join tables (e.g., OrganizationsUser, RequestDecisionIssue), which associate records in 2 other tables.
• Purple edges/arrows indicate custom relationships manually added to the diagram.
• Solid blue edges/arrows indicate polymorphic relationships between tables.

Notable observations:

• DecisionReviewIntake (intakes table) has a polymorphic association to DecisionReview (abstract class) via the detail_id column. However, each type of intake class maps to a specific table.
  • AppealIntake class maps to the appeals table (a.k.a. Appeal class).
  • HigherLevelReviewIntake class maps to the higher_level_reviews table (a.k.a. HigherLevelReview class).
  • SupplementalClaimIntake class maps to the supplemental_claims table (a.k.a. SupplementalClaim class).
  • RampElectionIntake class maps to the ramp_elections table (a.k.a. RampElection class).
  • RampRefilingIntake class maps to the ramp_refilings table (a.k.a. RampRefiling class).
• AppealStreamSnapshot (hearing_appeal_stream_snapshots table) maps a LegacyHearing with a LegacyAppeal. This association is also available in the legacy_hearings table via appeal_id. Perhaps AppealStreamSnapshot should be deprecated.
• AppealStreamSnapshot, AppealSeries, LegacyHearing, ClaimEstablishment, Dispatch::Task, EstablishClaim, and WorksheetIssue only exists to support LegacyAppeals.
• CavcRemand, AppellantSubstitution, and DocketSwitch reference two different Appeals.
• Task can be assigned_to a User or Organization, as indicated by the assigned_to_type column.
• The nodes labeled decision_review_type, review_type, and decision_review_remanded_type are synonyms. The naming difference is an artifact of different column names for the associations.

caseflow-belongs_to_erd.dot

This next diagram combines the above two diagrams.

caseflow-belongs_to_erd-subclasses.dot