Email storage and credential lifecycle

src/store.ts owns the host-selected plugin_email schema and exports SQL-only migrate(db). The host invokes migration version 1 before registration; repeated invocation uses its migration ledger and is idempotent. Version 1 is unreleased and targets clean installations only: its Microsoft-reference uniqueness is part of the initial schema, not a production repair or upgrade migration. Once shipped, later changes append versions and never rewrite a shipped step. Uninstall with data uses the host schema/ledger removal mechanism. There is no down migration; rollback retains the schema for the owning compatible plugin version.

Accounts and cursors

accounts stores owner ID, provider kind, address, display name, access, alert preference, IMAP/SMTP settings or a Microsoft reference, and creation time. Provider constraints reject mixed credential metadata. EmailStore.create is insert-only: it never changes another owner through a colliding ID. New connection callers generate a UUID before credential storage.

The partial unique index accounts_microsoft_owner enforces one Microsoft account row per (user_id, microsoft_ref), independently of address or caller. Both inserts and reconnect updates are atomic under this invariant, including concurrent verified imports/reconnects and direct SQL writes. A collision rejects without changing the existing row, cursor or Microsoft token; the connection API retains its existing connection-failure response. No refcount or browser prefilter owns this rule. Other owners may use the same reference, and IMAP/Google rows have null references and are outside the index. Consequently removing one Microsoft row cannot disconnect another row that shares its identity, because such duplicates cannot exist.

Every interactive read/write supplies (userId, accountId). require rejects missing or foreign accounts alike. update, remove and saveCursor lock the owner-matched account row in their transaction and use only that transaction's executor. The scheduler's alertAccounts() is the explicit cross-owner enumeration for opted-in background checks.

alert_cursors has one account-owned row, containing either IMAP UIDVALIDITY/last UID or a Graph delta, plus check time. Account deletion cascades it. Cursor writes verify the provider and owner; no body, header cache or attachment bytes are stored.

Reconnect without replacing the account

MailAccountConnection in src/types.ts contains address and either {kind:'imap'|'google',imap,smtp,username} or {kind:'microsoft',microsoftRef}. mailConnectionSchema in the existing store validates that exact metadata, rejecting extra fields. EmailStore.reconnect(userId,id,connection): Promise<MailAccount> locks the owner-matched row and changes only its connection columns. Provider kind and exact mailbox address must match. The account ID, creation time, display name, access, alert preference and cursor remain intact; there is no delete/re-add or schema migration.

Connection/API code calls the existing account service:

await accounts.reconnect(userId, accountId, connection, secret, verify);

The required verify(account: NewMailAccount, secret: MailAccountSecret | null): Promise<void> matches the connection layer's VerifyMailConnection: it must perform real login, verify SMTP when needed, and close resources. It receives the candidate with the existing account ID and owner. Password and Google kinds require their matching credential; Microsoft requires null, since its identity control owns the token. The API must select a Microsoft reference from that owner's mailAccounts and pass the authenticated Google profile address, never trust a browser-selected owner.

Verification happens before persistence and outside SQL transactions. Afterwards the service takes the same owner/account credential lock used by refresh and removal, rechecks account existence, saves the verified credential and commits connection metadata. A failed metadata write restores the previous credential before releasing the lock; failure to restore throws an AggregateError, never success. Microsoft stores only its verified reference. Missing/foreign accounts, provider/mailbox changes, failed verification and failed persistence reject. A mailbox removed during verification is not recreated.

Credentials and removal

EmailSecrets resolves scope for each operation without caching bags. With a current account, it checks that exact owner and uses ambient ctx.userSecrets() in both daemon and runners. Without a current account, only the authoritative daemon may call ctx.userSecretsFor(userId); the host checks owner existence and live grants for every operation, including late refresh writes. A runner without an account rejects and never invokes the daemon-only accessor. It stores validated JSON under account.<uuid>: a password, or Google refresh/access token and expiration. Invalid stored JSON rejects with a generic reconnect error that never includes credential bytes. Reads do not invent credentials.

withLock(userId, id, operation) rereads the secret inside the host vault's interprocess lock. The callback receives the current value and an awaited save function. Saving null deletes the credential; reconnect rollback uses that when no credential previously existed. The save function rechecks the current owner and expires with its callback, so retaining it cannot bypass scope or lock ownership. Google refresh belongs inside that callback. Ordinary writes and removal use the same account lock without decoding an old value; malformed JSON therefore does not prevent explicit replacement or deletion. remove accepts an awaited afterRemove callback for account-row cleanup while the same credential lock is still held.

EmailAccounts.remove first verifies ownership, then deletes the credential (or calls the live Microsoft control's mailDisconnect), then removes the local row and cursor. A credential or provider-control failure keeps the row available for retry and is propagated. Remote messages are never deleted. Removing a Microsoft reference requires the owning control; absence is an unavailable error, never a successful disconnect. Removal holds the same account credential lock through provider disconnect/vault deletion and metadata deletion, so concurrent reconnect cannot leave a credential behind after the account row disappears.

registerUserRemoved closes owner admission, stops and drains alert polls, awaits complete API/tool callbacks (including connections, refreshes and backend close), forgets pending Google sign-in and deletes that user's account rows and cursors idempotently. Core's subsequent account-deletion transaction deletes every user vault row, including email and Microsoft mail credentials. The callback never reads the administrator's ambient vault. Background access uses the coordinated owner-authorized vault seam described in runtime.md.

Verification

tests/emailFoundation.test.ts and tests/emailAccounts.test.ts use tests/helpers/pluginDb.mjs and its disposable socket-only PostgreSQL cluster. They cover migration replay, owner isolation, ID collision, UIDVALIDITY identity, cursor provider checks, cursor cascade, verified reconnect with stable IDs/cursors/preferences, credential rollback, malformed credentials and removal failures. tests/emailMicrosoftUniqueness.test.ts covers direct SQL/store collisions, concurrent verified imports/reconnects, owner/null-reference isolation and preserved native encrypted tokens after a rejected reconnect and source removal. tests/emailSecretsHost.test.ts uses the candidate's real plugin context, daemon host, per-account grants and durable vault locks to verify background access, runner ambient scope, expired/cross-owner save callbacks and deletion during refresh. No fixture connects to an instance database or boots the daemon.