email-gmail-minio-_documented-INGLES_092525
Description
Categories
🤖 AI & Machine Learning
Nodes Used
n8n-nodes-base.s3n8n-nodes-base.setn8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.coden8n-nodes-base.noOpn8n-nodes-base.noOpn8n-nodes-base.gmailn8n-nodes-base.gmailn8n-nodes-base.gmail
PriceGratis
Views0
Last Updated11/28/2025
workflow.json
{
"id": "FmREphiGbmZ0G1c2",
"meta": {
"instanceId": "899c18ce39f97e3899bbcfee71176bdf66f586807337797df5057e21f373d373",
"templateCredsSetupCompleted": true
},
"name": "email-gmail-minio-_documented-INGLES_092525",
"tags": [
{
"id": "yFL69KEFrYRFofXs",
"name": "Email Processing",
"createdAt": "2025-09-23T20:24:57.942Z",
"updatedAt": "2025-09-23T20:24:57.942Z"
}
],
"nodes": [
{
"id": "0a33ff73-f79e-4078-a0aa-5abaa9f4fa84",
"name": "SYSTEM OVERVIEW",
"type": "n8n-nodes-base.stickyNote",
"position": [
896,
0
],
"parameters": {
"color": 4,
"width": 728,
"height": 512,
"content": "## SYSTEM OVERVIEW\n\n**Automated Gmail Email Processing System**\n\nThis workflow automatically captures, processes, and stores Gmail emails in PostgreSQL database, including handling file attachments that are uploaded to S3/MinIO storage.\n\n**Main functions:**\n- Automatic email capture (individual and bulk)\n- Gmail API format → PostgreSQL transformation\n- File attachment processing\n- Organized database storage\n- S3/MinIO attachment backup\n\n**Integrated technologies:**\n- Gmail API (reading + attachments)\n- PostgreSQL (structured data)\n- S3/MinIO (files)\n- n8n (orchestration)\n\n**Result:** Complete and searchable archive of business communications with organized and backed up attachments."
},
"typeVersion": 1
},
{
"id": "aec18c14-63c7-45b2-9ad1-913460c7e12d",
"name": "SETUP REQUIREMENTS",
"type": "n8n-nodes-base.stickyNote",
"position": [
896,
528
],
"parameters": {
"width": 728,
"height": 900,
"content": "## SETUP REQUIREMENTS\n\n**Required Credentials:**\n- Gmail OAuth2 (read + attachments)\n- PostgreSQL (write access)\n- S3/MinIO (file upload)\n\n**Gmail OAuth2 Setup:**\n1. Go to Google Cloud Console\n2. Create new project or select existing\n3. Enable Gmail API\n4. Create OAuth2 credentials\n5. Add authorized redirect URIs\n6. Configure scopes: gmail.readonly\n7. Connect in n8n credentials\n\n**PostgreSQL Setup:**\n1. Create database\n2. Run the provided SQL schema\n3. Create user with INSERT/UPDATE permissions\n4. Configure connection in n8n\n\n**Database Schema:**\n```sql\nCREATE TABLE messages (\n message_id VARCHAR PRIMARY KEY,\n thread_id VARCHAR,\n sender JSONB,\n recipients JSONB,\n labels JSONB,\n subject TEXT,\n body TEXT,\n attachments JSONB,\n size INTEGER,\n timestamp TIMESTAMP,\n is_read BOOLEAN,\n is_outgoing BOOLEAN,\n is_deleted BOOLEAN,\n last_indexed TIMESTAMP,\n created_at TIMESTAMP DEFAULT NOW(),\n updated_at TIMESTAMP DEFAULT NOW()\n);\n```"
},
"typeVersion": 1
},
{
"id": "2480af25-7d24-4319-a0a2-fbfee28356b9",
"name": "SYSTEM TRIGGERS",
"type": "n8n-nodes-base.stickyNote",
"position": [
1648,
352
],
"parameters": {
"color": 4,
"width": 336,
"height": 1018,
"content": "## SYSTEM TRIGGERS\n\n**Gmail Trigger**: Detects new emails every 10min\n**Schedule Trigger**: Executes every minute for bulk emails\n\n**Requirements:**\n- Gmail OAuth2 credential configured\n- Gmail read permissions\n- downloadAttachments: true\n\n**Setup Instructions:**\n1. Create Gmail OAuth2 credentials in Google Cloud Console\n2. Enable Gmail API\n3. Configure OAuth2 scopes: gmail.readonly\n4. Connect credential in n8n"
},
"typeVersion": 1
},
{
"id": "2f399efb-9293-4d0b-ae12-d6f50527b92e",
"name": "EMAIL RETRIEVAL",
"type": "n8n-nodes-base.stickyNote",
"position": [
1984,
48
],
"parameters": {
"color": 4,
"width": 500,
"height": 1348,
"content": "## EMAIL RETRIEVAL\n\n**Configured Gmail nodes:**\n\n**Get Individual Message**: \n- Gets complete individual email with all metadata\n- Triggered by Gmail Trigger\n- Includes complete headers and structured data\n\n**Get Many Sent**: \n- Searches sent emails from last hour\n- Query: \"in:sent newer_than:1h\"\n- Triggered by Schedule Trigger every minute\n\n**Get Many Inbox**: \n- Searches received emails from last hour \n- Query: \"in:inbox newer_than:1h\"\n- Triggered by Schedule Trigger every minute\n\n**Critical configuration:**\n- `downloadAttachments: true` - Downloads attachments as binary data\n- `simple: false` - Returns complete Gmail API structure\n- `returnAll: true` - No email quantity limit (Get many only)\n\n**Retrieved data:**\n- Complete metadata: from, to, cc, bcc, subject, date\n- Content: html, text, textAsHtml\n- Labels: labelIds (INBOX, SENT, UNREAD, etc.)\n- Attachments: binary data with fileName, mimeType, size\n- Headers: message-id, thread-id, references\n\n**Required credential:** Gmail OAuth2 with read permissions"
},
"typeVersion": 1
},
{
"id": "b186aa06-a61b-4de2-8b17-2de984ff9db4",
"name": "ATTACHMENT PROCESSING",
"type": "n8n-nodes-base.stickyNote",
"position": [
2496,
0
],
"parameters": {
"color": 4,
"width": 370,
"height": 600,
"content": "## ATTACHMENT PROCESSING\n\n**Binary Data Filter**: Only emails with binary data\n- Verifies multiple source nodes\n- Condition: item.binary exists\n\n**Extract Binary Metadata**: Extracts metadata\n**Split Attachments**: Separates each individual attachment\n\n**Process:**\n1. Filter emails with attachments\n2. Extract file metadata (name, type, size)\n3. Split into individual items\n4. Upload to S3/MinIO\n5. Create attachment records"
},
"typeVersion": 1
},
{
"id": "2317a48d-be68-45de-bf66-544a2d1269e1",
"name": "DATA MAPPING",
"type": "n8n-nodes-base.stickyNote",
"position": [
3600,
400
],
"parameters": {
"color": 4,
"width": 800,
"height": 676,
"content": "## DATA MAPPING\n\n**Merge Attachments**: Links attachments with emails\n- Key field: originalJson.id ↔ id\n\n**Upload to Storage**: Uploads to S3/MinIO\n- Bucket: gmail-attachments\n- Path: /user/{messageId}/{fileName}\n\n**Structure Fields**: Prepares attachment data\n**Aggregate**: Groups by email\n- Field: adjunto (all attachments)\n\n**Final Merge**: Combines emails + aggregated attachments"
},
"typeVersion": 1
},
{
"id": "ca7df595-4f73-45ef-953b-221bcde715af",
"name": "DATA TRANSFORMATION",
"type": "n8n-nodes-base.stickyNote",
"position": [
4416,
224
],
"parameters": {
"color": 4,
"width": 450,
"height": 832,
"content": "## DATA TRANSFORMATION\n\n**Email to PostgreSQL Script**: Transforms Gmail format to PostgreSQL\n- Normalizes senders/recipients\n- Extracts plain text from HTML\n- Fields: is_read, is_outgoing, etc.\n\n**Link Email Attachments Script**: Maps attachments\n- Links by messageId ↔ message_id\n- Converts required fields to JSON\n\n**Process:**\n1. Separate input data: attachments vs emails\n2. For each email find its attachments by messageId\n3. Process attachments and convert to JSON string\n4. Combine email + related attachments\n5. Convert fields to JSON for PostgreSQL\n\n**Key relationship**: \nattachment.messageId ↔ email.message_id\n\n**Final output**: Complete email with mapped attachments\n\n**PostgreSQL transformations:**\n- sender → JSON.stringify(sender)\n- recipients → JSON.stringify(recipients) \n- labels → JSON.stringify(labels)\n- attachments → JSON string of related attachments or null"
},
"typeVersion": 1
},
{
"id": "92881631-dcd7-48a2-ab18-bb91f340f314",
"name": "DATABASE STORAGE",
"type": "n8n-nodes-base.stickyNote",
"position": [
4896,
224
],
"parameters": {
"color": 4,
"width": 640,
"height": 980,
"content": "## DATABASE STORAGE\n\n**Process Items Loop**: Processes one by one\n**Insert or Update**: UPSERT to PostgreSQL\n- Table: messages\n- Key: message_id\n- JSONB fields: sender, recipients, labels\n\n**Requirements:**\n- PostgreSQL credential configured\n- Table 'messages' created with JSONB fields\n\n**Performance notes:**\n- Loop processing prevents timeouts on large batches\n- UPSERT prevents duplicate entries\n- Indexes recommended on message_id and timestamp"
},
"typeVersion": 1
},
{
"id": "803691ca-35b2-41f5-b18d-4cdc9a4332ce",
"name": "SCRIPT: Extract Binary",
"type": "n8n-nodes-base.stickyNote",
"position": [
2864,
0
],
"parameters": {
"color": 4,
"width": 320,
"height": 778,
"content": "## SCRIPT: Extract Binary Metadata\n\n**Function**: Extracts metadata from binary file attachments\n\n**Process:**\n1. Loops through all binary objects in item\n2. Extracts metadata per attachment:\n - fileName, mimeType, sizeBytes\n - fileExtension, encoding, fileType\n3. Calculates general statistics \n4. Creates summary of all attachments\n\n**Input**: Items with binary data\n**Output**: Structured attachment metadata\n\n**Data extracted per attachment:**\n- key, mimeType, fileName, fileExtension\n- sizeBytes, sizeMB, hasData, encoding\n- fileType (image/document/audio/etc.)\n\n**Generated summary:**\n- totalAttachments, totalSizeBytes, totalSizeMB\n- attachmentKeys, fileTypes, mimeTypes, extensions"
},
"typeVersion": 1
},
{
"id": "cca7c12b-a517-4e24-a4a4-bdfa56c28ec8",
"name": "SCRIPT: Email Transform",
"type": "n8n-nodes-base.stickyNote",
"position": [
2752,
832
],
"parameters": {
"color": 4,
"width": 578,
"height": 600,
"content": "## SCRIPT: Email Transform\n\n**Function**: Transforms emails from Gmail API format to PostgreSQL format\n\n**Process:**\n1. Extracts sender/recipients from Gmail API\n2. Normalizes email addresses (address + name) \n3. Processes HTML content → plain text\n4. Calculates derived fields (is_read, is_outgoing)\n5. Converts timestamps to ISO format\n\n**Input**: Raw email from Gmail API\n**Output**: Structured object for DB\n\n**Processed fields:**\n- message_id, thread_id, sender, recipients\n- subject, body, labels, timestamp\n- is_read, is_outgoing, is_deleted, last_indexed\n\n**Key transformations:**\n- HTML → plain text (fallback to subject)\n- labelIds → is_read (!includes('UNREAD'))\n- sender.email vs authenticatedUserEmail → is_outgoing"
},
"typeVersion": 1
},
{
"id": "dfb06420-2df7-4358-ae18-1af7a6ddcb5c",
"name": "Process Items Loop",
"type": "n8n-nodes-base.splitInBatches",
"position": [
4992,
928
],
"parameters": {
"options": {}
},
"typeVersion": 3
},
{
"id": "780d103a-f2b6-4458-ae38-0c4c842cb309",
"name": "Extract Binary Metadata",
"type": "n8n-nodes-base.code",
"position": [
2960,
672
],
"parameters": {
"jsCode": "const results = [];\n\nfor (const item of $input.all()) {\n const binaryData = item.binary;\n const jsonData = item.json;\n \n if (!binaryData || typeof binaryData !== 'object') {\n results.push({\n json: {\n error: \"No binary data found in item\",\n originalJson: jsonData,\n itemIndex: results.length\n }\n });\n continue;\n }\n \n const attachmentMetadata = {};\n const attachmentList = [];\n let totalSize = 0;\n \n for (const [key, binaryItem] of Object.entries(binaryData)) {\n if (binaryItem && binaryItem.data) {\n const metadata = {\n key: key,\n mimeType: binaryItem.mimeType || 'unknown',\n fileName: binaryItem.fileName || key,\n fileExtension: binaryItem.fileName ? \n binaryItem.fileName.split('.').pop().toLowerCase() : 'unknown',\n sizeBytes: binaryItem.data ? Buffer.byteLength(binaryItem.data) : 0,\n sizeMB: binaryItem.data ? \n (Buffer.byteLength(binaryItem.data) / 1024 / 1024).toFixed(2) : 0,\n hasData: !!binaryItem.data,\n encoding: 'binary',\n fileType: 'unknown'\n };\n \n attachmentMetadata[key] = metadata;\n attachmentList.push(metadata);\n totalSize += metadata.sizeBytes;\n }\n }\n \n const summary = {\n totalAttachments: attachmentList.length,\n totalSizeBytes: totalSize,\n totalSizeMB: (totalSize / 1024 / 1024).toFixed(2)\n };\n \n results.push({\n json: {\n summary: summary,\n attachments: attachmentMetadata,\n attachmentList: attachmentList,\n originalJson: jsonData,\n extractedAt: new Date().toISOString(),\n itemIndex: results.length\n }\n });\n}\n\nreturn results;"
},
"typeVersion": 2
},
{
"id": "fd1530bf-7504-4020-bc53-76367009329c",
"name": "Link Email Attachments",
"type": "n8n-nodes-base.code",
"position": [
4480,
928
],
"parameters": {
"jsCode": "\n\nfunction processAttachments(attachmentsArray, messageId) {\n if (!attachmentsArray || !Array.isArray(attachmentsArray)) {\n return null;\n }\n \n const emailAttachments = attachmentsArray.filter(item => \n item.messageId === messageId\n );\n \n if (emailAttachments.length === 0) {\n return null;\n }\n \n const processedAttachments = emailAttachments.map(item => {\n const att = item.attachments || {};\n return {\n key: att.key || '',\n mimeType: att.mimeType || '',\n fileName: att.fileName || '',\n fileExtension: att.fileExtension || '',\n sizeBytes: att.sizeBytes || 0,\n sizeMB: att.sizeMB || '0',\n hasData: att.hasData || false,\n encoding: att.encoding || '',\n fileType: att.fileType || '',\n originalFileSize: att.originalFileSize || ''\n };\n });\n \n return JSON.stringify(processedAttachments);\n}\n\nconst transformedItems = [];\n\ntry {\n const inputData = $input.all().map(item => item.json).flat();\n \n let attachmentsData = [];\n const emailsData = [];\n \n inputData.forEach((item, index) => {\n if (item.adjunto && Array.isArray(item.adjunto)) {\n attachmentsData = attachmentsData.concat(item.adjunto);\n } else if (item.message_id) {\n emailsData.push(item);\n }\n });\n \n emailsData.forEach(emailData => {\n const emailAttachments = processAttachments(attachmentsData, emailData.message_id);\n \n const transformedMessage = {\n message_id: emailData.message_id,\n thread_id: emailData.thread_id,\n sender: JSON.stringify(emailData.sender),\n recipients: JSON.stringify(emailData.recipients),\n labels: JSON.stringify(emailData.labels),\n subject: emailData.subject,\n body: emailData.body,\n attachments: emailAttachments,\n size: emailData.size,\n timestamp: emailData.timestamp,\n is_read: emailData.is_read,\n is_outgoing: emailData.is_outgoing,\n is_deleted: emailData.is_deleted,\n last_indexed: emailData.last_indexed\n };\n \n transformedItems.push({ json: transformedMessage });\n });\n \n} catch (error) {\n transformedItems.push({\n json: {\n error: true,\n message: error.message,\n timestamp: new Date().toISOString()\n }\n });\n}\n\nreturn transformedItems;"
},
"typeVersion": 2
},
{
"id": "95720209-c7b9-4f42-ba46-8c34e67f4212",
"name": "Structure Fields",
"type": "n8n-nodes-base.set",
"position": [
3760,
768
],
"parameters": {
"mode": "raw",
"options": {},
"jsonOutput": "={\n \"attachments\": {{ $('Merge Attachments').item.json.attachments }},\n \"messageId\": \"{{ $('Merge Attachments').item.json['originalJson.id'] }}\"\n}"
},
"typeVersion": 3.4
},
{
"id": "36c166f5-b689-44fb-ba7c-97ea037aa414",
"name": "Final Merge",
"type": "n8n-nodes-base.merge",
"position": [
4208,
928
],
"parameters": {},
"typeVersion": 3.2
},
{
"id": "281c76ce-eaed-427c-88a2-5bced43cdc0b",
"name": "Aggregate Attachments",
"type": "n8n-nodes-base.aggregate",
"position": [
3920,
768
],
"parameters": {
"options": {},
"aggregate": "aggregateAllItemData",
"destinationFieldName": "adjunto"
},
"typeVersion": 1
},
{
"id": "feee0836-e419-43a0-a2e2-e61c8601fa17",
"name": "Insert or Update Database",
"type": "n8n-nodes-base.postgres",
"position": [
5280,
944
],
"parameters": {
"table": {
"__rl": true,
"mode": "list",
"value": "messages",
"cachedResultName": "messages"
},
"schema": {
"__rl": true,
"mode": "list",
"value": "public"
},
"columns": {
"value": {},
"schema": [
{
"id": "message_id",
"type": "string",
"display": true,
"removed": false,
"required": true,
"displayName": "message_id",
"defaultMatch": false,
"canBeUsedToMatch": true
}
],
"mappingMode": "autoMapInputData",
"matchingColumns": [
"message_id"
]
},
"options": {},
"operation": "upsert"
},
"credentials": {
"postgres": {
"id": "ND7MmENs7bvU1tdS",
"name": "gmail-postgresql"
}
},
"typeVersion": 2.6
},
{
"id": "42737d4f-7e8a-40c0-9a40-05fbfe75a4b4",
"name": "Binary Data Filter",
"type": "n8n-nodes-base.filter",
"position": [
2720,
672
],
"parameters": {
"options": {},
"conditions": {
"options": {
"version": 2,
"leftValue": "",
"caseSensitive": true,
"typeValidation": "loose"
},
"combinator": "and",
"conditions": [
{
"id": "binary-filter-condition",
"operator": {
"type": "object",
"operation": "exists",
"singleValue": true
},
"leftValue": "={{ ($('Get Individual Message').isExecuted ? $('Get Individual Message').item.binary : null) || ($('Get Many Sent').isExecuted ? $('Get Many Sent').item.binary : null) || ($('Get Many Inbox').isExecuted ? $('Get Many Inbox').item.binary : null) }}",
"rightValue": ""
}
]
},
"looseTypeValidation": true
},
"typeVersion": 2.2
},
{
"id": "c8686357-9600-4317-95c5-dbbd6b462378",
"name": "Upload to Storage",
"type": "n8n-nodes-base.s3",
"position": [
3600,
768
],
"parameters": {
"fileName": "=/emailuser/{{ $json[\"originalJson.id\"] }}/{{ $json.attachments.fileName }}",
"operation": "upload",
"bucketName": "=gmail-attachments",
"additionalFields": {},
"binaryPropertyName": "={{ $json.attachments.key }}"
},
"credentials": {
"s3": {
"id": "3k864gx3V3O1OCaQ",
"name": "S3 account"
}
},
"typeVersion": 1
},
{
"id": "e73cf96d-064f-4718-a110-7d5e0b2fb4ae",
"name": "Flow Convergence",
"type": "n8n-nodes-base.noOp",
"position": [
2368,
928
],
"parameters": {},
"typeVersion": 1
},
{
"id": "4ebdb948-2ddb-4902-b6fc-6e285865e29d",
"name": "End Loop",
"type": "n8n-nodes-base.noOp",
"position": [
5264,
704
],
"parameters": {},
"typeVersion": 1
},
{
"id": "15ec8e2f-b136-426d-b997-dbab3917f7db",
"name": "Get Individual Message",
"type": "n8n-nodes-base.gmail",
"position": [
2048,
784
],
"webhookId": "2db16144-ecf5-4b0e-8292-c0cd1e1c9cef",
"parameters": {
"simple": false,
"options": {
"downloadAttachments": true
},
"messageId": "={{ $json.id }}",
"operation": "get"
},
"credentials": {
"gmailOAuth2": {
"id": "O0hOO1rdssY1E4qM",
"name": "Gmail account"
}
},
"typeVersion": 2.1
},
{
"id": "9032c8cf-b8d1-4537-b939-d2010ebaf188",
"name": "Get Many Sent",
"type": "n8n-nodes-base.gmail",
"position": [
2048,
928
],
"webhookId": "06120048-cc84-485d-bdcb-f3581714c885",
"parameters": {
"simple": false,
"filters": {
"q": "in:sent newer_than:1h"
},
"options": {
"downloadAttachments": true
},
"operation": "getAll",
"returnAll": true
},
"credentials": {
"gmailOAuth2": {
"id": "O0hOO1rdssY1E4qM",
"name": "Gmail account"
}
},
"typeVersion": 2.1
},
{
"id": "e41c9979-10db-46e8-b62c-ba194182a52e",
"name": "Get Many Inbox",
"type": "n8n-nodes-base.gmail",
"position": [
2048,
1136
],
"webhookId": "841c056d-b5f8-426d-8b08-5a04be5fa4c1",
"parameters": {
"simple": false,
"filters": {
"q": "in:inbox newer_than:1h"
},
"options": {
"downloadAttachments": true
},
"operation": "getAll",
"returnAll": true
},
"credentials": {
"gmailOAuth2": {
"id": "O0hOO1rdssY1E4qM",
"name": "Gmail account"
}
},
"typeVersion": 2.1
},
{
"id": "bdf38440-c107-4237-b5f1-542f1355e7d5",
"name": "Schedule Trigger",
"type": "n8n-nodes-base.scheduleTrigger",
"position": [
1792,
1024
],
"parameters": {
"rule": {
"interval": [
{
"field": "hours"
}
]
}
},
"typeVersion": 1.2
},
{
"id": "0534de91-4193-4ce3-b22c-dcd8e5f2449b",
"name": "Gmail Trigger",
"type": "n8n-nodes-base.gmailTrigger",
"position": [
1792,
784
],
"parameters": {
"simple": false,
"filters": {},
"options": {
"downloadAttachments": true
},
"pollTimes": {
"item": [
{
"mode": "everyX",
"unit": "minutes",
"value": 30
}
]
}
},
"credentials": {
"gmailOAuth2": {
"id": "O0hOO1rdssY1E4qM",
"name": "Gmail account"
}
},
"typeVersion": 1.3
},
{
"id": "effb5527-f309-46f0-9fef-ac98ad9ecc17",
"name": "Email to PostgreSQL Transform",
"type": "n8n-nodes-base.code",
"position": [
3152,
944
],
"parameters": {
"jsCode": "// Gmail to PostgreSQL transformation\nconst authenticatedUserEmail = '[email protected]';\n\nfunction extractTextFromHtml(htmlContent) {\n if (!htmlContent) return '';\n return htmlContent.replace(/<[^>]+>/g, '').replace(/\\s+/g, ' ').trim().substring(0, 500) || '';\n}\n\nfunction normalizeEmailAddress(emailObj) {\n if (!emailObj) return { email: '', name: '' };\n return {\n email: emailObj.address || emailObj.email || '',\n name: emailObj.name || ''\n };\n}\n\nconst transformedItems = [];\n\nfor (const item of $input.all()) {\n const gmailData = item.json;\n \n const fromInfo = gmailData.from?.value?.[0] || {};\n const sender = normalizeEmailAddress(fromInfo);\n \n const recipients = {\n to: (gmailData.to?.value || []).map(normalizeEmailAddress),\n cc: (gmailData.cc?.value || []).map(normalizeEmailAddress),\n bcc: (gmailData.bcc?.value || []).map(normalizeEmailAddress)\n };\n \n const labels = gmailData.labelIds || [];\n \n let body = '';\n if (gmailData.text && gmailData.text.trim().length > 0) {\n body = gmailData.text.trim().substring(0, 1000);\n } else if (gmailData.textAsHtml && gmailData.textAsHtml.trim().length > 0) {\n body = gmailData.textAsHtml.replace(/<[^>]+>/g, '').trim().substring(0, 1000);\n } else if (gmailData.html) {\n body = extractTextFromHtml(gmailData.html);\n }\n \n if (!body || body.length === 0) {\n body = gmailData.subject || 'No content available';\n }\n \n const isRead = !labels.includes('UNREAD');\n const isOutgoing = sender.email.toLowerCase() === authenticatedUserEmail.toLowerCase();\n \n let timestamp = new Date().toISOString();\n if (gmailData.date) {\n try {\n timestamp = new Date(gmailData.date).toISOString();\n } catch (error) {\n timestamp = new Date().toISOString();\n }\n }\n \n const transformedMessage = {\n message_id: gmailData.id,\n thread_id: gmailData.threadId || gmailData.id,\n sender: sender,\n recipients: recipients,\n labels: labels,\n subject: gmailData.subject || '',\n body: body,\n attachments: null,\n size: gmailData.sizeEstimate || 0,\n timestamp: timestamp,\n is_read: isRead,\n is_outgoing: isOutgoing,\n is_deleted: false,\n last_indexed: new Date().toISOString()\n };\n \n transformedItems.push({ json: transformedMessage });\n}\n\nreturn transformedItems;"
},
"typeVersion": 2
},
{
"id": "2f4846f0-475d-4fb5-8999-f82e006df9b4",
"name": "Split Attachments",
"type": "n8n-nodes-base.splitOut",
"position": [
3152,
672
],
"parameters": {
"include": "selectedOtherFields",
"options": {},
"fieldToSplitOut": "attachments",
"fieldsToInclude": "originalJson.id"
},
"typeVersion": 1
},
{
"id": "7fa3c757-7f23-417a-a818-6f0b819334e1",
"name": "Merge Attachments",
"type": "n8n-nodes-base.merge",
"position": [
3440,
768
],
"parameters": {
"mode": "combine",
"options": {},
"advanced": true,
"mergeByFields": {
"values": [
{
"field1": "originalJson.id",
"field2": "id"
}
]
}
},
"typeVersion": 3.2
},
{
"id": "831155c0-bacf-4dd1-92a8-e7e8d5af2d7b",
"name": "Sticky Note",
"type": "n8n-nodes-base.stickyNote",
"position": [
0,
0
],
"parameters": {
"width": 880,
"height": 1504,
"content": "## Sync Gmail emails to PostgreSQL with S3 attachment storage\n\n**Automated Gmail Email Processing System**\n\n## Who's it for\nBusinesses and individuals who need to:\n- Archive email communications in a searchable database\n- Backup email attachments to cloud storage\n- Analyze email patterns and communication data\n- Comply with data retention policies\n- Integrate emails with other business systems\n\n## What it does\nThis 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).\n\n**Key features:**\n- Dual processing: real-time individual emails + scheduled bulk retrieval\n- Complete email metadata extraction (sender, recipients, labels, timestamps)\n- HTML to plain text conversion for searchable content\n- Binary attachment processing with metadata extraction\n- Organized S3/MinIO file storage structure\n- UPSERT database operations to prevent duplicates\n\n## How it works\n1. **Email Capture**: Gmail Trigger detects new emails, Schedule Trigger gets bulk emails from last hour\n2. **Parallel Processing**: Emails with attachments go through binary processing, others go directly to transformation\n3. **Attachment Handling**: Extract metadata, upload to S3/MinIO, create database references\n4. **Data Transformation**: Convert Gmail API format to PostgreSQL structure\n5. **Storage**: UPSERT emails to database with linked attachment information\n\n## Requirements\n**Credentials needed:**\n- Gmail OAuth2 (gmail.readonly scope)\n- PostgreSQL database connection\n- S3/MinIO storage credentials\n\n**Database setup:**\nRun the provided SQL schema to create the messages table with JSONB fields for flexible data storage.\n\n## How to set up\n1. **Gmail OAuth2**: Enable Gmail API in Google Cloud Console, create OAuth2 credentials\n2. **PostgreSQL**: Create database and run the SQL schema provided in setup sticky note\n3. **S3/MinIO**: Create bucket \"gmail-attachments\" with proper upload permissions\n4. **Configure**: Update authenticatedUserEmail in transform scripts to your email\n5. **Test**: Start with single email before enabling bulk processing\n\n## How to customize\n- **Email filters**: Modify Gmail queries (in:sent, in:inbox) to target specific emails\n- **Storage structure**: Change S3 file path format in Upload node\n- **Processing schedule**: Adjust trigger frequencies based on email volume\n- **Database fields**: Extend PostgreSQL schema for additional metadata\n- **Attachment types**: Add file type filtering in binary processing logic\n\n**Note**: This workflow processes emails from the last hour to avoid overwhelming the system. Adjust timeframes based on your email volume and processing needs."
},
"typeVersion": 1
}
],
"active": false,
"pinData": {},
"settings": {
"executionOrder": "v1"
},
"versionId": "38ad63c6-aa46-4c77-9fe1-d287f26daec2",
"connections": {
"Final Merge": {
"main": [
[
{
"node": "Link Email Attachments",
"type": "main",
"index": 0
}
]
]
},
"Get Many Sent": {
"main": [
[
{
"node": "Flow Convergence",
"type": "main",
"index": 0
}
]
]
},
"Gmail Trigger": {
"main": [
[
{
"node": "Get Individual Message",
"type": "main",
"index": 0
}
]
]
},
"Get Many Inbox": {
"main": [
[
{
"node": "Flow Convergence",
"type": "main",
"index": 0
}
]
]
},
"Flow Convergence": {
"main": [
[
{
"node": "Binary Data Filter",
"type": "main",
"index": 0
},
{
"node": "Merge Attachments",
"type": "main",
"index": 1
},
{
"node": "Email to PostgreSQL Transform",
"type": "main",
"index": 0
}
]
]
},
"Schedule Trigger": {
"main": [
[
{
"node": "Get Many Sent",
"type": "main",
"index": 0
},
{
"node": "Get Many Inbox",
"type": "main",
"index": 0
}
]
]
},
"Structure Fields": {
"main": [
[
{
"node": "Aggregate Attachments",
"type": "main",
"index": 0
}
]
]
},
"Merge Attachments": {
"main": [
[
{
"node": "Upload to Storage",
"type": "main",
"index": 0
}
]
]
},
"Split Attachments": {
"main": [
[
{
"node": "Merge Attachments",
"type": "main",
"index": 0
}
]
]
},
"Upload to Storage": {
"main": [
[
{
"node": "Structure Fields",
"type": "main",
"index": 0
}
]
]
},
"Binary Data Filter": {
"main": [
[
{
"node": "Extract Binary Metadata",
"type": "main",
"index": 0
}
]
]
},
"Process Items Loop": {
"main": [
[
{
"node": "End Loop",
"type": "main",
"index": 0
}
],
[
{
"node": "Insert or Update Database",
"type": "main",
"index": 0
}
]
]
},
"Aggregate Attachments": {
"main": [
[
{
"node": "Final Merge",
"type": "main",
"index": 0
}
]
]
},
"Get Individual Message": {
"main": [
[
{
"node": "Flow Convergence",
"type": "main",
"index": 0
}
]
]
},
"Link Email Attachments": {
"main": [
[
{
"node": "Process Items Loop",
"type": "main",
"index": 0
}
]
]
},
"Extract Binary Metadata": {
"main": [
[
{
"node": "Split Attachments",
"type": "main",
"index": 0
}
]
]
},
"Insert or Update Database": {
"main": [
[
{
"node": "Process Items Loop",
"type": "main",
"index": 0
}
]
]
},
"Email to PostgreSQL Transform": {
"main": [
[
{
"node": "Final Merge",
"type": "main",
"index": 1
}
]
]
}
}
}