{"id":"bc0accc7-6163-4733-9be4-7dc695d927fc","slug":"boxoffice","kind":"mcp","name":"boxoffice","summary":"A cinema-analytics agent on Gemini + ADK that runs read-only ClickHouse SQL through the official ClickHouse MCP server, grounds every number in real rows, and abstains when it cannot answer. Includes","owner":"doom2quake","repoUrl":"https://github.com/doom2quake/boxoffice","license":"MIT","licenseTier":"permissive","trust":"community","stars":0,"match":null,"fewStars":true,"format":"mcp","createdAt":"2026-08-28T10:05:33.000Z","addedAt":"2026-10-21T06:42:59.816Z","logoUrl":"https://avatars.githubusercontent.com/u/282944616?u=58500695b7fec0ee68f54604744c0f7a1e189a92&v=4","linkOnly":false,"collection":null,"skillPath":null,"categories":["databases","data-analytics","cloud-infra"],"tags":["ai-agents","analytics","clickhouse","python","agentic-cinema","gemini","google-adk","vertex-ai"],"runtime":"python","local":true,"cloudCapable":false,"cloudCapability":"unknown","cloudReadyOnPlatform":false,"needsApiKey":false,"reviewed":false,"syncedAt":"2026-10-27T22:17:13.668Z","sample":false,"url":"https://conduits.dev/tools/boxoffice","language":"Python","transports":["stdio"],"install":{"pypi":"boxoffice-brain"},"requirements":{"env":[],"upstreamMayCharge":false},"tools":null,"readme":"# BoxOffice Brain\n\nA data-backed multiverse projector for film ideas. Pose a what-if (a genre and a\nyear) and it pulls the real cohort of comparable titles from ClickHouse, shows\nthem as a living crowd on the rating axis, and Gemini narrates where the idea\nwould land using only the numbers that came back. Flip through parallel universes\nacross years and genres and the crowd and the story re-form as you go. When a\ncohort is too thin, it abstains instead of inventing a shape. A second \"Ask the\ndata\" tab is the direct plain-English to SQL path.\n\n**It runs real ClickHouse SQL, and it will not invent a box-office number.** Every\nfigure is quoted from a row the database returned; when the rows cannot answer, it\nabstains instead of guessing.\n\n**[▶ Live demo](https://boxoffice-brain-744757588430.us-central1.run.app)**  ·  **[3-minute walkthrough](VIDEO_URL)**  ·  **[Paper (PDF)](paper/paper.pdf)**  ·  **[Deck (PDF)](deck/deck.pdf)**  ·  Gemini + ADK over the **[official ClickHouse MCP server](https://github.com/ClickHouse/mcp-clickhouse)**\n\n![The Multiverse Projector](docs/ui-projector.png)\n\n## How the projector works\n\nYou pick a genre and a year and hit Project. Per scenario the agent runs four real\nread-only ClickHouse queries: the cohort (its size, mean rating, spread and range),\nthe crowd of individual titles that fills the rating axis, the year trajectory that\nsays whether the genre is trending up or down, and the sibling genres for the same\nyear that let you jump sideways across the multiverse. Gemini reads those rows and\nwrites the plain-language read; every dot, line and number on the page traces back\nto a row a query returned. If the cohort is too thin to call, it says so.\n\n```\n$ BOX_TRANSPORT=mcp boxoffice ask \"which genres underperformed in 1999 vs 1998?\" --no-llm\n\nGenres whose mean rating fell from 1998 to 1999 (12 of 18 genres with enough titles):\n  War: mean rating 6.08 in 1999 vs 6.86 in 1998 (delta -0.78, 28 vs 24 rated titles)\n  Short: mean rating 6.19 in 1999 vs 6.67 in 1998 (delta -0.48, 441 vs 327 rated titles)\n  Musical: mean rating 5.80 in 1999 vs 6.26 in 1998 (delta -0.46, 26 vs 20 rated titles)\nWorst: War.\nLowest-rated War titles of 1999: Active Stealth (3.00); Storm Catcher (3.60);\nCetverored (3.80). These titles sit inside the genre and year named above; that\nis an association within the same cohort, not a demonstrated cause.\n\n-- clickhouse_mcp: elapsed_ms=925.3\n-- 2 query trace(s) | abstained=False | error=None | transport=mcp | guardrails=6\n```\n\nBuilt for the **Agentic Cinema Blockbuster Hackathon 2026**, ClickHouse track, on\nthe mandated Google Cloud stack (Gemini + ADK).\n\n## The ClickHouse MCP server is doing the work\n\n`BOX_TRANSPORT=mcp` launches `uvx --python 3.11 mcp-clickhouse`, the MCP server\nClickHouse publishes, and every query the agent runs goes through that server's\n`run_query` tool. Not a wrapper of ours with an MCP-shaped name.\n\n```bash\nboxoffice mcp-check\n# launching official ClickHouse MCP server: uvx --python 3.11 mcp-clickhouse\n# endpoint: https://sql-clickhouse.clickhouse.com:8443/ (user=demo)\n# tools advertised: ['list_databases', 'list_tables', 'run_query']\n# tool call: run_query   latency: 1030.7 ms   rows: 5\n```\n\nCaptured output: [`docs/live-proof.txt`](docs/live-proof.txt). It needs no\ncredentials, because the default endpoint is the public ClickHouse SQL\nplayground with the read-only `demo` user, which is the endpoint the official\nserver itself defaults to. The data is IMDb: 388,269 films and 3.4M roles.\n\nIf the server cannot start, the MCP transport returns an error. It never\ndowngrades to in-process tools while still reporting success.\n\n## Three transports, one SQL statement\n\n| `BOX_TRANSPORT` | what runs the SQL | needs |\n|---|---|---|\n| `mcp` | official ClickHouse MCP server &rarr; ClickHouse | network, `uvx` |\n| `http` | ClickHouse HTTP interface (same SQL, no MCP hop) | network |\n| `offline` (default) | SQLite over a committed extract of the same ClickHouse tables | nothing |\n\nOffline mode **executes** the SQL. It does not match the query against canned\nanswers: `SELECT count(*) FROM imdb.movies WHERE 1=0` returns zero rows, and a\nbad column name returns an error, not an empty result set. The extract is real\nClickHouse data ([provenance](boxoffice/fixtures/SNAPSHOT.md)).\n\nBecause the statement text is identical on all three, they can be compared:\n\n```\n$ python scripts/verify_parity.py\noffline vs http: IDENTICAL\noffline vs mcp:  IDENTICAL\nhttp vs mcp:     IDENTICAL\n```\n\n18 rows, every numeric cell equal to within 1e-9. Table, latencies and the\ncaveats that go with them: [`docs/PARITY.md`](docs/PARITY.md).\n\n## Architecture\n\n```mermaid\nflowchart LR\n  Q[\"Studio question\\n(plain English)\"] --> Sup[\"Gemini/ADK supervisor\\n(agent-core)\"]\n  Sup --> Plan[\"Planner\\ndescribe_tables -> plan\"]\n  Plan --> Analyst[\"Analyst\\nwrites read-only SQL\"]\n  Analyst -->|run_clickhouse_sql| Guard{\"READ_ONLY_SQL\\nQUERY_SCOPE allowlist\\nACTION_LIMITER\"}\n  Guard -->|rejected / suppressed| Analyst\n  Guard -->|pass| MCP[\"official ClickHouse\\nMCP server (run_query)\"]\n  MCP --> CH[(\"ClickHouse\\n(or the offline snapshot)\")]\n  CH -->|rows + grounding trace| Analyst\n  Analyst --> Writer[\"Answer Writer\"]\n  Writer -->|rows support it| Ans[\"Grounded answer + trace\"]\n  Writer -->|rows cannot, or query failed| Abstain[\"Abstain:\\n'Insufficient comparable data'\"]\n```\n\n## Guardrails\n\nFour gates run before a byte leaves the process, and each decision is appended\nto the run's audit trail in the state store, so the UI shows the real record\nrather than a decorative tick.\n\n1. **READ_ONLY_SQL**: one SELECT/WITH statement, no writes or DDL.\n2. **QUERY_SCOPE**: no ClickHouse table functions (`url`, `file`, `s3`,\n   `remote`, `mysql`, ...), no `SETTINGS`, no `INTO OUTFILE`, and every table on\n   an explicit allowlist. Comments are stripped first so nothing can hide from\n   the scan. A read-only SELECT is *not* enough on its own: `SELECT * FROM\n   url('http://169.254.169.254/...')` is read-only and would reach the cloud\n   metadata endpoint. This gate is what stops it.\n3. **ACTION_LIMITER**: per-run and per-hour caps, keyed off a `ContextVar` so\n   concurrent runs cannot spend each other's budget.\n4. **Server-side bounds**: `readonly=1`, `max_result_rows`, `max_result_bytes`,\n   `max_execution_time`, plus a bounded client read, so an unbounded generated\n   query is stopped by ClickHouse rather than by our process running out of RAM.\n\n## It abstains, and it distinguishes \"no data\" from \"failed\"\n\n- Question with no matching query template &rarr; abstains before touching the\n  warehouse and lists what it can answer.\n- Query runs and returns nothing &rarr; abstains and names the data it needs.\n- Query **fails** (timeout, rejected, rate-limited) &rarr; reports an execution\n  error. It never reports a failure as \"nothing underperformed\".\n\n## The UI never pretends\n\n`boxoffice serve` puts the UI on `http://127.0.0.1:8765`. The banner is green\nand reads LIVE, and every panel is filled from the JSON the running agent\nreturned. Opened as a plain file there is no backend, so the banner turns amber\nand reads \"recorded transcript, not live\", naming the timestamp and transport of\nthe run it is replaying. That transcript is generated by\n`scripts/record_transcript.py` from an actual run; the guardrail refusals shown\nin the UI are real tool return values.\n\n## Run it\n\n```bash\npip install -e '.[mcp]'                      # BoxOffice Brain + MCP client (agent-core is vendored in-tree)\n\nboxoffice ask \"which genres underperformed in 1999 vs 1998?\" --no-llm   # offline\nBOX_TRANSPORT=mcp boxoffice ask \"...\" --no-llm                          # live, no key\nboxoffice mcp-check                                                     # prove MCP\nboxoffice serve                                                         # UI + JSON API\n\n# the full Gemini/ADK graph on Vertex: Gemini writes the SQL and calls the MCP tool\nGOOGLE_GENAI_USE_VERTEXAI=True GOOGLE_CLOUD_PROJECT=<your-project> \\\n  BOX_TRANSPORT=mcp boxoffice ask \"which genres underperformed in 1999 versus 1998?\"\n```\n\n## Tests\n\n```bash\nBOX_IN_MEMORY_STATE=true pytest tests -q                    # 35 passed, keyless\nBOX_LIVE_TESTS=1 pytest tests/test_live_clickhouse.py -q    # 4 passed, needs network\n```\n\nThe keyless 35 cover the read-only screen, the data-scope allowlist against five\ntable-function payloads and a cross-database read, comment-hidden payloads,\nper-context run isolation, the offline engine actually evaluating predicates and\nreporting bad SQL as an error, question routing, abstain on both empty rows and\nunsupported questions, a failed query not being read as no data, the correlated\nsignal being scoped to the named cohort, run finalisation and the persisted\nguardrail trail, fail-closed MCP behaviour, and that the UI's embedded\ntranscript came from a real run. The 4 live tests start ClickHouse's own MCP\nserver and check the live rows against the offline snapshot.\n\n## What is not demonstrated\n\nRead [`HONESTY.md`](HONESTY.md). Short version: Gemini and ADK run at runtime on\nVertex AI, and Gemini writes the SQL and calls the MCP tool itself ([captured\nrun](docs/gemini-run.txt)); the keyless router is the reproducible no-credential\npath used by the tests and screenshots; the offline snapshot covers 1998 to 1999\nonly; the live endpoint is the public ClickHouse playground rather than a private\nCloud service, so anyone can reproduce it without a key; and there is no holdout\nevaluation yet.\n\n## Paper, deck, demo\n\n- **Demo script:** [`DEMO.md`](DEMO.md)\n- **Paper:** `paper/paper.tex` (build: `tectonic paper/paper.tex`)\n- **Deck:** `deck/deck.md` (build: `marp deck/deck.md --pdf`)\n- **UI:** `boxoffice/ui/index.html`, screenshots in `docs/`\n\n## Cite\n\n```bibtex\n@software{sarkar_boxoffice_2026,\n  title  = {BoxOffice Brain: a grounded cinema-analytics agent on ClickHouse},\n  author = {Dipankar Sarkar},\n  year   = {2026},\n  url    = {https://github.com/doom2quake/boxoffice},\n  license = {MIT}\n}\n```\n\n## License\n\nMIT, see [LICENSE](LICENSE).\n"}