What is Snappy Idioms Dot Com?

An interactive, language-learning experience. Snappy Idioms Dot Com uses imagination, context, and active practice to build fluency, not just memorisation.

Supabase Migration Verification

Step-by-step verification and completion of your migration

Migration Script Executed Successfully

Your migration ran without errors. Now verify everything is set up correctly.

Step 1: Verification Queries

Run these in Supabase SQL Editor to confirm everything is working

Verify Tables Created

-- Check all tables exist
SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
ORDER BY table_name;

Expected: Should see 30+ tables including users, idioms, user_progress, grammar_topics, etc.

Verify pgcrypto Extension

-- Check extension
SELECT extname, extversion 
FROM pg_extension 
WHERE extname = 'pgcrypto';

Expected: Should return one row with pgcrypto

Verify RLS Enabled

-- Check RLS status
SELECT schemaname, tablename, rowsecurity 
FROM pg_tables 
WHERE schemaname = 'public' 
AND rowsecurity = true
ORDER BY tablename;

Expected: Should see users, idioms, user_progress, grammar_topics with rowsecurity = true

Verify RLS Policies

-- Check policies created
SELECT schemaname, tablename, policyname, cmd 
FROM pg_policies 
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Expected: Should see multiple policies like idioms_public_read, users_can_update_own, etc.

Verify Triggers

-- Check triggers
SELECT tgname, tgrelid::regclass AS table_name
FROM pg_trigger 
WHERE tgname LIKE 'update_%_updated_date'
ORDER BY tgname;

Expected: Should see update triggers for users, idioms, user_progress, grammar_topics

Verify Indexes

-- Check indexes
SELECT indexname, tablename 
FROM pg_indexes 
WHERE schemaname = 'public' 
AND indexname LIKE 'idx_%'
ORDER BY tablename, indexname;

Expected: Should see 15+ indexes including GIN indexes for array columns

Step 2: Complete Setup

Run these to finish your migration setup

Seed Admin User

Create an admin user for testing (CRITICAL - Replace YOUR_AUTH_UID)

⚠️ MUST replace YOUR_AUTH_UID_HERE with your actual Supabase auth user UUID

-- STEP 1: Get your auth.uid() first
-- Login to your app, then in browser console run:
-- fetch('/rest/v1/rpc/auth_uid', {headers: {apikey: 'YOUR_ANON_KEY'}})

-- STEP 2: Replace YOUR_AUTH_UID_HERE with actual UUID
INSERT INTO public.users (
  id, 
  email, 
  full_name, 
  role,
  is_premium,
  pearls_of_wisdom,
  golden_eggs
) VALUES (
  'YOUR_AUTH_UID_HERE'::uuid,  -- Replace with your actual auth.uid()
  'admin@snappyenglish.com',
  'Admin User',
  'admin',
  true,
  1000,
  50
)
ON CONFLICT (id) DO UPDATE SET
  role = 'admin',
  is_premium = true;

Add Remaining Triggers

Add update triggers for all other tables with updated_date

-- Add triggers for remaining tables
DO $$
BEGIN
  -- Vocabulary Words
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_vocabulary_words_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_vocabulary_words_updated_date BEFORE UPDATE ON public.vocabulary_words FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Vocabulary Packs
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_vocabulary_packs_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_vocabulary_packs_updated_date BEFORE UPDATE ON public.vocabulary_packs FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Animations
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_animations_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_animations_updated_date BEFORE UPDATE ON public.animations FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Featured Animation
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_featured_animation_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_featured_animation_updated_date BEFORE UPDATE ON public.featured_animation FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Leaderboard
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_leaderboard_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_leaderboard_updated_date BEFORE UPDATE ON public.leaderboard FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Study Circles
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_study_circles_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_study_circles_updated_date BEFORE UPDATE ON public.study_circles FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Tutoring Sessions
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_tutoring_sessions_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_tutoring_sessions_updated_date BEFORE UPDATE ON public.tutoring_sessions FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Tutors
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_tutors_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_tutors_updated_date BEFORE UPDATE ON public.tutors FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Tutor Availability
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_tutor_availability_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_tutor_availability_updated_date BEFORE UPDATE ON public.tutor_availability FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- NotebookLM Collections
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_notebooklm_collections_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_notebooklm_collections_updated_date BEFORE UPDATE ON public.notebooklm_collections FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- NotebookLM Curriculum
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_notebooklm_curriculum_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_notebooklm_curriculum_updated_date BEFORE UPDATE ON public.notebooklm_curriculum FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Toolkit Resources
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_toolkit_resources_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_toolkit_resources_updated_date BEFORE UPDATE ON public.toolkit_resources FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Educator Stories
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_educator_stories_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_educator_stories_updated_date BEFORE UPDATE ON public.educator_stories FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Settings
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_settings_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_settings_updated_date BEFORE UPDATE ON public.settings FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Referrals
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_referrals_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_referrals_updated_date BEFORE UPDATE ON public.referrals FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Flagged Accounts
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_flagged_accounts_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_flagged_accounts_updated_date BEFORE UPDATE ON public.flagged_accounts FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Shared Mnemonics
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_shared_mnemonics_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_shared_mnemonics_updated_date BEFORE UPDATE ON public.shared_mnemonics FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Classes
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_classes_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_classes_updated_date BEFORE UPDATE ON public.classes FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Teacher Assignments
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_teacher_assignments_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_teacher_assignments_updated_date BEFORE UPDATE ON public.teacher_assignments FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Assignment Submissions
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_assignment_submissions_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_assignment_submissions_updated_date BEFORE UPDATE ON public.assignment_submissions FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Daily Crosswords
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_daily_crosswords_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_daily_crosswords_updated_date BEFORE UPDATE ON public.daily_crosswords FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Crossword Attempts
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_crossword_attempts_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_crossword_attempts_updated_date BEFORE UPDATE ON public.crossword_attempts FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Content Pricing
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_content_pricing_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_content_pricing_updated_date BEFORE UPDATE ON public.content_pricing FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;

  -- Idiom Contributor Awards
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_idiom_contributor_awards_updated_date') THEN
    EXECUTE 'CREATE TRIGGER update_idiom_contributor_awards_updated_date BEFORE UPDATE ON public.idiom_contributor_awards FOR EACH ROW EXECUTE FUNCTION update_updated_date()';
  END IF;
END$$;

Add RLS for Access Tables

Enable RLS and create policies for toolkit_access, ai_session_access, etc.

-- Enable RLS for access tables
ALTER TABLE public.vocabulary_pack_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.ai_session_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.toolkit_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notebooklm_access ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.animations ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.featured_animation ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.vocabulary_words ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.vocabulary_packs ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.study_circles ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.circle_membership ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tutoring_sessions ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tutors ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tutor_availability ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notebooklm_collections ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.notebooklm_curriculum ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.toolkit_resources ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.educator_stories ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.tester_codes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.referrals ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.flagged_accounts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.shared_mnemonics ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.mnemonic_votes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.class_enrollment ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.daily_crosswords ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.crossword_attempts ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.content_pricing ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.idiom_contributor_awards ENABLE ROW LEVEL SECURITY;

-- Vocabulary Pack Access (users read/insert own)
DROP POLICY IF EXISTS "vocab_pack_access_read_own" ON public.vocabulary_pack_access;
CREATE POLICY "vocab_pack_access_read_own" ON public.vocabulary_pack_access
  FOR SELECT USING (user_email = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

DROP POLICY IF EXISTS "vocab_pack_access_insert_own" ON public.vocabulary_pack_access;
CREATE POLICY "vocab_pack_access_insert_own" ON public.vocabulary_pack_access
  FOR INSERT WITH CHECK (user_email = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

-- AI Session Access (users read/update own)
DROP POLICY IF EXISTS "ai_session_access_read_own" ON public.ai_session_access;
CREATE POLICY "ai_session_access_read_own" ON public.ai_session_access
  FOR SELECT USING (user_email = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

DROP POLICY IF EXISTS "ai_session_access_insert_own" ON public.ai_session_access;
CREATE POLICY "ai_session_access_insert_own" ON public.ai_session_access
  FOR INSERT WITH CHECK (user_email = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

DROP POLICY IF EXISTS "ai_session_access_update_own" ON public.ai_session_access;
CREATE POLICY "ai_session_access_update_own" ON public.ai_session_access
  FOR UPDATE USING (user_email = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

-- Toolkit Access (users read/insert own)
DROP POLICY IF EXISTS "toolkit_access_read_own" ON public.toolkit_access;
CREATE POLICY "toolkit_access_read_own" ON public.toolkit_access
  FOR SELECT USING (user_email = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

DROP POLICY IF EXISTS "toolkit_access_insert_own" ON public.toolkit_access;
CREATE POLICY "toolkit_access_insert_own" ON public.toolkit_access
  FOR INSERT WITH CHECK (user_email = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

-- NotebookLM Access (users read/insert own)
DROP POLICY IF EXISTS "notebooklm_access_read_own" ON public.notebooklm_access;
CREATE POLICY "notebooklm_access_read_own" ON public.notebooklm_access
  FOR SELECT USING (user_email = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

DROP POLICY IF EXISTS "notebooklm_access_insert_own" ON public.notebooklm_access;
CREATE POLICY "notebooklm_access_insert_own" ON public.notebooklm_access
  FOR INSERT WITH CHECK (user_email = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

-- Animations (public read, admin write)
DROP POLICY IF EXISTS "animations_read_public" ON public.animations;
CREATE POLICY "animations_read_public" ON public.animations
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "animations_admin_manage" ON public.animations;
CREATE POLICY "animations_admin_manage" ON public.animations
  FOR ALL USING (EXISTS (SELECT 1 FROM public.users WHERE id::text = auth.uid()::text AND role = 'admin'));

-- Featured Animation (public read, admin write)
DROP POLICY IF EXISTS "featured_animation_read_public" ON public.featured_animation;
CREATE POLICY "featured_animation_read_public" ON public.featured_animation
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "featured_animation_admin_manage" ON public.featured_animation;
CREATE POLICY "featured_animation_admin_manage" ON public.featured_animation
  FOR ALL USING (EXISTS (SELECT 1 FROM public.users WHERE id::text = auth.uid()::text AND role = 'admin'));

-- Vocabulary Words (public read, users can insert, admin can manage)
DROP POLICY IF EXISTS "vocabulary_words_read_public" ON public.vocabulary_words;
CREATE POLICY "vocabulary_words_read_public" ON public.vocabulary_words
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "vocabulary_words_insert_authenticated" ON public.vocabulary_words;
CREATE POLICY "vocabulary_words_insert_authenticated" ON public.vocabulary_words
  FOR INSERT WITH CHECK (auth.uid() IS NOT NULL);

DROP POLICY IF EXISTS "vocabulary_words_admin_manage" ON public.vocabulary_words;
CREATE POLICY "vocabulary_words_admin_manage" ON public.vocabulary_words
  FOR ALL USING (EXISTS (SELECT 1 FROM public.users WHERE id::text = auth.uid()::text AND role = 'admin'));

-- Vocabulary Packs (public read, admin write)
DROP POLICY IF EXISTS "vocabulary_packs_read_public" ON public.vocabulary_packs;
CREATE POLICY "vocabulary_packs_read_public" ON public.vocabulary_packs
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "vocabulary_packs_admin_manage" ON public.vocabulary_packs;
CREATE POLICY "vocabulary_packs_admin_manage" ON public.vocabulary_packs
  FOR ALL USING (EXISTS (SELECT 1 FROM public.users WHERE id::text = auth.uid()::text AND role = 'admin'));

-- Crosswords (public read, admin write)
DROP POLICY IF EXISTS "crosswords_read_public" ON public.daily_crosswords;
CREATE POLICY "crosswords_read_public" ON public.daily_crosswords
  FOR SELECT USING (true);

DROP POLICY IF EXISTS "crosswords_admin_manage" ON public.daily_crosswords;
CREATE POLICY "crosswords_admin_manage" ON public.daily_crosswords
  FOR ALL USING (EXISTS (SELECT 1 FROM public.users WHERE id::text = auth.uid()::text AND role = 'admin'));

-- Crossword Attempts (users manage own)
DROP POLICY IF EXISTS "crossword_attempts_read_own" ON public.crossword_attempts;
CREATE POLICY "crossword_attempts_read_own" ON public.crossword_attempts
  FOR SELECT USING (created_by = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

DROP POLICY IF EXISTS "crossword_attempts_insert_own" ON public.crossword_attempts;
CREATE POLICY "crossword_attempts_insert_own" ON public.crossword_attempts
  FOR INSERT WITH CHECK (created_by = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

DROP POLICY IF EXISTS "crossword_attempts_update_own" ON public.crossword_attempts;
CREATE POLICY "crossword_attempts_update_own" ON public.crossword_attempts
  FOR UPDATE USING (created_by = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

Test RLS Policies

Verify RLS is working correctly

⚠️ Run these after seeding admin user and logging in

-- Test 1: Public read (should work without auth)
SELECT COUNT(*) FROM public.idioms WHERE is_approved = true;
SELECT COUNT(*) FROM public.settings;
SELECT COUNT(*) FROM public.grammar_topics;

-- Test 2: User-scoped read (will return 0 until you auth and have data)
SELECT COUNT(*) FROM public.user_progress;

-- Test 3: Admin check (will work only with admin role)
SELECT COUNT(*) FROM public.idioms WHERE is_approved = false;

Migration Complete Checklist

✓ Tables created (30+)

✓ pgcrypto extension enabled

✓ RLS enabled on core tables

☐ Seed admin user

☐ Add remaining triggers

☐ Add RLS for access tables

☐ Test RLS policies

Next: Update your frontend to use Supabase client instead of Base44

Tip: Start with read-only operations (SELECT queries) first

Remember: Service role key bypasses RLS (use for admin operations only)