Supabase

• Self-hosted Supabase on the client's Azure VM (via docker compose)
• Public endpoint: https://api.jarilloengineering.com
• Architecture, infrastructure details are in software-design.md

Client

Singleton is defined in src/lib/supabaseClient.ts - use using the official supabase javascript client(not axios)

import { createClient } from '@supabase/supabase-js';
import AsyncStorage from '@react-native-async-storage/async-storage';

export const supabase = createClient(
  process.env.EXPO_PUBLIC_SUPABASE_URL!,
  process.env.EXPO_PUBLIC_SUPABASE_ANON_KEY!,
  {
    auth: {
      storage: AsyncStorage,
      autoRefreshToken: true,
      persistSession: true,
      detectSessionInUrl: false,
    },
  }
);

---

Auth

// Sign in
const { data, error } = await supabase.auth.signInWithPassword({ email, password });

// Sign out
await supabase.auth.signOut();

// Current user / session
const { data: { user } } = await supabase.auth.getUser();
const { data: { session } } = await supabase.auth.getSession();

// Listener (used in AuthContext)
const { data: { subscription } } = supabase.auth.onAuthStateChange((event, session) => {
  if (event === 'SIGNED_IN') setUser(session?.user ?? null);
  else if (event === 'SIGNED_OUT') { setUser(null); router.replace('/login'); }
});
return () => subscription.unsubscribe();

Edge Functions (The Security Proxy)

Sensitive operations and administrative tasks are offloaded to Supabase Edge Functions. This ensures that service-role keys and third-party API keys (like Azure) never reach the client.

Admin: Create User

const { error } = await supabase.functions.invoke('admin-create-user', {
  body: { email, password, role },
});

Azure OpenAI Proxy

const { data, error } = await supabase.functions.invoke('azure-openai', {
  body: { messages, temperature, max_tokens },
});

Azure Speech Proxy

const { data, error } = await supabase.functions.invoke('azure-speech', {
  body: { storagePath, language },
});

---

Database

All DB calls should be done from src/services/ - screens should not call supabase.from() directly

// SELECT with filter + pagination
const { data, error } = await supabase
  .from('cases')
  .select('*, client:clients(id, first_name, last_name)')
  .eq('status', 'open')
  .order('created_at', { ascending: false })
  .range(0, 9);

// INSERT
const { data, error } = await supabase
  .from('cases')
  .insert({ title, client_id, status: 'open', assigned_to: userId })
  .select()
  .single();

// UPDATE
const { data, error } = await supabase
  .from('cases')
  .update({ status: 'closed' })
  .eq('id', caseId)
  .select()
  .single();

// DELETE
const { error } = await supabase.from('cases').delete().eq('id', caseId);

Filter Reference

.eq('col', val)       .neq('col', val)
.gt / .gte / .lt / .lte
.ilike('col', '%pat%')            // case-insensitive LIKE
.in('col', [v1, v2])
.is('col', null)
.order('col', { ascending: false })
.limit(10)
.range(0, 9)                      // pagination

---

Storage

Audio recordings go in the private bucket audio-recordings - use signed URLs over public URLs

// Upload
const filePath = `${userId}/${caseId}/${Date.now()}.m4a`;
const { error } = await supabase.storage
  .from('audio-recordings')
  .upload(filePath, blob, { contentType: 'audio/m4a', upsert: false });

// Signed URL (valid 1 hour)
const { data } = await supabase.storage
  .from('audio-recordings')
  .createSignedUrl(filePath, 3600);

// Delete
await supabase.storage.from('audio-recordings').remove([filePath]);

---

Realtime

useEffect(() => {
  const sub = supabase
    .channel('cases-changes')
    .on('postgres_changes', { event: '*', schema: 'public', table: 'cases' }, (payload) => {
      if (payload.eventType === 'INSERT') setCases((p) => [payload.new as Case, ...p]);
      else if (payload.eventType === 'UPDATE')
        setCases((p) => p.map((c) => (c.id === payload.new.id ? payload.new as Case : c)));
      else if (payload.eventType === 'DELETE')
        setCases((p) => p.filter((c) => c.id !== payload.old.id));
    })
    .subscribe();
  return () => { supabase.removeChannel(sub); };
}, []);

---

Row Level Security (RLS)

RLS ensures that authorization is enforced at the database layer, independent of the frontend or API implementation
RLS is enabled and enforced on relevant tables in the database

Source of truth (what is deployed):

• fieldnotes-ai-app/supabase/migrations/*_remote_schema.sql

Human-maintained policy definitions (for readability/review):

• fieldnotes-ai-app/supabase/policies/cases_rls.sql
• fieldnotes-ai-app/supabase/policies/clients_rls.sql

Example intent:

-- Practitioners see only their own cases
CREATE POLICY "practitioners_select_own_cases" ON public.cases FOR SELECT
USING (assigned_to = auth.uid());

-- Admins see all (role stored in JWT user_metadata)
CREATE POLICY "admins_select_all_cases" ON public.cases FOR SELECT
USING ((auth.jwt() -> 'user_metadata' ->> 'role') = 'admin');
````

> Note: policy files under `supabase/policies/` should match what exists in the deployed schema (see migrations dump).

---

## Error Handling

```typescript
const { data, error } = await supabase.from('cases').select('*');
if (error) {
  console.error(`[caseService] ${error.code}: ${error.message}`);
  throw error;
}

http codes	meaning
401	Not authenticated (missing/invalid JWT)
403	Forbidden (often RLS policy blocked)
409	Conflict (e.g., unique constraint violation)
5xx	Server-side error

---

VM Access (Backend Only)

SSH Config (~/.ssh/config)

• add this to your config file:

Host supabase-vm
    HostName <vm-ip>
    User <username>
    IdentityFile ~/.ssh/<keyfile>.pem
    IdentitiesOnly yes

• check discord for these credentials

Supabase Studio(to access supabase studio)

ssh -L 8001:localhost:8000 supabase-vm
# open http://localhost:8001 on local web browser
# credentials: cat ~/supabase-deployment/supabase/docker/.env | grep DASHBOARD

DB Access

• Shouldnt do, except in exceptional circumstances

ssh -L 5433:localhost:5432 supabase-vm
psql -h localhost -p 5433 -U postgres -d postgres
# or pgAdmin/DBeaver: host=localhost, port=5433

Docker Operations

ssh supabase-vm && cd ~/supabase-deployment/supabase/docker
docker compose ps
docker compose logs -f [service]
docker compose restart [service]