Stop Using RAG for Spreadsheets — Use This Instead (n8n)

AI Agent for Spreadsheets and Databases: NLQ with Relational Databases

Key Concepts:

• Vector Stores: Databases for storing vector embeddings of unstructured data for semantic search.
• NLQ (Natural Language Query): Translating natural language questions into SQL queries to retrieve data from relational databases.
• Relational Databases: Structured databases with tables, rows, and columns, suitable for storing structured data like spreadsheets.
• JSONB: A binary JSON format in PostgreSQL for storing flexible data structures within a single column.
• Schema: The structure of a database or data file, including table names, column names, and data types.
• RAG (Retrieval-Augmented Generation): An AI framework that combines information retrieval with text generation.
• Data Ingestion Pipeline: The process of extracting, transforming, and loading data into a database or data store.

1. The Problem with Traditional Vector Stores for Structured Data

• Traditional vector stores often yield poor results when answering questions based on structured data like spreadsheets.
• Example: Asking "What is the sum of all my orders?" might retrieve irrelevant chunks of documents due to semantic similarity, leading to inaccurate answers.

2. Solution: Combining Vector Stores with Relational Databases and NLQ

• Instead of storing all data in a vector database, store structured data (spreadsheets) in a relational database (e.g., PostgreSQL in Supabase).
• Store the schema information of the spreadsheets in a vector database.
• When a question is asked, the agent can choose to:
  • Get semantic results from the vector database (for unstructured data).
  • Get precise quantitative results from the relational database using NLQ.

3. Workflow: Ingesting Spreadsheets and Using NLQ

1. Data Ingestion:
   • Spreadsheets (CSV, Excel, Google Sheets) are ingested into a relational database.
   • The schema information (column names) of the spreadsheets is stored in a single table within the vector database.
2. Question Answering:
   • The agent receives a question.
   • It retrieves available datasets and schema information from the record manager table.
   • If the question requires structured data, it generates a SQL query using NLQ.
   • The SQL query is executed against the relational database for accurate results.
   • For questions requiring unstructured data, the agent queries the vector database.

4. Demo: Breakdown of Orders by Country

• The demo uses a database with a "central record manager" table that tracks both unstructured documents (in the vector store) and tabular data (in the relational database).
• Two spreadsheets are ingested: a CSV file with 1000 rows and a Google Sheet with analytics data.
• Example Question 1: "Give me a breakdown of orders by country."
  • The agent retrieves datasets from the record manager.
  • It queries the tabular rows.
  • It returns the total count of orders for each country.
  • The results are verified using a pivot table in Excel.
• Example Question 2: "Give me a sum of all the orders by country."
  • The agent correctly interprets that the user is looking for the monetary figure.
  • It returns the sum of order amounts for each country.
  • The results are verified using a pivot table in Excel.

5. Agent's Process: Step-by-Step

1. Get Data Sets from Record Manager: The agent calls this function to retrieve available datasets and their schemas.
2. Query Tabular Rows: Based on the chosen file, the agent constructs a SQL query.
   • The query selects the sum of the order amount, groups it by country, and orders the result.
   • All row data is stored in a single table, regardless of the schema, using a JSONB column.
   • Items within the JSONB column are queried using specific syntax.

• Example Question 3: "What ad platform did we get the most conversions from?"
  • The agent responds in natural language: "We got the most conversions from the Google ad platform."
  • The agent constructs a SQL query to retrieve the data.

6. Considerations for RAG Systems

• There is no one-size-fits-all approach to RAG systems.
• Option 1: Store structured data only in the SQL database.
• Option 2: Store structured data both in the SQL database and vectorize it in the vector store (generally not recommended due to potential noise).
• Option 3: For small spreadsheets, store them directly in the vector database with a large enough chunk size.
• NLQ works well with simple data structures like spreadsheets.
• For complex databases with many tables, NLQ can become cumbersome.
• Alternatives for complex schemas:
  • Use database views (stored queries) as staging areas.
  • Denormalize data into a star schema for analytic systems.

7. Drawbacks of Storing Data in JSONB Format

• While the schema is tracked in the record manager table, examples of data, data types, and expected values are not.
• This limitation could potentially be addressed by using AI to assist with data type and value inference and storing that information in a separate field.

8. Data Ingestion Pipeline: Detailed Walkthrough

• The workflow handles both creation and updates of files from Google Drive.
• Unstructured files are embedded and stored in the vector database.
• CSV, Excel, and Google Sheets are processed separately and stored in the relational database.
• Triggers:
  • "New files" trigger: Detects new files in a Google Drive folder.
  • "Updated files" trigger: Detects updated files in the same folder.
  • "Recycling bin" workflow: Handles deleted files by removing vectors and records from the database.
• Process:
  1. Loop over items: Iterates through each file picked up by the triggers.
  2. Download file: Downloads the file from Google Drive.
  3. Switch node: Determines the file type (Google Docs, PDF, HTML, CSV, Excel, Google Sheets).
  4. Extract data:
     • CSV: Uses "extract from CSV" node.
     • Excel: Uses "extract from XLSX" node.
     • Google Sheets: Uses N8N's built-in Google Sheets node.
  5. Aggregate node: Combines all extracted items into a single array.
  6. Get schema: Extracts the top-level items (column names) from the spreadsheet using "array keys."
  7. Summarize node: Concatenates all data for the entire document into one string for potential vectorization and hash generation.
  8. Merge node: Combines the schema and concatenated data.
  9. Set text node: Consolidates data into a consistent format with fields for text, data type, array keys, and data rows.
  10. Generate hash: Creates a SHA-256 hash of the document's text to detect changes.
  11. Search record manager: Checks if the file already exists in the record manager table.
  12. Switch node: Determines whether to create a new record or update an existing one.
  13. Create record (if new): Creates a new row in the record manager table with the file ID, hash, data type, schema, and document title.
  14. Update record (if existing): Updates the existing record in the record manager table.
  15. Switch node (data type): Checks if the data is tabular or unstructured.
  16. Tabular data processing:
      • Delete old data rows: Deletes any existing rows in the "tabular document rows" table associated with the file.
      • Split out node: Splits the aggregated data rows into individual rows.
      • Postgres node: Inserts each row into the "tabular document rows" table using a Postgres node (due to issues with N8N's built-in Supabase node).
  17. Unstructured data processing: (Refer to the RAG masterclass for details) Chunks the data and loads it into the vector database.
  18. Loop over items: Repeats the process for multiple files.

9. Database Setup: Tabular Document Rows Table

• The "tabular document rows" table has the following fields:
  • ID: Primary key.
  • Created at: Timestamp.
  • Record manager ID: Foreign key to the "record manager" table.
  • Row data: JSONB column for storing the serialized row data.

10. AI Agent Setup: System Prompt and Tools

• System Prompt:
  • Defines the agent as an enterprise assistant for accessing information.
  • Instructs the agent to answer questions using information from the vector store or database.
  • Specifies the operating procedure: get available datasets and schema information before querying the database.
  • Instructs the agent to say "Sorry, I don't know" if it cannot answer the question.
• Tools:
  • Vector Store: (Refer to the RAG masterclass for details)
  • Get Data Sets from Record Manager:
    • Description: Fetches all available documents from the record manager, including schema and ID.
    • Filters: Returns only records where the data type is "tabular."
    • Output columns: ID, document title, and schema.
  • Query Tabular Rows:
    • Description: Executes a SQL query on the "tabular document rows" table.
    • Instructions:
      • Always query based on a specific ID.
      • Each row contains a "row data" field of type JSONB.
      • Filter based on the "record manager ID."
      • Extract values from the "row data" JSON using the ->> operator and cast them as needed.
    • Operation: Execute query.
    • Query: Uses an expression from AI to populate the query.

11. Conclusion

By combining vector stores with relational databases and NLQ, it's possible to create AI agents that can accurately answer questions based on both unstructured and structured data. The data ingestion pipeline and AI agent setup described in this summary provide a detailed framework for building such a system. The use of JSONB for storing flexible data structures in the relational database allows for efficient storage and querying of spreadsheet data. The detailed system prompt and tool descriptions guide the AI agent in choosing the appropriate data source and constructing accurate SQL queries.