Disaster Recovery, Continuous Archiving & Cloud SQL PITR

1. Compliance Rationale & Disaster Recovery Objectives

Healthcare data protection is strictly regulated by Brazilian law and professional medical councils. CFM Resolution 1.821/2007 mandates a minimum retention period of 20 years for electronic health records (EHR), while Anvisa RDC 67/2007 requires compounding records (Livro de Receituário Geral) to be preserved for at least 5 years.

In accordance with ADR-007, GERCIA enforces an enterprise-grade, cloud-native disaster recovery architecture powered by Google Cloud SQL for PostgreSQL in the southamerica-east1 region (São Paulo), backed by continuous Write-Ahead Log (WAL) archiving to Google Cloud Storage.

---

2. Service Level Objectives: RPO & RTO

Metric	Target SLA	Implementation Guarantee
RPO (Recovery Point Objective)	$\le$ 5 minutes	Continuous transaction log (WAL) archiving to Google Cloud Storage with real-time replication to regional HA standby.
RTO (Recovery Time Objective)	$\le$ 15 minutes	Automated one-click Point-in-Time Recovery (PITR) via Google Cloud SQL instance cloning to isolated private VPC IPs.
Regional Failover RTO	$< 60$ seconds	Synchronous multi-zone High Availability (HA) failover between southamerica-east1-a and southamerica-east1-b with zero manual intervention.
Long-Term Audit Retention	Permanent	Immutable relational audit trail (core.audit_history) in PostgreSQL protected by automated daily snapshots and continuous WAL archiving, complemented by native Google Cloud Logging retention.

---

3. Continuous Archiving & Recovery Architecture

flowchart TD
    subgraph GCP_SA ["Google Cloud Platform (southamerica-east1 - São Paulo)"]
        subgraph CLOUD_SQL ["Google Cloud SQL (gercia-db-prd)"]
            PRIMARY[("Primary Database (PostgreSQL 18+)<br>Zone: southamerica-east1-a")]
            STANDBY[("Standby Replica<br>Zone: southamerica-east1-b")]
            PRIMARY <-. "Synchronous HA Replication" .-> STANDBY
        end

        subgraph GCS ["Google Cloud Storage (Durable Backups & Documents)"]
            BACKUPS["Automated Daily Base Backups<br>(Retained 7–35 Days)"]
            WAL_STREAM["Continuous WAL Archive Logs<br>(Sub-Second PITR Granularity)"]
            DOCS["gs://gercia-documents-prd<br>(Sovereign Attachments • 5-Rule FinOps)"]
        end

        PRIMARY -. "Continuous WAL Flush" .-> WAL_STREAM
        PRIMARY -. "Automated Daily Snapshot" .-> BACKUPS
        PRIMARY -. "V4 Signed URL Attachments" .-> DOCS
    end

    subgraph RECOVERY ["Disaster Recovery Workflow"]
        RECOVER_INST[("Restored Instance (gercia-db-recovery)<br>Isolated Private IP (10.x.x.x)")]
    end

    WAL_STREAM --> RECOVER_INST
    BACKUPS --> RECOVER_INST

---

4. Point-in-Time Recovery (PITR) Execution Runbook

When human error, malicious activity, or software regression causes catastrophic data corruption:

Step 1: Identify Incident Window & Exact Timestamp

Determine the exact UTC timestamp immediately preceding the corruption event (e.g., 2026-10-10T14:30:00Z).

Step 2: Provision Isolated Recovery Clone via CLI

Never restore destructive backups over an active production instance. Execute PITR into a new target instance:

gcloud sql instances clone gercia-db-prd gercia-db-pitr-recovery \
    --project=gercia-gcp \
    --point-in-time="2026-10-10T14:30:00Z"

Step 3: Validate Tenant Data Integrity

Connect securely to the recovery instance through the Serverless VPC Connector:

1. Verify tenant counts: SELECT COUNT(*) FROM core.tenants;
2. Verify recent prescriptions: SELECT COUNT(*) FROM clinical.prescriptions WHERE created_at >= '2026-10-10';
3. Verify compounding orders: SELECT COUNT(*) FROM pharmacy.compounding_orders WHERE created_at >= '2026-10-10';

Step 4: Promote & Update Secrets

1. Update CONNECTION_STRING_SERVER_PRD in Google Secret Manager to direct application traffic to the restored instance.
2. In-flight Gercia.Server instances on Google Cloud Run automatically discover the updated connection string within 15 minutes (or trigger immediate zero-downtime revision rollout via gcloud run services update).

---

5. Automated Backup Verification & Drill Schedule

To guarantee recovery readiness and satisfy healthcare institutional compliance audits:

• Automated Weekly Verification: An automated Cloud Run Job provisions a lightweight ephemeral instance from the latest backup snapshot, executes database integrity checks (pg_isready, VACUUM ANALYZE), and tears down the instance.
• Quarterly Disaster Recovery Simulation: SRE conducts a full PITR drill, documenting RTO and RPO metrics in internal engineering audit records.