Issue: WAL remnants?

I was just curious after seeing some reddit debate, when a message is timer-deleted from a 1:1 chat and is overwritten with zeros, is recovery of these deleted texts from the WAL file able to happen until a checkpoint? Or is there a vacuum of these messages? Am I able to recover timer-deleted messages?

Would appreciate any form of feedback. I feel this is the only place where I can get a credible answer.

[ThomasSession]

Because the app uses SQLCipher, deleted content in ordinary tables is scrubbed by default via secure_delete=ON. In SQLite terms that means deleted content is overwritten with zeros on the affected page.

However, because we run in WAL mode, older page versions may remain in the WAL file until checkpoint/reset, and those older WAL frames are not immediately rewritten just because the row was later deleted.

The main database and WAL page data are encrypted under SQLCipher. In this app, the SQLCipher database secret is stored in app-private preferences as a sealed blob, with the sealing key held in Android Keystore. So recovery of any WAL remnants would generally require access not just to the database files but also to the protected key material, or a compromised/unlocked device environment.

We also periodically vacuum the database, but we do not currently force an immediate checkpoint each time a disappearing message expires. More aggressive WAL checkpointing could reduce that exposure window, though it would come with performance/battery tradeoffs.

Thank you for the feedback! Much appreciated. When the database is periodically vacuumed, is it also checkpointed? Or do checkpoints still only occur when the WAL hits its 1000 page limit?

[ThomasSession]

We don’t currently do an explicit wal_checkpoint alongside the periodic VACUUM.

So in practice, checkpoints are still the normal SQLite WAL ones: automatic PASSIVE checkpoints when a commit leaves the WAL at or above the default 1000-page threshold, and also when the last DB connection closes.

One nuance is that VACUUM is itself a write operation, so it can participate in SQLite’s normal auto-checkpoint behaviour, but we are not deliberately pairing “vacuum + checkpoint” in app code today.