Skip to content

VRO Database

Jump to bottom
Mason Watson edited this page Jan 19, 2024 · 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

The VRO platform only supports Postgres as a DB engine at this time.

Background Information

Local Development

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.

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.

Remote Environments

VRO uses Postgres on AWS RDS in all LHDI remote environments. The URLs for accessing RDS DB Instances are made available to applications as K8s secrets which are passed along as environment variables to applications through Helm.

A useful set of these environment variables can be referenced here. For any application seeking to use the DB, developers should ensure they include these definitions via symlink in their Helm chart deployment definition definition under a subdirectory named named_templates. Then in their deployment definitions they should set environment variables for the db client like in the xample-workflows deployment definition

Application developers may use their PostgresSQL client of choice in a supported language to connect to the database by querying these environment variables at application startup or runtime.

VRO Engineers

See the internal wiki for documentation on AWS entities created for the RDS integration.

These AWS entities are most easily managed by interacting with the LHDI kubernetes clusters (e.g. with the kubectl command or through the Lens GUI tool). They are specified using AWS controllers for Kubernetes.

In order to take administrative or maintenance actions on the Postgres instance within RDS (e.g. perform initialization steps to get a new Postgres instance ready for VRO platform, perform adhoc queries, add new DB users or schemas, etc.). You will need a shell in the K8s cluster which has access to a utility like psql. To achieve this, you can deploy a dev-tools pod using steps in Deploying VRO and then use Lens to get a shell into this pod in the namespace where you deployed it. This pod comes with psql utility pre-installed. Then, you can make a connection to the Postgres DB using the credentials stored in HashiCorp vault.

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.

NOTE: At this time, migrations are only applied in remote environments on deployment of the VRO Application.

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