Legacy media import

Moving ~300 GB of legacy WordPress exercise videos off a Windows box, into S3, and from there into the platform catalog as static exercises.

The shape of it: the upload is dumb and happens once; every intelligent step happens server-side afterwards. The Windows box does a single verbatim aws s3 sync into a plain, disposable bucket. Nothing is renamed, sorted, or restructured there — the filenames are already the join key, and the one irreversible, un-repeatable, bandwidth-bound step is the worst possible place to be clever. Selecting, slugging, and laying files out into the bake pipeline's structure all happen later, as S3-to-S3 copies inside AWS that cost seconds and can be re-run at will.

The backup bucket

Deliberately not built from the storage-s3 Terraform module. That module sets prevent_destroy = true and enables versioning unconditionally, which is right for platform substrate and exactly wrong here: this bucket is scaffolding, and it must be easy to delete the day the migration finishes.

aws s3api create-bucket \
  --bucket restartix-legacy-import \
  --region eu-central-1 \
  --create-bucket-configuration LocationConstraint=eu-central-1

aws s3api put-public-access-block \
  --bucket restartix-legacy-import \
  --public-access-block-configuration \
  "BlockPublicAcls=true,IgnorePublicAcls=true,BlockPublicPolicy=true,RestrictPublicBuckets=true"

# Orphaned parts from a failed 300 GB sync are invisible to `aws s3 ls`
# and billed as storage. This is the only lifecycle rule the bucket needs.
aws s3api put-bucket-lifecycle-configuration \
  --bucket restartix-legacy-import \
  --lifecycle-configuration '{"Rules":[{"ID":"abort-incomplete","Status":"Enabled","Filter":{},"AbortIncompleteMultipartUpload":{"DaysAfterInitiation":7}}]}'

No versioning (an overwrite should just overwrite), no Object Lock, no prevent_destroy. Same region as everything else — eu-central-1 — which is what makes the later copies free and instant.

Credentials for the Windows box

The Windows box needs its own key, scoped to this one bucket and nothing else — not your admin credentials. Four commands from a machine that already has IAM rights; no Console UI needed.

create-user grants programmatic access only — this user has no console password and cannot sign in to the AWS web console at all.

aws iam create-user --user-name legacy-media-uploader

cat > /tmp/legacy-uploader-policy.json <<'JSON'
{
  "Version": "2012-10-17",
  "Statement": [
    {
      "Sid": "ListTheBucket",
      "Effect": "Allow",
      "Action": ["s3:ListBucket", "s3:ListBucketMultipartUploads"],
      "Resource": "arn:aws:s3:::restartix-legacy-import"
    },
    {
      "Sid": "WriteUnderLegacyPrefix",
      "Effect": "Allow",
      "Action": [
        "s3:PutObject",
        "s3:GetObject",
        "s3:AbortMultipartUpload",
        "s3:ListMultipartUploadParts"
      ],
      "Resource": "arn:aws:s3:::restartix-legacy-import/legacy/*"
    }
  ]
}
JSON

aws iam put-user-policy \
  --user-name legacy-media-uploader \
  --policy-name legacy-import-write \
  --policy-document file:///tmp/legacy-uploader-policy.json

aws iam create-access-key --user-name legacy-media-uploader

The last command prints AccessKeyId and SecretAccessKey. The secret is shown once and never again — if you lose it, delete the key and make a new one.

What the policy deliberately does not grant:

• No s3:DeleteObject. A compromised Windows box cannot destroy an upload that is already 200 GB in. This is the single most valuable line in the policy.
• No access to any other bucket. Not the exercise-assets buckets, not uploads, not backups. If that key leaks, the blast radius is one disposable bucket full of videos you already have a copy of.
• No IAM, no EC2, no anything else.

s3:GetObject is granted, even though a one-way upload doesn't strictly need it — it costs nothing here (the box already holds the files) and it keeps aws s3 sync from failing partway through a 20-hour run over a permission it wanted for a comparison.

Now on the Windows box, in PowerShell:

aws configure --profile legacy
# AWS Access Key ID     [None]: AKIA...          <- paste from create-access-key
# AWS Secret Access Key [None]: ...              <- paste from create-access-key
# Default region name   [None]: eu-central-1
# Default output format [None]: json

aws s3 ls s3://restartix-legacy-import/ --profile legacy    # confirm it works

A named profile rather than [default], so nothing already configured on that box gets clobbered — and so --profile legacy in the sync command is an explicit reminder of which identity is doing the writing.

Revoking it afterwards

The key is long-lived and sits on a Windows box. Delete it the moment the upload is verified — don't wait for the bucket teardown.

aws iam list-access-keys --user-name legacy-media-uploader     # get the AccessKeyId
aws iam delete-access-key --user-name legacy-media-uploader --access-key-id AKIA...
aws iam delete-user-policy --user-name legacy-media-uploader --policy-name legacy-import-write
aws iam delete-user --user-name legacy-media-uploader

IAM refuses to delete a user that still has keys or inline policies attached, so the order matters.

Uploading from Windows

Install AWS CLI v2 for Windows, then in PowerShell:

The CLI defaults are fine — none of the tuning below is required, and the sync works without any of it.

# Optional. Fewer, larger parts: ~4,800 upload requests across 300 GB
# instead of ~38,000. Less per-part overhead on a flaky connection.
aws configure set s3.multipart_chunksize 64MB --profile legacy

# Optional, and ONLY if the connection is needed for other things while
# this runs. It is a throttle, so a value above your real upstream does
# nothing at all — measure first and set ~70% of the upload figure
# (50 Mbit -> 5MB/s, 100 Mbit -> 9MB/s, 300 Mbit -> 26MB/s).
# aws configure set s3.max_bandwidth 9MB/s --profile legacy

Deliberately not raising s3.max_concurrent_requests (default 10): more parallel streams only help when the link has headroom the CLI is not already filling. On a desktop uplink, 10 saturates it, and raising it just adds TLS work for the same throughput.

aws s3 sync "D:\legacy-videos" s3://restartix-legacy-import/legacy/ `
  --profile legacy `
  --region eu-central-1 `
  --checksum-algorithm SHA256 `
  --exclude "*" --include "*.mp4" --include "*.mkv"

Check the extension breakdown before running this — anything the --include list misses is skipped silently, and you would only find out at the coverage check:

Get-ChildItem $src -Recurse -File | Group-Object Extension |
  Sort-Object Count -Descending | Select-Object Count, Name

Notes that matter:

• Do not rename anything. The filename is _exercise_video_id and it is the only link to the title. The import script lowercases on the way out; the source stays untouched.
• --checksum-algorithm SHA256 is not optional. S3's ETag is not an MD5 for multipart uploads, so without it there is no integrity check on the large files.
• Stop the machine sleeping — powercfg /change standby-timeout-ac 0. A sleeping box mid-sync is the most common way this job silently stalls overnight.
• Resumable and idempotent: re-run after any interruption and it skips what already landed. Verify by re-running with --dryrun — empty output means everything is there at matching size.
• Check the connection's monthly data cap before starting. 300 GB of upstream overruns a lot of home plans.

Staging into an environment

Once the bytes are in the backup bucket, infra/scripts/legacy-import.py does the rest.

./infra/scripts/legacy-import.py index

Lists the bucket, joins it against .platform/legacy_exercises.csv, and reports both directions — files with no CSV row, and CSV rows with no file. Run this first; it is the proof the upload actually completed.

./infra/scripts/legacy-import.py apply --program 5360 --env dev

Stages one program's exercises for local testing. Program 5360 ("Exerciții pentru managementul durerii la genunchi") is the recommended trial: 5 exercises, ~280 MB, all of them real type=video rows. Each lands as the two files kind=static requires:

platform/lx-{video_id}/manifest.json
platform/lx-{video_id}/static-video-ro.mp4

Then in Console, run Sync from S3 — it scans for {slug}/manifest.json, creates a draft row per new slug, and projects kind from the manifest. Publish one and play it to confirm the whole chain works before committing to the full set.

./infra/scripts/legacy-import.py apply --all --env production

Every type=video row — 1,430 exercises after de-duplication. Production prompts for typed confirmation.

Do not stage the full set into more than one environment. 300 GB × 3 is real money for no benefit; dev gets the handful of programs you actually test against, production gets everything.

Why the slug is lx-{video_id} and not the title

exercises.slug is the S3 directory name, and the Console sync projects it into the row — so a slug change means moving every object under that prefix. Deriving it from the title would make 1,430 naming decisions, each of which breaks the moment someone renames an exercise.

reference_code (EX-0042) is the right concept — stable, language-neutral, immutable by trigger — but it cannot be the folder name: reference_number comes from a Postgres sequence at INSERT, and the row is inserted from the S3 folder. The code does not exist until after the folder has been read.

So the slug carries the legacy id, which is already stable, already unique, and already the filename. The display title lives in exercises.name + .translations, where it can change as often as anyone likes without touching S3. Lowercasing the 1,978 distinct legacy ids was checked and produces zero collisions.

Titles and taxonomy

The Console sync seeds exercises.name from the slug — the manifest has no display-name field — so without a backfill every imported exercise is called lx-jsicwtenlt. legacy-import.py sql emits that backfill.

What the legacy export does and does not carry:

• Titles — present for all 1,977, in legacy_exercises.Title.
• Descriptions — absent. Content is empty on all 1,992 rows.
• Taxonomy — absent, and there is nothing to re-export: the legacy WordPress never categorised exercises. Program-derived tagging is therefore not a fallback, it is the taxonomy, and this import is where it gets created. Apply it per selection (--tag body_region=shoulder), which is defensible because a program's exercises share a region by construction.

Two data shapes to know about. 676 rows carry a non-unique title — 178 Intro, 50 Introducere, 40 Outro — because they are per-session framing videos; --disambiguate rewrites them from the playlist position. And WordPress titles contain U+00A0 in places (Școala\xa0Spatelui), which renders identically to a space and breaks every search for the visible string; the script folds it at the boundary.

Rebuilding a legacy program

legacy-import.py structure turns a legacy program's playlist into a platform program.

The legacy shape is (phase, week, day) per playlist item. A (phase, week) pair is one stage (program_phases) and each day inside it is one session, so RestartiX Umăr Sănătos — 2 phases x 4 weeks x 5 days — becomes 8 stages of 5 sessions, 694 exercise rows. The legacy data validated clean: all 40 buckets ordered 1..N with no gaps, every one starting Intro and ending Outro.

Every legacy phase boundary becomes a kind='reassessment' checkpoint — no sessions, requires_unlock=true — so the second half stays shut until a specialist has actually reassessed. That is a stage in its own right, which is why the 8-stage program has 9 phases.

Two deliberate choices. Sessions are mode='video_only', sets=1: the source exercises are kind=static and a static video has no rep or hold structure to dose. And phase cadence is left NULL, which means inherit-from-protocol — the template stays undosed so the prescribe dialog asks per patient instead of baking one clinic's schedule into the content.

Re-running the program path deletes and rebuilds — structure cannot be diffed incrementally — so it refuses when anything depends on the program. The cascades are wide enough to matter: protocols.program_id is ON DELETE CASCADE, so deleting a template would delete every prescription using it, and session_runs.session_id is ON DELETE SET NULL, so play history would silently survive with no record of what was played. The guard checks prescriptions, derived instances, runs and appointments.

Not every legacy post is a program. --standalone emits a single library session (program_id NULL) for a flat playlist — Școala Spatelui is 12 ordered clips with no phase/week/day spread, and forcing that into a one-stage one-session program would invent structure the source does not have. sessions.program_id IS NULL is the schema's own word for it. The standalone path upserts instead: an existing session is UPDATED in place and keeps its id, so runs and appointments stay attached, and only session_exercises is rebuilt. Its identity key is idempotency_key (UNIQUE per org), because a session has no slug.

Known data problems

Surfaced while building this; none block the upload, all need a decision before the catalog is complete.

• The export has a quote bug, and the script repairs it on load. One exercise title (WP id 953) begins with a stray " — a data-entry typo. In the playlist JSON that is a legitimate \\" escape, but the export wrote the quote through without doubling it, and a lone " inside a quoted CSV field terminates that field. Python's csv module therefore truncated _post_playlist mid-value on the six programs containing that exercise — 476 (Restart Coloană Lombară), 1782, 1783, 1787, 1788, 1789 — and shifted the rest of each row into garbage. They looked like they needed a fresh WordPress export; they did not. The data was always in the file: 8 occurrences of the sequence, one repair in read_rows(), and all 53 programs parse. 476 recovers whole — 600 items, 170 exercises, 8 stages, 40 sessions.
• The linkage is _post_playlist, not _post_exercises. The latter points at sessions (its ids sit below 506, where the exercise id space starts) and is empty on almost every program. _post_playlist carries exerciseId plus the phase/week/day/order structure. Where it parses, the match rate against legacy_exercises.csv is 1,818 / 1,818.
• 7 duplicate video ids — two WP rows sharing one file. One video id is one folder is one slug, so the script collapses them onto the lowest WP id rather than letting a twin silently overwrite.
• 2 ids are not slug-safe: #Aniv413 and workshop-noi-orizonturi. Handle by hand.
• 6 rows are not videos at all — 4 audio (meditations, carrying _exercise_audio_id) and 2 file (eBooks). Row 1042 has neither a title nor a video id.
• 550 rows have no _exercise_type — 109.5 h, averaging ~12 min, sampling as recorded live sessions and webinars rather than exercises. They are roughly a third of the bytes and should not become static exercise rows. Their destination is the still-undesigned educational-video content type; leave them in the backup bucket until that exists.

Current state (2026-08-26)

Backup bucket	2,252 objects, 289.3 GiB, $7.09/month. Every CSV-referenced video present; 275 orphans.
Local	215 exercises published + named + tagged. Umăr Sănătos (9 phases / 40 sessions / 694 rows). Școala Spatelui standalone session.
Staging	S3 bundles only — 430 objects. No DB rows, nothing published.
Production	Nothing.

What is left

Do now

• Revoke the uploader key. legacy-media-uploader's access key is still Active on a Windows desktop and the upload is finished. See Revoking it afterwards. This is the only item with a security clock on it.
• FFMPEG_THREADS is unset in services/media/.env.local, so it defaults to 1. Bulk publish ran single-threaded.
• 213 _instructions renders sit pending because the dispatcher lives in the API service and it was not running during bulk publish. They clear themselves on the next API start — the dispatcher discovers the bundles ship no instructions footage and deletes each row. Harmless, but they show as phantom pending jobs on the render-pipeline page until then.

Before staging

Staging owes a deploy + database reset first; that gate is not this document's. Once it is through, staging is three steps and no new decisions: Sync Exercises → the two generated SQL files → make bulk-publish.

Before production

• Sync has to go async first. It already timed out at 231 exercises against HTTP_WRITE_TIMEOUT=30s — the API waits up to MEDIA_SERVICE_TIMEOUT=60s for media while the HTTP server abandons the response at 30s, and media walks S3 for every slug before returning anything. At 1,977 no timeout value is the right answer. The rows commit regardless, so the failure is invisible unless you count them.
• The 000040–000050 promotion is a separate, larger job — see production-apply-runbook.md.
• Then: apply --all --env production, production-media-toggle.sh up, make bulk-publish, toggle back down. Fargate for the full ingest is ~$10–14.
• Do not tear down the backup bucket until the transcoded output has been accepted. Ingest normalises every source to 720p/24fps at CRF 20; once the originals are gone there is no re-deriving at different settings. $7/month is the cheapest insurance in this project.

Open data questions

• Summit 2024 (5209) has no exercise playlist. Its _post_playlist parses to type: null with zero items, so the 13 summit2024 files in the backup bucket have no linkage. That is a genuine gap in the export rather than a parse failure — unlike the six programs above, which turned out to be recoverable in place.
• 275 orphan files — 174 provider-id-shaped, 61 numeric, 25 named (nutritional-1…12), 13 summit2024. They match no row in any export, which says the export did not cover every post type. Listed in .platform/legacy-orphans.txt.
• 550 rows carry no _exercise_type — 109.5 h averaging ~12 min, sampling as recorded webinars. Roughly a third of the bytes, and they should not become static exercises. They wait for the educational-video content type.
• Session covers. Every legacy program and session carries a live Image URL, but no session cover column exists and no session card renders one. Analysis in this document's history; the home question (catalog_entries.cover_url vs sessions.cover_url) is open.
• Exercise names and stage numbers disagree. Framing clips read Umăr Sănătos — Intro 1.1.1 (legacy phase.week.day) while the program now says Etapa 1 / Sesiunea 1. Deliberately deferred; one regenerate fixes it.

Teardown

The backup bucket is scaffolding. Once every wanted file has been staged into production and verified, and .platform/legacy-mapping.csv is backed up somewhere outside this machine:

aws s3 rm s3://restartix-legacy-import --recursive
aws s3api delete-bucket --bucket restartix-legacy-import --region eu-central-1

No versioning means no delete markers and no lingering noncurrent versions — the bucket empties and goes.

Keep legacy-mapping.csv — and keep the legacy_*.csv exports it was built from. .platform/ is gitignored (large media lives outside the repo), so none of them are in git: they exist on one machine until someone copies them elsewhere. They are the only record of which S3 object corresponds to which legacy exercise and title, and they are what the eventual DB import reads. Losing them after the backup bucket is deleted means 1,430 videos with no titles and no program structure.

What this does not do

Uploading is not importing. The ingest (POST /v1/exercises/static) transcodes every file through libx264 at CRF 20 — 328 hours of video on Fargate. Production media currently runs at desired_count = 0 and CPU target-tracking cannot scale from zero, so bakes are blocked there until that is fixed. The upload can proceed regardless; the two are independent.