Skip to main content

Migration 0010: User Tables

This migration creates the user-facing tables for the VanSafe mobile app, enabling user profiles, vehicle management, saved routes, and community features.

What This Migration Creates​

Core User Tables​

users​

Extends Supabase Auth with additional user profile information.

Fields:

  • User ID (references auth.users)
  • Username
  • Full name
  • Avatar URL
  • Preferences (JSONB)
  • Timestamps

user_profiles​

Detailed user profile information and settings.

Fields:

  • Profile settings
  • Privacy preferences
  • Notification settings
  • User statistics

Vehicle Management​

vehicles​

User vehicles with specifications for route planning.

Fields:

  • Vehicle type (caravan, motorhome, campervan, etc.)
  • Dimensions (length, width, height, weight)
  • Capabilities (4WD, off-road capable)
  • Towing specifications
  • Custom notes

Usage: Route planning uses vehicle specs to filter hazards and calculate safe routes.

Saved Data​

saved_routes​

User can save planned routes with hazard warnings.

Fields:

  • Route name
  • Origin and destination
  • Waypoints (array of coordinates)
  • Detected hazards
  • Route preferences
  • Sharing settings

saved_locations​

User favorite places and waypoints.

Fields:

  • Location name
  • Coordinates (PostGIS Point)
  • Category (rest area, caravan park, scenic spot, etc.)
  • Personal notes
  • Rating
  • Visit date

Community Features​

user_reported_hazards​

Community-contributed hazard reports.

Fields:

  • Location (PostGIS Point)
  • Hazard type
  • Severity
  • Description
  • Photos (array of URLs)
  • Status (pending, verified, resolved)
  • Verification count

Workflow:

  1. User reports a hazard via mobile app
  2. Other users can verify the report
  3. Once verified by multiple users, it becomes visible in route planning
  4. Admin can review and promote to official database

hazard_verifications​

Users can verify accuracy of community reports.

Fields:

  • Hazard ID (references user_reported_hazards)
  • User ID
  • Verification status (confirmed, disputed, resolved)
  • Comments
  • Timestamp

Running the Migration​

Prerequisites​

  • Supabase project set up
  • Database migrations 0001-0009 already applied
  • Admin access to Supabase SQL Editor

Steps​

  1. Go to your Supabase project dashboard:

    https://app.supabase.com/project/YOUR_PROJECT_ID

  2. Navigate to SQL Editor in the left sidebar

  3. Click New Query

  4. Open the migration file:

    packages/database/supabase/migrations/0010_add_user_tables.sql

  5. Copy the entire contents and paste into the SQL Editor

  6. Click Run or press Cmd/Ctrl + Enter

  7. Verify success - you should see "Success. No rows returned"

Option 2: Supabase CLI​

If you're using the Supabase CLI locally:

cd packages/database
supabase db push

This will automatically apply any unapplied migrations.

Verification​

After running the migration, verify the tables were created:

-- Check that all tables exist
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
  AND table_name IN (
    'users',
    'user_profiles',
    'vehicles',
    'saved_routes',
    'user_reported_hazards',
    'hazard_verifications',
    'saved_locations'
  )
ORDER BY table_name;

You should see all 7 tables listed.

Check Row Level Security​

Verify that RLS is enabled on all tables:

SELECT schemaname, tablename, rowsecurity
FROM pg_tables
WHERE schemaname = 'public'
  AND tablename IN (
    'users',
    'user_profiles',
    'vehicles',
    'saved_routes',
    'user_reported_hazards',
    'hazard_verifications',
    'saved_locations'
  );

All tables should show rowsecurity = true.

Row Level Security Policies​

The migration automatically creates RLS policies for data protection:

Users & Profiles​

  • Read: Users can view their own profile
  • Update: Users can update their own profile
  • Create: Automatic on sign-up

Vehicles​

  • Read: Users can view their own vehicles
  • Create/Update/Delete: Users can manage their own vehicles

Saved Routes​

  • Read: Users can view their own routes
  • Create/Update/Delete: Users can manage their own routes
  • Public sharing: Routes marked as public are visible to all

Saved Locations​

  • Read: Users can view their own locations
  • Create/Update/Delete: Users can manage their own locations

Community Hazards​

  • Read: All users can view verified hazards
  • Create: Authenticated users can report hazards
  • Update: Only hazard creator can edit (within time limit)
  • Delete: Admin only

Hazard Verifications​

  • Read: All users can view verifications
  • Create: Authenticated users can verify hazards
  • Delete: Only verification creator (or admin)

Mobile App Integration​

User Profile​

import { supabase } from '@/lib/supabase';

// Get current user profile
const { data: profile } = await supabase
  .from('user_profiles')
  .select('*')
  .eq('user_id', userId)
  .single();

// Update profile
await supabase
  .from('user_profiles')
  .update({ full_name: 'John Doe' })
  .eq('user_id', userId);

Vehicle Management​

// Add a vehicle
const { data: vehicle } = await supabase
  .from('vehicles')
  .insert({
    user_id: userId,
    name: 'My Caravan',
    vehicle_type: 'caravan',
    length_meters: 6.5,
    width_meters: 2.4,
    height_meters: 3.2,
    weight_kg: 2000,
  })
  .select()
  .single();

// Get user's vehicles
const { data: vehicles } = await supabase
  .from('vehicles')
  .select('*')
  .eq('user_id', userId);

Save Route​

// Save a planned route
const { data: savedRoute } = await supabase
  .from('saved_routes')
  .insert({
    user_id: userId,
    name: 'Sydney to Melbourne',
    origin: 'Sydney, NSW',
    destination: 'Melbourne, VIC',
    waypoints: [
      { lat: -33.8688, lon: 151.2093 },
      { lat: -34.9285, lon: 138.6007 },
      { lat: -37.8136, lon: 144.9631 },
    ],
    detected_hazards: hazardIds,
  })
  .select()
  .single();

Report Hazard​

// Report a new hazard
const { data: hazard } = await supabase
  .from('user_reported_hazards')
  .insert({
    reported_by: userId,
    geometry: `POINT(${lon} ${lat})`,
    hazard_type: 'low_clearance',
    severity: 'high',
    description: 'Low bridge - 2.8m clearance',
    photo_urls: ['https://...'],
  })
  .select()
  .single();

// Verify someone else's hazard report
await supabase
  .from('hazard_verifications')
  .insert({
    hazard_id: hazardId,
    user_id: userId,
    verification_status: 'confirmed',
    comments: 'Confirmed - just drove past it',
  });

Troubleshooting​

Migration Fails​

Error: "relation already exists"

The tables might have been created already. Check if they exist:

SELECT table_name FROM information_schema.tables
WHERE table_schema = 'public' AND table_name LIKE 'user%';

If tables exist, the migration has likely already been run.

Error: "column does not exist"

A previous migration might be missing. Check which migrations have been applied:

SELECT * FROM _migrations ORDER BY version;

Ensure migrations 0001-0009 are present.

RLS Policies Not Working​

If users can't access their own data:

  1. Check if RLS is enabled:

    SELECT tablename, rowsecurity FROM pg_tables
    WHERE schemaname = 'public' AND tablename = 'user_profiles';

  2. Check policies exist:

    SELECT * FROM pg_policies WHERE tablename = 'user_profiles';

  3. Verify user is authenticated:

    const { data: { user } } = await supabase.auth.getUser();
    console.log('User ID:', user?.id);

Performance Issues​

If queries are slow, ensure indexes exist:

-- Check indexes on user_profiles
SELECT indexname, indexdef
FROM pg_indexes
WHERE tablename = 'user_profiles';

The migration should have created indexes on:

  • user_id for all user-related tables
  • geometry (GIST) for spatial queries
  • created_at for timestamp ordering

Next Steps​

After this migration succeeds:

  1. Test user authentication - Sign up and sign in work correctly
  2. Create test data - Add a vehicle and saved route
  3. Test RLS policies - Verify users can only see their own data
  4. Implement mobile app features - Use the new tables in your React Native app
  5. Set up admin panel - For reviewing community hazard reports

Migration File Location​

packages/database/supabase/migrations/0010_add_user_tables.sql