Backend & Database

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

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 for details.

Database Schema

Supabase
Convex

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`

Automatic Sync: The profiles and user_preferences tables are automatically created via triggers whenever a new user signs up.

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`

Type Safety: Convex schemas provide end-to-end TypeScript types. Your queries and mutations are fully typed automatically.

Security

Supabase RLS
Convex Functions

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

-- 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);

Always enable RLS: When creating new tables, run ALTER TABLE name ENABLE ROW LEVEL SECURITY; and add policies.

Convex uses function-level security. The boilerplate includes security helpers in convex/lib/security.ts:

Security Helpers

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)

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

Supabase
Convex

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

# Create a new migration file
supabase migration new add_todos_table

# This creates: supabase/migrations/YYYYMMDDHHMMSS_add_todos_table.sql

Apply Migrations

# Link your project (one-time)
supabase link --project-ref your-project-ref

# Apply all migrations
supabase db push

Migration Best Practices

Always use IF NOT EXISTS - Makes migrations safe to run multiple times
Drop policies before recreating - Prevents “already exists” errors
Enable RLS on all user tables - Security best practice
Add indexes for foreign keys - Performance optimization

Example migration:

-- 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);

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:

ALTER TABLE public.[table_name] ADD COLUMN [column_name] [type] DEFAULT [value];

Adding New Tables

Edit convex/schema.ts:

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:

npx convex dev  # Development - auto-syncs
npx convex deploy  # Production - deploys changes

Indexes: Always add indexes for fields you query frequently. Use compound indexes for multi-field queries.

Seed Data

Supabase
Convex

Use the SQL Editor to insert test data:

INSERT INTO profiles (id, first_name, last_name)
VALUES ('user-id-here', 'Demo', 'User');

Or import from a file:

npx supabase db seed

The boilerplate includes seed utilities in convex/seed.ts:

Seed Commands

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

export const run = internalMutation({
  handler: async (ctx) => {
    // Add custom seed logic
    await ctx.db.insert("products", {
      name: "Demo Product",
      price: 99,
      createdAt: Date.now(),
    })
  },
})

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:

import { isConvex } from "@/config/env"

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

What Each Version Demonstrates

Supabase Version
Convex Version

File: screens/DataDemoScreen.supabase.tsxShows the standard React Query + Supabase SDK pattern:

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

File: screens/DataDemoScreen.convex.tsxShows the reactive Convex pattern:

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:

navigation.navigate('DataDemo')

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 guide for details.

Next Steps

Authentication

Set up user login and signup

Realtime

Add live updates and presence

Database Prompts

AI prompts for schema design

Mock Services

Develop without a backend

Introduction

Getting Started

Core Features

Development

Deployment

Support

Database Schema

Core Tables

Core Tables

Security

Security Helpers

Available Helpers

Managing the Schema

Using Migrations (Recommended)

Create a New Migration

Apply Migrations

Migration Best Practices

Manual Schema Changes (Not Recommended)

Adding New Tables

Schema Changes

Seed Data

Seed Commands

Demo Data Included

Custom Seed Data

Reference Implementation: DataDemoScreen

How It Works

What Each Version Demonstrates

Using the Demo Screen

Mock Database

Next Steps

Authentication

Realtime

Database Prompts

Mock Services

​Database Schema

​Core Tables

​Core Tables

​Security

​Security Helpers

​Available Helpers

​Managing the Schema

​Using Migrations (Recommended)

​Create a New Migration

​Apply Migrations

​Migration Best Practices

​Manual Schema Changes (Not Recommended)

​Adding New Tables

​Schema Changes

​Seed Data

​Seed Commands

​Demo Data Included

​Custom Seed Data

​Reference Implementation: DataDemoScreen

​How It Works

​What Each Version Demonstrates

​Using the Demo Screen

​Mock Database

​Next Steps

Authentication

Realtime

Database Prompts

Mock Services

Database Schema

Core Tables

Core Tables

Security

Security Helpers

Available Helpers

Managing the Schema

Using Migrations (Recommended)

Create a New Migration

Apply Migrations

Migration Best Practices

Manual Schema Changes (Not Recommended)

Adding New Tables

Schema Changes

Seed Data

Seed Commands

Demo Data Included

Custom Seed Data

Reference Implementation: DataDemoScreen

How It Works

What Each Version Demonstrates

Using the Demo Screen

Mock Database

Next Steps