> ## Documentation Index
> Fetch the complete documentation index at: https://docs.shipnative.app/llms.txt
> Use this file to discover all available pages before exploring further.

# Backend & Database

> Database schema, security, and data management with Supabase or Convex

Shipnative supports two backends: **Supabase** (PostgreSQL) and **Convex** (reactive TypeScript). This guide covers schema design, security patterns, and data management for both.

> [!NOTE]
>
> **Database vs Local Storage:** Store user data (profiles, preferences, content) in the database so it syncs across devices. Use MMKV only for local caching. See [State Management](https://docs.shipnative.app/development/state-management.md#storage-mmkv-vs-supabase) for details.

## Database Schema

> [tab] Supabase
>
> When you run `supabase/schema.sql`, it creates core tables for common app requirements.
>
> ### Core Tables
>
> | Table | Description | Key Fields |
> | - | - | - |
> | `profiles` | User profile information | `first_name`, `last_name`, `avatar_url`, `bio` |
> | `user_preferences` | App-specific settings | `language`, `timezone`, `profile_visibility` |
> | `push_tokens` | Device tokens for notifications | `token`, `platform`, `device_id`, `is_active` |
> | `waitlist` | Marketing waitlist | `email`, `source`, `created_at` |
>
> > [!TIP]
> >
> > **Automatic Sync:** The `profiles` and `user_preferences` tables are automatically created via triggers whenever a new user signs up.

> [tab] Convex
>
> The schema is defined in `convex/schema.ts` with TypeScript validation.
>
> ### Core Tables
>
> | Table | Description | Key Fields |
> | - | - | - |
> | `users` | User accounts (extends auth) | `name`, `email`, `role`, `preferences` |
> | `profiles` | Extended profile data | `displayName`, `username`, `bio`, `socialLinks` |
> | `posts` | Content/posts | `title`, `content`, `status`, `authorId` |
> | `comments` | Nested comments | `postId`, `authorId`, `content`, `parentId` |
> | `notifications` | User notifications | `userId`, `type`, `title`, `read` |
> | `pushTokens` | Push notification tokens | `userId`, `token`, `platform` |
> | `presence` | Realtime presence tracking | `channel`, `userId`, `state` |
> | `broadcasts` | Realtime messages | `channel`, `event`, `payload` |
> | `waitlist` | Marketing waitlist | `email`, `source`, `status` |
> | `auditLogs` | Action audit trail | `userId`, `action`, `resource` |
>
> > [!TIP]
> >
> > **Type Safety:** Convex schemas provide end-to-end TypeScript types. Your queries and mutations are fully typed automatically.

***

## Security

> [tab] Supabase RLS
>
> Security is built-in at the database level with Row Level Security (RLS):
>
> * **Strict Privacy**: Users can only access their own data
> * **Public Profiles**: Basic profile info is publicly readable for social features
> * **Tokens & Preferences**: Strictly private, owner-only access
>
> ```sql theme={null}
> -- Users can only access their own data
> CREATE POLICY "Users access own data"
>   ON profiles FOR ALL
>   USING (auth.uid() = id);
>
> -- Public read, authenticated write
> CREATE POLICY "Anyone can read posts"
>   ON posts FOR SELECT USING (true);
>
> CREATE POLICY "Users create own posts"
>   ON posts FOR INSERT
>   WITH CHECK (auth.uid() = author_id);
> ```
>
> > [!WARNING]
> >
> > **Always enable RLS:** When creating new tables, run `ALTER TABLE name ENABLE ROW LEVEL SECURITY;` and add policies.

> [tab] Convex Functions
>
> Convex uses function-level security. The boilerplate includes security helpers in `convex/lib/security.ts`:
>
> ### Security Helpers
>
> ```typescript theme={null}
> import { requireAuth, requireOwnership, requireRole } from "./lib/security"
>
> // Require authentication
> export const getMyProfile = query({
>   handler: async (ctx) => {
>     const userId = await requireAuth(ctx)
>     return await ctx.db.get(userId)
>   },
> })
>
> // Require ownership of a resource
> export const updatePost = mutation({
>   args: { postId: v.id("posts"), title: v.string() },
>   handler: async (ctx, args) => {
>     const userId = await requireAuth(ctx)
>     await requireOwnership(ctx, "posts", args.postId, userId, "authorId")
>     await ctx.db.patch(args.postId, { title: args.title })
>   },
> })
>
> // Require specific role
> export const adminAction = mutation({
>   handler: async (ctx) => {
>     const userId = await requireAuth(ctx)
>     await requireRole(ctx, userId, ["admin", "moderator"])
>     // Admin-only logic
>   },
> })
> ```
>
> ### Available Helpers
>
> | Helper | Description |
> | - | - |
> | `requireAuth(ctx)` | Throws if not authenticated, returns userId |
> | `getAuthUserId(ctx)` | Returns userId or null (no throw) |
> | `requireOwnership(ctx, table, id, userId, field?)` | Throws if user doesn't own the resource |
> | `requireRole(ctx, userId, roles[])` | Throws if user doesn't have required role |
> | `isOwner(ctx, table, id, userId, field?)` | Returns boolean ownership check |
> | `filterByOwner(ctx, table, userId, field?)` | Returns only user's records |
> | `auditLog(ctx, userId, action, metadata?)` | Logs to console in dev (extend for production) |
>
> > [!WARNING]
> >
> > **No Database-Level RLS:** Unlike Supabase, Convex has no automatic row-level security. You must use these helpers in every function that accesses user data.

***

## Managing the Schema

> [tab] Supabase
>
> ### Using Migrations (Recommended)
>
> Shipnative uses database migrations to version control schema changes. **Always use migrations instead of editing `schema.sql` directly.**
>
> Migrations provide version control and prevent "already exists" errors when running the schema multiple times—the industry standard used by Rails, Django, Laravel, and Prisma.
>
> #### Create a New Migration
>
> ```bash theme={null}
> # Create a new migration file
> supabase migration new add_todos_table
>
> # This creates: supabase/migrations/YYYYMMDDHHMMSS_add_todos_table.sql
> ```
>
> #### Apply Migrations
>
> ```bash theme={null}
> # Link your project (one-time)
> supabase link --project-ref your-project-ref
>
> # Apply all migrations
> supabase db push
> ```
>
> #### Migration Best Practices
>
> 1. **Always use `IF NOT EXISTS`** - Makes migrations safe to run multiple times
> 2. **Drop policies before recreating** - Prevents "already exists" errors
> 3. **Enable RLS on all user tables** - Security best practice
> 4. **Add indexes for foreign keys** - Performance optimization
>
> Example migration:
>
> ```sql theme={null}
> -- Create table
> CREATE TABLE IF NOT EXISTS public.todos (
>     id UUID PRIMARY KEY DEFAULT uuid_generate_v4(),
>     user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE NOT NULL,
>     title TEXT NOT NULL,
>     completed BOOLEAN DEFAULT false,
>     created_at TIMESTAMPTZ DEFAULT NOW()
> );
>
> -- Enable RLS
> ALTER TABLE public.todos ENABLE ROW LEVEL SECURITY;
>
> -- Add policies
> DROP POLICY IF EXISTS "Users can view own todos" ON public.todos;
> CREATE POLICY "Users can view own todos"
>     ON public.todos FOR SELECT
>     USING (auth.uid() = user_id);
>
> -- Add indexes
> CREATE INDEX IF NOT EXISTS todos_user_id_idx ON public.todos(user_id);
> ```
>
> > [!NOTE]
> >
> > See the complete migration guide at `supabase/migrations/README.md` in your project.
>
> ### Manual Schema Changes (Not Recommended)
>
> You can also run SQL directly in the Supabase SQL Editor, but this won't be version controlled:
>
> ```sql theme={null}
> ALTER TABLE public.[table_name] ADD COLUMN [column_name] [type] DEFAULT [value];
> ```

> [tab] Convex
>
> ### Adding New Tables
>
> Edit `convex/schema.ts`:
>
> ```typescript theme={null}
> import { defineSchema, defineTable } from "convex/server"
> import { v } from "convex/values"
>
> export default defineSchema({
>   // Add your new table
>   products: defineTable({
>     name: v.string(),
>     price: v.number(),
>     ownerId: v.id("users"),
>     createdAt: v.number(),
>   })
>     .index("by_owner", ["ownerId"])
>     .index("by_price", ["price"]),
> })
> ```
>
> ### Schema Changes
>
> Convex handles migrations automatically. Just update the schema and push:
>
> ```bash theme={null}
> npx convex dev  # Development - auto-syncs
> npx convex deploy  # Production - deploys changes
> ```
>
> > [!TIP]
> >
> > **Indexes:** Always add indexes for fields you query frequently. Use compound indexes for multi-field queries.

***

## Seed Data

> [tab] Supabase
>
> Use the SQL Editor to insert test data:
>
> ```sql theme={null}
> INSERT INTO profiles (id, first_name, last_name)
> VALUES ('user-id-here', 'Demo', 'User');
> ```
>
> Or import from a file:
>
> ```bash theme={null}
> npx supabase db seed
> ```

> [tab] Convex
>
> The boilerplate includes seed utilities in `convex/seed.ts`:
>
> ### Seed Commands
>
> ```bash theme={null}
> # Populate with demo data
> npx convex run seed:run
>
> # Clear all seed data
> npx convex run seed:clear
>
> # Check seed status
> npx convex run seed:status
> ```
>
> ### Demo Data Included
>
> * **2 demo users**: `demo@shipnative.app`, `test@shipnative.app`
> * **3 sample posts**: Published and draft examples
> * **Sample comments**: Nested comment thread
> * **Notifications**: Welcome and sample notifications
>
> ### Custom Seed Data
>
> Add your own seed data in `convex/seed.ts`:
>
> ```typescript theme={null}
> export const run = internalMutation({
>   handler: async (ctx) => {
>     // Add custom seed logic
>     await ctx.db.insert("products", {
>       name: "Demo Product",
>       price: 99,
>       createdAt: Date.now(),
>     })
>   },
> })
> ```
>
> > [!TIP]
> >
> > **Development Only:** Seed functions are internal mutations. They won't be accessible from the client.

***

## Reference Implementation: DataDemoScreen

The boilerplate includes a **DataDemoScreen** that demonstrates proper data fetching patterns for your chosen backend. Use it as a template when building your own data-driven screens.

### How It Works

The screen uses conditional exports to load the correct implementation:

```
screens/
├── DataDemoScreen.tsx           # Router - loads correct version
├── DataDemoScreen.supabase.tsx  # Supabase patterns
└── DataDemoScreen.convex.tsx    # Convex patterns
```

The main `DataDemoScreen.tsx` automatically exports the right version based on your `EXPO_PUBLIC_BACKEND_PROVIDER` setting:

```typescript theme={null}
import { isConvex } from "@/config/env"

export const DataDemoScreen = isConvex
  ? require("./DataDemoScreen.convex").DataDemoScreen
  : require("./DataDemoScreen.supabase").DataDemoScreen
```

### What Each Version Demonstrates

> [tab] Supabase Version
>
> **File:** `screens/DataDemoScreen.supabase.tsx`
>
> Shows the standard **React Query + Supabase SDK** pattern:
>
> ```typescript theme={null}
> import { useQuery, useMutation, useQueryClient } from "@tanstack/react-query"
> import { supabase } from "@/services/supabase"
>
> // Fetching data
> const usePosts = () => {
>   return useQuery({
>     queryKey: ["posts"],
>     queryFn: async () => {
>       const { data, error } = await supabase
>         .from("posts")
>         .select("*")
>         .order("created_at", { ascending: false })
>
>       if (error) throw error
>       return data
>     },
>   })
> }
>
> // Creating data with optimistic updates
> const useCreatePost = () => {
>   const queryClient = useQueryClient()
>
>   return useMutation({
>     mutationFn: async (newPost) => {
>       const { data, error } = await supabase
>         .from("posts")
>         .insert(newPost)
>         .select()
>         .single()
>
>       if (error) throw error
>       return data
>     },
>     onSettled: () => {
>       queryClient.invalidateQueries({ queryKey: ["posts"] })
>     },
>   })
> }
> ```
>
> **Key patterns:**
>
> * React Query for caching and state management
> * Manual cache invalidation after mutations
> * Pull-to-refresh for manual data refresh
> * Optimistic updates for better UX

> [tab] Convex Version
>
> **File:** `screens/DataDemoScreen.convex.tsx`
>
> Shows the **reactive Convex** pattern:
>
> ```typescript theme={null}
> import { useQuery, useMutation } from "@/hooks/convex"
> import { api } from "@convex/_generated/api"
>
> // Reactive query - auto-updates when data changes!
> const posts = useQuery(api.posts.list)
>
> // Mutation - queries auto-invalidate
> const createPost = useMutation(api.posts.create)
>
> const handleCreate = async () => {
>   await createPost({ title, content })
>   // No manual refetch needed - useQuery auto-updates!
> }
> ```
>
> **Key patterns:**
>
> * Queries are reactive (real-time updates)
> * No manual cache invalidation needed
> * No pull-to-refresh needed (data syncs automatically)
> * Type-safe with auto-generated types from schema

### Using the Demo Screen

Navigate to the DataDemoScreen from any authenticated screen:

```typescript theme={null}
navigation.navigate('DataDemo')
```

> [!TIP]
>
> **Copy the patterns, not the screen.** The DataDemoScreen is meant to be a reference. When building your own screens, look at the version matching your backend (`.supabase.tsx` or `.convex.tsx`) and copy the data fetching patterns.

***

## Mock Database

In development without backend credentials, the app uses a mock database:

* **In-memory storage**: Data persists during your session
* **Secure storage fallback**: Critical data (like auth) persists across restarts
* **API compatibility**: Same API as the real backend

See the [Mock Services](https://docs.shipnative.app/development/mock-services.md) guide for details.

***

## Next Steps

> [card] [Authentication](https://docs.shipnative.app/core-features/authentication.md)
>
> Set up user login and signup

> [card] [Realtime](https://docs.shipnative.app/core-features/realtime.md)
>
> Add live updates and presence

> [card] [Database Prompts](https://docs.shipnative.app/prompts/features/database)
>
> AI prompts for schema design

> [card] [Mock Services](https://docs.shipnative.app/development/mock-services.md)
>
> Develop without a backend


This documentation is built and hosted on [Mintlify](https://mintlify.com), a developer documentation platform.