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	VARCHAR, unique	Descriptive name of the bot (must be unique). Use a -voice suffix to route to the voice-chat UI instead of text chat.
prompt	TEXT	Prompt describing how the bot should behave.
ai_model	FK → chatbot_model	The AI model (provider + model ID) this bot uses.
model_type, model_id	VARCHAR	Legacy provider/model-ID fields, superseded by ai_model; kept for migration compatibility and not read by the chat path.
initial_utterance	TEXT	Optional initial message sent when a conversation starts.
avatar_type	VARCHAR	none, default, or user (participant-provided avatar).
avatar_prompt	TEXT	Prompt used to generate the bot’s avatar image; falls back to a default prompt when empty.
chunk_messages	BOOLEAN	If true, split responses into human-like chunks; if false, send each response as one block.
humanlike_delay	BOOLEAN	If true, apply human-like typing/reading delays; if false, show messages instantly.
reading_words_per_minute, reading_jitter_min, reading_jitter_max, reading_thinking_min, reading_thinking_max	FLOAT	Parameters controlling the simulated delay before the bot “reads” an incoming message, in seconds (jitter/thinking bounds) or words/minute.
writing_words_per_minute, writing_jitter_min, writing_jitter_max, writing_thinking_min, writing_thinking_max	FLOAT	Parameters controlling the simulated delay before the bot “writes” its reply, in seconds (jitter/thinking bounds) or words/minute.
intra_message_delay_min, intra_message_delay_max	FLOAT	Delay range between chunked message parts, in seconds. Only applies when chunk_messages is true.
min_reading_delay	FLOAT	Minimum reading delay applied regardless of message length, in seconds.
follow_up_on_idle	BOOLEAN	If true, the bot sends follow-up messages when the participant goes idle.
idle_time_minutes	INT	Minutes of inactivity before a participant is considered idle.
follow_up_instruction_prompt	TEXT	Instructions used to generate follow-up messages while idle.
recurring_followup	BOOLEAN	If true, keep sending follow-ups for the duration of an idle period; if false, send at most one per idle period.
max_transcript_length	INT	Max messages included in the chat history sent to the LLM. 0 = current message only, positive = that many most recent messages, negative = unlimited.
moderation_harassment, moderation_harassment_threatening, moderation_hate, moderation_hate_threatening, moderation_self_harm, moderation_self_harm_instructions, moderation_self_harm_intent, moderation_sexual, moderation_sexual_minors, moderation_violence, moderation_violence_graphic	DECIMAL(3,2)	Per-category moderation thresholds (0.0-1.0, lower = stricter). See Content Moderation for what each category means and how thresholds are applied.
personas	M2M → chatbot_persona	Personas this bot can embody, via the chatbot_bot_personas join table.

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	VARCHAR, unique	Response ID from Qualtrics (or equivalent) representing the unique session.
bot_name	VARCHAR	Name of the bot the participant interacted with. Stored as a plain string, not a foreign key to chatbot_bot — it isn’t enforced against, or automatically updated by, later changes to that bot.
bot_config	TEXT (JSON)	Snapshot of the bot’s configuration (model, personas, delay settings, etc.) captured when the conversation started. This is how “which model was actually used” survives later edits to the bot.
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	VARCHAR	The initial message if the bot began the conversation.
survey_id	VARCHAR	Qualtrics survey_id fetched via URL.
survey_meta_data	TEXT (JSON)	Full Qualtrics metadata payload received at start.
selected_persona	FK → chatbot_persona	The persona randomly selected for this conversation, if the bot has any personas configured; NULL otherwise.

—

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	Who sent the message: "user" or "assistant" on the main text- and follow-up-chat paths. The voice-chat path instead writes "participant" for the human side.
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).
instruction_prompt	TEXT	System prompt (bot prompt + persona) sent to the LLM.
chat_history_used	TEXT (JSON)	Chat history that was actually passed to the LLM.
moderation_category	VARCHAR	Moderation category that blocked this exchange (e.g. harassment); NULL when the message was not blocked.
moderation_scores	JSON	Full category → score map returned by the moderation API for a blocked message, including categories that passed. Recorded on the participant’s row only; NULL otherwise.

Note

When a message is blocked, no LLM call is made. Two rows are written: the participant’s message and a fixed warning reply. Both carry moderation_category, so WHERE moderation_category IS NOT NULL returns the complete exchange; to exclude canned warnings from a transcript, drop the assistant rows where that column is set. moderation_scores is set on the participant’s row only, since that is the message the scores describe. Blocks that predate these columns are labeled unknown — their real category and scores were never recorded.

—

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	VARCHAR	Matches chatbot_conversation.conversation_id by convention, but is not a foreign key — keystroke logging can happen before a conversation row is registered, so there’s no constraint tying the two together.
total_time_on_page	FLOAT	Time (in seconds) the participant spent on the chat page.
total_time_away_from_page	FLOAT	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 (set by the client, not auto-generated on write).

—

Schema Relationships

• Each conversation references one bot via bot_name — a plain string match, not an enforced foreign key.

• Each utterance belongs to one conversation, via the real foreign key conversation_id.

• Each keystroke record is associated with a conversation by matching conversation_id values, but this is not a foreign key relationship — see the note on the Keystrokes table above.

• Participants and studies can be linked across sessions by participant_id or study_name.

Note

moderation_category and moderation_scores on chatbot_utterance are documented here as they will exist once PR #192 merges; they are not yet present on main.

The diagram below abbreviates chatbot_bot to a representative subset of columns for readability — see the full Bots Table above for all fields.

        erDiagram
   chatbot_bot ||..o{ chatbot_conversation : "bot_name (string match, not FK)"
   chatbot_conversation ||--o{ chatbot_utterance : "conversation_id (FK)"
   chatbot_conversation ||..o{ chatbot_keystroke : "conversation_id (string match, not FK)"

   chatbot_bot {
     int id PK
     varchar name UK
     text prompt
     int ai_model FK
     varchar model_id
     varchar model_type
     text initial_utterance
     varchar avatar_type
     text avatar_prompt
     boolean chunk_messages
     boolean humanlike_delay
     float reading_words_per_minute
     float writing_words_per_minute
     int max_transcript_length
     decimal moderation_harassment
   }

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

   chatbot_utterance {
     int id PK
     varchar speaker_id
     varchar bot_name
     int conversation_id FK
     varchar participant_id
     text text
     datetime created_time
     varchar audio_file
     boolean is_voice
     text instruction_prompt
     json chat_history_used
     varchar moderation_category
     json moderation_scores
   }

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