使用JotForm到邮件生成专业建筑报价，集成Supabase CRM
高级
这是一个自动化工作流，包含 27 个节点。主要使用 Set, Code, Gmail, Webhook, SplitOut 等节点。 使用JotForm到邮件生成专业建筑报价，集成Supabase CRM
前置要求
- •Google 账号和 Gmail API 凭证
- •HTTP Webhook 端点（n8n 会自动生成）
- •Supabase URL 和 API Key
分类
-
工作流预览
可视化展示节点连接关系，支持缩放和平移
导出工作流
复制以下 JSON 配置到 n8n 导入，即可使用此工作流
{
"meta": {
"instanceId": "834bc6c387a1c56d0622a24b912577f9e6d66c5873f4e6426166054eb488d8fc",
"templateCredsSetupCompleted": true
},
"nodes": [
{
"id": "91382688-a0fe-4742-8ef3-5ea6ae60cf7a",
"name": "Webhook",
"type": "n8n-nodes-base.webhook",
"position": [
-2220,
480
],
"webhookId": "b7ad1332-8112-4ba1-bd78-af223e1566d7",
"parameters": {
"path": "b7ad1332-8112-4ba1-jotform",
"options": {},
"httpMethod": "POST"
},
"typeVersion": 2
},
{
"id": "e34d7b7d-2f72-4219-955f-0b9cf0f7767c",
"name": "分离",
"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": "更新插入表单提交 -错误",
"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": "更新插入客户 -错误",
"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": "当点击\"测试工作流\"时",
"type": "n8n-nodes-base.manualTrigger",
"disabled": true,
"position": [
-3020,
1120
],
"parameters": {},
"typeVersion": 1
},
{
"id": "da86a862-9673-4403-a53d-2e11c76a92dc",
"name": "阶段 1: Webhook 处理",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1880,
-320
],
"parameters": {
"color": 4,
"width": 600,
"height": 1220,
"content": "## 📥 阶段 1: 表单提交处理"
},
"typeVersion": 1
},
{
"id": "ea6aae0b-6f27-4163-9dd3-4e0fef2c70ed",
"name": "阶段 2: CRM 插入",
"type": "n8n-nodes-base.stickyNote",
"position": [
-1120,
-320
],
"parameters": {
"color": 5,
"width": 600,
"height": 680,
"content": "## 👤 阶段 2: 客户与交易创建"
},
"typeVersion": 1
},
{
"id": "45ce9276-6205-4082-87eb-ec4c2bd138a5",
"name": "阶段 3: 报价生成",
"type": "n8n-nodes-base.stickyNote",
"position": [
-380,
-320
],
"parameters": {
"color": 6,
"width": 1120,
"height": 1160,
"content": "## 💰 阶段 3: 报价计算引擎"
},
"typeVersion": 1
},
{
"id": "c1e7d64b-2d95-47c6-bb2b-893829df10ff",
"name": "解析器",
"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": "便签1",
"type": "n8n-nodes-base.stickyNote",
"position": [
-3140,
620
],
"parameters": {
"width": 660,
"height": 760,
"content": "# 💡 体验我们的报价系统"
},
"typeVersion": 1
},
{
"id": "c9bd65eb-077d-4121-abe8-ce951b488cd0",
"name": "保存表单提交",
"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": {
"id": "1TKWhxzbgDTvPzJH",
"name": "Supabase Jotform"
}
},
"retryOnFail": true,
"typeVersion": 1,
"waitBetweenTries": 3000
},
{
"id": "03f3eb45-f8f1-4fc0-bb4e-d874d143519e",
"name": "更新插入客户",
"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": {
"id": "1TKWhxzbgDTvPzJH",
"name": "Supabase Jotform"
}
},
"typeVersion": 1
},
{
"id": "83b46cfd-3d21-4a58-9919-216718375de1",
"name": "创建交易",
"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": {
"id": "1TKWhxzbgDTvPzJH",
"name": "Supabase Jotform"
}
},
"retryOnFail": true,
"typeVersion": 1,
"waitBetweenTries": 3000
},
{
"id": "c1a98178-4a69-47ac-a8fe-b0d18ca45b49",
"name": "获取定价规则",
"type": "n8n-nodes-base.supabase",
"position": [
-340,
520
],
"parameters": {
"tableId": "service_rules_enriched",
"operation": "getAll",
"returnAll": true
},
"credentials": {
"supabaseApi": {
"id": "1TKWhxzbgDTvPzJH",
"name": "Supabase Jotform"
}
},
"typeVersion": 1
},
{
"id": "233eb854-f2ba-46dd-a041-a526ddd9b150",
"name": "计算报价行项目",
"type": "n8n-nodes-base.code",
"position": [
-160,
520
],
"parameters": {
"jsCode": "// ================================================================\n// BUILD LINE ITEMS -- With Fallback Mapping\n// ================================================================\nconst DEFAULTS = { VAT_RATE: 21, PRICE_MULTIPLIER: 1 };\n\n// ================================================================\n// VALUE NORMALIZATION MAP (fallback if not pre-normalized)\n// ================================================================\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// ================================================================\n// GET AND VALIDATE INPUTS\n// ================================================================\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// ================================================================\n// HELPERS\n// ================================================================\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// ================================================================\n// FILTER & ACTIVATE RULES\n// ================================================================\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// ================================================================\n// BUILD RAW LINE ITEMS\n// ================================================================\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// ================================================================\n// GROUP IDENTICAL ITEMS\n// ================================================================\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// ================================================================\n// VALIDATE\n// ================================================================\nif (!lines.length)\n throw new Error(\"BUILD_LINE_ITEMS: No line items matched - check rules and normalized data\");\n\n// ================================================================\n// CALCULATE TOTALS\n// ================================================================\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// ================================================================\n// GET DEAL_ID\n// ================================================================\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// ================================================================\n// RETURN RESULTS\n// ================================================================\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": "保存估算头",
"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": {
"id": "1TKWhxzbgDTvPzJH",
"name": "Supabase Jotform"
}
},
"typeVersion": 1
},
{
"id": "71ab5d4a-fcdb-4c73-838e-cba1badde940",
"name": "插入行项目",
"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": {
"id": "1TKWhxzbgDTvPzJH",
"name": "Supabase Jotform"
}
},
"executeOnce": false,
"retryOnFail": true,
"typeVersion": 1,
"waitBetweenTries": 3000
},
{
"id": "ddfe1924-d3c7-4f59-b0dc-dd0a6ae22b0a",
"name": "准备行项目数据",
"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": "获取完整报价",
"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": {
"id": "1TKWhxzbgDTvPzJH",
"name": "Supabase Jotform"
}
},
"executeOnce": true,
"retryOnFail": true,
"typeVersion": 1,
"waitBetweenTries": 4000
},
{
"id": "2d56a903-f00f-458b-a3cf-227fa4ab3f2b",
"name": "生成邮件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;\">€${Number(item.unit_price).toFixed(2)}</p>\n <p style=\"font-size: 12px; color: #999; margin: 0;\">per ${item.unit_type || 'm²'}</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;\">€${Number(item.line_total).toFixed(2)}</p>\n <p style=\"font-size: 11px; color: #666; margin: 0;\">+€${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;\">📋 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;\">€${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;\">€${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;\">€${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 ✔️ 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 📅 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;\">⏰ 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 📞 +31 20 123 4567 | ✉️ info@yourcompany.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 ⚖️ 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 → <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 © ${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// ============================================================\n// MAIN EXECUTION - Map from v_estimate_proforma view\n// ============================================================\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": "获取映射规则",
"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": {
"id": "1TKWhxzbgDTvPzJH",
"name": "Supabase Jotform"
}
},
"typeVersion": 1
},
{
"id": "24829399-a739-47ac-8f49-ef0d69c58ad3",
"name": "准备 AI 上下文",
"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": "规范化表单数据",
"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": "阶段 3: 报价生成1",
"type": "n8n-nodes-base.stickyNote",
"position": [
880,
-580
],
"parameters": {
"color": 3,
"width": 880,
"height": 940,
"content": "## 📧 阶段 4: 专业报价邮件"
},
"typeVersion": 1
},
{
"id": "24fa29f7-0d5a-4426-94b4-7b65d7d57fbc",
"name": "便签",
"type": "n8n-nodes-base.stickyNote",
"position": [
-3160,
-220
],
"parameters": {
"width": 900,
"height": 540,
"content": "## 自动化报价生成系统"
},
"typeVersion": 1
},
{
"id": "0d8541d8-5adf-440f-8f6e-e59cf4e728c9",
"name": "发送邮件节点",
"type": "n8n-nodes-base.gmail",
"position": [
1460,
180
],
"webhookId": "480e9f8d-7ef2-44af-964c-bd4684af3d7d",
"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": {
"id": "HjgbZVUABUJxZWZD",
"name": "newGMAIL"
}
},
"typeVersion": 2.1
},
{
"id": "d9ce54c4-9beb-4565-a92a-ac5b5e9bf754",
"name": "SQL 模式生成器",
"type": "n8n-nodes-base.code",
"disabled": true,
"position": [
-2820,
1120
],
"parameters": {
"jsCode": "// ============================================\n// SQL SCHEMA GENERATOR FOR SUPABASE\n// ============================================\n// Outputs production-ready SQL for n8n Quote Automation workflow\n// Copy output → Paste in Supabase SQL Editor → Run\n\nconst generateSupabaseSchema = () => {\n return `-- ============================================\n-- N8N QUOTE AUTOMATION - SUPABASE SCHEMA\n-- ============================================\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-- ============================================\n\n-- Enable UUID extension\nCREATE EXTENSION IF NOT EXISTS \"uuid-ossp\";\n\n-- ============================================\n-- 1. CUSTOMERS\n-- ============================================\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-- ============================================\n-- 2. FORM_SUBMISSIONS\n-- ============================================\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-- ============================================\n-- 3. DEALS\n-- ============================================\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-- ============================================\n-- 4. PRICE_CATALOG\n-- ============================================\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-- ============================================\n-- 5. SERVICE_RULES\n-- ============================================\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-- ============================================\n-- 6. ESTIMATES\n-- ============================================\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-- ============================================\n-- 7. ESTIMATE_LINE_ITEMS\n-- ============================================\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-- ============================================\n-- 8. FORM_FIELD_MAPPINGS\n-- ============================================\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-- ============================================\n-- 9. FORM_VALUE_MAPPINGS\n-- ============================================\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-- ============================================\n-- 10. INVOICES (Future expansion)\n-- ============================================\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-- ============================================\n-- 11. INVOICE_LINE_ITEMS (Future expansion)\n-- ============================================\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-- ============================================\n-- 12. PROFILES (User management)\n-- ============================================\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-- ============================================\n-- 13. OPTIMIZED VIEW - V_ESTIMATE_PROFORMA\n-- ============================================\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_vat,\n e.grand_total,\n e.currency,\n e.valid_until,\n e.created_at::DATE AS estimate_date,\n \n c.customer_id,\n c.first_name,\n c.last_name,\n c.first_name || ' ' || c.last_name AS customer_full_name,\n c.email AS customer_email,\n c.phone AS customer_phone,\n c.billing_address,\n \n d.deal_id,\n d.form_submission_id,\n d.status AS deal_status,\n \n fs.form_id,\n fs.received_at AS form_received_at,\n fs.raw_payload->>'q3_project_postcode' AS project_postcode,\n fs.raw_payload->>'q9_property_status' AS property_status,\n fs.raw_payload->'q31_desired_timeline'->>'year' || '-' || \n fs.raw_payload->'q31_desired_timeline'->>'month' || '-' || \n fs.raw_payload->'q31_desired_timeline'->>'day' AS project_start_date,\n fs.raw_payload->>'q24_project_description' AS project_description,\n fs.form_submission_id AS raw_form_submission_id,\n \n COALESCE(\n (\n SELECT jsonb_agg(\n jsonb_build_object(\n 'sort_order', eli.sort_order,\n 'catalog_id', eli.catalog_id,\n 'item_code', pc.item_code,\n 'item_name', pc.name,\n 'unit_type', pc.unit_type,\n 'description', eli.description,\n 'quantity', eli.quantity,\n 'unit_price', eli.unit_price,\n 'vat_rate', eli.vat_rate,\n 'line_total', eli.line_total,\n 'vat_amount', eli.vat_amount,\n 'total_with_vat', eli.total_with_vat,\n 'currency', pc.currency\n ) ORDER BY eli.sort_order\n )\n FROM public.estimate_line_items eli\n LEFT JOIN public.price_catalog pc ON eli.catalog_id = pc.catalog_id\n WHERE eli.estimate_id = e.estimate_id\n ),\n '[]'::jsonb\n ) AS line_items\n \nFROM public.estimates e\nJOIN public.deals d ON e.deal_id = d.deal_id\nJOIN public.customers c ON d.customer_id = c.customer_id\nLEFT JOIN public.form_submissions fs ON d.form_submission_id = fs.form_submission_id;\n\n-- ============================================\n-- 14. SAMPLE DATA - PRICE CATALOG\n-- ============================================\nINSERT INTO public.price_catalog (item_code, name, name_nl, description, description_nl, unit_type, unit_price, vat_rate) VALUES\n('PLASTER_WALL_SMOOTH', 'Plaster Walls (Smooth)', 'Glad stucwerk muren', 'Smooth internal wall plaster finish ready for paint or wallpaper.', 'Glad afgewerkt stucwerk voor muren, klaar voor sausen of behangen.', 'm²', 21.28, 9),\n('PLASTER_CEILING_SMOOTH', 'Plaster Ceilings (Smooth)', 'Glad stucwerk plafonds', 'Smooth ceiling plaster ready for paint or wallpaper.', 'Glad afgewerkt stucwerk voor plafonds.', 'm²', 21.00, 9),\n('PLASTER_CEILING_DECORATIVE', 'Decorative Ceiling Plaster', 'Sierpleister plafond', 'Decorative plaster finish for ceilings.', 'Decoratieve stuclaag voor plafonds.', 'm²', 32.00, 9),\n('PLASTER_WALL_DECORATIVE', 'Decorative Wall Plaster', 'Sierpleister muren', 'Ornamental textured plaster for decorative walls.', 'Decoratieve structuurstuc voor muren.', 'm²', 28.00, 9),\n('PAINT_WALLS_WHITE', 'Paint Walls White', 'Muren schilderen wit', 'Standard white paint application on walls.', 'Standaard wit schilderwerk op muren.', 'm²', 12.50, 9)\nON CONFLICT (item_code) DO NOTHING;\n\n-- ============================================\n-- 15. SAMPLE DATA - SERVICE RULES\n-- ============================================\nINSERT INTO public.service_rules (item_code, source_field, trigger_field, trigger_value, priority, price_multiplier) VALUES\n('PLASTER_WALL_SMOOTH', 'm2_walls', 'core_service_type', 'glad_stucwerk', 100, 1.00),\n('PLASTER_CEILING_SMOOTH', 'm2_ceilings', 'core_service_type', 'glad_stucwerk', 100, 1.00),\n('PLASTER_CEILING_DECORATIVE', 'm2_ceilings', 'core_service_type', 'sierpleister', 100, 1.00),\n('PLASTER_WALL_DECORATIVE', 'm2_walls', 'core_service_type', 'sierpleister', 100, 1.00),\n('PAINT_WALLS_WHITE', 'm2_walls', 'core_service_type', 'schilderwerk', 90, 1.00)\nON CONFLICT (rule_id) DO NOTHING;\n\n-- ============================================\n-- 16. SAMPLE DATA - FORM VALUE MAPPINGS\n-- ============================================\nINSERT INTO public.form_value_mappings (field_name, jotform_value, normalized_value, notes) VALUES\n('core_service_type', 'Glad stucwerk (Saus- of Behangklaar)', 'glad_stucwerk', 'Smooth plaster finish'),\n('core_service_type', 'Sierpleister (Decoratieve structuur)', 'sierpleister', 'Decorative textured plaster'),\n('core_service_type', 'Schuurwerk en afwerken', 'schuurwerk', 'Sanding and finishing'),\n('core_service_type', 'Spuitwerk (latex/spachtelputz)', 'spuitwerk', 'Spray application'),\n('property_status', 'nieuwbouw', 'new_build', 'New construction'),\n('property_status', 'Renovatie', 'existing', 'Renovation project'),\n('ceiling_height', 'Standaard (2.4-2.6m)', 'standard', 'Standard ceiling height'),\n('ceiling_height', 'Hoog (2.7-3.5m)', 'high', 'High ceilings'),\n('ceiling_height', 'Zeer hoog (>3.5m)', 'very_high', 'Very high ceilings')\nON CONFLICT (mapping_id) DO NOTHING;\n\n-- ============================================\n-- SETUP COMPLETE!\n-- ============================================\n-- Next steps:\n-- 1. Verify all tables created: Go to Table Editor and refresh\n-- 2. Check sample data: SELECT * FROM price_catalog;\n-- 3. Update n8n Supabase credentials (Project URL + Service Role Key)\n-- 4. Test workflow with demo form: https://form.jotform.com/252844786304060\n-- 5. Customize services in price_catalog table\n-- 6. Add your pricing rules to service_rules table\n--\n-- Troubleshooting:\n-- - If errors occur, run sections individually\n-- - Check Supabase logs for detailed error messages\n-- - Ensure uuid-ossp extension is enabled\n-- ============================================`;\n};\n\n// Main execution\nconst sqlSchema = generateSupabaseSchema();\n\nreturn {\n json: {\n sql_schema: sqlSchema,\n instructions: \"Copy the 'sql_schema' output and paste it into Supabase SQL Editor, then click Run.\",\n character_count: sqlSchema.length,\n estimated_execution_time: \"~10 seconds\"\n }\n};"
},
"typeVersion": 2
}
],
"pinData": {
"Webhook": [
{
"ai": {
"content": "Project Basics:Existing Property, Project Postcode:9404EC, Which service(s) do you need? :Smooth Plaster (Paint or Wallpaper Ready), How many m² of ceilings for this service?:123, How many m² of walls for this service?:70, Is stucco work needed in a wet area? :NO, How high are the ceilings?:High (2.7-3.5m / 8.9-11.5 ft), Does anything need to be removed?:Yes, wallpaper needs removal, When do you want to start?:2026 11 02, Name:Stefan K, Email:hardskill.exchange@gmail.com, Address:Boumaboulevard, Groningen, Netherlands Boumaboulevard Groningen Groningen 9723 Netherlands, Phone Number:86 156 21124179"
},
"body": {
"rawRequest": {
"path": "/submit/252844786304060",
"slug": "submit/252844786304060",
"preview": "true",
"buildDate": "1760489218869",
"q21_typeA": "High (2.7-3.5m / 8.9-11.5 ft)",
"submitDate": "1760489309434",
"submitSource": "form",
"timeToSubmit": "20",
"q14_m2_walls14": "70",
"q17_qty_pieces": "",
"q18_qty_meters": "",
"q4_m2_ceilings": "123",
"dropdown_search": "Netherlands",
"uploadServerUrl": "https://upload.jotform.com/upload",
"q15_finish_level": "",
"q26_customer_name": {
"last": "K",
"first": "Stefan"
},
"jsExecutionTracker": "build-date-1760489218869=>init-started:1760489219755=>validator-called:1760489219793=>validator-mounted-false:1760489219794=>init-complete:1760489219798=>interval-complete:1760489240801=>observerSubmitHandler_received-submit-event:1760489309416=>submit-validation-passed:1760489309428=>observerSubmitHandler_validation-passed-submitting-form:1760489309434",
"q27_customer_email": "hardskill.exchange@gmail.com",
"q29_customer_phone": {
"area": "156",
"phone": "21124179",
"country": "86"
},
"q9_property_status": "Existing Property",
"q3_project_postcode": "9404EC",
"q28_customer_address": {
"city": "Groningen",
"state": "Groningen",
"postal": "9723",
"country": "Netherlands",
"addr_line1": "Boumaboulevard",
"addr_line2": "",
"addr_search": "Boumaboulevard, Groningen, Netherlands"
},
"q31_desired_timeline": {
"day": "02",
"year": "2026",
"month": "11"
},
"q10_core_service_type": [
"Smooth Plaster (Paint or Wallpaper Ready)"
],
"q20_includes_wet_area": "NO",
"q23_prep_removal_needed": "Yes, wallpaper needs removal",
"q24_project_description": "",
"validatedNewRequiredFieldIDs": "{\"new\":1,\"id_9\":\"Ex\",\"id_3\":\"94\",\"id_10\":\"Ex\",\"id_26\":\"K\",\"id_28\":\"97\"}"
}
},
"submission_id": "6362985104611119046"
}
]
},
"connections": {
"Parser": {
"main": [
[
{
"node": "Fetch Mapping Rules",
"type": "main",
"index": 0
},
{
"node": "Save Form Submission",
"type": "main",
"index": 0
}
]
]
},
"Webhook": {
"main": [
[
{
"node": "Parser",
"type": "main",
"index": 0
}
]
]
},
"Split Out": {
"main": [
[
{
"node": "Insert Line Items",
"type": "main",
"index": 0
}
]
]
},
"Create Deal": {
"main": [
[
{
"node": "Fetch Pricing Rules",
"type": "main",
"index": 0
}
]
]
},
"Upsert Customer": {
"main": [
[
{
"node": "Create Deal",
"type": "main",
"index": 0
}
],
[
{
"node": "upsert form customer -error",
"type": "main",
"index": 0
}
]
]
},
"Insert Line Items": {
"main": [
[
{
"node": "Fetch Complete Quote",
"type": "main",
"index": 0
}
]
]
},
"Prepare AI Context": {
"main": [
[
{
"node": "Normalize Form Data",
"type": "main",
"index": 0
}
]
]
},
"Fetch Mapping Rules": {
"main": [
[
{
"node": "Prepare AI Context",
"type": "main",
"index": 0
}
]
]
},
"Fetch Pricing Rules": {
"main": [
[
{
"node": "Calculate Quote Line Items",
"type": "main",
"index": 0
}
]
]
},
"Generate Email HTML": {
"main": [
[
{
"node": "Send Email node",
"type": "main",
"index": 0
}
]
]
},
"Normalize Form Data": {
"main": [
[
{
"node": "Upsert Customer",
"type": "main",
"index": 0
}
]
]
},
"Fetch Complete Quote": {
"main": [
[
{
"node": "Generate Email HTML",
"type": "main",
"index": 0
}
]
]
},
"Save Estimate Header": {
"main": [
[
{
"node": "Prepare Line Item Data",
"type": "main",
"index": 0
}
]
]
},
"Save Form Submission": {
"main": [
[],
[
{
"node": "upsert form submission -error",
"type": "main",
"index": 0
}
]
]
},
"Prepare Line Item Data": {
"main": [
[
{
"node": "Split Out",
"type": "main",
"index": 0
}
]
]
},
"Calculate Quote Line Items": {
"main": [
[
{
"node": "Save Estimate Header",
"type": "main",
"index": 0
}
]
]
},
"When clicking ‘Test workflow’": {
"main": [
[
{
"node": "SQL Schema Generator",
"type": "main",
"index": 0
}
]
]
}
}
}常见问题
如何使用这个工作流？
复制上方的 JSON 配置代码，在您的 n8n 实例中创建新工作流并选择「从 JSON 导入」，粘贴配置后根据需要修改凭证设置即可。
这个工作流适合什么场景？
高级
需要付费吗？
本工作流完全免费，您可以直接导入使用。但请注意，工作流中使用的第三方服务（如 OpenAI API）可能需要您自行付费。
相关工作流推荐
使用VEXA、OpenAI和Mem0自动化会议智能分析
使用VEXA、OpenAI和Mem0自动化会议对话洞察分析
Set
Code
Merge
+13
26 节点Stephan Koning
文档提取
使用GPT-5 nano和Yoast SEO自动化WordPress SEO优化
使用GPT-5 nano和Yoast SEO自动化WordPress SEO优化
Set
Code
Gmail
+11
35 节点Oriol Seguí
杂项
使用GPT-4o AI分析和多格式报告运行完整技术SEO审计
使用GPT-4o AI分析和多格式报告运行完整技术SEO审计
Set
Xml
Code
+14
45 节点Oriol Seguí Rotllant
人工智能
使用GPT-4o-mini的技术SEO审计与多格式报告（Sheets-Email）
使用GPT-4o-mini的技术SEO审计与多格式报告（Sheets/Email）
Set
Xml
Code
+14
45 节点Oriol Seguí
内容创作
AI驱动YouTube产品评论自动分析
基于Apify和GPT的AI驱动YouTube产品评论自动分析
Set
Code
Gmail
+10
25 节点Oriol Seguí
市场调研
潜在客户开发与邮件工作流
使用Google Maps、SendGrid和AI自动化B2B潜在客户开发与邮件营销
If
Set
Code
+21
141 节点Ezema Kingsley Chibuzo
潜在客户开发
工作流信息
难度等级
高级
节点数量27
分类-
节点类型9
作者
Stephan Koning
@reklaimAccount Executive by day , Noco builder for fun at night and always a proud dad of Togo the Samoyed.
外部链接
在 n8n.io 查看 →
分享此工作流