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-9fec9d1af4 resource: repo://src/app.rs
id: openwiki-source-043e6252ba resource: repo://src/autoreply.rs
id: openwiki-source-ce0c4ea8cd resource: repo://src/history.rs
id: openwiki-source-7ea5eac45e 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). 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 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).

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.

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):

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.
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.
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 (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 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:

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 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.

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 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.

history-store.md 15 KB History Raw