Database Schema

tenants
  ├── tenant_users → users
  ├── providers
  │     ├── providers_staff → users
  │     ├── programmes → cohorts, courses → course_pages → course_modules
  │     ├── mentor_programmes → mentors
  │     ├── events
  │     └── resources
  ├── companies
  │     ├── company_staff → users
  │     ├── programme_signups → programmes, cohorts
  │     ├── mentor_credits
  │     └── mentor_pairings → mentors
  └── places

users
  ├── user_preferences, user_embeddings, user_saved_items
  ├── course_signups, course_engagements, event_signups
  ├── mentor_session_signups
  └── notifications

SELECT p.*, ps.status AS enrollment_status
FROM programmes p
JOIN programme_signups ps ON ps.programme_id = p.id
WHERE ps.company_id = 'company-uuid'
  AND ps.status = 'active';

SELECT m.*, array_agg(t.name) AS expertise_tags
FROM mentors m
JOIN tag_links tl ON tl.target_id = m.id AND tl.target_type = 'mentor'
JOIN tags t ON t.id = tl.tag_id
WHERE t.name IN ('fundraising', 'product')
GROUP BY m.id;

SELECT c.title,
  COUNT(DISTINCT cm.id) AS total_modules,
  COUNT(DISTINCT ce.module_id) AS completed_modules,
  ROUND(COUNT(DISTINCT ce.module_id)::numeric / COUNT(DISTINCT cm.id) * 100) AS progress_percentage
FROM courses c
JOIN course_pages cp ON cp.course_id = c.id
JOIN course_modules cm ON cm.page_id = cp.id
LEFT JOIN course_engagements ce ON ce.module_id = cm.id AND ce.user_id = 'user-uuid'
WHERE c.id = 'course-uuid'
GROUP BY c.id, c.title;

import { createClient } from "@supabase/supabase-js";

const supabase = createClient(
  process.env.NEXT_PUBLIC_SUPABASE_URL!,
  process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!
);

const { data, error } = await supabase
  .from("programmes")
  .select("*, provider:providers(*), cohorts(*)")
  .eq("status", "published");