Implement Database Connection Retry Logic with Exponential Backoff

[grantfox-oss]

Type: Bug - Critical | Priority: CRITICAL | Effort: 3 hours
Component: backend/src/lib/database.ts, backend/src/index.ts

Problem:
Database connection failures cause immediate application crashes with no retry mechanism. This is a critical production issue where:

• Temporary network issues crash the entire application
• Database maintenance windows cause unnecessary downtime
• Connection pool exhaustion leads to cascading failures
• No graceful degradation when database is temporarily unavailable
• Users experience complete service outages for transient issues

Current Behavior:

// CURRENT (crashes immediately on connection failure)
const prisma = new PrismaClient();
await prisma.$connect(); // Throws error, crashes app

Real-world Impact:

• Production incidents: Average 3-5 per month
• Mean Time to Recovery (MTTR): 10-15 minutes
• User-facing downtime: 100% during database connectivity issues
• Lost transactions during brief network interruptions

Acceptance Criteria:

• Implement exponential backoff for database connections
  • Initial retry: 1 second
  • Subsequent retries: 2s, 4s, 8s, 16s, 32s
  • Maximum 6 retry attempts
• Add circuit breaker pattern to prevent overwhelming database
• Create health check endpoint that reflects database status
• Implement graceful degradation:
  • Read-only mode when database is degraded
  • Queue writes for retry when connection restored
  • Return cached data when possible
• Add connection pool monitoring and alerts
• Log connection attempts with correlation IDs
• Add metrics for connection success/failure rates
• Create runbook for database connection issues
• Test scenarios:
  • Database restart during operation
  • Network partition for 30 seconds
  • Connection pool exhaustion (100+ concurrent connections)
  • Database maintenance window simulation
  • Degraded database performance (slow queries)
• Performance requirements:
  • Recovery within 60 seconds of database availability
  • No data loss during transient failures
  • Queued operations replay in order
  • Circuit breaker opens after 5 consecutive failures
  • Circuit breaker half-open state after 30 seconds

Implementation Strategy:

// AFTER (with retry logic)
import retry from 'async-retry';

const prisma = new PrismaClient({
  log: ['error', 'warn'],
  errorFormat: 'minimal'
});

async function connectWithRetry() {
  await retry(async (bail, attempt) => {
    try {
      await prisma.$connect();
      logger.info(`Database connected on attempt ${attempt}`);
    } catch (error) {
      logger.warn(`Database connection attempt ${attempt} failed:`, error.message);
      
      // Don't retry on authentication errors
      if (error.code === 'P1001' || error.code === 'P1002') {
        bail(error);
        return;
      }
      
      // Throw to trigger retry
      throw error;
    }
  }, {
    retries: 6,
    factor: 2,
    minTimeout: 1000,
    maxTimeout: 32000,
    randomize: true,
    onRetry: (error, attempt) => {
      logger.warn(`Retry attempt ${attempt} for database connection`);
    }
  });
}

// Circuit breaker implementation
class DatabaseCircuitBreaker {
  private state: 'CLOSED' | 'OPEN' | 'HALF_OPEN' = 'CLOSED';
  private failureCount = 0;
  private lastFailureTime?: Date;
  
  async execute<T>(operation: () => Promise<T>): Promise<T> {
    if (this.state === 'OPEN') {
      const timeSinceFailure = Date.now() - this.lastFailureTime!.getTime();
      if (timeSinceFailure > 30000) { // 30 seconds
        this.state = 'HALF_OPEN';
      } else {
        throw new Error('Circuit breaker is OPEN');
      }
    }
    
    try {
      const result = await operation();
      this.onSuccess();
      return result;
    } catch (error) {
      this.onFailure();
      throw error;
    }
  }
  
  private onSuccess() {
    this.failureCount = 0;
    this.state = 'CLOSED';
  }
  
  private onFailure() {
    this.failureCount++;
    this.lastFailureTime = new Date();
    
    if (this.failureCount >= 5) {
      this.state = 'OPEN';
      logger.error('Circuit breaker opened due to consecutive failures');
    }
  }
}

Graceful Degradation Strategy:

// Health check endpoint
app.get('/health', async (req, res) => {
  try {
    await circuitBreaker.execute(async () => {
      await prisma.$queryRaw`SELECT 1`;
    });
    res.json({ status: 'healthy', database: 'connected' });
  } catch (error) {
    res.status(503).json({ 
      status: 'degraded', 
      database: 'disconnected',
      mode: 'read-only' 
    });
  }
});

// Read-only mode handler
async function handleDatabaseOperation<T>(
  operation: () => Promise<T>,
  fallbackCache?: () => T
): Promise<T> {
  try {
    return await circuitBreaker.execute(operation);
  } catch (error) {
    if (fallbackCache && circuitBreaker.state === 'OPEN') {
      logger.warn('Using cached data due to database unavailability');
      return fallbackCache();
    }
    throw error;
  }
}

Monitoring & Alerting:

• Database connection success rate metric (target: > 99.9%)
• Connection pool utilization metric
• Circuit breaker state changes logged
• Alert when circuit breaker opens
• Alert when connection retries exceed 3 attempts
• Dashboard showing connection health over time

Related Files:

• backend/src/lib/database.ts (main implementation)
• backend/src/lib/circuit-breaker.ts (create)
• backend/src/middleware/health-check.ts (create)
• backend/src/index.ts (initialization)
• backend/RUNBOOK.md (operational procedures)

Success Criteria:

• Zero application crashes due to transient database issues
• Sub-60-second recovery time after database becomes available
• All write operations preserved and replayed in order
• Read operations served from cache during outages
• Comprehensive monitoring and alerting in place

[JuanVP2]

I have extensive experience working with Prisma and building robust backend systems in Node.js. I understand the criticality of the database connection issues described, specifically the need for a resilient circuit breaker pattern and exponential backoff strategy to prevent cascading failures.

I am ready to implement the requested health checks, graceful degradation, and connection monitoring to ensure the system meets the >99.9% success rate target. I can deliver these changes within the 3-hour estimate, ensuring zero crashes and proper error handling. Looking forward to contributing to StellarStream.

[Psalmuel01]

I'd like to take this issue. I'll implement resilient database connection retry logic using exponential backoff with configurable retry limits and delays, ensuring transient connection failures are handled gracefully without affecting application stability. I'll include comprehensive tests for retry behavior and edge cases, while keeping the implementation aligned with the project's existing architecture and conventions.

[Georgebingi]

Ill like to work on this issue

[grantfox-oss]

🦊 GrantFox — @Georgebingi has been assigned to this issue as part of the Official Campaign campaign!

Next steps:

1. Open a Pull Request referencing this issue (e.g., Closes #1230)
2. Your PR will be reviewed by the StellarStream-HQ maintainers

Good luck! Track your progress on GrantFox.

[grantfox-oss]

🎉 This issue has been marked as completed on GrantFox as part of the Official Campaign campaign!

@Georgebingi's PR #1236 was approved and merged by @Folex1275.

🏆 @Georgebingi: You earned 35 FoxPoints for this contribution! Your current tier: Explorer (86 total points). Track your full progress on GrantFox.

👏 Great work, @Georgebingi! Keep contributing to StellarStream-HQ.