29b - AI辅助Schema设计

你是一位资深数据库架构师。请根据以下业务需求，生成 Mermaid ER 图。

## 业务需求
[粘贴 PRD、用户故事或功能描述]

## 输出要求
1. 使用 Mermaid erDiagram 语法
2. 每个实体包含：主键（PK）、外键（FK）、唯一键（UK）标注
3. 字段类型使用通用类型（uuid, varchar, text, int, decimal, boolean, timestamp, jsonb, enum）
4. 用注释说明关键设计决策（用 %% 注释）
5. 关系类型必须准确：
   - ||--o{ 表示一对多（左侧必须存在）
   - }o--o{ 表示多对多
   - ||--|| 表示一对一
6. 为多对多关系显式创建中间表

## 约束条件
- 数据库类型：[PostgreSQL / MySQL / SQLite]
- 预期数据量：[每个实体的数据量级]
- 读写比：[读多写少 / 写多读少 / 均衡]
- 多租户：[是/否，如果是，说明隔离策略]

## 额外要求
- 所有表包含 created_at 和 updated_at 时间戳
- 关键业务表使用软删除（deleted_at）
- 标注哪些字段需要索引

请将以下 Mermaid ER 图转换为 PlantUML 格式，要求：
1. 使用 @startuml / @enduml 包裹
2. 使用 entity 关键字定义实体
3. 用 * 标记必填字段，用 # 标记主键
4. 使用 PlantUML 的关系语法（||--o{, }o--o{ 等）
5. 添加颜色分组（核心表用蓝色，辅助表用灰色）
6. 添加标题和图例

## Mermaid ER 图
[粘贴 Mermaid ER 图代码]

@startuml 电商系统ER图
!theme plain
title 多商家电商平台 - 数据模型

' 颜色定义
skinparam entity {
    BackgroundColor<<core>> #E3F2FD
    BackgroundColor<<support>> #F5F5F5
    BackgroundColor<<audit>> #FFF3E0
}

entity "User" <<core>> {
    * **id** : uuid <<PK>>
    --
    * email : varchar <<UK>>
    * phone : varchar <<UK>>
    * password_hash : varchar
    * display_name : varchar
    avatar_url : varchar
    * role : enum
    * created_at : timestamp
    * updated_at : timestamp
    deleted_at : timestamp
}

entity "Product" <<core>> {
    * **id** : uuid <<PK>>
    --
    * merchant_id : uuid <<FK>>
    * name : varchar
    * slug : varchar <<UK>>
    description : text
    * status : enum
    attributes : jsonb
    * base_price : decimal(10,2)
    * created_at : timestamp
    * updated_at : timestamp
    deleted_at : timestamp
}

entity "Order" <<core>> {
    * **id** : uuid <<PK>>
    --
    * user_id : uuid <<FK>>
    * order_number : varchar <<UK>>
    * subtotal : decimal(10,2)
    * shipping_fee : decimal(10,2)
    * total_amount : decimal(10,2)
    * status : enum
    shipping_address_id : uuid <<FK>>
    note : text
    paid_at : timestamp
    shipped_at : timestamp
    * created_at : timestamp
    * updated_at : timestamp
}

User ||--o{ Order : places
Product ||--|{ SKU : "has variants"
Order ||--|{ OrderItem : contains
@enduml

请根据以下业务需求，生成 DBML 格式的数据库 Schema。

## 业务需求
[描述业务需求]

## DBML 输出要求
1. 使用 Table 关键字定义表
2. 使用 Ref 定义表间关系
3. 使用 Enum 定义枚举类型
4. 每个字段添加 [note: '说明'] 注释
5. 使用 indexes {} 块定义索引
6. 使用 TableGroup 对相关表分组

## 目标数据库
[PostgreSQL / MySQL]

// 多商家电商平台 Schema
// 生成时间：2025-06

Enum merchant_status {
  pending
  active
  suspended
}

Enum order_status {
  pending
  paid
  shipped
  delivered
  cancelled
  refunded
}

TableGroup ecommerce_core {
  merchants
  products
  skus
}

TableGroup order_management {
  orders
  order_items
  payments
}

Table merchants {
  id uuid [pk, default: `gen_random_uuid()`]
  name varchar(255) [not null, note: '商家名称']
  slug varchar(100) [unique, not null, note: 'URL 友好标识']
  status merchant_status [default: 'pending', note: '商家状态']
  commission_rate decimal(5,4) [default: 0.05, note: '佣金比例']
  settings jsonb [note: '商家配置 JSON']
  created_at timestamptz [default: `now()`, not null]
  updated_at timestamptz [default: `now()`, not null]

  indexes {
    slug [unique]
    status
    created_at
  }
}

Table products {
  id uuid [pk, default: `gen_random_uuid()`]
  merchant_id uuid [ref: > merchants.id, not null, note: '所属商家']
  name varchar(255) [not null, note: '商品名称']
  slug varchar(255) [unique, not null, note: 'URL 标识']
  description text [note: '商品描述']
  base_price decimal(10,2) [not null, note: '基础价格']
  status product_status [default: 'draft']
  attributes jsonb [note: '扩展属性']
  created_at timestamptz [default: `now()`, not null]
  updated_at timestamptz [default: `now()`, not null]
  deleted_at timestamptz [note: '软删除时间']

  indexes {
    merchant_id
    (merchant_id, status) [name: 'idx_products_merchant_status']
    slug [unique]
  }
}

Table orders {
  id uuid [pk, default: `gen_random_uuid()`]
  user_id uuid [ref: > users.id, not null]
  order_number varchar(50) [unique, not null, note: '订单编号']
  total_amount decimal(10,2) [not null]
  status order_status [default: 'pending']
  created_at timestamptz [default: `now()`, not null]

  indexes {
    user_id
    (user_id, created_at) [name: 'idx_orders_user_date']
    order_number [unique]
    status
  }
}

// 关系定义
Ref: order_items.order_id > orders.id [delete: cascade]
Ref: order_items.sku_id > skus.id
Ref: skus.product_id > products.id [delete: cascade]

# 全局安装
npm install -g @liam-hq/cli
 
# 或使用 npx 直接运行
npx @liam-hq/cli erd build --input ./prisma/schema.prisma --format prisma

# 从 Prisma Schema 生成交互式 ER 图
npx @liam-hq/cli erd build \
  --input ./prisma/schema.prisma \
  --format prisma \
  --output-dir ./docs/erd
 
# 从 SQL DDL 生成
npx @liam-hq/cli erd build \
  --input ./schema.sql \
  --format postgres \
  --output-dir ./docs/erd
 
# 从 DBML 生成
npx @liam-hq/cli erd build \
  --input ./schema.dbml \
  --format dbml \
  --output-dir ./docs/erd

# .github/workflows/erd.yml
name: Generate ERD
on:
  push:
    paths:
      - 'prisma/schema.prisma'
      - 'drizzle/schema/**'
 
jobs:
  generate-erd:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - uses: actions/setup-node@v4
        with:
          node-version: '20'
      - name: Generate ERD
        run: npx @liam-hq/cli erd build --input ./prisma/schema.prisma --format prisma --output-dir ./erd-output
      - name: Deploy to GitHub Pages
        uses: peaceiris/actions-gh-pages@v4
        with:
          github_token: ${{ secrets.GITHUB_TOKEN }}
          publish_dir: ./erd-output

-- PostgreSQL：导出 Schema 信息
SELECT
    table_name,
    column_name,
    data_type,
    is_nullable,
    column_default
FROM information_schema.columns
WHERE table_schema = 'public'
ORDER BY table_name, ordinal_position;

请分析当前数据库 Schema，提供以下建议：
1. 缺失的索引（基于常见查询模式）
2. 数据类型优化（过大或过小的类型）
3. 缺失的约束（UNIQUE、CHECK、NOT NULL）
4. 表关系是否合理（是否有隐式关系未建立外键）
5. 命名规范是否一致
6. 是否有冗余表或冗余字段

┌─────────────────────────────────────────────────────────┐
│                  ER 图格式选择决策树                       │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  使用场景？                                              │
│  ├── 文档内嵌（README/Wiki）──→ Mermaid                  │
│  ├── 完整 Schema 定义 ──→ DBML + dbdiagram.io            │
│  ├── 企业级文档 ──→ PlantUML                             │
│  ├── 现有数据库逆向工程 ──→ ChartDB                       │
│  ├── CI/CD 自动化文档 ──→ Liam ERD                       │
│  └── 从零开始快速原型 ──→ Structa / DBModeler AI          │
│                                                         │
│  AI 编码助手集成？                                        │
│  ├── Claude Code ──→ Mermaid（终端友好，文本输出）         │
│  ├── Kiro ──→ Mermaid + Liam ERD（Spec 驱动 + 自动化）   │
│  └── Cursor ──→ DBML / Mermaid（Composer 多文件编辑）     │
│                                                         │
│  团队协作需求？                                           │
│  ├── 需要实时协作 ──→ dbdiagram.io / DrawSQL              │
│  ├── 需要版本控制 ──→ Mermaid / DBML（纯文本）            │
│  └── 需要交互式浏览 ──→ Liam ERD / ChartDB               │
│                                                         │
└─────────────────────────────────────────────────────────┘

你是一位数据库范式分析专家。请对以下 Schema 进行逐级范式分析：

## Schema
[粘贴 Schema 代码（Prisma/Drizzle/SQL DDL）]

## 请逐级分析
1. **1NF 检查**：
   - 是否所有字段都是原子值？
   - 是否有重复组或数组字段应该拆分为独立表？
   - JSONB 字段是否合理（灵活属性 vs 应该规范化的数据）？

2. **2NF 检查**：
   - 是否有复合主键？如果有，是否存在部分依赖？
   - 非主键字段是否完全依赖于主键？

3. **3NF 检查**：
   - 是否存在传递依赖？（A → B → C，其中 A 是主键）
   - 是否有冗余字段可以通过 JOIN 获取？

4. **BCNF 检查**：
   - 每个函数依赖的决定因素是否都是候选键？

## 输出格式
对每个范式级别：
- ✅ 满足：说明原因
- ❌ 违反：指出具体的违规字段和表，给出修正建议
- ⚠️ 有意违反（反范式化）：说明这是否是合理的性能优化

## 上下文
- 应用类型：[SaaS / 电商 / 社交 / 内容管理]
- 读写比：[读多写少 / 写多读少 / 均衡]
- 数据量级：[万级 / 百万级 / 亿级]

model Order {
  id            String   @id @default(uuid())
  userId        String
  userName      String   // ⚠️ 冗余：可从 User 表获取
  userEmail     String   // ⚠️ 冗余：可从 User 表获取
  productId     String
  productName   String   // ⚠️ 这是价格快照还是冗余？
  productPrice  Decimal  // ⚠️ 这是价格快照还是冗余？
  quantity      Int
  totalAmount   Decimal
  tags          String   // ⚠️ 存储为 "tag1,tag2,tag3"
  status        String   // ⚠️ 应该用枚举
  createdAt     DateTime @default(now())
}

## 范式分析报告

### 1NF 检查 ❌ 违反
- **tags 字段**：存储为逗号分隔字符串 "tag1,tag2,tag3"，违反原子性
  - 修正：创建 Tag 表和 OrderTag 关联表
- **status 字段**：使用 String 而非 Enum，虽然不直接违反 1NF，但缺少约束
  - 修正：使用 Prisma enum OrderStatus

### 2NF 检查 ✅ 满足
- 主键为单一字段 id（UUID），不存在复合主键
- 所有非主键字段都依赖于 id

### 3NF 检查 ❌ 违反
- **userName 和 userEmail**：传递依赖 id → userId → userName/userEmail
  - 问题：如果用户修改了名称或邮箱，历史订单数据不一致
  - 修正方案 A（严格 3NF）：移除这两个字段，通过 JOIN User 表获取
  - 修正方案 B（有意反范式化）：如果这是"下单时的用户信息快照"，
    则重命名为 snapshot_user_name 和 snapshot_user_email，
    并在注释中说明这是有意的反范式化

- **productName 和 productPrice**：传递依赖 id → productId → productName/productPrice
  - 判断：这很可能是**合理的反范式化**（价格快照）
  - 建议：重命名为 snapshot_product_name 和 snapshot_unit_price，
    明确表达"快照"语义

### BCNF 检查 ✅ 满足
- 所有函数依赖的决定因素都是候选键（id）

### 修正后的 Schema

model Order {
  id                   String      @id @default(uuid())
  user                 User        @relation(fields: [userId], references: [id])
  userId               String
  // 价格快照（有意反范式化，订单创建后不随商品价格变化）
  snapshotProductName  String      @map("snapshot_product_name")
  snapshotUnitPrice    Decimal     @map("snapshot_unit_price")
  quantity             Int
  totalAmount          Decimal
  status               OrderStatus @default(PENDING)
  tags                 OrderTag[]  // 拆分为关联表
  createdAt            DateTime    @default(now())
  updatedAt            DateTime    @updatedAt
}

enum OrderStatus {
  PENDING
  PAID
  SHIPPED
  DELIVERED
  CANCELLED
  REFUNDED
}

请分析以下 Schema 中的 JSONB 字段，判断每个字段是否应该规范化为独立表：

## Schema
[粘贴包含 JSONB 字段的 Schema]

## 判断标准
1. **保留为 JSONB 的条件**（满足任一即可）：
   - 字段结构不固定，不同记录的 JSON 结构差异大
   - 字段仅用于存储和展示，不需要按内部字段查询或聚合
   - 字段是第三方 API 的原始响应，结构由外部决定
   - 字段变更频率极低

2. **应该规范化的条件**（满足任一即应规范化）：
   - 需要按 JSON 内部字段进行 WHERE 过滤或 ORDER BY
   - 需要对 JSON 内部字段进行聚合（SUM、COUNT、AVG）
   - JSON 内部有明确的实体关系（如嵌套的数组对象）
   - 多个表的 JSONB 字段包含相同结构的数据

## 请输出
每个 JSONB 字段的判断结果：
- ✅ 保留为 JSONB：原因
- ❌ 应该规范化：拆分方案和新表结构
- ⚠️ 混合方案：部分字段保留 JSONB，部分规范化

读多写少反范式化策略：

┌─────────────────────────────────────────────────────┐
│  优化目标：减少 JOIN，加速读取                          │
├─────────────────────────────────────────────────────┤
│                                                     │
│  1. 冗余常用字段                                     │
│     order_items 中冗余 product_name, product_image   │
│     → 避免每次展示订单列表都 JOIN products 表          │
│                                                     │
│  2. 预计算聚合字段                                    │
│     products 表添加 avg_rating, review_count          │
│     → 避免每次展示商品都 COUNT + AVG reviews 表        │
│                                                     │
│  3. 物化视图                                         │
│     CREATE MATERIALIZED VIEW product_stats AS ...     │
│     → 复杂统计查询预计算，定时刷新                      │
│                                                     │
│  4. 缓存层                                           │
│     Redis 缓存热点数据                                │
│     → 进一步减少数据库查询                             │
│                                                     │
│  代价：写入时需要同步更新冗余字段                       │
│  缓解：使用数据库触发器或应用层事件驱动更新              │
│                                                     │
└─────────────────────────────────────────────────────┘

写多读少反范式化策略：

┌─────────────────────────────────────────────────────┐
│  优化目标：减少写入开销，简化写入路径                    │
├─────────────────────────────────────────────────────┤
│                                                     │
│  1. 减少索引数量                                     │
│     只保留必要的索引，减少写入时的索引维护开销           │
│     → 每个索引都会增加 INSERT/UPDATE 的耗时            │
│                                                     │
│  2. 批量写入                                         │
│     使用 COPY 或批量 INSERT 代替逐条插入               │
│     → 减少事务开销和网络往返                           │
│                                                     │
│  3. 分区表                                           │
│     按时间分区（日/周/月），旧分区可以压缩或归档         │
│     → 减少单表大小，加速写入和查询                      │
│                                                     │
│  4. 异步聚合                                         │
│     写入原始数据，异步计算聚合结果                      │
│     → 不阻塞写入路径                                  │
│                                                     │
│  5. Append-Only 模式                                 │
│     只追加不更新，用最新记录覆盖旧记录                  │
│     → 避免 UPDATE 的锁竞争                            │
│                                                     │
└─────────────────────────────────────────────────────┘

你是一位数据库性能优化专家。请分析以下 Schema，判断哪些地方需要反范式化：

## 当前 Schema（严格范式化）
[粘贴 Schema 代码]

## 主要查询模式（按频率排序）
1. [查询 1：描述 + 预期 QPS]
2. [查询 2：描述 + 预期 QPS]
3. [查询 3：描述 + 预期 QPS]

## 环境信息
- 读写比：[如 8:2]
- 数据量：[每个表的预期行数]
- 响应时间要求：[如 <100ms for P99]
- 数据库：[PostgreSQL / MySQL]

## 请输出
对每个可能的反范式化点：
1. **当前问题**：严格范式化导致的性能问题
2. **反范式化方案**：具体的冗余字段或物化视图
3. **收益**：预期的性能提升
4. **代价**：数据一致性维护成本、存储开销
5. **一致性保证方案**：触发器 / 应用层事件 / 定时同步
6. **推荐等级**：强烈推荐 / 推荐 / 可选 / 不推荐

-- 每次加载帖子列表需要 4 个 JOIN + 2 个子查询
SELECT
    p.id, p.content, p.created_at,
    u.display_name, u.avatar_url,
    (SELECT COUNT(*) FROM likes WHERE post_id = p.id) AS like_count,
    (SELECT COUNT(*) FROM comments WHERE post_id = p.id) AS comment_count
FROM posts p
JOIN users u ON p.author_id = u.id
WHERE p.status = 'published'
ORDER BY p.created_at DESC
LIMIT 20;
-- 在百万级数据下，P99 延迟 > 500ms

-- 方案 1：在 posts 表中添加计数器缓存和作者快照
ALTER TABLE posts ADD COLUMN like_count INTEGER DEFAULT 0;
ALTER TABLE posts ADD COLUMN comment_count INTEGER DEFAULT 0;
ALTER TABLE posts ADD COLUMN author_name VARCHAR(100);
ALTER TABLE posts ADD COLUMN author_avatar VARCHAR(500);
 
-- 方案 2：创建触发器自动维护计数器
CREATE OR REPLACE FUNCTION update_post_like_count()
RETURNS TRIGGER AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        UPDATE posts SET like_count = like_count + 1 WHERE id = NEW.post_id;
    ELSIF TG_OP = 'DELETE' THEN
        UPDATE posts SET like_count = like_count - 1 WHERE id = OLD.post_id;
    END IF;
    RETURN NULL;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trigger_update_like_count
AFTER INSERT OR DELETE ON likes
FOR EACH ROW EXECUTE FUNCTION update_post_like_count();
 
-- 优化后的查询（快）
SELECT id, content, created_at, author_name, author_avatar,
       like_count, comment_count
FROM posts
WHERE status = 'published'
ORDER BY created_at DESC
LIMIT 20;
-- P99 延迟 < 10ms，无 JOIN，无子查询

model Post {
  id            String   @id @default(uuid())
  content       String
  status        PostStatus @default(PUBLISHED)
  author        User     @relation(fields: [authorId], references: [id])
  authorId      String
  // 反范式化：作者信息快照（避免 JOIN）
  authorName    String   @map("author_name")
  authorAvatar  String?  @map("author_avatar")
  // 反范式化：计数器缓存（避免 COUNT 子查询）
  likeCount     Int      @default(0) @map("like_count")
  commentCount  Int      @default(0) @map("comment_count")
  likes         Like[]
  comments      Comment[]
  createdAt     DateTime @default(now())
  updatedAt     DateTime @updatedAt
 
  @@index([status, createdAt(sort: Desc)])
  @@map("posts")
}

import { pgTable, uuid, text, varchar, integer, timestamp, pgEnum } from 'drizzle-orm/pg-core';
 
export const postStatusEnum = pgEnum('post_status', ['draft', 'published', 'archived']);
 
export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  content: text('content').notNull(),
  status: postStatusEnum('status').default('published').notNull(),
  authorId: uuid('author_id').references(() => users.id).notNull(),
  // 反范式化：作者信息快照
  authorName: varchar('author_name', { length: 100 }).notNull(),
  authorAvatar: varchar('author_avatar', { length: 500 }),
  // 反范式化：计数器缓存
  likeCount: integer('like_count').default(0).notNull(),
  commentCount: integer('comment_count').default(0).notNull(),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  statusCreatedIdx: index('idx_posts_status_created').on(table.status, table.createdAt),
}));

请为以下复杂查询创建 PostgreSQL 物化视图：

## 原始查询
[粘贴需要优化的复杂 SQL 查询]

## 请输出
1. CREATE MATERIALIZED VIEW 语句
2. 物化视图上的索引
3. 刷新策略（定时刷新 / 增量刷新 / 触发器刷新）
4. 刷新频率建议
5. 并发刷新配置（CONCURRENTLY 选项）
6. 监控查询（检查物化视图是否过期）

-- 创建物化视图：商家每日销售统计
CREATE MATERIALIZED VIEW mv_merchant_daily_sales AS
SELECT
    oi.merchant_id,
    m.name AS merchant_name,
    DATE(o.created_at) AS sale_date,
    COUNT(DISTINCT o.id) AS order_count,
    SUM(oi.quantity) AS items_sold,
    SUM(oi.subtotal) AS gross_revenue,
    SUM(oi.subtotal * m.commission_rate) AS platform_commission,
    SUM(oi.subtotal * (1 - m.commission_rate)) AS merchant_revenue,
    AVG(oi.subtotal) AS avg_order_value
FROM order_items oi
JOIN orders o ON oi.order_id = o.id
JOIN merchants m ON oi.merchant_id = m.id
WHERE o.status IN ('paid', 'shipped', 'delivered')
GROUP BY oi.merchant_id, m.name, DATE(o.created_at)
WITH DATA;
 
-- 在物化视图上创建索引
CREATE UNIQUE INDEX idx_mv_merchant_daily_sales_unique
    ON mv_merchant_daily_sales (merchant_id, sale_date);
CREATE INDEX idx_mv_merchant_daily_sales_date
    ON mv_merchant_daily_sales (sale_date DESC);
 
-- 定时刷新（使用 pg_cron 扩展）
SELECT cron.schedule(
    'refresh_merchant_daily_sales',
    '*/15 * * * *',  -- 每 15 分钟刷新一次
    'REFRESH MATERIALIZED VIEW CONCURRENTLY mv_merchant_daily_sales'
);
 
-- 查询物化视图（毫秒级响应）
SELECT * FROM mv_merchant_daily_sales
WHERE merchant_id = 'xxx'
AND sale_date BETWEEN '2025-01-01' AND '2025-01-31'
ORDER BY sale_date DESC;

你是一位数据库性能优化专家。请为以下 Schema 和查询模式推荐索引策略：

## Schema
[粘贴 Schema 代码]

## 主要查询模式（按频率和重要性排序）
1. [查询 1：SQL 或 ORM 代码 + 预期 QPS]
2. [查询 2：SQL 或 ORM 代码 + 预期 QPS]
3. [查询 3：SQL 或 ORM 代码 + 预期 QPS]

## 写入模式
- INSERT 频率：[QPS]
- UPDATE 频率：[QPS]
- DELETE 频率：[QPS]

## 数据量
- [表名]: [预期行数]

## 数据库
[PostgreSQL 16 / MySQL 8 / MongoDB 7]

## 请输出
对每个推荐的索引：
1. **索引定义**：完整的 CREATE INDEX 语句
2. **索引类型**：B-tree / GIN / GiST / BRIN / 复合 / 部分 / 覆盖
3. **覆盖的查询**：这个索引优化了哪些查询
4. **预期收益**：从全表扫描到索引扫描的预期提升
5. **写入开销**：对 INSERT/UPDATE 性能的影响
6. **存储开销**：预估的索引大小
7. **优先级**：必须 / 推荐 / 可选

## 额外要求
- 识别冗余索引（被其他索引覆盖的索引）
- 建议索引创建顺序（先创建高优先级索引）
- 对于大表，使用 CREATE INDEX CONCURRENTLY

复合索引 (a, b, c) 可以优化以下查询：
✅ WHERE a = ?
✅ WHERE a = ? AND b = ?
✅ WHERE a = ? AND b = ? AND c = ?
✅ WHERE a = ? ORDER BY b
✅ WHERE a = ? AND b > ? ORDER BY b

❌ WHERE b = ?              （跳过了 a）
❌ WHERE c = ?              （跳过了 a 和 b）
❌ WHERE b = ? AND c = ?    （跳过了 a）
⚠️ WHERE a = ? AND c = ?    （可以用 a 过滤，但 c 无法利用索引）

复合索引列顺序的黄金法则：

1. 等值条件的列放在前面
   WHERE status = 'active' AND created_at > '2025-01-01'
   → INDEX (status, created_at)  ✅
   → INDEX (created_at, status)  ❌ 效率低

2. 选择性高的列放在前面（在等值条件中）
   WHERE country = 'CN' AND city = 'Shanghai' AND district = 'Pudong'
   → INDEX (country, city, district)
   如果 country 只有 10 个值，city 有 1000 个值
   → 先 country 过滤到 10%，再 city 过滤到 0.1%

3. 范围条件的列放在最后
   WHERE user_id = ? AND status = 'active' AND created_at > ?
   → INDEX (user_id, status, created_at)  ✅
   → INDEX (user_id, created_at, status)  ❌ status 无法利用索引

4. ORDER BY 的列紧跟 WHERE 条件列
   WHERE status = 'active' ORDER BY created_at DESC
   → INDEX (status, created_at DESC)  ✅

请为以下查询设计最优的复合索引：

## 查询列表
[列出所有需要优化的查询]

## 请分析
1. 每个查询的 WHERE 条件列和 ORDER BY 列
2. 等值条件 vs 范围条件的区分
3. 列的选择性（基数）估算
4. 是否可以用一个复合索引覆盖多个查询
5. 最终推荐的索引列表（避免冗余）

## 输出格式
每个索引：
- CREATE INDEX 语句
- 覆盖的查询列表
- 列顺序的设计理由

-- PostgreSQL 部分索引示例
 
-- 1. 软删除场景：只索引未删除的记录
CREATE INDEX idx_users_email_active
    ON users (email)
    WHERE deleted_at IS NULL;
-- 效果：如果 90% 的用户未删除，索引大小减少 10%
-- 查询：SELECT * FROM users WHERE email = ? AND deleted_at IS NULL
 
-- 2. 订单状态场景：只索引进行中的订单
CREATE INDEX idx_orders_pending
    ON orders (user_id, created_at DESC)
    WHERE status IN ('pending', 'paid', 'shipped');
-- 效果：如果 80% 的订单已完成，索引大小减少 80%
-- 查询：SELECT * FROM orders WHERE user_id = ? AND status IN (...) ORDER BY created_at DESC
 
-- 3. 唯一约束 + 部分索引：只对活跃记录强制唯一
CREATE UNIQUE INDEX idx_merchants_slug_active
    ON merchants (slug)
    WHERE deleted_at IS NULL;
-- 效果：允许已删除的商家 slug 被重新使用
 
-- 4. 稀疏数据场景：只索引有值的字段
CREATE INDEX idx_products_featured
    ON products (featured_at DESC)
    WHERE featured_at IS NOT NULL;
-- 效果：如果只有 1% 的商品被推荐，索引极小

model User {
  id        String    @id @default(uuid())
  email     String
  deletedAt DateTime? @map("deleted_at")
 
  // 部分索引：只索引未删除的用户
  @@index([email], map: "idx_users_email_active")
  // 注意：Prisma 原生不支持部分索引的 WHERE 子句
  // 需要在迁移文件中手动添加 WHERE deleted_at IS NULL
  @@map("users")
}

import { pgTable, uuid, varchar, timestamp, index } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
 
export const users = pgTable('users', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).notNull(),
  deletedAt: timestamp('deleted_at', { withTimezone: true }),
}, (table) => ({
  // 部分索引：只索引未删除的用户
  emailActiveIdx: index('idx_users_email_active')
    .on(table.email)
    .where(sql`${table.deletedAt} IS NULL`),
}));

普通索引查询流程：
  查询 → 索引扫描（找到行指针）→ 回表读取完整行 → 返回结果
  
覆盖索引查询流程：
  查询 → 索引扫描（直接包含所需列）→ 返回结果
  ↑ 省去了回表步骤，性能提升 2-5 倍

-- PostgreSQL 覆盖索引（使用 INCLUDE 子句，PostgreSQL 11+）
 
-- 场景：订单列表页只需要展示订单号、状态、金额、创建时间
-- 查询：SELECT order_number, status, total_amount, created_at
--        FROM orders WHERE user_id = ? ORDER BY created_at DESC LIMIT 20
 
-- 覆盖索引：将展示字段包含在索引中
CREATE INDEX idx_orders_user_list
    ON orders (user_id, created_at DESC)
    INCLUDE (order_number, status, total_amount);
-- 效果：Index-Only Scan，无需回表
 
-- MySQL 覆盖索引（将所有需要的列放入索引）
CREATE INDEX idx_orders_user_list
    ON orders (user_id, created_at, order_number, status, total_amount);
-- 注意：MySQL 没有 INCLUDE 语法，所有列都参与索引排序

-- 验证是否使用了覆盖索引
EXPLAIN (ANALYZE, BUFFERS) 
SELECT order_number, status, total_amount, created_at
FROM orders 
WHERE user_id = 'xxx' 
ORDER BY created_at DESC 
LIMIT 20;
 
-- 期望看到：Index Only Scan using idx_orders_user_list
-- 如果看到：Index Scan（而非 Index Only Scan），说明需要回表

-- 场景：商品有灵活的扩展属性（颜色、尺寸、材质等）
-- 存储为 JSONB：{"color": "red", "size": "XL", "material": "cotton"}
 
-- 创建 GIN 索引（支持所有 JSONB 操作符）
CREATE INDEX idx_products_attributes_gin
    ON products USING GIN (attributes);
 
-- 支持的查询：
-- 1. 包含特定键值对
SELECT * FROM products WHERE attributes @> '{"color": "red"}';
 
-- 2. 包含特定键
SELECT * FROM products WHERE attributes ? 'color';
 
-- 3. 包含任一键
SELECT * FROM products WHERE attributes ?| array['color', 'size'];
 
-- 4. 包含所有键
SELECT * FROM products WHERE attributes ?& array['color', 'size'];
 
-- 优化：使用 jsonb_path_ops 操作符类（更小的索引，只支持 @>）
CREATE INDEX idx_products_attributes_gin_path
    ON products USING GIN (attributes jsonb_path_ops);
-- 索引大小约为默认 GIN 的 1/3，但只支持 @> 操作符

-- 场景：商品名称和描述的全文搜索
 
-- 方案 1：使用 tsvector 列 + GIN 索引
ALTER TABLE products ADD COLUMN search_vector tsvector;
 
-- 生成 tsvector（支持中文需要 zhparser 扩展）
UPDATE products SET search_vector =
    setweight(to_tsvector('english', coalesce(name, '')), 'A') ||
    setweight(to_tsvector('english', coalesce(description, '')), 'B');
 
-- 创建 GIN 索引
CREATE INDEX idx_products_search_gin
    ON products USING GIN (search_vector);
 
-- 创建触发器自动更新 search_vector
CREATE TRIGGER trigger_products_search_vector
BEFORE INSERT OR UPDATE OF name, description ON products
FOR EACH ROW EXECUTE FUNCTION
    tsvector_update_trigger(search_vector, 'pg_catalog.english', name, description);
 
-- 全文搜索查询
SELECT *, ts_rank(search_vector, query) AS rank
FROM products, to_tsquery('english', 'wireless & headphone') query
WHERE search_vector @@ query
ORDER BY rank DESC
LIMIT 20;
 
-- 方案 2：使用 pg_trgm 扩展的模糊搜索
CREATE EXTENSION IF NOT EXISTS pg_trgm;
 
CREATE INDEX idx_products_name_trgm
    ON products USING GIN (name gin_trgm_ops);
 
-- 模糊搜索（支持 LIKE、ILIKE、相似度）
SELECT * FROM products
WHERE name % 'wireles headphon'  -- 相似度搜索（容错拼写错误）
ORDER BY similarity(name, 'wireles headphon') DESC;

-- 场景 1：地理位置查询（需要 PostGIS 扩展）
CREATE EXTENSION IF NOT EXISTS postgis;
 
CREATE TABLE stores (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(255) NOT NULL,
    location GEOMETRY(Point, 4326) NOT NULL  -- WGS84 坐标系
);
 
-- 创建 GiST 空间索引
CREATE INDEX idx_stores_location_gist
    ON stores USING GIST (location);
 
-- 查询附近 5km 内的商店
SELECT name, ST_Distance(location, ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326)) AS distance
FROM stores
WHERE ST_DWithin(
    location,
    ST_SetSRID(ST_MakePoint(121.4737, 31.2304), 4326),  -- 上海坐标
    5000  -- 5000 米
)
ORDER BY distance
LIMIT 10;
 
-- 场景 2：时间范围重叠查询（会议室预订）
CREATE TABLE room_bookings (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    room_id UUID NOT NULL,
    time_range TSTZRANGE NOT NULL,  -- 时间范围类型
    EXCLUDE USING GIST (room_id WITH =, time_range WITH &&)  -- 排除重叠
);
 
-- GiST 索引支持范围重叠查询
CREATE INDEX idx_bookings_time_gist
    ON room_bookings USING GIST (time_range);
 
-- 查询与指定时间段重叠的预订
SELECT * FROM room_bookings
WHERE room_id = 'xxx'
AND time_range && tstzrange('2025-06-15 09:00', '2025-06-15 10:00');

-- 场景：日志表（按时间自然排序，数据量巨大）
CREATE TABLE event_logs (
    id BIGSERIAL PRIMARY KEY,
    event_type VARCHAR(50) NOT NULL,
    payload JSONB,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- BRIN 索引（极小的存储开销）
CREATE INDEX idx_event_logs_created_brin
    ON event_logs USING BRIN (created_at)
    WITH (pages_per_range = 128);
 
-- 对比索引大小（假设 1 亿行数据）：
-- B-tree 索引：约 2-3 GB
-- BRIN 索引：约 50-100 KB（小 10000 倍！）
 
-- 适用查询：时间范围查询
SELECT * FROM event_logs
WHERE created_at BETWEEN '2025-06-01' AND '2025-06-30'
AND event_type = 'purchase';
 
-- 注意：BRIN 索引的前提是数据物理排序与索引列排序一致
-- 如果数据经常 UPDATE 导致物理顺序混乱，BRIN 效果会下降
-- 解决方案：定期 CLUSTER 或使用 pg_repack 重组表

┌─────────────────────────────────────────────────────────┐
│                  索引类型选择决策树                        │
├─────────────────────────────────────────────────────────┤
│                                                         │
│  查询类型？                                              │
│  ├── 等值 / 范围 / 排序 ──→ B-tree（默认选择）            │
│  ├── JSONB 字段查询 ──→ GIN                              │
│  │   ├── 只用 @> 操作符 ──→ GIN (jsonb_path_ops)         │
│  │   └── 需要 ?, ?|, ?& ──→ GIN（默认操作符类）          │
│  ├── 全文搜索 ──→ GIN (tsvector)                         │
│  ├── 模糊搜索 / LIKE ──→ GIN (pg_trgm)                  │
│  ├── 地理空间查询 ──→ GiST (PostGIS)                     │
│  ├── 范围重叠查询 ──→ GiST (range types)                 │
│  └── 时序大表范围查询 ──→ BRIN                            │
│                                                         │
│  优化策略？                                              │
│  ├── 只查询部分数据 ──→ 部分索引（WHERE 子句）            │
│  ├── 避免回表 ──→ 覆盖索引（INCLUDE 子句）               │
│  ├── 多条件组合查询 ──→ 复合索引（注意列顺序）            │
│  └── 表达式查询 ──→ 表达式索引（如 LOWER(email)）        │
│                                                         │
│  特殊考虑？                                              │
│  ├── 大表创建索引 ──→ CREATE INDEX CONCURRENTLY           │
│  ├── 写入密集 ──→ 减少索引数量，考虑 BRIN                │
│  └── 索引膨胀 ──→ 定期 REINDEX 或 pg_repack              │
│                                                         │
└─────────────────────────────────────────────────────────┘

请帮我生成 PostgreSQL 索引健康检查的 SQL 查询，包括：

1. **未使用的索引**：找出创建后从未被查询使用的索引
2. **重复索引**：找出被其他索引完全覆盖的冗余索引
3. **索引膨胀**：找出膨胀率超过 30% 的索引
4. **缺失索引**：分析 pg_stat_user_tables 找出可能缺少索引的表
5. **索引大小排名**：按大小排列所有索引
6. **索引使用率**：每个索引的扫描次数和命中率

请输出可以直接执行的 SQL 查询。

-- 1. 未使用的索引（排除主键和唯一索引）
SELECT
    schemaname || '.' || relname AS table_name,
    indexrelname AS index_name,
    pg_size_pretty(pg_relation_size(i.indexrelid)) AS index_size,
    idx_scan AS times_used
FROM pg_stat_user_indexes i
JOIN pg_index USING (indexrelid)
WHERE idx_scan = 0
AND NOT indisunique
AND NOT indisprimary
ORDER BY pg_relation_size(i.indexrelid) DESC;
 
-- 2. 索引大小排名 Top 20
SELECT
    schemaname || '.' || tablename AS table_name,
    indexname AS index_name,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size,
    idx_scan AS scan_count
FROM pg_indexes
JOIN pg_stat_user_indexes ON indexname = indexrelname
ORDER BY pg_relation_size(indexname::regclass) DESC
LIMIT 20;
 
-- 3. 表的索引使用率（识别可能缺少索引的表）
SELECT
    schemaname || '.' || relname AS table_name,
    seq_scan AS sequential_scans,
    idx_scan AS index_scans,
    CASE WHEN (seq_scan + idx_scan) > 0
        THEN round(100.0 * idx_scan / (seq_scan + idx_scan), 1)
        ELSE 0
    END AS index_usage_percent,
    n_live_tup AS estimated_rows
FROM pg_stat_user_tables
WHERE n_live_tup > 10000  -- 只关注大表
ORDER BY seq_scan DESC;
-- 如果 index_usage_percent < 90%，可能需要添加索引
 
-- 4. 索引膨胀估算
SELECT
    current_database() AS db,
    schemaname || '.' || tablename AS table_name,
    indexname,
    pg_size_pretty(pg_relation_size(indexname::regclass)) AS index_size,
    pg_size_pretty(
        pg_relation_size(indexname::regclass) -
        pg_relation_size(tablename::regclass) * 0.1  -- 粗略估算
    ) AS estimated_bloat
FROM pg_indexes
WHERE schemaname = 'public'
ORDER BY pg_relation_size(indexname::regclass) DESC
LIMIT 20;

model Product {
  id          String   @id @default(uuid())
  merchantId  String   @map("merchant_id")
  name        String
  slug        String   @unique
  status      ProductStatus @default(DRAFT)
  price       Decimal
  attributes  Json?
  createdAt   DateTime @default(now()) @map("created_at")
 
  // 单字段索引
  @@index([merchantId])
 
  // 复合索引
  @@index([merchantId, status])
 
  // 复合索引（指定排序方向）
  @@index([merchantId, createdAt(sort: Desc)])
 
  // 复合唯一索引
  @@unique([merchantId, slug])
 
  // 全文搜索索引（PostgreSQL）
  // 注意：Prisma 原生不支持 GIN/GiST 索引类型
  // 需要在迁移文件中手动添加
 
  @@map("products")
}

import { pgTable, uuid, varchar, decimal, jsonb, timestamp,
         index, uniqueIndex, pgEnum } from 'drizzle-orm/pg-core';
import { sql } from 'drizzle-orm';
 
export const productStatusEnum = pgEnum('product_status', ['draft', 'active', 'archived']);
 
export const products = pgTable('products', {
  id: uuid('id').defaultRandom().primaryKey(),
  merchantId: uuid('merchant_id').notNull(),
  name: varchar('name', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).notNull(),
  status: productStatusEnum('status').default('draft').notNull(),
  price: decimal('price', { precision: 10, scale: 2 }).notNull(),
  attributes: jsonb('attributes'),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  // 单字段索引
  merchantIdx: index('idx_products_merchant').on(table.merchantId),
 
  // 复合索引
  merchantStatusIdx: index('idx_products_merchant_status')
    .on(table.merchantId, table.status),
 
  // 唯一索引
  slugIdx: uniqueIndex('idx_products_slug').on(table.slug),
 
  // 部分索引
  activeProductsIdx: index('idx_products_active')
    .on(table.merchantId, table.createdAt)
    .where(sql`${table.status} = 'active'`),
 
  // GIN 索引（JSONB）
  attributesGinIdx: index('idx_products_attributes_gin')
    .on(table.attributes)
    .using('gin'),
}));

-- 完整的索引创建脚本示例
 
-- B-tree 索引
CREATE INDEX idx_products_merchant ON products (merchant_id);
CREATE INDEX idx_products_merchant_status ON products (merchant_id, status);
CREATE INDEX idx_products_merchant_created ON products (merchant_id, created_at DESC);
 
-- 唯一索引
CREATE UNIQUE INDEX idx_products_slug ON products (slug);
CREATE UNIQUE INDEX idx_products_merchant_slug ON products (merchant_id, slug);
 
-- 部分索引
CREATE INDEX idx_products_active ON products (merchant_id, created_at DESC)
    WHERE status = 'active' AND deleted_at IS NULL;
 
-- 覆盖索引
CREATE INDEX idx_products_list ON products (merchant_id, created_at DESC)
    INCLUDE (name, slug, price, status);
 
-- GIN 索引（JSONB）
CREATE INDEX idx_products_attributes_gin ON products USING GIN (attributes jsonb_path_ops);
 
-- GIN 索引（全文搜索）
CREATE INDEX idx_products_search ON products USING GIN (search_vector);
 
-- GIN 索引（模糊搜索）
CREATE INDEX idx_products_name_trgm ON products USING GIN (name gin_trgm_ops);
 
-- 表达式索引
CREATE INDEX idx_products_lower_name ON products (LOWER(name));
 
-- BRIN 索引（大表时序数据）
CREATE INDEX idx_event_logs_created_brin ON event_logs USING BRIN (created_at);
 
-- 大表安全创建索引（不锁表）
CREATE INDEX CONCURRENTLY idx_orders_user_date ON orders (user_id, created_at DESC);

请根据以下业务需求，分别生成以下格式的 Schema：
1. Prisma Schema（PostgreSQL）
2. Drizzle Schema（PostgreSQL）
3. 原生 SQL DDL（PostgreSQL）
4. MongoDB Mongoose Schema
5. SQLAlchemy 模型（Python）

## 业务需求
[描述业务需求]

## 每种格式的要求
- 包含完整的类型定义、关系、索引、约束
- 包含注释说明设计决策
- 遵循各框架的最佳实践和命名规范

// schema.prisma - 博客系统
 
generator client {
  provider        = "prisma-client-js"
  previewFeatures = ["fullTextSearch"]
}
 
datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}
 
enum PostStatus {
  DRAFT
  PUBLISHED
  ARCHIVED
}
 
model Author {
  id        String   @id @default(uuid())
  email     String   @unique
  name      String
  bio       String?
  avatarUrl String?  @map("avatar_url")
  posts     Post[]
  createdAt DateTime @default(now()) @map("created_at")
  updatedAt DateTime @updatedAt @map("updated_at")
 
  @@map("authors")
}
 
model Post {
  id          String     @id @default(uuid())
  title       String
  slug        String     @unique
  content     String
  excerpt     String?
  status      PostStatus @default(DRAFT)
  author      Author     @relation(fields: [authorId], references: [id])
  authorId    String     @map("author_id")
  tags        PostTag[]
  viewCount   Int        @default(0) @map("view_count")
  publishedAt DateTime?  @map("published_at")
  createdAt   DateTime   @default(now()) @map("created_at")
  updatedAt   DateTime   @updatedAt @map("updated_at")
 
  @@index([authorId])
  @@index([status, publishedAt(sort: Desc)])
  @@index([slug])
  @@map("posts")
}
 
model Tag {
  id    String    @id @default(uuid())
  name  String    @unique
  slug  String    @unique
  posts PostTag[]
 
  @@map("tags")
}
 
model PostTag {
  post   Post   @relation(fields: [postId], references: [id], onDelete: Cascade)
  postId String @map("post_id")
  tag    Tag    @relation(fields: [tagId], references: [id], onDelete: Cascade)
  tagId  String @map("tag_id")
 
  @@id([postId, tagId])
  @@map("post_tags")
}

// schema/blog.ts - Drizzle ORM
 
import {
  pgTable, uuid, varchar, text, integer, timestamp,
  pgEnum, index, uniqueIndex, primaryKey
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const postStatusEnum = pgEnum('post_status', ['draft', 'published', 'archived']);
 
export const authors = pgTable('authors', {
  id: uuid('id').defaultRandom().primaryKey(),
  email: varchar('email', { length: 255 }).unique().notNull(),
  name: varchar('name', { length: 100 }).notNull(),
  bio: text('bio'),
  avatarUrl: varchar('avatar_url', { length: 500 }),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
});
 
export const posts = pgTable('posts', {
  id: uuid('id').defaultRandom().primaryKey(),
  title: varchar('title', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).unique().notNull(),
  content: text('content').notNull(),
  excerpt: text('excerpt'),
  status: postStatusEnum('status').default('draft').notNull(),
  authorId: uuid('author_id').references(() => authors.id).notNull(),
  viewCount: integer('view_count').default(0).notNull(),
  publishedAt: timestamp('published_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  authorIdx: index('idx_posts_author').on(table.authorId),
  statusPublishedIdx: index('idx_posts_status_published')
    .on(table.status, table.publishedAt),
}));
 
export const tags = pgTable('tags', {
  id: uuid('id').defaultRandom().primaryKey(),
  name: varchar('name', { length: 50 }).unique().notNull(),
  slug: varchar('slug', { length: 50 }).unique().notNull(),
});
 
export const postTags = pgTable('post_tags', {
  postId: uuid('post_id').references(() => posts.id, { onDelete: 'cascade' }).notNull(),
  tagId: uuid('tag_id').references(() => tags.id, { onDelete: 'cascade' }).notNull(),
}, (table) => ({
  pk: primaryKey({ columns: [table.postId, table.tagId] }),
}));
 
// 关系定义
export const authorsRelations = relations(authors, ({ many }) => ({
  posts: many(posts),
}));
 
export const postsRelations = relations(posts, ({ one, many }) => ({
  author: one(authors, { fields: [posts.authorId], references: [authors.id] }),
  postTags: many(postTags),
}));

-- 博客系统 DDL - PostgreSQL 16
 
-- 枚举类型
CREATE TYPE post_status AS ENUM ('draft', 'published', 'archived');
 
-- 作者表
CREATE TABLE authors (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email VARCHAR(255) UNIQUE NOT NULL,
    name VARCHAR(100) NOT NULL,
    bio TEXT,
    avatar_url VARCHAR(500),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- 文章表
CREATE TABLE posts (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title VARCHAR(255) NOT NULL,
    slug VARCHAR(255) UNIQUE NOT NULL,
    content TEXT NOT NULL,
    excerpt TEXT,
    status post_status NOT NULL DEFAULT 'draft',
    author_id UUID NOT NULL REFERENCES authors(id),
    view_count INTEGER NOT NULL DEFAULT 0,
    published_at TIMESTAMPTZ,
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
 
-- 标签表
CREATE TABLE tags (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name VARCHAR(50) UNIQUE NOT NULL,
    slug VARCHAR(50) UNIQUE NOT NULL
);
 
-- 文章-标签关联表
CREATE TABLE post_tags (
    post_id UUID NOT NULL REFERENCES posts(id) ON DELETE CASCADE,
    tag_id UUID NOT NULL REFERENCES tags(id) ON DELETE CASCADE,
    PRIMARY KEY (post_id, tag_id)
);
 
-- 索引
CREATE INDEX idx_posts_author ON posts (author_id);
CREATE INDEX idx_posts_status_published ON posts (status, published_at DESC);
CREATE INDEX idx_post_tags_tag ON post_tags (tag_id);
 
-- 自动更新 updated_at 触发器
CREATE OR REPLACE FUNCTION update_updated_at()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;
 
CREATE TRIGGER trigger_authors_updated_at
    BEFORE UPDATE ON authors
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();
 
CREATE TRIGGER trigger_posts_updated_at
    BEFORE UPDATE ON posts
    FOR EACH ROW EXECUTE FUNCTION update_updated_at();

# models/blog.py - SQLAlchemy 2.0
 
import enum
from datetime import datetime
from uuid import uuid4
 
from sqlalchemy import (
    String, Text, Integer, DateTime, Enum, ForeignKey,
    UniqueConstraint, Index
)
from sqlalchemy.dialects.postgresql import UUID
from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column, relationship
 
 
class Base(DeclarativeBase):
    pass
 
 
class PostStatus(enum.Enum):
    DRAFT = "draft"
    PUBLISHED = "published"
    ARCHIVED = "archived"
 
 
class Author(Base):
    __tablename__ = "authors"
 
    id: Mapped[str] = mapped_column(UUID(as_uuid=False), primary_key=True, default=uuid4)
    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    name: Mapped[str] = mapped_column(String(100), nullable=False)
    bio: Mapped[str | None] = mapped_column(Text)
    avatar_url: Mapped[str | None] = mapped_column(String(500))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)
 
    # 关系
    posts: Mapped[list["Post"]] = relationship(back_populates="author")
 
 
class Post(Base):
    __tablename__ = "posts"
    __table_args__ = (
        Index("idx_posts_author", "author_id"),
        Index("idx_posts_status_published", "status", "published_at"),
    )
 
    id: Mapped[str] = mapped_column(UUID(as_uuid=False), primary_key=True, default=uuid4)
    title: Mapped[str] = mapped_column(String(255), nullable=False)
    slug: Mapped[str] = mapped_column(String(255), unique=True, nullable=False)
    content: Mapped[str] = mapped_column(Text, nullable=False)
    excerpt: Mapped[str | None] = mapped_column(Text)
    status: Mapped[PostStatus] = mapped_column(Enum(PostStatus), default=PostStatus.DRAFT)
    author_id: Mapped[str] = mapped_column(ForeignKey("authors.id"), nullable=False)
    view_count: Mapped[int] = mapped_column(Integer, default=0)
    published_at: Mapped[datetime | None] = mapped_column(DateTime(timezone=True))
    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow)
    updated_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), default=datetime.utcnow, onupdate=datetime.utcnow)
 
    # 关系
    author: Mapped["Author"] = relationship(back_populates="posts")
    tags: Mapped[list["Tag"]] = relationship(secondary="post_tags", back_populates="posts")

// models/blog.js - Mongoose Schema
 
const mongoose = require('mongoose');
const { Schema } = mongoose;
 
// 作者 Schema
const authorSchema = new Schema({
  email: {
    type: String,
    required: true,
    unique: true,
    lowercase: true,
    trim: true,
    index: true,
  },
  name: { type: String, required: true, trim: true },
  bio: { type: String },
  avatarUrl: { type: String },
}, {
  timestamps: true,  // 自动添加 createdAt 和 updatedAt
  collection: 'authors',
});
 
// 文章 Schema（MongoDB 文档模型：标签嵌入）
const postSchema = new Schema({
  title: { type: String, required: true, trim: true },
  slug: {
    type: String,
    required: true,
    unique: true,
    lowercase: true,
    index: true,
  },
  content: { type: String, required: true },
  excerpt: { type: String },
  status: {
    type: String,
    enum: ['draft', 'published', 'archived'],
    default: 'draft',
    index: true,
  },
  author: {
    type: Schema.Types.ObjectId,
    ref: 'Author',
    required: true,
    index: true,
  },
  // MongoDB 设计决策：标签嵌入为数组（而非引用）
  // 原因：标签数量有限，嵌入避免额外查询
  tags: [{
    name: { type: String, required: true },
    slug: { type: String, required: true },
  }],
  viewCount: { type: Number, default: 0 },
  publishedAt: { type: Date },
}, {
  timestamps: true,
  collection: 'posts',
});
 
// 复合索引
postSchema.index({ status: 1, publishedAt: -1 });
postSchema.index({ author: 1, createdAt: -1 });
postSchema.index({ 'tags.slug': 1 });
 
// 全文搜索索引
postSchema.index({ title: 'text', content: 'text', excerpt: 'text' });
 
const Author = mongoose.model('Author', authorSchema);
const Post = mongoose.model('Post', postSchema);
 
module.exports = { Author, Post };

我正在构建一个在线教育平台，核心功能包括：
- 课程管理：创建课程、设置章节和课时、定价
- 视频课时：每个课时关联一个视频，支持视频时长记录
- 学员注册：用户可以购买和注册课程
- 学习进度：追踪每个学员在每个课时的观看进度
- 作业系统：课时可以关联作业，学员提交后讲师评分
- 评价系统：学员可以对课程评分和评价

请生成 Mermaid ER 图和 Drizzle ORM Schema。
数据库：PostgreSQL 16，使用 UUID 主键。

// schema/education.ts
 
import {
  pgTable, uuid, varchar, text, integer, decimal, boolean,
  timestamp, pgEnum, index, uniqueIndex
} from 'drizzle-orm/pg-core';
import { relations } from 'drizzle-orm';
 
export const courseLevelEnum = pgEnum('course_level', ['beginner', 'intermediate', 'advanced']);
export const courseStatusEnum = pgEnum('course_status', ['draft', 'published', 'archived']);
export const enrollmentStatusEnum = pgEnum('enrollment_status', ['active', 'expired', 'refunded']);
 
export const courses = pgTable('courses', {
  id: uuid('id').defaultRandom().primaryKey(),
  instructorId: uuid('instructor_id').references(() => instructors.id).notNull(),
  title: varchar('title', { length: 255 }).notNull(),
  slug: varchar('slug', { length: 255 }).unique().notNull(),
  description: text('description'),
  price: decimal('price', { precision: 10, scale: 2 }).notNull(),
  level: courseLevelEnum('level').default('beginner').notNull(),
  status: courseStatusEnum('status').default('draft').notNull(),
  // 反范式化字段
  enrollmentCount: integer('enrollment_count').default(0).notNull(),
  avgRating: decimal('avg_rating', { precision: 3, scale: 2 }),
  publishedAt: timestamp('published_at', { withTimezone: true }),
  createdAt: timestamp('created_at', { withTimezone: true }).defaultNow().notNull(),
  updatedAt: timestamp('updated_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  instructorIdx: index('idx_courses_instructor').on(table.instructorId),
  statusPublishedIdx: index('idx_courses_status_published')
    .on(table.status, table.publishedAt),
  slugIdx: uniqueIndex('idx_courses_slug').on(table.slug),
}));
 
export const enrollments = pgTable('enrollments', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  courseId: uuid('course_id').references(() => courses.id).notNull(),
  paidAmount: decimal('paid_amount', { precision: 10, scale: 2 }).notNull(),
  status: enrollmentStatusEnum('status').default('active').notNull(),
  progressPercent: integer('progress_percent').default(0).notNull(),
  enrolledAt: timestamp('enrolled_at', { withTimezone: true }).defaultNow().notNull(),
  completedAt: timestamp('completed_at', { withTimezone: true }),
}, (table) => ({
  userCourseIdx: uniqueIndex('idx_enrollments_user_course')
    .on(table.userId, table.courseId),
  courseIdx: index('idx_enrollments_course').on(table.courseId),
}));
 
export const lessonProgress = pgTable('lesson_progress', {
  id: uuid('id').defaultRandom().primaryKey(),
  userId: uuid('user_id').references(() => users.id).notNull(),
  lessonId: uuid('lesson_id').references(() => lessons.id).notNull(),
  watchedSeconds: integer('watched_seconds').default(0).notNull(),
  isCompleted: boolean('is_completed').default(false).notNull(),
  lastWatchedAt: timestamp('last_watched_at', { withTimezone: true }).defaultNow().notNull(),
}, (table) => ({
  userLessonIdx: uniqueIndex('idx_progress_user_lesson')
    .on(table.userId, table.lessonId),
  lessonIdx: index('idx_progress_lesson').on(table.lessonId),
}));