ADR 0051 — SweepMedia workflow

Deleting media is soft: it sets a deleted_at timestamp so an editor who deletes a file by accident can recover it from version history. The cost is unbounded growth — every soft-deleted row still occupies a D1 row and an R2 object. The SweepMedia workflow is the reaper that bounds it.

The decision

SweepMedia is a long-lived workflow class in src/worker/workflows/sweep-media.ts that follows the step.sleepUntil pattern used by the pruning and backup workflows.

• Schedule — daily at 02:00 UTC, one hour before the nightly backup so the backup captures the post-sweep state and never archives media the sweep just hard-deleted, and two hours before pruning. The install handler creates the instance at first deploy and persists its id under the _sweep_media_workflow_instance_id setting.
• Retention window — 30 days by default, configurable via the workflow's retentionDays parameter for tests and one-off operator overrides.

The per-pass algorithm

1. Find soft-deleted rows where deleted_at is older than the retention window, oldest first, capped at 500 per pass.
2. For each row, run the reference scan: the media repository's referencesIn hydrates the blocks_json and data_json (including R2 spillover) for every live document and walks the JSON for a matching mediaId.
3. If any reference remains, leave the row alone. The editor can clear the references and the next sweep will pick it up.
4. Otherwise hard-delete the row from media. The entity_versions snapshots under media_metadata remain, so site-wide time travel can still see that the row used to exist.
5. Refcount the R2 object: if another live row shares the content_hash, leave the bytes; otherwise delete the R2 object. R2 deletion is best-effort — a missing object is fine and a transient hiccup is retried on the next pass.

Each daily iteration is a single workflow step, so workflow retries cover transient D1 and R2 errors and permanent failures surface on the Health page.

Why these constraints

• Reference safety — a soft-deleted row may still be referenced by a published document. Hard-deleting it would surface a broken image to readers, so the scan must run first.
• Refcount on content_hash — dedup-by-hash means two rows can point at the same R2 object. Deleting one row's bytes while another live row still uses them would be data loss.
• Operator surprise — a deletion an editor regrets within days must be recoverable without restoring the whole database. Thirty days matches the conventional trash-bin default.

Consequences

• Soft-deleted media is recoverable for 30 days through version history; the tombstone version captures the pre-delete state.
• The reference scan is the load-bearing safety check. The 500-row cap keeps a pass to seconds on a personal site and minutes on a large one.
• A document that references a deleted media row indefinitely is detectable — the row sits in the candidate set forever and the renderer shows the missing-source fallback.
• The Health page lists the long-lived workflow instances so an operator can verify the sweeper is alive.