Cloud SQL Component

The Cloud SQL component provisions a fully managed PostgreSQL database with automatic backups and optional read replicas. The infrastructure includes both a main application database and an optional Langfuse database.

Architecture

Why Cloud SQL?

Cloud SQL provides:

• PostgreSQL Compatible: Full PostgreSQL 15 support
• Managed Operations: Automated backups and maintenance
• High Availability: Regional deployment with failover
• Private Connectivity: VPC-native with no public IP
• Read Replicas: Scale read workloads (production only)

Resources Created

Resource	Name Pattern	Purpose
Main Instance	serko-northsky-{env}-main-db	Application database
Main Database	serko_northsky	Application data
App User	app	Application connection user
Read Replica	serko-northsky-{env}-main-db-replica	Read scaling (prod only)
Langfuse Instance	serko-northsky-{env}-langfuse-db	Langfuse observability data

Configuration

# Pulumi.dev.yaml
config:
  serko-northsky:mainDbTier: "db-custom-2-8192"
  serko-northsky:mainDbDiskSizeGb: "20"
  serko-northsky:mainDbEnableHA: "false"
  serko-northsky:mainDbEnableReadReplica: "false"
  serko-northsky:langfuseDbEnabled: "true"
  serko-northsky:langfuseDbTier: "db-custom-1-4096"

# Pulumi.prod.yaml
config:
  serko-northsky:mainDbTier: "db-custom-4-16384"
  serko-northsky:mainDbDiskSizeGb: "100"
  serko-northsky:mainDbEnableHA: "true"
  serko-northsky:mainDbEnableReadReplica: "true"

Instance Sizing

Main Database

Environment	Machine Type	vCPUs	RAM	Disk	HA	Read Replica
Development	db-custom-2-8192	2	8 GB	20 GB	No	No
Testing	db-custom-2-8192	2	8 GB	20 GB	No	No
Production	db-custom-4-16384	4	16 GB	100 GB	Yes	Yes

Langfuse Database

Environment	Machine Type	vCPUs	RAM	Disk	HA
Development	db-custom-1-4096	1	4 GB	10 GB	No
Testing	db-custom-1-4096	1	4 GB	10 GB	No
Production	db-custom-2-8192	2	8 GB	50 GB	Yes

Backup Configuration

Automated backups are configured:

Setting	Value
Backup Window	02:00-06:00 UTC
Transaction Log Retention	7 days
Backup Retention	14 days (configurable)
Point-in-Time Recovery	Enabled

Outputs

interface MainDbOutputs {
  instanceName: string;
  privateIpAddress: string;
  connectionName: string;
  databaseName: string;
  userName: string;
  schemaName: string;
  appDatabaseUrl: string;  // Full connection string
  readReplicaIp?: string;
}

interface LangfuseDbOutputs {
  instanceName: string;
  privateIpAddress: string;
  connectionName: string;
  databaseName: string;
  userName: string;
  langfuseDatabaseUrl: string;
}

Connection Details

Connection String Format

postgresql://app:PASSWORD@PRIVATE_IP:5432/serko_northsky?sslmode=require

From GKE Pods

Applications connect using secrets synced from Secret Manager:

env:
- name: DATABASE_URL
  valueFrom:
    secretKeyRef:
      name: backend-config
      key: DATABASE_URL

Direct Connection (Development)

For local development, use Cloud SQL Auth Proxy:

# Install Cloud SQL Auth Proxy
gcloud components install cloud_sql_proxy

# Start the proxy
cloud_sql_proxy -instances=PROJECT_ID:REGION:INSTANCE_NAME=tcp:5432

# Connect via localhost
psql "postgresql://app:PASSWORD@localhost:5432/serko_northsky"

Or get the private IP directly:

gcloud sql instances describe serko-northsky-dev-main-db \
  --project=serko-northsky-dev \
  --format='value(ipAddresses[0].ipAddress)'

Security

• Private IP Only: No public IP address assigned
• VPC Connectivity: Accessible only within the VPC via private service access
• Auto-Generated Password: Secure random password stored in Pulumi state
• Encrypted Storage: Data encrypted at rest with Google-managed keys
• SSL Required: Connections require SSL/TLS
• IAM Integration: Service account access for GKE workloads

Network Requirements

Cloud SQL requires the Private Service Access connection to be established before instances can be created. This dependency is handled automatically in the infrastructure code:

const mainDb = new MainDB(
  'main-db',
  {
    projectId: envConfig.projectId,
    region: envConfig.region,
    environment: envConfig.environment,
    vpcSelfLink: network.vpc.selfLink,
    privateVpcConnection: network.privateVpcConnection,
    tier: envConfig.mainDbTier,
    diskSizeGb: envConfig.mainDbDiskSizeGb,
    enableHA: envConfig.mainDbEnableHA,
    enableReadReplica: envConfig.mainDbEnableReadReplica,
  },
  { dependsOn: [network] }
);

Database Schema

The main database uses a custom schema serko_northsky for application tables:

-- Schema is created automatically
CREATE SCHEMA IF NOT EXISTS serko_northsky;

-- App user has full access to the schema
GRANT ALL ON SCHEMA serko_northsky TO app;

Monitoring

View database metrics in the GCP Console:

# Open Cloud SQL in console
gcloud sql instances describe serko-northsky-dev-main-db \
  --project=serko-northsky-dev \
  --format='value(selfLink)' | xargs open

Key metrics to monitor:

• CPU utilization
• Memory utilization
• Disk utilization
• Active connections
• Query latency