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

> Explore the ChatMCP database structure chatmcp.db and discover how it stores chat history in SQLite. Learn about table organization and message storage for conversations.

- Repository: [刀刀/chatmcp](https://github.com/daodao97/chatmcp)
- Tags: internals
- Published: 2026-02-28

---

**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:

```dart
// 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.