Overview

ClinSpark customers have available to them a live read-only copy of their production ClinSpark database called a Read Replica. This document will describe what a Read Replica is from a technical perspective. It will provide descriptions of key aspects of the underlying Data Model so that technical users may better understand how the data is stored. And it will explore various use cases for leveraging the Read Replica as well as some examples.

Read Replicas

All ClinSpark data is stored within a MySQL compatible relational database within the AWS Cloud. The Master database is the production database for the ClinSpark instance. This is where all data is written to and updated during the use of the ClinSpark application. In addition to other live backup mechanisms for operational use, a special read-only copy of this production data can be made available to customers. This database is a dedicated copy of the production database, solely for the purpose of customer usage. It is not used by running ClinSpark instances in any way. It is read-only, meaning that it does not accept writes, and it is not possible for any usage of this database to impact the Master database in any way.

Here is a visual depiction of the Read Replica in the AWS environment:

Note that like all ClinSpark application assets, it lives within private subnets of the Foundry Health Production Virtual Private Cloud (VPC). It is not exposed to the internet. It's data is encrypted at rest, and in transit via SSH.

Latency: How old is the data in the Replica?

As updates are applied to Master, the Replica receives these updates in real time. There is a delay, but due to the architecture of AWS RDS Aurora, this delay is typically no greater than 20 ms. So 20 milliseconds after a change to the production database is made, the customer's Read Replica has this update available.

Connecting to your Replica

There are a variety of ways to access the Read Replica. For customers with established relationships with AWS and special connections between AWS and their site's data centers, a variety of approaches exist for providing more direct connections to the replica.

However for most customers, the most common way to access the Read Replica is via SSH. For these customers, we provision a special bastion host which has no purpose other than to provide this customer with access to this replica. This host is accessible only via SSH, and only using the customer-owned private SSH key. This host has no access to the environment other than to access this replica.

During onboarding, 2 DNS names and one set of credentials will be provided to you. One is the DNS name of your dedicated bastion host to access the replica via SSH. And the other is the DNS name of the read replica itself with the AWS VPC. For this example, let's pretend these DNS name are as follows:

Customer Bastion Host	customer-replica-bastion.clinspark.com
Customer Read Replica (private DNS)	customer-replica.crs8xf7ezw7g.us-east-1.rds.amazonaws.com
Read Replica Username	<username>
Read Replica Password	<password>

To connect to his replica via the command line, the steps are as follows:

Create a SSH key you will use to access the DB through the bastion. If you already have one that's fine. Here are instructions for doing this and also adding the key to the ssh-agent.
Provide Foundry Health with the public SSH keys of users who need access to this database. We will place this on the bastion, allowing these users to tunnel into the replica. Open a service ticket with the public key attached and we'll get this installed quickly.
Verify you have access by connecting to the bastion and replica as follows from the command line:

$ ssh ec2-user@customer-replica-bastion.clinspark.com
Last login: Fri Dec 16 18:39:49 2016 from nclarkllc03.n.subnet.rcn.com
 
       __|  __|_  )
       _|  (     /   Amazon Linux AMI
      ___|\___|___|
 
https://aws.amazon.com/amazon-linux-ami/2016.09-release-notes/
[ec2-user@ip-172-31-17-113 ~]$ mysql -u <username> -h customer-replica.crs8xf7ezw7g.us-east-1.rds.amazonaws.com -p<password>
Warning: Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 262
Server version: 5.6.22 MySQL Community Server (GPL)
 
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
 
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql>

The above shows a terminal session where the user has first added the private ssh key to the ssh-agent as described in the previous link. The user connects to the bastion, and from there is able to use the mysql client to interact with the replica directly from the bastion.

You'll probably want to establish a SSH tunnel between your local workstation and the replica through the bastion. This is a common usage pattern, see your SSH client of choice documentation for instructions on how to configure this.

The above approach is appropriate if only one or two trusted users will access the bastion. It is secure as long as the private keys are secured. Customers with more users will want to find more scalable ways to access the replica data. One could be a SSH persistent tunnel on the customer site, and used by other users at the site. AWS has a variety of options for this. See AWS VPNs and Direct Connect, which are two mechanisms to securely connect your sites to your own AWS account. From there a variety of options exist, including AWS PrivateLink to create secure connections between AWS accounts.

It is the customer's responsibility to select and configure any options such as the above. Foundry Health will assist by suggesting approaches and making required configurations on our end. But the majority of the setup is in customer-owned infrastructure, and for this the customer is solely responsible.

Clinical Data Interchange Standards Consortium Standard (CDISC)

To the maximum extent possible, the ClinSpark data model is based on the CDISC Operational Data Model (ODM) standard. Introduce CDISC ODM.

Links to CDISC documentation. Identify subject areas driven by CDISC. Introduce core entities to highlight how the standard represents clinical study design and study data. Introduction to these tables is given in the context of CDISC, and cleanly segues into the data model below. Highlights implicit point that the CDISC documentation itself is a valuable guide to the CS data model.

ClinSpark Data Model

to the extent possible and practical, the CS data model is tightly aligned to CDSIC 1.3.2. There are several benefits to this

Standards-based simplifies interoperability
Simplified training and transferable knowledge
High quality data foundation vs proprietary makes data more future-proof

The following section highlights a series of key subject areas within the ClinSpark data model. Where elements are defined within the CDISC ODM standard, the descriptions from this documentation are used.

Study Design

Studies, Sites, StudySites an Subjects

	Table	From CDISC?	Notes
1	study	Yes	This element collects static structural information about an individual study. A study is related to a given clinical trial protocol.
2	study_site	No	An association between a physical site and a study. A study site is different than a physical location. Often, pharma sponsors will specify sites with arbitrary codes and those codes must pass through during data export time. In addition, this domain encapsulates recruitment efforts for a given study / site.
3	site	No	A site is a physical place belonging to an organization. An organization having multiple physical clinical sites will have multiple site rows.
4	subject	No	Someone participating in clinical research within the context of a given study. Creates glue between the volunteer and the participation.

Form Definition

	Table	From CDISC?	Notes
1	StudyMetaData	Yes	StudyMetaData (MetadataVersion in CDISC) defines the types of study events, forms, item groups, and items that form the study data. This is basically an aggregation of all CRF design elements for a study.
2	Form	Yes	A Form (FormDef in CDISC) describes a type of form that can occur in a study. A form is basically a container for item groups. This class explicitly excludes the required 'repeating' (Yes\|No) attribute from the domain due to the fact that phase 1 studies are different and that it's likely that most of the forms will repeat as they are related to a study event. At ODM build time, we'll check for those forms that repeat and ensure that we create the repeating attribute properly.
3	ItemGroup	Yes	An ItemGroup (ItemGroupDef in CDISC) describes a type of item group that can occur within a study. It basically is collection of related items in a given form.
4	Item	Yes	An Item (ItemDef in CDISC) describes a type of item that can occur within a study. Item properties include name, datatype, measurement units, range or codelist restrictions, and several other properties. It basically represents the definition of a piece of data collected.
5	ItemGroupRef	Yes	A reference to a given ItemGroup. This reference can hold data about the association.
6	ItemRef	Yes	A reference to a given Item. This reference can hold data about the association.

Activity Plans

An activity plan is a schedule of events for a given cohort. Activity Plans do not appear in CDSIC, there is no similar construct in the ODM to capture this concept. Activity Plan, Segment and Scheduled Activity are ClinSpark specific.

	Table	From CDISC?	Notes
1	Study	Yes	This element collects static structural information about an individual study. A study is related to a given clinical trial protocol.
2	Activity Plan	No	A schedule of events for a given cohort. Plans can be assigned to multiple cohorts. A timed plan must have a reference time in order to properly provide UI feedback as segments and scheduled activities are set. Untimed Activity Plan: Reference time must be null Single segment; segment must be root and must have offset second set to zero Timed Activity Plan: Must have a reference time Can have 1-n segments; always sort by offset seconds Reference segment must have offset seconds of zero
3	Segment	Partially	Holds a group of scheduled activities in an activity plan. The segment's offset seconds is essentially the time of the reference event, all scheduled activities are relative to this. Modeled somewhat of off CDISC SDM: "Segments are often seen as the basic building blocks of study design. A segment usually specifies a combination of planned observations and interventions, which may or may not involve treatment, during a period of time."
4	Scheduled Activity	No	Wraps a form, but adds metadata including timing.
5	Form	Yes	A form is basically a container for item groups.
6	Study Event	Yes	A study event represents a given 'visit'. In phase 1 trials this will commonly simply refer to a 'day'. When scheduling forms for a given schedule, the builder must associate the study event. Note: there are common study events that are typically reserved for special events: unscheduled, common (AE, CM), etc

Epochs and Cohorts

	Table	From CDISC?	Notes
1	epoch	No	An epoch is typically specified in a study protocol and typically signifies some milestone type events within the trial. ie: screening, treatment, followup, etc
2	cohort	No	A cohort is a way to break up epochs into different groupings. Protocols will occasionally indicate that epochs should be broken up (perhaps in different trial arms to test different dose levels, etc), or this can just purely be an organizational thing
3	cohort_assignment	No	Binds a Volunteer as Subjects within a given cohort along with an Activity Plan. We allow assigning different schedules, and we allow for later scheduling a subject.
4	activity_plan	No	A schedule of events for a given cohort.
6	subject	No	Someone participating in clinical research within the context of a given study.

Clinical Data

Parallel data and definition objects in CDISC

CDISC ODM defines a pattern for modeling the definition of a clinical data element and the data captured. This pattern has one element describe the definition, and another element record the data for a particular clinical collection of this element type. ClinSpark adheres to this pattern, though as noted in the table below, the suffix "Def" is removed from the definition elements. Here are the examples

Data Definition Element	Data Capture Element	Notes
Item (ItemDef in CDISC)	ItemData	Represent the definition of a piece of data collected
ItemGroup (ItemGroupDef in CDISC)	ItemGroupData	Aggregates item data
Form (FormDef in CDISC)	FormData	Basically a container for item groups
StudyEvent (StudyEventDef in CDISC)	StudyEventData	Clinical data for a study event (visit) for a given subject

With this in mind, below is a view of how these elements relate to each other.

	Table	From CDISC?	Notes
1	form	Yes	Basically a container for item groups.
2	form_data	Yes	Form data represents data collected for a given subject. Instead of storing the scheduled time on this domain, we leverage the relationship to the encapsulated scheduledActivity domain and thus its relationship to the segment. We purposely don't set formRepeatKey in the domain. It is calculated later on when building ODM.
3	item_group	Yes	A collection of related items in a given form
4	item_group_ref	Yes	A given item group within a form definition.
5	item_group_data	Yes	Aggregates item data
6	item	Yes	Represents the definition of a piece of data collected.
7	item_ref	Yes	A given item within an item group definition.
8	item_data	Yes	A piece of collected data
9	study_event	Yes	A study event represents a given 'visit'. In phase 1 trials this will commonly simply refer to a 'day'. When scheduling forms for a given schedule, the builder must associate the study event. Note: there are common study events that are typically reserved for special events: unscheduled, common (AE, CM), etc
10	study_event_data	Yes	Clinical data for a study event (visit) for a given subject

Study Data

	Table	From CDISC?	Notes
1	item_data	Yes	A piece of collected data
2	item_group_data	Yes	Aggregates item data
3	form_data	Yes	Form data represents data collected for a given subject. Instead of storing the scheduled time on this domain, we leverage the relationship to the encapsulated scheduledActivity domain and thus its relationship to the segment.
4	study_event_data	Yes	Clinical data for a study event (visit) for a given subject
5	study_event	Yes	A study event represents a given 'visit'. In phase 1 trials this will commonly simply refer to a 'day'. When scheduling forms for a given schedule, the builder must associate the study event. Note: there are common study events that are typically reserved for special events: unscheduled, common (AE, CM), etc
6	subject	No	Someone participating in clinical research within the context of a given study.

Lab Data

Lab data can be reached via ItemData

	Table	From CDISC?	Notes
1	item_data	Yes	A piece of collected data. Note that lab orders and all results are associated to a given Item Data. You will need to join through Item Data when working with lab data.
2	base_specimen	Yes	Modeled off of CDISC Lab. A specimen is collected from a subject and assigned to a given item data instance. There can be multiple batteries (test groups) associated to a given specimen. Combines Accession Level and Base Specimen from spec.
3	base_battery	No	A panel related to a specimen - typically this is just a 1:1.
4	base_test_result	Partially	Combines CDISC Lab BaseTest and BaseResult. These are the results from the lab.
5	lab_order	No	When specimens are collected, this domain represents that an order is generated. It causes a manifest file to be created (PDF) and potentially a file order to be dumped on to the file system and made available to web services.
6	lab_interface	No	Encapsulates how to send and receive orders and results from a particular safety lab. Sites may have multiple labs, and if so each of these will have their own lab interface instance.
7	study_lab_panel	No	Something that can be ordered from item level
8	specimen_container	No	When setting up samples or labs, users can optionally choose a container.

Volunteers

Volunteers are not a part of the CDISC. We need to show a path to volunteer data and associated medical histories, medications, etc.

	Table	From CDISC?	Notes
1	volunteer	No	Someone who indicates that they are interested in participating in clinical research for the given org.Someone who indicates that they are interested in participating in clinical research for the given org.
2	volunteer_medical_condition	No	Associates a given condition to a given volunteer
3	volunteer_note	No	A simple note that can be attached to the volunteer record
4	volunteer_correspondence	No	Represents a call or text to / from a volunteer by way of Twilio
5	volunteer_substance_use	No	We purposely don't track SUOCCUR, it allows us to indicate that the vlunteer is not using the substance.
6	recruitment_appointment	No	Allows for a given volunteer to be assigned to a given time slot
7	study_site	No	An association between a study and a site
8	subject	No	Someone participating in clinical research within the context of a given study.

Users

	Table	From CDISC?	Notes
1	application_user	No	A user in the system
2	study	Yes	In this context, these are study restrictions. Studies which appear here mean the user is whitelisted to working only with data from these studies.
3	application_user_role	No	Roles which the user has
4	application_user_role_secure_actions	Yes	Secure Actions are permissions which the role entitles the user to.

Read Replica Usage

Integration to your Existing Data Warehouse

Ad Hoc SQL Queries

Probably all SQL tools connect to MySQL databases. So whatever SQL tool you typically use should work with very little configuration. Use the tool that is most familiar to you. If you do not have a tool preference, one option may be the free MySQL Workbench, which is a fairly full featured tool. Using a SQL tool like this is useful for creating queries to answer questions on the fly. It can also be used to generate simple reports, perform data modeling (the above diagrams were created with this tool), etc.

The connection instructions above show how to connect MySQL Workbench to your replica. And there is extensive help online and in the app for using the tool from there.