Data Model

Parent: Chat Service

---

Entity Relationship Diagram

erDiagram
    assignment_customer ||--o{ chat_sessions : "participates in"
    dentist_dentist ||--o{ chat_sessions : "participates in"
    chat_sessions ||--o{ chat_messages : "contains"
    assignment_customer ||--o{ customer_notification_mapping : "has"

    assignment_customer {
        int id PK
        string email
        string name
        string cognito_sub
    }

    dentist_dentist {
        int id PK
        string email
        string name
    }

    chat_sessions {
        int id PK
        int customer_id FK
        int dentist_id FK
        datetime created_at
        datetime updated_at
        enum status
    }

    chat_messages {
        int id PK
        int session_id FK
        enum sender_type
        int sender_id
        text content
        json image_urls
        datetime created_at
        bool is_read
        datetime read_at
    }

    customer_notification_mapping {
        int id PK
        int customer_id FK
        string onesignal_player_id
        datetime created_at
        datetime updated_at
    }

Database Overview

The Chat Service uses a dual-database architecture:

Database	Purpose	Access
PostgreSQL	Chat data (sessions, messages, notifications)	Read/Write
MySQL (Jarvis)	User data (customers, dentists)	Read-only

---

PostgreSQL Tables (Chat Service)

chat_sessions

Represents a chat conversation between a customer and a dentist.

Field	Type	Notes
id	int	Primary key
customer_id	int	FK to assignment_customer (MySQL)
dentist_id	int	FK to dentist_dentist (MySQL)
created_at	datetime	When the session was created
updated_at	datetime	Last activity timestamp
status	enum	active, archived

chat_messages

Individual messages within a chat session.

Field	Type	Notes
id	int	Primary key
session_id	int	FK to chat_sessions
sender_type	enum	customer, dentist
sender_id	int	ID of the sender (customer or dentist)
content	text	Message text content
image_urls	json	Array of S3 keys for attached images
created_at	datetime	When the message was sent
is_read	bool	Whether recipient has read the message
read_at	datetime	When the message was read (null if unread)

customer_notification_mapping

Maps customers to their push notification player IDs.

Field	Type	Notes
id	int	Primary key
customer_id	int	FK to assignment_customer (MySQL)
onesignal_player_id	string	OneSignal player ID for push notifications
created_at	datetime	Record creation time
updated_at	datetime	Last update time

---

MySQL Tables (Jarvis - Read-only)

assignment_customer

Customer information from the Jarvis system.

Field	Type	Notes
id	int	Primary key
email	string	Customer email address
name	string	Customer name
cognito_sub	string	AWS Cognito subject identifier

dentist_dentist

Dentist information from the Jarvis system.

Field	Type	Notes
id	int	Primary key
email	string	Dentist email address
name	string	Dentist name

---

Indexes

Key indexes for query performance:

• chat_sessions.customer_id - Find all chats for a customer
• chat_sessions.dentist_id - Find all chats for a dentist
• chat_sessions.updated_at - Order chats by recent activity
• chat_messages.session_id - Fetch messages for a session
• chat_messages.created_at - Paginate messages chronologically
• customer_notification_mapping.customer_id (unique) - Lookup notification mapping