boxoffice

BoxOffice Brain

A data-backed multiverse projector for film ideas. Pose a what-if (a genre and a year) and it pulls the real cohort of comparable titles from ClickHouse, shows them as a living crowd on the rating axis, and Gemini narrates where the idea would land using only the numbers that came back. Flip through parallel universes across years and genres and the crowd and the story re-form as you go. When a cohort is too thin, it abstains instead of inventing a shape. A second "Ask the data" tab is the direct plain-English to SQL path.

It runs real ClickHouse SQL, and it will not invent a box-office number. Every figure is quoted from a row the database returned; when the rows cannot answer, it abstains instead of guessing.

▶ Live demo · 3-minute walkthrough · Paper (PDF) · Deck (PDF) · Gemini + ADK over the official ClickHouse MCP server

How the projector works

You pick a genre and a year and hit Project. Per scenario the agent runs four real read-only ClickHouse queries: the cohort (its size, mean rating, spread and range), the crowd of individual titles that fills the rating axis, the year trajectory that says whether the genre is trending up or down, and the sibling genres for the same year that let you jump sideways across the multiverse. Gemini reads those rows and writes the plain-language read; every dot, line and number on the page traces back to a row a query returned. If the cohort is too thin to call, it says so.

$ BOX_TRANSPORT=mcp boxoffice ask "which genres underperformed in 1999 vs 1998?" --no-llm

Genres whose mean rating fell from 1998 to 1999 (12 of 18 genres with enough titles):
  War: mean rating 6.08 in 1999 vs 6.86 in 1998 (delta -0.78, 28 vs 24 rated titles)
  Short: mean rating 6.19 in 1999 vs 6.67 in 1998 (delta -0.48, 441 vs 327 rated titles)
  Musical: mean rating 5.80 in 1999 vs 6.26 in 1998 (delta -0.46, 26 vs 20 rated titles)
Worst: War.
Lowest-rated War titles of 1999: Active Stealth (3.00); Storm Catcher (3.60);
Cetverored (3.80). These titles sit inside the genre and year named above; that
is an association within the same cohort, not a demonstrated cause.

-- clickhouse_mcp: elapsed_ms=925.3
-- 2 query trace(s) | abstained=False | error=None | transport=mcp | guardrails=6

Built for the Agentic Cinema Blockbuster Hackathon 2026, ClickHouse track, on the mandated Google Cloud stack (Gemini + ADK).

The ClickHouse MCP server is doing the work

BOX_TRANSPORT=mcp launches uvx --python 3.11 mcp-clickhouse, the MCP server ClickHouse publishes, and every query the agent runs goes through that server's run_query tool. Not a wrapper of ours with an MCP-shaped name.

boxoffice mcp-check
# launching official ClickHouse MCP server: uvx --python 3.11 mcp-clickhouse
# endpoint: https://sql-clickhouse.clickhouse.com:8443/ (user=demo)
# tools advertised: ['list_databases', 'list_tables', 'run_query']
# tool call: run_query   latency: 1030.7 ms   rows: 5

Captured output: docs/live-proof.txt. It needs no credentials, because the default endpoint is the public ClickHouse SQL playground with the read-only demo user, which is the endpoint the official server itself defaults to. The data is IMDb: 388,269 films and 3.4M roles.

If the server cannot start, the MCP transport returns an error. It never downgrades to in-process tools while still reporting success.

Three transports, one SQL statement

BOX_TRANSPORT	what runs the SQL	needs
mcp	official ClickHouse MCP server → ClickHouse	network, uvx
http	ClickHouse HTTP interface (same SQL, no MCP hop)	network
offline (default)	SQLite over a committed extract of the same ClickHouse tables	nothing

Offline mode executes the SQL. It does not match the query against canned answers: SELECT count(*) FROM imdb.movies WHERE 1=0 returns zero rows, and a bad column name returns an error, not an empty result set. The extract is real ClickHouse data (provenance).

Because the statement text is identical on all three, they can be compared:

$ python scripts/verify_parity.py
offline vs http: IDENTICAL
offline vs mcp:  IDENTICAL
http vs mcp:     IDENTICAL

18 rows, every numeric cell equal to within 1e-9. Table, latencies and the caveats that go with them: docs/PARITY.md.

Architecture

flowchart LR
  Q["Studio question\n(plain English)"] --> Sup["Gemini/ADK supervisor\n(agent-core)"]
  Sup --> Plan["Planner\ndescribe_tables -> plan"]
  Plan --> Analyst["Analyst\nwrites read-only SQL"]
  Analyst -->|run_clickhouse_sql| Guard{"READ_ONLY_SQL\nQUERY_SCOPE allowlist\nACTION_LIMITER"}
  Guard -->|rejected / suppressed| Analyst
  Guard -->|pass| MCP["official ClickHouse\nMCP server (run_query)"]
  MCP --> CH[("ClickHouse\n(or the offline snapshot)")]
  CH -->|rows + grounding trace| Analyst
  Analyst --> Writer["Answer Writer"]
  Writer -->|rows support it| Ans["Grounded answer + trace"]
  Writer -->|rows cannot, or query failed| Abstain["Abstain:\n'Insufficient comparable data'"]

Guardrails

Four gates run before a byte leaves the process, and each decision is appended to the run's audit trail in the state store, so the UI shows the real record rather than a decorative tick.

1. READ_ONLY_SQL: one SELECT/WITH statement, no writes or DDL.
2. QUERY_SCOPE: no ClickHouse table functions (url, file, s3, remote, mysql, ...), no SETTINGS, no INTO OUTFILE, and every table on an explicit allowlist. Comments are stripped first so nothing can hide from the scan. A read-only SELECT is not enough on its own: SELECT * FROM url('http://169.254.169.254/...') is read-only and would reach the cloud metadata endpoint. This gate is what stops it.
3. ACTION_LIMITER: per-run and per-hour caps, keyed off a ContextVar so concurrent runs cannot spend each other's budget.
4. Server-side bounds: readonly=1, max_result_rows, max_result_bytes, max_execution_time, plus a bounded client read, so an unbounded generated query is stopped by ClickHouse rather than by our process running out of RAM.

It abstains, and it distinguishes "no data" from "failed"

• Question with no matching query template → abstains before touching the warehouse and lists what it can answer.
• Query runs and returns nothing → abstains and names the data it needs.
• Query fails (timeout, rejected, rate-limited) → reports an execution error. It never reports a failure as "nothing underperformed".

The UI never pretends

boxoffice serve puts the UI on http://127.0.0.1:8765. The banner is green and reads LIVE, and every panel is filled from the JSON the running agent returned. Opened as a plain file there is no backend, so the banner turns amber and reads "recorded transcript, not live", naming the timestamp and transport of the run it is replaying. That transcript is generated by scripts/record_transcript.py from an actual run; the guardrail refusals shown in the UI are real tool return values.

Run it

pip install -e '.[mcp]'                      # BoxOffice Brain + MCP client (agent-core is vendored in-tree)

boxoffice ask "which genres underperformed in 1999 vs 1998?" --no-llm   # offline
BOX_TRANSPORT=mcp boxoffice ask "..." --no-llm                          # live, no key
boxoffice mcp-check                                                     # prove MCP
boxoffice serve                                                         # UI + JSON API

# the full Gemini/ADK graph on Vertex: Gemini writes the SQL and calls the MCP tool
GOOGLE_GENAI_USE_VERTEXAI=True GOOGLE_CLOUD_PROJECT=<your-project> \
  BOX_TRANSPORT=mcp boxoffice ask "which genres underperformed in 1999 versus 1998?"

Tests

BOX_IN_MEMORY_STATE=true pytest tests -q                    # 35 passed, keyless
BOX_LIVE_TESTS=1 pytest tests/test_live_clickhouse.py -q    # 4 passed, needs network

The keyless 35 cover the read-only screen, the data-scope allowlist against five table-function payloads and a cross-database read, comment-hidden payloads, per-context run isolation, the offline engine actually evaluating predicates and reporting bad SQL as an error, question routing, abstain on both empty rows and unsupported questions, a failed query not being read as no data, the correlated signal being scoped to the named cohort, run finalisation and the persisted guardrail trail, fail-closed MCP behaviour, and that the UI's embedded transcript came from a real run. The 4 live tests start ClickHouse's own MCP server and check the live rows against the offline snapshot.

What is not demonstrated

Read HONESTY.md. Short version: Gemini and ADK run at runtime on Vertex AI, and Gemini writes the SQL and calls the MCP tool itself (captured run); the keyless router is the reproducible no-credential path used by the tests and screenshots; the offline snapshot covers 1998 to 1999 only; the live endpoint is the public ClickHouse playground rather than a private Cloud service, so anyone can reproduce it without a key; and there is no holdout evaluation yet.

Paper, deck, demo

• Demo script: DEMO.md
• Paper: paper/paper.tex (build: tectonic paper/paper.tex)
• Deck: deck/deck.md (build: marp deck/deck.md --pdf)
• UI: boxoffice/ui/index.html, screenshots in docs/

Cite

@software{sarkar_boxoffice_2026,
  title  = {BoxOffice Brain: a grounded cinema-analytics agent on ClickHouse},
  author = {Dipankar Sarkar},
  year   = {2026},
  url    = {https://github.com/doom2quake/boxoffice},
  license = {MIT}
}

License

MIT, see LICENSE.