94 lines
3.1 KiB
PL/PgSQL
94 lines
3.1 KiB
PL/PgSQL
-- ============================================================
|
|
-- Migration: user_roles_permissions + contributor_invitations
|
|
-- Timestamp: 20260321090000
|
|
-- ============================================================
|
|
|
|
-- 1. Add role column to user_profiles
|
|
ALTER TABLE public.user_profiles
|
|
ADD COLUMN IF NOT EXISTS role TEXT NOT NULL DEFAULT 'reader';
|
|
|
|
ALTER TABLE public.user_profiles
|
|
ADD COLUMN IF NOT EXISTS is_active BOOLEAN NOT NULL DEFAULT true;
|
|
|
|
-- 2. contributor_invitations table
|
|
CREATE TABLE IF NOT EXISTS public.contributor_invitations (
|
|
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
|
|
email TEXT NOT NULL,
|
|
invited_by UUID REFERENCES public.user_profiles(id) ON DELETE SET NULL,
|
|
role TEXT NOT NULL DEFAULT 'contributor',
|
|
token TEXT NOT NULL UNIQUE,
|
|
status TEXT NOT NULL DEFAULT 'pending',
|
|
message TEXT,
|
|
expires_at TIMESTAMPTZ NOT NULL DEFAULT (CURRENT_TIMESTAMP + INTERVAL '7 days'),
|
|
accepted_at TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP,
|
|
updated_at TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
|
|
);
|
|
|
|
CREATE INDEX IF NOT EXISTS idx_contributor_invitations_email ON public.contributor_invitations(email);
|
|
CREATE INDEX IF NOT EXISTS idx_contributor_invitations_token ON public.contributor_invitations(token);
|
|
CREATE INDEX IF NOT EXISTS idx_contributor_invitations_status ON public.contributor_invitations(status);
|
|
|
|
-- 3. Functions (BEFORE RLS policies)
|
|
CREATE OR REPLACE FUNCTION public.is_admin_user()
|
|
RETURNS BOOLEAN
|
|
LANGUAGE sql
|
|
STABLE
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT EXISTS (
|
|
SELECT 1 FROM auth.users au
|
|
WHERE au.id = auth.uid()
|
|
AND (
|
|
au.raw_user_meta_data->>'role' = 'admin'
|
|
OR au.raw_app_meta_data->>'role' = 'admin'
|
|
)
|
|
)
|
|
$$;
|
|
|
|
CREATE OR REPLACE FUNCTION public.get_user_role()
|
|
RETURNS TEXT
|
|
LANGUAGE sql
|
|
STABLE
|
|
SECURITY DEFINER
|
|
AS $$
|
|
SELECT COALESCE(
|
|
(SELECT role FROM public.user_profiles WHERE id = auth.uid() LIMIT 1),
|
|
'reader'
|
|
)
|
|
$$;
|
|
|
|
-- 4. Enable RLS
|
|
ALTER TABLE public.contributor_invitations ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- 5. RLS Policies for contributor_invitations
|
|
DROP POLICY IF EXISTS "admin_manage_invitations" ON public.contributor_invitations;
|
|
CREATE POLICY "admin_manage_invitations"
|
|
ON public.contributor_invitations
|
|
FOR ALL
|
|
TO authenticated
|
|
USING (public.is_admin_user())
|
|
WITH CHECK (public.is_admin_user());
|
|
|
|
DROP POLICY IF EXISTS "public_read_own_invitation_by_token" ON public.contributor_invitations;
|
|
CREATE POLICY "public_read_own_invitation_by_token"
|
|
ON public.contributor_invitations
|
|
FOR SELECT
|
|
TO public
|
|
USING (true);
|
|
|
|
-- 6. RLS Policies for user_profiles (admin can manage all)
|
|
DROP POLICY IF EXISTS "admin_manage_all_user_profiles" ON public.user_profiles;
|
|
CREATE POLICY "admin_manage_all_user_profiles"
|
|
ON public.user_profiles
|
|
FOR ALL
|
|
TO authenticated
|
|
USING (public.is_admin_user())
|
|
WITH CHECK (public.is_admin_user());
|
|
|
|
-- 7. Trigger for updated_at on invitations
|
|
DROP TRIGGER IF EXISTS update_contributor_invitations_updated_at ON public.contributor_invitations;
|
|
CREATE TRIGGER update_contributor_invitations_updated_at
|
|
BEFORE UPDATE ON public.contributor_invitations
|
|
FOR EACH ROW EXECUTE FUNCTION public.update_updated_at();
|