Hybrid Chat Reporting Database Schema

Entity Relationship Diagram

This is the SQL server reporting database schema of Hybrid Chat.

Agents

Field	Data Type	Constraints	Description
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.

Agent_MRD_State_Events

This table contains agent state change within that MRD. Upon each state change event, a record is inserted in this table.

Field	Data Type	Constraints	Description
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.

Queues

Field	Data Type	Constraints	Description
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.

Queue Chat Details

Each record in this table represents a chat handling session by a contact center resource (agent or bot).

Field	Data Type	Constraints	Description
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_idCHAT-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 Viber UserID in case of Viber - stored after Base64 encoding Facebook UserID in case of Facebook - stored after Base64 encoding 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.

Channels

Field	Data Type	Constraints	Description
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.

MRDs

Field	Data Type	Constraints	Description
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.

Bots

Field	Data Type	Constraints	Description
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 Codes

This table is intended for future use

Field	Data Type	Constraints	Description
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.

Chat Session

Session-wise detailed data.

For Future Use

Chat Volume by channel (day/hour wise)
- channel
- day
- session-count-by-channel
- average-session-duration

Field	Data Type	Constraints	Description
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

[data-colorid=alj61m9abg]{color:#202124} html[data-color-mode=dark] [data-colorid=alj61m9abg]{color:#dbdcdf}Entity Relationship Diagram

Bots

Entity Relationship Diagram