Caluru

Caluru - Backend & External Services

This document describes Supabase (Auth + Edge Functions), CloudKit, and other backend/external integrations.

Overview

System Purpose
Supabase Auth Sign in with Apple (optional during onboarding)
Supabase Edge Function openai-proxy Proxy for OpenAI Chat Completions (GPT-4o-mini for Assistant, GPT-4o for food photo); API key never in app
CloudKit SwiftData sync (private container iCloud.com.agarcia.caluru) for profile and data across devices
Open Food Facts API Barcode product lookup (public API, no key)

Supabase

Configuration (App)

Auth (Sign in with Apple)

Edge Function: openai-proxy

Deployment

Project: opxqcnxiqbitezragpch → https://opxqcnxiqbitezragpch.supabase.co

Prerequisites:

  1. Supabase CLI installed (brew install supabase/tap/supabase)
  2. Logged in: supabase login
  3. Linked from repo root: supabase link --project-ref opxqcnxiqbitezragpch

Deploy the function (source: supabase/functions/openai-proxy/index.ts):

# From project root
supabase functions deploy openai-proxy --project-ref opxqcnxiqbitezragpch

supabase/config.toml sets [functions.openai-proxy] verify_jwt = true (gateway rejects unsigned requests).

Set secrets (never commit these):

supabase secrets set OPENAI_API_KEY=sk-... --project-ref opxqcnxiqbitezragpch
# optional hourly cap override (default 50)
supabase secrets set OPENAI_HOURLY_LIMIT=50 --project-ref opxqcnxiqbitezragpch

Apply database migrations (rate limit table + RPC):

supabase db push --project-ref opxqcnxiqbitezragpch

Verify deployment:

# Expect 401 without a user JWT (function is up, auth required)
curl -s -o /dev/null -w "%{http_code}" -X POST \
  "https://opxqcnxiqbitezragpch.supabase.co/functions/v1/openai-proxy" \
  -H "Content-Type: application/json" -d '{}'

Rate limit (database-backed)

Request from App

Edge Function Behavior (Summary)

  1. CORS: Responds to OPTIONS; allows POST with Authorization, Content-Type, apikey, Accept-Language.
  2. Auth: Creates Supabase client with request Authorization; calls getUser(). If no user or error → 401.
  3. Pro gate: is_user_pro RPC; if false → 402.
  4. Body validation (before rate-limit charge): require feature; model allowlist gpt-4o-mini / gpt-4o; clamp max_tokens (1–4096) and temperature (0–2); max 50 messages / 100k content chars; reject client system roles (only user / assistant).
  5. Rate limit: increment_openai_usage RPC after validation (SECURITY DEFINER; table has no client policies); over limit → 429.
  6. System prompt: Inject locale-aware system message from feature + app_language (+ optional user_context for assistant), including mandatory safety/refusal rules (no diagnosis, no extreme restriction, no eating-disorder content; estimates only).
  7. Moderation (assistant feature): Call OpenAI Moderations (omni-moderation-latest) on the latest user text before rate-limit charge; on flag, return a synthetic chat completion with a localized refusal. After a non-streaming completion, moderate the assistant reply the same way. Moderation API failures fail-open (prompt rules remain). Streaming responses skip post-moderation.
  8. Forward to OpenAI with Bearer OPENAI_API_KEY; returns OpenAI response body and status (or synthetic refusal).

RevenueCat webhook (subscriptions)

Who Uses the Proxy

CloudKit

Open Food Facts

Security Notes

Intentional RLS lockout (user_subscriptions, openai_rate_limit)

These two tables have RLS enabled and zero policies on purpose. That is a client lockout, not a missing-policy bug.

Supabase advisor INFO rls_enabled_no_policy (lint 0008) will keep flagging them. Do not add client SELECT/INSERT policies unless product requirements change — the iOS app must not read or write these tables via the Data API.

Table Writers / readers Client path
user_subscriptions revenuecat-webhook Edge Function with SUPABASE_SERVICE_ROLE_KEY (bypasses RLS) Pro check only via SECURITY DEFINER RPC is_user_pro()
openai_rate_limit SECURITY DEFINER RPC increment_openai_usage(p_limit) (runs as owner; uses auth.uid()) Called by openai-proxy with user JWT + anon key — no service role, no direct table access

Defense in depth (migrations 20250219000000_openai_rate_limit.sql, 20260709000000_user_subscriptions.sql, 20260806140000_revoke_client_grants_locked_tables.sql):

Verified Edge Function access

Function Auth model How it touches locked tables
revenuecat-webhook verify_jwt = false; shared webhook secret in Authorization Creates admin client with SUPABASE_SERVICE_ROLE_KEY; select / upsert on user_subscriptions
openai-proxy verify_jwt = true; user JWT Anon client + request Authorization; rpc('is_user_pro') then rpc('increment_openai_usage') — never queries the tables directly

Clients do not need policies for reads: subscription state is exposed only as a boolean via is_user_pro(); rate-limit counters stay server-side.

RLS summary (profiles, family, gamification)

Run in SQL Editor to audit policies:

select schemaname, tablename, policyname, cmd, roles
from pg_policies
where schemaname = 'public'
  and tablename in (
    'profiles',
    'families', 'family_members', 'family_invites',
    'daily_progress', 'family_planned_meals',
    'user_gamification', 'xp_events', 'user_achievements',
    'user_subscriptions', 'openai_rate_limit'
  )
order by tablename, policyname;

Expected: RLS enabled on all tables above. user_subscriptions and openai_rate_limit have no policies (intentional lockout; see above). Family/gamification policies use private.current_family_id(), private.is_family_admin(), and private.family_member_ids() helpers (defined in 20260720120000_family_gamification_rls.sql; invite updates tightened there; profile + invite policy shape also in 20260613120000_usernames_and_userid_invites.sql).

Table Access model
profiles Own row; family members can read each other’s profiles
families Members, creator, or pending invitee (read name)
family_members Family roster; insert via admin bootstrap or accepted invite
family_invites Family admins send; invitee accepts/declines
daily_progress Own row; family members can read each other
family_planned_meals Family members read; admins write
user_gamification Own row upsert; family members read for leaderboard
xp_events / user_achievements Insert own; family members read
user_subscriptions No client policies; service_role webhook + is_user_pro()
openai_rate_limit No client policies; increment_openai_usage() only
user_blocks Blocker CRUD own rows; used to filter Groups UGC + pokes
content_reports Reporter insert/select own; status updates via service_role / admin hide RPC
hidden_ugc Family members read; family admins insert/delete via hide_family_ugc

Groups UGC safety (Guideline 1.2)

Migration: 20260809045055_family_content_moderation.sql

Swift: ContentModerationService, ReportContentSheet, entry points on Groups branding, shared recipes, tonight meals, meal planner slots, and member detail (Report + Block).