4e5147fb13
All checks were successful
Backend Deploy (Go + Docker) / deploy (push) Successful in 1m29s
239 lines
11 KiB
PL/PgSQL
239 lines
11 KiB
PL/PgSQL
-- ====================================================
|
||
-- InsightReply 数据库 Schema (PostgreSQL)
|
||
-- 版本: v1.1
|
||
-- 更新: 新增 api_usage_logs, subscriptions, user_style_profiles 表
|
||
-- ====================================================
|
||
|
||
-- users 表：存储业务用户
|
||
CREATE TABLE IF NOT EXISTS users (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
email VARCHAR(255) UNIQUE NOT NULL,
|
||
password_hash VARCHAR(255),
|
||
subscription_tier VARCHAR(50) DEFAULT 'Free', -- Free, Pro, Premium
|
||
identity_label VARCHAR(100), -- AI 创始人, SaaS Builder 等
|
||
language_preference VARCHAR(10) DEFAULT 'auto', -- en, zh, auto
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- monitored_accounts 表：存储用户重点监控的 X 账号
|
||
CREATE TABLE IF NOT EXISTS monitored_accounts (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
x_account_id VARCHAR(255),
|
||
x_handle VARCHAR(255) NOT NULL,
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE (user_id, x_handle)
|
||
);
|
||
|
||
-- monitored_keywords 表：存储用户重点监控的关键词
|
||
CREATE TABLE IF NOT EXISTS monitored_keywords (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
keyword VARCHAR(255) NOT NULL,
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE (user_id, keyword)
|
||
);
|
||
|
||
-- tweets 表：共享的推文数据池，AI 评论生成的上下文
|
||
CREATE TABLE IF NOT EXISTS tweets (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
x_tweet_id VARCHAR(255) UNIQUE NOT NULL,
|
||
author_id VARCHAR(255),
|
||
author_handle VARCHAR(255),
|
||
content TEXT NOT NULL,
|
||
posted_at TIMESTAMP WITH TIME ZONE,
|
||
like_count INTEGER DEFAULT 0,
|
||
retweet_count INTEGER DEFAULT 0,
|
||
reply_count INTEGER DEFAULT 0,
|
||
heat_score FLOAT DEFAULT 0.0,
|
||
crawl_queue VARCHAR(20) DEFAULT 'normal', -- high, normal, low (智能抓取频率)
|
||
is_processed BOOLEAN DEFAULT FALSE,
|
||
last_crawled_at TIMESTAMP WITH TIME ZONE, -- 上次抓取时间
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_tweets_x_tweet_id ON tweets(x_tweet_id);
|
||
CREATE INDEX IF NOT EXISTS idx_tweets_heat_score ON tweets(heat_score DESC);
|
||
CREATE INDEX IF NOT EXISTS idx_tweets_crawl_queue ON tweets(crawl_queue, last_crawled_at);
|
||
|
||
-- generated_replies 表：生成的 AI 评论记录
|
||
CREATE TABLE IF NOT EXISTS generated_replies (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
tweet_id UUID NOT NULL REFERENCES tweets(id) ON DELETE CASCADE,
|
||
strategy_type VARCHAR(100) NOT NULL, -- cognitive_upgrade, contrarian, data_supplement, empathy, founder_exp
|
||
content TEXT NOT NULL,
|
||
status VARCHAR(50) DEFAULT 'draft', -- draft, copied, posted
|
||
language VARCHAR(10) DEFAULT 'en', -- 生成语言
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_generated_replies_user_id ON generated_replies(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_generated_replies_tweet_id ON generated_replies(tweet_id);
|
||
|
||
-- reply_performance 表：针对已发布评论的效果数据回拨
|
||
CREATE TABLE IF NOT EXISTS reply_performance (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
reply_id UUID NOT NULL REFERENCES generated_replies(id) ON DELETE CASCADE,
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE, -- 冗余字段，便于按用户维度查询
|
||
like_count_increase INTEGER DEFAULT 0,
|
||
reply_count_increase INTEGER DEFAULT 0,
|
||
interaction_rate FLOAT DEFAULT 0.0,
|
||
check_time TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_reply_performance_reply_id ON reply_performance(reply_id);
|
||
CREATE INDEX IF NOT EXISTS idx_reply_performance_user_id ON reply_performance(user_id);
|
||
|
||
-- ====================================================
|
||
-- 新增表 (v1.1)
|
||
-- ====================================================
|
||
|
||
-- api_usage_logs 表：记录 LLM API 调用量和成本
|
||
CREATE TABLE IF NOT EXISTS api_usage_logs (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
provider VARCHAR(50) NOT NULL, -- openai, anthropic, deepseek
|
||
model VARCHAR(100) NOT NULL, -- gpt-4o-mini, claude-3.5-haiku 等
|
||
prompt_tokens INTEGER DEFAULT 0,
|
||
completion_tokens INTEGER DEFAULT 0,
|
||
total_tokens INTEGER GENERATED ALWAYS AS (prompt_tokens + completion_tokens) STORED,
|
||
cost_usd NUMERIC(10, 6) DEFAULT 0.0, -- 精确到 $0.000001
|
||
endpoint VARCHAR(100), -- /ai/generate
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_api_usage_logs_user_id ON api_usage_logs(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_api_usage_logs_created_at ON api_usage_logs(created_at DESC);
|
||
|
||
-- subscriptions 表：用户订阅记录（支付历史）
|
||
CREATE TABLE IF NOT EXISTS subscriptions (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
tier VARCHAR(50) NOT NULL, -- Pro, Premium
|
||
stripe_subscription_id VARCHAR(255), -- Stripe 订阅 ID
|
||
status VARCHAR(50) DEFAULT 'active', -- active, cancelled, past_due, expired
|
||
started_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
expires_at TIMESTAMP WITH TIME ZONE,
|
||
cancelled_at TIMESTAMP WITH TIME ZONE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_subscriptions_user_id ON subscriptions(user_id);
|
||
CREATE INDEX IF NOT EXISTS idx_subscriptions_status ON subscriptions(status);
|
||
|
||
-- user_style_profiles 表：用户风格画像（用于个性化 Prompt）
|
||
CREATE TABLE IF NOT EXISTS user_style_profiles (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
top_strategies JSONB DEFAULT '[]', -- 最常选择的策略排序
|
||
avg_reply_length INTEGER DEFAULT 200, -- 平均偏好回复长度
|
||
high_engagement_keywords JSONB DEFAULT '[]', -- 高互动关键词
|
||
tone_preference VARCHAR(100) DEFAULT 'professional', -- casual, professional, witty, provocative
|
||
custom_prompt_suffix TEXT, -- 用户自定义的额外 Prompt 指令
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- crawl_snapshots 表：异常抓取时的 HTML 快照（排错用）
|
||
CREATE TABLE IF NOT EXISTS crawl_snapshots (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
url TEXT NOT NULL,
|
||
http_status INTEGER,
|
||
html_content TEXT,
|
||
error_message TEXT,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_crawl_snapshots_created_at ON crawl_snapshots(created_at DESC);
|
||
|
||
-- ====================================================
|
||
-- 新增表 (v1.2) — 用户可配置系统
|
||
-- ====================================================
|
||
|
||
-- user_product_profiles 表：用户的产品档案（用于生成与产品相关联的评论）
|
||
-- 设计原则：所有字段用户自定义，系统不做任何硬编码
|
||
CREATE TABLE IF NOT EXISTS user_product_profiles (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID UNIQUE NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
product_name VARCHAR(255), -- 产品名称 (如 "SwiftBiu")
|
||
tagline TEXT, -- 一句话介绍
|
||
domain VARCHAR(255), -- 所属领域 (如 "AI Video Creation")
|
||
key_features JSONB DEFAULT '[]', -- 核心功能列表 ["视频生成", "多语言配音"]
|
||
target_users TEXT, -- 目标用户描述
|
||
product_url VARCHAR(500), -- 官网或商店链接
|
||
competitors JSONB DEFAULT '[]', -- 竞品名称列表 ["CapCut", "Descript"]
|
||
relevance_keywords JSONB DEFAULT '[]', -- 相关领域关键词 ["short video", "content creation", "AI dubbing"]
|
||
custom_context TEXT, -- 用户自定义的额外上下文（自由文本，注入 Prompt）
|
||
default_llm_provider VARCHAR(50), -- 用户专属模型偏好: openai, anthropic, deepseek, gemini (覆盖系统默认)
|
||
default_llm_model VARCHAR(100), -- 例如: claude-3-5-haiku-latest
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP
|
||
);
|
||
|
||
-- user_custom_strategies 表：用户自定义评论策略
|
||
-- 除系统内置的 5 种策略外，用户可以创建自己的策略模板
|
||
CREATE TABLE IF NOT EXISTS user_custom_strategies (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
strategy_key VARCHAR(100) NOT NULL, -- 策略标识 (如 "builder_story")
|
||
label VARCHAR(255) NOT NULL, -- 显示名称 (如 "创始人实战型")
|
||
icon VARCHAR(10), -- Emoji 图标
|
||
description TEXT, -- 策略描述（告诉 LLM 这种策略的写法）
|
||
prompt_template TEXT, -- 自定义 Prompt 模板（可包含 {tweet_content} {product_name} 等变量）
|
||
few_shot_examples JSONB DEFAULT '[]', -- 自定义 Few-shot 示例
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
sort_order INTEGER DEFAULT 0, -- 排序权重
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE (user_id, strategy_key)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_user_custom_strategies_user_id ON user_custom_strategies(user_id);
|
||
|
||
-- competitor_monitors 表：竞品品牌监控（复用后端雷达，按品牌词自动抓取）
|
||
CREATE TABLE IF NOT EXISTS competitor_monitors (
|
||
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
||
user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
|
||
brand_name VARCHAR(255) NOT NULL, -- 竞品品牌名
|
||
x_handle VARCHAR(255), -- 竞品官方 X 账号 (可选)
|
||
is_active BOOLEAN DEFAULT TRUE,
|
||
created_at TIMESTAMP WITH TIME ZONE DEFAULT CURRENT_TIMESTAMP,
|
||
UNIQUE (user_id, brand_name)
|
||
);
|
||
|
||
CREATE INDEX IF NOT EXISTS idx_competitor_monitors_user_id ON competitor_monitors(user_id);
|
||
|
||
-- ====================================================
|
||
-- 触发器：自动更新 updated_at
|
||
-- ====================================================
|
||
|
||
CREATE OR REPLACE FUNCTION update_modified_column()
|
||
RETURNS TRIGGER AS $$
|
||
BEGIN
|
||
NEW.updated_at = CURRENT_TIMESTAMP;
|
||
RETURN NEW;
|
||
END;
|
||
$$ language 'plpgsql';
|
||
|
||
-- 为所有需要追踪更新时间的表添加触发器
|
||
DO $$ BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_users_modtime') THEN
|
||
CREATE TRIGGER update_users_modtime
|
||
BEFORE UPDATE ON users
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_modified_column();
|
||
END IF;
|
||
END $$;
|
||
|
||
DO $$ BEGIN
|
||
IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_user_style_profiles_modtime') THEN
|
||
CREATE TRIGGER update_user_style_profiles_modtime
|
||
BEFORE UPDATE ON user_style_profiles
|
||
FOR EACH ROW
|
||
EXECUTE FUNCTION update_modified_column();
|
||
END IF;
|
||
END $$;
|