128 lines
3.9 KiB
PL/PgSQL
128 lines
3.9 KiB
PL/PgSQL
-- ============================================================
|
|
-- Migration: user_profiles + article_comments
|
|
-- Timestamp: 20260312060000
|
|
-- ============================================================
|
|
|
|
-- 1. user_profiles table
|
|
CREATE TABLE IF NOT EXISTS public.user_profiles (
|
|
id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
|
|
email TEXT NOT NULL,
|
|
full_name TEXT NOT NULL DEFAULT '',
|
|
avatar_url TEXT,
|
|
bio TEXT,
|
|
website TEXT,
|
|
location TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_user_profiles_id ON public.user_profiles(id);
|
|
|
|
-- 2. article_comments table
|
|
CREATE TABLE IF NOT EXISTS public.article_comments (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
article_slug TEXT NOT NULL,
|
|
user_id UUID NOT NULL REFERENCES public.user_profiles(id) ON DELETE CASCADE,
|
|
content TEXT NOT NULL,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_article_comments_slug ON public.article_comments(article_slug);
|
|
CREATE INDEX IF NOT EXISTS idx_article_comments_user_id ON public.article_comments(user_id);
|
|
|
|
-- 3. Functions (BEFORE RLS policies)
|
|
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
SECURITY DEFINER
|
|
AS $$
|
|
BEGIN
|
|
INSERT INTO public.user_profiles (id, email, full_name, avatar_url)
|
|
VALUES (
|
|
NEW.id,
|
|
NEW.email,
|
|
COALESCE(NEW.raw_user_meta_data->>'full_name', ''),
|
|
COALESCE(NEW.raw_user_meta_data->>'avatar_url', '')
|
|
)
|
|
ON CONFLICT (id) DO NOTHING;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.update_updated_at()
|
|
RETURNS TRIGGER
|
|
LANGUAGE plpgsql
|
|
AS $$
|
|
BEGIN
|
|
NEW.updated_at = CURRENT_TIMESTAMP;
|
|
RETURN NEW;
|
|
END;
|
|
$$;
|
|
|
|
-- 4. Enable RLS
|
|
ALTER TABLE public.user_profiles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.article_comments ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- 5. RLS Policies for user_profiles
|
|
DROP POLICY IF EXISTS "users_manage_own_user_profiles" ON public.user_profiles;
|
|
CREATE POLICY "users_manage_own_user_profiles"
|
|
ON public.user_profiles
|
|
FOR ALL
|
|
TO authenticated
|
|
USING (id = auth.uid())
|
|
WITH CHECK (id = auth.uid());
|
|
|
|
DROP POLICY IF EXISTS "public_read_user_profiles" ON public.user_profiles;
|
|
CREATE POLICY "public_read_user_profiles"
|
|
ON public.user_profiles
|
|
FOR SELECT
|
|
TO public
|
|
USING (true);
|
|
|
|
-- 6. RLS Policies for article_comments
|
|
DROP POLICY IF EXISTS "public_read_article_comments" ON public.article_comments;
|
|
CREATE POLICY "public_read_article_comments"
|
|
ON public.article_comments
|
|
FOR SELECT
|
|
TO public
|
|
USING (true);
|
|
|
|
DROP POLICY IF EXISTS "users_insert_own_comments" ON public.article_comments;
|
|
CREATE POLICY "users_insert_own_comments"
|
|
ON public.article_comments
|
|
FOR INSERT
|
|
TO authenticated
|
|
WITH CHECK (user_id = auth.uid());
|
|
|
|
DROP POLICY IF EXISTS "users_update_own_comments" ON public.article_comments;
|
|
CREATE POLICY "users_update_own_comments"
|
|
ON public.article_comments
|
|
FOR UPDATE
|
|
TO authenticated
|
|
USING (user_id = auth.uid())
|
|
WITH CHECK (user_id = auth.uid());
|
|
|
|
DROP POLICY IF EXISTS "users_delete_own_comments" ON public.article_comments;
|
|
CREATE POLICY "users_delete_own_comments"
|
|
ON public.article_comments
|
|
FOR DELETE
|
|
TO authenticated
|
|
USING (user_id = auth.uid());
|
|
|
|
-- 7. Triggers
|
|
DROP TRIGGER IF EXISTS on_auth_user_created ON auth.users;
|
|
CREATE TRIGGER on_auth_user_created
|
|
AFTER INSERT ON auth.users
|
|
FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();
|
|
|
|
DROP TRIGGER IF EXISTS update_user_profiles_updated_at ON public.user_profiles;
|
|
CREATE TRIGGER update_user_profiles_updated_at
|
|
BEFORE UPDATE ON public.user_profiles
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|
|
|
|
DROP TRIGGER IF EXISTS update_article_comments_updated_at ON public.article_comments;
|
|
CREATE TRIGGER update_article_comments_updated_at
|
|
BEFORE UPDATE ON public.article_comments
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|