5.3HAPI 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.

5.3.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.

5.3.2HFJ_RESOURCE: Resource Master Table

  Resources

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.

5.3.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 the RES_TEXT 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[] Contains the actual full text of the resource being stored.

Encodings

Value Description
JSONC The resource is serialized using FHIR JSON encoding, and then compressed into a byte stream using GZIP compression.

5.3.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.

5.3.5HFJ_RES_LINK: Search Links

  Resources

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.

5.3.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

Search Indexes

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 String 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`.

5.3.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.
  • For a point in time date to millisecond precision (such as an Instant with a value of 2020-05-26T15:00:00.000) this represents the exact value.
  • For an instant value with lower precision, this represents the start of the possible range denoted by the value. For example, for a value of 2020-05-26 this represents 2020-05-26T00:00:00.000.
  • For a Period with a lower (start) value present, this column contains that value.
  • For a Period with no lower (start) value present, this column contains a timestamp representing the "start of time".
SP_VALUE_HIGH Timestamp Nullable This is the upper bound of the date in question.
  • For a point in time date to millisecond precision (such as an Instant with a value of 2020-05-26T15:00:00.000) this represents the exact value.
  • For an instant value with lower precision, this represents the start of the possible range denoted by the value. For example, for a value of 2020-05-26 this represents 2020-05-26T23:59:59.999.
  • For a Period with an upper (end) value present, this column contains that value.
  • For a Period with no upper (end) value present, this column contains a timestamp representing the "end of time".
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".