miracum / fhir-gateway Goto Github PK

Hi,

we have used the FHIR-GW extensively at our site and the included postgres DB has reached the id limit for the id column.
hence we had to alter the id column from serial to bigserial.

See https://www.postgresql.org/docs/current/datatype-numeric.html

alter sequence resources_id_seq as bigint;
alter table resources alter id type bigint;

These command in our case also resulted in some data loss, which is why we had to reload the data.
Hence I would suggest to add to Database Tuning part, to consider bigserial as for the id column, if more data than 2 billion FHIR resources is expected.
This would avoid having need to create DB Dump, altering the structure and restoring the data for the db.

Currently exported as fhirgateway_pipeline_duration_seconds_seconds_bucket should just be fhirgateway_pipeline_duration_seconds_bucket.

Should be a simple fix in: https://github.com/miracum/fhir-gateway/blob/master/src/main/java/org/miracum/etl/fhirgateway/processors/ResourcePipeline.java#L18

See also: https://github.com/miracum/fhir-gateway/blob/master/src/main/java/org/miracum/etl/fhirgateway/stores/PostgresFhirResourceRepository.java#L32 fhirgateway_postgres_operation_duration_seconds_seconds_bucket

After sending an observation to the gateway the resource is saved, but with differences in the codes.
This happens when I send an observation with two codings. Example:

    "code": {
        "coding": [
            {
                "system": "http://example.de/fhir/CodeSystem/icu/ObservationCode",
                "code": "g-THRO",
                "display": "Eb-THRO"
            },
            {
                "system": "http://loinc.org",
                "code": "777-3"
            }
        ]
    }

After sending the resource via HTTP PUT to the gateway URL, i can find the resource in the database, but with this code:

  "code": {
    "coding": [
      {
        "code": "777-3",
        "system": "http://example.de/fhir/CodeSystem/icu/ObservationCode",
        "display": "Platelets [#/volume] in Blood by Automated count"
      },
      {
        "code": "777-3",
        "system": "http://loinc.org"
      }
    ]
  }

I think this has something to do with the LOINC converter, but as I already have a LOINC code in this resource LOINC converter should not delete my observation code.

This SQL statement takes very very long to return the results:

SELECT 
r1.fhir_id AS "Fall.Versorgungsstellenkontakt.Aufnahmenummer", 
r2.cd_system ->> 'code' AS "Laborbefund.Laboruntersuchung.Code" 
FROM ( SELECT * FROM ( 
SELECT 
fhir_id, 
to_timestamp(jsonb_path_query(DATA, '$.period') ->> 'start', 'YYYY-MM-DDTHH:MI:SS') AS fhir_start_date, 
jsonb_array_elements_text(jsonb_path_query(DATA, '$.meta.profile')) AS fhir_profile 
FROM resources 
WHERE TYPE = 'Encounter') AS r_intermediate ) r1 
LEFT JOIN ( 
SELECT 
REPLACE(jsonbdata2 -> 'encounter' ->> 'reference', 'Encounter/', '') AS eid, 
cd_system FROM ( 
SELECT 
DATA AS jsonbdata2, 
jsonb_array_elements(jsonb_path_query(DATA, '$.code.coding')) AS cd_system, 
jsonb_array_elements(jsonb_path_query(DATA, '$.category.coding')) AS cd_category 
FROM resources 
WHERE TYPE = 'Observation') r3 
WHERE r3.cd_system ->> 'system' = 'http://loinc.org/' AND 
r3.cd_category ->> 'code' = '26436-6' 
) r2 ON 
r2.eid = r1.fhir_id 
WHERE r1.fhir_profile = 'https://fhir.miracum.org/core/StructureDefinition/Versorgungsfall';

It would be great, if the fhir-gateway could somehow be "enhanced" in a way, that this and similar SQL statements can be executed in a reasonable amount of time.

Some suggestions exist already, e.g.

• introducing ID-columns with indices that can be used for joining: #31
• setting indices direclty inside the JSON: #42

Given that in future the FHIR Server will enable oAuth Authentication mechanismn for access the FHIR Resources, the FHIR Gateway should be able to access FHIR Stores, which uses that mechanism.

There are Spring reference implementation from the MII Feasibility Process, which could be a good starting point.

Reference Implementation:
https://github.com/medizininformatik-initiative/mii-process-feasibility/pull/113/files

(Maybe related to #31 as it also addresses performance)

Would it make sense to define the partitioning of the database to split the resources by type as "default setup" when working with the FHIR-gateway? (as described here: https://github.com/miracum/fhir-gateway#partitioning).

replaceFirst() gibt den Input-Topic-Name ohne Änderung zurück, falls generateTopicMatchExpression nicht gefunden wird, wodurch die eingelesenen Messages wieder im Input Topic landen und erneut eingelesen werden -> Infinite Loop

Is there an environment variable or a path mapping that i can use to add a root Certificate of my CA into the FHIR Gateway? This is needed so the FHIR Gateway trusts the certificate of my downstream FHIR-Server.

For performance purposes / faster filtering etc. it would be good to have 3 additional columns available in the 'resources' table, namely:

• Patient-ID:
  • REPLACE(jsonb_path_query(DATA, '$.subject') ->> 'reference', 'Patient/', '') or
  • DATA ->> 'id' (for resource-type = 'Patient')
• Encounter-ID:
  • REPLACE(DATA -> 'encounter' ->> 'reference', 'Encounter/', '') [e.g. for resource-type = 'Condition']
  • DATA ->> 'id' (for resource-type = 'Encounter')
• Encounter-Start-Date
  • to_timestamp(jsonb_path_query(DATA, '$.period') ->> 'start', 'YYYY-MM-DDTHH:MI:SS') [e.g. for resource-type = 'Encounter']

For better performance, set indices on json-items to enhance user-experience and speed when joining:

Patient-ID, e.g.:

• Patient.DATA ->> 'id'
• REPLACE(jsonb_path_query(Encounter.DATA, '$.subject') ->> 'reference', 'Patient/', '')

Encounter-ID, e.g.:

• Encounter.DATA ->> 'id'
• REPLACE(Condition.DATA -> 'encounter' ->> 'reference', 'Encounter/', '')
• REPLACE(Procedure.DATA -> 'encounter' ->> 'reference', 'Encounter/', '')
• REPLACE(Observation.DATA -> 'encounter' ->> 'reference', 'Encounter/', '')

When running the FHIR-GW without FHIR-DB (SPRING_DATASOURCE_URL set empty, SERVICES_PSQL_ENABLED set to false) the FHIR-GW does not start properly.
Instead it continously restarts every X seconds.

The Error Message vom Docker-Log includes:

org.springframework.context.ApplicationContextException: Unable to start web server; nested exception is org.springframework.boot.web.server.WebServerException: Unable to start embedded Tomcat

Caused by: org.springframework.beans.factory.BeanCreationException: Error creating bean with name 'skipPattern' defined in class path resource [io/opentracing/contrib/spring/web/starter/SkipPatternAutoConfiguration.class]

Caused by: org.h2.jdbc.JdbcSQLNonTransientException: Unknown data type: "JSONB";

Is there anything else to do, when trying to run FHIR-GW without FHIR-DB?

This issue lists Renovate updates and detected dependencies. Read the Dependency Dashboard docs to learn more.

Awaiting Schedule

These updates are awaiting their schedule. Click on a checkbox to get an update now.

• chore(deps): update docker.io/bitnami/kafka:3.7.0 docker digest to 9abe5ce
• chore(deps): update docker.io/library/debian:12.5-slim docker digest to 67f3931
• chore(deps): update docker.io/library/gradle:8.7.0-jdk21 docker digest to afba668
• chore(deps): update docker.io/library/postgres:16.2 docker digest to 4aea012
• chore(deps): update gcr.io/distroless/java21-debian12:nonroot docker digest to 0ba7333
• chore(deps): update github-actions (actions/checkout, actions/download-artifact, amannn/action-semantic-pull-request, github/codeql-action, miracum/.github, ossf/scorecard-action)
• chore(deps): update all non-major dependencies (docker.io/curlimages/curl, docker.io/hapiproject/hapi, docker.io/library/gradle, docker.io/library/postgres, ghcr.io/miracum/fhir-pseudonymizer, gradle, tchiotludo/akhq, io.micrometer:micrometer-core, io.micrometer:micrometer-registry-prometheus, org.miracum:kafka-fhir-serializer, ca.uhn.hapi.fhir:hapi-fhir-client-okhttp, ca.uhn.hapi.fhir:hapi-fhir-structures-r4, ca.uhn.hapi.fhir:hapi-fhir-client, ca.uhn.hapi.fhir:hapi-fhir-base, org.springframework.cloud:spring-cloud-dependencies, io.spring.dependency-management, org.springframework.boot)

Detected dependencies

• Check this box to trigger a request for Renovate to run again on this repository

Also a possibility for using Basic-Auth would be very useful.

Originally posted by @chrmey in #6 (comment)

Hi,

Error description

when using the given default docker-compose.yml and set SERVICES_PSQL_ENABLED false, the start of the container fails, because of an setting in the application.yml.

start up log.

.... 
gateway-1             | 2023-12-01 12:08:08.939  INFO --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Starting...
gateway-1             | 2023-12-01 12:08:09.116  INFO --- [           main] com.zaxxer.hikari.pool.HikariPool        : HikariPool-1 - Added connection conn0: url=jdbc:h2:mem:05d8e72e-3558-4220-9d9a-75b79f6835f0 user=SA
gateway-1             | 2023-12-01 12:08:09.118  INFO --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Start completed.
gateway-1             | 2023-12-01 12:08:09.138  WARN --- [           main] ConfigServletWebServerApplicationContext : Exception encountered during context initialization - cancelling refresh attempt: org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'fhirController' defined in file [/opt/fhir-gateway/BOOT-INF/classes/org/miracum/etl/fhirgateway/controllers/FhirController.class]: Unsatisfied dependency expressed through constructor parameter 1: Error creating bean with name 'resourcePipeline' defined in file [/opt/fhir-gateway/BOOT-INF/classes/org/miracum/etl/fhirgateway/processors/ResourcePipeline.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'postgresFhirResourceRepository' defined in file [/opt/fhir-gateway/BOOT-INF/classes/org/miracum/etl/fhirgateway/stores/PostgresFhirResourceRepository.class]: Unsatisfied dependency expressed through constructor parameter 1: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Failed to execute SQL script statement #1 of class path resource [schema.sql]: CREATE TABLE IF NOT EXISTS resources ( id bigserial PRIMARY KEY, fhir_id varchar(64) NOT NULL, type varchar(64) NOT NULL, data jsonb NOT NULL, created_at timestamp NOT NULL DEFAULT NOW(), last_updated_at timestamp NOT NULL DEFAULT NOW(), is_deleted boolean NOT NULL DEFAULT FALSE, CONSTRAINT fhir_id_unique UNIQUE (fhir_id, type) )
gateway-1             | 2023-12-01 12:08:09.138  INFO --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown initiated...
gateway-1             | 2023-12-01 12:08:09.145  INFO --- [           main] com.zaxxer.hikari.HikariDataSource       : HikariPool-1 - Shutdown completed.
gateway-1             | 2023-12-01 12:08:09.148  INFO --- [           main] o.apache.catalina.core.StandardService   : Stopping service [Tomcat]
gateway-1             | 2023-12-01 12:08:09.166  INFO --- [           main] .s.b.a.l.ConditionEvaluationReportLogger :
gateway-1             |
gateway-1             | Error starting ApplicationContext. To display the condition evaluation report re-run your application with 'debug' enabled.
gateway-1             | 2023-12-01 12:08:09.181 ERROR --- [           main] o.s.boot.SpringApplication               : Application run failed
gateway-1             |
gateway-1             | org.springframework.beans.factory.UnsatisfiedDependencyException: Error creating bean with name 'fhirController' defined in file [/opt/fhir-gateway/BOOT-INF/classes/org/miracum/etl/fhirgateway/controllers/FhirController.class]: Unsatisfied dependency expressed through constructor parameter 1: Error creating bean with name 'resourcePipeline' defined in file [/opt/fhir-gateway/BOOT-INF/classes/org/miracum/etl/fhirgateway/processors/ResourcePipeline.class]: Unsatisfied dependency expressed through constructor parameter 0: Error creating bean with name 'postgresFhirResourceRepository' defined in file [/opt/fhir-gateway/BOOT-INF/classes/org/miracum/etl/fhirgateway/stores/PostgresFhirResourceRepository.class]: Unsatisfied dependency expressed through constructor parameter 1: Error creating bean with name 'dataSourceScriptDatabaseInitializer' defined in class path resource [org/springframework/boot/autoconfigure/sql/init/DataSourceInitializationConfiguration.class]: Failed to execute SQL script statement #1 of class path resource [schema.sql]: CREATE TABLE IF NOT EXISTS resources ( id bigserial PRIMARY KEY, fhir_id varchar(64) NOT NULL, type varchar(64) NOT NULL, data jsonb NOT NULL, created_at timestamp NOT NULL DEFAULT NOW(), last_updated_at timestamp NOT NULL DEFAULT NOW(), is_deleted boolean NOT NULL DEFAULT FALSE, CONSTRAINT fhir_id_unique UNIQUE (fhir_id, type) )
gateway-1             |         at org.springframework.beans.factory.support.ConstructorResolver.createArgumentArray(ConstructorResolver.java:801) ~[spring-beans-6.0.13.jar:6.0.13]

Cause:

Solution

Set SPRING_SQL_INIT_MODE to never, so the SQL script is disabled, when wanting to deactivate the DB output.
Maybe you have a better solution to it, but the setting used to be on never in previous versions.

I would add to the SPRING_SERVICE_PSQL_ENABLED config in the docu, that the second attribute has to be set addtionally if the DB is to be deactivated.