ADR-112: Ralph Wiggum Database Architecture

Status

Proposed

Context

Problem Statement

ADRs 108, 109, 110, and 111 were drafted with FoundationDB references for state persistence. However, ADR-002 (PostgreSQL as Primary Database) established PostgreSQL as the accepted cloud database choice, explicitly rejecting FoundationDB due to:

"Operational complexity... No managed service... must run our own cluster"

This ADR reconciles the database architecture for Ralph Wiggum autonomous agent implementation, ensuring alignment with established architectural decisions.

Historical Context

1. FoundationDB Legacy: CODITECT v5 used FoundationDB for session storage and real-time collaboration
2. ADR-002 Decision (2025-11-17): PostgreSQL selected for cloud, rejecting FoundationDB
3. ADR-089 (2026-01-19): Two-database architecture for local (SQLite) vs cloud (PostgreSQL)
4. ADRs 108-111 (2026-01-24): Ralph Wiggum ADRs drafted with FoundationDB references

Gap Identified

The Ralph Wiggum ADRs need to align with the established two-tier database architecture:

• Local: SQLite for offline-first operation
• Cloud: PostgreSQL with RLS for multi-tenant isolation

Decision

Adopt a two-tier database architecture for Ralph Wiggum implementation that aligns with ADR-002 and ADR-089:

1. Architecture Overview

┌─────────────────────────────────────────────────────────────────────────┐
│                        DEVELOPER MACHINE                                 │
│  ┌─────────────────────────────────────────────────────────────────────┐│
│  │                         LOCAL TIER                                   ││
│  │  ┌──────────────────┐  ┌──────────────────┐  ┌──────────────────┐  ││
│  │  │   context.db     │  │   platform.db    │  │   projects.db    │  ││
│  │  │   (Customer)     │  │   (Read-Only)    │  │   (Project Data) │  ││
│  │  │                  │  │                  │  │                  │  ││
│  │  │ • checkpoints    │  │ • components     │  │ • code_chunks    │  ││
│  │  │ • token_records  │  │ • capabilities   │  │ • embeddings     │  ││
│  │  │ • health_events  │  │ • standards      │  │ • decisions      │  ││
│  │  │ • budgets        │  │ • health_scores  │  │                  │  ││
│  │  │ • sessions       │  │                  │  │                  │  ││
│  │  └────────┬─────────┘  └──────────────────┘  └──────────────────┘  ││
│  │           │                                                          ││
│  │           │ Sync Queue (offline-capable)                            ││
│  └───────────┼──────────────────────────────────────────────────────────┘│
└──────────────┼──────────────────────────────────────────────────────────┘
               │
               │ HTTPS (cursor-based polling)
               │ ADR-053 sync protocol
               ▼
┌─────────────────────────────────────────────────────────────────────────┐
│                          CLOUD TIER                                      │
│  ┌─────────────────────────────────────────────────────────────────────┐│
│  │                     PostgreSQL (Cloud SQL)                           ││
│  │                                                                      ││
│  │  ┌───────────────────────────────────────────────────────────────┐  ││
│  │  │                   Row-Level Security (RLS)                     │  ││
│  │  │                                                                │  ││
│  │  │  Organization A    │  Organization B    │  Organization C     │  ││
│  │  │  ┌─────────────┐   │  ┌─────────────┐   │  ┌─────────────┐   │  ││
│  │  │  │ checkpoints │   │  │ checkpoints │   │  │ checkpoints │   │  ││
│  │  │  │ tokens      │   │  │ tokens      │   │  │ tokens      │   │  ││
│  │  │  │ health      │   │  │ health      │   │  │ health      │   │  ││
│  │  │  │ budgets     │   │  │ budgets     │   │  │ budgets     │   │  ││
│  │  │  └─────────────┘   │  └─────────────┘   │  └─────────────┘   │  ││
│  │  │                    │                    │                    │  ││
│  │  └───────────────────────────────────────────────────────────────┘  ││
│  │                                                                      ││
│  │  Features: ACID, JSONB, Full-Text Search, Managed Service           ││
│  └─────────────────────────────────────────────────────────────────────┘│
└─────────────────────────────────────────────────────────────────────────┘

2. Why PostgreSQL Over FoundationDB

Factor	FoundationDB	PostgreSQL	Winner
Managed Service	None	Cloud SQL, RDS, Azure	PostgreSQL
Operational Complexity	High (cluster mgmt)	Low (managed)	PostgreSQL
Row-Level Security	Manual (key prefix)	Native RLS	PostgreSQL
SQL Queries	None (key-value)	Full SQL + JSONB	PostgreSQL
Multi-Tenant	Manual isolation	Automatic via RLS	PostgreSQL
Cost	Self-hosted only	$200/month managed	PostgreSQL
ACID	Yes	Yes	Tie
Horizontal Scale	Better	Vertical + Citus	FoundationDB

Decision: PostgreSQL wins on operational simplicity and managed service availability. Horizontal scaling is not a requirement at current scale.

3. Data Flow by Feature

Checkpoint Protocol (ADR-108)

Agent creates checkpoint
        │
        ▼
┌───────────────────┐
│ Write to SQLite   │  ← Immediate, offline-capable
│ (context.db)      │
└────────┬──────────┘
         │
         │ Background sync (async)
         ▼
┌───────────────────┐
│ Sync to PostgreSQL│  ← Multi-tenant, RLS protected
│ (cloud)           │
└───────────────────┘

Token Economics (ADR-111)

API call completes
        │
        ▼
┌───────────────────┐
│ Record locally    │  ← Zero latency impact on agent
│ (context.db)      │
│ • token counts    │
│ • cost calculation│
│ • budget check    │
└────────┬──────────┘
         │
         │ Batch sync (every 60s or 100 records)
         ▼
┌───────────────────┐
│ Aggregate in cloud│  ← Organization-level reporting
│ (PostgreSQL)      │
│ • materialized    │
│   views           │
│ • cross-project   │
│   analytics       │
└───────────────────┘

Health Monitoring (ADR-110)

Health event occurs
        │
        ▼
┌───────────────────┐
│ Log locally       │  ← Immediate detection
│ (context.db)      │
│ • heartbeat       │
│ • state change    │
│ • intervention    │
└────────┬──────────┘
         │
         │ Real-time sync (critical events)
         ▼
┌───────────────────┐
│ Alert cloud       │  ← PagerDuty, Slack integration
│ (PostgreSQL +     │
│  webhooks)        │
└───────────────────┘

4. Sync Protocol

Follows ADR-053 (Cloud Context Sync Architecture):

sync_protocol:
  method: cursor_based_polling

  local_to_cloud:
    trigger:
      - batch_size: 100 records
      - time_interval: 60 seconds
      - critical_event: immediate
    conflict_resolution: last_write_wins
    offline_queue: true
    max_queue_age: 72 hours

  cloud_to_local:
    trigger:
      - polling_interval: 30 seconds
      - websocket: real-time (optional)
    merge_strategy: cloud_authoritative

  endpoints:
    checkpoints: /api/v1/context/checkpoints
    tokens: /api/v1/context/token-records
    health: /api/v1/context/health-events
    budgets: /api/v1/context/budgets

5. Multi-Tenant Requirements

Requirement	Implementation
Multi-user	PostgreSQL users table with auth
Multi-team	organization_members with roles
Multi-project	projects with organization_id FK
Multi-tenant	RLS policies on all tables
Data isolation	Automatic via RLS, zero app code
Compliance	SOC2, GDPR, HIPAA via audit_log

6. Schema Updates to ADRs

This ADR supersedes database-specific sections in:

ADR	Section	Change
ADR-108	"FoundationDB Key Structure"	→ "Database Schema" (SQLite + PostgreSQL)
ADR-111	"FoundationDB Key Structure"	→ "Database Schema" (SQLite + PostgreSQL)

Consequences

Positive

1. Alignment - All Ralph Wiggum ADRs now align with ADR-002 and ADR-089
2. Managed Service - No FoundationDB cluster to operate
3. Multi-Tenant Ready - RLS provides automatic tenant isolation
4. Offline-First - SQLite ensures local operation without cloud
5. Cost Effective - ~$200/month vs self-hosted FoundationDB cluster
6. Familiar Stack - PostgreSQL expertise is more common

Negative

1. Horizontal Scaling - PostgreSQL vertical scaling has limits (mitigated by Citus if needed)
2. Sync Complexity - Two-tier requires sync protocol (already solved by ADR-053)
3. Eventual Consistency - Local-to-cloud sync is not immediate (acceptable for non-critical data)

Risks

Risk	Likelihood	Impact	Mitigation
Sync lag	Medium	Stale cloud data	Real-time sync for critical events
Conflict resolution	Low	Data inconsistency	Last-write-wins with conflict log
PostgreSQL scaling	Low	Performance	Citus extension, read replicas

Implementation

Phase 1: Schema Migration (Week 1)

• Update ADR-108 database schema (DONE in this session)
• Update ADR-111 database schema (DONE in this session)
• Create migration scripts for context.db

Phase 2: Sync Integration (Week 2)

• Extend ADR-053 sync client for checkpoint/token/health tables
• Add batch sync for token records
• Add real-time sync for critical health events

Phase 3: Cloud Deployment (Week 3)

• Deploy PostgreSQL schema with RLS
• Configure materialized views for aggregations
• Set up alerting webhooks

Phase 4: Testing (Week 4)

• Multi-tenant isolation tests
• Offline operation tests
• Sync conflict resolution tests

Glossary

Term	Definition
SQLite	Lightweight, file-based SQL database used for local offline storage
PostgreSQL	Enterprise-grade relational database used for cloud multi-tenant storage
RLS	Row-Level Security - PostgreSQL feature that automatically filters query results by tenant
Cloud SQL	Google Cloud Platform's managed PostgreSQL service
Two-Tier Architecture	Database design with local (SQLite) and cloud (PostgreSQL) layers
Cursor-based Polling	Sync method using timestamp cursors to fetch incremental changes
Last-Write-Wins	Conflict resolution strategy where most recent update takes precedence
Offline Queue	Local buffer storing changes when cloud connection is unavailable
Multi-Tenant	Architecture serving multiple organizations from shared infrastructure
Tenant Isolation	Ensuring one organization cannot access another's data
ACID	Atomicity, Consistency, Isolation, Durability - database transaction guarantees
FoundationDB	Distributed key-value database (rejected for cloud due to operational complexity)
Managed Service	Cloud-hosted database where provider handles operations and maintenance
context.db	Local SQLite database storing customer session and decision data
platform.db	Local SQLite database storing CODITECT framework components (read-only)

---

References

• ADR-002: PostgreSQL as Primary Database
• ADR-053: Cloud Context Sync Architecture
• ADR-089: Two-Database Architecture
• Ralph Wiggum Gap Analysis

---

ADR-112 | Created: 2026-01-25 | Status: Proposed