# 智源AI 数据库DDL实现参考
> 📅 创建时间：2026年1月11日
> 🎯 定位：`智源AI_数据架构设计文档_v2.0.md` 的技术实现补充
> 📦 内容：完整DDL代码、触发器实现、业务逻辑分析
---
## 文档说明
本文档为 **数据架构设计文档v2.0** 的技术实现补充，包含：
| 内容 | 说明 |
| ---- | ---- |
| **完整DDL代码** | 可直接执行的建表语句 |
| **触发器实现** | 自动计算、智能解析等触发器完整代码 |
| **业务逻辑分析** | mydb各表问题诊断与优化方案 |
| **枚举类型定义** | PostgreSQL ENUM类型定义 |
| **Prisma对齐建议** | 与现有Prisma Schema的对齐方案 |
---
## 一、mydb表结构业务逻辑分析
### 1.1 subscription_orders（订阅订单表）
**业务场景**：第一层账号服务的核心订阅管理，206条真实运营数据
**原表问题分析**：
| 问题 | 原设计 | 优化方案 |
| ---- | ------ | -------- |
| 字段冗余 | `title_note` 与 `original_info` 功能重叠 | 合并为 `subscription_note` |
| 类型不一致 | `record_type` 默认值含中文 | 使用英文枚举 + 中文注释 |
| 缺乏外键 | `member_id`/`customer_code` 无关联 | 统一关联customers表 |
| 动态计算依赖 | 触发器复杂度高 | 简化计算逻辑，增加缓存字段 |
**优化后字段设计**：
```sql
-- subscription_orders（订阅订单表）
-- 业务定位：第一层账号服务的订阅生命周期管理
-- 核心功能：自动计算到期日期、动态状态显示
CREATE TABLE subscription_orders (
-- 主键
id BIGSERIAL PRIMARY KEY,
-- 外键关联（核心改进）
customer_id UUID, -- 关联customers表
order_id UUID, -- 关联orders表（如有）
-- 订阅标识
serial_number VARCHAR(50), -- 序号（业务流水号）
member_id VARCHAR(50), -- 会员编号（淘宝场景）
-- 账号信息（核心数据）
original_info TEXT NOT NULL, -- 完整账号配套信息
apple_email VARCHAR(255), -- Apple ID邮箱（解析提取）
apple_password VARCHAR(255), -- Apple ID密码（建议加密）
email_password VARCHAR(255), -- 邮箱密码（建议加密）
id_package TEXT, -- ID配套完整信息（可选）
-- 服务信息
service_type VARCHAR(50) NOT NULL, -- 服务类型：Claude Pro/Max5/Max20等
subscription_duration VARCHAR(20) DEFAULT '年卡', -- 订阅时长：年卡/季卡/月卡
-- 时间管理
card_date DATE, -- 办卡日期（订阅开始）
expiry_date DATE, -- 到期日期（自动计算）
-- 状态管理（自动计算）
status VARCHAR(100), -- 动态状态文本
status_code VARCHAR(20) GENERATED ALWAYS AS (
CASE
WHEN expiry_date IS NULL THEN 'unknown'
WHEN expiry_date < CURRENT_DATE THEN 'expired'
WHEN expiry_date - CURRENT_DATE <= 7 THEN 'expiring_soon'
WHEN expiry_date - CURRENT_DATE <= 30 THEN 'expiring_month'
ELSE 'active'
END
) STORED, -- 状态代码（便于查询过滤）
-- 回执信息
receipt_info TEXT, -- 订阅回执
-- 记录类型
record_type VARCHAR(20) NOT NULL DEFAULT 'subscription'
CHECK (record_type IN ('subscription', 'renewal', 'transfer', 'refund')),
-- 备注
subscription_note TEXT, -- 订阅备注
-- 审计字段
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 优化后索引（基于实际查询场景）
CREATE INDEX idx_sub_orders_customer ON subscription_orders(customer_id);
CREATE INDEX idx_sub_orders_status_code ON subscription_orders(status_code);
CREATE INDEX idx_sub_orders_expiry ON subscription_orders(expiry_date);
CREATE INDEX idx_sub_orders_service ON subscription_orders(service_type);
CREATE INDEX idx_sub_orders_apple_email ON subscription_orders(apple_email);
CREATE INDEX idx_sub_orders_card_date ON subscription_orders(card_date DESC);
-- 简化后的触发器（到期日期计算）
CREATE OR REPLACE FUNCTION calculate_subscription_expiry()
RETURNS TRIGGER AS $$
DECLARE
v_months INTEGER;
BEGIN
IF NEW.card_date IS NULL THEN
NEW.expiry_date := NULL;
ELSE
-- 根据订阅时长计算月数
v_months := CASE
WHEN NEW.subscription_duration ILIKE '%年%' OR NEW.subscription_duration ILIKE '%12%' THEN 12
WHEN NEW.subscription_duration ILIKE '%半年%' OR NEW.subscription_duration ILIKE '%6%' THEN 6
WHEN NEW.subscription_duration ILIKE '%季%' OR NEW.subscription_duration ILIKE '%3%' THEN 3
WHEN NEW.subscription_duration ILIKE '%月%' OR NEW.subscription_duration ILIKE '%1%' THEN 1
ELSE 12 -- 默认年卡
END;
NEW.expiry_date := NEW.card_date + (v_months || ' months')::INTERVAL;
END IF;
-- 更新状态文本
NEW.status := CASE
WHEN NEW.expiry_date IS NULL THEN '未设置到期日期'
WHEN NEW.expiry_date < CURRENT_DATE THEN '已过期'
WHEN NEW.expiry_date = CURRENT_DATE THEN '今天到期'
WHEN NEW.expiry_date - CURRENT_DATE <= 7 THEN (NEW.expiry_date - CURRENT_DATE) || '天剩余，即将到期'
ELSE (NEW.expiry_date - CURRENT_DATE) || '天剩余'
END;
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_subscription_expiry
BEFORE INSERT OR UPDATE OF card_date, subscription_duration ON subscription_orders
FOR EACH ROW EXECUTE FUNCTION calculate_subscription_expiry();
业务场景：第一层账号库存管理，支持6种账号类型智能解析
原表问题分析：
| 问题 | 原设计 | 优化方案 |
|---|---|---|
| 账号类型硬编码 | 触发器中写死6种类型 | 使用配置表或枚举 |
| 解析逻辑耦合 | 触发器过于复杂 | 拆分解析函数 |
| 状态管理不足 | 无库存状态字段 | 增加inventory_status |
| 缺乏追踪 | 无分配记录 | 增加assigned_to字段 |
优化后字段设计：
-- accounts（成品账号表）
-- 业务定位：第一层成品号库存管理
-- 核心功能：账号信息智能解析、库存状态管理
-- 账号类型枚举
CREATE TYPE account_type_enum AS ENUM (
'outlook', -- Outlook邮箱账号
'yahoo', -- Yahoo邮箱账号
'gmx', -- GMX邮箱账号
'mail', -- Mail.com邮箱账号
'wearehackerone', -- WearehackerOne邮箱账号
'other' -- 其他类型
);
-- 账号库存状态枚举
CREATE TYPE account_status_enum AS ENUM (
'available', -- 可分配
'reserved', -- 已预留
'assigned', -- 已分配
'disabled', -- 已封禁
'expired' -- 已过期
);
CREATE TABLE accounts (
-- 主键
id BIGSERIAL PRIMARY KEY,
-- 账号分类
account_type account_type_enum NOT NULL DEFAULT 'other',
-- 原始信息（核心数据）
raw_info TEXT NOT NULL, -- 完整原始账号信息
-- 解析字段（自动提取）
email VARCHAR(255), -- 邮箱地址
email_password VARCHAR(255), -- 邮箱密码
recovery_email VARCHAR(255), -- 辅助邮箱
recovery_password VARCHAR(255), -- 辅助邮箱密码
-- Token类字段（针对特定账号类型）
access_token TEXT, -- 访问令牌
refresh_token TEXT, -- 刷新令牌
client_id VARCHAR(255), -- 客户端ID
-- 登录教程（自动生成）
login_tutorial TEXT, -- 登录教程文本
-- 库存状态管理
inventory_status account_status_enum NOT NULL DEFAULT 'available',
-- 分配信息
assigned_to_customer_id UUID, -- 分配给哪个客户
assigned_to_order_id UUID, -- 关联订单
assigned_at TIMESTAMP, -- 分配时间
-- 质量信息
quality_score INT DEFAULT 100 -- 账号质量评分（0-100）
CHECK (quality_score >= 0 AND quality_score <= 100),
last_verified_at TIMESTAMP, -- 最后验证时间
-- 来源信息
source VARCHAR(100), -- 采购来源
cost NUMERIC(10, 2), -- 采购成本
batch_no VARCHAR(50), -- 批次号
-- 备注
notes TEXT,
-- 审计字段
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 索引优化
CREATE INDEX idx_accounts_type ON accounts(account_type);
CREATE INDEX idx_accounts_status ON accounts(inventory_status);
CREATE INDEX idx_accounts_email ON accounts(email);
CREATE INDEX idx_accounts_assigned ON accounts(assigned_to_customer_id) WHERE assigned_to_customer_id IS NOT NULL;
CREATE INDEX idx_accounts_available ON accounts(account_type, inventory_status) WHERE inventory_status = 'available';
-- 账号解析触发器（简化版）
CREATE OR REPLACE FUNCTION parse_account_raw_info()
RETURNS TRIGGER AS $$
DECLARE
v_lines TEXT[];
v_email_domain VARCHAR(100);
BEGIN
-- 按行分割
v_lines := string_to_array(NEW.raw_info, E'\\n');
-- 提取邮箱（第一行通常是邮箱）
IF array_length(v_lines, 1) >= 1 THEN
NEW.email := trim(v_lines[1]);
-- 根据邮箱域名判断类型
v_email_domain := lower(split_part(NEW.email, '@', 2));
NEW.account_type := CASE
WHEN v_email_domain LIKE '%outlook%' OR v_email_domain LIKE '%hotmail%' OR v_email_domain LIKE '%live%' THEN 'outlook'
WHEN v_email_domain LIKE '%yahoo%' THEN 'yahoo'
WHEN v_email_domain LIKE '%gmx%' THEN 'gmx'
WHEN v_email_domain LIKE '%mail.com%' THEN 'mail'
WHEN v_email_domain LIKE '%wearehackerone%' THEN 'wearehackerone'
ELSE 'other'
END;
END IF;
-- 提取密码（第二行通常是密码）
IF array_length(v_lines, 1) >= 2 THEN
NEW.email_password := trim(v_lines[2]);
END IF;
-- 生成登录教程
NEW.login_tutorial := '1. 访问邮箱登录页面' || E'\\n' ||
'2. 输入邮箱: ' || COALESCE(NEW.email, '(未解析)') || E'\\n' ||
'3. 输入密码: ' || COALESCE(NEW.email_password, '(未解析)') || E'\\n' ||
'4. 完成二次验证（如需要）';
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_parse_account
BEFORE INSERT OR UPDATE OF raw_info ON accounts
FOR EACH ROW EXECUTE FUNCTION parse_account_raw_info();
业务场景：支撑三层业务的统一订单管理，已有10条真实订单
原表问题分析：
| 问题 | 原设计 | 优化方案 |
|---|---|---|
| 编号规则复杂 | 手动拼接 | 自动生成函数 |
| 状态流转不清晰 | 简单CHECK约束 | 增加状态机注释 |
| 支付信息不完整 | 仅基础字段 | 增加支付流水 |
| 缺乏服务期限管理 | 手动维护 | 增加自动计算 |
优化后字段设计：
-- orders（统一订单表）
-- 业务定位：三层业务统一订单管理
-- 核心功能：订单全生命周期管理
-- 订单状态枚举（含状态机说明）
CREATE TYPE order_status_enum AS ENUM (
'pending', -- 待支付 → paid/cancelled
'paid', -- 已支付 → processing/refunding
'processing', -- 处理中 → delivering/refunding
'delivering', -- 交付中 → delivered/refunding
'delivered', -- 已交付 → completed/refunding
'completed', -- 已完成（终态）
'cancelled', -- 已取消（终态）
'refunding', -- 退款中 → refunded
'refunded' -- 已退款（终态）
);
-- 产品类别枚举（三层业务）
CREATE TYPE product_category_enum AS ENUM (
'ACC', -- 第一层：Account接入服务
'API', -- 第二层：API/配置服务
'SOL' -- 第三层：Solution解决方案
);
-- 付费周期枚举
CREATE TYPE billing_cycle_enum AS ENUM (
'monthly', -- 月付
'quarterly', -- 季付
'semi_annual', -- 半年付
'annual', -- 年付
'one_time', -- 一次性
'custom' -- 自定义
);
CREATE TABLE orders (
-- 主键
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- 订单编号（自动生成）
order_number VARCHAR(60) NOT NULL UNIQUE,
-- 产品类别（三层业务分类）
product_category product_category_enum NOT NULL,
-- 客户关联
customer_id UUID NOT NULL, -- 关联customers表
-- 商品信息
product_sku VARCHAR(100) NOT NULL, -- 商品SKU
product_name VARCHAR(200), -- 商品名称（冗余便于显示）
-- 数量与价格
quantity INT NOT NULL DEFAULT 1 CHECK (quantity > 0),
unit_price NUMERIC(10, 2) NOT NULL CHECK (unit_price >= 0),
discount_rate NUMERIC(3, 2) DEFAULT 1.00 CHECK (discount_rate > 0 AND discount_rate <= 1),
discount_amount NUMERIC(10, 2) DEFAULT 0, -- 优惠金额（元）
total_amount NUMERIC(12, 2) NOT NULL, -- 订单总额（自动计算）
paid_amount NUMERIC(12, 2), -- 实付金额
-- 付费周期
billing_cycle billing_cycle_enum NOT NULL DEFAULT 'monthly',
duration_months INT DEFAULT 1, -- 购买时长（月）
-- 服务有效期（自动计算）
service_start_date DATE, -- 服务开始日期
service_end_date DATE, -- 服务结束日期
-- 订单状态
status order_status_enum NOT NULL DEFAULT 'pending',
-- 来源渠道
order_channel VARCHAR(20), -- taobao/wechat/website
channel_order_no VARCHAR(100), -- 渠道订单号
-- 支付信息
payment_method VARCHAR(50), -- 支付方式
payment_time TIMESTAMP, -- 支付时间
trade_no VARCHAR(100), -- 支付流水号
-- 物料分配（第一层专用）
allocated_account_id BIGINT, -- 分配的账号ID
-- 时间节点
order_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP, -- 下单时间
completion_time TIMESTAMP, -- 完成时间
cancellation_time TIMESTAMP, -- 取消时间
cancellation_reason TEXT, -- 取消原因
-- 备注
customer_remark TEXT, -- 客户备注
admin_remark TEXT, -- 管理员备注
-- 软删除
is_deleted BOOLEAN NOT NULL DEFAULT FALSE,
deleted_at TIMESTAMP,
-- 乐观锁
version INT NOT NULL DEFAULT 0,
-- 审计字段
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
created_by VARCHAR(50),
updated_by VARCHAR(50)
);
-- 订单编号自动生成函数
CREATE OR REPLACE FUNCTION generate_order_number()
RETURNS TRIGGER AS $$
DECLARE
v_time_code VARCHAR(8);
v_seq INT;
BEGIN
-- 时间码：YYMMDDHH
v_time_code := to_char(CURRENT_TIMESTAMP, 'YYMMDDHH24');
-- 获取当日序号
SELECT COALESCE(MAX(
CAST(SUBSTRING(order_number FROM '[0-9]+$') AS INT)
), 0) + 1 INTO v_seq
FROM orders
WHERE order_number LIKE 'ORD-' || NEW.product_category::TEXT || '-%' || v_time_code || '%';
-- 生成订单号：ORD-[类别]-[时间码]-[序号]
NEW.order_number := 'ORD-' || NEW.product_category::TEXT || '-' || v_time_code || '-' || LPAD(v_seq::TEXT, 3, '0');
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_generate_order_number
BEFORE INSERT ON orders
FOR EACH ROW
WHEN (NEW.order_number IS NULL)
EXECUTE FUNCTION generate_order_number();
-- 订单金额自动计算触发器
CREATE OR REPLACE FUNCTION calculate_order_amount()
RETURNS TRIGGER AS $$
BEGIN
-- 计算总金额
NEW.total_amount := NEW.unit_price * NEW.quantity * COALESCE(NEW.discount_rate, 1.00) - COALESCE(NEW.discount_amount, 0);
IF NEW.total_amount < 0 THEN
NEW.total_amount := 0;
END IF;
-- 计算服务结束日期
IF NEW.service_start_date IS NOT NULL AND NEW.duration_months IS NOT NULL THEN
NEW.service_end_date := NEW.service_start_date + (NEW.duration_months || ' months')::INTERVAL;
END IF;
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_calculate_order_amount
BEFORE INSERT OR UPDATE OF unit_price, quantity, discount_rate, discount_amount, service_start_date, duration_months ON orders
FOR EACH ROW EXECUTE FUNCTION calculate_order_amount();
-- 索引优化（基于查询场景）
CREATE INDEX idx_orders_number ON orders(order_number);
CREATE INDEX idx_orders_customer ON orders(customer_id) WHERE is_deleted = FALSE;
CREATE INDEX idx_orders_category ON orders(product_category) WHERE is_deleted = FALSE;
CREATE INDEX idx_orders_status ON orders(status) WHERE is_deleted = FALSE;
CREATE INDEX idx_orders_time ON orders(order_time DESC) WHERE is_deleted = FALSE;
CREATE INDEX idx_orders_sku ON orders(product_sku) WHERE is_deleted = FALSE;
CREATE INDEX idx_orders_channel ON orders(order_channel) WHERE order_channel IS NOT NULL;
业务场景：售后服务追踪，自动计算退款/补款金额
原表问题分析：
| 问题 | 原设计 | 优化方案 |
|---|---|---|
| 关联不明确 | 仅subscription_id |
增加order_id关联 |
| 状态不够细分 | 基础状态 | 增加处理进度 |
| 缺乏SLA追踪 | 无响应时间 | 增加时间节点 |
优化后字段设计：
-- after_sales_records（售后记录表）
-- 业务定位：售后服务全流程管理
-- 核心功能：退款/补款自动计算、SLA追踪
-- 售后类型枚举
CREATE TYPE after_sales_type_enum AS ENUM (
'refund', -- 退款
'exchange', -- 换货
'supplement', -- 补款
'renewal', -- 续费
'complaint', -- 投诉
'other' -- 其他
);
-- 售后状态枚举
CREATE TYPE after_sales_status_enum AS ENUM (
'pending', -- 待处理
'processing', -- 处理中
'waiting_customer', -- 等待客户
'approved', -- 已批准
'rejected', -- 已拒绝
'completed', -- 已完成
'cancelled' -- 已取消
);
CREATE TABLE after_sales_records (
-- 主键
id BIGSERIAL PRIMARY KEY,
-- 关联信息
order_id UUID, -- 关联订单
subscription_id BIGINT, -- 关联订阅（第一层）
customer_id UUID NOT NULL, -- 关联客户
-- 售后类型
after_sales_type after_sales_type_enum NOT NULL,
-- 问题描述
issue_description TEXT NOT NULL, -- 问题描述
issue_category VARCHAR(50), -- 问题分类
-- 原订单信息（冗余便于计算）
original_amount NUMERIC(10, 2), -- 原订单金额
original_service_days INT, -- 原服务天数
used_days INT DEFAULT 0, -- 已使用天数
remaining_days INT, -- 剩余天数（自动计算）
-- 金额计算（自动）
calculated_refund NUMERIC(10, 2), -- 计算退款金额
calculated_supplement NUMERIC(10, 2), -- 计算补款金额
final_amount NUMERIC(10, 2), -- 最终结算金额
-- 客户沟通文本（自动生成）
customer_message TEXT,
-- 状态管理
status after_sales_status_enum NOT NULL DEFAULT 'pending',
-- 处理信息
assigned_to VARCHAR(100), -- 处理人
resolution TEXT, -- 解决方案
rejection_reason TEXT, -- 拒绝原因
-- SLA时间节点
submitted_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
first_response_at TIMESTAMP, -- 首次响应时间
resolved_at TIMESTAMP, -- 解决时间
-- 评价
rating INT CHECK (rating >= 1 AND rating <= 5),
feedback TEXT,
-- 审计字段
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 售后金额自动计算触发器
CREATE OR REPLACE FUNCTION calculate_after_sales_amount()
RETURNS TRIGGER AS $$
BEGIN
-- 计算剩余天数
IF NEW.original_service_days IS NOT NULL AND NEW.used_days IS NOT NULL THEN
NEW.remaining_days := GREATEST(0, NEW.original_service_days - NEW.used_days);
END IF;
-- 计算退款金额（按比例）
IF NEW.after_sales_type = 'refund' AND NEW.original_amount IS NOT NULL AND NEW.original_service_days > 0 THEN
NEW.calculated_refund := ROUND(NEW.original_amount * NEW.remaining_days / NEW.original_service_days, 2);
END IF;
-- 生成客户沟通文本
NEW.customer_message := CASE NEW.after_sales_type
WHEN 'refund' THEN '您好，根据您的退款申请，' ||
'原订单金额' || COALESCE(NEW.original_amount::TEXT, '?') || '元，' ||
'已使用' || COALESCE(NEW.used_days::TEXT, '?') || '天，' ||
'预计退款' || COALESCE(NEW.calculated_refund::TEXT, '?') || '元。'
WHEN 'exchange' THEN '您好，您的换货申请已收到，我们将尽快为您处理。'
ELSE '您好，您的售后申请已收到，我们将尽快与您联系。'
END;
NEW.updated_at := CURRENT_TIMESTAMP;
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
CREATE TRIGGER trg_calculate_after_sales
BEFORE INSERT OR UPDATE OF original_amount, original_service_days, used_days, after_sales_type ON after_sales_records
FOR EACH ROW EXECUTE FUNCTION calculate_after_sales_amount();
-- 索引
CREATE INDEX idx_after_sales_order ON after_sales_records(order_id) WHERE order_id IS NOT NULL;
CREATE INDEX idx_after_sales_customer ON after_sales_records(customer_id);
CREATE INDEX idx_after_sales_status ON after_sales_records(status);
CREATE INDEX idx_after_sales_type ON after_sales_records(after_sales_type);
CREATE INDEX idx_after_sales_submitted ON after_sales_records(submitted_at DESC);
业务场景：礼品卡库存管理，22条真实数据
原表问题分析：
| 问题 | 原设计 | 优化方案 |
|---|---|---|
| 状态不明确 | 无状态字段 | 增加使用状态 |
| 缺乏追踪 | 无使用记录 | 增加使用信息 |
| 分类不清晰 | 无面额分类 | 增加面额字段 |
优化后字段设计：
-- gift_cards（礼品卡表）
-- 业务定位：礼品卡库存管理
-- 核心功能：礼品卡全生命周期追踪
-- 礼品卡状态枚举
CREATE TYPE gift_card_status_enum AS ENUM (
'available', -- 可用
'reserved', -- 已预留
'used', -- 已使用
'expired', -- 已过期
'invalid' -- 无效
);
CREATE TABLE gift_cards (
-- 主键
id BIGSERIAL PRIMARY KEY,
-- 卡片信息
card_code VARCHAR(100) NOT NULL UNIQUE, -- 卡号/兑换码
card_type VARCHAR(50) NOT NULL, -- 卡片类型
denomination NUMERIC(10, 2), -- 面额
currency VARCHAR(10) DEFAULT 'USD', -- 币种
-- 状态管理
status gift_card_status_enum NOT NULL DEFAULT 'available',
-- 有效期
issue_date DATE, -- 发行日期
expiry_date DATE, -- 过期日期
-- 使用信息
used_by_customer_id UUID, -- 使用客户
used_by_order_id UUID, -- 关联订单
used_at TIMESTAMP, -- 使用时间
-- 采购信息
source VARCHAR(100), -- 采购来源
cost NUMERIC(10, 2), -- 采购成本
batch_no VARCHAR(50), -- 批次号
-- 备注
notes TEXT,
-- 审计字段
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 索引
CREATE INDEX idx_gift_cards_code ON gift_cards(card_code);
CREATE INDEX idx_gift_cards_status ON gift_cards(status);
CREATE INDEX idx_gift_cards_type ON gift_cards(card_type);
CREATE INDEX idx_gift_cards_available ON gift_cards(card_type, status) WHERE status = 'available';
业务场景：统一商品目录，支撑三层业务
原表问题分析：
| 问题 | 原设计 | 优化方案 |
|---|---|---|
| 服务层级不明确 | 无tier字段 | 增加service_tier |
| 与Prisma不对齐 | 字段命名不一致 | 统一命名规范 |
| 价格结构单一 | 仅基础价格 | 增加多周期价格 |
优化后字段设计：
-- product_info（商品信息表）
-- 业务定位：三层业务统一商品目录
-- 核心功能：SKU管理、价格管理、服务层级分类
CREATE TABLE product_info (
-- 主键（与Prisma对齐使用UUID）
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
-- SKU信息
product_sku VARCHAR(100) NOT NULL UNIQUE, -- SKU编码
product_name VARCHAR(200) NOT NULL, -- 商品名称
-- 分类信息
product_type VARCHAR(50) NOT NULL, -- 商品类型描述
product_category product_category_enum NOT NULL, -- 产品类别（ACC/API/SOL）
service_tier VARCHAR(20) NOT NULL, -- 服务层级（tier1_access/tier2_config/tier3_agent）
-- 平台信息（第一层专用）
platform VARCHAR(50), -- claude/chatgpt/gemini等
-- 服务类型（第一层专用）
service_type_code VARCHAR(20), -- ready_made/customized/escort
-- 付费周期代码
payment_cycle VARCHAR(50), -- 月付/季付/年付
-- 价格信息（多周期）
price_monthly NUMERIC(10, 2), -- 月付价格
price_quarterly NUMERIC(10, 2), -- 季付价格
price_semi_annual NUMERIC(10, 2), -- 半年付价格
price_annual NUMERIC(10, 2), -- 年付价格
-- 成本信息
cost_price NUMERIC(10, 2), -- 成本价格
-- 质保信息
warranty_days INT DEFAULT 0, -- 质保天数
warranty_scope TEXT, -- 质保范围
-- 商品状态
status VARCHAR(20) NOT NULL DEFAULT 'active'
CHECK (status IN ('active', 'inactive', 'discontinued')),
-- 描述信息
description TEXT, -- 商品描述
features JSONB, -- 功能特性（JSON）
-- 排序
sort_order INT DEFAULT 0,
-- 审计字段
created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 索引
CREATE INDEX idx_product_info_sku ON product_info(product_sku);
CREATE INDEX idx_product_info_category ON product_info(product_category);
CREATE INDEX idx_product_info_tier ON product_info(service_tier);
CREATE INDEX idx_product_info_platform ON product_info(platform) WHERE platform IS NOT NULL;
CREATE INDEX idx_product_info_status ON product_info(status);
业务场景：订阅账号变更审计追踪
优化方案：保持原设计，仅优化命名
-- subscription_change_history（订阅变更历史表）
-- 业务定位：账号信息变更审计
-- 核心功能：记录original_info和id_package的变更历史
CREATE TABLE subscription_change_history (
-- 主键
id BIGSERIAL PRIMARY KEY,
-- 关联订阅
subscription_id BIGINT NOT NULL,
-- 变更类型
change_type VARCHAR(30) NOT NULL
CHECK (change_type IN ('original_info_change', 'id_package_change', 'both_change')),
-- 变更前后数据
old_original_info TEXT,
new_original_info TEXT,
old_id_package TEXT,
new_id_package TEXT,
-- 变更原因
change_reason TEXT,
-- 操作信息
operated_by VARCHAR(50),
changed_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
-- 索引
CREATE INDEX idx_change_history_subscription ON subscription_change_history(subscription_id);
CREATE INDEX idx_change_history_changed_at ON subscription_change_history(changed_at DESC);
现有Prisma schema已定义了完整的枚举和模型，优化后的mydb表需要与其对齐：
| Prisma模型 | mydb优化表 | 对齐策略 |
|---|---|---|
| Customer | customers | 主键改为UUID，增加认证字段 |
| Product | product_info | 字段对齐，增加tier字段 |
| Order | orders | 状态枚举对齐，增加product_category |
| Delivery | - | 保留Prisma原设计 |
| Inventory | accounts + gift_cards | 分离管理 |
| Ticket | after_sales_records | 功能补充 |
// 新增：订阅订单模型（用于第一层）
model SubscriptionOrder {
id BigInt @id @default(autoincrement())
customerId String @map("customer_id")
customer Customer @relation(fields: [customerId], references: [id])
serialNumber String? @map("serial_number") @db.VarChar(50)
memberId String? @map("member_id") @db.VarChar(50)
originalInfo String @map("original_info") @db.Text
appleEmail String? @map("apple_email") @db.VarChar(255)
applePassword String? @map("apple_password") @db.VarChar(255)
emailPassword String? @map("email_password") @db.VarChar(255)
idPackage String? @map("id_package") @db.Text
serviceType String @map("service_type") @db.VarChar(50)
subscriptionDuration String? @map("subscription_duration") @db.VarChar(20)
cardDate DateTime? @map("card_date") @db.Date
expiryDate DateTime? @map("expiry_date") @db.Date
status String? @db.VarChar(100)
statusCode String? @map("status_code") @db.VarChar(20)
receiptInfo String? @map("receipt_info") @db.Text
recordType String @default("subscription") @map("record_type") @db.VarChar(20)
subscriptionNote String? @map("subscription_note") @db.Text
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
changeHistory SubscriptionChangeHistory[]
@@index([customerId])
@@index([statusCode])
@@index([expiryDate])
@@index([serviceType])
@@map("subscription_orders")
}
// 新增：成品账号模型（用于第一层）
model Account {
id BigInt @id @default(autoincrement())
accountType String @default("other") @map("account_type") @db.VarChar(20)
rawInfo String @map("raw_info") @db.Text
email String? @db.VarChar(255)
emailPassword String? @map("email_password") @db.VarChar(255)
recoveryEmail String? @map("recovery_email") @db.VarChar(255)
recoveryPassword String? @map("recovery_password") @db.VarChar(255)
loginTutorial String? @map("login_tutorial") @db.Text
inventoryStatus String @default("available") @map("inventory_status") @db.VarChar(20)
assignedToCustomerId String? @map("assigned_to_customer_id")
assignedToOrderId String? @map("assigned_to_order_id")
assignedAt DateTime? @map("assigned_at")
qualityScore Int @default(100) @map("quality_score")
lastVerifiedAt DateTime? @map("last_verified_at")
source String? @db.VarChar(100)
cost Decimal? @db.Decimal(10, 2)
batchNo String? @map("batch_no") @db.VarChar(50)
notes String? @db.Text
createdAt DateTime @default(now()) @map("created_at")
updatedAt DateTime @updatedAt @map("updated_at")
@@index([accountType])
@@index([inventoryStatus])
@@index([email])
@@map("accounts")
}
| 序号 | 表名 | 说明 | 来源 | 服务层级 | 优化状态 |
|---|---|---|---|---|---|
| 1 | customers | 客户管理表 | Prisma | 全部 | ✅ 已存在 |
| 2 | products | 产品表 | Prisma | 全部 | ✅ 已存在 |
| 3 | service_types | 服务类型表 | Prisma | 全部 | ✅ 已存在 |
| 4 | orders | 订单表 | Prisma+mydb | 全部 | 🔄 需增强 |
| 5 | deliveries | 交付表 | Prisma | 第一层 | ✅ 已存在 |
| 6 | inventory | 库存表 | Prisma | 第一层 | ✅ 已存在 |
| 7 | tickets | 工单表 | Prisma | 全部 | ✅ 已存在 |
| 8 | contacts | 沟通记录表 | Prisma | 全部 | ✅ 已存在 |
| 9 | inquiries | 咨询表 | Prisma | 第二/三层 | ✅ 已存在 |
| 10 | subscription_orders | 订阅订单表 | mydb优化 | 第一层 | 🆕 需新增 |
| 11 | accounts | 成品账号表 | mydb优化 | 第一层 | 🆕 需新增 |
| 12 | after_sales_records | 售后记录表 | mydb优化 | 全部 | 🆕 需新增 |
| 13 | gift_cards | 礼品卡表 | mydb优化 | 第一层 | 🆕 需新增 |
| 14 | product_info | 商品信息表 | mydb优化 | 全部 | 🆕 可选新增 |
| 15 | subscription_change_history | 变更历史表 | mydb | 第一层 | 🆕 需新增 |
# 1. 修改 packages/database/prisma/schema.prisma
# 添加上述新模型定义
# 2. 生成迁移
cd packages/database
npx prisma migrate dev --name add_tier1_tables
# 3. 生成客户端
npx prisma generate
# 1. 从mydb导出数据
PGPASSWORD=mydb123456 pg_dump -h 192.168.1.160 -U mydb_user -d mydb \\
-t subscription_orders \\
-t accounts \\
-t after_sales_records \\
-t gift_cards \\
--data-only > mydb_data_export.sql
# 2. 数据转换（处理字段名映射）
# 需要编写转换脚本处理字段名差异
# 3. 导入到统一数据库
psql -d zyoperations < mydb_data_converted.sql
# 部署优化后的触发器和函数
psql -d zyoperations < optimized_triggers.sql
| 序号 | 问题 | 选项A | 选项B | 建议 |
|---|---|---|---|---|
| 1 | 主键类型 | UUID（与Prisma一致） | BIGSERIAL（与mydb一致） | A（统一性） |
| 2 | 枚举实现 | PostgreSQL ENUM | CHECK约束 | A（类型安全） |
| 3 | 触发器保留 | 全部保留 | 仅保留核心 | B（性能考虑） |
| 4 | 敏感字段加密 | 应用层加密 | 数据库加密 | A（灵活性） |
| 版本 | 日期 | 更新内容 |
|---|---|---|
| v1.0 | 2026-01-11 | 初始版本，完成三层业务表结构设计 |
| v1.1 | 2026-01-11 | 优化mydb表结构，增加业务逻辑分析，对齐Prisma |
docs/mydb_queries/*.sqlpackages/database/prisma/schema.prismadocs/kaifawenjian/项目背景知识库.md文档维护：开发团队 下一步：待用户确认后执行迁移