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

Autogenerated documentation

These are the most up-to-date documentation.

• Schema Diagrams
• Schema CSVs
  • Caseflow Schema CSV
  • ETL Schema CSV

Manual documentation

See Caseflow Data Model.

Participant IDs

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.

Semi-automated documentation

• 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.