Database Schema¶

This document describes the database schema for ChatbotLab’s backend. The database is implemented in MySQL and stores information about bots, conversations, utterances, and keystrokes. Each table is prefixed with chatbot_ and linked by foreign keys for data integrity.

Bots Table¶

Table name: chatbot_bot

This table defines each chatbot available in the system and stores its configuration, prompt, and assigned model.

Column	Type / Key	Description
`id`	PK	Unique ID of the bot.
`name`	PK	Descriptive name of the bot (must be unique). Use a `-voice` suffix for voice-enabled versions.
`prompt`	TEXT	Prompt describing how the bot should behave.
`model_id`	VARCHAR	Identifier for the connected model.
`model_type`	VARCHAR	Type or family of the connected model (e.g., GPT-4o).
`initial_utterance`	TEXT	Optional initial message sent when conversation starts.

Conversations Table¶

Table name: chatbot_conversation

Stores high-level metadata for each chat session, including study context, participant identifiers, and Qualtrics linkage.

Column	Type / Key	Description
`id`	PK	Auto-incremented unique ID for each conversation.
`conversation_id`	PK	Response ID from Qualtrics representing the unique session.
`bot_name`	FK → chatbot_bot	Which bot the participant interacted with.
`participant_id`	VARCHAR	Participant ID (fetched from Qualtrics or Prolific).
`study_name`	VARCHAR	Optional name of the study.
`user_group`	VARCHAR	Qualtrics condition or randomized group assignment.
`started_time`	DATETIME	Timestamp when the conversation began.
`initial_utterance`	TEXT	The initial message if the bot began the conversation.
`survey_id`	VARCHAR	Qualtrics `survey_id` fetched via URL.
`survey_meta_data`	JSON	Full Qualtrics metadata payload received at start.

—

Utterances Table¶

Table name: chatbot_utterance

Each row corresponds to a single message (utterance) in a conversation.

Column	Type / Key	Description
`id`	PK	Unique ID for each utterance.
`speaker_id`	VARCHAR	Identifier of the message speaker (bot or participant).
`bot_name`	VARCHAR	Name of the bot in this conversation.
`conversation_id`	FK → chatbot_conversation \| Conversation foreign key.
`participant_id`	VARCHAR	Qualtrics or Prolific participant ID.
`text`	TEXT	Content of the utterance.
`created_time`	DATETIME	When the utterance was created.
`audio_file`	VARCHAR (URL)	Link to audio file stored in S3 (if applicable).
`is_voice`	BOOLEAN	Indicates whether the utterance includes voice (1 = True).

—

Keystrokes Table¶

Table name: chatbot_keystroke

Captures timing and engagement metrics for each chat session, providing context about user attention and typing behavior.

Column	Type / Key	Description
`id`	PK	Unique ID for each keystroke record.
`conversation_id`	FK → chatbot_conversation	Conversation foreign key.
`total_time_on_page`	FLOAT / INT	Time (in seconds) the participant spent on the chat page.
`text_time_away_from_page`	FLOAT / INT	Time (in seconds) the participant was away from the page.
`keystroke_count`	INT	Number of keys pressed during the chat.
`timestamp`	DATETIME	When the keystroke data was recorded.

—

Schema Relationships¶

Each conversation is linked to exactly one bot via bot_name.
Each utterance belongs to one conversation (conversation_id).
Each keystroke record belongs to one conversation (conversation_id).
Participants and studies can be linked across sessions by participant_id or study_name.

        erDiagram
   chatbot_bot ||--o{ chatbot_conversation : "bot_name (FK)"
   chatbot_conversation ||--o{ chatbot_utterance : "conversation_id (FK)"
   chatbot_conversation ||--o{ chatbot_keystroke : "conversation_id (FK)"

   chatbot_bot {
     int id PK
     varchar name
     text prompt
     varchar model_id
     varchar model_type
     text initial_utterance
   }

   chatbot_conversation {
     int id PK
     varchar conversation_id
     varchar bot_name FK
     varchar participant_id
     varchar study_name
     varchar user_group
     datetime started_time
     text initial_utterance
     varchar survey_id
     json survey_meta_data
   }

   chatbot_utterance {
     int id PK
     varchar speaker_id
     varchar bot_name
     varchar conversation_id FK
     varchar participant_id
     text text
     datetime created_time
     varchar audio_file
     boolean is_voice
   }

   chatbot_keystroke {
     int id PK
     varchar conversation_id FK
     float total_time_on_page
     float text_time_away_from_page
     int keystroke_count
     datetime timestamp
   }