Skip to content

VRO Database

Jump to bottom
Alan Thompson edited this page Mar 12, 2023 · 64 revisions

Introduction (CURRENTY UNDER UPDATE-CONSTRUCTION)

VRO Database is primarily used for audit purposes. It does not store any Personal Identifiable Information (PII) or Personal Health Information (PHI). The following information is available

  • Claim ID (VBMS ID), disability action type, presumptive and RFD (Ready for Decision) flags
  • Claim Submission data associated with the attempt to process each Claim Submission Request through the workflow
    • any external vendor ID (Reference ID — used by external vendors to track the claim in their systems)
    • along with the associated type (representing the specific vendor associated with the Reference ID)
  • Non PII Ids for the Veteran who owns the claim
  • Contentions in the claim
  • Exam Order state as issued by VRO and updated from MAS
  • Non PHI summary information for the assessment for each of the contention
  • Non PHI summary information for each evidence PDF generated for the contention
  • Various event audit logs as a claim goes through VRO system
  • Database version information to support future changes through database migrations

All tables and fields are available in the Entity Relationship Diagram (ERD). This version of ERD is manually generated using DBeaver on 3/12/2023.

Note that the database is designed for multi issue claims although iMVP will not support such claims.

Technologies and Code Walkthrough

Database

VRO uses PostgreSQL. Since RDS is currently not available in LHDI, a PostgresSQL Docker based container serves as the database. The subproject postgres is used to build the container.

The Dockerfile in the postgres subproject is a simple wrapper around the Docker Hub PostgresSQL image. The primary reason to use the Dockerfile instead of using the Docker Hub image directly is to run the initialization script init_db.sh. This script creates the application database and a database user to run database migrations that are different from the default database postgres and the super user for security purposes.

The version of the database can be found in the subproject Dockerfile.

VRO achieves persistance through persistent Docker Volumes. The setup the volumes can be found in the application docker-compose file for local development and in the Helm's chart templates for deployment.

Database Migrations

VRO uses Flyway to initialize and change the database. The subproject db-init containts all database migration related artifacts. In particular all versioned migrations are SQL based and in the directory here. These migrations create all the schemas and tables. In addition an additional user with limited privileges is created. This user is used to access the database within all non migration VRO functionality.

The subproject db-init also contains a Dockerfile. The container based on this Dockerfile is used to run the migrations both in the local development docker-compose file and in application deployment.

The strategy for creating migration files is simple: Each work branch and subsequent Pull Request should be its own contained version number. Furthermore, you should create one migration file per proposed table change. Smaller, incremental changes on a per-file basis allows for better maintainability, troubleshooting, and testability.

Spring Data JPA

VRO uses Spring Data JPA to access and manage data in the database from the Java code. The subproject persistance/model contains the Object-relational mapping (ORM). In particular entity files that map to the database tables are in here.

To access and manage entity data , VRO uses JPA Repositories. All the JPA repositories are in here. These JPA Repositories inherit basic CRUD methods and also contains explicit definition of more complex methods needed by the VRO applications. In either case implementation of the methods are provided by Spring Data JPA automatically.

Service Interfaces and Implementations

VRO defines 3 service interfaces (Java Interfaces) in the service/spi subproject to populate and access the database. These interfaces are

  • Save to Db service: This service is used within the Camel routes to store information about claims, assessments, and evidence pdf generations.
  • Claim Metrics service: This service serves as the service later for the REST interface that exposes information in the database.
  • Audit Event service: This is also used within the Camel routes and logs various information about events that occur as claims are processed and external systems are called.

These services are implemented (Java Implementations of Interfaces) in the service/db subproject as Spring Boot services and are autowired in the rest of the projects. Implementations use JPA Repositories in the subproject persistance/model.

☝️ [From Alan: Why do we need the spi directory at all? Why can't we just put these into gov/va/vro/service/interfaces instead? Is implementing a full-on Service Provider Interface with an non-intuitive directory structure necessary for just 3 Java service interfaces???]

Usage within Camel Routes

The Save to Db service and Audit Event service are primarily used in Camel routes which are defined in the service/provider.

Usage within REST Interface

The Claim Metrics service is used in the implementation of claim-metrics and claim-info REST end points. These end points are defined in Claim Metrics resource and implemented in Claim Metrics controller.

Database Tables and Fields

General

The Entity Relationship Diagram (ERD) shows all the tables and fields used. All the tables reside in the claims schema. These are the tables in the database

  • claim: This table stores audit information about the incoming Claims themselves (as unique entities). The corresponding entity class is ClaimEntity.
  • claim_submission: This table stores audit information concerning each attempt to submit and process a Claim Submission through VRO. The corresponding entity class is ClaimSubmissionEntity.
  • veteran: This table stores audit information about the veterans in the claims. The corresponding entity class is VeteranEntity.
  • contention: This table stores audit information about the contentions in the claims. The corresponding entity class is ContentionEntity.
  • exam_order: This table stores audit information about the contentions in the Exam Order status. The corresponding entity class is ExamOrderEntity.
  • assessment_result: This table stores audit information about the assessmen results for the claims. The corresponding entity class is AssessmentResultEntity.
  • evidence_summary_document: This table stores audit information about the evidence documents created for the claims. The corresponding entity class is EvidenceSummaryDocumentEntity.
  • audit_event: This table stores log information as claim processing progresses through the Camel routes. The corresponding entity class is AuditEventEntity.
  • schema_history: This table stores migration version information and is used by Flyway database migrations. Database migration are described in Database Migrations

The tables claim, claim_submission, veteran, contention, exam_order, assessment_result and evidence_summary_document all have created_at and updated_at columns. These columns are inherited by the corresponding Entities from a BaseEntity. BaseEntity uses Spring Data JPA @CreatedAt and @LastModifiedDate annotations to implement the functionality. With these annotations Spring Data JPA automatically populates the fields without additional code in VRO.

For the same tables id column implementation is also shared in BaseEntity.

Details for Claim Table

The following are the column descriptions for the claim table.

  • vbms_id: This represents the VBMS system ID for the Claim. This is not intended as the source of truth and should be paired with created_at or submission_date

☝️ [From Alan: which one is better? see below note on submission_date as well...]

  • veteran_id: Foreign key to the veteran table and identifies the Veteran the claim belongs to.
  • disability_action_type:

☝️ [From Alan: find out what this means]

  • presumptive_flag: Represents the Claim's presumptive status for fast-tracking
  • rfd_flag: Represents a Ready For Decision (RFD) state

Details for Claim Submission Table

The following are the column descriptions for the claim_submission table.

  • claim_id: Foreign-key to the corresponding ID in the claim table.
  • reference_id: Represents an external vendor's internal system ID for tracking the Claim. Used in addition to id_type to identify the source of the claim.
  • id_type: Represents the external source, or vendor, of the Claim. Used in addition to reference_id to identify the source of the claim. This was the constant va.gov-Form526Submission for V1.

☝️ [From Alan: MAS is the other, yes?]

  • incoming_status: Status of the incoming claim. This was the constant submission for V1.
  • submission_source:

☝️ [From Alan: Identify this data... do we still need this, or is this deprecated?]

  • submission_date:

☝️ [From Alan: Identify this data... do we still need this, or is this deprecated?]

  • off_ramp_reason: Explanation for why the claim was off-ramped.
  • in_scope: Boolean flag representing the claim is in scope of being processed.

☝️ [From Alan: seek precision on this verbiage...]

Details for Veteran Table

The following are the column descriptions for the veteran table.

  • icn: The Internal Control Number (ICN) for the Veteran. It uniquely identifies the Veteran in VHA systems.
  • participant_id: The Participant Id for the Veteran. It uniquely identifies the Veteran in VBA systems and is actually the database ID in the CorpDb.
  • icn_timestamp: Since it is possible for ICNs to change, you can tell when the ICN was last updated with this timestamp. In theory, you could also use updated_at, but that column could possibly apply to other pieces of data here, so icn_timestamp provides a targeted "last known good time".

Details for Contention Table

The following are the column descriptions for the contention table.

  • claim_id: Foreign key that links the contention to the claim it is submitted with.
  • diagnostic_code: The diagnostic code for the contention. It links the contention to the VASRD codes.
  • condition_name: Name of the condition to be assessed
  • classification_code: ???

☝️ [From Alan: what is classification_code?]

Note that this design assumes multiple contentions per claim for future developments although iMVP will support only one contention (Hypertension) in single issue claims.

Details for Exam Order

The following are the column descriptions for the exam_order table.

  • claim_submission_id: Foreign key that links the Exam Order to the Claim Submission that issued it.
  • collection_id: ???
  • status: The current status of the Exam Order
    • ORDER_SUBMITTED: initial status from VRO, which creates a record when it issues a new Exam Order
    • VRO_NOTIFIED: ????????
    • DRAFT: status from IBM/MAS indicating the Exam Order is in Draft State
    • FINAL: status from IBM/MAS indicating the Exam Order has been completed
  • ordered_at: The time at which the Exam was Ordered

☝️ [From Alan: get clarification from IBM on VRO_NOTIFIED... we need to identify these statuses with them on expectations/meaning] ☝️ [From Alan: get clarification on ordered_at – do we update this only when we get updates from IBM/MAS?]

A Claim Submission can have multiple Exam Orders.

Details for Assessment Result Table

The following are the column descriptions for the assessment_result table.

  • contention_id: Foreign key that links assessment result to contention.
  • evidence_count_summary: Summary of evidence counts for the assessments. This is a JSON objects that summarizes assessment and is provided by the assessment microservice.
  • sufficient_evidence_flag: Represents that the Assessment Result has determined there is sufficient evidence to fast-track the claim.

☝️ [From Alan: seek precision on this verbiage...]

Details for Evidence Summary Document Table

The following are the column descriptions for the evidence_summary_document table.

  • contention_id: Foreign key that links evidence summary document to contention.
  • evidence_count: Summary of evidence counts for the document. This is a JSON objects that summarizes the information shown in the document.
  • document_name: Name of the document generated.
  • folder_id: Represents the UUID of the folderId returned by BIP on PDF upload, in order to facilitate easier tracking down of the file in eFolder.

Details for Audit Event Table

This is the table structure for audit events:

  • event_id: A unique id identifying the request
  • route_id: The id of the camel route from which the event is issued. Example: "mas-order-exam"
  • payload_type: The type of payload being processed. Example: "Automated Claim"
  • details: Other details pertinent to the event, but specific to the type of processing. For Example, collectionId, offRampReason, presumptiveFlag, et cetera.
  • message: A descriptive message explaining the action. Example: "Collecting evidence"
  • throwable: The stacktrace of an exception, if the even indicates an error.
  • event_time: Date and time the event was issued.

Simplified ER (detailed ERD can be found here: Entity Relationship Diagram (ERD)): image

Clone this wiki locally