Senior Backend

Senior Backend Engineer

Backend development patterns, API design, database optimization, and security practices.

---

Quick Start

# Generate API routes from OpenAPI spec
python scripts/api_scaffolder.py openapi.yaml --framework express --output src/routes/

# Analyze database schema and generate migrations
python scripts/database_migration_tool.py --connection postgres://localhost/mydb --analyze

# Load test an API endpoint
python scripts/api_load_tester.py https://api.example.com/users --concurrency 50 --duration 30

---

Tools Overview

1. API Scaffolder

Generates API route handlers, middleware, and OpenAPI specifications from schema definitions.

Input: OpenAPI spec (YAML/JSON) or database schema Output: Route handlers, validation middleware, TypeScript types

Usage:

# Generate Express routes from OpenAPI spec
python scripts/api_scaffolder.py openapi.yaml --framework express --output src/routes/
# Output: Generated 12 route handlers, validation middleware, and TypeScript types

# Generate from database schema
python scripts/api_scaffolder.py --from-db postgres://localhost/mydb --output src/routes/

# Generate OpenAPI spec from existing routes
python scripts/api_scaffolder.py src/routes/ --generate-spec --output openapi.yaml

Supported Frameworks:

• Express.js (--framework express)
• Fastify (--framework fastify)
• Koa (--framework koa)

---

2. Database Migration Tool

Analyzes database schemas, detects changes, and generates migration files with rollback support.

Input: Database connection string or schema files Output: Migration files, schema diff report, optimization suggestions

Usage:

# Analyze current schema and suggest optimizations
python scripts/database_migration_tool.py --connection postgres://localhost/mydb --analyze
# Output: Missing indexes, N+1 query risks, and suggested migration files

# Generate migration from schema diff
python scripts/database_migration_tool.py --connection postgres://localhost/mydb \
  --compare schema/v2.sql --output migrations/

# Dry-run a migration
python scripts/database_migration_tool.py --connection postgres://localhost/mydb \
  --migrate migrations/20240115_add_user_indexes.sql --dry-run

---

3. API Load Tester

Performs HTTP load testing with configurable concurrency, measuring latency percentiles and throughput.

Input: API endpoint URL and test configuration Output: Performance report with latency distribution, error rates, throughput metrics

Usage:

# Basic load test
python scripts/api_load_tester.py https://api.example.com/users --concurrency 50 --duration 30
# Output: Throughput (req/sec), latency percentiles (P50/P95/P99), error counts, and scaling recommendations

# Test with custom headers and body
python scripts/api_load_tester.py https://api.example.com/orders \
  --method POST \
  --header "Authorization: Bearer token123" \
  --body '{"product_id": 1, "quantity": 2}' \
  --concurrency 100 \
  --duration 60

# Compare two endpoints
python scripts/api_load_tester.py https://api.example.com/v1/users https://api.example.com/v2/users \
  --compare --concurrency 50 --duration 30

---

Backend Development Workflows

API Design Workflow

Use when designing a new API or refactoring existing endpoints.

Step 1: Define resources and operations

# openapi.yaml
openapi: 3.0.3
info:
  title: User Service API
  version: 1.0.0
paths:
  /users:
    get:
      summary: List users
      parameters:
        - name: "limit"
          in: query
          schema:
            type: integer
            default: 20
    post:
      summary: Create user
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/CreateUser'

Step 2: Generate route scaffolding

python scripts/api_scaffolder.py openapi.yaml --framework express --output src/routes/

Step 3: Implement business logic

// src/routes/users.ts (generated, then customized)
export const createUser = async (req: Request, res: Response) => {
  const { email, name } = req.body;

  // Add business logic
  const user = await userService.create({ email, name });

  res.status(201).json(user);
};

Step 4: Add validation middleware

# Validation is auto-generated from OpenAPI schema
# src/middleware/validators.ts includes:
# - Request body validation
# - Query parameter validation
# - Path parameter validation

Step 5: Generate updated OpenAPI spec

python scripts/api_scaffolder.py src/routes/ --generate-spec --output openapi.yaml

---

Database Optimization Workflow

Use when queries are slow or database performance needs improvement.

Step 1: Analyze current performance

python scripts/database_migration_tool.py --connection $DATABASE_URL --analyze

Step 2: Identify slow queries

-- Check query execution plans
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;

-- Look for: Seq Scan (bad), Index Scan (good)

Step 3: Generate index migrations

python scripts/database_migration_tool.py --connection $DATABASE_URL \
  --suggest-indexes --output migrations/

Step 4: Test migration (dry-run)

python scripts/database_migration_tool.py --connection $DATABASE_URL \
  --migrate migrations/add_indexes.sql --dry-run

Step 5: Apply and verify

# Apply migration
python scripts/database_migration_tool.py --connection $DATABASE_URL \
  --migrate migrations/add_indexes.sql

# Verify improvement
python scripts/database_migration_tool.py --connection $DATABASE_URL --analyze

---

Security Hardening Workflow

Use when preparing an API for production or after a security review.

Step 1: Review authentication setup

// Verify JWT configuration
const jwtConfig = {
  secret: process.env.JWT_SECRET,  // Must be from env, never hardcoded
  expiresIn: '1h',                 // Short-lived tokens
  algorithm: 'RS256'               // Prefer asymmetric
};

Step 2: Add rate limiting

import rateLimit from 'express-rate-limit';

const apiLimiter = rateLimit({
  windowMs: 15 * 60 * 1000,  // 15 minutes
  max: 100,                   // 100 requests per window
  standardHeaders: true,
  legacyHeaders: false,
});

app.use('/api/', apiLimiter);

Step 3: Validate all inputs

import { z } from 'zod';

const CreateUserSchema = z.object({
  email: z.string().email().max(255),
  name: z.string().min(1).max(100),
  age: z.number().int().positive().optional()
});

// Use in route handler
const data = CreateUserSchema.parse(req.body);

Step 4: Load test with attack patterns

# Test rate limiting
python scripts/api_load_tester.py https://api.example.com/login \
  --concurrency 200 --duration 10 --expect-rate-limit

# Test input validation
python scripts/api_load_tester.py https://api.example.com/users \
  --method POST \
  --body '{"email": "not-an-email"}' \
  --expect-status 400

Step 5: Review security headers

import helmet from 'helmet';

app.use(helmet({
  contentSecurityPolicy: true,
  crossOriginEmbedderPolicy: true,
  crossOriginOpenerPolicy: true,
  crossOriginResourcePolicy: true,
  hsts: { maxAge: 31536000, includeSubDomains: true },
}));

---

Reference Documentation

---

Common Patterns Quick Reference

REST API Response Format

{
  "data": { "id": 1, "name": "John" },
  "meta": { "requestId": "abc-123" }
}

Error Response Format

{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Invalid email format",
    "details": [{ "field": "email", "message": "must be valid email" }]
  },
  "meta": { "requestId": "abc-123" }
}

HTTP Status Codes

Database Index Strategy

-- Single column (equality lookups)
CREATE INDEX idx_users_email ON users(email);

-- Composite (multi-column queries)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- Partial (filtered queries)
CREATE INDEX idx_orders_active ON orders(created_at) WHERE status = 'active';

-- Covering (avoid table lookup)
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name);

---

Common Commands

# API Development
python scripts/api_scaffolder.py openapi.yaml --framework express
python scripts/api_scaffolder.py src/routes/ --generate-spec

# Database Operations
python scripts/database_migration_tool.py --connection $DATABASE_URL --analyze
python scripts/database_migration_tool.py --connection $DATABASE_URL --migrate file.sql

# Performance Testing
python scripts/api_load_tester.py https://api.example.com/endpoint --concurrency 50
python scripts/api_load_tester.py https://api.example.com/endpoint --compare baseline.json

---

Assumptions and Verifiable Success Criteria (Karpathy discipline)

Before this skill scaffolds, recommends a pattern, or modifies a schema, the following four assumptions MUST be surfaced. If any are unknown, the skill stops and walks the Forcing-question library instead.

1. Read/write ratio + one-year p99 QPS — drives DB, cache, queue, and partitioning choices. Kleppmann, DDIA (2017). 2. Tenancy model — single-tenant, shared multi-tenant, isolated multi-tenant. Drives data-access pattern. 3. Data sensitivity tier — public / internal / PII / PHI / PCI. Drives compliance floor. 4. SLO + named error-budget consumer — Google SRE Workbook canon. No SLO = no reliability work prioritization.

Verifiable success criteria (Karpathy #4) — every recommendation this skill emits must include:

• Latency targets (p50, p95, p99 in ms)
• Uptime / SLO target
• RPO + RTO

If any of those three is not stated, the recommendation is incomplete — return to Q7 of the forcing-question library.

The scripts/backend_decision_engine.py tool encodes these checks: it refuses to recommend a profile without read/write ratio + QPS + tenancy + data sensitivity + pattern preference.

---

Customization profiles

Four built-in profiles in profiles/ calibrate every recommendation:

Pick a profile via:

python scripts/backend_decision_engine.py \
  --team-size 8 --qps-p99 50 --read-write-ratio 20 \
  --tenancy shared-multi-tenant --data-sensitivity pii \
  --pattern modular-monolith --language-preference typescript

The tool returns the best-fit profile, runner-up tradeoff (if within 15%), stack picks, anti-patterns, named approvers, and SLO floor. This tool never auto-approves.

To add a custom profile: copy profiles/node-express.json to profiles/<your-org>.json and adjust constraints + success_thresholds + named_approver_chain.

---

Composition map

This skill does NOT reimplement scope owned by the POWERFUL-tier specialists. It forks into them. See references/composition_map.md for the full routing table. Key forks:

The cs-backend-engineer agent orchestrates these forks via context: fork. Invoke it from another agent with Agent({subagent_type: "cs-backend-engineer", prompt: "..."}) or via /cs:backend-review <your problem>.

---

Forcing-question library (Matt Pocock grill)

Before locking any backend decision, walk the seven forcing questions in references/forcing_questions.md. Discipline:

1. One question per turn. No bundling. 2. Always recommend the answer with cited canon. 3. Track answers in /tmp/backend-grill-<date>.md. 4. If a kill criterion trips, stop. Don't scaffold around an unresolved gap. 5. After Q7, run backend_decision_engine.py with the seven answers.

Summary:

1. Read/write ratio + p99 QPS forecast? 2. Tenancy model — single / shared / isolated? 3. Sync / async / event-driven — default + exceptions? 4. Data sensitivity tier — PII / PHI / PCI? 5. Monolith / modular monolith / microservices — team-size justification? 6. RPO + RTO? 7. SLO + named error-budget consumer?

---

Invocation from other agents and skills

Three surfaces:

1. Slash command: /cs:backend-review <prompt> — full grill + decision engine + composition routing. 2. Agent subagent: Agent({subagent_type: "cs-backend-engineer", prompt: "..."}) — forks context, returns ≤ 200-word digest. 3. Direct tool call: python scripts/backend_decision_engine.py ... — deterministic profile match when inputs are known.

See agents/engineering/cs-backend-engineer.md for the full invocation contract.

API Design Patterns

Concrete patterns for REST and GraphQL API design with examples.

Patterns Index

1. REST vs GraphQL Decision 2. Resource Naming Conventions 3. API Versioning Strategies 4. Error Handling Patterns 5. Pagination Patterns 6. Authentication Patterns 7. Rate Limiting Design 8. Idempotency Patterns

---

1. REST vs GraphQL Decision

When to Use REST

When to Use GraphQL

Hybrid Approach

┌─────────────────────────────────────────────────────┐
│                    API Gateway                       │
├─────────────────────────────────────────────────────┤
│  /api/v1/*  →  REST (Public API, webhooks)          │
│  /graphql   →  GraphQL (Mobile apps, dashboards)    │
│  /files/*   →  REST (File uploads/downloads)        │
└─────────────────────────────────────────────────────┘

---

2. Resource Naming Conventions

REST Endpoint Patterns

# Collections (plural nouns)
GET    /users              # List users
POST   /users              # Create user
GET    /users/{id}         # Get user
PUT    /users/{id}         # Replace user
PATCH  /users/{id}         # Update user
DELETE /users/{id}         # Delete user

# Nested resources
GET    /users/{id}/orders          # User's orders
POST   /users/{id}/orders          # Create order for user
GET    /users/{id}/orders/{orderId} # Specific order

# Actions (when CRUD doesn't fit)
POST   /users/{id}/activate        # Activate user
POST   /orders/{id}/cancel         # Cancel order
POST   /payments/{id}/refund       # Refund payment

# Filtering, sorting, pagination
GET    /users?status=active&sort=-created_at&limit=20&offset=40
GET    /orders?user_id=123&status=pending

Naming Rules

---

3. API Versioning Strategies

Strategy Comparison

Recommended: URL Path Versioning

// Express routing
import v1Routes from './routes/v1';
import v2Routes from './routes/v2';

app.use('/api/v1', v1Routes);
app.use('/api/v2', v2Routes);

Deprecation Strategy

// Add deprecation headers
app.use('/api/v1', (req, res, next) => {
  res.set('Deprecation', 'true');
  res.set('Sunset', 'Sat, 01 Jun 2025 00:00:00 GMT');
  res.set('Link', '</api/v2>; rel="successor-version"');
  next();
}, v1Routes);

Breaking vs Non-Breaking Changes

Non-breaking (safe):

• Adding new endpoints
• Adding optional fields
• Adding new enum values at end

Breaking (requires new version):

• Removing endpoints or fields
• Renaming fields
• Changing field types
• Changing required/optional status

---

4. Error Handling Patterns

Standard Error Response Format

{
  "error": {
    "code": "VALIDATION_ERROR",
    "message": "Request validation failed",
    "details": [
      {
        "field": "email",
        "code": "INVALID_FORMAT",
        "message": "Must be a valid email address"
      },
      {
        "field": "age",
        "code": "OUT_OF_RANGE",
        "message": "Must be between 18 and 120"
      }
    ],
    "documentation_url": "https://api.example.com/docs/errors#validation"
  },
  "meta": {
    "request_id": "req_abc123",
    "timestamp": "2024-01-15T10:30:00Z"
  }
}

Error Codes by Category

// Client errors (4xx)
const ClientErrors = {
  VALIDATION_ERROR: 400,
  INVALID_JSON: 400,
  AUTHENTICATION_REQUIRED: 401,
  INVALID_TOKEN: 401,
  TOKEN_EXPIRED: 401,
  PERMISSION_DENIED: 403,
  RESOURCE_NOT_FOUND: 404,
  METHOD_NOT_ALLOWED: 405,
  CONFLICT: 409,
  RATE_LIMIT_EXCEEDED: 429,
};

// Server errors (5xx)
const ServerErrors = {
  INTERNAL_ERROR: 500,
  DATABASE_ERROR: 500,
  EXTERNAL_SERVICE_ERROR: 502,
  SERVICE_UNAVAILABLE: 503,
};

Error Handler Implementation

// Express error handler
interface ApiError extends Error {
  code: string;
  statusCode: number;
  details?: Array<{ field: string; message: string }>;
}

const errorHandler: ErrorRequestHandler = (err: ApiError, req, res, next) => {
  const statusCode = err.statusCode || 500;
  const code = err.code || 'INTERNAL_ERROR';

  // Log server errors
  if (statusCode >= 500) {
    logger.error({ err, requestId: req.id }, 'Server error');
  }

  res.status(statusCode).json({
    error: {
      code,
      message: statusCode >= 500 ? 'An unexpected error occurred' : err.message,
      details: err.details,
      ...(process.env.NODE_ENV === 'development' && { stack: err.stack }),
    },
    meta: {
      request_id: req.id,
      timestamp: new Date().toISOString(),
    },
  });
};

---

5. Pagination Patterns

Offset-Based Pagination

GET /users?limit=20&offset=40

Response:
{
  "data": [...],
  "pagination": {
    "total": 1250,
    "limit": 20,
    "offset": 40,
    "has_more": true
  }
}

Pros: Simple, supports random access Cons: Inconsistent with concurrent inserts/deletes

Cursor-Based Pagination

GET /users?limit=20&cursor=eyJpZCI6MTIzfQ==

Response:
{
  "data": [...],
  "pagination": {
    "limit": 20,
    "next_cursor": "eyJpZCI6MTQzfQ==",
    "prev_cursor": "eyJpZCI6MTIzfQ==",
    "has_more": true
  }
}

Pros: Consistent with real-time data, efficient Cons: No random access, cursor encoding required

Implementation Example

// Cursor-based pagination
interface CursorPagination {
  limit: number;
  cursor?: string;
  direction?: 'forward' | 'backward';
}

async function paginatedQuery<T>(
  query: QueryBuilder,
  { limit, cursor, direction = 'forward' }: CursorPagination
): Promise<{ data: T[]; nextCursor?: string; hasMore: boolean }> {
  // Decode cursor
  const decoded = cursor ? JSON.parse(Buffer.from(cursor, 'base64').toString()) : null;

  // Apply cursor condition
  if (decoded) {
    query = direction === 'forward'
      ? query.where('id', '>', decoded.id)
      : query.where('id', '<', decoded.id);
  }

  // Fetch one extra to check if more exist
  const results = await query.limit(limit + 1).orderBy('id', direction === 'forward' ? 'asc' : 'desc');

  const hasMore = results.length > limit;
  const data = hasMore ? results.slice(0, -1) : results;

  // Encode next cursor
  const nextCursor = hasMore
    ? Buffer.from(JSON.stringify({ id: data[data.length - 1].id })).toString('base64')
    : undefined;

  return { data, nextCursor, hasMore };
}

---

6. Authentication Patterns

JWT Authentication Flow

┌──────────┐      1. Login        ┌──────────┐
│  Client  │ ──────────────────▶  │  Server  │
└──────────┘                      └──────────┘
                                       │
     2. Return JWT                     │
◀────────────────────────────────────────
     {access_token, refresh_token}     │
                                       │
     3. API Request                    │
───────────────────────────────────────▶
     Authorization: Bearer {token}     │
                                       │
     4. Validate & Respond             │
◀────────────────────────────────────────

JWT Implementation

import jwt from 'jsonwebtoken';

interface TokenPayload {
  userId: string;
  email: string;
  roles: string[];
}

// Generate tokens
function generateTokens(user: User): { accessToken: string; refreshToken: string } {
  const payload: TokenPayload = {
    userId: user.id,
    email: user.email,
    roles: user.roles,
  };

  const accessToken = jwt.sign(payload, process.env.JWT_SECRET!, {
    expiresIn: '15m',
    algorithm: 'RS256',
  });

  const refreshToken = jwt.sign(
    { userId: user.id, tokenVersion: user.tokenVersion },
    process.env.JWT_REFRESH_SECRET!,
    { expiresIn: '7d', algorithm: 'RS256' }
  );

  return { accessToken, refreshToken };
}

// Middleware
const authenticate: RequestHandler = async (req, res, next) => {
  const authHeader = req.headers.authorization;
  if (!authHeader?.startsWith('Bearer ')) {
    return res.status(401).json({ error: { code: 'AUTHENTICATION_REQUIRED' } });
  }

  try {
    const token = authHeader.slice(7);
    const payload = jwt.verify(token, process.env.JWT_SECRET!) as TokenPayload;
    req.user = payload;
    next();
  } catch (err) {
    if (err instanceof jwt.TokenExpiredError) {
      return res.status(401).json({ error: { code: 'TOKEN_EXPIRED' } });
    }
    return res.status(401).json({ error: { code: 'INVALID_TOKEN' } });
  }
};

API Key Authentication (Service-to-Service)

// API key middleware
const apiKeyAuth: RequestHandler = async (req, res, next) => {
  const apiKey = req.headers['x-api-key'] as string;

  if (!apiKey) {
    return res.status(401).json({ error: { code: 'API_KEY_REQUIRED' } });
  }

  // Hash and lookup (never store plain API keys)
  const hashedKey = crypto.createHash('sha256').update(apiKey).digest('hex');
  const client = await db.apiClients.findByHashedKey(hashedKey);

  if (!client || !client.isActive) {
    return res.status(401).json({ error: { code: 'INVALID_API_KEY' } });
  }

  req.apiClient = client;
  next();
};

---

7. Rate Limiting Design

Rate Limit Headers

HTTP/1.1 200 OK
X-RateLimit-Limit: 100
X-RateLimit-Remaining: 95
X-RateLimit-Reset: 1705312800
Retry-After: 60

Tiered Rate Limits

const rateLimits = {
  anonymous: { requests: 60, window: '1m' },
  authenticated: { requests: 1000, window: '1h' },
  premium: { requests: 10000, window: '1h' },
};

// Implementation with Redis
import { RateLimiterRedis } from 'rate-limiter-flexible';

const createRateLimiter = (tier: keyof typeof rateLimits) => {
  const config = rateLimits[tier];
  return new RateLimiterRedis({
    storeClient: redisClient,
    keyPrefix: `ratelimit:${tier}`,
    points: config.requests,
    duration: parseDuration(config.window),
  });
};

Rate Limit Response

{
  "error": {
    "code": "RATE_LIMIT_EXCEEDED",
    "message": "Too many requests",
    "details": {
      "limit": 100,
      "window": "1 minute",
      "retry_after": 45
    }
  }
}

---

8. Idempotency Patterns

Idempotency Key Header

POST /payments
Idempotency-Key: payment_abc123_attempt1
Content-Type: application/json

{
  "amount": 1000,
  "currency": "USD"
}

Implementation

const idempotencyMiddleware: RequestHandler = async (req, res, next) => {
  const idempotencyKey = req.headers['idempotency-key'] as string;

  if (!idempotencyKey) {
    return next(); // Optional for some endpoints
  }

  // Check for existing response
  const cached = await redis.get(`idempotency:${idempotencyKey}`);
  if (cached) {
    const { statusCode, body } = JSON.parse(cached);
    return res.status(statusCode).json(body);
  }

  // Store response after processing
  const originalJson = res.json.bind(res);
  res.json = (body: any) => {
    redis.setex(
      `idempotency:${idempotencyKey}`,
      86400, // 24 hours
      JSON.stringify({ statusCode: res.statusCode, body })
    );
    return originalJson(body);
  };

  next();
};

---

Quick Reference: HTTP Methods

Backend Security Practices

Security patterns and OWASP Top 10 mitigations for Node.js/Express applications.

Guide Index

1. OWASP Top 10 Mitigations 2. Input Validation 3. SQL Injection Prevention 4. XSS Prevention 5. Authentication Security 6. Authorization Patterns 7. Security Headers 8. Secrets Management 9. Logging and Monitoring

---

1. OWASP Top 10 Mitigations

A01: Broken Access Control

// BAD: Direct object reference
app.get('/users/:id/profile', async (req, res) => {
  const user = await db.users.findById(req.params.id);
  res.json(user); // Anyone can access any user!
});

// GOOD: Verify ownership
app.get('/users/:id/profile', authenticate, async (req, res) => {
  const userId = req.params.id;

  // Verify user can only access their own data
  if (req.user.id !== userId && !req.user.roles.includes('admin')) {
    return res.status(403).json({ error: { code: 'FORBIDDEN' } });
  }

  const user = await db.users.findById(userId);
  res.json(user);
});

A02: Cryptographic Failures

// BAD: Weak hashing
const hash = crypto.createHash('md5').update(password).digest('hex');

// GOOD: bcrypt with appropriate cost factor
import bcrypt from 'bcrypt';

const SALT_ROUNDS = 12; // Adjust based on hardware

async function hashPassword(password: string): Promise<string> {
  return bcrypt.hash(password, SALT_ROUNDS);
}

async function verifyPassword(password: string, hash: string): Promise<boolean> {
  return bcrypt.compare(password, hash);
}

A03: Injection

// BAD: String concatenation in SQL
const query = `SELECT * FROM users WHERE email = '${email}'`;

// GOOD: Parameterized queries
const result = await db.query(
  'SELECT * FROM users WHERE email = $1',
  [email]
);

A04: Insecure Design

// BAD: No rate limiting on sensitive operations
app.post('/forgot-password', async (req, res) => {
  await sendResetEmail(req.body.email);
  res.json({ message: 'If email exists, reset link sent' });
});

// GOOD: Rate limit + consistent response time
import rateLimit from 'express-rate-limit';

const passwordResetLimiter = rateLimit({
  windowMs: 15 * 60 * 1000,
  max: 3, // 3 attempts per 15 minutes
  skipSuccessfulRequests: false,
});

app.post('/forgot-password', passwordResetLimiter, async (req, res) => {
  const startTime = Date.now();

  try {
    const user = await db.users.findByEmail(req.body.email);
    if (user) {
      await sendResetEmail(user.email);
    }
  } catch (err) {
    logger.error(err);
  }

  // Consistent response time prevents timing attacks
  const elapsed = Date.now() - startTime;
  const minDelay = 500;
  if (elapsed < minDelay) {
    await sleep(minDelay - elapsed);
  }

  // Same response regardless of email existence
  res.json({ message: 'If email exists, reset link sent' });
});

A05: Security Misconfiguration

// BAD: Detailed errors in production
app.use((err, req, res, next) => {
  res.status(500).json({
    error: err.message,
    stack: err.stack, // Exposes internals!
  });
});

// GOOD: Environment-aware error handling
app.use((err: Error, req: Request, res: Response, next: NextFunction) => {
  const requestId = req.id;

  // Always log full error internally
  logger.error({ err, requestId }, 'Unhandled error');

  // Return safe response
  res.status(500).json({
    error: {
      code: 'INTERNAL_ERROR',
      message: process.env.NODE_ENV === 'development'
        ? err.message
        : 'An unexpected error occurred',
      requestId,
    },
  });
});

A06: Vulnerable Components

# Check for vulnerabilities
npm audit

# Fix automatically where possible
npm audit fix

# Check specific package
npm audit --package-lock-only

# Use Snyk for deeper analysis
npx snyk test

// Automated dependency updates (package.json)
{
  "scripts": {
    "security:audit": "npm audit --audit-level=high",
    "security:check": "snyk test",
    "preinstall": "npm audit"
  }
}

A07: Authentication Failures

// BAD: Weak session management
app.post('/login', async (req, res) => {
  const user = await authenticate(req.body);
  req.session.userId = user.id; // Session fixation risk
  res.json({ success: true });
});

// GOOD: Regenerate session on authentication
app.post('/login', async (req, res) => {
  const user = await authenticate(req.body);

  // Regenerate session to prevent fixation
  req.session.regenerate((err) => {
    if (err) return next(err);

    req.session.userId = user.id;
    req.session.createdAt = Date.now();

    req.session.save((err) => {
      if (err) return next(err);
      res.json({ success: true });
    });
  });
});

A08: Software and Data Integrity Failures

// Verify webhook signatures (e.g., Stripe)
import Stripe from 'stripe';

app.post('/webhooks/stripe',
  express.raw({ type: 'application/json' }),
  async (req, res) => {
    const sig = req.headers['stripe-signature'] as string;
    const endpointSecret = process.env.STRIPE_WEBHOOK_SECRET!;

    let event: Stripe.Event;

    try {
      event = stripe.webhooks.constructEvent(
        req.body,
        sig,
        endpointSecret
      );
    } catch (err) {
      logger.warn({ err }, 'Webhook signature verification failed');
      return res.status(400).json({ error: 'Invalid signature' });
    }

    // Process verified event
    await handleStripeEvent(event);
    res.json({ received: true });
  }
);

A09: Security Logging Failures

// Comprehensive security logging
import pino from 'pino';

const logger = pino({
  level: process.env.LOG_LEVEL || 'info',
  redact: ['req.headers.authorization', 'req.body.password'], // Redact sensitive
});

// Log security events
function logSecurityEvent(event: {
  type: 'LOGIN_SUCCESS' | 'LOGIN_FAILURE' | 'ACCESS_DENIED' | 'SUSPICIOUS_ACTIVITY';
  userId?: string;
  ip: string;
  userAgent: string;
  details?: Record<string, unknown>;
}) {
  logger.info({
    security: true,
    ...event,
    timestamp: new Date().toISOString(),
  }, `Security event: ${event.type}`);
}

// Usage
app.post('/login', async (req, res) => {
  try {
    const user = await authenticate(req.body);
    logSecurityEvent({
      type: 'LOGIN_SUCCESS',
      userId: user.id,
      ip: req.ip,
      userAgent: req.headers['user-agent'] || '',
    });
    // ...
  } catch (err) {
    logSecurityEvent({
      type: 'LOGIN_FAILURE',
      ip: req.ip,
      userAgent: req.headers['user-agent'] || '',
      details: { email: req.body.email },
    });
    // ...
  }
});

A10: Server-Side Request Forgery (SSRF)

// BAD: Unvalidated URL fetch
app.post('/fetch-url', async (req, res) => {
  const response = await fetch(req.body.url); // SSRF vulnerability!
  res.json({ data: await response.text() });
});

// GOOD: URL allowlist and validation
import { URL } from 'url';

const ALLOWED_HOSTS = ['api.example.com', 'cdn.example.com'];

function isAllowedUrl(urlString: string): boolean {
  try {
    const url = new URL(urlString);

    // Block internal IPs
    const blockedPatterns = [
      /^localhost$/i,
      /^127\./,
      /^10\./,
      /^172\.(1[6-9]|2[0-9]|3[0-1])\./,
      /^192\.168\./,
      /^0\./,
      /^169\.254\./,
      /^\[::1\]$/,
      /^metadata\.google\.internal$/,
      /^169\.254\.169\.254$/,
    ];

    if (blockedPatterns.some(p => p.test(url.hostname))) {
      return false;
    }

    // Only allow HTTPS
    if (url.protocol !== 'https:') {
      return false;
    }

    // Check allowlist
    return ALLOWED_HOSTS.includes(url.hostname);
  } catch {
    return false;
  }
}

app.post('/fetch-url', async (req, res) => {
  const { url } = req.body;

  if (!isAllowedUrl(url)) {
    return res.status(400).json({ error: { code: 'INVALID_URL' } });
  }

  const response = await fetch(url, {
    timeout: 5000,
    follow: 0, // Don't follow redirects
  });

  res.json({ data: await response.text() });
});

---

2. Input Validation

Schema Validation with Zod

import { z } from 'zod';

// Define schemas
const CreateUserSchema = z.object({
  email: z.string().email().max(255).toLowerCase(),
  password: z.string()
    .min(8, 'Password must be at least 8 characters')
    .max(72, 'Password must be at most 72 characters') // bcrypt limit
    .regex(/[A-Z]/, 'Password must contain uppercase letter')
    .regex(/[a-z]/, 'Password must contain lowercase letter')
    .regex(/[0-9]/, 'Password must contain number'),
  name: z.string().min(1).max(100).trim(),
  age: z.number().int().min(18).max(120).optional(),
});

const PaginationSchema = z.object({
  limit: z.coerce.number().int().min(1).max(100).default(20),
  offset: z.coerce.number().int().min(0).default(0),
  sort: z.enum(['asc', 'desc']).default('desc'),
});

// Validation middleware
function validate<T>(schema: z.ZodSchema<T>) {
  return (req: Request, res: Response, next: NextFunction) => {
    const result = schema.safeParse(req.body);

    if (!result.success) {
      const details = result.error.errors.map(err => ({
        field: err.path.join('.'),
        code: err.code,
        message: err.message,
      }));

      return res.status(400).json({
        error: {
          code: 'VALIDATION_ERROR',
          message: 'Request validation failed',
          details,
        },
      });
    }

    req.body = result.data;
    next();
  };
}

// Usage
app.post('/users', validate(CreateUserSchema), async (req, res) => {
  // req.body is now typed and validated
  const user = await userService.create(req.body);
  res.status(201).json(user);
});

Sanitization

import DOMPurify from 'isomorphic-dompurify';
import xss from 'xss';

// HTML sanitization for rich text fields
function sanitizeHtml(dirty: string): string {
  return DOMPurify.sanitize(dirty, {
    ALLOWED_TAGS: ['b', 'i', 'em', 'strong', 'a', 'p', 'br'],
    ALLOWED_ATTR: ['href'],
  });
}

// Plain text sanitization (strip all HTML)
function sanitizePlainText(dirty: string): string {
  return xss(dirty, {
    whiteList: {},
    stripIgnoreTag: true,
    stripIgnoreTagBody: ['script'],
  });
}

// File path sanitization
import path from 'path';

function sanitizePath(userPath: string, baseDir: string): string | null {
  const resolved = path.resolve(baseDir, userPath);

  // Prevent directory traversal
  if (!resolved.startsWith(baseDir)) {
    return null;
  }

  return resolved;
}

---

3. SQL Injection Prevention

Parameterized Queries

// BAD: String interpolation
const email = "'; DROP TABLE users; --";
db.query(`SELECT * FROM users WHERE email = '${email}'`);

// GOOD: Parameterized query (pg)
const result = await db.query(
  'SELECT * FROM users WHERE email = $1',
  [email]
);

// GOOD: Parameterized query (mysql2)
const [rows] = await connection.execute(
  'SELECT * FROM users WHERE email = ?',
  [email]
);

Query Builders

// Using Knex.js
const users = await knex('users')
  .where('email', email) // Automatically parameterized
  .andWhere('status', 'active')
  .select('id', 'name', 'email');

// Dynamic WHERE with safe column names
const ALLOWED_COLUMNS = ['name', 'email', 'created_at'] as const;

function buildUserQuery(filters: Record<string, string>) {
  let query = knex('users').select('id', 'name', 'email');

  for (const [column, value] of Object.entries(filters)) {
    // Validate column name against allowlist
    if (ALLOWED_COLUMNS.includes(column as any)) {
      query = query.where(column, value);
    }
  }

  return query;
}

ORM Safety

// Prisma (safe by default)
const user = await prisma.user.findUnique({
  where: { email }, // Automatically escaped
});

// TypeORM (safe by default)
const user = await userRepository.findOne({
  where: { email }, // Automatically escaped
});

// DANGER: Raw queries still require parameterization
// BAD
await prisma.$queryRawUnsafe(`SELECT * FROM users WHERE email = '${email}'`);

// GOOD
await prisma.$queryRaw`SELECT * FROM users WHERE email = ${email}`;

---

4. XSS Prevention

Output Encoding

// Server-side template rendering (EJS)
// In template: <%= userInput %> (escaped)
// NOT: <%- userInput %> (raw, dangerous)

// Manual HTML encoding
function escapeHtml(str: string): string {
  return str
    .replace(/&/g, '&amp;')
    .replace(/</g, '&lt;')
    .replace(/>/g, '&gt;')
    .replace(/"/g, '&quot;')
    .replace(/'/g, '&#039;');
}

// JSON response (automatically safe in modern frameworks)
res.json({ message: userInput }); // JSON.stringify escapes by default

Content Security Policy

import helmet from 'helmet';

app.use(helmet.contentSecurityPolicy({
  directives: {
    defaultSrc: ["'self'"],
    scriptSrc: ["'self'", "'strict-dynamic'"],
    styleSrc: ["'self'", "'unsafe-inline'"], // Consider using nonces
    imgSrc: ["'self'", "data:", "https:"],
    fontSrc: ["'self'"],
    objectSrc: ["'none'"],
    frameAncestors: ["'none'"],
    baseUri: ["'self'"],
    formAction: ["'self'"],
    upgradeInsecureRequests: [],
  },
}));

API Response Safety

// Set correct Content-Type for JSON APIs
app.use((req, res, next) => {
  res.setHeader('Content-Type', 'application/json; charset=utf-8');
  res.setHeader('X-Content-Type-Options', 'nosniff');
  next();
});

// Disable JSONP (if not needed)
// Don't implement callback parameter handling

// Safe JSON response
res.json({
  data: sanitizedData,
  // Never reflect raw user input
});

---

5. Authentication Security

Password Storage

import bcrypt from 'bcrypt';
import { randomBytes } from 'crypto';

const SALT_ROUNDS = 12;

async function hashPassword(password: string): Promise<string> {
  return bcrypt.hash(password, SALT_ROUNDS);
}

async function verifyPassword(password: string, hash: string): Promise<boolean> {
  return bcrypt.compare(password, hash);
}

// For password reset tokens
function generateSecureToken(): string {
  return randomBytes(32).toString('hex');
}

// Token expiration (store in DB)
interface PasswordResetToken {
  token: string; // Hashed
  userId: string;
  expiresAt: Date; // 1 hour from creation
}

JWT Best Practices

import jwt from 'jsonwebtoken';

// Use asymmetric keys in production
const PRIVATE_KEY = process.env.JWT_PRIVATE_KEY!;
const PUBLIC_KEY = process.env.JWT_PUBLIC_KEY!;

interface AccessTokenPayload {
  sub: string; // User ID
  email: string;
  roles: string[];
  iat: number;
  exp: number;
}

function generateAccessToken(user: User): string {
  const payload: Omit<AccessTokenPayload, 'iat' | 'exp'> = {
    sub: user.id,
    email: user.email,
    roles: user.roles,
  };

  return jwt.sign(payload, PRIVATE_KEY, {
    algorithm: 'RS256',
    expiresIn: '15m',
    issuer: 'api.example.com',
    audience: 'example.com',
  });
}

function verifyAccessToken(token: string): AccessTokenPayload {
  return jwt.verify(token, PUBLIC_KEY, {
    algorithms: ['RS256'],
    issuer: 'api.example.com',
    audience: 'example.com',
  }) as AccessTokenPayload;
}

// Refresh tokens should be stored in DB and rotated
interface RefreshToken {
  id: string;
  token: string; // Hashed
  userId: string;
  expiresAt: Date;
  family: string; // For rotation detection
  isRevoked: boolean;
}

Session Management

import session from 'express-session';
import RedisStore from 'connect-redis';
import { createClient } from 'redis';

const redisClient = createClient({ url: process.env.REDIS_URL });

app.use(session({
  store: new RedisStore({ client: redisClient }),
  name: 'sessionId', // Don't use default 'connect.sid'
  secret: process.env.SESSION_SECRET!,
  resave: false,
  saveUninitialized: false,
  cookie: {
    secure: process.env.NODE_ENV === 'production',
    httpOnly: true,
    sameSite: 'strict',
    maxAge: 24 * 60 * 60 * 1000, // 24 hours
    domain: process.env.COOKIE_DOMAIN,
  },
}));

// Regenerate session on privilege change
async function elevateSession(req: Request): Promise<void> {
  return new Promise((resolve, reject) => {
    const userId = req.session.userId;
    req.session.regenerate((err) => {
      if (err) return reject(err);
      req.session.userId = userId;
      req.session.elevated = true;
      req.session.elevatedAt = Date.now();
      resolve();
    });
  });
}

---

6. Authorization Patterns

Role-Based Access Control (RBAC)

type Role = 'user' | 'moderator' | 'admin';
type Permission = 'read:users' | 'write:users' | 'delete:users' | 'read:admin';

const ROLE_PERMISSIONS: Record<Role, Permission[]> = {
  user: ['read:users'],
  moderator: ['read:users', 'write:users'],
  admin: ['read:users', 'write:users', 'delete:users', 'read:admin'],
};

function hasPermission(userRoles: Role[], required: Permission): boolean {
  return userRoles.some(role =>
    ROLE_PERMISSIONS[role]?.includes(required)
  );
}

// Middleware
function requirePermission(permission: Permission) {
  return (req: Request, res: Response, next: NextFunction) => {
    if (!hasPermission(req.user.roles, permission)) {
      return res.status(403).json({
        error: { code: 'FORBIDDEN', message: 'Insufficient permissions' },
      });
    }
    next();
  };
}

// Usage
app.delete('/users/:id',
  authenticate,
  requirePermission('delete:users'),
  deleteUserHandler
);

Attribute-Based Access Control (ABAC)

interface AccessContext {
  user: { id: string; roles: string[]; department: string };
  resource: { ownerId: string; department: string; sensitivity: string };
  action: 'read' | 'write' | 'delete';
  environment: { time: Date; ip: string };
}

interface Policy {
  name: string;
  condition: (ctx: AccessContext) => boolean;
}

const policies: Policy[] = [
  {
    name: 'owner-full-access',
    condition: (ctx) => ctx.resource.ownerId === ctx.user.id,
  },
  {
    name: 'same-department-read',
    condition: (ctx) =>
      ctx.action === 'read' &&
      ctx.resource.department === ctx.user.department,
  },
  {
    name: 'admin-override',
    condition: (ctx) => ctx.user.roles.includes('admin'),
  },
  {
    name: 'no-sensitive-outside-hours',
    condition: (ctx) => {
      const hour = ctx.environment.time.getHours();
      return ctx.resource.sensitivity !== 'high' || (hour >= 9 && hour <= 17);
    },
  },
];

function evaluateAccess(ctx: AccessContext): boolean {
  return policies.some(policy => policy.condition(ctx));
}

---

7. Security Headers

Complete Helmet Configuration

import helmet from 'helmet';

app.use(helmet({
  // Content Security Policy
  contentSecurityPolicy: {
    directives: {
      defaultSrc: ["'self'"],
      scriptSrc: ["'self'"],
      styleSrc: ["'self'", "'unsafe-inline'"],
      imgSrc: ["'self'", "data:", "https:"],
      connectSrc: ["'self'", "https://api.example.com"],
      fontSrc: ["'self'"],
      objectSrc: ["'none'"],
      mediaSrc: ["'none'"],
      frameSrc: ["'none'"],
    },
  },
  // Strict Transport Security
  hsts: {
    maxAge: 31536000,
    includeSubDomains: true,
    preload: true,
  },
  // Prevent clickjacking
  frameguard: { action: 'deny' },
  // Prevent MIME sniffing
  noSniff: true,
  // XSS filter (legacy browsers)
  xssFilter: true,
  // Hide X-Powered-By
  hidePoweredBy: true,
  // Referrer policy
  referrerPolicy: { policy: 'strict-origin-when-cross-origin' },
  // Cross-origin policies
  crossOriginEmbedderPolicy: false, // Enable if using SharedArrayBuffer
  crossOriginOpenerPolicy: { policy: 'same-origin' },
  crossOriginResourcePolicy: { policy: 'same-origin' },
}));

// CORS configuration
import cors from 'cors';

app.use(cors({
  origin: ['https://example.com', 'https://app.example.com'],
  methods: ['GET', 'POST', 'PUT', 'DELETE', 'PATCH'],
  allowedHeaders: ['Content-Type', 'Authorization'],
  credentials: true,
  maxAge: 86400, // 24 hours
}));

Header Reference

---

8. Secrets Management

Environment Variables

// config/secrets.ts
import { z } from 'zod';

const SecretsSchema = z.object({
  DATABASE_URL: z.string().url(),
  JWT_SECRET: z.string().min(32),
  JWT_PRIVATE_KEY: z.string(),
  JWT_PUBLIC_KEY: z.string(),
  REDIS_URL: z.string().url(),
  STRIPE_SECRET_KEY: z.string().startsWith('sk_'),
  STRIPE_WEBHOOK_SECRET: z.string().startsWith('whsec_'),
});

// Validate on startup
export const secrets = SecretsSchema.parse(process.env);

// NEVER log secrets
console.log('Config loaded:', {
  database: secrets.DATABASE_URL.replace(/\/\/.*@/, '//***@'),
  redis: 'configured',
  stripe: 'configured',
});

Secret Rotation

// Support multiple keys during rotation
const JWT_SECRETS = [
  process.env.JWT_SECRET_CURRENT!,
  process.env.JWT_SECRET_PREVIOUS!, // Keep for grace period
].filter(Boolean);

function verifyTokenWithRotation(token: string): TokenPayload | null {
  for (const secret of JWT_SECRETS) {
    try {
      return jwt.verify(token, secret) as TokenPayload;
    } catch {
      continue;
    }
  }
  return null;
}

Vault Integration

import Vault from 'node-vault';

const vault = Vault({
  endpoint: process.env.VAULT_ADDR,
  token: process.env.VAULT_TOKEN,
});

async function getSecret(path: string): Promise<string> {
  const result = await vault.read(`secret/data/${path}`);
  return result.data.data.value;
}

// Cache secrets with TTL
const secretsCache = new Map<string, { value: string; expiresAt: number }>();
const CACHE_TTL = 5 * 60 * 1000; // 5 minutes

async function getCachedSecret(path: string): Promise<string> {
  const cached = secretsCache.get(path);
  if (cached && cached.expiresAt > Date.now()) {
    return cached.value;
  }

  const value = await getSecret(path);
  secretsCache.set(path, { value, expiresAt: Date.now() + CACHE_TTL });
  return value;
}

---

9. Logging and Monitoring

Security Event Logging

import pino from 'pino';

const logger = pino({
  level: 'info',
  redact: {
    paths: [
      'req.headers.authorization',
      'req.headers.cookie',
      'req.body.password',
      'req.body.token',
      '*.password',
      '*.secret',
      '*.apiKey',
    ],
    censor: '[REDACTED]',
  },
});

// Security event types
type SecurityEventType =
  | 'AUTH_SUCCESS'
  | 'AUTH_FAILURE'
  | 'AUTH_LOCKOUT'
  | 'PASSWORD_CHANGED'
  | 'PASSWORD_RESET_REQUEST'
  | 'PERMISSION_DENIED'
  | 'RATE_LIMIT_EXCEEDED'
  | 'SUSPICIOUS_ACTIVITY'
  | 'TOKEN_REVOKED';

interface SecurityEvent {
  type: SecurityEventType;
  userId?: string;
  ip: string;
  userAgent: string;
  path: string;
  details?: Record<string, unknown>;
}

function logSecurityEvent(event: SecurityEvent): void {
  logger.info({
    security: true,
    ...event,
    timestamp: new Date().toISOString(),
  }, `Security: ${event.type}`);
}

Request Logging

import pinoHttp from 'pino-http';

app.use(pinoHttp({
  logger,
  genReqId: (req) => req.headers['x-request-id'] || crypto.randomUUID(),
  serializers: {
    req: (req) => ({
      id: req.id,
      method: req.method,
      url: req.url,
      remoteAddress: req.remoteAddress,
      // Don't log headers by default (may contain sensitive data)
    }),
    res: (res) => ({
      statusCode: res.statusCode,
    }),
  },
  customLogLevel: (req, res, err) => {
    if (res.statusCode >= 500 || err) return 'error';
    if (res.statusCode >= 400) return 'warn';
    return 'info';
  },
}));

Alerting Thresholds

---

Quick Reference: Security Checklist

Authentication

• [ ] bcrypt with cost >= 12 for password hashing
• [ ] JWT with RS256, short expiry (15-30 min)
• [ ] Refresh token rotation with family detection
• [ ] Session regeneration on login
• [ ] Secure cookie flags (httpOnly, secure, sameSite)

Input Validation

• [ ] Schema validation on all inputs (Zod)
• [ ] Parameterized queries (never string concat)
• [ ] File path sanitization
• [ ] Content-Type validation

Headers

• [ ] Strict-Transport-Security
• [ ] Content-Security-Policy
• [ ] X-Content-Type-Options: nosniff
• [ ] X-Frame-Options: DENY
• [ ] CORS with specific origins

Logging

• [ ] Redact sensitive fields
• [ ] Log security events
• [ ] Include request IDs
• [ ] Alert on anomalies

Dependencies

• [ ] npm audit in CI
• [ ] Automated dependency updates
• [ ] Lock file committed

Backend Engineer — Composition Map

Principle (Karpathy #2, Simplicity First): do not reimplement scope that the POWERFUL-tier specialists already own. This skill is the backend orchestrator; the specialists are the implementers.

This map is the routing table for the cs-backend-engineer agent and the /cs:backend-review command.

Composition routing table

Composition rules

1. Fork via `context: fork` — the agent forks its own context, runs the sub-skill, returns a ≤ 200-word digest. 2. One sub-skill at a time. Matt Pocock's depth-first rule. Finish the DB branch before opening the API branch. 3. Honor sub-skill outputs as inputs. If database-designer recommends a schema, the next call to api-design-reviewer uses it. 4. Never reimplement specialist scope. If the user asks "what's my index strategy?" do not answer with handcrafted advice — fork into database-designer. 5. SLO before scale. If Q7 (SLO) is not set, don't burn cycles on caching / sharding / queue topology. Fork into slo-architect first.

Anti-patterns

• ❌ Recommending Kafka before naming a second team that needs it (premature event-driven).
• ❌ Recommending microservices before Q5 (team-size justification) passes.
• ❌ Designing API contracts without forking into api-design-reviewer (consistency, breaking-change risk).
• ❌ Skipping cs-karpathy-reviewer before commit — every commit must pass the diff-noise gate.
• ❌ Auto-approving a production schema migration — every migration names the on-call + DBA approver.

When to escalate out of backend

• Frontend integration questions → escalate to cs-frontend-engineer.
• Org-design / capacity / hiring → escalate to cs-vpe-advisor (engineering) or cs-bizops-orchestrator (cross-functional ops).
• Strategic build-vs-buy at company level → escalate to cs-cto-advisor.
• AI/ML pipeline + model serving → escalate to senior-ml-engineer.
• Data warehouse / dbt / lakehouse → escalate to senior-data-engineer.
• Pure security threat model → escalate to cs-ciso-advisor (strategic) or senior-security (tactical).

References

• Karpathy 4 principles → ../../../engineering/karpathy-coder/skills/karpathy-coder/references/karpathy-principles.md
• Matt Pocock grill discipline → ../../../engineering/grill-me/skills/grill-me/references/forcing_question_patterns.md
• Path-B 11-file contract → ../../../business-operations/CLAUDE.md
• SLO canon → ../../../engineering/slo-architect/skills/slo-architect/references/slo_principles.md

Database Optimization Guide

Practical strategies for PostgreSQL query optimization, indexing, and performance tuning.

Guide Index

1. Query Analysis with EXPLAIN 2. Indexing Strategies 3. N+1 Query Problem 4. Connection Pooling 5. Query Optimization Patterns 6. Database Migrations 7. Monitoring and Alerting

---

1. Query Analysis with EXPLAIN

Basic EXPLAIN Usage

-- Show query plan
EXPLAIN SELECT * FROM orders WHERE user_id = 123;

-- Show plan with actual execution times
EXPLAIN ANALYZE SELECT * FROM orders WHERE user_id = 123;

-- Show buffers and I/O statistics
EXPLAIN (ANALYZE, BUFFERS, FORMAT TEXT)
SELECT * FROM orders WHERE user_id = 123;

Reading EXPLAIN Output

                                    QUERY PLAN
---------------------------------------------------------------------------
 Index Scan using idx_orders_user_id on orders  (cost=0.43..8.45 rows=10 width=120)
   Index Cond: (user_id = 123)
   Buffers: shared hit=3
 Planning Time: 0.152 ms
 Execution Time: 0.089 ms

Key metrics:

• cost: Estimated cost (startup..total)
• rows: Estimated row count
• width: Average row size in bytes
• actual time: Real execution time (with ANALYZE)
• Buffers: shared hit: Pages read from cache

Scan Types (Best to Worst)

Warning Signs

-- BAD: Sequential scan on large table
Seq Scan on orders  (cost=0.00..1854231.00 rows=50000000 width=120)
  Filter: (status = 'pending')
  Rows Removed by Filter: 49500000

-- BAD: Nested loop with high iterations
Nested Loop  (cost=0.43..2847593.20 rows=12500000 width=240)
  ->  Seq Scan on users  (cost=0.00..1250.00 rows=50000 width=120)
  ->  Index Scan on orders  (cost=0.43..45.73 rows=250 width=120)
       Index Cond: (orders.user_id = users.id)

---

2. Indexing Strategies

Index Types

-- B-tree (default, most common)
CREATE INDEX idx_users_email ON users(email);

-- Hash (equality only, rarely better than B-tree)
CREATE INDEX idx_users_id_hash ON users USING hash(id);

-- GIN (arrays, JSONB, full-text search)
CREATE INDEX idx_products_tags ON products USING gin(tags);
CREATE INDEX idx_users_data ON users USING gin(metadata jsonb_path_ops);

-- GiST (geometric, range types, full-text)
CREATE INDEX idx_locations_point ON locations USING gist(coordinates);

Composite Indexes

-- Order matters! Column with = first, then range/sort
CREATE INDEX idx_orders_user_status_date
ON orders(user_id, status, created_at DESC);

-- This index supports:
-- WHERE user_id = ?
-- WHERE user_id = ? AND status = ?
-- WHERE user_id = ? AND status = ? ORDER BY created_at DESC
-- WHERE user_id = ? ORDER BY created_at DESC

-- This index does NOT efficiently support:
-- WHERE status = ? (user_id not in query)
-- WHERE created_at > ? (leftmost column not in query)

Partial Indexes

-- Index only active users (smaller, faster)
CREATE INDEX idx_users_active_email
ON users(email)
WHERE status = 'active';

-- Index only recent orders
CREATE INDEX idx_orders_recent
ON orders(created_at DESC)
WHERE created_at > CURRENT_DATE - INTERVAL '90 days';

-- Index only unprocessed items
CREATE INDEX idx_queue_pending
ON job_queue(priority DESC, created_at)
WHERE processed_at IS NULL;

Covering Indexes (Index-Only Scans)

-- Include non-indexed columns to avoid heap lookup
CREATE INDEX idx_users_email_covering
ON users(email)
INCLUDE (name, created_at);

-- Query can be satisfied from index alone
SELECT name, created_at FROM users WHERE email = 'test@example.com';
-- Result: Index Only Scan

Index Maintenance

-- Check index usage
SELECT
    schemaname,
    tablename,
    indexname,
    idx_scan,
    idx_tup_read,
    idx_tup_fetch,
    pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
ORDER BY idx_scan ASC;

-- Find unused indexes (candidates for removal)
SELECT indexrelid::regclass as index,
       relid::regclass as table,
       pg_size_pretty(pg_relation_size(indexrelid)) as size
FROM pg_stat_user_indexes
WHERE idx_scan = 0
  AND indexrelid NOT IN (SELECT conindid FROM pg_constraint);

-- Rebuild bloated indexes
REINDEX INDEX CONCURRENTLY idx_orders_user_id;

---

3. N+1 Query Problem

The Problem

// BAD: N+1 queries
const users = await db.query('SELECT * FROM users LIMIT 100');

for (const user of users) {
  // This runs 100 times!
  const orders = await db.query(
    'SELECT * FROM orders WHERE user_id = $1',
    [user.id]
  );
  user.orders = orders;
}
// Total queries: 1 + 100 = 101

Solution 1: JOIN

// GOOD: Single query with JOIN
const usersWithOrders = await db.query(`
  SELECT u.*, o.id as order_id, o.total, o.status
  FROM users u
  LEFT JOIN orders o ON o.user_id = u.id
  LIMIT 100
`);
// Total queries: 1

Solution 2: Batch Loading (DataLoader pattern)

// GOOD: Two queries with batch loading
const users = await db.query('SELECT * FROM users LIMIT 100');
const userIds = users.map(u => u.id);

const orders = await db.query(
  'SELECT * FROM orders WHERE user_id = ANY($1)',
  [userIds]
);

// Group orders by user_id
const ordersByUser = groupBy(orders, 'user_id');
users.forEach(user => {
  user.orders = ordersByUser[user.id] || [];
});
// Total queries: 2

Solution 3: ORM Eager Loading

// Prisma
const users = await prisma.user.findMany({
  take: 100,
  include: { orders: true }
});

// TypeORM
const users = await userRepository.find({
  take: 100,
  relations: ['orders']
});

// Sequelize
const users = await User.findAll({
  limit: 100,
  include: [{ model: Order }]
});

Detecting N+1 in Production

// Query logging middleware
let queryCount = 0;
const originalQuery = db.query;

db.query = async (...args) => {
  queryCount++;
  if (queryCount > 10) {
    console.warn(`High query count: ${queryCount} in single request`);
    console.trace();
  }
  return originalQuery.apply(db, args);
};

---

4. Connection Pooling

Why Pooling Matters

Without pooling:
Request → Create connection → Query → Close connection
         (50-100ms overhead)

With pooling:
Request → Get connection from pool → Query → Return to pool
         (0-1ms overhead)

pg-pool Configuration

import { Pool } from 'pg';

const pool = new Pool({
  host: process.env.DB_HOST,
  port: 5432,
  database: process.env.DB_NAME,
  user: process.env.DB_USER,
  password: process.env.DB_PASSWORD,

  // Pool settings
  min: 5,                    // Minimum connections
  max: 20,                   // Maximum connections
  idleTimeoutMillis: 30000,  // Close idle connections after 30s
  connectionTimeoutMillis: 5000, // Fail if can't connect in 5s

  // Statement timeout (cancel long queries)
  statement_timeout: 30000,
});

// Health check
pool.on('error', (err, client) => {
  console.error('Unexpected pool error', err);
});

Pool Sizing Formula

Optimal connections = (CPU cores * 2) + effective_spindle_count

For SSD with 4 cores:
connections = (4 * 2) + 1 = 9

For multiple app servers:
connections_per_server = total_connections / num_servers

PgBouncer for High Scale

# pgbouncer.ini
[databases]
mydb = host=localhost port=5432 dbname=mydb

[pgbouncer]
listen_port = 6432
listen_addr = 0.0.0.0
auth_type = md5
auth_file = /etc/pgbouncer/userlist.txt
pool_mode = transaction
max_client_conn = 1000
default_pool_size = 20
reserve_pool_size = 5

---

5. Query Optimization Patterns

Pagination Optimization

-- BAD: OFFSET is slow for large values
SELECT * FROM orders ORDER BY created_at DESC LIMIT 20 OFFSET 10000;
-- Must scan 10,020 rows, discard 10,000

-- GOOD: Cursor-based pagination
SELECT * FROM orders
WHERE created_at < '2024-01-15T10:00:00Z'
ORDER BY created_at DESC
LIMIT 20;
-- Only scans 20 rows

Batch Updates

-- BAD: Individual updates
UPDATE orders SET status = 'shipped' WHERE id = 1;
UPDATE orders SET status = 'shipped' WHERE id = 2;
-- ...repeat 1000 times

-- GOOD: Batch update
UPDATE orders
SET status = 'shipped'
WHERE id = ANY(ARRAY[1, 2, 3, ...1000]);

-- GOOD: Update from values
UPDATE orders o
SET status = v.new_status
FROM (VALUES
  (1, 'shipped'),
  (2, 'delivered'),
  (3, 'cancelled')
) AS v(id, new_status)
WHERE o.id = v.id;

Avoiding SELECT *

-- BAD: Fetches all columns including large text/blob
SELECT * FROM articles WHERE published = true;

-- GOOD: Only fetch needed columns
SELECT id, title, summary, author_id, published_at
FROM articles
WHERE published = true;

Using EXISTS vs IN

-- For checking existence, EXISTS is often faster
-- BAD
SELECT * FROM users
WHERE id IN (SELECT user_id FROM orders WHERE total > 1000);

-- GOOD (for large subquery results)
SELECT * FROM users u
WHERE EXISTS (
  SELECT 1 FROM orders o
  WHERE o.user_id = u.id AND o.total > 1000
);

Materialized Views for Complex Aggregations

-- Create materialized view for expensive aggregations
CREATE MATERIALIZED VIEW daily_sales_summary AS
SELECT
  date_trunc('day', created_at) as date,
  product_id,
  COUNT(*) as order_count,
  SUM(quantity) as total_quantity,
  SUM(total) as total_revenue
FROM orders
GROUP BY date_trunc('day', created_at), product_id;

-- Create index on materialized view
CREATE INDEX idx_daily_sales_date ON daily_sales_summary(date);

-- Refresh periodically
REFRESH MATERIALIZED VIEW CONCURRENTLY daily_sales_summary;

---

6. Database Migrations

Migration Best Practices

-- Always include rollback
-- migrations/20240115_001_add_user_status.sql
-- UP
ALTER TABLE users ADD COLUMN status VARCHAR(20) DEFAULT 'active';
CREATE INDEX CONCURRENTLY idx_users_status ON users(status);

-- DOWN (in separate file or comment)
DROP INDEX CONCURRENTLY IF EXISTS idx_users_status;
ALTER TABLE users DROP COLUMN IF EXISTS status;

Safe Column Addition

-- SAFE: Add nullable column (no table rewrite)
ALTER TABLE users ADD COLUMN phone VARCHAR(20);

-- SAFE: Add column with volatile default (PG 11+)
ALTER TABLE users ADD COLUMN created_at TIMESTAMP DEFAULT NOW();

-- UNSAFE: Add column with constant default (table rewrite before PG 11)
-- ALTER TABLE users ADD COLUMN score INTEGER DEFAULT 0;

-- SAFE alternative for constant default:
ALTER TABLE users ADD COLUMN score INTEGER;
UPDATE users SET score = 0 WHERE score IS NULL;
ALTER TABLE users ALTER COLUMN score SET DEFAULT 0;
ALTER TABLE users ALTER COLUMN score SET NOT NULL;

Safe Index Creation

-- UNSAFE: Locks table
CREATE INDEX idx_orders_user ON orders(user_id);

-- SAFE: Non-blocking
CREATE INDEX CONCURRENTLY idx_orders_user ON orders(user_id);

-- Note: CONCURRENTLY cannot run in a transaction

Safe Column Removal

-- Step 1: Stop writing to column (application change)
-- Step 2: Wait for all deployments
-- Step 3: Drop column
ALTER TABLE users DROP COLUMN IF EXISTS legacy_field;

---

7. Monitoring and Alerting

Key Metrics to Monitor

-- Active connections
SELECT count(*) FROM pg_stat_activity WHERE state = 'active';

-- Connection by state
SELECT state, count(*)
FROM pg_stat_activity
GROUP BY state;

-- Long-running queries
SELECT
  pid,
  now() - pg_stat_activity.query_start AS duration,
  query,
  state
FROM pg_stat_activity
WHERE (now() - pg_stat_activity.query_start) > interval '5 minutes'
  AND state != 'idle';

-- Table bloat
SELECT
  schemaname,
  tablename,
  pg_size_pretty(pg_total_relation_size(schemaname||'.'||tablename)) as total_size,
  pg_size_pretty(pg_relation_size(schemaname||'.'||tablename)) as table_size,
  pg_size_pretty(pg_indexes_size(schemaname||'.'||tablename)) as index_size
FROM pg_tables
WHERE schemaname = 'public'
ORDER BY pg_total_relation_size(schemaname||'.'||tablename) DESC
LIMIT 10;

pg_stat_statements for Query Analysis

-- Enable extension
CREATE EXTENSION IF NOT EXISTS pg_stat_statements;

-- Find slowest queries
SELECT
  round(total_exec_time::numeric, 2) as total_time_ms,
  calls,
  round(mean_exec_time::numeric, 2) as avg_time_ms,
  round((100 * total_exec_time / sum(total_exec_time) over())::numeric, 2) as percentage,
  query
FROM pg_stat_statements
ORDER BY total_exec_time DESC
LIMIT 10;

-- Find most frequent queries
SELECT
  calls,
  round(total_exec_time::numeric, 2) as total_time_ms,
  round(mean_exec_time::numeric, 2) as avg_time_ms,
  query
FROM pg_stat_statements
ORDER BY calls DESC
LIMIT 10;

Alert Thresholds

---

Quick Reference: PostgreSQL Commands

-- Check table sizes
SELECT pg_size_pretty(pg_total_relation_size('orders'));

-- Check index sizes
SELECT pg_size_pretty(pg_indexes_size('orders'));

-- Kill a query
SELECT pg_cancel_backend(pid);  -- Graceful
SELECT pg_terminate_backend(pid);  -- Force

-- Check locks
SELECT * FROM pg_locks WHERE granted = false;

-- Vacuum analyze (update statistics)
VACUUM ANALYZE orders;

-- Check autovacuum status
SELECT * FROM pg_stat_user_tables WHERE relname = 'orders';

Backend Engineer — Forcing-Question Library

Discipline (Matt Pocock, derived from `engineering/grill-me`, MIT): walk these one at a time. Do not skip ahead. Do not bundle. Answers must be written down. If the user cannot answer one, that is your next investigation — stop and surface the gap.

These seven questions gate every meaningful backend decision: pattern pick (monolith / modular / services / serverless), database choice, sync vs. async, tenancy model, SLO commitment.

---

Q1 — "What is your read/write ratio, and what is your one-year QPS forecast at p99?"

Recommended answer: two numbers (e.g., "20:1 reads-to-writes; 200 QPS p99 at 12 months, derived from current 30 QPS × 3× growth × 2× peak"). Both must trace to evidence (current production traffic + named growth model), not vibes.

Why it's the first question: every database, caching, queue, and sharding decision changes shape based on these numbers. A 100:1 read-heavy workload at < 1000 QPS is a Postgres-with-read-replicas problem — not a Cassandra problem. A 1:1 write-heavy workload at 5000 QPS p99 is a partitioning problem from day one.

Kill criterion: "we'll need to scale" with no QPS number — STOP. Pull current traffic from metrics; use the team's funding-stage growth model. Without numbers, every architecture choice is a guess.

Canon: Martin Kleppmann, Designing Data-Intensive Applications (2017), ch. 1 + ch. 5 (replication); Pat Helland, Life beyond Distributed Transactions (2007); Werner Vogels, Eventually Consistent (ACM, 2008).

---

Q2 — "Tenancy model: single-tenant, shared multi-tenant, or isolated multi-tenant?"

Recommended answer: one of the three, with explicit rationale tied to data-sensitivity (Q4). B2C → shared multi-tenant default; B2B SaaS → shared multi-tenant with row-level isolation; B2B regulated (healthcare, defense, finance) → isolated multi-tenant or single-tenant.

Why it matters: the tenancy model decides 80% of the data-access pattern. Migrating between models is expensive (3–9 months in most cases). Picking implicitly leaves the team rebuilding in year 2 to win an enterprise deal that requires tenancy isolation.

Kill criterion: "single-tenant for every customer" without an enterprise-pricing model — STOP. Single-tenant cost economics only work at $100K+ ARR per tenant; for everything else, shared with isolation guarantees.

Canon: AWS SaaS Tenant Isolation Strategies whitepaper (2021); Tomasz Tunguz, Multi-tenancy economics for SaaS (2019); Aaron Patterson + Rails security advisories (2014–2024) on row-level isolation patterns.

---

Q3 — "Sync request/response, async (queue), or event-driven? Pick a default and a rationale."

Recommended answer: one of the three as the default, with the named exception class (e.g., "sync default for all customer-facing APIs; async via Postgres LISTEN/NOTIFY for emails + webhooks; defer event-driven until 2nd team owns 2nd bounded context").

Why it matters: premature event-driven architecture is the #1 architecture-failure mode in mid-stage startups. It distributes the problem across nine systems before the team understands the original one. Reinertsen + Helland are both explicit: pick sync default and EARN your way into async.

Kill criterion: "event-driven across all services" with team size < 20 — STOP. Reduce to sync-default with an explicit async lane for genuinely-async work (emails, webhooks, batch processing).

Canon: Donald Reinertsen, Principles of Product Development Flow (2009), Principle Q5 (queueing theory); Pat Helland, Life beyond Distributed Transactions (2007); Martin Fowler, What do you mean by Event-Driven? (martinfowler.com, 2017); Bernd Rücker, Practical Process Automation (2021).

---

Q4 — "Data sensitivity tier: public, internal, PII, PHI, or PCI?"

Recommended answer: the highest tier present in the system. PII triggers GDPR / CCPA / state privacy laws + encryption-at-rest + audit logs. PHI triggers HIPAA + BAA chain + dedicated infrastructure or HIPAA-compliant managed services. PCI triggers PCI-DSS Level 1–4 with attached scope-reduction obligations.

Why it matters: data sensitivity changes the floor of every other decision. PHI + a single shared-tenant Postgres + no audit logging = enforcement risk. PCI in scope + handing card data to a startup-built API = avoidable scope. Stripe / Plaid / Auth0 exist specifically to remove scope.

Kill criterion: PHI or PCI in scope + no named compliance owner + no encryption-at-rest plan — STOP. Bring in ra-qm-team skill (HIPAA / FDA) or escalate to cs-ciso-advisor.

Canon: HIPAA Security Rule (45 CFR § 164); PCI-DSS v4.0 (2024); GDPR Articles 5, 25, 32 (EU 2016/679); NIST SP 800-53 rev. 5 (security controls); CISA Secure by Design guidance (2023+).

---

Q5 — "Monolith, modular monolith, or microservices — and what is the team-size justification?"

Recommended answer: modular monolith default for team size < 30; microservices ONLY when (a) team size ≥ 30 with named domain owners, (b) bounded contexts have provably-independent deployment cadence, AND (c) a platform team exists or is funded. Anything else → modular monolith.

Why it matters: Sam Newman's MonolithFirst is the canon. Premature microservices distribute the design problem across N services + a network. Andy Hunt's Pragmatic Programmer second edition (2019) reaffirms: the cost of a microservice is the cost of a system, not a module.

Kill criterion: "microservices because [reason that isn't team-size + bounded-context independence + platform team]" — STOP. Modular monolith with clear module boundaries. Extract a service only when the second team needs to own it.

Canon: Sam Newman, Building Microservices 2e (2021), ch. 3 "Splitting the Monolith"; Martin Fowler, MonolithFirst (2015); Susan Fowler, Production-Ready Microservices (2017); Matthew Skelton & Manuel Pais, Team Topologies (2019); Eric Evans, Domain-Driven Design (2003).

---

Q6 — "What is your RPO and RTO?"

Recommended answer: two numbers (e.g., "RPO 5 min, RTO 30 min for prod database; RPO 24h, RTO 4h for analytics warehouse"). Different surfaces can have different targets. Both must be named in writing.

Why it matters: RPO (data loss tolerance) and RTO (recovery time tolerance) decide backup cadence, replication topology, multi-region cost, and runbook ownership. Without them, the team rebuilds the same disaster-recovery surprise during every outage.

Kill criterion: customer-facing prod database + no RPO/RTO documented — STOP. Define them. Then implement the runbook + restore drill BEFORE the launch.

Canon: Google SRE Workbook (Beyer et al., 2018), ch. 7 + ch. 8 on disaster recovery; ISO 22301 (Business Continuity); AWS Disaster Recovery of Workloads on AWS whitepaper (2024).

---

Q7 — "What is the SLO (service-level objective), and who is the named error-budget consumer?"

Recommended answer: an SLO tied to a measurable SLI (e.g., "99.9% of requests succeed in < 500ms over rolling 30 days"), AND a named team that consumes the error budget (e.g., "engineering — when budget is < 25% remaining, feature work halts and reliability work starts").

Why it matters: without a named SLO consumer, the error budget is rhetorical. Without a measurable SLO, "reliability" is a vibe. Google's SRE program is built around this loop: SLI → SLO → error budget → budget consumer. Fork into slo-architect to formalize the design.

Kill criterion: "we want high availability" with no SLO number AND no budget consumer — STOP. Pick a number (99%, 99.5%, 99.9%, 99.99%) and the consumer (engineering, product, executive). No SLO = no error budget = no reliability work prioritization.

Canon: Google SRE Workbook (2018), ch. 2–4; Niall Murphy + Betsy Beyer, Site Reliability Engineering (2016); Andrew Clay Shafer, The SLO Handbook (2019); Google Implementing SLOs (engineering.google.com, 2024).

---

How to use this library in a conversation

1. State the rule first — seven questions, one at a time, before any DB / API / pattern recommendation. 2. One question per turn. No bundling. 3. Recommend the answer. Cite the canon every time. 4. Surface the kill criterion. If the user trips one, stop and resolve the gap. 5. Track the answers. Write them to /tmp/backend-grill-<date>.md. 6. After Q7, run `backend_decision_engine.py` with the seven answers as inputs.