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

Postgres DB

VRO currently uses Postgres (VRO plans to move to LHDI's AWS RDS soon). The postgres subproject builds the Docker container to serve as the database, which is useful for local testing. The postgres/Dockerfile uses the 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).

Connect to DB

To connect to the local Postgres DB:

  • Start the VRO Platform Base: ./gradlew :dockerComposeUp (see Docker Compose)
    • Or start only the postgres container: docker compose up -d postgres-service db-init
  • Get the connection URI: echo postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@localhost:5432/vro
  • Connect using psql (if needed, install psql)
    • psql postgres://$POSTGRES_USER:$POSTGRES_PASSWORD@localhost:5432/vro -c "\dt claims.*" 
                         List of relations
 Schema |         Name         | Type  |     Owner
--------+----------------------+-------+----------------
 claims | bie_contention_event | table | vro_admin_user
 claims | schema_history       | table | vro_admin_user
(2 rows)
    • To connect using a GUI, try DBeaver -- it provides an Entity Relationship Diagram (ERD) of the tables and columns.

Database Migrations

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

  • All versioned migrations are SQL based and in the migrations directory. 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/Dockerfile 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).

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