Dailies Triage

Inspiration

An assistant editor logs dailies by hand, overnight, every night of a shoot — writing down who's in each clip, how close the camera is, whether the take is usable. Weeks later a director asks for "the take where she's holding the letter and the light was still warm," and someone goes digging through handwritten logs to find it.

What it does

Dailies Triage parses a screenplay into a controlled vocabulary — the cast, locations, and props a production actually uses — then has Gemini log every shot of footage against that vocabulary: shot size, characters, location, props, time of day, quality. The result lands in ClickHouse as structured rows.

An agent answers plain-English questions about the footage by writing real SQL against those rows, and it doesn't just dump results: it ranks takes, compares them, groups consecutive clips into scenes, and defends a pick if you push back — "are you sure?" makes it re-examine the evidence, never just raise its confidence. Circle a take and it goes on a select report you can export as CSV. Every answer's clip links play inline in a purpose-built viewer, not ADK's generic developer chat — an interface built for an editor: a dim viewing bay next to a camera-report log, with the picked take circled in grease pencil.

We've run this on two real productions, not one demo case:

• Night of the Living Dead (1968) — 20 camera-roll clips, 180 shot rows, characters resolved to names pulled from the screenplay (Ben, Barbara), not unknown.
• The Layla and Majnun legend — an original public-domain screenplay we wrote, paired with 171 clips covering a full feature end to end, logged as 2,309 shot rows.

How we built it

Gemini for both stages — parsing the screenplay into a vocabulary, and logging each clip against it — via Vertex AI on Google Cloud. Google ADK runs the agent, deployed to Cloud Run. Retrieval goes through the official mcp-clickhouse MCP server (read-only, query timeouts enforced), so the agent talks to ClickHouse the way any MCP client would, not through a bespoke wrapper.

Three Cloud Run services, each doing one job: the agent, a clip player that streams footage straight out of Cloud Storage with range-request support (so the viewer can scrub, not just play from the start), and the editor-facing UI that proxies both so the browser only ever talks to one origin.

Challenges we ran into

A fixed vocabulary can't describe every production. A period drama has oil lamps; a sci-fi has plasma rifles. So the vocabulary isn't fixed — the screenplay generates it. Gemini reads the script once and extracts that production's cast, locations, and props, and that becomes the only vocabulary Gemini is allowed to use when logging shots, and the only one the agent is allowed to filter on. One field table generates the Gemini response schema, the ClickHouse DDL, and the agent's prompt from the same source, and a test fails the build if the two ever cover different fields.

That still leaves the harder half: a vocabulary derived from a screenplay is only as complete as the screenplay. Judy is a character in Night of the Living Dead, but the 1968 draft names her once in ninety-one pages, so the parser correctly left her out. Then Gemini saw her on screen. The row it wrote: characters: ['Tom', 'unknown'], action: "Judy clings emotionally to Tom while hugging him tight." The closed field refused to guess — it could have picked Barbara or Helen, both plausible — and said unknown instead. The open prose field caught what the enum couldn't express. That's the whole argument for the two-layer design in one row: an editor will forgive an empty result, and will not forgive a shot confidently labelled with the wrong actor's name.

ClickHouse silently drops identical insert blocks. It hashes each inserted block for deduplication, by design, for retry safety — but re-logging a clip whose shots happened to come out identical to a previous run hit that exact case. The insert wrote nothing and the client reported success. Fixed by giving every insert a fresh insert_deduplication_token.

An empty query result looks exactly like an empty archive. If the agent searches for 'wide' but the logger wrote "wide shot", the query returns zero rows — identical to what a genuinely empty archive returns. So the agent follows an explicit protocol before it will tell a user "no footage": check any literal value against the vocabulary first, drop filters one at a time, then search the free-text fields before giving up.

A re-cut clip kept its old description under the same filename. Camera-roll names repeat by convention — every production's first clip is A001_C0001.mp4. When we re-cut our 171-clip corpus, the ingest skip meant to avoid re-spending an API call on an already-logged clip instead kept the old rows under the new clip's name. Fixed at the root: a clip is now stale when its file's modification time is newer than the last time that exact production+filename was logged, and a stale clip gets re-logged, not skipped.

The deployed clip player only had one production's footage. The Cloud Run image for the clip service was built once, staging only the first production's 20 clips. Every watch link the agent produced for the second production 404'd. The fix wasn't a bigger image — 2.5 GB of video does not belong in a container. The player checks local disk first, then streams from the same Cloud Storage bucket the ingest pipeline already writes to, in 1 MiB chunks with range support, never public, always behind the service's own credentials.

A "just retry" fix that cost a full day's quota. The Gemini free tier caps at 20 requests per model per day. Our first ingest batch of 20 clips left one unlogged after a transient error, and the tool's own advice was "re-run to retry" — correct about correctness, ruinous about cost. The re-run re-sent all 20, burning the day's allowance without reaching the one clip that needed it. Fixed: the batch now checks ClickHouse for what's already logged before calling Gemini, and skips it.

Accomplishments that we're proud of

The write side (what Gemini may log) and the read side (what the agent may filter on) are generated from the same source table, and a test fails the build if the two ever cover different fields.

This isn't theory: 2,309 real shot rows across two full productions, sitting alongside a 2,000,000-row synthetic archive used purely to stress-test query latency — the two are never mixed in a claim. Verified live, end to end, against the deployed stack: asked "show me a night shot I can watch," got back a real clip name and a link, and that link plays.

What we learned

Availability is not the same as listing. Four Gemini model ids showed up when we called models.list() against our project, and none of them were actually callable at the time — one 404'd as not available to new accounts, two 503'd as temporarily unavailable, one 429'd on quota. The model that ended up doing all the work wasn't the first one we tried; it was whichever one actually answered. The same split showed up again between Vertex AI and the Gemini Developer API: the model id that works on one backend 404s on the other, and there is no single value that works on both — the agent and the ingest pipeline each resolve their model from which backend they're actually talking to.

What's next

Real dailies for continuity checking — it's only ever run clean against a finished feature, which proves sensitivity, not usefulness. The two-pass survey for unscripted footage needs proving past three clips, where the carried-forward list of known terms grows long enough to become a prompt-size problem of its own. And the ingest trigger we added to the wizard only runs locally today — a 171-clip Gemini batch doesn't belong behind a scale-to-zero Cloud Run request, so that stays a deliberate limit, not an oversight.

Built With

• clickhouse
• cloud-run
• google-adk
• google-gemini
• mcp
• python