Building a Local SMS Archive System: Querying macOS Messages Database for Business Line Integration

When you need to integrate incoming SMS messages into a broader business automation system, the naive approach—polling a third-party SMS gateway—introduces cost, latency, and external dependencies. This post documents a different strategy: leveraging the local macOS Messages database (chat.db) as a free, persistent archive that can be queried on-demand.

The Problem: Business SMS Without Third-Party Overhead

A business line (619-986-7344, routed to an iPhone) was receiving SMS messages that needed to be accessible to automation scripts and agents. The requirements were:

• Zero cost (no SMS gateway subscription)
• Local storage (data stays on device)
• Easy programmatic access (queryable by scripts)
• Privacy-aware (separate business line from personal SMS)
• Minimal setup overhead

The macOS Messages app already stores all SMS/iMessage data in a SQLite database at ~/Library/Messages/chat.db. Rather than sync messages to a remote service, we could query this database directly and export messages on demand.

Technical Architecture: SQLite as the Source of Truth

The Messages database structure includes three key tables for SMS filtering:

• chat — conversation metadata, includes guid (unique identifier per contact/service)
• message — individual messages with text, date, is_from_me fields
• chat_message_join — junction table linking messages to conversations

The critical insight: each distinct contact/service combination gets a unique guid in the format sms;-;[phone_number] or sms;-;[email] depending on how iMessage routing is configured. By querying for a specific guid, we isolate one conversation thread across potentially multiple contacts.

Implementation: The read-sms Script

A shell script at /Users/cb/bin/read-sms handles database queries and exports. The script structure:

#!/bin/bash
# Query macOS Messages database for a specific phone number
# Usage: read-sms [phone_number] [output_format]

PHONE="619-986-7344"
DB_PATH="$HOME/Library/Messages/chat.db"
OUTPUT_FILE="$HOME/exports/sms_jada_line_$(date +%Y%m%d_%H%M%S).txt"

# Step 1: Find the chat guid for this phone number
CHAT_GUID=$(sqlite3 "$DB_PATH" \
  "SELECT guid FROM chat WHERE guid LIKE '%619%' LIMIT 1;")

# Step 2: Export all messages for this conversation
sqlite3 -header "$DB_PATH" \
  "SELECT 
     datetime(message.date/1000000000 + 978307200, 'unixepoch', 'localtime') AS timestamp,
     CASE WHEN message.is_from_me = 1 THEN 'SENT' ELSE 'RECEIVED' END AS direction,
     message.text
   FROM message
   JOIN chat_message_join ON message.ROWID = chat_message_join.message_id
   WHERE chat_message_join.chat_id = (
     SELECT ROWID FROM chat WHERE guid = '$CHAT_GUID'
   )
   ORDER BY message.date ASC;" > "$OUTPUT_FILE"

echo "Exported to: $OUTPUT_FILE"

Why this approach:

• sqlite3 is built into macOS — no external dependencies
• The date/1000000000 + 978307200 calculation converts Apple's internal timestamp format (nanoseconds since 2001-01-01) to Unix epoch
• The chat_message_join table is queried rather than raw message rows to ensure we only get messages belonging to the correct conversation
• Output is plain text, line-delimited, easy to parse by downstream agents

Key Database Queries: Finding the Right Conversation

The trickiest part is identifying the correct chat.guid` for the business line. If multiple devices sync (iPhone + Mac) and iMessage is enabled, the same phone number may appear under different guids or accounts.

# List all SMS conversations with their guids
sqlite3 ~/Library/Messages/chat.db \
  "SELECT guid, chat_identifier FROM chat WHERE guid LIKE 'sms%' ORDER BY guid;"

# Find conversations for a specific phone pattern
sqlite3 ~/Library/Messages/chat.db \
  "SELECT guid, chat_identifier FROM chat WHERE chat_identifier LIKE '%619%';"

# Count messages per conversation to verify you have the right one
sqlite3 ~/Library/Messages/chat.db \
  "SELECT chat.guid, COUNT(message.ROWID) as msg_count
   FROM chat
   JOIN chat_message_join ON chat.ROWID = chat_message_join.chat_id
   JOIN message ON message.ROWID = chat_message_join.message_id
   GROUP BY chat.guid
   ORDER BY msg_count DESC;"