Get Started
Back to Templates

Sync Gmail emails to PostgreSQL with S3 attachment storage

Created by

Created by: Jose Cuartas || josecuartas

Jose Cuartas

Last update

Last update 2 days ago

Categories

Share

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.