ChatMCP Database Structure: How `chatmcp.db` Stores Chat History in SQLite

ChatMCP persists all conversation data in a single SQLite file named chatmcp.db using two related tables—chat for conversation metadata and chat_message for individual messages with threaded reply support.

ChatMCP, an open-source chat client by daodao97, maintains local chat history using a lightweight SQLite database architecture. The application stores every conversation and message in chatmcp.db, with the schema defined in lib/dao/init_db.dart and database initialization handled by the DatabaseHelper class. This design enables offline access to chat history while supporting complex conversation threading through parent-child message relationships.

Database Location and Initialization

Platform-Specific Storage Paths

The physical location of chatmcp.db is resolved by StorageManager.getDatabasePath() in lib/utils/storage_manager.dart (lines 55-58). This method constructs a platform-specific path within the application's data directory—typically ~/.local/share/ChatMcp/chatmcp.db on Linux systems or equivalent folders on macOS and Windows.

Database Initialization

When the application launches, DatabaseHelper in lib/dao/init_db.dart opens or creates the database file. The helper executes the initial schema migration (CommandScriptV1) to establish the table structure before any chat operations occur.

SQLite Schema Design

The ChatMCP database structure centers on two normalized tables that separate conversation metadata from message content.

The chat Table

The chat table stores one row per conversation with the following schema defined at lines 44-63 of lib/dao/init_db.dart:

• id: Integer primary key with auto-increment
• title: Text field for the user-editable conversation name
• model: Text field recording the LLM identifier (e.g., gpt-4o) used for that chat
• createdAt and updatedAt: Datetime fields tracking conversation lifecycle

The chat_message Table

Individual messages reside in chat_message (lines 53-63), which includes:

• id: Auto-incrementing primary key
• chatId: Foreign key referencing chat(id) to establish conversation membership
• messageId: Text field storing the global identifier assigned by the LLM service
• parentMessageId: Text field enabling threaded replies by referencing the preceding message's messageId
• body: Text content of the message
• createdAt and updatedAt: Datetime tracking

This schema supports branching conversations where any message can serve as the parent for subsequent replies.

How Chat History Is Stored

ChatMCP follows a transactional workflow when persisting conversation data.

Creating Conversations

When a user initiates a new chat, the application inserts a row into the chat table containing the title, model identifier, and timestamps. The returned id value becomes the permanent chat identifier used for all related messages.

Inserting User and Assistant Messages

Each message—whether from the user or the LLM—is inserted into chat_message with the corresponding chatId. The messageId field receives the provider-specific identifier, while parentMessageId remains null for conversation starters or references the previous message ID for replies.

Retrieving Conversation History

To reconstruct a chat in the UI, the application queries the chat table by identifier, then fetches all chat_message rows where chatId matches, ordering results by createdAt. The parent-child relationships enable the interface to render threaded discussions and branching conversation paths.

Database Migrations

The schema evolves through versioned migrations defined in lib/dao/init_db.dart. Migration v1 creates the initial tables, while subsequent migrations (v2, v3) serve as placeholders for schema alterations. For example, migration v3 (lines 77-89) demonstrates table recreation to fix the model column while preserving existing conversation data through temporary table creation and data migration.

Practical Code Examples

The following Dart snippets demonstrate direct database operations using the ChatMCP SQLite layer:

// Obtain database instance (initialized via initDb())
final db = await DatabaseHelper.instance.database;

// Insert a new conversation
int chatId = await db.insert('chat', {
  'title': 'Project Planning Discussion',
  'model': 'gpt-4o',
  'createdAt': DateTime.now().toIso8601String(),
  'updatedAt': DateTime.now().toIso8601String(),
});

// Store initial user message
await db.insert('chat_message', {
  'chatId': chatId,
  'messageId': 'msg-user-001',
  'parentMessageId': null,
  'body': 'Help me plan a new mobile app feature',
  'createdAt': DateTime.now().toIso8601String(),
  'updatedAt': DateTime.now().toIso8601String(),
});

// Store assistant reply with threading
await db.insert('chat_message', {
  'chatId': chatId,
  'messageId': 'msg-assistant-002',
  'parentMessageId': 'msg-user-001',
  'body': 'I can help with that. What platform are you targeting?',
  'createdAt': DateTime.now().toIso8601String(),
  'updatedAt': DateTime.now().toIso8601String(),
});

// Retrieve full conversation history
List<Map<String, dynamic>> history = await db.query(
  'chat_message',
  where: 'chatId = ?',
  whereArgs: [chatId],
  orderBy: 'createdAt ASC',
);

Summary

• ChatMCP stores all data in a single SQLite file named chatmcp.db located in platform-specific application data directories via StorageManager.getDatabasePath().
• The schema consists of two tables: chat for conversation metadata and chat_message for individual messages with foreign key relationships.
• Threading support comes from the parentMessageId column in chat_message, which references the preceding message's identifier to create reply chains.
• Schema evolution is handled through versioned migrations in lib/dao/init_db.dart, allowing structural changes without data loss.
• All database operations flow through DatabaseHelper, which manages connections, migrations, and the initial CommandScriptV1 setup.

Frequently Asked Questions

Where is the chatmcp.db file located on my system?

According to the source code in lib/utils/storage_manager.dart (lines 55-58), the database file resides in the platform-specific application data directory. On Linux, this is typically ~/.local/share/ChatMcp/chatmcp.db, while macOS and Windows use their respective application support folders. The StorageManager class resolves this path dynamically based on the operating system.

Does ChatMCP support conversation branching or threaded replies?

Yes. The chat_message table includes a parentMessageId column that references the messageId of the preceding message. This design allows any message to serve as the parent for subsequent replies, enabling the application to render branched conversation threads rather than simple linear chat histories.

How does ChatMCP handle database schema updates without losing existing chats?

The application implements versioned migrations in lib/dao/init_db.dart. When the schema requires changes—such as migration v3's fix for the model column—the system creates temporary tables, migrates existing data, drops the old tables, and recreates them with the updated structure. This preserves all conversation history and message content across application updates.

Can I query the chatmcp.db file directly with external tools?

Yes, since chatmcp.db is a standard SQLite 3 database file, you can open it using any SQLite client such as DB Browser for SQLite, the sqlite3 command-line tool, or programmatic libraries. The schema uses standard SQL types and foreign key constraints, making the data fully accessible for backups, exports, or external analysis.

{
  "mcpServers": {
    "instagit": {
      "command": "npx",
      "args": ["-y", "instagit@latest"]
    }
  }
}