如何设计产品功能表

-- 产品配置示例
UPDATE products SET config = '{
  "model": "gpt-4o",
  "max_tokens": 4096,
  "temperature": 0.7,
  "supported_languages": ["en", "zh", "ja", "ko"],
  "features": {
    "streaming": true,
    "image_upload": true,
    "file_export": ["pdf", "docx", "md"]
  }
}' WHERE id = 'prod_001';
 
-- 查询特定配置
SELECT name, config->>'model' AS model
FROM products
WHERE config->'features'->>'streaming' = 'true';

model Product {
  id          String   @id @default(uuid())
  name        String   @db.VarChar(100)
  slug        String   @unique @db.VarChar(100)
  description String?
  category    String   @db.VarChar(50)
  status      ProductStatus @default(draft)
  config      Json?
  pricingType PricingType @default(free)
  unitCost    Decimal? @db.Decimal(10, 4)
  iconUrl     String?  @map("icon_url") @db.VarChar(500)
  sortOrder   Int?     @map("sort_order")
  createdAt   DateTime @default(now()) @map("created_at")
  updatedAt   DateTime @updatedAt @map("updated_at")
 
  generations    Generation[]
  productVersions ProductVersion[]
 
  @@map("products")
}
 
enum ProductStatus {
  draft
  active
  deprecated
  archived
}
 
enum PricingType {
  free
  per_use
  subscription
  credit_based
}

-- 写作工具的生成记录
INSERT INTO generations (user_id, product_id, status, input_params, output_result)
VALUES (
  'user_001', 'prod_writing_001', 'completed',
  '{"prompt": "Write a blog post about AI trends", "language": "en", "tone": "professional", "max_length": 1000}',
  '{"content": "## AI Trends in 2026\n\n...", "word_count": 980, "language": "en"}'
);
 
-- 图片生成工具的生成记录
INSERT INTO generations (user_id, product_id, status, input_params, output_result)
VALUES (
  'user_001', 'prod_image_001', 'completed',
  '{"prompt": "A futuristic city skyline at sunset", "size": "1024x1024", "style": "photorealistic", "count": 4}',
  '{"images": [{"url": "https://cdn.example.com/img1.png", "seed": 42}, {"url": "https://cdn.example.com/img2.png", "seed": 108}]}'
);

-- 按用户查询历史记录（最常见）
CREATE INDEX idx_generations_user_id ON generations(user_id, created_at DESC);
 
-- 按产品统计用量
CREATE INDEX idx_generations_product_id ON generations(product_id, created_at DESC);
 
-- 按状态筛选待处理任务（Worker 使用）
CREATE INDEX idx_generations_status ON generations(status) WHERE status IN ('pending', 'processing');
 
-- 按时间范围统计（计费和对账使用）
CREATE INDEX idx_generations_created_at ON generations(created_at DESC);

model Generation {
  id           String   @id @default(uuid())
  userId       String   @map("user_id")
  productId    String   @map("product_id")
  status       GenerationStatus @default(pending)
  inputParams  Json     @map("input_params")
  outputResult Json?    @map("output_result")
  modelUsed    String?  @map("model_used") @db.VarChar(100)
  tokensInput  Int?     @map("tokens_input")
  tokensOutput Int?     @map("tokens_output")
  costCredits  Int?     @map("cost_credits")
  costAmount   Decimal? @map("cost_amount") @db.Decimal(10, 6)
  latencyMs    Int?     @map("latency_ms")
  errorMessage String?  @map("error_message")
  metadata     Json?
  createdAt    DateTime @default(now()) @map("created_at")
  completedAt  DateTime? @map("completed_at")
 
  user    User    @relation(fields: [userId], references: [id])
  product Product @relation(fields: [productId], references: [id])
 
  @@index([userId, createdAt(sort: Desc)])
  @@index([productId, createdAt(sort: Desc)])
  @@index([status])
  @@map("generations")
}
 
enum GenerationStatus {
  pending
  processing
  completed
  failed
  cancelled
}

INSERT INTO product_versions (generation_id, version_number, content, change_type, is_current)
VALUES ('gen_001', 1, '{"title": "AI Trends", "body": "Full article content..."}', 'created', true);
 
-- 创建新版本时，先将旧版本标记为非当前
UPDATE product_versions SET is_current = false WHERE generation_id = 'gen_001' AND is_current = true;
 
-- 插入新版本
INSERT INTO product_versions (generation_id, version_number, content, change_type, is_current)
VALUES ('gen_001', 2, '{"title": "AI Trends", "body": "Revised article content..."}', 'edited', true);

-- 使用子查询确保版本号递增正确
INSERT INTO product_versions (generation_id, version_number, content, change_type, is_current)
SELECT
  'gen_001',
  COALESCE(MAX(version_number), 0) + 1,
  '{"title": "Updated", "body": "..."}',
  'edited',
  true
FROM product_versions
WHERE generation_id = 'gen_001';

model ProductVersion {
  id            String   @id @default(uuid())
  generationId  String   @map("generation_id")
  versionNumber Int      @map("version_number")
  content       Json
  contentHash   String?  @map("content_hash") @db.VarChar(64)
  changeType    VersionChangeType @default(created)
  changeSummary String?  @map("change_summary") @db.VarChar(500)
  diffData      Json?    @map("diff_data")
  createdBy     String   @map("created_by")
  isCurrent     Boolean  @default(false) @map("is_current")
  createdAt     DateTime @default(now()) @map("created_at")
 
  generation Generation @relation(fields: [generationId], references: [id])
 
  @@unique([generationId, versionNumber])
  @@index([generationId, isCurrent])
  @@index([generationId, createdAt(sort: Desc)])
  @@map("product_versions")
}
 
enum VersionChangeType {
  created
  edited
  regenerated
  restored
}

-- 查询某用户最近 10 次生成记录及其关联的产品信息
SELECT
  g.id,
  p.name AS product_name,
  g.status,
  g.created_at,
  g.cost_credits
FROM generations g
JOIN products p ON g.product_id = p.id
WHERE g.user_id = 'user_001'
ORDER BY g.created_at DESC
LIMIT 10;
 
-- 查询某次生成的所有版本历史
SELECT
  pv.version_number,
  pv.change_type,
  pv.change_summary,
  pv.content,
  pv.created_at
FROM product_versions pv
WHERE pv.generation_id = 'gen_001'
ORDER BY pv.version_number ASC;
 
-- 统计某产品过去 30 天的生成量和成功率
SELECT
  p.name,
  COUNT(*) AS total_generations,
  COUNT(*) FILTER (WHERE g.status = 'completed') AS completed,
  COUNT(*) FILTER (WHERE g.status = 'failed') AS failed,
  ROUND(
    COUNT(*) FILTER (WHERE g.status = 'completed')::numeric / COUNT(*) * 100, 2
  ) AS success_rate
FROM generations g
JOIN products p ON g.product_id = p.id
WHERE g.created_at >= NOW() - INTERVAL '30 days'
  AND p.id = 'prod_001'
GROUP BY p.name;
 
-- 查询某用户在各产品上的消费汇总
SELECT
  p.name AS product_name,
  COUNT(*) AS generation_count,
  COALESCE(SUM(g.cost_credits), 0) AS total_credits,
  COALESCE(SUM(g.cost_amount), 0) AS total_amount
FROM generations g
JOIN products p ON g.product_id = p.id
WHERE g.user_id = 'user_001'
  AND g.status = 'completed'
GROUP BY p.name
ORDER BY total_credits DESC;

-- 产品表
CREATE TABLE products (
  id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name          VARCHAR(100) NOT NULL,
  slug          VARCHAR(100) NOT NULL UNIQUE,
  description   TEXT,
  category      VARCHAR(50) NOT NULL,
  status        VARCHAR(20) NOT NULL DEFAULT 'draft'
                  CHECK (status IN ('draft', 'active', 'deprecated', 'archived')),
  config        JSONB,
  pricing_type  VARCHAR(20) NOT NULL DEFAULT 'free'
                  CHECK (pricing_type IN ('free', 'per_use', 'subscription', 'credit_based')),
  unit_cost     DECIMAL(10, 4),
  icon_url      VARCHAR(500),
  sort_order    INT DEFAULT 0,
  created_at    TIMESTAMPTZ NOT NULL DEFAULT now(),
  updated_at    TIMESTAMPTZ NOT NULL DEFAULT now()
);
 
-- 生成记录表
CREATE TABLE generations (
  id             UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id        UUID NOT NULL REFERENCES users(id),
  product_id     UUID NOT NULL REFERENCES products(id),
  status         VARCHAR(20) NOT NULL DEFAULT 'pending'
                   CHECK (status IN ('pending', 'processing', 'completed', 'failed', 'cancelled')),
  input_params   JSONB NOT NULL,
  output_result  JSONB,
  model_used     VARCHAR(100),
  tokens_input   INT,
  tokens_output  INT,
  cost_credits   INT,
  cost_amount    DECIMAL(10, 6),
  latency_ms     INT,
  error_message  TEXT,
  metadata       JSONB,
  created_at     TIMESTAMPTZ NOT NULL DEFAULT now(),
  completed_at   TIMESTAMPTZ
);
 
CREATE INDEX idx_gen_user_created ON generations(user_id, created_at DESC);
CREATE INDEX idx_gen_product_created ON generations(product_id, created_at DESC);
CREATE INDEX idx_gen_status_pending ON generations(status) WHERE status IN ('pending', 'processing');
 
-- 版本历史表
CREATE TABLE product_versions (
  id              UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  generation_id   UUID NOT NULL REFERENCES generations(id),
  version_number  INT NOT NULL,
  content         JSONB NOT NULL,
  content_hash    VARCHAR(64),
  change_type     VARCHAR(20) NOT NULL DEFAULT 'created'
                    CHECK (change_type IN ('created', 'edited', 'regenerated', 'restored')),
  change_summary  VARCHAR(500),
  diff_data       JSONB,
  created_by      UUID NOT NULL REFERENCES users(id),
  is_current      BOOLEAN NOT NULL DEFAULT false,
  created_at      TIMESTAMPTZ NOT NULL DEFAULT now(),
 
  UNIQUE(generation_id, version_number)
);
 
CREATE INDEX idx_pv_gen_current ON product_versions(generation_id, is_current);
CREATE INDEX idx_pv_gen_created ON product_versions(generation_id, created_at DESC);

INSERT INTO products (id, name, slug, category, status, config, pricing_type, unit_cost)
VALUES (
  'prod_writer_001',
  'AI Article Writer',
  'ai-article-writer',
  'writing',
  'active',
  '{
    "models": ["gpt-4o", "claude-3-5-sonnet"],
    "default_model": "gpt-4o",
    "features": {
      "generate": true,
      "rewrite": true,
      "translate": true,
      "continue_writing": true
    },
    "limits": {
      "max_tokens_per_request": 8192,
      "max_words_per_request": 3000,
      "supported_languages": ["en", "zh", "ja", "ko", "es", "fr", "de"]
    },
    "defaults": {
      "tone": "professional",
      "language": "en",
      "output_format": "markdown"
    }
  }'::jsonb,
  'credit_based',
  0.0020
);

-- 用户提交生成请求
INSERT INTO generations (id, user_id, product_id, status, input_params)
VALUES (
  'gen_writer_001',
  'user_001',
  'prod_writer_001',
  'processing',
  '{
    "action": "generate",
    "prompt": "Write a comprehensive guide about remote work productivity tips",
    "language": "en",
    "tone": "professional",
    "max_words": 2000,
    "include_outline": true
  }'::jsonb
);
 
-- 模型返回结果后更新记录
UPDATE generations SET
  status = 'completed',
  output_result = '{
    "content": "## Remote Work Productivity: A Complete Guide\n\n...",
    "word_count": 1856,
    "language": "en",
    "reading_time_minutes": 7
  }'::jsonb,
  model_used = 'gpt-4o',
  tokens_input = 156,
  tokens_output = 2340,
  cost_credits = 5,
  cost_amount = 0.010000,
  latency_ms = 8500,
  completed_at = now()
WHERE id = 'gen_writer_001';

-- 初始版本（自动生成时创建）
INSERT INTO product_versions (generation_id, version_number, content, change_type, change_summary, created_by, is_current)
VALUES (
  'gen_writer_001', 1,
  '{"content": "## Remote Work Productivity: A Complete Guide\n\n...original version..."}',
  'created', 'Initial generation', 'system', true
);
 
-- 用户要求改写语气
UPDATE product_versions SET is_current = false WHERE generation_id = 'gen_writer_001' AND is_current = true;
 
INSERT INTO product_versions (generation_id, version_number, content, change_type, change_summary, created_by, is_current)
VALUES (
  'gen_writer_001', 2,
  '{"content": "## Remote Work Productivity: A Complete Guide\n\n...casual tone version..."}',
  'edited', 'Changed tone from professional to casual', 'user_001', true
);
 
-- 用户要求扩展某个章节
UPDATE product_versions SET is_current = false WHERE generation_id = 'gen_writer_001' AND is_current = true;
 
INSERT INTO product_versions (generation_id, version_number, content, change_type, change_summary, created_by, is_current)
VALUES (
  'gen_writer_001', 3,
  '{"content": "## Remote Work Productivity: A Complete Guide\n\n...expanded version..."}',
  'edited', 'Expanded the time management section with more examples', 'user_001', true
);

INSERT INTO products (id, name, slug, category, status, config, pricing_type, unit_cost)
VALUES (
  'prod_image_001',
  'AI Image Generator',
  'ai-image-generator',
  'image',
  'active',
  '{
    "models": ["dall-e-3", "stable-diffusion-xl", "midjourney-v6"],
    "default_model": "dall-e-3",
    "features": {
      "text_to_image": true,
      "image_to_image": true,
      "style_transfer": true,
      "inpainting": false
    },
    "limits": {
      "max_images_per_request": 4,
      "supported_sizes": ["512x512", "1024x1024", "1792x1024"],
      "supported_formats": ["png", "webp", "jpg"],
      "max_upload_size_mb": 10
    },
    "defaults": {
      "size": "1024x1024",
      "format": "png",
      "style": "photorealistic",
      "count": 1
    }
  }'::jsonb,
  'credit_based',
  0.0400
);

-- 文生图请求
INSERT INTO generations (id, user_id, product_id, status, input_params)
VALUES (
  'gen_image_001',
  'user_002',
  'prod_image_001',
  'processing',
  '{
    "action": "text_to_image",
    "prompt": "A serene Japanese garden with cherry blossoms, koi pond, and traditional wooden bridge",
    "negative_prompt": "blurry, low quality, distorted",
    "size": "1792x1024",
    "style": "photorealistic",
    "count": 4,
    "seed": -1
  }'::jsonb
);
 
-- 生成完成后更新
UPDATE generations SET
  status = 'completed',
  output_result = '{
    "images": [
      {"url": "https://cdn.example.com/gen/gen_image_001/img_1.webp", "seed": 48291},
      {"url": "https://cdn.example.com/gen/gen_image_001/img_2.webp", "seed": 48292},
      {"url": "https://cdn.example.com/gen/gen_image_001/img_3.webp", "seed": 48293},
      {"url": "https://cdn.example.com/gen/gen_image_001/img_4.webp", "seed": 48294}
    ],
    "revised_prompt": "A serene Japanese garden with cherry blossoms in full bloom...",
    "model": "dall-e-3"
  }'::jsonb,
  model_used = 'dall-e-3',
  cost_credits = 16,
  cost_amount = 0.064000,
  latency_ms = 12000,
  completed_at = now()
WHERE id = 'gen_image_001';

-- 原始版本
INSERT INTO product_versions (generation_id, version_number, content, change_type, change_summary, created_by, is_current)
VALUES (
  'gen_image_001', 1,
  '{"selected_image": 0, "url": "https://cdn.example.com/gen/gen_image_001/img_1.webp", "seed": 48291}',
  'created', 'Initial generation - selected image 1', 'user_002', true
);
 
-- 风格迁移
UPDATE product_versions SET is_current = false WHERE generation_id = 'gen_image_001' AND is_current = true;
 
INSERT INTO product_versions (generation_id, version_number, content, change_type, change_summary, created_by, is_current)
VALUES (
  'gen_image_001', 2,
  '{"selected_image": 0, "url": "https://cdn.example.com/gen/gen_image_001/img_1_v2.webp", "style": "watercolor", "seed": 48291}',
  'regenerated', 'Applied watercolor style transfer', 'user_002', true
);