Open Source Bible School LMS — we need your help (React, FastAPI, Supabase)

Solution The architecture decouples presentation, business logic, and data persistence while leveraging Supabase as a unified backend-as-a-service. React (Vite + TypeScript) serves as a PWA with service-worker-driven offline caching. FastAPI handles lightweight orchestration, webhook processing, and complex aggregations that exceed Supabase's SQL capabilities. Supabase provides PostgreSQL, Auth, Storage, Realtime, and Edge Functions.

Database Schema (Supabase SQL)

CREATE TABLE courses (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT NOT NULL,
  description TEXT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE lessons (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  course_id UUID REFERENCES courses(id) ON DELETE CASCADE,
  title TEXT NOT NULL,
  content_url TEXT,
  duration_minutes INT,
  created_at TIMESTAMPTZ DEFAULT now()
);

CREATE TABLE student_progress (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  student_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  lesson_id UUID REFERENCES lessons(id) ON DELETE CASCADE,
  completed BOOLEAN DEFAULT FALSE,
  last_accessed TIMESTAMPTZ DEFAULT now(),
  UNIQUE(student_id, lesson_id)
);

-- Enable RLS
ALTER TABLE courses ENABLE ROW LEVEL SECURITY;
ALTER TABLE lessons ENABLE ROW LEVEL SECURITY;
ALTER TABLE student_progress ENABLE ROW LEVEL SECURITY;

-- Policy: Students can read courses/lessons, write only their own progress
CREATE POLICY "Public read access for courses" ON courses FOR SELECT USING (true);
CREATE POLICY "Public read access for lessons" ON lessons FOR SELECT USING (true);
CREATE POLICY "Students manage own progress" ON student_progress 
  FOR ALL USING (auth.uid() = student_id);

FastAPI Endpoint (Progress Aggregation)

from fastapi import FastAPI, Depends, HTTPException
from supabase import create_client, Client
import os

app = FastAPI()
supabase: Client = create_client(os.getenv("SUPABASE_URL"), os.getenv("SUPABASE_SERVICE_KEY"))

@app.get("/api/courses/{course_id}/progress")
async def get_course_progress(course_id: str, student_id: str):
    try:
        response = supabase.table("lessons").select(
            "id, title, student_progress!inner(completed)"
        ).eq("course_id", course_id).eq("student_progress.student_id", student_id).execute()
        
        total = len(response.data)
        completed = sum(1 for lesson in response.data if lesson["student_progress"]["completed"])
        return {"course_id": course_id, "total": total, "completed": completed, "percentage": (completed/total)*100 if total > 0 else 0}
    except Exception as e:
        raise HTTPException(status_code=500, detail=str(e))

React Offline Sync Hook

import { useState, useEffect } from 'react';
import { supabase } from './supabaseClient';

export function useOfflineSync(studentId: string) {
  const [queue, setQueue] = useState<any[]>([]);

  useEffect(() => {
    const syncQueue = async () => {
      if (navigator.onLine && queue.length > 0) {
        const { error } = await supabase
          .from('student_progress')
          .upsert(queue, { onConflict: 'student_id,lesson_id' });
        if (!error) setQueue([]);
      }
    };
    window.addEventListener('online', syncQueue);
    return () => window.removeEventListener('online', syncQueue);
  }, [queue, studentId]);

  const queueProgress = (lessonId: string, completed: boolean) => {
    setQueue(prev => [...prev, { student_id: studentId, lesson_id: lessonId, completed }]);
  };

  return { queueProgress, isOnline: navigator.onLine };
}

Pitfall Guide

1. Ignoring Supabase Row-Level Security (RLS): Leaving tables without RLS exposes student data and allows unauthorized progress manipulation. Best Practice: Enable RLS on all tables, enforce policies using auth.uid() and JWT claims, and test policies using Supabase CLI's supabase db test.
2. N+1 Query Patterns in FastAPI/Supabase: Fetching lessons and progress in separate loops causes latency spikes under concurrent volunteer access. Best Practice: Use Supabase's .select('*, student_progress!inner(...)') joins or batch endpoints. Avoid ORM-level lazy loading; prefer explicit SQL joins or Supabase query builder chaining.
3. State Management Bloat in React: Overusing Redux/Zustand for transient UI state increases bundle size and complicates debugging. Best Practice: Use React Query/TanStack Query for server state caching, local useState/useReducer for UI transitions, and Context only for global auth/theme configuration.
4. CORS & Preflight Failures: FastAPI misconfigured with allow_origins=["*"] breaks authentication cookies and exposes endpoints to CSRF. Best Practice: Use CORSMiddleware with dynamic origin validation, restrict methods/headers, and never allow wildcard origins in production. Validate Origin headers against a whitelist.
5. Neglecting Offline-First Architecture: LMS fails in low-connectivity regions common in field training programs. Best Practice: Implement IndexedDB + Service Worker sync queue, fallback to cached lessons, and use optimistic UI updates with conflict resolution strategies (last-write-wins or manual merge).
6. Over-Provisioning Supabase Free Tier: Hitting row/connection limits causes silent failures during peak training sessions. Best Practice: Implement connection pooling via Supabase's built-in PgBouncer, archive inactive courses quarterly, use materialized views for heavy aggregations, and upgrade to Supabase Pro when concurrent connections exceed 100.
7. Hardcoded Environment Variables: Breaks CI/CD pipelines and volunteer deployments across environments. Best Practice: Use .env.example templates, inject runtime configuration via Docker/CI variables, and leverage Supabase CLI for local development parity. Never commit secrets to version control.

Deliverables

• Architecture Blueprint: Decoupled PWA frontend (React + Vite + Service Worker) → lightweight orchestration layer (FastAPI + async workers) → unified data plane (Supabase PostgreSQL + Realtime + Storage + Edge Functions). Includes data flow diagrams for offline sync, RLS enforcement, and progress aggregation.
• Deployment & Security Checklist:
  • Enable RLS on all Supabase tables and validate with supabase db test
  • Configure FastAPI CORS with strict origin whitelisting
  • Implement React Query cache invalidation strategies
  • Set up Supabase PgBouncer and connection pooling
  • Configure Service Worker for offline lesson caching
  • Add automated backups and point-in-time recovery in Supabase
  • Run OWASP ZAP scan on FastAPI endpoints before production
• Configuration Templates:
  • docker-compose.yml (FastAPI + PostgreSQL local dev)
  • supabase/config.toml (Auth providers, storage policies, realtime settings)
  • fastapi/.env.example (Database URLs, JWT secrets, CORS origins)
  • vite.config.ts (PWA manifest, service worker injection, proxy rules)
  • supabase/migrations/001_initial_schema.sql (RLS policies, indexes, triggers)