---
type: concept
title: Message history store
description: The local SQLite database in src/history.rs — its schema and no-migration lifecycle, the deduplicating save, the read queries, contacts, the per-chat and global autoreply flags, image descriptions, chat deletion, and update_message_text as the only in-place rewrite of a stored message body (voice-note transcriptions stored inside messages.text).
tags: [sqlite, rusqlite, history, persistence, schema, transcription]
sources:
  - id: openwiki-source-9fec9d1af408501c9c455161
    resource: repo://src/app.rs
  - id: openwiki-source-043e6252badc57a1ee6bfb48
    resource: repo://src/autoreply.rs
  - id: openwiki-source-ce0c4ea8cd3b15431fa691ce
    resource: repo://src/history.rs
  - id: openwiki-source-7ea5eac45e5030178a6ea05b
    resource: repo://src/whatsapp.rs
verified:
  - by: openwiki/0.7.2
    at: 2026-10-24T16:42:08.550Z
generated: { by: "openwiki/0.7.2", at: "2026-10-24T16:42:08.550Z" }
---

# Message history store

`src/history.rs` wraps a single `rusqlite::Connection` in the `History` struct
(`repo://src/history.rs#L57-L60`). It is the app's only persistent state of its
own — the WhatsApp session lives in a separate database owned by
`whatsapp-rust` (see
[Configuration and data](../operations/configuration-and-data.md)). `main`
opens it before building the app and passes it in `AppDeps`
(`repo://src/main.rs#L51-L68`, `repo://src/app.rs#L193-L199`);
[`App`](../architecture/app-state.md) keeps it as a private field
(`repo://src/app.rs#L233`) and calls it synchronously from the event loop, so
all writes happen on the loop thread. Failures come back as `Err` and end up
either as a startup abort or in the `Errore storico: …` status line.

## Opening and schema lifecycle

- `History::open(path)` (`repo://src/history.rs#L67-L76`) creates missing parent
  directories, opens the file and delegates to `from_connection`.
- `from_connection` (`repo://src/history.rs#L94-L100`) executes the whole schema
  script, so every construction of a store also ensures the schema exists.
- `History::open_in_memory()` (`repo://src/history.rs#L82-L88`) exists only under
  `#[cfg(test)]`.
- The script is built by `schema_sql` from the table/index name constants
  (`repo://src/history.rs#L415-L450`). It uses only `CREATE TABLE/INDEX IF NOT
  EXISTS` and there are **no migrations**: an older `history.db` gains any
  *new* table on the next start (for example `settings` and
  `image_descriptions`), but a new column in an existing table is never added.
  Resetting the local store therefore means deleting `history.db`
  (`history_db_path`, see
  [Configuration and data](../operations/configuration-and-data.md)).

## Schema

| Table | Columns | Notes |
|---|---|---|
| `messages` | `message_id` (PK), `chat_jid`, `sender_jid`, `sender_name`, `text`, `timestamp`, `from_me`, `is_ai` | Only `sender_name` is nullable; index `idx_messages_chat_timestamp` on `(chat_jid, timestamp)` |
| `chats` | `jid` (PK), `name`, `last_timestamp` (default 0), `autoreply` (default 0) | One row per chat; `name` is nullable and may stay empty |
| `contacts` | `jid` (PK), `name` (NOT NULL) | Names from the address book / push names |
| `settings` | `key` (PK), `value` (INTEGER NOT NULL) | App-wide flags; currently only `global_autoreply` |
| `image_descriptions` | `message_id` (PK), `chat_jid`, `description` | AI description of an image message, one row per image |

Booleans are stored as `INTEGER` and read back as `bool` (`from_me`, `is_ai`,
`chats.autoreply`, `settings.value`); timestamps are unix seconds. The Rust
counterparts are `StoredMessage`, `ChatSummary` and `Contact`
(`repo://src/history.rs#L29-L55`), and the default constants
(`DEFAULT_LAST_TIMESTAMP`, `DEFAULT_AUTOREPLY`, `SETTING_GLOBAL_AUTOREPLY`,
`DEFAULT_GLOBAL_AUTOREPLY`) sit at `repo://src/history.rs#L16-L23`.

```mermaid
erDiagram
    messages {
        TEXT message_id PK
        TEXT chat_jid
        TEXT sender_jid
        TEXT sender_name
        TEXT text
        INTEGER timestamp
        INTEGER from_me
        INTEGER is_ai
    }
    chats {
        TEXT jid PK
        TEXT name
        INTEGER last_timestamp
        INTEGER autoreply
    }
    contacts {
        TEXT jid PK
        TEXT name
    }
    settings {
        TEXT key PK
        INTEGER value
    }
    image_descriptions {
        TEXT message_id PK
        TEXT chat_jid
        TEXT description
    }
    chats ||--o{ messages : "chat_jid"
    chats ||--o{ image_descriptions : "chat_jid"
    messages ||--o| image_descriptions : "message_id"
```

The five tables and the join keys the code uses; `contacts` is matched to
`chats` by the same `jid` value when a display name is resolved.

No table declares a `FOREIGN KEY` and no `ON DELETE` cascade exists
(`repo://src/history.rs#L415-L450`): referential integrity between `messages`,
`image_descriptions` and `chats` is maintained entirely by the methods below,
and `delete_chat` deletes the child rows explicitly.

## Saving messages and deduplication

`save_message` (`repo://src/history.rs#L107-L118`) runs in one
`unchecked_transaction` (the method takes `&self`, so the transaction is taken
from the shared connection):

1. `INSERT OR IGNORE` into `messages` (`insert_message`,
   `repo://src/history.rs#L456-L477`) — the WhatsApp message id is the primary
   key, so the deduplication key space is global across chats.
2. Only when a row was inserted, upsert the `chats` row
   (`upsert_chat_for_message`, `repo://src/history.rs#L484-L497`):
   `last_timestamp = MAX(last_timestamp, excluded.last_timestamp)` keeps the
   maximum seen, and `name = COALESCE(NULLIF(name, ''), excluded.name)` sets the
   name only while the chat has no non-empty one.
3. Return `true` for a new message, `false` for a duplicate.

A duplicate save changes **nothing**: not the text, not the flags, and not the
chat row (the upsert is skipped entirely). The unit test
`duplicate_message_id_is_ignored` asserts that a re-delivered message with a
different body leaves the stored one intact
(`repo://src/history.rs#L587-L599`).

The chat name candidate is the trimmed sender push name of an **incoming**
message; own messages never name a chat (`chat_name_candidate`,
`repo://src/history.rs#L503-L513`).

The boolean return value drives the app: `on_message` returns immediately on
`false`, so history-sync replays and redeliveries neither refresh the UI nor
trigger the [AI autoreply](../workflows/ai-autoreply.md)
(`repo://src/app.rs#L485-L499`).

## Reading

- `recent_messages(chat_jid, limit)` (`repo://src/history.rs#L124-L142`): the
  latest `limit` messages, queried newest first (`timestamp DESC, rowid DESC`)
  and reversed to ascending order. Used for the open chat view
  (`MESSAGES_SHOWN`), the last-message lookup of the startup catch-up, the
  "owner answered meanwhile" check and the AI context cap.
- `messages_since(chat_jid, since, limit)` (`repo://src/history.rs#L150-L168`):
  the messages with `timestamp >= since` (inclusive), capped to the `limit` most
  recent ones (`timestamp DESC, rowid DESC LIMIT ?3`) and reversed to ascending
  order. The [AI autoreply](../workflows/ai-autoreply.md) uses it to load the
  context from local midnight of yesterday, at most `CONTEXT_MESSAGES` messages
  (`repo://src/app.rs#L991-L998`).
- `list_chats()` (`repo://src/history.rs#L175-L191`): every `chats` row ordered
  by `last_timestamp DESC, jid`; the name is the contact name, else the chat
  name, else the user part of the JID, resolved in `chat_summary_from_row`
  (`repo://src/history.rs#L544-L555`).
- `list_contacts()` (`repo://src/history.rs#L217-L236`): ordered by
  `name COLLATE NOCASE, jid`.
- `display_name(jid)` (`repo://src/history.rs#L242-L253`): the same contact →
  chat → JID-user-part fallback (`jid_user_part`,
  `repo://src/history.rs#L519-L521`), used for the open chat title, status
  lines and the AI chat description.

The list-style read queries are prepared with `prepare_cached` and the point
lookups (`display_name`, `is_autoreply`, `is_global_autoreply`,
`message_exists`) use `query_row`, all on the single shared connection.

## Contacts

`upsert_contact` (`repo://src/history.rs#L197-L211`) inserts or renames a
contact and ignores blank names, so an empty push name never erases a known
name. `on_contact` stores the contact and reloads the lists
(`repo://src/app.rs#L465-L477`).

## Autoreply flags

- `set_autoreply(chat_jid, enabled)` (`repo://src/history.rs#L259-L270`) upserts
  the `chats` row, creating it with defaults when the chat has no messages yet.
- `is_autoreply(chat_jid)` (`repo://src/history.rs#L276-L285`) returns the stored
  flag, or `false` for an unknown chat.
- `set_global_autoreply(enabled)` / `is_global_autoreply()`
  (`repo://src/history.rs#L291-L317`) upsert and read the `global_autoreply` row
  of `settings`; the default is `false` when it was never stored. The global
  flag is independent of the per-chat flags: toggling it never changes a
  `chats.autoreply` value, and a chat is answered when either is on
  (`repo://src/app.rs#L854-L860`).

Two consequences of `set_autoreply` upserting the row: a chat toggled on from
the contacts list exists in `chats` with `last_timestamp = 0` and no messages,
so it is listed (sorted last, named by the JID user part until a contact or a
message names it), and the startup catch-up skips it because it has no last
message to inspect (`repo://src/app.rs#L964-L981`). New messages never reset the
per-chat flag, because the message upsert only touches `last_timestamp` and
`name`.

## Stored message bodies: write-once, except transcriptions

A message body is written once, by `save_message`. The insert is
`INSERT OR IGNORE`, so a second save of the same id cannot change `text`, and no
other method writes the column — `upsert_chat_for_message` touches only
`chats`. The single exception is `update_message_text`:

```rust
UPDATE messages SET text = ?1 WHERE message_id = ?2
```

`update_message_text(message_id, new_text)`
(`repo://src/history.rs#L338-L346`) replaces the body of an existing message and
returns `true` when a row matched the id, `false` when the id is not stored (in
which case nothing is written). It is not scoped by chat or sender and it
touches no other column, so `timestamp`, `from_me`, `is_ai` and the chat row are
preserved.

The only caller is the transcription step of the
[AI autoreply](../workflows/ai-autoreply.md) workflow
(`repo://src/app.rs#L784-L801`). For each transcribed audio whose message still
exists it stores

```
[messaggio vocale] <transcription>
```

i.e. the constant `VOICE_NOTE_TEXT` (`repo://src/app.rs#L38-L41`, a deliberate
duplicate of the private `VOICE_PLACEHOLDER` in
`repo://src/whatsapp.rs#L45-L46`), a space, and the transcript. The previous
body was exactly `[messaggio vocale]`, so this is an **overwrite** with the
placeholder re-created as a prefix, not an append to arbitrary text. The write is
guarded by `message_exists`, and the `bool` returned by `update_message_text` is
discarded because of that guard.

There is no transcription column and no transcription table: the transcript
lives only inside `messages.text`. Deleting the chat (or its messages) therefore
discards the transcripts too, and the text after the placeholder is the only
record that an audio was transcribed.

### Why the placeholder stops matching

`is_voice_note` returns `true` only when the trimmed text equals
`[messaggio vocale]` exactly (`repo://src/whatsapp.rs#L1174-L1181`). Once a
transcription is stored the body is `[messaggio vocale] <transcription>`, which
no longer matches, and two consumers depend on that:

- `audios_to_transcribe` treats a message as already transcribed when an audio
  is in progress **or** its stored text is no longer the plain placeholder
  (`repo://src/app.rs#L900-L918`), so each audio is transcribed once even though
  nothing else records it — and an audio whose transcription failed keeps the
  bare placeholder.
- `user_content` in `src/autoreply.rs` replaces the body with the
  "the contact sent a voice message you cannot listen to" sentinel only while it
  still matches, otherwise it passes the stored text through
  (`repo://src/autoreply.rs#L364-L379`). A transcribed voice note therefore
  reaches the model as `[messaggio vocale] <transcription>` in that reply and in
  every later context built from the store.

```mermaid
flowchart TD
    A["audio message saved with text [messaggio vocale]"] --> B["is_voice_note true, AI sees the cannot-listen sentinel"]
    B --> C["transcription succeeds, message_exists true"]
    C --> D["update_message_text stores [messaggio vocale] plus transcript"]
    D --> E["is_voice_note false, AI sees the transcript"]
    E --> F["audios_to_transcribe skips it as done"]
    C -->|"chat deleted meanwhile"| G["nothing written, no row"]
```

The body-mutation lifecycle that makes a transcription a one-time, permanent
part of the stored message.

## Image descriptions

The [AI autoreply](../workflows/ai-autoreply.md) asks the vision model to
describe each image once and stores the text, so later replies reuse it instead
of downloading and describing the image again:

- `message_exists(message_id)` (`repo://src/history.rs#L323-L332`) tells whether
  a message is still stored. The app checks it before saving a description
  (`repo://src/app.rs#L747-L759`), so a chat deleted while its images were being
  described gets no orphan rows.
- `save_image_description(message_id, chat_jid, description)`
  (`repo://src/history.rs#L352-L363`) upserts the row, replacing a previous
  description of the same message.
- `image_descriptions(chat_jid)` (`repo://src/history.rs#L369-L381`) returns the
  chat's descriptions keyed by message id; the app uses it to skip images
  already described (`repo://src/app.rs#L882-L898`) and to turn image messages
  into text when building the AI context
  (`repo://src/app.rs#L1009-L1028`).

Descriptions are separate from `messages.text`, which keeps only the image
placeholder plus optional caption rendered by `src/whatsapp.rs`
(`repo://src/whatsapp.rs#L1085-L1103`); editing a body would never touch them,
and only `delete_chat` removes them.

## Deleting a chat

`delete_chat(chat_jid)` (`repo://src/history.rs#L389-L408`) removes, in one
transaction, all messages of the chat, all their image descriptions and the
`chats` row (and therefore its per-chat autoreply flag). Because the
deletion is by `chat_jid`, it covers the transcripts and image descriptions
stored for that chat as well. The contact row, other chats, `settings` and the
WhatsApp session are untouched; deleting an unknown chat is not an error, and a
later message recreates the chat row through the message upsert.

The app calls it after a double Delete press in the chat list; `confirm_delete`
also drops the scheduled reply and the kept images/audios of that chat and
closes it if open (`repo://src/app.rs#L1249-L1272`).

## Tests

The inline module (`repo://src/history.rs#L557-L879`) covers duplicate ids and
the untouched stored body, limit and ordering of `recent_messages`,
`messages_since` (boundary inclusion, limit keeping the most recent, ascending
order), flag and sender-name round trips, chat ordering, name fallbacks, chat
naming rules (set once, never from own messages), per-chat autoreply defaults,
persistence and survival of incoming messages, the global autoreply default,
toggle and independence from chat flags, `message_exists` across a chat
deletion, `update_message_text` (body changed, missing id reported as `false`),
image descriptions (per chat, replaced, deleted with the chat), `delete_chat`
(messages and row gone, contact and other chats kept), contact ordering and
blank names, and on-disk persistence with parent directory creation. See
[Unit tests](../testing/unit-tests.md).