class HPO(ModelMixin, BaseModel):
"""An awardee, containing organizations (which in turn contain sites.)"""
__tablename__ = 'hpo'
hpoId = Column('hpo_id', Integer, unique=True, autoincrement=False)
name = Column('name', String(20), unique=True)
displayName = Column('display_name', String(255))
organizationType = Column('organization_type',
ModelEnum(OrganizationType),
default=OrganizationType.UNSET)
organizations = relationship('Organization',
cascade='all, delete-orphan',
order_by='Organization.externalId')
isObsolete = Column('is_obsolete', ModelEnum(ObsoleteStatus))
class PhysicalMeasurements(ModelMixin, BaseModel):
__tablename__ = 'physical_measurements'
physicalMeasurementsId = Column('physical_measurements_id', Integer, unique=True,
autoincrement=False)
participantId = Column('participant_id', String(20), ForeignKey('participant.participant_id'),
nullable=False)
# created = Column('created', UTCDateTime, nullable=False)
resource = Column('resource', BLOB, nullable=False)
final = Column('final', Boolean, nullable=False)
# The ID that these measurements are an amendment of (points from new to old)
amendedMeasurementsId = Column('amended_measurements_id', Integer,
ForeignKey('physical_measurements.physical_measurements_id'))
logPositionId = Column('log_position_id', Integer, ForeignKey('log_position.log_position_id'),
nullable=False)
# The site that created the physical measurements.
createdSiteId = Column('created_site_id', Integer, ForeignKey('site.site_id'))
# The username / email of the HealthPro user that created the physical measurements.
createdUsername = Column('created_username', String(255))
# The site that finalized the physical measurements.
finalizedSiteId = Column('finalized_site_id', Integer, ForeignKey('site.site_id'))
# The username / email of the HealthPro user that finalized the physical measurements.
finalizedUsername = Column('finalized_username', String(255))
logPosition = relationship('LogPosition')
finalized = Column('finalized', UTCDateTime)
# Restored/amended measurements will be UNSET.
status = Column('status', ModelEnum(PhysicalMeasurementsStatus))
cancelledUsername = Column('cancelled_username', String(255))
cancelledSiteId = Column('cancelled_site_id', Integer, ForeignKey('site.site_id'))
cancelledTime = Column('cancelled_time', UTCDateTime)
reason = Column('reason', UnicodeText)
measurements = relationship('Measurement', cascade='all, delete-orphan')
class Site(ModelMixin, BaseModel):
__tablename__ = 'site'
siteId = Column('site_id', Integer, unique=True)
siteName = Column('site_name', String(255), nullable=False)
# The Google group for the site; this is a unique key used externally.
googleGroup = Column('google_group',
String(255),
nullable=False,
unique=True)
mayolinkClientNumber = Column('mayolink_client_number', Integer)
organizationId = Column('organization_id', Integer,
ForeignKey('organization.organization_id'))
# Deprecated; this is being replaced by organizationId.
hpoId = Column('hpo_id', Integer, ForeignKey('hpo.hpo_id'))
siteStatus = Column('site_status', ModelEnum(SiteStatus))
enrollingStatus = Column('enrolling_status', ModelEnum(EnrollingStatus))
digitalSchedulingStatus = Column('digital_scheduling_status',
ModelEnum(DigitalSchedulingStatus))
scheduleInstructions = Column('schedule_instructions', String(2048))
scheduleInstructions_ES = Column('schedule_instructions_es', String(2048))
launchDate = Column('launch_date', Date)
notes = Column('notes', UnicodeText)
notes_ES = Column('notes_es', UnicodeText)
latitude = Column('latitude', Float)
longitude = Column('longitude', Float)
timeZoneId = Column('time_zone_id', String(1024))
directions = Column('directions', UnicodeText)
physicalLocationName = Column('physical_location_name', String(1024))
address1 = Column('address_1', String(1024))
address2 = Column('address_2', String(1024))
city = Column('city', String(255))
state = Column('state', String(2))
zipCode = Column('zip_code', String(10))
phoneNumber = Column('phone_number', String(80))
adminEmails = Column('admin_emails', String(4096))
link = Column('link', String(255))
isObsolete = Column('is_obsolete', ModelEnum(ObsoleteStatus))
class QuestionnaireBase(ModelMixin):
"""
Mixin containing columns for Questionnaire and QuestionnaireHistory
"""
questionnaireId = Column('questionnaire_id', Integer, unique=True)
# Incrementing version, starts at 1 and is incremented on each update.
version = Column('version', Integer, nullable=False)
# created = Column('created', UTCDateTime, nullable=False)
lastModified = Column('last_modified', UTCDateTime, nullable=False)
# The JSON representation of the questionnaire provided by the client.
# Concepts and questions can be be parsed out of this for use in querying.
resource = Column('resource', JSON, nullable=False)
status = Column('status', ModelEnum(QuestionnaireDefinitionStatus),
default=QuestionnaireDefinitionStatus.VALID)
class MetricSet(BaseMetricsModel, ModelMixin):
"""A version containing a set of metrics in the database, generated by a pipeline.
Contains buckets with metrics grouped by HPO ID and date.
"""
__tablename__ = 'metric_set'
metricSetId = Column('metric_set_id', String(50), unique=True)
metricSetType = Column('metric_set_type',
ModelEnum(MetricSetType),
nullable=False)
lastModified = Column('last_modified', UTCDateTime, nullable=False)
metrics = relationship('AggregateMetrics',
cascade='all, delete-orphan',
passive_deletes=True)
class AggregateMetrics(BaseMetricsModel, ModelMixin):
"""Aggregate metric value within a metric set."""
__tablename__ = 'aggregate_metrics'
metricSetId = Column(
'metric_set_id', String(50),
ForeignKey('metric_set.metric_set_id', ondelete='CASCADE'))
metricsKey = Column('metrics_key', ModelEnum(MetricsKey))
value = Column('value', String(50))
count = Column('count', Integer, nullable=False)
__table_args__ = (UniqueConstraint('metric_set_id',
'metrics_key',
'value',
name='uidx_msetid_key_value'), )
class Organization(ModelMixin, BaseModel):
"""An organization, under an awardee/HPO, and containing sites."""
__tablename__ = 'organization'
# Database ID for the organization
organizationId = Column('organization_id', Integer, unique=True)
# External ID for the organization, e.g. WISC_MADISON
externalId = Column('external_id', String(80), nullable=False)
# Human readable display name for the organization, e.g. University of Wisconsin, Madison
displayName = Column('display_name', String(255), nullable=False)
# Foreign key to awardee/hpo this organization belongs to.
hpoId = Column('hpo_id', Integer, ForeignKey('hpo.hpo_id'), nullable=False)
# Sites belonging to this organization.
sites = relationship('Site',
cascade='all, delete-orphan',
order_by='Site.googleGroup')
isObsolete = Column('is_obsolete', ModelEnum(ObsoleteStatus))
class _CodeBase(ModelMixin):
"""Mixin with shared columns for Code and CodeHistory"""
codeId = Column('code_id', Integer, unique=True)
system = Column('system', String(255), nullable=False)
value = Column('value', String(80), nullable=False)
# OMOP codes are supposed to be at most 50 characters long; for legacy codes that exceeded this
# limit, we populate a shortened version for use in OMOP here. Otherwise, shortValue is identical
# to value.
shortValue = Column('short_value', String(50))
display = Column('display', UnicodeText)
topic = Column('topic', UnicodeText)
codeType = Column('code_type', ModelEnum(CodeType), nullable=False)
mapped = Column('mapped', Boolean, nullable=False)
# created = Column('created', UTCDateTime, nullable=False)
@declared_attr
def codeBookId(cls):
return Column('code_book_id', Integer,
ForeignKey('code_book.code_book_id'))
@declared_attr
def parentId(cls):
return Column('parent_id', Integer, ForeignKey('code.code_id'))
class BiobankStoredSample(ModelMixin, BaseModel):
"""Physical sampels which have been reported as received at Biobank.
Each participant has an associated list of samples. Biobank uploads a list of all received
samples periodically, and we update our list of stored samples to match. The output is a
reconciliation report of ordered and stored samples; see BiobankOrder.
Note that additional columns appear in the CSV uploaded from Biobank but are not persisted since
they are unused in the reconciliation report; we also only exclude child samples.
Additional field information from biobank:
A sample family (family_id) is created upon a parent tube being created. The family
contains study, visit, parent specimen information and others. Knowing the study,
visit and specimen information we are able to capture the test code value from the
study build information. Each sample is then linked to the family by their family id.
Order Identifier (biobank_order_identifier.value) is the unique id generated for
each order placed into MayoLink. This value has two different phases, before Aug
2018 and after.
Sample Order Id (biobank_ordered_sample.order_id) is the unique id generated by MayoLink as a
internal primary key value.
Sample Id (biobank_stored_sample_id) is the unique specimen id. Each sample created has a
unique Id. Sample ids are contained within a family. Family is created at same time the
parent tube is created.
Specimen Id for this project now is the order id plus an extra 4 numeric values.
First 2 represent study and the next two are identifying the test we are receiving.
"""
__tablename__ = 'biobank_stored_sample'
# A unique ID assigned by Biobank for the sample. (AKA "RLIMS Sample ID.)
# We omit autoincrement=False to avoid warnings & instead validate clients provide an ID upstream.
biobankStoredSampleId = Column('biobank_stored_sample_id',
String(80),
unique=True)
# The participant the sample is associated to. We use Biobank's ID for streamlined importing.
biobankId = Column('biobank_id', Integer,
ForeignKey('participant.biobank_id'))
# The biobank order identifier. to enable joining on reconciliation report.
biobankOrderIdentifier = Column('biobank_order_identifier',
String(80),
nullable=False)
# Which test was performed to produce this sample (ex: "1UR10" for blood draw). Rarely, the same
# test may be performed multiple times for the same participant.
test = Column('test', String(80), nullable=False)
# Timestamp when Biobank finished receiving/preparing the sample (status changed from "In Prep"
# to "In Circulation" in Mayo). This is the end time used for order-to-sample latency measurement.
# We may receive samples before they are confirmed (and see a confirmed date added later).
confirmed = Column('confirmed', UTCDateTime)
# Timestamp when Biobank received / created the sample.
# created = Column('created', UTCDateTime)
# sample status, includes all the statuses from SampleStatus Enum.
status = Column('status',
ModelEnum(SampleStatus),
default=SampleStatus.RECEIVED)
# Timestamp sample was disposed.
disposed = Column('disposed', UTCDateTime)
# Sample family ID
family_id = Column('family_id', String(80), nullable=True)
class BiobankOrderBase(ModelMixin):
"""An order requesting samples.
The order contains a list of samples stored in BiobankOrderedSample; the actual delivered and
stored samples are tracked in BiobankStoredSample. Our reconciliation report compares the two.
"""
_MAIN_ID_SYSTEM = 'https://orders.mayomedicallaboratories.com'
# A GUID for the order, provided by Biobank. This is the ID assigned in HealthPro, which is sent
# to us as an identifier with the mayomedicallaboritories.com "system".
biobankOrderId = Column('biobank_order_id', String(80), unique=True)
# Incrementing version, starts at 1 and is incremented on each update.
version = Column('version', Integer, nullable=False)
# The username / email of the HealthPro user that created the order -- createdInfo['author']
# in the resulting JSON.
sourceUsername = Column('source_username', String(255))
# The username / email of the HealthPro user that collected the order -- collectedInfo['author']
# in the resulting JSON.
collectedUsername = Column('collected_username', String(255))
# The username / email of the HealthPro user that processed the order -- processedInfo['author']
# in the resulting JSON.
processedUsername = Column('processed_username', String(255))
# The username / email of the HealthPro user that finalized the order -- finalizedInfo['author']
# in the resulting JSON.
finalizedUsername = Column('finalized_username', String(255))
# cancelled finalized order may still be shipped to biobank for destruction
# orderstatus can be cancelled/amended/restored
# A null value or UNSET == finalized (i.e. the current accepted value)
orderStatus = Column('order_status', ModelEnum(BiobankOrderStatus))
# a cancelled or edited order must have a reason. Set on the old row because cancelled orders
# don't create a new row like amended orders do.
amendedReason = Column('amended_reason', UnicodeText)
lastModified = Column('last_modified', UTCDateTime)
restoredUsername = Column('restored_username', String(255))
restoredTime = Column('restored_time', UTCDateTime)
amendedUsername = Column('amended_username', String(255))
amendedTime = Column('amended_time', UTCDateTime)
cancelledUsername = Column('cancelled_username', String(255))
cancelledTime = Column('cancelled_time', UTCDateTime)
# Additional fields stored for future use.
# created = Column('created', UTCDateTime, nullable=False)
collectedNote = Column('collected_note', UnicodeText)
processedNote = Column('processed_note', UnicodeText)
finalizedNote = Column('finalized_note', UnicodeText)
@declared_attr
def participantId(cls):
return Column('participant_id', String(20), ForeignKey(
'participant.participant_id'), nullable=False)
@declared_attr
def amendedBiobankOrderId(cls):
return Column('amended_biobank_order_id', String(80),
ForeignKey('biobank_order.biobank_order_id'))
# For syncing new orders.
@declared_attr
def logPositionId(cls):
return Column('log_position_id', Integer, ForeignKey(
'log_position.log_position_id'), nullable=False)
# The site that created the order -- createdInfo['site'] in the resulting JSON
@declared_attr
def sourceSiteId(cls):
return Column('source_site_id', Integer, ForeignKey('site.site_id'))
# The site that collected the order -- collectedInfo['site'] in the resulting JSON
@declared_attr
def collectedSiteId(cls):
return Column('collected_site_id', Integer, ForeignKey('site.site_id'))
# The site that processed the order -- processedInfo['site'] in the resulting JSON
@declared_attr
def processedSiteId(cls):
return Column('processed_site_id', Integer, ForeignKey('site.site_id'))
# The site that finalized the order -- finalizedInfo['site'] in the resulting JSON
@declared_attr
def finalizedSiteId(cls):
return Column('finalized_site_id', Integer, ForeignKey('site.site_id'))
@declared_attr
def restoredSiteId(cls):
return Column('restored_site_id', Integer, ForeignKey('site.site_id'))
@declared_attr
def amendedSiteId(cls):
return Column('amended_site_id', Integer, ForeignKey('site.site_id'))
@declared_attr
def cancelledSiteId(cls):
return Column('cancelled_site_id', Integer, ForeignKey('site.site_id'))
class ParticipantSummary(ModelMixin, BaseModel):
"""Summary fields extracted from participant data (combined from multiple tables).
Consented participants only."""
__tablename__ = 'participant_summary'
participantFk = Column('participant_fk',
ForeignKey('participant.id'),
unique=True)
participantId = Column('participant_id', String(20), unique=True)
biobankId = Column('biobank_id', Integer, nullable=False)
lastModified = Column('last_modified', UTCDateTime)
# PTC string fields will generally be limited to 255 chars; set our field lengths accordingly to
# ensure that long values can be inserted.
firstName = Column('first_name', String(255), nullable=False)
middleName = Column('middle_name', String(255))
lastName = Column('last_name', String(255), nullable=False)
zipCode = Column('zip_code', String(10))
stateId = Column('state_id', Integer, ForeignKey('code.code_id'))
city = Column('city', String(255))
streetAddress = Column('street_address', String(255))
streetAddress2 = Column('street_address2', String(255))
phoneNumber = Column('phone_number', String(80))
loginPhoneNumber = Column('login_phone_number', String(80))
email = Column('email', String(255))
primaryLanguage = Column('primary_language', String(80))
recontactMethodId = Column('recontact_method_id', Integer,
ForeignKey('code.code_id'))
# deprecated - will remove languageId in the future
languageId = Column('language_id', Integer, ForeignKey('code.code_id'))
dateOfBirth = Column('date_of_birth', Date)
genderIdentityId = Column('gender_identity_id', Integer,
ForeignKey('code.code_id'))
sexId = Column('sex_id', Integer, ForeignKey('code.code_id'))
sexualOrientationId = Column('sexual_orientation_id', Integer,
ForeignKey('code.code_id'))
educationId = Column('education_id', Integer, ForeignKey('code.code_id'))
incomeId = Column('income_id', Integer, ForeignKey('code.code_id'))
enrollmentStatus = Column('enrollment_status',
ModelEnum(EnrollmentStatus),
default=EnrollmentStatus.INTERESTED)
race = Column('race', ModelEnum(Race), default=Race.UNSET)
physicalMeasurementsStatus = Column(
'physical_measurements_status',
ModelEnum(PhysicalMeasurementsStatus),
default=PhysicalMeasurementsStatus.UNSET)
# The first time that physical measurements were submitted for the participant.
physicalMeasurementsTime = Column('physical_measurements_time',
UTCDateTime)
# The time that physical measurements were finalized (before submission to the RDR)
physicalMeasurementsFinalizedTime = Column(
'physical_measurements_finalized_time', UTCDateTime)
physicalMeasurementsCreatedSiteId = Column(
'physical_measurements_created_site_id', Integer,
ForeignKey('site.site_id'))
physicalMeasurementsFinalizedSiteId = Column(
'physical_measurements_finalized_site_id', Integer,
ForeignKey('site.site_id'))
signUpTime = Column('sign_up_time', UTCDateTime)
# The time that this participant become a member
enrollmentStatusMemberTime = Column('enrollment_status_member_time',
UTCDateTime)
# The time when we get the first stored sample
enrollmentStatusCoreStoredSampleTime = Column(
'enrollment_status_core_stored_sample_time', UTCDateTime)
# The time when we get a DNA order
enrollmentStatusCoreOrderedSampleTime = Column(
'enrollment_status_core_ordered_sample_time', UTCDateTime)
# Fields for which questionnaires have been submitted, and at what times.
consentForStudyEnrollment = Column('consent_for_study_enrollment',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
consentForStudyEnrollmentTime = Column('consent_for_study_enrollment_time',
UTCDateTime)
consentForElectronicHealthRecords = Column(
'consent_for_electronic_health_records',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
consentForElectronicHealthRecordsTime = Column(
'consent_for_electronic_health_records_time', UTCDateTime)
consentForDvElectronicHealthRecordsSharing = Column(
'consent_for_dv_electronic_health_records_sharing',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
consentForDvElectronicHealthRecordsSharingTime = Column(
'consent_for_dv_electronic_health_records_sharing_time', UTCDateTime)
consentForCABoR = Column('consent_for_cabor',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
consentForCABoRTime = Column('consent_for_cabor_time', UTCDateTime)
questionnaireOnOverallHealth = Column('questionnaire_on_overall_health',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
questionnaireOnOverallHealthTime = Column(
'questionnaire_on_overall_health_time', UTCDateTime)
questionnaireOnLifestyle = Column('questionnaire_on_lifestyle',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
questionnaireOnLifestyleTime = Column('questionnaire_on_lifestyle_time',
UTCDateTime)
questionnaireOnTheBasics = Column('questionnaire_on_the_basics',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
questionnaireOnTheBasicsTime = Column('questionnaire_on_the_basics_time',
UTCDateTime)
questionnaireOnHealthcareAccess = Column(
'questionnaire_on_healthcare_access',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
questionnaireOnHealthcareAccessTime = Column(
'questionnaire_on_healthcare_access_time', UTCDateTime)
questionnaireOnMedicalHistory = Column('questionnaire_on_medical_history',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
questionnaireOnMedicalHistoryTime = Column(
'questionnaire_on_medical_history_time', UTCDateTime)
questionnaireOnMedications = Column('questionnaire_on_medications',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
questionnaireOnMedicationsTime = Column(
'questionnaire_on_medications_time', UTCDateTime)
questionnaireOnFamilyHealth = Column('questionnaire_on_family_health',
ModelEnum(QuestionnaireStatus),
default=QuestionnaireStatus.UNSET)
questionnaireOnFamilyHealthTime = Column(
'questionnaire_on_family_health_time', UTCDateTime)
# Fields for which samples have been received, and at what times.
sampleStatus1SST8 = Column('sample_status_1sst8',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1SST8Time = Column('sample_status_1sst8_time', UTCDateTime)
sampleStatus2SST8 = Column('sample_status_2sst8',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus2SST8Time = Column('sample_status_2sst8_time', UTCDateTime)
sampleStatus1SS08 = Column('sample_status_1ss08',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1SS08Time = Column('sample_status_1ss08_time', UTCDateTime)
sampleStatus1PST8 = Column('sample_status_1pst8',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1PST8Time = Column('sample_status_1pst8_time', UTCDateTime)
sampleStatus2PST8 = Column('sample_status_2pst8',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus2PST8Time = Column('sample_status_2pst8_time', UTCDateTime)
sampleStatus1PS08 = Column('sample_status_1ps08',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1PS08Time = Column('sample_status_1ps08_time', UTCDateTime)
sampleStatus1HEP4 = Column('sample_status_1hep4',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1HEP4Time = Column('sample_status_1hep4_time', UTCDateTime)
sampleStatus1ED04 = Column('sample_status_1ed04',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1ED04Time = Column('sample_status_1ed04_time', UTCDateTime)
sampleStatus1ED10 = Column('sample_status_1ed10',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1ED10Time = Column('sample_status_1ed10_time', UTCDateTime)
sampleStatus2ED10 = Column('sample_status_2ed10',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus2ED10Time = Column('sample_status_2ed10_time', UTCDateTime)
sampleStatus1UR10 = Column('sample_status_1ur10',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1UR10Time = Column('sample_status_1ur10_time', UTCDateTime)
sampleStatus1UR90 = Column('sample_status_1ur90',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1UR90Time = Column('sample_status_1ur90_time', UTCDateTime)
sampleStatus1SAL = Column('sample_status_1sal',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1SALTime = Column('sample_status_1sal_time', UTCDateTime)
sampleStatus1SAL2 = Column('sample_status_1sal2',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1SAL2Time = Column('sample_status_1sal2_time', UTCDateTime)
sampleStatus1ED02 = Column('sample_status_1ed02',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1ED02Time = Column('sample_status_1ed02_time', UTCDateTime)
sampleStatus1CFD9 = Column('sample_status_1cfd9',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1CFD9Time = Column('sample_status_1cfd9_time', UTCDateTime)
sampleStatus1PXR2 = Column('sample_status_1pxr2',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
sampleStatus1PXR2Time = Column('sample_status_1pxr2_time', UTCDateTime)
# Fields for which samples have been ordered, and at what times.
sampleOrderStatus1SST8 = Column('sample_order_status_1sst8',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1SST8Time = Column('sample_order_status_1sst8_time',
UTCDateTime)
sampleOrderStatus2SST8 = Column('sample_order_status_2sst8',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus2SST8Time = Column('sample_order_status_2sst8_time',
UTCDateTime)
sampleOrderStatus1SS08 = Column('sample_order_status_1ss08',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1SS08Time = Column('sample_order_status_1ss08_time',
UTCDateTime)
sampleOrderStatus1PST8 = Column('sample_order_status_1pst8',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1PST8Time = Column('sample_order_status_1pst8_time',
UTCDateTime)
sampleOrderStatus2PST8 = Column('sample_order_status_2pst8',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus2PST8Time = Column('sample_order_status_2pst8_time',
UTCDateTime)
sampleOrderStatus1PS08 = Column('sample_order_status_1ps08',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1PS08Time = Column('sample_order_status_1ps08_time',
UTCDateTime)
sampleOrderStatus1HEP4 = Column('sample_order_status_1hep4',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1HEP4Time = Column('sample_order_status_1hep4_time',
UTCDateTime)
sampleOrderStatus1ED04 = Column('sample_order_status_1ed04',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1ED04Time = Column('sample_order_status_1ed04_time',
UTCDateTime)
sampleOrderStatus1ED10 = Column('sample_order_status_1ed10',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1ED10Time = Column('sample_order_status_1ed10_time',
UTCDateTime)
sampleOrderStatus2ED10 = Column('sample_order_status_2ed10',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus2ED10Time = Column('sample_order_status_2ed10_time',
UTCDateTime)
sampleOrderStatus1UR10 = Column('sample_order_status_1ur10',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1UR10Time = Column('sample_order_status_1ur10_time',
UTCDateTime)
sampleOrderStatus1UR90 = Column('sample_order_status_1ur90',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1UR90Time = Column('sample_order_status_1ur90_time',
UTCDateTime)
sampleOrderStatus1SAL = Column('sample_order_status_1sal',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1SALTime = Column('sample_order_status_1sal_time',
UTCDateTime)
sampleOrderStatus1SAL2 = Column('sample_order_status_1sal2',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1SAL2Time = Column('sample_order_status_1sal2_time',
UTCDateTime)
sampleOrderStatus1ED02 = Column('sample_order_status_1ed02',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1ED02Time = Column('sample_order_status_1ed02_time',
UTCDateTime)
sampleOrderStatus1CFD9 = Column('sample_order_status_1cfd9',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1CFD9Time = Column('sample_order_status_1cfd9_time',
UTCDateTime)
sampleOrderStatus1PXR2 = Column('sample_order_status_1pxr2',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
sampleOrderStatus1PXR2Time = Column('sample_order_status_1pxr2_time',
UTCDateTime)
numCompletedBaselinePPIModules = Column(
'num_completed_baseline_ppi_modules', SmallInteger, default=0)
numCompletedPPIModules = Column('num_completed_ppi_modules',
SmallInteger,
default=0)
# The number of BiobankStoredSamples recorded for this participant, limited to those samples
# where testCode is one of the baseline tests (listed in the config).
numBaselineSamplesArrived = Column('num_baseline_samples_arrived',
SmallInteger,
default=0)
samplesToIsolateDNA = Column('samples_to_isolate_dna',
ModelEnum(SampleStatus),
default=SampleStatus.UNSET)
# Whether biospecimens have been finalized or not, and the time at which they were
# finalized.
biospecimenStatus = Column('biospecimen_status',
ModelEnum(OrderStatus),
default=OrderStatus.UNSET)
biospecimenOrderTime = Column('biospecimen_order_time', UTCDateTime)
biospecimenSourceSiteId = Column('biospecimen_source_site_id', Integer,
ForeignKey('site.site_id'))
biospecimenCollectedSiteId = Column('biospecimen_collected_site_id',
Integer, ForeignKey('site.site_id'))
biospecimenProcessedSiteId = Column('biospecimen_processed_site_id',
Integer, ForeignKey('site.site_id'))
biospecimenFinalizedSiteId = Column('biospecimen_finalized_site_id',
Integer, ForeignKey('site.site_id'))
# Withdrawal from the study of the participant's own accord.
withdrawalStatus = Column('withdrawal_status',
ModelEnum(WithdrawalStatus),
nullable=False)
withdrawalReason = Column('withdrawal_reason', ModelEnum(WithdrawalReason))
withdrawalTime = Column('withdrawal_time', UTCDateTime)
withdrawalReasonJustification = Column('withdrawal_reason_justification',
UnicodeText)
suspensionStatus = Column('suspension_status',
ModelEnum(SuspensionStatus),
nullable=False)
suspensionTime = Column('suspension_time', UTCDateTime)
participant = relationship("Participant",
back_populates="participantSummary")
@declared_attr
def hpoId(cls):
return Column('hpo_id',
Integer,
ForeignKey('hpo.hpo_id'),
nullable=False)
@declared_attr
def organizationId(cls):
return Column('organization_id', Integer,
ForeignKey('organization.organization_id'))
@declared_attr
def siteId(cls):
return Column('site_id', Integer, ForeignKey('site.site_id'))
class Calendar(ModelMixin, BaseModel):
__tablename__ = 'calendar'
day = Column('day', Date, unique=True)
enrollmentStatus = Column('enrollment_status', ModelEnum(EnrollmentStatus))
SampleStatus = Column('sample_status', ModelEnum(SampleStatus))
class ParticipantBase(ModelMixin):
"""Mixin with shared columns for Participant and ParticipantHistory"""
# Randomly assigned internal ID. We tack 'P' on the front whenever we use this externally.
participantId = Column('participant_id', String(20), unique=True)
# Assigned ID from PTSC. Recieved in request to create a new Participant.
externalId = Column('external_id', BigInteger, unique=True)
# Incrementing version, starts at 1 and is incremented on each update.
version = Column('version', Integer, nullable=False)
# Randomly assigned ID used with Biobank. Prefixed with 'B' whenever we use this externally.
biobankId = Column('biobank_id', Integer, nullable=False)
lastModified = Column('last_modified', UTCDateTime, nullable=False)
signUpTime = Column('sign_up_time', UTCDateTime, nullable=False)
# One or more HPO IDs in FHIR JSON. (The primary link is separately stored as hpoId.)
providerLink = Column('provider_link', BLOB)
# Both HealthPro and PTC can mutate participants; we use clientId to track
# which system did it. An client ID of [email protected] means we created fake data for this
# participant.
clientId = Column('client_id', String(80))
# Default values for withdrawal and suspension are managed through the DAO (instead of column
# defaults here) to simplify insert v. update semantics.
# Withdrawal is permanent, and indicates we should neither contact the participant nor use their
# data in the future.
withdrawalStatus = Column('withdrawal_status',
ModelEnum(WithdrawalStatus),
nullable=False)
# The time at which the participants set their withdrawal status to NO_USE.
withdrawalTime = Column('withdrawal_time', UTCDateTime)
withdrawalReason = Column('withdrawal_reason', ModelEnum(WithdrawalReason))
withdrawalReasonJustification = Column('withdrawal_reason_justification',
UnicodeText)
# Suspension may be temporary, and indicates we should not contact the participant but may
# continue using their data.
suspensionStatus = Column('suspension_status',
ModelEnum(SuspensionStatus),
nullable=False)
# The time at which the participant set their suspension status to NO_CONTACT.
suspensionTime = Column('suspension_time', UTCDateTime)
# If a participant is deemed to be a "ghost" i.e. not real or empty participant obj.
isGhostId = Column('is_ghost_id', Boolean)
# The date the participant was marked as ghost
dateAddedGhost = Column('date_added_ghost', UTCDateTime)
@declared_attr
def hpoId(cls):
return Column('hpo_id',
Integer,
ForeignKey('hpo.hpo_id'),
nullable=False)
@declared_attr
def organizationId(cls):
return Column('organization_id', Integer,
ForeignKey('organization.organization_id'))
@declared_attr
def siteId(cls):
return Column('site_id', Integer, ForeignKey('site.site_id'))