Concentrate local mailbox persistence in one deep module

[ymcheung]

Problem Statement

Local mailbox persistence knowledge is scattered through native commands and background behavior. Schema creation, queries, mapping, transaction ordering, unread counts, body caching, account removal, and pending remote actions share invariants but do not have one owning module. Tests rebuild partial schemas instead of exercising the real persistence implementation.

Solution

Concentrate local mailbox persistence in one deep module that owns the SQLite connection lifecycle, schema, queries, transactions, and outbox invariants. Use the same implementation with in-memory SQLite for tests.

User Stories

1. As a mail user, I want synchronized folders and messages stored atomically, so that interrupted sync cannot leave a partial mailbox.
2. As a mail user, I want unread counts to match message state, so that folder badges remain trustworthy.
3. As a mail user, I want message bodies cached with their attachments, so that offline reads remain coherent.
4. As a mail user, I want removing an account to remove its local mailbox data consistently, so that stale data does not remain.
5. As a mail user, I want queued remote actions to survive restarts, so that offline changes can complete later.
6. As a mail user, I want failed persistence operations to leave prior data intact, so that errors do not cause data loss.
7. As a developer changing a mailbox invariant, I want one persistence module to update, so that SQL behavior is not duplicated.
8. As a developer testing synchronization, I want tests to use the production schema in memory, so that schema drift is detected.
9. As a maintainer, I want commands to express mailbox intent instead of embedding SQL, so that native behavior is easier to navigate.
10. As an agent fixing a persistence bug, I want queries and transactions to have locality, so that one fix covers all callers.
11. As a security-conscious user, I want the encrypted local database setup preserved, so that deepening does not weaken mailbox protection.
12. As a developer adding a migration, I want one initialization path, so that disk and in-memory databases behave the same way.

Implementation Decisions

• The mailbox persistence module will own SQLite schema initialization and connection usage.
• SQLite remains the concrete implementation; no repository trait or hypothetical adapter will be introduced.
• In-memory SQLite is local-substitutable and will exercise the same schema and transaction code.
• Mailbox reads, writes, unread-count refresh, body caching, attachments, sync storage, and pending actions will live behind the module interface.
• Transactional invariants will remain inside the module rather than being coordinated by callers.
• Native command transport may remain thin, but SQL will not remain in command implementations.
• Existing data structures and serialized command results will be preserved unless an invariant requires clarification.
• Database encryption and Keychain-derived key behavior will be preserved.
• No schema change is required merely to move ownership; any discovered migration need must be explicit.
• Existing partial-schema tests will be replaced by tests through the persistence interface.

Testing Decisions

• Test through the mailbox persistence interface using in-memory SQLite and the production initialization path.
• Assert observable records, counts, action outcomes, and transaction rollback behavior rather than SQL statements.
• Cover sync replacement, unread transitions, local trash versus permanent delete, body caching, attachments, account cascade removal, and pending-action replay.
• Keep one end-to-end native command check where transport serialization adds behavior.
• Reuse the current in-memory deletion test as prior art, but remove its hand-built partial schema.
• Tests should survive query rewrites and internal module reorganization.

Out of Scope

• Replacing SQLite or SQLCipher.
• Adding a repository trait with one implementation.
• Redesigning provider synchronization.
• Changing mailbox UI state.
• Adding new tables without a demonstrated invariant need.

Further Notes

This deepening can be performed independently, but provider synchronization should consume the resulting persistence interface rather than embed new SQL.