41e686c560
Full-stack Next.js 15 application with: - Landing page with marketing components (Hero, Testimonials, Pricing, FAQ) - Multi-step candidature form with API route - Stripe Checkout integration (subscription + webhooks) - Supabase Auth (login/register) with middleware protection - Dashboard with progress tracking and module system - Formations pages with completion tracking - Profile management with password change - Database schema with RLS policies - Resend email integration for transactional emails Stack: Next.js 15, TypeScript, Tailwind CSS v4, Supabase, Stripe, Resend https://claude.ai/code/session_01H2aRGDaKgarPvhay2HxN6Y
134 lines
4.2 KiB
PL/PgSQL
134 lines
4.2 KiB
PL/PgSQL
-- HookLab - Schéma initial de la base de données
|
|
-- À exécuter dans Supabase SQL Editor
|
|
|
|
-- Table profiles (extension de auth.users)
|
|
CREATE TABLE public.profiles (
|
|
id UUID REFERENCES auth.users PRIMARY KEY,
|
|
email TEXT UNIQUE NOT NULL,
|
|
full_name TEXT,
|
|
persona TEXT CHECK (persona IN ('jeune', 'parent')),
|
|
stripe_customer_id TEXT UNIQUE,
|
|
subscription_status TEXT DEFAULT 'inactive' CHECK (subscription_status IN ('inactive', 'active', 'cancelled', 'paused')),
|
|
subscription_end_date TIMESTAMPTZ,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
updated_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Table candidatures
|
|
CREATE TABLE public.candidatures (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
email TEXT NOT NULL,
|
|
firstname TEXT NOT NULL,
|
|
phone TEXT NOT NULL,
|
|
persona TEXT NOT NULL,
|
|
age INTEGER NOT NULL,
|
|
experience TEXT NOT NULL,
|
|
time_daily TEXT NOT NULL,
|
|
availability TEXT NOT NULL,
|
|
start_date TEXT NOT NULL,
|
|
motivation TEXT NOT NULL,
|
|
monthly_goal TEXT NOT NULL,
|
|
biggest_fear TEXT NOT NULL,
|
|
tiktok_username TEXT,
|
|
status TEXT DEFAULT 'pending' CHECK (status IN ('pending', 'approved', 'rejected')),
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Table modules formations
|
|
CREATE TABLE public.modules (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
title TEXT NOT NULL,
|
|
description TEXT,
|
|
week_number INTEGER NOT NULL,
|
|
order_index INTEGER NOT NULL,
|
|
content_type TEXT CHECK (content_type IN ('video', 'pdf', 'text', 'quiz')),
|
|
content_url TEXT,
|
|
duration_minutes INTEGER,
|
|
is_published BOOLEAN DEFAULT false,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Table progression élèves
|
|
CREATE TABLE public.user_progress (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
|
|
module_id UUID REFERENCES public.modules(id) ON DELETE CASCADE,
|
|
completed BOOLEAN DEFAULT false,
|
|
completed_at TIMESTAMPTZ,
|
|
notes TEXT,
|
|
created_at TIMESTAMPTZ DEFAULT NOW(),
|
|
UNIQUE(user_id, module_id)
|
|
);
|
|
|
|
-- Table paiements (log Stripe)
|
|
CREATE TABLE public.payments (
|
|
id UUID DEFAULT gen_random_uuid() PRIMARY KEY,
|
|
user_id UUID REFERENCES public.profiles(id) ON DELETE CASCADE,
|
|
stripe_payment_intent_id TEXT UNIQUE NOT NULL,
|
|
amount INTEGER NOT NULL,
|
|
currency TEXT DEFAULT 'eur',
|
|
status TEXT NOT NULL,
|
|
metadata JSONB,
|
|
created_at TIMESTAMPTZ DEFAULT NOW()
|
|
);
|
|
|
|
-- Row Level Security
|
|
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.user_progress ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.modules ENABLE ROW LEVEL SECURITY;
|
|
ALTER TABLE public.payments ENABLE ROW LEVEL SECURITY;
|
|
|
|
-- Policies profiles
|
|
CREATE POLICY "Users can view own profile" ON public.profiles
|
|
FOR SELECT USING (auth.uid() = id);
|
|
|
|
CREATE POLICY "Users can update own profile" ON public.profiles
|
|
FOR UPDATE USING (auth.uid() = id);
|
|
|
|
-- Policies user_progress
|
|
CREATE POLICY "Users can view own progress" ON public.user_progress
|
|
FOR SELECT USING (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can insert own progress" ON public.user_progress
|
|
FOR INSERT WITH CHECK (auth.uid() = user_id);
|
|
|
|
CREATE POLICY "Users can update own progress" ON public.user_progress
|
|
FOR UPDATE USING (auth.uid() = user_id);
|
|
|
|
-- Policies modules (lecture publique pour les modules publiés)
|
|
CREATE POLICY "Anyone can view published modules" ON public.modules
|
|
FOR SELECT USING (is_published = true);
|
|
|
|
-- Policies payments
|
|
CREATE POLICY "Users can view own payments" ON public.payments
|
|
FOR SELECT USING (auth.uid() = user_id);
|
|
|
|
-- Fonction trigger pour updated_at
|
|
CREATE OR REPLACE FUNCTION update_updated_at()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
NEW.updated_at = NOW();
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql;
|
|
|
|
CREATE TRIGGER profiles_updated_at
|
|
BEFORE UPDATE ON public.profiles
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION update_updated_at();
|
|
|
|
-- Fonction pour créer un profil automatiquement à l'inscription
|
|
CREATE OR REPLACE FUNCTION public.handle_new_user()
|
|
RETURNS TRIGGER AS $$
|
|
BEGIN
|
|
INSERT INTO public.profiles (id, email, full_name)
|
|
VALUES (NEW.id, NEW.email, NEW.raw_user_meta_data->>'full_name');
|
|
RETURN NEW;
|
|
END;
|
|
$$ LANGUAGE plpgsql SECURITY DEFINER;
|
|
|
|
CREATE TRIGGER on_auth_user_created
|
|
AFTER INSERT ON auth.users
|
|
FOR EACH ROW
|
|
EXECUTE FUNCTION public.handle_new_user();
|