我的工作流

• •PostgreSQL 数据库连接信息
• •OpenAI API Key

{
  "id": "uYYw1E8JAYRYC7sE",
  "meta": {
    "instanceId": "d4f16bea1a8eb4bd7217a4d0fe9a09f643e7227b28772e65da5a86a149783124"
  },
  "name": "我的工作流",
  "tags": [],
  "nodes": [
    {
      "id": "c46b2f23-abb5-4ae8-8f16-0ecfd39533d7",
      "name": "当收到聊天消息时",
      "type": "@n8n/n8n-nodes-langchain.chatTrigger",
      "position": [
        1344,
        464
      ],
      "webhookId": "42fcd254-5e76-4ca9-8413-6ba8d3d837a6",
      "parameters": {
        "public": true,
        "options": {}
      },
      "typeVersion": 1.1
    },
    {
      "id": "22f89ea8-71d0-40bf-bad1-24a803ddbdcb",
      "name": "便签",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        1232,
        352
      ],
      "parameters": {
        "color": 5,
        "width": 1232,
        "height": 624,
        "content": "## 数据库查询代理"
      },
      "typeVersion": 1
    },
    {
      "id": "ec22405a-53d0-47ee-b667-2132ecce2a1a",
      "name": "设置表名",
      "type": "n8n-nodes-base.set",
      "position": [
        1600,
        464
      ],
      "parameters": {
        "options": {},
        "assignments": {
          "assignments": [
            {
              "id": "25d58d22-7bf2-45eb-900b-5e35ced2d6d3",
              "name": "table_name",
              "type": "string",
              "value": "product_inventory"
            }
          ]
        }
      },
      "typeVersion": 3.4
    },
    {
      "id": "addd05ca-9db5-436c-93e8-9c08ce40bc14",
      "name": "OpenAI 聊天模型",
      "type": "@n8n/n8n-nodes-langchain.lmChatOpenAi",
      "position": [
        1616,
        768
      ],
      "parameters": {
        "model": {
          "__rl": true,
          "mode": "list",
          "value": "gpt-4o-mini",
          "cachedResultName": "gpt-4o-mini"
        },
        "options": {}
      },
      "typeVersion": 1.2
    },
    {
      "id": "ed57c796-1d8c-4b68-aa95-ee5a65f27b38",
      "name": "简单记忆",
      "type": "@n8n/n8n-nodes-langchain.memoryBufferWindow",
      "position": [
        1808,
        768
      ],
      "parameters": {
        "sessionKey": "={{ $('When chat message received').item.json.sessionId }}",
        "sessionIdType": "customKey"
      },
      "typeVersion": 1.3
    },
    {
      "id": "8ac733ea-97de-4bd3-b669-6235c4ec55c6",
      "name": "执行 SQL 查询",
      "type": "n8n-nodes-base.postgresTool",
      "position": [
        2000,
        768
      ],
      "parameters": {
        "query": "{{ $fromAI(\"sql_query\", \"SQL Query\") }}",
        "options": {},
        "operation": "executeQuery",
        "descriptionType": "manual",
        "toolDescription": "Fetch all the data from Postgres, ensuring that table names are always written with their appropriate schema prefix. Each table in the database belongs to a specific schema, so make sure to reference every table using its full schema-qualified name."
      },
      "typeVersion": 2.5
    },
    {
      "id": "e9e87bc9-048b-47fa-b52c-937563c74c1c",
      "name": "获取表定义",
      "type": "n8n-nodes-base.postgresTool",
      "position": [
        2224,
        768
      ],
      "parameters": {
        "query": "select\n  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_column\nfrom\n  information_schema.columns c\nLEFT join\n  information_schema.key_column_usage kcu\n  ON c.table_name = kcu.table_name\n  AND c.column_name = kcu.column_name\nLEFT join\n  information_schema.table_constraints tc\n  ON kcu.constraint_name = tc.constraint_name\n  AND tc.constraint_type = 'FOREIGN KEY'\nLEFT join\n  information_schema.constraint_column_usage ccu\n  ON tc.constraint_name = ccu.constraint_name\nwhere\n  c.table_name = '{{ $json.table_name }}'\n  AND c.table_schema = 'public'\norder by\n  c.ordinal_position",
        "options": {},
        "operation": "executeQuery",
        "descriptionType": "manual",
        "toolDescription": "Get table schema definition to find all columns and its types"
      },
      "executeOnce": true,
      "typeVersion": 2.5
    },
    {
      "id": "1246e3ac-9a70-4bd7-a736-bb8e0d82d757",
      "name": "数据库代理",
      "type": "@n8n/n8n-nodes-langchain.agent",
      "position": [
        1856,
        464
      ],
      "parameters": {
        "text": "={{ $('When chat message received').item.json.chatInput }}",
        "options": {
          "systemMessage": "=# AI Agent Database Query System - Dynamic Column Mapping\n\n# Role\nYou are an expert SQL assistant that can answer natural-language questions by querying a **single table**.  \nThe active table schema will always be provided via the **Get Table Definition** tool.  \nYour job is to understand the user’s request, map it to the actual columns/values in that table, generate a safe SQL query, run it with **Execute SQL Query**, and return only the matching rows.  \nThis must work for any type of table (education, product inventory, HR, finance, etc.).\n\n# Workflow (internal – do not show to the user)\n1. Call **Get Table Definition** first to get the schema (`table` name and `columns`).  \n2. Identify which user terms map to which columns by inspecting column names + data types.  \n   - Examples:  \n     - “bachelor/master/PhD” → `course_level` / `level`  \n     - “price under 500” → numeric/price/cost/fee column  \n     - “in stock/available” → quantity/stock/active/available columns  \n     - “Sydney/Melbourne” → location/campus/city/state columns  \n     - Free-text terms → name/title/description columns  \n3. For each relevant column, run a **discovery query** to fetch distinct values (e.g. `SELECT DISTINCT col FROM {{ $json.table_name }} LIMIT 200`) and use fuzzy/ILIKE matching to align user terms with actual values.  \n4. Build a safe SQL query:  \n   - **FROM**: always the `{{ $json.table_name }}` from schema.  \n   - **SELECT**: `*` unless the user asks for specific columns (then only select existing ones).  \n   - **WHERE**:  \n     - String: `col ILIKE '%term%'`  \n     - Exact match if distinct value matches exactly  \n     - For multi-value/CSV/JSON text fields: `col::text ILIKE '%term%'`  \n     - Numeric filters: parse numbers from text; use `<`, `>`, `BETWEEN` with safe casting  \n     - Date filters: cast text to date if needed; support “before/after/between/latest”  \n     - Boolean filters: map “yes/true/available/on” → `true`; “no/false/off” → `false`  \n   - **ORDER BY**:  \n     - Prefer `updated_at` or `last_updated` desc if present and user asks for “latest/recent”  \n     - Or order by `created_at` if that exists  \n   - **LIMIT**: 50 by default; adjust only if user explicitly requests a number.  \n5. Run the query with **Execute SQL Query**.  \n6. Return only the resulting rows to the user.  \n7. If no results, reply: *“No records found matching your criteria.”*\n\n# Output Rules\n- Do **not** show SQL, tool calls, or internal reasoning.  \n- Always return just the clean data results (table or JSON) to the user.\n\n# Example usage\n- User: “Show bachelor IT courses in Sydney under 35,000”  \n  → Return rows where course_level matches “Undergraduate”, course_name contains “IT”/“Information Technology”, campus_location contains “Sydney”, fee < 35000.\n\n- User: “List in-stock laptops under $800 in Melbourne”  \n  → Return rows where category/name contains “laptop”, stock > 0, price < 800, location contains “Melbourne”.\n"
        },
        "promptType": "define"
      },
      "typeVersion": 2
    },
    {
      "id": "f13f2521-0c8a-41da-aaad-7c3e82fff652",
      "name": "便签1",
      "type": "n8n-nodes-base.stickyNote",
      "position": [
        2496,
        352
      ],
      "parameters": {
        "color": 4,
        "width": 320,
        "height": 624,
        "content": ""
      },
      "typeVersion": 1
    }
  ],
  "active": false,
  "pinData": {},
  "settings": {
    "executionOrder": "v1"
  },
  "versionId": "4043d2be-67cd-491d-854a-cec912f59ec0",
  "connections": {
    "Simple Memory": {
      "ai_memory": [
        [
          {
            "node": "Database Agent",
            "type": "ai_memory",
            "index": 0
          }
        ]
      ]
    },
    "Set Table Name": {
      "main": [
        [
          {
            "node": "Database Agent",
            "type": "main",
            "index": 0
          }
        ]
      ]
    },
    "Execute SQL Query": {
      "ai_tool": [
        [
          {
            "node": "Database Agent",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "OpenAI Chat Model": {
      "ai_languageModel": [
        [
          {
            "node": "Database Agent",
            "type": "ai_languageModel",
            "index": 0
          }
        ]
      ]
    },
    "Get Table Definition": {
      "ai_tool": [
        [
          {
            "node": "Database Agent",
            "type": "ai_tool",
            "index": 0
          }
        ]
      ]
    },
    "When chat message received": {
      "main": [
        [
          {
            "node": "Set Table Name",
            "type": "main",
            "index": 0
          }
        ]
      ]
    }
  }
}