StudioOps AI

An agentic AI dashboard that translates natural language into ClickHouse SQL to instantly diagnose streaming viewership anomalies.

Comment

Inspiration

In the streaming era, platforms collect massive amounts of telemetry data—every click, pause, and drop-off is tracked. Yet, when an executive asks, "Why did Episode 4 underperform?", data analysts often spend hours writing SQL queries, slicing demographics, and building charts to find the answer. I wanted to eliminate that bottleneck. StudioOps AI was inspired by the idea of giving streaming platforms an "Agentic Data Analyst"—an AI that can instantly translate natural language into complex database queries, pinpoint anomalies, and visualize the root cause in real-time.

What it does

StudioOps AI is a full-stack, containerized application designed for high-performance data analytics:

The Brains (AI): The Gemini API powers our "Director Agent." Instead of just generating text, the agent is equipped with custom tools that allow it to autonomously write SQL, query the database, and interpret the results.

The Engine (Database): We used ClickHouse as our OLAP database because of its lightning-fast performance with massive telemetry datasets. I built an automated initialization script that seeds the database with mock viewership data on boot.

The Backend (API & Orchestration): Built with Node.js and Express, the backend handles the tool orchestration. Crucially, it streams the AI's reasoning and data payloads back to the client in real-time using Server-Sent Events (SSE).

The Frontend (UI): Built with React, Vite, and Tailwind CSS v4, the dashboard features a premium, dark-mode design inspired by data science environments, featuring a live-streaming terminal execution log and dynamic charting widgets.

Infrastructure: The entire stack is orchestrated using Docker Compose, running isolated containers for the frontend, backend, and ClickHouse database to ensure a seamless, reproducible build environment.

How we built it

Building an agentic loop that executes real code against a live database introduced several tough engineering hurdles:

Docker Networking & Race Conditions: Initially, the backend container would boot faster than ClickHouse. When the Node server tried to seed the database, the connection would fail, or it would throw an Unknown table error. I solved this by building a bulletproof retry-loop mechanism directly into the backend's initialization script to wait for ClickHouse to fully wake up before applying the schema.

SSE Streaming Syntax: Streaming real-time AI responses required setting up a persistent connection. We ran into application crashes due to escaped characters in the SSE stream, which we fixed by enforcing strict, clean template literals in the Node.js API routes.

Data Alignment: Getting the AI to successfully query the database required aligning natural language prompts (e.g., "Episode 4") perfectly with the underlying seed data schema. We had to carefully engineer the mock data generation to ensure the anomalies we planted (a massive drop-off in completion rate for the 18-24 demographic on mobile) could be reliably detected by the AI's SQL queries.

Challenges we ran into

This project was a massive leveling-up experience. I learned how to orchestrate complex multi-container Docker environments and manage internal container DNS. I gained hands-on experience building Server-Sent Events (SSE) to create real-time, streaming UIs. Most importantly, I learned how to bridge the gap between LLM reasoning and raw database telemetry, turning passive analytics into an active, autonomous investigation tool.

Built with react

node.js

clickhouse

gemini-api

docker

docker-compose

express

tailwind-css

vite

javascript

Accomplishments that we're proud of

The Agentic Data Loop: Successfully bridging the gap between a generative LLM (Gemini) and a high-performance OLAP database (ClickHouse). Watching the agent autonomously write SQL, query the database, and pinpoint a demographic anomaly without human intervention was a massive win.

Bulletproof Orchestration: Resolving complex Docker network race conditions. We engineered an intelligent initialization script that forces the Node.js backend to wait for ClickHouse to fully boot before applying schemas and seeding data, ensuring a flawless, crash-free startup.

Real-Time SSE Streaming: Implementing a robust Server-Sent Events (SSE) pipeline. We are proud of the polished, terminal-style React UI that parses the agent's reasoning in real-time, completely avoiding connection drops or frontend rendering errors.

What we learned

Containerized Networking: We heavily leveled up our DevOps skills, specifically in orchestrating multi-container Docker environments, managing internal DNS bridges, and handling volume persistence to keep our database stateful.

Streaming Architectures: We learned the intricacies of keeping persistent HTTP connections open using SSE, and how to safely format and stream complex JSON payloads from an Express server directly to a Vite frontend.

Prompt-to-SQL Alignment: We discovered how critical data alignment is when working with LLMs. We learned to precisely engineer our database seed logic and schema definitions so the AI could reliably map natural language inputs to the correct telemetry data.

What's next for StudioOps AI

Predictive ML Integration: Transitioning from reactive diagnostics to proactive forecasting. We plan to integrate custom Python-based machine learning models to predict episode drop-off rates based on early telemetry before a show finishes its premiere week.

Live Data Ingestion: Replacing the static mock data seeder with a live event streaming platform, allowing the dashboard to process and analyze viewer telemetry in absolute real-time.

Expanded Agent Toolset: Upgrading the Gemini agent's capabilities to not just investigate, but take action—such as automatically generating shareable PDF performance reports and triggering automated alerts to marketing teams when a viewership anomaly is detected.

Built With

Share this project:

Updates

Submission history