CastIQ

4 AI agents powered by Gemini query ClickHouse in real-time to turn streaming analytics into instant content strategy decisions.

Comment

Inspiration

What it does

How we built it

Challenges we ran into

Accomplishments that we're proud of

Inspiration

The media & entertainment industry spends $200B+ annually on content production and acquisition — yet the decisions behind these investments rely on dashboards with 3-6 month delayed data. When a show starts losing viewers mid-season, studios find out from quarterly reports. When a movie goes viral on TikTok, the marketing team misses the 48-hour window to capitalize.

We asked: What if streaming platforms had an autonomous intelligence layer — not another dashboard to stare at, but AI agents that actively monitor, reason, and recommend in real-time?

That's CastIQ.

What it does

CastIQ is a multi-agent content strategy intelligence engine that runs on Google Cloud with Gemini and ClickHouse. It deploys 4 specialized AI agents that autonomously analyze a streaming platform's entire content portfolio:

Agent Role ClickHouse Tools
Trend Sentinel Monitors viewership anomalies & sentiment shifts query_viewership, query_anomalies, query_sentiment
Strategy Advisor Generates content investment recommendations query_content_list, query_revenue, run_sql
Forensics Analyst Deep-dives into root causes of anomalies query_viewership, query_sentiment, run_sql
Executive Brief Synthesizes all findings into C-suite briefings Receives outputs from all agents

The Pipeline

$$\text{Streaming Data} \xrightarrow{\text{ingest}} \text{ClickHouse} \xrightarrow{\text{MCP/SQL}} \text{Gemini Agents} \xrightarrow{\text{orchestrate}} \text{Dashboard}$$

Each agent uses Gemini function calling to query ClickHouse through 7 specialized tools — from viewership metrics and sentiment analysis to custom SQL execution. The orchestrator runs them in sequence: detect → strategize → investigate → brief.

Demo Scenarios

We seeded 3 real-world anomaly scenarios to showcase the pipeline:

  1. "Midnight Protocol S2" — Viewership Cliff

    • Views dropped from $2.1M \to 400K$ after episode 6
    • Completion rate crashed: $0.78 \to 0.35$
    • Sentiment flipped: $82\% \to 38\%$ positive
    • Root cause: Bottle episode format change + runtime increase from 42min → 58min

  2. "Quantum Heist" — Viral Spike

    • Baseline $200K$ views/day → suddenly $1.8M$
    • Social mentions: $500 \to 45{,}000$/day
    • Trigger: TikTok meme trend featuring the villain's catchphrase

  3. "Shadow Empire S3" — Competitor Cannibalization

    • Steady $1.5M$ views → dropped to $900K$
    • Coincided with "Iron Dominion" premiere on rival platform
    • Audience overlap: $72\%$

How we built it

Architecture

┌─────────────────────────────────────────────────┐ │ Next.js 15 Frontend │ │ (Glassmorphism + Framer Motion) │ ├─────────────────────────────────────────────────┤ │ API Routes (8 endpoints) │ │ /orchestrator /query /agents/* /dashboard │ ├─────────────────────────────────────────────────┤ │ Gemini Agent System │ │ ┌───────────┐ ┌──────────┐ ┌─────────────────┐ │ │ │ Trend │ │ Strategy │ │ Forensics │ │ │ │ Sentinel │ │ Advisor │ │ Analyst │ │ │ └─────┬─────┘ └────┬─────┘ └───────┬─────────┘ │ │ │ │ │ │ │ └────────────┼───────────────┘ │ │ ▼ │ │ 7 Function-Calling Tools │ │ (query_viewership, query_anomalies, │ │ query_sentiment, query_revenue, │ │ query_content_list, run_sql, ...) │ ├─────────────────────────────────────────────────┤ │ ClickHouse Cloud │ │ 5 tables + materialized view │ │ 90 days × 20 titles × hourly granularity │ │ Sub-second OLAP on millions of rows │ └─────────────────────────────────────────────────┘

Tech Stack

Layer Technology Why
AI Engine Google Gemini API (@google/generative-ai) Function calling enables agents to autonomously query databases
Analytics DB ClickHouse Cloud Sub-second analytical queries on billions of rows — perfect for real-time streaming metrics
Frontend Next.js 15, Tailwind CSS, Framer Motion App Router + glassmorphism UI with staggered animations
Visualization Recharts Area charts for viewership trends with custom dark theme
Deployment Vercel Zero-config deployment for Next.js

The Agent-Database Loop

The core innovation is the function calling loop between Gemini and ClickHouse:

  1. Agent receives a task (e.g., "find anomalies")
  2. Gemini decides which ClickHouse tools to call
  3. Tool executes SQL query → returns results to Gemini
  4. Gemini reasons over data → may call more tools
  5. Loop repeats (up to 10 iterations) until Gemini has enough context
  6. Agent returns structured analysis

This makes the agents data-grounded — every recommendation cites real metrics, not hallucinations.

Challenges we ran into

  • Gemini SDK migration: The @google/generative-ai SDK was recently deprecated in favor of @google/genai. We discovered this mid-build and had to evaluate both APIs before choosing the stable path.

  • Function calling type system: Gemini requires SchemaType enums (not string literals) for function parameter types. Getting TypeScript to cooperate with the SDK's type system required careful casting.

  • ClickHouse window functions in anomaly detection: Detecting day-over-day viewership changes of >30% required lagInFrame() window functions with proper partitioning — ClickHouse's SQL dialect differs from PostgreSQL in subtle ways.

  • Structured output parsing: Gemini agents sometimes return JSON wrapped in markdown code blocks. We built fallback parsers that extract JSON from freeform text responses using regex matching.

  • Demo data realism: Creating synthetic streaming data that looks realistic required modeling viewership lifecycle curves (exponential decay), hour-of-day multipliers, and specific anomaly injections at exact episode boundaries.

Accomplishments that we're proud of

  • 4 fully autonomous agents that query a real database through function calling — not prompt-only wrappers
  • 7 ClickHouse tools including custom SQL execution with read-only safety guards
  • Full orchestration pipeline: Detect → Strategize → Investigate → Brief runs end-to-end
  • Natural language queries: Users can ask questions in plain English and get SQL + results + AI interpretation
  • Award-quality UI: Glassmorphism, Framer Motion animations, gradient borders, ambient orb effects — built for the big screen

What we learned

  • Agentic AI is about tools, not prompts: The real power comes from giving agents the ability to query structured data autonomously. A well-designed tool set matters more than a clever system prompt.

  • ClickHouse is absurdly fast: Queries that would take seconds on PostgreSQL return in milliseconds. For real-time AI agent workflows, this speed difference is the difference between a usable product and a demo that times out.

  • Function calling is the bridge: Gemini's function calling capability is what makes "agentic" more than a buzzword — it lets the model decide what data it needs and how to get it, rather than us pre-fetching everything.

What's next for CastIQ

  • Real streaming platform integrations (Netflix API, YouTube Analytics, Twitch)
  • Multi-agent collaboration where agents can query each other's findings
  • Predictive models using ClickHouse's ML functions for viewership forecasting
  • Slack/Teams integration for automated executive briefings
  • A/B testing agent that recommends thumbnail, title, and description optimizations

What we learned

What's next for CastIQ

Built With

Share this project:

Updates

Submission history