Shot Memory

Search 100 hours of footage in plain language and let an agent team assemble the first cut, with ClickHouse as their memory.

Comment

Inspiration

A documentary assistant editor logs 100 to 500 hours of footage by hand: who is in the shot, where, what time of day, what shot size, what mood. It takes weeks. When the editor asks for "a rainy night close-up of two people", the assistant spends half a day going back through the log sheet, and a trailer repeats that search dozens of times. We wanted the log sheet to write itself and the search to take a second.

What it does

Shot Memory ingests a film, detects every shot, has Gemini write a structured log entry for each one, embeds a representative frame, and stores it all in ClickHouse. An editor then types a description ("lonely man walking through an empty street, wide shot") or an editing brief ("build a 60-second teaser: tension rising to a climax, then release"). A Google ADK agent team searches the archive with hybrid SQL, assembles an ordered sequence, checks continuity between adjacent shots, and exports a CMX3600 EDL that opens in Resolve, Premiere, or Final Cut.

How we built it

  • Ingest: PySceneDetect finds shot boundaries, ffmpeg cuts a 320p proxy and three thumbnails per shot, Gemini 3.8 Flash watches the proxy and returns a structured log (response_schema enforced), Gemini Embedding 2 embeds the middle frame in image mode (3072 dimensions). Rows go into ClickHouse in batches with clickhouse-connect.
  • Storage: one ClickHouse table, shots, with typed metadata columns and an Array(Float32) embedding column, so one SQL statement combines hard filters (time_of_day = 'night' AND people_count = 2) with cosineDistance ordering. Query vectors are staged in a query_vectors table so the agent's SQL stays short. Assembled sequences and search history are written back.
  • Agents (Google ADK): EditorAssistant routes the request. Librarian embeds the query and writes one hybrid SQL query. CutAssembler splits a brief into beats and calls the Librarian per beat in parallel. ContinuityChecker applies metadata rules, then asks Gemini to compare frames visually. Narrator explains the result. Every agent runs on Gemini 3.8 Flash through google-genai.
  • ClickHouse at runtime: the agents reach ClickHouse only through the official mcp-clickhouse MCP server, loaded as an ADK McpToolset. The only direct driver access is the ingest job.
  • UI: React + Vite. The agent panel streams every tool call from every agent over SSE, the SQL panel shows the query the Librarian ran and the ClickHouse latency, and the timeline supports reorder, remove, and EDL export.

Challenges we ran into

  • A scene-detection threshold that works for modern footage found almost no cuts in flat-lit 1940s dialogue scenes; we tuned it per film and made the ingest resumable.
  • The Librarian initially explored the schema with dozens of queries per request. Giving it a strict query budget and moving row hydration out of the LLM (the agent returns shot ids, the backend fetches rows from ClickHouse) cut a rough-cut request from over eight minutes to about a minute.
  • The ContinuityChecker skipped its own rules when left to the model, so the metadata rules became a deterministic Python tool and the model only handles the visual comparison.
  • Gemini rate limits during ingest slowed the agents; retries with exponential backoff and async embedding calls fixed it.

Accomplishments that we're proud of

  • A real archive, not a mock. Four public-domain films, 2,166 shots, 6 hours of footage, every shot watched by Gemini and stored in ClickHouse with a 3072-dimensional embedding.
  • Hybrid search in one SQL statement. Hard filters and vector distance in the same query, answered by ClickHouse in 50 to 100 ms, run by the agent through the official ClickHouse MCP server and shown to the editor verbatim.
  • A working rough-cut loop end to end. Brief in, beats out, four parallel Librarian searches, deterministic continuity rules plus Gemini frame comparison, and a CMX3600 EDL that opens in Resolve.
  • Cutting a rough-cut request from over eight minutes to about a minute by giving the Librarian a strict query budget and moving row hydration out of the model.
  • Full agent transparency. Every tool call from every sub-agent streams to the UI over SSE with timestamps, so the editor sees what the team did, not a spinner.
  • Deployed and live on a self-hosted stack with Docker Compose, HTTPS, and a resumable ingest pipeline that survived rate limits and a spending cap mid-run.

What we learned

ClickHouse is a good fit for agent memory: one table, one query language, hybrid filter plus vector ordering in tens of milliseconds, and the MCP server means the agent writes SQL the editor can read. The biggest wins came from deciding what the model should not do.

What's next for Shot Memory

Per-beat emotion curves on the timeline, FCPXML export, cloud ingest jobs for multi-hundred-hour archives, and a "why this shot" explanation per card.

Built With

Share this project:

Updates

Submission history