Chat with your database - Postgres Database Chat Assistant – Full Workflow Guide

Welcome to this guide on building a Postgres Database Chat Assistant. Instead of writing SQL, team members can simply ask questions in plain English and get instant answers from your Postgres database

What We’re Building

We’re creating a conversational interface that lets users:

• Ask natural-language questions (e.g., “How many rows are in the users table?”)

• Have the AI agent generate the correct SQL on the fly

• Execute that SQL against Postgres

• Return a formatted, human-readable answer

All of this happens seamlessly via n8n nodes and a (new n8n) AI agent, so users never need to touch SQL or manage custom server code.

---

How It Works

1. Chat Trigger → n8n
   A user sends a JSON payload (with a messages array) to an n8n webhook.

2. AI Agent (new n8n) Interprets Intent
   The AI agent examines the user’s text and decides which tool to call:

   • List schemas/tables

   • Show table definition (columns, types, keys)

   • Run a custom SQL query

3. Tool Nodes Interact with Postgres

   • Get DB Schema and Tables List
     Returns every schema.table pair so the AI agent knows which tables exist.

   • Get Table Definition
     Returns column names, data types, nullability, defaults, and foreign keys for a specified table.

   • Execute SQL Query
     Runs the AI-generated SQL and returns raw rows.

4. AI Agent Formats Results
   Once a tool’s output arrives, the AI agent transforms the raw JSON into a readable summary or a markdown-style table.

5. Chat History (Memory Buffer)
   Keeps the last few messages (default: 5) in context so that follow-ups like “What about July?” work seamlessly.

---

What This Unlocks

• 🚀 Instant Insights
  Anyone can get data by simply asking in plain English—no SQL knowledge required.

• 🔄 Always Up to Date
  Schema changes are automatically reflected whenever the AI agent calls the schema‐listing or definition tools.

• 📈 Scalable & Safe
  By default, the AI agent only runs safe, read‐only queries. No accidental DROP TABLE or destructive commands.

• 🔒 Auditability
  Every query can be logged (via an optional Google Sheets or Airtable node), so you know who asked what and when.

• 💬 Conversational Experience
  Follow-up questions naturally refer back to earlier context, making the interaction feel like a real conversation.

---

Use Cases

1. Data Exploration
   A product manager asks, “Which tables contain a column named ‘email’?”

   • The assistant locates all tables with an email column by querying schema and column metadata.

2. Ad-Hoc Reporting
   Sales asks, “Show me total revenue by month for the past year.”

   • The assistant generates and runs:

SELECT DATE_TRUNC('month', order_date) AS month, SUM(total_amount) AS revenue FROM sales.orders WHERE order_date >= NOW() - INTERVAL '1 year' GROUP BY month ORDER BY month;

• It returns a month-by-month revenue breakdown.

1. Quick Troubleshooting
   A developer says, “Show me any rows in users where is_active = false, limit to 5.”

   • The assistant runs:

SELECT * FROM public.users WHERE is_active = false LIMIT 5;

It displays a sample of inactive users.

1. Schema Discovery & Onboarding
   A new analyst asks, “What tables do we have in the inventory schema?”

   • The assistant lists all inventory.* tables without requiring the analyst to explore pgAdmin or psql.

---

Implementation Steps

Below is a simplified, step-by-step guide to build this workflow in n8n. We’ve focused on essential configuration and omitted low-level details like node positions and type versions.

1. Create a New Workflow in n8n

1. Log into your n8n instance.

2. Click Workflows → New Workflow (or the ➕ icon).

3. Name it Postgres Database Chat Assistant.

2. Add & Configure Nodes

2.1 Chat Trigger

• Node: Chat Trigger

• Purpose: Receives incoming HTTP POST requests containing chat messages. Note: The native chat trigger doesn’t need much setup, the below is for a custom one.

• Configuration:

  • Webhook Path: e.g., /postgres-chat

  • Method: POST

2.2 AI Agent

• Node: AI Agent

• Purpose:

  1. Reads the user’s message.

  2. Determines which Postgres tool to call (schema list, table definition, or custom SQL).

  3. After getting results from a tool, formats them into readable text.

• Configuration:

  • Agent Type: openAiFunctionsAgent (so it can call tools)

  • System Prompt (copy exactly, including line breaks):

You are DB assistant. You need to run queries in the database aligned with user requests. Run custom SQL queries to aggregate or fetch data and respond to the user. Make sure every table name uses its schema prefix (e.g., public.users) by using the ‘Get DB Schema and Tables List’ tool whenever necessary. If the user needs column info, use the ‘Get Table Definition’ tool by passing schema_name and table_name. Tools available: - Execute SQL Query: Runs any SQL query. - Get DB Schema and Tables List: Returns every schema.table pair. - Get Table Definition: Returns column names and metadata for a given table.

• Tool Definitions: Make sure the AI Agent can call these three tool nodes (added next).

2.3 OpenAI Chat Model

• Node: OpenAI Chat Model (LM Chat OpenAI)

• Purpose: Provides the LLM (e.g., gpt-4o-mini) that the AI agent uses to interpret user intent and generate SQL or tool calls.

• Configuration:

  • Model: gpt-4o-mini (or another chat-capable model)

  • API Credential: Link your OpenAI API key (create an “OpenAI” credential first).

2.4 Chat History (Memory Buffer)

• Node: Memory Buffer Window

• Purpose: Retains the last 5 messages (or so) for context, enabling follow-up queries.

• Configuration: Default settings are fine (window size = 5).

2.5 Execute SQL Query

• Node: Postgres Tool

• Purpose: Runs arbitrary SQL queries generated by the AI agent.

• Configuration:

  • Operation: executeQuery

  • Query Field: {{ $fromAI("sql_query", "SQL Query") }}

    • This placeholder is replaced at runtime with the SQL string the AI agent produced.

  • Description (optional): “Runs the SQL query returned by the AI Agent.”

  • Postgres Credential: Link a Postgres credential (host, port, database, user, password).

2.6 Get DB Schema & Tables List

• Node: Postgres Tool

• Purpose: Returns all (schema, table_name) pairs. The AI agent uses this to know which tables exist.

• Configuration:

  • Operation: executeQuery

  • Query (exactly):

SELECT table_schema, table_name FROM information_schema.tables WHERE table_type = 'BASE TABLE' AND table_schema NOT IN ('pg_catalog', 'information_schema') ORDER BY table_schema, table_name;

• Description (optional): “List all schemas and tables.”

  • Postgres Credential: Use the same credential as above.

2.7 Get Table Definition

• Node: Postgres Tool

• Purpose: Returns columns, data types, nullability, defaults, and foreign keys for a specified table. The AI agent calls this to answer “What columns does X table have?” queries.

• Configuration:

  • Operation: executeQuery

  • Query (exactly):

SELECT c.column_name, c.data_type, c.is_nullable, c.column_default, tc.constraint_type, ccu.table_name AS referenced_table, ccu.column_name AS referenced_column FROM information_schema.columns c LEFT JOIN information_schema.key_column_usage kcu ON c.table_name = kcu.table_name AND c.column_name = kcu.column_name LEFT JOIN information_schema.table_constraints tc ON kcu.constraint_name = tc.constraint_name AND tc.constraint_type = 'FOREIGN KEY' LEFT JOIN information_schema.constraint_column_usage ccu ON tc.constraint_name = ccu.constraint_name WHERE c.table_name = '{{ $fromAI("table_name") }}' AND c.table_schema = '{{ $fromAI("schema_name") }}' ORDER BY c.ordinal_position;

• Description (optional): “Get column metadata for the specified table.”

  • Postgres Credential: Same credential as above.

2.8 Sticky Notes (Optional Documentation)

• Place a few Sticky Note nodes on the canvas to remind yourself and others about the workflow. For example:

  • Sticky Note A (Setup Instructions)

🚀 Postgres Database Chat Assistant 📝 Setup 1. Enter your Postgres and OpenAI credentials 2. Click “Chat” to query your database 3. Activate the workflow to publish the chat

• Sticky Note B (Tools Overview)

🛠️ Tools Used - Execute SQL Query: Runs AI-generated SQL - Get DB Schema & Tables: Lists all schemas and tables - Get Table Definition: Returns column names and metadata

• Sticky Note C (Memory Buffer)

👆 Use “Memory Buffer” to keep the last 5 messages in context.

• Sticky Note D (Example Models)

🤖 Chat Model Using gpt-4o-mini (fast, cost-effective) Other options: gpt-4, gpt-3.5-turbo, llama-2-13b-chat, etc.