Supabase Reference

Multi-tenant database patterns for Sanctiv.

Multi-Tenant Architecture

Key Concepts:

Organizations: Churches with unique slugs and settings
Users: Belong to exactly one organization, have roles (member/admin/pastor)
Data Isolation: Row Level Security (RLS) ensures users only access their org’s data
Journal Entries: Include user_id and org_id for multi-tenant isolation
Goals & Reports: Also scoped to user_id and org_id

Database Tables:

organizations - Churches with slug, settings, timezone
public.users - User profiles linked to organizations
journal_entries - Journal entries with multi-tenant isolation
goals - User goals with categories
weekly_reports - AI-generated weekly insights

Critical Rules:

✅ Always include org_id when querying or inserting data
❌ Never hard-code organization IDs
✅ Always test with users from different organizations
✅ Verify RLS policies prevent cross-org access

Environment Variables

2025 API Key Update: Supabase has transitioned from legacy JWT-based keys to new publishable/secret keys. Always use publishable keys - legacy anon keys are disabled.

# Required for Supabase (2025 Structure)
EXPO_PUBLIC_SUPABASE_URL=https://your-project.supabase.co
EXPO_PUBLIC_SUPABASE_KEY=sb_publishable_your-key-here

# CRITICAL: Always use publishable key (sb_publishable_... format)
# Legacy anon keys (eyJhb... format) are disabled and will not work

# Existing AI API Keys (already configured)
EXPO_PUBLIC_VIBECODE_ANTHROPIC_API_KEY=your-key
EXPO_PUBLIC_VIBECODE_OPENAI_API_KEY=your-key
EXPO_PUBLIC_VIBECODE_GROK_API_KEY=your-key

Key Changes in 2025:

Always use sb_publishable_... keys - legacy anon keys are disabled
Better security: Independent rotation, shorter expiry periods
Legacy keys no longer work - use supabase projects api-keys to get publishable key
Setup workflow automatically extracts publishable key from CLI

Schema

-- Enable UUID extension
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- ORGANIZATIONS (Churches)
CREATE TABLE organizations (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    name TEXT NOT NULL,
    slug TEXT UNIQUE NOT NULL,
    settings JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    CONSTRAINT organizations_slug_format CHECK (slug ~ '^[a-z0-9-]+$')
);

ALTER TABLE organizations ENABLE ROW LEVEL SECURITY;

-- USERS (Extends auth.users)
CREATE TABLE public.users (
    id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
    org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    email TEXT NOT NULL,
    full_name TEXT,
    role TEXT NOT NULL DEFAULT 'member',
    preferences JSONB DEFAULT '{}'::jsonb,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    CONSTRAINT users_role_check CHECK (role IN ('member', 'admin', 'pastor'))
);

CREATE INDEX users_org_id_idx ON public.users(org_id);
CREATE INDEX users_email_idx ON public.users(email);

ALTER TABLE public.users ENABLE ROW LEVEL SECURITY;

-- JOURNAL ENTRIES
CREATE TABLE journal_entries (
    id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
    user_id UUID NOT NULL REFERENCES public.users(id) ON DELETE CASCADE,
    org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
    journal_type TEXT NOT NULL,
    content TEXT NOT NULL,
    emotion TEXT,
    created_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    updated_at TIMESTAMP WITH TIME ZONE DEFAULT TIMEZONE('utc', NOW()) NOT NULL,
    CONSTRAINT journal_entries_type_check CHECK (journal_type IN ('gratitude', 'reflection', 'prayer', 'scripture', 'custom')),
    CONSTRAINT journal_entries_org_consistency CHECK (
        org_id = (SELECT org_id FROM public.users WHERE id = user_id)
    )
);

CREATE INDEX journal_entries_user_id_idx ON journal_entries(user_id);
CREATE INDEX journal_entries_org_id_idx ON journal_entries(org_id);
CREATE INDEX journal_entries_created_at_idx ON journal_entries(created_at DESC);
CREATE INDEX journal_entries_user_org_idx ON journal_entries(user_id, org_id);

ALTER TABLE journal_entries ENABLE ROW LEVEL SECURITY;

-- RLS POLICIES

-- Users can view only their organization
CREATE POLICY "users_can_view_own_organization" ON organizations
    FOR SELECT
    USING (id IN (SELECT org_id FROM public.users WHERE id = auth.uid()));

-- Users can view/update their own profile
CREATE POLICY "users_can_view_own_profile" ON public.users
    FOR SELECT USING (id = auth.uid());

CREATE POLICY "users_can_update_own_profile" ON public.users
    FOR UPDATE USING (id = auth.uid()) WITH CHECK (id = auth.uid());

-- Users CRUD their own journal entries
CREATE POLICY "users_can_view_own_entries" ON journal_entries
    FOR SELECT USING (user_id = auth.uid());

CREATE POLICY "users_can_insert_own_entries" ON journal_entries
    FOR INSERT WITH CHECK (
        user_id = auth.uid() AND
        org_id = (SELECT org_id FROM public.users WHERE id = auth.uid())
    );

CREATE POLICY "users_can_update_own_entries" ON journal_entries
    FOR UPDATE USING (user_id = auth.uid()) WITH CHECK (user_id = auth.uid());

CREATE POLICY "users_can_delete_own_entries" ON journal_entries
    FOR DELETE USING (user_id = auth.uid());

-- TRIGGERS
CREATE OR REPLACE FUNCTION update_updated_at_column()
RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = TIMEZONE('utc', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_organizations_updated_at
    BEFORE UPDATE ON organizations
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_users_updated_at
    BEFORE UPDATE ON public.users
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_journal_entries_updated_at
    BEFORE UPDATE ON journal_entries
    FOR EACH ROW EXECUTE FUNCTION update_updated_at_column();

-- PERMISSIONS
GRANT SELECT ON organizations TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON public.users TO authenticated;
GRANT SELECT, INSERT, UPDATE, DELETE ON journal_entries TO authenticated;

Client Setup

Location: src/lib/supabase.ts 2025 Update: Uses AsyncStorage for React Native session persistence.

// src/lib/supabase.ts
import AsyncStorage from "@react-native-async-storage/async-storage";
import { createClient } from '@supabase/supabase-js';
import type { Database } from './database.types';

const supabaseUrl = process.env.EXPO_PUBLIC_SUPABASE_URL;
const supabaseKey = process.env.EXPO_PUBLIC_SUPABASE_KEY;

export const supabase = createClient<Database>(supabaseUrl, supabaseKey, {
  auth: {
    storage: AsyncStorage,
    autoRefreshToken: true,
    persistSession: true,
    detectSessionInUrl: false,
  },
});

// Helper: Get current user's org_id
export const getCurrentUserOrgId = async (): Promise<string | null> => {
  const { data: { session } } = await supabase.auth.getSession();
  if (!session?.user) return null;

  // Try JWT claims first
  const orgId = session.user.user_metadata?.org_id || session.user.app_metadata?.org_id;
  if (orgId) return orgId;

  // Fallback: Query database
  const { data } = await supabase
    .from('users')
    .select('org_id')
    .eq('id', session.user.id)
    .maybeSingle();

  return (data as { org_id: string } | null)?.org_id ?? null;
};

Dependencies:

@supabase/supabase-js - Supabase client library
@react-native-async-storage/async-storage - Already installed in Expo SDK 53

Usage Patterns

Query with RLS

import { supabase, getCurrentUserOrgId } from '@/lib/supabase';

const orgId = await getCurrentUserOrgId();
const userId = (await supabase.auth.getUser()).data.user?.id;

// RLS automatically filters to user's data
const { data, error } = await supabase
  .from('journal_entries')
  .select('*')
  .eq('user_id', userId)
  .eq('org_id', orgId)
  .order('created_at', { ascending: false });

Insert

const { data, error } = await supabase
  .from('journal_entries')
  .insert({
    user_id: userId,
    org_id: orgId,
    journal_type: 'reflection',
    content: 'Today I reflected on...',
    emotion: 'peaceful'
  })
  .select()
  .single();

Row-Level Security (RLS)

Critical: All queries are automatically filtered by RLS policies. Users can only access data from their organization. Testing RLS:

Create test users in different organizations
Verify user A cannot see user B’s data if in different orgs
Never bypass RLS in client-side code
Test all CRUD operations with multi-org users

Service Layer Pattern

After Phase 4: Always use service layer

❌ DON’T: Direct Supabase calls in components

const { data } = await supabase.from('journal_entries').select('*')

✅ DO: Use service layer

import { journalService } from '@/services/supabase/journal'
const entries = await journalService.fetchEntries(userId, orgId)

Zustand Integration

// Store actions call services
addEntry: async (entry) => {
  const newEntry = await journalService.createEntry(entry)
  set({ entries: [newEntry, ...get().entries] })
}

Multi-Tenant Checklist

When creating/updating data operations:

Include org_id in all inserts
Filter queries by org_id
Test with users from different organizations
Verify RLS policies prevent cross-org access
Never hard-code organization IDs
Use getCurrentUserOrgId() helper
Handle cases where org_id is null

TypeScript Types

// src/database/types/database.ts
export interface Database {
  public: {
    Tables: {
      organizations: {
        Row: {
          id: string
          name: string
          slug: string
          settings: Json
          created_at: string
          updated_at: string
        }
      }
      users: {
        Row: {
          id: string
          org_id: string
          email: string
          full_name: string | null
          role: 'member' | 'admin' | 'pastor'
          preferences: Json
          created_at: string
          updated_at: string
        }
      }
      journal_entries: {
        Row: {
          id: string
          user_id: string
          org_id: string
          journal_type: 'gratitude' | 'reflection' | 'prayer' | 'scripture' | 'custom'
          content: string
          emotion: string | null
          created_at: string
          updated_at: string
        }
      }
    }
  }
}

export type Tables<T extends keyof Database['public']['Tables']> =
  Database['public']['Tables'][T]['Row'];

Seed Data (10 Pilot Churches)

INSERT INTO organizations (id, name, slug, settings) VALUES
('00000000-0000-0000-0000-000000000001'::uuid, 'Grace Community Church', 'grace-community', 
  '{"timezone": "America/Los_Angeles", "features": {"companion_mode": true, "insights": true}}'::jsonb),
('00000000-0000-0000-0000-000000000002'::uuid, 'First Baptist Church', 'first-baptist',
  '{"timezone": "America/Chicago", "features": {"companion_mode": true, "insights": true}}'::jsonb),
-- ... (8 more churches)

Source: sanctiv-master/packages/database/
Package: @supabase/supabase-js

Getting Started

Infrastructure

Philosophy

Features

Planning

SUPABASE

Supabase Reference

Multi-Tenant Architecture

Environment Variables

Schema

Client Setup

Usage Patterns

Query with RLS

Insert

Row-Level Security (RLS)

Service Layer Pattern

❌ DON’T: Direct Supabase calls in components

✅ DO: Use service layer

Zustand Integration

Multi-Tenant Checklist

TypeScript Types

Seed Data (10 Pilot Churches)

Getting Started

Infrastructure

Philosophy

Features

Planning

​Supabase Reference

​Multi-Tenant Architecture

​Environment Variables

​Schema

​Client Setup

​Usage Patterns

​Query with RLS

​Insert

​Row-Level Security (RLS)

​Service Layer Pattern

​❌ DON’T: Direct Supabase calls in components

​✅ DO: Use service layer

​Zustand Integration

​Multi-Tenant Checklist

​TypeScript Types

​Seed Data (10 Pilot Churches)

Supabase Reference

Multi-Tenant Architecture

Environment Variables

Schema

Client Setup

Usage Patterns

Query with RLS

Insert

Row-Level Security (RLS)

Service Layer Pattern

❌ DON’T: Direct Supabase calls in components

✅ DO: Use service layer

Zustand Integration

Multi-Tenant Checklist

TypeScript Types

Seed Data (10 Pilot Churches)