5.4.1HAPI 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.4.2Background: 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.4.3HFJ_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.

5.4.3.1Columns

5.4.4HFJ_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.

5.4.4.1Columns

5.4.4.2Encodings

5.4.4.3Columns

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

5.4.5.1Columns

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

5.4.6.1Search 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.

5.4.6.2Tables

5.4.6.3Common Search Index Columns

The following columns are common to all HFJ_SPIDX_xxx tables.

5.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. Range queries with Date parameters (e.g. Observation?date=ge2020-01-01) will query the HASH_IDENTITY, SP_VALUE_LOW_DATE_ORDINAL and/or SP_VALUE_HIGH_DATE_ORDINAL columns. Range queries with DateTime parameters (e.g. Observation?date=ge2021-01-01T10:30:00) will query the HASH_IDENTITY, SP_VALUE_LOW and/or SP_VALUE_HIGH columns. Sorting is done by the SP_VALUE_LOW column.

5.4.7.1Columns

Note: This table has the columns listed below, but it also has all common columns listed above in Common Search Index Columns.

5.4.8HFJ_SPIDX_NUMBER: Number Search Parameters

FHIR Search Parameters of type number produce rows in the HFJ_SPIDX_NUMBER table. Range queries and sorting use the HASH_IDENTITY and SP_VALUE columns.

5.4.8.1Columns

Note: This table has the columns listed below, but it also has all common columns listed above in Common Search Index Columns.

5.4.9HFJ_SPIDX_QUANTITY: Quantity Search Parameters

FHIR Search Parameters of type quantity produce rows in the HFJ_SPIDX_QUANTITY table. Range queries (e.g. Observation?valueQuantity=gt100) with no units provided will query the HASH_IDENTITY and SP_VALUE columns. Range queries (e.g. Observation?valueQuantity=gt100||mmHg) with a unit but not unit-sytem provided will use the HASH_IDENTITY_AND_UNITS and SP_VALUE columns. Range queries (e.g. Observation?valueQuantity=gt100|http://unitsofmeasure.org|mmHg) with a full system and unit will use the HASH_IDENTITY_SYS_UNITS and SP_VALUE columns. Sorting is done via the HASH_IDENTITY and SP_VALUE columns.

5.4.9.1Columns

Note: This table has the columns listed below, but it also has all common columns listed above in Common Search Index Columns.

5.4.10HFJ_SPIDX_QUANTITY_NRML: Normalized Quantity Search Parameters

Hapi Fhir supports searching by normalized units when enabled (see https://hapifhir.io/hapi-fhir/apidocs/hapi-fhir-jpaserver-model/ca/uhn/fhir/jpa/model/entity/StorageSettings.html#getNormalizedQuantitySearchLevel()). When this feature is enabled, each row stored in HFJ_SPIDX_QUANTITY to also store a row in HFJ_SPIDX_QUANTITY_NRML in canonical UCUM units. E.g. a weight recorded in an Observation as

"valueQuantity" : {
    "value" : 172,
    "unit" : "lb_av",
    "system" : "http://unitsofmeasure.org",
    "code" : "[lb_av]"
  },

would match the search Observation?valueQuantity=172, but would also match the search Observation?valueQuantity=78|http://unitsofmeasure.org|kg. The row in HFJ_SPIDX_QUANTITY would contain the value 172 pounds, while the HFJ_SPIDX_QUANTITY_NRML table would hold the equivalent 78 kg value. Only value searches that provide fully qualified units are eligible for normalized searches. Sorting only uses the HFJ_SPIDX_QUANTITY table.

5.4.10.1Columns

Same as HFJ_SPIDX_QUANTITY above, except the SP_VALUE, SP_SYSTEM, and SP_UNITS columns hold the converted value in canonical units instead of the value extracted by the SearchParameter. This table is only used for range queries with a unit which can be converted to canonical units.

5.4.11HFJ_SPIDX_STRING: String Search Parameters

FHIR Search Parameters of type string produce rows in the HFJ_SPIDX_STRING table. The default string search matches by prefix, ignoring case or accents. This uses the HASH_IDENTITY column and a LIKE prefix clause on the SP_VALUE_NORMALIZED columns. The :exact string search matches exactly. This uses only the HASH_EXACT column. Sorting is done via the HASH_IDENTITY and SP_VALUE_NORMALIZED columns.

5.4.11.1Columns

Note: This table has the columns listed below, but it also has all common columns listed above in Common Search Index Columns.

5.4.12HFJ_SPIDX_TOKEN: Token Search Parameters

FHIR Search Parameters of type token extract values of type Coding, code, and others. These produce rows in the HFJ_SPIDX_TOKEN table. The default token search accepts three parameter formats: matching the code (e.g. Observation?code=15074-8), matching both system and code (e.g. Observation?code=http://loinc.org|15074-8), or matching a system with any code (e.g. Observation?http://loinc.org|). All three are exact searches and use the hashes: HASH_VALUE, HASH_SYS_AND_VALUE, and HASH_SYS respectively. Sorting is done via the HASH_IDENTITY and SP_VALUE columns.

5.4.12.1Columns

Note: This table has the columns listed below, but it also has all common columns listed above in Common Search Index Columns.

5.4.13HFJ_SPIDX_URI: URI Search Parameters

FHIR Search Parameters of type uri produce rows in the HFJ_SPIDX_URI table. The default uri search matches the complete uri. This uses the HASH_URI column for an exact match. A uri search with the :above modifier will match any prefix. This also uses the HASH_URI column, but also tests hashes of every prefix of the query value. A uri search with the :below modifier will match any extension. This query uses the HASH_IDENTITY and a LIKE prefix match of the SP_URI column. Sorting is done via the HASH_IDENTITY and SP_URI columns.

5.4.13.1Columns

Note: This table has the columns listed below, but it also has all common columns listed above in Common Search Index Columns.

5.4.14HFJ_SPIDX_IDENTITY: Search Parameter Identities

The HFJ_SPIDX_IDENTITY table stores hash identities representing unique combinations of search parameter name and resource type of non-reference FHIR Search Datatypes: Date, Number, Quantity, String, Token, and URI.

While not currently used in search execution, this table provides metadata to interpret hash identities in the HFJ_SPIDX_xxx tables. When the server is configured to omit writing SP_NAME and RES_TYPE to the HFJ_SPIDX_xxx tables, the HFJ_SPIDX_IDENTITY table can be used to reconstruct the SP_NAME and RES_TYPE values for the stored hash identities. See Enabling Index Storage Optimization for more information.

5.4.14.1Columns

5.4.15HFJ_IDX_CMB_TOK_NU: Combo Non-Unique Search Param

This table is used to index Non-Unique Combo Search Parameters.

5.4.15.1Columns

5.4.16HFJ_IDX_CMP_STRING_UNIQ: Combo Unique Search Param

This table is used to index Unique Combo Search Parameters.

5.4.16.1Columns