Supabase Setup

This guide walks you through setting up Supabase as your backend infrastructure, including database configuration, authentication, and Row Level Security (RLS) policies.

Prerequisites

A Supabase account (sign up at supabase.com)
Supabase CLI installed (optional, for local development)
Basic understanding of PostgreSQL and SQL

Create a Supabase Project

Go to app.supabase.com and sign in with your account.

Create New Project

Click New Project and provide:

Project Name: transport-logistics (or your preferred name)
Database Password: A strong, secure password (save this securely)
Region: Choose the region closest to your users
Pricing Plan: Select Free tier for development or Pro for production

Wait for Provisioning

Supabase will provision your project. This typically takes 1-2 minutes. Once ready, you’ll see your project dashboard.

Note Your Project Credentials

In the project dashboard, navigate to Settings > API:

Project URL: https://xxxxx.supabase.co
Anon (public) Key: Your public API key
Service Role Key: Your private API key (keep this secret)

Never expose the Service Role Key in client-side code. Only use the Anon Key in your application.

Database Schema Setup

The Transport Logistics application uses the following main tables:

profiles - User profiles with role-based access
vehicles - Fleet vehicle information
shipments - Shipment tracking data
packages - Package details
materials - Material inventory
routes - Delivery routes
transporters - Transporter information
user_settings - User preferences

Running Migrations

You have two options for setting up your database schema:

Via Dashboard (Recommended)
Via Supabase CLI

Navigate to SQL Editor

In your Supabase dashboard, go to SQL Editor.

Create Tables and Functions

Copy and execute your migration files in order. The application includes:

Initial Schema: Create all tables, relationships, and functions
RLS Policies: Enable Row Level Security (see next section)
Unique Constraints: Add data integrity rules

Verify Schema

Navigate to Database > Tables to verify all tables were created successfully.

Install Supabase CLI

npm install -g supabase

Link to Your Project

supabase link --project-ref your-project-ref

Find your project ref in Settings > General > Reference ID.

Run Migrations

supabase db push

This will apply all migrations from the supabase/migrations/ directory.

Verify Migrations

supabase db diff

Should show no differences if migrations applied successfully.

Row Level Security (RLS) Configuration

Row Level Security ensures users can only access data they’re authorized to see. The application implements role-based access control.

Understanding RLS Policies

The application uses three main user roles:

Admin: Full access to all resources
Manager: Can view and manage assigned resources
User: Limited access to assigned packages and shipments

Enable RLS on Tables

Navigate to Table Editor

Go to Database > Tables in your Supabase dashboard.

Enable RLS

For each table (vehicles, shipments, packages, materials, profiles, user_settings, routes, transporters):

Click on the table name
Go to the Policies tab
Click Enable RLS if not already enabled

Create Policies

Execute the RLS policy migration file (20250626084408-*.sql) which creates:

View policies: Control who can SELECT data
Insert policies: Control who can INSERT data
Update policies: Control who can UPDATE data
Delete policies: Control who can DELETE data

Key RLS Policies

Vehicles, Shipments, Routes, Materials, Transporters

View: All authenticated users
Manage: Admin users only

Packages

View: Admin users, package creators, or assigned users
Manage: Admin users and package creators

Profiles

View: Users can view their own profile; admins can view all
Update: Users can update their own profile; admins can manage all

User Settings

All Operations: Users can only access their own settings

Helper Function

The application uses a security definer function to check user roles:

CREATE OR REPLACE FUNCTION public.get_current_user_role()
RETURNS TEXT AS $$
  SELECT role FROM public.profiles WHERE id = auth.uid();
$$ LANGUAGE SQL SECURITY DEFINER STABLE;

This function is used in RLS policies to enforce role-based access control.

Authentication Configuration

Enable Auth Providers

Navigate to Authentication > Providers:

Email: Enabled by default (recommended)
Magic Link: Optional, for passwordless authentication
OAuth Providers: Configure Google, GitHub, etc. (optional)

Configure Email Templates

Go to Authentication > Email Templates and customize:

Confirm Signup: Welcome email with verification link
Magic Link: Passwordless login email
Change Email Address: Email change confirmation
Reset Password: Password reset link

Update templates with your brand name and customize messaging for better user experience.

Set URL Configuration

Go to Authentication > URL Configuration:

Site URL: Your application URL (e.g., https://your-domain.com)
Redirect URLs: Add allowed callback URLs:
- http://localhost:8080/** (for local development)
- https://your-domain.com/** (for production)
- https://preview-*.vercel.app/** (for preview deployments)

Configure User Management

Go to Authentication > Settings:

Enable Email Confirmations: Recommended for production
Disable Sign-ups: Enable if you want invite-only access
Session Duration: Configure token expiry (default: 3600 seconds)

Edge Functions (Optional)

The application includes Edge Functions for server-side operations:

Available Functions

create-user: Server-side user creation with JWT verification
change-user-password: Password management with authentication
delete_user: User deletion with cleanup
get_user_email: Retrieve user email by ID

Deploying Edge Functions

Navigate to Functions Directory

cd supabase/functions

Deploy Function

supabase functions deploy create-user
supabase functions deploy change-user-password

Set Secrets

If your functions require secrets:

supabase secrets set API_KEY=your-secret-key

Test Function

supabase functions invoke create-user --data '{"email":"[email protected]"}'

Getting API Keys

Navigate to API Settings

Go to Settings > API in your Supabase dashboard.

Copy Keys

You’ll need two keys for your application:For Client-Side (Application):

VITE_SUPABASE_URL=https://xxxxx.supabase.co
VITE_SUPABASE_ANON_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

For Server-Side/Admin Operations (keep secret):

SUPABASE_SERVICE_ROLE_KEY=eyJhbGciOiJIUzI1NiIsInR5cCI6IkpXVCJ9...

Configure Environment

Add these to your environment:

Local development: Create .env.local file
Production: Add to your deployment platform’s environment variables

Never commit .env files with real credentials to version control. Use .env.example with placeholder values.

Connecting to Production Database

For direct database access (migrations, backups, etc.):

Get Connection String

Navigate to Settings > Database and copy the connection string:

postgresql://postgres:[YOUR-PASSWORD]@db.xxxxx.supabase.co:5432/postgres

Connect with psql

psql "postgresql://postgres:[YOUR-PASSWORD]@db.xxxxx.supabase.co:5432/postgres"

Or Use Database URL in Tools

Use the connection string with tools like:

pgAdmin
DBeaver
DataGrip
TablePlus

Database Backups

Supabase automatically backs up your database:

Free Tier: Daily backups, 7-day retention
Pro Tier: Daily backups, 30-day retention, point-in-time recovery

Manual Backup

Using Supabase CLI

supabase db dump -f backup.sql

Using pg_dump

pg_dump "postgresql://postgres:[PASSWORD]@db.xxxxx.supabase.co:5432/postgres" > backup.sql

Restore from Backup

psql "postgresql://postgres:[PASSWORD]@db.xxxxx.supabase.co:5432/postgres" < backup.sql

Security Best Practices

Use RLS Policies

Always enable Row Level Security on all tables. Never disable RLS in production.

Rotate Keys Regularly

Regenerate API keys periodically and after any suspected security breach.

Monitor Logs

Review authentication logs and API usage regularly in the Supabase dashboard.

Limit Permissions

Grant minimum required permissions. Use service role key only server-side.

Monitoring and Performance

Enable Monitoring

View Database Usage

Navigate to Settings > Usage to monitor:

Database size
API requests
Bandwidth usage
Authentication users

Check Database Performance

Go to Database > Performance to:

Identify slow queries
View index usage
Monitor connection pool

Set Up Alerts

Configure alerts for:

Database size approaching limit
High error rates
Slow query performance

Optimize Performance

Add Indexes: Create indexes on frequently queried columns
Use Connection Pooling: Enable Supavisor for connection management
Optimize Queries: Use the SQL Editor to analyze and optimize slow queries
Enable Caching: Use Supabase’s built-in caching for read-heavy operations

Troubleshooting

Connection Errors

Verify API keys are correct and not expired
Check if IP is allowed (Supabase allows all IPs by default)
Ensure network/firewall isn’t blocking requests

RLS Policy Errors

Check user has correct role in profiles table
Verify RLS policies are created and enabled
Test policies using the SQL Editor with different user contexts

Migration Failures

Run migrations in order (check timestamps in filenames)
Verify no conflicting table/column names
Check PostgreSQL logs in Supabase dashboard

Authentication Issues

Verify redirect URLs are configured correctly
Check email templates are set up
Ensure SMTP settings are correct (if using custom email)

Next Steps

Deployment Guide

Deploy your application to production

Database Schema

Explore the database tables and relationships

Architecture

Development

Deployment

Prerequisites

Create a Supabase Project

Database Schema Setup

Running Migrations

Row Level Security (RLS) Configuration

Understanding RLS Policies

Enable RLS on Tables

Key RLS Policies

Helper Function

Authentication Configuration

Edge Functions (Optional)

Available Functions

Deploying Edge Functions

Getting API Keys

Connecting to Production Database

Database Backups

Manual Backup

Security Best Practices

Use RLS Policies

Rotate Keys Regularly

Monitor Logs

Limit Permissions

Monitoring and Performance

Enable Monitoring

Optimize Performance

Troubleshooting

Connection Errors

RLS Policy Errors

Migration Failures

Authentication Issues

Next Steps

Deployment Guide

Database Schema

Build docs developers (and LLMs) love

Architecture

Development

Deployment

​Prerequisites

​Create a Supabase Project

​Database Schema Setup

​Running Migrations

​Row Level Security (RLS) Configuration

​Understanding RLS Policies

​Enable RLS on Tables

​Key RLS Policies

​Helper Function

​Authentication Configuration

​Edge Functions (Optional)

​Available Functions

​Deploying Edge Functions

​Getting API Keys

​Connecting to Production Database

​Database Backups

​Manual Backup

​Security Best Practices

Use RLS Policies

Rotate Keys Regularly

Monitor Logs

Limit Permissions

​Monitoring and Performance

​Enable Monitoring

​Optimize Performance

​Troubleshooting

​Connection Errors

​RLS Policy Errors

​Migration Failures

​Authentication Issues

​Next Steps

Deployment Guide

Database Schema

Build docs developers (and LLMs) love

Prerequisites

Create a Supabase Project

Database Schema Setup

Running Migrations

Row Level Security (RLS) Configuration

Understanding RLS Policies

Enable RLS on Tables

Key RLS Policies

Helper Function

Authentication Configuration

Edge Functions (Optional)

Available Functions

Deploying Edge Functions

Getting API Keys

Connecting to Production Database

Database Backups

Manual Backup

Security Best Practices

Monitoring and Performance

Enable Monitoring

Optimize Performance

Troubleshooting

Connection Errors

RLS Policy Errors

Migration Failures

Authentication Issues

Next Steps