Skip to content

sql.md

Latest commit

 

History

History
122 lines (103 loc) · 4.09 KB

sql.md

File metadata and controls

122 lines (103 loc) · 4.09 KB

-- 创建用户表结构 - 第一部分（不包含函数） -- Migration: 0001_create_user_tables.sql

-- ============================================ -- 1. user_profiles 表：扩展 auth.users 的用户资料 -- ============================================ CREATE TABLE IF NOT EXISTS public.user_profiles ( id UUID REFERENCES auth.users(id) PRIMARY KEY, email TEXT NOT NULL, name TEXT, avatar_url TEXT, role TEXT DEFAULT 'user' CHECK (role IN ('user', 'premium', 'admin')), email_verified BOOLEAN DEFAULT false, status TEXT DEFAULT 'active' CHECK (status IN ('active', 'suspended')), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW(), updated_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() );

ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;

CREATE INDEX IF NOT EXISTS idx_user_profiles_email ON public.user_profiles(email); CREATE INDEX IF NOT EXISTS idx_user_profiles_role ON public.user_profiles(role); CREATE INDEX IF NOT EXISTS idx_user_profiles_status ON public.user_profiles(status);

CREATE POLICY "Users can view own profile" ON public.user_profiles FOR SELECT USING (auth.uid() = id);

CREATE POLICY "Users can insert own profile" ON public.user_profiles FOR INSERT WITH CHECK (auth.uid() = id);

CREATE POLICY "Users can update own profile" ON public.user_profiles FOR UPDATE USING (auth.uid() = id);

CREATE POLICY "Admins can view all profiles" ON public.user_profiles FOR SELECT USING ( EXISTS ( SELECT 1 FROM public.user_profiles WHERE id = auth.uid() AND role = 'admin' ) );

CREATE POLICY "Admins can update all profiles" ON public.user_profiles FOR UPDATE USING ( EXISTS ( SELECT 1 FROM public.user_profiles WHERE id = auth.uid() AND role = 'admin' ) );

-- ============================================ -- 2. user_sessions 表：用户会话记录 -- ============================================ CREATE TABLE IF NOT EXISTS public.user_sessions ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, user_id UUID REFERENCES auth.users(id) NOT NULL, device_name TEXT, device_type TEXT, ip_address INET, user_agent TEXT, last_active TIMESTAMP WITH TIME ZONE DEFAULT NOW(), created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() );

ALTER TABLE public.user_sessions ENABLE ROW LEVEL SECURITY;

CREATE INDEX IF NOT EXISTS idx_user_sessions_user_id ON public.user_sessions(user_id); CREATE INDEX IF NOT EXISTS idx_user_sessions_last_active ON public.user_sessions(last_active);

CREATE POLICY "Users can view own sessions" ON public.user_sessions FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Admins can view all sessions" ON public.user_sessions FOR SELECT USING ( EXISTS ( SELECT 1 FROM public.user_profiles WHERE id = auth.uid() AND role = 'admin' ) );

-- ============================================ -- 3. user_security_log 表：安全日志 -- ============================================ CREATE TABLE IF NOT EXISTS public.user_security_log ( id UUID DEFAULT gen_random_uuid() PRIMARY KEY, user_id UUID REFERENCES auth.users(id) NOT NULL, event_type TEXT NOT NULL, ip_address INET, user_agent TEXT, success BOOLEAN NOT NULL, details JSONB, created_at TIMESTAMP WITH TIME ZONE DEFAULT NOW() );

ALTER TABLE public.user_security_log ENABLE ROW LEVEL SECURITY;

CREATE INDEX IF NOT EXISTS idx_user_security_log_user_id ON public.user_security_log(user_id); CREATE INDEX IF NOT EXISTS idx_user_security_log_created_at ON public.user_security_log(created_at); CREATE INDEX IF NOT EXISTS idx_user_security_log_event_type ON public.user_security_log(event_type);

CREATE POLICY "Users can view own security logs" ON public.user_security_log FOR SELECT USING (auth.uid() = user_id);

CREATE POLICY "Admins can view all security logs" ON public.user_security_log FOR SELECT USING ( EXISTS ( SELECT 1 FROM public.user_profiles WHERE id = auth.uid() AND role = 'admin' ) );

COMMENT ON TABLE public.user_profiles IS '用户资料表，扩展 auth.users'; COMMENT ON TABLE public.user_sessions IS '用户会话记录表'; COMMENT ON TABLE public.user_security_log IS '用户安全日志表';