notes

Email Inbox Processing for Venmo and Zelle Transactions

Overview

Process a user’s email inbox to extract Venmo and Zelle transaction information and create events in the ledger. User authenticates to the app first (email/password, Google, etc.); inbox access is requested separately via a “Connect email” flow, not at login.

Summary of Decisions

1. Connector Setup + Scope Permissions

Separate connectors per provider: Gmail, Outlook, Yahoo, etc. Each has its own connector type (connector.gmail, connector.outlook, etc.) with provider-specific OAuth and fetch logic.
Post-login flow only: Gmail/Outlook scope is never requested at login. User must explicitly “Connect Gmail” or “Connect Outlook” after logging in.
Token storage: Tokens (access_token, refresh_token, expires_at) stored in connector private data. Multiple connectors per user allowed (e.g. personal Gmail + work Gmail); use connected email as context.
Identity linking: Connect flow also upserts UserIdentity for the provider so the user can “Sign in with Google” or “Sign in with Microsoft” in the future. If identity already exists for same user, no duplicate; if exists for different user, reject with conflict.

2. Periodic Incremental Pull

Incremental by default: Use last_at (minus fudge factor) as the boundary for fetching new messages. Deduplicate by excluding already-processed message IDs before creating events.
Fudge factor: Email timestamps can lag inbox delivery; query last_at - N (e.g. 5–15 min) to overlap and avoid missing late-arriving messages.
Trigger: Periodic job (cron/worker), not Lambda for now. Lambda might make sense later if processing becomes heavy or we need to segment backend from batch work.
Bulk refetch: TBD – manual or very long interval.

3. Deduplication via external_ids

New indexed external_ids on ledger: Array of namespaced ids, always indexed. Each event can carry multiple ids for different dedup dimensions.
Why multiple ids: Message id alone isn’t enough. We also need the payload id (e.g. Venmo transaction id, Zelle reference) – the same transaction can appear in multiple emails (forwarded, duplicate notifications), or the same email might be reprocessed. Dedup by either.
Example: external_ids = ['gmail:{message.id}', 'venmo:{transaction_id}'] – message-level (“we processed this email”) and transaction-level (“we saw this Venmo tx”) dedup.
external_ids in event references: Include references.external_ids (array) so the ledger preprocessor can dedup generically: if any id in the array already exists on a ledger record, skip.

4. Processing the Relevant Emails

Filter: Gmail/Outlook query for Venmo/Zelle senders (e.g. from:venmo.com, subject contains “Zelle”).
Fetch: Get full message body (MIME parse via stdlib email).
Parse: Lightweight LLM extraction – “Extract amount, currency, date, direction (sent/received), counterparty, note. Return JSON.” Validation and optional regex fallback. No existing Python packages for Venmo/Zelle email parsing.
Map: Build EventCreate with source (e.g. connector.gmail), references.email.message_id, references.external_ids = [f'{provider}:{message.id}', f'{payload_source}:{payload_id}'] (e.g. ['gmail:msg_123', 'venmo:tx_456']). Ledger preprocessor dedupes if any id in the array already exists.

Connect Flow (Gmail, Outlook, etc.)

Standalone flow: Same pattern for Gmail and Outlook – user clicks “Connect Gmail” or “Connect Outlook”, redirects to provider OAuth, callback stores tokens on connector and links identity.
Auto-detect provider: Use domain for consumer addresses (@gmail.com → Gmail, @outlook.com → Outlook, etc.). For custom domains (e.g. @modfin.com hosted by Google), use MX lookup or prompt. Always prompt if ambiguous – don’t route silently when uncertain.

Provider-Specific Notes

Provider	API / Protocol	OAuth
Gmail	Gmail API	Google OAuth
Outlook/Hotmail	Microsoft Graph	Microsoft OAuth
Yahoo	IMAP or Yahoo API	Yahoo OAuth
iCloud	IMAP	Apple Sign In
Generic	IMAP	App passwords

For generic IMAP, there is no federated identity to link – connector provides mailbox access only, not a new sign-in option.

Microsoft (Outlook) Case

App registration in Azure Portal; client_id, client_secret, redirect URIs.
OAuth: https://login.microsoftonline.com/{tenant}/oauth2/v2.0/authorize with scopes Mail.Read, offline_access, openid, email. Use tenant common for personal (Outlook.com, Hotmail) and work accounts.
Token exchange at https://login.microsoftonline.com/{tenant}/oauth2/v2.0/token.
Fetch messages via Microsoft Graph https://graph.microsoft.com/v1.0/me/messages.

Files / Touchpoints (API)

New connector services: gmail, outlook, etc. (connectors/consts, schemas, routes).
New Connect flows: auth routes or connectors routes for OAuth initiate + callback.
New module: Gmail/Outlook API clients, token refresh, Venmo/Zelle parsers, sync orchestration.
Ledger: add indexed external_ids column (array; check for overlap with incoming ids for dedup).
Events: allow source for email connectors; add references.external_ids (array of namespaced ids, e.g. ['gmail:msg_123', 'venmo:tx_456']); ledger preprocessor dedupes if any id already exists.
Periodic job: invoke sync for connectors due for pull.