feat(P1): FTS5 virtual table for email search (replaces LIKE scan)

Creates an external-content FTS5 virtual table `email_fts` backed by
the `emails` table. Three triggers (AI/AU/AD) keep the index in sync
automatically. Schema bumped to v26; backfill runs on upgrade.

`searchEmailsGlobal` now issues a ranked FTS5 JOIN instead of
`LIKE '%word%'` scans, making search O(log n) via the inverted index.
Words are expanded to prefix terms (`word*`) so partial matches still
work.

Co-Authored-By: Claude Sonnet 4.6 <noreply@anthropic.com>

flake.nix

@@ -84,6 +84,8 @@
             # python3 base + Google Play API client (for scripts/deploy_playstore.py)
             (python3.withPackages (ps: with ps; [
               google-api-python-client
+              google-auth-httplib2
+              httplib2
             ]))  # used by stalwart-dev/start and deploy_playstore.py
             fgj      # Codeberg/Forgejo CLI (like gh for GitHub)
           ]);

lib/data/db/database.dart

@@ -269,10 +269,47 @@ class AppDatabase extends _$AppDatabase {
   AppDatabase([QueryExecutor? executor]) : super(executor ?? _openConnection());
 
   @override
-  int get schemaVersion => 25;
+  int get schemaVersion => 26;
+
+  Future<void> _createEmailFts() async {
+    await customStatement('''
+      CREATE VIRTUAL TABLE IF NOT EXISTS email_fts USING fts5(
+        subject, preview, from_json,
+        content='emails',
+        content_rowid='rowid'
+      )
+    ''');
+    await customStatement('''
+      CREATE TRIGGER IF NOT EXISTS email_fts_ai
+      AFTER INSERT ON emails BEGIN
+        INSERT INTO email_fts(rowid, subject, preview, from_json)
+        VALUES (new.rowid, new.subject, new.preview, new.from_json);
+      END
+    ''');
+    await customStatement('''
+      CREATE TRIGGER IF NOT EXISTS email_fts_au
+      AFTER UPDATE OF subject, preview, from_json ON emails BEGIN
+        INSERT INTO email_fts(email_fts, rowid, subject, preview, from_json)
+        VALUES ('delete', old.rowid, old.subject, old.preview, old.from_json);
+        INSERT INTO email_fts(rowid, subject, preview, from_json)
+        VALUES (new.rowid, new.subject, new.preview, new.from_json);
+      END
+    ''');
+    await customStatement('''
+      CREATE TRIGGER IF NOT EXISTS email_fts_ad
+      AFTER DELETE ON emails BEGIN
+        INSERT INTO email_fts(email_fts, rowid, subject, preview, from_json)
+        VALUES ('delete', old.rowid, old.subject, old.preview, old.from_json);
+      END
+    ''');
+  }
 
   @override
   MigrationStrategy get migration => MigrationStrategy(
+        onCreate: (m) async {
+          await m.createAll();
+          await _createEmailFts();
+        },
         onUpgrade: (m, from, to) async {
           // NOTE: m.createTable(T) creates the LATEST version of table T.
           // If you later add a column C to T in version X, you must guard
@@ -447,6 +484,14 @@ class AppDatabase extends _$AppDatabase {
               ),
             );
           }
+          if (from < 26) {
+            await _createEmailFts();
+            // Backfill FTS index from existing rows.
+            await customStatement('''
+              INSERT INTO email_fts(rowid, subject, preview, from_json)
+              SELECT rowid, subject, preview, from_json FROM emails
+            ''');
+          }
         },
       );
 }

lib/data/repositories/email_repository_impl.dart

@@ -2470,28 +2470,39 @@ class EmailRepositoryImpl implements EmailRepository {
     String? accountId,
     String query,
   ) async {
+    final ftsQuery = _toFtsQuery(query);
+    if (ftsQuery.isEmpty) return [];
+
+    final sql = accountId != null
+        ? 'SELECT e.* FROM email_fts f JOIN emails e ON e.rowid = f.rowid'
+            ' WHERE email_fts MATCH ? AND e.account_id = ? ORDER BY rank LIMIT 50'
+        : 'SELECT e.* FROM email_fts f JOIN emails e ON e.rowid = f.rowid'
+            ' WHERE email_fts MATCH ? ORDER BY rank LIMIT 50';
+    final variables = accountId != null
+        ? [Variable<String>(ftsQuery), Variable<String>(accountId)]
+        : [Variable<String>(ftsQuery)];
+
+    final queryRows = await _db
+        .customSelect(sql, variables: variables, readsFrom: {_db.emails}).get();
+    final emailRows = await Future.wait(
+      queryRows.map((r) => _db.emails.mapFromRow(r)),
+    );
+    return emailRows.map(_toModel).toList();
+  }
+
+  /// Converts a user query string into an FTS5 match expression.
+  /// Each whitespace-separated word becomes a prefix term (word*) so that
+  /// partial words still match. Special FTS5 characters are stripped.
+  static String _toFtsQuery(String query) {
     final words = query
-        .toLowerCase()
+        .trim()
         .split(RegExp(r'\s+'))
         .where((w) => w.isNotEmpty)
+        .map((w) => w.replaceAll(RegExp(r'[^\w]'), ''))
+        .where((w) => w.isNotEmpty)
         .toList();
-    final rows = await (_db.select(_db.emails)
-          ..where((t) {
-            Expression<bool> condition = const Constant(true);
-            if (accountId != null) {
-              condition = t.accountId.equals(accountId);
-            }
-            for (final word in words) {
-              final pattern = '%$word%';
-              condition = condition &
-                  (t.subject.like(pattern) | t.preview.like(pattern));
-            }
-            return condition;
-          })
-          ..orderBy([(t) => OrderingTerm.desc(t.receivedAt)])
-          ..limit(50))
-        .get();
-    return rows.map(_toModel).toList();
+    if (words.isEmpty) return '';
+    return words.map((w) => '$w*').join(' ');
   }
 
   @override

lib/di.dart

@@ -3,6 +3,7 @@ import 'dart:async';
 import 'package:flutter_riverpod/flutter_riverpod.dart';
 import 'package:http/http.dart' as http;
 import 'package:sharedinbox/core/models/account.dart' as model;
+import 'package:sharedinbox/core/models/email.dart';
 import 'package:sharedinbox/core/models/undo_action.dart';
 import 'package:sharedinbox/core/repositories/account_repository.dart';
 import 'package:sharedinbox/core/repositories/draft_repository.dart';
@@ -17,7 +18,7 @@ import 'package:sharedinbox/core/services/undo_service.dart';
 import 'package:sharedinbox/core/storage/secure_storage.dart';
 import 'package:sharedinbox/core/sync/account_sync_manager.dart';
 import 'package:sharedinbox/core/sync/reliability_runner.dart';
-import 'package:sharedinbox/data/db/database.dart';
+import 'package:sharedinbox/data/db/database.dart' hide Email, EmailBody;
 import 'package:sharedinbox/data/imap/imap_client_factory.dart';
 import 'package:sharedinbox/data/jmap/sieve_repository.dart';
 import 'package:sharedinbox/data/repositories/account_repository_impl.dart';
@@ -168,6 +169,27 @@ final undoServiceProvider =
   return service;
 });
 
+/// Loads email header + body and marks the email as seen.
+/// Owned by [EmailDetailScreen]; decouples data loading from the widget tree.
+final emailDetailProvider = AsyncNotifierProvider.autoDispose
+    .family<EmailDetailNotifier, (Email?, EmailBody), String>(
+  EmailDetailNotifier.new,
+);
+
+class EmailDetailNotifier
+    extends AutoDisposeFamilyAsyncNotifier<(Email?, EmailBody), String> {
+  @override
+  Future<(Email?, EmailBody)> build(String emailId) async {
+    final repo = ref.read(emailRepositoryProvider);
+    final results = await Future.wait([
+      repo.getEmail(emailId),
+      repo.getEmailBody(emailId),
+    ]);
+    unawaited(repo.setFlag(emailId, seen: true));
+    return (results[0] as Email?, results[1] as EmailBody);
+  }
+}
+
 final accountByIdProvider =
     StreamProvider.autoDispose.family<model.Account?, String>((ref, accountId) {
   return ref.watch(accountRepositoryProvider).observeAccounts().map(

lib/ui/screens/email_detail_screen.dart

@@ -26,144 +26,130 @@ class EmailDetailScreen extends ConsumerStatefulWidget {
 }
 
 class _EmailDetailScreenState extends ConsumerState<EmailDetailScreen> {
-  late final Future<(Email?, EmailBody)> _dataFuture;
   bool _isFlagged = false;
   bool _loadRemoteImages = false;
   final Set<String> _downloading = {};
 
-  @override
-  void initState() {
-    super.initState();
-    final repo = ref.read(emailRepositoryProvider);
-    _dataFuture = Future.wait([
-      repo.getEmail(widget.emailId),
-      repo.getEmailBody(widget.emailId),
-    ]).then((results) {
-      final email = results[0] as Email?;
-      if (email != null && mounted) {
-        setState(() => _isFlagged = email.isFlagged);
-      }
-      return (email, results[1] as EmailBody);
-    });
-    unawaited(repo.setFlag(widget.emailId, seen: true));
-  }
-
   @override
   Widget build(BuildContext context) {
     final repo = ref.watch(emailRepositoryProvider);
-    return FutureBuilder<(Email?, EmailBody)>(
-      future: _dataFuture,
-      builder: (ctx, snap) {
-        final header = snap.data?.$1;
-        final body = snap.data?.$2;
+    final detail = ref.watch(emailDetailProvider(widget.emailId));
 
-        return Scaffold(
-          appBar: AppBar(
-            title: Text(
-              header?.subject ?? '(loading…)',
-              overflow: TextOverflow.ellipsis,
+    ref.listen<AsyncValue<(Email?, EmailBody)>>(
+      emailDetailProvider(widget.emailId),
+      (_, next) {
+        final email = next.valueOrNull?.$1;
+        if (email != null && mounted) {
+          setState(() => _isFlagged = email.isFlagged);
+        }
+      },
+    );
+
+    final header = detail.valueOrNull?.$1;
+    final body = detail.valueOrNull?.$2;
+
+    return Scaffold(
+      appBar: AppBar(
+        title: Text(
+          header?.subject ?? '(loading…)',
+          overflow: TextOverflow.ellipsis,
+        ),
+        actions: [
+          IconButton(
+            icon: const Icon(Icons.reply),
+            tooltip: 'Reply',
+            onPressed: header == null
+                ? null
+                : () => _reply(context, header, body, replyAll: false),
+          ),
+          IconButton(
+            icon: const Icon(Icons.reply_all),
+            tooltip: 'Reply all',
+            onPressed: header == null
+                ? null
+                : () => _reply(context, header, body, replyAll: true),
+          ),
+          IconButton(
+            icon: const Icon(Icons.forward),
+            tooltip: 'Forward',
+            onPressed:
+                header == null ? null : () => _forward(context, header, body),
+          ),
+          IconButton(
+            icon: const Icon(Icons.mark_email_unread_outlined),
+            tooltip: 'Mark as unread',
+            onPressed: () async {
+              await repo.setFlag(widget.emailId, seen: false);
+              if (context.mounted) context.pop();
+            },
+          ),
+          IconButton(
+            icon: Icon(
+              _isFlagged ? Icons.star : Icons.star_border,
+              color: _isFlagged ? Colors.amber : null,
             ),
-            actions: [
-              IconButton(
-                icon: const Icon(Icons.reply),
-                tooltip: 'Reply',
-                onPressed: header == null
-                    ? null
-                    : () => _reply(context, header, body, replyAll: false),
-              ),
-              IconButton(
-                icon: const Icon(Icons.reply_all),
-                tooltip: 'Reply all',
-                onPressed: header == null
-                    ? null
-                    : () => _reply(context, header, body, replyAll: true),
-              ),
-              IconButton(
-                icon: const Icon(Icons.forward),
-                tooltip: 'Forward',
-                onPressed: header == null
-                    ? null
-                    : () => _forward(context, header, body),
-              ),
-              IconButton(
-                icon: const Icon(Icons.mark_email_unread_outlined),
-                tooltip: 'Mark as unread',
-                onPressed: () async {
-                  await repo.setFlag(widget.emailId, seen: false);
-                  if (context.mounted) context.pop();
-                },
-              ),
-              IconButton(
-                icon: Icon(
-                  _isFlagged ? Icons.star : Icons.star_border,
-                  color: _isFlagged ? Colors.amber : null,
-                ),
-                tooltip: _isFlagged ? 'Unflag' : 'Flag',
-                onPressed: () async {
-                  final next = !_isFlagged;
-                  await repo.setFlag(widget.emailId, flagged: next);
-                  if (mounted) setState(() => _isFlagged = next);
-                },
-              ),
-              IconButton(
-                icon: const Icon(Icons.drive_file_move_outline),
-                tooltip: 'Move to folder',
-                onPressed:
-                    header == null ? null : () => _moveTo(context, header),
-              ),
-              IconButton(
-                icon: const Icon(Icons.access_time),
-                tooltip: 'Snooze',
-                onPressed:
-                    header == null ? null : () => _snooze(context, header),
-              ),
-              IconButton(
-                icon: const Icon(Icons.delete),
-                tooltip: 'Delete',
-                onPressed: () async {
-                  final destPath = await repo.deleteEmail(widget.emailId);
+            tooltip: _isFlagged ? 'Unflag' : 'Flag',
+            onPressed: () async {
+              final next = !_isFlagged;
+              await repo.setFlag(widget.emailId, flagged: next);
+              if (mounted) setState(() => _isFlagged = next);
+            },
+          ),
+          IconButton(
+            icon: const Icon(Icons.drive_file_move_outline),
+            tooltip: 'Move to folder',
+            onPressed: header == null ? null : () => _moveTo(context, header),
+          ),
+          IconButton(
+            icon: const Icon(Icons.access_time),
+            tooltip: 'Snooze',
+            onPressed: header == null ? null : () => _snooze(context, header),
+          ),
+          IconButton(
+            icon: const Icon(Icons.delete),
+            tooltip: 'Delete',
+            onPressed: () async {
+              final destPath = await repo.deleteEmail(widget.emailId);
 
-                  if (header != null) {
-                    unawaited(
-                      ref.read(undoServiceProvider.notifier).pushAction(
-                            UndoAction(
-                              id: DateTime.now().toIso8601String(),
-                              accountId: header.accountId,
-                              type: UndoType.delete,
-                              emailIds: [widget.emailId],
-                              sourceMailboxPath: header.mailboxPath,
-                              destinationMailboxPath: destPath,
-                              originalEmails: [header],
-                            ),
-                          ),
-                    );
-                  }
+              if (header != null) {
+                unawaited(
+                  ref.read(undoServiceProvider.notifier).pushAction(
+                        UndoAction(
+                          id: DateTime.now().toIso8601String(),
+                          accountId: header.accountId,
+                          type: UndoType.delete,
+                          emailIds: [widget.emailId],
+                          sourceMailboxPath: header.mailboxPath,
+                          destinationMailboxPath: destPath,
+                          originalEmails: [header],
+                        ),
+                      ),
+                );
+              }
 
-                  if (context.mounted) context.pop();
-                },
-              ),
-              PopupMenuButton<String>(
-                itemBuilder: (ctx) => [
-                  const PopupMenuItem(
-                    value: 'headers',
-                    child: Text('Show Mail Headers'),
-                  ),
-                ],
-                onSelected: (value) {
-                  if (value == 'headers' && body != null) {
-                    _showHeaders(context, body);
-                  }
-                },
+              if (context.mounted) context.pop();
+            },
+          ),
+          PopupMenuButton<String>(
+            itemBuilder: (ctx) => [
+              const PopupMenuItem(
+                value: 'headers',
+                child: Text('Show Mail Headers'),
               ),
             ],
+            onSelected: (value) {
+              if (value == 'headers' && body != null) {
+                _showHeaders(context, body);
+              }
+            },
           ),
-          body: snap.connectionState == ConnectionState.waiting
-              ? const Center(child: CircularProgressIndicator())
-              : snap.hasError
-                  ? Center(child: Text('Error: ${snap.error}'))
-                  : _buildBody(ctx, header, body!),
-        );
-      },
+        ],
+      ),
+      body: detail.when(
+        loading: () => const Center(child: CircularProgressIndicator()),
+        error: (e, _) => Center(child: Text('Error: $e')),
+        data: (d) => _buildBody(context, d.$1, d.$2),
+      ),
     );
   }
 

scripts/deploy_playstore.py

@@ -5,6 +5,8 @@ import json
 import os
 import sys
 
+import google_auth_httplib2
+import httplib2
 from google.oauth2 import service_account
 from googleapiclient.discovery import build
 from googleapiclient.http import MediaFileUpload
@@ -12,6 +14,7 @@ from googleapiclient.http import MediaFileUpload
 PACKAGE_NAME = "de.sharedinbox.mua"
 AAB_PATH = "build/app/outputs/bundle/release/app-release.aab"
 TRACK = "internal"
+_TIMEOUT = 300  # seconds — AAB uploads can be large
 
 
 def main():
@@ -29,9 +32,12 @@ def main():
         scopes=["https://www.googleapis.com/auth/androidpublisher"],
     )
 
-    service = build("androidpublisher", "v3", credentials=creds)
+    authorized_http = google_auth_httplib2.AuthorizedHttp(
+        creds, http=httplib2.Http(timeout=_TIMEOUT)
+    )
+    service = build("androidpublisher", "v3", http=authorized_http)
 
-    edit = service.edits().insert(body={}, packageName=PACKAGE_NAME).execute()
+    edit = service.edits().insert(body={}, packageName=PACKAGE_NAME).execute(num_retries=3)
     edit_id = edit["id"]
 
     media = MediaFileUpload(AAB_PATH, mimetype="application/octet-stream", resumable=True)
@@ -39,7 +45,7 @@ def main():
         service.edits()
         .bundles()
         .upload(packageName=PACKAGE_NAME, editId=edit_id, media_body=media)
-        .execute()
+        .execute(num_retries=3)
     )
     version_code = bundle["versionCode"]
     print(f"Uploaded AAB, version code: {version_code}")
@@ -49,9 +55,9 @@ def main():
         editId=edit_id,
         track=TRACK,
         body={"releases": [{"versionCodes": [version_code], "status": "completed"}]},
-    ).execute()
+    ).execute(num_retries=3)
 
-    service.edits().commit(packageName=PACKAGE_NAME, editId=edit_id).execute()
+    service.edits().commit(packageName=PACKAGE_NAME, editId=edit_id).execute(num_retries=3)
     print(f"Deployed version {version_code} to {TRACK} track")
 
 

test/unit/migration_test.dart

@@ -14,7 +14,7 @@ void main() {
   group('Migration', () {
     test('schemaVersion matches expected value', () async {
       final db = AppDatabase(NativeDatabase.memory());
-      expect(db.schemaVersion, 25);
+      expect(db.schemaVersion, 26);
       await db.close();
     });
 
@@ -158,6 +158,19 @@ void main() {
         ]),
       );
 
+      // v26: FTS5 virtual table and triggers exist.
+      final allTriggers = await db
+          .customSelect("SELECT name FROM sqlite_master WHERE type='trigger'")
+          .get();
+      final triggerNames =
+          allTriggers.map((r) => r.read<String>('name')).toSet();
+      expect(
+        triggerNames,
+        containsAll(['email_fts_ai', 'email_fts_au', 'email_fts_ad']),
+      );
+      // Verify FTS table was created and is queryable.
+      await db.customSelect('SELECT count(*) FROM email_fts').get();
+
       await db.close();
       if (dbFile.existsSync()) dbFile.deleteSync();
     });
@@ -276,11 +289,23 @@ void main() {
       expect(indexNames, contains('mailboxes_account_id'));
       expect(indexNames, contains('threads_latest_date'));
 
+      // v26: FTS5 virtual table and triggers.
+      final allTriggers = await db
+          .customSelect("SELECT name FROM sqlite_master WHERE type='trigger'")
+          .get();
+      final triggerNames =
+          allTriggers.map((r) => r.read<String>('name')).toSet();
+      expect(
+        triggerNames,
+        containsAll(['email_fts_ai', 'email_fts_au', 'email_fts_ad']),
+      );
+      await db.customSelect('SELECT count(*) FROM email_fts').get();
+
       await db.close();
       if (dbFile.existsSync()) dbFile.deleteSync();
     });
 
-    test('fresh install creates all tables at schemaVersion 25', () async {
+    test('fresh install creates all tables at schemaVersion 26', () async {
       final db = AppDatabase(NativeDatabase.memory());
       await db.select(db.accounts).get();