suggestTribeImports loads 2,000 full event rows with JSONB to compute handle frequency in Node memory

[atomantic]

Problem

In server/services/tribeContacts.js:116-181, suggestTribeImports({ limit = 50 }) identifies frequent iMessage contacts not yet tracked in Tribe and ranks them by handle frequency.

To compute handle frequency, line 122 calls:

  const events = await humanActivity.listEvents({ source: 'imessage', limit: 2000 });

This executes:

SELECT * FROM human_activity_events
WHERE source = 'imessage'
ORDER BY happened_at DESC
LIMIT 2000

This loads up to 2,000 full event records from PostgreSQL across the wire, with node-pg parsing both participants JSONB and metadata JSONB for every single row.

Then lines 123-133 loop through all 2,000 events:

  const handleCounts = new Map();
  for (const ev of events) {
    const h = ev.metadata?.handle;
    if (!h) continue;
    const key = normalizePhone(h) || normalizeIdentifier(h);
    if (!key) continue;
    // Skip handles already in Tribe.
    const id = identityFromHandle(h);
    if (matchPerson(id, tribeIndex)) continue;
    handleCounts.set(key, (handleCounts.get(key) || 0) + 1);
  }

Every column except ev.metadata?.handle (title, summary, url, duration_s, participants, dedupe_key, created_at, happened_at, kind) is completely unused and discarded.

Trigger

• User visits the Contacts tab in Comms / Messages (client/src/components/messages/ContactsTab.jsx:75), which invokes api.suggestTribeFromContacts({ limit: 30 }).
• GET /api/contacts/suggest-tribe (server/routes/contacts.js:25-29) calls tribeContacts.suggestTribeImports(q).

Impact

Transferring and allocating 2,000 full rows with complex JSONB payloads to calculate frequency of a single metadata string wastes database bandwidth and causes unnecessary Node.js heap allocations and JSON deserialization overhead. On an install with 2,000 iMessage events, this costs significant latency for an operation that should only retrieve ~50-100 distinct candidate handles.

Fix

1. Add a dedicated aggregate query helper to server/services/humanActivity.js:
   getRecentHandleCounts({ source, limit = 2000, maxHandles = 200 })

   WITH recent AS (
     SELECT metadata->>'handle' AS handle
     FROM human_activity_events
     WHERE source = $1 AND metadata->>'handle' IS NOT NULL AND metadata->>'handle' <> ''
     ORDER BY happened_at DESC
     LIMIT $2
   )
   SELECT handle, COUNT(*)::int AS count
   FROM recent
   GROUP BY handle
   ORDER BY count DESC
   LIMIT $3

   This lets PostgreSQL do the grouping and counting, returning only a small list of { handle, count } rows with zero JSONB parsing in Node.
2. In server/services/tribeContacts.js:116-157, call getRecentHandleCounts instead of listEvents({ source: 'imessage', limit: 2000 }), and iterate over the aggregated handle entries to match against Tribe and Contacts.
3. Rejected alternative: Streaming cursor over listEvents — rejected because PostgreSQL can group and count 2,000 recent rows in ~2ms, eliminating the data transfer entirely.

Files: server/services/humanActivity.js, server/services/tribeContacts.js, server/services/tribeContacts.test.js, server/services/humanActivity.test.js.

Tests:

• Add unit test for getRecentHandleCounts in server/services/humanActivity.test.js verifying handle frequency aggregation and sorting.
• Update server/services/tribeContacts.test.js to assert suggestTribeImports produces the same ranked suggestions using the aggregated helper.
• cd server && npm test green.

Acceptance criteria

• suggestTribeImports does not load 2,000 full event records via listEvents.
• Handle aggregation is performed in PostgreSQL via getRecentHandleCounts, returning only distinct handles and occurrence counts.
• Suggestion ordering, counts, and reasons match existing behavior.
• cd server && npm test green.