Movements library update (canonical exercises)

How to refresh the canonical movement library in production — cleaned taxonomy/names, per-movement category/kind, and owned soft-3D thumbnails.

There are two operations, and only the second touches the prod DB:

The name/category pass must run offline (it needs movements.input.json, which isn’t in the repo or the API). Thumbnails run in the API (a Bull queue), so the expensive step is triggered from prod admin — no laptop.

Prerequisites (one-time)

• Prod env (@taikan/api): FAL_KEY, R2_THUMBNAILS_BUCKET (the dedicated public images bucket, e.g. movements-thumbnails), R2_PUBLIC_BASE (that bucket’s public domain — pub-xxxx.r2.dev or custom), plus the already-present CANONICAL_R2_BUCKET, VOYAGE_API_KEY, ANTHROPIC_API_KEY, R2_* creds.
• Dedicated public bucket for thumbnails — images only, so the canonical JSON bucket stays private. Create movements-thumbnails, enable public access (Cloudflare → R2 → bucket → Settings → Public access); its r2.dev domain is R2_PUBLIC_BASE.
• fal.ai balance topped up.
• Local: movements.input.json present in tools/movements-pipeline/; CANONICAL_R2_BUCKET set locally to the same bucket prod reads; pip install -r tools/movements-pipeline/requirements.txt.

Step 1 — Generate the data (offline)

make movements-canonicalize   # taxonomy/naming → movements.canonical.json
make movements-polish         # LLM names + category/kind → movements.polished.json  (~$1)

Review tools/movements-pipeline/canonicalization_report.md and polish_report.md (renames, category fill, the dedup/flag review queue).

Step 2 — Publish to R2

make movements-publish MOVEMENTS_SRC=tools/movements-pipeline/movements.polished.json

Prints the versioned r2Key (e.g. v2026-06-01T…Z.json) — keys live at the root of the dedicated movements bucket, so no movements/ prefix. The publish script self-loads apps/api/.env; ensure CANONICAL_R2_BUCKET there points at the prod bucket.

Step 3 — Cleanup orphans (after seed, before thumbnails)

Renamed/demoted slugs leave orphan rows on upsert (the seed adds/updates by slug but never deletes). There is no exposed purge endpoint by design — cleanup is a manual, engineer-run script against prod:

# read-only dry-run: lists canonical rows whose slug isn't in the published lib
DATABASE_URL="<Postgres service → DATABASE_PUBLIC_URL>" \
  pnpm exec tsx scripts/cleanup-canonical-orphans.ts
 
# soft-delete them (deleted_at = now()) — reversible, preserves FK + history
DATABASE_URL="<…>" pnpm exec tsx scripts/cleanup-canonical-orphans.ts --apply

It soft-deletes (never hard-deletes), only touches organization_id IS NULL rows, and aborts if orphans exceed 20% of the canonical set (bad-source guard). Run it after the seed and before thumbnails so the in-API queue doesn’t burn fal credits rendering soon-to-be-hidden rows. Orphans still referenced by real data (workouts/results/PRs) stay resolvable — they’re just hidden from search/pickers. Repoint those references to the canonical replacement first if a clean history matters.

Step 4 — Seed the prod DB (admin)

POST /admin/exercises/canonical/seed
{ "r2Key": "<version>.json", "rebuild": true, "rpm": 60 }

rebuild: true overwrites enriched fields; rpm: 60 uses the paid Voyage tier (free tier is 3 rpm → hours for ~600 rows). Monitor:

GET /admin/exercises/canonical/jobs
GET /admin/exercises/canonical/jobs/{queue}/{id}

Step 5 — Generate thumbnails (admin)

POST /admin/exercises/canonical/thumbnails        # missing only
POST /admin/exercises/canonical/thumbnails { "force": true }   # regenerate all

Enqueues one exercise-thumbnail job per canonical row. Each job: fal/FLUX (ControlNet-Canny when the row has a clean reference, flux/dev otherwise) → upload PNG to the public R2 bucket at movements/thumbnails/<slug>.png → set thumbnailUrl to R2_PUBLIC_BASE/.... Safe no-op if FAL_KEY/R2_PUBLIC_BASE are unset.

Step 6 — Verify

Spot-check exercises: names cleaned (Deadlift, not Conventional Deadlift), category/kind populated, thumbnailUrl is on R2_PUBLIC_BASE and renders.

Custom movements (coaches)

• Creating a custom exercise auto-enqueues a thumbnail in the same style.
• Coaches can upload their own image: PUT /organizations/:orgId/exercises/:id/thumbnail (multipart file, png/jpeg/webp ≤5 MB) — org-owned movements only; canonical thumbnails are platform-managed.

Pipeline reference

The offline pipeline lives in tools/movements-pipeline/ (see its RUNBOOK.md): canonicalize.py → canonicalize_llm.py → publish-movements.ts. The legacy offline image_prod.py is superseded in production by Step 5 (the in-API queue).