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.

Staged Migration Guide

Run Supabase migration in safe, verifiable stages

How to Use This Guide

Open Supabase SQL Editor (Dashboard → SQL Editor → New Query)

Copy/paste each stage SQL and click "Run"

After each stage, run the verification query to confirm success

If verification passes, proceed to next stage

If any stage fails, fix the issue before continuing

Extensions & Functions

Enable pgcrypto and create trigger function

SQL to Run:

-- Stage 1: Extensions & Functions
-- Safe to run multiple times (idempotent)

CREATE EXTENSION IF NOT EXISTS "pgcrypto";

-- Updated_date trigger function
CREATE OR REPLACE FUNCTION update_updated_date()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_date = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Verification Query:

-- Verify Stage 1
SELECT * FROM pg_extension WHERE extname = 'pgcrypto';
SELECT proname FROM pg_proc WHERE proname = 'update_updated_date';

Expected: Should see pgcrypto extension and update_updated_date function

Core Tables (Part 1)

Create users, idioms, vocabulary, grammar tables

SQL to Run:

-- Stage 2: Core Tables (Part 1)
-- Run once, idempotent with IF NOT EXISTS

CREATE TABLE IF NOT EXISTS public.users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  email TEXT UNIQUE NOT NULL,
  full_name TEXT,
  role TEXT DEFAULT 'user' CHECK (role IN ('user', 'admin')),
  profession TEXT,
  native_language TEXT,
  country_of_domicile TEXT,
  thinking_style TEXT DEFAULT 'undetermined',
  is_premium BOOLEAN DEFAULT false,
  is_educator BOOLEAN DEFAULT false,
  pearls_of_wisdom INTEGER DEFAULT 0,
  golden_eggs INTEGER DEFAULT 0,
  referral_code TEXT,
  profile_completed BOOLEAN DEFAULT false,
  quiz_completed BOOLEAN DEFAULT false,
  onboarding_step INTEGER DEFAULT 0,
  stripe_customer_id TEXT,
  subscription_id TEXT,
  subscription_status TEXT,
  last_active TIMESTAMPTZ DEFAULT NOW()
);

CREATE UNIQUE INDEX IF NOT EXISTS idx_users_referral_code 
ON public.users(referral_code) WHERE referral_code IS NOT NULL;

CREATE TABLE IF NOT EXISTS public.idioms (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  phrase TEXT NOT NULL,
  meaning TEXT NOT NULL,
  origin TEXT,
  example_sentence TEXT NOT NULL,
  category TEXT NOT NULL,
  difficulty TEXT NOT NULL,
  thinking_style TEXT DEFAULT 'universal',
  thinking_style_tags TEXT[],
  image_url TEXT,
  video_url TEXT,
  animation_url TEXT,
  pronunciation_tip TEXT,
  usage_note TEXT,
  mnemonic_tip TEXT,
  visual_cue TEXT,
  qr_code_url TEXT,
  is_premium BOOLEAN DEFAULT false,
  is_approved BOOLEAN DEFAULT false,
  contributor_name TEXT
);

CREATE TABLE IF NOT EXISTS public.user_progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT NOT NULL,
  idiom_id UUID NOT NULL REFERENCES public.idioms(id) ON DELETE CASCADE,
  status TEXT DEFAULT 'new',
  times_practiced INTEGER DEFAULT 0,
  times_correct INTEGER DEFAULT 0,
  last_practiced TIMESTAMPTZ,
  is_favourite BOOLEAN DEFAULT false,
  custom_sentence TEXT,
  notes TEXT,
  custom_mnemonic TEXT,
  mnemonic_type TEXT
);

CREATE TABLE IF NOT EXISTS public.grammar_topics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  title TEXT NOT NULL,
  category TEXT,
  explanation TEXT NOT NULL,
  correct_examples TEXT[],
  common_mistakes TEXT[],
  pdf_url TEXT,
  is_premium_pdf BOOLEAN DEFAULT false
);

CREATE TABLE IF NOT EXISTS public.grammar_access (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  grammar_topic_id UUID REFERENCES public.grammar_topics(id) ON DELETE CASCADE,
  user_email TEXT NOT NULL,
  unlocked_date TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS public.vocabulary_words (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  word TEXT NOT NULL,
  part_of_speech TEXT NOT NULL,
  definition TEXT NOT NULL,
  example_sentence TEXT NOT NULL,
  phonetic_spelling TEXT,
  topic TEXT,
  level TEXT DEFAULT 'A2',
  syllable_count INTEGER,
  syllable_breaks TEXT,
  stress_pattern TEXT,
  stress_position INTEGER,
  morphology_data TEXT
);

CREATE TABLE IF NOT EXISTS public.vocabulary_packs (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  pack_name TEXT NOT NULL,
  description TEXT,
  word_ids TEXT[],
  pearl_cost INTEGER DEFAULT 0,
  egg_cost INTEGER DEFAULT 0,
  is_premium_only BOOLEAN DEFAULT false
);

Verification Query:

-- Verify Stage 2
SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public' 
AND table_name IN ('users', 'idioms', 'user_progress', 'grammar_topics', 'vocabulary_words');

Expected: Should see 5 tables created

Education Tables

Classes, assignments, tutoring, study circles

SQL to Run:

-- Stage 3: Education Tables

CREATE TABLE IF NOT EXISTS public.classes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  class_name TEXT NOT NULL,
  teacher_email TEXT NOT NULL,
  grade_level TEXT,
  subject TEXT DEFAULT 'English',
  school_name TEXT,
  class_code TEXT UNIQUE NOT NULL,
  student_count INTEGER DEFAULT 0,
  is_active BOOLEAN DEFAULT true
);

CREATE TABLE IF NOT EXISTS public.class_enrollment (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  class_id UUID REFERENCES public.classes(id) ON DELETE CASCADE,
  student_email TEXT NOT NULL,
  enrollment_date TIMESTAMPTZ DEFAULT NOW(),
  status TEXT DEFAULT 'active'
);

CREATE TABLE IF NOT EXISTS public.teacher_assignments (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  class_id UUID REFERENCES public.classes(id) ON DELETE CASCADE,
  teacher_email TEXT NOT NULL,
  due_date TIMESTAMPTZ NOT NULL,
  assignment_type TEXT DEFAULT 'custom',
  target_idioms TEXT[],
  min_score_required NUMERIC,
  points_possible INTEGER DEFAULT 100,
  pearls_reward INTEGER DEFAULT 10,
  status TEXT DEFAULT 'draft',
  auto_grade BOOLEAN DEFAULT true,
  allow_late_submission BOOLEAN DEFAULT false,
  late_penalty_percent INTEGER DEFAULT 10,
  rubric TEXT,
  total_submissions INTEGER DEFAULT 0,
  avg_score NUMERIC DEFAULT 0
);

CREATE TABLE IF NOT EXISTS public.assignment_submissions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT NOT NULL,
  assignment_id UUID REFERENCES public.teacher_assignments(id) ON DELETE CASCADE,
  student_email TEXT NOT NULL,
  submission_text TEXT,
  submission_url TEXT,
  score NUMERIC,
  feedback TEXT,
  submitted_at TIMESTAMPTZ DEFAULT NOW(),
  graded_at TIMESTAMPTZ,
  status TEXT DEFAULT 'submitted'
);

CREATE TABLE IF NOT EXISTS public.study_circles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  name TEXT NOT NULL,
  description TEXT,
  creator_email TEXT NOT NULL,
  focus_area TEXT,
  member_count INTEGER DEFAULT 0,
  max_members INTEGER DEFAULT 10,
  is_active BOOLEAN DEFAULT true
);

CREATE TABLE IF NOT EXISTS public.circle_membership (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  circle_id UUID REFERENCES public.study_circles(id) ON DELETE CASCADE,
  user_email TEXT NOT NULL,
  joined_date TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS public.tutors (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  full_name TEXT NOT NULL,
  email TEXT NOT NULL,
  bio TEXT,
  qualifications TEXT,
  teaching_experience_years INTEGER,
  specializations TEXT[],
  languages_spoken TEXT[],
  hourly_rate_gbp NUMERIC NOT NULL,
  character_name TEXT,
  status TEXT DEFAULT 'pending',
  timezone TEXT,
  total_sessions_completed INTEGER DEFAULT 0,
  average_rating NUMERIC DEFAULT 0
);

CREATE TABLE IF NOT EXISTS public.tutoring_sessions (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  student_email TEXT NOT NULL,
  tutor_id UUID,
  session_date TIMESTAMPTZ NOT NULL,
  duration_minutes INTEGER NOT NULL,
  cost_golden_eggs INTEGER NOT NULL,
  status TEXT DEFAULT 'scheduled'
);

Verification Query:

-- Verify Stage 3
SELECT COUNT(*) FROM information_schema.tables 
WHERE table_schema = 'public' 
AND table_name IN ('classes', 'teacher_assignments', 'tutors', 'study_circles');

Expected: Should return count = 4

Supporting Tables

Settings, animations, crosswords, referrals

SQL to Run:

-- Stage 4: Supporting Tables

CREATE TABLE IF NOT EXISTS public.settings (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  key TEXT UNIQUE NOT NULL,
  value TEXT NOT NULL,
  description TEXT
);

CREATE TABLE IF NOT EXISTS public.animations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  title TEXT NOT NULL,
  idiom_id UUID REFERENCES public.idioms(id) ON DELETE CASCADE,
  video_url TEXT NOT NULL,
  thumbnail_url TEXT,
  is_featured BOOLEAN DEFAULT false
);

CREATE TABLE IF NOT EXISTS public.daily_crosswords (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  puzzle_date DATE UNIQUE NOT NULL,
  grid_data TEXT NOT NULL,
  clues_data TEXT NOT NULL,
  difficulty TEXT,
  pearl_reward INTEGER DEFAULT 10
);

CREATE TABLE IF NOT EXISTS public.referrals (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  referrer_email TEXT NOT NULL,
  referred_email TEXT,
  referral_code TEXT NOT NULL,
  status TEXT DEFAULT 'pending',
  signup_date TIMESTAMPTZ
);

CREATE TABLE IF NOT EXISTS public.leaderboard (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  user_email TEXT NOT NULL,
  user_name TEXT NOT NULL,
  idioms_mastered INTEGER DEFAULT 0,
  week_ending DATE NOT NULL
);

CREATE TABLE IF NOT EXISTS public.shared_mnemonics (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  created_date TIMESTAMPTZ DEFAULT NOW(),
  updated_date TIMESTAMPTZ DEFAULT NOW(),
  created_by TEXT,
  idiom_id UUID REFERENCES public.idioms(id) ON DELETE CASCADE,
  mnemonic_text TEXT NOT NULL,
  upvotes INTEGER DEFAULT 0,
  is_approved BOOLEAN DEFAULT false
);

Verification Query:

-- Verify Stage 4
SELECT table_name FROM information_schema.tables 
WHERE table_schema = 'public' 
AND table_name IN ('settings', 'animations', 'referrals', 'leaderboard');

Expected: Should see 4 tables

Indexes

B-tree and GIN indexes for performance

SQL to Run:

-- Stage 5: Performance Indexes

-- B-tree indexes
CREATE INDEX IF NOT EXISTS idx_user_progress_user ON public.user_progress(created_by);
CREATE INDEX IF NOT EXISTS idx_user_progress_idiom ON public.user_progress(idiom_id);
CREATE INDEX IF NOT EXISTS idx_idioms_category ON public.idioms(category);
CREATE INDEX IF NOT EXISTS idx_idioms_difficulty ON public.idioms(difficulty);
CREATE INDEX IF NOT EXISTS idx_idioms_approved ON public.idioms(is_approved);
CREATE INDEX IF NOT EXISTS idx_grammar_access_user ON public.grammar_access(user_email);
CREATE INDEX IF NOT EXISTS idx_leaderboard_week ON public.leaderboard(week_ending);

-- GIN indexes for array columns
CREATE INDEX IF NOT EXISTS idx_idioms_thinking_style_tags_gin 
ON public.idioms USING GIN (thinking_style_tags);

CREATE INDEX IF NOT EXISTS idx_vocabulary_packs_word_ids_gin 
ON public.vocabulary_packs USING GIN (word_ids);

CREATE INDEX IF NOT EXISTS idx_tutors_specializations_gin 
ON public.tutors USING GIN (specializations);

Verification Query:

-- Verify Stage 5
SELECT indexname FROM pg_indexes 
WHERE schemaname = 'public' 
AND indexname LIKE 'idx_%'
ORDER BY indexname;

Expected: Should see 10+ indexes created

Triggers

Auto-update updated_date on all tables

SQL to Run:

-- Stage 6: Update Triggers
-- Idempotent: CREATE TRIGGER will skip if exists

DO $$ 
BEGIN
  -- Users
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_users_updated_date') THEN
    CREATE TRIGGER update_users_updated_date BEFORE UPDATE ON public.users
    FOR EACH ROW EXECUTE FUNCTION update_updated_date();
  END IF;

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

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

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

  -- Vocabulary
  IF NOT EXISTS (SELECT 1 FROM pg_trigger WHERE tgname = 'update_vocabulary_words_updated_date') THEN
    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
    CREATE TRIGGER update_vocabulary_packs_updated_date BEFORE UPDATE ON public.vocabulary_packs
    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
    CREATE TRIGGER update_classes_updated_date BEFORE UPDATE ON public.classes
    FOR EACH ROW EXECUTE FUNCTION update_updated_date();
  END IF;

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

Verification Query:

-- Verify Stage 6
SELECT tgname FROM pg_trigger 
WHERE tgname LIKE 'update_%_updated_date'
ORDER BY tgname;

Expected: Should see 8+ triggers

RLS Policies

Row Level Security for all tables

SQL to Run:

-- Stage 7: Enable RLS and Create Policies

-- Enable RLS
ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.idioms ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.user_progress ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.grammar_topics ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.settings ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.classes ENABLE ROW LEVEL SECURITY;
ALTER TABLE public.teacher_assignments ENABLE ROW LEVEL SECURITY;

-- Users policies
DROP POLICY IF EXISTS "Users can read own data" ON public.users;
CREATE POLICY "Users can read own data" ON public.users
  FOR SELECT USING (
    id::text = auth.uid()::text OR 
    EXISTS (SELECT 1 FROM public.users WHERE id::text = auth.uid()::text AND role = 'admin')
  );

DROP POLICY IF EXISTS "Users can insert own data" ON public.users;
CREATE POLICY "Users can insert own data" ON public.users
  FOR INSERT WITH CHECK (id::text = auth.uid()::text);

DROP POLICY IF EXISTS "Users can update own data" ON public.users;
CREATE POLICY "Users can update own data" ON public.users
  FOR UPDATE USING (id::text = auth.uid()::text);

-- Idioms policies
DROP POLICY IF EXISTS "Anyone can read approved idioms" ON public.idioms;
CREATE POLICY "Anyone can read approved idioms" ON public.idioms
  FOR SELECT USING (
    is_approved = true OR 
    EXISTS (SELECT 1 FROM public.users WHERE id::text = auth.uid()::text AND role = 'admin')
  );

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

-- User Progress policies
DROP POLICY IF EXISTS "Users can read own progress" ON public.user_progress;
CREATE POLICY "Users can read own progress" ON public.user_progress
  FOR SELECT USING (created_by = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

DROP POLICY IF EXISTS "Users can insert own progress" ON public.user_progress;
CREATE POLICY "Users can insert own progress" ON public.user_progress
  FOR INSERT WITH CHECK (created_by = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

DROP POLICY IF EXISTS "Users can update own progress" ON public.user_progress;
CREATE POLICY "Users can update own progress" ON public.user_progress
  FOR UPDATE USING (created_by = (SELECT email FROM public.users WHERE id::text = auth.uid()::text));

-- Grammar policies (public read, admin write)
DROP POLICY IF EXISTS "Anyone can read grammar" ON public.grammar_topics;
CREATE POLICY "Anyone can read grammar" ON public.grammar_topics
  FOR SELECT USING (true);

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

-- Settings policies (public read, admin write)
DROP POLICY IF EXISTS "Anyone can read settings" ON public.settings;
CREATE POLICY "Anyone can read settings" ON public.settings
  FOR SELECT USING (true);

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

Verification Query:

-- Verify Stage 7
SELECT schemaname, tablename, policyname 
FROM pg_policies 
WHERE schemaname = 'public'
ORDER BY tablename, policyname;

Expected: Should see multiple policies per table

Final Verification Tests

Test 1: Seed Admin User

-- Create an admin user for testing (replace with your auth.uid())
INSERT INTO public.users (id, email, full_name, role)
VALUES (
  'YOUR_AUTH_UID_HERE'::uuid,
  'admin@snappyenglish.com',
  'Test Admin',
  'admin'
);

Test 2: Verify RLS Works

-- Should return rows (public read)
SELECT COUNT(*) FROM public.settings;

-- Should fail or return 0 (user-scoped)
SELECT COUNT(*) FROM public.user_progress;

Test 3: Count All Tables

SELECT COUNT(*) as total_tables
FROM information_schema.tables
WHERE table_schema = 'public';
-- Expected: 30-40 tables

⚠️ Important: Run in staging/dev environment first

⚠️ Backup: Create snapshot before running in production

⚠️ Service Role: Some operations require service_role key