Building an Analytics Chatbot for your SaaS app in 1 day

1:03Hey there everyone. Good morning. Good afternoon, good evening, wherever you're coming in from. Welcome to our webinar today on building an AI chatbot with the MotherDuck MCP. It'll be more of a workshop — we'll go through how to build it yourselves.

1:40My name is Gerald. I'm on the marketing team here at MotherDuck. I'm joined by Ryan Boyd, one of the co-founders of MotherDuck. Jacob from our DevRel team is also here to help answer questions.

2:43Hey everyone, super excited to do this. We have had so much fun internally with the MCP server building AI capabilities into applications. I've tried to build AI chatbots that were reasonable for many years and it hasn't really worked out. Now we've gotten to the point where we can build it in a day.

3:41I'm going to show you the application I built for the workshop — a dashboard for e-commerce stores so that a store owner can see analytics about how their store is performing. You can consider it like the Shopify dashboard for analytics.

6:27I said I want to build a chatbot because I want to ask questions of my data. With the help of Claude I built this chatbot really quickly once our MCP server launched. You can build the basic framework in just several prompts to your LLM.

8:40What is MotherDuck? A serverless data warehouse built on DuckDB designed for low-cost, low-friction analytics by humans, agents, and applications.

8:52A traditional data warehouse is often more like a monolith with shared resources. The distributed architectures make it very hard to build applications because they expect response times in tens to hundreds of milliseconds, not tens of seconds.

9:56MotherDuck offers what we call hypertenancy. Each user has their own isolated compute that can scale up and scale down. You can give the CEO a larger instance than your day-to-day analyst — or in a good company, maybe you do the inverse.

10:45This isolated compute really helps in in-app analytics. It allows us to build a chatbot on top of the data while trusting that users can't affect the performance of other users or the data of other companies.

11:11DuckDB is also extremely fast — it's a columnar database. A lot of people come to us having tried Postgres for analytics and realize Postgres is a row store: amazing but not great for analytics workloads.

12:07We now have a Postgres endpoint in our docs and UI so you can connect to MotherDuck using the Postgres protocol and interact with your MotherDuck data too.

13:04For customer-facing analytics, each customer has their own isolated database and users within those customers can access that data. If you have a Shopify-type site with hundreds of stores, each store gets its own isolated database and compute.

15:51The architecture has a single orchestrator database that stores the list of all e-commerce stores and two types of tokens per store: a read-write token for ingesting data, and a read-scaling token for queries and the analytics chatbot.

17:15The application is a Node.js app deployed to Vercel. It connects to Claude — Sonnet 4.6 in this case — and to the MotherDuck remote MCP server, which connects to the duckling for each individual store.

19:01You can also use the Anthropic SDK with OpenRouter to access other models. For another app I built, I used Gemini because it was fast and I could put enough in the system prompt to make it work well on the given data.

20:48The repo has two branches: main (without AI chat) and AI chat branch. In Claude Code I simply said: create a new branch called AI chat, create an AI chat window in my application, deploy it on a separate domain. That's the set of prompts to build the initial version.

25:36It's easy because Claude Code is awesome nowadays, and because the hypertenancy architecture isolates data per customer. With that isolation, we don't have to build a ton of security features into the chatbot to ensure users only see the right data.

26:06The backend route is 325 lines of code. The React chatbot component is 213 lines. That's about it beyond some minor import and CSS changes.

27:09Now I'm doing this live in Claude Code on the main branch. I'm creating a new branch called live-ai-chat and telling it to use the MCP server — not the Node MotherDuck client — because the MCP server provides additional context about DuckDB syntax, schema exploration, and best practices.

33:17The MCP server converts tool definitions to the Anthropic tool format, mapping all tools advertised by the server into the application. Using a read-scaling token is important because some tools allow writing back to the database — and we don't want to allow that.

37:05The app deployed to Vercel. Two environment variables: the MotherDuck token for the main store listing and the Anthropic SDK token. The app is live.

39:51What's the top category of products sold in January? It's connecting to Claude, figuring out the list of tables available, and writing SQL against those tables.

41:53Seth asks: show me a week over week analysis of the top three customers from January 1 onward. You can balance model speed against accuracy. Nicer models take more time but give better results. You'll want to keep adding to the system prompt over time.

43:27Ryan David asks: how do we increase orders next quarter? Claude can pull in real-world context — for an e-commerce business it noticed a tariff announcement probably caused a shift in orders last week. It's as smart as Claude is nowadays, which is pretty smart.

47:53For charts: the MotherDuck homepage itself uses the MCP server for live analyses with concurrent load, so there's good evidence concurrency works well. Specify charting libraries in your system prompt so the chatbot knows what to use.

50:33For the role of MCP: there are two architectures. One is LLM-generated SQL sent directly to MotherDuck via the DuckDB client. The other uses the MCP server — LLMs know about it natively, and it advertises schema exploration, DuckDB syntax guidance, and query best practices.

53:30For PII: the orchestrator database is never accessed by the LLM — only by SQL I've written. You can create service accounts with access to a subset of data that excludes PII. MotherDuck's team is happy to walk you through the right architecture for your scenario.

55:30Pricing: service accounts are sub-accounts within one org. On the Business plan, one $250/month fee covers unlimited service accounts, each with their own isolated data and ducklings. Ducklings are only active when you're working with that store's data.

57:35For background jobs: GitHub Actions works well for running recurring jobs with MotherDuck. For more complex orchestration, Airflow and other tools in the modern data stack integrate well.

59:57Thank you Ryan and thank you everyone for the great questions. The recording will be sent out and posted on our website. Looking forward to seeing what you build!