# Castor EDC schema ERD

Table and field reference for the Fivetran Castor EDC connector.

## Table & Field Reference

### participant

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| created_by | Unknown | FK |
| site_id | Unknown | FK |
| updated_by | Unknown | FK |
| archived | Unknown |  |
| archived_reason | Unknown |  |
| ccr_patient_id | Unknown |  |
| created_on_date | Unknown |  |
| created_on_timezone | Unknown |  |
| created_on_timezone_type | Unknown |  |
| deleted | Unknown |  |
| last_opened_step | Unknown |  |
| locked | Unknown |  |
| participant_status_id | Unknown |  |
| participant_status_name | Unknown |  |
| progress | Unknown |  |
| randomization_group | Unknown |  |
| randomization_group_name | Unknown |  |
| randomized_id | Unknown |  |
| randomized_on_date | Unknown |  |
| randomized_on_timezone | Unknown |  |
| randomized_on_timezone_type | Unknown |  |
| status | Unknown |  |
| updated_on_date | Unknown |  |
| updated_on_timezone | Unknown |  |
| updated_on_timezone_type | Unknown |  |

### repeating_data_instance_data_point

| Column | Type | Keys |
| --- | --- | --- |
| field_id | Unknown | PK, FK |
| participant_id | Unknown | PK, FK |
| repeating_data_instance_id | Unknown | PK, FK |
| study_id | Unknown | PK, FK |
| field_value | Unknown |  |
| repeating_data_instance_name | Unknown |  |
| updated_on | Unknown |  |

### institute_permission

| Column | Type | Keys |
| --- | --- | --- |
| institute_id | Unknown | PK, FK |
| study_user_id | Unknown | PK, FK |
| add | Unknown |  |
| decrypt | Unknown |  |
| delete | Unknown |  |
| edit | Unknown |  |
| email_addresses | Unknown |  |
| encrypts | Unknown |  |
| export | Unknown |  |
| lock | Unknown |  |
| query | Unknown |  |
| randomization_read | Unknown |  |
| randomization_write | Unknown |  |
| role | Unknown |  |
| sdv | Unknown |  |
| sign | Unknown |  |
| survey_send | Unknown |  |
| survey_view | Unknown |  |
| televisit | Unknown |  |
| view | Unknown |  |

### study_user

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| department | Unknown |  |
| email_address | Unknown |  |
| full_name | Unknown |  |
| institute | Unknown |  |
| last_login | Unknown |  |
| manage_permissions_manage_encryption | Unknown |  |
| manage_permissions_manage_form | Unknown |  |
| manage_permissions_manage_records | Unknown |  |
| manage_permissions_manage_settings | Unknown |  |
| manage_permissions_manage_users | Unknown |  |
| name_first | Unknown |  |
| name_last | Unknown |  |
| name_middle | Unknown |  |

### study

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| created_by | Unknown | FK |
| main_contact | Unknown | FK |
| created_on | Unknown |  |
| domain | Unknown |  |
| duration | Unknown |  |
| expected_centers | Unknown |  |
| expected_records | Unknown |  |
| gcp_enabled | Unknown |  |
| live | Unknown |  |
| name | Unknown |  |
| premium_support_enabled | Unknown |  |
| randomization_enabled | Unknown |  |
| slug | Unknown |  |
| surveys_enabled | Unknown |  |
| trial_registry_id | Unknown |  |
| version | Unknown |  |

### role

| Column | Type | Keys |
| --- | --- | --- |
| name | Unknown | PK |
| study_id | Unknown | PK, FK |
| description | Unknown |  |

### country

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| country_cca_2 | Unknown |  |
| country_cca_3 | Unknown |  |
| country_name | Unknown |  |
| country_tld | Unknown |  |

### field_dependency

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| parent_id | Unknown | FK |
| child_id | Unknown |  |
| description | Unknown |  |
| field_dependency_type | Unknown |  |
| operator | Unknown |  |
| value | Unknown |  |

### repeating_data

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| description | Unknown |  |
| name | Unknown |  |
| type | Unknown |  |

### study_statistics_institute

| Column | Type | Keys |
| --- | --- | --- |
| institute_id | Unknown | PK |
| study_statistics_study_id | Unknown | PK, FK |
| institute_name | Unknown |  |
| record_count | Unknown |  |

### study_statistics

| Column | Type | Keys |
| --- | --- | --- |
| study_id | Unknown | PK, FK |
| records_total_count | Unknown |  |

### field_option

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| field_option_group_id | Unknown | PK, FK |
| group_order | Unknown |  |
| name | Unknown |  |
| value | Unknown |  |

### field_option_group

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| description | Unknown |  |
| layout | Unknown |  |
| name | Unknown |  |

### participant_progress_form

| Column | Type | Keys |
| --- | --- | --- |
| form_id | Unknown | PK, FK |
| participant_progress_participant_id | Unknown | PK, FK |
| participant_progress_study_id | Unknown | PK, FK |
| complete | Unknown |  |
| locked | Unknown |  |
| progress | Unknown |  |
| sdv | Unknown |  |
| signed | Unknown |  |

### repeating_data_form

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| repeating_data_id | Unknown | FK |
| repeating_data_form_description | Unknown |  |
| repeating_data_form_name | Unknown |  |
| repeating_data_form_number | Unknown |  |

### site

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| country_id | Unknown | FK |
| abbreviation | Unknown |  |
| code | Unknown |  |
| date_format | Unknown |  |
| deleted | Unknown |  |
| name | Unknown |  |
| orders | Unknown |  |

### survey_instance

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK, FK |
| study_id | Unknown | PK, FK |
| field_id | Unknown |  |
| field_value | Unknown |  |
| participant_id | Unknown |  |
| survey_name | Unknown |  |
| updated_on | Unknown |  |

### form

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| visit_id | Unknown | FK |
| form_description | Unknown |  |
| form_name | Unknown |  |
| form_order | Unknown |  |

### survey_package_instance_survey_instance

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| survey_package_instance_id | Unknown | PK, FK |
| survey_id | Unknown | FK |
| progress | Unknown |  |
| progress_total_fields | Unknown |  |
| progress_total_fields_not_empty | Unknown |  |

### survey_package_instance

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| parent_id | Unknown | FK |
| participant_id | Unknown | FK |
| site_id | Unknown | FK |
| survey_package_id | Unknown | FK |
| all_fields_filled_on_date | Unknown |  |
| all_fields_filled_on_timezone | Unknown |  |
| all_fields_filled_on_timezone_type | Unknown |  |
| archived | Unknown |  |
| auto_lock_on_finish | Unknown |  |
| auto_send | Unknown |  |
| available_from_date | Unknown |  |
| available_from_timezone | Unknown |  |
| available_from_timezone_type | Unknown |  |
| created_by | Unknown |  |
| created_on_date | Unknown |  |
| created_on_timezone | Unknown |  |
| created_on_timezone_type | Unknown |  |
| expire_on_date | Unknown |  |
| expire_on_timezone | Unknown |  |
| expire_on_timezone_type | Unknown |  |
| finished_on_date | Unknown |  |
| finished_on_timezone | Unknown |  |
| finished_on_timezone_type | Unknown |  |
| first_opened_on_date | Unknown |  |
| first_opened_on_timezone | Unknown |  |
| first_opened_on_timezone_type | Unknown |  |
| invitation_content | Unknown |  |
| invitation_subject | Unknown |  |
| locked | Unknown |  |
| parent_type | Unknown |  |
| progress | Unknown |  |
| received_on_date | Unknown |  |
| received_on_timezone | Unknown |  |
| received_on_timezone_type | Unknown |  |
| sent_on_date | Unknown |  |
| sent_on_timezone | Unknown |  |
| sent_on_timezone_type | Unknown |  |
| site_name | Unknown |  |
| started_on_date | Unknown |  |
| started_on_timezone | Unknown |  |
| started_on_timezone_type | Unknown |  |
| survey_package_instance_id | Unknown |  |
| survey_package_name | Unknown |  |
| survey_reminders_send_ondate | Unknown |  |
| survey_reminders_send_ontimezone | Unknown |  |
| survey_reminders_send_ontimezone_type | Unknown |  |
| survey_remindersid | Unknown |  |
| survey_remindersmessage | Unknown |  |
| survey_reminderssubject | Unknown |  |
| survey_url_string | Unknown |  |

### meta_data

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| field_id | Unknown | PK, FK |
| parent_id | Unknown | FK |
| _embedded_metadata_type_description | Unknown |  |
| _embedded_metadata_type_id | Unknown |  |
| _embedded_metadata_type_name | Unknown |  |
| description | Unknown |  |
| value | Unknown |  |

### field_field_option_group

| Column | Type | Keys |
| --- | --- | --- |
| field_id | Unknown | PK, FK |
| id | Unknown | PK, FK |

### field_field_validation

| Column | Type | Keys |
| --- | --- | --- |
| field_id | Unknown | PK, FK |
| id | Unknown | PK, FK |

### field

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| parent_id | Unknown | FK |
| additional_config | Unknown |  |
| dependency_children | Json |  |
| dependency_parents | Json |  |
| exclude_on_data_export | Unknown |  |
| field_enforce_decimals | Unknown |  |
| field_hidden | Unknown |  |
| field_image | Unknown |  |
| field_info | Unknown |  |
| field_label | Unknown |  |
| field_length | Unknown |  |
| field_max | Unknown |  |
| field_max_label | Unknown |  |
| field_min | Unknown |  |
| field_min_label | Unknown |  |
| field_number | Unknown |  |
| field_required | Unknown |  |
| field_slider_step | Unknown |  |
| field_slider_step_value | Unknown |  |
| field_summary_template | Unknown |  |
| field_type | Unknown |  |
| field_units | Unknown |  |
| field_variable_name | Unknown |  |
| randomize_option_order | Unknown |  |
| report_id | Unknown |  |

### survey_form

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| survey_id | Unknown | FK |
| field_alignment | Unknown |  |
| form_numbering | Unknown |  |
| label_bold_if_required | Unknown |  |
| survey_form_description | Unknown |  |
| survey_form_name | Unknown |  |
| survey_form_number | Unknown |  |

### survey_survey_form

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK, FK |
| survey_id | Unknown | PK, FK |

### survey

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| description | Unknown |  |
| intro_text | Unknown |  |
| name | Unknown |  |
| outro_text | Unknown |  |

### repeating_data_instance

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| created_by | Unknown | FK |
| parent_id | Unknown | FK |
| participant_id | Unknown | FK |
| repeating_data_id | Unknown | FK |
| archived | Unknown |  |
| created_on | Unknown |  |
| name | Unknown |  |
| parent_type | Unknown |  |
| repeating_data_name | Unknown |  |
| status | Unknown |  |

### study_data_entry

| Column | Type | Keys |
| --- | --- | --- |
| field_id | Unknown | PK, FK |
| participant_id | Unknown | PK, FK |
| study_id | Unknown | PK, FK |
| field_variable_name | Unknown |  |
| updated_on | Unknown |  |
| value | Unknown |  |

### randomization

| Column | Type | Keys |
| --- | --- | --- |
| randomized_id | Unknown | PK |
| participant_id | Unknown | PK, FK |
| study_id | Unknown | PK, FK |
| randomization_group | Unknown |  |
| randomization_group_name | Unknown |  |
| randomized_on_date | Unknown |  |
| randomized_on_timezone | Unknown |  |
| randomized_on_timezone_type | Unknown |  |

### visit

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| visit_description | Unknown |  |
| visit_duration | Unknown |  |
| visit_name | Unknown |  |
| visit_order | Unknown |  |

### query_remark

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| query_id | Unknown | PK, FK |
| created_by | Unknown | FK |
| created_on_date | Unknown |  |
| created_on_timezone | Unknown |  |
| created_on_timezone_type | Unknown |  |
| description | Unknown |  |
| title | Unknown |  |

### query

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| created_by | Unknown | FK |
| participant_id | Unknown | FK |
| updated_by | Unknown | FK |
| created_on_date | Unknown |  |
| created_on_timezone | Unknown |  |
| created_on_timezone_type | Unknown |  |
| field_id | Unknown |  |
| first_query_remark | Unknown |  |
| record_id | Unknown |  |
| status | Unknown |  |
| updated_on_date | Unknown |  |
| updated_on_timezone | Unknown |  |
| updated_on_timezone_type | Unknown |  |

### survey_package_survey

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK, FK |
| survey_package_id | Unknown | PK, FK |

### survey_package

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| allow_open_survey_link | Unknown |  |
| allow_step_navigation | Unknown |  |
| as_needed | Unknown |  |
| auto_lock_on_finish | Unknown |  |
| auto_send | Unknown |  |
| default_invitation | Unknown |  |
| default_invitation_subject | Unknown |  |
| description | Unknown |  |
| expire_after_hours | Unknown |  |
| field_pagination | Unknown |  |
| finish_url | Unknown |  |
| intro_text | Unknown |  |
| is_mobile | Unknown |  |
| is_repeatable | Unknown |  |
| is_resumable | Unknown |  |
| is_training | Unknown |  |
| name | Unknown |  |
| outro_text | Unknown |  |
| sender_email | Unknown |  |
| sender_name | Unknown |  |
| show_step_navigator | Unknown |  |
| survey_package_id | Unknown |  |

### field_validation

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| field_id | Unknown | FK |
| operator | Unknown |  |
| text | Unknown |  |
| type | Unknown |  |
| value | Unknown |  |

### verification

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| study_id | Unknown | PK, FK |
| parent_id | Unknown | FK |
| changed_field | Unknown |  |
| dropped_by | Unknown |  |
| dropped_on_date | Unknown |  |
| dropped_on_timezone | Unknown |  |
| dropped_on_timezone_type | Unknown |  |
| entity_id | Unknown |  |
| entity_type | Unknown |  |
| record_id | Unknown |  |
| status | Unknown |  |
| verification_type_id | Unknown |  |
| verification_type_is_sdv | Unknown |  |
| verification_type_name | Unknown |  |
| verified_by | Unknown |  |
| verified_on_date | Unknown |  |
| verified_on_timezone | Unknown |  |
| verified_on_timezone_type | Unknown |  |

## Relationships

- participant(created_by) → study_user(id)
- participant(updated_by) → study_user(id)
- participant(site_id) → site(id)
- participant(study_id) → study(id)
- repeating_data_instance_data_point(repeating_data_instance_id) → repeating_data_instance(id)
- repeating_data_instance_data_point(participant_id) → participant(id)
- repeating_data_instance_data_point(field_id) → field(id)
- repeating_data_instance_data_point(study_id) → study(id)
- institute_permission(institute_id) → study_statistics_institute(institute_id)
- institute_permission(study_user_id) → study_user(id)
- study_user(study_id) → study(id)
- study(created_by) → study_user(id)
- study(main_contact) → study_user(id)
- role(study_id) → study(id)
- field_dependency(parent_id) → field_dependency(id)
- field_dependency(study_id) → study(id)
- repeating_data(study_id) → study(id)
- study_statistics_institute(study_statistics_study_id) → study_statistics(study_id)
- study_statistics(study_id) → study(id)
- field_option(field_option_group_id) → field_option_group(id)
- field_option_group(study_id) → study(id)
- participant_progress_form(form_id) → form(id)
- participant_progress_form(participant_progress_participant_id) → participant(id)
- participant_progress_form(participant_progress_study_id) → study(id)
- repeating_data_form(repeating_data_id) → repeating_data(id)
- repeating_data_form(study_id) → study(id)
- site(country_id) → country(id)
- site(study_id) → study(id)
- survey_instance(id) → participant(id)
- survey_instance(study_id) → study(id)
- form(visit_id) → visit(id)
- form(study_id) → study(id)
- survey_package_instance_survey_instance(survey_id) → survey(id)
- survey_package_instance_survey_instance(survey_package_instance_id) → survey_package_instance(id)
- survey_package_instance(participant_id) → participant(id)
- survey_package_instance(parent_id) → survey_package_instance(id)
- survey_package_instance(survey_package_id) → survey_package(id)
- survey_package_instance(site_id) → site(id)
- survey_package_instance(study_id) → study(id)
- meta_data(parent_id) → meta_data(id)
- meta_data(field_id) → field(id)
- field_field_option_group(id) → field_option_group(id)
- field_field_option_group(field_id) → field(id)
- field_field_validation(id) → field_validation(id)
- field_field_validation(field_id) → field(id)
- field(parent_id) → field(id)
- field(study_id) → study(id)
- survey_form(survey_id) → survey(id)
- survey_form(study_id) → study(id)
- survey_survey_form(id) → survey_form(id)
- survey_survey_form(survey_id) → survey(id)
- survey(study_id) → study(id)
- repeating_data_instance(created_by) → study_user(id)
- repeating_data_instance(participant_id) → participant(id)
- repeating_data_instance(parent_id) → repeating_data_instance(id)
- repeating_data_instance(repeating_data_id) → repeating_data(id)
- repeating_data_instance(study_id) → study(id)
- study_data_entry(participant_id) → participant(id)
- study_data_entry(field_id) → field(id)
- study_data_entry(study_id) → study(id)
- randomization(study_id) → study(id)
- randomization(participant_id) → participant(id)
- visit(study_id) → study(id)
- query_remark(created_by) → study_user(id)
- query_remark(query_id) → query(id)
- query(participant_id) → participant(id)
- query(created_by) → study_user(id)
- query(updated_by) → study_user(id)
- query(study_id) → study(id)
- survey_package_survey(id) → survey(id)
- survey_package_survey(survey_package_id) → survey_package(id)
- survey_package(study_id) → study(id)
- field_validation(field_id) → field(id)
- field_validation(study_id) → study(id)
- verification(parent_id) → verification(id)
- verification(study_id) → study(id)