Performance Memory Issues.md

Performance and Memory Issues

Memory Management Problems

Memory Leaks Detection and Resolution

Problem: Application memory usage continuously increases over time

Symptoms:

Memory usage growing from 100MB to 2GB+ over hours
Node.js process killed by system (OOMKilled)
"JavaScript heap out of memory" errors
Performance degradation over time

Diagnosis Tools:

Memory monitoring setup

// memory-monitor.js
const v8 = require('v8');
const { performance, PerformanceObserver } = require('perf_hooks');

class MemoryMonitor {
  constructor(options = {}) {
    this.options = {
      interval: options.interval || 30000, // 30 seconds
      threshold: options.threshold || 500, // 500MB
      logFile: options.logFile || './logs/memory.log',
      ...options
    };
    
    this.startMonitoring();
  }

  startMonitoring() {
    // Memory usage monitoring
    setInterval(() => {
      this.logMemoryUsage();
    }, this.options.interval);

    // Garbage collection monitoring
    const obs = new PerformanceObserver((list) => {
      const entries = list.getEntries();
      entries.forEach((entry) => {
        if (entry.entryType === 'gc') {
          this.logGarbageCollection(entry);
        }
      });
    });
    obs.observe({ entryTypes: ['gc'] });

    // V8 heap monitoring
    setInterval(() => {
      this.logHeapStatistics();
    }, this.options.interval * 2);
  }

  logMemoryUsage() {
    const usage = process.memoryUsage();
    const timestamp = new Date().toISOString();
    
    const memoryInfo = {
      timestamp,
      rss: Math.round(usage.rss / 1024 / 1024), // MB
      heapTotal: Math.round(usage.heapTotal / 1024 / 1024),
      heapUsed: Math.round(usage.heapUsed / 1024 / 1024),
      external: Math.round(usage.external / 1024 / 1024),
      arrayBuffers: Math.round(usage.arrayBuffers / 1024 / 1024)
    };

    console.log('Memory Usage:', memoryInfo);

    // Alert if memory usage is high
    if (memoryInfo.heapUsed > this.options.threshold) {
      console.warn(`⚠️  High memory usage detected: ${memoryInfo.heapUsed}MB`);
      this.triggerMemoryAlert(memoryInfo);
    }

    return memoryInfo;
  }

  logGarbageCollection(entry) {
    const gcInfo = {
      timestamp: new Date().toISOString(),
      kind: this.getGCKind(entry.detail?.kind),
      duration: Math.round(entry.duration * 100) / 100,
      flags: entry.detail?.flags || 0
    };

    console.log('GC Event:', gcInfo);

    // Alert on long GC pauses
    if (gcInfo.duration > 100) { // > 100ms
      console.warn(`⚠️  Long GC pause detected: ${gcInfo.duration}ms`);
    }
  }

  logHeapStatistics() {
    const heapStats = v8.getHeapStatistics();
    const heapInfo = {
      timestamp: new Date().toISOString(),
      totalHeapSize: Math.round(heapStats.total_heap_size / 1024 / 1024),
      usedHeapSize: Math.round(heapStats.used_heap_size / 1024 / 1024),
      heapSizeLimit: Math.round(heapStats.heap_size_limit / 1024 / 1024),
      mallocedMemory: Math.round(heapStats.malloced_memory / 1024 / 1024),
      peakMallocedMemory: Math.round(heapStats.peak_malloced_memory / 1024 / 1024)
    };

    console.log('Heap Statistics:', heapInfo);
    return heapInfo;
  }

  getGCKind(kind) {
    const gcTypes = {
      1: 'Scavenge',
      2: 'Mark-Sweep-Compact',
      4: 'Incremental Marking',
      8: 'Weak Phantom',
      15: 'All'
    };
    return gcTypes[kind] || 'Unknown';
  }

  triggerMemoryAlert(memoryInfo) {
    // Could send alerts to monitoring systems
    console.error('Memory threshold exceeded:', memoryInfo);
    
    // Force garbage collection if available
    if (global.gc) {
      console.log('Forcing garbage collection...');
      global.gc();
    }
  }

  generateHeapSnapshot() {
    const snapshot = v8.writeHeapSnapshot();
    console.log(`Heap snapshot written to: ${snapshot}`);
    return snapshot;
  }
}

// Usage
const monitor = new MemoryMonitor({
  interval: 10000, // 10 seconds
  threshold: 512   // 512MB
});

module.exports = MemoryMonitor;

Common Memory Leak Sources and Fixes:

1. Event Listener Leaks

// Problem: Not removing event listeners
class ComponentWithLeaks {
  constructor() {
    this.handler = this.onData.bind(this);
    process.on('data', this.handler); // Leak: never removed
  }
  
  onData(data) {
    // Handle data
  }
}

// Solution: Proper cleanup
class ComponentWithoutLeaks {
  constructor() {
    this.handler = this.onData.bind(this);
    this.listeners = new Map();
    this.addListener(process, 'data', this.handler);
  }
  
  addListener(emitter, event, handler) {
    emitter.on(event, handler);
    
    if (!this.listeners.has(emitter)) {
      this.listeners.set(emitter, []);
    }
    this.listeners.get(emitter).push({ event, handler });
  }
  
  cleanup() {
    for (const [emitter, listeners] of this.listeners) {
      listeners.forEach(({ event, handler }) => {
        emitter.removeListener(event, handler);
      });
    }
    this.listeners.clear();
  }
  
  onData(data) {
    // Handle data
  }
}

2. Timer and Interval Leaks

// Problem: Timers not cleared
class TimerLeaks {
  constructor() {
    setInterval(() => {
      this.doSomething();
    }, 1000); // Leak: interval never cleared
  }
}

// Solution: Track and clear timers
class TimerManagement {
  constructor() {
    this.timers = new Set();
    this.startPeriodicTask();
  }
  
  startPeriodicTask() {
    const intervalId = setInterval(() => {
      this.doSomething();
    }, 1000);
    
    this.timers.add(intervalId);
  }
  
  setTimeout(callback, delay) {
    const timeoutId = setTimeout(() => {
      this.timers.delete(timeoutId);
      callback();
    }, delay);
    
    this.timers.add(timeoutId);
    return timeoutId;
  }
  
  cleanup() {
    for (const timerId of this.timers) {
      clearTimeout(timerId);
      clearInterval(timerId);
    }
    this.timers.clear();
  }
}

3. Database Connection Leaks

// Problem: Not closing database connections
async function queryWithLeak() {
  const connection = await database.getConnection();
  const result = await connection.query('SELECT * FROM users');
  return result; // Leak: connection never released
}

// Solution: Always release connections
async function queryWithoutLeak() {
  let connection;
  try {
    connection = await database.getConnection();
    const result = await connection.query('SELECT * FROM users');
    return result;
  } finally {
    if (connection) {
      await connection.release();
    }
  }
}

// Better: Use connection pools with automatic management
class DatabaseService {
  constructor(pool) {
    this.pool = pool;
  }
  
  async query(sql, params) {
    return this.pool.query(sql, params); // Pool handles connection lifecycle
  }
  
  async transaction(operations) {
    const connection = await this.pool.getConnection();
    const transaction = await connection.beginTransaction();
    
    try {
      const results = [];
      for (const operation of operations) {
        const result = await operation(connection);
        results.push(result);
      }
      
      await transaction.commit();
      return results;
    } catch (error) {
      await transaction.rollback();
      throw error;
    } finally {
      connection.release();
    }
  }
}

---

CPU Performance Optimization

High CPU Usage Diagnosis

Problem: Framework consuming excessive CPU resources

Symptoms:

CPU usage consistently >80%
Application becomes unresponsive
Long response times
System becomes sluggish

CPU Profiling and Analysis:

// cpu-profiler.js
const { performance } = require('perf_hooks');

class CPUProfiler {
  constructor() {
    this.samples = [];
    this.isProfileng = false;
  }

  startProfiling(duration = 30000) {
    if (this.isProfileng) {
      console.log('Profiling already in progress');
      return;
    }

    console.log(`Starting CPU profiling for ${duration}ms...`);
    this.isProfileng = true;
    this.samples = [];

    const startTime = performance.now();
    const sampleInterval = 100; // Sample every 100ms

    const sampler = setInterval(() => {
      this.takeSample();
    }, sampleInterval);

    setTimeout(() => {
      clearInterval(sampler);
      this.isProfileng = false;
      this.analyzeSamples();
    }, duration);
  }

  takeSample() {
    const cpuUsage = process.cpuUsage();
    const memUsage = process.memoryUsage();
    
    this.samples.push({
      timestamp: performance.now(),
      cpuUser: cpuUsage.user,
      cpuSystem: cpuUsage.system,
      memoryUsed: memUsage.heapUsed,
      memoryTotal: memUsage.heapTotal
    });
  }

  analyzeSamples() {
    if (this.samples.length === 0) return;

    console.log('\n=== CPU Profile Analysis ===');
    console.log(`Total samples: ${this.samples.length}`);

    // Calculate CPU usage over time
    const cpuDeltas = [];
    for (let i = 1; i < this.samples.length; i++) {
      const prev = this.samples[i - 1];
      const curr = this.samples[i];
      
      const userDelta = curr.cpuUser - prev.cpuUser;
      const systemDelta = curr.cpuSystem - prev.cpuSystem;
      const timeDelta = curr.timestamp - prev.timestamp;
      
      cpuDeltas.push({
        userCpu: (userDelta / 1000) / (timeDelta / 1000) * 100, // Convert to percentage
        systemCpu: (systemDelta / 1000) / (timeDelta / 1000) * 100,
        totalCpu: ((userDelta + systemDelta) / 1000) / (timeDelta / 1000) * 100
      });
    }

    // Calculate statistics
    const avgCpu = cpuDeltas.reduce((sum, delta) => sum + delta.totalCpu, 0) / cpuDeltas.length;
    const maxCpu = Math.max(...cpuDeltas.map(d => d.totalCpu));
    const minCpu = Math.min(...cpuDeltas.map(d => d.totalCpu));

    console.log(`Average CPU Usage: ${avgCpu.toFixed(2)}%`);
    console.log(`Maximum CPU Usage: ${maxCpu.toFixed(2)}%`);
    console.log(`Minimum CPU Usage: ${minCpu.toFixed(2)}%`);

    // Identify high CPU periods
    const highCpuSamples = cpuDeltas.filter(d => d.totalCpu > 50);
    if (highCpuSamples.length > 0) {
      console.log(`\nHigh CPU periods detected: ${highCpuSamples.length} samples > 50%`);
    }

    return {
      averageCpu: avgCpu,
      maxCpu: maxCpu,
      minCpu: minCpu,
      highCpuPeriods: highCpuSamples.length,
      samples: this.samples
    };
  }
}

// Usage
const profiler = new CPUProfiler();
profiler.startProfiling(60000); // Profile for 1 minute

Event Loop Monitoring:

// event-loop-monitor.js
const { monitorEventLoopDelay } = require('perf_hooks');

class EventLoopMonitor {
  constructor(options = {}) {
    this.options = {
      resolution: options.resolution || 10,
      threshold: options.threshold || 50, // 50ms lag threshold
      ...options
    };
    
    this.histogram = monitorEventLoopDelay({ 
      resolution: this.options.resolution 
    });
    
    this.startMonitoring();
  }

  startMonitoring() {
    this.histogram.enable();
    
    setInterval(() => {
      this.checkEventLoopLag();
    }, 5000); // Check every 5 seconds
  }

  checkEventLoopLag() {
    const lag = {
      min: this.histogram.min / 1e6, // Convert to milliseconds
      max: this.histogram.max / 1e6,
      mean: this.histogram.mean / 1e6,
      stddev: this.histogram.stddev / 1e6,
      p50: this.histogram.percentile(50) / 1e6,
      p95: this.histogram.percentile(95) / 1e6,
      p99: this.histogram.percentile(99) / 1e6
    };

    console.log('Event Loop Lag:', {
      mean: `${lag.mean.toFixed(2)}ms`,
      p95: `${lag.p95.toFixed(2)}ms`,
      p99: `${lag.p99.toFixed(2)}ms`,
      max: `${lag.max.toFixed(2)}ms`
    });

    // Alert on high lag
    if (lag.mean > this.options.threshold) {
      console.warn(`⚠️  High event loop lag detected: ${lag.mean.toFixed(2)}ms average`);
    }

    if (lag.p99 > this.options.threshold * 2) {
      console.error(`🚨 Critical event loop lag: ${lag.p99.toFixed(2)}ms (p99)`);
    }

    // Reset histogram for next measurement
    this.histogram.reset();

    return lag;
  }

  stop() {
    this.histogram.disable();
  }
}

// Usage
const monitor = new EventLoopMonitor({
  threshold: 10 // 10ms threshold
});

CPU Optimization Strategies:

1. Offload CPU-intensive tasks to worker threads

// worker-pool.js
const { Worker, isMainThread, parentPort, workerData } = require('worker_threads');
const path = require('path');

class WorkerPool {
  constructor(workerScript, poolSize = 4) {
    this.workerScript = path.resolve(workerScript);
    this.poolSize = poolSize;
    this.workers = [];
    this.availableWorkers = [];
    this.taskQueue = [];
    
    this.createWorkers();
  }

  createWorkers() {
    for (let i = 0; i < this.poolSize; i++) {
      const worker = new Worker(this.workerScript);
      worker.on('message', (result) => {
        this.handleWorkerResult(worker, result);
      });
      
      worker.on('error', (error) => {
        console.error('Worker error:', error);
        this.replaceWorker(worker);
      });

      this.workers.push(worker);
      this.availableWorkers.push(worker);
    }
  }

  async executeTask(taskData) {
    return new Promise((resolve, reject) => {
      const task = { taskData, resolve, reject, id: Date.now() + Math.random() };
      
      if (this.availableWorkers.length > 0) {
        this.assignTask(task);
      } else {
        this.taskQueue.push(task);
      }
    });
  }

  assignTask(task) {
    const worker = this.availableWorkers.pop();
    worker.currentTask = task;
    worker.postMessage({ taskId: task.id, data: task.taskData });
  }

  handleWorkerResult(worker, result) {
    const task = worker.currentTask;
    worker.currentTask = null;
    
    if (result.success) {
      task.resolve(result.data);
    } else {
      task.reject(new Error(result.error));
    }

    // Return worker to available pool
    this.availableWorkers.push(worker);

    // Process queued tasks
    if (this.taskQueue.length > 0) {
      const nextTask = this.taskQueue.shift();
      this.assignTask(nextTask);
    }
  }

  replaceWorker(failedWorker) {
    const index = this.workers.indexOf(failedWorker);
    if (index !== -1) {
      this.workers.splice(index, 1);
      
      const availableIndex = this.availableWorkers.indexOf(failedWorker);
      if (availableIndex !== -1) {
        this.availableWorkers.splice(availableIndex, 1);
      }

      // Create replacement worker
      const worker = new Worker(this.workerScript);
      worker.on('message', (result) => {
        this.handleWorkerResult(worker, result);
      });
      
      this.workers.push(worker);
      this.availableWorkers.push(worker);
    }
  }

  terminate() {
    return Promise.all(this.workers.map(worker => worker.terminate()));
  }
}

// cpu-intensive-worker.js
if (!isMainThread) {
  parentPort.on('message', ({ taskId, data }) => {
    try {
      // Perform CPU-intensive operation
      const result = performHeavyComputation(data);
      
      parentPort.postMessage({
        taskId,
        success: true,
        data: result
      });
    } catch (error) {
      parentPort.postMessage({
        taskId,
        success: false,
        error: error.message
      });
    }
  });

  function performHeavyComputation(data) {
    // Example: Complex mathematical calculation
    let result = 0;
    for (let i = 0; i < data.iterations; i++) {
      result += Math.sqrt(i) * Math.sin(i) * Math.cos(i);
    }
    return result;
  }
}

// Usage in main thread
const workerPool = new WorkerPool('./cpu-intensive-worker.js', 4);

async function handleCpuIntensiveRequest(req, res) {
  try {
    const result = await workerPool.executeTask({
      iterations: 1000000,
      inputData: req.body.data
    });
    
    res.json({ result });
  } catch (error) {
    res.status(500).json({ error: error.message });
  }
}

2. Implement request throttling and rate limiting

// rate-limiter.js
const rateLimit = require('express-rate-limit');

// Different limits for different endpoint types
const createRateLimiter = (windowMs, max, message) => {
  return rateLimit({
    windowMs,
    max,
    message: { error: message },
    standardHeaders: true,
    legacyHeaders: false,
    handler: (req, res) => {
      console.warn(`Rate limit exceeded for IP: ${req.ip}, Path: ${req.path}`);
      res.status(429).json({
        error: 'Too many requests',
        message: message,
        retryAfter: Math.ceil(windowMs / 1000)
      });
    }
  });
};

// Conservative limits for CPU-intensive endpoints
const heavyLimiter = createRateLimiter(
  15 * 60 * 1000, // 15 minutes
  5, // 5 requests
  'Too many CPU-intensive requests, please try again later'
);

// Standard limits for regular API endpoints
const standardLimiter = createRateLimiter(
  15 * 60 * 1000, // 15 minutes
  100, // 100 requests
  'Too many requests, please try again later'
);

// Lenient limits for read-only endpoints
const readLimiter = createRateLimiter(
  15 * 60 * 1000, // 15 minutes
  500, // 500 requests
  'Too many read requests, please try again later'
);

module.exports = {
  heavyLimiter,
  standardLimiter,
  readLimiter
};

---

Database Query Performance

Slow Query Identification and Optimization

Problem: Database queries taking too long to execute

Symptoms:

API response times >5 seconds
Database connection pool exhaustion
Query timeout errors
High database CPU usage

Query Performance Monitoring:

// query-monitor.js
class QueryMonitor {
  constructor(database) {
    this.db = database;
    this.slowQueries = [];
    this.queryStats = new Map();
    this.setupMonitoring();
  }

  setupMonitoring() {
    // Wrap database query method
    const originalQuery = this.db.query.bind(this.db);
    
    this.db.query = async (sql, params = []) => {
      const queryId = this.generateQueryId(sql);
      const startTime = performance.now();
      
      try {
        const result = await originalQuery(sql, params);
        const duration = performance.now() - startTime;
        
        this.recordQuery(queryId, sql, params, duration, true);
        
        if (duration > 1000) { // Log slow queries >1s
          console.warn(`Slow query detected (${duration.toFixed(2)}ms):`, {
            sql: sql.substring(0, 200),
            duration: `${duration.toFixed(2)}ms`
          });
        }
        
        return result;
      } catch (error) {
        const duration = performance.now() - startTime;
        this.recordQuery(queryId, sql, params, duration, false, error);
        throw error;
      }
    };
  }

  generateQueryId(sql) {
    // Normalize SQL for grouping similar queries
    return sql
      .replace(/\s+/g, ' ')
      .replace(/\$\d+|\?/g, '?') // Replace parameters
      .replace(/IN\s*\([^)]+\)/gi, 'IN (?)') // Normalize IN clauses
      .substring(0, 100);
  }

  recordQuery(queryId, sql, params, duration, success, error = null) {
    if (!this.queryStats.has(queryId)) {
      this.queryStats.set(queryId, {
        sql: queryId,
        count: 0,
        totalDuration: 0,
        averageDuration: 0,
        maxDuration: 0,
        minDuration: Infinity,
        errors: 0,
        lastExecuted: null
      });
    }

    const stats = this.queryStats.get(queryId);
    stats.count++;
    stats.totalDuration += duration;
    stats.averageDuration = stats.totalDuration / stats.count;
    stats.maxDuration = Math.max(stats.maxDuration, duration);
    stats.minDuration = Math.min(stats.minDuration, duration);
    stats.lastExecuted = new Date();

    if (!success) {
      stats.errors++;
    }

    // Track slow queries
    if (duration > 1000) {
      this.slowQueries.push({
        queryId,
        sql: sql.substring(0, 500),
        params: params.slice(0, 10), // Limit param logging
        duration,
        timestamp: new Date(),
        error: error?.message
      });

      // Keep only last 100 slow queries
      if (this.slowQueries.length > 100) {
        this.slowQueries.shift();
      }
    }
  }

  getSlowQueries(limit = 10) {
    return this.slowQueries
      .sort((a, b) => b.duration - a.duration)
      .slice(0, limit);
  }

  getQueryStats(sortBy = 'averageDuration', limit = 20) {
    return Array.from(this.queryStats.values())
      .sort((a, b) => {
        if (sortBy === 'averageDuration') return b.averageDuration - a.averageDuration;
        if (sortBy === 'totalDuration') return b.totalDuration - a.totalDuration;
        if (sortBy === 'count') return b.count - a.count;
        return 0;
      })
      .slice(0, limit);
  }

  generateReport() {
    const slowQueries = this.getSlowQueries(5);
    const topQueries = this.getQueryStats('totalDuration', 10);

    console.log('\n=== Database Query Performance Report ===');
    
    console.log('\nTop 5 Slowest Queries:');
    slowQueries.forEach((query, index) => {
      console.log(`${index + 1}. ${query.duration.toFixed(2)}ms - ${query.sql.substring(0, 100)}...`);
    });

    console.log('\nTop 10 Queries by Total Duration:');
    topQueries.forEach((query, index) => {
      console.log(`${index + 1}. Avg: ${query.averageDuration.toFixed(2)}ms, Total: ${query.totalDuration.toFixed(2)}ms, Count: ${query.count}`);
      console.log(`    ${query.sql.substring(0, 100)}...`);
    });

    return { slowQueries, topQueries };
  }
}

Database Index Optimization:

-- Common BMAD query optimizations

-- 1. Business Models queries
CREATE INDEX IF NOT EXISTS idx_bmad_models_composite 
ON bmad_business_models(organization_id, status, updated_at DESC);

CREATE INDEX IF NOT EXISTS idx_bmad_models_search 
ON bmad_business_models(name, description) 
USING gin(to_tsvector('english', name || ' ' || description)); -- PostgreSQL full-text search

-- 2. Stakeholder queries
CREATE INDEX IF NOT EXISTS idx_bmad_stakeholders_composite 
ON bmad_stakeholders(business_model_id, type, influence_level DESC);

CREATE INDEX IF NOT EXISTS idx_bmad_stakeholders_engagement 
ON bmad_stakeholders(engagement_level, updated_at DESC);

-- 3. Canvas queries
CREATE INDEX IF NOT EXISTS idx_bmad_canvas_model_type 
ON bmad_canvases(business_model_id, canvas_type, version DESC);

-- 4. Analytics queries
CREATE INDEX IF NOT EXISTS idx_bmad_analytics_time 
ON bmad_analytics(created_at DESC, metric_type);

-- 5. Audit queries
CREATE INDEX IF NOT EXISTS idx_bmad_audit_composite 
ON bmad_audit_logs(resource_type, resource_id, created_at DESC);

-- Query optimization examples

-- Before: Slow query
-- SELECT * FROM bmad_business_models WHERE organization_id = ? ORDER BY updated_at DESC LIMIT 20;

-- After: Optimized with proper indexing
-- Uses idx_bmad_models_composite index efficiently

-- Before: Slow stakeholder query
-- SELECT s.*, bm.name as model_name 
-- FROM bmad_stakeholders s 
-- JOIN bmad_business_models bm ON s.business_model_id = bm.id 
-- WHERE s.influence_level > 3;

-- After: Optimized query
-- SELECT s.id, s.name, s.type, s.influence_level, s.engagement_level, bm.name as model_name
-- FROM bmad_stakeholders s 
-- JOIN bmad_business_models bm ON s.business_model_id = bm.id 
-- WHERE s.influence_level > 3
-- ORDER BY s.influence_level DESC, s.engagement_level DESC;
-- Uses idx_bmad_stakeholders_composite index

Query Optimization Techniques:

// optimized-queries.js
class OptimizedBMADQueries {
  constructor(database, cache) {
    this.db = database;
    this.cache = cache;
  }

  async getBusinessModels(filters = {}, pagination = {}) {
    const { 
      organizationId, 
      status, 
      search, 
      includeStakeholderCount = false 
    } = filters;
    
    const { 
      page = 1, 
      limit = 20, 
      sortBy = 'updated_at', 
      sortOrder = 'DESC' 
    } = pagination;

    // Build cache key
    const cacheKey = `bmad:models:${JSON.stringify(filters)}:${JSON.stringify(pagination)}`;
    
    // Check cache first
    const cached = await this.cache.get(cacheKey);
    if (cached) return cached;

    // Build optimized query
    let baseQuery = `
      SELECT 
        bm.id, 
        bm.name, 
        bm.description, 
        bm.status, 
        bm.created_at, 
        bm.updated_at,
        bm.created_by
    `;

    if (includeStakeholderCount) {
      baseQuery += `, COUNT(s.id) as stakeholder_count`;
    }

    baseQuery += ` FROM bmad_business_models bm`;

    if (includeStakeholderCount) {
      baseQuery += ` LEFT JOIN bmad_stakeholders s ON bm.id = s.business_model_id`;
    }

    const conditions = [];
    const params = [];

    if (organizationId) {
      conditions.push('bm.organization_id = ?');
      params.push(organizationId);
    }

    if (status) {
      if (Array.isArray(status)) {
        conditions.push(`bm.status IN (${status.map(() => '?').join(', ')})`);
        params.push(...status);
      } else {
        conditions.push('bm.status = ?');
        params.push(status);
      }
    }

    if (search) {
      conditions.push('(bm.name ILIKE ? OR bm.description ILIKE ?)');
      params.push(`%${search}%`, `%${search}%`);
    }

    if (conditions.length > 0) {
      baseQuery += ` WHERE ${conditions.join(' AND ')}`;
    }

    if (includeStakeholderCount) {
      baseQuery += ` GROUP BY bm.id`;
    }

    // Add sorting
    const allowedSortFields = ['name', 'status', 'created_at', 'updated_at'];
    const sortField = allowedSortFields.includes(sortBy) ? sortBy : 'updated_at';
    const order = sortOrder.toUpperCase() === 'ASC' ? 'ASC' : 'DESC';
    
    baseQuery += ` ORDER BY bm.${sortField} ${order}`;

    // Add pagination
    const offset = (page - 1) * limit;
    baseQuery += ` LIMIT ? OFFSET ?`;
    params.push(limit, offset);

    // Execute query
    const models = await this.db.query(baseQuery, params);

    // Get total count for pagination
    let countQuery = `SELECT COUNT(DISTINCT bm.id) as total FROM bmad_business_models bm`;
    
    if (conditions.length > 0) {
      countQuery += ` WHERE ${conditions.join(' AND ')}`;
    }

    const [{ total }] = await this.db.query(countQuery, params.slice(0, -2)); // Remove LIMIT/OFFSET params

    const result = {
      data: models,
      pagination: {
        page,
        limit,
        total: parseInt(total),
        pages: Math.ceil(total / limit)
      }
    };

    // Cache for 5 minutes
    await this.cache.set(cacheKey, result, 300);

    return result;
  }

  async getBusinessModelWithDetails(modelId) {
    const cacheKey = `bmad:model:full:${modelId}`;
    const cached = await this.cache.get(cacheKey);
    if (cached) return cached;

    // Single optimized query to get all related data
    const query = `
      SELECT 
        bm.*,
        json_agg(
          DISTINCT jsonb_build_object(
            'id', s.id,
            'name', s.name,
            'type', s.type,
            'influence_level', s.influence_level,
            'engagement_level', s.engagement_level
          )
        ) FILTER (WHERE s.id IS NOT NULL) as stakeholders,
        json_agg(
          DISTINCT jsonb_build_object(
            'id', c.id,
            'canvas_type', c.canvas_type,
            'version', c.version,
            'sections', c.sections
          )
        ) FILTER (WHERE c.id IS NOT NULL) as canvases
      FROM bmad_business_models bm
      LEFT JOIN bmad_stakeholders s ON bm.id = s.business_model_id
      LEFT JOIN bmad_canvases c ON bm.id = c.business_model_id
      WHERE bm.id = ?
      GROUP BY bm.id
    `;

    const [model] = await this.db.query(query, [modelId]);

    if (!model) {
      throw new Error('Business model not found');
    }

    // Parse JSON fields
    if (model.stakeholders && model.stakeholders[0] !== null) {
      model.stakeholders = model.stakeholders;
    } else {
      model.stakeholders = [];
    }

    if (model.canvases && model.canvases[0] !== null) {
      model.canvases = model.canvases;
    } else {
      model.canvases = [];
    }

    // Cache for 10 minutes
    await this.cache.set(cacheKey, model, 600);

    return model;
  }

  async invalidateCache(modelId) {
    const patterns = [
      `bmad:models:*`,
      `bmad:model:full:${modelId}`,
      `bmad:model:${modelId}`,
      `bmad:stakeholders:${modelId}`,
      `bmad:canvas:${modelId}`
    ];

    for (const pattern of patterns) {
      await this.cache.deletePattern(pattern);
    }
  }
}

---

Caching Strategy and Issues

Cache Performance Problems

Problem: Inefficient caching leading to poor performance

Symptoms:

Cache miss rates >50%
Stale data being served
Cache memory usage growing uncontrollably
Cache invalidation issues

Comprehensive Caching Strategy:

// cache-manager.js
const Redis = require('redis');

class AdvancedCacheManager {
  constructor(options = {}) {
    this.options = {
      redis: {
        host: options.redis?.host || 'localhost',
        port: options.redis?.port || 6379,
        password: options.redis?.password,
        db: options.redis?.db || 0
      },
      defaultTTL: options.defaultTTL || 3600, // 1 hour
      maxMemory: options.maxMemory || '500mb',
      keyPrefix: options.keyPrefix || 'bmad:',
      ...options
    };

    this.client = Redis.createClient(this.options.redis);
    this.stats = {
      hits: 0,
      misses: 0,
      sets: 0,
      deletes: 0,
      errors: 0
    };

    this.setupClient();
  }

  async setupClient() {
    this.client.on('error', (error) => {
      console.error('Redis client error:', error);
      this.stats.errors++;
    });

    this.client.on('connect', () => {
      console.log('Connected to Redis cache');
    });

    await this.client.connect();

    // Configure Redis for optimal performance
    await this.client.configSet('maxmemory', this.options.maxMemory);
    await this.client.configSet('maxmemory-policy', 'allkeys-lru');
  }

  generateKey(key) {
    return `${this.options.keyPrefix}${key}`;
  }

  async get(key) {
    try {
      const fullKey = this.generateKey(key);
      const value = await this.client.get(fullKey);
      
      if (value !== null) {
        this.stats.hits++;
        return JSON.parse(value);
      } else {
        this.stats.misses++;
        return null;
      }
    } catch (error) {
      console.error('Cache get error:', error);
      this.stats.errors++;
      return null;
    }
  }

  async set(key, value, ttl = this.options.defaultTTL) {
    try {
      const fullKey = this.generateKey(key);
      const serialized = JSON.stringify(value);
      
      await this.client.setEx(fullKey, ttl, serialized);
      this.stats.sets++;
      
      return true;
    } catch (error) {
      console.error('Cache set error:', error);
      this.stats.errors++;
      return false;
    }
  }

  async delete(key) {
    try {
      const fullKey = this.generateKey(key);
      const result = await this.client.del(fullKey);
      this.stats.deletes++;
      return result > 0;
    } catch (error) {
      console.error('Cache delete error:', error);
      this.stats.errors++;
      return false;
    }
  }

  async deletePattern(pattern) {
    try {
      const fullPattern = this.generateKey(pattern);
      const keys = await this.client.keys(fullPattern);
      
      if (keys.length > 0) {
        const result = await this.client.del(keys);
        this.stats.deletes += result;
        return result;
      }
      
      return 0;
    } catch (error) {
      console.error('Cache delete pattern error:', error);
      this.stats.errors++;
      return 0;
    }
  }

  async mget(keys) {
    try {
      const fullKeys = keys.map(key => this.generateKey(key));
      const values = await this.client.mGet(fullKeys);
      
      const results = {};
      keys.forEach((key, index) => {
        if (values[index] !== null) {
          results[key] = JSON.parse(values[index]);
          this.stats.hits++;
        } else {
          results[key] = null;
          this.stats.misses++;
        }
      });
      
      return results;
    } catch (error) {
      console.error('Cache mget error:', error);
      this.stats.errors++;
      return {};
    }
  }

  async mset(items, ttl = this.options.defaultTTL) {
    try {
      const pipeline = this.client.multi();
      
      Object.entries(items).forEach(([key, value]) => {
        const fullKey = this.generateKey(key);
        const serialized = JSON.stringify(value);
        pipeline.setEx(fullKey, ttl, serialized);
      });
      
      await pipeline.exec();
      this.stats.sets += Object.keys(items).length;
      
      return true;
    } catch (error) {
      console.error('Cache mset error:', error);
      this.stats.errors++;
      return false;
    }
  }

  async exists(key) {
    try {
      const fullKey = this.generateKey(key);
      return await this.client.exists(fullKey) === 1;
    } catch (error) {
      console.error('Cache exists error:', error);
      this.stats.errors++;
      return false;
    }
  }

  async ttl(key) {
    try {
      const fullKey = this.generateKey(key);
      return await this.client.ttl(fullKey);
    } catch (error) {
      console.error('Cache TTL error:', error);
      this.stats.errors++;
      return -1;
    }
  }

  async expire(key, ttl) {
    try {
      const fullKey = this.generateKey(key);
      return await this.client.expire(fullKey, ttl) === 1;
    } catch (error) {
      console.error('Cache expire error:', error);
      this.stats.errors++;
      return false;
    }
  }

  getStats() {
    const total = this.stats.hits + this.stats.misses;
    const hitRate = total > 0 ? (this.stats.hits / total * 100).toFixed(2) : 0;
    
    return {
      ...this.stats,
      hitRate: `${hitRate}%`,
      total
    };
  }

  async getMemoryUsage() {
    try {
      const info = await this.client.info('memory');
      return this.parseRedisInfo(info);
    } catch (error) {
      console.error('Error getting memory usage:', error);
      return null;
    }
  }

  parseRedisInfo(info) {
    const lines = info.split('\r\n');
    const memory = {};
    
    lines.forEach(line => {
      if (line.startsWith('used_memory:')) {
        memory.usedMemory = parseInt(line.split(':')[1]);
      } else if (line.startsWith('used_memory_human:')) {
        memory.usedMemoryHuman = line.split(':')[1];
      } else if (line.startsWith('used_memory_peak:')) {
        memory.peakMemory = parseInt(line.split(':')[1]);
      } else if (line.startsWith('used_memory_peak_human:')) {
        memory.peakMemoryHuman = line.split(':')[1];
      }
    });
    
    return memory;
  }

  async flushAll() {
    try {
      await this.client.flushDb();
      console.log('Cache flushed successfully');
      return true;
    } catch (error) {
      console.error('Cache flush error:', error);
      this.stats.errors++;
      return false;
    }
  }

  async close() {
    await this.client.quit();
  }
}

module.exports = AdvancedCacheManager;

---

Last Updated: June 28, 2025
Framework Version: CursorRIPER.sigma v1.0+