extension RAG/IA clé-en-main pour votre agent n8n (WhatsApp ↔ Google Sheets),

voici l’extension RAG/IA clé-en-main pour votre agent n8n (WhatsApp ↔ Google Sheets), avec deux niveaux :

MVP “FAQ-augmentée” (sans base vectorielle)
RAG “vrai” avec embeddings + base vectorielle (pgvector / Qdrant / Pinecone)

Source https://chatgpt.com/c/68f10435-7e9c-8329-9e20-ec889ea48b6b

Je vous donne les chaînes de nœuds, les prompts, le schéma de sortie JSON (pour parser proprement), et le circuit d’apprentissage avec validation humaine.

1) MVP “FAQ-augmentée” (sans vecteurs)

Chaîne de nœuds (après votre Lookup “faq” non concluant)

Code – Build Prompt (construit le contexte, inclut dernières Q/R et la question)
OpenAI (ou Gemini) – Chat
IF – Confiance ≥ seuil (basé sur un score renvoyé par le LLM)
- Yes → Set (Proposition d’answer + metadata)
- No → Set (Demande de précision / fallback)
Google Sheets – Append (propositions) → onglet to_review (pas directement faq)
HTTP Request (WhatsApp) → envoyer la réponse à l’utilisateur (si confiance OK), OU une question de clarification (si confiance faible)

Prompt recommandé (nœud “OpenAI Chat” → “System” + “User”)

System

Vous êtes un assistant WhatsApp relié à un tableur. Votre rôle : 1) Répondre de manière concise et fiable à partir du contexte fourni (FAQ partielle + journal). 2) Si l’information est incertaine, poser une question de clarification au lieu d’inventer. 3) Retourner STRICTEMENT un JSON valide conforme au schéma ci-dessous. Schéma de sortie JSON: { "answer": "string", // la réponse proposée à envoyer "confidence": 0..1, // score de confiance "should_ask_clarifying": true|false, "suggested_faq_entry": { // suggestion d’apprentissage "keyword": "string|null", "question": "string|null", "answer": "string|null", "tags": ["string"] }, "citations": ["string"] // facultatif, si vous déduisez la source du contexte } Rappel: répondez toujours en JSON strict (aucun texte hors JSON).

User


Contexte_FAQ:
{{ $json.faq_context }}  // (optionnel: 3-10 lignes proches, ou vos 5 dernières entrées pertinentes)

Historique_message_utilisateur:
- Dernier message: "{{ $json.wa_text }}"
- Dernières 2 interactions: {{ $json.last_turns }}

Contraintes:
- Ton: simple, poli, en français.
- Si incertain => should_ask_clarifying = true, question brève.

Tâche:
1) Proposez "answer".
2) Fixez "confidence".
3) Si la réponse comble un manque récurrent, proposez "suggested_faq_entry".
4) Sortez UNIQUEMENT le JSON final.

Extraction & Routage (nœud “Code – Parse JSON”)


const out = JSON.parse($json.data); // data = sortie brute du LLM
return [{ json: out }];

Seuils (nœud “IF”)

Confiance OK si {{$json.confidence}} >= 0.65 & {{$json.should_ask_clarifying}} == false
- → on répond directement sur WhatsApp avec json.answer
Sinon
- → envoyer une question de clarification (ex: “Pouvez-vous préciser le modèle ?”)

Apprentissage différé (HITL)

Google Sheets → Append dans onglet to_review :


ts | wa_from | user_question | proposed_answer | confidence | keyword | tags | reviewer | status

status = "PENDING"

Vue de validation (hors n8n) : quand un humain passe status = "APPROVED", un workflow n8n planifié (cron toutes les 10 min) déplace la ligne vers l’onglet faq (colonnes keyword | answer | tags), puis notifie sur Slack/Email.

2) RAG “vrai” (embeddings + base vectorielle)

Options de stockage

PostgreSQL + pgvector (self-host / low-cost, parfait si vous avez déjà Postgres)
Qdrant (open-source, simple à piloter via HTTP)
Pinecone (managé, pratique mais payant)

Schéma pgvector (exemple)


CREATE EXTENSION IF NOT EXISTS vector;

CREATE TABLE kb_chunks (
  id SERIAL PRIMARY KEY,
  doc_id TEXT,
  chunk_id TEXT,
  content TEXT,
  tags TEXT[],
  embedding vector(1536)  -- dimension selon le modèle choisi
);

-- Recherche kNN (cosine) :
-- SELECT *, 1 - (embedding <=> $1) AS similarity
-- FROM kb_chunks
-- ORDER BY embedding <=> $1
-- LIMIT 5;

Pipeline d’ingestion (workflow n8n séparé)

Google Drive / Files / Notion / CSV → (source de documents)
Split (Chunker) : découpe en blocs ~300-500 tokens + overlap
OpenAI Embeddings (ex: text-embedding-3-small)
Postgres (Insert) → kb_chunks(content, tags, embedding)

Chaîne de requête (dans votre agent)

Code – Build Query (question = wa_text)
OpenAI Embeddings (embed la question)

Postgres – Query (kNN) :


SELECT content, tags, 1 - (embedding <=> $1) AS similarity
FROM kb_chunks
ORDER BY embedding <=> $1
LIMIT 5;

Code – Build Context : concaténer top-k (seuil similarity >= 0.78 par ex.)
OpenAI/Gemini – Chat (prompt “RAG strict” ci-dessous)
IF – Confiance → réponse / clarification
Google Sheets – Append vers to_review (comme plus haut)

Prompt “RAG strict” (pour éviter le hors-piste)

System

Vous êtes un assistant RAG. NE répondez QUE à partir du CONTEXTE ci-dessous. Si le contexte ne suffit pas, demandez une précision et n'inventez pas. Répondez STRICTEMENT en JSON selon: { "answer": "string", "confidence": 0..1, "should_ask_clarifying": true|false, "suggested_faq_entry": {"keyword": "string|null", "question": "string|null", "answer": "string|null", "tags": ["string"]}, "citations": ["string"] // si possible, incluez l'identifiant doc_id/chunk_id }

User


CONTEXTE (extraits triés par similarité):
{{ $json.top_k_context }}

QUESTION:
"{{ $json.wa_text }}"

Contraintes:
- Français, concret, 1 à 3 phrases si possible.
- Si contexte insuffisant => should_ask_clarifying=true.
- JSON strict uniquement.

3) Circuit d’apprentissage avec validation humaine (HITL)

Onglets Google Sheets

to_review (propositions IA)


ts | reviewer | user_question | proposed_answer | keyword | tags | confidence | source | status

faq (après validation)


keyword | answer | tags | created_at | source

messages_log (déjà en place)

Workflow “Promote to FAQ” (cron)

Google Sheets – Read to_review WHERE status="APPROVED"
Google Sheets – Append sur faq
Google Sheets – Update la ligne to_review.status="PUBLISHED"
Slack/Email → “Entrée FAQ publiée : {{keyword}}”
(Optionnel) Re-ingestion embeddings → insérer le nouveau couple dans kb_chunks

4) Gestion des templates WhatsApp (hors fenêtre 24 h)

IF (hors 24 h) → HTTP Request (Graph API) avec un message template (HSM) approuvé côté Meta, ex. faq_followup_1 :


{
  "messaging_product": "whatsapp",
  "to": "{{wa_from}}",
  "type": "template",
  "template": {
    "name": "faq_followup_1",
    "language": { "code": "fr" },
    "components": [
      { "type": "body", "parameters": [ { "type": "text", "text": "{{username}}" } ] }
    ]
  }
}

Une fois l’utilisateur répond, vous avez de nouveau 24h en “session”.
5) Contrôles qualité & robustesse
- Parse JSON strict : entourez JSON.parse d’un try/catch → fallback “clarification”.
- Détection PII/Compliance : IF wa_text contient N° carte, IBAN, etc. → handoff humain + masquage.
- Coût / latence :
  - Embeddings : cachez les embeddings pour les requêtes proches (hash approx. de la question).
  - Chat max tokens: max_output_tokens=300, température basse (0.2–0.4).
- Langue : auto-detect possible → route vers faq_fr, faq_mg, etc.
- Observabilité : logguez confidence, similarity, latency_ms dans messages_log.

5) Contrôles qualité & robustesse

Parse JSON strict : entourez JSON.parse d’un try/catch → fallback “clarification”.
Détection PII/Compliance : IF wa_text contient N° carte, IBAN, etc. → handoff humain + masquage.
Coût / latence :
- Embeddings : cachez les embeddings pour les requêtes proches (hash approx. de la question).
- Chat max tokens: max_output_tokens=300, température basse (0.2–0.4).
Langue : auto-detect possible → route vers faq_fr, faq_mg, etc.
Observabilité : logguez confidence, similarity, latency_ms dans messages_log.

6) Exemples concrets (nœuds n8n)

Code – Build Prompt (MVP)


const lastTurns = $json.last_turns || [];
const faqContext = ($json.faq_context || []).slice(0,5).map(r => `- ${r.keyword}: ${r.answer}`).join('\n');
return [{
  json: {
    wa_text: $json.wa_text,
    last_turns: lastTurns,
    faq_context: faqContext
  }
}];

Code – Build Context (RAG top-k)


const rows = $json.knn_results || []; // [{content, doc_id, chunk_id, similarity}]
const ctx = rows.map((r,i)=>`[${i+1}] sim=${r.similarity.toFixed(3)}\n${r.content}`).join('\n---\n');
return [{ json: { wa_text: $json.wa_text, top_k_context: ctx } }];

Postgres – Query paramétrée (kNN)

Query:


SELECT doc_id, content, 1 - (embedding <=> $1) AS similarity
FROM kb_chunks
ORDER BY embedding <=> $1
LIMIT 5;

Binary Parameter: $1 = vecteur (format array → buffer) fourni par le nœud embeddings (selon connecteur n8n ; sinon HTTP → REST Qdrant/Pinecone).

7) Stratégie d’amélioration continue

Jours 1–3 : MVP sans vecteurs, seuil confiance + to_review.
Semaine 2 : Ajout RAG avec pgvector (Docker Postgres + extension).
Semaine 3 : Table “metrics” (latence, hit@k, %clarifications) + review hebdo.
Mensuel : Purge/merge des entrées FAQ redondantes, ré-embedding périodique.

Si vous le souhaitez, je peux vous livrer : JSON n8n “MVP FAQ-augmentée” prêt à importer (avec les nœuds Webhook → Parse → GS Append → LLM → IF → WhatsApp → GS to_review).

Je vous ai préparé les livrables prêts à importer/utiliser :

Workflow n8n (MVP, WhatsApp ↔ Google Sheets + fallback LLM)
Télécharger
Workflow n8n (cron de promotion to_review → faq)
Télécharger
Schéma SQL pgvector (base RAG)
Télécharger
Modèles Google Sheets (CSV)
• messages_log.csv — Télécharger
• faq.csv — Télécharger

Toujours afficher les détails
# Create deliverable files for the user: n8n workflows (JSON), SQL schema, and Google Sheets templates (CSV)
import json, os, textwrap, pandas as pd

base_dir = "/mnt/data"
os.makedirs(base_dir, exist_ok=True)
tmpl_dir = os.path.join(base_dir, "gs_templates")
os.makedirs(tmpl_dir, exist_ok=True)

# 1) n8n Workflow: WhatsApp ↔ Google Sheets with LLM fallback (MVP FAQ-augmentée)
workflow_mvp = {
  "name": "WhatsApp → n8n → GoogleSheets (+ LLM fallback) [MVP]",
  "nodes": [
    {
      "parameters": {
        "httpMethod": "POST",
        "path": "whatsapp/in",
        "responseMode": "onReceived",
        "options": {
          "binaryData": False
        }
      },
      "id": "WebhookTrigger",
      "name": "Webhook (WhatsApp)",
      "type": "n8n-nodes-base.webhook",
      "typeVersion": 1,
      "position": [200, 200]
    },
    {
      "parameters": {
        "functionCode": textwrap.dedent("""\
        // Parse WhatsApp inbound payload (Cloud API webhook)
        const entry = $json.entry?.[0];
        const value = entry?.changes?.[0]?.value;
        const msg = value?.messages?.[0] || {};
        const contact = value?.contacts?.[0] || {};
        const text =
          msg.text?.body ||
          msg.button?.text ||
          msg.interactive?.list_reply?.title ||
          msg.interactive?.button_reply?.title ||
          '';
        return [{
          json: {
            ts: new Date().toISOString(),
            wa_from: msg.from,
            wa_name: contact.profile?.name || null,
            wa_id: contact.wa_id || null,
            wa_type: msg.type || null,
            wa_text: (text || '').trim(),
            raw: $json
          }
        }];
        """)
      },
      "id": "ParseFunction",
      "name": "Code (Parse & Normalize)",
      "type": "n8n-nodes-base.function",
      "typeVersion": 2,
      "position": [450, 200]
    },
    {
      "parameters": {
        "operation": "appendOrUpdate",
        "sheetId": "",
        "range": "messages_log!A:G",
        "key": "ts",
        "options": {
          "valueInputMode": "RAW"
        },
        "columns": "ts,wa_from,wa_name,wa_text,intent,reply,status"
      },
      "id": "GSAppendLog",
      "name": "Google Sheets (Append → messages_log)",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4,
      "credentials": {
        "googleApi": {
          "id": "replace-with-your-cred-id"
        }
      },
      "position": [700, 200]
    },
    {
      "parameters": {
        "operation": "lookup",
        "sheetId": "",
        "range": "faq!A:C",
        "lookupColumn": "keyword",
        "lookupValue": "={{$json.wa_text}}",
        "options": {
          "returnAllMatches": True
        }
      },
      "id": "GSLookupFAQ",
      "name": "Google Sheets (Lookup → faq)",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4,
      "credentials": {
        "googleApi": {
          "id": "replace-with-your-cred-id"
        }
      },
      "position": [950, 120]
    },
    {
      "parameters": {
        "conditions": {
          "boolean": [],
          "string": [
            {
              "value1": "={{$json['data'] && $json['data'].length > 0}}",
              "operation": "isTrue"
            }
          ]
        }
      },
      "id": "IFMatched",
      "name": "IF (FAQ match ?)",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [1200, 120]
    },
    {
      "parameters": {
        "functionCode": textwrap.dedent("""\
        // Build reply from FAQ match (take first match)
        const rows = $json.data || [];
        const answer = rows[0]?.answer || rows[0]?.A || rows[0]?.C || rows[0]?.B || rows[0]?.value || '…';
        return [{ json: { reply: answer, intent: 'faq_match', confidence: 0.99 } }];
        """)
      },
      "id": "BuildFAQReply",
      "name": "Code (Build reply from FAQ)",
      "type": "n8n-nodes-base.function",
      "typeVersion": 2,
      "position": [1450, 40]
    },
    {
      "parameters": {
        "authentication": "predefinedCredentialType",
        "requestMethod": "POST",
        "url": "https://graph.facebook.com/v20.0/{{ $json.PHONE_NUMBER_ID || 'YOUR_PHONE_NUMBER_ID' }}/messages",
        "jsonParameters": True,
        "options": {},
        "headers": {
          "Authorization": "={{'Bearer ' + $json.WHATSAPP_TOKEN || 'Bearer YOUR_TOKEN'}}",
          "Content-Type": "application/json"
        },
        "bodyParametersJson": textwrap.dedent("""\
        {
          "messaging_product": "whatsapp",
          "to": "={{$json.wa_from}}",
          "type": "text",
          "text": { "body": "={{$json.reply}}" }
        }
        """)
      },
      "id": "SendWhatsAppFAQ",
      "name": "HTTP (Send WhatsApp reply)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4,
      "position": [1700, 40],
      "credentials": {}
    },
    {
      "parameters": {
        "functionCode": textwrap.dedent("""\
        // Build LLM prompt context (simple MVP: last FAQ rows optional)
        const userQ = $json.wa_text || '';
        const faqContext = 'Aucune correspondance trouvée';
        const payload = {
          system: `Vous êtes un assistant WhatsApp relié à un tableur. Répondez en JSON strict (voir schéma).`,
          user: `Question: "${userQ}"\\nContexte_FAQ: ${faqContext}\\nContraintes: Français, concis.`,
          schema: {
            answer: "string",
            confidence: "number (0..1)",
            should_ask_clarifying: "boolean",
            suggested_faq_entry: {
              keyword: "string|null",
              question: "string|null",
              answer: "string|null",
              tags: ["string"]
            },
            citations: ["string"]
          }
        };
        return [{ json: { llm_payload: payload } }];
        """)
      },
      "id": "BuildLLMPrompt",
      "name": "Code (Build LLM prompt)",
      "type": "n8n-nodes-base.function",
      "typeVersion": 2,
      "position": [1450, 280]
    },
    {
      "parameters": {
        "model": "gpt-4o-mini",
        "mode": "chat",
        "systemMessage": "={{$json.llm_payload.system}}",
        "prompt": "={{$json.llm_payload.user}}",
        "temperature": 0.2,
        "jsonOutput": True
      },
      "id": "OpenAIChat",
      "name": "OpenAI (Chat → JSON)",
      "type": "n8n-nodes-base.openAi",
      "typeVersion": 4,
      "position": [1700, 280],
      "credentials": {
        "openAiApi": {
          "id": "replace-with-your-openai-cred-id"
        }
      }
    },
    {
      "parameters": {
        "functionCode": textwrap.dedent("""\
        // Ensure LLM returns valid JSON
        let out = $json;
        if (typeof out === 'string') {
          try { out = JSON.parse(out); } catch(e) { out = {}; }
        }
        // Normalize fields
        out.answer = out.answer || "Pouvez-vous préciser votre demande ?";
        out.confidence = Number.isFinite(out.confidence) ? out.confidence : 0.0;
        out.should_ask_clarifying = !!out.should_ask_clarifying;
        out.suggested_faq_entry = out.suggested_faq_entry || {keyword:null,question:null,answer:null,tags:[]};
        return [{ json: out }];
        """)
      },
      "id": "ParseLLMJSON",
      "name": "Code (Parse LLM JSON)",
      "type": "n8n-nodes-base.function",
      "typeVersion": 2,
      "position": [1900, 280]
    },
    {
      "parameters": {
        "conditions": {
          "string": [
            {
              "value1": "={{$json.confidence >= 0.65 && $json.should_ask_clarifying === false}}",
              "operation": "isTrue"
            }
          ]
        }
      },
      "id": "IFConfident",
      "name": "IF (confidence ≥ 0.65 && no clarification)",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [2100, 280]
    },
    {
      "parameters": {
        "authentication": "predefinedCredentialType",
        "requestMethod": "POST",
        "url": "https://graph.facebook.com/v20.0/{{ $json.PHONE_NUMBER_ID || 'YOUR_PHONE_NUMBER_ID' }}/messages",
        "jsonParameters": True,
        "headers": {
          "Authorization": "={{'Bearer ' + $json.WHATSAPP_TOKEN || 'Bearer YOUR_TOKEN'}}",
          "Content-Type": "application/json"
        },
        "bodyParametersJson": textwrap.dedent("""\
        {
          "messaging_product": "whatsapp",
          "to": "={{$json.wa_from}}",
          "type": "text",
          "text": { "body": "={{$json.answer}}" }
        }
        """)
      },
      "id": "SendWhatsAppLLM",
      "name": "HTTP (Send WhatsApp from LLM)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4,
      "position": [2350, 220]
    },
    {
      "parameters": {
        "authentication": "predefinedCredentialType",
        "requestMethod": "POST",
        "url": "https://graph.facebook.com/v20.0/{{ $json.PHONE_NUMBER_ID || 'YOUR_PHONE_NUMBER_ID' }}/messages",
        "jsonParameters": True,
        "headers": {
          "Authorization": "={{'Bearer ' + $json.WHATSAPP_TOKEN || 'Bearer YOUR_TOKEN'}}",
          "Content-Type": "application/json"
        },
        "bodyParametersJson": textwrap.dedent("""\
        {
          "messaging_product": "whatsapp",
          "to": "={{$json.wa_from}}",
          "type": "text",
          "text": { "body": "J’ai besoin d’une précision pour bien répondre : {{ $json.answer }}" }
        }
        """)
      },
      "id": "SendWhatsAppClarify",
      "name": "HTTP (Ask Clarifying Question)",
      "type": "n8n-nodes-base.httpRequest",
      "typeVersion": 4,
      "position": [2350, 340]
    },
    {
      "parameters": {
        "operation": "appendOrUpdate",
        "sheetId": "",
        "range": "to_review!A:I",
        "key": "ts",
        "columns": "ts,reviewer,user_question,proposed_answer,keyword,tags,confidence,source,status",
        "options": {
          "valueInputMode": "RAW"
        }
      },
      "id": "GSAppendToReview",
      "name": "Google Sheets (Append → to_review)",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4,
      "credentials": {
        "googleApi": {
          "id": "replace-with-your-cred-id"
        }
      },
      "position": [2550, 280]
    },
    {
      "parameters": {
        "functionCode": textwrap.dedent("""\
        // Map fields for to_review append
        return [{
          json: {
            ts: new Date().toISOString(),
            reviewer: '',
            user_question: $json.raw?.entry?.[0]?.changes?.[0]?.value?.messages?.[0]?.text?.body || $json.wa_text || '',
            proposed_answer: $json.answer || '',
            keyword: $json.suggested_faq_entry?.keyword || '',
            tags: ($json.suggested_faq_entry?.tags || []).join(','),
            confidence: $json.confidence,
            source: 'LLM',
            status: 'PENDING'
          }
        }];
        """)
      },
      "id": "MapToReview",
      "name": "Code (Map → to_review)",
      "type": "n8n-nodes-base.function",
      "typeVersion": 2,
      "position": [2100, 520]
    }
  ],
  "connections": {
    "Webhook (WhatsApp)": { "main": [[ { "node": "Code (Parse & Normalize)", "type": "main", "index": 0 } ]] },
    "Code (Parse & Normalize)": { "main": [[
      { "node": "Google Sheets (Append → messages_log)", "type": "main", "index": 0 },
      { "node": "Google Sheets (Lookup → faq)", "type": "main", "index": 0 }
    ]]},
    "Google Sheets (Lookup → faq)": { "main": [[ { "node": "IF (FAQ match ?)", "type": "main", "index": 0 } ]] },
    "IF (FAQ match ?)": { "main": [
      [ { "node": "Code (Build reply from FAQ)", "type": "main", "index": 0 } ],
      [ { "node": "Code (Build LLM prompt)", "type": "main", "index": 0 } ]
    ]},
    "Code (Build reply from FAQ)": { "main": [[ { "node": "HTTP (Send WhatsApp reply)", "type": "main", "index": 0 } ]] },
    "Code (Build LLM prompt)": { "main": [[ { "node": "OpenAI (Chat → JSON)", "type": "main", "index": 0 } ]] },
    "OpenAI (Chat → JSON)": { "main": [[ { "node": "Code (Parse LLM JSON)", "type": "main", "index": 0 } ]] },
    "Code (Parse LLM JSON)": { "main": [[
      { "node": "IF (confidence ≥ 0.65 && no clarification)", "type": "main", "index": 0 },
      { "node": "Code (Map → to_review)", "type": "main", "index": 0 }
    ]]},
    "IF (confidence ≥ 0.65 && no clarification)": { "main": [
      [ { "node": "HTTP (Send WhatsApp from LLM)", "type": "main", "index": 0 } ],
      [ { "node": "HTTP (Ask Clarifying Question)", "type": "main", "index": 0 } ]
    ]},
    "Code (Map → to_review)": { "main": [[ { "node": "Google Sheets (Append → to_review)", "type": "main", "index": 0 } ]] }
  }
}

# 2) n8n Workflow: Promote APPROVED entries from to_review → faq (cron)
workflow_promote = {
  "name": "Promote to FAQ (cron)",
  "nodes": [
    {
      "parameters": {
        "triggerTimes": { "item": [{ "mode": "everyMinute", "minuteInterval": 10 }] }
      },
      "id": "Cron",
      "name": "Cron (every 10 min)",
      "type": "n8n-nodes-base.cron",
      "typeVersion": 1,
      "position": [200, 200]
    },
    {
      "parameters": {
        "operation": "read",
        "sheetId": "",
        "range": "to_review!A:I",
        "options": {
          "returnAllMatches": True
        }
      },
      "id": "GSReadToReview",
      "name": "Google Sheets (Read to_review)",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4,
      "credentials": {
        "googleApi": {
          "id": "replace-with-your-cred-id"
        }
      },
      "position": [450, 200]
    },
    {
      "parameters": {
        "conditions": {
          "string": [{
            "value1": "={{$json.status === 'APPROVED'}}",
            "operation": "isTrue"
          }]
        }
      },
      "id": "IFApproved",
      "name": "IF (status == APPROVED)",
      "type": "n8n-nodes-base.if",
      "typeVersion": 1,
      "position": [700, 200]
    },
    {
      "parameters": {
        "operation": "appendOrUpdate",
        "sheetId": "",
        "range": "faq!A:E",
        "key": "keyword",
        "columns": "keyword,answer,tags,created_at,source",
        "options": { "valueInputMode": "RAW" }
      },
      "id": "GSAppendFAQ",
      "name": "Google Sheets (Append → faq)",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4,
      "credentials": {
        "googleApi": {
          "id": "replace-with-your-cred-id"
        }
      },
      "position": [950, 160]
    },
    {
      "parameters": {
        "functionCode": textwrap.dedent("""\
        // Map to FAQ row
        return [{
          json: {
            keyword: $json.keyword || '',
            answer: $json.proposed_answer || '',
            tags: $json.tags || '',
            created_at: new Date().toISOString(),
            source: $json.source || 'LLM'
          }
        }];
        """)
      },
      "id": "MapFAQRow",
      "name": "Code (Map to FAQ Row)",
      "type": "n8n-nodes-base.function",
      "typeVersion": 2,
      "position": [700, 340]
    },
    {
      "parameters": {
        "operation": "update",
        "sheetId": "",
        "range": "to_review!A:I",
        "key": "ts",
        "options": { "valueInputMode": "RAW" },
        "columns": "status"
      },
      "id": "GSMarkPublished",
      "name": "Google Sheets (Update → to_review)",
      "type": "n8n-nodes-base.googleSheets",
      "typeVersion": 4,
      "credentials": {
        "googleApi": {
          "id": "replace-with-your-cred-id"
        }
      },
      "position": [1200, 260]
    }
  ],
  "connections": {
    "Cron (every 10 min)": { "main": [[ { "node": "Google Sheets (Read to_review)", "type": "main", "index": 0 } ]] },
    "Google Sheets (Read to_review)": { "main": [[
      { "node": "IF (status == APPROVED)", "type": "main", "index": 0 },
      { "node": "Code (Map to FAQ Row)", "type": "main", "index": 0 }
    ]]},
    "IF (status == APPROVED)": { "main": [[ { "node": "Google Sheets (Append → faq)", "type": "main", "index": 0 } ]] },
    "Code (Map to FAQ Row)": { "main": [[ { "node": "Google Sheets (Append → faq)", "type": "main", "index": 0 } ]] },
    "Google Sheets (Append → faq)": { "main": [[ { "node": "Google Sheets (Update → to_review)", "type": "main", "index": 0 } ]] }
  }
}

# 3) SQL schema for pgvector
pgvector_sql = textwrap.dedent("""\
-- Enable pgvector (on PostgreSQL 15+)
CREATE EXTENSION IF NOT EXISTS vector;

-- Knowledge base chunks
CREATE TABLE IF NOT EXISTS kb_chunks (
  id SERIAL PRIMARY KEY,
  doc_id TEXT,
  chunk_id TEXT,
  content TEXT NOT NULL,
  tags TEXT[],
  embedding vector(1536) -- adjust to your embedding model dimension
);

-- Example kNN query (cosine distance)
-- SELECT doc_id, content, 1 - (embedding <=> $1) AS similarity
-- FROM kb_chunks
-- ORDER BY embedding <=> $1
-- LIMIT 5;

-- Optional metadata table
CREATE TABLE IF NOT EXISTS kb_docs (
  doc_id TEXT PRIMARY KEY,
  title TEXT,
  source TEXT,
  created_at TIMESTAMP DEFAULT NOW()
);
""")

# 4) Google Sheets templates
messages_log_cols = ["ts","wa_from","wa_name","wa_text","intent","reply","status"]
faq_cols = ["keyword","answer","tags"]
to_review_cols = ["ts","reviewer","user_question","proposed_answer","keyword","tags","confidence","source","status"]

pd.DataFrame(columns=messages_log_cols).to_csv(os.path.join(tmpl_dir, "messages_log.csv"), index=False)
pd.DataFrame(columns=faq_cols).to_csv(os.path.join(tmpl_dir, "faq.csv"), index=False)
pd.DataFrame(columns=to_review_cols).to_csv(os.path.join(tmpl_dir, "to_review.csv"), index=False)

# Write files
with open(os.path.join(base_dir, "n8n_workflow_whatsapp_gs_rag_mvp.json"), "w") as f:
    json.dump(workflow_mvp, f, ensure_ascii=False, indent=2)

with open(os.path.join(base_dir, "n8n_workflow_promote_to_faq.json"), "w") as f:
    json.dump(workflow_promote, f, ensure_ascii=False, indent=2)

with open(os.path.join(base_dir, "pgvector_schema.sql"), "w") as f:
    f.write(pgvector_sql)

# Display small summary as output
file_list = [
    "/mnt/data/n8n_workflow_whatsapp_gs_rag_mvp.json",
    "/mnt/data/n8n_workflow_promote_to_faq.json",
    "/mnt/data/pgvector_schema.sql",
    "/mnt/data/gs_templates/messages_log.csv",
    "/mnt/data/gs_templates/faq.csv",
    "/mnt/data/gs_templates/to_review.csv"
]
file_list

Résultat

['/mnt/data/n8n_workflow_whatsapp_gs_rag_mvp.json',
 '/mnt/data/n8n_workflow_promote_to_faq.json',
 '/mnt/data/pgvector_schema.sql',
 '/mnt/data/gs_templates/messages_log.csv',
 '/mnt/data/gs_templates/faq.csv',
 '/mnt/data/gs_templates/to_review.csv']

Mise en route (ultra-rapide)

Importez messages_log.csv, faq.csv, to_review.csv dans un Google Spreadsheet nommé (p. ex.) CRM_WhatsApp.
Dans n8n, Import workflow → n8n_workflow_whatsapp_gs_rag_mvp.json.
- Renseignez vos Credentials :
  - Google Sheets (OAuth2 / Service Account).
  - OpenAI (ou remplacez le nœud par Gemini si vous préférez).
- Dans les nœuds Google Sheets, indiquez l’ID du spreadsheet et vérifiez les ranges (messages_log!A:G, faq!A:C, to_review!A:I).
- Dans les nœuds HTTP WhatsApp, remplacez YOUR_PHONE_NUMBER_ID et le Bearer token.
Sur Meta for Developers, validez le Webhook (URL du nœud + token).
Importez et activez le workflow Promote to FAQ (cron) pour publier automatiquement les entrées approuvées.

Souhaitez-vous que je vous ajoute aussi le workflow d’ingestion RAG (Docs → Chunk → Embeddings → Postgres kb_chunks) pour pgvector, ou que je vous remplace le nœud LLM par Gemini dans le JSON ?