Database schema, Supabase patterns, RLS policies, Edge Functions, realtime subscriptions, migrations, and query patterns for Sparq Connection. Use this skill whenever working on ANY database migration, Supabase query, RLS policy, Edge Function, realtime subscription, data model change, or API route that touches the database in Sparq Connection.
Sparq uses Supabase (PostgreSQL) with RLS on every table. The schema is defined in supabase/schema.sql (base) plus 11 real migrations in supabase/migrations/.
Full table definitions with columns: see
references/schema.md
┌─────────────────────────────────────────────────────────────────────┐
│ USER DOMAIN │
│ profiles ←──── user_roles user_preferences │
│ │ user_entitlements profile_traits (AI-inferred) │
│ │ │
│ PARTNERSHIPS │
│ profiles.partner_id ←→ profiles (bidirectional link) │
│ partner_invitations (7-day expiring invite codes) │
│ partner_syntheses (shared AI reflections) │
│ vulnerability_escrow (mutual-unlock deep prompts) │
│ │
│ DAILY LOOP │
│ daily_sessions (1 per user per day, 4-phase) │
│ daily_questions → daily_question_responses │
│ │
│ JOURNEYS │
│ journeys → journey_questions │
│ user_journeys (progress per user per journey) │
│ journey_responses (answers to journey questions) │
│ │
│ SKILL TREE │
│ skill_progress (per-skill level + XP) │
│ user_skill_tracks (aggregate XP per track, level-up via RPC) │
│ │
│ AI & INSIGHTS │
│ memories (pgvector embeddings for Mem0 semantic search) │
│ weekly_insights (cached weekly analysis: patterns, growth edge) │
│ graduation_reports (Day 14 personalized report) │
│ profile_traits (attachment, conflict, love language inferences) │
│ │
│ SAFETY & TRACKING │
│ conflict_episodes (conflict → resolution tracking) │
│ outcome_assessments (milestone assessments with scores) │
│ analytics_events (event log: event_name + event_props) │
│ user_activities (activity log: activity_type + details) │
│ │
│ OTHER │
│ goals → goal_milestones (user goal tracking) │
│ date_ideas → user_date_ideas (catalog + user saves) │
│ system_settings (admin key-value config) │
└─────────────────────────────────────────────────────────────────────┘
profiles.id references auth.users(id) — every auth user has a profileprofiles.partner_id → profiles.id — bidirectional partner link (both sides set)partner_invitations.sender_id → auth.users(id) — invite flow creates link on acceptancedaily_sessions unique on (user_id, session_local_date) — one session per user per dayjourney_responses unique on (user_id, question_id) — one answer per questionuser_skill_tracks primary key (user_id, track_key) — one row per user per trackpartner_syntheses unique on (user_a_id, user_b_id, day_index) — one synthesis per couple per dayvulnerability_escrow unique on (couple_id, prompt_id, user_id) — one response per user per promptuser_role: 'user' | 'admin' | 'partner'
relationship_type: 'monogamous' | 'polyamorous' | 'lgbtq' | 'long-distance'
journey_type: 'communication' | 'intimacy' | 'trust' | 'growth' | 'conflict'
subscription_tier: 'free' | 'premium' | 'platinum'
invitation_status: 'pending' | 'accepted' | 'declined' | 'expired'
question_modality: 'reflection' | 'discussion' | 'activity'
love_language: 'words-of-affirmation' | 'acts-of-service' | 'receiving-gifts' | 'quality-time' | 'physical-touch'