Generate Pro Construction Quotes From Jotform to Email with Supabase CRM

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 →

{
  "meta": {
    "templateCredsSetupCompleted": true
  },
  "nodes": [
    {
      "id": "91382688-a0fe-4742-8ef3-5ea6ae60cf7a",
      "name": "Webhook",
      "type": "n8n-nodes-base.webhook",
      "position": [
        -2220,
        480
      ],
      "parameters": {
        "path": "b7ad1332-8112-4ba1-jotform",
        "options": {},
        "httpMethod": "POST"
      },
      "typeVersion": 2
    },
    {
      "id": "e34d7b7d-2f72-4219-955f-0b9cf0f7767c",
      "name": "Split Out",
      "type": "n8n-nodes-base.splitOut",
      "position": [
        360,
        520
      ],
      "parameters": {
        "include": "selectedOtherFields",
        "options": {},
        "fieldToSplitOut": "lines",
        "fieldsToInclude": "estimate_id"
      },
      "typeVersion": 1
    },
    {
      "id": "79089f76-5979-40da-8413-294ab2c36a9f",
      "name": "upsert form submission -error",
      "type": "n8n-nodes-base.stopAndError",
      "position": [
        -1500,
        340
      ],
      "parameters": {
        "errorType": "errorObject",
        "errorObject": "={{\n  {\n    failed_node: \"upsert form submission\",\n    error_message: $json.error || \"Failed to save form submission\",\n    submission_id: $('Webhook').item.json.body.submissionID || \"unknown\",\n    form_id: $('Webhook').item.json.body.formID || \"unknown\",\n    timestamp: new Date().toISOString(),\n    execution_id: $execution.id\n  }\n}}"
      },
      "typeVersion": 1
    },
    {
      "id": "274e1421-0331-4c7b-9f12-18f56f7f693b",
      "name": "upsert form customer -error",
      "type": "n8n-nodes-base.stopAndError",
      "position": [
        -760,
        720
      ],
      "parameters": {
        "errorType": "errorObject",
        "errorObject": "={{\n  {\n    failed_node: \"upsert form submission\",\n    error_message: $json.error || \"Node execution failed\",\n    submission_id: $('Webhook').item.json.body.submissionID || \"unknown\",\n    timestamp: new Date().toISOString(),\n    execution_id: $execution.id\n  }\n}}"
      },
      "typeVersion": 1
    },
    {
      "id": "91a02c0c-0d25-461b-b4fd-b4bb4b383e73",
      "name": "When clicking \u2018Test workflow\u2019",
      "type": "n8n-nodes-base.manualTrigger",
      "disabled": true,
      "position": [
        -3020,
        1120
      ],
      "parameters": {},
      "typeVersion": 1
    },
    {
      "id": "da86a862-9673-4403-a53d-2e11c76a92dc",
      "name": "Stage 1: Webhook Processing",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1880,
        -320
      ],
      "parameters": {
        "color": 4,
        "width": 600,
        "height": 1220,
        "content": "## \ud83d\udce5 STAGE 1: Form Submission Processing\n\n**Flow:**\nWebhook \u2192 Parser \u2192 Fetch Mapping Rules \u2192 Normalize Form Data \u2192 Save Form Submission\n\n**What happens:**\n1. **Webhook** receives JotForm POST (triggered on form submit)\n2. **Parser** extracts rawRequest, pretty text, and submissionID\n3. **Fetch Mapping Rules** pulls active normalization config from Supabase\n4. **Normalize Form Data** transforms messy input:\n   - \"Glad stucwerk (Saus- of Behangklaar)\" \u2192 `glad_stucwerk`\n   - \"Hoog (2.7-3.5m)\" \u2192 `high`\n   - Nested customer objects \u2192 flat fields\n   - String numbers \u2192 typed numbers\n5. **Save Form Submission** stores raw data with metadata (timestamp, submission_id, time_to_submit)\n\n**Key benefit:**\nConfiguration-driven normalization means adding new form options = SQL INSERT, not code deployment.\n\n**Error handling:**\nIf save fails, workflow stops with contextual error (submission_id, timestamp, failure reason)."
      },
      "typeVersion": 1
    },
    {
      "id": "ea6aae0b-6f27-4163-9dd3-4e0fef2c70ed",
      "name": "Stage 2: CRM Insert",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -1120,
        -320
      ],
      "parameters": {
        "color": 5,
        "width": 600,
        "height": 680,
        "content": "## \ud83d\udc64 STAGE 2: Customer & Deal Creation\n\n**Flow:**\nNormalize Form Data \u2192 Upsert Customer \u2192 Create Deal \u2192 Fetch Pricing Rules\n\n**What happens:**\n1. **Upsert Customer** (idempotent on email):\n   - Stores: name, email, phone, address, billing_address (JSONB)\n   - Links to: form_submission_id\n   - Returns: customer_id (UUID)\n\n2. **Create Deal** (new record per submission):\n   - Deal name: `{property_status} - {postcode}` (e.g., \"nieuwbouw - 9408 EC\")\n   - Status: `lead` (start of sales funnel)\n   - Stores: m2_input (total area), project notes\n   - Links to: customer_id, form_submission_id\n   - Returns: deal_id (UUID)\n\n**Key benefit:**\nComplete lead tracking from first contact \u2192 quote \u2192 (future) job \u2192 invoice. Nothing falls through cracks.\n\n**Error handling:**\nBoth nodes use `continueErrorOutput` - failures trigger dedicated error handlers with full context."
      },
      "typeVersion": 1
    },
    {
      "id": "45ce9276-6205-4082-87eb-ec4c2bd138a5",
      "name": "Stage 3: Quote Generation",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -380,
        -320
      ],
      "parameters": {
        "color": 6,
        "width": 1120,
        "height": 1160,
        "content": "## \ud83d\udcb0 STAGE 3: Quote Calculation Engine\n\n**Flow:**\nCreate Deal \u2192 Fetch Pricing Rules \u2192 Calculate Quote Line Items \u2192 Save Estimate Header \u2192 Prepare Line Item Data \u2192 Split Out \u2192 Insert Line Items\n\n**What happens:**\n1. **Fetch Pricing Rules** pulls ALL active rules from `service_rules_enriched` view:\n   - Each rule: item_code, source_field, trigger conditions, unit_price, VAT rate, priority\n   - Conditions stored as JSONB: `[{\"field\": \"ceiling_height\", \"value\": \"high\", \"operator\": \"equals\"}]`\n\n2. **Calculate Quote Line Items** applies business logic:\n   - Filter rules: `is_active = true` AND `quantity > 0`\n   - Match triggers: Check if customer selected required service type\n   - Validate conditions: Ceiling height, property type, wet area requirements\n   - Calculate: `quantity \u00d7 unit_price \u00d7 price_multiplier`\n   - Apply VAT: Default 21% or rule-specific (9% for certain services)\n   - Group duplicates: Combine identical items for cleaner quotes\n   - Sort by priority: Higher priority rules appear first\n\n3. **Save Estimate Header**:\n   - Links to: deal_id\n   - Stores: subtotal, total_vat, grand_total, currency (EUR), status (draft)\n   - estimate_number: Uses execution_id (unique)\n\n4. **Insert Line Items** (batched operation):\n   - Split array \u2192 individual records\n   - Foreign keys: estimate_id, catalog_id\n   - Line data: description, quantity, unit_price, vat_rate, totals, sort_order\n\n**Key benefit:**\nRules-based pricing eliminates manual calculation errors. New services = add database rule (no code changes).\n\n**Validation:**\nIf no line items match, throws error with debug context (services selected, m\u00b2 values, ceiling height, active rules count)."
      },
      "typeVersion": 1
    },
    {
      "id": "c1e7d64b-2d95-47c6-bb2b-893829df10ff",
      "name": "Parser",
      "type": "n8n-nodes-base.set",
      "position": [
        -2080,
        480
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "6791498e-0349-4091-962a-46b070f9b524",
              "name": "body.rawRequest",
              "type": "object",
              "value": "={{ $json.body.rawRequest }}"
            },
            {
              "id": "d997880b-c531-40d2-b985-e1daa6a4b8a8",
              "name": "ai.content",
              "type": "string",
              "value": "={{ $json.ai.content }}"
            },
            {
              "id": "55f06ed8-5645-45c5-9b2b-1b02e71e3c6a",
              "name": "submission_id",
              "type": "string",
              "value": "={{ $json.submission_id }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "d8f73def-48d3-4902-adac-27a72833342f",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -3140,
        620
      ],
      "parameters": {
        "width": 660,
        "height": 760,
        "content": "# \ud83d\udca1 Experience Our Quote System\n\n## Get Your Instant Quote\n**https://form.jotform.com/252844786304060**\n\n**See how fast it is:**\nDescribe your plastering project \u2192 Get detailed quote in under 1 minute\n\n**What you'll get:**\n\u2713 Professional quote email\n\u2713 Itemized pricing (labor + materials)\n\u2713 VAT breakdown\n\u2713 30-day validity\n\u2713 Direct booking links\n\n**This is a demo** - Feel free to experiment with different project sizes and service combinations. Your email receives the quote, but no actual booking is created.\n\nPerfect for testing before integrating into your own business!\n\n\n"
      },
      "typeVersion": 1
    },
    {
      "id": "c9bd65eb-077d-4121-abe8-ce951b488cd0",
      "name": "Save Form Submission",
      "type": "n8n-nodes-base.supabase",
      "onError": "continueErrorOutput",
      "position": [
        -1760,
        320
      ],
      "parameters": {
        "tableId": "form_submissions",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldId": "form_submission_id",
              "fieldValue": "={{ $json.submission_id }}"
            },
            {
              "fieldId": "received_at",
              "fieldValue": "={{ $now }}"
            },
            {
              "fieldId": "form_id",
              "fieldValue": "=1760342472777"
            },
            {
              "fieldId": "raw_payload",
              "fieldValue": "={{ $json.body.rawRequest }}"
            },
            {
              "fieldId": "customer_email",
              "fieldValue": "={{ $json.body.rawRequest.q27_customer_email }}"
            },
            {
              "fieldId": "customer_name",
              "fieldValue": "={{ $json.body.rawRequest.q26_customer_name.first }} {{ $json.body.rawRequest.q26_customer_name.last }}"
            },
            {
              "fieldId": "suspicious_flag",
              "fieldValue": "false"
            },
            {
              "fieldId": "time_to_submit",
              "fieldValue": "={{ $json.body.rawRequest.timeToSubmit }}"
            },
            {
              "fieldId": "processed",
              "fieldValue": "true"
            }
          ]
        }
      },
      "credentials": {
        "supabaseApi": {
          "name": "<your credential>"
        }
      },
      "retryOnFail": true,
      "typeVersion": 1,
      "waitBetweenTries": 3000
    },
    {
      "id": "03f3eb45-f8f1-4fc0-bb4e-d874d143519e",
      "name": "Upsert Customer",
      "type": "n8n-nodes-base.supabase",
      "onError": "continueErrorOutput",
      "position": [
        -1040,
        580
      ],
      "parameters": {
        "tableId": "customers",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldId": "email",
              "fieldValue": "={{ $json.normalized.ContactDetails.email }}"
            },
            {
              "fieldId": "first_name",
              "fieldValue": "={{ $json.normalized.ContactDetails.first_name }}"
            },
            {
              "fieldId": "last_name",
              "fieldValue": "={{ $json.normalized.ContactDetails.last_name }}"
            },
            {
              "fieldId": "billing_address",
              "fieldValue": "={{ $json.normalized.ContactDetails.address.full_address }}"
            },
            {
              "fieldId": "phone",
              "fieldValue": "={{ $json.normalized.ContactDetails.phone }}"
            },
            {
              "fieldId": "notes",
              "fieldValue": "={{ $json.normalized.project_description }}"
            }
          ]
        }
      },
      "credentials": {
        "supabaseApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "83b46cfd-3d21-4a58-9919-216718375de1",
      "name": "Create Deal",
      "type": "n8n-nodes-base.supabase",
      "position": [
        -760,
        460
      ],
      "parameters": {
        "tableId": "deals",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldId": "customer_id",
              "fieldValue": "={{ $json.customer_id }}"
            },
            {
              "fieldId": "form_submission_id",
              "fieldValue": "={{ $('Prepare AI Context').item.json.submission_id }}"
            },
            {
              "fieldId": "deal_name",
              "fieldValue": "={{ $json.last_name }}_{{ $json.phone }}"
            }
          ]
        }
      },
      "credentials": {
        "supabaseApi": {
          "name": "<your credential>"
        }
      },
      "retryOnFail": true,
      "typeVersion": 1,
      "waitBetweenTries": 3000
    },
    {
      "id": "c1a98178-4a69-47ac-a8fe-b0d18ca45b49",
      "name": "Fetch Pricing Rules",
      "type": "n8n-nodes-base.supabase",
      "position": [
        -340,
        520
      ],
      "parameters": {
        "tableId": "service_rules_enriched",
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "supabaseApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "233eb854-f2ba-46dd-a041-a526ddd9b150",
      "name": "Calculate Quote Line Items",
      "type": "n8n-nodes-base.code",
      "position": [
        -160,
        520
      ],
      "parameters": {
        "jsCode": "// YOUR_AWS_SECRET_KEY_HERE========================\n// BUILD LINE ITEMS  --  With Fallback Mapping\n// YOUR_AWS_SECRET_KEY_HERE========================\nconst DEFAULTS = { VAT_RATE: 21, PRICE_MULTIPLIER: 1 };\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// VALUE NORMALIZATION MAP (fallback if not pre-normalized)\n// YOUR_AWS_SECRET_KEY_HERE========================\nconst VALUE_MAP = {\n  property_status: {\n    'Existing Property': 'bestaand',\n    'New Construction': 'nieuwbouw',\n    'Renovation': 'bestaand',\n  },\n  core_service_type: {\n    'Smooth Plaster (Paint or Wallpaper Ready)': 'glad_stucwerk',\n    'Decorative Plaster (Ornamental Texture)': 'sierpleister',\n    'Sanding Finish (Fan Pattern)': 'schuurwerk',\n    'Spray Application (Machine-Applied Texture)': 'spuitwerk',\n    'Skim Coating': 'skim_coating',\n    'Exterior Plaster (Facades)': 'buiten_stucwerk',\n    'Moldings & Ornaments (Ceiling Decoration)': 'ornamenten',\n    'Custom Decorative Plasterwork': 'vrije_stuc',\n  },\n  prep_removal_needed: {\n    'Yes, wallpaper needs removal': 'ja_behang',\n    'Yes, old paint layers need removal': 'ja_verf',\n    'Yes, both wallpaper and paint': 'ja_beide',\n    'No, surface is bare': 'nee_kaal',\n  },\n  finish_level: {\n    'Paint Ready': 'sausklaar',\n    'Wallpaper Ready': 'behangklaar',\n    'Spray Ready': 'spuitklaar',\n  },\n  ceiling_height: {\n    'Standard (up to 2.7m / 8.9 ft)': 'standard',\n    'High (2.7-3.5m / 8.9-11.5 ft)': 'high',\n    'Very High (>3.5m / >11.5 ft)': 'very_high',\n  },\n  surface_condition: {\n    'Minor Damage (small cracks, holes)': 'minor_damage',\n    'Major Damage (water damage, structural issues)': 'major_damage',\n    'Good Condition (no visible damage)': 'good_condition',\n  },\n};\n\n// Helper to normalize a value\nconst normalizeValue = (field, value) => {\n  if (!value) return value;\n  if (Array.isArray(value)) {\n    return value.map(v => VALUE_MAP[field]?.[v] || v);\n  }\n  return VALUE_MAP[field]?.[value] || value;\n};\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// GET AND VALIDATE INPUTS\n// YOUR_AWS_SECRET_KEY_HERE========================\nconst rulesInput = $input.all();\nif (!rulesInput.length) throw new Error(\"BUILD_LINE_ITEMS: No rules data received from previous node\");\nconst enrichedRules = rulesInput.map((i) => i.json);\n\nconst cleanedDataNode = $(\"Normalize Form Data\").all();\nif (!cleanedDataNode.length) throw new Error(\"BUILD_LINE_ITEMS: Normalization node returned no data\");\n\nconst cleanedData = cleanedDataNode[0].json || {};\nconst normalized =\n  cleanedData.normalized || { project: cleanedData.project, form_data: cleanedData.form_data };\n\nconst rawAnswers =\n  normalized.project || normalized.form_data\n    ? { ...normalized.project, ...normalized.form_data }\n    : normalized;\n\n// Apply secondary normalization for any English values\nconst answers = {};\nfor (const [key, value] of Object.entries(rawAnswers)) {\n  answers[key] = normalizeValue(key, value);\n}\n\nconsole.log(\"=== NORMALIZED VALUES ===\");\nconsole.log(\"property_status:\", answers.property_status);\nconsole.log(\"core_service_type:\", answers.core_service_type);\nconsole.log(\"prep_removal_needed:\", answers.prep_removal_needed);\n\nif (!answers || Object.keys(answers).length === 0)\n  throw new Error(\"BUILD_LINE_ITEMS: No normalized data found from previous node\");\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// HELPERS\n// YOUR_AWS_SECRET_KEY_HERE========================\nconst getFieldValue = (f, d) => d?.[f];\nconst stringsMatch = (a, b) =>\n  a && b && String(a).trim().toLowerCase() === String(b).trim().toLowerCase();\n\nconst normalizeConditions = (c) => {\n  if (!c) return [];\n  if (Array.isArray(c)) return c;\n  if (typeof c === \"string\") {\n    try { const j = JSON.parse(c); return Array.isArray(j) ? j : []; } catch { return []; }\n  }\n  return [];\n};\n\nconst checkTriggerMatch = (tf, tv, data) => {\n  if (!tf || !tv) return true;\n  const val = getFieldValue(tf, data);\n  if (val === undefined || val === null) return false;\n  if (Array.isArray(val)) return val.some((v) => stringsMatch(v, tv));\n  return stringsMatch(val, tv);\n};\n\nconst checkAdditionalConditions = (conds, data) =>\n  normalizeConditions(conds).every((c) => {\n    const v = getFieldValue(c.field, data);\n    const exp = c.value;\n    const op = c.operator || \"equals\";\n    switch (op) {\n      case \"equals\": return stringsMatch(v, exp);\n      case \"contains\":\n        return Array.isArray(v)\n          ? v.some((x) => stringsMatch(x, exp))\n          : String(v || \"\").toLowerCase().includes(String(exp).toLowerCase());\n      case \"greater_than\": return Number(v || 0) > Number(exp);\n      case \"less_than\": return Number(v || 0) < Number(exp);\n      default: return false;\n    }\n  });\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// FILTER & ACTIVATE RULES\n// YOUR_AWS_SECRET_KEY_HERE========================\nconst activeRules = enrichedRules\n  .filter((r) => r.is_active)\n  .map((r) => ({ ...r, quantity: Number(getFieldValue(r.source_field, answers)) || 0 }))\n  .filter((r) => r.quantity > 0)\n  .filter((r) => checkTriggerMatch(r.trigger_field, r.trigger_value, answers))\n  .filter((r) => checkAdditionalConditions(r.additional_conditions, answers))\n  .sort((a, b) => (b.priority || 0) - (a.priority || 0));\n\nconsole.log(\"=== ACTIVE RULES COUNT ===\", activeRules.length);\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// BUILD RAW LINE ITEMS\n// YOUR_AWS_SECRET_KEY_HERE========================\nconst rawLines = activeRules.map((r, i) => {\n  const qty = Number(r.quantity) || 0;\n  const rate = Number(r.unit_price) || 0;\n  const vat = Number(r.vat_rate) || DEFAULTS.VAT_RATE;\n  const mult = Number(r.price_multiplier) || DEFAULTS.PRICE_MULTIPLIER;\n  const adj = rate * mult;\n  const line = +(qty * adj).toFixed(2);\n  const vatAmt = +(line * (vat / 100)).toFixed(2);\n  return {\n    catalog_id: r.catalog_id,\n    item_code: r.item_code,\n    description: r.catalog_description || r.description || r.name || \"\",\n    quantity: qty,\n    unit_price: adj,\n    vat_rate: vat,\n    line_total: line,\n    vat_amount: vatAmt,\n    total_with_vat: +(line + vatAmt).toFixed(2),\n    sort_order: i + 1,\n  };\n});\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// GROUP IDENTICAL ITEMS\n// YOUR_AWS_SECRET_KEY_HERE========================\nfunction groupLines(list) {\n  const map = {};\n  for (const l of list) {\n    const key = `${l.catalog_id || \"\"}_${l.item_code}`;\n    if (!map[key]) map[key] = { ...l };\n    else {\n      map[key].quantity += l.quantity;\n      map[key].line_total += l.line_total;\n      map[key].vat_amount += l.vat_amount;\n      map[key].total_with_vat += l.total_with_vat;\n      if (l.unit_price > map[key].unit_price) map[key].unit_price = l.unit_price;\n    }\n  }\n  return Object.values(map).map((g, i) => ({\n    ...g,\n    sort_order: i + 1,\n    line_total: +g.line_total.toFixed(2),\n    vat_amount: +g.vat_amount.toFixed(2),\n    total_with_vat: +g.total_with_vat.toFixed(2),\n  }));\n}\n\nconst lines = groupLines(rawLines);\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// VALIDATE\n// YOUR_AWS_SECRET_KEY_HERE========================\nif (!lines.length)\n  throw new Error(\"BUILD_LINE_ITEMS: No line items matched - check rules and normalized data\");\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// CALCULATE TOTALS\n// YOUR_AWS_SECRET_KEY_HERE========================\nconst subtotal = +(lines.reduce((s, l) => s + l.line_total, 0).toFixed(2));\nconst vat_total = +(lines.reduce((s, l) => s + l.vat_amount, 0).toFixed(2));\nconst grand_total = +(subtotal + vat_total).toFixed(2);\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// GET DEAL_ID\n// YOUR_AWS_SECRET_KEY_HERE========================\nconst dealNode = $(\"Create Deal\").all();\nif (!dealNode.length) throw new Error(\"BUILD_LINE_ITEMS: Deal creation node returned no data\");\nconst dealData = dealNode[0].json;\nconst deal_id = dealData.deal_id || dealData.id;\nif (!deal_id) throw new Error(\"BUILD_LINE_ITEMS: No deal_id found in deal data\");\n\n// YOUR_AWS_SECRET_KEY_HERE========================\n// RETURN RESULTS\n// YOUR_AWS_SECRET_KEY_HERE========================\nreturn [\n  {\n    json: {\n      deal_id,\n      lines,\n      summary: {\n        subtotal,\n        vat_total,\n        grand_total,\n        line_count: lines.length,\n      },\n    },\n  },\n];"
      },
      "typeVersion": 2
    },
    {
      "id": "056fd166-3e72-42aa-bcf7-53c2e3832566",
      "name": "Save Estimate Header",
      "type": "n8n-nodes-base.supabase",
      "position": [
        20,
        520
      ],
      "parameters": {
        "tableId": "estimates",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldId": "deal_id",
              "fieldValue": "={{ $json.deal_id }}"
            },
            {
              "fieldId": "subtotal",
              "fieldValue": "={{ $json.summary.subtotal.round(2) }}"
            },
            {
              "fieldId": "total_vat",
              "fieldValue": "={{ $json.summary.vat_total.round(2) }}"
            },
            {
              "fieldId": "currency",
              "fieldValue": "USD"
            },
            {
              "fieldId": "estimate_number",
              "fieldValue": "={{ $execution.id }}"
            }
          ]
        }
      },
      "credentials": {
        "supabaseApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "71ab5d4a-fcdb-4c73-838e-cba1badde940",
      "name": "Insert Line Items",
      "type": "n8n-nodes-base.supabase",
      "position": [
        520,
        520
      ],
      "parameters": {
        "tableId": "estimate_line_items",
        "fieldsUi": {
          "fieldValues": [
            {
              "fieldId": "estimate_id",
              "fieldValue": "={{ $json.estimate_id }}"
            },
            {
              "fieldId": "description",
              "fieldValue": "={{ $json.lines.description }}"
            },
            {
              "fieldId": "quantity",
              "fieldValue": "={{ $json.lines.quantity }}"
            },
            {
              "fieldId": "unit_price",
              "fieldValue": "={{ $json.lines.unit_price }}"
            },
            {
              "fieldId": "vat_rate",
              "fieldValue": "={{ $json.lines.vat_rate }}"
            },
            {
              "fieldId": "sort_order",
              "fieldValue": "={{ $json.lines.sort_order }}"
            },
            {
              "fieldId": "catalog_id",
              "fieldValue": "={{ $json.lines.catalog_id }}"
            }
          ]
        }
      },
      "credentials": {
        "supabaseApi": {
          "name": "<your credential>"
        }
      },
      "executeOnce": false,
      "retryOnFail": true,
      "typeVersion": 1,
      "waitBetweenTries": 3000
    },
    {
      "id": "ddfe1924-d3c7-4f59-b0dc-dd0a6ae22b0a",
      "name": "Prepare Line Item Data",
      "type": "n8n-nodes-base.set",
      "position": [
        200,
        520
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "92d3e913-cc8f-44e0-9a13-2ff66f9abe7c",
              "name": "estimate_id",
              "type": "string",
              "value": "={{ $json.estimate_id }}"
            },
            {
              "id": "90a80953-cca1-48e9-9833-571042a918c6",
              "name": "deal_id",
              "type": "string",
              "value": "={{ $json.deal_id }}"
            },
            {
              "id": "d07e74f9-5d10-4003-97f2-fab1f4682fa4",
              "name": "lines",
              "type": "array",
              "value": "={{ $('Calculate Quote Line Items').item.json.lines }}"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "850a5d90-1799-4366-a035-3800704ebe9d",
      "name": "Fetch Complete Quote",
      "type": "n8n-nodes-base.supabase",
      "onError": "continueErrorOutput",
      "maxTries": 2,
      "position": [
        960,
        200
      ],
      "parameters": {
        "filters": {
          "conditions": [
            {
              "keyName": "estimate_id",
              "keyValue": "={{ $('Save Estimate Header').item.json.estimate_id }}",
              "condition": "eq"
            }
          ]
        },
        "tableId": "v_estimate_proforma",
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "supabaseApi": {
          "name": "<your credential>"
        }
      },
      "executeOnce": true,
      "retryOnFail": true,
      "typeVersion": 1,
      "waitBetweenTries": 4000
    },
    {
      "id": "2d56a903-f00f-458b-a3cf-227fa4ab3f2b",
      "name": "Generate Email HTML",
      "type": "n8n-nodes-base.code",
      "position": [
        1200,
        180
      ],
      "parameters": {
        "jsCode": "function createQuotationHtml(props) {\n  const {\n    clientName = \"Valued Customer\",\n    quotationNumber = \"Q-2025-001\",\n    items = [],\n    summary = { subtotal: 0, total_vat: 0, grand_total: 0 },\n    contact = {},\n    project = {}\n  } = props;\n\n  const generateItemRows = () => {\n    return items.map(item => `\n      <tr style=\"border-top: 1px solid #444;\">\n        <td style=\"padding: 16px;\">\n          <p style=\"font-size: 14px; font-weight: 500; color: #f1f1f1; margin: 0 0 4px 0;\">${item.description}</p>\n          <p style=\"font-size: 12px; color: #999; margin: 0;\">VAT: ${item.vat_rate}%</p>\n        </td>\n        <td style=\"padding: 16px; text-align: center;\">\n          <p style=\"font-size: 14px; color: #f1f1f1; margin: 0;\">\u20ac${Number(item.unit_price).toFixed(2)}</p>\n          <p style=\"font-size: 12px; color: #999; margin: 0;\">per ${item.unit_type || 'm\u00b2'}</p>\n        </td>\n        <td style=\"padding: 16px; text-align: center;\">\n          <p style=\"font-size: 14px; color: #f1f1f1; margin: 0;\">${item.quantity}</p>\n        </td>\n        <td style=\"padding: 16px; text-align: right;\">\n          <p style=\"font-size: 14px; color: #f1f1f1; margin: 0; font-weight: 500;\">\u20ac${Number(item.line_total).toFixed(2)}</p>\n          <p style=\"font-size: 11px; color: #666; margin: 0;\">+\u20ac${Number(item.vat_amount).toFixed(2)} VAT</p>\n        </td>\n      </tr>\n    `).join('');\n  };\n\n  const today = new Date();\n  const expirationDate = new Date();\n  expirationDate.setDate(today.getDate() + 30);\n  const formattedExpirationDate = expirationDate.toLocaleDateString('en-GB', { year: 'numeric', month: 'long', day: 'numeric' });\n  const formattedToday = today.toLocaleDateString('en-GB', { year: 'numeric', month: 'long', day: 'numeric' });\n  \n  const baseUrl = \"https://your-n8n-instance.com\";\n  const acceptUrl = `${baseUrl}/webhook/quote-accepted?quoteId=${quotationNumber}&email=${encodeURIComponent(contact.email)}`;\n  const calendlyUrl = \"https://calendly.com/your-username/quote-discussion\";\n  const unsubscribeUrl = `${baseUrl}/webhook/unsubscribe?email=${encodeURIComponent(contact.email)}`;\n  \n  const logoUrl = \"https://picsum.photos/seed/company-logo/300/120\";\n\n  return `\n    <!DOCTYPE html>\n    <html lang=\"en\">\n    <head>\n      <meta charset=\"UTF-8\">\n      <meta name=\"viewport\" content=\"width=device-width, initial-scale=1.0\">\n      <title>Quotation ${quotationNumber}</title>\n    </head>\n    <body style=\"margin: 0; font-family: -apple-system, BlinkMacSystemFont, 'Segoe UI', Roboto, sans-serif; background-color: #f3f4f6; padding: 40px 0;\">\n      <table cellpadding=\"0\" cellspacing=\"0\" style=\"margin: 0 auto; background-color: #121212; color: #f1f1f1; max-width: 650px; border-radius: 8px; overflow: hidden; box-shadow: 0 4px 6px rgba(0,0,0,0.3);\">\n        \n        <!-- Header -->\n        <tr>\n          <td style=\"padding: 32px; text-align: center; background: linear-gradient(135deg, #1a1a1a 0%, #2d2d2d 100%);\">\n            <img src=\"${logoUrl}\" alt=\"Company Logo\" width=\"150\" style=\"margin-bottom: 24px; border-radius: 8px;\" />\n            <h1 style=\"color: #ffffff; font-size: 28px; font-weight: 700; margin: 0;\">Professional Quotation</h1>\n            <p style=\"color: #999; font-size: 16px; margin: 8px 0 0 0; font-weight: 500;\">#${quotationNumber}</p>\n            <p style=\"color: #666; font-size: 12px; margin: 8px 0 0 0;\">Date: ${formattedToday}</p>\n          </td>\n        </tr>\n\n        <!-- Greeting & Contact Info -->\n        <tr>\n          <td style=\"padding: 32px;\">\n            <p style=\"font-size: 16px; margin: 0 0 8px 0; font-weight: 500;\">Dear ${clientName},</p>\n            <p style=\"color: #bbb; font-size: 14px; margin: 0 0 24px 0; line-height: 22px;\">\n              Thank you for your inquiry. Please find below the detailed quotation for your plastering project.\n            </p>\n\n            ${project.postcode || project.status || project.start_date || project.description ? `\n            <table cellpadding=\"0\" cellspacing=\"0\" style=\"width: 100%; margin-bottom: 24px; background-color: #1a1a1a; border-radius: 8px; padding: 16px; border-left: 4px solid #E63946;\">\n              <tr>\n                <td>\n                  <p style=\"color: #FF8C00; font-size: 12px; font-weight: 600; margin: 0 0 12px 0; text-transform: uppercase; letter-spacing: 0.05em;\">\ud83d\udccb Project Details</p>\n                  ${project.postcode ? `<p style=\"color: #f1f1f1; font-size: 13px; margin: 0 0 8px 0;\"><strong>Location:</strong> ${project.postcode}</p>` : ''}\n                  ${project.status ? `<p style=\"color: #f1f1f1; font-size: 13px; margin: 0 0 8px 0;\"><strong>Property Type:</strong> ${project.status}</p>` : ''}\n                  ${project.start_date && project.start_date !== 'null' ? `<p style=\"color: #f1f1f1; font-size: 13px; margin: 0 0 8px 0;\"><strong>Desired Start Date:</strong> ${new Date(project.start_date).toLocaleDateString('en-GB')}</p>` : ''}\n                  ${project.description ? `<p style=\"color: #f1f1f1; font-size: 13px; margin: 0;\"><strong>Notes:</strong> ${project.description}</p>` : ''}\n                </td>\n              </tr>\n            </table>\n            ` : ''}\n\n            <!-- Line Items Table -->\n            <table cellpadding=\"0\" cellspacing=\"0\" style=\"width: 100%; border-radius: 8px; overflow: hidden; margin-bottom: 32px; border-collapse: collapse; background-color: #212121;\">\n              <thead>\n                <tr style=\"background: linear-gradient(135deg, #333 0%, #444 100%);\">\n                  <th style=\"padding: 14px 16px; text-align: left;\"><p style=\"font-size: 12px; font-weight: 600; margin: 0; text-transform: uppercase; letter-spacing: 0.05em; color: #f1f1f1;\">Service</p></th>\n                  <th style=\"padding: 14px 16px; text-align: center;\"><p style=\"font-size: 12px; font-weight: 600; margin: 0; text-transform: uppercase; letter-spacing: 0.05em; color: #f1f1f1;\">Unit Price</p></th>\n                  <th style=\"padding: 14px 16px; text-align: center;\"><p style=\"font-size: 12px; font-weight: 600; margin: 0; text-transform: uppercase; letter-spacing: 0.05em; color: #f1f1f1;\">Quantity</p></th>\n                  <th style=\"padding: 14px 16px; text-align: right;\"><p style=\"font-size: 12px; font-weight: 600; margin: 0; text-transform: uppercase; letter-spacing: 0.05em; color: #f1f1f1;\">Amount</p></th>\n                </tr>\n              </thead>\n              <tbody>\n                ${generateItemRows()}\n              </tbody>\n            </table>\n            \n            <!-- Summary Table -->\n            <table cellpadding=\"0\" cellspacing=\"0\" style=\"width: 100%; background-color: #212121; border-radius: 8px; padding: 24px; margin-bottom: 32px;\">\n              <tr>\n                <td style=\"padding-bottom: 12px;\"><p style=\"color: #999; font-size: 15px; margin: 0;\">Subtotal (excl. VAT)</p></td>\n                <td style=\"padding-bottom: 12px; text-align: right;\"><p style=\"color: #f1f1f1; font-size: 15px; margin: 0; font-weight: 500;\">\u20ac${Number(summary.subtotal).toFixed(2)}</p></td>\n              </tr>\n              <tr>\n                <td style=\"padding-bottom: 20px;\"><p style=\"color: #999; font-size: 15px; margin: 0;\">VAT</p></td>\n                <td style=\"padding-bottom: 20px; text-align: right;\"><p style=\"color: #f1f1f1; font-size: 15px; margin: 0; font-weight: 500;\">\u20ac${Number(summary.total_vat).toFixed(2)}</p></td>\n              </tr>\n              <tr style=\"border-top: 2px solid #444;\">\n                <td style=\"padding-top: 20px;\"><p style=\"font-size: 18px; font-weight: 700; margin: 0; color: #f1f1f1;\">Total Amount</p></td>\n                <td style=\"padding-top: 20px; text-align: right;\"><p style=\"font-size: 26px; font-weight: 700; margin: 0; background: linear-gradient(45deg, #FF8C00, #E63946); -webkit-background-clip: text; -webkit-text-fill-color: transparent; background-clip: text;\">\u20ac${Number(summary.grand_total).toFixed(2)}</p></td>\n              </tr>\n            </table>\n\n            <!-- Call to Action Buttons -->\n            <table cellpadding=\"0\" cellspacing=\"0\" style=\"width: 100%; text-align: center; margin-bottom: 24px;\">\n              <tr>\n                <td style=\"padding: 12px;\">\n                  <a href=\"${acceptUrl}\" style=\"background: linear-gradient(45deg, #E63946, #FF8C00); color: #ffffff; padding: 16px 32px; border-radius: 8px; text-decoration: none; font-weight: bold; font-size: 16px; display: inline-block; box-shadow: 0 4px 6px rgba(230, 57, 70, 0.3);\">\n                    \u2714\ufe0f Accept This Quote\n                  </a>\n                </td>\n              </tr>\n              <tr>\n                <td style=\"padding: 12px;\">\n                  <a href=\"${calendlyUrl}\" style=\"background-color: #333; color: #f1f1f1; padding: 14px 28px; border-radius: 8px; text-decoration: none; font-weight: 500; font-size: 14px; display: inline-block; border: 1px solid #555;\">\n                    \ud83d\udcc5 Schedule a Discussion\n                  </a>\n                </td>\n              </tr>\n            </table>\n\n            <!-- Validity Notice -->\n            <table cellpadding=\"0\" cellspacing=\"0\" style=\"width: 100%; margin-top: 24px; padding: 20px; background: linear-gradient(135deg, #1a1a1a 0%, #2d2d2d 100%); border-radius: 8px; border-left: 4px solid #FF8C00;\">\n              <tr>\n                <td>\n                  <p style=\"color: #FF8C00; font-size: 12px; margin: 0 0 8px 0; font-weight: 600; text-transform: uppercase; letter-spacing: 0.05em;\">\u23f0 Quote Validity</p>\n                  <p style=\"color: #f1f1f1; font-size: 15px; margin: 0; font-weight: 500;\">In the name of: ${clientName}</p>\n                  <p style=\"color: #f1f1f1; font-size: 15px; margin: 8px 0; font-weight: 500;\">This quotation is valid until ${formattedExpirationDate}</p>\n                  <p style=\"color: #999; font-size: 12px; margin: 0;\">Please confirm your acceptance before this date to secure these prices.</p>\n                </td>\n              </tr>\n            </table>\n          </td>\n        </tr>\n\n        <!-- Footer -->\n        <tr>\n          <td style=\"padding: 32px; border-top: 1px solid #444; text-align: center; background-color: #0d0d0d;\">\n            <!-- Company Info -->\n            <p style=\"color: #f1f1f1; font-size: 13px; font-weight: 600; margin: 0 0 8px 0;\">\n              Your Company Name Ltd.\n            </p>\n            <p style=\"color: #999; font-size: 12px; margin: 0 0 4px 0; line-height: 18px;\">\n              123 Main Street, 1011 AA Amsterdam, Netherlands\n            </p>\n            <p style=\"color: #999; font-size: 12px; margin: 0 0 4px 0;\">\n              \ud83d\udcde +1234567890 | \u2709\ufe0f user@example.com\n            </p>\n            <p style=\"color: #777; font-size: 11px; margin: 0 0 20px 0;\">\n              CoC: 12345678 | VAT: NL123456789B01\n            </p>\n            \n            <!-- Legal Notice -->\n            <p style=\"color: #999; font-size: 11px; margin: 0 0 4px 0; line-height: 16px;\">\n              \u2696\ufe0f This quotation is based on the information you provided. The final price may differ if the\n            </p>\n            <p style=\"color: #999; font-size: 11px; margin: 0 0 6px 0; line-height: 16px;\">\n              actual situation deviates from the stated details.\n            </p>\n            <p style=\"color: #777; font-size: 11px; margin: 0 0 20px 0;\">\n              \u2192 <a href=\"https://yourcompany.com/terms-and-conditions\" style=\"color: #FF8C00; text-decoration: none;\">View our Terms & Conditions</a>\n            </p>\n            \n            <!-- Unsubscribe & Copyright -->\n            <p style=\"color: #666; font-size: 11px; margin: 0 0 8px 0;\">\n              <a href=\"${unsubscribeUrl}\" style=\"color: #666; text-decoration: none;\">Unsubscribe from emails</a>\n            </p>\n            <p style=\"color: #555; font-size: 10px; margin: 0;\">\n              \u00a9 ${new Date().getFullYear()} Your Company Name Ltd. All rights reserved.\n            </p>\n          </td>\n        </tr>\n      </table>\n    </body>\n    </html>\n  `;\n}\n\n// YOUR_AWS_SECRET_KEY_HERE====================\n// MAIN EXECUTION - Map from v_estimate_proforma view\n// YOUR_AWS_SECRET_KEY_HERE====================\n\nconst proforma = $input.first().json;\n\nconst props = {\n  clientName: proforma.customer_full_name || `${proforma.first_name} ${proforma.last_name}`.trim(),\n  quotationNumber: proforma.estimate_number || 'N/A',\n  items: proforma.line_items || [],\n  summary: {\n    subtotal: proforma.subtotal,\n    total_vat: proforma.total_vat,\n    grand_total: proforma.grand_total\n  },\n  contact: {\n    email: proforma.customer_email,\n    phone: proforma.customer_phone,\n    address: proforma.billing_address\n  },\n  project: {\n    postcode: proforma.project_postcode,\n    status: proforma.property_status,\n    start_date: proforma.project_start_date,\n    description: proforma.project_description\n  }\n};\n\nconst htmlOutput = createQuotationHtml(props);\n\nreturn { \n  json: { \n    html: htmlOutput,\n    email_to: proforma.customer_email,\n    email_subject: `Your Quotation ${proforma.estimate_number}`,\n    estimate_id: proforma.estimate_id,\n    quotation_number: proforma.estimate_number,\n    customer_name: props.clientName\n  } \n};"
      },
      "typeVersion": 2
    },
    {
      "id": "e08e4103-f939-409d-8efc-74e700923700",
      "name": "Fetch Mapping Rules",
      "type": "n8n-nodes-base.supabase",
      "position": [
        -1800,
        680
      ],
      "parameters": {
        "filters": {
          "conditions": [
            {
              "keyName": "is_active",
              "keyValue": "true",
              "condition": "is"
            }
          ]
        },
        "tableId": "form_value_mappings",
        "operation": "getAll",
        "returnAll": true
      },
      "credentials": {
        "supabaseApi": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 1
    },
    {
      "id": "24829399-a739-47ac-8f49-ef0d69c58ad3",
      "name": "Prepare AI Context",
      "type": "n8n-nodes-base.set",
      "position": [
        -1620,
        680
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "63c2e962-d73a-42bf-b51f-f36e42fa521a",
              "name": "service_types",
              "type": "array",
              "value": "={{ [...new Set($input.all().filter(item => item.json.field_name === 'core_service_type').map(item => item.json.normalized_value))] }}"
            },
            {
              "id": "252752eb-3cb0-4517-823a-c7797bd83110",
              "name": "property_types",
              "type": "array",
              "value": "={{ [...new Set($input.all().filter(item => item.json.field_name === 'property_status').map(item => item.json.normalized_value))].toJsonString()}}"
            },
            {
              "id": "de2e1996-8153-4dd2-a575-222d154f25f5",
              "name": "ceiling_heights",
              "type": "array",
              "value": "={{ [...new Set($input.all().filter(item => item.json.field_name === 'ceiling_height').map(item => item.json.normalized_value))] }}"
            },
            {
              "id": "f3090dd9-724e-49cf-9a8b-188122b16c8e",
              "name": "body.rawRequest",
              "type": "object",
              "value": "={{ $('Parser').item.json.body.rawRequest.removeField('slug').removeField('passProtectToken').removeField('uploadServerUrl').removeField('jsExecutionTracker').removeField('submitSource').removeField('buildDate').removeField('path').removeField('preview').removeField('validatedNewRequiredFieldIDs').removeField('timeToSubmit').removeField('dropdown_search')}}"
            },
            {
              "id": "a39717bc-de9f-488c-b233-dc88d43973d5",
              "name": "ai.content",
              "type": "string",
              "value": "={{ $('Webhook').item.json.body.pretty }}"
            },
            {
              "id": "af177051-0c1d-4320-b3dd-f4063ec77171",
              "name": "submission_id",
              "type": "string",
              "value": "={{ $('Parser').item.json.submission_id }}"
            }
          ]
        }
      },
      "executeOnce": true,
      "retryOnFail": true,
      "typeVersion": 3.4,
      "waitBetweenTries": 4000
    },
    {
      "id": "f75648b6-fc6b-490a-b675-f9f32a022e36",
      "name": "Normalize Form Data",
      "type": "n8n-nodes-base.code",
      "position": [
        -1440,
        680
      ],
      "parameters": {
        "jsCode": "const input = $input.all()[0].json;\n\n// Helper for safe access\nconst raw = input.body?.rawRequest || {};\nconst name = raw.q26_customer_name || {};\nconst addr = raw.q28_customer_address || {};\nconst phone = raw.q29_customer_phone || {};\nconst timeline = raw.q31_desired_timeline || {};\n\nreturn [\n  {\n    json: {\n      submission_id: input.submission_id,\n      normalized: {\n        property_status: raw.q9_property_status,\n        project_postcode: raw.q3_project_postcode,\n        core_service_type: Array.isArray(raw.q10_core_service_type)\n          ? raw.q10_core_service_type.map(v =>\n              v.includes('Glad stucwerk') ? 'glad_stucwerk' :\n              v.includes('Sierpleister') ? 'sierpleister' :\n              v.includes('Schuurwerk') ? 'schuurwerk' :\n              v.includes('Spuitwerk') ? 'spuitwerk' :\n              v.includes('skimming') ? 'skim_coating' :\n              v.includes('Buiten') ? 'buiten_stucwerk' :\n              v.includes('Ornament') ? 'ornamenten' :\n              v.includes('Vrije') ? 'vrije_stuc' :\n              v\n            )\n          : [],\n        m2_ceilings: Number(raw.q4_m2_ceilings) || 0,\n        m2_walls: Number(raw.q14_m2_walls14) || 0,\n        finish_level: raw.q15_finish_level,\n        ornament_pieces: Number(raw.q17_qty_pieces) || 0,\n        molding_meters: Number(raw.q18_qty_meters) || 0,\n        includes_wet_area: raw.q20_includes_wet_area === 'YES',\n        ceiling_height:\n          raw.q21_typeA?.includes('Hoog (2.7-3.5m)') ? 'high' :\n          raw.q21_typeA?.includes('Zeer hoog') ? 'very_high' : 'standard',\n        prep_removal_needed: raw.q23_prep_removal_needed,\n        project_start_date: `${timeline.year}-${timeline.month}-${timeline.day}`,\n        project_description: raw.q24_project_description,\n        ContactDetails: {\n          first_name: name.first,\n          last_name: name.last,\n          full_name: `${name.first || ''} ${name.last || ''}`.trim(),\n          email: raw.q27_customer_email,\n          phone: `${phone.country || ''}${phone.area || ''}${phone.phone || ''}`.replace(/\\s+/g, ''),\n          address: {\n            full_address: addr.addr_search,\n            line1: addr.addr_line1,\n            line2: addr.addr_line2,\n            city: addr.city,\n            state: addr.state,\n            postal: addr.postal,\n            country: addr.country,\n          },\n        },\n      },\n    },\n  },\n];"
      },
      "typeVersion": 2
    },
    {
      "id": "54e66e54-b426-4607-95d6-d72d7c4a129e",
      "name": "Stage 3: Quote Generation1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        880,
        -580
      ],
      "parameters": {
        "color": 3,
        "width": 880,
        "height": 940,
        "content": "## \ud83d\udce7 STAGE 4: Professional Quote Email\n\n**Flow:**\nInsert Line Items \u2192 Fetch Complete Quote \u2192 Generate Email HTML \u2192 (Send Email - not shown)\n\n**What happens:**\n1. **Fetch Complete Quote** queries `v_estimate_proforma` view:\n   - Single query returns: customer details, estimate header, ALL line items (JSONB array)\n   - Filters by: estimate_id from previous step\n   - Returns fully enriched data (no joins needed in n8n)\n\n2. **Generate Email HTML**:\n   - Maps proforma data \u2192 HTML template props\n   - Generates professional dark-themed email:\n     * Company header with logo\n     * Customer greeting (personalized)\n     * Project details (if available)\n     * Line items table (service, qty, price, VAT)\n     * Financial summary (subtotal, VAT, grand total)\n     * CTA buttons (Accept Quote, Schedule Call)\n     * Validity period (30 days from issue)\n     * Legal disclaimer + Terms link\n     * Company footer (contact, CoC, VAT number)\n   - Returns: HTML string + email metadata (to, subject, estimate_id)\n\n**Key benefit:**\nEmail matches database exactly (single source of truth). No calculation drift between quote and email.\n\n**Next step:**\nPass HTML to Send Email node (Gmail/SendGrid) for delivery."
      },
      "typeVersion": 1
    },
    {
      "id": "24fa29f7-0d5a-4426-94b4-7b65d7d57fbc",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -3160,
        -220
      ],
      "parameters": {
        "width": 900,
        "height": 540,
        "content": "## Automated Quote Generation System\n\n**What it does:**\nTransforms JotForm submissions into professional quotes with CRM integration and email delivery.\n\n**The problem it solves:**\nManual quote creation took 30-60 minutes with frequent errors. This workflow completes the same process in seconds with guaranteed accuracy.\n\n**Key features:**\n- Intelligent form data normalization (Dutch labels \u2192 structured JSON)\n- Rules-based pricing engine (no hardcoded prices)\n- Complete audit trail (form \u2192 customer \u2192 deal \u2192 estimate \u2192 line items)\n- Automatic email generation with professional HTML template\n\n**Business impact:**\nZero manual data entry, 95% faster turnaround, eliminates pricing errors, ensures no lead gets lost in handoffs."
      },
      "typeVersion": 1
    },
    {
      "id": "0d8541d8-5adf-440f-8f6e-e59cf4e728c9",
      "name": "Send Email node",
      "type": "n8n-nodes-base.gmail",
      "position": [
        1460,
        180
      ],
      "parameters": {
        "sendTo": "={{ $('Generate Email HTML').item.json.email_to }}",
        "message": "={{ $('Generate Email HTML').item.json.html }}",
        "options": {
          "senderName": "Stucco Planet",
          "appendAttribution": false
        },
        "subject": "={{ $('Generate Email HTML').item.json.email_subject }}"
      },
      "credentials": {
        "gmailOAuth2": {
          "name": "<your credential>"
        }
      },
      "typeVersion": 2.1
    },
    {
      "id": "d9ce54c4-9beb-4565-a92a-ac5b5e9bf754",
      "name": "SQL Schema Generator",
      "type": "n8n-nodes-base.code",
      "disabled": true,
      "position": [
        -2820,
        1120
      ],
      "parameters": {
        "jsCode": "// YOUR_AWS_SECRET_KEY_HERE====\n// SQL SCHEMA GENERATOR FOR SUPABASE\n// YOUR_AWS_SECRET_KEY_HERE====\n// Outputs production-ready SQL for n8n Quote Automation workflow\n// Copy output \u2192 Paste in Supabase SQL Editor \u2192 Run\n\nconst generateSupabaseSchema = () => {\n  return `-- YOUR_AWS_SECRET_KEY_HERE====\n-- N8N QUOTE AUTOMATION - SUPABASE SCHEMA\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- WARNING: This is production-ready SQL for n8n workflow\n-- Run this in Supabase SQL Editor (not context-only)\n--\n-- Version: 1.0\n-- Last Updated: 2025-01-14\n-- GitHub: [your-repo-link]\n-- License: MIT\n--\n-- CREATES:\n-- - 9 core tables (customers, deals, estimates, catalog, rules)\n-- - 1 optimized view (v_estimate_proforma)\n-- - Sample data (5 services, pricing rules, form mappings)\n-- - Indexes, constraints, auto-calculations\n--\n-- TIME: ~10 seconds to complete\n-- YOUR_AWS_SECRET_KEY_HERE====\n\n-- Enable UUID extension\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 1. CUSTOMERS\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.customers (\n  customer_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  email TEXT UNIQUE CHECK (email ~* '^[A-Z0-9._%+-]+@[A-Z0-9.-]+\\\\\\\\.[A-Z]{2,}$'),\n  first_name TEXT,\n  last_name TEXT,\n  phone TEXT,\n  billing_address JSONB,\n  address_street TEXT,\n  address_postcode TEXT,\n  address_city TEXT,\n  address_country TEXT,\n  vat_number TEXT,\n  notes TEXT,\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\nCREATE INDEX IF NOT EXISTS idx_customers_email ON public.customers(email);\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 2. FORM_SUBMISSIONS\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.form_submissions (\n  form_submission_id TEXT PRIMARY KEY,\n  form_id TEXT NOT NULL,\n  received_at TIMESTAMPTZ DEFAULT NOW(),\n  raw_payload JSONB NOT NULL,\n  customer_email TEXT,\n  customer_name TEXT,\n  suspicious_flag BOOLEAN DEFAULT FALSE,\n  preview_flag BOOLEAN DEFAULT FALSE,\n  time_to_submit INTEGER,\n  processed BOOLEAN DEFAULT FALSE,\n  processed_at TIMESTAMPTZ,\n  error_message TEXT,\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\nCREATE INDEX IF NOT EXISTS idx_submissions_email ON public.form_submissions(customer_email);\nCREATE INDEX IF NOT EXISTS idx_submissions_date ON public.form_submissions(received_at DESC);\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 3. DEALS\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.deals (\n  deal_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  customer_id UUID NOT NULL REFERENCES public.customers(customer_id),\n  form_submission_id TEXT UNIQUE REFERENCES public.form_submissions(form_submission_id),\n  deal_name TEXT NOT NULL,\n  status TEXT NOT NULL DEFAULT 'lead' CHECK (\n    status IN ('lead', 'qualified', 'proposal', 'won', 'lost')\n  ),\n  m2_input NUMERIC CHECK (m2_input >= 0),\n  notes TEXT,\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\nCREATE INDEX IF NOT EXISTS idx_deals_customer ON public.deals(customer_id);\nCREATE INDEX IF NOT EXISTS idx_deals_status ON public.deals(status);\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 4. PRICE_CATALOG\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.price_catalog (\n  catalog_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  item_code TEXT UNIQUE NOT NULL,\n  name TEXT NOT NULL,\n  name_nl TEXT,\n  description TEXT,\n  description_nl TEXT,\n  unit_type TEXT NOT NULL,\n  unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),\n  vat_rate NUMERIC DEFAULT 21.00 CHECK (vat_rate >= 0 AND vat_rate <= 100),\n  currency TEXT DEFAULT 'EUR',\n  is_active BOOLEAN DEFAULT TRUE,\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\nCREATE INDEX IF NOT EXISTS idx_catalog_active ON public.price_catalog(is_active) WHERE is_active = TRUE;\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 5. SERVICE_RULES\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.service_rules (\n  rule_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  item_code TEXT REFERENCES public.price_catalog(item_code),\n  source_field TEXT NOT NULL,\n  trigger_field TEXT,\n  trigger_value TEXT,\n  additional_conditions JSONB DEFAULT '[]',\n  priority INTEGER DEFAULT 100,\n  price_multiplier NUMERIC DEFAULT 1.00,\n  is_active BOOLEAN DEFAULT TRUE,\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\nCREATE INDEX IF NOT EXISTS idx_rules_active ON public.service_rules(is_active) WHERE is_active = TRUE;\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 6. ESTIMATES\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.estimates (\n  estimate_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  deal_id UUID NOT NULL REFERENCES public.deals(deal_id),\n  estimate_number TEXT UNIQUE NOT NULL,\n  status TEXT NOT NULL DEFAULT 'draft' CHECK (\n    status IN ('draft', 'sent', 'accepted', 'rejected')\n  ),\n  subtotal NUMERIC DEFAULT 0 CHECK (subtotal >= 0),\n  total_vat NUMERIC DEFAULT 0 CHECK (total_vat >= 0),\n  grand_total NUMERIC DEFAULT (subtotal + total_vat),\n  currency TEXT DEFAULT 'EUR',\n  valid_until DATE,\n  invoice_id UUID UNIQUE,\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\nCREATE INDEX IF NOT EXISTS idx_estimates_deal ON public.estimates(deal_id);\nCREATE INDEX IF NOT EXISTS idx_estimates_number ON public.estimates(estimate_number);\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 7. ESTIMATE_LINE_ITEMS\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.estimate_line_items (\n  est_line_item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  estimate_id UUID NOT NULL REFERENCES public.estimates(estimate_id),\n  catalog_id UUID REFERENCES public.price_catalog(catalog_id),\n  description TEXT,\n  quantity NUMERIC NOT NULL CHECK (quantity >= 0),\n  unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),\n  vat_rate NUMERIC DEFAULT 21.00 CHECK (vat_rate >= 0),\n  line_total NUMERIC DEFAULT (quantity * unit_price),\n  vat_amount NUMERIC DEFAULT (((quantity * unit_price) * vat_rate) / 100),\n  total_with_vat NUMERIC DEFAULT ((quantity * unit_price) * (1 + (vat_rate / 100))),\n  sort_order INTEGER DEFAULT 0,\n  created_at TIMESTAMPTZ DEFAULT NOW()\n);\n\nCREATE INDEX IF NOT EXISTS idx_line_items_estimate ON public.estimate_line_items(estimate_id);\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 8. FORM_FIELD_MAPPINGS\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.form_field_mappings (\n  mapping_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  form_id TEXT NOT NULL,\n  jotform_field_name TEXT NOT NULL,\n  normalized_field_name TEXT NOT NULL,\n  field_type TEXT DEFAULT 'string' CHECK (\n    field_type IN ('string', 'number', 'object', 'array', 'boolean', 'date')\n  ),\n  is_required BOOLEAN DEFAULT FALSE,\n  default_value TEXT,\n  is_active BOOLEAN DEFAULT TRUE,\n  notes TEXT,\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 9. FORM_VALUE_MAPPINGS\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.form_value_mappings (\n  mapping_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  field_name TEXT NOT NULL,\n  jotform_value TEXT NOT NULL,\n  normalized_value TEXT NOT NULL,\n  is_active BOOLEAN DEFAULT TRUE,\n  notes TEXT,\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\nCREATE INDEX IF NOT EXISTS idx_value_mappings_field ON public.form_value_mappings(field_name) WHERE is_active = TRUE;\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 10. INVOICES (Future expansion)\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.invoices (\n  invoice_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  deal_id UUID UNIQUE NOT NULL REFERENCES public.deals(deal_id),\n  invoice_number TEXT UNIQUE NOT NULL,\n  issue_date DATE NOT NULL DEFAULT CURRENT_DATE,\n  due_date DATE,\n  subtotal NUMERIC DEFAULT 0 CHECK (subtotal >= 0),\n  total_vat NUMERIC DEFAULT 0 CHECK (total_vat >= 0),\n  grand_total NUMERIC DEFAULT (subtotal + total_vat),\n  currency TEXT DEFAULT 'EUR',\n  payment_status TEXT DEFAULT 'unpaid' CHECK (\n    payment_status IN ('unpaid', 'partially_paid', 'paid', 'overdue')\n  ),\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 11. INVOICE_LINE_ITEMS (Future expansion)\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.invoice_line_items (\n  line_item_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  invoice_id UUID NOT NULL REFERENCES public.invoices(invoice_id),\n  catalog_id UUID REFERENCES public.price_catalog(catalog_id),\n  description TEXT,\n  quantity NUMERIC NOT NULL CHECK (quantity >= 0),\n  unit_price NUMERIC NOT NULL CHECK (unit_price >= 0),\n  vat_rate NUMERIC DEFAULT 21.00 CHECK (vat_rate >= 0),\n  line_total NUMERIC DEFAULT (quantity * unit_price),\n  vat_amount NUMERIC DEFAULT (((quantity * unit_price) * vat_rate) / 100),\n  total_with_vat NUMERIC DEFAULT ((quantity * unit_price) * (1 + (vat_rate / 100))),\n  sort_order INTEGER DEFAULT 0,\n  created_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 12. PROFILES (User management)\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE TABLE IF NOT EXISTS public.profiles (\n  profile_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),\n  full_name TEXT NOT NULL,\n  role TEXT NOT NULL CHECK (\n    role IN ('admin', 'sales', 'estimator', 'viewer')\n  ),\n  created_at TIMESTAMPTZ DEFAULT NOW(),\n  updated_at TIMESTAMPTZ DEFAULT NOW()\n);\n\n-- YOUR_AWS_SECRET_KEY_HERE====\n-- 13. OPTIMIZED VIEW - V_ESTIMATE_PROFORMA\n-- YOUR_AWS_SECRET_KEY_HERE====\nCREATE OR REPLACE VIEW public.v_estimate_proforma AS\nSELECT \n  e.estimate_id,\n  e.estimate_number,\n  e.status,\n  e.subtotal,\n  e.total

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
• supabaseApi

About this workflow

Construction and renovation businesses that need to generate detailed quotes from customer inquiries—plasterers, painters, contractors, renovation specialists, or any construction service provider handling quote requests through online forms.

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

More Email & Gmail workflows → · Browse all categories →

Related workflows

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