-- ─────────────────────────────────────────────────────────────────────────────
-- 061 — Record backup deletion
--
-- Lets an operator delete a backup object from R2 while KEEPING the run row,
-- marked `deleted` with who did it and when.
--
-- The row is kept deliberately. A deleted backup that vanishes from the run
-- history erases the evidence that it ever existed — so "we had a backup from
-- the 3rd, and someone removed it on the 5th" becomes unanswerable. That is the
-- one question worth asking after a bad restore, and the only reason this
-- subsystem keeps a manifest in Postgres at all.
--
-- Deletion is deliberately limited to LOGICAL (pg_dump) backups. A pgBackRest
-- base backup must never be deleted this way: pgBackRest owns its repository
-- layout and knows which WAL segments are still required by which retained
-- backup. Removing a base backup out from under it leaves WAL that replays onto
-- nothing and a repository that reports healthy while being unrestorable.
-- Physical backups expire through `pgbackrest expire`, which understands the
-- dependency graph. The service enforces this; the comment records why.
-- ─────────────────────────────────────────────────────────────────────────────

BEGIN;

ALTER TABLE platform.backup_runs
  ADD COLUMN IF NOT EXISTS "deletedAt" TIMESTAMP(3),
  ADD COLUMN IF NOT EXISTS "deletedBy" TEXT;

-- The run history filters deleted rows to the bottom rather than hiding them,
-- and the quota recount ignores them, so both want this.
CREATE INDEX IF NOT EXISTS "backup_runs_deletedAt_idx"
  ON platform.backup_runs("deletedAt");

COMMIT;