A user question — "why am I not seeing the latest emails on this deal?" — turned out to be two things. First, a diagnosis that cleared the air: nothing was stale. Both mirrors are current (Zoho to 07-13/14, Graph email to today), and on the TruBoard Cleantech deal Jul 10 genuinely IS the newest email — confirmed by scanning the whole mirror for any TruBoard mail after it (zero). Anisha's mailbox is synced (anisha@antino.io — .io not .com; her login/CRM identity vs her mailbox differ, bridged by the mapping).
But the diagnosis surfaced the real limitation: the deal's email thread matched only its mapped Zoho contacts — here a single contact, S. Kadhirvel — while the person actually driving the thread, Sohail Usmani (@truboardpartners.com), isn't a Zoho contact at all. His mail showed only incidentally (he cc'd Kadhirvel); drop that cc and it'd vanish. The whole TruBoard account has a team in Zoho (Srickant, Vipul, Rakesh…), none attached to this deal.
The fix (#13): match a deal's emails by the company domain, not just its contacts. src/lib/graph/match.ts — a reusable matcher: corporateDomains() (derive domains from the deal + account contacts, dropping a personal/internal exclusion list — gmail/yahoo/… and antino.com/antino.io/goldenflitch.com, so we never over-match free providers or internal mail) + matchedMessageIds(emails, domains) (a UNION of four branches: from-email, from-domain, recipient-email, recipient-domain). Wired into getDealTimeline (the thread — full 4-way match) and getDealInbox (the left-pane ranking — exact-email + from-domain only). TruBoard now surfaces 10 of Sohail's emails directly, plus the rest of the account's conversation.
The performance was the hard part (the whole reason it's worth logging): the natural shapes are traps. A top-level OR … EXISTS predicate → 8,305 ms (seq-scans all 170k messages). IN (select … from cte) → 1,078 ms (a CTE defeats the functional index — Postgres hash-semi-joins with a full seq scan; confirmed via EXPLAIN). The fix: pass emails/domains as array parameters and use = any($arr) in a UNION of index-backed branches — 29 ms. Added one index (idx_gmr_domain_lower on lower(split_part(email,'@',2)) for the recipient side) to scripts/perf/add-indexes.mjs. The inbox ranking stayed lean (exact-email + from-domain, recipient-domain dropped — it cost +480ms for barely any ranking change): 162 ms → 54 ms. Lesson banked: never match email sets against a CTE with `IN`/`EXISTS`; materialize to an array and `= any` the functional index. Second gotcha (caught in the browser after the first commit): ` sql${jsArray}::text[] renders as a **row constructor** ($1,$2,…)::text[] — a cast error — because drizzle expands a JS array into a paren-list. Build the literal explicitly: sqlarray[${sql.join(vals.map(v => sql${v}), sql, )}]::text[] → array[$1,$2,…]::text[]. Verified via PgDialect().sqlToQuery + a live getDealTimeline run, not just a raw-pg` scratch (which had hidden the bug — raw pg accepts a single array param, drizzle doesn't render one).