Supabase

Supabase is Mosayic’s choice for everything data-related: user accounts, the application database, file storage, realtime updates, and authentication.

What it is

Supabase is an open-source bundle of best-in-class tools wrapped around a Postgres database:

• Postgres — the database itself
• PostgREST — auto-generated REST API for your tables
• GoTrue — authentication (email, OAuth, phone, magic links, etc.)
• Storage — S3-compatible file storage with row-level access control
• Realtime — websocket subscriptions to database changes
• Studio — a web UI for browsing your data and writing SQL
• Edge Functions — Deno-based serverless functions (Mosayic doesn’t use these by default)

The hosted version runs on supabase.com. The local version runs in Docker on your machine, controlled by the supabase CLI.

Why Mosayic uses Supabase

• It’s Postgres. When your app grows to needing real database features (CTEs, materialized views, full-text search, JSONB, partial indexes), you already have them. No migration to a “real” database when you scale.
• Auth that doesn’t suck. GoTrue handles Google OAuth, Apple Sign-In, magic links, password reset, and JWT issuance with sensible defaults.
• Row Level Security. Postgres’s built-in RLS lets you write security rules in SQL, enforced at the database. Your mobile app can talk to PostgREST directly for many CRUD operations without you writing custom API endpoints.
• Generous free tier. 500 MB database, 1 GB file storage, 50k monthly active users, all free. Enough to validate most ideas.
• No lock-in. If you ever want to leave Supabase, your data is in standard Postgres. Export, import, done.

Local vs hosted

Mosayic uses both, at different times:

• During development, you run Supabase locally with supabase start. Everything is on your laptop. Free, fast, and you can wipe the DB by stopping and re-starting.
• In production, your deployed app talks to a hosted Supabase project at <project>.supabase.co. You create this when you’re ready to ship.

The two are kept in sync via SQL migrations in api/supabase/migrations/. Both run the exact same DDL.

Schema management

Schema lives in .sql files under api/supabase/migrations/, named like 20260101120000_initial_schema.sql.

Mosayic ships with a starter migration that creates:

• public.users — synced from auth.users via trigger; stores Google profile data
• public.projects (in the Mosayic backend’s own DB; doesn’t apply to your app)
• An updated_at trigger function used everywhere
• RLS policies that scope all queries to auth.uid() = user_id

When you add new tables, you create new migration files alongside this one. The Supabase CLI applies them in order.

To apply a new migration locally:

cd api
supabase db reset    # destroys your local DB and re-runs all migrations

To apply migrations to production, push to main on your <project>-api repo. The supabase-deploy-migrations.yaml GitHub Action runs supabase db push against your production project.

Row Level Security

Every table in your app should have RLS enabled. The default Mosayic migration shows the pattern:

alter table public.users enable row level security;

create policy "Users can read their own row"
  on public.users for select
  using (auth.uid() = id);

create policy "Users can update their own row"
  on public.users for update
  using (auth.uid() = id);

With these in place, your mobile app can query users directly via PostgREST and only see its own row. The anon key being public doesn’t matter — Postgres enforces the rules.

Caution

RLS is not optional. A table without RLS is readable and writable by anyone with your anon key — which is anyone who downloads your app. Always enable RLS, always write at least one policy, and always test by signing in as a different user.

Auth in your app

The mobile starter uses Supabase’s JS SDK:

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

await supabase.auth.signInWithPassword({ email, password });
await supabase.auth.signInWithOAuth({ provider: 'google' });
await supabase.auth.signOut();

On successful sign-in, the SDK persists the session in AsyncStorage and auto-refreshes tokens. Subsequent queries to PostgREST or your API automatically include the JWT.

Talking to Supabase from your API

Your FastAPI backend uses the official Supabase Python client:

from supabase import Client

result = await client.table("users").select("*").eq("id", user_id).single().execute()

The backend uses the service role key (which bypasses RLS), so it can act on behalf of any user. Be careful — this is a powerful key. Mosayic stores it in Google Cloud Secret Manager and injects it into the running container at runtime, never in the mobile app.

Storage

Supabase Storage buckets work like S3 buckets. From the mobile app:

await supabase.storage
  .from('avatars')
  .upload(`${userId}/avatar.jpg`, file);

Buckets can be public or private. Private buckets enforce access via RLS-style policies (bucket_id and name are checked against the calling user’s JWT).

Realtime

You can subscribe to inserts/updates/deletes on any table:

supabase
  .channel('messages')
  .on('postgres_changes', { event: '*', schema: 'public', table: 'messages' }, payload => {
    console.log('Change:', payload);
  })
  .subscribe();

This gives you live-updating UIs without polling. Use it for chat, dashboards, collaborative editing, etc.

Going to production

When you’re ready to ship:

1. Create a free hosted Supabase project at supabase.com
2. Copy the project URL and anon key into your Mosayic dashboard’s Secrets screen
3. Copy the service role key into Google Secret Manager (the dashboard does this for you)
4. Push your code — GitHub Actions runs supabase db push against the new project
5. Your production API now reads from the hosted Supabase

You can keep developing against your local Supabase indefinitely — the two never need to talk to each other.