Database Structure for Storing Messages, Chats, and Contacts in WACLI

WACLI uses an embedded SQLite database with three core tables—chats, contacts, and messages—defined in internal/store/migrations.go and mapped to Go structs in internal/store/types.go to persist WhatsApp data locally.

The open-source command-line tool WACLI (steipete/wacli) provides a local-first interface for WhatsApp by persisting all conversations, address book entries, and message history to disk. Understanding the database structure for storing messages, chats, and contacts in WACLI is essential for developers building queries, backups, or integrations against the wacli.db SQLite file.

Core Database Tables in WACLI

WACLI creates the following tables automatically on first start. The schema is versioned and migrates incrementally via internal/store/migrations.go.

The chats Table

Each row represents a single conversation thread, whether direct messages, groups, or broadcasts.

Column	Type	Description
jid	TEXT PK	WhatsApp JID (e.g., [email protected])
kind	TEXT	Chat type: dm, group, broadcast, status
name	TEXT	Display name of the chat
last_message_ts	INTEGER	Unix timestamp of the most recent message

Source: internal/store/migrations.go lines 74-79.

The contacts Table

Stores the local address book synced from WhatsApp.

Column	Type	Description
jid	TEXT PK	WhatsApp JID
phone	TEXT	E.164 phone number
push_name	TEXT	Push name provided by the contact
full_name	TEXT	Full name if available
first_name	TEXT	First name
business_name	TEXT	Business profile name
updated_at	INTEGER	Last update timestamp

Source: internal/store/migrations.go lines 81-89.

The messages Table

The central message log with full media metadata.

Column	Type	Description
rowid	INTEGER PK	Auto-increment primary key
chat_jid	TEXT FK	References chats.jid
chat_name	TEXT	Denormalized chat name
msg_id	TEXT	WhatsApp message ID (unique per chat)
sender_jid	TEXT	Message author
sender_name	TEXT	Denormalized sender name
ts	INTEGER	Unix timestamp
from_me	INTEGER	Boolean flag (0/1)
text	TEXT	Raw message text
display_text	TEXT	Human-readable text (added v2)
media_type	TEXT	image, video, audio, etc.
media_caption	TEXT	User caption for media
filename	TEXT	Original filename
mime_type	TEXT	MIME type
direct_path	TEXT	WhatsApp CDN path
media_key	BLOB	Encryption key
file_sha256	BLOB	File hash
file_enc_sha256	BLOB	Encrypted file hash
file_length	INTEGER	File size in bytes
local_path	TEXT	Local filesystem path
downloaded_at	INTEGER	Download timestamp

Source: internal/store/migrations.go lines 122-146.

Go Struct Mappings in WACLI

The internal/store/types.go file defines Go structs that mirror the SQL schema exactly, enabling type-safe database operations.

Chat Struct

type Chat struct {
    JID           string    `db:"jid"`
    Kind          string    `db:"kind"`
    Name          string    `db:"name"`
    LastMessageTS time.Time `db:"last_message_ts"`
}

Defined in: internal/store/types.go lines 10-15.

Contact Struct

type Contact struct {
    JID          string    `db:"jid"`
    Phone        string    `db:"phone"`
    Name         string    `db:"name"`        // push_name
    Alias        string    `db:"alias"`       // full_name
    Tags         []string  `db:"tags"`        // parsed from contact_tags table
    UpdatedAt    time.Time `db:"updated_at"`
}

Defined in: internal/store/types.go lines 70-77.

Message Struct

type Message struct {
    ChatJID     string    `db:"chat_jid"`
    ChatName    string    `db:"chat_name"`
    MsgID       string    `db:"msg_id"`
    SenderJID   string    `db:"sender_jid"`
    SenderName  string    `db:"sender_name"`
    Timestamp   time.Time `db:"ts"`
    FromMe      bool      `db:"from_me"`
    Text        string    `db:"text"`
    DisplayText string    `db:"display_text"`
    MediaType   string    `db:"media_type"`
    Snippet     string    // computed for FTS
}

Defined in: internal/store/types.go lines 48-60.

Schema Migrations and Versioning

WACLI uses a sequential migration system stored in internal/store/migrations.go. When the database opens, the app checks the schema_migrations table and applies any pending upgrades.

Current migration sequence:

1. Version 1: Creates core tables (chats, contacts, messages, groups, group_participants)
2. Version 2: Adds display_text column to messages for human-readable message previews
3. Version 3: Creates messages_fts virtual table for Full-Text Search using SQLite FTS5

This ensures that existing wacli.db files remain compatible when users upgrade the CLI binary.

Practical Code Examples

The following snippets demonstrate how WACLI interacts with the database structure for storing messages, chats, and contacts.

Inserting or Updating a Chat

// UpsertChat creates or updates a chat thread
err := d.UpsertChat(
    "[email protected]",  // jid
    "dm",                    // kind: dm, group, broadcast
    "Alice",                 // name
    time.Now(),              // last_message_ts
)
if err != nil {
    log.Fatalf("upsert chat: %v", err)
}

Implementation: internal/store/chats_contacts_groups.go lines 9-22.

Storing a Contact

// UpsertContact syncs address book entries
err := d.UpsertContact(
    "[email protected]",  // jid
    "+15551234567",          // phone
    "Bob",                   // push_name
    "Bob Smith",             // full_name
    "Bob",                   // first_name
    "",                      // business_name
)
if err != nil {
    log.Fatalf("upsert contact: %v", err)
}

Implementation: internal/store/chats_contacts_groups.go lines 46-60.

Saving a Message with Media Metadata

msg := store.Message{
    ChatJID:     "[email protected]",
    ChatName:    "Alice",
    MsgID:       "3EB0C9E8F1B2",
    SenderJID:   "[email protected]",
    Timestamp:   time.Now(),
    FromMe:      true,
    Text:        "Check this image",
    DisplayText: "Check this image",
    MediaType:   "image",
}

// Insert with upsert logic (update on conflict)
_, err := d.sql.Exec(`
    INSERT INTO messages(chat_jid, chat_name, msg_id, sender_jid, ts,
                       from_me, text, display_text, media_type)
    VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?)
    ON CONFLICT(chat_jid, msg_id) DO UPDATE SET
        text=excluded.text,
        display_text=excluded.display_text,
        ts=excluded.ts`,
    msg.ChatJID, msg.ChatName, msg.MsgID, msg.SenderJID,
    msg.Timestamp.Unix(), boolToInt(msg.FromMe), msg.Text, 
    msg.DisplayText, msg.MediaType)

Querying Recent Chats

// ListChats retrieves conversations ordered by last activity
chats, err := d.ListChats("", 20) // empty query = all, limit 20
if err != nil {
    log.Fatalf("list chats: %v", err)
}

for _, c := range chats {
    fmt.Printf("%s (%s) – last message: %s\n", 
        c.Name, c.JID, c.LastMessageTS)
}

Implementation: internal/store/chats_contacts_groups.go lines 24-55.

Summary

• WACLI persists all WhatsApp data to a local SQLite database (wacli.db) using three core tables: chats, contacts, and messages.
• The schema is defined in internal/store/migrations.go and mapped to Go structs (Chat, Contact, Message) in internal/store/types.go.
• Foreign key relationships link messages to chats via chat_jid, while denormalized columns (chat_name, sender_name) optimize read performance.
• Schema versioning ensures backward compatibility through sequential migrations (currently at version 3, adding FTS support).

Frequently Asked Questions

What database engine does WACLI use?

WACLI uses SQLite as its embedded database engine. The database file is created automatically on first run and stored locally as wacli.db. This choice eliminates external dependencies and allows the CLI to operate entirely offline after initial synchronization.

How does WACLI handle schema updates without losing data?

WACLI implements a sequential migration system in internal/store/migrations.go. Each schema version is applied exactly once and recorded in the schema_migrations table. Current versions include: v1 (core tables), v2 (added display_text column), and v3 (added FTS5 virtual table messages_fts). This ensures existing databases migrate safely when users upgrade the binary.

What is the relationship between the messages and chats tables?

The messages table contains a foreign key chat_jid that references the jid primary key in the chats table. This establishes a one-to-many relationship where one chat can contain thousands of messages. Additionally, the messages table includes denormalized fields like chat_name and sender_name to reduce JOIN operations when rendering message lists.

Where are media files stored in the WACLI database?

Media metadata is stored in the messages table within columns like media_type, filename, mime_type, direct_path, media_key, file_sha256, and file_length. The actual file content is stored on the local filesystem; the local_path column in the database records the absolute path to the downloaded media file, while downloaded_at tracks when the file was saved locally.

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