# Freshchat schema ERD

Table and field reference for the Fivetran Freshchat connector.

## Table & Field Reference

### agent_group

| Column | Type | Keys |
| --- | --- | --- |
| group_id | String | PK |
| agent_id | String | PK, FK |
| id | Unknown | FK |

### agent_social_profile

| Column | Type | Keys |
| --- | --- | --- |
| _fivetran_id | String | PK |
| agent_id | String | FK |
| id | String |  |
| type | String |  |

### agent

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| role_id | String | FK |
| agent_capacity | String |  |
| agent_status_name | String |  |
| availability_status | String |  |
| avatar | Json |  |
| biography | String |  |
| created_time | Unknown |  |
| email | String |  |
| first_name | String |  |
| is_deactivated | Boolean |  |
| is_deleted | Boolean |  |
| last_name | String |  |
| locale | String |  |
| login_status | Boolean |  |
| routing_type | String |  |
| skill_id | String |  |
| timezone | String |  |
| title | String |  |

### role

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| description | String |  |
| is_custom_role | Boolean |  |
| name | String |  |

### csat_metrics

| Column | Type | Keys |
| --- | --- | --- |
| _fivetran_id | String | PK |
| end_date | Unknown |  |
| key | Unknown |  |
| start_date | Unknown |  |
| value | Unknown |  |

### channel_tag

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

### channel_welcome_message

| Column | Type | Keys |
| --- | --- | --- |
| index | Unknown | PK |
| channel_id | String | PK, FK |
| content | String |  |

### channel

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| bots_private_note | Boolean |  |
| enabled | Boolean |  |
| locale | String |  |
| message_type | String |  |
| name | String |  |
| public | Boolean |  |
| restrict_response | Boolean |  |
| updated_time | Unknown |  |

### conversation_message_part

| Column | Type | Keys |
| --- | --- | --- |
| _fivetran_id | String | PK |
| conversation_message_id | String | FK |
| content | String |  |
| content_type | Unknown |  |
| file_extension | Unknown |  |
| file_hash | Unknown |  |
| file_name | Unknown |  |
| file_size_in_bytes | Unknown |  |
| file_source | Unknown |  |
| url_button_lable | Unknown |  |
| url_button_target | Unknown |  |

### conversation_message

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| channel_id | String | FK |
| conversation_id | String | FK |
| user_id | String | FK |
| actor_id | String |  |
| actor_type | String |  |
| app_id | String |  |
| bots_private_note | Boolean |  |
| created_time | Unknown |  |
| interaction_id | String |  |
| message_type | String |  |
| reply_parts | Json |  |
| restrict_response | Boolean |  |

### conversation_property_choice

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| conversation_property_id | Unknown | PK, FK |
| value | Unknown |  |

### conversation_property

| Column | Type | Keys |
| --- | --- | --- |
| id | Unknown | PK |
| column_name | Unknown |  |
| custom | Unknown |  |
| default_value | Unknown |  |
| editable | Unknown |  |
| label | Unknown |  |
| name | Unknown |  |
| position | Unknown |  |
| required | Unknown |  |
| type | Unknown |  |

### team_performance_first_response_time

| Column | Type | Keys |
| --- | --- | --- |
| agent_id | Unknown | PK, FK |
| group_id | Unknown | PK, FK |
| end_time | Unknown |  |
| first_response_time | Unknown |  |
| start_time | Unknown |  |

### conversation_metrics

| Column | Type | Keys |
| --- | --- | --- |
| _fivetran_id | String | PK |
| end_date | Unknown |  |
| key | Unknown |  |
| start_date | Unknown |  |
| value | Unknown |  |

### team_performance_resolution_time

| Column | Type | Keys |
| --- | --- | --- |
| agent_id | Unknown | PK, FK |
| group_id | Unknown | PK, FK |
| end_time | Unknown |  |
| resolution_time | Unknown |  |
| start_time | Unknown |  |

### conversation_custom_property

| Column | Type | Keys |
| --- | --- | --- |
| property_name | Unknown | PK |
| conversation_id | String | PK, FK |
| property_value | Unknown |  |

### conversation

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| assigned_agent_id | String | FK |
| assigned_group_id | String | FK |
| assigned_org_agent_id | String | FK |
| channel_id | String | FK |
| user_id | String | FK |
| app_id | String |  |
| created_time | String |  |
| org_contact_id | String |  |
| skill_id | String |  |
| status | String |  |
| updated_time | String |  |
| url | String |  |

### groups

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| description | String |  |
| name | String |  |
| routing_logic | String |  |
| routing_type | String |  |

### users

| Column | Type | Keys |
| --- | --- | --- |
| id | String | PK |
| avatar | Json |  |
| created_time | Unknown |  |
| email | String |  |
| first_name | String |  |
| last_name | String |  |
| phone | String |  |
| reference_id | String |  |
| restore_id | String |  |
| updated_time | Unknown |  |
| custom_** |  |  |

### team_performance_response_time

| Column | Type | Keys |
| --- | --- | --- |
| agent_id | Unknown | PK, FK |
| group_id | Unknown | PK, FK |
| end_time | Unknown |  |
| response_time | Unknown |  |
| start_time | Unknown |  |

### csat_report

| Column | Type | Keys |
| --- | --- | --- |
| csat_submitter_user_id | Unknown | PK |
| interaction_id | Unknown | PK |
| agent_id | Unknown | FK |
| channel_id | Unknown | FK |
| conversation_id | Unknown | FK |
| group_id | Unknown | FK |
| actor_belongs_to_group | Unknown |  |
| actor_id | Unknown |  |
| actor_name | Unknown |  |
| created_at | Unknown |  |
| csat_id | Unknown |  |
| csat_reopen | Unknown |  |
| csat_response | Unknown |  |
| csat_submitter_user_name | Unknown |  |
| has_response | Unknown |  |
| interaction_raw_id | Unknown |  |
| issue_resolved | Unknown |  |
| reopened | Unknown |  |
| value | Unknown |  |

### conversation_report

| Column | Type | Keys |
| --- | --- | --- |
| interaction_id | Unknown | PK |
| label_category_id | Unknown | PK |
| label_sub_category_id | Unknown | PK |
| agent_id | Unknown | FK |
| channel_id | Unknown | FK |
| conversation_id | Unknown | FK |
| group_id | Unknown | FK |
| actor_belongs_to_group | Unknown |  |
| actor_id | Unknown |  |
| actor_name | Unknown |  |
| actor_type | Unknown |  |
| created_at | Unknown |  |
| interaction_raw_id | Unknown |  |
| label_category_name | Unknown |  |
| labelled_by_type | Unknown |  |
| reopened | Unknown |  |
| user_name | Unknown |  |

## Relationships

- agent_group(id) → groups(id)
- agent_group(agent_id) → agent(id)
- agent_social_profile(agent_id) → agent(id)
- agent(role_id) → role(id)
- channel_tag(channel_id) → channel(id)
- channel_welcome_message(channel_id) → channel(id)
- conversation_message_part(conversation_message_id) → conversation_message(id)
- conversation_message(user_id) → users(id)
- conversation_message(channel_id) → channel(id)
- conversation_message(conversation_id) → conversation(id)
- conversation_property_choice(conversation_property_id) → conversation_property(id)
- team_performance_first_response_time(agent_id) → agent(id)
- team_performance_first_response_time(group_id) → groups(id)
- team_performance_resolution_time(agent_id) → agent(id)
- team_performance_resolution_time(group_id) → groups(id)
- conversation_custom_property(conversation_id) → conversation(id)
- conversation(user_id) → users(id)
- conversation(assigned_group_id) → groups(id)
- conversation(channel_id) → channel(id)
- conversation(assigned_agent_id) → agent(id)
- conversation(assigned_org_agent_id) → agent(id)
- team_performance_response_time(agent_id) → agent(id)
- team_performance_response_time(group_id) → groups(id)
- csat_report(channel_id) → channel(id)
- csat_report(group_id) → groups(id)
- csat_report(agent_id) → agent(id)
- csat_report(conversation_id) → conversation(id)
- conversation_report(channel_id) → channel(id)
- conversation_report(group_id) → groups(id)
- conversation_report(agent_id) → agent(id)
- conversation_report(conversation_id) → conversation(id)