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

> Explore the WACLI database structure using SQLite with chats contacts and messages tables. Understand how WhatsApp data is stored locally in this technical deep dive.

- Repository: [Peter Steinberger/wacli](https://github.com/steipete/wacli)
- Tags: internals
- Published: 2026-04-17

---

**WACLI uses an embedded SQLite database with three core tables—`chats`, `contacts`, and `messages`—defined in [`internal/store/migrations.go`](https://github.com/steipete/wacli/blob/main/internal/store/migrations.go) and mapped to Go structs in [`internal/store/types.go`](https://github.com/steipete/wacli/blob/main/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`](https://github.com/steipete/wacli/blob/main/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., `12345@s.whatsapp.net`) |
| `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`](https://github.com/steipete/wacli/blob/main/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`](https://github.com/steipete/wacli/blob/main/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`](https://github.com/steipete/wacli/blob/main/internal/store/migrations.go) lines 122-146.

## Go Struct Mappings in WACLI

The [`internal/store/types.go`](https://github.com/steipete/wacli/blob/main/internal/store/types.go) file defines Go structs that mirror the SQL schema exactly, enabling type-safe database operations.

### Chat Struct

```go
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`](https://github.com/steipete/wacli/blob/main/internal/store/types.go) lines 10-15.

### Contact Struct

```go
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`](https://github.com/steipete/wacli/blob/main/internal/store/types.go) lines 70-77.

### Message Struct

```go
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`](https://github.com/steipete/wacli/blob/main/internal/store/types.go) lines 48-60.

## Schema Migrations and Versioning

WACLI uses a sequential migration system stored in [`internal/store/migrations.go`](https://github.com/steipete/wacli/blob/main/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

```go
// UpsertChat creates or updates a chat thread
err := d.UpsertChat(
    "12345@s.whatsapp.net",  // 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`](https://github.com/steipete/wacli/blob/main/internal/store/chats_contacts_groups.go) lines 9-22.

### Storing a Contact

```go
// UpsertContact syncs address book entries
err := d.UpsertContact(
    "98765@s.whatsapp.net",  // 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`](https://github.com/steipete/wacli/blob/main/internal/store/chats_contacts_groups.go) lines 46-60.

### Saving a Message with Media Metadata

```go
msg := store.Message{
    ChatJID:     "12345@s.whatsapp.net",
    ChatName:    "Alice",
    MsgID:       "3EB0C9E8F1B2",
    SenderJID:   "12345@s.whatsapp.net",
    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

```go
// 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`](https://github.com/steipete/wacli/blob/main/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`](https://github.com/steipete/wacli/blob/main/internal/store/migrations.go)** and mapped to Go structs (`Chat`, `Contact`, `Message`) in **[`internal/store/types.go`](https://github.com/steipete/wacli/blob/main/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`](https://github.com/steipete/wacli/blob/main/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.