Documentation

DATABASE_RESILIENCE

docs/DATABASE_RESILIENCE.md

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
  • /docs/DATABASE_MIGRATIONS.md - Migration procedures
  • /docs/features/DATABASE_SCHEMA.md - Schema documentation
  • /TESTING.md - Test structure including chaos tests