Self-hosted, Self-Improving, Federated Everything App — dev management, agent orchestration, persistent mind, digital twin, second brain, and creative pipeline from a single dashboard over Tailscale to port the user into the OS
JavaScript ai-agentsdashboarddeveloper-toolsdigital-twinquantified-selfsecond-brainself-hostedtailscale
/**
* iMessage ingestion (#2151) — read the local macOS Messages database
* (`~/Library/Messages/chat.db`) and feed both the Tribe touchpoint log and the
* machine-local human-activity timeline (#2150).
*
* Design constraints (see docs/plans/2026-07-04-human-activity-tracking.md):
*
* - **Zero new dependencies.** SQLite reads use Node's built-in `node:sqlite`
* (Node ≥22). The DB is opened `readOnly: true` — we NEVER write to Apple's DB.
* - **Machine-local.** The incremental cursor lives in a `data/` JSON file, not a
* federated store — chat.db is per-machine. Derived events land in the
* already-machine-local `human_activity_events` table.
* - **LLM-free + idempotent.** Deterministic identity matching via `tribeMatch`;
* every event/touchpoint carries a stable dedupe key so re-syncs are no-ops.
* - **Best-effort text.** When `message.text` is NULL, the body is extracted from
* the archived `attributedBody` typedstream; a parse failure is counted and
* skipped, never fatal.
* - **Full Disk Access.** Opening chat.db requires the node/PM2 process to have
* macOS Full Disk Access; `checkSetup()` reports an actionable error when blocked.
*
* The pure mappers (epoch conversion, typedstream decode, candidate mapping) are
* exported and unit-tested with fixtures — no real chat.db required.
*/
import { DatabaseSync } from 'node:sqlite';
import { statSync } from 'node:fs';
import { expandHome, dataPath, atomicWrite, tryReadFile, safeJSONParse } from '../lib/fileUtils.js';
import { getUserTimezone } from './userTimezone.js';
import { identityFromHandle } from '../lib/tribeMatch.js';
import { shortSummary, localDayKey } from './humanActivity.js';
import { getSettings } from './settings.js';
import { fullDiskAccessDetails } from '../lib/fullDiskAccess.js';
// ---------------------------------------------------------------------------
// Constants
// ---------------------------------------------------------------------------
// Milliseconds between the Unix epoch (1970-01-01) and the Apple/Cocoa epoch
// (2001-01-01 UTC). `message.date` is measured from the Apple epoch.
const APPLE_EPOCH_OFFSET_MS = 978307200000;
// The default location of the macOS Messages database. Overridable via env for
// tests / non-default home layouts. `expandHome` resolves the leading `~/`.
const DEFAULT_CHAT_DB = '~/Library/Messages/chat.db';
const STATE_FILE = 'imessage-sync-state.json';
// Config defaults — surfaced via getImessageConfig(). Sync is OFF by default;
// the user opts in from the iMessage Settings drawer (Comms → Messages →
// iMessage). Reading chat.db needs Full Disk Access, so we never enable it
// silently.
const DEFAULT_CONFIG = {
enabled: false,
intervalMinutes: 30,
};
// How many messages to pull per sync pass. Bounds memory + keeps a first sync on
// a huge history from blocking; the ROWID cursor means the next pass resumes.
const SCAN_LIMIT = 5000;
// ---------------------------------------------------------------------------
// Pure helpers (exported for unit tests — no DB, no filesystem, no side effects)
// ---------------------------------------------------------------------------
/**
* Convert an Apple `message.date` value to a JS Date, or `null` when unusable.
*
* Modern macOS (10.13+) stores nanoseconds since the Apple epoch; legacy stores
* whole seconds. A post-2001 nanosecond value is ~1e17+, a seconds value is
* ~1e8–1e9, so a `1e12` threshold cleanly separates the two. (Sub-millisecond
* precision is irrelevant here and Number is plenty accurate at second scale.)
*/
export function appleDateToDate(value) {
if (value == null) return null;
const n = typeof value === 'bigint' ? Number(value) : Number(value);
if (!Number.isFinite(n) || n === 0) return null;
const seconds = Math.abs(n) >= 1e12 ? n / 1e9 : n;
const ms = APPLE_EPOCH_OFFSET_MS + seconds * 1000;
const d = new Date(ms);
return Number.isNaN(d.getTime()) ? null : d;
}
/**
* Best-effort plain-text extraction from an archived `attributedBody` typedstream
* (the `streamtyped` NSAttributedString blob Messages stores when `text` is NULL).
* Returns the message string, or `null` when the blob can't be parsed — the caller
* counts a decode failure and skips the row rather than erroring the whole sync.
*
* Heuristic (matches the well-known imessage-exporter approach): locate the
* `NSString` class marker, then the length-prefixed UTF-8 payload after the `+`
* (0x2b) start-of-value marker. Lengths use typedstream's varint-ish prefix:
* a byte < 0x80 is the length; 0x81 = uint16-LE follows; 0x82 = uint32-LE follows.
*/
export function decodeAttributedBody(buffer) {
if (!buffer || !buffer.length) return null;
const buf = Buffer.isBuffer(buffer) ? buffer : Buffer.from(buffer);
const marker = buf.indexOf('NSString', 0, 'latin1');
if (marker === -1) return null;
let i = buf.indexOf(0x2b, marker); // '+' — start-of-value marker
if (i === -1) return null;
i += 1;
if (i >= buf.length) return null;
let len = buf[i];
i += 1;
if (len === 0x81) {
if (i + 1 >= buf.length) return null;
len = buf[i] | (buf[i + 1] << 8);
i += 2;
} else if (len === 0x82) {
if (i + 3 >= buf.length) return null;
len = buf[i] | (buf[i + 1] << 8) | (buf[i + 2] << 16) | (buf[i + 3] * 0x1000000);
i += 4;
} else if (len > 0x80) {
return null; // an encoding we don't handle — best-effort skip
}
if (len <= 0 || i + len > buf.length) return null;
const text = buf.toString('utf8', i, i + len).trim();
return text || null;
}
/**
* Resolve a normalized message record's body text: the plain `text` column when
* present, else a best-effort decode of `attributedBody`. Returns
* `{ text, decodeFailed }` — `decodeFailed` is true only when `text` was NULL AND
* the attributedBody blob existed but could not be parsed (so the caller can tally
* skips without treating an empty-body message as a failure).
*/
export function resolveMessageText(row) {
if (row?.text != null && String(row.text).length > 0) {
return { text: String(row.text), decodeFailed: false };
}
if (row?.attributedBody && row.attributedBody.length) {
const decoded = decodeAttributedBody(row.attributedBody);
if (decoded != null) return { text: decoded, decodeFailed: false };
return { text: '', decodeFailed: true };
}
return { text: '', decodeFailed: false };
}
/**
* Map one normalized message record to a human-activity candidate, or `null`.
* `msg`: { guid, rowid, at: Date, text, isFromMe, handle, chatGuid, chatName,
* service, participants: string[] }
* Dedupe key is `imsg:<message.guid>` (one activity event per message).
*/
export function imessageActivityCandidate(msg) {
if (!msg?.guid || !(msg.at instanceof Date) || Number.isNaN(msg.at.getTime())) return null;
const kind = msg.isFromMe ? 'message.sent' : 'message.received';
const counterpart = msg.isFromMe ? '' : (msg.handle || '');
const participants = (msg.participants || [])
.map(identityFromHandle)
.filter((p) => p && (p.email || p.phone));
return {
source: 'imessage',
accountId: null,
kind,
happenedAt: msg.at.toISOString(),
title: msg.chatName || counterpart || 'iMessage',
summary: shortSummary(msg.text || ''),
participants,
dedupeKey: `imsg:${msg.guid}`,
metadata: {
chatGuid: msg.chatGuid || null,
service: msg.service || null,
handle: counterpart || null,
rowid: msg.rowid ?? null,
// Tag Tapbacks/reactions so downstream consumers (Tribe outreach #2158) can
// exclude them — a reaction is not a message awaiting a reply. Only set when
// true to keep ordinary-message metadata unchanged.
...(Number(msg.associatedType) ? { isReaction: true } : {}),
},
};
}
export function imessageActivityCandidates(messages = []) {
return (messages || []).map(imessageActivityCandidate).filter(Boolean);
}
/**
* Group message records into Tribe touchpoint candidates — one per (chat, local
* day) so a busy thread logs a single daily touchpoint per matched person. Each
* candidate's `identities` are the chat's participant handles (classified as
* email-or-phone), matched deterministically to tracked people downstream.
* Dedupe key is `imsg:<chatGuid>:<localDay>` (mirrors the `msg:` convention).
*/
export function imessageTouchpointCandidates(messages = [], timezone) {
const byKey = new Map();
for (const msg of messages || []) {
if (!(msg?.at instanceof Date) || Number.isNaN(msg.at.getTime())) continue;
if (!msg.chatGuid) continue;
const day = localDayKey(msg.at, timezone);
if (!day) continue;
const key = `${msg.chatGuid}\u0000${day}`;
let entry = byKey.get(key);
if (!entry) {
entry = { chatGuid: msg.chatGuid, day, at: msg.at, name: msg.chatName || '', handles: new Set() };
byKey.set(key, entry);
}
for (const h of msg.participants || []) if (h) entry.handles.add(h);
if (msg.at > entry.at) entry.at = msg.at; // latest instant in the day
}
const out = [];
for (const entry of byKey.values()) {
const identities = [...entry.handles]
.map(identityFromHandle)
.filter((p) => p && (p.email || p.phone));
if (identities.length === 0) continue;
out.push({
identities,
source: 'imessage',
happenedAt: entry.at.toISOString(),
channel: 'iMessage',
summary: entry.name || 'iMessage conversation',
dedupeKey: `imsg:${entry.chatGuid}:${entry.day}`,
metadata: { chatGuid: entry.chatGuid, day: entry.day },
});
}
return out;
}
// ---------------------------------------------------------------------------
// Config + machine-local cursor state
// ---------------------------------------------------------------------------
export async function getImessageConfig() {
const settings = await getSettings().catch(() => ({}));
const c = settings?.imessage || {};
return {
enabled: typeof c.enabled === 'boolean' ? c.enabled : DEFAULT_CONFIG.enabled,
intervalMinutes: Number.isFinite(c.intervalMinutes) && c.intervalMinutes >= 1
? Math.floor(c.intervalMinutes)
: DEFAULT_CONFIG.intervalMinutes,
};
}
function stateFilePath() {
return dataPath(STATE_FILE);
}
// Machine-local incremental cursor. NOT federated — chat.db and its ROWIDs are
// per-machine, so this state must never sync to a peer.
export async function readSyncState() {
const raw = await tryReadFile(stateFilePath());
const parsed = raw ? safeJSONParse(raw, null, { allowArray: false }) : null;
return {
cursorRowid: Number.isFinite(parsed?.cursorRowid) ? parsed.cursorRowid : 0,
lastRunAt: parsed?.lastRunAt || null,
lastResult: parsed?.lastResult || null,
};
}
async function writeSyncState(state) {
await atomicWrite(stateFilePath(), JSON.stringify(state, null, 2));
}
// ---------------------------------------------------------------------------
// chat.db access (side-effecting — try/catch is sanctioned here: this runs
// outside the Express request lifecycle and a native open error must not crash
// the process). We open readOnly and never mutate Apple's database.
// ---------------------------------------------------------------------------
export function chatDbPath() {
return expandHome(process.env.IMESSAGE_CHAT_DB || DEFAULT_CHAT_DB);
}
// Classify a chat.db open failure so the UI can surface an actionable message.
// A macOS sandbox / TCC denial surfaces as EPERM/EACCES or SQLite "unable to
// open database file" (SQLITE_CANTOPEN) — but SQLITE_CANTOPEN is ALSO what a
// simply-nonexistent chat.db produces (Linux install, a Mac that never used
// Messages), and telling those users to grant Full Disk Access can't help them.
// Disambiguate via stat: a TCC denial surfaces as EPERM/EACCES on stat, while
// ENOENT means the file genuinely doesn't exist.
function isFullDiskAccessError(err) {
if (err?.code === 'EACCES' || err?.code === 'EPERM') return true;
const msg = String(err?.message || '').toLowerCase();
const ambiguous = msg.includes('unable to open')
|| msg.includes('not authorized')
|| msg.includes('operation not permitted');
if (!ambiguous) return false;
try {
statSync(chatDbPath());
return true; // file exists but SQLite couldn't open it — permission problem
} catch (statErr) {
if (statErr?.code === 'ENOENT') return false; // chat.db genuinely absent
return true; // EPERM/EACCES on stat — the TCC denial itself
}
}
/**
* Attempt to open chat.db read-only and run a trivial probe query. Returns a
* structured report — never throws — so the setup-check route can render an
* actionable Full Disk Access error when blocked.
*/
export async function checkSetup() {
const path = chatDbPath();
let db;
try {
db = new DatabaseSync(path, { readOnly: true });
const row = db.prepare('SELECT COUNT(*) AS n FROM message').get();
return { ok: true, dbPath: path, messageCount: Number(row?.n || 0) };
} catch (err) {
const fullDiskAccessRequired = isFullDiskAccessError(err);
return {
ok: false,
dbPath: path,
error: err?.message || 'Failed to open chat.db',
fullDiskAccessRequired,
...(fullDiskAccessRequired
? await fullDiskAccessDetails()
: { remediation: `Could not read ${path}: ${err?.message || 'unknown error'}` }),
};
} finally {
try { db?.close(); } catch { /* already closed / never opened */ }
}
}
// Read a batch of messages after `cursorRowid` plus the chat→participants map.
// Returns normalized message records (the shape the pure mappers consume) and the
// max ROWID seen. Kept separate from runSync so the SQL boundary is small and the
// mapping stays pure/testable.
function readMessages(db, cursorRowid, limit) {
const partRows = db.prepare(
`SELECT c.guid AS chat_guid, h.id AS handle
FROM chat c
JOIN chat_handle_join chj ON chj.chat_id = c.ROWID
JOIN handle h ON h.ROWID = chj.handle_id`,
).all();
const participantsByChat = new Map();
for (const r of partRows) {
if (!r.chat_guid || !r.handle) continue;
const list = participantsByChat.get(r.chat_guid) || [];
list.push(r.handle);
participantsByChat.set(r.chat_guid, list);
}
// `m.date` is nanoseconds since 2001 on modern macOS — a value that exceeds
// Number.MAX_SAFE_INTEGER, which node:sqlite REFUSES to return as a JS number
// (throws ERR_OUT_OF_RANGE). CAST it to TEXT so it comes back as a decimal
// string; appleDateToDate parses it (second-scale precision is all we need).
const rows = db.prepare(
`SELECT m.ROWID AS rowid, m.guid, CAST(m.date AS TEXT) AS date, m.text, m.attributedBody,
m.is_from_me AS is_from_me, m.service, m.associated_message_type AS assoc_type,
h.id AS handle,
c.guid AS chat_guid, c.display_name AS chat_name, c.chat_identifier
FROM message m
JOIN chat_message_join cmj ON cmj.message_id = m.ROWID
JOIN chat c ON c.ROWID = cmj.chat_id
LEFT JOIN handle h ON h.ROWID = m.handle_id
WHERE m.ROWID > ?
ORDER BY m.ROWID ASC
LIMIT ?`,
).all(cursorRowid, limit);
let maxRowid = cursorRowid;
let decodeFailures = 0;
const messages = [];
for (const row of rows) {
const rowid = Number(row.rowid);
if (rowid > maxRowid) maxRowid = rowid;
const at = appleDateToDate(row.date);
if (!at) continue;
const { text, decodeFailed } = resolveMessageText(row);
if (decodeFailed) decodeFailures += 1;
const chatGuid = row.chat_guid || null;
const participants = chatGuid ? (participantsByChat.get(chatGuid) || []) : [];
// Fall back to the message sender's handle when the chat has no join rows.
if (participants.length === 0 && row.handle) participants.push(row.handle);
messages.push({
guid: row.guid,
rowid,
at,
text,
isFromMe: Number(row.is_from_me) === 1,
handle: row.handle || '',
chatGuid,
chatName: row.chat_name || row.chat_identifier || '',
service: row.service || null,
// Non-zero `associated_message_type` = a Tapback/reaction/edit attached to
// another message, not a standalone message (2000-3005 reactions, etc.).
associatedType: Number(row.assoc_type) || 0,
participants,
});
}
return { messages, maxRowid, scanned: rows.length, decodeFailures };
}
/**
* Run one incremental sync pass: read new messages after the stored ROWID cursor,
* record activity events + Tribe touchpoints, and advance the cursor. Returns a
* summary. Safe to call repeatedly — dedupe keys make re-ingestion a no-op. This
* runs outside the request lifecycle (scheduler / explicit endpoint), so the
* chat.db open is try/catch-guarded and returns an error report instead of throwing.
*
* Re-entrancy guarded: a manual "Sync now" overlapping a scheduler tick would
* double-read the same cursor (deduped, but doubled work, and the slower writer
* could stamp a stale cursor over the fresher one) — so concurrent callers share
* the in-flight pass instead.
*/
let syncInFlight = null;
export async function runSync() {
if (syncInFlight) return syncInFlight;
syncInFlight = doRunSync().finally(() => { syncInFlight = null; });
return syncInFlight;
}
async function doRunSync() {
const state = await readSyncState();
const path = chatDbPath();
let db;
let batch;
try {
db = new DatabaseSync(path, { readOnly: true });
batch = readMessages(db, state.cursorRowid, SCAN_LIMIT);
} catch (err) {
const fullDiskAccessRequired = isFullDiskAccessError(err);
console.error(`❌ iMessage sync failed to open chat.db: ${err?.message || err}`);
return {
ok: false,
error: err?.message || 'Failed to open chat.db',
fullDiskAccessRequired,
...(fullDiskAccessRequired ? await fullDiskAccessDetails() : {}),
};
} finally {
try { db?.close(); } catch { /* noop */ }
}
const timezone = await getUserTimezone();
const { recordEvents } = await import('./humanActivity.js');
const tribe = await import('./tribe.js');
// Dynamic import avoids a circular static edge with imessageManage (which
// pulls getStatus from this module for the manager stats endpoint) (#2413).
const manage = await import('./imessageManage.js');
const activityCandidates = imessageActivityCandidates(batch.messages);
const touchpointCandidates = imessageTouchpointCandidates(batch.messages, timezone);
// PortOS-side blocklist: skip spam handles before they land in the activity
// store or Tribe touchpoint log. Apple's chat.db is never mutated.
const blocklist = await manage.readBlocklist().catch(() => ({ handles: [] }));
const blockedKeys = new Set(blocklist.handles || []);
const activityFiltered = manage.filterBlockedActivityCandidates(activityCandidates, blockedKeys);
const touchpointFiltered = manage.filterBlockedTouchpointCandidates(touchpointCandidates, blockedKeys);
// Persistence failures must NOT advance the cursor: the dedupe keys make
// re-processing the batch on the next pass a harmless no-op, but skipping
// past unpersisted messages loses them permanently (the cursor never revisits
// a ROWID). Track failure and hold the cursor so the batch gets retried.
let persistFailed = false;
const activityResult = await recordEvents(activityFiltered.kept).catch((err) => {
console.error(`❌ iMessage activity record failed: ${err?.message || err}`);
persistFailed = true;
return { recorded: 0, skipped: activityFiltered.kept.length };
});
const touchpointResult = await tribe.autoLogTouchpoints(touchpointFiltered.kept).catch((err) => {
console.error(`❌ iMessage touchpoint log failed: ${err?.message || err}`);
persistFailed = true;
return { created: 0, matched: 0 };
});
const nextCursor = persistFailed ? state.cursorRowid : batch.maxRowid;
const result = {
ok: !persistFailed,
...(persistFailed ? { error: 'Persistence failed — cursor held so the batch retries next sync' } : {}),
scanned: batch.scanned,
recorded: activityResult.recorded,
blockedSkipped: activityFiltered.skipped,
touchpointsCreated: touchpointResult.created,
touchpointsMatched: touchpointResult.matched,
decodeFailures: batch.decodeFailures,
cursorRowid: nextCursor,
// A full batch means older history remains beyond this pass — the caller
// (UI toast / next scheduler tick) should run again to keep draining.
hasMore: batch.scanned === SCAN_LIMIT,
};
await writeSyncState({ cursorRowid: nextCursor, lastRunAt: new Date().toISOString(), lastResult: result });
console.log(`💬 iMessage sync: scanned ${result.scanned}, recorded ${result.recorded} event(s), blocked ${result.blockedSkipped}, ${result.touchpointsCreated} touchpoint(s), ${result.decodeFailures} decode-skip(s), cursor→${result.cursorRowid}${result.hasMore ? ' (more remaining)' : ''}${persistFailed ? ' — PERSIST FAILED, cursor held' : ''}`);
return result;
}
// Status for the settings UI: config + cursor state (no chat.db open).
export async function getStatus() {
const [config, state] = await Promise.all([getImessageConfig(), readSyncState()]);
return { config, state };
}