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.

Example

If you were to create a custom user field called Supervisor to select a user's supervisor from a list of users, the user_id column would contain the ID of the supervisee while the user_id_value column would contain the ID of the supervisor.