# Gem schema ERD

Table and field reference for the Fivetran Gem connector.

## Table & Field Reference

### project_value_option

| Column | Type | Keys |
| --- | --- | --- |
| value_option_id | Unknown | PK |
| field_project_id | Unknown | PK, FK |
| project_field_index | Unknown | PK, FK |

### project_field

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| project_id | Unknown | PK, FK |
| id | Unknown |  |
| name | Unknown |  |
| value | Unknown |  |
| value_type | Unknown |  |

### project

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| user_id | Unknown | FK |
| context | Unknown |  |
| created_at | Unknown |  |
| description | Unknown |  |
| is_archived | Unknown |  |
| name | Unknown |  |
| privacy_type | Unknown |  |

### candidate_custom_value_option

| Column | Type | Keys |
| --- | --- | --- |
| candidate_custom_field_index | Unknown | PK, FK |
| custom_field_candidate_id | Unknown | PK, FK |
| custom_value_option_id | Unknown | PK, FK |

### candidate_custom_field

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| candidate_id | Unknown | PK, FK |
| id | Unknown | FK |
| project_id | Unknown | FK |

### candidate_work_info

| Column | Type | Keys |
| --- | --- | --- |
| _fivetran_id | String | PK |
| candidate_id | Unknown | FK |
| company | Unknown |  |
| is_current | Unknown |  |
| title | Unknown |  |
| work_end_date | Unknown |  |
| work_start_date | Unknown |  |

### candidate_education_info

| Column | Type | Keys |
| --- | --- | --- |
| _fivetran_id | String | PK |
| candidate_id | Unknown | FK |
| degree | Unknown |  |
| end_date | Unknown |  |
| field_of_study | Unknown |  |
| school | Unknown |  |
| start_date | Unknown |  |

### candidate_profile

| Column | Type | Keys |
| --- | --- | --- |
| _fivetran_id | String | PK |
| candidate_id | Unknown | FK |
| network | Unknown |  |
| url | Unknown |  |
| user_name | Unknown |  |

### candidate_email

| Column | Type | Keys |
| --- | --- | --- |
| _fivetran_id | String | PK |
| candidate_id | Unknown | FK |
| email_address | Unknown |  |
| is_primary | Unknown |  |

### candidate_project

| Column | Type | Keys |
| --- | --- | --- |
| candidate_id | Unknown | PK, FK |
| project_id | Unknown | PK, FK |

### candidate

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| created_by | Unknown | FK |
| user_id | Unknown | FK |
| candidate_greenhouse_id | Unknown |  |
| company | Unknown |  |
| created_at | Unknown |  |
| due_date | Unknown |  |
| first_name | Unknown |  |
| last_name | Unknown |  |
| linked_in_handle | Unknown |  |
| location | Unknown |  |
| nickname | Unknown |  |
| note | Unknown |  |
| phone_number | Unknown |  |
| school | Unknown |  |
| sourced_from | Unknown |  |
| title | Unknown |  |
| weblink | Unknown |  |

### ats_office

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| child_department_external_id | Json |  |
| child_id | Json |  |
| deleted_at | Instant |  |
| location_name | Unknown |  |
| name | Unknown |  |
| parent_id | Unknown |  |
| parent_office_external_id | Unknown |  |

### custom_field_option

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| custom_field_id | Unknown | PK, FK |
| is_hidden | Unknown |  |
| value | Unknown |  |

### custom_field

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| project_id | Unknown | FK |
| created_at | Unknown |  |
| is_hidden | Unknown |  |
| name | Unknown |  |
| scope | Unknown |  |
| value_type | Unknown |  |

### users

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| email | Unknown |  |
| name | Unknown |  |

### sequence

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| user_id | Unknown | FK |
| created_at | Instant |  |
| name | Unknown |  |

### candidate_note

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| candidate_id | Unknown | FK |
| user_id | Unknown | FK |
| content | Unknown |  |
| is_private | Unknown |  |
| time_stamp | Unknown |  |

### candidate_uploaded_resumes

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| candidate_id | Unknown | FK |
| user_id | Unknown | FK |
| created_at | Unknown |  |
| download_url | Unknown |  |
| file_name | Unknown |  |

### ats_scheduled_interviewer

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_scheduled_interview_id | Unknown | PK, FK |
| scorecard_id | Unknown | FK |
| email | Unknown |  |
| employee_id | Unknown |  |
| name | Unknown |  |
| response_status | Unknown |  |

### ats_scheduled_interview_question

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_scheduled_interview_id | Unknown | PK, FK |
| answer | Unknown |  |
| option | Json |  |
| question | Unknown |  |
| type | Unknown |  |

### ats_scheduled_interview

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_application_id | Unknown | FK |
| ats_candidate_id | Unknown | FK |
| organizer_id | Unknown | FK |
| created_at | Instant |  |
| end_date_time | Instant |  |
| external_event_id | Unknown |  |
| interview_id | Unknown |  |
| interview_name | Unknown |  |
| job_stage_interview_item_id | Unknown |  |
| location | Unknown |  |
| start_date_time | Instant |  |
| status | Unknown |  |
| updated_at | Instant |  |
| video_conferencing_url | Unknown |  |

### ats_application_job

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_application_id | Unknown | PK, FK |
| name | Unknown |  |

### ats_application

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_candidate_id | Unknown | FK |
| ats_job_post_id | Unknown | FK |
| applied_at | Instant |  |
| coordinator_email | Unknown |  |
| coordinator_id | Unknown |  |
| credited_to | Unknown |  |
| current_stage_id | Unknown |  |
| current_stage_name | Unknown |  |
| deleted_at | Instant |  |
| last_activity_at | Instant |  |
| recruiter_email | Unknown |  |
| recruiter_id | Unknown |  |
| rejected_at | Instant |  |
| rejection_reason_id | Unknown |  |
| rejection_reason_name | Unknown |  |
| rejection_reason_type | Json |  |
| source_public_name | Unknown |  |
| source_user_id | Unknown |  |
| sourcer_email | Unknown |  |
| sourcer_id | Unknown |  |
| status | Unknown |  |
| custom_fields_** |  |  |

### ats_opening

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_application_id | Unknown | FK |
| closed_at | Instant |  |
| deleted_at | Instant |  |
| opened_at | Instant |  |
| opening_id | Unknown |  |
| status | Unknown |  |
| target_hire_date | LocalDate |  |
| target_start_date | LocalDate |  |
| custom_fields_** |  |  |

### ats_scorecard_question

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_scorecard_id | Unknown | PK, FK |
| answer | Unknown |  |
| option | Json |  |
| question | Unknown |  |
| type | Unknown |  |

### ats_scorecard

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_application_id | Unknown | FK |
| ats_candidate_id | Unknown | FK |
| created_at | Instant |  |
| deleted_at | Instant |  |
| interview | Unknown |  |
| interview_id | Unknown |  |
| interview_step_id | Unknown |  |
| interview_step_name | Unknown |  |
| interviewed_at | Instant |  |
| interviewer_employee_id | Unknown |  |
| interviewer_first_name | Unknown |  |
| interviewer_id | Unknown |  |
| interviewer_last_name | Unknown |  |
| interviewer_name | Unknown |  |
| overall_feedback | Unknown |  |
| overall_recommendation | Unknown |  |
| submitted_at | Instant |  |
| submitted_by_employee_id | Unknown |  |
| submitted_by_first_name | Unknown |  |
| submitted_by_id | Unknown |  |
| submitted_by_last_name | Unknown |  |
| submitted_by_name | Unknown |  |
| updated_at | Instant |  |

### ats_candidate_attachment

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| ats_candidate_id | Unknown | PK, FK |
| created_at | Instant |  |
| file_name | Unknown |  |
| type | Unknown |  |
| url | Unknown |  |

### ats_candidate_education

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_candidate_id | Unknown | PK, FK |
| degree | Unknown |  |
| discipline | Unknown |  |
| end_date | LocalDate |  |
| school_name | Unknown |  |
| start_date | LocalDate |  |

### ats_candidate_employment

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_candidate_id | Unknown | PK, FK |
| company_name | Unknown |  |
| end_date | LocalDate |  |
| start_date | LocalDate |  |
| title | Unknown |  |

### ats_candidate

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| company | Unknown |  |
| created_at | Instant |  |
| deleted_at | Instant |  |
| email_address | Json |  |
| first_name | Unknown |  |
| is_private | Unknown |  |
| last_activity | Instant |  |
| last_name | Unknown |  |
| linked_user_id | Json |  |
| phone_number | Json |  |
| social_media_address | Json |  |
| tags | Json |  |
| title | Unknown |  |
| updated_at | Instant |  |

### ats_user_office

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

### ats_user_department

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

### ats_user

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| created_at | Instant |  |
| deleted_at | Instant |  |
| disabled | Unknown |  |
| email | Json |  |
| employee_id | Unknown |  |
| first_name | Unknown |  |
| last_name | Unknown |  |
| linked_ats_candidate_id | Json |  |
| name | Unknown |  |
| primary_email_address | Unknown |  |
| site_admin | Unknown |  |
| updated_at | Instant |  |

### ats_job_post

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_id | Unknown | FK |
| active | Unknown |  |
| content | Unknown |  |
| created_at | Instant |  |
| deleted_at | Instant |  |
| external | Unknown |  |
| first_published_at | Instant |  |
| internal | Unknown |  |
| internal_content | Unknown |  |
| listed | Unknown |  |
| live | Unknown |  |
| title | Unknown |  |
| updated_at | Instant |  |

### ats_job_department

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_id | Unknown | PK, FK |
| parent_id | Unknown | FK |
| child_department_external_id | Json |  |
| child_id | Json |  |
| deleted_at | Instant |  |
| name | Unknown |  |
| parent_department_external_id | Unknown |  |

### ats_job_office

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

### ats_job_hiring_team_manager

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_id | Unknown | PK, FK |
| employee_id | Unknown |  |
| first_name | Unknown |  |
| last_name | Unknown |  |
| name | Unknown |  |

### ats_job_hiring_team_recruiter

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_id | Unknown | PK, FK |
| employee_id | Unknown |  |
| first_name | Unknown |  |
| last_name | Unknown |  |
| name | Unknown |  |

### ats_job_hiring_team_coordinator

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_id | Unknown | PK, FK |
| employee_id | Unknown |  |
| first_name | Unknown |  |
| last_name | Unknown |  |
| name | Unknown |  |

### ats_job_hiring_team_sourcer

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_id | Unknown | PK, FK |
| employee_id | Unknown |  |
| first_name | Unknown |  |
| last_name | Unknown |  |
| name | Unknown |  |

### ats_job

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| closed_at | Instant |  |
| confidential | Unknown |  |
| created_at | Instant |  |
| deleted_at | Instant |  |
| is_template | Unknown |  |
| name | Unknown |  |
| opened_at | Instant |  |
| requisition_id | Unknown |  |
| status | Unknown |  |
| updated_at | Instant |  |
| custom_fields_** |  |  |

### ats_offer_compensation

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| ats_offer_id | Unknown | PK, FK |
| currency_code | Unknown |  |
| interval | Unknown |  |
| type | Unknown |  |
| unit | Unknown |  |
| value | Unknown |  |
| value_type | Unknown |  |

### ats_offer

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_application_id | Unknown | FK |
| ats_candidate_id | Unknown | FK |
| ats_job_id | Unknown | FK |
| opening_id | Unknown | FK |
| created_at | Instant |  |
| deleted_at | Instant |  |
| sent_at | LocalDate |  |
| starts_at | LocalDate |  |
| status | Unknown |  |
| updated_at | Instant |  |

### ats_job_stage_interview_default_interviewer_user

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_stage_id | Unknown | PK, FK |
| ats_job_stage_interview_id | Unknown | PK, FK |
| employee_id | Unknown |  |
| first_name | Unknown |  |
| last_name | Unknown |  |
| name | Unknown |  |

### ats_job_stage_interview_question

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_stage_id | Unknown | PK, FK |
| ats_job_stage_interview_id | Unknown | PK, FK |
| question | Unknown |  |

### ats_job_stage_interview

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_stage_id | Unknown | PK, FK |
| deleted_at | Instant |  |
| estimated_minutes | Unknown |  |
| interview_kit_content | Unknown |  |
| interview_kit_id | Unknown |  |
| item_id | Unknown |  |
| name | Unknown |  |
| schedulable | Unknown |  |

### ats_job_stage

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| ats_job_id | Unknown | FK |
| active | Unknown |  |
| created_at | Instant |  |
| deleted_at | Instant |  |
| name | Unknown |  |
| priority | Unknown |  |
| updated_at | Instant |  |

## Relationships

- project_value_option(project_field_index) → project_field(index)
- project_value_option(field_project_id) → project_field(project_id)
- project_field(project_id) → project(id)
- project(user_id) → users(id)
- candidate_custom_value_option(custom_value_option_id) → custom_field_option(id)
- candidate_custom_value_option(candidate_custom_field_index) → candidate_custom_field(index)
- candidate_custom_value_option(custom_field_candidate_id) → candidate_custom_field(candidate_id)
- candidate_custom_field(id) → custom_field(id)
- candidate_custom_field(project_id) → project(id)
- candidate_custom_field(candidate_id) → candidate(id)
- candidate_work_info(candidate_id) → candidate(id)
- candidate_education_info(candidate_id) → candidate(id)
- candidate_profile(candidate_id) → candidate(id)
- candidate_email(candidate_id) → candidate(id)
- candidate_project(project_id) → project(id)
- candidate_project(candidate_id) → candidate(id)
- candidate(created_by) → users(id)
- candidate(user_id) → users(id)
- custom_field_option(custom_field_id) → custom_field(id)
- custom_field(project_id) → project(id)
- sequence(user_id) → users(id)
- candidate_note(candidate_id) → candidate(id)
- candidate_note(user_id) → users(id)
- candidate_uploaded_resumes(candidate_id) → candidate(id)
- candidate_uploaded_resumes(user_id) → users(id)
- ats_scheduled_interviewer(scorecard_id) → ats_scorecard(id)
- ats_scheduled_interviewer(ats_scheduled_interview_id) → ats_scheduled_interview(id)
- ats_scheduled_interview_question(ats_scheduled_interview_id) → ats_scheduled_interview(id)
- ats_scheduled_interview(ats_application_id) → ats_application(id)
- ats_scheduled_interview(ats_candidate_id) → ats_candidate(id)
- ats_scheduled_interview(organizer_id) → ats_user(id)
- ats_application_job(ats_application_id) → ats_application(id)
- ats_application(ats_candidate_id) → ats_candidate(id)
- ats_application(ats_job_post_id) → ats_job_post(id)
- ats_opening(ats_application_id) → ats_application(id)
- ats_scorecard_question(ats_scorecard_id) → ats_scorecard(id)
- ats_scorecard(ats_application_id) → ats_application(id)
- ats_scorecard(ats_candidate_id) → ats_candidate(id)
- ats_candidate_attachment(ats_candidate_id) → ats_candidate(id)
- ats_candidate_education(ats_candidate_id) → ats_candidate(id)
- ats_candidate_employment(ats_candidate_id) → ats_candidate(id)
- ats_user_office(id) → ats_office(id)
- ats_user_office(ats_user_id) → ats_user(id)
- ats_user_department(id) → ats_job_department(id)
- ats_user_department(ats_user_id) → ats_user(id)
- ats_job_post(ats_job_id) → ats_job(id)
- ats_job_department(parent_id) → ats_job_department(id)
- ats_job_department(ats_job_id) → ats_job(id)
- ats_job_office(id) → ats_office(id)
- ats_job_office(ats_job_id) → ats_job(id)
- ats_job_hiring_team_manager(ats_job_id) → ats_job(id)
- ats_job_hiring_team_recruiter(ats_job_id) → ats_job(id)
- ats_job_hiring_team_coordinator(ats_job_id) → ats_job(id)
- ats_job_hiring_team_sourcer(ats_job_id) → ats_job(id)
- ats_offer_compensation(ats_offer_id) → ats_offer(id)
- ats_offer(ats_application_id) → ats_application(id)
- ats_offer(opening_id) → ats_opening(id)
- ats_offer(ats_job_id) → ats_job(id)
- ats_offer(ats_candidate_id) → ats_candidate(id)
- ats_job_stage_interview_default_interviewer_user(ats_job_stage_id) → ats_job_stage_interview(ats_job_stage_id)
- ats_job_stage_interview_default_interviewer_user(ats_job_stage_interview_id) → ats_job_stage_interview(id)
- ats_job_stage_interview_question(ats_job_stage_id) → ats_job_stage_interview(ats_job_stage_id)
- ats_job_stage_interview_question(ats_job_stage_interview_id) → ats_job_stage_interview(id)
- ats_job_stage_interview(ats_job_stage_id) → ats_job_stage(id)
- ats_job_stage(ats_job_id) → ats_job(id)