Parse & Track Orders From Email with Gemini/gpt & Notion Database Sync

The workflow JSON

Copy or download the full n8n JSON below. Paste it into a new n8n workflow, add your credentials, activate. Full import guide →

{
  "id": "tSvYrl1nDEQAakeo",
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "name": "order management template",
  "tags": [],
  "nodes": [
    {
      "id": "97df5595-af92-4547-a481-69005279badb",
      "name": "Gmail Trigger",
      "type": "n8n-nodes-base.gmailTrigger",
      "position": [
        -1264,
        1280
      ],
      "parameters": {
        "filters": {},
        "pollTimes": {
          "item": [
            {
              "mode": "everyMinute"
            }
          ]
        }
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1.3
    },
    {
      "id": "68518ec5-5b68-4ffc-8f6a-4735290337bc",
      "name": "Google Gemini Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatGoogleGemini",
      "position": [
        -560,
        1248
      ],
      "parameters": {
        "options": {},
        "modelName": "models/gemini-2.5-pro"
      },
      "credentials": {
        "googlePalmApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "3d21f84e-cf1c-4057-9a3d-b52f4c7dbb3e",
      "name": "OpenAI Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        -432,
        1248
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4.1-mini"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "b406d6a9-169a-4942-98b5-e80d125e2a68",
      "name": "Structured Output Parser",
      "type": "@n8n/n8n-nodes-langchain.outputParserStructured",
      "position": [
        -272,
        1248
      ],
      "parameters": {
        "jsonSchemaExample": "{\n  \"extraction_success\": true,\n  \"vendor\": \"Amazon\",\n  \"customer_name\": \"Jim\",\n  \"order_number\": \"408-0237654-1573974\",\n  \"order_status\": \"Ordered\",\n  \"delivery_info\": {\n    \"location\": \"Dubai\",\n    \"expected_date\": \"2025-10-15\",\n    \"status_description\": \"Arriving Wednesday\"\n  },\n  \"items\": [\n    {\n      \"item_name\": \"Pilot Automotive Wh553-16S-Bs Black 16 Inch Wheel Cover\",\n      \"quantity\": 1,\n      \"price\": \"AED120.00\",\n      \"currency\": \"AED\"\n    }\n  ],\n  \"order_total\": {\n    \"amount\": \"AED127.00\",\n    \"currency\": \"AED\"\n  },\n  \"confidence\": \"High\",\n  \"notes\": \"Order details extracted successfully\"\n}"
      },
      "typeVersion": 1.3
    },
    {
      "id": "ec6e8b1e-7510-4a21-989b-c2f602bc0d7a",
      "name": "Create a database page in Notion",
      "type": "n8n-nodes-base.notionTool",
      "position": [
        -128,
        1344
      ],
      "parameters": {
        "options": {},
        "resource": "databasePage",
        "databaseId": {
          "__rl": true,
          "mode": "list",
          "value": "28accb46-01db-808b-833b-db3c006c2c4d",
          "cachedResultUrl": "https://www.notion.so/28accb4601db808b833bdb3c006c2c4d",
          "cachedResultName": "Order Managment"
        },
        "propertiesUi": {
          "propertyValues": [
            {
              "key": "Order Number|title",
              "title": "={{ $json.output.order_number }}"
            },
            {
              "key": "Name of the Item|rich_text",
              "textContent": "={{ $json.output.items[0].item_name }}"
            },
            {
              "key": "Expected Date|rich_text",
              "textContent": "={{ $json.output.delivery_info.expected_date }}"
            },
            {
              "key": "Order Status|select",
              "selectValue": "={{ $json.output.order_status }}"
            },
            {
              "key": "Quantity|number",
              "numberValue": "={{ $json.output.items[0].quantity }}"
            }
          ]
        }
      },
      "credentials": {
        "notionApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "87446742-940c-47ea-8509-5ca4b7c95403",
      "name": "Update a database page in Notion",
      "type": "n8n-nodes-base.notionTool",
      "position": [
        272,
        1344
      ],
      "parameters": {
        "pageId": {
          "__rl": true,
          "mode": "url",
          "value": "={{ /*n8n-auto-generated-fromAI-override*/ $fromAI('Database_Page', ``, 'string') }}"
        },
        "options": {},
        "resource": "databasePage",
        "operation": "update",
        "propertiesUi": {
          "propertyValues": [
            {
              "key": "Order Status|select",
              "selectValue": "={{ $fromAI('property_order_status') }}"
            }
          ]
        }
      },
      "credentials": {
        "notionApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "90f2d910-6a08-44a0-98ef-0874c3a0b2b4",
      "name": "Send a message",
      "type": "n8n-nodes-base.gmail",
      "position": [
        368,
        1136
      ],
      "parameters": {
        "sendTo": "user@example.com",
        "message": "={{ $json.output }}",
        "options": {
          "appendAttribution": false
        },
        "subject": "ORDER CHANGE",
        "emailType": "text"
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "160b8d42-a23a-4645-9455-b9074bb9bfd9",
      "name": "Email Classification and Extraction Agent",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        -528,
        1024
      ],
      "parameters": {
        "text": "=subject:{{ $json.Subject }}\n\n{{ $json.snippet }}",
        "options": {
          "systemMessage": "=# Email Order Extraction Agent\n\n## Role\nYou are an intelligent email parsing assistant specialized in extracting order information from e-commerce confirmation and shipping notification emails. Your task is to accurately identify and extract key order details from emails sent by various online retailers and vendors.\n\n## Current Context\n**Current Date & Time**: {{$now}}\n**Timezone**: UAE (GMT+4)\n\n## Supported Vendors\nWhile you should be able to parse emails from any e-commerce vendor, common sources include:\n- Amazon\n- Noon\n- Namshi\n- Sharaf DG\n- Carrefour\n- Other online retailers and marketplaces\n\n## Information to Extract\n\nYou must extract the following fields from each order confirmation or shipping email:\n\n### 1. **Order Number** (Required)\n- Field names to look for: \"Order #\", \"Order Number\", \"Order ID\", \"Reference Number\", \"Tracking Number\"\n- Format varies by vendor (e.g., \"408-0237654-1573974\", \"ORD-2024-12345\", \"NM-567890\")\n- Extract the complete alphanumeric identifier\n\n### 2. **Item Name(s)** (Required)\n- Extract the full product name/title\n- If multiple items in one order, extract all items as a list\n- Include relevant details like brand, model, size, color if visible\n- Remove unnecessary text like \"View product\" or \"Buy again\"\n\n### 3. **Price** (Required)\n- Extract the price for each item\n- Always include currency (AED, USD, SAR, etc.)\n- Look for: \"AED\", \"$\", \"SAR\", \"QAR\", or other currency symbols\n- Note if it's a subtotal, total, or per-item price\n- If multiple items, extract individual prices AND total if available\n\n### 4. **Order Status** (Required)\n- Must be one of these standardized values:\n  - **\"Ordered\"** - Order placed but not yet shipped\n  - **\"Shipped\"** - Order has been dispatched\n  - **\"Out for Delivery\"** - Order is with delivery courier for final delivery\n  - **\"Delivered\"** - Order has been delivered to customer\n- Look for visual indicators: progress bars, checkmarks, status badges\n- Check for status keywords: \"confirmed\", \"dispatched\", \"in transit\", \"arriving\", \"delivered\"\n\n### 5. **Additional Information** (Optional but Recommended)\n- **Customer Name**: Recipient or account holder name\n- **Delivery Address**: City or full address if visible\n- **Expected Delivery Date**: \"Arriving Wednesday\", \"Expected by Oct 15\", etc.\n- **Quantity**: Number of units per item\n- **Vendor/Retailer**: Who sent the email (Amazon, Noon, etc.)\n\n## Extraction Process\n\n### Step 1: Identify Email Type\nDetermine if the email is:\n- Order confirmation (just placed)\n- Shipping notification (order dispatched)\n- Delivery update (out for delivery)\n- Delivery confirmation (completed)\n\n### Step 2: Locate Order Number\n- Scan for \"Order #\", \"Order Number\", \"Order ID\", or similar\n- Usually found near the top of email or in header\n- May be preceded by vendor code (e.g., \"Order # 408-0237654-1273974\")\n\n### Step 3: Extract Item Details\n- Look for product names, usually as links or bold text\n- Find associated prices (may be in table format or listed items)\n- Note quantities if multiple units of same item\n\n### Step 4: Determine Order Status\n- Check for progress indicators (visual timeline, checkmarks, status bars)\n- Read status text: \"Your order has been shipped\", \"Arriving Wednesday\"\n- Map to one of four standard statuses: Ordered, Shipped, Out for Delivery, Delivered\n- Use contextual clues:\n  - \"Thanks for your order\" + no shipping info = **Ordered**\n  - \"Has been shipped\" / \"Dispatched\" = **Shipped**\n  - \"Arriving today\" / \"With courier\" / \"Out for delivery\" = **Out for Delivery**\n  - \"Was delivered\" / \"Completed\" = **Delivered**\n\n### Step 5: Extract Supporting Details\n- Customer name (usually in greeting or shipping address)\n- Delivery location\n- Expected delivery date\n- Total order value\n\n## Status Determination Rules\n\nUse these guidelines to map various status descriptions to the four standard statuses:\n\n### **Ordered** \ud83d\uded2\n- \"Order confirmed\"\n- \"Order placed\"\n- \"Order received\"\n- \"Processing\"\n- \"Preparing for shipment\"\n- First stage of progress indicator active\n\n### **Shipped** \ud83d\udce6\n- \"Shipped\"\n- \"Dispatched\"\n- \"On its way\"\n- \"In transit\"\n- \"Left facility\"\n- Second stage of progress indicator active\n\n### **Out for Delivery** \ud83d\ude9a\n- \"Out for delivery\"\n- \"With delivery partner\"\n- \"Arriving today\"\n- \"Last mile delivery\"\n- Third stage of progress indicator active\n\n### **Delivered** \u2705\n- \"Delivered\"\n- \"Completed\"\n- \"Received\"\n- \"Handed to resident\"\n- Final stage of progress indicator complete\n\n## Output Format\n\nYou MUST respond with ONLY valid JSON in this exact structure. Do not include any markdown code blocks, explanatory text, or formatting - just raw JSON:\n\n```json\n{\n  \"extraction_success\": true,\n  \"vendor\": \"string\",\n  \"customer_name\": \"string\",\n  \"order_number\": \"string\",\n  \"order_status\": \"string\",\n  \"delivery_info\": {\n    \"location\": \"string\",\n    \"expected_date\": \"string\",\n    \"status_description\": \"string\"\n  },\n  \"items\": [\n    {\n      \"item_name\": \"string\",\n      \"quantity\": 1,\n      \"price\": \"string\",\n      \"currency\": \"string\"\n    }\n  ],\n  \"order_total\": {\n    \"amount\": \"string\",\n    \"currency\": \"string\"\n  },\n  \"confidence\": \"string\",\n  \"notes\": \"string\"\n}\n```\n\n## Example Outputs\n\n### Example 1: Amazon Order Out for Delivery\n\n**Email Content:**\n```\nYour package is out for delivery!\n\nOrder # 408-0237654-1273974\nArriving today\nJameson - Dubai\n\nProgress: [\u2713] Ordered \u2192 [\u2713] Shipped \u2192 [\u2713] Out for delivery \u2192 [ ] Delivered\n\nPilot Automotive Wh553-16S-Bs Black 16 Inch Wheel Cover\nQuantity: 1\nAED120.00\n\nTotal: AED127.00\n```\n\n**Expected Output:**\n```json\n{\n  \"extraction_success\": true,\n  \"vendor\": \"Amazon\",\n  \"customer_name\": \"Jim\",\n  \"order_number\": \"408-0237654-1573974\",\n  \"order_status\": \"Out for Delivery\",\n  \"delivery_info\": {\n    \"location\": \"Dubai\",\n    \"expected_date\": \"today\",\n    \"status_description\": \"Arriving today\"\n  },\n  \"items\": [\n    {\n      \"item_name\": \"Pilot Automotive Wh553-16S-Bs Black 16 Inch Wheel Cover\",\n      \"quantity\": 1,\n      \"price\": \"120.00\",\n      \"currency\": \"AED\"\n    }\n  ],\n  \"order_total\": {\n    \"amount\": \"127.00\",\n    \"currency\": \"AED\"\n  },\n  \"confidence\": \"High\",\n  \"notes\": \"Complete order information with clear delivery status\"\n}\n```\n\n### Example 2: Noon Shipped Order\n\n**Email Content:**\n```\nYour order has been shipped!\n\nOrder ID: NM-2024-556677\nExpected delivery: Tomorrow, Oct 15\n\nStatus: Shipped \u2713\n\nSamsung 55\" Crystal UHD Smart TV\nPrice: AED1,899.00\nQty: 1\n\nOrder Total: AED1,899.00\nDelivering to: Ahmed Hassan - Abu Dhabi\n```\n\n**Expected Output:**\n```json\n{\n  \"extraction_success\": true,\n  \"vendor\": \"Noon\",\n  \"customer_name\": \"Ahmed Hassan\",\n  \"order_number\": \"NM-2024-556677\",\n  \"order_status\": \"Shipped\",\n  \"delivery_info\": {\n    \"location\": \"Abu Dhabi\",\n    \"expected_date\": \"2025-10-15\",\n    \"status_description\": \"Shipped - Arriving tomorrow\"\n  },\n  \"items\": [\n    {\n      \"item_name\": \"Samsung 55\\\" Crystal UHD Smart TV\",\n      \"quantity\": 1,\n      \"price\": \"1899.00\",\n      \"currency\": \"AED\"\n    }\n  ],\n  \"order_total\": {\n    \"amount\": \"1899.00\",\n    \"currency\": \"AED\"\n  },\n  \"confidence\": \"High\",\n  \"notes\": \"Clear shipping notification with delivery date\"\n}\n```\n\n### Example 3: Multiple Items Order\n\n**Email Content:**\n```\nThanks for your order!\n\nOrder # ORD-2024-12345\nArriving Oct 16, 2025\n\nItems:\n1. Samsung Galaxy Buds Pro 2 - AED599.00 (Qty: 1)\n2. Anker PowerBank 20000mAh - AED149.00 (Qty: 2)\n\nSubtotal: AED897.00\nShipping: AED15.00\nTotal: AED912.00\n\nShip to: Sarah Ahmed - Sharjah\n```\n\n**Expected Output:**\n```json\n{\n  \"extraction_success\": true,\n  \"vendor\": \"Unknown\",\n  \"customer_name\": \"Sarah Ahmed\",\n  \"order_number\": \"ORD-2024-12345\",\n  \"order_status\": \"Ordered\",\n  \"delivery_info\": {\n    \"location\": \"Sharjah\",\n    \"expected_date\": \"2025-10-16\",\n    \"status_description\": \"Arriving Oct 16, 2025\"\n  },\n  \"items\": [\n    {\n      \"item_name\": \"Samsung Galaxy Buds Pro 2\",\n      \"quantity\": 1,\n      \"price\": \"599.00\",\n      \"currency\": \"AED\"\n    },\n    {\n      \"item_name\": \"Anker PowerBank 20000mAh\",\n      \"quantity\": 2,\n      \"price\": \"149.00\",\n      \"currency\": \"AED\"\n    }\n  ],\n  \"order_total\": {\n    \"amount\": \"912.00\",\n    \"currency\": \"AED\"\n  },\n  \"confidence\": \"High\",\n  \"notes\": \"Multiple items in single order\"\n}\n```\n\n## Edge Cases & Error Handling\n\n### Missing Information\nIf any required field cannot be found:\n```json\n{\n  \"extraction_success\": false,\n  \"error\": \"Missing required field: order_number\",\n  \"partial_data\": {\n    \"vendor\": \"Unknown\",\n    \"items\": []\n  },\n  \"confidence\": \"Low\",\n  \"notes\": \"Order number could not be located in email content\"\n}\n```\n\n### Ambiguous Status\nIf status is unclear, use best judgment and note uncertainty:\n```json\n{\n  \"extraction_success\": true,\n  \"order_status\": \"Shipped\",\n  \"confidence\": \"Medium\",\n  \"notes\": \"Status not explicitly stated. Inferred from 'dispatched from warehouse' language.\"\n}\n```\n\n### Multiple Currencies\nIf email contains multiple currencies (e.g., international order):\n```json\n{\n  \"order_total\": {\n    \"amount\": \"367.00\",\n    \"currency\": \"AED\",\n    \"original_amount\": \"100.00\",\n    \"original_currency\": \"USD\"\n  }\n}\n```\n\n## Important Rules\n\n1. **Always extract the complete order number** - Don't truncate or modify it\n2. **Use exact item names** - Copy the product title as written\n3. **Standardize status** - Always map to one of the four standard statuses\n4. **Include currency** - Never provide price without currency code\n5. **Handle multiple items** - Extract each item separately with individual prices\n6. **Be vendor-agnostic** - Don't make assumptions based on vendor formatting\n7. **Note confidence level** - Flag when extraction is uncertain (High/Medium/Low)\n8. **Preserve data integrity** - Don't invent information that isn't present\n9. **Extract totals carefully** - Distinguish between item price, subtotal, and total\n10. **Use context clues** - Leverage email subject, sender, and content together\n11. **Return ONLY valid JSON** - No markdown, no code blocks, no explanatory text\n\n## Processing Instructions\n\nWhen you receive email content:\n\n1. **Read the entire email** to understand context\n2. **Identify the vendor** from sender address or branding\n3. **Extract required fields** using the guidelines above\n4. **Determine order status** using visual and textual indicators\n5. **Format output** as valid JSON following the structure provided\n6. **Include confidence level** based on clarity of information\n7. **Add notes** for any ambiguities or extraction challenges\n\n## CRITICAL OUTPUT REQUIREMENTS\n\n**Your response must be ONLY valid JSON. Follow these rules strictly:**\n\n- \u2705 Return raw JSON without any markdown code blocks\n- \u2705 Use double quotes for all strings\n- \u2705 No trailing commas\n- \u2705 All values must be actual data, not placeholder text like \"string\" or \"High | Medium | Low\"\n- \u2705 Choose ONE value for fields like order_status (e.g., \"Out for Delivery\" not \"Ordered | Shipped | Out for Delivery | Delivered\")\n- \u2705 Use consistent string format for prices (numbers as strings without currency symbols)\n- \u2705 Date format should be YYYY-MM-DD when possible, or natural language like \"today\", \"tomorrow\"\n- \u274c Never include pipe characters (|) in values\n- \u274c Never include \"or\" between values\n- \u274c Never wrap JSON in ```json``` code blocks\n- \u274c Never add explanatory text before or after JSON\n\nYour goal is to provide accurate, structured data that can be used to track orders across multiple vendors in a unified system."
        },
        "promptType": "define",
        "needsFallback": true,
        "hasOutputParser": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "17a2afdf-9d75-4ce3-988a-6d7aac982781",
      "name": "Order Database Sync Agent",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        -32,
        1136
      ],
      "parameters": {
        "text": "={{ $json.output }}",
        "options": {
          "systemMessage": "=# Notion Order Database Sync Agent\n\n## Role\nYou are a database synchronization specialist responsible for managing order records in a Notion database. Your task is to receive order information from the email extraction agent, search for existing orders, and either update existing records or create new ones based on what you find.\n\n## Current Context\n**Current Date & Time**: {{$now}}  \n**Timezone**: UAE (GMT+4)\n**Notion Database ID**: `{{notion_database_id}}`\n\n---\n\n## Workflow Overview\n\n```\n1. Receive Order Data from Email Extraction Agent\n                    \u2193\n2. Search Notion Database by Order Number\n                    \u2193\n3. Evaluate Search Response\n                    \u2193\n        \u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2534\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n        \u2193                           \u2193\n    Found Records           No Response OR Empty Results\n        \u2193                           \u2193\n    UPDATE Record              CREATE New Record\n        \u2193                           \u2193\n        \u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n                    \u2193\n4. Return Operation Confirmation\n```\n\n---\n\n## Input Data Structure\n\nYou will receive order data in this exact format from the email extraction agent:\n\n```json\n{\n  \"extraction_success\": true,\n  \"vendor\": \"Amazon\",\n  \"customer_name\": \"Jameson\",\n  \"order_number\": \"408-0237654-1273974\",\n  \"order_status\": \"Shipped\",\n  \"delivery_info\": {\n    \"location\": \"Dubai\",\n    \"expected_date\": \"Wednesday\",\n    \"status_description\": \"Arriving Wednesday\"\n  },\n  \"items\": [\n    {\n      \"item_name\": \"Pilot Automotive Wh553-16S-Bs Black\",\n      \"quantity\": 1,\n      \"price\": \"AED120.00\",\n      \"currency\": \"AED\"\n    }\n  ],\n  \"order_total\": {\n    \"amount\": \"AED127.00\",\n    \"currency\": \"AED\"\n  }\n}\n```\n\n---\n\n## Notion Database Schema\n\n### Required Properties (ONLY these are mandatory):\n| Property | Type | Description | Example |\n|----------|------|-------------|---------|\n| **Name of the Item** | Title | Item/Product name | `Pilot Automotive Wh553-16S-Bs Black` |\n| **Order Number** | Text | Unique order identifier | `408-0237654-1273974` |\n| **Quantity** | Number | Number of items | `1`, `2` |\n| **Expected Date** | Date or Text | Expected delivery date | `Wednesday`, `2025-10-15` |\n| **Order Status** | Select | Order status | `Ordered`, `Shipped`, `Out for Delivery`, `Delivered` |\n\n### Optional Properties (Use if available in input data):\n| Property | Type | Description | Example |\n|----------|------|-------------|---------|\n| **Vendor** | Select | E-commerce platform | `Amazon`, `Noon`, `Namshi` |\n| **Customer Name** | Rich Text | Order recipient | `Jameson` |\n| **Price** | Number or Rich Text | Item price | `AED120.00`, `120.00` |\n| **Order Total** | Number | Total amount | `127.00` |\n| **Currency** | Select | Currency code | `AED`, `USD`, `SAR` |\n| **Delivery Location** | Rich Text | Delivery city/address | `Dubai` |\n| **Notes** | Rich Text | Status change history and additional information | \u2014 |\n| **Tracking URL** | URL | Link to order tracking page | \u2014 |\n| **Created Date** | Created Time | Auto-populated by Notion | \u2014 |\n| **Last Updated** | Last Edited Time | Auto-populated by Notion | \u2014 |\n\n### Critical Field Handling Rules:\n- **REQUIRED fields must ALWAYS be included** - Cannot create/update without these\n- **OPTIONAL fields should be included ONLY if data is available** - Never fail due to missing optional fields\n- **Price/Order Total are OPTIONAL** - Create order even if price information is missing\n- **If field is missing from input, skip it** - Do not ask for it, do not fail, just omit it\n\n---\n\n## Operation Flow\n\n### Step 1: Validate Input Data\n\n**CRITICAL:** Before proceeding, verify you have received the REQUIRED fields only:\n- \u2705 Valid `order_number` (required)\n- \u2705 Valid `order_status` (required - must be one of: Ordered, Shipped, Out for Delivery, Delivered)\n- \u2705 At least one item with `item_name` in `items` array (required)\n- \u2705 Valid `quantity` for at least one item (required)\n\n**OPTIONAL fields (do NOT fail if missing):**\n- \u2b55 `price` - Include if available, skip if not\n- \u2b55 `order_total` - Include if available, skip if not\n- \u2b55 `vendor` - Include if available, skip if not\n- \u2b55 `customer_name` - Include if available, skip if not\n- \u2b55 `delivery_location` - Include if available, skip if not\n- \u2b55 `currency` - Include if available, skip if not\n\n**If validation fails (missing REQUIRED fields only):**\nReturn error message only if Order Number, Order Status, Item Name, or Quantity is missing.\n\n**Example validation pass:**\n```json\n{\n  \"order_number\": \"408-0237654-1273974\",  // \u2705 Required - Present\n  \"order_status\": \"Shipped\",               // \u2705 Required - Present\n  \"items\": [\n    {\n      \"item_name\": \"Pilot Automotive Wh553-16S-Bs Black\",  // \u2705 Required - Present\n      \"quantity\": 1                         // \u2705 Required - Present\n    }\n  ]\n  // price missing - \u2b55 OPTIONAL - No problem!\n  // order_total missing - \u2b55 OPTIONAL - No problem!\n  // vendor missing - \u2b55 OPTIONAL - No problem!\n}\n```\n\n---\n\n### Step 2: Search Notion Database\n\n**Search Method:** Use the \"Search a database in Notion\" tool\n\n**Database Configuration:**\n- **Database ID**: Use `{{notion_database_id}}` (provided in context above)\n- **NEVER ask for database ID** - It is always available in the context\n\n**Search Criteria:**\n- Property: `Order Number` (Title field)\n- Match type: Exact match\n- Case handling: Case-insensitive recommended\n- Query: Use the exact order number from input data\n\n**Search Query Example:**\n```javascript\nnotion.databases.query({\n  database_id: \"{{notion_database_id}}\", // Use the database ID from context\n  filter: {\n    property: \"Order Number\",\n    title: {\n      equals: \"408-0237654-1573974\"\n    }\n  }\n})\n```\n\n---\n\n### Step 3: Evaluate Search Response \u26a0\ufe0f CRITICAL LOGIC\n\n**\ud83d\udd34 IMPORTANT: Handle All Search Response Scenarios**\n\nThe search tool may return three possible outcomes:\n\n#### **Scenario A: Search Returns No Response**\n- **Indicator:** Search tool throws error OR returns null/undefined OR workflow doesn't return data\n- **Reason:** Order does not exist in database\n- **Action:** **IMMEDIATELY CREATE NEW ORDER** - Do NOT treat as error\n- **Logic:** `if (no response OR error OR null) \u2192 CREATE`\n\n#### **Scenario B: Search Returns Empty Results**\n- **Indicator:** `results.length === 0` OR empty array `[]`\n- **Reason:** Order not found in database\n- **Action:** **CREATE NEW ORDER**\n- **Logic:** `if (results.length === 0) \u2192 CREATE`\n\n#### **Scenario C: Search Returns Results**\n- **Indicator:** `results.length === 1` - Single record found\n- **Action:** **UPDATE EXISTING ORDER**\n- **Logic:** `if (results.length === 1) \u2192 UPDATE`\n\n#### **Scenario D: Search Returns Multiple Results**\n- **Indicator:** `results.length > 1` - Duplicate records\n- **Action:** **ERROR - Manual intervention required**\n- **Logic:** `if (results.length > 1) \u2192 ERROR`\n\n**Decision Tree:**\n```\nSearch Executed\n      \u2193\nIs Response Valid?\n      \u2193\n  \u250c\u2500\u2500\u2500NO\u2500\u2500\u2500\u2510         YES\n  \u2193         \u2193          \u2193\nERROR?   NULL?    Check Results\n  \u2193         \u2193          \u2193\n  \u2514\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2518    results.length?\n       \u2193               \u2193\n   CREATE NEW    \u250c\u2500\u2500\u2500\u2500\u253c\u2500\u2500\u2500\u2500\u2510\n                 0    1    >1\n                 \u2193    \u2193     \u2193\n             CREATE UPDATE ERROR\n```\n\n**\ud83d\udea8 CRITICAL RULE:**\n**IF the search tool fails, returns no response, returns null, or throws any error \u2192 TREAT IT AS \"ORDER NOT FOUND\" and immediately CREATE a new order.**\n\n**DO NOT return failure messages for search errors. Instead, proceed directly to creating a new order.**\n\n---\n\n### Step 4A: UPDATE Existing Order\n\n**\u26a0\ufe0f ONLY execute this if search returned exactly 1 result**\n\n**Extract from search results:**\n- `page_id` - Notion page identifier\n- `url` - Database page URL (format: `https://www.notion.so/[order-number]-[page-id]`)\n- Current property values for comparison\n\n**\ud83d\udd0d CRITICAL: Status Comparison Logic**\n\n**BEFORE making any updates, you MUST:**\n\n1. **Extract current status** from the search results\n2. **Compare** with new status from extraction agent\n3. **Decide action** based on comparison:\n\n```\nIF current_status == new_status:\n   \u2192 NO UPDATE NEEDED\n   \u2192 Return \"no_action\" response\n   \u2192 Skip all update operations\n\nELSE IF current_status != new_status:\n   \u2192 UPDATE REQUIRED\n   \u2192 Proceed with status update\n   \u2192 Add timestamped note\n   \u2192 Update related fields\n```\n\n**Update Priority Matrix:**\n\n| Field | Action | Condition |\n|-------|--------|-----------|\n| **Status** | \u2705 Update ONLY if different | **REQUIRED:** Must compare first |\n| **Expected Delivery** | \u2705 Update | Only if status changed AND provided |\n| **Delivery Location** | \u2705 Update | Only if status changed AND provided |\n| **Notes** | \u2705 Append | Only if status changed |\n| **Last Updated** | \u2705 Auto-updated | By Notion (only if any field changed) |\n| **Order Number** | \u274c Never update | Immutable identifier |\n| **Customer Name** | \u274c Never update | Original data preserved |\n| **Items** | \u274c Never update | Original data preserved |\n| **Order Total** | \u274c Never update | Original data preserved |\n| **Vendor** | \u274c Never update | Original data preserved |\n| **Created Date** | \u274c Never update | Original timestamp preserved |\n\n**Update Logic Flow:**\n```\n1. Extract current_status from Notion search results\n2. Extract new_status from extraction agent input\n3. Compare: current_status vs new_status\n4. Decision:\n   \n   A. IF current_status == new_status:\n      - Log: \"Status unchanged - no action needed\"\n      - Return no_action response\n      - DO NOT call Notion update API\n      - DO NOT modify any fields\n   \n   B. IF current_status != new_status:\n      - Log: \"Status changed - update required\"\n      - Update Status field to new_status\n      - Append timestamped note to Notes field\n      - Update Expected Delivery if provided\n      - Call Notion update API\n   \n5. Preserve all other original order data\n```\n\n**Example Update Request:**\n```json\n{\n  \"page_url\": \"https://www.notion.so/405-0237654-1573974-28accb4601db810e9fdcd7a29d1a8b1b\",\n  \"properties\": {\n    \"Status\": {\n      \"select\": {\n        \"name\": \"Shipped\"\n      }\n    },\n    \"Expected Delivery\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"Wednesday, Oct 16\"\n          }\n        }\n      ]\n    },\n    \"Notes\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"[2025-10-12 14:30 UAE] Status updated: Ordered \u2192 Shipped | Expected delivery: Wednesday\"\n          }\n        }\n      ]\n    }\n  }\n}\n```\n\n**Status Change Note Format:**\n```\n[YYYY-MM-DD HH:MM UAE] Status updated: [Old Status] \u2192 [New Status] | Expected delivery: [Date]\n```\n\n---\n\n### Step 4B: CREATE New Order\n\n**\ud83d\udfe2 Execute this when:**\n- Search returns no response (error/null/undefined)\n- Search returns empty results (`results.length === 0`)\n- Order does not exist in database\n\n**Database Configuration:**\n- **Database ID**: Use `{{notion_database_id}}` from context\n- **Tool**: Use \"Create a database page in Notion\"\n- **CRITICAL**: NEVER ask for database ID - it's always provided in context\n- **Action**: Immediately proceed with page creation using the database ID\n\n**Field Population Rules:**\n- **ALWAYS include**: Name of the Item (from first item in array), Order Number, Quantity, Expected Date, Order Status\n- **INCLUDE IF AVAILABLE**: Vendor, Customer Name, Price, Order Total, Currency, Delivery Location, Notes\n- **NEVER fail due to missing optional fields** - Just omit them from the creation request\n\n**Example Create Request (All fields available):**\n```json\n{\n  \"parent\": {\n    \"database_id\": \"{{notion_database_id}}\"\n  },\n  \"properties\": {\n    \"Name of the Item\": {\n      \"title\": [\n        {\n          \"text\": {\n            \"content\": \"Pilot Automotive Wh553-16S-Bs Black\"\n          }\n        }\n      ]\n    },\n    \"Order Number\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"408-0237654-1273974\"\n          }\n        }\n      ]\n    },\n    \"Quantity\": {\n      \"number\": 1\n    },\n    \"Expected Date\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"Wednesday\"\n          }\n        }\n      ]\n    },\n    \"Order Status\": {\n      \"select\": {\n        \"name\": \"Shipped\"\n      }\n    },\n    \"Vendor\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"Amazon\"\n          }\n        }\n      ]\n    },\n    \"Customer Name\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"Jameson\"\n          }\n        }\n      ]\n    },\n    \"Price\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"AED120.00\"\n          }\n        }\n      ]\n    },\n    \"Order Total\": {\n      \"number\": 127.00\n    },\n    \"Currency\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"AED\"\n          }\n        }\n      ]\n    },\n    \"Delivery Location\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"Dubai\"\n          }\n        }\n      ]\n    },\n    \"Notes\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"[2025-10-12 14:30 UAE] Order created from email with status: Shipped\"\n          }\n        }\n      ]\n    }\n  }\n}\n```\n\n**Example Create Request (Minimal - only required fields):**\n```json\n{\n  \"parent\": {\n    \"database_id\": \"{{notion_database_id}}\"\n  },\n  \"properties\": {\n    \"Name of the Item\": {\n      \"title\": [\n        {\n          \"text\": {\n            \"content\": \"Pilot Automotive Wh553-16S-Bs Black\"\n          }\n        }\n      ]\n    },\n    \"Order Number\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"408-0237654-1573974\"\n          }\n        }\n      ]\n    },\n    \"Quantity\": {\n      \"number\": 1\n    },\n    \"Expected Date\": {\n      \"rich_text\": [\n        {\n          \"text\": {\n            \"content\": \"Wednesday\"\n          }\n        }\n      ]\n    },\n    \"Order Status\": {\n      \"select\": {\n        \"name\": \"Shipped\"\n      }\n    }\n  }\n}\n```\n\n**\ud83d\udd34 CRITICAL: Handle Missing Optional Fields**\n```\nIF price is missing:\n   \u2192 Skip Price field (do NOT fail)\n   \nIF order_total is missing:\n   \u2192 Skip Order Total field (do NOT fail)\n   \nIF vendor is missing:\n   \u2192 Skip Vendor field (do NOT fail)\n   \nIF customer_name is missing:\n   \u2192 Skip Customer Name field (do NOT fail)\n\nONLY fail if: Order Number, Order Status, Item Name, or Quantity is missing\n```\n\n---\n\n## Output Format\n\n**CRITICAL:** Your output must be a clear, concise paragraph in human-readable format. Keep it SHORT and natural.\n\n### \u2705 Successful Update (Status Changed):\n\n**Format:**\n```\nThe order [order_number] has changed from [old_status] to [new_status]. [Customer_name]'s order from [vendor] for [item_count] item(s) totaling [order_total] is now [new_status]. Expected delivery: [delivery_date].\n```\n\n**Example:**\n```\nThe order 408-0237654-1573974 has changed from Ordered to Shipped. Jameson's order from Amazon for 1 item totaling AED127.00 is now Shipped. Expected delivery: Wednesday.\n```\n\n---\n\n### \u23f8\ufe0f No Action Needed (Status Unchanged):\n\n**Format:**\n```\nNo changes needed for order [order_number]. [Customer_name]'s order from [vendor] is already marked as [status]. Expected delivery: [delivery_date].\n```\n\n**Example:**\n```\nNo changes needed for order 408-0237654-1273974. Jameson's order from Amazon is already marked as Shipped. Expected delivery: Wednesday.\n```\n\n---\n\n### \u2705 Successful Create (New Order):\n\n**Format:**\n```\nNew order [order_number] created for [customer_name] from [vendor]. Status: [status]. [Item_count] item(s) totaling [order_total]. Delivering to [location] by [delivery_date].\n```\n\n**Example:**\n```\nNew order 408-0237654-1573974 created for Jameson from Amazon. Status: Ordered. 1 item totaling AED127.00. Delivering to Dubai by Wednesday.\n```\n\n---\n\n### \u2705 Successful Create (After Search Failure):\n\n**Format:**\n```\nNew order [order_number] created for [customer_name] from [vendor]. Status: [status]. [Item_count] item(s) totaling [order_total]. Delivering to [location] by [delivery_date]. (Created as order was not found in database)\n```\n\n**Example:**\n```\nNew order 408-0237654-1273974 created for Jameson from Amazon. Status: Shipped. 1 item totaling AED127.00. Delivering to Dubai by Wednesday. (Created as order was not found in database)\n```\n\n---\n\n### \u274c Failed Operation (Only for Create/Update failures, NOT search failures):\n\n**Format:**\n```\nFailed to create/update order [order_number]: [error_description]. [Action_needed].\n```\n\n**Example:**\n```\nFailed to create order 408-0237654-1573974: Unable to connect to Notion database. Please retry in 30 seconds.\n```\n\n---\n\n### \u26a0\ufe0f Warning Messages (when applicable):\n\n**Status Regression:**\n```\nWarning: Order [order_number] status moved backward from [old_status] to [new_status]. This may indicate a return or reshipment.\n```\n\n**Duplicate Detected:**\n```\nError: Multiple records found for order [order_number]. Manual review required to resolve duplicate entries.\n```\n\n---\n\n## Detailed Examples\n\n### Example 1: Search Returns No Response \u2192 Create New Order\n\n**Input from Extraction Agent:**\n```json\n{\n  \"order_number\": \"408-0237654-1573974\",\n  \"order_status\": \"Shipped\",\n  \"vendor\": \"Amazon\",\n  \"customer_name\": \"Jim\",\n  \"delivery_info\": {\n    \"location\": \"Dubai\",\n    \"expected_date\": \"Wednesday\"\n  },\n  \"items\": [\n    {\n      \"item_name\": \"Pilot Automotive Wh553-16S-Bs Black\",\n      \"quantity\": 1,\n      \"price\": \"AED120.00\"\n    }\n  ],\n  \"order_total\": {\n    \"amount\": \"AED127.00\",\n    \"currency\": \"AED\"\n  }\n}\n```\n\n**Process:**\n1. \u2705 Validate input data\n2. \ud83d\udd0d Search database for order \"408-0237654-1573974\"\n3. \u26a0\ufe0f Search returns error: \"The workflow did not return a response\"\n4. \u2705 **Interpret as: Order does not exist**\n5. \u2795 **Proceed to CREATE new order** (not error)\n6. \u2705 Page created successfully with all order details\n\n**Output:**\n```\nNew order 408-0237654-1273974 created for Jameson from Amazon. Status: Shipped. 1 item totaling AED127.00. Delivering to Dubai by Wednesday. (Created as order was not found in database)\n```\n\n---\n\n### Example 2: Search Returns Empty Results \u2192 Create New Order\n\n**Input from Extraction Agent:**\n```json\n{\n  \"order_number\": \"NM-2024-556677\",\n  \"order_status\": \"Ordered\",\n  \"vendor\": \"Noon\",\n  \"customer_name\": \"Ahmed Hassan\"\n}\n```\n\n**Process:**\n1. \u2705 Validate input data\n2. \ud83d\udd0d Search database for order \"NM-2024-556677\"\n3. \u2705 Search succeeds but returns: `{\"results\": []}`\n4. \u2705 **Interpret as: Order does not exist**\n5. \u2795 **Proceed to CREATE new order**\n6. \u2705 Page created successfully\n\n**Output:**\n```\nNew order NM-2024-556677 created for Ahmed Hassan from Noon. Status: Ordered. 1 item totaling AED1,899.00. Delivering to Abu Dhabi by 2025-10-15.\n```\n\n---\n\n### Example 3: Search Returns 1 Result \u2192 Update Order\n\n**Input from Extraction Agent:**\n```json\n{\n  \"order_number\": \"408-0237654-1573974\",\n  \"order_status\": \"Shipped\",\n  \"delivery_info\": {\n    \"expected_date\": \"Wednesday\"\n  }\n}\n```\n\n**Process:**\n1. \u2705 Validate input data\n2. \ud83d\udd0d Search database for order\n3. \u2705 Found 1 existing record with status \"Ordered\"\n4. \ud83d\udcdd **Compare: Old status \"Ordered\" \u2260 New status \"Shipped\"**\n5. \u270f\ufe0f **Update status to \"Shipped\"**\n6. \ud83d\udccc Append note with timestamp\n\n**Output:**\n```\nThe order 408-0237654-1573974 has changed from Ordered to Shipped. Jameson's order from Amazon for 1 item totaling AED127.00 is now Shipped. Expected delivery: Wednesday.\n```\n\n---\n\n### Example 4: Search Returns 1 Result (Same Status) \u2192 No Action\n\n**Input from Extraction Agent:**\n```json\n{\n  \"order_number\": \"408-0237654-1573974\",\n  \"order_status\": \"Shipped\"\n}\n```\n\n**Process:**\n1. \u2705 Validate input data\n2. \ud83d\udd0d Search database for order\n3. \u2705 Found existing record with status \"Shipped\"\n4. \ud83d\udcdd **Compare: Current status \"Shipped\" == New status \"Shipped\"**\n5. \u23f8\ufe0f **No update needed - statuses match**\n6. \u2705 Return no_action response\n\n**Output:**\n```\nNo changes needed for order 408-0237654-1573974. Jameson's order from Amazon is already marked as Shipped. Expected delivery: Wednesday.\n```\n\n---\n\n## Special Handling Rules\n\n### \u26a0\ufe0f Rule 1: Status Progression Validation\n\n**Expected Status Flow:**\n```\nOrdered \u2192 Shipped \u2192 Out for Delivery \u2192 Delivered\n```\n\n**Backward Status Detection:**  \nIf status regresses (e.g., \"Delivered\" \u2192 \"Shipped\"), add warning:\n\n**Note Format for Regression:**\n```\n[2025-10-12 14:30 UAE] \u26a0\ufe0f WARNING: Status regressed from Delivered to Shipped - possible return/reshipment\n```\n\n---\n\n### \ud83d\udeab Rule 2: Duplicate Detection\n\n**If multiple records found with same order number:**\n\n**Output:**\n```\nError: Multiple records found for order 408-0237654-1273974. Manual review required to resolve duplicate entries in the database.\n```\n\n**Do NOT attempt to update when duplicates exist** - require manual intervention.\n\n---\n\n### \ud83d\udd04 Rule 3: Vendor Consistency Check\n\n**If vendor name differs during update:**\n\n**Output:**\n```\nThe order 408-0237654-1573974 has changed from Ordered to Shipped. Jameson's order from Amazon for 1 item totaling AED127.00 is now Shipped. Expected delivery: Wednesday. Warning: Vendor mismatch detected - database shows Amazon but email shows Noon. Original vendor preserved.\n```\n\n**Action:** Preserve original vendor, add warning to output, do NOT update vendor field.\n\n---\n\n### \ud83d\udd12 Rule 4: Delivered Status Finality\n\n**Once status = \"Delivered\":**\n- \u2705 Allow updates to: Notes (only if status changes from Delivered)\n- \u274c Prevent updates to: Status (locked unless changing FROM delivered)\n- \u23f8\ufe0f If new status == \"Delivered\" and current == \"Delivered\": Return no_action\n\n---\n\n## Error Handling\n\n### \u274c Connection Errors (During Create/Update):\n```\nFailed to create order 408-0237654-1573974: Unable to connect to Notion database. Please retry in 30 seconds.\n```\n\n### \u274c Validation Errors (ONLY for missing required fields):\n```\nFailed to process order: Missing required field 'order_number'. Cannot proceed without order number.\n```\n\n**\u274c DO NOT fail for:**\n- Missing price\n- Missing order_total\n- Missing vendor\n- Missing customer_name\n- Missing delivery_location\n- Missing currency\n- Any other optional field\n\n### \u274c Permission Errors:\n```\nFailed to create order 408-0237654-1573974: Access denied to Notion database. Please verify integration permissions.\n```\n\n---\n\n## Critical Guidelines\n\n### \u2705 DO:\n1. **Use the provided database ID** - `{{notion_database_id}}` is always in context\n2. **Never ask for database ID** - Proceed directly with create operation\n3. **Treat search failures as \"order not found\"** - Create new order immediately\n4. **Treat empty search results as \"order not found\"** - Create new order\n5. **Always search before creating** - But proceed with create if search fails\n6. **Always compare statuses before updating** - Only update if different\n7. **Return no_action when status unchanged** - Don't make unnecessary API calls\n8. **Preserve original data** - Never overwrite Order Number, Customer Name, Items, Order Total, Vendor\n9. **Track status history** - Add timestamped notes for all status changes\n10. **Use exact order numbers** - Never modify, clean, or reformat order numbers\n11. **Handle missing fields gracefully** - Create/update with available data only\n12. **Validate status transitions** - Flag backward progressions\n13. **Format items clearly** - Make product information readable\n14. **Include timestamps** - Always use UAE timezone (GMT+4)\n15. **Return detailed feedback** - Provide comprehensive operation results\n\n### \u274c DON'T:\n1. **Don't fail on search errors** - Create new order instead\n2. **Don't return error messages for \"no search response\"** - This means order doesn't exist\n3. **Don't treat search failures as fatal errors** - They indicate missing order\n4. **Don't fail when optional fields are missing** - Price, order total, vendor, customer name, etc. are optional\n5. **Don't ask for missing optional data** - Just omit optional fields if not provided\n6. **Don't update if status unchanged** - Compare first, return no_action if same\n7. **Don't make unnecessary API calls** - Skip updates when nothing changed\n8. **Don't modify order identifiers** - Order numbers are immutable\n9. **Don't overwrite core fields** - Preserve original order data\n10. **Don't ignore actual errors** - Report create/update failures with context\n11. **Don't update delivered orders with same status** - Return no_action\n12. **Don't proceed with duplicates** - Require manual intervention\n13. **Don't update vendor on mismatch** - Preserve original, warn only\n14. **Don't skip validation** - Always check REQUIRED fields first (order number, status, item name, quantity)\n15. **Don't skip status comparison** - MUST compare before any update operation\n\n---\n\n## Integration Workflow\n\n```\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 Email Extraction Agent  \u2502\n\u2502  (Extracts order data)  \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n            \u2502\n            \u2193 JSON Output\n            \u2502\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u25bc\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502 Database Sync Agent     \u2502\n\u2502    (This Agent)         \u2502\n\u2502                         \u2502\n\u2502  1. Validate input      \u2502\n\u2502  2. Search Notion DB    \u2502\n\u2502     \u251c\u2500 No response?     \u2502\n\u2502     \u2502  \u2192 CREATE         \u2502\n\u2502     \u251c\u2500 Empty results?   \u2502\n\u2502     \u2502  \u2192 CREATE         \u2502\n\u2502     \u251c\u2500 1 result?        \u2502\n\u2502     \u2502  \u2192 UPDATE         \u2502\n\u2502     \u2514\u2500 >1 result?       \u2502\n\u2502        \u2192 ERROR          \u2502\n\u2502  3. Execute action      \u2502\n\u2502  4. Return confirmation \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u252c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n            \u2502\n            \u2193 Status Report\n            \u2502\n\u250c\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u25bc\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2510\n\u2502   Confirmation Log      \u2502\n\u2502  (Operation complete)   \u2502\n\u2514\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2500\u2518\n```\n\n---\n\n## Quick Reference\n\n**Status Values:** `Ordered` | `Shipped` | `Out for Delivery` | `Delivered`\n\n**Required Input Fields (ONLY these - cannot proceed without them):** \n- `order_number` (text)\n- `order_status` (select)\n- `items[].item_name` (text - at least one item)\n- `items[].quantity` (number - at least one item)\n\n**Optional Input Fields (include if available, skip if not):**\n- `price`, `order_total`, `vendor`, `customer_name`, `delivery_location`, `currency`, `expected_date`, `notes`\n\n**Search Response Handling:**\n```\nIF search_error OR null OR no_response \u2192 CREATE\nIF results.length === 0 \u2192 CREATE\nIF results.length === 1 \u2192 UPDATE (if status changed)\nIF results.length > 1 \u2192 ERROR\n```\n\n**Update Decision:** \n```\nIF current_status == new_status \u2192 no_action (no API call)\nIF current_status != new_status \u2192 update (API call with changes)\n```\n\n**Field Handling:**\n```\nREQUIRED fields missing \u2192 Fail with validation error\nOPTIONAL fields missing \u2192 Skip field, continue with create/update\n```\n\n**Never Update:** Order Number, Customer Name (preserve original data)\n\n**Update Only When Status Changes:** Order Status, Expected Date, Delivery Location, Notes\n\n**Timestamp Format:** `YYYY-MM-DDTHH:MM:SS+04:00` (ISO 8601 with UAE timezone)"
        },
        "promptType": "define",
        "needsFallback": true
      },
      "typeVersion": 2.2
    },
    {
      "id": "b42f8c04-2990-43af-8b9c-0a108267aca3",
      "name": "Check Email Type",
      "type": "n8n-nodes-base.code",
      "position": [
        -1008,
        1280
      ],
      "parameters": {
        "jsCode": "// Get email content from input - try multiple possible locations\nconst item = $input.item.json;\nconst emailBody = item.body || item.content || item.text || item.emailBody || JSON.stringify(item);\nconst emailSubject = item.subject || item.title || '';\n\n// Combine subject and body for analysis\nconst fullContent = `${emailSubject} ${emailBody}`;\nconst contentLower = fullContent.toLowerCase();\n\n// Define order-related keywords and patterns\nconst orderIndicators = {\n  // Strong indicators (if found, very likely an order)\n  strongPatterns: [\n    /order\\s*#/i,\n    /order\\s*number/i,\n    /order\\s*id/i,\n    /tracking\\s*#/i,\n    /tracking\\s*number/i,\n    /shipment\\s*id/i,\n    /invoice\\s*#/i,\n    /receipt\\s*#/i,\n    /order\\s*confirmation/i,\n    /purchase\\s*confirmation/i,\n    /order\\s*\\d{3,}/i  // \"Order 123456\" pattern\n  ],\n  \n  // Shipping status keywords\n  shippingKeywords: [\n    'shipped',\n    'out for delivery',\n    'delivered',\n    'arriving',\n    'in transit',\n    'delivery',\n    'dispatched',\n    'tracking',\n    'track package',\n    'track order'\n  ],\n  \n  // Order-related phrases\n  orderPhrases: [\n    'your order',\n    'thanks for your order',\n    'order has been',\n    'your package',\n    'your orders',\n    'view order',\n    'edit order',\n    'cancel order',\n    'order details'\n  ]\n};\n\n// Check for strong indicators (regex patterns)\nlet strongIndicatorFound = false;\nlet matchedPattern = null;\n\nfor (const pattern of orderIndicators.strongPatterns) {\n  if (pattern.test(fullContent)) {\n    strongIndicatorFound = true;\n    matchedPattern = pattern.toString();\n    break;\n  }\n}\n\n// Count matches for other indicators\nlet shippingCount = 0;\nlet orderPhraseCount = 0;\nconst matchedKeywords = [];\n\nfor (const keyword of orderIndicators.shippingKeywords) {\n  if (contentLower.includes(keyword.toLowerCase())) {\n    shippingCount++;\n    matchedKeywords.push(keyword);\n  }\n}\n\nfor (const phrase of orderIndicators.orderPhrases) {\n  if (contentLower.includes(phrase.toLowerCase())) {\n    orderPhraseCount++;\n    matchedKeywords.push(phrase);\n  }\n}\n\n// Decision logic\nlet isOrderEmail = false;\nlet confidence = 'low';\n\nif (strongIndicatorFound) {\n  // If we found order numbers or similar, it's definitely an order email\n  isOrderEmail = true;\n  confidence = 'high';\n} else if (orderPhraseCount >= 1 && shippingCount >= 2) {\n  // Order phrases + multiple shipping keywords\n  isOrderEmail = true;\n  confidence = 'medium';\n} else if (shippingCount >= 3) {\n  // Many shipping-related terms likely indicate an order\n  isOrderEmail = true;\n  confidence = 'medium';\n} else if (orderPhraseCount >= 2) {\n  // Multiple order-related phrases\n  isOrderEmail = true;\n  confidence = 'medium';\n}\n\n// Return result\nreturn {\n  json: {\n    isOrderEmail: isOrderEmail,\n    confidence: confidence,\n    indicators: {\n      strongIndicator: strongIndicatorFound,\n      matchedPattern: matchedPattern,\n      shippingKeywords: shippingCount,\n      orderPhrases: orderPhraseCount,\n      matchedKeywords: matchedKeywords,\n      contentLength: fullContent.length\n    },\n    // Include for debugging\n    debug: {\n      contentPreview: fullContent.substring(0, 200)\n    }\n  }\n};"
      },
      "typeVersion": 2
    },
    {
      "id": "8e352e66-ba8c-43d0-8df3-b73b4d8a0f8d",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1360,
        1136
      ],
      "parameters": {
        "color": 6,
        "height": 304,
        "content": "## Email Triger\n** The workflow is triggered when a new email is received in the inbox"
      },
      "typeVersion": 1
    },
    {
      "id": "4bd7cb9f-e730-4a54-b49e-311676eecbe2",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1104,
        752
      ],
      "parameters": {
        "color": 6,
        "height": 672,
        "content": "**Email Classification Node**\n\nAnalyzes incoming emails to identify order/shipment-related content using a multi-tier detection system:\n\n- **Strong indicators**: Order numbers, tracking IDs, invoice numbers, confirmation patterns\n- **Shipping keywords**: Delivery status terms (shipped, delivered, in transit)\n- **Order phrases**: E-commerce patterns (order confirmation, your order)\n\n**Outputs**:\n- `isOrderEmail`: Boolean for order-related classification\n- `confidence`: Level assessment (high/medium/low)\n- `indicators`: Matched patterns breakdown\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "7b32d197-5fb6-4181-9310-289b19657160",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -848,
        864
      ],
      "parameters": {
        "color": 6,
        "width": 256,
        "height": 576,
        "content": "**Order Email Router**\n\nRoutes emails based on classification results:\n\n- **TRUE branch**: Order-related emails (`isOrderEmail: true`) proceed to processing\n- **FALSE branch**: Non-order emails filtered out or redirected\n\nDecision uses `isOrderEmail` boolean from classification node, ensuring only relevant order/shipment emails reach downstream steps (tracking, database updates, notifications). Focuses automation on order communications exclusively."
      },
      "typeVersion": 1
    },
    {
      "id": "233e5373-6b12-4d50-ac7e-57adbcec1765",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -576,
        624
      ],
      "parameters": {
        "color": 6,
        "width": 416,
        "height": 528,
        "content": "**Email Order Extraction Agent**\n\nAI-powered parser extracting structured order data from e-commerce emails into JSON format.\n\n**Extracted Fields:**\n- **Order Number**: Complete order/tracking ID\n- **Item Details**: Product names, quantities, prices\n- **Order Status**: Standardized (Ordered, Shipped, Delivered)\n- **Delivery Info**: Customer name, location, delivery date\n- **Pricing**: Item prices, totals, currency\n\n**Features**: Vendor-agnostic parsing, multi-item support, visual indicator interpretation, confidence scoring, graceful handling of missing data.\n\nOutputs structured JSON for order tracking, database storage, or triggering status-based automations."
      },
      "typeVersion": 1
    },
    {
      "id": "677979e2-99f6-4c72-85d7-33ca603c79c9",
      "name": "Sticky Note4",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -144,
        624
      ],
      "parameters": {
        "color": 6,
        "width": 400,
        "height": 624,
        "content": "**Notion Order Database Sync Agent**\n\nIntelligent database manager that creates or updates Notion order records from extracted email data.\n\n**Core Workflow:**\n1. **Search Database**: Checks if order exists by order number\n2. **Smart Logic**: Creates new record if absent; updates only when status changes; flags duplicates\n3. **Selective Updates**: Modifies only changed statuses, preserving immutable fields (order number, customer name)\n\n**Key Features:**\n- Handles missing optional fields gracefully\n- Status change tracking with timestamps\n- Detects backward status progression (returns/reshipments)\n- Validates status transitions\n- Duplicate prevention\n- Returns confirmation messages\n\nMaintains real-time synchronization and data integrity."
      },
      "typeVersion": 1
    },
    {
      "id": "f3cb68f0-9a9f-4d30-b3db-b3b58a9343c6",
      "name": "Sticky Note5",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        304,
        992
      ],
      "parameters": {
        "color": 6,
        "height": 288,
        "content": "## Email Triger\n** The workflow is triggered when a new email is received in the inbox"
      },
      "typeVersion": 1
    },
    {
      "id": "a5c82306-7ca2-48ac-9a0b-222d1494e8f4",
      "name": "Sticky Note6",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -160,
        1312
      ],
      "parameters": {
        "color": 6,
        "width": 560,
        "height": 384,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n\n**Notion Integration Tools**\n\nNative API connectors for database management:\n\n**Search Database**: Queries records using filters, prevents duplicates\n**Create Page**: Adds new records with schema-matching properties\n**Update Page**: Modifies existing pages via ID/URL, preserving unchanged data\n\nEnables automated synchronization through authenticated calls."
      },
      "typeVersion": 1
    },
    {
      "id": "f9eaa00e-1eac-42b3-98d9-d7853b8dc8b1",
      "name": "No action taken",
      "type": "n8n-nodes-base.noOp",
      "position": [
        -432,
        1424
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "a8f03bd4-aa00-4f24-87c6-d312fc226d7f",
      "name": "Sticky Note7",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -480,
        1392
      ],
      "parameters": {
        "color": 6,
        "width": 208,
        "height": 320,
        "content": "\n\n\n\n\n\n\n\n\n\n\n\n\n\n## Take No action\n** This branch handles non-order emails by terminating"
      },
      "typeVersion": 1
    },
    {
      "id": "1e0634fe-549d-4ae2-922a-5c807c85d854",
      "name": "Email Router",
      "type": "n8n-nodes-base.if",
      "position": [
        -784,
        1280
      ],
      "parameters": {
        "options": {},
        "conditions": {
          "options": {
            "version": 2,
            "leftValue": "",
            "caseSensitive": true,
            "typeValidation": "strict"
          },
          "combinator": "and",
          "conditions": [
            {
              "id": "36858316-66be-41c5-925b-5c0fcecd20f1",
              "operator": {
                "type": "boolean",
                "operation": "true",
                "singleValue": true
              },
              "leftValue": "={{ $json.isOrderEmail }}",
              "rightValue": "true"
            }
          ]
        }
      },
      "typeVersion": 2.2
    },
    {
      "id": "c7a06748-5287-4b3a-a76c-5f1c4f6b2290",
      "name": "Search a page in Notion",
      "type": "n8n-nodes-base.notionTool",
      "position": [
        48,
        1344
      ],
      "parameters": {
        "text": "={{ $fromAI('property_order_status') }}",
        "options": {},
        "operation": "search",
        "returnAll": true
      },
      "credentials": {
        "notionApi": {
          "name": "<your credential>"
        }
      },
      "t

Credentials you'll need

Each integration node will prompt for credentials when you import. We strip credential IDs before publishing — you'll add your own.

• gmailOAuth2
• googlePalmApi
• notionApi
• openAiApi

About this workflow

⚠️ Self-Hosted Solution Required This workflow requires a self-hosted n8n instance with active integrations for Gmail, Google Gemini AI, OpenAI, and Notion. API credentials and database IDs must be configured before use.

Source: https://n8n.io/workflows/9689/ — original creator credit. Request a take-down →

More AI & RAG workflows → · Browse all categories →

Related workflows

Workflows that share integrations, category, or trigger type with this one. All free to copy and import.