Building Your Own "Chat with Data" (MCP) Server for Knack Apps

API & Custom Code
1

Building Your Own “Chat with Data” (MCP) Server for Knack Apps

Empower Your Knack Data with AI: A Step-by-Step Guide

Hey Knack Community!

Have you ever wished you could simply ask your Knack application a question in plain English and get an instant, intelligent answer? Imagine chatting with your database, asking “What was our last deal in July?” and getting a direct response!

This guide will walk you through the crucial first step: extracting your application’s full data schema programmatically. This schema is your “data dictionary” – it tells your AI-powered server exactly what objects (tables) and fields (columns) exist in your app, identified by their unique IDs. Without it, your server won’t know how to talk to your Knack data.

We’ll then provide a prompt you can use with AI to kickstart the development of your own Knack “Chat with Data” (MCP) server.

Phase 1: Extracting Your Knack App’s Schema (The “Data Dictionary”)

The Knack API is powerful, but you need to know the specific object_IDs and field_IDs to make requests. Manually copying these can be tedious, especially for larger apps. Thankfully, Knack provides internal JavaScript functions that let us grab this entire schema automatically!

Important Note: This script must be run on your “Live App”, not in the App Builder. The JavaScript objects we need to access (like Knack.objects) only exist on the front-end application that your users see.

Step-by-Step Guide to Extracting Your Schema (Classic Apps):

  1. Go to Your Live Knack App (Critical Step):

    • Log in to your Knack Builder.

    • In the top-right corner, click “Go to Live App”. This will open your application in a new browser tab.

    • This is where you must run the script.

    • Ensure you are logged in to your live app if it requires a login.

  2. Open Your Browser’s Developer Tools:

    • Windows: Press F12

    • Mac: Press Cmd + Option + I

    • This will open a panel, usually on the side or bottom of your browser window.

  3. Navigate to the “Console” Tab:

    • In the Developer Tools panel, click on the “Console” tab.

  4. Clear the Console (Optional, but Recommended):

    • Right-click anywhere within the console window and select “Clear console”. This gives you a clean slate.

  5. Paste the Schema Extraction Script:

    • Copy the entire JavaScript code block below (including the try...catch):

    JavaScript

    try {
  const fullSchema = { objects: [] };
  
  // 1. Loop through all the objects (tables)
  _.each(Knack.objects.models, function(obj) {
    
    const objectData = {
      name: obj.attributes.name,
      key: obj.attributes.key,
      fields: []
    };
    
    // 2. Loop through all the fields for this object
    _.each(obj.fields.models, function(field) {
      objectData.fields.push({
        name: field.attributes.name,
        key: field.attributes.key,
        type: field.attributes.type
      });
    });
    
    fullSchema.objects.push(objectData);
  });

  // 3. Log the complete schema
  console.log("--- COPY THE ENTIRE SCHEMA BELOW (CLASSIC APP) ---");
  console.log(JSON.stringify(fullSchema, null, 2));
  console.log("--- END OF SCHEMA ---");

} catch (error) {
  console.error("Failed to fetch schema:", error);
}

  6. Run the Script:

    • Paste the script into the console and press Enter.

    • Note: If the browser shows a warning about pasting code, you may need to type allow pasting and press Enter first, then paste the script again.

  7. Copy Your Schema:

    • The console will now print your entire application’s schema as a large JSON object. It will be between the lines:

      -– COPY THE ENTIRE SCHEMA BELOW (CLASSIC APP) —

      -– END OF SCHEMA —

    • Carefully select and copy this entire JSON text.

    • Paste it into a text file (e.g., schema.json) and save it. This file is your “data dictionary” for your AI server!

Phase 2: Kickstarting Your AI-Powered (MCP) Server Development

Now that you have your schema, you’re ready to build the server that will interpret user questions, query Knack, and provide answers. You can use this comprehensive prompt to work with an AI assistant (like ChatGPT, Gemini, etc.) to get your project off the ground.

AI Development Prompt:

"I am building a custom chat server (an MCP) to interact with my Knack application named ‘Your app name,’ which we use for tracking ship fixtures and operations.

My core requirement is that this server must run in a Docker container.

I have completed the pre-development data gathering and have the following information ready:

  1. My Knack Application ID (found in Settings > API & Code)

  2. My Knack API Key (generated in Settings > API & Code)

  3. A complete JSON schema of my app, which lists all my Object IDs (e.g., object_1) and their corresponding Field Keys (e.g., field_123, field_124). I can provide this JSON when needed.

My goal is to build a server using Node.js with Express that can be containerized. This server will eventually take natural language questions, translate them into Knack API requests, fetch data, and formulate natural language answers.

Can you help me with the initial file structure for this Docker project? I’ll need the following:

  1. A Dockerfile for building the Node.js application image.

  2. A basic package.json file listing initial dependencies (e.g., express for the web server, axios for making HTTP requests to the Knack API).

  3. A docker-compose.yml file to define and run the container, demonstrating how to securely pass my Knack Application ID and API Key as environment variables to the container.

  4. A simple index.js file (the Express server) that:

    • Initializes an Express app.

    • Retrieves the Knack API credentials from environment variables.

    • Includes a basic route (e.g., /test-knack-api).

    • Within this route, it should make one authenticated GET API call to my Knack application to retrieve the first 5 records from a specified ‘Fixtures’ object (I will provide the object ID later).

    • It should then return the raw JSON response from Knack.

This initial setup will confirm that my Docker environment is working and that I can successfully connect to and retrieve data from my Knack app."

Relevant Knack Documentation Links:

I hope this post empowers many in the Knack community to start building their own intelligent data interfaces! Good luck with your project! I used Claude Code to develop mine. The AI will create a basic one to begin with but you can keep prompting it to add more features (the tools that you want the MCP Server to handle).

To get your MCP Server to do more analytics, take a look at the below. This guide shows you how to transform a basic Knack MCP server (that just reads/writes records) into an intelligent analytics platform that can answer complex business questions.

The Pattern

1. Start with Three Core Analytics Tools

Every Knack app benefits from these three tools, regardless of your domain:

Tool 1: Aggregation & Grouping

  • Groups records by any field (person, category, status, etc.)

  • Calculates: COUNT, SUM, AVG, MIN, MAX

  • Answers: “How many X?”, “What’s the total Y?”

Tool 2: Time-Series Trends

  • Groups by time periods (month, quarter, year)

  • Tracks changes over time

  • Answers: “Show monthly trends”, “Compare this year vs last”

Tool 3: Multi-Dimensional Pivot

  • Cross-tabulates two dimensions (e.g., person × month)

  • Creates matrix views

  • Answers: “Show X by Y”, “Create a summary table”

2. Add Smart Caching

Analytics queries fetch many records. Caching makes repeat queries 100-500x faster:

  • Cache analytics results for 5-15 minutes

  • Cache raw data fetches for 1-5 minutes

  • Include a tool to view cache statistics

  • Include a tool to clear cache when needed

3. Build Domain-Specific Tools

Beyond generic analytics, add tools that understand YOUR business:

Examples by domain:

  • Sales: Revenue analysis, customer segmentation, conversion funnels

  • Projects: Budget vs actual, resource utilization, milestone tracking

  • Inventory: Stock levels, turnover rates, reorder alerts

  • Finance: Cash flow, receivables aging, payment cycles

  • Operations: Workload distribution, cycle times, bottleneck detection

4. Add Computed Fields

Enhance records with calculated values that don’t exist in Knack:

  • Derived metrics: profit = revenue - cost

  • Status flags: is_overdue, is_urgent, is_complete

  • Time calculations: days_since_created, days_until_due

  • Risk scores: based on multiple factors

  • Formatted values: currency, dates, percentages

5. Use Client-Side Filtering

Knack’s API has limited filtering. For complex queries:

  1. Fetch broader dataset (e.g., last 1000 records)

  2. Filter in memory using JavaScript

  3. Enables: partial text matching, multi-field AND/OR logic, complex date ranges

Implementation Checklist

Give this to your AI assistant:

Step 1: Discovery

"Analyze my Knack schema and identify:

  • What are the main objects/tables?

  • What fields contain numeric values that could be summed/averaged?

  • What fields are good for grouping (categories, statuses, people)?

  • What date fields exist for time-based analysis?

  • What relationships exist between objects?"

Step 2: Define Analytics Needs

"Based on my Knack app, what business questions would users ask? Examples:

  • How many [records] per [category]?

  • What’s the total [amount] by [person/company]?

  • Show trends over time for [metric]

  • What’s overdue/at risk/urgent?"

Step 3: Build Core Tools

"Create three MCP tools for my Knack app:

  1. An ‘analyze’ tool that groups records and calculates aggregations

  2. A ‘trend’ tool that shows metrics over time periods

  3. A ‘pivot’ tool that creates matrix views of data

Use my object keys and field keys from the schema."

Step 4: Add Caching

"Add a caching layer to my MCP server:

  • Cache analytics results for 15 minutes

  • Cache raw data for 5 minutes

  • Track cache hit rates

  • Provide tools to view stats and clear cache"

Step 5: Add Domain Intelligence

"Based on my business [describe your business], add specialized analytics tools that:

  • Calculate domain-specific metrics

  • Identify risks/alerts/opportunities

  • Score or rate records by importance/urgency

  • Provide actionable insights"

Step 6: Enhance Records

"Add computed fields to my records:

  • Calculate derived values from existing fields

  • Add boolean flags for common filters (is_overdue, is_complete)

  • Calculate time-based values (days_since_X, days_until_Y)

  • Format values for display (currency, dates)"

Example: Before & After

Before (basic MCP):

  • list_records: Returns raw records

  • get_record: Gets one record by ID

  • create_record: Creates a record

  • update_record: Updates a record

After (analytics-enhanced MCP):

  • :white_check_mark: All the above, plus:

  • analyze: “How many orders per customer?”

  • trend: “Show monthly revenue”

  • pivot: “Orders by customer × month”

  • financial_analytics: “Cash flow and receivables”

  • risk_analysis: “What’s at risk?”

  • operational_metrics: “Workload and bottlenecks”

  • cache_stats: “Cache performance”

Key Principles

  1. Fetch once, analyze many ways: Get data from Knack, then do complex analysis in your code

  2. Cache aggressively: Analytics users ask similar questions repeatedly

  3. Rich descriptions: Make tools discoverable with detailed descriptions and examples

  4. Structured responses: Return JSON with summary stats, detailed data, and metadata

  5. Error handling: Validate inputs and provide helpful error messages

  6. Progressive enhancement: Start simple, add complexity based on actual usage

Testing Your Analytics

Test with natural language questions:

  • “How many X did Y do last month?”

  • “What’s our total Z by category?”

  • “Show me monthly trends for A”

  • “Which B has the highest C?”

  • “What’s at risk or overdue?”

Your MCP server should handle these without custom code for each question.

Result

Users get an intelligent assistant that answers complex questions about their Knack data, without writing custom reports or complex filters in the Knack interface.

3 Likes
2

one thing I should add is if after you generate the schema, I would go in there and add descriptions to fields that are not too obvious for the AI to figure out what they refer to. This helps the AI greatly.