Database Migrations

Overview

The Transport Logistics platform uses Supabase for database management, which provides built-in support for PostgreSQL migrations. Migrations are SQL files that define incremental changes to your database schema.

All migration files are stored in the supabase/migrations/ directory and are automatically applied in chronological order based on their timestamp prefixes.

Migration Files

The project currently includes these migrations:

Row Level Security

File: 20250626084408-*.sqlSets up RLS policies for all tables with role-based access control

Unique Constraints

File: 20250710103823-*.sqlAdds unique constraint to vehicle numbers

Migration File Structure

Naming Convention

Migration files follow this pattern:

[TIMESTAMP]-[UUID].sql

Example: 20250626084408-40640dda-5b51-48b2-b2ac-0a64dc5022c0.sql

Timestamp: YYYYMMDDHHMMSS format ensures chronological ordering
UUID: Unique identifier prevents naming conflicts
Extension: .sql for SQL migration files

Directory Structure

supabase/
├── config.toml              # Supabase project configuration
├── migrations/              # Database migrations
│   ├── 20250626084408-*.sql # Initial RLS setup
│   └── 20250710103823-*.sql # Vehicle constraints
└── functions/               # Database functions and triggers
    ├── get_user_email.sql
    ├── delete_user.sql
    └── delete_user_rpc.sql

Existing Migrations

Migration 1: Row Level Security (RLS)

File: 20250626084408-40640dda-5b51-48b2-b2ac-0a64dc5022c0.sql This migration establishes comprehensive security policies:

Overview
Security Function
Policy Examples

Enables Row Level Security on all public tables:

vehicles
shipments
packages
materials
profiles
user_settings
routes
transporters

Creates a helper function to get the current user’s role:

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.

Admin Access (full control):

CREATE POLICY "Admin users can manage vehicles" ON public.vehicles
  FOR ALL TO authenticated 
  USING (public.get_current_user_role() = 'admin')
  WITH CHECK (public.get_current_user_role() = 'admin');

User Access (read-only):

CREATE POLICY "Authenticated users can view vehicles" ON public.vehicles
  FOR SELECT TO authenticated USING (true);

Owner Access (own data only):

CREATE POLICY "Users can view their own profile" ON public.profiles
  FOR SELECT TO authenticated USING (auth.uid() = id);

Migration 2: Vehicle Number Uniqueness

File: 20250710103823-11aa3e41-d45b-4c07-877d-ef8adc04984c.sql Enforces unique vehicle numbers across the system:

ALTER TABLE public.vehicles 
ADD CONSTRAINT vehicles_vehicle_number_unique UNIQUE (vehicle_number);

This constraint prevents duplicate vehicle registrations and ensures data integrity for vehicle tracking.

Creating New Migrations

Identify Database Changes

Determine what schema changes you need:

New tables or columns
Modified constraints or indexes
Updated RLS policies
New database functions

Generate Migration File

Create a new migration file with timestamp and UUID:

# Manual creation
touch supabase/migrations/$(date +%Y%m%d%H%M%S)-$(uuidgen).sql

Or use Supabase CLI (recommended):

# Install Supabase CLI if not already installed
npm install -g supabase

# Create new migration
supabase migration new add_driver_table

This generates: supabase/migrations/YYYYMMDDHHMMSS_add_driver_table.sql

Write Migration SQL

Add your SQL commands to the migration file:

-- Create drivers table
CREATE TABLE public.drivers (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES auth.users(id) ON DELETE CASCADE,
  license_number TEXT UNIQUE NOT NULL,
  full_name TEXT NOT NULL,
  phone TEXT,
  status TEXT DEFAULT 'active',
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Enable RLS
ALTER TABLE public.drivers ENABLE ROW LEVEL SECURITY;

-- Add policies
CREATE POLICY "Authenticated users can view drivers" ON public.drivers
  FOR SELECT TO authenticated USING (true);

CREATE POLICY "Admin users can manage drivers" ON public.drivers
  FOR ALL TO authenticated 
  USING (public.get_current_user_role() = 'admin')
  WITH CHECK (public.get_current_user_role() = 'admin');

-- Add indexes
CREATE INDEX idx_drivers_user_id ON public.drivers(user_id);
CREATE INDEX idx_drivers_status ON public.drivers(status);

Always enable RLS on new tables and create appropriate policies. Forgetting RLS leaves data exposed!

Test Migration Locally

Apply the migration to your local Supabase instance:

# If using Supabase CLI
supabase db reset

# Or apply just the new migration
supabase migration up

Verify the changes in your local database:

# Connect to local database
supabase db

# Check table structure
\d public.drivers

# Check policies
\dp public.drivers

Commit Migration

Add the migration file to version control:

git add supabase/migrations/
git commit -m "Add drivers table migration"
git push

Applying Migrations

Local Development

If you’re using Supabase CLI with local development:

# Apply all pending migrations
supabase migration up

# This runs all .sql files in migrations/ that haven't been applied

Remote Supabase Project

For hosted Supabase projects:

Link Your Project

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

Find your project ref in the Supabase dashboard URL: https://app.supabase.com/project/[your-project-ref]

Push Migrations

supabase db push

This applies all local migrations to the remote database.

Verify Changes

Check the Supabase dashboard:Table Editor → View new tables and columnsDatabase → Migrations → See applied migrations

Manual Application

If not using Supabase CLI, run migrations manually:

Access SQL Editor

Go to Supabase Dashboard → SQL Editor

Copy Migration SQL

Open the migration file locally and copy its contents

Execute SQL

Paste the SQL into the editor and click Run

Verify Execution

Check for errors and verify the changes in Table Editor

Migration Best Practices

Always Use Transactions

Wrap migrations in transactions for atomicity:

BEGIN;
-- Your changes here
COMMIT;

Make Idempotent

Use IF NOT EXISTS and IF EXISTS:

CREATE TABLE IF NOT EXISTS drivers (...);
ALTER TABLE vehicles ADD COLUMN IF NOT EXISTS driver_id UUID;

Add Comments

Document complex logic:

-- This policy allows drivers to view their assigned shipments
CREATE POLICY ...

Test Rollbacks

Create corresponding down migrations:

-- Down migration: 20250710103823_down.sql
ALTER TABLE public.vehicles 
DROP CONSTRAINT IF EXISTS vehicles_vehicle_number_unique;

Security Checklist

Before applying migrations:

Enable RLS

✅ All new tables have ENABLE ROW LEVEL SECURITY

Create Policies

✅ Appropriate SELECT, INSERT, UPDATE, DELETE policies

Test Access

✅ Verify users can only access permitted data

Review Functions

✅ Security definer functions are properly scoped

Database Functions

The project includes custom PostgreSQL functions in supabase/functions/:

get_user_email.sql

Retrieves user email addresses (likely for admin features)

delete_user.sql & delete_user_rpc.sql

Provides safe user deletion with cascade handling

These are database functions (PL/pgSQL), not Edge Functions (TypeScript). They run in PostgreSQL and can be called from your application.

Troubleshooting

Migration fails with constraint error

Problem: Existing data violates new constraintSolution:

Add data cleanup before the constraint:

-- Remove duplicates before adding unique constraint
DELETE FROM vehicles v1
WHERE EXISTS (
  SELECT 1 FROM vehicles v2
  WHERE v2.vehicle_number = v1.vehicle_number
  AND v2.id < v1.id
);

ALTER TABLE vehicles ADD CONSTRAINT vehicles_vehicle_number_unique UNIQUE (vehicle_number);

Or make the migration less strict initially

RLS policies are too restrictive

Problem: Users can’t access data they should be able toSolution:

Test policies in SQL editor:

-- Test as specific user
SET LOCAL ROLE authenticated;
SET LOCAL "request.jwt.claims" TO '{"sub": "user-uuid"}';
SELECT * FROM vehicles;

Temporarily disable RLS for debugging (development only):

ALTER TABLE vehicles DISABLE ROW LEVEL SECURITY;

Review policy logic and adjust as needed

Migration applied in wrong order

Problem: Timestamps don’t reflect dependency orderSolution:

Rename migration files to correct the order
Or create a new migration that fixes the issue
Never modify already-applied migrations in production

Can't connect to run migrations

Problem: Supabase CLI can’t reach databaseSolution:

Check your internet connection
Verify project ref: supabase projects list
Re-link project: supabase link --project-ref your-ref
Check database pooler settings in Supabase dashboard

Schema Management Tips

Version Control

✅ DO commit all migration files
✅ DO include descriptive migration names
❌ DON’T modify migrations after they’re applied
❌ DON’T delete old migrations

Production Safety

Use staging environment to test migrations first
Schedule migrations during low-traffic periods
Have rollback plan ready
Monitor application errors after deployment
Keep database backups before major changes

Performance Considerations

Add indexes for frequently queried columns
Use partial indexes for filtered queries

Consider concurrent index creation for large tables:

CREATE INDEX CONCURRENTLY idx_name ON table(column);

Analyze query plans before and after schema changes

Next Steps

Local Setup

Complete development environment setup

Environment Variables

Configure Supabase connection

Supabase Docs

Official Supabase migration guide

PostgreSQL Docs

PostgreSQL documentation

Architecture

Development

Deployment

Overview

Migration Files

Row Level Security

Unique Constraints

Migration File Structure

Naming Convention

Directory Structure

Existing Migrations

Migration 1: Row Level Security (RLS)

Migration 2: Vehicle Number Uniqueness

Creating New Migrations

Applying Migrations

Local Development

Remote Supabase Project

Manual Application

Migration Best Practices

Always Use Transactions

Make Idempotent

Add Comments

Test Rollbacks

Security Checklist

Database Functions

get_user_email.sql

delete_user.sql & delete_user_rpc.sql

Troubleshooting

Schema Management Tips

Version Control

Production Safety

Performance Considerations

Next Steps

Local Setup

Environment Variables

Supabase Docs

PostgreSQL Docs

Build docs developers (and LLMs) love

Architecture

Development

Deployment

​Overview

​Migration Files

Row Level Security

Unique Constraints

​Migration File Structure

​Naming Convention

​Directory Structure

​Existing Migrations

​Migration 1: Row Level Security (RLS)

​Migration 2: Vehicle Number Uniqueness

​Creating New Migrations

​Applying Migrations

​Local Development

​Remote Supabase Project

​Manual Application

​Migration Best Practices

Always Use Transactions

Make Idempotent

Add Comments

Test Rollbacks

​Security Checklist

​Database Functions

​get_user_email.sql

​delete_user.sql & delete_user_rpc.sql

​Troubleshooting

​Schema Management Tips

​Version Control

​Production Safety

​Performance Considerations

​Next Steps

Local Setup

Environment Variables

Supabase Docs

PostgreSQL Docs

Build docs developers (and LLMs) love

Overview

Migration Files

Migration File Structure

Naming Convention

Directory Structure

Existing Migrations

Migration 1: Row Level Security (RLS)

Migration 2: Vehicle Number Uniqueness

Creating New Migrations

Applying Migrations

Local Development

Remote Supabase Project

Manual Application

Migration Best Practices

Security Checklist

Database Functions

get_user_email.sql

delete_user.sql & delete_user_rpc.sql

Troubleshooting

Schema Management Tips

Version Control

Production Safety

Performance Considerations

Next Steps