Understanding SQLite Log Modes and Fixing Duplicate Message ID Issues in Chat Applications

In chat applications the database is a core component responsible for storing IM messages, handling concurrent reads/writes, and managing error scenarios. SQLite supports two main logging modes: Rollback (DELETE, TRUNCATE, etc.) which records pre‑change pages, and WAL (Write‑Ahead Log) which records post‑change pages.

When WAL is enabled, SQLite creates three files ( db , db-wal , db-shm ). Writes go to the -wal file and are later checkpointed to the main database file, allowing readers to see a consistent snapshot while writers continue appending.

The article then describes a user‑reported bug where after upgrading the app, duplicate system messages appear after a message is retracted. Investigation revealed that older versions of the app did not enforce a UNIQUE constraint on the msgId column, so existing duplicate rows persisted after schema migration.

Several experiments were performed:

Creating a table with msgId VARCHAR(256) UNIQUE and inserting duplicate rows using INSERT caused an error and no insertion.

Using INSERT OR IGNORE with the same schema silently ignored duplicates.

Creating a table without the UNIQUE constraint allowed duplicate inserts with both INSERT and INSERT OR IGNORE .

These results show that the presence of a UNIQUE constraint determines whether duplicate messages can be stored.

To fix the issue, the app attempts to create a unique index:

CREATE UNIQUE INDEX IF NOT EXISTS index_chat_msgId ON ChatModelDB (msgId);

Because existing duplicate rows prevent index creation, a cleanup routine is added to delete extra rows while keeping one copy per msgId :

DELETE FROM ChatModelDB WHERE rowid NOT IN (SELECT MIN(rowid) FROM ChatModelDB GROUP BY msgId);

After cleanup, the unique index is created successfully, and subsequent duplicate inserts are rejected or ignored as expected.

The article also explains how to detect whether a table already has a UNIQUE constraint or index by querying the sqlite_master table, for example:

SELECT sql FROM sqlite_master WHERE type='table' AND name='ChatModelDB' AND sql LIKE '%UNIQUE%msgId%';

SELECT name FROM sqlite_master WHERE type='index' AND tbl_name='ChatModelDB' AND sql LIKE '%UNIQUE%msgId%';

By combining these checks with the cleanup logic, the app ensures that all users, even those who upgraded from older versions, have a consistent and safe database schema.