Remote subscriber data definition - Revision history

AdminJo at 10:49, 7 May 2025

2025-05-07T10:49:16Z

AdminJo: /* Process steps */

2025-05-07T10:40:35Z

Process steps

← Older revision		Revision as of 10:40, 7 May 2025
Line 1:		Line 1:
	This article describes how the ~~Discovery Data Service~~ makes data available for Remote Subscriber Databases (RSDs) and how the ~~DDS~~ Remote Filer application interacts with this to update an RSD. Although it is recommended that the Remote Filer application be used for updating RSDs, any other technical solution can be substituted provided it is able to match the Remote Filer behaviour as far as the points of interaction are concerned.		This article describes how the Voror Health Platform (VHP) makes data available for Remote Subscriber Databases (RSDs) and how the VHP Remote Filer application interacts with this to update an RSD. Although it is recommended that the Remote Filer application be used for updating RSDs, any other technical solution can be substituted provided it is able to match the Remote Filer behaviour as far as the points of interaction are concerned.

	== Subscriber database schema ==		== Subscriber database schema ==
	The ~~DDS~~ currently provides SQL scripts for creating subscriber databases, one for each of the two support database engines; the database scripts are the same no matter what configuration options are selected when setting up the feed from the ~~DDS~~ (for example, PI versus de-identified).		The VHP currently provides SQL scripts for creating subscriber databases, one for each of the two support database engines; the database scripts are the same no matter what configuration options are selected when setting up the feed from the VHP (for example, PI versus de-identified).

	* [https://github.com/endeavourhealth/EDS/blob/master/src/database/mySQL/compass%20v2%20(subscriber)/create_tables_NO_FKS.sql MySQL]		* [https://github.com/endeavourhealth/EDS/blob/master/src/database/mySQL/compass%20v2%20(subscriber)/create_tables_NO_FKS.sql MySQL]
Line 216:		Line 216:
	{{Tip\| See [[Remote subscriber data definition#Required parameters\|Required parameters]] for more details.}}		{{Tip\| See [[Remote subscriber data definition#Required parameters\|Required parameters]] for more details.}}
	<li>The Remote Filer downloads all zip files found in the pre-configured source incoming directory and writes them to temporary local storage.</li>		<li>The Remote Filer downloads all zip files found in the pre-configured source incoming directory and writes them to temporary local storage.</li>
	~~<li>The Remote Filer deletes the downloaded zip files from the SFTP server.</li>~~
	<li>The downloaded PGP encrypted zip file is decrypted using the pre-configured PGP private key.</li>		<li>The downloaded PGP encrypted zip file is decrypted using the pre-configured PGP private key.</li>
	<li> Multi-part zips are merged into standalone zip files.</li>		<li> Multi-part zips are merged into standalone zip files.</li>
Line 235:		Line 234:
	<li>The results zip file is then uploaded to the SFTP server to another pre-configured directory.</li>		<li>The results zip file is then uploaded to the SFTP server to another pre-configured directory.</li>
	</ol>		</ol>
			<li>The Remote Filer deletes the downloaded zip files from the SFTP server.</li>
	</ol>		</ol>

JoC at 14:53, 18 June 2021

2021-06-18T14:53:55Z

← Older revision		Revision as of 14:53, 18 June 2021
Line 191:		Line 191:
	#: {{Note\|This is done for the Published Data Feed only}}		#: {{Note\|This is done for the Published Data Feed only}}

	~~Since it~~ is Java-based, it can be run on a Windows or Linux server.		Because the DDS Remote Filer is Java-based, it can be run on a Windows or Linux server, however, the selected server (and infrastructure hosting that server) must allow:

	~~The~~ server (and infrastructure hosting that server) must allow:

	* outbound connectivity to the DDS SFTP server to download new data and upload feedback files.		* outbound connectivity to the DDS SFTP server to download new data and upload feedback files.
Line 240:		Line 238:

	===Error handling===		===Error handling===
	If there is an error applying data to the RSD there is no need to retain the zip file that caused the error. Provided the error is reported back to DDS through the '''failure.txt''' file ~~then~~ DDS will ~~be able to re-send~~ the data when the issue causing the error is resolved. The '''failure.txt''' file is automatically picked up by DDS monitoring and the operational team informed of the error.		If there is an error applying data to the RSD, there is no need to retain the zip file that caused the error.

			Provided the error is reported back to the DDS through the '''failure.txt''' file, the DDS will resend the data when the issue causing the error is resolved.

			The '''failure.txt''' file is automatically picked up by DDS monitoring and the operational team informed of the error.

	===Required parameters===		===Required parameters===

	The configuration parameters the Remote Filer ~~requires~~ to perform the above ~~process~~ are:		The configuration parameters needed for the Remote Filer to perform the above processes are:

	* SFTP address – the host name / IP address of the DDS SFTP server. This may be an internet or HSCN address, depending on each subscriber configuration,		* SFTP address – the host name / IP address of the DDS SFTP server. This may be an internet or HSCN address, depending on each subscriber configuration,
Line 259:		Line 261:

	== Remote Filer: Reference Data Feed ==		== Remote Filer: Reference Data Feed ==
	When the DDS has new reference data to send to an RSD, it generates SQL and makes it available on the DDS SFTP server in a PGP encrypted file called '''concepts.zip''', as described in [[Remote subscriber data definition#Reference data feed staging directory\|Reference data feed staging directory]] . The Remote Filer is then ~~able to connect~~ and ~~download~~ these files to process them.		When the DDS has new reference data to send to an RSD, it generates SQL and makes it available on the DDS SFTP server in a PGP encrypted file called '''concepts.zip''', as described in [[Remote subscriber data definition#Reference data feed staging directory\|Reference data feed staging directory]] . The Remote Filer then connects and downloads these files to process them.

	=== Process steps ===		=== Process steps ===
Line 282:		Line 284:

	=== Error Handling ===		=== Error Handling ===
	There is currently no mechanism for automatically reporting errors ~~with~~ applying the reference update to the RSD to DDS. The Remote Filer logs contain any errors and are periodically checked.		There is currently no mechanism for automatically reporting errors when applying the reference update to the RSD to the DDS.

			The Remote Filer logs contain any errors and are periodically checked.

	=== Required Parameters ===		=== Required Parameters ===
	The configuration parameters the Remote Filer ~~requires~~ to perform the above ~~process~~ are:		The configuration parameters needed for the Remote Filer to perform the above processes are:

	* SFTP address – the host name / IP address of the DDS SFTP server. This may be an internet or HSCN address, depending on each subscriber configuration,		* SFTP address – the host name / IP address of the DDS SFTP server. This may be an internet or HSCN address, depending on each subscriber configuration,

JoC at 14:47, 18 June 2021

2021-06-18T14:47:01Z

Show changes

JoC at 14:44, 18 June 2021

2021-06-18T14:44:46Z

Show changes

JoC: /* Subscriber database schema */

2021-06-18T14:44:39Z

Subscriber database schema

JoC: /* Remote Filer: Reference Data Feed */

2021-06-18T13:21:16Z

Remote Filer: Reference Data Feed

← Older revision		Revision as of 13:21, 18 June 2021
Line 280:		Line 280:
	<ol><li>Execute the file as SQL against the RSD.</li></ol></li>		<ol><li>Execute the file as SQL against the RSD.</li></ol></li>
	</ol>		</ol>
	~~<br />~~

	=== Error Handling ===		=== Error Handling ===

JoC: /* Remote Filer: Reference Data Feed */

2021-06-18T13:20:57Z

Remote Filer: Reference Data Feed

← Older revision		Revision as of 13:20, 18 June 2021
Line 279:		Line 279:
	<li>For each execute file in the unzipped content:		<li>For each execute file in the unzipped content:
	<ol><li>Execute the file as SQL against the RSD.</li></ol></li>		<ol><li>Execute the file as SQL against the RSD.</li></ol></li>
			</ol>
	<br />		<br />

JoC at 13:20, 18 June 2021

2021-06-18T13:20:34Z

← Older revision		Revision as of 13:20, 18 June 2021
Line 268:		Line 268:
	<ol>		<ol>
	<li>The Remote Filer connects to the DDS SFTP server.<li>		<li>The Remote Filer connects to the DDS SFTP server.<li>
	<li>The Remote Filer downloads any ~~“concepts~~.~~zip”~~ content from the configured directory for staging the reference data feed.</li>		<li>The Remote Filer downloads any concepts.zip content from the configured directory for staging the reference data feed.</li>
	<li>Having downloaded the file, the Remote Filer deletes the data from the DDS SFTP server.</li>		<li>Having downloaded the file, the Remote Filer deletes the data from the DDS SFTP server.</li>
	<li>Any downloaded file(s) will be PGP encrypted. These files should be decrypted.</li>		<li>Any downloaded file(s) will be PGP encrypted. These files should be decrypted.</li>
Line 275:		Line 275:
	<li>For each SQL file in the unzipped content:		<li>For each SQL file in the unzipped content:
	<ol><li>Execute the SQL file against the RSD.</li></ol></li>		<ol><li>Execute the SQL file against the RSD.</li></ol></li>
	<li>For each ~~“setup”~~ file in the unzipped content:		<li>For each setup file in the unzipped content:
	<ol><li>Execute the file as SQL against the RSD.</li></ol></li>		<ol><li>Execute the file as SQL against the RSD.</li></ol></li>
	<li>For each ~~“execute”~~ file in the unzipped content:		<li>For each execute file in the unzipped content:
	<ol><li>Execute the file as SQL against the RSD.</li></ol></li>		<ol><li>Execute the file as SQL against the RSD.</li></ol></li>
	<br />		<br />

JoC: /* Reference data feed staging directory */

2021-06-18T13:19:46Z

Reference data feed staging directory

@@ Line 162: / Line 162: @@
 === Reference data feed staging directory ===
-When DDS has new reference data to sent to an RSD, a file called “concepts.zip” is put in this directory. This zip file is PGP encrypted in the same way as the Published Data Feed files. As with the Published Data Feed staging directory, if this file is over 10MB it will be split into a multi-part zip file.
+When DDS has new reference data to sent to an RSD, a file called '''Concepts.zip''' is put in this directory. This zip file is PGP encrypted in the same way as the Published Data Feed files. As with the Published Data Feed staging directory, if this file is over 10MB it will be split into a multi-part zip file.
 [[File:DDS SFTP server concepts zip.png|border]]
@@ Line 210: / Line 210: @@
 ===Process steps===
-The following diagram shows the steps the Remote Filer (shown in green) performs for the Published Data feed. This entire end to end process would normally be performed once daily, typically scheduled using a suitable scheduling tool such as cron on Ubuntu or Windows Scheduler.
+The following diagram shows the steps that the Remote Filer (shown in green) performs for the Published Data feed. This entire end to end process would normally be performed once daily, typically scheduled using a suitable scheduling tool such as cron on Ubuntu or Windows Scheduler.
 [[File:Process_steps.png|alt=|border|right]]
 The steps in detail:
@@ Line 259: / Line 259: @@
 == Remote Filer: Reference Data Feed ==
-When the DDS has new reference data to send to an RSD, it generates SQL and makes it available on the DDS SFTP server in a PGP encrypted file called '''concepts.zip''', as described in [[Remote subscriber data definition#Reference data feed staging directory|Reference data feed staging directory]] . The Remote Filer is then able to connect and download these files to process them.<br />
+When the DDS has new reference data to send to an RSD, it generates SQL and makes it available on the DDS SFTP server in a PGP encrypted file called '''concepts.zip''', as described in [[Remote subscriber data definition#Reference data feed staging directory|Reference data feed staging directory]] . The Remote Filer is then able to connect and download these files to process them.

← Older revision		Revision as of 10:49, 7 May 2025
Line 13:		Line 13:
	* The Remote Filer application currently only supports loading data into these two database engines, and part of the data feed (for reference data) is sent as raw SQL and only these two formats are supported.		* The Remote Filer application currently only supports loading data into these two database engines, and part of the data feed (for reference data) is sent as raw SQL and only these two formats are supported.

	* The ~~DDS~~ still supports data feeds to an older version of the subscriber database known as '''Compass v1''' (the current version being v2). Although v1 is still supported, new instances of this will not be deployed and this article specifically addresses the v2 standard. Future developments/improvements to the ~~DDS~~ subscriber database will be iterative upgrades to v2.}}		* The VHP still supports data feeds to an older version of the subscriber database known as '''Compass v1''' (the current version being v2). Although v1 is still supported, new instances of this will not be deployed and this article specifically addresses the v2 standard. Future developments/improvements to the VHP subscriber database will be iterative upgrades to v2.}}

	==Subscriber feeds==		==Subscriber feeds==
	There are two separate feeds of data that the ~~DDS~~ sends to each RSD:		There are two separate feeds of data that the VHP sends to each RSD:

	* '''Published Data''' '''Feed''' – this includes all patient data, plus some supporting data (clinicians and organisations for example) that is sent into the ~~DDS~~ by external publishers.		* '''Published Data''' '''Feed''' – this includes all patient data, plus some supporting data (clinicians and organisations for example) that is sent into the VHP by external publishers.

	* '''Reference Data''' '''Feed''' – this includes lookups and mappings for clinical codes (Read2 to SNOMED for example) that is not directly published into the ~~DDS~~ but is updated in subscriber databases.		* '''Reference Data''' '''Feed''' – this includes lookups and mappings for clinical codes (Read2 to SNOMED for example) that is not directly published into the VHP but is updated in subscriber databases.

	Data for each feed is staged in a separate directory on the ~~DDS~~ SFTP server for each subscriber. The ~~DDS~~ Remote Filer application runs supports running in two different modes, one to download and process the '''Published Data Feed''' and the other to download and process the '''Reference Data Feed'''.		Data for each feed is staged in a separate directory on the VHP SFTP server for each subscriber. The VHP Remote Filer application runs supports running in two different modes, one to download and process the '''Published Data Feed''' and the other to download and process the '''Reference Data Feed'''.

	{{Warning\| If you replace the Remote Filer application with an alternative solution it must support both feeds.}}		{{Warning\| If you replace the Remote Filer application with an alternative solution it must support both feeds.}}

	== ~~DDS~~ SFTP server ==		== VHP SFTP server ==
	The ~~DDS~~ SFTP server is used to stage all data for all RSDs.		The VHP SFTP server is used to stage all data for all RSDs.

	A user is created on this server for each ~~DDS~~ subscriber, with their own username and SSH certificate, to allow them to securely access the data intended for them.		A user is created on this server for each VHP subscriber, with their own username and SSH certificate, to allow them to securely access the data intended for them.

	If a ~~DDS~~ subscriber has multiple RSDs (for example, one for GP data and one for acute data), the same SFTP user is used for both RSDs.		If a VHP subscriber has multiple RSDs (for example, one for GP data and one for acute data), the same SFTP user is used for both RSDs.

	For each RSD, the following three directories are created under the SFTP user home directory to:		For each RSD, the following three directories are created under the SFTP user home directory to:

← Older revision		Revision as of 14:44, 18 June 2021
Line 2:		Line 2:

	== Subscriber database schema ==		== Subscriber database schema ==
	DDS currently provides SQL scripts for creating subscriber databases, one for each of the two support database engines; the database scripts are the same no matter what configuration options are selected when setting up the feed from DDS (~~e.g.~~ PI versus de-identified).		The DDS currently provides SQL scripts for creating subscriber databases, one for each of the two support database engines; the database scripts are the same no matter what configuration options are selected when setting up the feed from the DDS (for example, PI versus de-identified).

	* [https://github.com/endeavourhealth/EDS/blob/master/src/database/mySQL/compass%20v2%20(subscriber)/create_tables_NO_FKS.sql MySQL]		* [https://github.com/endeavourhealth/EDS/blob/master/src/database/mySQL/compass%20v2%20(subscriber)/create_tables_NO_FKS.sql MySQL]
	* [https://github.com/endeavourhealth/EDS/blob/master/src/database/SQL%20Server/compass%20v2%20(subscriber)/create_tables_NO_FKS.sql SQL Server] (or compatible, ~~e.g.~~ Azure SQL DB)		* [https://github.com/endeavourhealth/EDS/blob/master/src/database/SQL%20Server/compass%20v2%20(subscriber)/create_tables_NO_FKS.sql SQL Server] (or compatible, such as Azure SQL DB)

	{{Note\| Please note the following:		{{Note\| Please note the following:

	* The above ~~are~~ links to the public GitHub repository ~~which~~ was in active development until early 2021. Development has moved to private GitHub repositories ~~since this date~~, so the SQL schemas ~~linked to above~~ should only be taken as illustrations of the schema and not the latest version (which can be provided on request).		* The above links navigate to the public GitHub repository that was in active development until early 2021. Development has since moved to private GitHub repositories, and therefore the SQL schemas links should only be taken as illustrations of the schema and not the latest version (which can be provided on request).

	* The Remote Filer application currently only supports loading data into these two database engines, and part of the data feed (for reference data) is sent as raw SQL and only these two formats are supported.		* The Remote Filer application currently only supports loading data into these two database engines, and part of the data feed (for reference data) is sent as raw SQL and only these two formats are supported.

	* DDS still supports data feeds to an older version of the subscriber database known as '''Compass v1''' (the current version being v2). Although v1 is still supported, new instances of this will not be deployed and this article specifically addresses the v2 standard. Future developments/improvements to the DDS subscriber database will be iterative upgrades to v2.}}		* The DDS still supports data feeds to an older version of the subscriber database known as '''Compass v1''' (the current version being v2). Although v1 is still supported, new instances of this will not be deployed and this article specifically addresses the v2 standard. Future developments/improvements to the DDS subscriber database will be iterative upgrades to v2.}}

	==Subscriber feeds==		==Subscriber feeds==
	There are two separate feeds of data that DDS sends to each RSD:		There are two separate feeds of data that the DDS sends to each RSD:

	* '''Published Data''' '''Feed''' – this includes all patient data, plus some supporting data (clinicians and organisations for example) that is sent into DDS by external publishers.		* '''Published Data''' '''Feed''' – this includes all patient data, plus some supporting data (clinicians and organisations for example) that is sent into the DDS by external publishers.

	* '''Reference Data''' '''Feed''' – this includes lookups and mappings for clinical codes (Read2 to SNOMED for example) that is not directly published into DDS but is updated in subscriber databases.		* '''Reference Data''' '''Feed''' – this includes lookups and mappings for clinical codes (Read2 to SNOMED for example) that is not directly published into the DDS but is updated in subscriber databases.

	Data for each feed is staged in a separate directory on the DDS SFTP server for each subscriber. The DDS Remote Filer application runs supports running in two different modes, one to download and process the '''Published Data Feed''' and the other to download and process the '''Reference Data Feed'''.		Data for each feed is staged in a separate directory on the DDS SFTP server for each subscriber. The DDS Remote Filer application runs supports running in two different modes, one to download and process the '''Published Data Feed''' and the other to download and process the '''Reference Data Feed'''.
Line 44:		Line 44:

	=== Published data feed staging directory ===		=== Published data feed staging directory ===
	This directory is used by DDS to stage the published data intended for the RSD, and includes all the patient and clinical data. When data from the DDS is available, for a subscriber, it is placed in this directory.		This directory is used by the DDS to stage the published data intended for the RSD, and includes all the patient and clinical data. When data from the DDS is available, for a subscriber, it is placed in this directory.

	The files placed in this directory are always named in the following format:		The files placed in this directory are always named in the following format: