Database - Customer Main Table (customer_entity)

Description

This table holds the primary customer data. Some associated data like address, phone number, employer, etc. is found in other tables. Note: Because some of the values in this table are, by definition, personally identifiable information (PII), or could be PII in combination with other values, they are encrypted at rest. This includes driver license number, social security/insurance number, and date of birth. It is not possible to pull unencrypted data from the database. These values must be pulled through the API.

Common Uses

This table is commonly used to find:

  • Customer Name
  • Customer Email
  • Customer Gender

Note: If you need to pull social security number, date of birth, or driver license number, you must do it through the API. These values are encrypted in the database and, therefore, cannot be retrieved directly.

Column

Data Type

Column Info

Value Notes

Other

id

int(11)

The id of the customer. This ID is the primary key, and is used to associate other tables with the customer. The ID is generated by LoanPro.

custom_id

varchar(255)

Custom ID for the customer. This ID is set by the user, not by LoanPro, and is often used when updating the customer through an import.

mc_id

int(11)

This is the ID of the customer inside of PCI Wallet. If there are no payment profiles on file for the customer, the ID will be NULL

customer_type

char(100)

This is the type of the customer. The choices are individual, company, and flooring customer. Some of the other fields in this table will only be relevant to specific customer types. This field holds an enumerated value from one of LoanPro's customer collections.

customer.type.company

status

varchar(255)

This is the status of the customer. Statuses are dynamic, and a list of statuses can be found in LoanPro. This field holds the label of the status, and not the ID.

first_name

varchar(50)

This is the customer's first name. This field is specific to individual type customers.

last_name

varchar(50)

This is the customer's last name. This field is specific to individual type customers.

middle_name

varchar(50)

This is the customer's middle name. This field is specific to individual type customers.

birth_date

varbinary(264)

This field is not currently in use. It has been replaced by birth_date_update which has more room to hold the larger encryption string generated by a more recent encryption algorithm. At some point, the size of this field will be increased and it will be used again.

Encrypted

birth_date_update

varbinary(1024)

This is the customers date of birth. This value is encrypted at rest, and therefore won't be of value when viewed in the database. This field holds a date string that looks like this: "/Date(1561593600)/".  The numeric portion of the value is a Unix timestamp.

Encrypted

gender

char(100)

This is the customer's gender. This field holds an enumerated value from one of LoanPro's  customer collections.

customer.gender.female

generation_code

char(100)

This is the customer's generation suffix. If there is no generation suffix, the value of this field will be "customer.generationCode.none".  This field holds an enumerated value from one of LoanPro's  customer collections.

customer.generationCode.ii

email

varchar(255)

This is the customer's email address. This field is specific to individual type customers. LoanPro gives a warning if a customer is created with an email address that is shared with another customer, but it is still possible to create the customer anyway. Therefore, email addresses may not be unique.

ssn

varbinary(255)

This field is not currently in use. It has been replaced by ssn_update, which has more room to hold the larger encryption string generated by a more recent encryption algorithm. At some point, the size of this field will be increased and it will be used again.

Encrypted

ssn_update

varbinary(1024)

For individual type customers, this is the customer's social security number (USA), or social insurance number (Canada). This field will also hold an EIN for company or flooring type customers.

Encrypted

driver_license

varchar(50)

This is the customer's driver license number. This field is specific to individual type customers.

Encrypted

driver_license_update

varchar(50)

This is the customer's driver license number in an encrypted format to accommodate CCPA regulations. This field is specific to individual type customers.

company_name

varchar(255)

This is the company name. This field is used for both company and flooring type customers.

contact_name

varchar(255)

This is the name of the primary contact at the customer's company. This field is specific to company and flooring type customers.

customer_id_type

char(100)

This is the type of ID used to identify the customer. The options are SSN, SIN, and EIN. This field holds an enumerated value from one of LoanPro's  customer collections.

customer.idType.employerNumber

customer_id

varbinary(255)

This field is not supported.

Encrypted

customer_id_update

varbinary(1024)

This field is not supported.

Encrypted

credit_score_id

int(11)

This is the ID of the record of the customer's credit score found in the customer_credit_score_entity table.

credit_limit

decimal(10,2)

This is the credit limit assigned to the customer. This field is specific to flooring type customers.

access_username

varchar(255)

This is the username the customer will use to log in to the customer website.

access_password

varchar(255)

This is the password the customer will use to log in to the  customer website.

web_password_hash

char(32)

web_password_hash_timestamp

datetime

primary_address_id

int(11)

This is the ID of the customer's primary address, found in the address_entity table.

mail_address_id

int(11)

This is the customer's mailing address, found in the address_entity table.

employer_id

int(11)

This is the ID of the record of the customer's employer information, found in the customer_employer_entity table.

has_avatar

tinyint(4)

This value indicates whether an image of the customer has been uploaded.

1 - yes, 0 - no

ofac_match

tinyint(4)

The value in this field indicates whether the customer's information matched information for any specially designated nationals when the OFAC compliance check was done.

1 - yes, 0 - no

ofac_tested

tinyint(4)

The value in this field indicates whether the customer's information has been checked for OFAC compliance.

1 - yes, 0 - no

sale_transfer_pii

tinyint(1)

This value shows whether the customer data can be sold under CCPA (1 - yes), or if the customer has opted out (0 - no).

1 - yes, 0 - no

last_update

datetime

This value is a time and date stamp showing the last time the customer information was updated.

created

timestamp

This value is a time and date stamp that shows when the customer was created.

lastUpdated

timestamp

This value is a time and date stamp that shows when the index data for the customer was updated in Elasticsearch, making it searchable. This will usually occur shortly after the last_updated time.

active

tinyint(4)

This value shows whether the customer is active or not. This value will have no bearing on the customer.

1 - yes, 0 - no

deleted

tinyint(4)

This value shows whether the customer has been deleted (1) or whether it is available (0). If the customer has been deleted, it will not be available via the UI or API. Most entities in LoanPro use this type of soft delete, but may be completely deleted after a period of time.

1 - yes, 0 - no


How did we do?


Powered by HelpDocs (opens in a new tab)