Using the chat sqlite database

[mitchellkelly]

When messages are sent and received on a Mac, there is a Sqlite database that is updated along with the archive files.

All of this info was accurate for Mac OS X 10.12. Things could be different in different OS X versions

The chat database is located at ~/Library/Messages/chat.db

The database provides seven tables: message, attachment, message_attachment_join, handle, chat, chat_handle_join, chat_message_join

Message

The message table provides all of the info specific to a single message.

message table schema:

CREATE TABLE message (
	ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
	guid TEXT UNIQUE NOT NULL,
	text TEXT,
	replace INTEGER DEFAULT 0,
	service_center TEXT,
	handle_id INTEGER DEFAULT 0,
	subject TEXT,
	country TEXT,
	attributedBody BLOB,
	version INTEGER DEFAULT 0,
	type INTEGER DEFAULT 0,
	service TEXT,
	account TEXT,
	account_guid TEXT,
	error INTEGER DEFAULT 0,
	date INTEGER,
	date_read INTEGER,
	date_delivered INTEGER,
	is_delivered INTEGER DEFAULT 0,
	is_finished INTEGER DEFAULT 0,
	is_emote INTEGER DEFAULT 0,
	is_from_me INTEGER DEFAULT 0,
	is_empty INTEGER DEFAULT 0,
	is_delayed INTEGER DEFAULT 0,
	is_auto_reply INTEGER DEFAULT 0,
	is_prepared INTEGER DEFAULT 0,
	is_read INTEGER DEFAULT 0,
	is_system_message INTEGER DEFAULT 0,
	is_sent INTEGER DEFAULT 0,
	has_dd_results INTEGER DEFAULT 0,
	is_service_message INTEGER DEFAULT 0,
	is_forward INTEGER DEFAULT 0,
	was_downgraded INTEGER DEFAULT 0,
	is_archive INTEGER DEFAULT 0,
	cache_has_attachments INTEGER DEFAULT 0,
	cache_roomnames TEXT,
	was_data_detected INTEGER DEFAULT 0,
	was_deduplicated INTEGER DEFAULT 0,
	is_audio_message INTEGER DEFAULT 0,
	is_played INTEGER DEFAULT 0,
	date_played INTEGER,
	item_type INTEGER DEFAULT 0,
	other_handle INTEGER DEFAULT 0,
	group_title TEXT,
	group_action_type INTEGER DEFAULT 0,
	share_status INTEGER DEFAULT 0,
	share_direction INTEGER DEFAULT 0,
	is_expirable INTEGER DEFAULT 0,
	expire_state INTEGER DEFAULT 0,
	message_action_type INTEGER DEFAULT 0,
	message_source INTEGER DEFAULT 0,
	associated_message_guid TEXT,
	associated_message_type INTEGER DEFAULT 0,
	balloon_bundle_id TEXT,
	payload_data BLOB,
	expressive_send_style_id TEXT,
	associated_message_range_location INTEGER DEFAULT 0,
	associated_message_range_length INTEGER DEFAULT 0,
	time_expressive_send_played INTEGER,
	message_summary_info BLOB
);

Attachment

The attachment table provides information about a file that was attached to a message.

attachment table schema:

CREATE TABLE attachment (
	ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
	guid TEXT UNIQUE NOT NULL,
	created_date INTEGER DEFAULT 0,
	start_date INTEGER DEFAULT 0,
	filename TEXT,
	uti TEXT,
	mime_type TEXT,
	transfer_state INTEGER DEFAULT 0,
	is_outgoing INTEGER DEFAULT 0,
	user_info BLOB,
	transfer_name TEXT,
	total_bytes INTEGER DEFAULT 0,
	is_sticker INTEGER DEFAULT 0,
	sticker_user_info BLOB,
	attribution_info BLOB,
	hide_attachment INTEGER DEFAULT 0
);

Message / Attachment join

The message_attachment_join table is used to create a relationship between messages and attachments. Rows in this table have a message_id and an attachment_id. Both of these fields correspond the a ROWID in their respective tables.

message_attachment_join table schema:

CREATE TABLE message_attachment_join (
	message_id INTEGER REFERENCES message (ROWID) ON DELETE CASCADE,
	attachment_id INTEGER REFERENCES attachment (ROWID) ON DELETE CASCADE,
	UNIQUE(
		message_id,
		attachment_id
	)
);

Handle

The handle table provides information for each iMessage user that has a message stored in the message table

handle table schema:

CREATE TABLE handle (
	ROWID INTEGER PRIMARY KEY AUTOINCREMENT UNIQUE,
	id TEXT NOT NULL,
	country TEXT,
	service TEXT NOT NULL,
	uncanonicalized_id TEXT,
	UNIQUE (
		id,
		service
	)
);

Chat

The chat table provides some information about a room that messages are sent in.

chat table schema:

CREATE TABLE chat (
	ROWID INTEGER PRIMARY KEY AUTOINCREMENT,
	guid TEXT UNIQUE NOT NULL,
	style INTEGER,
	state INTEGER,
	account_id TEXT,
	properties BLOB,
	chat_identifier TEXT,
	service_name TEXT,
	room_name TEXT,
	account_login TEXT,
	is_archived INTEGER DEFAULT 0,
	last_addressed_handle TEXT,
	display_name TEXT,
	group_id TEXT,
	is_filtered INTEGER,
	successful_query INTEGER
);

Chat / Handle join

The chat_handle_join table is used to create a relationship between the chat and handle tables. Similar to the other join tables, the rows in this table connect a ROWID in the chat table to a ROWID in the handle table.

Each row in this table signifies that a specific iMessage user is in a specific chat.

chat_handle_join table schema:

CREATE TABLE chat_handle_join (
	chat_id INTEGER REFERENCES chat (ROWID) ON DELETE CASCADE,
	handle_id INTEGER REFERENCES handle (ROWID) ON DELETE CASCADE,
	UNIQUE (
		chat_id,
		handle_id
	)
);

Chat / Message join

The chat_message_join table is used to create a relationship between the chat and message tables. Similar to the other join tables, the rows in this table connect a ROWID in the chat table to a ROWID in the message table.

Each row in this table signifies that a specific message belongs to a specific chat.

chat_message_join table schema:

CREATE TABLE chat_message_join (
	chat_id INTEGER REFERENCES chat (ROWID) ON DELETE CASCADE,
	message_id INTEGER REFERENCES message (ROWID) ON DELETE CASCADE,
	PRIMARY KEY (
		chat_id,
		message_id
	)
);

[mitchellkelly]

One limitation the chat database has is that contact names are not stored in the database itself. The handle table only has an id field for a user which will either be a user's phone number or iCloud address.

This data could possibly be gathered using AppleScript and the Contacts app though

[jabyrd3]

@mitchellkelly I actually did this in a standalone imessage gateway, here:

https://github.com/jabyrd3/ymessage/blob/612c40c0487866482339b1cbaa658f6b86c132f7/contacts.mjs#L2

using this: https://github.com/pepebecker/contacts-cli

so that i can lookup contact name by id for display in the client

[jabyrd3]

(don't use that repo though, its a personal project and its not really intended to be installed by anyone else. I am messing with this bridge in an attempt to replace that thing :-X )

[mitchellkelly]

@jabyrd3 That looks really promising. Thank you for sharing!