One Swing, Many Frames: How We Aggregated Video into a Summary with Pixeltable

Summary: When a golfer uploads a swing video to PixelGolf, we need one summary from dozens of frames: swing phases, tempo ratio, spine angle change, head stability. MediaPipe runs per frame. The math runs per frame. The product shows one card per swing. That shape, many child rows folded into one parent row, was the hardest part of our Pixeltable schema. Not pose detection. Not LLM coaching. The fold. If you are building sports video, sensor bursts, document chunks, or session events, this post is a map of which Pixeltable primitive to reach for.

PixelGolf is a private project we are building internally, not a public repo yet. This post shares the aggregation patterns from that work.

The one problem#

User story: Upload one video, get one swing analysis (phases, metrics, coaching).

Data shape:

swings (1 row per upload)
  └── swing_frames (view: ~50-100 rows per video)
        └── frame_metrics (computed per frame)
              └── ??? → swings.swing_analysis (1 JSON blob)
                    └── coaching_feedback, fault_tags, swing_score (cascade)

Everything below is a different answer to that single arrow.

Wrong tool #1: @pxt.udf on the parent table#

A @pxt.udf is row-local: one input row, one output value. It runs once per row in whatever table it is attached to.

We initially thought: put compile_swing_analysis() on swings as a computed column. Problem: when Pixeltable evaluates that column for a swing row, it only sees that swing row. It does not automatically gather the view's frame rows.

Verdict: Not a Pixeltable bug. Wrong primitive. UDFs are for pose(frame) → landmarks, not frames[] → summary. See Python UDFs in Pixeltable for row-local transforms.

Wrong tool #2: @pxt.udf + secret collect() inside#

The escape hatch: write a UDF that looks row-local but secretly queries the child table:

This works. We had something like it early on. We removed it because:

• Lineage breaks. Pixeltable's dependency graph does not know the parent column depends on swing_frames.
• Recompute is opaque. Change the metrics UDF? The parent may not invalidate correctly.
• Ordering is yours to enforce. Frame order matters for tempo and phases; easy to get wrong outside the query planner.
• Upload becomes a lie. You think you declared the pipeline in schema.py, but the real logic hides inside a function body.

Verdict: Same one problem, workable hack, bad long-term tradeoff. We do not recommend it.

Red herring: @pxt.query and retrieval_udf#

These are for lookups: find swings similar to X, fetch session by id, tool-calling templates. Point queries, not folds.

If you are aggregating N rows into 1, @pxt.query is the wrong drawer. We mention it only because the word "query" sounds general-purpose. It is not. See reusable query patterns for when queries fit.

Right primitive: @pxt.uda (User Defined Aggregator)#

Pixeltable's aggregator is the SQL-shaped answer: update() called per row in a group, value() called once at the end. See Beyond AVG(): custom aggregations with UDA for the full API tour.

This is the right place for compile_swing_analysis(). The fold logic lives in one declarative class, not a Python loop in a router.

Gotcha we hit: with requires_order_by=True, the ordering expression must be the first positional argument, not order_by=pos:

Another gotcha: do not add custom __init__ params like fps= unless you are sure Pixeltable binds them to init only. We passed fps=4.0 and it leaked into update(). We moved fps into value() via config.FRAME_FPS instead.

How we ship it today: group_by + UDA in a query#

The UDA defines how to fold. A group_by query defines which rows to fold:

One result row per swing. Ordered frames. Same JSON we would get from hand-rolled Python.

Our upload pipeline still has three steps:

1. Materialize frames: force pose and frame_metrics to compute on the view
2. Run the UDA query: aggregate into analysis JSON
3. update(swing_analysis=…, cascade=True): write to parent and trigger LLM columns

Steps 2-3 live in our upload pipeline module (finalize_swing_analysis and run_upload_pipeline). Step 1 is still required; the UDA does not replace frame extraction, only the manual list assembly.

The finish line we could not reach: parent computed column#

What we wanted: insert-only upload, no pipeline module:

Insert swing → view materializes → parent column fills → coaching_feedback cascades. No run_upload_pipeline(). Lineage visible in the catalog.

We tried it. Pixeltable raised NotFoundError: Table was dropped during add_computed_column. Query-time group_by on the view works; declarative parent rollup does not (yet). That is the remaining gap: not "aggregation is impossible," but "persist aggregated child data onto parent declaratively."

Decision guide#

You want to…	Reach for…
Transform one column on one row	@pxt.udf
Look up rows by key / power tool calling	@pxt.query, retrieval_udf
Fold many rows into one value (sum, list, custom JSON)	@pxt.uda + group_by
Fold child view rows onto parent at insert time	Parent computed column + window UDA (blocked for us; worth retrying on newer Pixeltable)
Fold child rows imperatively at upload	UDA query + update() (what we ship in PixelGolf today)

What we would tell our past selves#

1. It is one problem. Do not read six Pixeltable features as six bugs.
2. Start with UDA, not UDF-on-parent.
3. Never collect() inside a UDF if you care about lineage.
4. Order matters for sports and video: use requires_order_by=True and test with reversed frames.
5. Ship the query + update pattern while waiting for declarative parent rollup.

Pixeltable fits our core loop: video → frames → pose → metrics → LLM coaching. The rough edge is cross-table aggregation ergonomics, not the multimodal pipeline itself. For a full video pipeline walkthrough, see Build a Complete Video Intelligence Pipeline in 20 Minutes.

About PixelGolf#

PixelGolf is a practice swing journal we are building as a private project: upload a range clip, get body-motion analysis and one coaching fix per swing. The repo is not public yet; this post documents the Pixeltable aggregation patterns we landed on while building it.

If you have a cleaner parent-rollup pattern, we would love to hear about it on Discord or in a Pixeltable discussion.