Google Cloud SQL for PostgreSQL Architecture, HA & Security

1. Cloud-Native Relational Persistence Architecture

The persistence tier of the GERCIA Platform operates on Google Cloud SQL for PostgreSQL in the southamerica-east1 region (São Paulo, Brazil). In accordance with ADR-007, all database workloads are consolidated into a high-availability, fully managed cloud persistence engine, eliminating bare-metal operating system maintenance, physical storage bottlenecks, and manual replication topologies.

High-Availability (HA) Regional Topology

flowchart TD
    subgraph INGRESS ["Serverless Ingress Tier (GCP southamerica-east1)"]
        SERVER_SVC["Gercia.Server (Cloud Run Service)<br>Max Pool: 80 Connections • REST, OpenIddict STS, MCP"]
        SERVER_JOB["gercia-migrator (Cloud Run Job)<br>Max Pool: 5 Connections (Ephemeral) • DDL Migrations"]
        VPC["Serverless VPC Access Connector<br>(Private IP Subnet • Zero Public Internet Exposure)"]
    end

    subgraph CLOUD_SQL ["Google Cloud SQL for PostgreSQL (Enterprise HA)"]
        subgraph ZONE_A ["Primary Zone: southamerica-east1-a"]
            PG_PRD[("Primary Instance (gercia-db-prd)<br>vCPU & Dedicated RAM • Private IP (10.x.x.x)<br>TLS 1.3 • SCRAM-SHA-256 • 8 Sovereign Schemas")]
        end

        subgraph ZONE_B ["Standby Zone: southamerica-east1-b"]
            PG_STANDBY[("Synchronous Standby Replica<br>(Automated Regional Failover < 60s RTO)")]
        end

        WAL["Continuous WAL Archiving (PITR)<br>Retained in Google Cloud Storage"]
        INSIGHTS["Cloud SQL Query Insights<br>(Query Plans & Latency APM)"]
    end

    SERVER_SVC --> VPC
    SERVER_JOB --> VPC

    VPC -- "Private Service Connect / TLS 1.3" --> PG_PRD
    PG_PRD <-. "Synchronous Block Replication" .-> PG_STANDBY
    PG_PRD -. "WAL Streaming" .-> WAL
    PG_PRD -. "Execution Telemetry" .-> INSIGHTS

---

2. Infrastructure Sizing & Environment Matrix

The platform segregates database environments logically into dedicated Cloud SQL instances within Google Cloud Platform (gercia-gcp):

Parameter	Production Environment (gercia-db-prd)	Staging Environment (gercia-db-stg)
Instance ID	gercia-db-prd	gercia-db-stg
GCP Cloud Region	southamerica-east1 (São Paulo)	southamerica-east1 (São Paulo)
High Availability (HA)	Regional HA (Automated synchronous cross-zone failover)	Single-Zone (Cost-optimized for staging tests)
Private IP Isolation	Private IP only (VPC peering via Google Private Services Access)	Private IP only (VPC peering via Google Private Services Access)
Public IP Address	Disabled (Zero public ingress)	Disabled (Zero public ingress)
Continuous Backups & PITR	Enabled (7–35 days continuous WAL transaction logs)	Enabled (7 days retention)
Storage Engine	Dynamic auto-expanding SSD array	Dynamic auto-expanding SSD array
Database Engine	PostgreSQL 18+	PostgreSQL 18+

---

3. Consolidated Single Database Catalog with 8-Schema Segregation

To eliminate distributed transaction complexity, cross-database latency, and connection fragmentation, all business verticals share a single PostgreSQL database catalog named gercia, strictly segregated through eight sovereign PostgreSQL schemas:

flowchart TD
    subgraph DATABASE ["Catalog: gercia (Single Logical Database)"]
        S_CORE["1. core<br>Multi-tenancy, Master Civil Identity, IAM Users, Audit Log, OpenIddict"]
        S_CLINICAL["2. clinical<br>Medical Charts, Health Professionals, Consultations, ICD/CID, EHR"]
        S_PHARMACY["3. pharmacy<br>Galenic Formulas, Orders, Cleanroom Weighing Logs, ANVISA RDC 67"]
        S_DENTAL["4. dental<br>Odontograms, Periodontics, Prosthetics, Dental Compounding, CFO/CRO"]
        S_VET["5. veterinary<br>Species Taxonomy, Breeds, Legal Tutors, CFMV Prescriptions, MAPA/SIPEAGRO"]
        S_OPS["6. operations<br>SaaS Subscriptions, Multi-Tenant Billing, Plans, Invoices"]
        S_AI["7. ai<br>AI Agent Runtimes, Tool Execution Logs, pgvector Embeddings"]
        S_EXT["8. extensions<br>pgcrypto, uuid-ossp, pgvector, pgaudit, pg_stat_statements"]
    end

Architectural Advantages of Schema Segregation

1. Atomic Cross-Domain Transactions: Transactions that alter domain records (e.g., dispensing a compounding medication in pharmacy) and write immutable compliance logs (core.audit_history) commit in a single atomic database transaction without distributed two-phase commits (2PC).
2. Unified Row-Level Security (RLS): Multitenancy policies are enforced uniformly across all tenant-aware schemas using the shared SET LOCAL app.current_tenant_id session variable.
3. Optimized Connection Pooling: Backend hosts maintain a consolidated connection pool targeting gercia, rather than dividing connection quotas across multiple physical databases.

---

4. Multi-Tenancy & Row-Level Security (RLS) Kernel Invariants

Every table containing tenant-scoped data in schemas core, clinical, pharmacy, dental, veterinary, and operations enforces native PostgreSQL Row-Level Security (RLS):

-- Standard RLS policy applied across all tenant-aware tables
ALTER TABLE pharmacy.compounding_orders ENABLE ROW LEVEL SECURITY;
ALTER TABLE pharmacy.compounding_orders FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_policy ON pharmacy.compounding_orders
    FOR ALL
    USING (tenant_id = NULLIF(current_setting('app.current_tenant_id', true), '')::uuid);

Application Interceptor (TenantRowLevelSecurityInterceptor)

Whenever EF Core opens or leases a database connection from the pool, the interceptor executes a local session command before user queries run:

SET LOCAL app.current_tenant_id = '0191c784-0a32-7210-9031-1f9e2b1049c1';

Because SET LOCAL is scoped strictly to the current transaction boundary, connection reuse in the Npgsql connection pool cannot leak tenant contexts between requests.

---

5. Transport Security (TLS 1.3 over Private VPC) & SCRAM-SHA-256 Authentication

Database security enforces end-to-end transport encryption over isolated Google Cloud VPC networking:

• Enforced TLS 1.3 Encryption: All incoming connections must negotiate TLS 1.3 (ssl=on, ssl_mode=ENCRYPTED_ONLY). Unencrypted plaintext TCP connections are dropped immediately at the socket layer.
• Serverless VPC Access Isolation: Cloud SQL endpoints operate strictly on private IPs (10.x.x.x) through the Serverless VPC Access connector (10.8.0.0/28), completely invisible to the public internet.
• SCRAM-SHA-256 Authentication: Database role credentials use 64-character high-entropy secrets hashed via Salted Challenge Response Authentication Mechanism (SCRAM-SHA-256).
• Zero-Downtime Password Rotation: Connection strings are hydrated dynamically from Google Secret Manager (CONNECTION_STRING_*_PRD/STG). Npgsql’s UsePeriodicPasswordProvider in PostgresSecurityHelper refreshes credentials periodically every 15 minutes, ensuring password rotation requires zero service restarts.

---

6. Connection Pool Budgeting & Resource Allocation

Connection limits are enforced programmatically in each host’s bootstrap via NpgsqlConnectionStringBuilder:

Application Host	Execution Environment	Max Pool Size	Timeout	Purpose
Gercia.Server	Google Cloud Run (Serverless)	80	15s	Public REST Minimal APIs, OpenIddict STS, and MCP endpoints
gercia-migrator	Cloud Run Job (Ephemeral)	10	60s	Database pre-deploy migrations executed via Gercia.Server --migrate
DBA / SRE Buffer	Dedicated Admin Sessions	20	60s	Reserved connection headroom for Cloud SQL Query Insights and maintenance

---

7. Database Observability: Query Insights & Audit Logging

Database performance and telemetry are managed natively via Google Cloud services:

• Cloud SQL Query Insights: Continuously monitors query execution latency, CPU load, and lock contentions. Provides detailed query execution plans for clinical, dental, veterinary, and pharmacy queries without manual EXPLAIN ANALYZE scripts.
• Cloud Logging & PGAudit: Captures all administrative DDL modifications and permission grants (cloudsql.enable_pgaudit=on), satisfying CFM Res. 1.821 and ANVISA RDC 67/2007 regulatory compliance audits.

---

8. AI Vector Search & Native pgvector Extension in Google Cloud SQL

The persistence tier natively supports high-dimensional vector search to power Model Context Protocol (MCP) clinical agents, semantic search over electronic health records (EHR), and compounding formulation similarity:

• Official Google Cloud SQL Support: Google Cloud SQL for PostgreSQL provides first-class managed support for the vector (pgvector) extension across all modern PostgreSQL versions, maintained and updated automatically by Google Cloud.
• Isolated Extension Provisioning: In compliance with schema hygiene standards, the extension is installed strictly within the isolated extensions schema:

  CREATE EXTENSION IF NOT EXISTS vector SCHEMA extensions;

• Dedicated AI Schema (ai): All vector columns, embeddings, and similarity indexes are encapsulated within the ai schema (e.g., ai.prescription_embeddings, ai.clinical_guideline_vectors with dimensions tailored to models such as Vertex AI text-embedding-005 or open-source embeddings):

  CREATE TABLE ai.clinical_knowledge_embeddings (
      id UUID PRIMARY KEY DEFAULT extensions.uuid_generate_v7(),
      tenant_id UUID NOT NULL,
      content TEXT NOT NULL,
      embedding extensions.vector(768) NOT NULL,
      created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
  );

• High-Performance Indexing (HNSW & IVFFlat): Google Cloud SQL supports both Hierarchical Navigable Small World (HNSW) and Inverted File Flat (IVFFlat) indexes, enabling sub-millisecond approximate nearest neighbor (ANN) lookups using cosine distance (<=>), L2 distance (<->), or inner product (<#>):

  CREATE INDEX idx_clinical_knowledge_embedding_hnsw
  ON ai.clinical_knowledge_embeddings
  USING hnsw (embedding extensions.vector_cosine_ops);

• Development & Production Parity: Local development utilizes a local PostgreSQL 18 container pre-configured with pgvector, guaranteeing identical SQL execution plans, index behaviors, and EF Core mappings (Pgvector.EntityFrameworkCore) between local workstations and Google Cloud SQL.