Database Backup and Disaster Recovery Contract Design

Wrote docs/architecture/tasks/aurora-backup-001/backup-design/db-backup-plan.md. Complete database backup and restore contract specification establishing point-in-time recovery, RPO/RTO SLAs, automated restore drills, and WORM storage isolation.

---

skill: backup-design

Database Backup & Restore Plan: Core Payment Ledger [BKP-PAY-001]

Summary

This specification establishes the database backup, disaster recovery, Point-in-Time Recovery (PITR), and automated restore verification plan for core-payment-ledger-db v2.0 under run ID aurora-backup-001. It governs backup and recovery architecture across primary AWS Aurora PostgreSQL 16 clusters managing $65B in transaction balances across 18 million active credit accounts. It decisively investigates and resolves the catastrophic data loss and recovery paralysis demonstrated in incident BKP-4919 (where automated nightly snapshot backups were never tested for restore, and when a storage volume corrupted, the restore script failed due to an outdated PostgreSQL major version incompatibility, extending downtime to 54 hours, losing 18 hours of transaction history, and incurring $5.2M in regulatory fines and ledger reconciliations). The plan defines continuous write-ahead log (WAL) archiving with a 5-minute RPO, establishes automated multi-AZ cross-region Point-in-Time Recovery with a 30-minute RTO, enforces WORM (Write Once, Read Many) ransomware-isolated snapshot vaults, and mandates

automated weekly synthetic restore verification drills.

Detailed Description

A backup that has never been restored is not a backup; it is merely an unverified assumption. In enterprise financial systems, relying on automated cloud provider snapshot checkmarks without executing regular, automated restore verification guarantees catastrophic downtime when real storage failures or ransomware attacks occur. Comprehensive Backup Design specifies the complete recovery lifecycle: continuous transaction log archiving to achieve near-zero Recovery Point Objectives (RPO), automated snapshot lifecycle tiers, cross-region replication to survive regional cloud outages, immutable storage locks, and automated restore testing pipelines that validate data integrity down to the transaction row.

Primary Production Database (AWS Aurora PostgreSQL 16)
                         │
        ┌────────────────┴────────────────┐
        ▼ (Continuous Stream: RPO <= 5m)  ▼ (Daily Snapshot: 02:00 UTC)
  [ Write-Ahead Log (WAL) Stream ]   [ Automated Aurora Storage Snapshot ]
        │                                 │
        ▼                                 ▼ (Replicated via AWS Backup)
  [ Amazon S3 Vault (Cross-Region) ] [ Immutable AWS Backup Vault (WORM) ]
        │                                 ├── Object Lock Legal Hold (365 Days)
        │                                 └── Ransomware-Proof Air Gap
        └────────────────┬────────────────┘
                         ▼
[ Automated Weekly Restore Verification Pipeline (Synthetic Staging) ]
  ├── 1. Provisions Ephemeral Isolated Aurora Cluster from Backup
  ├── 2. Runs Cryptographic Row Checksum & Ledger Integrity Verification
  └── 3. Tears Down Test Cluster & Emits Compliance Attestation Receipt

Criteria and weights

Comparison

Result

Option C is selected. Continuous write-ahead log (WAL) archiving is paired with daily storage snapshots replicated to an immutable cross-region AWS Backup vault; automated weekly restore drills verify recovery pipelines in staging.

---

Required Mechanisms

1. Backup Schedule & Lifecycle Retention Matrix [MC-BL-01]

2. Recovery Objective Service Levels [MC-RO-01]

Recovery Point Objective (RPO):

$\le 5\text{ minutes}$ (Maximum allowable transaction data loss window under catastrophic failure).

Recovery Time Objective (RTO):

$\le 30\text{ minutes}$ (Maximum allowable elapsed time from failure declaration to active database read/write availability).

3. Point-in-Time Recovery (PITR) Execution Procedure [MC-PR-01]

• The BKP-4919 Prevention Workflow:
  1. Operator or automated orchestrator identifies failure timestamp $T_{\text{fail}}$.
  2. Issues Aurora clone command targeting timestamp $T_{\text{fail}} - 60\text{ seconds}$.
  3. Aurora storage layer recreates cluster using copy-on-write pointers in $< 12\text{ minutes}$.
  4. Database DNS endpoint payment-db.internal swings to the restored cluster via automated Route53 health checks.

4. Automated Weekly Restore Verification Drill [MC-RV-01]

• An automated AWS Step Functions workflow executes every Tuesday at 01:00 UTC:
  • Provisions a fresh staging Aurora cluster from the latest cross-region weekly snapshot.
  • Executes data integrity query: SELECT count(*), sum(balance_cents) FROM customer_accounts.
  • Verifies that row count and ledger sums match the production source snapshot within 0.00% tolerance.
  • Emits cryptographic verification attestation receipt to compliance audit bucket; tears down the test cluster.

---

Invariants and Contracts

Mandatory Automated Restore Verification [INV-BKP-01]
  Database backups must undergo automated synthetic restore verification at least once every 7 calendar days.
  Relying on un-tested backup snapshots without automated restore verification is strictly prohibited.

RPO Bound Ceiling Invariant (RPO <= 5 min) [INV-BKP-02]
  Continuous transaction log archiving must not experience replication lag exceeding 300 seconds (5 minutes).
  WAL archive lag exceeding 5 minutes triggers an immediate Sev-1 alert to Database Reliability Engineering.

Immutable WORM Retention Compliance [INV-BKP-03]
  Production backup snapshots must be locked under Write-Once-Read-Many (WORM) compliance mode.
  Deleting or modifying backup snapshots prior to their designated retention expiration is physically blocked.

Explicit Unknowns

• Cross-region AWS data transfer bandwidth throttling during nationwide fiber optic congestion events (G-1).
• Time required to restore 45 TB of historical transactional audit archives from Glacier Deep Archive (G-2).

Traceability

Verification

No validator was supplied, so no command was run.

Reviewer self-check against backup design standards:

• RPO/RTO Realism: PASS. 5-minute RPO achieved via WAL streaming; 30-minute RTO via Aurora fast cloning.
• Restore Verification: PASS. Automated weekly Step Functions drill prevents repeat of incident BKP-4919.
• Ransomware Protection: PASS. WORM compliance lock prevents malicious or accidental snapshot deletion.
• Markdown Hygiene: PASS. Native Markdown syntax strictly adheres to rule_markdown.md.

Open Decisions

• DEC-BKP-01: Elena Rostova to determine whether cross-region disaster recovery standby should maintain a live hot-standby read replica or rely on 30-minute on-demand PITR restoration (Owner: Elena Rostova).

Next steps

1. Database Platform squad configures continuous WAL streaming to S3 and enables AWS Backup WORM locks.
2. SRE team implements the automated weekly restore verification Step Functions workflow in staging.
3. Conduct disaster recovery game day simulating total primary AWS AZ failure to verify 30-minute RTO restoration.