Developers need to query live production analytics using local AI assistants without compromising database security or spending engineering cycles building complex, custom user interfaces.
- Model Context Protocol: Expose a single registry of read-only analytical tools to local AI clients via a secure HTTP proxy and scoped JWTs.
- Dual-Database Engine: Stream high-volume write events from PostgreSQL into DuckDB to leverage its columnar architecture for fast reads.
- Structured Content: Return data payloads containing raw JSON, charting instructions, and deep links mapped directly to the MCP specification.
Maintaining the continuous streaming pipeline between the row-oriented and columnar databases requires operational maturity, as silent synchronization failures will cause the AI to confidently serve stale data.
Script
Picture this. You're staring at a raw database table on a Tuesday afternoon. Traffic to your main pricing page dropped significantly earlier in the day, and you're trying to figure out why. You know the answer is somewhere in your analytics data. You just need to join the pageviews against the referral sources for the last six hours, group them by region, and map out the drop-off.
Normally, this means writing a sprawling SQL query, exporting a CSV, and maybe dragging that file into an AI window to ask questions about it. What you really want is to open up Cursor, or Claude Desktop, and ask your assistant to write the query, run it against the live data, and give you the answer. But you absolutely do not want to hand your production database credentials over to a desktop client.
The Solution: An AI-Native Analytics Engine
This exact friction is the target of a new open-source project called InsightsTrack. It's a self-hosted analytics engine.
We're skipping the standard privacy-first, no-cookie pitch today. There are plenty of battle-tested open-source options out there if you just want to replace Google Analytics. Instead, we're focusing entirely on the architecture.
Bypassing the UI with the Model Context Protocol
The developer behind InsightsTrack treated this as a practical case study in how to use the Model Context Protocol to bypass building complex user interfaces.
If you're building an AI assistant to sit inside a web application, the default architectural move is to build a standard API. You expose endpoints for your frontend, and your internal AI calls them. InsightsTrack throws that model out. The developer built a unified tool registry. It's a single file containing seventeen read-only analytical tools. These are deterministic functions that fetch top pages, calculate KPIs, and map funnels.
This single registry powers two completely different front doors. First, it powers the dashboard's internal chat interface. But second, it's exposed directly as an MCP server. This means you don't have two separate logic paths to maintain. By exposing the exact same registry over MCP, external clients like Claude Desktop become instant, custom analytics clients.
Every time one of these tools is called, it doesn't just return a flat string of numbers. It returns a structured envelope. The payload includes a plain-text summary, the raw JSON data, rendering instructions for charting, a download link, and a deep link back to the web dashboard. This envelope maps directly to MCP’s structured content specification.
When you ask Claude for your top traffic sources, it gets the data, understands exactly how to structure the table, and provides you with a direct link to the full report. You get the richness of a dedicated analytics dashboard right inside your IDE.
A Dual-Database Design for Interactive Speed
To make this fast enough for an LLM to query interactively, the underlying data layer uses a dual-database design.
When you self-host web analytics, you hit a scaling wall quickly. Relational databases like PostgreSQL are fantastic for accepting a high volume of writes. Every time a user clicks a button or loads a page, Postgres handles the insertion perfectly. But Postgres is row-oriented. When you ask it to scan millions of rows to calculate a ninety-day traffic funnel, it chokes. It's not built for massive aggregations.
To solve this, InsightsTrack streams every tracking event from PostgreSQL into DuckDB. DuckDB is an embedded columnar engine. It lives in-process and is designed specifically for analytical reads.
Because the engine only scans the specific columns needed for a query, those ninety-day rollups execute in under one hundred milliseconds. Writes go to Postgres. Reads come from DuckDB. That split is how you keep a self-hosted application incredibly fast without paying for a massive cloud data warehouse.
The Catch: Operational Maturity
Keeping a system like this running requires operational maturity. The marketing materials claim you can deploy this in under fifteen minutes. Initial provisioning might be fast, but managing the operational overhead is the real cost.
You're running a continuous streaming pipeline between Postgres and DuckDB. If that synchronization silently fails or starts lagging, your AI assistant will confidently feed you stale data. You also have to monitor the memory consumption on your single server as the embedded DuckDB instance scales into millions of historical events. It's a clever architecture, but it requires a willingness to manage your infrastructure.
Securely Connecting to Desktop Clients
Assuming your data layer is healthy, the next challenge is access. You have a fast, read-only analytics engine. How do you safely connect it to a desktop AI client?
MCP supports different transport layers. The server supports remote connections via Streamable-HTTP, speaking JSON-RPC 2.0. If your client supports remote HTTP connections, you just pass it a URL and a bearer token.
But many desktop clients expect MCP servers to run locally. They communicate over standard input and output. They expect to spawn a local process on your machine. You obviously cannot spawn a local process that connects directly to a remote production database. That breaks the security model, and it breaks DuckDB's strict single-writer lock.
The solution here is a secure proxy. When you configure the local connection bridge, it runs a tiny MCP server process directly on your laptop. But that process contains no database logic. It just listens to the local channel, takes the tool call, and proxies it over HTTP to your remote API. The local client thinks it's talking to a local tool. The remote server just sees an authenticated HTTP request.
The connection is secured using scoped JSON Web Tokens. These tokens are generated with unique IDs, so if a developer’s laptop is compromised, you can revoke their specific token without taking down the whole system. The database remains completely isolated behind the API, and all tools remain strictly read-only. The AI can query your traffic, but it can't drop your tables.
The Intelligence Layer: Bring-Your-Own-Key
To power the actual intelligence, the platform uses a bring-your-own-key layer. You can plug in Anthropic, OpenAI, or Gemini. Those API keys are encrypted at rest using AES-256-GCM, derived from the server secret. They never sit in plain text in your database.
The Devil in the Streaming Details
Building multi-provider support sounds straightforward until you actually implement the streaming protocols. The developer highlighted a specific, gritty struggle with Gemini’s API that proves how brittle these abstractions can be.
When you stream a tool call using OpenAI, the API sends the tool arguments as tiny text fragments. The client’s job is to receive each fragment and concatenate them until the stream closes, resulting in a complete JSON object.
Gemini’s OpenAI-compatible endpoint doesn't behave this way. Instead of sending fragments, Gemini sends the complete arguments object in every single delta. Sometimes it even repeats the same object. If your code naively concatenates the stream, expecting fragments, you end up mashing complete objects together.
You get invalid JSON strings, like two empty bracket objects fused into one. The parsing fails, and on the next round trip, Gemini rejects the call with a mysterious invalid argument error.
The fix was highly specific. The application logic has to check every incoming fragment. If the fragment is already a valid JSON object, the system treats it as the whole value and stops appending. If it's not, it concatenates. It's a minor detail, but it's the exact type of provider quirk that turns a fast integration into a multi-day debugging session.
A Blueprint for Modern Data Exposure
This entire project is a blueprint for how modern applications should handle data exposure. Think about the internal tools your team maintains. The administrative panels, the custom reporting views, the one-off dashboards. We spend an immense amount of engineering time building out complex user interfaces, wiring up filter states, and maintaining CSV export buttons.
InsightsTrack demonstrates a different path. By focusing your effort on a robust, read-only tool registry, you can turn any local AI environment into a custom client. You don't have to build the UI for a highly specific data question. You just expose the capability. You give the LLM the tools to query the data safely, and you let the developer ask for exactly what they want, right inside their editor.
It fundamentally changes what you need to build. Stop treating AI as just a chat window you bolt onto an existing dashboard. Treat it as the rendering engine for your API. Expose the data safely, map it to structured content, and let your existing workflow tools handle the rest.
TAKEYOURPILLS.TECH. Go ship something.