BI Connector schema update - December 29, 2021

We are making the following changes to the BI Connector schema on December 29th, 2021. See below for a description, and see attached for the latest schema.

This update allows customers with the Business Intelligence Connector to view data from Greenhouse Onboarding. Customers can opt-in to receiving this data by updating additional settings from the BI Connector setup page.

We are adding the following tables to the BI Connector schema:

Table What does the table represent?
gho_assigned_tasks Descriptions of tasks that have been assigned to users on behalf of new hires
gho_custom_field_values Custom field values for employees
gho_custom_fields Metadata about custom fields
gho_employee_roles Assignments of roles to employees
gho_employees Basic data including permissions and department/location
gho_roles Names of custom user roles
gho_user_activities Hourly snapshots of users' activities in the app

---

  CREATE TABLE gho_assigned_tasks (
      id bigint NOT NULL,
      task_name character varying NOT NULL,
      task_description character varying,
      task_category character varying(255) NOT NULL,
      assigned_for_id integer NOT NULL,
      assigned_by_id integer,
      completed_at timestamp without time zone,
      due_date_relative_days integer,
      assign_days_before integer NOT NULL,
      calculated_due_date date,
      calculated_assign_date date,
      attachment_required boolean NOT NULL,
      created_at timestamp without time zone NOT NULL,
      updated_at timestamp without time zone NOT NULL,
      assigned_employee_id bigint,
      third_party_system_email character varying,
      assigned_custom_field_id integer,
      assigned_coworker_employee_id integer,
      completed_by_employee_id integer,
      completed_by_third_party_system_email character varying,
      assignee_updated_at timestamp without time zone,
      assigned_at timestamp without time zone
  );

  CREATE TABLE gho_custom_field_values (
      id bigint NOT NULL,
      employee_id bigint NOT NULL,
      custom_field_id bigint NOT NULL,
      custom_field_permanent_field_id character varying(255),
      custom_field_field_type character varying(255),
      value character varying,
      value_employee_id integer,
      value_date date,
      value_updated_at timestamp without time zone,
      created_at timestamp without time zone NOT NULL,
      updated_at timestamp without time zone NOT NULL
  );

  CREATE TABLE gho_custom_fields (
      id bigint NOT NULL,
      permanent_field_id character varying(255),
      name character varying(255),
      field_type character varying(255),
      created_at timestamp without time zone NOT NULL,
      updated_at timestamp without time zone NOT NULL
  );

  CREATE TABLE gho_employee_roles (
      role_id bigint,
      employee_id bigint NOT NULL
  );

  CREATE TABLE gho_employees (
      id bigint NOT NULL,
      created_at timestamp without time zone NOT NULL,
      updated_at timestamp without time zone NOT NULL,
      personal_email character varying,
      work_email character varying,
      start_date date,
      termination_date date,
      employment_status character varying,
      manager_id bigint,
      hr_manager_id bigint,
      title character varying,
      global_role character varying,
      n_direct_reports bigint,
      department character varying,
      location character varying,
      legal_name_first_name character varying,
      legal_name_middle_name character varying,
      legal_name_last_name character varying,
      legal_name_suffix character varying,
      preferred_first_name character varying,
      preferred_last_name character varying
  );

  CREATE TABLE gho_roles (
      id bigint NOT NULL,
      name character varying(255) NOT NULL
  );

  CREATE TABLE gho_user_activities (
      employee_id bigint NOT NULL,
      hourly_last_sign_in_at timestamp without time zone
  );