高级后端工程师技能包：API设计、数据库优化与安全实践工具集

senior-backend by borghei/claude-skills

105 周安装量

53 GitHub Stars

GitHub

安装命令

npx skills add https://github.com/borghei/claude-skills --skill senior-backend

开发性能优化 API

🇨🇳中文介绍

高级后端工程师

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

快速开始

# 根据 OpenAPI 规范生成 API 路由
python scripts/api_scaffolder.py openapi.yaml --framework express --output src/routes/

# 分析数据库模式并生成迁移
python scripts/database_migration_tool.py --connection postgres://localhost/mydb --analyze

# 对 API 端点进行负载测试
python scripts/api_load_tester.py https://api.example.com/users --concurrency 50 --duration 30

工具概览

1. API 脚手架生成器

根据模式定义生成 API 路由处理器、中间件和 OpenAPI 规范。

输入： OpenAPI 规范 (YAML/JSON) 或数据库模式 输出： 路由处理器、验证中间件、TypeScript 类型

用法：

# 根据 OpenAPI 规范生成 Express 路由
python scripts/api_scaffolder.py openapi.yaml --framework express --output src/routes/

# 输出：
# 在 src/routes/ 中生成了 12 个路由处理器
# - GET /users (listUsers)
# - POST /users (createUser)
# - GET /users/{id} (getUser)
# - PUT /users/{id} (updateUser)
# - DELETE /users/{id} (deleteUser)
# ...
# 创建了验证中间件：src/middleware/validators.ts
# 创建了 TypeScript 类型：src/types/api.ts

# 根据数据库模式生成
python scripts/api_scaffolder.py --from-db postgres://localhost/mydb --output src/routes/

# 根据现有路由生成 OpenAPI 规范
python scripts/api_scaffolder.py src/routes/ --generate-spec --output openapi.yaml

广告位招租

在这里展示您的产品或服务

触达数万 AI 开发者，精准高效

联系我们

2. 数据库迁移工具

分析数据库模式，检测变更，并生成支持回滚的迁移文件。

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

# 分析当前模式并提供优化建议
python scripts/database_migration_tool.py --connection postgres://localhost/mydb --analyze

# 输出：
# === 数据库分析报告 ===
# 表数量：24
# 总行数：1,247,832
#
# 缺失的索引 (发现 5 个)：
#   orders.user_id - 平均查询时间 847ms，建议添加索引
#   products.category_id - 平均查询时间 234ms，建议添加索引
#
# N+1 查询风险 (发现 3 个)：
#   users -> orders 关系 (未使用预加载)
#
# 建议的迁移：
#   1. 在 orders(user_id) 上添加索引
#   2. 在 products(category_id) 上添加索引
#   3. 在 order_items(order_id, product_id) 上添加复合索引

# 根据模式差异生成迁移
python scripts/database_migration_tool.py --connection postgres://localhost/mydb \
  --compare schema/v2.sql --output migrations/

# 输出：
# 生成的迁移：migrations/20240115_add_user_indexes.sql
# 生成的回滚文件：migrations/20240115_add_user_indexes_rollback.sql

# 模拟运行迁移
python scripts/database_migration_tool.py --connection postgres://localhost/mydb \
  --migrate migrations/20240115_add_user_indexes.sql --dry-run

3. API 负载测试工具

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

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

# 基本负载测试
python scripts/api_load_tester.py https://api.example.com/users --concurrency 50 --duration 30

# 输出：
# === 负载测试结果 ===
# 目标：https://api.example.com/users
# 持续时间：30s | 并发数：50
#
# 吞吐量：
#   总请求数：15,247
#   请求数/秒：508.2
#   成功：15,102 (99.0%)
#   失败：145 (1.0%)
#
# 延迟 (ms)：
#   最小值：12
#   平均值：89
#   P50：67
#   P95：198
#   P99：423
#   最大值：1,247
#
# 错误：
#   连接超时：89
#   HTTP 503：56
#
# 建议：P99 延迟 (423ms) 超过了 200ms 的目标。
# 考虑：连接池、查询优化或水平扩展。

# 使用自定义请求头和请求体进行测试
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

# 比较两个端点
python scripts/api_load_tester.py https://api.example.com/v1/users https://api.example.com/v2/users \
  --compare --concurrency 50 --duration 30

后端开发工作流

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

步骤 1：定义资源和操作

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

步骤 2：生成路由脚手架

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

步骤 3：实现业务逻辑

// src/routes/users.ts (生成后自定义)
export const createUser = async (req: Request, res: Response) => {
  const { email, name } = req.body;

  // 添加业务逻辑
  const user = await userService.create({ email, name });

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

步骤 4：添加验证中间件

# 验证逻辑根据 OpenAPI 模式自动生成
# src/middleware/validators.ts 包含：
# - 请求体验证
# - 查询参数验证
# - 路径参数验证

步骤 5：生成更新后的 OpenAPI 规范

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

数据库优化工作流

在查询缓慢或数据库性能需要改进时使用。

步骤 1：分析当前性能

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

步骤 2：识别慢查询

-- 检查查询执行计划
EXPLAIN ANALYZE SELECT * FROM orders
WHERE user_id = 123
ORDER BY created_at DESC
LIMIT 10;

-- 查找：顺序扫描 (差)，索引扫描 (好)

步骤 3：生成索引迁移

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

步骤 4：测试迁移 (模拟运行)

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

步骤 5：应用并验证

# 应用迁移
python scripts/database_migration_tool.py --connection $DATABASE_URL \
  --migrate migrations/add_indexes.sql

# 验证改进
python scripts/database_migration_tool.py --connection $DATABASE_URL --analyze

安全加固工作流

在为生产环境准备 API 或进行安全审查后使用。

步骤 1：审查认证设置

// 验证 JWT 配置
const jwtConfig = {
  secret: process.env.JWT_SECRET,  // 必须来自环境变量，切勿硬编码
  expiresIn: '1h',                 // 短期令牌
  algorithm: 'RS256'               // 首选非对称加密
};

步骤 2：添加速率限制

import rateLimit from 'express-rate-limit';

const apiLimiter = rateLimit({
  windowMs: 15 * 60 * 1000,  // 15 分钟
  max: 100,                   // 每个窗口 100 个请求
  standardHeaders: true,
  legacyHeaders: false,
});

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

步骤 3：验证所有输入

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

// 在路由处理器中使用
const data = CreateUserSchema.parse(req.body);

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

# 测试速率限制
python scripts/api_load_tester.py https://api.example.com/login \
  --concurrency 200 --duration 10 --expect-rate-limit

# 测试输入验证
python scripts/api_load_tester.py https://api.example.com/users \
  --method POST \
  --body '{"email": "not-an-email"}' \
  --expect-status 400

步骤 5：审查安全头部

import helmet from 'helmet';

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

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

常用模式速查表

REST API 响应格式

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

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

状态码	使用场景
200	成功 (GET, PUT, PATCH)
201	已创建 (POST)
204	无内容 (DELETE)
400	验证错误
401	需要认证
403	权限不足
404	资源未找到
429	超出速率限制
500	内部服务器错误

数据库索引策略

-- 单列 (等值查找)
CREATE INDEX idx_users_email ON users(email);

-- 复合 (多列查询)
CREATE INDEX idx_orders_user_status ON orders(user_id, status);

-- 部分 (过滤查询)
CREATE INDEX idx_orders_active ON orders(created_at) WHERE status = 'active';

-- 覆盖 (避免表查找)
CREATE INDEX idx_users_email_name ON users(email) INCLUDE (name);

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

# 数据库操作
python scripts/database_migration_tool.py --connection $DATABASE_URL --analyze
python scripts/database_migration_tool.py --connection $DATABASE_URL --migrate file.sql

# 性能测试
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

🇺🇸English

Senior Backend Engineer

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

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:

# 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

Backend Development Workflows

API Design Workflow

Use when designing a new API or refactoring existing endpoints.

Step 1: Define resources and operations

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

Step 2: Generate route scaffolding

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

Step 3: Implement business logic

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

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

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

Step 4: Add validation middleware

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

Step 5: Generate updated OpenAPI spec

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

Database Optimization Workflow

Use when queries are slow or database performance needs improvement.

Step 1: Analyze current performance

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

Step 2: Identify slow queries

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

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

Step 3: Generate index migrations

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

Step 4: Test migration (dry-run)

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

Step 5: Apply and verify

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

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

Security Hardening Workflow

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

Step 1: Review authentication setup

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

Step 2: Add rate limiting

import rateLimit from 'express-rate-limit';

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

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

Step 3: Validate all inputs

import { z } from 'zod';

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

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

Step 4: Load test with attack patterns

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

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

Step 5: Review security headers

import helmet from 'helmet';

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

Reference Documentation

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

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

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

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

Weekly Installs

Repository

borghei/claude-skills

GitHub Stars

First Seen

Jan 24, 2026

Security Audits

Gen Agent Trust HubPass SocketPass SnykPass

Installed on

claude-code56

opencode53

gemini-cli51

codex47

cursor46

github-copilot45

agent-browser 浏览器自动化工具 - Vercel Labs 命令行网页操作与测试

157,400 周安装

高级后端工程师技能包：API设计、数据库优化与安全实践工具集

🇨🇳中文介绍

高级后端工程师

目录

快速开始

工具概览

1. API 脚手架生成器

相关 Skills

2. 数据库迁移工具

3. API 负载测试工具

后端开发工作流

API 设计工作流

数据库优化工作流

安全加固工作流

参考文档

常用模式速查表

REST API 响应格式

错误响应格式

HTTP 状态码

数据库索引策略

常用命令

🇺🇸English

Senior Backend Engineer

Table of Contents

Quick Start

Tools Overview

1. API Scaffolder

2. Database Migration Tool

3. API Load Tester

Backend Development Workflows

API Design Workflow

Database Optimization Workflow

Security Hardening Workflow

Reference Documentation

Common Patterns Quick Reference

REST API Response Format

Error Response Format

HTTP Status Codes

Database Index Strategy

Common Commands

最新 Skills