Daily Variance Agent

AI-powered production assistant that catches film budget overruns in real time by reasoning across ClickHouse data with Google's Gemini ADK.

Comment

Inspiration

Film productions hemorrhage money when daily overruns go unnoticed until wrap. A single scene running 5 hours overdue due to weather and lighting delays can blow $8,000+ in a day and producers usually find out too late. I wanted to build an agent that thinks like a line producer, not just another dashboard.

What it does

The Daily Variance Agent is built on ClickHouse Cloud for fast analytical queries. It lets you upload your shooting schedule (the plan) and daily timecards (the actuals). It inserts both into ClickHouse Cloud, then a Google ADK agent powered by Gemini 2.5 Flash queries the data dynamically, compares planned vs. actual, checks historical benchmarks, and generates a structured producer report with specific dollar figures and actionable recommendations all in under 30 seconds.

How we built it

  • Data layer: ClickHouse Cloud for fast analytical queries on structured production data
  • Agent layer: Google ADK with Gemini 2.5 Flash via Vertex AI, using Application Default Credentials
  • Frontend: Streamlit for rapid UI development with a dark-themed dashboard
  • Infrastructure: Docker + Google Cloud Run for serverless deployment
  • Tools: clickhouse-connect for database operations, "pandas" for CSV parsing

Challenges we ran into

Our biggest pivot was authentication. ClickHouse Cloud's managed MCP endpoint uses OAuth, which is incompatible with the Python `library's SSE transport. After hitting 404s and HTML responses, we pivoted to directclickhouse-connect` tools wrapped as ADK functions—actually a more robust architecture for a programmatic agent.

We also fought Google's auth for a full day. AI Studio was generating AQ. prefix keys that return 401 against the Generative Language API. We eventually switched to application default credentials via gcloud auth application-default login and enabled the Vertex AI API much cleaner for production anyway.

Accomplishments that we're proud of

  • The agent trace. You can literally watch the agent decide to call get_variance_for_day, receive 20 rows of raw ClickHouse data, and synthesize a natural language report. That visibility is what makes this genuinely agentic.
  • The Day 12 variance story. We engineered a clear narrative: SCENE_12_03 at Downtown Rooftop, 137.5% over schedule, $8,323.37 over budget, caused by lighting setup + weather. The agent catches it every time.
  • Deployed and live. It's not a local demo—it's on Cloud Run with a public URL.

What we learned

  • ADK 2.7.1's event structure puts tool calls in event.content.parts.function_call, not event.actions.tool_calls. Finding that required deep debugging.
  • ClickHouse Cloud's managed MCP is designed for desktop clients (Claude Desktop, Cursor), not Python agents. If you're building a backend agent, use clickhouse-connect.
  • google-adk with Vertex AI is powerful but requires the Vertex AI API to be explicitly enabled, and the Cloud Run service account needs roles/aiplatform.user.

What's next for Daily Variance Agent

  • PDF ingestion for call sheets and production reports
  • Real-time weather API integration (currently using historical weather_failure_rate)
  • Slack integration for automated daily wrap reports
  • Multi-project support for production companies managing multiple shoots

Built With

Share this project:

Updates

Submission history