De-identification of OpenSpecimen data

In most centers using OpenSpecimen, the application is hosted on two servers i.e. test and production. System administrators set up test servers as a replica of production for training and debugging purposes. But to secure the data, the PHI or the patient identifiable information needs to be masked out before copying production data to test server.

Follow below steps to de-identify the PHI data as well as make the data size smaller by removing audit data:

Make a copy of production DB
Run below SQLs on the copy
Export the data dump and import into test server.

SQLs:

Click here to expand...

update catissue_participant set LAST_NAME=null, FIRST_NAME=null, MIDDLE_NAME=null, BIRTH_DATE=null, SOCIAL_SECURITY_NUMBER=null, DEATH_DATE=null, EMPI_ID=null;

update catissue_part_medical_id set MEDICAL_RECORD_NUMBER=null;

update catissue_specimen_coll_group set SURGICAL_PATHOLOGY_NUMBER=null;

TRUNCATE TABLE cat_collection_protocol_aud;

TRUNCATE TABLE cat_consent_tier_response_aud;

TRUNCATE TABLE cat_specimen_coll_group_aud;

TRUNCATE TABLE catissue_coll_coordinators_aud;

TRUNCATE TABLE catissue_coll_prot_event_aud;

TRUNCATE TABLE catissue_coll_prot_reg_aud;

TRUNCATE TABLE catissue_consent_tier_aud;

TRUNCATE TABLE catissue_cp_req_specimen_aud;

TRUNCATE TABLE catissue_institution_aud;

TRUNCATE TABLE catissue_part_medical_id_aud;

TRUNCATE TABLE catissue_participant_aud;

TRUNCATE TABLE catissue_race_aud;

TRUNCATE TABLE catissue_site_aud;

TRUNCATE TABLE catissue_site_coordinators_aud;

TRUNCATE TABLE catissue_site_cp_aud;

TRUNCATE TABLE catissue_specimen_aud;

TRUNCATE TABLE catissue_user_aud;

TRUNCATE TABLE os_container_positions_aud;

TRUNCATE TABLE os_departments_aud;

TRUNCATE TABLE os_specimen_biohazards_aud;

TRUNCATE TABLE os_stor_cont_spec_classes_aud;

TRUNCATE TABLE os_stor_cont_spec_types_aud;

TRUNCATE TABLE os_stor_container_cps_aud;

TRUNCATE TABLE os_storage_containers_aud;

TRUNCATE TABLE cat_collection_protocol_aud;

TRUNCATE TABLE cat_consent_tier_response_aud;

TRUNCATE TABLE cat_distribution_protocol_aud;

TRUNCATE TABLE cat_specimen_coll_group_aud;

TRUNCATE TABLE catissue_coll_coordinators_aud;

TRUNCATE TABLE catissue_coll_prot_event_aud;

TRUNCATE TABLE catissue_coll_prot_reg_aud;

TRUNCATE TABLE catissue_consent_tier_aud;

TRUNCATE TABLE catissue_cp_req_specimen_aud;

TRUNCATE TABLE catissue_institution_aud;

TRUNCATE TABLE catissue_part_medical_id_aud;

TRUNCATE TABLE catissue_participant_aud;

TRUNCATE TABLE catissue_race_aud;

TRUNCATE TABLE catissue_site_aud;

TRUNCATE TABLE catissue_site_coordinators_aud;

TRUNCATE TABLE catissue_site_cp_aud;

TRUNCATE TABLE catissue_specimen_aud;

TRUNCATE TABLE catissue_user_aud;

TRUNCATE TABLE os_consent_statements_aud;

TRUNCATE TABLE os_container_positions_aud;

TRUNCATE TABLE os_container_types_aud;

TRUNCATE TABLE os_departments_aud;

TRUNCATE TABLE os_dist_protocol_sites_aud;

TRUNCATE TABLE os_dp_coordinators_aud;

TRUNCATE TABLE os_dp_requirements_aud;

TRUNCATE TABLE os_dpr_pathology_statuses_aud;

TRUNCATE TABLE os_order_items_aud;

TRUNCATE TABLE os_orders_aud;

TRUNCATE TABLE os_shipment_items_aud;

TRUNCATE TABLE os_shipment_notify_users_aud;

TRUNCATE TABLE os_shipments_aud;

TRUNCATE TABLE os_specimen_biohazards_aud;

TRUNCATE TABLE os_specimen_kits_aud;

TRUNCATE TABLE os_specimen_request_items_aud;

TRUNCATE TABLE os_specimen_requests_aud;

TRUNCATE TABLE os_spmn_lbl_print_settings_aud;

TRUNCATE TABLE os_stor_cont_spec_classes_aud;

TRUNCATE TABLE os_stor_cont_spec_types_aud;

TRUNCATE TABLE os_stor_container_cps_aud;

TRUNCATE TABLE os_storage_containers_aud;

TRUNCATE TABLE catissue_query_audit_logs;

TRUNCATE TABLE os_login_audit_logs;

TRUNCATE TABLE os_revision_entities;

TRUNCATE TABLE os_revisions;

TRUNCATE TABLE CATISSUE_AUDIT_EVENT;

TRUNCATE TABLE CATISSUE_AUDIT_EVENT_DETAILS;

TRUNCATE TABLE CATISSUE_AUDIT_EVENT_LOG;

TRUNCATE TABLE CATISSUE_AUDIT_EVENT_QUERY_LOG;

TRUNCATE TABLE CATISSUE_LOGIN_AUDIT_EVENT_LOG;

TRUNCATE TABLE CATISSUE_DATA_AUDIT_EVENT_LOG;