whatsapp-tail-merge

Merges the recently-diverged tails of two decrypted WhatsApp-for-Android msgstore.db files that share most of their history (post-2021 schema, the one with message/chat/jid tables). Typical cause: a backup was restored on a second phone and both kept receiving messages for a while.

The base database is copied to the output and the other database to a temporary sibling snapshot — both through read-only connections, so the inputs are never written to, not even SQLite sidecar files. Everything the other has that the base lacks is then inserted with all row-id references remapped: contacts (jid), chats, group membership, lid↔jid pairs, calls, messages, add-ons (reactions/polls/pins), and every satellite table hanging off those (media, quotes, vcards, receipts, locations, system-message details, …).

Usage

pnpm install
pnpm merge --base newer.db --other older.db --out merged.db

Pick as --base the database whose device state (drafts, mute settings, read positions, group membership) you want to win; message content merges symmetrically either way. --help lists the remaining flags (--force-sort-renumber, --no-vacuum, --verbose).

How it works

1. Copy base → out and other → a temporary snapshot next to it, via SQLite's online backup API over read-only connections; ATTACH the snapshot. (Plan disk space for the output plus that snapshot; the snapshot is removed when the run ends, and a failed run also removes its partial output.)
2. Sanity checks: post-2021 schema markers and integrity_check on both.
3. Drop the output's triggers for the duration (restored at the end), as WhatsApp-Db-Merger does; the FTS/cascade triggers must not fire mid-merge.
4. Root tables get full old→new id maps in temp tables. Row identity uses WhatsApp's own UNIQUE indexes: jid(raw_string), chat(jid_row_id), message/message_add_on (chat_row_id, from_me, key_id, sender_jid_row_id), call_log (jid_row_id, from_me, call_id, transaction_id), call_link(token), bcall_session(session_id), and labels(label_name).
5. Satellite tables are discovered by column-name conventions (*_message_row_id, *jid_row_id, *chat_row_id, vcard_row_id, …) and anchored on the ref that owns the row (message-ish parents before satellite parents before chat, jid last; an exact <parent>_row_id name beats prefixed variants like parent_message_row_id). Chains through other satellites (annotations → vertices, compositions → mentions) are followed, topologically sorted, and rows belonging to newly inserted anchors are copied with refs remapped. Unknown positive refs become NULL with a logged count, never silently wrong ids; where the column is NOT NULL (e.g. message.chat_row_id), the row was an invisible orphan in the other database already — its parent is gone there too — and is skipped with a logged count instead.
6. Post-fixes: message.sort_id is renumbered by (timestamp, _id) when the tails interleave (that column exists precisely so display order can diverge from insertion order); chat preview columns, sort_timestamp, and reaction pointers are repaired for chats that gained messages; props.fts_ready = 0 forces a search-index rebuild; backup_changes is cleared.
7. Audit: message keys unique, no orphaned satellites, integrity_check. Everything runs in one transaction; any failure rolls back.

What is not merged

State and legacy tables where the base's version should win are skipped and reported when the other side has rows there: receipts, message_thumbnails, messages_quotes, group_participants(_history), frequent(s), media_refs, status(_list), user_device(_info), props, backup_changes, and all *fts* tables. Per-recipient delivery/read receipts (receipt_user, receipt_device, add-on receipts) are an exception: rows the other device has for shared messages are copied when the base lacks that (message, recipient) pair, keyed by WhatsApp's own UNIQUE indexes; where both devices recorded the same receipt, the base's row wins. The device's own read-state (chat.last_read_*, legacy receipts) keeps the base's values. // XXX comments in src/merge.ts mark every judgement call.

Around this tool

Encrypted .crypt15 backups are decrypted/re-encrypted with wa-crypt-tools (wadecrypt / waencrypt with the 64-hex-digit backup key). Media files are plain files — merge the Media/ directories by copying.

In a full app-data pull from a (rooted) device, the already-decrypted database is com.whatsapp/databases/msgstore.db — but with WhatsApp's multi-account feature (Settings → Account list, on Android since late 2023) that path holds only the primary account. Every account added later gets an isolated private tree of its own, including com.whatsapp/accounts/<id>/databases/msgstore.db (numeric ids such as 1001), so a pull can contain several msgstore.db files for different phone numbers. Merge only databases belonging to the same account; to check which is which, compare message counts, newest timestamps, and top chats — e.g. sqlite3 "file:msgstore.db?mode=ro&immutable=1" "SELECT COUNT(*), datetime(MAX(timestamp)/1000,'unixepoch') FROM message;". A file of only ~3 MB is roughly the empty schema: an account that saw little or no use. Prior art this borrows from: natario1/whatsapp-database-merger, WhatsApp-Db-Merger, SenCodeMaker's SQL fork, and EkriirkE's WAMerge. Several fixes (receipt/poll-vote handling, input snapshotting, identity ambiguity guards) came out of reviewing an independent ChatGPT 5.6 Pro implementation of the same idea, kept in Alternate implementation/ for reference.

Development

pnpm lint   # oxlint
pnpm check  # tsc
pnpm test   # vitest (unit + fast-check property tests against a real 2025-03 schema dump)