6.4HAPI FHIR JPA Schema
This page is a work in progress. It is not yet comprehensive.
It contains a description of the tables within the HAPI FHIR JPA database. Note that columns are shown using Java datatypes as opposed to SQL datatypes, because the exact SQL datatype used will vary depending on the underlying database platform. The schema creation scripts can be used to determine the underlying column types.
6.4.1Background: Persistent IDs (PIDs)
The HAPI FHIR JPA schema relies heavily on the concept of internal persistent IDs on tables, using a Java type of Long (8-byte integer, which translates to an int8 or number(19) on various database platforms).
Many tables use an internal persistent ID as their primary key, allowing the flexibility for other more complex business identifiers to be changed and minimizing the amount of data consumed by foreign key relationships. These persistent ID columns are generally assigned using a dedicated database sequence on platforms which support sequences.
The persistent ID column is generally called PID in the database schema, although there are exceptions.
6.4.2HFJ_RESOURCE: Resource Master Table
The HFJ_RESOURCE table indicates a single resource of any type in the database. For example, the resource Patient/1 will have exactly one row in this table, representing all versions of the resource.
Columns
| Name | Relationships | Datatype | Nullable | Description |
|---|---|---|---|---|
| PARTITION_ID | Integer | Nullable | This is the optional partition ID, if the resource is in a partition. See Partitioning. | |
| PARTITION_DATE | Timestamp | Nullable | This is the optional partition date, if the resource is in a partition. See Partitioning. | |
| RES_VER | Long |
This is the current version ID of the resource. Will contain 1 when the resource is first
created, 2 the first time it is updated, etc.
This column is equivalent to the HFJ_RES_VER.RES_VER
column, although it does not have a foreign-key dependency in order to allow selective expunge of versions
when necessary. Not to be confused with RES_VERSION below.
|
||
| RES_VERSION | String | This column contains the FHIR version associated with this resource, using a constant drawn from FhirVersionEnum. Not to be confused with RES_VER above. | ||
| RES_TYPE | String |
Contains the resource type (e.g. Patient)
|
||
| HASH_SHA256 | Long | This column contains a SHA-256 hash of the current resource contents, exclusive of resource metadata. This is used in order to detect NO-OP writes to the resource. | ||
| RES_PUBLISHED | Timestamp | Contains the date that the first version of the resource was created. | ||
| RES_UPDATED | Timestamp | Contains the date that the most recent version of the resource was created. | ||
| RES_DELETED_AT | Timestamp | Nullable | If the most recent version of the resource is a delete, this contains the timestamp at which the resource was deleted. Otherwise, contains NULL. |
6.4.3HFJ_RES_VER: Resource Versions and Contents
The HFJ_RES_VER table contains individual versions of a resource. If the resource Patient/1 has 3 versions, there will be 3 rows in this table.
The complete raw contents of the resource is stored in either the RES_TEXT or the RES_TEXT_VC column, using the encoding specified in the RES_ENCODING column.
Columns
| Name | Relationships | Datatype | Nullable | Description |
|---|---|---|---|---|
| PARTITION_ID | Integer | Nullable | This is the optional partition ID, if the resource is in a partition. See Partitioning. | |
| PARTITION_DATE | Timestamp | Nullable | This is the optional partition date, if the resource is in a partition. See Partitioning. | |
| PID | PK | Long | This is the row persistent ID. | |
| RES_ID | FK to HFJ_RESOURCE | Long | This is the persistent ID of the resource being versioned. | |
| RES_VER | Long | Contains the specific version (starting with 1) of the resource that this row corresponds to. | ||
| RES_ENCODING | String | Describes the encoding of the resource being used to store this resource in RES_TEXT. See Encodings below for allowable values. | ||
| RES_TEXT | byte[] (SQL LOB) | Contains the actual full text of the resource being stored, stored in a binary LOB. | ||
| RES_TEXT_VC | String (SQL VARCHAR2) |
Contains the actual full text of the resource being stored, stored in a textual VARCHAR2 column. Only one of RES_TEXT and RES_TEXT_VC will be populated for any given row. The other column in either case will be null.
|
Encodings
| Value | Description |
|---|---|
| JSONC | The resource is serialized using FHIR JSON encoding, and then compressed into a byte stream using GZIP compression. |
6.4.4HFJ_FORCED_ID: Client Assigned/Visible Resource IDs
By default, the HFJ_RESOURCE.RES_ID column is used as the resource ID for all server-assigned IDs. For example, if a Patient resource is created in a completely empty database, it will be assigned the ID Patient/1 by the server and RES_ID will have a value of 1.
However, when client-assigned IDs are used, these may contain text values to allow a client to create an ID such as Patient/ABC. When a client-assigned ID is given to a resource, a row is created in the HFJ_RESOURCE table. When an HFJ_FORCED_ID row exists corresponding to the equivalent HFJ_RESOURCE row, the RES_ID value is no longer visible or usable by FHIR clients and it becomes purely an internal ID to the JPA server.
If the server has been configured with a Resource Server ID Strategy of UUID, or the server has been configured with a Resource Client ID Strategy of ANY the server will create a Forced ID for all resources (not only resources having textual IDs).
Columns
| Name | Relationships | Datatype | Nullable | Description |
|---|---|---|---|---|
| PARTITION_ID | Integer | Nullable | This is the optional partition ID, if the resource is in a partition. See Partitioning. | |
| PARTITION_DATE | Timestamp | Nullable | This is the optional partition date, if the resource is in a partition. See Partitioning. | |
| PID | PK | Long | This is the row persistent ID. | |
| RESOURCE_PID | FK to HFJ_RESOURCE | Long | This is the persistent ID of the resource being versioned. | |
| FORCED_ID | String | Contains the specific version (starting with 1) of the resource that this row corresponds to. | ||
| RESOURCE_TYPE | String | Contains the string specifying the type of the resource (Patient, Observation, etc). |
6.4.5HFJ_RES_LINK: Search Links
When a resource is created or updated, it is indexed for searching. Any search parameters of type Reference are resolved, and one or more rows may be created in the HFJ_RES_LINK table.
Columns
| Name | Relationships | Datatype | Nullable | Description |
|---|---|---|---|---|
| PARTITION_ID | Integer | Nullable | This is the optional partition ID, if the resource is in a partition. See Partitioning. Note that the partition indicated by the PARTITION_ID and PARTITION_DATE columns refers to the partition of the SOURCE resource, and not necessarily the TARGET. | |
| PARTITION_DATE | Timestamp | Nullable | This is the optional partition date, if the resource is in a partition. See Partitioning. Note that the partition indicated by the PARTITION_ID and PARTITION_DATE columns refers to the partition of the SOURCE resource, and not necessarily the TARGET. | |
| PID | Long | Holds the persistent ID | ||
| SRC_PATH | String | Contains the FHIRPath expression within the source resource containing the path to the target resource, as supplied by the SearchParameter resource that defined the link. | ||
| SRC_RESOURCE_ID | Long | Contains a FK reference to the resource containing the link to the target resource. | ||
| TARGET_RESOURCE_ID | Long | Nullable | Contains a FK reference to the resource that is the target resource. Will not be populated if the link contains a reference to an external resource, or a canonical reference. | |
| TARGET_RESOURCE_URL | String | Nullable | If this row contains a reference to an external resource or a canonical reference, this column will contain the absolute URL. | |
| SP_UPDATED | Timestamp | Contains the last updated timestamp for this row. |
6.4.6Background: Search Indexes
The HFJ_SPIDX (Search Parameter Index) tables are used to index resources for searching. When a resource is created or updated, a set of rows in these tables will be added. These are used for finding appropriate rows to return when performing FHIR searches. There are dedicated tables for supporting each of the non-reference FHIR Search Datatypes: Date, Number, Quantity, String, Token, and URI. Note that Reference search parameters are implemented using the HFJ_RES_LINK table above.
Search Hashes
The SPIDX tables leverage "hash columns", which contain a hash of multiple columns in order to reduce index size and improve search performance. Hashes currently use the MurmurHash3_x64_128 hash algorithm, keeping only the first 64 bits in order to produce a LongInt value.
For example, all search index tables have columns for storing the search parameter name (SP_NAME) and resource type (RES_TYPE). An additional column which hashes these two values is provided, called HASH_IDENTITY.
In some configurations, the partition ID is also factored into the hashes.
Tables
Columns
The following columns are common to all HFJ_SPIDX_xxx tables.
| Name | Relationships | Datatype | Nullable | Description |
|---|---|---|---|---|
| PARTITION_ID | Integer | Nullable | This is the optional partition ID, if the resource is in a partition. See Partitioning. Note that the partition indicated by the PARTITION_ID and PARTITION_DATE columns refers to the partition of the SOURCE resource, and not necessarily the TARGET. | |
| PARTITION_DATE | Timestamp | Nullable | This is the optional partition date, if the resource is in a partition. See Partitioning. Note that the partition indicated by the PARTITION_ID and PARTITION_DATE columns refers to the partition of the SOURCE resource, and not necessarily the TARGET. | |
| SP_ID | Long | Holds the persistent ID | ||
| RES_ID | FK to HFJ_RESOURCE | Long | Contains the PID of the resource being indexed. | |
| SP_NAME | String | This is the name of the search parameter being indexed. | ||
| RES_TYPE | String | This is the name of the resource being indexed. | ||
| SP_UPDATED | Timestamp | This is the time that this row was last updated. | ||
| SP_MISSING | boolean | If this row represents a search parameter that is **not** populated at all in the resource being indexed, this will be populated with the value `true`. Otherwise it will be populated with `false`. |
6.4.7HFJ_SPIDX_DATE: Date Search Parameters
For any FHIR Search Parameter of type date that generates a database index, a row in the HFJ_SPIDX_DATE table will be created.
Columns
The following columns are common to all HFJ_SPIDX_xxx tables.
| Name | Relationships | Datatype | Nullable | Description |
|---|---|---|---|---|
| SP_VALUE_LOW | Timestamp | Nullable |
This is the lower bound of the date in question.
|
|
| SP_VALUE_HIGH | Timestamp | Nullable |
This is the upper bound of the date in question.
|
|
| SP_VALUE_LOW_DATE_ORDINAL | Integer | Nullable |
This column contains the same Timestamp as SP_VALUE_LOW, but truncated to Date precision and formatted as an integer in the format "YYYYMMDD".
|
|
| SP_VALUE_HIGH_DATE_ORDINAL | Integer | Nullable |
This column contains the same Timestamp as SP_VALUE_HIGH, but truncated to Date precision and formatted as an integer in the format "YYYYMMDD".
|