Data Model Archived
There are two models of data within Boss Insights:
The common data model (CDM) - a standardized set of business data objects which map to app integrations that can be used to push or pull data.
The forms and workflow data model (FDM) - a customized set of form and field definitions used to configure portal form fields, account invitations and portfolio views.
Common Data Model (CDM)
The CDM provides a standardized library of business data objects, each account within the platform has its own copy of the the CDM which becomes populated with data from the applications connected to that account.
Examples of business data objects include Vendors, Invoices, Bank Transactions, Balance Sheets, Customers, Credit Notes and many more.
After an end user has provided authorization to connect an app, data will pull from that app in to the common data model on a periodic basis. As the data is pulled through it goes through a process of standardization before being added to the common data model. This process of standardization greatly simplifies the consumption of the data for analytical purposes as you can care less about which specific app provided the data and instead focus on the business objects of interest. Vice-versa this standardization process enables pushing data you generate to your customers apps in a scalable way.
Push of CDM data requires configuration for your use case and is not enabled by default.
A full list of CDM objects can be seen in our API documentation, The CDM can be accessed either via real-time API calls or via SQL for bulk data transfer.
API
Our API documentation including a list of all CDM objects, steps to connect and a ready to run Postman collection are available at api.bossinsights.com .
Authentication for the API uses a user name, not email address
SQL
In addition to API access the CDM can be read from a database connection via SQL to support bulk data transfer use cases such as feeding data in to machine learning models for further analysis. The SQL access is supported via a MySQL compatible database protocol accessed via an SSH tunnel. This connection type is generally well supported by various programming language database connection libraries as well as popular business intelligence tools.
Before gaining access to your database you will need to exchange an SSH key with your administrator.
Before you Start
There are a number of steps that need to be completed to access data via SQL, they are:
An SSH key needs to be created and shared with Boss Insights to gain access to a secure tunnel to access data
Your administrator needs to enable SQL Access for your user account in the admin portal
You need to obtain your access identifier from your profile
Exchange of SSH KeysSend your public key to Boss Insights and take note of location of keys, particularly your <private key file>. To send the key file and request access create a support request for SQL access. If you already have existing SSH key pairs on your local machine for accessing other resources, please ensure you do not overwrite them when creating the required keys for your project. This can be avoided by assigning a unique name to the key pair file that is specific to your Boss Insights project (e.g Users/userid/.ssh/boss). Generate a pair of SSH keys1. Download Cygwin from https://cygwin.com/install.html 2. Install Cygwin on Windows (reference https://www.youtube.com/watch?v=hh-V6el8Oxk ), On the 'Select Packages' page, enter 'openssh' in the search box, expand 'Net' and select to install. 3. Generate a pair of ssh keys on Cygwin terminal with the command:
The default location of the pair of keys is: C:\cygwin64\home<userid>.ssh Or if the key file name is changed during key pair generation without pointing to an absolute path, the location of the pair of keys: C:\cygwin64\home<userid>Where <userid> is your local computer's username
xx 1. Open a terminal on your computer 2. Generate a pair of ssh keys in the terminal window with the command:
3. Choose a location to put the generated key files, being careful not to overwrite existing keys. 4. Then enter a passphrase and confirm the passphrase. The key pair is generated.
|
Granting Access
An administrator of your account will need to edit your user account from the administration portal and choose Show Advanced Options and then choose to enable SQL Data Access. It can take up to one hour after this access is enabled for our backend database systems to synchronize.
Connecting
Exact instructions will differ depending on the tool being used to connect to the database but generally you will need the following connection information:
SSH Tunnel
The SSH host name you connect to is region dependant:
Host | |
|---|---|
Port | 22 |
User | Within the main portal click on the user icon in the top right corner and choose “View Profile” and then click the “Show Access Identifier” button. |
Password | N/A - Use a key |
To test connectivity to the SSH Tunnel you will need to connect using a MySQL client that supports SSH tunnels such as MySQL Workbench. ( https://www.mysql.com/products/workbench/ ) or a compatible database library if connecting from a specific programming language.
You cannot connect to the bastion host with a regular SSH client as shell access is disabled. The bastion only allows MySQL traffic to be tunnelled.
Database Connection
Host | db |
|---|---|
Port | 3306 |
User / Password | Available within your user profile, click the “Show Access Identifier” button |
Once connected you can access each CDM by the schema name “database-KEY” where KEY is the unique alphanumeric account key. If multiple accounts share data with you they will be shown as individual schemas.
Python Help
Python is a popular programming language choice for our users, the following package and code example has been a helpful starter: https://github.com/pahaz/sshtunnel/
# pip install pymysql sshtunnel
import pymysql
from sshtunnel import SSHTunnelForwarder
tunnel = SSHTunnelForwarder(
("db.myintranetapps.com", 22),
ssh_host_key=None,
ssh_username="<INSERT USER>",
ssh_password=None,
ssh_private_key="<INSERT PRIVATE KEY>",
ssh_private_key_password=None,
remote_bind_address=("db", 3306))
tunnel.start()
connection = pymysql.connect(user='<INSERT USER>', passwd='<INSERT PASSWORD>',
host="127.0.0.1", port=tunnel.local_bind_port
database='database-<INSERT ACCOUNT KEY>')
cursor = connection.cursor()Other languages and tools
If you are using a language or tool that doesn't support SSH tunnelling natively you can use SSH itself to forward the remote connection to a local network port that you can then connect to:
ssh -fNg -L 3307:db:3306 <INSERT USER>@db.myintranetapps.com This will open the local network port 3307 so you can connect to localhost:3307 using your mysql client.
Form and Workflow Data Model (FDM)
The FDM is used to configure what non-application data (data that doesn’t come from 3rd party app integrations) will be collected from your end user. It is also used to configure what data may be entered or imported when an end user is invited to the platform as well as what data is displayed in the portfolio view.
The FDM is a hybrid model that configures both storage of data as well as data entry. It includes data types, validations, transformations and access control in a succinct JSON5 based data format.
Example FDM configurations can be seen in the https://github.com/boss-insights/kitchen-sink and https://github.com/boss-insights/simple-account repositories, the FDM must always be named data.json5 .
Configuration sections
The configuration is composed of the following sections: profile, portfolio, invitations.
Profile & Forms
By default every account has a built-in profile, this profile is a set of information that can be configured to contain an arbitrary number of data fields that will be associated with the end users account. Once defined the fields are displayed in an automatically generated HTML form for use within a workflow.
Each field should be given a name and type. The name is how the field can be uniquely referenced, should not contain space characters and is not visible to the end user. A field type should be specified to govern what type of data is required, if the type is not provided it will default to the text type. Depending on the field type chosen default validations will be applied, e.g. the email type will ensure a valid email address is provided.
Fields types
Type | Description |
|---|---|
| Regular plain text, no line breaks |
| A uniform resource locator |
| An email address |
| A number, can be either an integer or decimal |
| A method of managing rows and columns of data |
| A container of fields, used to create logical groupings and repeating sets of fields |
| Decorative field type, does not store data but creates a visual separator across a form page to create sections |
| Decorative field type that creates an HTML comment |
| Decorative field type that creates an empty field |
| Stores data but is not visible to the end user |
| Regular plain text, supports line breaks for multiline text |
| Formatted text that can contain a subset of HTML formatting including basic text formatting and hyperlinks |
| Allows for the selection of a file previously uploaded in the document manager |
| Allows for the selection of one or more predefined options |
| One or more files can be uploaded and accessed |
| A single line address, can optionally show a map for visual confirmation |
| Select a specific Month/Year combination |
| Select a specific date |
| Regular plain text, no line breaks, decorated with a cursive signature like font |
| Telephone number |
Fields support a common set of attributes as well as attributes that are unique to a specific field type
Common attributes
Attribute | Sub Attributes | Description |
|---|---|---|
|
| The name is used to uniquely identify this field Every non-decorative field should have a name |
|
| field type governs what type of data can be input, e.g. date or richtext |
|
| Used to label a field and is the primary indicator of what the user should enter, e.g. “Date of birth” |
|
| Controls which user groups can either read or modify a given field. |
|
| Custom HTML attributes can be set on any field, which fields will be supported depends on the field type, e.g. “step” is supported for the “number” field type |
|
| Additional context can be provided to augment the field title and further explain a field |
|
| Used to indicate whether a field should synchronize with an external CRM/LOS platform |
|
| A visibility condition can be provided as a Javascript expression, which when evaluating to true will show the field, otherwise hiding it. A simpler alternative is using “contains” and then specifying a field name on the same form and what value it should contain in order to show the current field. If |
|
| An icon close to the field title which when clicked on or hovered over will reveal additional information. Supports HTML formatting and links. |
Unique attributes
Attribute | Applies to | Sub Attributes | Description |
|---|---|---|---|
|
|
| true or false, whether the field can allow multiple values. For fieldsets this allows for multiple copies of all fields. |
|
|
| a string that describes a type of file that may be selected by the user, e.g. “.pdf“ |
|
|
| A fieldset can contain a list of fields. Note a fieldset cannot contain a fieldset. |
|
|
| Values for |
|
|
| e.g. “today” |
|
|
| A short piece of text to add to the right side of an input, e.g. “%” |
|
|
| A short piece of text to add to the left side of an input, e.g. “$” |
|
|
| Specified in CSS width units. Useful to indicate to the user the expected length of input. |
|
|
| Select inputs automatically adjust to either HTML select or input widgets (as either radio or checkboxes) depending on the number of selectable values. This attribute allows you do override this to be either:
|
|
|
| It is possible for one field to refer to the value of another field, either in the same form or a different one, i.e. an additional form may refer to a value in the profile form. When byRef is set true then the value of the current field is always the value of the referred field. |
Example Code
{
name: 'full_name',
type: 'text',
title: 'Full Name',
access: {
read: [
'admins','underwriters','borrowers'
],
modify: [
'admins','underwriters','borrowers'
]
},
attributes: {
placeholder: "Enter your full name",
required: true,
},
description: "This is the full name input field",
visibility: {
contains: {field: 'required-info', value: 'user-info'},
// OR condition: '$("input[title=\\\'User Info\\\']:checked").val() === "user_info"'
},
hint: "Enter your full name"
},Output
Additional Forms
In addition to the built in “profile” you may add additional forms by specifying them in the forms object within the data.json5 file. Additional forms may be added by specifying a unique form key which will be used to refer to the form from a workflow, then form then has a fields property which contains fields in the same format as the built in profile, a form may also have a title attribute which will be used as the form page header.
Portfolio
The portfolio is configured in the following sections:
owners- specifies the user groups that can be owners (managers, officers) of your portfolio accounts. Anyone who is a member of a group listed here will be able to be assigned as an ownerpermissions- a list of permissions which specifyview,editandaddaccessfields- a set of profile fields to be shown in the portfolio, the syntax of these is similar to form fields (see above) but they are used for display purposes only and do not store data
Invitations
The invitations are configured in the following sections:
uniqueJoinField- specifies a unique field the value of which will be shown to an end user when they join the platform, e.g. “legal_name”. This helps disambiguate multiple invitations sent to the same email addressowners- specifies the user groups that can be owners (managers, officers) of your invitations, usually this is the same set as the portfolio but may be different. Anyone who is a member of a group listed here will be able to be assigned as an owner.permissions- a list of permissions which specifyview,editandaddaccessfields- a set of fields that will appear when adding a new invitation and will also be available in the invitation file import process (when bulk importing users). When the names of these fields match names in the profile they will be automatically copied in to the profile when the user joins the platform. The syntax of these is similar to form fields (see above).
Deployment
To deploy changes to the FDM you upload the new data.json5 via WebDAV to the /public/dashboard folder.
Additional Logic
Javascript code can be added to FDM pages by creating a forms.js in the same folder as the data.json5 file. This Javascript will be included on every FDM based form page and can be used to provide additional logic, fetch external data and other purposes.