ADR-002: Database Choice - PostgreSQL Implementation (Part 2 - Technical)

Document Specification Block

Document: ADR-002-database-choice-part2-technical
Version: 1.0.0
Purpose: Technical implementation blueprint for PostgreSQL deployment and optimization
Audience: AI agents, developers, database administrators, DevOps engineers
Date Created: 2025-10-03
Date Updated: 2025-10-03
Status: PROPOSED
Type: DUAL-PART (Part 2 of 2)
Related: ADR-002-database-choice-part1-human
Score Required: 100% (40/40 points)
Implementation Ready: TRUE

Infrastructure Configuration

Terraform Deployment

# infrastructure/database/main.tf
resource "google_sql_database_instance" "primary" {
  name             = "qr-generator-primary"
  database_version = "POSTGRES_15"
  region          = "us-central1"
  
  settings {
    tier              = "db-n1-standard-2"
    availability_type = "REGIONAL"  # HA enabled
    disk_size         = 100
    disk_type         = "PD_SSD"
    
    backup_configuration {
      enabled                        = true
      start_time                    = "03:00"
      point_in_time_recovery_enabled = true
      transaction_log_retention_days = 7
    }
    
    ip_configuration {
      ipv4_enabled    = false  # VPC only
      private_network = google_compute_network.vpc.id
    }
    
    database_flags {
      name  = "max_connections"
      value = "500"
    }
    
    database_flags {
      name  = "shared_buffers"
      value = "256MB"
    }
    
    database_flags {
      name  = "effective_cache_size"
      value = "1GB"
    }
    
    database_flags {
      name  = "work_mem"
      value = "4MB"
    }
    
    database_flags {
      name  = "maintenance_work_mem"
      value = "64MB"
    }
    
    database_flags {
      name  = "random_page_cost"
      value = "1.1"  # SSD optimization
    }
  }
}

# Read replicas
resource "google_sql_database_instance" "replica_eu" {
  name                 = "qr-generator-replica-eu"
  master_instance_name = google_sql_database_instance.primary.name
  region              = "europe-west1"
  database_version    = "POSTGRES_15"
  
  replica_configuration {
    failover_target = false
  }
  
  settings {
    tier      = "db-n1-standard-1"
    disk_size = 100
  }
}

Connection Pooling

# k8s/pgbouncer/deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: pgbouncer
  namespace: qr-generator
spec:
  replicas: 3
  selector:
    matchLabels:
      app: pgbouncer
  template:
    metadata:
      labels:
        app: pgbouncer
    spec:
      containers:
      - name: pgbouncer
        image: pgbouncer/pgbouncer:1.21.0
        ports:
        - containerPort: 5432
        env:
        - name: DATABASES_HOST
          valueFrom:
            secretKeyRef:
              name: database-config
              key: host
        - name: DATABASES_PORT
          value: "5432"
        - name: DATABASES_USER
          valueFrom:
            secretKeyRef:
              name: database-config
              key: user
        - name: POOL_MODE
          value: "transaction"
        - name: MAX_CLIENT_CONN
          value: "1000"
        - name: DEFAULT_POOL_SIZE
          value: "25"
        volumeMounts:
        - name: pgbouncer-config
          mountPath: /etc/pgbouncer

Database Schema

Optimized Schema Design

-- migrations/001_optimized_schema.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
CREATE EXTENSION IF NOT EXISTS "pg_trgm";  -- For text search
CREATE EXTENSION IF NOT EXISTS "btree_gin"; -- For composite indexes

-- Users table with optimizations
CREATE TABLE users (
    user_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    email_normalized VARCHAR(255) GENERATED ALWAYS AS (LOWER(email)) STORED,
    password_hash VARCHAR(255) NOT NULL,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    email_verified BOOLEAN DEFAULT FALSE,
    last_login TIMESTAMPTZ,
    marketing_consent BOOLEAN DEFAULT FALSE,
    marketing_consent_at TIMESTAMPTZ,
    account_status VARCHAR(20) DEFAULT 'active',
    metadata JSONB DEFAULT '{}'::jsonb
);

-- Indexes for users
CREATE INDEX idx_users_email_normalized ON users(email_normalized);
CREATE INDEX idx_users_created_at ON users(created_at);
CREATE INDEX idx_users_status_created ON users(account_status, created_at);
CREATE INDEX idx_users_metadata_gin ON users USING gin(metadata);

-- Contact cards with partitioning ready
CREATE TABLE contact_cards (
    card_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    user_id UUID NOT NULL,
    slug VARCHAR(100) UNIQUE NOT NULL,
    full_name VARCHAR(255) NOT NULL,
    full_name_search tsvector GENERATED ALWAYS AS (to_tsvector('english', full_name)) STORED,
    organization VARCHAR(255),
    title VARCHAR(255),
    email VARCHAR(255) NOT NULL,
    phone VARCHAR(50),
    website VARCHAR(500),
    qr_data_url TEXT,
    qr_cloud_url VARCHAR(500),
    qr_error_correction VARCHAR(10) DEFAULT 'M',
    qr_size INTEGER DEFAULT 512,
    created_at TIMESTAMPTZ DEFAULT NOW(),
    updated_at TIMESTAMPTZ DEFAULT NOW(),
    view_count INTEGER DEFAULT 0,
    scan_count INTEGER DEFAULT 0,
    is_active BOOLEAN DEFAULT TRUE,
    metadata JSONB DEFAULT '{}'::jsonb,
    CONSTRAINT fk_user FOREIGN KEY (user_id) 
        REFERENCES users(user_id) ON DELETE CASCADE
) PARTITION BY RANGE (created_at);

-- Create partitions
CREATE TABLE contact_cards_2025_q1 PARTITION OF contact_cards
    FOR VALUES FROM ('2025-01-01') TO ('2025-04-01');

-- Indexes for cards
CREATE INDEX idx_cards_user_id ON contact_cards(user_id);
CREATE INDEX idx_cards_slug ON contact_cards(slug) WHERE is_active = true;
CREATE INDEX idx_cards_search ON contact_cards USING gin(full_name_search);
CREATE INDEX idx_cards_active_created ON contact_cards(is_active, created_at DESC);

-- Viral tracking with optimized queries
CREATE TABLE viral_invitations (
    invitation_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    sender_user_id UUID NOT NULL,
    recipient_email VARCHAR(255) NOT NULL,
    recipient_email_normalized VARCHAR(255) GENERATED ALWAYS AS (LOWER(recipient_email)) STORED,
    card_id UUID NOT NULL,
    channel VARCHAR(50) NOT NULL,
    sent_at TIMESTAMPTZ DEFAULT NOW(),
    opened_at TIMESTAMPTZ,
    clicked_at TIMESTAMPTZ,
    converted_at TIMESTAMPTZ,
    conversion_user_id UUID,
    tracking_data JSONB DEFAULT '{}'::jsonb,
    CONSTRAINT fk_sender FOREIGN KEY (sender_user_id) 
        REFERENCES users(user_id) ON DELETE CASCADE,
    CONSTRAINT fk_card FOREIGN KEY (card_id) 
        REFERENCES contact_cards(card_id) ON DELETE CASCADE
);

-- Viral indexes
CREATE INDEX idx_viral_sender_sent ON viral_invitations(sender_user_id, sent_at DESC);
CREATE INDEX idx_viral_recipient ON viral_invitations(recipient_email_normalized);
CREATE INDEX idx_viral_converted ON viral_invitations(converted_at) 
    WHERE converted_at IS NOT NULL;
CREATE INDEX idx_viral_channel_sent ON viral_invitations(channel, sent_at DESC);

-- Materialized view for K-factor calculation
CREATE MATERIALIZED VIEW viral_metrics AS
SELECT 
    DATE_TRUNC('day', u.created_at) as cohort_day,
    COUNT(DISTINCT u.user_id) as cohort_size,
    COUNT(DISTINCT vi.invitation_id) as invitations_sent,
    COUNT(DISTINCT CASE WHEN vi.converted_at IS NOT NULL THEN vi.recipient_email END) as conversions,
    COALESCE(
        COUNT(DISTINCT CASE WHEN vi.converted_at IS NOT NULL THEN vi.recipient_email END)::FLOAT / 
        NULLIF(COUNT(DISTINCT u.user_id), 0), 
        0
    ) as k_factor
FROM users u
LEFT JOIN viral_invitations vi ON u.user_id = vi.sender_user_id
WHERE u.created_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE_TRUNC('day', u.created_at);

CREATE UNIQUE INDEX idx_viral_metrics_day ON viral_metrics(cohort_day);

Query Optimization

-- Optimized queries for common operations

-- 1. Get card with user (using covering index)
CREATE INDEX idx_cards_covering ON contact_cards(slug) 
INCLUDE (card_id, user_id, full_name, email, qr_cloud_url) 
WHERE is_active = true;

-- Query uses index-only scan
EXPLAIN (ANALYZE, BUFFERS) 
SELECT card_id, user_id, full_name, email, qr_cloud_url
FROM contact_cards
WHERE slug = 'john-doe' AND is_active = true;

-- 2. K-factor calculation (using materialized view)
REFRESH MATERIALIZED VIEW CONCURRENTLY viral_metrics;

SELECT cohort_day, k_factor 
FROM viral_metrics 
WHERE cohort_day >= NOW() - INTERVAL '7 days'
ORDER BY cohort_day DESC;

-- 3. Viral invitation tracking (optimized for write)
WITH invitation AS (
    INSERT INTO viral_invitations 
    (sender_user_id, recipient_email, card_id, channel)
    VALUES ($1, $2, $3, $4)
    RETURNING invitation_id
)
INSERT INTO viral_event_log (event_type, invitation_id, timestamp)
SELECT 'invitation_created', invitation_id, NOW()
FROM invitation;

Connection Pool Configuration

// src/db/pool.rs
use deadpool_postgres::{Config, ManagerConfig, Pool, RecyclingMethod};
use tokio_postgres::NoTls;

pub fn create_pool() -> Pool {
    let mut cfg = Config::new();
    cfg.host = Some("pgbouncer-service".to_string());
    cfg.port = Some(5432);
    cfg.dbname = Some("qr_generator".to_string());
    cfg.user = Some("app_user".to_string());
    cfg.password = Some(env::var("DB_PASSWORD").unwrap());
    
    cfg.manager = Some(ManagerConfig {
        recycling_method: RecyclingMethod::Fast,
    });
    
    cfg.pool = Some(deadpool_postgres::PoolConfig {
        max_size: 32,
        timeouts: deadpool_postgres::Timeouts {
            wait: Some(Duration::from_secs(5)),
            create: Some(Duration::from_secs(5)),
            recycle: Some(Duration::from_secs(5)),
        },
        ..Default::default()
    });
    
    cfg.create_pool(None, NoTls).unwrap()
}

Performance Monitoring

-- monitoring/performance_views.sql
CREATE VIEW slow_queries AS
SELECT 
    query,
    calls,
    total_exec_time,
    mean_exec_time,
    stddev_exec_time,
    rows
FROM pg_stat_statements
WHERE mean_exec_time > 100  -- queries over 100ms
ORDER BY mean_exec_time DESC;

CREATE VIEW table_bloat AS
SELECT
    schemaname,
    tablename,
    pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) AS size,
    CASE WHEN pg_relation_size(schemaname||'.'||tablename) > 0
        THEN (100 * (pg_relation_size(schemaname||'.'||tablename) - 
              pg_relation_size(schemaname||'.'||tablename, 'main')) / 
              pg_relation_size(schemaname||'.'||tablename))::numeric
        ELSE 0
    END AS bloat_ratio
FROM pg_tables
WHERE schemaname NOT IN ('pg_catalog', 'information_schema')
ORDER BY pg_relation_size(schemaname||'.'||tablename) DESC;

Backup and Recovery

#!/bin/bash
# scripts/backup-restore.sh

# Automated backup verification
verify_backup() {
    local backup_file=$1
    
    # Test restore to temporary database
    createdb test_restore
    pg_restore -d test_restore $backup_file
    
    # Verify row counts
    PROD_USERS=$(psql -d qr_generator -t -c "SELECT COUNT(*) FROM users")
    TEST_USERS=$(psql -d test_restore -t -c "SELECT COUNT(*) FROM users")
    
    if [ "$PROD_USERS" -eq "$TEST_USERS" ]; then
        echo "Backup verified: $backup_file"
        dropdb test_restore
        return 0
    else
        echo "Backup verification failed!"
        return 1
    fi
}

# Point-in-time recovery
restore_to_timestamp() {
    local timestamp=$1
    gcloud sql backups restore \
        --backup-id=$(gcloud sql backups list --instance=qr-generator-primary \
            --filter="windowStartTime<'$timestamp'" --limit=1 --format="value(id)") \
        --instance=qr-generator-recovery
}

Migration Strategy

# migrations/config.yaml
migration_phases:
  phase1_baseline:
    - 001_initial_schema.sql
    - 002_add_indexes.sql
    - 003_create_partitions.sql
    
  phase2_optimization:
    - 004_add_materialized_views.sql
    - 005_add_covering_indexes.sql
    
  phase3_sharding_prep:
    - 006_add_shard_key.sql
    - 007_create_shard_functions.sql

Testing Implementation

// tests/db_performance_test.rs
#[tokio::test]
async fn test_concurrent_load() {
    let pool = create_pool().await;
    let start = Instant::now();
    
    let handles: Vec<_> = (0..100)
        .map(|i| {
            let pool = pool.clone();
            tokio::spawn(async move {
                let client = pool.get().await.unwrap();
                let row = client.query_one(
                    "INSERT INTO users (email) VALUES ($1) RETURNING user_id",
                    &[&format!("user{}@test.com", i)]
                ).await.unwrap();
                row.get::<_, Uuid>(0)
            })
        })
        .collect();
    
    let results = futures::future::join_all(handles).await;
    assert_eq!(results.len(), 100);
    assert!(start.elapsed() < Duration::from_secs(5));
}

Summary

This implementation provides:

Automated deployment via Terraform
Optimized schema with partitioning and indexes
Connection pooling supporting 1000+ concurrent connections
Performance monitoring and slow query detection
Automated backups with verification
Clear migration path for future scaling

The configuration is production-ready and optimized for agent-based development with comprehensive monitoring and testing.

Document Specification Block​

Infrastructure Configuration​

Terraform Deployment​

Connection Pooling​

Database Schema​

Optimized Schema Design​

Query Optimization​

Connection Pool Configuration​

Performance Monitoring​

Backup and Recovery​

Migration Strategy​

Testing Implementation​

Summary​

Document Specification Block

Infrastructure Configuration

Terraform Deployment

Connection Pooling

Database Schema

Optimized Schema Design

Query Optimization

Connection Pool Configuration

Performance Monitoring

Backup and Recovery

Migration Strategy

Testing Implementation

Summary