FirstLook

Stop guessing which trailer cut to run. Test cuts against ~44k real releases in ClickHouse, simulate rollout, and let Gemini optimize campaign engagement before you spend.

Comment

Inspiration

An independent filmmaker finishing a film has raw footage, a poster, and a release date — but almost zero marketing capacity. They are forced to guess which teaser cut to run, which caption to post, and how to schedule their rollout. Every wrong guess burns audience attention they can never recover.

While generative AI makes it easy to produce dozens of video variants in an afternoon, it answers the wrong question. The bottleneck in film marketing isn't generating more content — it's knowing whether any cut is actually competitive before spending budget on it.

We asked a fundamental question: Before you spend a single dollar, can an AI agent test your campaign against how real theatrical and streaming releases actually performed, at a scale no human team could manually query?

ClickHouse's public SQL Playground contains 4.56 billion real YouTube rows, including tens of thousands of real movie trailers. That is the objective benchmark. FirstLook puts it at the center of the film-marketing loop.

What it does

FirstLook is an autonomous film-marketing agent that plans campaigns and benchmarks creative performance against real releases:

  1. Brief the Film: The filmmaker uploads video clips, poster art, logline, target audience, release date, and spoiler exclusion constraints.
  2. Asset-Grounded Campaign Planning: Gemini 3.6 Flash plans a spoiler-safe rollout grounded strictly in the film's provided assets, creating multi-platform post hooks across TikTok, Instagram, and X.
  3. Off-Serverless Media Rendering: Remotion renders a 16:9 main teaser, a 9:16 vertical cut, and a 4:5 poster. Any AI proxy shot is visibly stamped with a GENERATED · PROXY badge and hatch overlay to preserve transparency.
  4. 4.56B-Row ClickHouse Benchmark via MCP: The load-bearing core. Every asset is benchmarked against ClickHouse's public 4,557,605,031-row youtube dataset via the official mcp-clickhouse server (run_query tool). The system computes real median and top 90th-percentile (p90) engagement rates across 44,000+ movie trailers.
  5. Honest Campaign Calendar: Scheduled deliverables are laid out on a filmstrip calendar leading up to release day. Publishing is simulated (Published · SIM) so nothing modifies real accounts without authorization.
  6. Data-Grounded LLM Revision Loop: The agent ingests campaign engagement into a ClickHouse MergeTree table, identifies underperforming posts relative to the 4.56B-row benchmark, and prompts Gemini to reason against the data and propose higher-performing captions.
  7. Interactive Preview Testing: An owned mobile preview page allows testing cut variants (Action Hook vs Emotional Hook), streaming telemetry with synthetic = 1 tagging directly into ClickHouse.

Why ClickHouse is Essential

The core value proposition — "this teaser cut beats the p90 benchmark for real movie trailers" — is only meaningful if computed over real industry-scale data:

  • Billion-Row Analytical Scan: FirstLook executes real OLAP aggregations across 4,557,605,031 rows in youtube.youtube in seconds. PostgreSQL or SQLite would timeout, run out of memory, or take hours to run what ClickHouse computes in sub-seconds.
  • Stage-One MCP Track Compliance: All database queries are executed at runtime via the official mcp-clickhouse MCP server (run_query tool) using @modelcontextprotocol/sdk, enabling agentic tool execution.
  • MergeTree High-Throughput Ingestion: Event telemetry lands in a MergeTree columnar table (campaign_events) with synthetic = 1 labels, enabling real-time countIf aggregations.

How we built it

Built 100% using Google Antigravity (agy) / Gemini CLI:

  • AI & Planning: Gemini 3.6 Flash (@google/genai) for structured JSON campaign plans and comparative benchmark revision.
  • Concept Art & Audio: Google Imagen for labeled proxy poster concept art and Google Lyria 3 for original soundtrack score.
  • Cloud Storage & Security: Google Cloud Storage (@google-cloud/storage) for media deliverables and Google Cloud Secret Manager (secretmanager.googleapis.com) for production IAM credential mounting.
  • OLAP Engine: ClickHouse via official mcp-clickhouse stdio tool transport.
  • Video Rendering Engine: Remotion 4 (@remotion/renderer) driven by a background job queue.
  • Frontend & App Shell: Next.js 15 (App Router), React 19, Tailwind CSS v4, and SQLite (node:sqlite).

🏛️ ClickHouse Necessity Table

ClickHouse Feature Where Used in Code Why It's Essential
4.56B-Row YouTube Benchmark Scan (count(), median(), quantile(0.9)(), positionCaseInsensitive()) lib/clickhouse-mcp.ts:98 Scans 4,557,605,031 rows in youtube.youtube to calculate real median and p90 engagement across 44,000+ movie trailers. Non-substitutable OLAP that Postgres cannot do.
MergeTree Event Engine (DateTime64, Enum8, ORDER BY (brief_id, item_id, ts)) lib/clickhouse-mcp.ts:135 High-volume columnar ingestion of impression, like, click, and complete events.
Server-Side OLAP Aggregation (countIf(kind = ...)) lib/clickhouse-mcp.ts:205 Computes real-time engagement rates directly within ClickHouse to feed the revision loop.
Official mcp-clickhouse MCP Server (run_query) lib/clickhouse-mcp.ts:25 Routes all runtime ClickHouse operations through MCP tool calls for strict track compliance.

🧠 ClickHouse Agent Skills Integration (Sponsor-Encouraged)

Per the official Devpost hackathon rules ("Use of ClickHouse Agent Skills during development is optional but encouraged"), FirstLook actively leverages the official clickhouse/agent-skills suite under .agents/skills/. Our schemas, queries, and pipelines directly implement the validated rules:

  • agent-connect-mcp: Stdio JSON-RPC transport via uvx mcp-clickhouse with zero-credential prompting.
  • query-mv-incremental: AggregatingMergeTree with SimpleAggregateFunction(sum, UInt64) for zero-raw-scan reads.
  • decision-real-time-preaggregation: Dual-path architecture querying the rollup MV with a raw table fallback.
  • agent-query-safety: Strict SETTINGS max_execution_time = 30, timeout_before_checking_execution_speed = 0 on 4.56B scans and max_execution_time = 15, LIMIT 100 on dashboard queries.
  • query-index-skipping-indices: Bloom filter index INDEX idx_session_id session_id TYPE bloom_filter GRANULARITY 4 to accelerate windowFunnel and session queries.
  • schema-types-lowcardinality: platform LowCardinality(String) for group-by dictionary optimization.
  • schema-types-enum: kind Enum8(...) for compact 1-byte action taxonomies.
  • schema-types-avoid-nullable: Zero Nullable columns to boost vectorized processing.
  • schema-pk-cardinality-order: ORDER BY (brief_id, item_id, ts) for optimal granule skipping.
  • insert-batch-size: Batch size set to 1,000 rows (batchSize = 1000) to avoid tiny part fragmentation.

⚙️ Architecture Diagram

FirstLook System Architecture

Note: The high-resolution architecture topology is also available in the Project Gallery and live in the application at https://firstlook-web-933560214849.us-central1.run.app/architecture.

Challenges we ran into

  • Dual ClickHouse Topology (Read-Only vs. Writable): ClickHouse's public SQL Playground hosts the 4.56B-row youtube dataset as strictly read-only, while our campaign telemetry requires a high-throughput write path. We solved this by architecting a dual-leg routing engine through the official mcp-clickhouse server: analytical benchmark scans target the public playground, while event streaming lands in ClickHouse Cloud with zero cross-instance pollution.
  • Stdio MCP Transport in Serverless Runtimes: Standard MCP setups expect local CLI environments. Packaging uvx mcp-clickhouse inside a containerized Next.js Cloud Run service required explicit stdio lifecycle management, persistent process isolation, and fallback handling to ensure queries execute reliably under concurrent user requests.

Accomplishments that we're proud of

  • Sub-Second Scans Across 4.55B Rows: Using mcp-clickhouse's run_query tool, FirstLook aggregates 4,557,605,031 YouTube rows and scans ~44,000+ theatrical trailers to compute real median (0.53%) and p90 (2.98%) engagement benchmarks in under two seconds.
  • Zero Raw Table Scans via Incremental Rollups: Designed an AggregatingMergeTree Materialized View (campaign_rollup) and windowFunnel(3600000) conversion pipeline that calculates drop-offs at insert time, fully eliminating expensive runtime table scans.
  • End-to-End Multimodal Asset Pipeline: Built a programmatic media factory pairing Google Gemini 3.6 Flash planning, Google Lyria 3 original score generation, Google Imagen concept stills, and Remotion 4 rendering to output 16:9 teasers, 9:16 vertical cuts, and 4:5 posters in minutes.
  • 100% Google-Tool Compliant: Built strictly using Google Antigravity and Gemini CLI in full adherence to Section 7.B with zero non-Google coding or AI APIs.

What we learned

  • Pre-Aggregation Over Raw Scans: ClickHouse's AggregatingMergeTree with SimpleAggregateFunction(sum) and sumSimpleState proves that high-volume marketing telemetry doesn't need external rollups or periodic cron jobs—ClickHouse handles incremental pre-aggregation at insert time with zero orchestration.
  • Simulated Metrics Demand Structural Separation: Synthetic data is dangerous if conflated with real results. Tagging synthetic = 1 into the database schema and enforcing Published · SIM badges in the UI gave creative teams confidence without ever overpromising real reach.
  • Agentic OLAP Needs Guardrails: Direct LLM SQL generation is brittle on billion-row tables. Using validated rules from the ClickHouse Agent Skills suite (agent-query-safety, schema-pk-cardinality-order, bloom filter indices) ensured queries always execute within deterministic latency budgets.

What's next for FirstLook

  • Vector Similarity Cohorts: Implement ClickHouse vector search over 768-dimensional multimodal embeddings (gemini-embedding-001) to match film cuts against narrow micro-cohorts (specific sub-genres, tone, pacing, and visual style) rather than platform-wide trailer percentiles.
  • Consented Screener A/B Testing: Connect FirstLook to real, password-protected private screening links for festival programmers and test audiences, streaming consented telemetry directly into ClickHouse to compare real viewer retention against the 4.56B baseline.
  • Automated Long-Form to Short-Form Adaptation: Ingest full feature films and generate spoiler-safe promotional cuts automatically by combining scene-boundary detection, narrative pacing analysis, and ClickHouse benchmark optimization.

Built With

  • clickhouse
  • gemini-3.6-flash
  • google-cloud-run
  • google-cloud-secret-manager
  • google-cloud-storage-(@google-cloud/storage)
  • google-gen-ai-sdk-(@google/genai)
  • mcp-clickhouse-(official-clickhouse-mcp-server)
  • next.js-15
  • react-19
  • remotion-4
  • tailwind-css-v4
  • typescript
Share this project:

Updates