-- ─────────────────────────────────────────────────────────────────────────────
-- 062 — Physical base backups, so PITR is possible on this platform
--
-- Adds a `basebackup` policy: pg_basebackup, compressed server-side, uploaded
-- to <env>/database/backups/base/<timestamp>/.
--
-- ── WHY THIS EXISTS SEPARATELY FROM THE DUMP POLICY ──────────────────────────
--
-- WAL replays onto a PHYSICAL copy of the data directory. A pg_dump is a pile
-- of INSERT statements — there is nothing for WAL to replay onto. So point-in-
-- time recovery needs its own base backup; enabling WAL archiving does not
-- upgrade the logical dumps we already take.
--
--   pg_dump      -> restore to the instant the dump ran, nothing between
--   pg_basebackup + WAL -> restore to ANY SECOND in the window
--
-- ── THE BUDGET, MEASURED RATHER THAN ESTIMATED ───────────────────────────────
--
-- On this database, with server-side zstd:9:
--
--   base backup, uncompressed   435 MB
--   base backup, compressed      45 MB   (~10:1)
--   WAL segment, busy            2.2 MB  (from 16 MB)
--   WAL segment, quiet          0.02 MB  (from 16 MB)
--   WAL segment, average        0.34 MB  (~47:1)
--
-- Uncompressed, one base backup is 42% of the entire 1 GB budget before a
-- single WAL segment. Compressed, two base backups plus a long WAL window fit
-- in roughly 250 MB. Compression is not an optimisation here — it is the only
-- reason PITR is purchasable at this budget.
--
-- retentionCount is 2, and that number is a recovery decision rather than a
-- disk one: a base backup is the EARLIEST point its WAL can replay onto, so
-- dropping the oldest moves the start of the recovery window forward.
--
-- Ships DISABLED. Enabling it is deliberate: it only produces something useful
-- if WAL archiving is also on, and on a developer machine that is a config
-- change plus a service restart.
-- ─────────────────────────────────────────────────────────────────────────────

BEGIN;

INSERT INTO platform.backup_policies
  ("id", "targetId", "name", "backupType", "cronExpression", "timezone",
   "windowMinutes", "retentionCount", "retentionDays", "slaHours", "isEnabled", "sortOrder")
VALUES
  ('bkp_basebackup', 'bkt_forgestack', 'Base backup → R2 (PITR)', 'basebackup',
   -- Daily at 01:00, before the logical dump at 02:00: the base backup is the
   -- expensive one, and giving it the quieter slot keeps the two from competing.
   '0 1 * * *', 'Asia/Kolkata',
   -- A wide window, for the same reason as the dev dump policy: a developer
   -- machine is asleep at 01:00 and the run should still happen when it wakes,
   -- rather than being skipped as "out of window".
   720, 2, 7, 30, false, 5)
ON CONFLICT ("id") DO NOTHING;

COMMIT;