Senior Backend

Senior Backend Engineer

Scaffold and review backend services: API design and OpenAPI-driven code generation for Express/Fastify/Koa, PostgreSQL schema analysis and migration generation, HTTP load testing, and production security hardening. Outputs ready-to-run route handlers, Zod validators, TypeScript types, migrations with rollbacks, and load-test reports.

Core Capabilities

• API scaffolding — generate route handlers, validation middleware, TypeScript types, and OpenAPI specs across Express, Fastify, and Koa.
• Database optimization — schema analysis, missing-index detection, N+1 risk detection, and migration generation with paired rollback scripts.
• Load testing — configurable concurrency with latency percentiles (P50/P90/P95/P99), throughput, error rates, and endpoint comparison.
• Security hardening — JWT config, rate limiting, input validation (Zod), and security headers (helmet) for production readiness.
• Standardized contracts — consistent data/error/meta response envelope and HTTP status conventions.

When to Use

• Designing a new API or refactoring existing endpoints.
• Slow queries or database performance needs improvement.
• Preparing an API for production or after a security review.
• Building regression/load-test baselines for backend endpoints.

Clarify First

Before scaffolding, confirm these inputs. If any is unknown or vague, ASK — do not assume:

• [ ] Framework — Express / Fastify / Koa (--framework; changes the generated route handlers, validators, and types)
• [ ] API contract source — the OpenAPI spec or endpoint list to scaffold from (the input the scaffolder reads)
• [ ] Database intent — the schema file and whether you want analysis vs migration generation (drives database_migration_tool.py)

Stop rule: ask only the 2-3 that most change the output. If the user says "just draft it," proceed and list your assumptions at the top of the artifact.

Tools

References

Load the reference that matches the task — keep this file lean and pull detail on demand:

• [references/tools-reference.md](references/tools-reference.md) — full usage examples, flag tables, and sample output for the scaffolder, migration tool, and load tester, plus quick-start and common commands. Read when running any tool.
• [references/workflows-and-patterns.md](references/workflows-and-patterns.md) — the API-design, database-optimization, and security-hardening workflows, common response/index patterns, the troubleshooting table, and the success-criteria bar. Read when designing or hardening a service.
• [references/api_design_patterns.md](references/api_design_patterns.md) — REST vs GraphQL, versioning, error handling, pagination. Read when designing new APIs.
• [references/database_optimization_guide.md](references/database_optimization_guide.md) — indexing strategies, query optimization, N+1 solutions. Read when fixing slow queries.
• [references/backend_security_practices.md](references/backend_security_practices.md) — OWASP Top 10, auth patterns, input validation. Read when hardening security.

Scope & Limitations

What this skill covers:

• REST API design, scaffolding, and OpenAPI-driven code generation for Express, Fastify, and Koa
• PostgreSQL schema analysis, index optimization, migration generation with rollback support
• HTTP load testing with latency percentile analysis, throughput measurement, and endpoint comparison
• Backend security patterns including JWT configuration, rate limiting, input validation, and security headers

What this skill does NOT cover:

• Frontend development, UI components, or client-side state management -- see senior-frontend
• Infrastructure provisioning, container orchestration, or CI/CD pipeline setup -- see senior-devops
• GraphQL schema design, resolvers, or subscriptions -- see senior-fullstack
• Application performance monitoring (APM), distributed tracing, or log aggregation -- see senior-secops

Integration Points

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

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 Tools Reference

Read this when running the API scaffolder, database migration tool, or load tester — full usage examples, flag tables, and sample output.

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 in src/routes/
# - GET /users (listUsers)
# - POST /users (createUser)
# - GET /users/{id} (getUser)
# - PUT /users/{id} (updateUser)
# - DELETE /users/{id} (deleteUser)
# ...
# Created validation middleware: src/middleware/validators.ts
# Created TypeScript types: src/types/api.ts

# 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:
# === Database Analysis Report ===
# Tables: 24
# Total rows: 1,247,832
#
# MISSING INDEXES (5 found):
#   orders.user_id - 847ms avg query time, ADD INDEX recommended
#   products.category_id - 234ms avg query time, ADD INDEX recommended
#
# N+1 QUERY RISKS (3 found):
#   users -> orders relationship (no eager loading)
#
# SUGGESTED MIGRATIONS:
#   1. Add index on orders(user_id)
#   2. Add index on products(category_id)
#   3. Add composite index on order_items(order_id, product_id)

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

# Output:
# Generated migration: migrations/20240115_add_user_indexes.sql
# Generated rollback: migrations/20240115_add_user_indexes_rollback.sql

# 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:
# === Load Test Results ===
# Target: https://api.example.com/users
# Duration: 30s | Concurrency: 50
#
# THROUGHPUT:
#   Total requests: 15,247
#   Requests/sec: 508.2
#   Successful: 15,102 (99.0%)
#   Failed: 145 (1.0%)
#
# LATENCY (ms):
#   Min: 12
#   Avg: 89
#   P50: 67
#   P95: 198
#   P99: 423
#   Max: 1,247
#
# ERRORS:
#   Connection timeout: 89
#   HTTP 503: 56
#
# RECOMMENDATION: P99 latency (423ms) exceeds 200ms target.
# Consider: connection pooling, query optimization, or horizontal scaling.

# 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

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

Tool Reference

api_scaffolder.py

Purpose: Generate Express.js/Fastify/Koa route handlers, Zod validators, and TypeScript types from an OpenAPI specification.

Usage:

python scripts/api_scaffolder.py <spec> [flags]

Flags:

Example:

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

API Scaffolder - Express
Spec: openapi.yaml
Output: src/routes/
--------------------------------------------------
Loaded: User Service API v1.0.0
  Generated: src/routes/types.ts
  Generated: src/routes/validators.ts
  Generated: src/routes/users.routes.ts (5 handlers)
  Generated: src/routes/index.ts
--------------------------------------------------
Generated 5 route handlers
Generated 3 type definitions
Output: src/routes/

Output Formats: Human-readable console output by default. Add --json for machine-readable JSON with status, generated_files, routes_count, and types_count fields.

database_migration_tool.py

Purpose: Analyze SQL schema files for issues, compare schemas to generate migrations with rollback scripts, and suggest missing indexes.

Usage:

python scripts/database_migration_tool.py <schema> [flags]

Flags:

Example:

python scripts/database_migration_tool.py schema.sql --analyze --verbose

Database Migration Tool
Schema: schema.sql
--------------------------------------------------
Parsed 8 tables

Analysis Results:
  Tables: 8
  Errors: 1
  Warnings: 3
  Suggestions: 7

ERRORS:
  [audit_log] Table 'audit_log' has no primary key
    Suggestion: Add a primary key column (e.g., 'id SERIAL PRIMARY KEY')

WARNINGS:
  [orders] Foreign key column 'user_id' is not indexed
    Suggestion: CREATE INDEX idx_orders_user_id ON orders(user_id);

Output Formats: Human-readable console output by default. Add --json for structured JSON with issues_detail array containing severity, category, table, message, and suggestion for each finding. When using --compare --output, generates timestamped _migration.sql and _migration_rollback.sql files.

api_load_tester.py

Purpose: Perform HTTP load testing with configurable concurrency, measuring latency percentiles (p50/p90/p95/p99), throughput, error rates, and optional endpoint comparison.

Usage:

python scripts/api_load_tester.py <urls...> [flags]

Flags:

Example:

python scripts/api_load_tester.py https://api.example.com/users \
  --method GET \
  --header "Authorization: Bearer tok_abc123" \
  --concurrency 50 \
  --duration 30 \
  --verbose

============================================================
LOAD TEST RESULTS
============================================================

Target: https://api.example.com/users
Method: GET
Duration: 30.2s
Concurrency: 50

THROUGHPUT:
  Total requests: 14,832
  Requests/sec: 491.1
  Successful: 14,710 (99.2%)
  Failed: 122

LATENCY (ms):
  Min: 11.3
  Avg: 92.4
  P50: 71.2
  P90: 165.8
  P95: 201.3
  P99: 387.6
  Max: 1,102.5
  StdDev: 89.2

TRANSFER:
  Total bytes: 45,291,520
  Throughput: 12.01 Mbps

RECOMMENDATIONS:
  Warning: P99 latency (388ms) exceeds 500ms
    Consider: Connection pooling, query optimization, caching
  Performance looks good for this load level
============================================================

Output Formats: Human-readable console report by default with latency distribution and recommendations. Add --json for structured JSON output. Use --output results.json to write results to a file. When using --compare with two URLs, outputs a side-by-side metric comparison table.

Backend Workflows & Patterns

Read this when designing an API, optimizing a database, hardening security, or looking up common response/index patterns, troubleshooting, and the success bar.

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 },
}));

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

Troubleshooting

Success Criteria

• API p99 latency under 200ms at production concurrency levels, verified by api_load_tester.py
• Zero N+1 query patterns detected in schema analysis via database_migration_tool.py --analyze
• All foreign key columns indexed with no "missing index" warnings from the migration tool
• 100% of generated routes include input validation middleware (Zod schemas auto-generated from OpenAPI spec)
• Success rate above 99.5% during sustained load tests at target concurrency for 60+ seconds
• Every migration paired with a rollback script to enable zero-downtime deployment reversals
• API response format consistency across all endpoints following the standardized data/error/meta envelope pattern