Skip to content

VRO Database

Jump to bottom
Yoom Lam edited this page Aug 3, 2023 · 64 revisions

VRO's database does not store any Personal Identifiable Information (PII) or Personal Health Information (PHI).

  • Use non-PII identifiers for the Veteran
  • Use non-PHI summary information for contentions

To view an Entity Relationship Diagram (ERD) of the tables and columns, try DBeaver.

Postgres DB

VRO currently uses Postgres (VRO plans to move to LHDI's AWS RDS soon). The postgres subproject builds the PostgresSQL Docker container to serve as the database, which is useful for local testing.

The Dockerfile in the postgres subproject uses the Docker Hub PostgresSQL image as a base image and runs the initialization script init_db.sh. This script creates a database user to run Flyway database migrations -- this user must be separate from the super user for security purposes.

Data is retained between container restarts through persistent Docker Volumes -- see the volumes configuration in docker-compose.yml for local development and in the Helm's chart for LHDI deployments (also see Helm-Charts#persistent-volumes).

Database Migrations

VRO uses Flyway to initialize and change the database. The subproject db-init contains all database migrations and is used to initialize and update the database schema.

  • All versioned migrations are SQL based and in the directory here. These migrations create all the schemas and tables.
  • An additional user with limited privileges is created. This user is used to access the database within all non-migration VRO functionality. A separate user can be created for each new domain.

The db-init subproject contains a Dockerfile that will run the migrations both in the local development and in LHDI deployments.

The strategy for creating migration files is simple:

  • Each work branch and subsequent Pull Request should be its own contained version number.
  • Create one migration file per proposed table change.
  • Smaller, incremental changes on a per-file basis allows for better maintainability, troubleshooting, and testability.

Database Tables and Fields

DB tables 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.

Similarly, the id UUID column is also included in BaseEntity.

All the tables reside in the claims schema. These are the tables in the database:

  • schema_history: stores DB migration version information and is used by Flyway for database migrations.
  • bie_contention_event: stores BIE Kafka events related to contentions

Accessing the DB via Java

VRO uses Spring Data JPA to access and manage data in the database from the Java code. The subproject shared/persistence-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 shared/persistence-model. These JPA Repositories inherit basic CRUD methods and also contains explicit definition of more complex methods needed by the VRO applications.

Clone this wiki locally