89 lines
3.0 KiB
PL/PgSQL
89 lines
3.0 KiB
PL/PgSQL
-- ============================================================
|
|
-- ViGent 手机号登录迁移脚本
|
|
-- 用于将 email 字段改为 phone 字段
|
|
--
|
|
-- 执行方式（任选一种）：
|
|
-- 1. Supabase Studio: 打开 https://supabase.hbyrkj.top -> SQL Editor -> 粘贴执行
|
|
-- 2. Docker 命令: docker exec -i supabase-db psql -U postgres < migrate_to_phone.sql
|
|
-- ============================================================
|
|
|
|
-- 注意：此脚本会删除现有的用户数据！
|
|
-- 如需保留数据，请先备份
|
|
|
|
-- 1. 删除依赖表（有外键约束）
|
|
DROP TABLE IF EXISTS user_sessions CASCADE;
|
|
DROP TABLE IF EXISTS social_accounts CASCADE;
|
|
|
|
-- 2. 删除用户表
|
|
DROP TABLE IF EXISTS users CASCADE;
|
|
|
|
-- 3. 重新创建 users 表（使用 phone 字段）
|
|
CREATE TABLE users (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
phone TEXT UNIQUE NOT NULL,
|
|
password_hash TEXT NOT NULL,
|
|
username TEXT,
|
|
role TEXT DEFAULT 'pending' CHECK (role IN ('pending', 'user', 'admin')),
|
|
is_active BOOLEAN DEFAULT FALSE,
|
|
expires_at TIMESTAMP WITH TIME ZONE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- 4. 重新创建 user_sessions 表
|
|
CREATE TABLE user_sessions (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE UNIQUE,
|
|
session_token TEXT UNIQUE NOT NULL,
|
|
device_info TEXT,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW()
|
|
);
|
|
|
|
-- 5. 重新创建 social_accounts 表
|
|
CREATE TABLE social_accounts (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
user_id UUID REFERENCES users(id) ON DELETE CASCADE,
|
|
platform TEXT NOT NULL CHECK (platform IN ('bilibili', 'douyin', 'xiaohongshu')),
|
|
logged_in BOOLEAN DEFAULT FALSE,
|
|
created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(),
|
|
UNIQUE(user_id, platform)
|
|
);
|
|
|
|
-- 6. 创建索引
|
|
CREATE INDEX idx_users_phone ON users(phone);
|
|
CREATE INDEX idx_sessions_user_id ON user_sessions(user_id);
|
|
CREATE INDEX idx_social_user_platform ON social_accounts(user_id, platform);
|
|
|
|
-- 7. 启用 RLS
|
|
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE user_sessions ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE social_accounts ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- 8. 创建 RLS 策略
|
|
CREATE POLICY "Users can view own profile" ON users
|
|
FOR SELECT USING (auth.uid()::text = id::text);
|
|
|
|
CREATE POLICY "Users can access own sessions" ON user_sessions
|
|
FOR ALL USING (user_id::text = auth.uid()::text);
|
|
|
|
CREATE POLICY "Users can access own social accounts" ON social_accounts
|
|
FOR ALL USING (user_id::text = auth.uid()::text);
|
|
|
|
-- 9. 更新时间触发器
|
|
CREATE OR REPLACE FUNCTION update_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
DROP TRIGGER IF EXISTS users_updated_at ON users;
|
|
CREATE TRIGGER users_updated_at
|
|
BEFORE UPDATE ON users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- 完成！
|
|
-- 管理员账号会在后端服务重启时自动创建 (15549380526)
|