Vanroute Mobile App - Integration Plan with Existing Database

Date: October 15, 2025 Status: Ready for Implementation

---

Executive Summary

We have a comprehensive design document from Claude Opus and a highly advanced backend database already built. This document outlines how to integrate the mobile app design with our existing infrastructure.

What We Already Have ✅

Our database is significantly more advanced than the design doc proposes:

Feature	Design Doc	Our Implementation	Status
Road Hazards	User-reported only	57,742 OSM hazards + user reports	✅ Better
Route Segments	Basic polylines	420,083 detailed segments with geometry	✅ Better
Elevation Data	Not included	2.97M elevation profiles with grades	✅ Better
Towns	Not included	8,142 towns with boundaries	✅ Better
Amenities	Not included	56,588 amenities (fuel, toilets, etc.)	✅ Better
Mobile Coverage	Not included	In progress (Phase 3)	🔄 Better
Weather	Not included	Framework ready (Phase 2)	🔄 Better
User Management	Included	Need to add	❌ Need
Saved Routes	Included	Need to add	❌ Need
Vehicles	Included	Need to add	❌ Need

Our Competitive Advantage

We have professional-grade data that competitors don't:

1. ✅ Complete Australian road network (420K segments)
2. ✅ Verified OSM hazards (57K records)
3. ✅ Elevation profiles for grades (2.97M records)
4. ✅ Town boundaries and amenities
5. ✅ Real-time traffic incidents (Phase 1)
6. ✅ Fuel stations and rest areas

This is a commercial-grade database worth $$$ - we just need the mobile app frontend!

---

Architecture Overview

┌─────────────────────────────────────────────────────────────┐
│                    MOBILE APP (React Native)                 │
│  Following Claude Opus Design Document                       │
│  • Maps • Navigation • User Profiles • Subscriptions         │
└─────────────────────────────────────────────────────────────┘
                            ↓ API Calls
┌─────────────────────────────────────────────────────────────┐
│                    SUPABASE API LAYER                        │
│  • Authentication • Real-time • Storage • Functions          │
└─────────────────────────────────────────────────────────────┘
                            ↓ Queries
┌─────────────────────────────────────────────────────────────┐
│                 EXISTING DATABASE (PostgreSQL)               │
│                                                              │
│  CORE DATA (Already Built):                                 │
│  ├─ road_hazards (57,742) - OSM verified hazards           │
│  ├─ route_segments (420,083) - Full road network           │
│  ├─ road_elevations (2.97M) - Grade calculations           │
│  ├─ towns (8,142) - Town boundaries                        │
│  ├─ amenities (56,588) - Fuel, toilets, etc.              │
│  ├─ rest_areas - Official rest stops                       │
│  └─ fuel_stations - Service stations                       │
│                                                              │
│  USER DATA (Need to Add):                                   │
│  ├─ users - User accounts                                   │
│  ├─ profiles - User preferences                             │
│  ├─ vehicles - User vehicle specs                          │
│  ├─ saved_routes - User saved routes                       │
│  ├─ user_reported_hazards - Community reports              │
│  └─ hazard_verifications - Community confirmations         │
└─────────────────────────────────────────────────────────────┘

---

Database Schema Integration

Existing Tables (Keep & Enhance)

Our existing tables are production-ready and more comprehensive than the design doc:

-- ✅ ALREADY EXISTS - OSM verified hazards
CREATE TABLE road_hazards (
  id UUID PRIMARY KEY,
  hazard_type hazard_type, -- height_restriction, low_bridge, etc.
  severity hazard_severity, -- critical, high, medium, low
  latitude DECIMAL(10, 7),
  longitude DECIMAL(10, 7),
  clearance_meters DECIMAL(4, 2),
  width_meters DECIMAL(4, 2),
  affects_caravans BOOLEAN,
  description TEXT,
  data_source TEXT, -- 'openstreetmap', 'user_reported'
  osm_id BIGINT, -- Original OSM ID
  -- ... more fields
);

-- ✅ ALREADY EXISTS - Detailed road network
CREATE TABLE route_segments (
  id UUID PRIMARY KEY,
  geometry geometry(LineString, 4326),
  highway_type TEXT,
  surface TEXT,
  caravan_suitability caravan_suitability,
  fourwd_required BOOLEAN,
  length_meters DECIMAL(10, 2),
  -- ... more fields
);

-- ✅ ALREADY EXISTS - Elevation and grades
CREATE TABLE road_elevations (
  id UUID PRIMARY KEY,
  road_segment_id UUID,
  start_elevation_meters DOUBLE PRECISION,
  end_elevation_meters DOUBLE PRECISION,
  max_grade_percent DOUBLE PRECISION,
  grade_category TEXT, -- flat, gentle, moderate, steep, very_steep
  -- ... more fields
);

-- ✅ ALREADY EXISTS - Amenities
CREATE TABLE amenities (
  id UUID PRIMARY KEY,
  category amenity_category,
  name TEXT,
  latitude DECIMAL(10, 7),
  longitude DECIMAL(10, 7),
  facilities JSONB,
  opening_hours TEXT,
  -- ... more fields
);

New Tables (Add for User Features)

Following the design doc, we need to add user-centric tables:

-- NEW: User accounts (use Supabase Auth)
CREATE TABLE users (
  id UUID PRIMARY KEY REFERENCES auth.users(id),
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- NEW: User profiles
CREATE TABLE profiles (
  user_id UUID PRIMARY KEY REFERENCES users(id),
  display_name VARCHAR(100),
  avatar_url TEXT,
  phone VARCHAR(20),
  subscription_tier VARCHAR(20) DEFAULT 'free',
  subscription_expiry DATE,
  trust_score INTEGER DEFAULT 50,
  hazards_reported INTEGER DEFAULT 0,
  verifications_count INTEGER DEFAULT 0,
  preferences JSONB,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- NEW: User vehicles
CREATE TABLE vehicles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  name VARCHAR(100) NOT NULL,
  type VARCHAR(20), -- caravan, motorhome, camper, trailer
  make VARCHAR(50),
  model VARCHAR(50),
  year INTEGER,
  specs JSONB, -- { height, weight, length, width, gcm, atm }
  is_default BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- NEW: Saved routes
CREATE TABLE saved_routes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id),
  name VARCHAR(255),
  origin_location JSONB,
  destination_location JSONB,
  waypoints JSONB,
  route_geometry geometry(LineString, 4326),
  distance_meters INTEGER,
  duration_seconds INTEGER,
  vehicle_id UUID REFERENCES vehicles(id),
  preferences JSONB,
  hazards_on_route JSONB, -- Cache of hazards
  created_at TIMESTAMPTZ DEFAULT NOW(),
  last_used TIMESTAMPTZ
);

-- NEW: User-reported hazards (supplements OSM data)
CREATE TABLE user_reported_hazards (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  reported_by UUID REFERENCES users(id),
  hazard_type VARCHAR(20),
  latitude DECIMAL(10, 8),
  longitude DECIMAL(11, 8),
  restrictions JSONB, -- { maxHeight, maxWidth, maxWeight }
  description TEXT,
  photos TEXT[], -- Array of photo URLs
  verification_count INTEGER DEFAULT 0,
  status VARCHAR(20) DEFAULT 'pending', -- pending, verified, rejected
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- NEW: Hazard verifications (for both OSM and user-reported)
CREATE TABLE hazard_verifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  hazard_id UUID, -- Can reference road_hazards OR user_reported_hazards
  hazard_source VARCHAR(20), -- 'osm' or 'user_reported'
  verified_by UUID REFERENCES users(id),
  verification_type VARCHAR(20), -- confirm, dispute, update
  status VARCHAR(20), -- still_present, resolved, modified
  notes TEXT,
  photo_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX idx_vehicles_user ON vehicles(user_id);
CREATE INDEX idx_saved_routes_user ON saved_routes(user_id);
CREATE INDEX idx_user_hazards_location ON user_reported_hazards(latitude, longitude);
CREATE INDEX idx_verifications_hazard ON hazard_verifications(hazard_id, hazard_source);

---

API Layer Design

Supabase Client Configuration

// src/services/api/supabase.js
import { createClient } from '@supabase/supabase-js';
import AsyncStorage from '@react-native-async-storage/async-storage';

const supabaseUrl = process.env.EXPO_PUBLIC_SUPABASE_URL;
const supabaseAnonKey = process.env.EXPO_PUBLIC_SUPABASE_ANON_KEY;

export const supabase = createClient(supabaseUrl, supabaseAnonKey, {
  auth: {
    storage: AsyncStorage,
    autoRefreshToken: true,
    persistSession: true,
    detectSessionInUrl: false,
  },
});

API Service Layer

// src/services/api/hazards.js
export const HazardsAPI = {
  /**
   * Get hazards within map bounds
   * Combines OSM hazards + user-reported hazards
   */
  getHazardsInBounds: async (bounds, filters = {}) => {
    const { north, south, east, west } = bounds;

    // Query OSM hazards
    let query = supabase
      .from('road_hazards')
      .select(`
        id, hazard_type, severity, latitude, longitude,
        clearance_meters, width_meters, affects_caravans,
        description, data_source
      `)
      .gte('latitude', south)
      .lte('latitude', north)
      .gte('longitude', west)
      .lte('longitude', east);

    // Apply filters
    if (filters.types && filters.types.length > 0) {
      query = query.in('hazard_type', filters.types);
    }

    if (filters.severity) {
      query = query.in('severity', filters.severity);
    }

    const { data: osmHazards, error: osmError } = await query;

    // Query user-reported hazards
    const { data: userHazards, error: userError } = await supabase
      .from('user_reported_hazards')
      .select('*')
      .eq('status', 'verified')
      .gte('latitude', south)
      .lte('latitude', north)
      .gte('longitude', west)
      .lte('longitude', east);

    // Combine and return
    return {
      osmHazards: osmHazards || [],
      userHazards: userHazards || [],
      error: osmError || userError,
    };
  },

  /**
   * Get hazard details with verifications
   */
  getHazardDetails: async (hazardId, source = 'osm') => {
    const table = source === 'osm' ? 'road_hazards' : 'user_reported_hazards';

    // Get hazard
    const { data: hazard, error: hazardError } = await supabase
      .from(table)
      .select('*')
      .eq('id', hazardId)
      .single();

    // Get verifications
    const { data: verifications, error: verifyError } = await supabase
      .from('hazard_verifications')
      .select(`
        *,
        verified_by_profile:profiles(display_name, avatar_url)
      `)
      .eq('hazard_id', hazardId)
      .eq('hazard_source', source)
      .order('created_at', { ascending: false });

    return {
      hazard,
      verifications: verifications || [],
      error: hazardError || verifyError,
    };
  },

  /**
   * Report new hazard
   */
  reportHazard: async (hazardData, userId) => {
    const { data, error } = await supabase
      .from('user_reported_hazards')
      .insert({
        reported_by: userId,
        hazard_type: hazardData.type,
        latitude: hazardData.latitude,
        longitude: hazardData.longitude,
        restrictions: hazardData.restrictions,
        description: hazardData.description,
        photos: hazardData.photos || [],
        status: 'pending', // Requires verification
      })
      .select()
      .single();

    return { data, error };
  },

  /**
   * Verify existing hazard
   */
  verifyHazard: async (hazardId, source, verificationData, userId) => {
    const { data, error } = await supabase
      .from('hazard_verifications')
      .insert({
        hazard_id: hazardId,
        hazard_source: source,
        verified_by: userId,
        verification_type: verificationData.type,
        status: verificationData.status,
        notes: verificationData.notes,
        photo_url: verificationData.photo,
      })
      .select()
      .single();

    // Update hazard verification count
    if (!error && source === 'user_reported') {
      await supabase.rpc('increment_verification_count', {
        hazard_id: hazardId,
      });
    }

    return { data, error };
  },
};

// src/services/api/routes.js
export const RoutesAPI = {
  /**
   * Calculate route with hazard checking
   * Uses our route_segments + road_elevations data
   */
  calculateRoute: async (origin, destination, vehicleSpecs, preferences) => {
    // Call Supabase Edge Function or PostGIS function
    const { data, error } = await supabase.rpc('calculate_safe_route', {
      origin_lat: origin.latitude,
      origin_lng: origin.longitude,
      dest_lat: destination.latitude,
      dest_lng: destination.longitude,
      vehicle_height: vehicleSpecs.height,
      vehicle_weight: vehicleSpecs.weight,
      vehicle_width: vehicleSpecs.width,
      avoid_unsealed: preferences.avoidUnsealed,
      avoid_steep_grades: preferences.avoidSteepGrades,
      max_grade: preferences.maxGrade || 10,
    });

    return { data, error };
  },

  /**
   * Get hazards along route
   */
  getHazardsAlongRoute: async (routeGeometry, vehicleSpecs, bufferMeters = 100) => {
    const { data, error } = await supabase.rpc('hazards_along_route', {
      route_geom: routeGeometry,
      buffer_meters: bufferMeters,
      vehicle_height: vehicleSpecs.height,
      vehicle_weight: vehicleSpecs.weight,
    });

    return { data, error };
  },

  /**
   * Get steep grades along route
   */
  getSteepGradesAlongRoute: async (routeGeometry, bufferMeters = 50) => {
    const { data, error } = await supabase.rpc('steep_grades_along_route', {
      route_geom: routeGeometry,
      buffer_meters: bufferMeters,
      min_grade_percent: 8.0,
    });

    return { data, error };
  },

  /**
   * Save route
   */
  saveRoute: async (routeData, userId) => {
    const { data, error } = await supabase
      .from('saved_routes')
      .insert({
        user_id: userId,
        name: routeData.name,
        origin_location: routeData.origin,
        destination_location: routeData.destination,
        waypoints: routeData.waypoints,
        route_geometry: routeData.geometry,
        distance_meters: routeData.distance,
        duration_seconds: routeData.duration,
        vehicle_id: routeData.vehicleId,
        preferences: routeData.preferences,
        hazards_on_route: routeData.hazards, // Cache for offline
      })
      .select()
      .single();

    return { data, error };
  },

  /**
   * Get saved routes
   */
  getSavedRoutes: async (userId) => {
    const { data, error } = await supabase
      .from('saved_routes')
      .select(`
        *,
        vehicle:vehicles(name, specs)
      `)
      .eq('user_id', userId)
      .order('last_used', { ascending: false });

    return { data, error };
  },
};

// src/services/api/amenities.js
export const AmenitiesAPI = {
  /**
   * Get amenities near location
   * Uses our existing amenities table
   */
  getAmenitiesNearPoint: async (lat, lng, radiusKm = 50, categories = []) => {
    let query = supabase.rpc('amenities_near_point', {
      lat,
      lng,
      radius_meters: radiusKm * 1000,
    });

    const { data, error } = await query;

    // Filter by category if specified
    let filtered = data || [];
    if (categories.length > 0) {
      filtered = filtered.filter(a => categories.includes(a.category));
    }

    return { data: filtered, error };
  },

  /**
   * Get fuel stations along route
   */
  getFuelStationsAlongRoute: async (routeGeometry, bufferKm = 5) => {
    const { data, error } = await supabase
      .from('amenities')
      .select('*')
      .eq('category', 'fuel_station')
      .rpc('within_distance_of_line', {
        line_geom: routeGeometry,
        distance_meters: bufferKm * 1000,
      });

    return { data, error };
  },

  /**
   * Get rest areas along route
   */
  getRestAreasAlongRoute: async (routeGeometry, bufferKm = 2) => {
    const { data, error } = await supabase
      .from('rest_areas')
      .select('*')
      .rpc('within_distance_of_line', {
        line_geom: routeGeometry,
        distance_meters: bufferKm * 1000,
      });

    return { data, error };
  },
};

---

PostGIS Helper Functions (Backend)

These need to be added to Supabase as database functions:

-- Function: Calculate safe route
CREATE OR REPLACE FUNCTION calculate_safe_route(
  origin_lat DECIMAL,
  origin_lng DECIMAL,
  dest_lat DECIMAL,
  dest_lng DECIMAL,
  vehicle_height DECIMAL,
  vehicle_weight DECIMAL,
  vehicle_width DECIMAL,
  avoid_unsealed BOOLEAN DEFAULT true,
  avoid_steep_grades BOOLEAN DEFAULT true,
  max_grade DECIMAL DEFAULT 10.0
)
RETURNS JSONB AS $$
DECLARE
  route_result JSONB;
BEGIN
  -- This would use pgRouting or external routing API
  -- For now, return structure:
  route_result := jsonb_build_object(
    'distance_meters', 0,
    'duration_seconds', 0,
    'geometry', ST_AsGeoJSON(ST_MakeLine(
      ST_SetSRID(ST_MakePoint(origin_lng, origin_lat), 4326),
      ST_SetSRID(ST_MakePoint(dest_lng, dest_lat), 4326)
    )),
    'segments', '[]'::jsonb
  );

  RETURN route_result;
END;
$$ LANGUAGE plpgsql;

-- Function: Get hazards along route
CREATE OR REPLACE FUNCTION hazards_along_route(
  route_geom geometry,
  buffer_meters INTEGER DEFAULT 100,
  vehicle_height DECIMAL DEFAULT NULL,
  vehicle_weight DECIMAL DEFAULT NULL
)
RETURNS TABLE (
  id UUID,
  hazard_type TEXT,
  severity TEXT,
  latitude DECIMAL,
  longitude DECIMAL,
  clearance_meters DECIMAL,
  distance_from_start DECIMAL,
  passable BOOLEAN
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    h.id,
    h.hazard_type::TEXT,
    h.severity::TEXT,
    h.latitude,
    h.longitude,
    h.clearance_meters,
    ST_LineLocatePoint(route_geom, ST_SetSRID(ST_MakePoint(h.longitude, h.latitude), 4326)) *
      ST_Length(route_geom::geography) AS distance_from_start,
    CASE
      WHEN vehicle_height IS NOT NULL AND h.clearance_meters IS NOT NULL
      THEN vehicle_height <= h.clearance_meters
      ELSE true
    END AS passable
  FROM road_hazards h
  WHERE ST_DWithin(
    ST_SetSRID(ST_MakePoint(h.longitude, h.latitude), 4326)::geography,
    route_geom::geography,
    buffer_meters
  )
  ORDER BY distance_from_start;
END;
$$ LANGUAGE plpgsql;

-- Function: Get steep grades along route
CREATE OR REPLACE FUNCTION steep_grades_along_route(
  route_geom geometry,
  buffer_meters INTEGER DEFAULT 50,
  min_grade_percent DECIMAL DEFAULT 8.0
)
RETURNS TABLE (
  id UUID,
  road_name TEXT,
  max_grade_percent DECIMAL,
  grade_category TEXT,
  distance_from_start DECIMAL,
  latitude DECIMAL,
  longitude DECIMAL
) AS $$
BEGIN
  RETURN QUERY
  SELECT
    re.id,
    re.road_name,
    re.max_grade_percent,
    re.grade_category,
    ST_LineLocatePoint(route_geom, ST_Centroid(re.segment_geom)) *
      ST_Length(route_geom::geography) AS distance_from_start,
    ST_Y(ST_Centroid(re.segment_geom)) AS latitude,
    ST_X(ST_Centroid(re.segment_geom)) AS longitude
  FROM road_elevations re
  WHERE ST_DWithin(
    re.segment_geom::geography,
    route_geom::geography,
    buffer_meters
  )
  AND re.max_grade_percent >= min_grade_percent
  ORDER BY distance_from_start;
END;
$$ LANGUAGE plpgsql;

---

Mobile App Implementation Steps

Phase 1: Core Setup

1. Initialize Expo Project

cd apps
npx create-expo-app mobile --template
cd mobile

# Install core dependencies
npm install @supabase/supabase-js
npm install react-native-maps
npm install @react-navigation/native
npm install @react-navigation/bottom-tabs
npm install @react-navigation/stack
npm install react-native-paper
npm install @react-native-async-storage/async-storage

2. Configure Supabase

// apps/mobile/.env
EXPO_PUBLIC_SUPABASE_URL=your-project-url
EXPO_PUBLIC_SUPABASE_ANON_KEY=your-anon-key

3. Set Up Navigation

• Follow design doc's navigation structure
• Tab navigator with 5 tabs
• Stack navigators for each section

Phase 2: Map & Hazards

1. Map Screen

• React Native Maps implementation
• Cluster hazard markers
• Filter chips for hazard types
• Bottom sheet for hazard details

2. Hazard Data Integration

• Query road_hazards table
• Display OSM hazards on map
• Show hazard details
• Filter by vehicle specs

3. User-Reported Hazards

• Add database tables
• Photo upload to Supabase Storage
• Report hazard form
• Moderation queue

Phase 3: Route Planning

1. Route Planning Screen

• Origin/destination input
• Vehicle selector
• Route preferences

2. Route Calculation

• Implement PostGIS routing
• Hazard checking along route
• Steep grade warnings
• Alternative routes

3. Navigation

• Turn-by-turn guidance
• Hazard alerts
• Real-time location tracking

Phase 4: User Features

1. Authentication

• Supabase Auth integration
• Email/password + OAuth
• Profile management

2. Vehicle Management

• CRUD for vehicles
• Vehicle specs input
• Default vehicle selection

3. Saved Routes

• Save/load routes
• Route history
• Favorite locations

Phase 5: Premium Features

1. Offline Maps

• Download map tiles
• Cache hazard data
• Offline routing

2. Subscriptions

• RevenueCat integration
• Free vs Premium features
• Billing management

3. Weather Integration

• Use Phase 2 weather data
• Weather warnings on route
• Forecast display

Phase 6: Polish & Launch

1. UI/UX Refinement

• Follow design doc styles
• Animations
• Loading states
• Error handling

2. Testing

• Unit tests
• Integration tests
• Beta testing with TestFlight

3. App Store Submission

• Screenshots
• App Store listing
• Privacy policy
• Launch!

---

Migration Script for User Tables

-- Migration: 0010_add_user_tables.sql

-- Users table (extends Supabase auth.users)
CREATE TABLE users (
  id UUID PRIMARY KEY REFERENCES auth.users(id) ON DELETE CASCADE,
  email VARCHAR(255) UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- User profiles
CREATE TABLE profiles (
  user_id UUID PRIMARY KEY REFERENCES users(id) ON DELETE CASCADE,
  display_name VARCHAR(100),
  avatar_url TEXT,
  phone VARCHAR(20),
  subscription_tier VARCHAR(20) DEFAULT 'free' CHECK (subscription_tier IN ('free', 'premium')),
  subscription_expiry DATE,
  trust_score INTEGER DEFAULT 50 CHECK (trust_score >= 0 AND trust_score <= 100),
  hazards_reported INTEGER DEFAULT 0,
  verifications_count INTEGER DEFAULT 0,
  preferences JSONB DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Vehicles
CREATE TABLE vehicles (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(100) NOT NULL,
  type VARCHAR(20) CHECK (type IN ('caravan', 'motorhome', 'camper', 'trailer', 'other')),
  make VARCHAR(50),
  model VARCHAR(50),
  year INTEGER CHECK (year >= 1950 AND year <= EXTRACT(YEAR FROM CURRENT_DATE) + 2),
  specs JSONB NOT NULL DEFAULT '{}'::jsonb,
  is_default BOOLEAN DEFAULT false,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW(),

  CONSTRAINT specs_required CHECK (
    specs ? 'height' AND
    specs ? 'weight' AND
    specs ? 'length' AND
    specs ? 'width'
  )
);

-- Saved routes
CREATE TABLE saved_routes (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID REFERENCES users(id) ON DELETE CASCADE,
  name VARCHAR(255) NOT NULL,
  origin_location JSONB NOT NULL,
  destination_location JSONB NOT NULL,
  waypoints JSONB DEFAULT '[]'::jsonb,
  route_geometry geometry(LineString, 4326),
  distance_meters INTEGER,
  duration_seconds INTEGER,
  vehicle_id UUID REFERENCES vehicles(id) ON DELETE SET NULL,
  preferences JSONB DEFAULT '{}'::jsonb,
  hazards_on_route JSONB DEFAULT '[]'::jsonb,
  created_at TIMESTAMPTZ DEFAULT NOW(),
  last_used TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- User reported hazards
CREATE TABLE user_reported_hazards (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  reported_by UUID REFERENCES users(id) ON DELETE SET NULL,
  hazard_type VARCHAR(20) NOT NULL,
  latitude DECIMAL(10, 8) NOT NULL,
  longitude DECIMAL(11, 8) NOT NULL,
  geometry geometry(Point, 4326) GENERATED ALWAYS AS (
    ST_SetSRID(ST_MakePoint(longitude, latitude), 4326)
  ) STORED,
  restrictions JSONB DEFAULT '{}'::jsonb,
  description TEXT,
  photos TEXT[] DEFAULT '{}',
  verification_count INTEGER DEFAULT 0,
  status VARCHAR(20) DEFAULT 'pending' CHECK (status IN ('pending', 'verified', 'rejected', 'resolved')),
  created_at TIMESTAMPTZ DEFAULT NOW(),
  updated_at TIMESTAMPTZ DEFAULT NOW()
);

-- Hazard verifications
CREATE TABLE hazard_verifications (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  hazard_id UUID NOT NULL,
  hazard_source VARCHAR(20) NOT NULL CHECK (hazard_source IN ('osm', 'user_reported')),
  verified_by UUID REFERENCES users(id) ON DELETE SET NULL,
  verification_type VARCHAR(20) CHECK (verification_type IN ('confirm', 'dispute', 'update')),
  status VARCHAR(20) CHECK (status IN ('still_present', 'resolved', 'modified')),
  notes TEXT,
  photo_url TEXT,
  created_at TIMESTAMPTZ DEFAULT NOW()
);

-- Indexes
CREATE INDEX idx_vehicles_user ON vehicles(user_id);
CREATE INDEX idx_vehicles_default ON vehicles(user_id, is_default) WHERE is_default = true;
CREATE INDEX idx_saved_routes_user ON saved_routes(user_id);
CREATE INDEX idx_saved_routes_geom ON saved_routes USING GIST (route_geometry);
CREATE INDEX idx_user_hazards_geom ON user_reported_hazards USING GIST (geometry);
CREATE INDEX idx_user_hazards_status ON user_reported_hazards(status);
CREATE INDEX idx_verifications_hazard ON hazard_verifications(hazard_id, hazard_source);
CREATE INDEX idx_verifications_user ON hazard_verifications(verified_by);

-- RLS Policies
ALTER TABLE profiles ENABLE ROW LEVEL SECURITY;
ALTER TABLE vehicles ENABLE ROW LEVEL SECURITY;
ALTER TABLE saved_routes ENABLE ROW LEVEL SECURITY;
ALTER TABLE user_reported_hazards ENABLE ROW LEVEL SECURITY;
ALTER TABLE hazard_verifications ENABLE ROW LEVEL SECURITY;

-- Profiles: Users can read all, update own
CREATE POLICY "Profiles are viewable by everyone"
  ON profiles FOR SELECT
  USING (true);

CREATE POLICY "Users can update own profile"
  ON profiles FOR UPDATE
  USING (auth.uid() = user_id);

-- Vehicles: Users can CRUD own vehicles
CREATE POLICY "Users can view own vehicles"
  ON vehicles FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create own vehicles"
  ON vehicles FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own vehicles"
  ON vehicles FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own vehicles"
  ON vehicles FOR DELETE
  USING (auth.uid() = user_id);

-- Saved routes: Users can CRUD own routes
CREATE POLICY "Users can view own routes"
  ON saved_routes FOR SELECT
  USING (auth.uid() = user_id);

CREATE POLICY "Users can create own routes"
  ON saved_routes FOR INSERT
  WITH CHECK (auth.uid() = user_id);

CREATE POLICY "Users can update own routes"
  ON saved_routes FOR UPDATE
  USING (auth.uid() = user_id);

CREATE POLICY "Users can delete own routes"
  ON saved_routes FOR DELETE
  USING (auth.uid() = user_id);

-- User hazards: Everyone can read verified, users can create
CREATE POLICY "Verified hazards are viewable by everyone"
  ON user_reported_hazards FOR SELECT
  USING (status IN ('verified', 'pending'));

CREATE POLICY "Users can report hazards"
  ON user_reported_hazards FOR INSERT
  WITH CHECK (auth.uid() = reported_by);

CREATE POLICY "Users can update own hazards"
  ON user_reported_hazards FOR UPDATE
  USING (auth.uid() = reported_by);

-- Verifications: Everyone can read, authenticated users can create
CREATE POLICY "Verifications are viewable by everyone"
  ON hazard_verifications FOR SELECT
  USING (true);

CREATE POLICY "Authenticated users can verify hazards"
  ON hazard_verifications FOR INSERT
  WITH CHECK (auth.uid() = verified_by);

-- Triggers for updated_at
CREATE TRIGGER update_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_profiles_updated_at
  BEFORE UPDATE ON profiles
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_vehicles_updated_at
  BEFORE UPDATE ON vehicles
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_saved_routes_updated_at
  BEFORE UPDATE ON saved_routes
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

CREATE TRIGGER update_user_hazards_updated_at
  BEFORE UPDATE ON user_reported_hazards
  FOR EACH ROW
  EXECUTE FUNCTION update_updated_at_column();

-- Function: Auto-create profile on user signup
CREATE OR REPLACE FUNCTION handle_new_user()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO users (id, email)
  VALUES (NEW.id, NEW.email);

  INSERT INTO profiles (user_id, display_name)
  VALUES (NEW.id, COALESCE(NEW.raw_user_meta_data->>'display_name', split_part(NEW.email, '@', 1)));

  RETURN NEW;
END;
$$ LANGUAGE plpgsql SECURITY DEFINER;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW
  EXECUTE FUNCTION handle_new_user();

COMMENT ON TABLE users IS 'User accounts extending Supabase auth.users';
COMMENT ON TABLE profiles IS 'User profiles and preferences';
COMMENT ON TABLE vehicles IS 'User vehicle specifications';
COMMENT ON TABLE saved_routes IS 'User saved routes and route history';
COMMENT ON TABLE user_reported_hazards IS 'Community-reported hazards';
COMMENT ON TABLE hazard_verifications IS 'Community verifications of hazards';

---

Key Advantages of Our Approach

1. Data Quality 🏆

We have professional-grade data:

• ✅ 57,742 verified OSM hazards (not just user reports)
• ✅ 420,083 road segments with full geometry
• ✅ 2.97M elevation profiles with grade calculations
• ✅ Real amenities data (fuel, rest areas, toilets)

Competitors typically have:

• ❌ Only user-reported hazards (unreliable)
• ❌ Basic routing (no detailed road network)
• ❌ No elevation data
• ❌ Manual amenity entry

2. Technical Architecture 🚀

We're building on Supabase:

• Real-time database updates
• Built-in authentication
• File storage for photos
• PostGIS spatial queries
• Edge functions for complex logic
• Row-level security

This means:

• Faster development
• Lower costs
• Better scalability
• Modern stack

3. Feature Richness 💎

Phase 1 (Launch):

• Map with OSM hazards
• Route planning with hazard warnings
• Elevation/grade warnings
• Fuel stations and rest areas
• Basic user accounts

Phase 2 (3 months):

• User-reported hazards
• Community verifications
• Saved routes
• Offline maps
• Premium subscriptions

Phase 3 (6 months):

• Weather warnings
• Mobile coverage maps
• Traffic incidents
• Tourist POIs
• Social features

---

Next Steps

Immediate Actions (This Week)

1. ✅ Review this integration plan
2. ✅ Run user tables migration (0010_add_user_tables.sql)
3. ✅ Initialize mobile app:

   cd apps
   npx create-expo-app mobile
   cd mobile
   npm install @supabase/supabase-js react-native-maps

4. ✅ Set up Supabase client in mobile app
5. ✅ Create first screen (Map with hazards)

Short Term (Next 2 Weeks)

1. Implement map screen with hazard markers
2. Add hazard filtering
3. Show hazard details
4. Test with real data

Medium Term (Next Month)

1. Route planning screen
2. Vehicle management
3. User authentication
4. Saved routes

Resources

• Design Doc: /MOBILE_APP_DESIGN_SPEC.md (from Claude Opus)
• Integration Plan: This document
• Database Docs: /packages/database/README.md
• Phase 3 Plan: /packages/database/PHASE3_PLAN.md

---

Summary

We're in an excellent position:

✅ Backend: Production-ready database with comprehensive data ✅ Design: Professional UI/UX specification from Claude Opus ✅ Integration: Clear plan to connect frontend + backend ✅ Advantage: Better data than competitors

Next step: Start building the mobile app following the design doc, connected to our superior backend!

---

Last Updated: October 15, 2025 Status: Ready to Start Mobile Development Estimated Time to MVP: 6-8 weeks