🚀 后端技术方案标准模板

# 1. 用户表 (users)
CREATE TABLE users (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    username        VARCHAR(50) UNIQUE NOT NULL,
    email           VARCHAR(100) UNIQUE NOT NULL,
    password_hash   VARCHAR(255) NOT NULL,
    role            VARCHAR(20) NOT NULL DEFAULT 'developer',
    department      VARCHAR(50),
    status          VARCHAR(20) DEFAULT 'active',
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    last_login_at   TIMESTAMP WITH TIME ZONE,
    
    INDEX idx_users_username (username),
    INDEX idx_users_email (email),
    INDEX idx_users_role (role)
);

# 2. 项目表 (projects)
CREATE TABLE projects (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name            VARCHAR(100) NOT NULL,
    description     TEXT,
    status          VARCHAR(20) DEFAULT 'planning',
    tech_stack      JSONB,
    created_by      UUID REFERENCES users(id),
    start_date      DATE,
    end_date        DATE,
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_projects_status (status),
    INDEX idx_projects_created_by (created_by)
);

# 3. 需求表 (requirements)
CREATE TABLE requirements (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id      UUID REFERENCES projects(id) ON DELETE CASCADE,
    title           VARCHAR(200) NOT NULL,
    content         TEXT NOT NULL,
    priority        VARCHAR(20) DEFAULT 'medium',
    status          VARCHAR(20) DEFAULT 'draft',
    prd_document_id UUID,
    created_by      UUID REFERENCES users(id),
    assigned_to     UUID REFERENCES users(id),
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_requirements_project (project_id),
    INDEX idx_requirements_status (status),
    INDEX idx_requirements_priority (priority)
);

# 4. 任务表 (tasks)
CREATE TABLE tasks (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    requirement_id      UUID REFERENCES requirements(id) ON DELETE CASCADE,
    name                VARCHAR(200) NOT NULL,
    description         TEXT,
    task_type           VARCHAR(30) NOT NULL,
    status              VARCHAR(20) DEFAULT 'pending',
    priority            VARCHAR(20) DEFAULT 'medium',
    assigned_to         UUID REFERENCES users(id),
    estimated_hours     DECIMAL(5,2),
    actual_hours        DECIMAL(5,2),
    parent_task_id      UUID REFERENCES tasks(id),
    dependency_ids      UUID[],
    metadata            JSONB,
    created_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    updated_at          TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    completed_at        TIMESTAMP WITH TIME ZONE,
    
    INDEX idx_tasks_requirement (requirement_id),
    INDEX idx_tasks_status (status),
    INDEX idx_tasks_assigned_to (assigned_to),
    INDEX idx_tasks_type (task_type)
);

# 5. 代码制品表 (code_artifacts)
CREATE TABLE code_artifacts (
    id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    task_id         UUID REFERENCES tasks(id) ON DELETE CASCADE,
    file_path       VARCHAR(500) NOT NULL,
    file_name       VARCHAR(200) NOT NULL,
    content         TEXT,
    language        VARCHAR(30),
    version         VARCHAR(20) DEFAULT '1.0.0',
    storage_url     VARCHAR(500),
    checksum        VARCHAR(64),
    generated_by    VARCHAR(50) DEFAULT 'claude_code',
    review_status   VARCHAR(20) DEFAULT 'pending',
    created_by      UUID REFERENCES users(id),
    created_at      TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_artifacts_task (task_id),
    INDEX idx_artifacts_language (language),
    INDEX idx_artifacts_version (version)
);

# 6. 测试结果表 (test_results)
CREATE TABLE test_results (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    artifact_id         UUID REFERENCES code_artifacts(id) ON DELETE CASCADE,
    test_type           VARCHAR(30) NOT NULL,
    test_framework      VARCHAR(50),
    total_tests         INTEGER,
    passed_tests        INTEGER,
    failed_tests        INTEGER,
    skipped_tests       INTEGER,
    coverage_percent    DECIMAL(5,2),
    execution_time_ms   INTEGER,
    error_message       TEXT,
    report_url          VARCHAR(500),
    status              VARCHAR(20) DEFAULT 'completed',
    executed_by         UUID REFERENCES users(id),
    executed_at         TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
    
    INDEX idx_test_results_artifact (artifact_id),
    INDEX idx_test_results_type (test_type),
    INDEX idx_test_results_status (status)
);

# 7. 部署记录表 (deployments)
CREATE TABLE deployments (
    id                  UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    project_id          UUID REFERENCES projects(id),
    artifact_ids        UUID[],
    environment         VARCHAR(30) NOT NULL,
    version             VARCHAR(20) NOT NULL,
    status              VARCHAR(20) DEFAULT 'pending',
    k8s_namespace       VARCHAR(50),
    k8s_deployment_name VARCHAR(100),
    jenkins_build_id    VARCHAR(50),
    deployed_by         UUID REFERENCES users(id),
    started_at          TIMESTAMP WITH TIME ZONE,
    completed_at        TIMESTAMP WITH TIME ZONE,
    rollback_to         UUID REFERENCES deployments(id),
    
    INDEX idx_deployments_project (project_id),
    INDEX idx_deployments_environment (environment),
    INDEX idx_deployments_status (status)
);

// 1. PRD 文档集合 (prd_documents)
{
    "_id": ObjectId("..."),
    "requirement_id": UUID("..."),
    "title": "用户管理系统 PRD",
    "version": "1.0.0",
    "sections": [
        {
            "section_id": "overview",
            "title": "产品概述",
            "content": "...",
            "ai_generated": true
        },
        {
            "section_id": "features",
            "title": "功能列表",
            "content": "...",
            "features": [
                {"id": "F001", "name": "用户注册", "priority": "high"}
            ]
        }
    ],
    "metadata": {
        "generated_by": "claude_code",
        "generated_at": ISODate("2026-03-13T10:00:00Z"),
        "reviewed_by": ["user_id_1", "user_id_2"],
        "status": "approved"
    },
    "created_at": ISODate("2026-03-13T10:00:00Z"),
    "updated_at": ISODate("2026-03-13T10:00:00Z")
}

// 2. API 接口定义集合 (api_definitions)
{
    "_id": ObjectId("..."),
    "service_name": "user-service",
    "openapi_version": "3.0.3",
    "endpoints": [
        {
            "path": "/api/v1/users",
            "method": "POST",
            "summary": "创建用户",
            "request_schema": {...},
            "response_schema": {...},
            "authentication": "bearer"
        }
    ],
    "generated_from_task": UUID("..."),
    "created_at": ISODate("2026-03-13T10:00:00Z")
}

# 示例 1: 创建需求 (POST /api/v1/requirements)

# Request:
{
    "title": "实现用户登录功能",
    "content": "需要实现基于 JWT 的用户登录功能，支持邮箱和密码验证",
    "project_id": "550e8400-e29b-41d4-a716-446655440000",
    "priority": "high",
    "metadata": {
        "estimated_hours": 8,
        "tags": ["authentication", "security"]
    }
}

# Response (201 Created):
{
    "code": 201,
    "message": "需求创建成功",
    "data": {
        "id": "660e8400-e29b-41d4-a716-446655440001",
        "title": "实现用户登录功能",
        "status": "draft",
        "created_at": "2026-03-13T10:30:00Z"
    }
}

# 示例 2: AI 任务拆解 (POST /api/v1/tasks/decompose)

# Request:
{
    "requirement_id": "660e8400-e29b-41d4-a716-446655440001",
    "decomposition_strategy": "role_based",
    "target_roles": ["backend", "frontend", "tester"]
}

# Response (200 OK):
{
    "code": 200,
    "message": "任务拆解完成",
    "data": {
        "requirement_id": "660e8400-e29b-41d4-a716-446655440001",
        "tasks": [
            {
                "id": "770e8400-e29b-41d4-a716-446655440010",
                "name": "[后端] 设计用户认证 API",
                "type": "backend",
                "description": "实现 JWT 认证相关的 API 接口",
                "estimated_hours": 4
            },
            {
                "id": "770e8400-e29b-41d4-a716-446655440011",
                "name": "[后端] 实现密码加密逻辑",
                "type": "backend",
                "description": "使用 bcrypt 进行密码哈希",
                "estimated_hours": 2
            },
            {
                "id": "770e8400-e29b-41d4-a716-446655440012",
                "name": "[前端] 开发登录页面 UI",
                "type": "frontend",
                "estimated_hours": 3
            },
            {
                "id": "770e8400-e29b-41d4-a716-446655440013",
                "name": "[测试] 编写认证模块测试用例",
                "type": "testing",
                "estimated_hours": 2
            }
        ],
        "total_estimated_hours": 11,
        "generated_by": "openclaw_ai"
    }
}

# 示例 3: 代码生成 (POST /api/v1/code/generate)

# Request:
{
    "task_id": "770e8400-e29b-41d4-a716-446655440010",
    "language": "python",
    "framework": "fastapi",
    "specifications": {
        "include_tests": true,
        "include_docs": true,
        "coding_standards": "pep8"
    }
}

# Response (200 OK):
{
    "code": 200,
    "message": "代码生成成功",
    "data": {
        "artifact_id": "880e8400-e29b-41d4-a716-446655440020",
        "files": [
            {
                "path": "src/routes/auth.py",
                "lines_of_code": 156,
                "storage_url": "s3://artifacts/..."
            },
            {
                "path": "tests/test_auth.py",
                "lines_of_code": 89,
                "storage_url": "s3://artifacts/..."
            }
        ],
        "generated_by": "claude_code_v2",
        "generation_time_ms": 3450
    }
}

# JWT Payload 示例
{
    "sub": "550e8400-e29b-41d4-a716-446655440000",
    "username": "zhangsan",
    "email": "zhangsan@example.com",
    "role": "developer",
    "permissions": [
        "tasks:read",
        "tasks:write",
        "code:read",
        "code:write"
    ],
    "iat": 1710324000,
    "exp": 1710331200,
    "iss": "rd-automation-system",
    "aud": "rd-api",
    "jti": "unique-token-id-12345"
}

# Redis 配置示例
# 缓存键命名规范
user:profile:{user_id}              # 用户信息
project:details:{project_id}        # 项目详情
requirement:list:{project_id}       # 需求列表
task:assigned:{user_id}             # 用户分配的任务
api:rate_limit:{ip}:{endpoint}      # API 限流
session:{session_id}                # 会话数据
token:blacklist:{jti}               # Token 黑名单

# TTL 配置（秒）
USER_PROFILE_TTL = 3600             # 1 小时
PROJECT_DETAILS_TTL = 1800          # 30 分钟
API_RESPONSE_TTL = 300              # 5 分钟
SESSION_TTL = 604800                # 7 天
TOKEN_BLACKLIST_TTL = 7200          # 2 小时

// Jenkinsfile for Backend Service
pipeline {
    agent {
        kubernetes {
            yaml """
                apiVersion: v1
                kind: Pod
                spec:
                  containers:
                  - name: docker
                    image: docker:24-dind
                    command: ["cat"]
                    tty: true
                    securityContext:
                      privileged: true
                  - name: kubectl
                    image: bitnami/kubectl:latest
                    command: ["cat"]
                    tty: true
            """
        }
    }

    environment {
        REGISTRY = 'registry.example.com'
        IMAGE_NAME = 'rd-automation/backend-service'
        KUBE_NAMESPACE_DEV = 'rd-dev'
        KUBE_NAMESPACE_PROD = 'rd-prod'
    }

    stages {
        stage('Checkout') {
            steps {
                checkout scm
            }
        }

        stage('Code Quality') {
            steps {
                sh 'pip install flake8 black mypy'
                sh 'flake8 src/'
                sh 'black --check src/'
                sh 'mypy src/'
            }
        }

        stage('Unit Test') {
            steps {
                sh 'pip install -r requirements.txt'
                sh 'pytest tests/ --cov=src --cov-report=xml --cov-fail-under=80'
            }
            post {
                always {
                    junit 'reports/*.xml'
                    publishHTML([
                        allowMissing: false,
                        alwaysLinkToLastBuild: true,
                        keepAll: true,
                        reportDir: 'htmlcov',
                        reportFiles: 'index.html',
                        reportName: 'Coverage Report'
                    ])
                }
            }
        }

        stage('Build Docker Image') {
            steps {
                script {
                    env.BUILD_TAG = "${env.BUILD_NUMBER}-${env.GIT_COMMIT.take(7)}"
                    sh "docker build -t ${REGISTRY}/${IMAGE_NAME}:${BUILD_TAG} ."
                    sh "docker tag ${REGISTRY}/${IMAGE_NAME}:${BUILD_TAG} ${REGISTRY}/${IMAGE_NAME}:latest"
                }
            }
        }

        stage('Push to Registry') {
            steps {
                withCredentials([usernamePassword(credentialsId: 'docker-registry', usernameVariable: 'DOCKER_USER', passwordVariable: 'DOCKER_PASS')]) {
                    sh "echo ${DOCKER_PASS} | docker login ${REGISTRY} -u ${DOCKER_USER} --password-stdin"
                    sh "docker push ${REGISTRY}/${IMAGE_NAME}:${BUILD_TAG}"
                    sh "docker push ${REGISTRY}/${IMAGE_NAME}:latest"
                }
            }
        }

        stage('Deploy to Dev') {
            steps {
                sh "kubectl set image deployment/backend-service backend-service=${REGISTRY}/${IMAGE_NAME}:${BUILD_TAG} -n ${KUBE_NAMESPACE_DEV}"
                sh "kubectl rollout status deployment/backend-service -n ${KUBE_NAMESPACE_DEV} --timeout=300s"
            }
        }

        stage('Integration Test') {
            steps {
                sh 'pytest tests/integration/ --base-url=http://backend-service.rd-dev.svc.cluster.local:8000'
            }
        }

        stage('Manual Approval') {
            steps {
                input message: 'Deploy to Production?', ok: 'Approve'
            }
        }

        stage('Deploy to Prod') {
            steps {
                sh "kubectl set image deployment/backend-service backend-service=${REGISTRY}/${IMAGE_NAME}:${BUILD_TAG} -n ${KUBE_NAMESPACE_PROD}"
                sh "kubectl rollout status deployment/backend-service -n ${KUBE_NAMESPACE_PROD} --timeout=600s"
            }
        }
    }

    post {
        success {
            echo 'Pipeline completed successfully!'
        }
        failure {
            echo 'Pipeline failed!'
            slackSend channel: '#deployments', message: "Build failed: ${env.JOB_NAME} ${env.BUILD_NUMBER}"
        }
        always {
            cleanWs()
        }
    }
}

# deployment.yaml
apiVersion: apps/v1
kind: Deployment
metadata:
  name: backend-service
  namespace: rd-prod
  labels:
    app: backend-service
    version: v1.0.0
spec:
  replicas: 3
  selector:
    matchLabels:
      app: backend-service
  strategy:
    type: RollingUpdate
    rollingUpdate:
      maxSurge: 1
      maxUnavailable: 0
  template:
    metadata:
      labels:
        app: backend-service
      annotations:
        prometheus.io/scrape: "true"
        prometheus.io/port: "8000"
    spec:
      containers:
      - name: backend-service
        image: registry.example.com/rd-automation/backend-service:latest
        ports:
        - containerPort: 8000
          name: http
        env:
        - name: DATABASE_URL
          valueFrom:
            secretKeyRef:
              name: db-credentials
              key: url
        - name: REDIS_URL
          valueFrom:
            secretKeyRef:
              name: redis-credentials
              key: url
        resources:
          requests:
            memory: "512Mi"
            cpu: "250m"
          limits:
            memory: "1Gi"
            cpu: "500m"
        livenessProbe:
          httpGet:
            path: /health
            port: 8000
          initialDelaySeconds: 30
          periodSeconds: 10
        readinessProbe:
          httpGet:
            path: /ready
            port: 8000
          initialDelaySeconds: 5
          periodSeconds: 5
      affinity:
        podAntiAffinity:
          preferredDuringSchedulingIgnoredDuringExecution:
          - weight: 100
            podAffinityTerm:
              labelSelector:
                matchLabels:
                  app: backend-service
              topologyKey: kubernetes.io/hostname

---
# service.yaml
apiVersion: v1
kind: Service
metadata:
  name: backend-service
  namespace: rd-prod
spec:
  selector:
    app: backend-service
  ports:
  - port: 80
    targetPort: 8000
    protocol: TCP
  type: ClusterIP

---
# hpa.yaml (Horizontal Pod Autoscaler)
apiVersion: autoscaling/v2
kind: HorizontalPodAutoscaler
metadata:
  name: backend-service-hpa
  namespace: rd-prod
spec:
  scaleTargetRef:
    apiVersion: apps/v1
    kind: Deployment
    name: backend-service
  minReplicas: 3
  maxReplicas: 10
  metrics:
  - type: Resource
    resource:
      name: cpu
      target:
        type: Utilization
        averageUtilization: 70
  - type: Resource
    resource:
      name: memory
      target:
        type: Utilization
        averageUtilization: 80