For any teams that would like to export their data from Gem ATS, the following tables delineate the schema that you can expect within the export.
Application data
The applications table
The applications table represents a snapshot of application data. Each row represents the latest status of a single application.
Column | Description |
id | The application ID. This appears as application_id on other tables. |
candidate_id | The candidate ID. |
coordinator | The coordinator’s name. |
coordinator_id | The candidate’s coordinator’s ID. |
applied_at | The date and time the candidate applied, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
status | The application’s current status and is one of active, rejected, hired, or dropped_out. |
prospect | N/A |
stage_id | The stage ID of the application. |
source_id | The source ID for the application. |
recruiter | The recruiter’s name. |
recruiter_id | The candidate’s recruiter’s ID. |
referrer_id | The referrer ID for the application. |
rejected_at | The date and time the application was rejected, if applicable, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
rejected_by_id | The user ID of the person who rejected the application, if applicable. |
rejection_reason_id | The rejection reason ID for a rejected application, if applicable. |
pipeline_percent | N/A |
migrated | N/A |
rejected_by | The name of the Gem user who rejected the application, if applicable. |
stage_name | The application’s current stage name. |
job_post_id | The job post ID associated with the application. |
prospect_pool | N/A |
prospect_pool_stage | N/A |
prospect_owner_id | N/A |
created_at | The date and time the application was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The last time the application was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
event_id | The event ID associated with a prospect application, if applicable. |
converted_prospect_application_id | N/A |
sent_demographic_questions | N/A |
organization_id | Your organization’s unique ID in Gem. |
The application custom fields table
The application_custom_fields table represents custom application fields associated with an application. Each row represents a single value of a single custom field for a single application.
Column | Description |
application_id | The application ID. |
custom_field | The custom application field name. |
float_value | These are number values entered in the custom application field, if applicable. |
date_value | The date entered in the custom application field, if applicable. |
display_value | How the custom application field value appears on the candidate profile. |
unit | The currency type entered in the custom application field, if applicable. |
min_value | The maximum value of the range. |
max_value | The minimum value of the range. |
user_id | The user ID for custom application fields with user field types. |
created_at | The date and time the custom application field was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the custom application field was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
key | The immutable field key for the custom application field. |
text_value | The text value as it appears on the application’s custom application field. |
organization_id | Your organization’s unique ID in Gem. |
The application question answers table
The application_question_answers table represents questions and answers that candidates submitted in response to job posts with their applications. Each row represents a single job post application question and answer from a single application.
Column | Description |
job_post_id | The job post ID. |
application_id | The application ID. |
question | The application question from the job post. |
answer | The applicant’s answer to the application question. |
created_at | The date the application was received, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date the application was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The application stages table
The application_stages table represents the historical activity of all stages an application has been on, as well as each stage that an application could be on, taken from the job stage setup. Each row represents a single stage that an application can be in and the timestamp that the application entered and exited the stage.
Column | Description |
application_id | The application ID. |
stage_id | The stage ID. |
entered_on | The date the application entered the stage, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
exited_on | The date the application exited the stage, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
stage_name | The job stage name. |
organization_id | Your organization’s unique ID in Gem. |
The applications jobs table
The applications_jobs table represents applications for jobs. Each row represents one application to one job.
Column | Description |
application_id | The application ID. |
job_id | The job ID. |
organization_id | Your organization’s unique ID in Gem. |
Candidate data
The candidates table
The candidates table represents a snapshot of candidate data. Each row represents a single candidate. A candidate is a person.
Column | Description |
id | The candidate ID. This appears as candidate_id on other tables. |
first_name | The candidate’s first name. |
last_name | The candidate’s last name. |
preferred_name | The candidate’s preferred name. |
migrated | N/A |
company | The candidate’s current company, if applicable. |
title | The candidate’s current title, if applicable. |
created_at | The date and time the candidate was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the candidate was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
private | Boolean. Results are either true or false, indicating whether the candidate is private. |
organization_id | Your organization’s unique ID in Gem. |
The candidate custom fields table
The candidate_custom_fields table represents custom candidate fields associated with a candidate. Each row represents a single value of a single custom field for a single candidate.
Column | Description |
candidate_id | The candidate ID. |
custom_field | The custom candidate field. |
float_value | These are numbers entered in the custom candidate field, if applicable. |
date_value | The date entered in the custom candidate field, if applicable. |
display_value | The value entered into the custom candidate field as it appears in Gem. |
unit | The currency type entered in the custom candidate field, if applicable. |
min_value | The maximum value of the range. |
max_value | The minimum value of the range, |
user_id | The Gem user ID entered for custom candidate fields with user field types. |
created_at | The date and time the custom candidate field value was entered, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the custom candidate field value was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
key | The immutable field key for the custom candidate field. |
text_value | The answer as it appears on the application’s custom candidate field. |
organization_id | Your organization’s unique ID in Gem. |
The educations table
The educations table represents all structured education fields associated with a candidate. Each row represents a single structured education for a single candidate.
Column | Description |
candidate_id | The candidate ID. |
school_name | The school name associated with the candidate. |
degree | The degree associated with the candidate. |
discipline | The school discipline associated with the candidate. |
start | The date the candidate started the education, if applicable, like 2024-01-01. |
end | The date the candidate ended the education, if applicable, like 2024-01-01. |
latest | Boolean. Results are either true or false, indicating whether the education is the latest the candidate has experienced. |
created_at | The date and time the education was associated with the candidate profile, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the education for the candidate profile was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The employments table
The employments table represents all structured employment fields associated with a candidate. Each row represents a single structured employment record for a single candidate.
Column | Description |
candidate_id | The candidate ID. |
company_name | The company name associated with the candidate. |
title | The title associated with the candidate. |
start | The date the candidate started the employment, if applicable, like2024-01-01. |
end | The date the candidate ended the employment, if applicable, like2024-01-01. |
latest | Boolean. Results are either true or false, indicating whether the employment is the latest the candidate has experienced. |
created_at | The date and time the employment was associated with the candidate profile, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the employment for the candidate profile was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
Candidate PII data
The candidate email addresses table
The candidate_email_addresses table represents a list of email addresses by candidate. Each row represents a single email address and the associated candidate.
Column | Description |
candidate_id | The candidate ID. |
value | The candidate’s email address. |
created_at | The date and time the email address was associated with the candidate, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the email address for the candidate was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The candidate phone numbers table
The candidate_phone_numbers table represents a list of phone numbers by candidate. Each row represents a single phone number and the associated candidate.
Column | Description |
candidate_id | The candidate ID. |
value | The candidate’s phone number. |
created_at | The date and time the phone number was associated with the candidate, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the phone number for the candidate was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
Demographic data
The EEOC responses table
The eeoc_responses table represents responses to EEOC questions. Each row represents a single application’s responses to EEOC questions.
Column | Description |
application_id | The application ID. |
status | The EEOC response status and is one of responded or pending response. |
race | The application response to the EEOC questions Are you Hispanic/Latino? and Please identify your race. |
gender | The application response to the EEOC question Gender. |
disability_status | The application response to the EEOC question Disability Status. |
veteran_status | The application response to the EEOC question Veteran Status. |
submitted_at | The date and time the EEOC response was submitted, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
Job data
The jobs table
The jobs table represents a list of jobs and their statuses in Gem. Each row represents the status of a single job.
Column | Description |
id | The job ID. |
organization_id | Your organization’s unique ID in Gem. |
name | The job’s internal name. |
requisition_id | The job’s requisition ID. |
status | The job status and is one of open, closed, or draft. |
opened_at | The date and time the job was opened, if applicable, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
closed_at | The date and time the job was closed, if applicable, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
department_id | The job’s department ID. |
level | The job’s level. |
confidential | Boolean. Results are either true or false, indicating whether the job is confidential. |
created_at | The date and time the job was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
notes | These are notes entered on the job. |
updated_at | The date and time the job was updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
is_template | Whether or not the associated job is a template job. |
The job custom fields table
The job_custom_fields table represents custom job fields associated with a job. Each row represents a custom job field associated with a single job.
Column | Description |
job_id | The job ID. |
custom_field | The custom job field name as it appears in Gem. |
float_value | These are numbers entered in the custom job field, if applicable. |
date_value | The date entered in the custom job field, if applicable. |
display_value | The value entered into the custom job field as it appears in Gem. |
unit | The currency type entered in the custom job field, if applicable. |
min_value | The minimum value of the range. |
max_value | The maximum value of the range. |
user_id | The user ID entered for custom job fields with user field types. |
created_at | The date and time the custom job field value was associated with the job, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the custom job field value was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
key | The immutable field key for the custom job field. |
text_value | The value entered on the custom job field as it appears in Gem. |
organization_id | Your organization’s unique ID in Gem. |
The job snapshots table
The job_snapshots table represents a daily snapshot of job activity. Each row represents job-level metrics for a single day.
Column | Description |
job_id | The job ID. |
date | The date of the snapshot, like 2024-01-31. |
hired_count | The count of new hires in the day. |
prospect_count | The count of new prospects in the day. |
new_today | The count of new applications in the day. |
rejected_today | The count of rejected applications in the day. |
advanced_today | The count of applications advanced in the day. |
interviews_today | The count of interviews in the day. |
organization_id | Your organization’s unique ID in Gem. |
The jobs departments table
The jobs_departments table represents a list of departments by job. Each row represents a single department on a single job.
Column | Description |
id | The primary key to this table. |
job_id | The job ID. |
department_id | The department ID associated with the job. |
created_at | The date and time the department was associated with the job, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the department on the job was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The jobs interviews table
The jobs_interviews table represents a list of interviews by job. Each row represents a single interview on a single stage for a single job.
Column | Description |
id | The interview ID. |
job_id | The job ID. |
stage_id | The stage ID. |
interview_id | The interview ID. |
order | The order of the interview on a stage with multiple interviews. |
estimated_duration | The estimated duration of the interview in minutes. |
created_at | The date and time the interview was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the interview was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
interview_name | The interview name as it appears in Gem. |
stage_name | The stage name as it appears in Gem. |
organization_id | Your organization’s unique ID in Gem. |
The jobs offices table
The jobs_offices table represents a list of offices by job. Each row represents a single office on a single job.
Column | Description |
id | The primary key to this table. |
job_id | The job ID. |
office_id | The office ID associated with the job. |
created_at | The date and time the job was associated with the office, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the job’s office was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The jobs posts table
The job_posts table represents a list of job posts by job. Each row represents details of a job post associated with a job.
Column | Description |
id | The job post ID. |
job_id | The job ID. |
title | The job post title. |
live | Boolean. Results are either true or false, indicating whether the job post is live on your job board. |
job_board_name | N/A |
language | The language the job post was published in, like en for English. |
created_at | The date and time the job post was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the job post was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
eeoc_enabled | N/A |
indeed_feed | N/A |
linked_in_feed | N/A |
glassdoor_feed | N/A |
first_published | The date and time the job post was first published, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The job post locations table
The job_post_locations table represents a list of job locations and their statuses in Gem. Each row represents the status of a single job.
Column | Description |
id | The job location ID. |
name | The job location name as it appears in Gem. |
type | The type of job location. |
job_post_id | The job post ID. |
created_at | The date and time the job post location was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the job post location was updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The job post questions table
The job_post_questions table represents a list of job post questions by job post. Each row represents a single job post question associated with a single job post.
Column | Description |
job_post_id | The job post ID. |
question | The job post question. |
created_at | The date and time the job post question was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the job post question was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The jobs stages table
The jobs_stages table represents a list of stages by job. Each row represents a single stage on a single job.
Column | Description |
job_id | The job ID. |
stage_id | The stage ID. |
order | The order the stage appears on the job. |
name | The name of the stage as it appears in Gem. |
stage_alert_setting | N/A |
created_at | The date and time the job stage was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the job stage was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
milestones | N/A |
organization_id | Your organization’s unique ID in Gem. |
The hiring team table
The hiring_team table represents a list of hiring teams by job. Each row represents a single Gem user on a hiring team role on a single job.
Column | Description |
job_id | The job ID. |
user_id | The user ID of the person associated with the hiring team role. |
role | The hiring team role associated with the person. |
responsible | Boolean. Results are either true or false, indicating whether the person holds a responsible role on the hiring team. |
created_at | The date and time the hiring team role was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the hiring team role was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The openings table
The openings table represents a list of openings by job. Each row represents a single opening for a single job.
Column | Description |
id | The primary key to this table. |
job_id | The job ID. |
opening_id | The opening ID. |
opened_at | The date and time the opening was opened, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
closed_at | The date and time the opening was closed, if applicable, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
hired_application_id | The hired application’s ID, if applicable. |
close_reason | The close reason selected, if applicable. |
created_at | The date and time the opening was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the opening was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
target_start_date | The opening’s target start date. |
organization_id | Your organization’s unique ID in Gem. |
status | Whether or not the job opening is currently active. |
The opening custom fields table
The opening_custom_fields table represents a snapshot of custom opening fields. Each row represents a single value of a single custom opening field for a single opening.
Column | Description |
opening_id | The opening ID. |
custom_field | The custom opening field. |
float_value | These are numbers entered in the custom opening field, if applicable. |
date_value | The date entered in the custom opening field, if applicable. |
display_value | The value entered into the custom opening field as it appears in Gem. |
unit | The currency type entered in the custom opening field, if applicable. |
min_value | The minimum value of the range. |
max_value | The maximum value of the range. |
user_id | The Gem user ID entered for custom opening fields with user field types. |
created_at | The date and time the custom opening field value was associated with the opening, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the custom opening field value was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
key | The immutable field key for the custom opening field. |
text_value | The value entered on the custom opening field as it appears in Gem. |
organization_id | Your organization’s unique ID in Gem. |
Offer data
The offers table
The offers table represents all offers. Each row represents the details of a single version of an offer for a single application. Each new version of an offer generates a new row of data.
Column | Description |
id | The offer ID. |
application_id | The application ID. |
status | The status of the offer and is one of created, accepted, rejected, or deprecated. |
created_at | The date and time the offer was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
sent_at | The date and time the offer was sent to the candidate, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
resolved_at | The date and time the offer was accepted or rejected, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
start_date | The date and time of the new hire’s start date, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
created_by | The Gem user who created the offer. |
updated_at | The date and time the offer was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
The offer custom fields table
The offer_custom_fields table represents all custom offer fields associated with an offer. Each row represents a single value of a single custom field for a single offer.
Column | Description |
custom_field | The custom offer field. |
float_value | These are numbers entered in the custom offer field, if applicable. |
date_value | The date entered in the custom offer field, if applicable. |
display_value | The value entered into the custom offer field as it appears in Gem. |
unit | The currency type entered in the custom offer field, if applicable. |
min_value | The minimum value of the range. |
max_value | The maximum value of the range. |
user_id | The Gem user ID entered for custom offer fields with user field types. |
created_at | The date and time the custom offer field value was associated with the offer, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the custom offer field value was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
key | The immutable field key for the custom offer field. |
text_value | The value entered on the custom offer field as it appears in Gem. |
organization_id | Your organization’s unique ID in Gem. |
offer_id | The unique identifier for the offer. |
Organization data
The departments table
The departments table represents a list of all departments. Each row represents a single department. This table can be joined to any table that has a department_id to get the name of the department.
Column | Description |
id | The department ID. |
organization_id | Your organization’s unique ID in Gem. |
name | The name of the department as it appears in Gem. |
parent_id | The department’s parent department ID, if applicable. |
created_at | The date and time the department was created, like 2024-01-31 06:00:00.000. |
updated_at | The date and time the department was last updated, like 2024-01-31 06:00:00.000. |
The interviews table
The interviews table represents a list of all your organization’s interviews. Each row represents a single interview.
Column | Description |
id | The interview ID. |
organization_id | Your organization’s unique ID in Gem. |
name | The name of the interview as it appears in Gem. |
created_at | The date and time the interview was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the interview was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
The offices table
The offices table represents a list of all offices. Each row represents a single office location. This table can be joined to any table that has a office_id to get the name of the office location.
Column | Description |
id | The office ID. |
organization_id | Your organization’s unique ID in Gem. |
name | The name of the office as it appears in Gem. |
parent_id | The office’s parent office ID, if applicable. |
created_at | The date and time the office was created, like 2024-01-31 06:00:00.000. |
updated_at | The date and time the office was last updated, like 2024-01-31 06:00:00.000. |
The referrers table
The referrers table represents a list of all referrers. Each row represents a single referrer. This table can be joined to any table that has a referrer_id to get the name of the referrer.
Column | Description |
id | The referrer ID. |
organization_id | Your organization’s unique ID in Gem. |
name | The name of the referrer as it appears in Gem. |
user_id | The user ID associated with the referrer, if applicable. |
created_at | The date and time the referrer was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the referrer was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
The rejection reasons table
The rejection_reasons table represents a list of all rejection reasons. Each row represents a single rejection reason.
Column | Description |
id | The rejection reason ID. |
organization_id | Your organization’s unique ID in Gem. |
name | The name of the rejection reason as it appears in Gem. |
type | The type of the rejection reason. |
created_at | The date and time the rejection reason was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the rejection reason was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
The sources table
The sources table represents a list of all sources. Each row represents a single source. This table can be joined to any table with a source_id to get the name of the source.
Column | Description |
id | The source ID. |
organization_id | Your organization’s unique ID in Gem. |
name | The name of the source as it appears in Gem. |
type | The type of the source. |
created_at | The date and time the source was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the source was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
The stages table
The stages table represents a list of stages by job, whether they are active or not.
Column | Description |
id | The stage ID. |
organization_id | Your organization’s unique ID in Gem. |
name | The name of the stage as it appears in Gem. |
order | The order the stage appears on the job. |
active | Boolean. Results are either true or false, indicating whether the stage is active. |
created_at | The date and time the stage was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the stage was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
The users table
The users table represents a list of all users. Each row represents a single Gem user. This table can be joined to any other table that has a user_id to get the name of a user.
Column | Description |
id | The user ID. |
organization_id | Your organization’s unique ID in Gem. |
first_name | The user’s first name. |
last_name | The user’s last name. |
email | The user’s email address. |
status | The user’s current status in Gem and is one of active or disabled. |
full_name | The user’s full name. |
employee_id | N/A |
created_at | The date and time the user was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the user was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
The user candidate links table
The user_candidate_links table represents a list of all user candidate links. Each row represents a single user linked to a single candidate.
Column | Description |
user_id | The user ID. |
candidate_id | The candidate ID linked to the user. |
created_at | The date and time the user candidate link was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the user candidate link was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
Process data
The scheduled interviews table
The scheduled_interviews table represents a list of scheduled interviews. Each row represents a single scheduled interview.
Column | Description |
id | The interviewer ID associated with the scheduled interview. |
application_id | The application ID associated with the scheduled interview. |
interview_id | The interview ID. |
status | The interview’s feedback status. |
scheduled_by_id | The original scheduler or organizer of the interview. If an interview is rescheduled, this field doesn’t update. |
scheduled_at | The date and time the interview was scheduled at, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). If an interview is rescheduled, this timestamp doesn’t update. |
starts_at | The date and time the interview was scheduled to start, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
ends_at | The date and time the interview was scheduled to end, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
all_day_start_date | The start date for all-day interviews, like 2024-01-31. |
all_day_end_date | The end date for all-day interviews, like 2024-01-31. |
stage_name | The stage name associated with the scheduled interview. |
interview_name | The interview name. |
organization_id | Your organization’s unique ID in Gem. |
location | The location of the interview. |
The interviewers table
The interviewers table represents a list of interviewers per scheduled interview. Each row represents a single interviewer for a scheduled interview.
Column | Description |
interview_id | The interview ID. |
user_id | The user ID associated with the interview. |
scorecard_id | The scorecard associated with the interview. |
user | The user name associated with the interview. |
organization_id | Your organization’s unique ID in Gem. |
The scorecards table
The scorecards table represents data on submitted scorecards. Each row represents a single submitted scorecard. Connect the scheduled_interviews table to the interviewers table, then connect the scorecards table to the interviewers table.
Column | Description |
id | The scorecard ID. |
application_id | The application ID. |
stage_id | The stage ID. |
interview_id | The interview ID. |
interviewer_id | The interviewer ID. |
submitter_id | The user ID of the person who submitted the scorecard. |
overall_recommendation | The scorecard’s overall recommendation and is one of Yes, Strong Yes, No, Strong No or No Decision. |
submitted_at | The date and time the scorecard was submitted, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
scheduled_interview_ended_at | The date and time the scheduled interview ended, like 2024-01-31 06:00:00.000. |
total_focus_attributes | N/A |
completed_focus_attributes | N/A |
interviewer | The interviewer’s name. |
submitter | The scorecard submitter’s name. |
stage_name | The stage associated with the scorecard. |
created_at | The date and time the scorecard was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the scorecard was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
interview_name | The interview name. |
organization_id | Your organization’s unique ID in Gem. |
key_takeaways | The text included in the key takeaways field on the scorecard. |
The scorecard questions table
The scorecard_questions table represents a snapshot of your organization’s scorecard questions. Each row represents a single scorecard question from a single job.
Column | Description |
id | The scorecard question ID. |
interview_kit_id | N/A |
question | The scorecard question. |
answer_type | The scorecard question answer type. |
active | Boolean. Results are either true or false, indicating whether the scorecard question is active. |
priority | The priority order of the question on the scorecard. |
required | Boolean. Results are either true or false, indicating whether the scorecard question is required. |
created_at | The date and time the scorecard question was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the scorecard question was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
focus_attributes | N/A |
organization_id | Your organization’s unique ID in Gem. |
The scorecard question answers table
The scorecard_question_answers table represents submitted scorecard questions and answers. Each row represents a single scorecard question and a single submitted scorecard answer.
Column | Description |
scorecard_id | The scorecard ID. |
application_id | The application ID. |
question | The scorecard question. |
answer | The scorecard answer. |
created_at | The date and time the scorecard was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the scorecard was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
scorecard_question_id | The scorecard question ID. |
organization_id | Your organization’s unique ID in Gem. |
The stage snapshots table
The stage_snapshots table represents a daily snapshot of stage-level activity. Each row represents the number of active applications in the stage at 4am ET on a given date for jobs that were open at that time.
Column | Description |
stage_id | The stage ID. |
date | The date of the stage snapshot. |
active_count | The number of active applications on the stage. |
job_id | The job ID. |
stage_name | The stage name as it appears in Gem. |
organization_id | Your organization’s unique ID in Gem. |
The approvals table
The approvals table represents a list of approvals. Each row with an offer_id represents a specific job or offer approval associated with each approver.
Column | Description |
offer_id | The offer ID. |
application_id | The application ID. |
job_id | The job ID. |
job_name | The job name as it appears in Gem. |
candidate_id | The candidate ID. |
candidate_name | The candidate’s name. |
approver_id | The user ID of the approver. |
approver_name | The approver’s name. |
approval_type | The approval type. Responses are either Offer Candidate for candidate offers, Open Job for one-stage approvals, and Offer Job for two-stage approvals. |
status | The approval status and is one of Due, Waiting, Approved, or Rejected. |
version | N/A |
version_final | N/A |
group_id | N/A |
group_order | N/A |
group_quorum | N/A |
assigned_at | The date and time the approval was assigned, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
completed_at | The date and time the approval was completed, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
created_at | The date and time the approval was created, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
updated_at | The date and time the approval was last updated, like 2024-01-31 06:00:00.000. Timestamps are in Coordinated Universal Time (UTC). |
organization_id | Your organization’s unique ID in Gem. |
Have any issues or questions on this topic? Please feel free to contact your dedicated Gem Customer Success Manager directly or our Support team at support@gem.com.
- Application data
- Candidate data
- Candidate PII data
- Demographic data
- Job data
- Offer data
- Organization data
- Process data