CoffeeHouse Supabase Auth Studio #20

-- 1. Create Public User Profiles Table (Linked to auth.users)
CREATE TABLE IF NOT EXISTS public.profiles (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    email TEXT NOT NULL,
    full_name TEXT DEFAULT '',
    role TEXT NOT NULL DEFAULT 'admin' CHECK (role IN ('admin', 'editor', 'viewer')),
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- 2. Row Level Security
ALTER TABLE public.profiles ENABLE ROW LEVEL SECURITY;

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

-- 3. Automatic Trigger to Sync auth.users with public.profiles
CREATE OR REPLACE FUNCTION public.handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO public.profiles (id, email, full_name, role)
    VALUES (
        NEW.id,
        NEW.email,
        COALESCE(NEW.raw_user_meta_data->>'full_name', split_part(NEW.email, '@', 1)),
        COALESCE(NEW.raw_user_meta_data->>'role', 'admin')
    );
    RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE OR REPLACE TRIGGER on_auth_user_created
    AFTER INSERT ON auth.users
    FOR EACH ROW EXECUTE FUNCTION public.handle_new_user();

-- 4. Protected Newsletter Campaigns
CREATE TABLE IF NOT EXISTS public.newsletter_campaigns (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    title TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'Draft',
    created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

ALTER TABLE public.newsletter_campaigns ENABLE ROW LEVEL SECURITY;

CREATE POLICY "Authenticated users can manage campaigns"
    ON public.newsletter_campaigns FOR ALL
    TO authenticated
    USING (true)
    WITH CHECK (true);

Build a production-grade CoffeeHouse Admin Portal in Bolt.new with Vite, React, TypeScript, and Supabase JS v2.

1. Visual Styling & Branding:
   - Match the CoffeeHouse warm parchment aesthetic: #faf6ee background, #78350f amber text and logo, centered card with subtle shadow.
   - Admin Login title: "Admin Login" with subtitle "Sign in to access the newsletter dashboard".
   - Inputs for Email and Password with toggle visibility eye icon.
   - Warm brown button "Sign In" with disabled loading spinner and state "Signing In...".

2. Supabase Authentication (The Clue):
   - Configure @supabase/supabase-js reading VITE_SUPABASE_URL and VITE_SUPABASE_ANON_KEY.
   - Use supabase.auth.signInWithPassword({ email, password }).
   - Handle unconfirmed emails gracefully: if error.message includes "Email not confirmed", show banner "Please confirm your email address before logging in".
   - Listen to supabase.auth.onAuthStateChange for session transitions.

3. Protected Destination Redirection:
   - Upon successful login (SIGNED_IN event), redirect user to "/admin/newsletter".
   - Wrap "/admin/newsletter" with a ProtectedRoute component; unauthenticated visitors are redirected to "/login".
   - Protected Dashboard includes:
     * KPI row: Active Subscribers (1,420), Open Rate (68.4%), Campaigns (18).
     * Quick campaign composer with subject input and send button.
     * Table of verified subscribers from PostgreSQL table `coffee_subscribers`.
     * User avatar with email and "Sign Out" button calling supabase.auth.signOut().