数据库模式分析 (Database Schema Analysis)

文件概述

这个文档分析了项目中使用的 Supabase 数据库模式。该数据库设计支持用户认证、客户管理、订阅管理和积分系统等核心功能。

数据库架构概览

核心表结构

1. 用户表 (Supabase Auth Users)
2. 客户表 (customers)
3. 订阅表 (subscriptions)
4. 积分历史表 (credits_history)

关系图

auth.users (Supabase Auth)
    ↓ (user_id)
customers
    ↓ (customer_id)
subscriptions
    ↓ (customer_id)
credits_history

详细表结构分析

1. 客户表 (customers)

CREATE TABLE customers (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  creem_customer_id TEXT UNIQUE NOT NULL,
  email TEXT NOT NULL,
  name TEXT,
  country TEXT,
  credits INTEGER DEFAULT 0,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

字段说明

• id: 主键，UUID类型，自动生成
• user_id: 外键，关联 Supabase Auth 用户
• creem_customer_id: Creem 系统中的客户ID，唯一且必需
• email: 客户邮箱地址
• name: 客户姓名
• country: 客户所在国家
• credits: 积分余额，默认为0
• created_at: 创建时间，自动设置
• updated_at: 更新时间，需手动维护

设计特点

• 双重ID系统: 本地ID和Creem ID的映射
• 级联删除: 用户删除时自动删除客户记录
• 积分管理: 内置积分字段支持积分系统
• 审计字段: 创建和更新时间追踪

索引建议

CREATE INDEX idx_customers_user_id ON customers(user_id);
CREATE UNIQUE INDEX idx_customers_creem_id ON customers(creem_customer_id);
CREATE INDEX idx_customers_email ON customers(email);

2. 订阅表 (subscriptions)

CREATE TABLE subscriptions (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  customer_id UUID REFERENCES customers(id) ON DELETE CASCADE,
  creem_subscription_id TEXT UNIQUE NOT NULL,
  creem_product_id TEXT NOT NULL,
  status TEXT NOT NULL,
  current_period_start TIMESTAMP WITH TIME ZONE,
  current_period_end TIMESTAMP WITH TIME ZONE,
  canceled_at TIMESTAMP WITH TIME ZONE,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
  updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

字段说明

• id: 主键，UUID类型
• customer_id: 外键，关联客户表
• creem_subscription_id: Creem 系统中的订阅ID
• creem_product_id: Creem 系统中的产品ID
• status: 订阅状态（active, canceled, expired等）
• current_period_start: 当前计费周期开始时间
• current_period_end: 当前计费周期结束时间
• canceled_at: 取消时间
• metadata: 额外的元数据，JSONB格式
• 审计字段同客户表

订阅状态值

• active: 活跃订阅
• trialing: 试用期
• canceled: 已取消
• expired: 已过期
• past_due: 逾期
• unpaid: 未付款
• paused: 暂停

设计特点

• 状态管理: 完整的订阅生命周期状态
• 周期管理: 支持计费周期跟踪
• 元数据存储: 使用JSONB存储额外信息
• 外键约束: 确保数据完整性

索引建议

CREATE INDEX idx_subscriptions_customer_id ON subscriptions(customer_id);
CREATE UNIQUE INDEX idx_subscriptions_creem_id ON subscriptions(creem_subscription_id);
CREATE INDEX idx_subscriptions_status ON subscriptions(status);
CREATE INDEX idx_subscriptions_period_end ON subscriptions(current_period_end);

3. 积分历史表 (credits_history)

CREATE TABLE credits_history (
  id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
  customer_id UUID REFERENCES customers(id) ON DELETE CASCADE,
  amount INTEGER NOT NULL,
  type TEXT NOT NULL CHECK (type IN ('add', 'subtract')),
  description TEXT,
  creem_order_id TEXT,
  metadata JSONB,
  created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
);

字段说明

• id: 主键，UUID类型
• customer_id: 外键，关联客户表
• amount: 积分数量，正整数
• type: 交易类型，枚举值（add/subtract）
• description: 交易描述
• creem_order_id: 关联的Creem订单ID（可选）
• metadata: 额外的元数据
• created_at: 创建时间

设计特点

• 交易记录: 完整的积分变动历史
• 类型约束: 使用CHECK约束确保类型有效性
• 关联订单: 可选的订单关联
• 只读设计: 没有更新时间，记录不可修改

索引建议

CREATE INDEX idx_credits_history_customer_id ON credits_history(customer_id);
CREATE INDEX idx_credits_history_created_at ON credits_history(created_at);
CREATE INDEX idx_credits_history_type ON credits_history(type);
CREATE INDEX idx_credits_history_creem_order ON credits_history(creem_order_id);

数据关系分析

主要关系

1. 用户-客户关系: 一对一关系，一个用户对应一个客户
2. 客户-订阅关系: 一对多关系，一个客户可以有多个订阅
3. 客户-积分历史关系: 一对多关系，一个客户有多条积分记录

关系约束

-- 用户删除时级联删除客户
ALTER TABLE customers 
ADD CONSTRAINT fk_customers_user_id 
FOREIGN KEY (user_id) REFERENCES auth.users(id) ON DELETE CASCADE;

-- 客户删除时级联删除订阅
ALTER TABLE subscriptions 
ADD CONSTRAINT fk_subscriptions_customer_id 
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;

-- 客户删除时级联删除积分历史
ALTER TABLE credits_history 
ADD CONSTRAINT fk_credits_history_customer_id 
FOREIGN KEY (customer_id) REFERENCES customers(id) ON DELETE CASCADE;

数据一致性保证

1. 事务处理

-- 积分交易示例
BEGIN;
  UPDATE customers SET credits = credits + 100 WHERE id = 'customer_id';
  INSERT INTO credits_history (customer_id, amount, type, description) 
  VALUES ('customer_id', 100, 'add', 'Purchase credits');
COMMIT;

2. 约束检查

• 外键约束确保引用完整性
• CHECK约束确保数据有效性
• UNIQUE约束防止重复数据

3. 触发器（可选）

-- 自动更新updated_at字段
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = NOW();
    RETURN NEW;
END;
$$ language 'plpgsql';

CREATE TRIGGER update_customers_updated_at 
BEFORE UPDATE ON customers 
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

性能优化建议

1. 索引策略

• 为外键创建索引
• 为查询频繁的字段创建索引
• 为复合查询创建复合索引

2. 分区策略（大数据量时）

-- 按时间分区积分历史表
CREATE TABLE credits_history_2024_01 PARTITION OF credits_history
FOR VALUES FROM ('2024-01-01') TO ('2024-02-01');

3. 查询优化

• 使用EXPLAIN分析查询计划
• 避免N+1查询问题
• 合理使用JOIN和子查询

安全性考虑

1. 行级安全策略 (RLS)

-- 客户表RLS策略
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Users can view own customer data" ON customers
FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Users can update own customer data" ON customers
FOR UPDATE USING (auth.uid() = user_id);

2. 权限管理

-- 为不同角色分配权限
GRANT SELECT, INSERT, UPDATE ON customers TO authenticated;
GRANT SELECT, INSERT ON credits_history TO authenticated;
GRANT ALL ON subscriptions TO service_role;

3. 数据加密

• 敏感字段使用pgcrypto加密
• 传输过程使用SSL/TLS
• 定期备份和恢复测试

备份和恢复策略

1. 备份策略

• 每日全量备份
• 每小时增量备份
• 重要操作前手动备份

2. 恢复测试

• 定期进行恢复演练
• 验证数据完整性
• 测试备份文件可用性

监控和维护

1. 监控指标

• 数据库连接数
• 查询执行时间
• 磁盘空间使用
• 索引使用率

2. 维护任务

• 定期VACUUM和ANALYZE
• 监控表膨胀
• 更新统计信息
• 检查约束违规

最佳实践总结

1. 数据完整性: 使用外键和约束确保数据一致性
2. 性能优化: 合理创建索引和查询优化
3. 安全性: 实施RLS和权限控制
4. 可扩展性: 设计支持未来功能扩展
5. 监控: 持续监控数据库性能和健康状态
6. 备份: 制定完善的备份和恢复策略
7. 文档: 维护完整的数据库文档

这个数据库设计为订阅和积分管理系统提供了坚实的基础，通过合理的表结构、关系设计和安全策略，确保了系统的可靠性和可扩展性。

本文档为站内渲染。原始文件本地路径：saas/source/templates/模版-模版文档对比说明-09-数据库-database-schema-ceb6af.md（仅本地保留，不入库不部署）