This workflow follows the OpenAI → Postgres recipe pattern — see all workflows that pair these two integrations.
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 →
{
"name": "analysis",
"nodes": [
{
"parameters": {
"path": "consumo/ultimos-3-meses",
"responseMode": "responseNode",
"options": {}
},
"type": "n8n-nodes-base.webhook",
"typeVersion": 2.1,
"position": [
-224,
0
],
"id": "d1c9154e-741a-4f57-a94d-03c52068f2db",
"name": "Webhook"
},
{
"parameters": {
"path": "consumo",
"responseMode": "responseNode",
"options": {}
},
"type": "n8n-nodes-base.webhook",
"typeVersion": 2.1,
"position": [
-224,
288
],
"id": "7812c202-a6e6-4f25-b1c1-ca48bb16888e",
"name": "Webhook1"
},
{
"parameters": {
"respondWith": "json",
"responseBody": "={{ $json }}",
"options": {
"responseCode": 200,
"responseHeaders": {
"entries": [
{
"name": "Content-Type",
"value": "application/json"
}
]
}
}
},
"type": "n8n-nodes-base.respondToWebhook",
"typeVersion": 1.5,
"position": [
1136,
144
],
"id": "7bb8ab80-a45e-4195-aeab-440667d522a5",
"name": "Respond to Webhook1"
},
{
"parameters": {
"operation": "executeQuery",
"query": "SELECT\n c.id AS client_id,\n c.name AS client_name,\n AVG(r.kwh)::float AS avg_kwh,\n COUNT(*) AS readings_count\nFROM clients c\nJOIN contracts ct\n ON ct.client_id = c.id\n AND ct.is_active = TRUE\n AND ct.start_date <= CURRENT_DATE\nJOIN energy_readings r\n ON r.contract_id = ct.id\n AND r.reading_at >= (CURRENT_DATE - (INTERVAL '1 month' * {{ $json.months }}))\nGROUP BY c.id, c.name\nORDER BY c.name;\n",
"options": {}
},
"type": "n8n-nodes-base.postgres",
"typeVersion": 2.6,
"position": [
192,
144
],
"id": "84ff492f-acb6-4573-a067-b3219bb02553",
"name": "Execute a SQL query",
"credentials": {
"postgres": {
"name": "<your credential>"
}
}
},
{
"parameters": {
"jsCode": "// Resolve months window from querystring (?months=6)\n// Safe defaults and hard limits to avoid abuse / invalid intervals.\n\nconst DEFAULT_MONTHS = 3;\nconst MIN_MONTHS = 1;\nconst MAX_MONTHS = 24;\n\nconst q = items[0]?.json?.query ?? items[0]?.json?.queryParameters ?? {};\nconst raw = q.months;\n\nlet months = DEFAULT_MONTHS;\n\nif (raw !== undefined && raw !== null && String(raw).trim() !== \"\") {\n const n = Number(raw);\n if (!Number.isInteger(n)) {\n return [{\n json: {\n ok: false,\n statusCode: 400,\n error: \"Invalid 'months' parameter. It must be an integer.\",\n example: \"/webhook/consumo?months=6\",\n }\n }];\n }\n if (n < MIN_MONTHS || n > MAX_MONTHS) {\n return [{\n json: {\n ok: false,\n statusCode: 400,\n error: `Invalid 'months' parameter. Allowed range: ${MIN_MONTHS}..${MAX_MONTHS}.`,\n received: n,\n }\n }];\n }\n months = n;\n}\n\nreturn [{\n json: {\n ok: true,\n months,\n window_label: `last_${months}_months`,\n }\n}];\n"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
-16,
144
],
"id": "07f1284c-2f6b-436a-9780-6c06321f3c13",
"name": "get month"
},
{
"parameters": {
"jsCode": "/**\n * Outlier detection strategy:\n * Compute the distribution of avg_kwh across active clients (last 3 months).\n *\n * Primary method: MAD (Median Absolute Deviation) with modified z-score:\n * mz = 0.6745 * (x - median) / MAD\n * Flag outlier if |mz| > THRESH (default 3.5).\n *\n * Why MAD:\n * - Robust to extreme values (outliers don't inflate the dispersion estimate).\n * - Common baseline for anomaly detection when no domain thresholds exist.\n * - Works better than mean/std when distributions are skewed.\n *\n * Guards:\n * - If there are < 3 valid clients => no outlier detection (all \"normal\").\n * - If MAD == 0 (no dispersion) => fallback to z-score (mean \u00b1 2*std) if std > 0,\n * otherwise no outliers.\n * - Ignores invalid avg_kwh (NaN/Infinity).\n *\n * Output:\n * {\n * generated_at: ISO string,\n * window: \"last_3_months\",\n * outlier_method: string,\n * stats: object | null,\n * results: Array<{ client_name, avg_kwh, status, readings_count, score? }>\n * }\n */\n\nconst rows = items.map(i => i.json);\n\nfunction normalizeRow(r, statusOverride = null, extra = {}) {\n return {\n client_name: String(r.client_name ?? \"\"),\n avg_kwh: Number(r.avg_kwh),\n status: statusOverride ?? \"normal\",\n readings_count: Number(r.readings_count ?? 0),\n ...extra, // score\n };\n}\n\nfunction payload({ outlier_method, stats = null, results = [] }) {\n return [{\n json: {\n generated_at: new Date().toISOString(),\n window: \"last_3_months\",\n outlier_method,\n stats,\n results: Array.isArray(results) ? results : [],\n },\n }];\n}\n\nfunction median(arr) {\n const a = [...arr].sort((x, y) => x - y);\n const n = a.length;\n if (n === 0) return NaN;\n const mid = Math.floor(n / 2);\n return n % 2 ? a[mid] : (a[mid - 1] + a[mid]) / 2;\n}\n\nfunction mad(arr, med) {\n const dev = arr.map(v => Math.abs(v - med));\n return median(dev);\n}\n\n// No data\nif (!rows || rows.length === 0) {\n return payload({\n outlier_method: \"MAD modified z-score (|mz| > 3.5)\",\n stats: null,\n results: [],\n });\n}\n\n// Clean invalid avg_kwh\nconst cleaned = rows\n .map(r => ({ ...r, avg_kwh: Number(r.avg_kwh) }))\n .filter(r => Number.isFinite(r.avg_kwh));\n\nif (cleaned.length === 0) {\n return payload({\n outlier_method: \"MAD modified z-score (|mz| > 3.5)\",\n stats: { note: \"All avg_kwh values were invalid (NaN/Infinity).\" },\n results: [],\n });\n}\n\n// Too few clients for reliable outlier detection\nif (cleaned.length < 3) {\n return payload({\n outlier_method: \"MAD modified z-score (|mz| > 3.5)\",\n stats: { note: \"Too few clients (<3) for outlier detection.\" },\n results: cleaned.map(r => normalizeRow(r, \"normal\")),\n });\n}\n\n// ----- MAD method -----\nconst values = cleaned.map(r => r.avg_kwh);\nconst med = median(values);\nconst madValue = mad(values, med);\n\n// Threshold for modified z-score\nconst THRESH = 3.5;\n\n// If MAD is 0 (or non-finite), fallback to z-score\nif (!Number.isFinite(madValue) || madValue === 0) {\n // ----- Fallback: z-score (mean \u00b1 2*std) -----\n const mean = values.reduce((a, b) => a + b, 0) / values.length;\n const variance = values.reduce((acc, v) => acc + Math.pow(v - mean, 2), 0) / values.length;\n const std = Math.sqrt(variance);\n\n // Still no dispersion => no outliers\n if (!Number.isFinite(std) || std === 0) {\n return payload({\n outlier_method: \"MAD=0 => fallback z-score, but std=0 => no outliers\",\n stats: { median: med, mad: madValue, mean, std, lower: mean, upper: mean },\n results: cleaned.map(r => normalizeRow(r, \"normal\")),\n });\n }\n\n const k = 2;\n const lower = mean - k * std;\n const upper = mean + k * std;\n\n const results = cleaned.map(r => {\n const avg = r.avg_kwh;\n const isOutlier = avg < lower || avg > upper;\n // optional score: classic z\n const z = (avg - mean) / std;\n return normalizeRow(r, isOutlier ? \"outlier\" : \"normal\", { score: Number(z.toFixed(4)) });\n });\n\n return payload({\n outlier_method: `MAD=0 => fallback z-score (mean \u00b1 ${k}*std)`,\n stats: { median: med, mad: madValue, mean, std, lower, upper },\n results,\n });\n}\n\n// Normal MAD path\nconst results = cleaned.map(r => {\n const x = r.avg_kwh;\n const mz = 0.6745 * (x - med) / madValue; // modified z-score\n const isOutlier = Math.abs(mz) > THRESH;\n return normalizeRow(r, isOutlier ? \"outlier\" : \"normal\", { score: Number(mz.toFixed(4)) });\n});\n\nreturn payload({\n outlier_method: `MAD modified z-score (|mz| > ${THRESH})`,\n stats: { median: med, mad: madValue, threshold: THRESH },\n results,\n});\n"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
400,
144
],
"id": "bdbf1a80-d0a0-491d-bb3e-becaa343f7f7",
"name": "Outlier detection"
},
{
"parameters": {
"modelId": {
"__rl": true,
"value": "gpt-4o-mini",
"mode": "list",
"cachedResultName": "GPT-4O-MINI"
},
"responses": {
"values": [
{
"content": "=Com base no JSON a seguir, gere um relat\u00f3rio em portugu\u00eas com:\n- total de clientes analisados\n- quantos outliers\n- lista de outliers com suas m\u00e9dias\n- explica\u00e7\u00e3o avisando se \u00e9 maior ou menor do que o esperado\n- poss\u00edveis causas (apenas hip\u00f3teses, sem afirmar)\n- recomenda\u00e7\u00e3o de pr\u00f3xima a\u00e7\u00e3o\n- Retorne texto simples, sem Markdown, sem t\u00edtulos com #.\u201d\n\nJSON:\n{{ JSON.stringify($json.results) }}\n"
}
]
},
"builtInTools": {},
"options": {}
},
"type": "@n8n/n8n-nodes-langchain.openAi",
"typeVersion": 2.1,
"position": [
592,
144
],
"id": "d29e0d03-3861-44c8-bb4d-71a65eadb5c9",
"name": "Message a model",
"credentials": {
"openAiApi": {
"name": "<your credential>"
}
},
"onError": "continueRegularOutput"
},
{
"parameters": {
"jsCode": "const analytics = $node[\"Outlier detection\"].json; // node que produz generated_at/window/outlier_method/results/stats\nconst llm = $node[\"Message a model\"].json; // node da LLM\n\n// tenta capturar o texto independente do formato do node\nconst llm_report =\n llm?.output?.[0]?.content?.[0]?.text ??\n llm?.output?.[0]?.content?.[0]?.content ??\n llm?.output?.[0]?.content ??\n llm?.output?.[0]?.text ??\n llm?.text ??\n llm?.message ??\n llm?.error?.message ??\n null;\n\nreturn [{\n json: {\n generated_at: analytics.generated_at,\n window: analytics.window,\n outlier_method: analytics.outlier_method,\n results: analytics.results,\n stats: analytics.stats ?? null,\n llm_report,\n llm_ok: llm?.error ? false : true,\n }\n}];\n"
},
"type": "n8n-nodes-base.code",
"typeVersion": 2,
"position": [
944,
144
],
"id": "024413aa-87b1-467c-92fa-cf015ff4bef1",
"name": "Aggregate message"
}
],
"connections": {
"Webhook": {
"main": [
[
{
"node": "get month",
"type": "main",
"index": 0
}
]
]
},
"Webhook1": {
"main": [
[
{
"node": "get month",
"type": "main",
"index": 0
}
]
]
},
"Execute a SQL query": {
"main": [
[
{
"node": "Outlier detection",
"type": "main",
"index": 0
}
]
]
},
"get month": {
"main": [
[
{
"node": "Execute a SQL query",
"type": "main",
"index": 0
}
]
]
},
"Outlier detection": {
"main": [
[
{
"node": "Message a model",
"type": "main",
"index": 0
}
]
]
},
"Message a model": {
"main": [
[
{
"node": "Aggregate message",
"type": "main",
"index": 0
}
]
]
},
"Aggregate message": {
"main": [
[
{
"node": "Respond to Webhook1",
"type": "main",
"index": 0
}
]
]
}
},
"active": true,
"settings": {
"executionOrder": "v1",
"availableInMCP": false
},
"versionId": "8ae9a18f-4b9b-482a-aa58-4d683a3fad7c",
"meta": {
"templateCredsSetupCompleted": true
},
"id": "31ylqFuUlcUjqdrxS4a8r",
"tags": []
}
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.
openAiApipostgres
Pro
For the full experience including quality scoring and batch install features for each workflow upgrade to Pro
About this workflow
analysis. Uses postgres, openAi. Webhook trigger; 8 nodes.
Source: https://github.com/LucasCollevatti/solar-n8n-postgres/blob/14065be80ceec66c7e7665d709270e68ad3549dc/n8n/exports/analysis.json — original creator credit. Request a take-down →
Related workflows
Workflows that share integrations, category, or trigger type with this one. All free to copy and import.
Eu Clara – Funil Kiwify Completo. Uses postgres, openAi, httpRequest, gmail. Webhook trigger; 70 nodes.
Postgres, OpenAI, HTTP Request +1
Lua Nova - Sistema Completo. Uses postgres, httpRequest, openAi. Webhook trigger; 55 nodes.
Postgres, HTTP Request, OpenAI
User Signup & Verification: The workflow starts when a user signs up. It generates a verification code and sends it via SMS using Twilio. Code Validation: The user replies with the code. The workflow
Postgres, HTTP Request, OpenAI +2
Pyragogy AI Village - Orchestrazione Master (Architettura Profonda V2). Uses start, postgres, openAi, emailSend. Webhook trigger; 36 nodes.
Start, Postgres, OpenAI +4
Pyragogy AI Village - Orchestrazione Master (Architettura Profonda V2). Uses start, postgres, openAi, emailSend. Webhook trigger; 35 nodes.
Start, Postgres, OpenAI +3