From the second you first meet someone and put their phone number in your phone, you are creating history through your conversation. A few years ago, my wife and I realized that the evolution of our relationship was archived in our texts and we didn’t want to lose it. So I wrote this script to visualize our entire SMS conversation in one PDF.

The Idea

I wanted to be able to generate a PDF that I could archive so we could always reflect on our old conversations. To get started, I knew that every time you plug your device into a mac and sync that data is backed up. After some more research, I found that each time a user syncs their device, a folder is created in the folder called: /Library/YourUserName/Application Support/Mobile Sync/Backup. Each of these folders contains a backup of your phone, inside of which is the magic file we are looking for 3d0d7e5fb2ce288813306e4d4636395e047a3d28 this is the cryptic name of the database where all of your messages are stored.

The Analysis

Even though there isn’t an extension on this file, I discovered that this is a SQLite database. That means it’ll be super easy for us to access.

Opening up the database in DB Browser (a free SQLite browser), I saw 10 tables:

Of course, message jumped out at me right away, so I looked into the table and low and behold, every message I ever sent was there.

As I looked at the data, I realized that handle_id is the field that identifies the person that you are texting with. If you look into the handle database, there is a unique ID for each of the phone numbers (or email addresses) that you’ve messaged with. With this, we can now find all of the messages in conjunction with a specific user.

I wanted to be able to quickly script out something that would allow me to pass in a phone number, look up that user, and return the conversation to and from that number in the messages table.

Even though there are over 20 fields in the message database, all we care about is the following fields: date, is_from_me, and text

The Code

So, now I knew what my script needed to do:

• The user would run the script and supply a phone number of who they want to see the text exchange with
• My script would look for the handle(s) that matched the phone number (there could be more than one if you both text and use iMessage to the same number)
• I would search the messages table with the handle_id as a query parameter and return date, is_from_me, and text
• I would do a bit of manipulation of the the results and use a ruby gem tool called Prawn to create a PDF with the data so that the messages could be visualized

The source code is on Github at https://github.com/timjuravich/relation-text and free to use.

The Output

It took a lot of finagling with date transforms and styling but after a few hours of coding, I had an awesome PDF generating. For my conversation with my wife, the PDF was over 700 pages long! Here is an example excerpt:

Reflection

I wrote this code many years ago, when me and my wife were still dating. It’s funny to see how the text conversations and technology have changed over the years. In the early days, we used “:)” because emoticons didn’t exist and you couldn’t include pictures in texts. Nowadays, our texts are riddled with emoticons and pictures.