数据库设计文档

CREATE TABLE users ( user_id VARCHAR(32) PRIMARY KEY, -- usr_xxx phone VARCHAR(20) UNIQUE, email VARCHAR(100), nickname VARCHAR(30) NOT NULL, avatar_url TEXT, mother_tongue VARCHAR(10) NOT NULL, -- zh-CN / en-US target_language VARCHAR(10) NOT NULL, gender VARCHAR(10), -- male/female/other/prefer_not_say birth_year SMALLINT, country_code VARCHAR(5) DEFAULT 'SG', vip_status VARCHAR(20) DEFAULT 'none', -- none/active/expired vip_expire_at BIGINT, coins_balance INTEGER DEFAULT 0, free_calls_left SMALLINT DEFAULT 10, free_calls_reset_at BIGINT, rating_avg DECIMAL(3,1) DEFAULT 5.0, total_calls INTEGER DEFAULT 0, trust_score SMALLINT DEFAULT 100, is_active BOOLEAN DEFAULT TRUE, is_banned BOOLEAN DEFAULT FALSE, ban_reason VARCHAR(100), device_ids JSONB DEFAULT '[]', created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL, deleted_at BIGINT -- 软删除时间戳 ); CREATE INDEX idx_users_lang ON users(mother_tongue, target_language); CREATE INDEX idx_users_online ON users(is_active) WHERE is_active = TRUE; CREATE INDEX idx_users_vip ON users(vip_status, vip_expire_at);

CREATE TABLE calls ( call_id VARCHAR(32) PRIMARY KEY, -- call_xxx caller_id VARCHAR(32) NOT NULL REFERENCES users(user_id), callee_id VARCHAR(32) NOT NULL REFERENCES users(user_id), start_time BIGINT NOT NULL, end_time BIGINT, duration_seconds INTEGER DEFAULT 0, caller_language VARCHAR(10), callee_language VARCHAR(10), cost_coins INTEGER DEFAULT 0, earner_coins INTEGER DEFAULT 0, caller_rating SMALLINT, -- 1-5 callee_rating SMALLINT, end_reason VARCHAR(20), -- normal_hangup/timeout/error/report has_translation BOOLEAN DEFAULT TRUE, avg_latency_ms INTEGER, quality_score DECIMAL(3,1), created_at BIGINT NOT NULL ) PARTITION BY RANGE (start_time); -- 按月分区（自动创建） CREATE INDEX idx_calls_user_time ON calls(caller_id, start_time DESC); CREATE INDEX idx_calls_callee_time ON calls(callee_id, start_time DESC);

CREATE TABLE translations ( translation_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, call_id VARCHAR(32) NOT NULL REFERENCES calls(call_id), seq INTEGER NOT NULL, speaker_id VARCHAR(32) NOT NULL, source_lang VARCHAR(10) NOT NULL, target_lang VARCHAR(10) NOT NULL, source_text TEXT NOT NULL, translated_text TEXT, asr_confidence DECIMAL(4,3), translate_confidence DECIMAL(4,3), latency_ms INTEGER, engine VARCHAR(30), -- google_translate/azure/self_hosted was_cached BOOLEAN DEFAULT FALSE, is_flagged BOOLEAN DEFAULT FALSE, created_at BIGINT NOT NULL, UNIQUE(call_id, seq, speaker_id) ) PARTITION BY RANGE (created_at); CREATE INDEX idx_translations_call ON translations(call_id, seq); CREATE INDEX idx_translations_flagged ON translations(is_flagged) WHERE is_flagged = TRUE;

CREATE TABLE payments ( payment_id VARCHAR(32) PRIMARY KEY, user_id VARCHAR(32) NOT NULL REFERENCES users(user_id), type VARCHAR(20) NOT NULL, -- coin_purchase/vip_subscription/gift amount_sgd DECIMAL(10,2) NOT NULL, coins_amount INTEGER, channel VARCHAR(20) NOT NULL, -- paynow/stripe/apple_iap/google_play channel_tx_id VARCHAR(100), status VARCHAR(20) NOT NULL, -- pending/completed/failed/refunded failure_reason VARCHAR(200), receipt_data TEXT, -- Apple/Google 回执（加密） metadata JSONB, created_at BIGINT NOT NULL, completed_at BIGINT, CHECK(amount_sgd >= 0) ); CREATE INDEX idx_payments_user ON payments(user_id, created_at DESC); CREATE INDEX idx_payments_status ON payments(status, created_at);

CREATE TABLE subscriptions ( subscription_id VARCHAR(32) PRIMARY KEY, user_id VARCHAR(32) NOT NULL REFERENCES users(user_id), plan VARCHAR(20) NOT NULL, -- vip_week/vip_month/vip_year status VARCHAR(20) NOT NULL, -- active/cancelled/expired/past_due start_date BIGINT NOT NULL, end_date BIGINT NOT NULL, auto_renew BOOLEAN DEFAULT TRUE, channel VARCHAR(20), original_transaction_id VARCHAR(100), created_at BIGINT NOT NULL, updated_at BIGINT NOT NULL ); CREATE INDEX idx_subs_user ON subscriptions(user_id, status); CREATE INDEX idx_subs_expire ON subscriptions(end_date) WHERE status = 'active';

CREATE TABLE buddies ( buddy_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, user_a_id VARCHAR(32) NOT NULL REFERENCES users(user_id), user_b_id VARCHAR(32) NOT NULL REFERENCES users(user_id), status VARCHAR(20) DEFAULT 'active', -- active/blocked/muted is_favorite BOOLEAN DEFAULT FALSE, total_calls INTEGER DEFAULT 0, last_call_at BIGINT, created_at BIGINT NOT NULL, CHECK(user_a_id < user_b_id), -- 保证唯一对 UNIQUE(user_a_id, user_b_id) ); CREATE INDEX idx_buddies_user ON buddies(user_a_id, status);

CREATE TABLE messages ( message_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, sender_id VARCHAR(32) NOT NULL REFERENCES users(user_id), receiver_id VARCHAR(32) NOT NULL REFERENCES users(user_id), type VARCHAR(10) DEFAULT 'text', -- text/gift/system content TEXT, translated_content TEXT, is_read BOOLEAN DEFAULT FALSE, is_flagged BOOLEAN DEFAULT FALSE, created_at BIGINT NOT NULL ) PARTITION BY RANGE (created_at); CREATE INDEX idx_messages_conv ON messages(sender_id, receiver_id, created_at DESC); CREATE INDEX idx_messages_unread ON messages(receiver_id, is_read) WHERE is_read = FALSE;

CREATE TABLE reports ( report_id VARCHAR(32) PRIMARY KEY, reporter_id VARCHAR(32) NOT NULL REFERENCES users(user_id), reported_user_id VARCHAR(32) NOT NULL REFERENCES users(user_id), call_id VARCHAR(32) REFERENCES calls(call_id), reason VARCHAR(30) NOT NULL, description TEXT, severity VARCHAR(10) DEFAULT 'medium', -- low/medium/high/critical status VARCHAR(20) DEFAULT 'pending', -- pending/reviewing/resolved/dismissed resolution VARCHAR(50), -- user_warned/user_banned/false_positive moderator_id VARCHAR(32), evidence JSONB, created_at BIGINT NOT NULL, resolved_at BIGINT ); CREATE INDEX idx_reports_pending ON reports(status, severity, created_at) WHERE status = 'pending'; CREATE INDEX idx_reports_user ON reports(reported_user_id, status);

CREATE TABLE gifts ( gift_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, from_user_id VARCHAR(32) NOT NULL REFERENCES users(user_id), to_user_id VARCHAR(32) NOT NULL REFERENCES users(user_id), gift_type VARCHAR(20) NOT NULL, -- flower/applause/melody/crown/heart coins_cost INTEGER NOT NULL, call_id VARCHAR(32) REFERENCES calls(call_id), is_anonymous BOOLEAN DEFAULT FALSE, created_at BIGINT NOT NULL ); CREATE INDEX idx_gifts_receiver ON gifts(to_user_id, created_at DESC);

CREATE TABLE ad_events ( event_id BIGINT PRIMARY KEY GENERATED ALWAYS AS IDENTITY, user_id VARCHAR(32) NOT NULL REFERENCES users(user_id), ad_type VARCHAR(20) NOT NULL, -- rewarded_video/banner event_type VARCHAR(20) NOT NULL, -- impression/click/reward/dismiss/error provider VARCHAR(20), -- admob/ironsource ad_unit_id VARCHAR(50), reward_granted BOOLEAN DEFAULT FALSE, reward_coins INTEGER DEFAULT 0, error_code VARCHAR(20), created_at BIGINT NOT NULL ) PARTITION BY RANGE (created_at); CREATE INDEX idx_ads_user_daily ON ad_events(user_id, event_type, created_at DESC);

# 加入匹配池 ZADD match:pool:zh-CN <rating_score> <user_id> ZADD match:pool:en-US <rating_score> <user_id> # 查找匹配（按评分邻近） ZRANGEBYSCORE match:pool:en-US <min_score> <max_score> LIMIT 0 10 # 移除（匹配成功/取消/离线） ZREM match:pool:zh-CN <user_id> # 过期清理（定时任务每 30s） ZREMRANGEBYSCORE match:pool:zh-CN -inf <current_time - 60s>

-- 每月 1 号 03:00 执行（pg_cron） SELECT create_archive_partition( p_table_name := 'calls', p_month := DATE_TRUNC('month', NOW() - INTERVAL '3 months') ); -- 流程： -- 1. 将 3 个月前的分区数据导出为 Parquet → S3 -- 2. 验证 S3 数据完整性（行数 + checksum） -- 3. DROP 原分区 -- 4. 创建 FOREIGN TABLE 指向 S3（仍可查询）

migrations/ ├── V1__init_schema.sql -- 所有 CREATE TABLE ├── V2__create_indexes.sql -- 所有索引 ├── V3__seed_countries.sql -- 国家/语言种子数据 ├── V4__create_partitions.sql -- 分区表初始化 ├── V5__setup_pgcron.sql -- 定时任务 ├── V6__encrypt_existing.sql -- 列加密迁移 └── V7__add_feature_flags.sql -- 功能开关种子

users 1──┐ │ ├──< calls >──┐ │ │ users 2──┘ ├──< translations │ └──< gifts users 1──< buddies >── users 2 (self-referencing) users 1──< messages >── users 2 users ──< payments users ──< subscriptions users ──< reports (as reporter) users ──< reports (as reported) users ──< ad_events