Scene Autopsy

Ask plain-English questions about audience reactions, then inspect the live ClickHouse evidence, SQL, and public comments behind every finding.

Comment

Inspiration

When people react strongly to a scene, the evidence is often scattered across comments, timestamps, episode discussions, and separate analytics tools.

A creator may see that people are talking about a show, but still not know:

  • Which moment they are talking about
  • Which episode created the strongest reaction
  • What viewers are confused about
  • Whether a conclusion is supported by evidence or is just a guess

Answering these questions usually requires manually reading hundreds of comments, writing SQL, and understanding a complex data system.

We built Scene Autopsy to make that investigation accessible through plain English while keeping the evidence visible.

What it does

Scene Autopsy is an evidence workspace for investigating how people publicly reacted to a show or video.

A user chooses a show, optionally chooses an episode, and asks a question such as:

  • “Which timestamp is discussed most in public comments?”
  • “What are viewers confused about?”
  • “Which episode received the strongest public reaction?”
  • “What scene do people keep bringing up?”

Scene Autopsy turns the question into a guarded, read-only ClickHouse query and returns:

  • A plain-language answer
  • The exact SQL query that ran
  • The raw evidence rows behind the answer
  • The evidence source and counts
  • A direct link to a video moment when a public timestamp is available

The product also supports deep investigations. It forms an initial hypothesis, creates an alternative explanation, runs a challenge query, and shows whether the available evidence supports either conclusion.

The app includes a permanent synthetic showcase called Nightfall Protocol · demo, which lets people try the full workflow immediately.

Users can also connect real public YouTube playlists or individual videos. For real YouTube sources, Scene Autopsy uses public video metadata, public counts, public comments, and timestamp mentions extracted from those comments.

It does not claim access to private YouTube retention, rewatch, completion, drop-off, session, or owner-only Analytics data.

How we built it

The runtime investigation path uses Google Cloud Vertex AI and the official ClickHouse MCP server.

When a user asks a question:

  1. Vertex AI interprets the question and generates a read-only SQL query.
  2. Scene Autopsy applies source, series, episode, and safety guards.
  3. The official mcp-clickhouse server is started through the Model Context Protocol.
  4. The guarded query is executed against ClickHouse through MCP.
  5. The returned evidence rows are shown in the interface.
  6. Vertex AI generates a plain-language explanation grounded in those rows.

Google Cloud Vertex AI is called from:

artifacts/api-server/src/scene-autopsy/agent/geminiClient.js

The application imports GoogleGenAI, creates a Vertex AI client with vertexai: true, and calls generateContent() for query generation, query repair, investigation reasoning, and evidence narration.

The ClickHouse MCP runtime path is implemented in:

artifacts/api-server/src/scene-autopsy/db/mcpClickhouseClient.js

The application starts the official mcp-clickhouse service, discovers its available tools, and calls the read-only query tool through the MCP client.

Public YouTube ingestion uses the YouTube Data API to collect video metadata, public counts, public comments, and timestamp mentions. The direct ClickHouse driver is used for ingestion and write operations, while user investigations use the ClickHouse MCP path.

The frontend is built with React and Vite. The backend is an Express API running in a pnpm workspace.

Challenges we ran into

The biggest challenge was maintaining a strict boundary between public evidence and synthetic demonstration data.

Public YouTube data does not provide private audience retention, rewatch behavior, completion rate, or viewer drop-off. We therefore had to prevent real YouTube investigations from using synthetic session or viewer-event data.

We also had to make sure that generated SQL could not escape the selected series or episode. Every generated query is checked for read-only behavior, source isolation, and episode scope before execution.

Another challenge was handling SQL generated by an AI model. Invalid queries can occur because SQL dialects differ. Scene Autopsy retries failed queries through Vertex AI and applies only a narrow, deterministic ClickHouse repair when it is safe to do so. If the query is still unsafe or invalid, the app fails clearly rather than hiding the problem.

YouTube imports also need to be validated before writing data. The importer rejects invalid video IDs, duplicate ownership, conflicting show assignments, and demo-name collisions.

Accomplishments that we're proud of

  • Built a working natural-language-to-SQL investigation workflow.
  • Used Google Cloud Vertex AI at runtime for reasoning and evidence narration.
  • Used the official ClickHouse MCP server at runtime for live analytical queries.
  • Made SQL and raw evidence visible instead of hiding them behind an AI-generated paragraph.
  • Added a deep-investigation flow that tests alternative hypotheses with a second query.
  • Added public YouTube playlist and video imports.
  • Kept synthetic demo data permanently separate from real public evidence.
  • Implemented fail-closed behavior when no evidence is available.
  • Validated the workflow with the official RocketJump Video Game High School Season 1 playlist.
  • Created a truthful 16:9 hackathon demonstration video showing the live investigation workflow.

What we learned

We learned that an AI-generated answer is only useful when the user can inspect how it was produced.

Showing the SQL, raw rows, source labels, and evidence counts changes the experience from “trust the AI” to “inspect the investigation.”

We also learned that evidence boundaries are a product feature. It is better to say “no public evidence was found” than to present a confident answer based on data the source never provided.

Finally, we learned that MCP is valuable when it is part of the real runtime path, not just listed as a technology. Scene Autopsy uses MCP to execute the analytical ClickHouse queries that power the user-facing investigation.

What's next for Scene Autopsy

The next step is to add more public-source connectors while preserving the same evidence rules.

Possible future sources include other public video platforms, public discussion communities, and public review sources.

The core experience would remain the same:

  • Connect a source
  • Ask a question in plain English
  • Run a guarded investigation
  • Inspect the SQL
  • Review the raw evidence
  • Understand what the data supports
  • Clearly separate missing evidence from actual findings

Built With

Share this project:

Updates

Submission history