bw-mini-app-server/docs/template-hybrid-architecture.md

AI模板管理系统 - 混合式架构设计

🎯 核心设计理念

基于现有的优雅抽象类设计，实现代码定义逻辑 + 数据库存储配置的混合式架构：

• 代码层：抽象类定义执行逻辑和接口规范
• 数据库层：存储具体模板的配置信息
• Entity层：动态从数据库加载配置，创建模板实例
• Migration层：通过数据库迁移脚本初始化模板配置数据

🏗️ 架构层次分析

现有代码结构

Template (公共属性抽象类) [src/templates/types.ts:4-13]
├── ImageGenerateTemplate (图片生成抽象) [src/templates/types.ts:18-20]
│   └── N8nImageGenerateTemplate (N8n图片实现) [src/templates/n8nTemplate.ts:4-32]
│       ├── PhotoRestoreTemplate (具体配置) [src/templates/n8nTemplates/PhotoRestoreTemplate.ts]
│       ├── PetFigurineTemplate (具体配置) 
│       └── CosplayRealPersonTemplate (具体配置)
└── VideoGenerateTemplate (视频生成抽象) [src/templates/types.ts:23-25]
    └── N8nVideoGenerateTemplate (N8n视频实现) [src/templates/n8nTemplate.ts:35-74]
        ├── CharacterFigurineTemplate (具体配置) [src/templates/n8nTemplates/CharacterFigurineTemplate.ts]
        ├── GarageOpeningTemplate (具体配置)
        └── NuclearExplosionTemplate (具体配置)

设计要点

1. 抽象类保持不变：Template、ImageGenerateTemplate、VideoGenerateTemplate、N8nTemplate系列
2. 具体配置存数据库：PhotoRestoreTemplate、CharacterFigurineTemplate等的配置信息
3. 动态实例化：通过Entity层从数据库加载配置，创建模板实例

📊 数据库架构设计

1. 模板配置表 (n8n_templates)

CREATE TABLE n8n_templates (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  
  -- 基础配置 (对应Template抽象类)
  code VARCHAR(100) NOT NULL UNIQUE COMMENT '模板唯一标识码',
  name VARCHAR(200) NOT NULL COMMENT '模板名称',
  description TEXT COMMENT '模板详细描述',
  credit_cost INT NOT NULL DEFAULT 0 COMMENT '积分消耗',
  version VARCHAR(50) NOT NULL COMMENT '版本号',
  input_example_url TEXT COMMENT '输入示例图片',
  output_example_url TEXT COMMENT '输出示例图片',
  tags JSON COMMENT '标签数组',
  
  -- 模板类型配置
  template_type ENUM('image', 'video') NOT NULL COMMENT '模板类型',
  template_class VARCHAR(100) NOT NULL COMMENT '具体模板类名',
  
  -- N8n配置 (对应N8nTemplate系列)
  image_model VARCHAR(100) COMMENT '图片生成模型',
  image_prompt TEXT COMMENT '图片生成提示词',
  video_model VARCHAR(100) COMMENT '视频生成模型', 
  video_prompt TEXT COMMENT '视频生成提示词',
  duration INT COMMENT '视频时长(秒)',
  aspect_ratio VARCHAR(20) COMMENT '视频比例',
  
  -- 系统字段
  is_active BOOLEAN DEFAULT TRUE COMMENT '是否启用',
  sort_order INT DEFAULT 0 COMMENT '排序权重',
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  KEY idx_code (code),
  KEY idx_type (template_type),
  KEY idx_class (template_class),
  KEY idx_active (is_active)
) COMMENT 'N8n模板配置表';

2. 初始化数据示例

INSERT INTO n8n_templates (
  code, name, description, credit_cost, version, 
  input_example_url, output_example_url, tags,
  template_type, template_class,
  image_model, image_prompt,
  video_model, video_prompt, duration, aspect_ratio
) VALUES 
(
  'photo_restore_v1', 
  '老照片修复上色',
  '将黑白老照片修复并上色，让历史照片重现生机',
  12, '1.0.0',
  'https://file.302.ai/gpt/imgs/20250903/6033b7e701a64e4a97d4608d4a0ed308.jpg',
  'https://file.302.ai/gpt/imgs/20250903/07ed5fb40090ac6ff51c874cc75ea8f2.jpg',
  '["老照片", "修复", "上色", "黑白转彩色", "AI修复"]',
  'image', 'PhotoRestoreTemplate',
  'gemini-2.5-flash-image-preview',
  'Restore this old photo and colorize the entire black and white photo',
  NULL, NULL, NULL, NULL
),
(
  'character_figurine_v1',
  '人物手办',
  '将人物照片制作成精细的角色手办模型，展示在收藏家房间中，并生成抚摸手办的视频',
  28, '1.0.0',
  'https://cdn.roasmax.cn/upload/3d590851eb584e92aa415a964e93260e.jpg',
  'https://file.302.ai/gpt/imgs/20250828/2283106b31faf2066e1a72d955f65bca.jpg',
  '["人物", "手办", "模型", "收藏", "PVC", "角色模型", "视频生成"]',
  'video', 'CharacterFigurineTemplate',
  'gemini-2.5-flash-image-preview',
  'Transform this photo into a highly detailed character model...',
  '302/MiniMax-Hailuo-02',
  '一只手摸了摸手办的脑袋',
  6, '9:16'
);

💾 Entity层设计

1. 模板配置实体

// src/entities/n8n-template.entity.ts
import { Entity, Column, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn } from 'typeorm';

export enum TemplateType {
  IMAGE = 'image',
  VIDEO = 'video'
}

@Entity('n8n_templates')
export class N8nTemplateEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ unique: true })
  code: string;

  @Column()
  name: string;

  @Column('text')
  description: string;

  @Column({ name: 'credit_cost' })
  creditCost: number;

  @Column()
  version: string;

  @Column({ name: 'input_example_url', nullable: true })
  inputExampleUrl: string;

  @Column({ name: 'output_example_url', nullable: true })
  outputExampleUrl: string;

  @Column('json', { nullable: true })
  tags: string[];

  @Column({ name: 'template_type', type: 'enum', enum: TemplateType })
  templateType: TemplateType;

  @Column({ name: 'template_class' })
  templateClass: string;

  @Column({ name: 'image_model', nullable: true })
  imageModel: string;

  @Column({ name: 'image_prompt', type: 'text', nullable: true })
  imagePrompt: string;

  @Column({ name: 'video_model', nullable: true })
  videoModel: string;

  @Column({ name: 'video_prompt', type: 'text', nullable: true })
  videoPrompt: string;

  @Column({ nullable: true })
  duration: number;

  @Column({ name: 'aspect_ratio', nullable: true })
  aspectRatio: string;

  @Column({ name: 'is_active', default: true })
  isActive: boolean;

  @Column({ name: 'sort_order', default: 0 })
  sortOrder: number;

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;
}

2. 动态模板实体类

// src/entities/n8n-template-instance.entity.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { N8nTemplateEntity, TemplateType } from './n8n-template.entity';
import { N8nImageGenerateTemplate, N8nVideoGenerateTemplate } from '../templates/n8nTemplate';
import axios from 'axios';

// 动态图片模板实现
export class DynamicN8nImageTemplate extends N8nImageGenerateTemplate {
  private config: N8nTemplateEntity;
  
  constructor(
    private templateId: number,
    private templateRepository: Repository<N8nTemplateEntity>
  ) {
    super();
  }

  async onInit(): Promise<DynamicN8nImageTemplate> {
    this.config = await this.templateRepository.findOne({
      where: { id: this.templateId, templateType: TemplateType.IMAGE, isActive: true }
    });
    
    if (!this.config) {
      throw new Error(`Template with id ${this.templateId} not found`);
    }
    
    return this;
  }

  // 从数据库动态获取配置
  get code(): string { return this.config?.code || ''; }
  get name(): string { return this.config?.name || ''; }
  get description(): string { return this.config?.description || ''; }
  get creditCost(): number { return this.config?.creditCost || 0; }
  get version(): string { return this.config?.version || ''; }
  get input(): string { return this.config?.inputExampleUrl || ''; }
  get output(): string { return this.config?.outputExampleUrl || ''; }
  get tags(): string[] { return this.config?.tags || []; }
  get imageModel(): string { return this.config?.imageModel || ''; }
  get imagePrompt(): string { return this.config?.imagePrompt || ''; }

  // 执行方法继承自N8nImageGenerateTemplate，无需修改
}

// 动态视频模板实现
export class DynamicN8nVideoTemplate extends N8nVideoGenerateTemplate {
  private config: N8nTemplateEntity;
  
  constructor(
    private templateId: number,
    private templateRepository: Repository<N8nTemplateEntity>
  ) {
    super();
  }

  async onInit(): Promise<DynamicN8nVideoTemplate> {
    this.config = await this.templateRepository.findOne({
      where: { id: this.templateId, templateType: TemplateType.VIDEO, isActive: true }
    });
    
    if (!this.config) {
      throw new Error(`Template with id ${this.templateId} not found`);
    }
    
    return this;
  }

  // 从数据库动态获取配置
  get code(): string { return this.config?.code || ''; }
  get name(): string { return this.config?.name || ''; }
  get description(): string { return this.config?.description || ''; }
  get creditCost(): number { return this.config?.creditCost || 0; }
  get version(): string { return this.config?.version || ''; }
  get input(): string { return this.config?.inputExampleUrl || ''; }
  get output(): string { return this.config?.outputExampleUrl || ''; }
  get tags(): string[] { return this.config?.tags || []; }
  get imageModel(): string { return this.config?.imageModel || ''; }
  get imagePrompt(): string { return this.config?.imagePrompt || ''; }
  get videoModel(): string { return this.config?.videoModel || ''; }
  get videoPrompt(): string { return this.config?.videoPrompt || ''; }
  get duration(): number { return this.config?.duration || 6; }
  get aspectRatio(): string { return this.config?.aspectRatio || '9:16'; }

  // 执行方法继承自N8nVideoGenerateTemplate，无需修改
}

3. 模板执行记录表 (template_executions)

CREATE TABLE template_executions (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  
  -- 关联字段
  template_id BIGINT NOT NULL COMMENT '模板ID，关联n8n_templates.id',
  user_id VARCHAR(100) NOT NULL COMMENT '用户ID，关联users.id',
  platform ENUM('wechat', 'alipay', 'baidu', 'bytedance', 'jd', 'qq', 'feishu', 'kuaishou', 'h5', 'rn') NOT NULL COMMENT '执行平台',
  
  -- 执行内容
  type ENUM('image', 'video') NOT NULL COMMENT '生成类型',
  prompt TEXT NOT NULL COMMENT '用户输入提示词',
  input_image_url VARCHAR(500) COMMENT '输入图片URL',
  output_url VARCHAR(500) COMMENT '生成结果URL',
  thumbnail_url VARCHAR(500) COMMENT '缩略图URL',
  
  -- 执行状态和结果
  status ENUM('pending', 'processing', 'completed', 'failed', 'cancelled') NOT NULL DEFAULT 'pending' COMMENT '执行状态',
  progress INT DEFAULT 0 COMMENT '执行进度(0-100)',
  error_message TEXT COMMENT '错误信息',
  execution_result JSON COMMENT '执行结果详情',
  
  -- 积分和计费
  credit_cost INT NOT NULL DEFAULT 0 COMMENT '消耗积分数',
  credit_transaction_id VARCHAR(100) COMMENT '积分交易ID，关联user_credits.id',
  
  -- 执行参数和配置
  input_params JSON COMMENT '输入参数',
  execution_config JSON COMMENT '执行时的配置快照',
  
  -- 性能指标
  started_at TIMESTAMP NULL COMMENT '开始执行时间',
  completed_at TIMESTAMP NULL COMMENT '完成时间',
  execution_duration INT COMMENT '执行耗时(秒)',
  
  -- 系统字段
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  
  KEY idx_template_id (template_id),
  KEY idx_user_id (user_id),
  KEY idx_platform (platform),
  KEY idx_status (status),
  KEY idx_type (type),
  KEY idx_created_at (created_at),
  KEY idx_credit_transaction_id (credit_transaction_id),
  
  FOREIGN KEY (template_id) REFERENCES n8n_templates(id) ON DELETE CASCADE
) COMMENT '模板执行记录表';

4. 模板执行记录实体

// src/entities/template-execution.entity.ts
import { Entity, Column, PrimaryGeneratedColumn, CreateDateColumn, UpdateDateColumn, ManyToOne, JoinColumn, Index } from 'typeorm';
import { N8nTemplateEntity } from './n8n-template.entity';
import { PlatformType } from './platform-user.entity';

export enum ExecutionStatus {
  PENDING = 'pending',
  PROCESSING = 'processing', 
  COMPLETED = 'completed',
  FAILED = 'failed',
  CANCELLED = 'cancelled'
}

export enum ExecutionType {
  IMAGE = 'image',
  VIDEO = 'video'
}

@Entity('template_executions')
@Index(['templateId'])
@Index(['userId'])
@Index(['platform'])
@Index(['status'])
@Index(['type'])
@Index(['creditTransactionId'])
export class TemplateExecutionEntity {
  @PrimaryGeneratedColumn()
  id: number;

  @Column({ name: 'template_id' })
  templateId: number;

  @Column({ name: 'user_id' })
  userId: string;

  @Column({ type: 'enum', enum: PlatformType })
  platform: PlatformType;

  @Column({ type: 'enum', enum: ExecutionType })
  type: ExecutionType;

  @Column({ type: 'text' })
  prompt: string;

  @Column({ name: 'input_image_url', length: 500, nullable: true })
  inputImageUrl: string;

  @Column({ name: 'output_url', length: 500, nullable: true })
  outputUrl: string;

  @Column({ name: 'thumbnail_url', length: 500, nullable: true })
  thumbnailUrl: string;

  @Column({ type: 'enum', enum: ExecutionStatus, default: ExecutionStatus.PENDING })
  status: ExecutionStatus;

  @Column({ default: 0 })
  progress: number;

  @Column({ name: 'error_message', type: 'text', nullable: true })
  errorMessage: string;

  @Column({ name: 'execution_result', type: 'json', nullable: true })
  executionResult: any;

  // 积分和计费相关
  @Column({ name: 'credit_cost', default: 0 })
  creditCost: number;

  @Column({ name: 'credit_transaction_id', length: 100, nullable: true })
  creditTransactionId: string;

  // 执行参数和配置
  @Column({ name: 'input_params', type: 'json', nullable: true })
  inputParams: any;

  @Column({ name: 'execution_config', type: 'json', nullable: true })
  executionConfig: any;

  // 性能指标
  @Column({ name: 'started_at', nullable: true })
  startedAt: Date;

  @Column({ name: 'completed_at', nullable: true })
  completedAt: Date;

  @Column({ name: 'execution_duration', nullable: true })
  executionDuration: number;

  @CreateDateColumn({ name: 'created_at' })
  createdAt: Date;

  @UpdateDateColumn({ name: 'updated_at' })
  updatedAt: Date;

  @ManyToOne(() => N8nTemplateEntity)
  @JoinColumn({ name: 'template_id' })
  template: N8nTemplateEntity;
}

5. 模板工厂服务

// src/services/n8n-template-factory.service.ts
import { Injectable } from '@nestjs/common';
import { InjectRepository } from '@nestjs/typeorm';
import { Repository } from 'typeorm';
import { N8nTemplateEntity, TemplateType } from '../entities/n8n-template.entity';
import { TemplateExecutionEntity, ExecutionStatus } from '../entities/template-execution.entity';
import { DynamicN8nImageTemplate, DynamicN8nVideoTemplate } from '../templates/n8n-dynamic-template';

@Injectable()
export class N8nTemplateFactoryService {
  constructor(
    @InjectRepository(N8nTemplateEntity)
    private templateRepository: Repository<N8nTemplateEntity>,
    @InjectRepository(TemplateExecutionEntity)
    private executionRepository: Repository<TemplateExecutionEntity>,
  ) {}

  // 创建图片模板实例
  async createImageTemplate(templateId: number): Promise<DynamicN8nImageTemplate> {
    const instance = new DynamicN8nImageTemplate(templateId, this.templateRepository);
    return await instance.onInit();
  }

  // 创建视频模板实例  
  async createVideoTemplate(templateId: number): Promise<DynamicN8nVideoTemplate> {
    const instance = new DynamicN8nVideoTemplate(templateId, this.templateRepository);
    return await instance.onInit();
  }

  // 通过模板代码创建实例
  async createTemplateByCode(code: string): Promise<DynamicN8nImageTemplate | DynamicN8nVideoTemplate> {
    const config = await this.templateRepository.findOne({
      where: { code, isActive: true }
    });

    if (!config) {
      throw new Error(`Template with code ${code} not found`);
    }

    if (config.templateType === TemplateType.IMAGE) {
      return this.createImageTemplate(config.id);
    } else {
      return this.createVideoTemplate(config.id);
    }
  }

  // 创建执行记录
  async createExecution(data: Partial<TemplateExecutionEntity>): Promise<TemplateExecutionEntity> {
    const execution = this.executionRepository.create(data);
    return this.executionRepository.save(execution);
  }

  // 更新执行记录
  async updateExecution(id: number, data: Partial<TemplateExecutionEntity>): Promise<TemplateExecutionEntity> {
    await this.executionRepository.update(id, data);
    return this.executionRepository.findOne({ where: { id } });
  }

  // 获取用户执行记录
  async getUserExecutions(userId: string, limit = 20): Promise<TemplateExecutionEntity[]> {
    return this.executionRepository.find({
      where: { userId },
      relations: ['template'],
      order: { createdAt: 'DESC' },
      take: limit
    });
  }

  // 获取模板执行统计
  async getTemplateExecutionStats(templateId: number): Promise<any> {
    const stats = await this.executionRepository
      .createQueryBuilder('execution')
      .select('execution.status', 'status')
      .addSelect('COUNT(*)', 'count')
      .addSelect('AVG(execution.executionDuration)', 'avgDuration')
      .where('execution.templateId = :templateId', { templateId })
      .groupBy('execution.status')
      .getRawMany();
    
    return stats;
  }

  // 获取所有可用模板
  async getAllTemplates(): Promise<N8nTemplateEntity[]> {
    return this.templateRepository.find({
      where: { isActive: true },
      order: { sortOrder: 'DESC', createdAt: 'DESC' }
    });
  }

  // 按类型获取模板
  async getTemplatesByType(type: TemplateType): Promise<N8nTemplateEntity[]> {
    return this.templateRepository.find({
      where: { templateType: type, isActive: true },
      order: { sortOrder: 'DESC', createdAt: 'DESC' }
    });
  }
}

🚀 使用示例

1. 带执行记录的模板执行

// 在Controller中使用，自动记录执行过程
@Controller('templates')
export class TemplateController {
  constructor(
    private readonly templateFactory: N8nTemplateFactoryService,
  ) {}

  @Post(':templateId/execute')
  async executeTemplate(
    @Param('templateId', ParseIntPipe) templateId: number,
    @Body() { imageUrl, taskId }: { imageUrl: string, taskId: string },
    @CurrentUser() user: any
  ) {
    // 1. 创建执行记录
    const execution = await this.templateFactory.createExecution({
      taskId,
      templateId,
      userId: user.id,
      status: ExecutionStatus.PENDING,
      inputParams: { imageUrl },
      progress: 0
    });

    try {
      // 2. 更新状态为处理中
      await this.templateFactory.updateExecution(execution.id, {
        status: ExecutionStatus.PROCESSING,
        startedAt: new Date(),
        progress: 10
      });

      // 3. 创建模板实例并执行
      const template = await this.templateFactory.createTemplateByCode(templateId);
      
      // 4. 更新进度
      await this.templateFactory.updateExecution(execution.id, { progress: 50 });
      
      // 5. 执行模板
      const result = await template.execute(imageUrl);
      
      // 6. 记录执行完成
      await this.templateFactory.updateExecution(execution.id, {
        status: ExecutionStatus.COMPLETED,
        completedAt: new Date(),
        progress: 100,
        executionResult: result,
        executionDuration: Math.floor((Date.now() - execution.startedAt.getTime()) / 1000)
      });

      return { success: true, data: result, executionId: execution.id };
      
    } catch (error) {
      // 7. 记录执行失败
      await this.templateFactory.updateExecution(execution.id, {
        status: ExecutionStatus.FAILED,
        completedAt: new Date(),
        errorMessage: error.message,
        executionDuration: execution.startedAt ? 
          Math.floor((Date.now() - execution.startedAt.getTime()) / 1000) : 0
      });
      
      throw error;
    }
  }

  @Get('executions/:userId')
  async getUserExecutions(@Param('userId') userId: string) {
    return this.templateFactory.getUserExecutions(userId);
  }

  @Get(':templateId/stats')
  async getTemplateStats(@Param('templateId', ParseIntPipe) templateId: number) {
    return this.templateFactory.getTemplateExecutionStats(templateId);
  }

  @Get()
  async getAllTemplates() {
    return this.templateFactory.getAllTemplates();
  }

  @Get('image')
  async getImageTemplates() {
    return this.templateFactory.getTemplatesByType(TemplateType.IMAGE);
  }

  @Get('video') 
  async getVideoTemplates() {
    return this.templateFactory.getTemplatesByType(TemplateType.VIDEO);
  }
}

2. 执行记录查询示例

// 查询用户最近的执行记录
const userExecutions = await templateFactory.getUserExecutions('user123', 10);

// 查询特定模板的执行统计
const stats = await templateFactory.getTemplateExecutionStats(1);
// 返回格式: [
//   { status: 'completed', count: '85', avgDuration: '12.5' },
//   { status: 'failed', count: '5', avgDuration: '8.2' },
//   { status: 'processing', count: '2', avgDuration: null }
// ]

// 根据执行ID查询详细信息
const execution = await executionRepository.findOne({
  where: { id: executionId },
  relations: ['template']
});

✅ 架构优势

1. 保持代码优势

• 类型安全：继承现有抽象类，保持TypeScript类型检查
• 执行逻辑不变：N8nTemplate的execute方法逻辑完全保持不变
• 抽象层次清晰：Template → ImageGenerate/VideoGenerate → N8nTemplate → 具体实现

2. 新增数据库能力

• 配置动态化：模板配置存储在数据库中，可在线修改
• 运营友好：通过管理后台可以调整模板参数、启用/禁用模板
• A/B测试：可以创建同一功能的不同版本进行测试
• 统计分析：可以跟踪每个模板的使用情况和效果
• 执行跟踪：完整记录每次模板执行的过程、结果和性能指标
• 故障诊断：详细的错误记录和执行时长，便于问题排查

3. 使用体验优化

// 带执行记录的模板调用
const execution = await templateFactory.createExecution({
  taskId: 'task_123',
  templateId: 1,
  userId: 'user_456',
  inputParams: { imageUrl: 'https://...' }
});

const template = await templateFactory.createTemplateByCode('character_figurine_v1');
const result = await template.execute(imageUrl);

// 查询执行历史
const history = await templateFactory.getUserExecutions('user_456');

// 查看模板统计
const stats = await templateFactory.getTemplateExecutionStats(1);

📈 执行记录的业务价值

1. 运营数据支持

• 使用频率分析：哪些模板最受欢迎
• 成功率监控：模板执行的成功率和失败原因
• 性能监控：平均执行时长和性能瓶颈

2. 用户体验优化

• 执行历史查看：用户可以查看自己的执行记录
• 进度显示：实时显示模板执行进度
• 错误反馈：清晰的错误信息和重试建议

3. 系统稳定性

• 故障排查：详细的执行日志便于问题定位
• 性能优化：基于执行数据优化模板参数
• 容量规划：基于使用数据进行系统容量规划

这种设计完美融合了您的面向对象架构优势和数据库配置管理的灵活性！