PCM Raw data Basic tables

The PCM Raw data basic tables are:

cm_category_choices
cm_channels
cm_customers
cm_forms
cm_organisations
cm_parent_sub_tickets
cm_team_queues
cm_teams
cm_templates
cm_tenants
cm_users

cm_category_choices

Contains/used for: All tickets may (or may not) be related to categories, for which a set of category choices are available - and can be chosen/tagged. What's chosen is stored in this table.

Column name	Data type	Description
category_choice_id	bigint	Identifier for the category field choice
category_id	int	Identifier for the category field
ticket_id	bigint	Ref. to the ticket, foreign key to cm_tickets.
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
category_name	nvarchar(200)	Name given to a category field
category_value	nvarchar(1000)	Value of the populated category field
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData
ticket_number	int	Ticket reference number

cm_channels

Contains/used for: <...to be filled out....>

Column name	Data type	Description
channel_id	int	Unique ID of the channel
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
channel_type	nvarchar(200)	SMS / Email / API
name	nvarchar(1000)	Name applied to the channel
dte_created_at	datetime2	The date/time the channel was created
dte_updated_at	datetime2	The date/time the channel was last updated
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData

cm_customers

Contains/used for: All the external customers/contacts for a tenant (= a Puzzel customer) is represented with a record in this table. All tickets (in table cm_tickets) will refer to such a customer through the customer_id column.

Column name	Data type	Description
customer_id	bigint	Unique ID of the customer record
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
name	nvarchar(1000)	Name of the customer
dte_created_at	datetime2	Date/Time of the customer record creation
dte_updated_at	datetime2	Date/Time of the customer record last update
dte_deleted_at	datetime2	Date/Time of the customer record deletion
dte_expiration_date	datetime2	Date/Time of retention period expiration
created_from	nvarchar(200)	Indicate source of the customer record creation
email	nvarchar(1000)	Email address associated to customer record
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData

cm_forms

Contains/used for: A ticket may (or may not) relate to a form. If such a relation is made, the form_id column in the cm_tickets table will refer to a form in this table.

Column name	Data type	Description
form_id	int	Unique ID of a Form
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
name	nvarchar(1000)	Name given to a Form
dte_created_at	datetime2	Date/Time of Form creation
dte_updated_at	datetime2	Date/Time of Form latest update
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData

cm_organisations

Contains/used for: A tenant (i.e. a Puzzel customer) can optionally use the dimentsion 'organisations'. If used, you will find the name and description for each organisation record in this table.

Column name	Data type	Description
org_id	int	Unique ID for an Organisation
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
name	nvarchar(1000)	Name given to the Organisation
description	nvarchar(1000)	Description given to the Organisation
dte_deleted_at	datetime2	Date/Time of Organisation deletion
dte_created_at	datetime2	Date/Time of Organisation creation
dte_updated_at	datetime2	Date/Time of Organisation latest update
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData

cm_parent_sub_tickets

Contains/used for: Tickets might be linked together through a parent/child relationship. When done, such relations are shown in this table.

Column name	Data type	Description
parent_sub_id	int	Unique ID for a child ticket record
parent_ticket_id	bigint	Unique ticket_id from table: cm_tickets of linked Parent Ticket
sub_ticket_id	bigint	Unique ticket_id from table: cm_tickets of linked Child Ticket
dte_created_at	datetime2	Date child ticket created
dte_updated_at	datetime2	Date child ticket latest update
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData

cm_team_queues

Contains/used for: Mapping 'Teams' in Puzzel Case Management to Puzzel Contact Center 'Queues'

Column name	Data type	Description
team_queue_id	int	Unique ID for the team/queue record
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
team_id	int	Ref. to table cm_teams.
queue_key	nvarchar(100)	Ref. of the mapped PCC queue
dte_created_at	datetime2	Date/Time the mapping was created
dte_updated_at	datetime2	Date/Time the mapping was updated
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData

cm_teams

Contains/used for: Information relating to the definition of a Team

Column name	Data type	Description
team_id	int	Unique ID for the 'Team' database record
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
name	nvarchar(1000)	Name given to the 'Team'
dte_created_at	datetime2	Date/Time of the Team creation
dte_updated_at	datetime2	Date/Time of the Team latest update
dte_deleted_at	datetime2	Date/Time of the Team deletion
puzzel_id	int	Relating to legacy PCC integration records
pcc_access_point	nvarchar(200)	Relating to legacy PCC integrations
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData

cm_templates

Contains/used for: Storing template records

Column name	Data type	Description
template_id	int	Unique ID for a template record
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
name	nvarchar(500)	Name of the template
description	nvarchar(500)	Description field of the template
dte_created_at	datetime2	Date/Time template was created
dte_updated_at	datetime2	Date/Time template was last updated
dte_deleted_at	datetime2	Date/Time template was last deleted
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData

cm_tenants

Contains/used for: A record here represent a PCM customer seen from Puzzel's side. Normally we will have only one record here, but bringing the tenant_id column into most of the tables, combined with this cm_tenants table, allows us to have more than one tenant represented in one rawdata database. Some bigger customers might want this.

Column name	Data type	Description
tenant_id	int	Unique ID given to a tenant record
name	nvarchar(1000)	Name of the Tenant
dte_deleted_at	datetime	Recorded date of a PCM tenant deletion
dte_created_at	datetime	Recorded date of a PCM tenant creation
dte_updated_at	datetime	Recorded date of a PCM tenant configuration change
puzzel_service_id	nvarchar(200)	??
puzzel_username	nvarchar(200)	??
puzzel_customer_number	nvarchar(200)	Customer number used for Puzzel integrations with PCM
solution_id	nvarchar(200)	Unique ID given to each Puzzel solution attached to the customer
customer_key	nvarchar(200)	Master Puzzel Customer Record Number
description	nvarchar(510)	??
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData

cm_users

Contains/used for: This table is used for User configurations within PCM

Column name	Data type	Description
user_id	bigint	Unique ID given to a user record within PCM
tenant_id	int	Ref. to tenant, foreign key to cm_tenants.
puzzel_id	int	Linked PCC User ID (used for mapping between PCM and PCC)
name	nvarchar(1000)	Name given to the user
username	nvarchar(200)	username specified for the user
dte_created_at	datetime2	Date/Time the user record was created
dte_updated_at	datetime2	Date/Time the user record was last edited
dte_deleted_at	datetime2	Date/Time the user record was deleted
utc_transfer_timestamp	datetime2	Date/timestamp that the data was transferred to RawData