Background
A full forensic audit of the prod beach-balling (main-process freezes) on build 0.15.0-beta.3 (= d42f50e, current main) identified five verified causes. All are synchronous better-sqlite3 work on the Electron main process against a 1.9 GB exo.db whose emails.body column holds 1.54 GB of base64-inlined images (avg 106 KB/row, max 29.4 MB).
The two biggest causes are being fixed in the primary PR (strip data URIs on the write path + one-time backfill/VACUUM migration + covering index). This issue tracks the remaining three, which stay pathological even after that PR: they are O(full-table) scans or unbounded payloads whose cost will grow back as the DB grows.
Every number below was measured against the real prod DB (read-only) and prod logs, and each mechanism was verified to exist in the shipped binary.
1. Kill the pathological queries
1a. Style-profiler SENT scans (worst freeze in prod: 67–237 s, with force-quits)
Each draft generation runs 2–6 sequential full-table scans shaped like label_ids LIKE '%"SENT"%' from buildStyleContext / computeCorrespondentProfile / selectExamples:
- Call chain:
src/main/services/style-profiler.ts:107,146,270,295,322,341 → the five SENT-scan functions in src/main/db/index.ts:1586-1710 (getRecentSentEmailsWithBody, getSentEmailsToRecipient, getSentEmailCountToRecipient, getSentEmailsToSameDomain, formality-range variants). Callers: src/main/services/draft-pipeline.ts:67, src/main/agents/agent-coordinator.ts:196.
- Measured per-scan on prod DB (semi-warm cache): 0.96–3.8 s. The formality-range join did not finish in 120 s (timed out).
- Prod logs (2026-07-16) show main-process freezes of 67 s, 178 s, 237 s, 195 s, 198 s during draft/style activity; the first two were followed by an app relaunch, i.e. the user force-quit the beach ball.
- Frequency: 10–89 auto-drafts/day, up to
MAX_CONCURRENT_AGENT_DRAFTS=3 running in bursts (3 drafts started within 9 ms in logs).
Fix directions: replace the LIKE '%"SENT"%' scans with an indexed lookup (normalized email_labels(email_id, label) table, or a generated/indexed is_sent column); rewrite or delete the formality-range join; cache the style context per account instead of recomputing per draft (it changes only when new mail is sent).
1b. Search LIKE fallback fires on every zero-result keystroke
searchEmails() (src/main/db/index.ts:2696) falls back to a LIKE full scan of the emails table when FTS5 throws or when FTS returns 0 rows (rows.length === 0 at :2769; fallback SQL at :2772-2799).
- Search-as-you-type debounces at 150 ms, and mid-word prefixes (
schedu, quarterl, invoi) legitimately return 0 FTS rows → the silent fallback runs while the user is typing.
- Measured: 0.83 s warm / 2.3–7.4 s cold per fallback. Prod logs: 72 logged
[DB] FTS5 search error, falling back to LIKE occurrences in 8.4 days with gap-to-next-log-line p50 = 1.65 s, max = 7.8 s. The zero-result trigger is unlogged, so the true rate is strictly higher.
Fix directions: use FTS5 prefix queries (term*) so mid-word searches match without a fallback; drop the automatic zero-result fallback entirely (or make it explicit/opt-in and bounded, e.g. LIMIT + subject/from only, never body_text); log the zero-result fallback path if kept.
2. Startup FTS5 integrity check + unbounded thread payloads
2a. SELECT COUNT(*) FROM emails_fts walks the entire emails table on every launch
initFTS5 (src/main/db/index.ts:110-116, reached from initDatabase at :66, which runs at module scope — before the window even exists) compares COUNT(*) FROM emails_fts to COUNT(*) FROM emails on every launch. emails_fts is an external-content FTS5 table, so counting it walks the full 1.6 GB emails B-tree (410,695 pages).
- Measured: 6.2 s (cold page cache) vs 4.8 ms for
COUNT(*) FROM emails. A/B against the prod binary: DB-open gap 122 ms with an empty DB vs 5,456 ms with a prod-DB clone.
- Prod logs: launch-time gaps of 1.8–7.8 s (median ≈ 5.4 s) on all 12 logged launches.
Fix directions: count the shadow table directly (SELECT COUNT(*) FROM emails_fts_docsize — same answer, no content-table walk), or persist a "FTS healthy as of schema version X" flag and only re-check after a migration/crash, or move the check off the critical launch path.
2b. emails:get-thread returns full bodies for the entire merged thread
getEmailsByThread (src/main/db/index.ts:582) selects e.body for every message in the merged thread with no size cap, and the handler fires twice per thread open (immediate + background refresh re-query).
- Prod DB today: thread
19d7874de9ce6f40 = 40 messages / 69 MB of body, 4 of them in INBOX (user-reachable by a normal click) → ~0.7 s query + serialize per fire, ~1.4 s per open. Thread 19cd419e30f56794 = 50 messages / 313 MB (search-reachable) → ~4.8 s and possible renderer OOM. 22 threads exceed 10 MB.
- After the data-URI strip PR these shrink dramatically (the 29 MB email strips to 9.4 KB), but the path remains unbounded — one future pathological thread reintroduces the freeze.
Fix directions: cap per-message body size at the query/IPC boundary (strip data URIs before returning, truncate with a "load full message" escape hatch); load bodies lazily per expanded message instead of for the whole thread; drop the second synchronous re-query.
3. Structural: move better-sqlite3 into a worker thread
Every cause above (and the two fixed in the primary PR) shares one enabling defect: all DB access runs synchronously on the Electron main process, so any slow query — known or not-yet-written — freezes the entire app (window events, IPC, timers all stop; prod logs show total main-process silence during the freeze windows).
Fix direction: host the better-sqlite3 connection in a worker_threads worker and expose an async request/response API to the main process (the codebase already uses thread-stream for pino logging, so the pattern exists). This converts every future slow query from "app beach-balls" into "one feature is slow", and makes the remaining items above latency bugs rather than availability bugs.
This is the largest item — it touches every db.* call site in src/main — and is worth doing last, after items 1–2 shrink the worst offenders.
Verification notes
- Prod data dir (
~/Library/Application Support/exo/) is read-only for all testing — use sqlite3 -readonly, or copy the DB to a project-local dir to test migrations/timings.
- Re-measure all numbers after the body-strip PR lands; magnitudes for 1a/1b/2a/2b will drop, but the asymptotic shapes (full scans, unbounded payloads) are unchanged.
Background
A full forensic audit of the prod beach-balling (main-process freezes) on build
0.15.0-beta.3(=d42f50e, currentmain) identified five verified causes. All are synchronous better-sqlite3 work on the Electron main process against a 1.9 GBexo.dbwhoseemails.bodycolumn holds 1.54 GB of base64-inlined images (avg 106 KB/row, max 29.4 MB).The two biggest causes are being fixed in the primary PR (strip data URIs on the write path + one-time backfill/VACUUM migration + covering index). This issue tracks the remaining three, which stay pathological even after that PR: they are O(full-table) scans or unbounded payloads whose cost will grow back as the DB grows.
Every number below was measured against the real prod DB (read-only) and prod logs, and each mechanism was verified to exist in the shipped binary.
1. Kill the pathological queries
1a. Style-profiler SENT scans (worst freeze in prod: 67–237 s, with force-quits)
Each draft generation runs 2–6 sequential full-table scans shaped like
label_ids LIKE '%"SENT"%'frombuildStyleContext/computeCorrespondentProfile/selectExamples:src/main/services/style-profiler.ts:107,146,270,295,322,341→ the five SENT-scan functions insrc/main/db/index.ts:1586-1710(getRecentSentEmailsWithBody,getSentEmailsToRecipient,getSentEmailCountToRecipient,getSentEmailsToSameDomain, formality-range variants). Callers:src/main/services/draft-pipeline.ts:67,src/main/agents/agent-coordinator.ts:196.MAX_CONCURRENT_AGENT_DRAFTS=3running in bursts (3 drafts started within 9 ms in logs).Fix directions: replace the
LIKE '%"SENT"%'scans with an indexed lookup (normalizedemail_labels(email_id, label)table, or a generated/indexedis_sentcolumn); rewrite or delete the formality-range join; cache the style context per account instead of recomputing per draft (it changes only when new mail is sent).1b. Search LIKE fallback fires on every zero-result keystroke
searchEmails()(src/main/db/index.ts:2696) falls back to aLIKEfull scan of the emails table when FTS5 throws or when FTS returns 0 rows (rows.length === 0at:2769; fallback SQL at:2772-2799).schedu,quarterl,invoi) legitimately return 0 FTS rows → the silent fallback runs while the user is typing.[DB] FTS5 search error, falling back to LIKEoccurrences in 8.4 days with gap-to-next-log-line p50 = 1.65 s, max = 7.8 s. The zero-result trigger is unlogged, so the true rate is strictly higher.Fix directions: use FTS5 prefix queries (
term*) so mid-word searches match without a fallback; drop the automatic zero-result fallback entirely (or make it explicit/opt-in and bounded, e.g.LIMIT+ subject/from only, neverbody_text); log the zero-result fallback path if kept.2. Startup FTS5 integrity check + unbounded thread payloads
2a.
SELECT COUNT(*) FROM emails_ftswalks the entire emails table on every launchinitFTS5(src/main/db/index.ts:110-116, reached frominitDatabaseat:66, which runs at module scope — before the window even exists) comparesCOUNT(*) FROM emails_ftstoCOUNT(*) FROM emailson every launch.emails_ftsis an external-content FTS5 table, so counting it walks the full 1.6 GB emails B-tree (410,695 pages).COUNT(*) FROM emails. A/B against the prod binary: DB-open gap 122 ms with an empty DB vs 5,456 ms with a prod-DB clone.Fix directions: count the shadow table directly (
SELECT COUNT(*) FROM emails_fts_docsize— same answer, no content-table walk), or persist a "FTS healthy as of schema version X" flag and only re-check after a migration/crash, or move the check off the critical launch path.2b.
emails:get-threadreturns full bodies for the entire merged threadgetEmailsByThread(src/main/db/index.ts:582) selectse.bodyfor every message in the merged thread with no size cap, and the handler fires twice per thread open (immediate + background refresh re-query).19d7874de9ce6f40= 40 messages / 69 MB of body, 4 of them in INBOX (user-reachable by a normal click) → ~0.7 s query + serialize per fire, ~1.4 s per open. Thread19cd419e30f56794= 50 messages / 313 MB (search-reachable) → ~4.8 s and possible renderer OOM. 22 threads exceed 10 MB.Fix directions: cap per-message body size at the query/IPC boundary (strip data URIs before returning, truncate with a "load full message" escape hatch); load bodies lazily per expanded message instead of for the whole thread; drop the second synchronous re-query.
3. Structural: move better-sqlite3 into a worker thread
Every cause above (and the two fixed in the primary PR) shares one enabling defect: all DB access runs synchronously on the Electron main process, so any slow query — known or not-yet-written — freezes the entire app (window events, IPC, timers all stop; prod logs show total main-process silence during the freeze windows).
Fix direction: host the better-sqlite3 connection in a
worker_threadsworker and expose an async request/response API to the main process (the codebase already uses thread-stream for pino logging, so the pattern exists). This converts every future slow query from "app beach-balls" into "one feature is slow", and makes the remaining items above latency bugs rather than availability bugs.This is the largest item — it touches every
db.*call site insrc/main— and is worth doing last, after items 1–2 shrink the worst offenders.Verification notes
~/Library/Application Support/exo/) is read-only for all testing — usesqlite3 -readonly, or copy the DB to a project-local dir to test migrations/timings.