Senior Backend Engineer

资深后端工程师

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

后端开发模式、API设计、数据库优化及安全实践。

Quick Start

快速开始

bash

undefined

bash

undefined

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

---

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

undefined

从架构定义生成API路由处理器、中间件及OpenAPI规范。

输入： OpenAPI规范（YAML/JSON）或数据库架构 输出： 路由处理器、验证中间件、TypeScript类型定义

用法：

bash

undefined

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

---

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


**支持的框架：**
- 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

undefined

分析数据库架构、检测变更并生成支持回滚的迁移文件。

输入： 数据库连接字符串或架构文件 输出： 迁移文件、架构差异报告、优化建议

用法：

bash

undefined

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

---

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

undefined

执行可配置并发数的HTTP负载测试，测量延迟百分位数及吞吐量。

输入： API端点URL及测试配置 输出： 包含延迟分布、错误率、吞吐量指标的性能报告

用法：

bash

undefined

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

---

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

API设计工作流

Use when designing a new API or refactoring existing endpoints.

Step 1: Define resources and operations

yaml

undefined

用于设计新API或重构现有端点时。

步骤1：定义资源及操作

yaml

undefined

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

undefined

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'


**步骤2：生成路由脚手架**
```bash
python scripts/api_scaffolder.py openapi.yaml --framework express --output src/routes/

步骤3：实现业务逻辑

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

步骤4：添加验证中间件

bash

undefined

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


**步骤5：生成更新后的OpenAPI规范**
```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

undefined

用于查询缓慢或需要提升数据库性能时。

步骤1：分析当前性能

bash

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

步骤2：识别慢查询

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)

步骤3：生成索引迁移文件

bash

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

步骤4：测试迁移（预演）

bash

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

步骤5：应用并验证

bash

undefined

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

---

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

undefined

用于API准备上线或安全审查后。

步骤1：检查身份验证设置

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

步骤2：添加速率限制

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

步骤3：验证所有输入

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

步骤4：使用攻击模式进行负载测试

bash

undefined

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

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


**步骤5：检查安全头**
```typescript
import helmet from 'helmet';

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

Reference Documentation

参考文档

File	Contains	Use When
`references/api_design_patterns.md`	REST vs GraphQL, versioning, error handling, pagination	Designing new APIs
`references/database_optimization_guide.md`	Indexing strategies, query optimization, N+1 solutions	Fixing slow queries
`references/backend_security_practices.md`	OWASP Top 10, auth patterns, input validation	Security hardening

文件	包含内容	使用场景
`references/api_design_patterns.md`	REST与GraphQL对比、版本控制、错误处理、分页	设计新API时
`references/database_optimization_guide.md`	索引策略、查询优化、N+1问题解决方案	修复慢查询时
`references/backend_security_practices.md`	OWASP Top 10、身份验证模式、输入验证	安全加固时

Common Patterns Quick Reference

常用模式速查

REST API Response Format

REST API响应格式

json

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

json

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

Error Response Format

错误响应格式

json

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

json

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

HTTP Status Codes

HTTP状态码

Code	Use Case
200	Success (GET, PUT, PATCH)
201	Created (POST)
204	No Content (DELETE)
400	Validation error
401	Authentication required
403	Permission denied
404	Resource not found
429	Rate limit exceeded
500	Internal server error

状态码	使用场景
200	成功（GET、PUT、PATCH请求）
201	创建成功（POST请求）
204	无内容（DELETE请求）
400	验证错误
401	需要身份验证
403	权限不足
404	资源不存在
429	超出速率限制
500	服务器内部错误

Database Index Strategy

数据库索引策略

sql

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

sql

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

常用命令

bash

undefined

bash

undefined

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

undefined

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

undefined

senior-backend

Original

Translation

Senior Backend Engineer

资深后端工程师

Table of Contents

目录

Quick Start

快速开始

Generate API routes from OpenAPI spec

Generate API routes from OpenAPI spec

Analyze database schema and generate migrations

Analyze database schema and generate migrations

Load test an API endpoint

Load test an API endpoint

Tools Overview

工具概览

1. API Scaffolder

1. API Scaffolder

Generate Express routes from OpenAPI spec

Generate Express routes from OpenAPI spec

Output:

Output:

Generated 12 route handlers in src/routes/

Generated 12 route handlers in src/routes/

- GET /users (listUsers)

- GET /users (listUsers)

- POST /users (createUser)

- POST /users (createUser)

- GET /users/{id} (getUser)

- GET /users/{id} (getUser)

- PUT /users/{id} (updateUser)

- PUT /users/{id} (updateUser)

- DELETE /users/{id} (deleteUser)

- DELETE /users/{id} (deleteUser)

...

...

Created validation middleware: src/middleware/validators.ts

Created validation middleware: src/middleware/validators.ts

Created TypeScript types: src/types/api.ts

Created TypeScript types: src/types/api.ts

Generate from database schema

Generate from database schema

Generate OpenAPI spec from existing routes

Generate OpenAPI spec from existing routes

2. Database Migration Tool

2. Database Migration Tool

Analyze current schema and suggest optimizations

Analyze current schema and suggest optimizations

Output:

Output:

=== Database Analysis Report ===

=== Database Analysis Report ===

Tables: 24

Tables: 24

Total rows: 1,247,832

Total rows: 1,247,832

MISSING INDEXES (5 found):

MISSING INDEXES (5 found):

orders.user_id - 847ms avg query time, ADD INDEX recommended

orders.user_id - 847ms avg query time, ADD INDEX recommended

products.category_id - 234ms avg query time, ADD INDEX recommended

products.category_id - 234ms avg query time, ADD INDEX recommended

N+1 QUERY RISKS (3 found):

N+1 QUERY RISKS (3 found):

users -> orders relationship (no eager loading)

users -> orders relationship (no eager loading)

SUGGESTED MIGRATIONS:

SUGGESTED MIGRATIONS:

1. Add index on orders(user_id)

1. Add index on orders(user_id)

2. Add index on products(category_id)

2. Add index on products(category_id)

3. Add composite index on order_items(order_id, product_id)

3. Add composite index on order_items(order_id, product_id)

Generate migration from schema diff

Generate migration from schema diff

Output:

Output:

Generated migration: migrations/20240115_add_user_indexes.sql