Build a support knowledge base from historical Gmail emails with OpenAI and PostgreSQL

KB Builder — Historical Emails n8n Workflow Template --- 📘 Description This workflow automates the process of building a structured Knowledge Base (KB) from your existing Gmail inbox by processing historical email threads, extracting customer-support conversation pairs, classifying them with AI, generating vector embeddings, and storing everything in a PostgreSQL database — ready to power AI-assisted draft generation in downstream workflows. The workflow is triggered manually and fetches the last N emails from your connected Gmail account. Each email is parsed, filtered, and deduplicated before being processed. A thread-level fetch retrieves the full conversation context, separating customer messages from support replies. An AI classification step categorises each thread into a defined scenario type. Vector embeddings are generated for semantic similarity search. Qualified threads are inserted into three database tables — kbdata for clean Q&A pairs, scenariopatterns for reusable handling logic, and corrections for diverse real-world examples — with strict deduplication to keep the KB clean and non-redundant. --- ⚙️ What This Workflow Does (Step-by-Step) 📥 Manual Trigger — Run on demand whenever you want to import a batch of historical emails into the KB. No scheduling required. 📬 Fetch Emails from Gmail — Pulls the last N emails (configurable, default 100) from your Gmail account using OAuth2. Returns full email metadata including thread IDs, sender, subject, labels, and body. 🔍 Parse & Filter — Extracts structured fields from each email: sender address, subject, body (cleaned of links and quoted history), date, and thread ID. Filters out emails from your own domain (outbound support replies) and auto-generated emails (no-reply, notifications, newsletters). Only genuine inbound customer emails proceed. 🧵 Fetch Full Thread — For each qualified email, fetches the entire Gmail thread using the thread ID. Retrieves all messages in the conversation to identify the original customer message and the latest support reply. 🗂️ Assemble Thread Context — Reconstructs the conversation by separating customer messages from support replies. Builds a clean conversationText block used as AI input. Flags threads with no support reply for conditional handling. 🤖 AI Classification — Calls GPT-4o-mini (or Claude) with the assembled conversation. Returns a structured JSON output containing: scenario category, a concise Q&A pair, a handling pattern description, key entities, sentiment, resolution status, and a summary. 🔢 Generate Embeddings — Calls the OpenAI Embeddings API to generate a 1536-dimension vector for both the KB entry and the correction record. Used for semantic similarity search in the downstream draft-generation workflow. 🔁 Duplicate Detection & DB Insert — Performs cosine similarity checks against existing records before inserting. KB entries and scenario patterns are blocked if a match exceeds 92% similarity. Corrections are inserted freely (deduped at 92%) since diverse examples improve AI draft quality. New records are written to three tables: kbdata, scenariopatterns, and corrections. --- 🧩 Prerequisites - Gmail account — OAuth2 credentials connected in n8n. The account must be the support inbox you want to import from. - OpenAI API key — Used for GPT-4o-mini classification (~$0.002–0.005 per thread) and text-embedding-3-small for vector generation (~$0.0001 per record). - PostgreSQL database — With the pgvector extension enabled. Must have the three tables set up per the schema below. - n8n instance — Self-hosted or cloud. Requires the PostgreSQL and OpenAI nodes. --- 🗄️ Database Schema Table: kbdata | Field | Type | Notes | |---|---|---| | id | SERIAL PRIMARY KEY | Auto-increment | | threadid | TEXT | Gmail thread ID | | subject | TEXT | Email subject | | category | TEXT | AI-assigned scenario type | | question | TEXT | Customer issue / question | | answer | TEXT | Support resolution | | entities | TEXT | Key entities