{
  "id": "eOUewYsEzJmQixI6",
  "meta": {
    "instanceId": "77c4feba8f41570ef06dc76ece9a6ded0f0d44f7f1477a64c2d71a8508c11faa",
    "templateCredsSetupCompleted": true
  },
  "name": "Chat with Postgresql Database",
  "tags": [],
  "nodes": [
    {
      "id": "6501a54f-a68c-452d-b353-d7e871ca3780",
      "name": "When chat message received",
      "type": "@n8n/n8n-nodes-langchain.chatTrigger",
      "position": [
        -300,
        -80
      ],
      "webhookId": "cf1de04f-3e38-426c-89f0-3bdb110a5dcf",
      "parameters": {
        "options": {}
      },
      "typeVersion": 1.1
    },
    {
      "id": "cd32221b-2a36-408d-b57e-8115fcd810c9",
      "name": "AI Agent",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        0,
        -80
      ],
      "parameters": {
        "agent": "openAiFunctionsAgent",
        "options": {
          "systemMessage": "You are DB assistant. You need to run queries in DB aligned with user requests.nnRun custom SQL query to aggregate data and response to user. Make sure every table has schema prefix to it in sql query which you can get from `Get DB Schema and Tables List` tool.nnFetch all data to analyse it for response if needed.nn## Toolsnn- Execute SQL query - Executes any sql query generated by AIn- Get DB Schema and Tables List - Lists all the tables in database with its schema namen- Get Table Definition - Gets the table definition from db using table name and schema name"
        }
      },
      "typeVersion": 1.7
    },
    {
      "id": "8accbeeb-7eaf-4e9e-aabc-de8ab3a0459b",
      "name": "OpenAI Chat Model",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        -60,
        160
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini"
        },
        "options": {}
      },
      "credentials": {
        "openAiApi": {
          "id": "48uG61Ilo8jndw3r",
          "name": "Your OpenAI Account Credentials"
        }
      },
      "typeVersion": 1.2
    },
    {
      "id": "11f2013f-a080-4c9e-8773-c90492e2c628",
      "name": "Get Table Definition",
      "type": "n8n-nodes-base.postgresTool",
      "position": [
        780,
        140
      ],
      "parameters": {
        "query": "selectn  c.column_name,n  c.data_type,n  c.is_nullable,n  c.column_default,n  tc.constraint_type,n  ccu.table_name AS referenced_table,n  ccu.column_name AS referenced_columnnfromn  information_schema.columns cnLEFT joinn  information_schema.key_column_usage kcun  ON c.table_name = kcu.table_namen  AND c.column_name = kcu.column_namenLEFT joinn  information_schema.table_constraints tcn  ON kcu.constraint_name = tc.constraint_namen  AND tc.constraint_type = 'FOREIGN KEY'nLEFT joinn  information_schema.constraint_column_usage ccun  ON tc.constraint_name = ccu.constraint_namenwheren  c.table_name = '{{ $fromAI("table_name") }}'n  AND c.table_schema = '{{ $fromAI("schema_name") }}'norder byn  c.ordinal_position",
        "options": {},
        "operation": "executeQuery",
        "descriptionType": "manual",
        "toolDescription": "Get table definition to find all columns and types"
      },
      "credentials": {
        "postgres": {
          "id": "nGI61D0TEEZz18rr",
          "name": "Your Postgresql Database Credentials"
        }
      },
      "typeVersion": 2.5
    },
    {
      "id": "760bc9bc-0057-4088-b3f0-3ee37b3519df",
      "name": "Sticky Note",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -300,
        -240
      ],
      "parameters": {
        "color": 5,
        "width": 560,
        "height": 120,
        "content": "### ud83dudc68u200dud83cudfa4 Setupn1. Add your **postgresql** and **OpenAI** credentials.n2. Click **Chat** button and start asking questions to your database.n3. Activate the workflow and you can make the chat publicly available."
      },
      "typeVersion": 1
    },
    {
      "id": "0df33341-c859-4a54-b6d9-a99670e8d76d",
      "name": "Chat History",
      "type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
      "position": [
        120,
        160
      ],
      "parameters": {},
      "typeVersion": 1.3
    },
    {
      "id": "4938b22e-f187-4ca0-b9f1-60835e823799",
      "name": "Sticky Note3",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        360,
        300
      ],
      "parameters": {
        "color": 7,
        "width": 562,
        "height": 156,
        "content": "ud83dudee0ufe0f Tools Used:n1. Execute SQL Query: Used to execute any query generated by the agent.n2. Get DB Schema and Tables List: It returns the list of all the tables with its schema name.n3. Get Table Definition: It returns table details like column names, foreign keys and more of a particular table in a schema."
      },
      "typeVersion": 1
    },
    {
      "id": "39780c78-4fbc-403e-a220-aa6a4b06df8c",
      "name": "Sticky Note1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        -100,
        300
      ],
      "parameters": {
        "color": 7,
        "width": 162,
        "height": 99,
        "content": "ud83dudc46 You can exchange this with any other chat model of your choice."
      },
      "typeVersion": 1
    },
    {
      "id": "28a5692c-5003-46cb-9a09-b7867734f446",
      "name": "Sticky Note2",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        100,
        300
      ],
      "parameters": {
        "color": 7,
        "width": 162,
        "height": 159,
        "content": "ud83dudc46 You can change how many number of messages to keep using `Context Window Length` option. It's 5 by default."
      },
      "typeVersion": 1
    },
    {
      "id": "c18ced71-6330-4ba0-9c52-1bb5852b3039",
      "name": "Execute SQL Query",
      "type": "n8n-nodes-base.postgresTool",
      "position": [
        380,
        140
      ],
      "parameters": {
        "query": "{{ $fromAI("sql_query", "SQL Query") }}",
        "options": {},
        "operation": "executeQuery",
        "descriptionType": "manual",
        "toolDescription": "Get all the data from Postgres, make sure you append the tables with correct schema. Every table is associated with some schema in the database."
      },
      "credentials": {
        "postgres": {
          "id": "nGI61D0TEEZz18rr",
          "name": "Your Postgresql Database Credentials"
        }
      },
      "typeVersion": 2.5
    },
    {
      "id": "557623c6-e499-48a6-a066-744f64f8b6f3",
      "name": "Get DB Schema and Tables List",
      "type": "n8n-nodes-base.postgresTool",
      "position": [
        580,
        140
      ],
      "parameters": {
        "query": "SELECT n    table_schema,n    table_namenFROM information_schema.tablesnWHERE table_type = 'BASE TABLE'n    AND table_schema NOT IN ('pg_catalog', 'information_schema')nORDER BY table_schema, table_name;",
        "options": {},
        "operation": "executeQuery",
        "descriptionType": "manual",
        "toolDescription": "Get list of all tables with their schema in the database"
      },
      "credentials": {
        "postgres": {
          "id": "nGI61D0TEEZz18rr",
          "name": "Your Postgresql Database Credentials"
        }
      },
      "typeVersion": 2.5
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "10c7c74e-b383-4ac7-8cb2-c9a15a2818fe",
  "connections": {
    "Chat History": {
      "ai_memory": [
        [
          {
            "node": "AI Agent",
            "type": "ai_memory",
            "index": 0
          }
        ]
      ]
    },
    "Execute SQL Query": {
      "ai_tool": [
        [
          {
            "node": "AI Agent",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "AI Agent",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Get Table Definition": {
      "ai_tool": [
        [
          {
            "node": "AI Agent",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "When chat message received": {
      "main": [
        [
          {
            "node": "AI Agent",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Get DB Schema and Tables List": {
      "ai_tool": [
        [
          {
            "node": "AI Agent",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    }
  }
}