# Zendesk Chat schema ERD

Table and field reference for the Fivetran Zendesk Chat connector.

## Table & Field Reference

### agent_status_history

| Column | Type | Keys |
| --- | --- | --- |
| agent_id | Long | PK, FK |
| account_id | Long |  |
| event_id | String |  |
| value | String |  |

### agent_engagement_history

| Column | Type | Keys |
| --- | --- | --- |
| agent_id | Long | PK, FK |
| account_id | Long |  |
| event_id | String |  |
| value | String |  |

### engagement

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| agent_id | Long | FK |
| chat_id | String | FK |
| department_id | Long | FK |
| accepted | String |  |
| assigned | String |  |
| comment | String |  |
| duration | Long |  |
| rating | String |  |
| started_by | String |  |
| timestamps | Instant |  |

### visitor

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| email | String |  |
| name | String |  |
| notes | String |  |
| phone | String |  |

### chat_history

| Column | Type | Keys |
| --- | --- | --- |
| index | Long | PK |
| timestamps | Instant | PK |
| chat_id | String | PK, FK |
| agent_id | Long | FK |
| department_id | Long | FK |
| prev_department_id | Long | FK |
| channel | String |  |
| msg | String |  |
| msg_id | String |  |
| name | String |  |
| sender_type | String |  |
| type | String |  |

### chat_agent

| Column | Type | Keys |
| --- | --- | --- |
| agent_id | Long | PK, FK |
| chat_id | String | PK, FK |

### chat_tag

| Column | Type | Keys |
| --- | --- | --- |
| tags | String | PK |
| chat_id | String | PK, FK |

### chat

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| visitor_id | String | FK |
| comment | String |  |
| count_agent | Long |  |
| count_total | Long |  |
| count_visitor | Long |  |
| dropped | Boolean |  |
| duration | Long |  |
| missed | Boolean |  |
| preview | String |  |
| rating | String |  |
| response_time_avg | Long |  |
| response_time_first | Long |  |
| response_time_max | Long |  |
| session_browser | String |  |
| session_city | String |  |
| session_country_code | String |  |
| session_country_name | String |  |
| session_end_date | Instant |  |
| session_id | String |  |
| session_ip | String |  |
| session_platform | String |  |
| session_region | String |  |
| session_start_date | Instant |  |
| session_user_agent | String |  |
| started_by | String |  |
| timestamps | Instant |  |
| triggered | Boolean |  |
| triggered_response | Boolean |  |
| type | String |  |
| unread | Boolean |  |
| zendesk_ticket_id | Long |  |

### department_member

| Column | Type | Keys |
| --- | --- | --- |
| agent_id | Long | PK, FK |
| department_id | Long | PK, FK |

### department

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| description | String |  |
| enabled | Boolean |  |
| name | String |  |

### ban_visitor

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| visitor_id | String | FK |
| reason | String |  |

### ban_ip

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| ip_address | String |  |
| reason | String |  |

### agent_role

| Column | Type | Keys |
| --- | --- | --- |
| agent_id | Long | PK |
| role | String | PK |

### agent

| Column | Type | Keys |
| --- | --- | --- |
| id | Long | PK |
| create_date | Instant |  |
| display_name | String |  |
| email | String |  |
| enabled | Boolean |  |
| first_name | String |  |
| im_server_id | String |  |
| last_login | Instant |  |
| last_name | String |  |
| login_count | Long |  |

## Relationships

- agent_status_history(agent_id) → agent(id)
- agent_engagement_history(agent_id) → agent(id)
- engagement(department_id) → department(id)
- engagement(chat_id) → chat(id)
- engagement(agent_id) → agent(id)
- chat_history(department_id) → department(id)
- chat_history(prev_department_id) → department(id)
- chat_history(agent_id) → agent(id)
- chat_history(chat_id) → chat(id)
- chat_agent(agent_id) → agent(id)
- chat_agent(chat_id) → chat(id)
- chat_tag(chat_id) → chat(id)
- chat(visitor_id) → visitor(id)
- department_member(agent_id) → agent(id)
- department_member(department_id) → department(id)
- ban_visitor(visitor_id) → visitor(id)