Database Setup Guide - Video Course Platform

This guide will help you set up the database for the video course platform.

Prerequisites

• Supabase account and project
• Access to Supabase SQL Editor

Step 1: Run the Migration

Option A: Using Supabase Dashboard (Recommended)

1. Go to your Supabase project dashboard
2. Navigate to SQL Editor in the left sidebar
3. Click New Query
4. Copy the contents of supabase/migrations/20251030000000_video_courses.sql
5. Paste into the SQL Editor
6. Click Run (or press Cmd+Enter / Ctrl+Enter)
7. Wait for completion message

Expected output:

✅ Video Course Platform Migration Complete!
📊 Tables Created: 7 new tables + extended nova_tracks
🔐 RLS Policies: Applied to all tables
⚡ Functions: 5 database functions created

Option B: Using Supabase CLI

# Make sure you're in the project root
cd /path/to/project

# Run migration
supabase db push

# Or apply specific migration
supabase migration up

Step 2: Verify Migration

Run this query to verify all tables were created:

SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name IN (
    'course_sections',
    'course_lessons',
    'lesson_progress',
    'lesson_notes',
    'admin_users',
    'course_certificates',
    'quiz_questions'
  )
ORDER BY table_name;

You should see all 7 tables listed.

Step 3: Create Your First Admin User

3.1 Sign Up

First, create a user account:

• Go to your application
• Sign up with your email and password
• Verify your email if required

3.2 Get Your User UUID

Option A: From Supabase Dashboard

1. Go to Authentication > Users
2. Find your user in the list
3. Copy the UUID (looks like: 123e4567-e89b-12d3-a456-426614174000)

Option B: From SQL Query

SELECT id, email FROM auth.users WHERE email = 'your-email@example.com';

3.3 Run the Seed Script

1. Open supabase/seed_admin_user.sql
2. Replace 'YOUR_USER_UUID_HERE' with your actual UUID
3. Run the script in Supabase SQL Editor

Example:

DO $$
DECLARE
  v_user_id UUID := '123e4567-e89b-12d3-a456-426614174000'::UUID;  -- Your actual UUID
  -- rest of script...

Or use the email-based version:

-- Uncomment the email version in the script
v_user_email TEXT := 'your-email@example.com';

3.4 Verify Admin Access

SELECT
  au.role,
  au.permissions,
  u.email,
  au.created_at
FROM admin_users au
JOIN auth.users u ON u.id = au.user_id
WHERE u.email = 'your-email@example.com';

You should see:

• Role: super_admin
• Permissions with all access granted

Step 4: Test the Setup

Test 1: Check RLS Policies

-- This should work (you're authenticated as admin)
SELECT * FROM course_sections;

-- This should work (public can view)
SELECT * FROM course_lessons WHERE is_preview = true;

Test 2: Create Sample Course

-- Insert a test course
INSERT INTO nova_tracks (
  slug,
  code,
  title,
  description,
  instructor_name,
  pricing_tier,
  status
) VALUES (
  'test-course',
  'TEST101',
  'Test Course',
  'This is a test course',
  'Test Instructor',
  'free',
  'open'
) RETURNING *;

Test 3: Create Sample Section

-- Get the course_id from previous query
-- Replace with your actual course_id
INSERT INTO course_sections (
  course_id,
  title,
  description,
  position
) VALUES (
  'YOUR_COURSE_ID_HERE',
  'Introduction',
  'Getting started with the course',
  0
) RETURNING *;

Test 4: Create Sample Lesson

-- Replace course_id and section_id with your actual IDs
-- Using a real YouTube video ID (11 characters)
INSERT INTO course_lessons (
  section_id,
  course_id,
  title,
  description,
  youtube_video_id,
  duration_seconds,
  position,
  is_preview
) VALUES (
  'YOUR_SECTION_ID_HERE',
  'YOUR_COURSE_ID_HERE',
  'Welcome Video',
  'Welcome to the course',
  'dQw4w9WgXcQ',  -- Valid YouTube video ID format
  213,  -- 3:33 minutes
  0,
  true  -- Make it a preview
) RETURNING *;

Test 5: Verify Function Works

-- Test get_course_progress function
SELECT * FROM get_course_progress(
  'YOUR_USER_ID_HERE'::UUID,
  'YOUR_COURSE_ID_HERE'::UUID
);

Expected output:

total_lessons | completed_lessons | in_progress_lessons | ...
-------------+-----------------+---------------------+-----
          1 |               0 |                   0 | ...

Step 5: Clean Up Test Data (Optional)

If you created test data, you can remove it:

-- Delete test course (cascades to sections and lessons)
DELETE FROM nova_tracks WHERE slug = 'test-course';

Troubleshooting

Error: "relation already exists"

If you see this error, some tables already exist. You can either:

1. Drop existing tables (⚠️ WARNING: This deletes all data!)

DROP TABLE IF EXISTS quiz_questions CASCADE;
DROP TABLE IF EXISTS course_certificates CASCADE;
DROP TABLE IF EXISTS admin_users CASCADE;
DROP TABLE IF EXISTS lesson_notes CASCADE;
DROP TABLE IF EXISTS lesson_progress CASCADE;
DROP TABLE IF EXISTS course_lessons CASCADE;
DROP TABLE IF EXISTS course_sections CASCADE;

-- Then re-run the migration

2. Skip creating existing tables - The migration uses IF NOT EXISTS, so it should be safe to re-run

Error: "permission denied for schema public"

You need to run the migration as the Postgres user or with proper permissions. Use the Supabase SQL Editor which has the right permissions.

Error: "function uuid_generate_v4() does not exist"

Enable the UUID extension:

CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

RLS Policies Not Working

Check if RLS is enabled:

SELECT tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename LIKE '%course%';

All should show rowsecurity = true.

Next Steps

After completing database setup:

1. ✅ Get YouTube Data API key (see main plan)
2. ✅ Create YouTube service (src/services/youtube.js)
3. ✅ Start building admin panel components
4. ✅ Test video integration

Database Schema Overview

nova_tracks (courses)
├── course_sections (modules)
│   └── course_lessons (videos)
│       ├── lesson_progress (user watch history)
│       └── lesson_notes (user notes)
├── course_enrollments (who enrolled)
└── course_certificates (issued certs)

admin_users (who can manage courses)

Useful Queries

View all courses

SELECT
  slug,
  title,
  instructor_name,
  total_lessons,
  status
FROM nova_tracks
WHERE total_lessons > 0
ORDER BY created_at DESC;

View course structure

SELECT
  nt.title as course,
  cs.title as section,
  cl.title as lesson,
  cl.duration_seconds,
  cl.is_preview
FROM nova_tracks nt
JOIN course_sections cs ON cs.course_id = nt.id
JOIN course_lessons cl ON cl.section_id = cs.id
WHERE nt.slug = 'YOUR_COURSE_SLUG'
ORDER BY cs.position, cl.position;

View user progress

SELECT
  u.email,
  nt.title as course,
  COUNT(lp.id) FILTER (WHERE lp.completed) as completed_lessons,
  COUNT(cl.id) as total_lessons
FROM auth.users u
JOIN course_enrollments ce ON ce.user_id = u.id
JOIN nova_tracks nt ON nt.id = ce.course_id
LEFT JOIN course_lessons cl ON cl.course_id = nt.id
LEFT JOIN lesson_progress lp ON lp.lesson_id = cl.id AND lp.user_id = u.id
WHERE u.email = 'YOUR_EMAIL'
GROUP BY u.email, nt.title;

Support

If you encounter any issues:

1. Check the troubleshooting section above
2. Verify you're using Supabase SQL Editor (not psql)
3. Make sure you're running as authenticated user
4. Check Supabase logs for detailed error messages

---

Database setup complete! You're ready to move to Phase 2: YouTube Integration.