The Problem: AI that doesn't know your database

When Anthropic released the Model Context Protocol (MCP) in mid-August 2024, it opened up exciting possibilities for connecting AI assistants directly to business systems. For NetSuite users, this was particularly interesting because NetSuite quickly added built-in MCP tools that let AI assistants interact with your data.

One of the most powerful built-in tools NetSuite provides is the ability to run SuiteQL - a SQL-like query language that lets you ask questions about your data in plain English. Imagine saying to your AI assistant: "Show me all customers who haven't placed an order in the last 90 days" and getting actual data back from your NetSuite account.

But there was a catch

In practice, even simple queries were failing. A lot.

Ask for customer data? The AI might query a field that doesn't exist. Request inventory information? The query would reference the wrong table. Try anything account-specific? Complete failure.

The reason? The AI models had no idea what your NetSuite schema looked like.

Think of it like this: Imagine asking someone to find a book in a library they've never been to, without giving them a map or catalog. They know what a library is, they understand how to search, but they don't know your library's layout, sections, or where anything is shelved.

That's exactly the problem AI models face with NetSuite. They understand SQL syntax, they know general NetSuite concepts, but they don't know:

• What tables exist in your account

• What fields are available on those tables

• What custom fields you've added

• Which fields are actually queryable via SuiteQL

The Inspiration: Standing on the shoulders of giants

While researching solutions, we discovered the excellent work of Tim Dietrich, a NetSuite developer who had been exploring this exact problem space. His post on the NetSuite Records Catalog API was particularly illuminating.

💡 Huge thanks to Tim Dietrich for pioneering this approach and sharing his knowledge with the community!

NetSuite's Records Catalog API is a hidden gem - it's essentially a database schema browser that knows about every table, every field, and every relationship in your specific account. Perfect for what we needed!

But there were challenges:

Challenge #1: Client-side only

The Records Catalog API can only be accessed from client-side scripts (code that runs in your browser), not from server-side scripts or external integrations. This is a security limitation by NetSuite.

Challenge #2: Performance and load

We didn't want to query the Records Catalog every time the AI needed schema information. NetSuite schemas don't change every day - they're fairly stable unless you're actively developing. Repeatedly hitting the API would:

• Slow down responses

• Add unnecessary load to your NetSuite account

• Consume governance units (NetSuite's usage limits)

Challenge #3: AI context limits

Even if we could access the catalog in real-time, sending the entire schema of hundreds of tables on every AI request would be wasteful and slow.

Our Solution: ContextQL custom tool

We realized we needed a hybrid approach:

1. Generate once, use many times - Create schema files periodically, not on-demand

2. Store efficiently - Save schemas in NetSuite's File Cabinet as JSON files

3. Query smartly - Let the AI search for only the tables it needs

4. Make it a Custom Tool - NetSuite allows you to create custom MCP tools alongside the built-in ones

Here's how it works:

Step 1: Schema generation (one-time setup)

We built a Suitelet (a NetSuite web page) that administrators can access to generate schema files. When you click "Generate":

A client-side script queries the Records Catalog API for all tables in your account

1. For each table, it retrieves complete field definitions, types, and metadata

2. It filters out fields that aren't available in SuiteQL (no point including them)

3. It saves each table as an individual JSON file: customer.json, transaction.json, etc.

4. It creates a manifest.json file that acts as an index of all available tables

All of this gets stored in /SuiteScripts/ContextQL/TablesSchemas/ in your File Cabinet.

Key Innovation: Because this runs client-side in your browser session, it has full access to the Records Catalog API. But because it only runs when you trigger it (not on every AI request), it doesn't create performance issues.

Step 2: Custom tool functions

We created a Custom Tool that exposes two simple functions to AI clients:

listTables - Discover what's available

// AI asks: "What tables are related to customers?"
Input: { "search": "customer" }

// Tool returns:
{
  "tables": [
    { "id": "customer", "label": "Customer" },
    { "id": "customerCategory", "label": "Customer Category" },
    { "id": "customerStatus", "label": "Customer Status" }
  ],
  "totalFound": 3
}

getTableSchema - Get detailed field information

// AI asks: "What fields are on the customer table?"
Input: { "id": "customer" }

// Tool returns:
{
  "id": "customer",
  "details": {
    "fields": [
      { "id": "id", "label": "Internal ID", "type": "INTEGER" },
      { "id": "entityid", "label": "Name", "type": "STRING" },
      { "id": "email", "label": "Email", "type": "STRING" },
      // ... all customer fields
    ]
  }
}

Step 3: AI integration via MCP

When connected to an MCP-compatible AI client (like Claude), the AI now has access to your schema information through natural language:

Before ContextQL:

User: "Show me all customers with no sales this year"
AI: *attempts query with wrong field names*

After ContextQL:

User: "Show me all customers with no sales this year"
AI: *first checks what fields are available on customer table*
AI: *sees there's no annual_sales field, but there's a relationship to transactions*
AI: *constructs correct query using actual field names*

How it works: The complete flow

Here's the journey from schema generation to AI query:

1. Administrator generates schemas (one-time or when schema changes)

   • Suitelet interface → Client script → Records Catalog API → JSON files in File Cabinet

2. AI receives a question about NetSuite data

   • User: "What fields track customer payment terms?"

3. AI uses ContextQL to understand the schema

   • Calls listTables with search term "customer"

   • Calls getTableSchema for "customer" table

   • Sees fields like terms, creditlimit, daysoverdue

4. AI constructs accurate query with real field names

   • Generates SuiteQL using actual schema

   • Uses NetSuite's built-in SuiteQL tool to execute

Demo: See it in action

Let's watch ContextQL solve a real business question:

Scenario: A business owner wants to find all customers in California who have open invoices over $10,000.

Without ContextQL:

1. User asks AI the question

2. AI guesses at table and field names

3. Query fails - state field doesn't exist, it's billingState

4. User manually looks up schema

5. Query fails again - invoiceAmount doesn't exist, invoices are in a related table

6. After 5-10 minutes: Success (or user gives up)

With ContextQL:

1. User asks AI the question

2. AI uses listTables to find customer and invoice tables

3. AI uses getTableSchema to see exact field names

4. AI sees billingState field and understands invoice relationships

5. AI constructs perfect query on first attempt

6. After 30 seconds: Success ✅

Credits & thanks

• Tim Dietrich - For pioneering NetSuite Records Catalog API exploration and sharing knowledge with the community

• The NetSuite Developer Community - For continuous inspiration and collaboration

Try it yourself

Ready to give your AI assistant real knowledge about your NetSuite schema?

Want to see how AI can truly understand your NetSuite data?

Explore ContextQL on GitHub, try it in your own environment, and start building AI workflows that actually know your schema.

👉 Get started with ContextQL