# How wacli's Message Upsert Logic Prevents Duplicate Entries During History Sync Replays

> Discover how wacli's message upsert logic prevents duplicate entries during history sync replays using SQLite's ON CONFLICT clause and a unique composite constraint. Learn to avoid data duplication.

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

---

**wacli prevents duplicate message entries during history sync replays by using SQLite's `INSERT … ON CONFLICT` clause with a unique composite constraint on `(chat_jid, msg_id)`, ensuring that existing messages are updated rather than duplicated when the same history payload is processed multiple times.**

When syncing historical WhatsApp data, the `steipete/wacli` tool must handle redundant `HistorySync` events without creating duplicate database entries. The wacli message upsert logic leverages database-level constraints to guarantee idempotent storage, ensuring that replaying the same sync operation multiple times produces consistent results.

## Understanding the History Sync Challenge

During initial bootstrap or manual synchronization, wacli receives `HistorySync` events containing batches of historical messages. These events may be replayed due to network retries, retries, or partial sync operations. Without proper duplicate detection, the same message would be inserted multiple times, corrupting the chat history and inflating storage usage.

## Database-Level Protection via Unique Constraints

The foundation of wacli's duplicate prevention lies in its SQLite schema design and conflict resolution strategy.

### Composite Key Design in migrations.go

The database schema defined in [`internal/store/migrations.go`](https://github.com/steipete/wacli/blob/main/internal/store/migrations.go) establishes a **unique constraint on the combination of `chat_jid` and `msg_id`**. This composite key ensures that a single message can be identified uniquely within its specific chat context, preventing collisions between messages with identical IDs from different conversations.

### SQLite ON CONFLICT Resolution Strategy

When `store.UpsertMessage` executes in [`internal/store/messages.go`](https://github.com/steipete/wacli/blob/main/internal/store/messages.go), it uses the `INSERT … ON CONFLICT` SQL construct:

```sql
INSERT INTO messages( … )
VALUES ( … )
ON CONFLICT(chat_jid, msg_id) DO UPDATE SET
    chat_name   = COALESCE(NULLIF(excluded.chat_name,''), messages.chat_name),
    sender_jid  = excluded.sender_jid,
    …
    file_length = CASE WHEN excluded.file_length>0 THEN excluded.file_length ELSE messages.file_length END

```

This query operates in two phases:
1. **Attempted Insert**: SQLite tries to insert the new message row.
2. **Conflict Resolution**: If the `(chat_jid, msg_id)` combination already exists, the `DO UPDATE` clause activates, merging the new data with existing fields using `COALESCE` logic to preserve non-empty values.

## The Message Upsert Implementation

The `UpsertMessage` function in [`internal/store/messages.go`](https://github.com/steipete/wacli/blob/main/internal/store/messages.go) (lines 30-38) encapsulates this logic in Go:

```go
func (d *Store) UpsertMessage(params UpsertMessageParams) error {
    _, err := d.sql.Exec(`
        INSERT INTO messages(chat_jid, msg_id, sender_jid, timestamp, from_me, text, chat_name, file_length, ...)
        VALUES (?, ?, ?, ?, ?, ?, ?, ?, ...)
        ON CONFLICT(chat_jid, msg_id) DO UPDATE SET
            chat_name   = COALESCE(NULLIF(excluded.chat_name,''), messages.chat_name),
            sender_jid  = excluded.sender_jid,
            timestamp   = excluded.timestamp,
            from_me     = excluded.from_me,
            text        = excluded.text,
            file_length = CASE WHEN excluded.file_length>0 THEN excluded.file_length ELSE messages.file_length END
    `, params.ChatJID, params.MsgID, params.SenderJID, params.Timestamp, params.FromMe, params.Text, params.ChatName, params.FileLength)
    return err
}

```

This implementation guarantees that calling `UpsertMessage` with identical parameters multiple times results in exactly one database row, with subsequent calls updating rather than duplicating the record.

## History Sync Processing Pipeline

The orchestration layer that feeds messages into this upsert logic resides in [`internal/app/sync.go`](https://github.com/steipete/wacli/blob/main/internal/app/sync.go).

### Event Handling in app/sync.go

When a `HistorySync` event arrives, the application iterates through all conversations and their messages (lines 15-39):

```go
func (a *App) handleHistorySync(v *events.HistorySync) {
    for _, conv := range v.Data.Conversations {
        chatID := strings.TrimSpace(conv.GetID())
        for _, m := range conv.Messages {
            // Parse the raw WhatsApp message into a structured format
            pm := wa.ParseHistoryMessage(chatID, m.Message)
            
            // The upsert ensures idempotency - safe to call on replays
            if err := a.storeParsedMessage(ctx, pm); err == nil {
                messagesStored.Add(1)   // counted even if it was an update
            }
        }
    }
}

```

The `storeParsedMessage` method delegates to `store.UpsertMessage`, creating the complete pipeline from raw WhatsApp events to deduplicated database storage.

## Idempotency Guarantees and Conflict Resolution

The combination of schema constraints and SQL conflict resolution delivers several key guarantees:

* **True Idempotency**: Replaying the same `HistorySync` payload *n* times produces identical database state as processing it once.
* **Data Preservation**: The `COALESCE` and `CASE` expressions in the `DO UPDATE` clause ensure that existing non-empty values (like `chat_name`) are not overwritten by empty strings, while newer values (like `file_length` if greater than zero) take precedence.
* **Atomic Operations**: SQLite's `ON CONFLICT` handling occurs within the same transaction boundary, preventing race conditions between inserts and updates.

## Summary

* wacli prevents duplicate message entries using **SQLite's `INSERT … ON CONFLICT`** clause with a **unique constraint on `(chat_jid, msg_id)`**.
* The `UpsertMessage` function in [`internal/store/messages.go`](https://github.com/steipete/wacli/blob/main/internal/store/messages.go) executes atomic upsert operations that update existing rows rather than creating duplicates.
* During history sync replays, [`internal/app/sync.go`](https://github.com/steipete/wacli/blob/main/internal/app/sync.go) processes each message through this upsert pipeline, ensuring **idempotent storage** even when the same payload is received multiple times.
* The conflict resolution strategy preserves existing data using `COALESCE` logic while allowing newer values to override older ones where appropriate.

## Frequently Asked Questions

### What happens when wacli encounters a message with the same ID during history sync?

When wacli encounters a message with an existing `(chat_jid, msg_id)` combination, SQLite's `ON CONFLICT` clause triggers an update rather than an insertion. The existing row is modified to merge new data with preserved values, ensuring no duplicate rows are created while keeping the database current.

### Does wacli's upsert logic preserve existing message data when conflicts occur?

Yes, the upsert logic specifically preserves existing data through selective field updates. The SQL query uses `COALESCE(NULLIF(excluded.chat_name,''), messages.chat_name)` to retain existing chat names when incoming data is empty, and conditional logic like `CASE WHEN excluded.file_length>0` ensures file metadata is only updated when valid new data exists.

### Which database constraint prevents duplicate entries in wacli?

The database schema defines a **unique constraint on the composite key `(chat_jid, msg_id)`** in the `messages` table. This constraint, defined in [`internal/store/migrations.go`](https://github.com/steipete/wacli/blob/main/internal/store/migrations.go), ensures that the combination of chat identifier and message identifier remains unique across the entire database, enabling the conflict detection mechanism that powers the upsert logic.

### Is the history sync process in wacli idempotent?

Yes, the history sync process is fully idempotent. The `handleHistorySync` function in [`internal/app/sync.go`](https://github.com/steipete/wacli/blob/main/internal/app/sync.go) processes each message through `storeParsedMessage`, which calls the atomic `UpsertMessage` operation. Because the underlying SQL uses `INSERT … ON CONFLICT`, processing the same `HistorySync` payload multiple times produces identical database state as processing it once, with no duplicate rows created.