Admin Data Structure

Admin schema

Bot

Base informations from a virtual agent (VA), internally named bot. Most information regarding it is actually found within the environment table, but some information are relevant for authentication and user access delegation and are thus mirrored in the Admin schema.

Column Name

Data Type

Not Null

Description

uuid

varchar(36)

Yes

The bot's UUID.

environment_uuid

varchar(36)

Yes

UUID from the environment where the bot belongs

name

varchar(50)

Yes

The bot's Name

image_url

varchar(100)

No

The bot's image URL, if any

removed

tinyint(1)

No

True if the bot was deleted

created_at

datetime

No

The date this object was created

updated-at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

Bucket Address

A bucket name table for Minio. Rather than using the uuid's from organization and environments, we actually map them into a fully random uuid for each distinct kinf of resource for granularization; this resulted uuid is found on file resource urls, such as images.

Column Name

Data Type

Not Null

Description

id

bigint(20)

Yes

This object's ID.

org_uuid

varchar(50)

Yes

A valid organization UUID.

env_uuid

varchar(50)

No

An optional environment UUID. Some resources, such as user images, does not require an environment.

bucket_resource

varchar(36)

No

Which kind of resource is stored in this bucket, such as bot_image, etc.

bucket_name

varchar(36)

No

The bucket's name. It is an uuid.

Environment

Table containing all data regarding an environment, including which instance it's located in.

Column Name

Data Type

Not Null

Description

uuid

varchar(36)

Yes

ID representing the entity value

instance_uuid

varchar(36)

Yes

UUID of the instance this environment is hosted

channel_instance_uuid

varchar(36)

Yes

UUID of the channel instance this environment is hosted

connector_instance_uuid

varchar(36)

Yes

UUID of the connector instance this environment is hosted

organization_uuid

varchar(36)

Yes

UUID of the organization this environment belongs to

name

varchar(50)

Yes

The environment's name

removed

tinyint(1)

No

True if this environment was deleted

created_at

datetime

No

The date this object was created

updated_at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

Instance

Instance configuration store it's basic data but also it's DNS. All environments pointing to this instance will have the same DNS value.

Column Name

Data Type

Not Null

Description

uuid

varchar(36)

Yes

This instance's UUID

name

varchar(50)

Yes

This instance's name

dns

varchar(50)

No

This instance's DNS

created_at

datetime

No

The date this object was created

updated_at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

Organization

Organizations are the mother structure in Syntphony CAI 4.0.0. Objects belong to virtual agents, which in turn belong to environments, which belongs to organizations. All the data that the organization table stores are uuid and name.

Column Name

Data Type

Not Null

Description

uuid

varchar(36)

Yes

This organization's UUID.

name

varchar(50)

Yes

This organization's name.

created_at

datetime

No

The date this object was created

updated_at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

Permission

The Permissions table holds a flat out name map for several permissions, which are mapped for every single controller action in Syntphony CAI. They are associated to roles, which are attributed to users.

Column Name

Data Type

Not Null

Description

id

bigint(20)

Yes

This permission's ID

name

varchar(255)

Yes

The name of this permission. These match internal mappings in Syntphony CAI's microservices.

Role

Roles determine what Syntphony CAI's users may or not do within any given environment or virtual agent. They are not directly assigned to users, but rather to each resource and user, and mapped NxN to permissions. This table only stores their names and descriptions.

Column Name

Data Type

Not Null

Description

id

bigint(20)

Yes

This role's ID

description

varchar(255)

No

A short description of the role

name

varchar(255)

Yes

This role's name

User

The user table stores the Cockpit users created in the keycloak, which is an access control tool responsible for Syntphony CAI’s user authentication.

Column Name

Data Type

Not Null

Description

uuid

varchar(36)

Yes

ID representing the entity value

organization_uuid

varchar(36)

Yes

UUID of the organization this user belongs to.

identity_provider_ reference

varchar(36)

Yes

This user's UUID within keycloak.

name

varchar(100)

Yes

The user's name

email

varchar(100)

Yes

The user's email and login key

image_url

varchar(255)

No

URL for it's image, if any.

company

varchar(50)

No

Identifies the company where the user works

admin

tinyint(1)

Yes

True, if the user has admin to this organization.

removed

tinyint(1)

Yes

True, if the user was deleted.

created_at

datetime

No

The date this object was created

updated_at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

User Configuration

This table stores all user's cockpit usage configuration. Those configurations reflect first sight of tooltips, "don't show me again" checkboxes and so on.

Column Name

Data Type

Not Null

Description

id

bigint(20)

Yes

ID of this configuration

user_uuid

varchar(36)

Yes

The user's UUID

key

varchar(50)

Yes

Name of the configuration key

value

varchar(255)

No

Value of the configuration key

Role_Permission relationship

This relationship table maps which permissions are mapped to which roles.

Column Name

Data Type

Not Null

Description

role_id

bigint(20)

Yes

A valid Role ID

permission_id

bigint(20)

Yes

a valid Permission ID

Relationhip Tables

user_bot relationship

This structure represents which user has access to which virtual agent. This table will always have no value for a given user if it's field 'admin' is true since it has access to all VAs and environments.

Column Name

Data Type

Not Null

Description

id

bigint(20)

Yes

The relationship ID

user_uuid

varchar(36)

Yes

The user's UUID

environment_uuid

varchar(36)

Yes

The environment's UUID

bot_uuid

varchar(36)

Yes

UUID of the VA this user is being granted access to

created_at

datetime

No

The date this object was created

updated_at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

user_environment Relationship

This table maps which environment is accessible to which user. It is also responsible for storing the user's role within each environment. As with user_bot, this table will always have no value for a given user if it's field 'admin' is true since it has access to all environments with the admin role.

Column Name

Data Type

Not Null

Description

uuid

varchar(36)

Yes

ID representing the entity value

created_at

datetime

No

The date this object was created

updated_at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

Analytics Admin Tables

The following tables may be found within the Admin Schema, if you have an analytics engine in your Syntphony CAI system. Those tables are not found otherwise.

Analytics Pipeline

The analytics_pipeline table stores information regarding the availability and class of your analytics pipeline.

Column Name

Data Type

Not Null

Description

id

bigint(20)

Yes

ID of this analytics pipeline.

dag_class

datetime

No

The date this object was created

updated_at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

Analytics Configuration

The analytics_conf table stores information regarding your analytics' configurations, mainly in JSON structures.e.

Column Name

Data Type

Not Null

Description

uuid

varchar(36)

Yes

ID representing the entity value

created_at

datetime

No

The date this object was created

updated_at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

Analytics Pipeline Conf Organization Environment Relationship

The analytics_pipeline_conf_organization_environment e.

Column Name

Data Type

Not Null

Description

uuid

varchar(36)

Yes

ID representing the entity value

created_at

datetime

No

The date this object was created

updated_at

datetime

No

The date this object was last updated

created_by

varchar(36)

No

UUID representing the user who created this object

updated_by

varchar(36)

No

UUID representing the user who updated this object

Configuration Schema

Configuration

This table stores all environment configuration properties for each application. Most of it's data values are encrypted and this should not be read through database access.

Column Name

Data Type

Not Null

Description

id

bigint(20)

Yes

This configuration's ID

organization_uuid

varchar(36)

No

Optional field. An organization UUID to better filter values.

environment_uuid

varchar(36)

No

Optional field. AN environment's UUID to better filter values.

application

varchar(200)

No

Name of the application this configuration refers to.

profile

varchar(200)

No

The configuration profile, usually set to default.

label

varchar(200)

No

Which instance this configuration refers to.

key_

varchar(200)

No

The configuration's Key

value

varchar(800)

No

The configuration's Value

Last updated