supabase-setup.sql

-- ==========================================
-- SUPABASE DATABASE SETUP SCRIPT
-- ==========================================
-- Run this script in your Supabase SQL Editor to create the necessary tables, 
-- functions, and Row Level Security (RLS) policies for the New Bridges ABA website.

-- 1. Create the Tables
-- ------------------------------------------

-- Table: newbridges_admin
-- Stores the email addresses of users authorized to edit content.
CREATE TABLE public.newbridges_admin (
    email TEXT PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL
);

-- Table: newbridges_content
-- Stores the blog posts and dynamic content for the site.
CREATE TABLE public.newbridges_content (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    title TEXT NOT NULL,
    slug TEXT NOT NULL UNIQUE,
    category TEXT,
    emoji TEXT,
    excerpt TEXT,
    content TEXT
);

-- Table: newbridges_tracking
-- Stores the analytics data (page views, clicks, sessions) collected from the site.
CREATE TABLE public.newbridges_tracking (
    id BIGINT GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT timezone('utc'::text, now()) NOT NULL,
    event_type TEXT NOT NULL,
    page_name TEXT,
    page_url TEXT,
    ip_address TEXT,
    session_id TEXT,
    meta_browser TEXT,
    meta_os TEXT,
    meta_screen TEXT,
    meta_language TEXT,
    meta_referrer TEXT,
    event_data JSONB
);

-- 2. Enable Row Level Security (RLS)
-- ------------------------------------------
-- This ensures that access to these tables is restricted by the policies defined below.
ALTER TABLE public.newbridges_admin ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.newbridges_content ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.newbridges_tracking ENABLE ROW LEVEL SECURITY;

-- 3. Create Authorization Helper Function
-- ------------------------------------------
-- This function checks if the currently authenticated user's email exists in the newbridges_admin table.
CREATE OR REPLACE FUNCTION public.is_admin()
RETURNS BOOLEAN AS 
  SELECT EXISTS (
    SELECT 1 FROM public.newbridges_admin 
    WHERE email = auth.jwt() ->> 'email'
  );
 LANGUAGE sql SECURITY DEFINER;

-- 4. Define RLS Policies
-- ------------------------------------------

-- Tracking Table Policies
-- Anyone (public, unauthenticated users) can log new analytics data.
CREATE POLICY "Allow public insert to tracking" ON public.newbridges_tracking
    FOR INSERT TO public WITH CHECK (true);

-- Only authenticated admins can read/view analytics data.
CREATE POLICY "Allow admins to read tracking" ON public.newbridges_tracking
    FOR SELECT TO authenticated USING (public.is_admin());

-- Content Table Policies
-- Anyone (public, unauthenticated users) can read published blog posts.
CREATE POLICY "Allow public read access to content" ON public.newbridges_content
    FOR SELECT TO public USING (true);

-- Only authenticated admins can create new blog posts.
CREATE POLICY "Allow admins to insert content" ON public.newbridges_content
    FOR INSERT TO authenticated WITH CHECK (public.is_admin());

-- Only authenticated admins can update existing blog posts.
CREATE POLICY "Allow admins to update content" ON public.newbridges_content
    FOR UPDATE TO authenticated USING (public.is_admin());

-- Only authenticated admins can delete blog posts.
CREATE POLICY "Allow admins to delete content" ON public.newbridges_content
    FOR DELETE TO authenticated USING (public.is_admin());

-- Admin Table Policies
-- Only authenticated users can see the list of admin emails (this allows the is_admin function to work for them).
CREATE POLICY "Allow authenticated users to read admins" ON public.newbridges_admin
    FOR SELECT TO authenticated USING (true);


-- 5. Insert Seed Data (Optional)
-- ------------------------------------------
-- Adds an initial placeholder blog post to verify the content table is working.
INSERT INTO public.newbridges_content (title, slug, category, emoji, excerpt, content)
VALUES (
    'Understanding Evidence-Based ABA Therapy',
    'understanding-evidence-based-aba-therapy',
    'Article',
    '📝',
    'Families raising children on the autism spectrum often look for care that feels steady, respectful, and truly helpful in daily life. Applied Behavior Analysis (ABA) has emerged as one of the most effective, evidence-based approaches for supporting these children.',
    '<p class="mb-6">Families raising children on the autism spectrum often look for care that feels steady, respectful, and truly helpful in daily life. Applied Behavior Analysis (ABA) has emerged as one of the most effective, evidence-based approaches for supporting these children.</p><h2 class="text-2xl font-black text-gray-900 mt-10 mb-4">What Makes ABA Effective?</h2><p class="mb-6">At its core, ABA involves breaking down complex skills into smaller, manageable steps. This structured methodology allows children to experience small victories that build confidence and pave the way for broader learning. It’s not just about managing behavior; it’s about opening doors to communication and independence.</p><h2 class="text-2xl font-black text-gray-900 mt-10 mb-4">Consistency is Key</h2><p class="mb-6">A major pillar of successful ABA therapy is consistency across different environments. When a child receives the same level of care and utilizes the same strategies at school, at home, and in clinical settings, the lessons become deeply ingrained. The familiarity reduces anxiety and creates a safe space for growth.</p><p class="mb-6">Whether you''re in New Jersey or Ohio, finding a provider who understands the nuances of state-specific educational mandates and insurance requirements is crucial. Professional ABA therapy isn’t just a service; it''s a partnership with families designed to foster long-term stability and success.</p>'
);