Database API Issues.md

Database and API Issues

Database Connection Problems

Database Connection Failures

Problem: Framework cannot connect to the database

Symptoms:

Error: Connection ECONNREFUSED
Database connection timeout
Authentication failed for user
SSL connection error

Solutions:

Test database connectivity

# Test PostgreSQL connection
psql -h localhost -p 5432 -U your_user -d your_database -c "SELECT 1;"

# Test MySQL connection
mysql -h localhost -P 3306 -u your_user -p your_database -e "SELECT 1;"

# Test SQLite file access
sqlite3 ./data/framework.sqlite "SELECT 1;"

Fix connection configuration

// framework.config.js - Database configuration examples

// PostgreSQL
module.exports = {
  database: {
    type: 'postgresql',
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 5432,
    username: process.env.DB_USER || 'framework_user',
    password: process.env.DB_PASSWORD || 'your_password',
    database: process.env.DB_NAME || 'framework_db',
    ssl: process.env.NODE_ENV === 'production' ? { rejectUnauthorized: false } : false,
    synchronize: false,
    logging: process.env.NODE_ENV === 'development',
    extra: {
      connectionLimit: 10,
      acquireTimeout: 60000,
      timeout: 60000
    }
  }
};

// MySQL
module.exports = {
  database: {
    type: 'mysql',
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 3306,
    username: process.env.DB_USER || 'framework_user',
    password: process.env.DB_PASSWORD || 'your_password',
    database: process.env.DB_NAME || 'framework_db',
    synchronize: false,
    logging: process.env.NODE_ENV === 'development',
    extra: {
      connectionLimit: 10,
      acquireTimeout: 60000,
      timeout: 60000,
      reconnect: true,
      reconnectTries: Number.MAX_VALUE,
      reconnectInterval: 1000
    }
  }
};

// SQLite (Development)
module.exports = {
  database: {
    type: 'sqlite',
    database: './data/framework.sqlite',
    synchronize: true,
    logging: false
  }
};

Database connection recovery

class DatabaseConnectionManager {
  constructor(framework) {
    this.framework = framework;
    this.db = framework.getDatabase();
    this.reconnecting = false;
    this.setupConnectionMonitoring();
  }

  setupConnectionMonitoring() {
    // Monitor connection health
    setInterval(async () => {
      try {
        await this.db.query('SELECT 1');
      } catch (error) {
        console.error('Database health check failed:', error);
        this.handleConnectionLoss();
      }
    }, 30000); // Check every 30 seconds

    // Handle connection errors
    this.db.on('error', (error) => {
      console.error('Database connection error:', error);
      this.handleConnectionLoss();
    });

    this.db.on('disconnect', () => {
      console.warn('Database disconnected');
      this.handleConnectionLoss();
    });
  }

  async handleConnectionLoss() {
    if (this.reconnecting) return;
    
    this.reconnecting = true;
    console.log('Attempting to reconnect to database...');

    let retries = 0;
    const maxRetries = 10;
    const retryDelay = 5000; // 5 seconds

    while (retries < maxRetries) {
      try {
        await this.db.authenticate();
        console.log('Database reconnection successful');
        this.reconnecting = false;
        return;
      } catch (error) {
        retries++;
        console.log(`Reconnection attempt ${retries}/${maxRetries} failed:`, error.message);
        
        if (retries < maxRetries) {
          await new Promise(resolve => setTimeout(resolve, retryDelay * retries));
        }
      }
    }

    console.error('Failed to reconnect to database after maximum retries');
    this.reconnecting = false;
    
    // Notify application of persistent database failure
    this.framework.events.emit('database.connection.failed', {
      retries: maxRetries,
      timestamp: new Date()
    });
  }
}

Database Migration Issues

Problem: Database schema migrations fail or are incomplete

Symptoms:

Migration failed: Table already exists
Column doesn't exist error
Foreign key constraint fails
Migration rollback failed

Solutions:

Check migration status

# Framework migration commands
npm run db:migrate:status
npm run db:migrate:up
npm run db:migrate:down

# Manual migration check
npx @cursoriper/cli db:check-migrations

Fix failed migrations

// Manual migration repair
const { Framework } = require('@cursoriper/core');

async function repairMigrations() {
  const framework = new Framework();
  await framework.initialize();
  
  const db = framework.getDatabase();
  
  try {
    // Check migration table
    const migrations = await db.query(`
      SELECT * FROM migrations 
      ORDER BY executed_at DESC
    `);
    
    console.log('Current migrations:');
    migrations.forEach(migration => {
      console.log(`- ${migration.name}: ${migration.status}`);
    });

    // Find failed migrations
    const failed = migrations.filter(m => m.status === 'failed');
    
    for (const migration of failed) {
      console.log(`Attempting to repair migration: ${migration.name}`);
      
      // Mark as not executed to retry
      await db.query(`
        UPDATE migrations 
        SET status = 'pending', executed_at = NULL 
        WHERE name = ?
      `, [migration.name]);
      
      // Re-run migration
      await framework.migrationRunner.runMigration(migration.name);
    }
    
  } catch (error) {
    console.error('Migration repair failed:', error);
  }
}

repairMigrations();

Create emergency migration

-- emergency_schema_fix.sql
-- Run this to fix common schema issues

-- Ensure migrations table exists
CREATE TABLE IF NOT EXISTS migrations (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(255) NOT NULL,
    executed_at TIMESTAMP NULL,
    status ENUM('pending', 'running', 'completed', 'failed') DEFAULT 'pending'
);

-- Add missing columns (adjust as needed)
ALTER TABLE bmad_business_models 
ADD COLUMN IF NOT EXISTS organization_id VARCHAR(36),
ADD COLUMN IF NOT EXISTS version VARCHAR(20) DEFAULT '1.0.0';

-- Add missing indexes
CREATE INDEX IF NOT EXISTS idx_models_org ON bmad_business_models(organization_id);
CREATE INDEX IF NOT EXISTS idx_models_status ON bmad_business_models(status);

-- Fix foreign key constraints
ALTER TABLE bmad_stakeholders 
ADD CONSTRAINT fk_stakeholder_model 
FOREIGN KEY (business_model_id) 
REFERENCES bmad_business_models(id) 
ON DELETE CASCADE;

---

API Performance and Reliability Issues

API Response Time Issues

Problem: API endpoints are slow or timing out

Symptoms:

Request timeout errors
High response times (>5 seconds)
504 Gateway timeout
API rate limiting triggered

Solutions:

Implement request monitoring

const express = require('express');
const responseTime = require('response-time');

// Add response time monitoring
app.use(responseTime((req, res, time) => {
  const route = req.route ? req.route.path : req.url;
  console.log(`${req.method} ${route} - ${time.toFixed(2)}ms`);
  
  // Log slow requests
  if (time > 1000) {
    console.warn(`Slow request detected: ${req.method} ${route} - ${time.toFixed(2)}ms`);
  }
}));

// Request logging middleware
app.use((req, res, next) => {
  const start = Date.now();
  
  res.on('finish', () => {
    const duration = Date.now() - start;
    console.log(`${req.method} ${req.url} ${res.statusCode} - ${duration}ms`);
  });
  
  next();
});

Optimize database queries

class OptimizedAPIService {
  constructor(framework) {
    this.db = framework.getDatabase();
    this.cache = framework.getCache();
  }

  async getBusinessModels(req, res) {
    try {
      const { page = 1, limit = 20, status, search } = req.query;
      const offset = (page - 1) * limit;
      
      // Build cache key
      const cacheKey = `api:models:${page}:${limit}:${status || 'all'}:${search || ''}`;
      
      // Check cache first
      let result = await this.cache.get(cacheKey);
      
      if (!result) {
        // Build optimized query
        let query = `
          SELECT bm.id, bm.name, bm.description, bm.status, bm.created_at, bm.updated_at,
                 COUNT(s.id) as stakeholder_count
          FROM bmad_business_models bm
          LEFT JOIN bmad_stakeholders s ON bm.id = s.business_model_id
        `;
        
        const conditions = [];
        const params = [];
        
        if (status) {
          conditions.push('bm.status = ?');
          params.push(status);
        }
        
        if (search) {
          conditions.push('(bm.name LIKE ? OR bm.description LIKE ?)');
          params.push(`%${search}%`, `%${search}%`);
        }
        
        if (conditions.length > 0) {
          query += ' WHERE ' + conditions.join(' AND ');
        }
        
        query += ' GROUP BY bm.id ORDER BY bm.updated_at DESC';
        query += ` LIMIT ${limit} OFFSET ${offset}`;
        
        const models = await this.db.query(query, params);
        
        // Get total count
        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, conditions.length));
        
        result = {
          data: models,
          pagination: {
            page: parseInt(page),
            limit: parseInt(limit),
            total: parseInt(total),
            pages: Math.ceil(total / limit)
          }
        };
        
        // Cache for 5 minutes
        await this.cache.set(cacheKey, result, 300);
      }
      
      res.json(result);
      
    } catch (error) {
      console.error('API error:', error);
      res.status(500).json({
        error: 'Internal server error',
        message: process.env.NODE_ENV === 'development' ? error.message : 'An error occurred'
      });
    }
  }
}

Add request timeout handling

// Request timeout middleware
function timeoutHandler(timeout = 30000) {
  return (req, res, next) => {
    const timer = setTimeout(() => {
      if (!res.headersSent) {
        res.status(408).json({
          error: 'Request timeout',
          message: 'The request took too long to process'
        });
      }
    }, timeout);
    
    res.on('finish', () => {
      clearTimeout(timer);
    });
    
    next();
  };
}

// Apply to all routes
app.use(timeoutHandler(30000)); // 30 second timeout

// Or specific routes
app.get('/api/slow-endpoint', timeoutHandler(60000), slowEndpointHandler);

API Authentication Issues

Problem: API authentication failing or inconsistent

Symptoms:

401 Unauthorized errors
JWT token validation failures
Session expired unexpectedly
CORS preflight failures

Solutions:

Debug authentication flow

// Enhanced JWT middleware with debugging
const jwt = require('jsonwebtoken');

function authenticateToken(req, res, next) {
  const authHeader = req.headers['authorization'];
  const token = authHeader && authHeader.split(' ')[1]; // Bearer TOKEN

  if (!token) {
    console.log('Authentication failed: No token provided');
    return res.status(401).json({ 
      error: 'Access denied',
      message: 'No authentication token provided'
    });
  }

  try {
    const decoded = jwt.verify(token, process.env.JWT_SECRET);
    
    // Additional validation
    if (decoded.exp && decoded.exp < Date.now() / 1000) {
      console.log('Authentication failed: Token expired');
      return res.status(401).json({ 
        error: 'Token expired',
        message: 'Please log in again'
      });
    }
    
    req.user = decoded;
    next();
    
  } catch (error) {
    console.log('Authentication failed:', error.message);
    
    if (error.name === 'TokenExpiredError') {
      return res.status(401).json({ 
        error: 'Token expired',
        message: 'Please log in again'
      });
    } else if (error.name === 'JsonWebTokenError') {
      return res.status(401).json({ 
        error: 'Invalid token',
        message: 'Authentication token is invalid'
      });
    } else {
      return res.status(500).json({ 
        error: 'Authentication error',
        message: 'An error occurred during authentication'
      });
    }
  }
}

Fix CORS issues

const cors = require('cors');

// Enhanced CORS configuration
const corsOptions = {
  origin: function (origin, callback) {
    // Allow requests with no origin (mobile apps, etc.)
    if (!origin) return callback(null, true);
    
    const allowedOrigins = [
      'http://localhost:3000',
      'http://localhost:3001',
      'https://yourdomain.com',
      process.env.FRONTEND_URL
    ].filter(Boolean);
    
    if (allowedOrigins.includes(origin)) {
      callback(null, true);
    } else {
      console.log('CORS blocked origin:', origin);
      callback(new Error('Not allowed by CORS'));
    }
  },
  credentials: true,
  optionsSuccessStatus: 200,
  methods: ['GET', 'POST', 'PUT', 'DELETE', 'OPTIONS'],
  allowedHeaders: [
    'Origin',
    'X-Requested-With', 
    'Content-Type', 
    'Accept',
    'Authorization',
    'Cache-Control'
  ]
};

app.use(cors(corsOptions));

// Handle preflight requests
app.options('*', cors(corsOptions));

Session management fixes

const session = require('express-session');
const MongoStore = require('connect-mongo');

// Robust session configuration
app.use(session({
  secret: process.env.SESSION_SECRET || 'your-session-secret',
  resave: false,
  saveUninitialized: false,
  rolling: true, // Reset expiry on activity
  cookie: {
    secure: process.env.NODE_ENV === 'production', // HTTPS only in production
    httpOnly: true, // Prevent XSS
    maxAge: 24 * 60 * 60 * 1000, // 24 hours
    sameSite: process.env.NODE_ENV === 'production' ? 'strict' : 'lax'
  },
  store: process.env.MONGODB_URI ? MongoStore.create({
    mongoUrl: process.env.MONGODB_URI,
    touchAfter: 24 * 3600 // Update session once per 24 hours unless changed
  }) : undefined
}));

---

Data Consistency Issues

Database Transaction Problems

Problem: Data inconsistencies due to failed transactions

Symptoms:

Partial data updates
Orphaned records
Foreign key constraint violations
Deadlock errors

Solutions:

Implement proper transaction handling

class TransactionManager {
  constructor(database) {
    this.db = database;
  }

  async executeTransaction(operations) {
    const transaction = await this.db.transaction();
    
    try {
      const results = [];
      
      for (const operation of operations) {
        const result = await operation(transaction);
        results.push(result);
      }
      
      await transaction.commit();
      return results;
      
    } catch (error) {
      await transaction.rollback();
      console.error('Transaction failed, rolled back:', error);
      throw error;
    }
  }

  async createBusinessModelWithStakeholders(modelData, stakeholders) {
    return await this.executeTransaction([
      // Create business model
      async (trx) => {
        const [model] = await trx.query(`
          INSERT INTO bmad_business_models (id, name, description, canvas_data, status, created_by)
          VALUES (?, ?, ?, ?, ?, ?)
        `, [
          modelData.id,
          modelData.name,
          modelData.description,
          JSON.stringify(modelData.canvas_data),
          modelData.status,
          modelData.created_by
        ]);
        return model;
      },
      
      // Create stakeholders
      async (trx) => {
        const stakeholderPromises = stakeholders.map(stakeholder => 
          trx.query(`
            INSERT INTO bmad_stakeholders (id, business_model_id, name, type, influence_level, engagement_level)
            VALUES (?, ?, ?, ?, ?, ?)
          `, [
            stakeholder.id,
            modelData.id,
            stakeholder.name,
            stakeholder.type,
            stakeholder.influence_level,
            stakeholder.engagement_level
          ])
        );
        return await Promise.all(stakeholderPromises);
      }
    ]);
  }
}

Handle deadlock situations

class DeadlockHandler {
  static async withRetry(operation, maxRetries = 3, delay = 1000) {
    let lastError;
    
    for (let attempt = 1; attempt <= maxRetries; attempt++) {
      try {
        return await operation();
      } catch (error) {
        lastError = error;
        
        // Check if it's a deadlock error
        if (this.isDeadlockError(error) && attempt < maxRetries) {
          console.warn(`Deadlock detected, retry ${attempt}/${maxRetries}`);
          await this.delay(delay * attempt); // Exponential backoff
          continue;
        }
        
        throw error;
      }
    }
    
    throw lastError;
  }

  static isDeadlockError(error) {
    // MySQL deadlock error code
    if (error.code === 'ER_LOCK_DEADLOCK') return true;
    
    // PostgreSQL deadlock error code
    if (error.code === '40P01') return true;
    
    // Check error message
    const deadlockMessages = [
      'deadlock detected',
      'lock wait timeout',
      'lock timeout'
    ];
    
    return deadlockMessages.some(msg => 
      error.message.toLowerCase().includes(msg)
    );
  }

  static delay(ms) {
    return new Promise(resolve => setTimeout(resolve, ms));
  }
}

// Usage
await DeadlockHandler.withRetry(async () => {
  return await transactionManager.createBusinessModelWithStakeholders(modelData, stakeholders);
});

Data Validation Issues

Problem: Invalid data being stored in the database

Symptoms:

Invalid JSON in database fields
Null values where required
Data type mismatches
Constraint violations

Solutions:

Implement comprehensive validation

const Joi = require('joi');

class DataValidator {
  static businessModelSchema = Joi.object({
    id: Joi.string().uuid().required(),
    name: Joi.string().min(1).max(255).required(),
    description: Joi.string().max(1000).allow(''),
    canvas_data: Joi.object({
      value_propositions: Joi.array().items(Joi.object({
        id: Joi.string().required(),
        text: Joi.string().required(),
        description: Joi.string().allow(''),
        validated: Joi.boolean().default(false)
      })),
      customer_segments: Joi.array().items(Joi.object({
        id: Joi.string().required(),
        text: Joi.string().required(),
        description: Joi.string().allow(''),
        validated: Joi.boolean().default(false)
      }))
      // ... other canvas sections
    }).required(),
    status: Joi.string().valid('draft', 'review', 'approved', 'active', 'archived').default('draft'),
    created_by: Joi.string().uuid().required(),
    organization_id: Joi.string().uuid().optional()
  });

  static stakeholderSchema = Joi.object({
    id: Joi.string().uuid().required(),
    business_model_id: Joi.string().uuid().required(),
    name: Joi.string().min(1).max(255).required(),
    type: Joi.string().valid('internal', 'external', 'customer', 'partner', 'investor', 'regulator').required(),
    influence_level: Joi.number().integer().min(1).max(5).required(),
    engagement_level: Joi.number().integer().min(1).max(5).required(),
    contact_info: Joi.object().optional()
  });

  static async validateBusinessModel(data) {
    try {
      const validated = await this.businessModelSchema.validateAsync(data, {
        abortEarly: false,
        stripUnknown: true
      });
      return { valid: true, data: validated, errors: [] };
    } catch (error) {
      return {
        valid: false,
        data: null,
        errors: error.details.map(detail => ({
          field: detail.path.join('.'),
          message: detail.message,
          value: detail.context.value
        }))
      };
    }
  }

  static async validateStakeholder(data) {
    try {
      const validated = await this.stakeholderSchema.validateAsync(data, {
        abortEarly: false,
        stripUnknown: true
      });
      return { valid: true, data: validated, errors: [] };
    } catch (error) {
      return {
        valid: false,
        data: null,
        errors: error.details.map(detail => ({
          field: detail.path.join('.'),
          message: detail.message,
          value: detail.context.value
        }))
      };
    }
  }
}

// Usage in API endpoints
app.post('/api/business-models', async (req, res) => {
  try {
    const validation = await DataValidator.validateBusinessModel(req.body);
    
    if (!validation.valid) {
      return res.status(400).json({
        error: 'Validation failed',
        details: validation.errors
      });
    }
    
    // Proceed with validated data
    const model = await bmadService.createBusinessModel(validation.data);
    res.status(201).json(model);
    
  } catch (error) {
    console.error('Business model creation failed:', error);
    res.status(500).json({ error: 'Internal server error' });
  }
});

---

Emergency Recovery Procedures

Database Recovery

#!/bin/bash
# Database emergency recovery script

echo "Starting database recovery..."

# 1. Stop application
echo "Stopping application..."
pm2 stop framework || pkill -f "node.*framework"

# 2. Backup current state
echo "Creating emergency backup..."
BACKUP_DIR="emergency_backup_$(date +%Y%m%d_%H%M%S)"
mkdir -p "$BACKUP_DIR"

# PostgreSQL backup
if command -v pg_dump &> /dev/null; then
    pg_dump -U "$DB_USER" -h "$DB_HOST" "$DB_NAME" > "$BACKUP_DIR/database_emergency.sql"
fi

# MySQL backup
if command -v mysqldump &> /dev/null; then
    mysqldump -u "$DB_USER" -p"$DB_PASSWORD" -h "$DB_HOST" "$DB_NAME" > "$BACKUP_DIR/database_emergency.sql"
fi

# SQLite backup
if [ -f "./data/framework.sqlite" ]; then
    cp "./data/framework.sqlite" "$BACKUP_DIR/framework_emergency.sqlite"
fi

# 3. Attempt automatic recovery
echo "Attempting automatic recovery..."
npm run db:repair || echo "Automatic repair failed"

# 4. Restore from latest backup if needed
if [ -f "backups/latest.sql" ]; then
    echo "Restoring from latest backup..."
    # Restore commands here
fi

# 5. Restart application
echo "Restarting application..."
npm start

echo "Recovery procedure completed. Check logs for details."

API Service Recovery

#!/bin/bash
# API service recovery script

echo "Starting API service recovery..."

# Check if service is responding
if curl -f http://localhost:3000/health > /dev/null 2>&1; then
    echo "API service is responding normally"
    exit 0
fi

# Graceful restart
echo "Attempting graceful restart..."
pkill -TERM -f "node.*framework"
sleep 10

# Force restart if needed
if pgrep -f "node.*framework" > /dev/null; then
    echo "Force killing API service..."
    pkill -KILL -f "node.*framework"
fi

# Clear locks and temporary files
rm -f tmp/pids/*.pid
rm -f tmp/locks/*

# Restart service
echo "Starting API service..."
npm start

# Wait and verify
sleep 30
if curl -f http://localhost:3000/health > /dev/null 2>&1; then
    echo "API service recovery successful"
else
    echo "API service recovery failed - manual intervention required"
    exit 1
fi

---

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