Many resources are needed to download a project. Please understand that we have to compensate our server costs. Thank you in advance. Project price only 1 $
You can buy this project and download/modify it how often you want.
# HAPI 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.
# Background: 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.
# HFJ_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)
FHIR_ID
String
Contains the FHIR Resource id element. Either the PID, or the client-assigned id.
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.
# HFJ_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.
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.
## 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.
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).
# HFJ_RES_LINK: Search Links
When a resource is created or updated, it is indexed for searching. Any search parameters of type [Reference](http://hl7.org/fhir/search.html#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.
# Background: 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](http://hl7.org/fhir/search.html): Date, Number, Quantity, String, Token, and URI. Note that Reference search parameters are implemented using the [HFJ_RES_LINK](#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](https://en.wikipedia.org/wiki/MurmurHash) 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
## Common Search Index 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.
This is the name of the search parameter being indexed.
RES_TYPE
String
Nullable
This is the name of the resource being indexed.
HASH_IDENTITY
Long
A hash of SP_NAME and RES_TYPE. Used to narrow the table to a specific SearchParameter during sorting, and some queries.
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`.
# HFJ_SPIDX_DATE: Date Search Parameters
For any FHIR Search Parameter of type [*date*](https://www.hl7.org/fhir/search.html#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.
## Columns
Note: This table has the columns listed below, but it also has all common columns listed above in [Common Search Index Columns](#HFJ_SPIDX_common).
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".
# HFJ_SPIDX_NUMBER: Number Search Parameters
FHIR Search Parameters of type [*number*](https://www.hl7.org/fhir/search.html#number) produce rows in the `HFJ_SPIDX_NUMBER` table.
Range queries and sorting use the HASH_IDENTITY and SP_VALUE columns.
## Columns
Note: This table has the columns listed below, but it also has all common columns listed above in [Common Search Index Columns](#HFJ_SPIDX_common).
Name
Relationships
Datatype
Nullable
Description
SP_VALUE
Double
Not nullable
This is the value extracted by the SearchParameter expression.
# HFJ_SPIDX_QUANTITY: Quantity Search Parameters
FHIR Search Parameters of type [*quantity*](https://www.hl7.org/fhir/search.html#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.
## Columns
Note: This table has the columns listed below, but it also has all common columns listed above in [Common Search Index Columns](#HFJ_SPIDX_common).
Name
Relationships
Datatype
Nullable
Description
HASH_IDENTITY_AND_UNITS
Long
A hash like HASH_IDENTITY that also includes the SP_UNITS column.
HASH_IDENTITY_SYS_UNITS
Long
A hash like HASH_IDENTITY that also includes the SP_SYSTEM and SP_UNITS columns.
SP_SYSTEM
String
The system of the quantity units. e.g. "http://unitsofmeasure.org".
SP_UNITS
String
The units of the quantity. E.g. "mg".
SP_VALUE
Double
This is the value extracted by the SearchParameter expression.
# HFJ_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.
## Columns
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.
# HFJ_SPIDX_STRING: String Search Parameters
FHIR Search Parameters of type [*string*](https://www.hl7.org/fhir/search.html#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.
## Columns
Note: This table has the columns listed below, but it also has all common columns listed above in [Common Search Index Columns](#HFJ_SPIDX_common).
Name
Relationships
Datatype
Nullable
Description
HASH_EXACT
Long
A hash like HASH_IDENTITY that also includes the SP_VALUE_EXACT column.
SP_VALUE_NORMALIZED
String
An UPPERCASE string with accents removed.
SP_VALUE_EXACT
String
The extracted string unchanged.
# HFJ_SPIDX_TOKEN: Token Search Parameters
FHIR Search Parameters of type [*token*](https://www.hl7.org/fhir/search.html#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.
## Columns
Note: This table has the columns listed below, but it also has all common columns listed above in [Common Search Index Columns](#HFJ_SPIDX_common).
Name
Relationships
Datatype
Nullable
Description
HASH_VALUE
Long
A hash like HASH_IDENTITY that also includes the SP_VALUE column.
HASH_SYS_AND_VALUE
Long
A hash like HASH_IDENTITY that also includes the SP_SYSTEM and SP_VALUE columns.
HASH_SYS
Long
A hash like HASH_IDENTITY that also includes the SP_SYSTEM column.
SP_SYSTEM
String
The system of the code.
SP_VALUE
String
This is the bare code value.
# HFJ_SPIDX_URI: URI Search Parameters
FHIR Search Parameters of type [*uri*](https://www.hl7.org/fhir/search.html#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.
## Columns
Note: This table has the columns listed below, but it also has all common columns listed above in [Common Search Index Columns](#HFJ_SPIDX_common).
Name
Relationships
Datatype
Nullable
Description
HASH_URI
Long
A hash like HASH_IDENTITY that also includes the SP_URI column.
SP_URI
String
The uri string extracted by the SearchParameter.
# HFJ_IDX_CMB_TOK_NU: Combo Non-Unique Search Param
This table is used to index [Non-Unique Combo Search Parameters](https://smilecdr.com/docs/fhir_standard/fhir_search_custom_search_parameters.html#combo-search-index-parameters).
## Columns
Name
Relationships
Datatype
Nullable
Description
PID
Long
A unique persistent identifier for the given index row.
This column contains a FHIR search expression indicating what is being indexed. For example, if a
non-unique combo search parameter is present which indexes a combination of Observation#code and
Observation#status, this column might contain a value such as
Observation?code=http://loinc.org|1234-5&status=final
HASH_COMPLETE
Long
This column contains a hash of the value in column IDX_STRING.
# HFJ_IDX_CMP_STRING_UNIQ: Combo Unique Search Param
This table is used to index [Unique Combo Search Parameters](https://smilecdr.com/docs/fhir_standard/fhir_search_custom_search_parameters.html#combo-search-index-parameters).
## Columns
Name
Relationships
Datatype
Nullable
Description
PID
Long
A unique persistent identifier for the given index row.
This column contains a FHIR search expression indicating what is being indexed. For example, if a
unique combo search parameter is present which indexes a combination of Observation#code and
Observation#status, this column might contain a value such as
Observation?code=http://loinc.org|1234-5&status=final
HASH_COMPLETE
Long
This column contains a hash of the value in column IDX_STRING.
HASH_COMPLETE_2
Long
This column contains an additional hash of the value in column IDX_STRING, using a
static salt of the value prior to the hashing. This is done in order to increase the number
of bits used to hash the index string from 64 to 128.
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
When a resource is created or updated, it is indexed for searching. Any search parameters of type [Reference](http://hl7.org/fhir/search.html#reference) are resolved, and one or more rows may be created in the **HFJ_RES_LINK** table.
## Columns
## Common Search Index Columns
The following columns are common to **all HFJ_SPIDX_xxx tables**.