4c42d6b6f5
## 完成的功能 - 用户登录/注册（邮箱 + Google OAuth） - 初始问题引导流程（4 个问题） - 智能体自动生成（根据用户回答） - 智能体列表页面 ## 新增文件 - 登录/注册页面和组件 - Onboarding 页面和组件 - 智能体列表页面 - 智能体 API 端点 - 智能体 Prompt 设计 ## 数据库迁移 - onboarding_fix 迁移（users.has_completed_onboarding） ## 测试 - 登录/注册：100% 通过 - Onboarding：85% 通过 - 智能体功能：85% 通过 Co-Authored-By: Claude <noreply@anthropic.com>
380 lines
16 KiB
PL/PgSQL
380 lines
16 KiB
PL/PgSQL
-- ============================================================================
|
||
-- Echo (回声) - 初始数据库 Schema 迁移脚本
|
||
-- 版本: 202601120001
|
||
-- 创建日期: 2026-01-12
|
||
-- ============================================================================
|
||
|
||
-- 启用 UUID 扩展
|
||
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
|
||
|
||
-- ============================================================================
|
||
-- 第一部分: 公共函数定义
|
||
-- ============================================================================
|
||
|
||
-- 更新时间戳触发器函数
|
||
CREATE OR REPLACE FUNCTION update_updated_at_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = NOW();
|
||
RETURN NEW;
|
||
END;
|
||
$$ language 'plpgsql';
|
||
|
||
-- ============================================================================
|
||
-- 第二部分: 表创建（按依赖顺序）
|
||
-- ============================================================================
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.1 users - 用户表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE users (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
email VARCHAR(255) UNIQUE NOT NULL,
|
||
google_id VARCHAR(255) UNIQUE,
|
||
avatar_url TEXT,
|
||
language VARCHAR(10) DEFAULT 'zh-CN',
|
||
subscription_status VARCHAR(20) DEFAULT 'free',
|
||
subscription_expires_at TIMESTAMP WITH TIME ZONE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 用户表索引
|
||
CREATE INDEX idx_users_email ON users(email);
|
||
CREATE INDEX idx_users_google_id ON users(google_id);
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.2 agents - 智能体表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE agents (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
name VARCHAR(100) NOT NULL,
|
||
personality JSONB NOT NULL DEFAULT '{}',
|
||
background TEXT,
|
||
avatar_url TEXT,
|
||
is_custom BOOLEAN DEFAULT FALSE,
|
||
is_default BOOLEAN DEFAULT FALSE,
|
||
language VARCHAR(10) DEFAULT 'zh-CN',
|
||
system_prompt TEXT,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 智能体表索引
|
||
CREATE INDEX idx_agents_user_id ON agents(user_id);
|
||
CREATE INDEX idx_agents_default ON agents(user_id, is_default) WHERE is_custom = FALSE;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.3 onboarding_answers - 初始问题回答表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE onboarding_answers (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
question_1 TEXT NOT NULL,
|
||
question_2 TEXT NOT NULL,
|
||
question_3 TEXT NOT NULL,
|
||
question_4 TEXT NOT NULL,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 初始问题回答表索引
|
||
CREATE INDEX idx_onboarding_answers_user_id ON onboarding_answers(user_id);
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.4 letters - 信件表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE letters (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
agent_id UUID NOT NULL REFERENCES agents(id) ON DELETE CASCADE,
|
||
content TEXT NOT NULL,
|
||
ai_reply TEXT,
|
||
status VARCHAR(20) DEFAULT 'draft',
|
||
scheduled_at TIMESTAMP WITH TIME ZONE,
|
||
replied_at TIMESTAMP WITH TIME ZONE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 信件表索引
|
||
CREATE INDEX idx_letters_user_id ON letters(user_id);
|
||
CREATE INDEX idx_letters_agent_id ON letters(agent_id);
|
||
CREATE INDEX idx_letters_status ON letters(status);
|
||
CREATE INDEX idx_letters_scheduled ON letters(status, scheduled_at)
|
||
WHERE status = 'pending';
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.5 stamp_definitions - 邮票定义表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE stamp_definitions (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
code VARCHAR(50) UNIQUE NOT NULL,
|
||
name VARCHAR(100) NOT NULL,
|
||
description TEXT,
|
||
stamp_type VARCHAR(30) NOT NULL,
|
||
rarity VARCHAR(20) DEFAULT 'common',
|
||
image_url TEXT NOT NULL,
|
||
valid_from TIMESTAMP WITH TIME ZONE,
|
||
valid_until TIMESTAMP WITH TIME ZONE,
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 邮票定义表索引
|
||
CREATE INDEX idx_stamp_definitions_code ON stamp_definitions(code);
|
||
CREATE INDEX idx_stamp_definitions_type ON stamp_definitions(stamp_type);
|
||
CREATE INDEX idx_stamp_definitions_active ON stamp_definitions(is_active);
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.6 user_stamps - 用户邮票表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE user_stamps (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
stamp_def_id UUID NOT NULL REFERENCES stamp_definitions(id),
|
||
source VARCHAR(30) NOT NULL,
|
||
letter_id UUID REFERENCES letters(id),
|
||
obtained_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
used_at TIMESTAMP WITH TIME ZONE
|
||
);
|
||
|
||
-- 用户邮票表索引
|
||
CREATE INDEX idx_user_stamps_user_id ON user_stamps(user_id);
|
||
CREATE INDEX idx_user_stamps_letter_id ON user_stamps(letter_id);
|
||
CREATE INDEX idx_user_stamps_unused ON user_stamps(user_id, used_at)
|
||
WHERE used_at IS NULL;
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.7 growth_tags - 成长标签表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE growth_tags (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
letter_id UUID NOT NULL REFERENCES letters(id) ON DELETE CASCADE,
|
||
mood_tags TEXT[] DEFAULT '{}',
|
||
topic_tags TEXT[] DEFAULT '{}',
|
||
behavior_tags TEXT[] DEFAULT '{}',
|
||
sentiment_score DECIMAL(3,2),
|
||
keywords TEXT[] DEFAULT '{}',
|
||
ai_analysis TEXT,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 成长标签表索引
|
||
CREATE INDEX idx_growth_tags_user_id ON growth_tags(user_id);
|
||
CREATE INDEX idx_growth_tags_letter_id ON growth_tags(letter_id);
|
||
CREATE INDEX idx_growth_tags_mood ON growth_tags USING GIN (mood_tags);
|
||
CREATE INDEX idx_growth_tags_topic ON growth_tags USING GIN (topic_tags);
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.8 milestones - 里程碑定义表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE milestones (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
code VARCHAR(50) UNIQUE NOT NULL,
|
||
name VARCHAR(100) NOT NULL,
|
||
description TEXT,
|
||
condition_type VARCHAR(30) NOT NULL,
|
||
condition_value INTEGER NOT NULL,
|
||
reward_stamp_def_id UUID REFERENCES stamp_definitions(id),
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
||
);
|
||
|
||
-- 里程碑定义表索引
|
||
CREATE INDEX idx_milestones_code ON milestones(code);
|
||
CREATE INDEX idx_milestones_active ON milestones(is_active);
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.9 achievements - 用户成就表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE achievements (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
milestone_id UUID NOT NULL REFERENCES milestones(id),
|
||
achieved_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
||
notification_sent BOOLEAN DEFAULT FALSE,
|
||
UNIQUE(user_id, milestone_id)
|
||
);
|
||
|
||
-- 用户成就表索引
|
||
CREATE INDEX idx_achievements_user_id ON achievements(user_id);
|
||
CREATE INDEX idx_achievements_milestone_id ON achievements(milestone_id);
|
||
|
||
-- ---------------------------------------------------------------------------
|
||
-- 2.10 daily_stats - 每日统计表
|
||
-- ---------------------------------------------------------------------------
|
||
CREATE TABLE daily_stats (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
stat_date DATE NOT NULL,
|
||
letters_sent INTEGER DEFAULT 0,
|
||
stamps_used INTEGER DEFAULT 0,
|
||
stamps_granted INTEGER DEFAULT 0,
|
||
last_active_at TIMESTAMP WITH TIME ZONE,
|
||
UNIQUE(user_id, stat_date)
|
||
);
|
||
|
||
-- 每日统计表索引
|
||
CREATE INDEX idx_daily_stats_user_date ON daily_stats(user_id, stat_date);
|
||
|
||
-- ============================================================================
|
||
-- 第三部分: 自动更新时间戳触发器
|
||
-- ============================================================================
|
||
|
||
CREATE TRIGGER update_users_updated_at
|
||
BEFORE UPDATE ON users
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER update_agents_updated_at
|
||
BEFORE UPDATE ON agents
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
CREATE TRIGGER update_letters_updated_at
|
||
BEFORE UPDATE ON letters
|
||
FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();
|
||
|
||
-- ============================================================================
|
||
-- 第四部分: Row Level Security (RLS) 策略
|
||
-- ============================================================================
|
||
|
||
-- 4.1 users - 用户表 RLS
|
||
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view own profile" ON users
|
||
FOR SELECT USING (auth.uid() = id);
|
||
|
||
CREATE POLICY "Users can update own profile" ON users
|
||
FOR UPDATE USING (auth.uid() = id);
|
||
|
||
-- 4.2 agents - 智能体表 RLS
|
||
ALTER TABLE agents ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view own agents" ON agents
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can create agents" ON agents
|
||
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update own agents" ON agents
|
||
FOR UPDATE USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can delete own agents" ON agents
|
||
FOR DELETE USING (auth.uid() = user_id);
|
||
|
||
-- 4.3 onboarding_answers - 初始问题回答表 RLS
|
||
ALTER TABLE onboarding_answers ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view own onboarding answers" ON onboarding_answers
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can create onboarding answers" ON onboarding_answers
|
||
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update own onboarding answers" ON onboarding_answers
|
||
FOR UPDATE USING (auth.uid() = user_id);
|
||
|
||
-- 4.4 letters - 信件表 RLS
|
||
ALTER TABLE letters ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view own letters" ON letters
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can create letters" ON letters
|
||
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can update own letters" ON letters
|
||
FOR UPDATE USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "Users can delete own letters" ON letters
|
||
FOR DELETE USING (auth.uid() = user_id);
|
||
|
||
-- 4.5 stamp_definitions - 邮票定义表 RLS（公开查询）
|
||
ALTER TABLE stamp_definitions ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Anyone can view stamp definitions" ON stamp_definitions
|
||
FOR SELECT USING (true);
|
||
|
||
-- 4.6 user_stamps - 用户邮票表 RLS
|
||
ALTER TABLE user_stamps ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view own stamps" ON user_stamps
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "System can update user stamps" ON user_stamps
|
||
FOR UPDATE USING (auth.role() = 'service_role');
|
||
|
||
CREATE POLICY "System can insert user stamps" ON user_stamps
|
||
FOR INSERT WITH CHECK (auth.role() = 'service_role');
|
||
|
||
-- 4.7 growth_tags - 成长标签表 RLS
|
||
ALTER TABLE growth_tags ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view own growth tags" ON growth_tags
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "System can create growth tags" ON growth_tags
|
||
FOR INSERT WITH CHECK (auth.role() = 'service_role');
|
||
|
||
CREATE POLICY "System can update growth tags" ON growth_tags
|
||
FOR UPDATE USING (auth.role() = 'service_role');
|
||
|
||
-- 4.8 milestones - 里程碑定义表 RLS（公开查询）
|
||
ALTER TABLE milestones ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Anyone can view milestones" ON milestones
|
||
FOR SELECT USING (true);
|
||
|
||
-- 4.9 achievements - 用户成就表 RLS
|
||
ALTER TABLE achievements ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view own achievements" ON achievements
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "System can create achievements" ON achievements
|
||
FOR INSERT WITH CHECK (auth.role() = 'service_role');
|
||
|
||
-- 4.10 daily_stats - 每日统计表 RLS
|
||
ALTER TABLE daily_stats ENABLE ROW LEVEL SECURITY;
|
||
|
||
CREATE POLICY "Users can view own daily stats" ON daily_stats
|
||
FOR SELECT USING (auth.uid() = user_id);
|
||
|
||
CREATE POLICY "System can update daily stats" ON daily_stats
|
||
FOR UPDATE USING (auth.role() = 'service_role');
|
||
|
||
CREATE POLICY "System can insert daily stats" ON daily_stats
|
||
FOR INSERT WITH CHECK (auth.role() = 'service_role');
|
||
|
||
-- ============================================================================
|
||
-- 第五部分: 初始数据插入
|
||
-- ============================================================================
|
||
|
||
-- 5.1 邮票定义初始数据
|
||
INSERT INTO stamp_definitions (code, name, description, stamp_type, rarity, image_url, is_active) VALUES
|
||
('daily_default', '每日邮票', '每日自动发放的基础邮票', 'daily', 'common', '/stamps/daily.png', TRUE),
|
||
('welcome', '初遇回声', '完成注册，获得的第一个成就', 'achievement', 'rare', '/stamps/welcome.png', TRUE),
|
||
('first_letter', '第一封信', '成功发送第一封信件', 'achievement', 'rare', '/stamps/first_letter.png', TRUE),
|
||
('week_streak', '持续对话', '连续 7 天写信', 'achievement', 'epic', '/stamps/week_streak.png', TRUE),
|
||
('month_streak', '一月相伴', '连续 30 天写信', 'achievement', 'legendary', '/stamps/month_streak.png', TRUE),
|
||
('spring_2025', '春日限定 2025', '2025 春季活动限定邮票', 'limited', 'epic', '/stamps/spring_2025.png', TRUE),
|
||
('collector_10', '集邮家', '收集 10 种不同邮票', 'achievement', 'rare', '/stamps/collector_10.png', TRUE),
|
||
('collector_50', '邮票猎人', '收集 50 种不同邮票', 'achievement', 'epic', '/stamps/collector_50.png', TRUE);
|
||
|
||
-- 5.2 里程碑定义初始数据
|
||
INSERT INTO milestones (code, name, description, condition_type, condition_value, reward_stamp_def_id, is_active) VALUES
|
||
('welcome', '初遇回声', '完成注册，开启回声之旅', 'registration', 1, (SELECT id FROM stamp_definitions WHERE code = 'welcome'), TRUE),
|
||
('first_letter', '第一封信', '成功发送第一封信给未来的自己', 'letter_count', 1, (SELECT id FROM stamp_definitions WHERE code = 'first_letter'), TRUE),
|
||
('week_streak', '持续对话', '连续 7 天写信', 'consecutive_days', 7, (SELECT id FROM stamp_definitions WHERE code = 'week_streak'), TRUE),
|
||
('month_streak', '一月相伴', '连续 30 天写信', 'consecutive_days', 30, (SELECT id FROM stamp_definitions WHERE code = 'month_streak'), TRUE),
|
||
('letters_10', '十封信', '累计发送 10 封信', 'total_letters', 10, NULL, TRUE),
|
||
('letters_50', '五十封信', '累计发送 50 封信', 'total_letters', 50, NULL, TRUE),
|
||
('letters_100', '百封信', '累计发送 100 封信', 'total_letters', 100, NULL, TRUE),
|
||
('collector_10', '集邮家初级', '收集 10 种不同邮票', 'unique_stamps', 10, (SELECT id FROM stamp_definitions WHERE code = 'collector_10'), TRUE),
|
||
('collector_50', '集邮家高级', '收集 50 种不同邮票', 'unique_stamps', 50, (SELECT id FROM stamp_definitions WHERE code = 'collector_50'), TRUE);
|
||
|
||
-- ============================================================================
|
||
-- 迁移完成
|
||
-- ============================================================================
|