Developer
Database Schema
How Retrace stores data in SQLite.
Overview
Retrace stores all data in a single SQLite database on your machine. The schema is managed by Drizzle ORM with versioned migrations.
The database file is located at ~/Library/Application Support/retrace/retrace.db.
Core tables
conversations
| Column | Type | Description |
|---|---|---|
id | text | Unique conversation ID |
platform | text | Source platform (whatsapp, messenger, chatgpt, etc.) |
title | text | Conversation title or participant names |
participant_count | integer | Number of participants |
message_count | integer | Number of messages |
first_message_at | text | Timestamp of first message |
last_message_at | text | Timestamp of last message |
metadata | text | JSON blob with platform-specific data |
messages
| Column | Type | Description |
|---|---|---|
id | text | Unique message ID |
conversation_id | text | FK to conversations |
sender_name | text | Display name of the sender |
content | text | Message text |
timestamp | text | ISO timestamp |
is_from_me | integer | Whether the message is from the user |
person_id | text | FK to people (nullable) |
metadata | text | JSON blob with original payload |
people
| Column | Type | Description |
|---|---|---|
id | text | Unique person ID |
name | text | Display name |
avatar_url | text | Profile picture URL |
segments
| Column | Type | Description |
|---|---|---|
id | text | Unique segment ID |
conversation_id | text | FK to conversations |
summary | text | LLM-generated summary |
topics | text | JSON array of extracted topics |
sentiment | text | Sentiment label |
start_at | text | Segment start timestamp |
end_at | text | Segment end timestamp |
embeddings
Vector embeddings for semantic search.
| Column | Type | Description |
|---|---|---|
id | text | References segment ID |
embedding | blob | Float vector |
labels
| Column | Type | Description |
|---|---|---|
id | text | Unique label ID |
name | text | Label display name |
type | text | system, topic, or custom |
color | text | Hex color code |
label_assignments
Many-to-many join between labels and resources (conversations or people).
consumers + permission_rules
Permission profiles controlling which labels each consumer (MCP server, Claude chat, etc.) can access.
Metadata preservation
Every parser stores the complete original payload in the metadata JSON column. Fields not used today are preserved for future features. Storage is cheap; throwing away metadata is irreversible.