Manipulating the Signal Desktop database externally

If you use the desktop client for the chat app Signal, you probably already know that it is in fact made out of garbage. As a result the app's performance gets pretty bad once you get loaded up on messages. Until someone rewrites it as a real desktop app, one simple way to improve the performance is to regularly delete old messages from it. I wanted to made a script that automatically does this for me on a repeating schedule. Here is a short description of what I figured out in the process. You can find download links for my script at the bottom of the page.

Database Location

Ok first of all, I'm doing this on Linux. Paths mentioned here will need to be slightly adjusted for Windows systems.

signal-desktop uses a SQLCipher database stored at ~/.config/Signal/sql/db.sqlite. SQLCipher is a modified version of SQLite 3 that encrypts the contents of the database. It is unclear what the point of using SQLCipher in this case is, because the key for the database is conveniently stored in ~/.config/Signal/config.json. With the key from there you should be able to install SQLCipher from your distribution and read from the database. You enter the key using the PRAGMA key statement as described here. Note that the key in that config file is the raw key in hex, not a passphrase, so enter it like PRAGMA key = "x'YOUR_KEY_HERE'";.

Attachments

The basic task of removing the old messages is as simple as using the time_received column to determine whether the message is old enough, and deleting the rows that match our criteria for this. However, we must also consider that some messages contain attachments. These are stored outside of the database in ~/.config/Signal/attachments.noindex. Each message has a bunch of data in JSON in the json column of the messages table. In the JSON data, each element in the attachments array has a path property that references the file on the filesystem. It may also have a thumbnail.path property depending on the file type.

fts5

If you tried to manipulate the database using your distribution's version of SQLCipher, you probably were able to read it fine, but if you tried to make any changes you may have gotten an error like no such module: fts5. fts5 is a module for allowing full text searches of text type columns, and signal-desktop's internal copy of SQLCipher seems to use it. Apparently a table created with fts5 can't be modified by a version of SQLite (or SQLCipher, in this case) without fts5. The copy of SQLCipher I obtained from the Debian repositories did not have this module enabled. This module ships with the SQLite and SQLCipher sources, but needs to be enabled at compile time with ./configure --enable-fts5. This needs to be combined with the other flags normally needed to compile SQLCipher as described here.

SQLCipher version

I'm not sure exactly what SQLCipher has going on for their versioning situation, but according to their CHANGELOG.md some of the "releases" on their GitHub repository are technically unreleased. I was able to compile the unreleased versions fine, but it wouldn't decrypt the database successfully (i.e. file is not a database). At the time of writing, the latest actually released version was 3.4.2, and I was able to use this version just fine.

Note that my setup.sh downloads that version to the current directory, and db-cleaner.sh expects it to be there. You can, of course, modify the script to locate SQLCipher wherever you prefer.

jq

My script uses jq to parse JSON data from the shell. You can probably get it from your distribution.

Conclusions

Well, signal-desktop loaded a lot faster. Seems like it works.

Here's the scripts I made for myself. The scripts themselves are licensed under The GNU General Public License, version 3.0. Note that script calls a number of external programs, which may be licensed differently.

Posted