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

# ✅ 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

// ✅ 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):

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):

# ❌ 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):

# ✅ 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):

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):

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):

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):

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:

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

// ❌ 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

// ❌ 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

// ✅ 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

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)

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

-- ✅ 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

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)