Database Resilience Documentation

This document describes the database resilience mechanisms implemented in the Petunia codebase.

Circuit Breaker

Location: lib/services/circuit-breaker/ (adapters/database-adapter.ts)

Overview

The database circuit breaker protects against cascading failures when the database is unavailable or slow. It implements the standard circuit breaker pattern with three states:

• CLOSED: Normal operation. All requests pass through.
• OPEN: Database is failing. Requests fail fast without hitting the database.
• HALF_OPEN: Testing if database recovered. Limited requests allowed.

Configuration

const DEFAULT_CONFIG = {
  failureThreshold: 5,      // Failures before opening circuit
  timeout: 30000,           // 30 seconds before half-open attempt
  monitoringPeriod: 300000, // 5 minutes for failure counting window
  successThreshold: 3,      // Successes needed to close circuit
  operationTimeout: 10000   // 10 second timeout per operation
};

Usage

import { withDatabaseCircuitBreaker, DatabaseOperationType } from '@/lib/services/circuit-breaker';

// Wrap database operations
const result = await withDatabaseCircuitBreaker(
  () => prisma.user.findUnique({ where: { id } }),
  'user.findUnique',
  DatabaseOperationType.READ
);

Features

• Automatic operation timeout (10s default)
• Telemetry integration for error tracking
• Manual controls (forceOpen(), reset())
• Status monitoring (getStatus(), getFailureHistory())

Connection Pool

Location: lib/connections/connection-pool.ts

Overview

Generic connection pooling for API connections with:

• Configurable min/max connections (default: 2-10)
• Idle timeout with automatic cleanup (30s default)
• Health checks at configurable intervals (60s default)
• Waiting queue for connection exhaustion

Configuration

const options: ConnectionPoolOptions = {
  min: 2,
  max: 10,
  idleTimeoutMillis: 30000,
  acquireTimeoutMillis: 5000,
  createRetries: 3,
  healthCheckIntervalMillis: 60000
};

Row-Level Security (RLS)

Migrations:

• 20251218_complete_rls_coverage - Core tables
• 20251218_complete_rls_coverage_part2 - Extended tables

Coverage

RLS policies now cover 50+ tables with tenant data. Policy pattern:

• SELECT: User has access via junction tables (UserCompany, UserClient, UserPortalAccess) OR service_role
• INSERT/UPDATE/DELETE: service_role only

Tenant Isolation Verification

The codebase uses Supabase's createClient() which automatically respects RLS. Routes using this pattern are protected at the database level:

// Create Supabase client (respects RLS automatically)
const supabase = await createClient();

Chaos Testing

Location: tests/chaos/resilience.chaos.test.ts

Chaos tests cover:

1. Database connection failures - Retry with exponential backoff
2. Query timeouts - 1s timeout on slow queries
3. Connection pool exhaustion - Graceful degradation

Service Mesh Circuit Breaker

Location: lib/services/mesh/CircuitBreaker.ts

Additional circuit breaker for service-to-service communication with:

• Configurable thresholds
• Automatic recovery
• Metrics collection

Monitoring

Circuit Breaker Status

const status = databaseCircuitBreaker.getStatus();
// Returns: { state, failureCount, successCount, recentFailures, lastFailureTime, config }

API Endpoint

GET /api/auth/circuit-breaker-status - Returns current circuit breaker state

Best Practices

1. Use circuit breaker for external calls - Wrap database and API calls
2. Set appropriate timeouts - 10s for database, adjust based on operation
3. Monitor failure rates - Track recentFailures in dashboards
4. Test recovery - Verify HALF_OPEN state transitions work
5. Log state changes - All state transitions are logged

Related Documentation

• /docs/DATABASE_MIGRATIONS.md - Migration procedures
• /docs/features/DATABASE_SCHEMA.md - Schema documentation
• /TESTING.md - Test structure including chaos tests