Add backup verification job that restores nightly snapshot into ephemeral DB and runs smoke checks

[thlpkee20-wq]

Description

Backups are only useful if restorable. Add a nightly job that restores the latest backup into an ephemeral database and runs smoke queries, failing loudly if restore breaks or core invariants don't hold.

Requirements and context

• Must be secure, tested, and documented
• Should be efficient and easy to review
• Relevant code: src/db/, scripts/
• Smoke checks must include row-count sanity and migration head verification

Suggested execution

• Fork the repo and create a branch
• git checkout -b devops/backup-verify
• Implement changes
  • Add scripts/verify-backup.ts orchestrator
  • Add smoke-query suite executed post-restore
  • Wire CI job with secrets-scoped credentials
• Validate security and correctness assumptions

Test and commit

• Run tests
  • npm test
• Cover edge cases
  • Corrupt archive, partial restore, migration head drift
• Include test output and notes

Example commit message

devops: nightly backup restore verification

Guidelines

• Minimum 95 percent test coverage
• Clear documentation
• Timeframe: 96 hours

[esenyidaniel-hash]

@esenyidaniel-hash has applied to work on this issue as part of the Stellar Wave Program's 6th wave.

Please can I be assigned this issue to work on?

ℹ️ Repo Maintainers: To accept this application, review their application or assign @esenyidaniel-hash to this issue.