Sync Gmail emails to PostgreSQL with S3 attachment storage
## Sync Gmail emails to PostgreSQL with S3 attachment storage
**Automated Gmail Email Processing System**
## Who's it for
Businesses and individuals who need to:
- Archive email communications in a searchable database
- Backup email attachments to cloud storage
- Analyze email patterns and communication data
- Comply with data retention policies
- Integrate emails with other business systems
## What it does
This workflow automatically captures, processes, and stores Gmail emails in a PostgreSQL database while uploading file attachments to S3/MinIO storage. It handles both individual emails (via Gmail Trigger) and bulk processing (via Schedule Trigger).
**Key features:**
- Dual processing: real-time individual emails + scheduled bulk retrieval
- Complete email metadata extraction (sender, recipients, labels, timestamps)
- HTML to plain text conversion for searchable content
- Binary attachment processing with metadata extraction
- Organized S3/MinIO file storage structure
- UPSERT database operations to prevent duplicates
## How it works
1. **Email Capture**: Gmail Trigger detects new emails, Schedule Trigger gets bulk emails from last hour
2. **Parallel Processing**: Emails with attachments go through binary processing, others go directly to transformation
3. **Attachment Handling**: Extract metadata, upload to S3/MinIO, create database references
4. **Data Transformation**: Convert Gmail API format to PostgreSQL structure
5. **Storage**: UPSERT emails to database with linked attachment information
## Requirements
**Credentials needed:**
- Gmail OAuth2 (gmail.readonly scope)
- PostgreSQL database connection
- S3/MinIO storage credentials
**Database setup:**
Run the provided SQL schema to create the messages table with JSONB fields for flexible data storage.
## How to set up
1. **Gmail OAuth2**: Enable Gmail API in Google Cloud Console, create OAuth2 credentials
2. **PostgreSQL**: Create database and run the SQL schema provided in setup sticky note
3. **S3/MinIO**: Create bucket "gmail-attachments" with proper upload permissions
4. **Configure**: Update authenticatedUserEmail in transform scripts to your email
5. **Test**: Start with single email before enabling bulk processing
## How to customize
- **Email filters**: Modify Gmail queries (in:sent, in:inbox) to target specific emails
- **Storage structure**: Change S3 file path format in Upload node
- **Processing schedule**: Adjust trigger frequencies based on email volume
- **Database fields**: Extend PostgreSQL schema for additional metadata
- **Attachment types**: Add file type filtering in binary processing logic
**Note**: This workflow processes emails from the last hour to avoid overwhelming the system. Adjust timeframes based on your email volume and processing needs.
