Hybrid Chat Reporting Database Schema

agent_id

NVARCHAR(50)

PK, NOT NULL

In the case of standalone deployment, this is the same as the UMM Username.

In the case of CCX integration, this is CCX resource_login_id. 

In the case of Cisco CCE integration, it's PeripheralNumber.

name

NVARCHAR(110)

NOT NULL

In the case of standalone deployment, This is UMM fullName. 

In the case of Cisco Finesse integration, this is concatenated resource_first_name + space + last_name.

createdAt

DATETIME

NOT NULL

The time when this record was created/inserted into the database.

updatedAt

DATETIME

NOT NULL

The time when the agent record was last updated in the database.

is_deleted

BIT

NOT NULL

If the agent is deleted, the value should be 1. 

If the agent is active, the value should be 0. 

id

INT

PK
NOT NULL

Autoincrement

agent_id

NVARCHAR(50)

FK
NOT NULL

Foreign key from the Agent table

mrd_id

INT

FK
NOT NULL

Foreign key from the MRD table

state

VARCHAR(50)

NOT NULL

Hybrid Chat MRD states. 

[LOGGED_IN | READY | ACTIVE | NOT_READY | BUSY | INTERRUPTED | UNKNOWN | LOGOUT]

mode

BIT

NOT NULL

If agent mode is routable, the value should be 1.
If agent mode is not routable, the value should be 0.
It is used for Pending NOT READY state handling. Routable mode specifies that an agent can accept new chats either he/she is in READY / ACTIVE states. If an agent's mode is not routable, he/she cannot accept new chat requests and after current chats (if any) end, the agent state will be changed to NOT READY.

reason_code

INT

FK, NULL

Foreign key from the Reason Code table - Reserved for future use.

event_time

DATETIME

NOT NULL

The time when the state change event occurred.

createdAt

DATETIME

NOT NULL

The time when the record is inserted in this table.

login_time

DATETIME

NOT NULL

The time when the agent logged in. For all agent state change events, this value remains the same for a single logged-in session.

queue_id

INT

PK, NOT NULL

The auto-generated auto-increment Queue identifier. 

queue_name

NVARCHAR(50)

NOT NULL

The queue name as mentioned in MRE UI. This name cannot be changed in MRE once created.

service_level_threshold

SMALL INT

NOT NULL

Option to add the SL threshold of the queue at the time of the creation. This is the time in seconds. It contains only positive values from 0-MAX_INT.

service_level_type

SMALL_INT

NOT NULL

The value will be (1,2, or 3)

1- Ignore abandoned chats (Subtracted from the Offered Chats during Service Level Calculation).

2- Abandoned chats have a negative impact (Not included at all in the Service Level Calculation).

3- Abandoned chats have a positive impact ( Added with the Service Level answered chats). 

createdAt

DATETIME

NOT NULL

The time when the queue was created in MRE/MRE_UI. 

updatedAt

DATETIME

NOT NULL

The time when the queue is updated in MRE/MRE_UI. 

is_deleted

BIT

NOT NULL

If the queue is deleted, the value should be 1. 

If the queue is active, the value should be 0.

id

INT

PK
NOT NULL

auto increment

session_id 

task-id

VARCHAR (50)

NULL

This ID is assigned to every chat request for queuing to the agent by the Routing Engine. If the chat request is re-routed, the same session_id is preserved. There can be multiple sessions for the same conversation. 

In the customer-to-bot conversation leg, the value is NULL. This is not a chat session-id. If you want to see individual sessions in a conversation, use customer_id.

conversation_id chat-session-id

VARCHAR (100)

NOT NULL

It's the Conversation ID from the Chat Server. It's unique ID for individual chat session. 

customer_id

VARCHAR (100)

NOT NULL

A random id assigned to the customer by the Chat Server as a chat participant. It has nothing to do with customer identification.

customer_name

NVARCHAR (100)

NOT NULL

The name of the customer as received from the customer channel. For: 

• Web Chat, it's the concatenation (FName, LName)

• WhatsApp, it's the WhatsApp id sent by Twilio i.e +<phone-number> 

• Facebook, it's the concatenation of customer account first name & last name

• Viber, it's the concatenation of customer account first name & last name

• SMS, it's the customer phone number

customer_nd

VARCHAR (100)

NOT NULL

Customer unique identifier

1. Viber UserID in case of Viber - stored after Base64 encoding

2. Facebook UserID in case of Facebook - stored after Base64 encoding

3. Phone Number in case of WhatsApp, SMS, Web Chat - stored as received

queue_id

int

FK, NULL

Foreign key from the Queue table

agent_id

NVARCHAR(50)

FK, NULL

Foreign key from the Agent table. The value will be NULL for the customer-2-bot leg.

bot_id

NVARCHAR(50)

FK, NULL

The ID of the bot that handled this chat leg.

Foreign key from the Bots table

type

VARCHAR (50)

NOT NULL

INBOUND | TRANSFER | CONFERENCE

• INBOUND, when a customer's chat request is received

• TRANSFER, when a chat is transferred to another agent

• CONFERENCE, when the chat is conferenced.

enqueue_time

DATETIME

NULL

The time when the chat was enqueued. If NULL, the chat was handled by the chatbot and wasn't enqueued for routing.

start_time

DATETIME

NULL

The time when the task state transitions to ACTIVE. This happens when the agent accepts the chat or when the chat is automatically started with the agent due to AUTO_ACCEPT.
If NULL means that the agent did not accept the chat request.

end_time

DATETIME

NULL

• For Transferred chat, it's the time of transfer.

• For RONA, it's the event time when the chat is re-queued on no response from an agent

• When an agent leaves the conference, end_time is updated. 

• For all other cases, it's the time when the task sate changed to CLOSED. That's when the chat is ended either by the customer, agent, or network. 

• If NULL, the chat is not ended yet.

ended_by

VARCHAR(50)

NULL

Specifies which participant ended the chat, or it was ended due to a network outage.
Possible values are CUSTOMER | AGENT | NETWORK | RONA | BOT

• RONA - In case ended_by RONA, the agent_id contains ID of the agent and start_time is null.

chat_link

NVARCHAR(250)

NULL

This is the chat history URL for the conversation, so all messages exchanged on that conversation will be available on this link.

createdAt

DATETIME

NOT NULL

It's the time when the record was inserted into the database

updatedAt

DATETIME

NULL

It's the time when the record was updated into the database

channel_code

INT

FK  NULL

Foreign key from the Channel table

channel_data_1

NVARCHAR(250)

NULL

Channel data parameter 1

channel_data_2

NVARCHAR(250)

NULL

Channel data parameter 2

channel_data_3

NVARCHAR(250)

NULL

Channel data parameter 3

channel_data_4

NVARCHAR(250)

NULL

Channel data parameter 4

channel_data_5

NVARCHAR(250)

NULL

Channel data parameter 5

session_start_time

DATETIME

NULL

The time when this chat session was started. This is application event time.

channel_code

INT

PK, NOT NULL

Name

VARCHAR(50)

NOT NULL

Multiple channel instances of the same channel may be specified as WhatsApp099, WhatsApp9800

createdAt

DATETIME

NOT NULL

The time when the record is created. 

updatedAt

DATETIME

NOT NULL

The time when the record is updated.

is_deleted

BIT

NOT NULL

If the queue is deleted, the value should be 1. 

If the queue is active, the value should be 0. 

mrd_id

INT

PK NOT NULL

Name

VARCHAR(50)

NULL

createdAt

DATETIME

NOT NULL

The time when the record is inserted into this table

updatedAt

DATETIME

NOT NULL

The time when the record is updated in this table

is_deleted

BIT

NOT NULL

If the queue is deleted, the value should be 1. 

If the queue is active, the value should be 0. 

bot_id

NVARCHAR(50)

NOT NULL

Identifier of the bot that participated in this conversation

bot_name

NVARCHAR(50)

NOT NULL

Name of the bot as given in the bot-connector configurations

createdAt

DATETIME

NOT NULL

The time when the record is inserted into this table

updatedAt

DATETIME

NOT NULL

The time when the record is updated in this table

is_deleted

BIT

NOT NULL

If the bot is deleted, the value should be 1. 

If the bot is active, the value should be 0. 

reason_code

INT

PK
NOT NULL

state

VARCHAR(50)

NOT NULL

Hybrid Chat MRD states. 

[NOT_READY | UNKNOWN | LOGOUT]

Description

VARCHAR(100)

NULL

createdAt

DATETIME

NOT NULL

The time when the record is inserted into this table

updatedAt

DATETIME

NOT NULL

The time when the record is updated in this table

is_deleted

BIT

NOT NULL

If the queue is deleted, the value should be 1. 

If the queue is active, the value should be 0. 

id

INT

PK

Autoincrement

session_id

VARCHAR(100)

NOT NULL

Chat session id

session_type

VARCHAR(50)

NOT NULL

Chat session type e.g. BOT | AGENT | HYBRID

conversation_id

VARCHAR(100)

NOT NULL

Conversation Id

customer_nd

NVARCHAR(100)

NOT NULL

Customer identifier

start_time

DATETIME

NOT NULL

Chat session start time

end_time

DATETIME

NOT NULL

Chat session end time

transcript_url

NVARCHAR(250)

NOT NULL

Chat transcript URL, not to be confused by chat history URL.

channel_code

INT

NOT NULL

Channel for which this chat session was initiated