create table event_log(
timestamp int,
user_name text,
user_email text,
candidate_id int, -- internal Gem ID
candidate_greenhouse_id text,
candidate_first_name text,
candidate_last_name text,
candidate_email text,
event_type text, -- see below
event_subtype text, -- see below
event_metadata text -- see below
);
create table projects( -- there will be one row per candidate per project
added_timestamp int, -- when the candidate was added to the project
project_id int, -- internal Gem ID
project_name text,
project_owner_name text,
project_owner_email text,
candidate_id int, -- internal Gem ID
candidate_greenhouse_id text,
candidate_first_name text,
candidate_last_name text,
candidate_email text,
adding_user_name text,
adding_user_email text
);
create table custom_field_data(
candidate_id int, -- internal Gem ID
candidate_greenhouse_id text,
candidate_first_name text,
candidate_last_name text,
candidate_email text,
-- There will be a column for each custom field
);
create table notes( -- this includes private notes
timestamp int, -- unix timestamp of when the note was added
user_name text, -- the name of the user who added the note
user_email text, -- the email of the user who added the note
candidate_id int, -- internal Gem ID
candidate_greenhouse_id text,
candidate_first_name text,
candidate_last_name text,
candidate_email text,
note_content text, -- the note content in HTML format
);
create table candidate_info( -- not exported if PII is hidden
candidate_id int, -- internal Gem ID
candidate_greenhouse_id text,
candidate_first_name text,
candidate_last_name text,
candidate_email text,
all_candidate_emails text, -- a JSON formatted list of emails
candidate_phone_number text,
candidate_location text,
candidate_linkedin_url text,
candidate_company text,
candidate_title text,
candidate_school text,
candidate_source text, -- the location the candidate was sourced from
candidate_ats_id text,
sourced_timestamp int,
);
-- only included if the team has requested DEI data in the export
-- and if the team has signed the data agreement contract
create table candidate_dei_data( -- not exported if PII is hidden
candidate_id int, -- internal Gem ID
candidate_greenhouse_id text,
candidate_predicted_gender text,
candidate_predicted_race_ethnicity text,
sourced_timestamp int,
);