Skip to content

Episode & Clip Lifecycle — App-Side Handling

How the application layer handles trimming, clipping, promoting, and deleting episodes, and the exact database + S3 effects of each action. This is the app-side companion to DATABASE.md: the schema (schemas/episodes.ddl, schemas/clips.ddl) and the lifecycle functions (queries/admin_lifecycle.sql) provide the primitives; this doc defines the orchestration the app performs around them.

Golden rule — the database moves rows, the app moves bytes. Every function in admin_lifecycle.sql runs under RLS as tobor_app and only ever touches DB state. It hands the app the S3 paths to act on; the app is responsible for all S3 / Glacier object movement and MP4 / parquet extraction. If the app crashes mid-flow, DB state and S3 state can diverge — so every flow below is written to be resumable and to change DB state only after the irreversible byte work is either done or safely deferred.

0. The pieces

Store Holds Key columns
episodes One row per full recording. Owns its own S3 objects. mp4_s3_path, parquet_s3_path, frame_count, duration_seconds, aggregate_score, origin, promoted_from_clip_id, storage_state, deleted_at, purge_after
clips Metadata only — a named [start_time_ms, end_time_ms) span into a source episode's video. Owns no S3 objects of its own; points at the parent's paths. episode_id, start_time_ms, end_time_ms, source_mp4_s3_path, source_parquet_s3_path, deleted_at
upload_parts Multipart-upload ledger for an episode's MP4. Disposable once the object has landed. episode_id

Two structural facts drive everything below:

  1. A clip is a pointer, not a copy. It stores offsets + the parent's S3 paths. Deleting the parent's bytes would orphan every clip — so the clips → episodes FK is RESTRICT (no ON DELETE CASCADE): the database physically refuses to delete an episode while a live clip references it. Any flow that removes a parent must therefore deal with its clips first.
  2. storage_state is where the bytes are, and it is the app's contract with itself. standard → glaciering → glacier → restoring → standard. The DB sets the intent (e.g. glaciering); the app performs the S3 transition and flips the state to the terminal value when the bytes have actually moved.

1. Trim & save — the destructive in-place edit

The client scrubs to a new [start, end] and chooses Save (trim the episode). This mutates the episode in place: the same episodes.id survives, but its video, parquet, and duration are permanently narrowed to the selected span. This is not recoverable through the product.

1.1 The warning gate (required)

Because trim is destructive, the app must show a confirmation after the client has made all their edits and pressed Save — a blocking modal:

Trimming is permanent. Saving will replace this episode with the selected span and delete everything outside it. The original will be kept in cold storage for 7 days in case of accident, then permanently erased. This cannot be undone from the app. Trim and save? — [Cancel] / [Yes, trim]

Only on explicit Yes does the app proceed. On Cancel, nothing changes.

1.2 What happens on Yes

Let E be the episode, [s, e) the new span in ms.

  1. Extract the span from the stored MP4 (E.mp4_s3_path) into a new object in the same S3 folder (e.g. …/<episode>/video-trimmed-<s>-<e>.mp4). Extract the matching parquet span too.
  2. Archive the old original. Copy the pre-trim E.mp4_s3_path (and parquet) to Glacier under a 7-day safety key, then remove the old standard-tier object. An S3 lifecycle rule hard-deletes the Glacier safety copy after 7 days — no product surface exposes it; it exists only for ops-level accident recovery.
  3. Repoint the row in one transaction: mp4_s3_path/parquet_s3_path → the new objects.
  4. Drop the stale ledger — delete upload_parts for E (the old multipart ledger is meaningless for the new object).
  5. Re-base and recount. The extracted parquet in step 1 is written with every timestamp_ms re-based to 0 (subtract s), so the trimmed episode starts at zero; update frame_count, duration_seconds, and (if changed) fps to match the new span. Re-basing keeps a trimmed episode indistinguishable from a freshly recorded one — important for any clip later taken from it. Reset aggregate_score to 0 as well: the score described the old span, and re-scoring the trimmed episode is a fresh run.

Ordering for resumability: do the irreversible S3 work (steps 1–2) first, then the DB repoint + recount (steps 3–5) in a single transaction. If the app dies between them, the new object exists but the row still points at the archived original — a retry re-extracts idempotently (same deterministic key) and completes the DB flip. Never delete the old objects before the new MP4 and parquet are durably written.

1.3 Clips on a trimmed episode

A trim narrows the parent, so a pre-existing clip's offsets may now fall partially or fully outside the new span. Trim is only offered when the episode has no live clips (or the app forces the client to resolve them first). Trimming an episode with clips is not supported — promote or delete the clips before trimming. (This restriction avoids silently corrupting clip offsets; if the product later needs trim-with-clips, define the re-clamping rule explicitly.)

2. Save as clip — the non-destructive path

The client scrubs to [start, end] and chooses Save as clip. Nothing about the source episode changes.

  1. Insert one clips row: episode_id = the source episode, start_time_ms, end_time_ms, session_id = the session the clip was created in (may differ from the episode's own session), and source_mp4_s3_path / source_parquet_s3_path copied from the parent so the clip records exactly which bytes it references.
  2. No S3 work. No new MP4. No parquet copy. The clip is pure metadata; play back is done by seeking into the parent's MP4 between the offsets.
  3. The original episode is never deleted or modified by clipping. Multiple clips can reference the same parent.

A clip is cheap and reversible: deleting a clip is a plain hard-delete of the one metadata row (it owns no bytes), and it never touches the parent.

3. Promote a clip to an episode

A client action Promote to episode turns a clip's referenced span into a first-class, self-owned episode. After promotion the clip's content lives on independently of its former parent.

Let C be the clip, P its parent episode.

  1. Extract C's span ([C.start_time_ms, C.end_time_ms)) from C.source_mp4_s3_path into a brand-new S3 location owned by the new episode (its own folder — not the parent's). Extract the parquet span too.
  2. Create the new episode N:
  3. origin = 'promoted', promoted_from_clip_id = C.id (a bare UUID, no FK — deliberately, so the id survives C being deleted, mirroring audit_logs.actor_id).
  4. mp4_s3_path / parquet_s3_path → the new objects from step 1.
  5. robot_id, session_id, task (from C.description / P.task), fps (from P), timing, etc. carried from C / P as appropriate; duration_seconds/frame_count computed from the span; the extracted parquet's timestamp_ms re-based to 0 (as in §1.2); aggregate_score left at its 0 default — N is unscored until a scoring run assesses it, and inheriting P's score would be a claim about data nobody has evaluated.
  6. Delete the clip row C. It owned no bytes, so this is a plain delete of the metadata.
  7. The parent P is left completely untouched — same bytes, same row. Promotion never modifies the parent. (If the client also wants the parent gone, that is a separate delete action — §4.)

Result: a promoted episode is indistinguishable from an original except that origin='promoted' and promoted_from_clip_id records its lineage.

Do the S3 extraction and new-episode INSERT before deleting C, so a crash leaves the clip intact and the promotion simply retries.

4. Deleting a whole episode (not trim)

When the client deletes an entire episode, the app offers two choices. Both paths must first deal with any clips (§4.1), because the clips → episodes FK is RESTRICT.

4.1 Clips are auto-promoted first (both paths)

If the episode has any live clips, the app promotes every one of them to its own standalone episode before deleting the parent — running the §3 promote flow per clip (extract each clip's own MP4/parquet into its own new folder, create its origin='promoted' episode, delete the clip row). Only once no clip references the parent does the app proceed to delete it; otherwise the database would reject the delete.

This is an app-side orchestration, not a DB cascade. Its consequences, which the UI should make explicit before the client confirms:

  • Each former clip becomes an independent episode with its own storage. They no longer depend on the parent's bytes.
  • This detachment is permanent and is not reversed if the parent is later restored from the bin (§4.2). Once promoted, a clip is its own episode forever. (This intentionally differs from the DB's built-in trash_episode/restore_episode clip-cascade, which the app bypasses by emptying the clip set up front.)

Suggested confirmation copy when clips exist:

This episode has N clip(s). Deleting it will first save each clip as its own episode (with its own video), then remove this one. The saved clips will not come back into this episode even if you restore it. Continue?

4.2 Choice A — move to the bin (recoverable, → Glacier)

Calls trash_episode(episode_id[, retention_days]):

  • Sets deleted_at, deleted_by, purge_after, and storage_state = 'glaciering'. Retention precedence: explicit arg → the org plan's retention_days → 30 days default.
  • The app then moves the episode's S3 bytes to Glacier and flips storage_state = 'glacier'.
  • upload_parts is hard-deleted immediately (a ledger for bytes that already landed, and that survive in Glacier — keeping it hot would buy nothing).
  • Because clips were already emptied in §4.1, the DB's clip-cascade in trash_episode is a no-op here.

Restore (in-window): restore_episode(episode_id) clears the trash columns, sets storage_state = 'restoring', and returns the S3 paths. The app thaws the Glacier objects, then flips storage_state = 'standard'.

Purge (retention elapsed): the scheduled purge_trashed() job hard-deletes every episode past its purge_after, returning the S3 paths so the caller can delete the Glacier objects. This is the point of no return for a binned episode.

4.3 Choice B — delete permanently now

Calls delete_episode(episode_id) — an immediate hard delete: captures the episode's set links onto a delete tombstone (for link preservation, see tests/09_link_preservation.sql), drops upload_parts / link rows, then the episode row. The app then deletes the standard-tier S3 objects. There is no bin and no Glacier copy. Refuses on an already-trashed episode (restore it first, or let purge_trashed() remove it).

4.4 The two choices at a glance

Bin (§4.2) Delete permanently (§4.3)
DB call trash_episode() delete_episode()
Row kept (soft-deleted) removed
S3 bytes → Glacier, kept for retention window deleted now
upload_parts dropped dropped
Recoverable? Yes, until purge_after No
Clips auto-promoted first (§4.1) auto-promoted first (§4.1)

5. Storage-state machine

episodes.storage_state is the single source of truth for where the bytes are and what the app still owes:

                    trash_episode()            app moves bytes
   standard ───────────────────────► glaciering ──────────────► glacier
      ▲                                                             │
      │  app finishes the thaw,                                     │ restore_episode()
      │  flips state                                                ▼
      └──────────────────────────────── restoring ◄────────────────┘
                                                   (app thaws Glacier)

   glacier ──(purge_after elapsed: purge_trashed())──► row + bytes deleted

A trim (§1) does not use these states — it stays standard throughout; its 7-day safety copy is managed by an S3 lifecycle rule, invisible to the schema.

6. Invariants the app must preserve

  1. Never delete bytes the DB still points at, and never leave the DB pointing at bytes that are gone. Extract/write new objects before repointing rows; delete old objects after the row no longer references them.
  2. A live clip always has a live parent. Enforced by the RESTRICT FK; the app must promote/reassign clips before removing a parent (§4.1).
  3. The parquet is authoritative for frame-level data. There is no frames table: episodes.parquet_s3_path is the system of record, and any per-timestep read goes to it. Trim keeps the trimmed parquet; trash keeps the Glacier parquet; permanent delete removes it.
  4. Trim and permanent-delete are irreversible from the product. The only safety nets are the 7-day trim Glacier copy (ops-only) and the bin retention window — both time-boxed.
  5. Promotion lineage is immutable. origin='promoted' + promoted_from_clip_id are set once at creation and never rewritten, even after the source clip and/or parent are gone.