Senior Backend Engineer

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

Table of Contents

• Quick Start
• Tools Overview
  • API Scaffolder
  • Database Migration Tool
  • API Load Tester
• Backend Development Workflows
  • API Design Workflow
  • Database Optimization Workflow
  • Security Hardening Workflow
• Reference Documentation
• Common Patterns Quick Reference

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: ```bash

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: ```bash

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: ```bash

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

Backend Development Workflows

API Design Workflow

Use when designing a new API or refactoring existing endpoints.

Step 1: Define resources and operations ```yaml

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 bash python scripts/api_scaffolder.py openapi.yaml --framework express --output src/routes/

Step 3: Implement business logic ```typescript // 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 ```bash

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**
```bash
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 bash python scripts/database_migration_tool.py --connection $DATABASE_URL --analyze

Step 2: Identify slow queries ```sql -- 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 bash python scripts/database_migration_tool.py --connection $DATABASE_URL \ --suggest-indexes --output migrations/

Step 4: Test migration (dry-run) bash python scripts/database_migration_tool.py --connection $DATABASE_URL \ --migrate migrations/add_indexes.sql --dry-run

Step 5: Apply and verify ```bash

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 typescript // 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 ```typescript 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 ```typescript 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 ```bash

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 ```typescript 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