> ## 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.

<Info>
  **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](/development/state-management#storage-mmkv-vs-supabase) for details.
</Info>

## Database Schema

<Tabs>
  <Tab title="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.
    </Tip>
  </Tab>

  <Tab title="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.
    </Tip>
  </Tab>
</Tabs>

***

## Security

<Tabs>
  <Tab title="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.
    </Warning>
  </Tab>

  <Tab title="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.
    </Warning>
  </Tab>
</Tabs>

***

## Managing the Schema

<Tabs>
  <Tab title="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);
    ```

    <Info>
      See the complete migration guide at `supabase/migrations/README.md` in your project.
    </Info>

    ### 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>

  <Tab title="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.
    </Tip>
  </Tab>
</Tabs>

***

## Seed Data

<Tabs>
  <Tab title="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>

  <Tab title="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.
    </Tip>
  </Tab>
</Tabs>

***

## 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

<Tabs>
  <Tab title="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>

  <Tab title="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
  </Tab>
</Tabs>

### 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.
</Tip>

***

## 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](/development/mock-services) guide for details.

***

## Next Steps

<CardGroup cols={2}>
  <Card title="Authentication" icon="lock" href="/core-features/authentication">
    Set up user login and signup
  </Card>

  <Card title="Realtime" icon="bolt" href="/core-features/realtime">
    Add live updates and presence
  </Card>

  <Card title="Database Prompts" icon="wand-magic-sparkles" href="/prompts/features/database">
    AI prompts for schema design
  </Card>

  <Card title="Mock Services" icon="flask" href="/development/mock-services">
    Develop without a backend
  </Card>
</CardGroup>