# Backend Expert Advisor

> Backend expert guidance for API/DB/Security/Architecture

- Skill: `diegosouzapw/backend-expert-advisor` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx skillmds@latest add diegosouzapw/backend-expert-advisor`
- Raw SKILL.md: https://api.skillmd.com/api/skills/diegosouzapw/backend-expert-advisor/raw
- Safety review: pending (external: skill-scanner PASS, skillspector CAUTION)
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- Author: diegosouzapw (https://skillmd.com/u/diegosouzapw)
- Updated: 2026-09-08
- Page: https://skillmd.com/skills/diegosouzapw/backend-expert-advisor

---


# Backend Expert Advisor

**Version**: 1.0.0  
**Last Updated**: 2025-01-24  
**Specialization**: Professional Backend Development (API/DB/Security/Architecture)  
**Target Audience**: Intermediate to Advanced Backend Developers  
**Language Support**: Korean + English

---

## 📖 Overview

Backend Expert Advisor is a comprehensive skill that provides expert-level guidance for backend development challenges. Built on 45+ research papers in prompt engineering and curated from authoritative sources including RFC standards, OWASP guidelines, and enterprise engineering blogs (Netflix, Uber, Kakao, Naver), this skill delivers production-ready solutions with security and performance best practices.

### Core Strengths
- **API Design**: REST/GraphQL/gRPC with industry standards (OpenAPI 3.1, RFC 9110)
- **Database Optimization**: Query tuning, indexing, sharding strategies for SQL/NoSQL
- **Security**: OWASP Top 10 compliance, authentication/authorization patterns
- **Architecture**: Microservices, event-driven, domain-driven design
- **Korean Regulations**: KISA, PIPC compliance for payment/personal data

### Knowledge Base
- **Official Documentation**: PostgreSQL, MongoDB, Redis, Kubernetes, Docker
- **Standards**: RFC (HTTP, OAuth), ISO (SQL), OWASP (Security)
- **Academic Research**: ACM SIGMOD, IEEE ICDE, USENIX papers
- **Industry Practices**: Netflix, Uber, Slack, Kakao, Naver engineering blogs
- **Korean Specifics**: 개인정보보호법, 전자금융거래법, CSAP guidelines

---

## 🎯 When to Use This Skill

Use Backend Expert Advisor when you need to:

### API Development
- Design RESTful APIs following best practices (versioning, pagination, HATEOAS)
- Implement GraphQL schemas with optimal resolver patterns
- Choose between REST/GraphQL/gRPC based on use case
- Set up API gateway patterns (Kong, AWS API Gateway, NGINX)
- Handle rate limiting and throttling strategies

### Database & Performance
- Optimize slow queries and design efficient indexes
- Choose between SQL and NoSQL databases for your use case
- Implement connection pooling and transaction management
- Design database sharding and partitioning strategies
- Set up caching layers (Redis, Memcached, CDN)

### Security & Authentication
- Implement OAuth 2.1 and OpenID Connect flows
- Design JWT-based authentication with refresh tokens
- Set up RBAC (Role-Based Access Control) or ABAC systems
- Prevent common vulnerabilities (SQL injection, XSS, CSRF)
- Comply with Korean regulations (개인정보보호법, KISA standards)

### Architecture & Scalability
- Design microservices architecture with proper boundaries
- Implement event-driven patterns (message queues, pub/sub)
- Choose between monolith, SOA, and microservices
- Design for horizontal scaling and load balancing
- Implement circuit breaker and saga patterns

### Monitoring & Operations
- Set up structured logging with ELK or Loki
- Implement metrics collection (Prometheus, Grafana)
- Design distributed tracing (OpenTelemetry, Jaeger)
- Create effective alerting rules and SLA monitoring
- Build CI/CD pipelines with Docker and Kubernetes

### Korean Market Specifics
- Integrate with Korean payment systems (KG이니시스, NHN KCP, 토스페이먼츠)
- Implement personal data protection (개인정보보호법 준수)
- Handle electronic financial transactions (전자금융거래법)
- Use government frameworks (전자정부 표준프레임워크)
- Deploy to Korean cloud platforms (Naver Cloud, KT Cloud)

---

## 💡 Core Capabilities

### 1. Architecture Design & Review
- Evaluate existing architecture and suggest improvements
- Design scalable, maintainable backend systems
- Identify bottlenecks and single points of failure
- Recommend appropriate patterns (microservices, event-driven, etc.)
- Create architecture decision records (ADRs)

### 2. API Design & Best Practices
- Generate OpenAPI 3.1 specifications
- Design consistent REST API naming and structure
- Implement versioning strategies (URL, header, content negotiation)
- Set up pagination, filtering, and sorting patterns
- Handle error responses with RFC 7807 Problem Details

### 3. Database Optimization
- Analyze and optimize slow queries
- Design indexes for specific query patterns
- Recommend database schema improvements
- Suggest sharding/partitioning strategies
- Provide ORM best practices (Prisma, TypeORM, SQLAlchemy)

### 4. Security Hardening
- Audit code for OWASP Top 10 vulnerabilities
- Design secure authentication flows (OAuth 2.1, OIDC)
- Implement proper token management and rotation
- Set up rate limiting and DDoS protection
- Encrypt sensitive data at rest and in transit

### 5. Performance Tuning
- Identify and resolve N+1 query problems
- Implement multi-level caching strategies
- Optimize API response times
- Design asynchronous processing patterns
- Profile and optimize resource usage

### 6. DevOps & Deployment
- Create Dockerfiles following best practices
- Design Kubernetes deployments with proper resource limits
- Set up CI/CD pipelines (GitHub Actions, GitLab CI)
- Implement blue-green or canary deployments
- Configure monitoring and logging infrastructure

### 7. Code Review & Quality
- Review backend code for common issues
- Suggest refactoring opportunities
- Identify code smells and anti-patterns
- Recommend testing strategies (unit, integration, e2e)
- Ensure adherence to SOLID principles

### 8. Korean Compliance & Integration
- Guide personal data protection implementation
- Integrate payment gateways (Korean providers)
- Handle resident registration numbers securely
- Comply with cloud security standards (CSAP)
- Use Korean-specific APIs (공공데이터포털, etc.)

---

## 📚 Usage Guide

### Quick Start

**Basic Query Format**
```
"I need help with [specific problem].
 
Context:
- Tech stack: [e.g., Node.js + PostgreSQL + Redis]
- Current issue: [describe the problem]
- Constraints: [performance requirements, regulations, etc.]"
```

**Example**
```
"I need help optimizing a slow API endpoint.

Context:
- Tech stack: Express.js + PostgreSQL + Redis
- Current issue: /users endpoint takes 3-5 seconds
- The query joins 4 tables and returns 10,000+ rows
- Need to reduce to under 500ms"
```

### Advanced Usage Patterns

#### Pattern 1: Architecture Review
```
"Review my microservices architecture:

Services:
1. User Service (Node.js + MongoDB)
2. Product Service (Java + PostgreSQL)
3. Order Service (Python + PostgreSQL)
4. Payment Service (Go + MySQL)

Communication: REST APIs
Message Queue: RabbitMQ for async events

Issues:
- Frequent timeouts between services
- Difficulty maintaining data consistency
- Deployment takes 30+ minutes

Suggest improvements with Korean cloud deployment in mind."
```

#### Pattern 2: Security Audit
```
"Audit my authentication system for security issues:

[PASTE YOUR CODE OR ARCHITECTURE DIAGRAM]

Requirements:
- OAuth 2.1 compliance
- JWT with refresh tokens
- OWASP Top 10 compliance
- 개인정보보호법 준수 (for Korean users)"
```

#### Pattern 3: Database Optimization
```
"Optimize this query:

```sql
SELECT u.*, p.*, o.* 
FROM users u
LEFT JOIN profiles p ON u.id = p.user_id
LEFT JOIN orders o ON u.id = o.user_id
WHERE u.created_at > '2024-01-01'
ORDER BY u.created_at DESC
LIMIT 100;
```

Current performance:
- Execution time: 4.2 seconds
- Rows scanned: 1.2M
- Database: PostgreSQL 15

Target: <500ms"
```

#### Pattern 4: API Design
```
"Design a REST API for a blog system with:

Entities:
- Users (authentication required)
- Posts (public + private)
- Comments (nested, max 3 levels)
- Categories & Tags

Requirements:
- RESTful design
- Pagination
- Filtering by category/tag/date
- Search functionality
- Rate limiting (100 req/min per user)

Generate OpenAPI 3.1 spec."
```

---

## 🛠️ Framework & Tool Specific Guides

### Node.js + Express.js
```javascript
// ✅ Best Practice: Async Error Handling
const asyncHandler = (fn) => (req, res, next) => {
  Promise.resolve(fn(req, res, next)).catch(next);
};

app.get('/users/:id', asyncHandler(async (req, res) => {
  const user = await User.findById(req.params.id);
  if (!user) throw new NotFoundError('User not found');
  res.json(user);
}));

// ✅ Best Practice: Structured Logging
const logger = winston.createLogger({
  format: winston.format.combine(
    winston.format.timestamp(),
    winston.format.json()
  ),
  transports: [new winston.transports.Console()]
});

app.use((req, res, next) => {
  logger.info('Request', {
    method: req.method,
    path: req.path,
    ip: req.ip,
    userId: req.user?.id
  });
  next();
});

// ✅ Best Practice: Connection Pooling
const pool = new Pool({
  host: process.env.DB_HOST,
  database: process.env.DB_NAME,
  max: 20, // max connections
  idleTimeoutMillis: 30000,
  connectionTimeoutMillis: 2000,
});
```

### Python + FastAPI
```python
# ✅ Best Practice: Dependency Injection
from fastapi import FastAPI, Depends
from sqlalchemy.orm import Session

def get_db():
    db = SessionLocal()
    try:
        yield db
    finally:
        db.close()

@app.get("/users/{user_id}")
async def get_user(
    user_id: int,
    db: Session = Depends(get_db),
    current_user: User = Depends(get_current_user)
):
    user = db.query(User).filter(User.id == user_id).first()
    if not user:
        raise HTTPException(status_code=404, detail="User not found")
    return user

# ✅ Best Practice: Pydantic Validation
from pydantic import BaseModel, EmailStr, Field

class UserCreate(BaseModel):
    email: EmailStr
    password: str = Field(..., min_length=8)
    name: str = Field(..., max_length=100)
    
    class Config:
        json_schema_extra = {
            "example": {
                "email": "user@example.com",
                "password": "SecurePass123!",
                "name": "John Doe"
            }
        }
```

### Java + Spring Boot
```java
// ✅ Best Practice: Service Layer Pattern
@Service
@Transactional
public class UserService {
    
    private final UserRepository userRepository;
    private final PasswordEncoder passwordEncoder;
    
    @Autowired
    public UserService(UserRepository userRepository, 
                       PasswordEncoder passwordEncoder) {
        this.userRepository = userRepository;
        this.passwordEncoder = passwordEncoder;
    }
    
    public UserDTO createUser(UserCreateRequest request) {
        // Validation
        if (userRepository.existsByEmail(request.getEmail())) {
            throw new DuplicateEmailException("Email already exists");
        }
        
        // Business logic
        User user = User.builder()
            .email(request.getEmail())
            .password(passwordEncoder.encode(request.getPassword()))
            .name(request.getName())
            .build();
        
        User savedUser = userRepository.save(user);
        return UserDTO.from(savedUser);
    }
}

// ✅ Best Practice: Global Exception Handler
@RestControllerAdvice
public class GlobalExceptionHandler {
    
    @ExceptionHandler(ResourceNotFoundException.class)
    public ResponseEntity<ErrorResponse> handleNotFound(
            ResourceNotFoundException ex) {
        ErrorResponse error = ErrorResponse.builder()
            .status(HttpStatus.NOT_FOUND.value())
            .message(ex.getMessage())
            .timestamp(LocalDateTime.now())
            .build();
        return new ResponseEntity<>(error, HttpStatus.NOT_FOUND);
    }
}
```

---

## 📊 Examples

### Example 1: API Rate Limiting Implementation

**Scenario**: Prevent API abuse with Redis-based rate limiting

**Problem**:
- Public API receiving 10,000+ requests per second
- Need to limit to 100 requests per minute per user
- Must return proper HTTP 429 status with retry-after header

**Solution** (Node.js + Express + Redis):

```javascript
const Redis = require('ioredis');
const redis = new Redis(process.env.REDIS_URL);

// Sliding window rate limiter
async function rateLimiter(req, res, next) {
  const userId = req.user?.id || req.ip;
  const key = `rate_limit:${userId}`;
  const limit = 100;
  const window = 60; // seconds
  
  try {
    const current = await redis.incr(key);
    
    if (current === 1) {
      await redis.expire(key, window);
    }
    
    if (current > limit) {
      const ttl = await redis.ttl(key);
      res.set('Retry-After', ttl);
      return res.status(429).json({
        error: 'Too Many Requests',
        message: `Rate limit exceeded. Try again in ${ttl} seconds.`,
        retryAfter: ttl
      });
    }
    
    res.set('X-RateLimit-Limit', limit);
    res.set('X-RateLimit-Remaining', limit - current);
    next();
  } catch (error) {
    console.error('Rate limiter error:', error);
    next(); // Fail open
  }
}

// Apply to all routes
app.use('/api/', rateLimiter);
```

**Result**:
- Reduced server load by 70%
- Proper HTTP 429 responses
- User-friendly retry-after headers
- Fail-open design (continues if Redis is down)

---

### Example 2: N+1 Query Optimization

**Scenario**: Optimize blog post listing with author and comment counts

**Problem** (Bad Code):
```python
# ❌ N+1 Query Problem
@app.get("/posts")
async def list_posts(db: Session = Depends(get_db)):
    posts = db.query(Post).limit(20).all()
    
    result = []
    for post in posts:
        # N additional queries!
        author = db.query(User).filter(User.id == post.author_id).first()
        comment_count = db.query(Comment).filter(
            Comment.post_id == post.id
        ).count()
        
        result.append({
            "id": post.id,
            "title": post.title,
            "author": author.name,
            "comment_count": comment_count
        })
    
    return result

# Query count: 1 (posts) + 20 (authors) + 20 (counts) = 41 queries!
```

**Solution** (Optimized):
```python
# ✅ Optimized with Eager Loading
from sqlalchemy.orm import joinedload, selectinload
from sqlalchemy import func

@app.get("/posts")
async def list_posts(db: Session = Depends(get_db)):
    # Single query with joins
    posts = db.query(
        Post.id,
        Post.title,
        Post.created_at,
        User.name.label('author_name'),
        func.count(Comment.id).label('comment_count')
    ).join(
        User, Post.author_id == User.id
    ).outerjoin(
        Comment, Post.id == Comment.post_id
    ).group_by(
        Post.id, User.name
    ).limit(20).all()
    
    return [
        {
            "id": post.id,
            "title": post.title,
            "author": post.author_name,
            "comment_count": post.comment_count
        }
        for post in posts
    ]

# Query count: 1 query total!
# Performance: 41 queries (2.3s) → 1 query (45ms)
```

**Key Techniques**:
- Use `JOIN` instead of separate queries
- Aggregate functions (`COUNT`) in single query
- Proper indexing on foreign keys
- Result: **95% faster** (2.3s → 45ms)

---

### Example 3: Secure JWT Authentication (OAuth 2.1 Compliant)

**Scenario**: Implement secure authentication with refresh token rotation

**Requirements**:
- JWT access tokens (15 min expiry)
- Refresh tokens (7 days, rotation on use)
- Secure cookie storage (HttpOnly, Secure, SameSite)
- CSRF protection
- 개인정보보호법 준수 (Korean regulation)

**Implementation** (Node.js + Express):

```javascript
const jwt = require('jsonwebtoken');
const bcrypt = require('bcrypt');
const crypto = require('crypto');

// Token generation
function generateTokens(userId) {
  const accessToken = jwt.sign(
    { userId, type: 'access' },
    process.env.ACCESS_TOKEN_SECRET,
    { expiresIn: '15m' }
  );
  
  const refreshToken = jwt.sign(
    { userId, type: 'refresh', jti: crypto.randomUUID() },
    process.env.REFRESH_TOKEN_SECRET,
    { expiresIn: '7d' }
  );
  
  return { accessToken, refreshToken };
}

// Login endpoint
app.post('/auth/login', async (req, res) => {
  const { email, password } = req.body;
  
  // 1. Find user
  const user = await User.findOne({ email });
  if (!user) {
    return res.status(401).json({ error: 'Invalid credentials' });
  }
  
  // 2. Verify password
  const isValid = await bcrypt.compare(password, user.password);
  if (!isValid) {
    return res.status(401).json({ error: 'Invalid credentials' });
  }
  
  // 3. Generate tokens
  const { accessToken, refreshToken } = generateTokens(user.id);
  
  // 4. Store refresh token (with rotation)
  await RefreshToken.create({
    userId: user.id,
    token: refreshToken,
    expiresAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000)
  });
  
  // 5. Set secure cookies
  res.cookie('refreshToken', refreshToken, {
    httpOnly: true,
    secure: true, // HTTPS only
    sameSite: 'strict',
    maxAge: 7 * 24 * 60 * 60 * 1000 // 7 days
  });
  
  // 6. Return access token
  res.json({
    accessToken,
    expiresIn: 900, // 15 minutes
    tokenType: 'Bearer'
  });
});

// Token refresh endpoint
app.post('/auth/refresh', async (req, res) => {
  const { refreshToken } = req.cookies;
  
  if (!refreshToken) {
    return res.status(401).json({ error: 'Refresh token required' });
  }
  
  try {
    // 1. Verify token
    const payload = jwt.verify(
      refreshToken,
      process.env.REFRESH_TOKEN_SECRET
    );
    
    // 2. Check if token exists in DB
    const storedToken = await RefreshToken.findOne({
      userId: payload.userId,
      token: refreshToken
    });
    
    if (!storedToken) {
      // Token reuse detected - possible attack!
      await RefreshToken.deleteMany({ userId: payload.userId });
      return res.status(401).json({ error: 'Invalid token' });
    }
    
    // 3. Rotate refresh token (delete old, create new)
    await RefreshToken.deleteOne({ _id: storedToken._id });
    
    const tokens = generateTokens(payload.userId);
    
    await RefreshToken.create({
      userId: payload.userId,
      token: tokens.refreshToken,
      expiresAt: new Date(Date.now() + 7 * 24 * 60 * 60 * 1000)
    });
    
    // 4. Set new cookie
    res.cookie('refreshToken', tokens.refreshToken, {
      httpOnly: true,
      secure: true,
      sameSite: 'strict',
      maxAge: 7 * 24 * 60 * 60 * 1000
    });
    
    res.json({
      accessToken: tokens.accessToken,
      expiresIn: 900
    });
    
  } catch (error) {
    return res.status(401).json({ error: 'Invalid token' });
  }
});

// Authentication middleware
async function authenticate(req, res, next) {
  const authHeader = req.headers.authorization;
  
  if (!authHeader?.startsWith('Bearer ')) {
    return res.status(401).json({ error: 'No token provided' });
  }
  
  const token = authHeader.substring(7);
  
  try {
    const payload = jwt.verify(token, process.env.ACCESS_TOKEN_SECRET);
    req.user = { id: payload.userId };
    next();
  } catch (error) {
    return res.status(401).json({ error: 'Invalid or expired token' });
  }
}

// Protected route example
app.get('/api/profile', authenticate, async (req, res) => {
  const user = await User.findById(req.user.id);
  res.json(user);
});
```

**Security Features**:
- ✅ Short-lived access tokens (15 min)
- ✅ Refresh token rotation (prevents replay attacks)
- ✅ HttpOnly cookies (prevents XSS)
- ✅ Secure & SameSite flags (prevents CSRF)
- ✅ Token reuse detection (invalidates all tokens)
- ✅ Database-backed refresh tokens (revocable)

**Korean Compliance**:
- 개인정보 (이메일) 암호화 저장
- 로그인 시도 로깅 (접근 기록)
- 비밀번호 bcrypt 해싱 (단방향 암호화)

---

### Example 4: Microservices Circuit Breaker Pattern

**Scenario**: Prevent cascading failures between microservices

**Problem**:
- Order Service calls Payment Service
- Payment Service occasionally times out (3-5% of requests)
- Timeouts cause Order Service to hang, affecting all users

**Solution** (Node.js with `opossum` library):

```javascript
const CircuitBreaker = require('opossum');
const axios = require('axios');

// Payment service client with circuit breaker
function createPaymentClient() {
  // Base function to call payment service
  async function processPayment(orderId, amount) {
    const response = await axios.post(
      'http://payment-service/api/payments',
      { orderId, amount },
      { timeout: 3000 } // 3 second timeout
    );
    return response.data;
  }
  
  // Circuit breaker options
  const options = {
    timeout: 3000, // If function takes > 3s, trigger failure
    errorThresholdPercentage: 50, // Open circuit at 50% failure rate
    resetTimeout: 30000, // Try again after 30 seconds
    rollingCountTimeout: 10000, // 10 second window for stats
    rollingCountBuckets: 10, // 10 buckets (1 second each)
    
    // Fallback function
    fallback: (orderId, amount) => {
      console.log(`Payment service unavailable, queuing order ${orderId}`);
      // Queue for later processing
      return messageQueue.send('payment-retry', { orderId, amount });
    }
  };
  
  const breaker = new CircuitBreaker(processPayment, options);
  
  // Event listeners
  breaker.on('open', () => {
    console.error('Circuit opened - payment service is down');
    // Alert monitoring system
    metrics.increment('circuit_breaker.payment.opened');
  });
  
  breaker.on('halfOpen', () => {
    console.log('Circuit half-open - testing payment service');
    metrics.increment('circuit_breaker.payment.half_open');
  });
  
  breaker.on('close', () => {
    console.log('Circuit closed - payment service recovered');
    metrics.increment('circuit_breaker.payment.closed');
  });
  
  breaker.on('fallback', (result) => {
    console.log('Fallback executed - payment queued');
    metrics.increment('circuit_breaker.payment.fallback');
  });
  
  return breaker;
}

// Usage in Order Service
const paymentClient = createPaymentClient();

app.post('/api/orders', async (req, res) => {
  try {
    // Create order
    const order = await Order.create({
      userId: req.user.id,
      items: req.body.items,
      total: req.body.total
    });
    
    // Process payment with circuit breaker
    const payment = await paymentClient.fire(order.id, order.total);
    
    if (payment.status === 'queued') {
      // Fallback was triggered
      return res.status(202).json({
        message: 'Order received, payment processing',
        orderId: order.id,
        status: 'pending'
      });
    }
    
    // Success
    await order.update({ status: 'confirmed', paymentId: payment.id });
    
    res.status(201).json({
      message: 'Order confirmed',
      orderId: order.id,
      status: 'confirmed'
    });
    
  } catch (error) {
    console.error('Order creation failed:', error);
    res.status(500).json({ error: 'Order processing failed' });
  }
});
```

**Circuit Breaker States**:

```
CLOSED (Normal)
   ↓ (50% errors in 10s window)
OPEN (Reject all requests)
   ↓ (After 30 seconds)
HALF-OPEN (Allow 1 request to test)
   ↓ (If successful)
CLOSED (Resume normal)
```

**Benefits**:
- Prevents cascading failures
- Automatic recovery detection
- Graceful degradation (fallback to queue)
- Real-time metrics and alerting
- User experience maintained (202 Accepted vs 500 Error)

**Monitoring Dashboard** (Grafana):
```
Circuit Breaker Status:
- State: CLOSED ✅ / OPEN ❌ / HALF-OPEN ⚠️
- Success Rate: 95.2%
- Average Response Time: 245ms
- Fallback Triggered: 12 times (last hour)
```

---

### Example 5: Database Connection Pooling (PostgreSQL)

**Scenario**: Optimize database connections for high-concurrency API

**Problem**:
- API handles 1000+ concurrent requests
- Each request creates new DB connection
- Connection limit reached (max 100)
- "Too many connections" errors

**Solution** (Node.js + `pg` library):

```javascript
const { Pool } = require('pg');

// ✅ Proper connection pool configuration
const pool = new Pool({
  host: process.env.DB_HOST,
  port: process.env.DB_PORT,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,
  
  // Pool configuration
  max: 20, // Maximum number of connections
  idleTimeoutMillis: 30000, // Close idle connections after 30s
  connectionTimeoutMillis: 2000, // Timeout when acquiring connection
  
  // Connection validation
  query_timeout: 10000, // Timeout individual queries after 10s
  statement_timeout: 10000,
  
  // SSL for production
  ssl: process.env.NODE_ENV === 'production' ? {
    rejectUnauthorized: false
  } : false
});

// Health check
pool.on('connect', (client) => {
  console.log('New database connection established');
});

pool.on('error', (err, client) => {
  console.error('Database pool error:', err);
  // Alert monitoring system
});

// ✅ Query helper with automatic connection management
async function query(text, params) {
  const start = Date.now();
  
  try {
    const result = await pool.query(text, params);
    const duration = Date.now() - start;
    
    // Log slow queries
    if (duration > 1000) {
      console.warn('Slow query detected', {
        duration,
        query: text,
        params
      });
    }
    
    return result;
  } catch (error) {
    console.error('Query error:', {
      query: text,
      params,
      error: error.message
    });
    throw error;
  }
}

// ✅ Transaction helper
async function transaction(callback) {
  const client = await pool.connect();
  
  try {
    await client.query('BEGIN');
    const result = await callback(client);
    await client.query('COMMIT');
    return result;
  } catch (error) {
    await client.query('ROLLBACK');
    throw error;
  } finally {
    client.release(); // Return to pool
  }
}

// Usage examples

// Simple query
app.get('/users/:id', async (req, res) => {
  const result = await query(
    'SELECT * FROM users WHERE id = $1',
    [req.params.id]
  );
  
  if (result.rows.length === 0) {
    return res.status(404).json({ error: 'User not found' });
  }
  
  res.json(result.rows[0]);
});

// Transaction example
app.post('/orders', async (req, res) => {
  try {
    const order = await transaction(async (client) => {
      // Insert order
      const orderResult = await client.query(
        'INSERT INTO orders (user_id, total) VALUES ($1, $2) RETURNING *',
        [req.user.id, req.body.total]
      );
      
      // Insert order items
      for (const item of req.body.items) {
        await client.query(
          'INSERT INTO order_items (order_id, product_id, quantity) VALUES ($1, $2, $3)',
          [orderResult.rows[0].id, item.productId, item.quantity]
        );
      }
      
      // Update inventory
      for (const item of req.body.items) {
        await client.query(
          'UPDATE products SET stock = stock - $1 WHERE id = $2',
          [item.quantity, item.productId]
        );
      }
      
      return orderResult.rows[0];
    });
    
    res.status(201).json(order);
  } catch (error) {
    console.error('Transaction failed:', error);
    res.status(500).json({ error: 'Order creation failed' });
  }
});

// Graceful shutdown
process.on('SIGTERM', async () => {
  console.log('SIGTERM received, closing database pool');
  await pool.end();
  process.exit(0);
});
```

**Performance Comparison**:
```
Without Connection Pool:
- Concurrent requests: 100
- Connection creation time: ~50ms each
- Total overhead: 5 seconds
- Errors: "Too many connections"

With Connection Pool (max: 20):
- Concurrent requests: 100
- Connection reuse: Instant
- Total overhead: Negligible
- Errors: None
- Response time: 50ms → 5ms (90% improvement)
```

**Monitoring Metrics** (Prometheus):
```javascript
const metrics = {
  poolSize: new Gauge({ name: 'db_pool_size', help: 'Current pool size' }),
  poolIdle: new Gauge({ name: 'db_pool_idle', help: 'Idle connections' }),
  poolWaiting: new Gauge({ name: 'db_pool_waiting', help: 'Waiting clients' })
};

setInterval(() => {
  metrics.poolSize.set(pool.totalCount);
  metrics.poolIdle.set(pool.idleCount);
  metrics.poolWaiting.set(pool.waitingCount);
}, 5000);
```

---

### Example 6: Korean Payment Integration (토스페이먼츠)

**Scenario**: Integrate Toss Payments with proper error handling and compliance

**Requirements**:
- 전자금융거래법 준수
- PCI DSS compliance (no card data storage)
- Webhook verification
- Idempotency for duplicate payments

**Implementation**:

```javascript
const axios = require('axios');
const crypto = require('crypto');

// Toss Payments client
class TossPaymentsClient {
  constructor() {
    this.secretKey = process.env.TOSS_SECRET_KEY;
    this.clientKey = process.env.TOSS_CLIENT_KEY;
    this.baseURL = process.env.NODE_ENV === 'production'
      ? 'https://api.tosspayments.com'
      : 'https://api-sandbox.tosspayments.com';
  }
  
  // Create payment
  async createPayment(orderId, amount, orderName, customerEmail) {
    // Generate idempotency key
    const idempotencyKey = crypto.createHash('sha256')
      .update(`${orderId}-${Date.now()}`)
      .digest('hex');
    
    try {
      const response = await axios.post(
        `${this.baseURL}/v1/payments`,
        {
          orderId,
          amount,
          orderName,
          customerEmail,
          successUrl: `${process.env.APP_URL}/payments/success`,
          failUrl: `${process.env.APP_URL}/payments/fail`
        },
        {
          headers: {
            'Authorization': `Basic ${Buffer.from(this.secretKey + ':').toString('base64')}`,
            'Content-Type': 'application/json',
            'Idempotency-Key': idempotencyKey
          }
        }
      );
      
      return response.data;
    } catch (error) {
      console.error('Toss payment creation failed:', error.response?.data);
      throw new Error(`Payment failed: ${error.response?.data?.message}`);
    }
  }
  
  // Confirm payment (after user authorization)
  async confirmPayment(paymentKey, orderId, amount) {
    try {
      const response = await axios.post(
        `${this.baseURL}/v1/payments/confirm`,
        {
          paymentKey,
          orderId,
          amount
        },
        {
          headers: {
            'Authorization': `Basic ${Buffer.from(this.secretKey + ':').toString('base64')}`,
            'Content-Type': 'application/json'
          }
        }
      );
      
      return response.data;
    } catch (error) {
      console.error('Payment confirmation failed:', error.response?.data);
      throw error;
    }
  }
  
  // Cancel payment
  async cancelPayment(paymentKey, cancelReason) {
    try {
      const response = await axios.post(
        `${this.baseURL}/v1/payments/${paymentKey}/cancel`,
        { cancelReason },
        {
          headers: {
            'Authorization': `Basic ${Buffer.from(this.secretKey + ':').toString('base64')}`,
            'Content-Type': 'application/json'
          }
        }
      );
      
      return response.data;
    } catch (error) {
      console.error('Payment cancellation failed:', error.response?.data);
      throw error;
    }
  }
  
  // Verify webhook signature
  verifyWebhook(signature, body) {
    const computedSignature = crypto
      .createHmac('sha256', this.secretKey)
      .update(JSON.stringify(body))
      .digest('hex');
    
    return signature === computedSignature;
  }
}

// API endpoints
const toss = new TossPaymentsClient();

// Step 1: Create payment
app.post('/api/payments/create', async (req, res) => {
  try {
    const { orderId, amount, orderName } = req.body;
    
    // Validate order
    const order = await Order.findById(orderId);
    if (!order) {
      return res.status(404).json({ error: 'Order not found' });
    }
    
    if (order.status !== 'pending') {
      return res.status(400).json({ error: 'Order already processed' });
    }
    
    // Create payment
    const payment = await toss.createPayment(
      orderId,
      amount,
      orderName,
      req.user.email
    );
    
    // Store payment info
    await Payment.create({
      orderId,
      paymentKey: payment.paymentKey,
      amount,
      status: 'ready',
      method: payment.method
    });
    
    res.json({
      paymentKey: payment.paymentKey,
      checkoutUrl: payment.checkoutUrl
    });
    
  } catch (error) {
    console.error('Payment creation error:', error);
    res.status(500).json({ error: error.message });
  }
});

// Step 2: Success callback (user redirected here after payment)
app.get('/payments/success', async (req, res) => {
  const { paymentKey, orderId, amount } = req.query;
  
  try {
    // Confirm payment with Toss
    const result = await toss.confirmPayment(paymentKey, orderId, amount);
    
    // Update database
    await Payment.updateOne(
      { paymentKey },
      {
        status: 'done',
        approvedAt: new Date(result.approvedAt),
        receipt: result.receipt
      }
    );
    
    await Order.updateOne(
      { id: orderId },
      { status: 'paid' }
    );
    
    // Log for 전자금융거래법 compliance
    await PaymentLog.create({
      orderId,
      paymentKey,
      action: 'confirmed',
      amount,
      timestamp: new Date(),
      userIp: req.ip,
      userAgent: req.get('user-agent')
    });
    
    res.redirect(`/orders/${orderId}/success`);
    
  } catch (error) {
    console.error('Payment confirmation error:', error);
    res.redirect(`/orders/${orderId}/fail`);
  }
});

// Step 3: Webhook handler (for async notifications)
app.post('/webhooks/toss', async (req, res) => {
  const signature = req.headers['toss-signature'];
  
  // Verify webhook
  if (!toss.verifyWebhook(signature, req.body)) {
    console.error('Invalid webhook signature');
    return res.status(401).json({ error: 'Invalid signature' });
  }
  
  const { eventType, data } = req.body;
  
  try {
    switch (eventType) {
      case 'PAYMENT_CONFIRMED':
        await handlePaymentConfirmed(data);
        break;
      
      case 'PAYMENT_CANCELED':
        await handlePaymentCanceled(data);
        break;
      
      case 'PAYMENT_FAILED':
        await handlePaymentFailed(data);
        break;
    }
    
    res.json({ success: true });
  } catch (error) {
    console.error('Webhook processing error:', error);
    res.status(500).json({ error: error.message });
  }
});

async function handlePaymentConfirmed(data) {
  await Payment.updateOne(
    { paymentKey: data.paymentKey },
    { status: 'done', webhookReceived: true }
  );
  
  // Send confirmation email
  await emailService.send({
    to: data.customerEmail,
    subject: '결제가 완료되었습니다',
    template: 'payment-confirmed',
    data: {
      orderName: data.orderName,
      amount: data.amount,
      approvedAt: data.approvedAt
    }
  });
}

async function handlePaymentCanceled(data) {
  await Payment.updateOne(
    { paymentKey: data.paymentKey },
    { status: 'canceled', cancelReason: data.cancelReason }
  );
  
  await Order.updateOne(
    { id: data.orderId },
    { status: 'canceled' }
  );
}

async function handlePaymentFailed(data) {
  await Payment.updateOne(
    { paymentKey: data.paymentKey },
    { status: 'failed', failReason: data.failReason }
  );
}
```

**Compliance Checklist**:
- ✅ 카드정보 미저장 (PCI DSS)
- ✅ 거래기록 5년 보관 (전자금융거래법 제22조)
- ✅ 사용자 IP/User-Agent 로깅
- ✅ Webhook 서명 검증
- ✅ Idempotency 키 사용 (중복 결제 방지)
- ✅ HTTPS 필수
- ✅ 결제 취소 기능 제공

---

## 🔒 Security Best Practices

### OWASP Top 10 Prevention

#### 1. Broken Access Control
```javascript
// ❌ Bad: No authorization check
app.get('/api/users/:id', async (req, res) => {
  const user = await User.findById(req.params.id);
  res.json(user); // Anyone can access any user!
});

// ✅ Good: Proper authorization
app.get('/api/users/:id', authenticate, async (req, res) => {
  if (req.user.id !== req.params.id && !req.user.isAdmin) {
    return res.status(403).json({ error: 'Access denied' });
  }
  
  const user = await User.findById(req.params.id);
  res.json(user);
});
```

#### 2. SQL Injection Prevention
```javascript
// ❌ Bad: String concatenation
const query = `SELECT * FROM users WHERE email = '${req.body.email}'`;
// Vulnerable to: ' OR '1'='1

// ✅ Good: Parameterized queries
const query = 'SELECT * FROM users WHERE email = $1';
const result = await pool.query(query, [req.body.email]);
```

#### 3. XSS Prevention
```javascript
// ✅ Content Security Policy
app.use((req, res, next) => {
  res.setHeader(
    'Content-Security-Policy',
    "default-src 'self'; script-src 'self'; style-src 'self' 'unsafe-inline'"
  );
  next();
});

// ✅ Sanitize user input
const sanitizeHtml = require('sanitize-html');

app.post('/posts', async (req, res) => {
  const cleanContent = sanitizeHtml(req.body.content, {
    allowedTags: ['b', 'i', 'em', 'strong', 'a'],
    allowedAttributes: { 'a': ['href'] }
  });
  
  await Post.create({ content: cleanContent });
});
```

#### 4. CSRF Prevention
```javascript
const csrf = require('csurf');
const csrfProtection = csrf({ cookie: true });

// All state-changing operations
app.post('/api/orders', csrfProtection, async (req, res) => {
  // CSRF token automatically validated
  // ...
});

// Provide token to frontend
app.get('/api/csrf-token', csrfProtection, (req, res) => {
  res.json({ csrfToken: req.csrfToken() });
});
```

---

### Secure Password Storage (Korean Standards)

```javascript
const bcrypt = require('bcrypt');

// ✅ KISA 권장: bcrypt with salt rounds 12+
async function hashPassword(password) {
  // Validation
  if (password.length < 10) {
    throw new Error('Password must be at least 10 characters');
  }
  
  // Check complexity (영문+숫자+특수문자)
  const hasLetter = /[a-zA-Z]/.test(password);
  const hasNumber = /[0-9]/.test(password);
  const hasSpecial = /[!@#$%^&*]/.test(password);
  
  if (!(hasLetter && hasNumber && hasSpecial)) {
    throw new Error('Password must contain letters, numbers, and special characters');
  }
  
  // Hash with bcrypt
  const saltRounds = 12; // KISA 권장
  return await bcrypt.hash(password, saltRounds);
}

async function verifyPassword(password, hash) {
  return await bcrypt.compare(password, hash);
}

// Additional: Password change tracking (for compliance)
async function changePassword(userId, oldPassword, newPassword) {
  const user = await User.findById(userId);
  
  // Verify old password
  const isValid = await verifyPassword(oldPassword, user.passwordHash);
  if (!isValid) {
    throw new Error('Current password is incorrect');
  }
  
  // Check password history (prevent reuse)
  const recentPasswords = await PasswordHistory.find({ userId })
    .sort({ createdAt: -1 })
    .limit(3);
  
  for (const record of recentPasswords) {
    if (await bcrypt.compare(newPassword, record.passwordHash)) {
      throw new Error('Cannot reuse recent passwords');
    }
  }
  
  // Hash new password
  const newHash = await hashPassword(newPassword);
  
  // Update user
  await User.updateOne({ _id: userId }, { passwordHash: newHash });
  
  // Save to history
  await PasswordHistory.create({
    userId,
    passwordHash: newHash,
    changedAt: new Date()
  });
  
  // Log for audit (개인정보보호법)
  await AuditLog.create({
    userId,
    action: 'password_changed',
    ip: req.ip,
    timestamp: new Date()
  });
}
```

---

## 📈 Performance Optimization Checklist

### API Response Time Targets
```
Target Response Times:
- Simple queries (1 table): < 50ms
- Complex queries (3+ tables): < 200ms
- API Gateway: < 10ms overhead
- 95th percentile: < 500ms
- 99th percentile: < 1s
```

### Optimization Strategies

#### 1. Database Indexing
```sql
-- ✅ Index for common queries
CREATE INDEX idx_users_email ON users(email);
CREATE INDEX idx_posts_author_created ON posts(author_id, created_at DESC);

-- ✅ Partial index for filtered queries
CREATE INDEX idx_active_users ON users(email) WHERE status = 'active';

-- ✅ Covering index (includes all query columns)
CREATE INDEX idx_posts_list ON posts(author_id, created_at DESC) 
INCLUDE (title, excerpt);

-- ❌ Avoid over-indexing
-- Too many indexes slow down writes
-- Rule of thumb: 3-5 indexes per table maximum
```

#### 2. Caching Strategy
```javascript
const redis = require('redis');
const client = redis.createClient();

// Multi-level caching
async function getUser(userId) {
  // L1: In-memory cache (fastest)
  if (memoryCache.has(userId)) {
    return memoryCache.get(userId);
  }
  
  // L2: Redis cache (fast)
  const cached = await client.get(`user:${userId}`);
  if (cached) {
    const user = JSON.parse(cached);
    memoryCache.set(userId, user); // Populate L1
    return user;
  }
  


…(truncated)