Sail JADA — Engineering Log

Technical work on sailjada.com and the JADA booking systems

Building a Local SMS Digest System: macOS Messages Integration Without Twilio

Over the past development session, I built a SMS digest system that reads message threads from macOS Messages.app and compiles summaries without requiring external SMS service credentials. This approach leverages the local SQLite database that Messages maintains, making it ideal for development environments and reducing dependency on third-party services.

The Problem Statement

The original infrastructure relied on Twilio for SMS operations, but credentials weren't consistently available in the development environment. Additionally, for local testing and ops workflows, we needed a way to quickly surface recent SMS conversations without hitting external APIs. The goal was to enable rapid SMS digest generation from existing local message data.

Technical Architecture

The solution consists of two main components:

  • Local SMS Export Script: /Users/cb/Documents/repos/tools/samsung_sms_sync.py — reads the macOS Messages SQLite database and extracts conversation threads
  • LaunchAgent Daemon: /Users/cb/Library/LaunchAgents/com.cb.samsung-sms-sync.plist — schedules periodic SMS digest generation and email delivery

Database Schema and Query Strategy

macOS Messages stores all SMS and iMessage data in a SQLite database located at:

~/Library/Messages/chat.db

The database contains several key tables:

  • chat — conversation metadata including participant identifiers
  • message — individual message records with timestamps and content
  • chat_message_join — mapping between chats and messages
  • handle — phone numbers and identifiers associated with participants

The critical query pattern filters for recent SMS threads by joining these tables and sorting by timestamp:

SELECT DISTINCT c.rowid, h.id as phone_number, m.text, m.date, m.is_from_me
FROM chat c
JOIN chat_message_join cmj ON c.rowid = cmj.chat_id
JOIN message m ON cmj.message_id = m.rowid
JOIN handle h ON c.rowid = (SELECT chat_id FROM chat_message_join WHERE message_id = m.rowid LIMIT 1)
WHERE m.date > ? -- timestamp threshold
ORDER BY m.date DESC

The timestamp comparison uses macOS's epoch format (seconds since 2001-01-01), which required explicit conversion logic in the Python script to align with Unix epoch.

Implementation: samsung_sms_sync.py

The main script handles three operations:

  1. Database Access: Opens the Messages chat.db with read-only permissions to prevent locking issues with the active Messages.app process
  2. Thread Extraction: Queries conversations modified in the last 24-48 hours, grouping messages by participant phone number
  3. Digest Compilation: Summarizes each thread with key operational items, financial mentions, and action items

Key function structure:

  • read_messages_db(time_threshold_hours) — opens chat.db, handles schema variations across macOS versions, returns list of conversation objects
  • extract_thread(phone_number, limit=50) — retrieves up to 50 most recent messages from a specific participant
  • compile_digest(threads) — analyzes threads for business-critical information (payments, equipment issues, deadlines) and formats for email delivery
  • send_digest_email(digest_text, recipient) — uses AWS SES to deliver the summary

The script explicitly handles macOS Messages quirks:

  • Phone numbers in the handle table are stored with country codes (e.g., +1-530-262-3442), requiring normalization
  • The date field in the message table uses macOS absolute time (seconds since 2001), not Unix epoch
  • Messages.app locks the database while running, so the script opens with timeout=2.0 to avoid hangs

LaunchAgent Configuration

The daemon is configured in:

/Users/cb/Library/LaunchAgents/com.cb.samsung-sms-sync.plist

This XML plist file specifies:

  • Label: com.cb.samsung-sms-sync — unique identifier for launchctl
  • ProgramArguments: Full path to Python executable and script
  • StartInterval: Frequency in seconds (e.g., 3600 for hourly runs)
  • StandardOutPath/StandardErrorPath: Log files for debugging, typically ~/Library/Logs/samsung-sms-sync.log

Installation and management:

launchctl load ~/Library/LaunchAgents/com.cb.samsung-sms-sync.plist
launchctl list | grep samsung-sms-sync
launchctl unload ~/Library/LaunchAgents/com.cb.samsung-sms-sync.plist

Email Delivery via AWS SES

Digests are sent through AWS SES rather than local mail, providing reliability and audit trails. The script reads SES credentials from the environment (stored in a secured vault, not in source control) and uses the boto3 SDK:

import boto3
ses = boto3.client('ses', region_name='us-west-2')
response = ses.send_email(
    Source='ops@sailjada.com',
    Destination={'ToAddresses': ['c.b.ladd@gmail.com']},
    Message={
        'Subject': {'Data': 'SMS Digest - ' + date_str},
        'Body': {'Text': {'Data': digest_text}}
    }
)

Key Design Decisions

  • No Twilio Dependency: By reading the local Messages database, we eliminate the need for Twilio credentials in dev environments. This reduces attack surface and speeds up testing cycles.
  • Read-Only Access: The script opens chat.db with sqlite3.PARSE_DECLTYPES and check_same_thread=False, ensuring it doesn't interfere with the active Messages.app process.
  • Digest-First Format: Rather than dumping raw messages, the script applies domain knowledge to surface operational issues (equipment failures, payments, deadlines) relevant to the business.
  • Scheduled Execution: Using LaunchAgent instead of cron provides better integration with macOS and simpler log management through Console.app.

Limitations and Future Improvements

The current implementation has intentional constraints:

  • Read-Only: The system cannot send SMS replies; it's digest-only. Outbound SMS still requires Twilio integration or the native Messages.app interface.
  • macOS-Specific: This approach doesn't work on Linux or Windows servers. For production multi-platform support, Twilio or a similar service becomes necessary.