Business Intelligence Connector update – December 14, 2022

We're making updates to the Business Intelligence Connector schema on Wednesday, December 14, 2022.

Download the latest schema (.sql).

Overview of changes

Custom referral question table update

The referral_question_custom_fields table will receive an update to make it consistent with all other custom field tables in our schema:

• Currently, the user_id column on the table is of datatype integer. In order to support large ids, we'll change this to datatype bigint.

New custom user fields table

We're also adding a new table to accommodate custom user fields. The user_attribute_custom_fields table will be in a similar format to all other custom fields in our schema. Check out the new table below:

CREATE TABLE user_attribute_custom_fields (
     user_id bigint,
     custom_field character varying,
     float_value double precision,
     date_value date,
     display_value character varying(1024),
     unit character varying,
     min_value numeric,
     max_value numeric,
     user_id_value bigint,
     created_at timestamp without time zone,
     updated_at timestamp without time zone,
     key character varying(1024),
     text_value character varying(16384)
);

ALTER TABLE ONLY user_attribute_custom_fields
     ADD CONSTRAINT user_attribute_custom_fields_user_id_fk FOREIGN KEY (user_id) REFERENCES users(id);
ALTER TABLE ONLY user_attribute_custom_fields
     ADD CONSTRAINT user_attribute_custom_fields_user_id_value_fk FOREIGN KEY (user_id_value) REFERENCES users(id);

The primary differences between the user_attribute_custom_fields table and the other custom field tables include the following:

• The first column indicates a user_id. This refers to the user to which the custom field value is attached. This is analogous to the job_id column in the job_custom_fields table or the candidate_id column in the candidate_custom_fields table.
• The user_id column that would normally hold values for user value types has been renamed user_id_value.