Reporting Database Schema

Introduction

This document covers the SQL-based reporting database schema.

The naming convention for table names and field names is an all-small snake case. Example: multiple_word_variable.

Records are updated in the following tables in the reporting database, as per the scheduled interval of all ETL jobs.
If any changes are done to the configurations, the previous report records are not updated and the new changes are applied to the new reporting data only.
This schema guide provides details about the reporting database tables which are used only for historical reporting. For real-time dashboards, the data is extracted through real-time REST APIs and available on real-time dashboards for supervisors.

On This Page

Channels and Channel Sessions

channel_type

A channel type describes the type/class of the channels, for example, WHATSAPP, SMS, WEB. Multiple channels of a channel type can be created.

See more on _Channel related terms

See Administrator Guide -> Add Channel Type for more details on Channel types.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
channel_type_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique id to identify this channel type
channel_type_name	NOT NULL	[nvarchar](50)	[varchar](50)	Records the name for this channel type, any alphanumeric value

channel

A channel instance is created when a new communication channel is registered by the admin. It includes all the configuration details of a channel, for example, the channel name, service identifier, channel mode, etc. A channel is of a particular channel type. For instance, we can create two channels WhatsApp-external and WhatsApp-internal, both of a channel type WHATSAPP.

See more on _Channel related terms

See Administrator Guide -> Step 11: Set up a new Channel for more details on Channel types.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
channel_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique id to identify this channel
channel_name	NOT NULL	[nvarchar](50)	[varchar](50)	Records the name for this channel
channel_type_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the channel type for this channel
channel_mode	NOT NULL	[nvarchar](20)	[varchar](20)	Indicates the channel mode for this channel HYBRID - Both the bot and the human agents are engaged on communication with the customer. BOT - Only bot is engaged in communication with the customer. AGENT - Only human agents are engaged in communication with the customer.
service_identifier	NOT NULL	[nvarchar](MAX)	[varchar](250)	Unique identifier like a phone number or URL associated to this channel. Example: For a WhatsApp channel, the business WhatsApp account's phone number which is registered for receiving customer's messages will come here.
created_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is created.
updated_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is updated.
is_deleted	NOT NULL	[bit]	[TINYINT](1)	Indicates that this channel has been deleted from the system. (Used for soft deletion)

channel_session

A customer can converse on multiple channels at a time in one conversation. A new channel session is created every time a customer initiates a session on a channel. For instance, a customer first opens up a chat from Webchat and later on, sends a message from its WhatsApp.

See more on Channel related terms

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
session_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique id to identify this channel session.
conversation_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the conversation, this channel session is associated to
channel_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the channel, this channel session is associated to
start_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when the channel session is started.
end_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when the channel session is ended.
total_session_duration	NOT NULL	[int]	[INT]	Records the value of total duration of this channel session i.e. end_time minus start_time
disposition	NOT NULL	[nvarchar](20)	[varchar](20)	Indicates the reason why the channel session was ended. AGENT - the human agent ended the channel session. This is valid in case of Pull-based chats from the list by clicking the "Close" button. CUSTOMER - the customer ended the session. This happens in case when the web chat widget is closed by the customer. Only true for web chat channels. INACTIVITY - the customer was inactive for a configured timeout (timeout is managed by the Bot). NETWORK - the conversation ended due to some network error usually in case when the network is disconnected. FORCE_CLOSED - the system (ExpertFlow CX) ends the channel session.
bot_activity_count	NOT NULL	[int]	[INT]	Records the number of messages sent by the bot on this channel session
agent_activity_count	NOT NULL	[int]	[INT]	Records the number of messages sent by the agent(s) on this channel session
customer_activity_count	NOT NULL	[int]	[INT]	Records the number of messages sent by the customer on this channel session
~~avg_bot_confidence_level~~	~~NOT NULL~~	[decimal]	[DECIMAL(5,5)]	Reserved for future. For each bot response, there is a percentage value that tells how confidently the bot was able to answer a query. This field gives us the average value for all bot-confidence values.
record_creation_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when the record is created in source DB i.e. MongoDB
device	NULL	[nvarchar](40)	[varchar](40)
browser_type	NULL	[nvarchar](40)	[varchar](40)
country	NULL	[nvarchar](40)	[varchar](40)
language	NULL	[nvarchar](40)	[varchar](40)

Conversations

conversation

A conversation entity records the customer's conversation details. A conversation consists of all channel sessions where a customer is conversing, the conversation participants (bots, agents, customers) and optionally also some conversation data.

See more about Conversations on Conversation ControlUnique id to identify this conversation

This table only records the essential conversation fields. There are separate tables for conversation data and conversation participants.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
conversation_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)
room_id (reserved for future)	FK, NULL	[nvarchar](50)	[varchar](50)	Identifies the room this conversation is a part of. Currently, null since a conversation is not a part of any room.
customer_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the customer with whom this conversation is associated.
customer_name	NOT NULL	[nvarchar](50)	[varchar](50)	Records the customer name with whom this conversation is associated.
start_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this conversation was started.
end_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this conversation was ended.
conversation_duration	NOT NULL	[int]	[INT]	Records the value of total duration of this conversation i.e. end_time minus start_time
bot_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifier of the bot who was a participant of this conversation.
disposition	NOT NULL	[nvarchar](20)	[varchar](20)	This is the reason why the conversation was closed. BOT-HANDLED - It is when the bot handled the conversation. This could happen in the following scenarios: If there is no AGENT_SUBSCRIBED event in the conversation events and there is no agent task with CANCELLED disposition. If the latest task in the conversation has the disposition i.e. NO_AGENT_AVAILABLE. For instance, When the task was routed to the agent but the agent did not accept and RONA occurred, the conversation was re-queued but no agents were available to handle the conversation. When the agent was handling the conversation and it got logged out, the conversation was re-queued but no agents were available to handle the conversation. When the customer stays in the conversation but the customer inactivity timeout occurs, the conversation is closed by the bot. AGENT-HANDLED - Task with the state CLOSED and with reason code DONE. This is in case when the agent successfully answered the customer queries and closes the conversation from Agent Desk while the customer also closes the chat (for instance, in case of webchat). If the agent closes but the customer stays in the conversation, the conversation is not considered to be closed yet. The customer might or might not engage the bot again so the conversation now activates with the bot. ABANDONED - Task with the state CLOSED and with reason code CANCELLED There can be multiple tasks created for a conversation. We will sort these tasks w.r.t the date and time and see the latest ones. The disposition will be considered on last task.
direction	NOT NULL	[nvarchar](20)	[varchar](20)	Records the direction of conversation i.e. INBOUND/OUTBOUND. There can be multiple channel sessions (INBOUND/OUTBOUND) in a conversation. We'll consider the conversation's direction based on the first channel session's direction in a conversation.

conversation_data

A conversation can have key-value pairs to record any arbitrary additional data required. This is known as the conversation data. A single key-value pair for a conversation is inserted as one record in this table.

See Conversation Data for more details.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
conversation_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the conversation for which this conversation data record is inserted
key	NOT NULL	[nvarchar](50)	[varchar](50)	Any alphanumeric value
value	NOT NULL	[nvarchar](100)	[varchar](100)	Any alphanumeric value

conversation_participant

Conversation participants are the participants that take part in a conversation. For example , customers, bots and agents. Each record in the table represents a Conversation Participant.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	A unique identification of a participation by agent/customer/bot etc.
participant_id	NOT NULL	[nvarchar](50)	[varchar](50)	Unique id (Customer ID/ Bot ID / Agent ID) to identify a participant.
conversation_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the conversation this participant is part of.
participant_role (AGENT \| CUSTOMER \| EXTERNAL)	NOT NULL	[nvarchar](30)	[varchar](30)	Indicates the role of a participant in the conversation AGENT - If the participant is an agent CUSTOMER - If the participant is one of the channel_sessions that a customer is conversing on. EXTERNAL - Other participants like a bot etc.
record_creation_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when the record is created in source DB i.e. MongoDB

wrapup_detail

A conversation's wrap-up details are recorded in this table.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
wrapup_time	NOT NULL	[DateTime]	[DateTime](3)	Records the time when the wrap-up was applied.
conversation_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the conversation to which the wrap-up was applied
wrapup_label	NULL	[nvarchar](50)	[varchar](50)	The wrap-up label for the applied wrap-up
category_name	NULL	[nvarchar](50)	[varchar](50)	Records the wrap-up category
agent_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the agent who did the wrap up on the associated conversation
notes	NULL	[nvarchar](350)	[varchar](350)	Optional notes entered by agents while applying wrap up to a conversation
record_creation_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when the record is created in source DB i.e. MongoDB

Room (reserved for future)

room

A room is a group of customers conversing among each other and with the system entities like a bot or human agents. One customer participating in a room is inserted as one record in this table

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
room_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique id to identify the room
customer_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies a customer participating in this room
creation_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is created in this table.

Bots

bot

This table records the details of a bot configured in the system by the admin. One bot's details are added as a single record in this table

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
bot_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique id of identifying the bot
bot_name	NOT NULL	[nvarchar](50)	[varchar](50)	Records the name of the bot.
created_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is created.
updated_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is updated.
is_deleted	NOT NULL	[bit]	[TINYINT](1)	Indicates the bot has been deleted from the system.(Used for soft deletion)

Agents and States

agent

This table records the details of an agent. One agent's details are inserted as a single record in the table.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
agent_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique id to identify this agent
agent_name	NOT NULL	[nvarchar](100)	[varchar](100)	Records the agent name
username	NOT NULL	[nvarchar](100)	[varchar](100)	The agent's unique username is logged.
agent_extension	NULL	[nvarchar](10)	[varchar](10)	Records the agent's extension
team_id (reserved for future)	FK, NULL	[nvarchar](50)	[varchar](50)	Identifies the team this agent is part of. This field is currently null since teams are not yet set up
created_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is created.
updated_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is updated.
is_deleted	NOT NULL	[bit]	[TINYINT](1)	Indicates the agent has been deleted from the system. (Used for soft deletion)

agent_mrd_state

This table records the details of an agent's MRD state at a particular instance. Each time an agent changes its state on the MRD, a new record is inserted into this table.

Learn more about Agent MRD state transitions on Agent Guide

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
login_date_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when MRD state changed to login.
agent_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the Agent whose Agent MRD state is recorded here
mrd_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the MRD this MRD state is associated to
agent_mrd_state	NOT NULL	[nvarchar](20)	[varchar](20)	Records the MRD state. For example 'READY', 'ACTIVE' etc.
duration	NOT NULL	[int]	[INT]	The length of time MRD spent in the specified state as recorded in the "agent_mrd_state" column. This duration is recorded in seconds. Note: When the duration value is "-1" , it indicates that the MRD is presently in its current state and has not transitioned to the succeeding state yet.
state_change_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when the MRD state was changed

agent_state

This table records the details of an agent's state at a particular instance. For example, if this agent's state is 'READY' then 'NOT_READY' and then 'READY' again, three records will be inserted into the table.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
login_date_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when MRD state changed to login.
agent_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the agent whose Agent state is recorded here.
agent_state	NOT NULL	[nvarchar](20)	[varchar](20)	Records the Agent state value. For example, 'READY', 'NOT_READY', 'LOGOUT' etc.
reason_code	NULL	[nvarchar](100)	[varchar](100)	Records the reason why agent switched to NOT_READY or LOGOUT state. For example , SHORT_BREAK , TIME_OFF etc
duration	NOT NULL	[int]	[INT]	The length of time agent spent in the specified state as recorded in the "agent_state" column. This duration is recorded in seconds. Note: When the duration value is "-1" , it indicates that the Agent is presently in its current state and has not transitioned to the succeeding state yet.
state_change_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when the Agent state was changed.

agent_team (reserved for future)

A team is a list of agents that are grouped together to be managed by a contact center supervisor.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
team_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique id to identify this team. ETL job will put dummy values.
team_name	NOT NULL	[nvarchar](50)	[varchar](50)	Records the team name, any alphanumeric value
description	NULL	[nvarchar](100)	[varchar](100)	Records a short description of this team's value proposition
created_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is created.
updated_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is updated.
is_deleted	NOT NULL	[bit]	[TINYINT](1)	Indicates the team has been deleted from the system. (Used for soft deletion)

Routing

queue

This table records the properties of a routing queue. Whenever a human agent is required for a conversation, a request to assign an agent is created by the system and offered to a precision queue.

To learn more about queues, see precision routing

Also, see Unified Admin Guide to learn more about each queue properties listed below

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
queue_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique Id to identify this queue
queue_name	NOT NULL	[nvarchar](50)	[varchar](50)	Records the queue name, any alphanumeric string
sl_type	NOT NULL	[int]	[INT]	Service level type can have the following values 1 = Ignore Abandoned Chats 2 = Abandoned Chats have a Negative Impact 3 = Abandoned Chats have a Positive Impact
sl_threshold	NOT NULL	[int]	[INT]	Indicates the number of seconds in which a request must be routed to an agent
created_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is created.
updated_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is updated.
is_deleted	NOT NULL	[bit]	[TINYINT](1)	Indicates that the queue has been deleted from the system.(Used for soft deletion)

agent_task

Whenever a conversation needs a human agent, Expertflow CX creates an agent task to be routed to an agent.

This is also true when a conversation is re-queued to the queue for whatever reasons, say, RONA occurred, no agents were available or agent got logged out.

This means that a conversation can have multiple agent tasks created for each agent joining the conversation.

This table keeps the details of an agent task whenever a new task is created for a conversation.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
agent_task_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique id to identify this task
conversation_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)	Identifies the conversation this task is created for
agent_id	FK, NULL	[nvarchar](50)	[varchar](50)	Identifies the agent which was assigned to this task. If the task was ended while in the queued state then this field is null
task_queue_time	NULL	[DateTime]	[DateTime](3)	Records the date and time when the task is offered to a routing queue like in case of PUSH mode routing. This field is NULL in following cases : If the task routing is skipped and the task is directly assigned to the agent, like in the case of pull mode routing. If agent has initiated the chat i.e. OUTBOUND chat.
task_reserved_time	NULL	[DateTime]	[DateTime](3)	Records the date and time when an agent is reserved for this task. This field is NULL in following cases : If the task is directly assigned to the agent, like in the case of pull mode routing. If agent has initiated the chat i.e. OUTBOUND chat. If the task was ended while in the queued state i.e. for PUSH mode routing.
task_answered_time	NULL	[DateTime]	[DateTime](3)	Records the date and time when the reserved agent accepts the task. This field is null if the agent never answered the task. For example in following cases : if the task was closed while in the queued state. If the task was presented to the agent but the agent did not accept it.
queue_id	FK, NULL	[nvarchar](50)	[varchar](50)	Identifies the queue in which this task is enqueued for routing. This field is NULL in following cases : In case where queue routing is skipped and the task is directly assigned to an agent like in the case of pull mode routing. If agent has initiated the chat i.e. OUTBOUND chat.
task_queue_duration	NOT NULL	[int]	[INT]	For PUSH mode routing , it will record the task_reserved_time minus task_queue_time value. It will be 0 in following cases : For PULL mode routing. If agent has initiated the chat i.e. OUTBOUND chat. If the task was ended while in the queued state i.e. for PUSH mode routing.
task_alert_duration	NOT NULL	[int]	[INT]	For PUSH mode routing , it will record the task_answered_time minus task_reserved_time value. It will be 0 in following cases : For PULL mode routing. If agent has initiated the chat i.e. OUTBOUND chat. If the task was ended while in the queued state i.e. for PUSH mode routing.
task_end_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when the task was ended/closed.
task_disposition	NOT NULL	[nvarchar](30)	[varchar](30)	This is basically the reason why the task was ended. The reason could be one of the following. DONE RONA RESPONSE_TIMEOUT NO_AGENT_AVAILABLE REROUTE CANCELLED AGENT_LOGOUT TRANSFERRED FORCE_CLOSED.
record_creation_time	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when the record is created in source DB i.e. MongoDB
wrapup_duration (future use)	NOT NULL	[int]	[INT]
mrd_id	FK, NULL	[nvarchar](50)	[varchar](50)	Unique Id to identify the MRD through which this task is created.
task_direction	NOT NULL	[nvarchar](50)	[varchar](50)	This records the direction how the task was created. The directions can be one of the following. INBOUND OUTBOUND CONSULT CONSULT_TRANSFER: A task direction changed from Consult to transfer, when the call is transferred to a consulted agent CONSULT_CONFERENCE: A task direction changed changed from Consult to conference, when the call is conferenced to a consulted agent DIRECT_TRANSFER: Directly transferred to an agent (the agent is not consulted first in this case) DIRECT_CONFERENCE : Directly transferred to an agent (the agent is not consulted first in this case)
task_mode	NOT NULL	[nvarchar](50)	[varchar](50)	This records the mode how the task was created. The mode can be one of the following. AGENT : Directly assigned to an agent. QUEUE : Task pushed to queue to route to any agent.
routing_mode	NOT NULL	[nvarchar](50)	[varchar](50)	This records one of the following outing mode. PUSH : The incoming request was pushed to a queue to be routed to agents. PULL : Broadcasted to a Pull-based List to be taken up by any agents subscribed to the List.
channel_session_id	NOT NULL	[nvarchar](50)	[varchar](50)	A unique identifier for this channel session that is associated with this task.
list_id	NULL	[nvarchar](50)	[varchar](50)

media_routing_domain

This table records the properties of a media routing domain (MRD.) .

To learn more about queues, see Media Routing Domain

Also, see Unified Admin Guide to learn more about each MRD properties listed below

Field	Constraints	Data type (MsSQL)	Data type (MySQL)	Description
mrd_id	PK, NOT NULL	[nvarchar](50)	[varchar](50)	Unique Id to identify this MRD.
mrd_name	NOT NULL	[nvarchar](50)	[varchar](50)	Records the MRD name, any alphanumeric string
description	NULL	[nvarchar](250)	[varchar](250)	Short description of the media routing domain
max_task_request	NOT NULL	[int]	[INT]	This is the max tasks that the MRD can receive.
is_interruptible	NOT NULL	[bit]	[TINYINT](1)	Tells whether a conversion of a specific media domain can be interrupted at the agent's end by a higher priority conversation. For instance, a chat conversation on WhatsApp can be interrupted by a voice call. On the contrary an ongoing voice call cannot be interrupted by a chat request.
is_managed_by_re	NOT NULL	[bit]	[TINYINT](1)	When the value is true, it indicates that the Routing Engine is responsible for managing this MRD. Conversely, if the value is false, it means that other sources, such as Cisco, are in charge of managing this specific MRD.
created_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is created.
updated_at	NOT NULL	[DateTime]	[DateTime](3)	Records the date and time when this record is updated.
is_deleted	NOT NULL	[bit]	[TINYINT](1)	Indicates that the queue has been deleted from the system.(Used for soft deletion)

IVR

ivr

This table records the properties of a IVR.

Field	Constraints	Data type (MsSQL)	Data type (MySQL)
conversation_id	FK, NOT NULL	[nvarchar](50)	[varchar](50)
help_line	NULL	[nvarchar](50)	[varchar](50)
start_date_time	NULL	DATETIME	DATETIME
caller_number	NULL	[nvarchar](20)	[varchar](20)
main_menu_selection	NULL	[nvarchar](20)	[varchar](20)
sub_menu_option_selected	NULL	[nvarchar](20)	[varchar](20)
journey	NULL	[nvarchar](250)	[varchar](250)
selection	NULL	[nvarchar](250)	[varchar](250)
duration	NOT NULL	INT	INT
language	NULL	[nvarchar](20)	[varchar](20)
call_id	NOT NULL	[nvarchar](50)	[varchar](50)
end_date_time	NULL	DATETIME	DATETIME
start_direction	NULL	[nvarchar](50)	[varchar](50)
call_disposition	NULL	[nvarchar](50)	[varchar](50)
record_creation_time	NOT NULL	DATETIME	DATETIME

LIST

list