FleetOS Company-Owned Migration Plan

Date: 2026-01-09 Status: APPROVED - Ready to Execute Effort: 6-8 hours Risk: LOW

🎯 Objective

Transform FleetOS from user-owned to company-owned architecture:

✅ RFQs owned by companies, not individual users
✅ Team members see all company work
✅ Boss oversight built-in
✅ Emergency access works
✅ BIBLE-compliant

📋 Phase 1: Database Migration

Step 1.1: Add Company Columns to service_requests

File: database/fleetos/add_company_columns.sql (NEW)

-- ============================================
-- FLEETOS COMPANY-OWNED MIGRATION
-- Add company columns to service_requests table
-- Safe to run: Uses IF NOT EXISTS, backward compatible
-- ============================================

-- Add fleet_company_id column
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='service_requests' AND column_name='fleet_company_id'
  ) THEN
    ALTER TABLE service_requests
    ADD COLUMN fleet_company_id UUID REFERENCES companies(company_id);

    RAISE NOTICE '✅ Added fleet_company_id column';
  ELSE
    RAISE NOTICE 'ℹ️  fleet_company_id column already exists';
  END IF;
END $$;

-- Add supplier_company_id column
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='service_requests' AND column_name='supplier_company_id'
  ) THEN
    ALTER TABLE service_requests
    ADD COLUMN supplier_company_id UUID REFERENCES companies(company_id);

    RAISE NOTICE '✅ Added supplier_company_id column';
  ELSE
    RAISE NOTICE 'ℹ️  supplier_company_id column already exists';
  END IF;
END $$;

-- Add submitted_by_user_id column (audit trail)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='service_requests' AND column_name='submitted_by_user_id'
  ) THEN
    ALTER TABLE service_requests
    ADD COLUMN submitted_by_user_id UUID REFERENCES users(user_id);

    RAISE NOTICE '✅ Added submitted_by_user_id column';
  ELSE
    RAISE NOTICE 'ℹ️  submitted_by_user_id column already exists';
  END IF;
END $$;

-- Add assigned_to_user_id column (current tech working)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='service_requests' AND column_name='assigned_to_user_id'
  ) THEN
    ALTER TABLE service_requests
    ADD COLUMN assigned_to_user_id UUID REFERENCES users(user_id);

    RAISE NOTICE '✅ Added assigned_to_user_id column';
  ELSE
    RAISE NOTICE 'ℹ️  assigned_to_user_id column already exists';
  END IF;
END $$;

-- Create indexes for performance
CREATE INDEX IF NOT EXISTS idx_service_requests_fleet_company
  ON service_requests(fleet_company_id);

CREATE INDEX IF NOT EXISTS idx_service_requests_supplier_company
  ON service_requests(supplier_company_id);

CREATE INDEX IF NOT EXISTS idx_service_requests_submitted_by
  ON service_requests(submitted_by_user_id);

CREATE INDEX IF NOT EXISTS idx_service_requests_assigned_to
  ON service_requests(assigned_to_user_id);

-- Log completion
DO $$
BEGIN
  RAISE NOTICE '✅ FleetOS company-owned migration complete!';
  RAISE NOTICE '   - fleet_company_id added';
  RAISE NOTICE '   - supplier_company_id added';
  RAISE NOTICE '   - submitted_by_user_id added';
  RAISE NOTICE '   - assigned_to_user_id added';
  RAISE NOTICE '   - Indexes created';
  RAISE NOTICE '⚠️  Old columns (fleet_user_id, supplier_id) still exist for backward compatibility';
  RAISE NOTICE '⚠️  Update application code to use new columns';
END $$;

How to run:

Copy SQL above
Go to Supabase SQL Editor
Paste and click "Run"
Verify output shows ✅ messages

Verification:

-- Check columns exist
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_name = 'service_requests'
  AND column_name IN (
    'fleet_company_id',
    'supplier_company_id',
    'submitted_by_user_id',
    'assigned_to_user_id'
  );

-- Should return 4 rows

📝 Phase 2: Code Changes

Step 2.1: Update QueryService.createServiceRequest()

File: services/shared/QueryService.ts

Current Code (Lines 59-84):

static async createServiceRequest(requestData: any) {
  const { data, error } = await supabase
    .from('service_requests')
    .insert({
      supplier_id: requestData.supplier_id,              // ❌ OLD
      fleet_id: requestData.fleet_id || requestData.fleet_user_id,  // ❌ OLD
      fleet_name: requestData.fleet_name,
      contact_name: requestData.contact_name,
      fleet_contact: requestData.fleet_contact,
      contact_type: requestData.contact_type || 'email',
      vehicle_identifier: requestData.vehicle_identifier,
      location: requestData.location,
      description: requestData.description,
      urgency: requestData.urgency,
      scheduled_time: requestData.scheduled_time || null,
      status: requestData.status || 'pending',
      source_product: requestData.source_product || 'fleetos',
      created_via: requestData.created_via || 'manual',
      is_manual_entry: requestData.is_manual_entry || false,
    })
    .select()
    .single();

  if (error) throw error;
  return data;
}

NEW Code:

/**
 * Create a new service request (COMPANY-OWNED)
 * RFQs are owned by companies, not individual users
 */
static async createServiceRequest(requestData: any) {
  const { data, error } = await supabase
    .from('service_requests')
    .insert({
      // COMPANY-OWNED: Work belongs to organizations
      fleet_company_id: requestData.fleet_company_id,          // ✅ NEW
      supplier_company_id: requestData.supplier_company_id,    // ✅ NEW

      // AUDIT TRAIL: Track who acted (optional)
      submitted_by_user_id: requestData.submitted_by_user_id,  // ✅ NEW
      assigned_to_user_id: requestData.assigned_to_user_id,    // ✅ NEW

      // Core request data (unchanged)
      fleet_name: requestData.fleet_name,
      contact_name: requestData.contact_name,
      fleet_contact: requestData.fleet_contact,
      contact_type: requestData.contact_type || 'email',
      vehicle_identifier: requestData.vehicle_identifier,
      location: requestData.location,
      description: requestData.description,
      urgency: requestData.urgency,
      scheduled_time: requestData.scheduled_time || null,
      status: requestData.status || 'pending',
      source_product: requestData.source_product || 'fleetos',
      created_via: requestData.created_via || 'manual',
      is_manual_entry: requestData.is_manual_entry || false,

      // BACKWARD COMPATIBILITY: Keep old columns populated for now
      fleet_user_id: requestData.fleet_user_id || requestData.submitted_by_user_id,
      supplier_id: requestData.supplier_id || requestData.supplier_company_id,
    })
    .select()
    .single();

  if (error) throw error;
  return data;
}

Changes:

✅ Added fleet_company_id (NEW - company owns)
✅ Added supplier_company_id (NEW - company owns)
✅ Added submitted_by_user_id (NEW - audit trail)
✅ Added assigned_to_user_id (NEW - current tech)
✅ Kept old columns for backward compatibility

Step 2.2: Update Seed Functions (Fleet Side)

File: App.tsx

Current Code (Lines 83-200):

async function seedShepherdRelationships(shepherdId: string): Promise<{ success: boolean; error?: string }> {
  try {
    const suppliers = await getAllSuppliers();
    if (suppliers.length === 0) {
      return { success: true };
    }

    const tireSupplier = suppliers.find(s => s.category === 'tyres') || suppliers[0];
    const supplierId = tireSupplier.supplier_id;

    const demoRequests = [
      {
        supplier_id: supplierId,              // ❌ OLD
        fleet_id: shepherdId,                 // ❌ OLD
        fleet_name: 'ABC Logistics',
        // ... rest of request data
      },
      // ... 5 more requests
    ];

    for (const request of demoRequests) {
      await QueryService.createServiceRequest(request);
    }

    return { success: true };
  } catch (error: any) {
    return { success: false, error: error.message };
  }
}

NEW Code:

async function seedShepherdRelationships(
  shepherdId: string,
  fleetCompanyId: string  // ✅ NEW parameter
): Promise<{ success: boolean; error?: string }> {
  try {
    const suppliers = await getAllSuppliers();
    if (suppliers.length === 0) {
      return { success: true };
    }

    const tireSupplier = suppliers.find(s => s.category === 'tyres') || suppliers[0];

    // Get supplier's company_id
    const { data: supplierCompany } = await supabase
      .from('companies')
      .select('company_id')
      .eq('domain', extractDomain(tireSupplier.email))
      .single();

    const demoRequests = [
      {
        // COMPANY-OWNED: Companies own the work
        fleet_company_id: fleetCompanyId,                    // ✅ NEW
        supplier_company_id: supplierCompany?.company_id,    // ✅ NEW
        submitted_by_user_id: shepherdId,                     // ✅ NEW (audit)

        // Core data (unchanged)
        fleet_name: 'ABC Logistics',
        fleet_contact: 'fleet@abc-logistics.com',
        contact_type: 'email' as const,
        vehicle_identifier: 'Truck #127',
        location: '555 Market St, San Francisco, CA',
        description: 'Flat tire on front passenger side. Need immediate replacement.',
        urgency: 'URGENT' as const,
        status: 'pending' as const,
        source_product: 'fleetos' as const,
        created_via: 'demo_seed',
      },
      // ... 5 more requests (update all)
    ];

    for (const request of demoRequests) {
      await QueryService.createServiceRequest(request);
    }

    console.log('✅ Seeded 6 FleetOS service requests (COMPANY-OWNED)');
    return { success: true };
  } catch (error: any) {
    console.error('❌ Failed to seed shepherd relationships:', error);
    return { success: false, error: error.message };
  }
}

Call Site Update (Line ~706):

// OLD:
const seedResult = await seedShepherdRelationships(userId);

// NEW:
const seedResult = await seedShepherdRelationships(userId, companyId);

Step 2.3: Update Seed Functions (Supplier Side)

File: App.tsx

Current Code (Lines 205-304):

async function seedSupplierRelationships(supplierId: string): Promise<{ success: boolean; error?: string }> {
  try {
    const demoRequests = [
      {
        supplier_id: supplierId,       // ❌ OLD
        fleet_id: 'demo-fleet-001',    // ❌ OLD
        fleet_name: 'XYZ Delivery Services',
        // ... rest of request data
      },
      // ... 5 more requests
    ];

    for (const request of demoRequests) {
      await QueryService.createServiceRequest(request);
    }

    return { success: true };
  } catch (error: any) {
    return { success: false, error: error.message };
  }
}

NEW Code:

async function seedSupplierRelationships(
  supplierId: string,
  supplierCompanyId: string  // ✅ NEW parameter
): Promise<{ success: boolean; error?: string }> {
  try {
    // Create demo fleet companies for seed data
    const demoFleetDomains = [
      'xyzdelivery.com',
      'quicktransport.com',
      'metrologistics.com',
      'bayareacouriers.com',
      'swiftmovers.com',
      'coastalshipping.com',
    ];

    const demoRequests = [];

    for (let i = 0; i < 6; i++) {
      const domain = demoFleetDomains[i];

      // Get or create demo fleet company
      let { data: fleetCompany } = await supabase
        .from('companies')
        .select('company_id')
        .eq('domain', domain)
        .single();

      if (!fleetCompany) {
        const companyName = domain.split('.')[0]
          .replace(/[-_]/g, ' ')
          .split(' ')
          .map(w => w.charAt(0).toUpperCase() + w.slice(1))
          .join(' ');

        const { data: newCompany } = await supabase
          .from('companies')
          .insert({
            domain,
            company_name: companyName,
            company_type: 'fleet',
          })
          .select()
          .single();

        fleetCompany = newCompany;
      }

      // Seed data variations (same as before but with company IDs)
      const requests = [
        {
          // COMPANY-OWNED
          fleet_company_id: fleetCompany.company_id,    // ✅ NEW
          supplier_company_id: supplierCompanyId,       // ✅ NEW

          // Core data
          fleet_name: 'XYZ Delivery Services',
          fleet_contact: '+1-415-555-0101',
          contact_type: 'whatsapp' as const,
          vehicle_identifier: 'Truck #12',
          location: '555 California St, San Francisco, CA',
          description: 'Blown tire on highway - need emergency roadside assistance.',
          urgency: 'URGENT' as const,
          status: 'pending' as const,
          source_product: 'fleetos' as const,
          created_via: 'demo_seed',
        },
        // ... add other variations
      ];

      demoRequests.push(requests[i % requests.length]);
    }

    for (const request of demoRequests) {
      await QueryService.createServiceRequest(request);
    }

    console.log('✅ Seeded 6 FleetOS service requests (COMPANY-OWNED)');
    return { success: true };
  } catch (error: any) {
    console.error('❌ Failed to seed supplier relationships:', error);
    return { success: false, error: error.message };
  }
}

Call Site Update (Line ~728):

// OLD:
const seedResult = await seedSupplierRelationships(supplierId);

// NEW:
const seedResult = await seedSupplierRelationships(supplierId, companyId);

Step 2.4: Update Manual Request Form

File: components/fleetos/forms/ManualRequestForm.tsx

Find the onSubmit handler and update:

// OLD:
const requestData = {
  supplier_id: supplierId,
  fleet_id: fleetId,
  // ...
};

// NEW:
const requestData = {
  supplier_company_id: supplierCompanyId,  // ✅ NEW
  fleet_company_id: fleetCompanyId,        // ✅ NEW
  submitted_by_user_id: currentUserId,     // ✅ NEW
  // ...
};

Props update needed:

interface ManualRequestFormProps {
  supplierId: string;
  supplierCompanyId: string;  // ✅ ADD THIS
  onSuccess: () => void;
  onCancel: () => void;
}

Step 2.5: Update Fleet Request Form

File: components/fleetos/forms/FleetRequestForm.tsx

Similar updates to ManualRequestForm:

interface FleetRequestFormProps {
  supplierId: string;
  supplierCompanyId: string;  // ✅ ADD THIS
  supplierName: string;
  fleetId: string;
  fleetCompanyId: string;     // ✅ ADD THIS
  onSubmit: (data: any) => Promise<void>;
  onCancel: () => void;
}

Step 2.6: Update App.tsx Route Handlers

File: App.tsx

Update FLEETOS_SUPPLIER_ADD_MANUAL_REQUEST:

{currentView === 'FLEETOS_SUPPLIER_ADD_MANUAL_REQUEST' && (
  <ManualRequestForm
    supplierId={user.supplier_id}
    supplierCompanyId={user.company_id}  // ✅ ADD THIS
    onSuccess={() => setCurrentView('FLEETOS_SUPPLIER_DASHBOARD')}
    onCancel={() => setCurrentView('FLEETOS_SUPPLIER_DASHBOARD')}
  />
)}

Update FLEETOS_FLEET_CREATE_REQUEST:

{currentView === 'FLEETOS_FLEET_CREATE_REQUEST' && fleetRequestTarget && (
  <FleetRequestForm
    supplierId={fleetRequestTarget.supplierId}
    supplierCompanyId={fleetRequestTarget.supplierCompanyId}  // ✅ ADD THIS
    supplierName={fleetRequestTarget.supplierName}
    fleetId={user.id}
    fleetCompanyId={user.company_id}  // ✅ ADD THIS
    onSubmit={async (requestData) => {
      await QueryService.createServiceRequest({
        source_product: 'fleetos',
        fleet_company_id: user.company_id,           // ✅ NEW
        supplier_company_id: fleetRequestTarget.supplierCompanyId,  // ✅ NEW
        submitted_by_user_id: user.id,               // ✅ NEW
        fleet_name: user.name || 'Fleet User',
        fleet_contact: user.email,
        contact_type: 'email',
        ...requestData,
        status: 'pending',
        created_via: 'fleet_dashboard'
      });
      setFleetRequestTarget(null);
      setCurrentView('FLEETOS_FLEET_DASHBOARD');
    }}
    onCancel={() => {
      setFleetRequestTarget(null);
      setCurrentView('FLEETOS_FLEET_DASHBOARD');
    }}
  />
)}

🧪 Phase 3: Testing Checklist

Pre-Migration Tests

Build passes: npm run build
No TypeScript errors
Database backup created

Migration Tests

Run SQL migration in Supabase
Verify 4 new columns exist
Verify indexes created
No SQL errors

Code Tests

Test 1: Company Creation (Signup)

Sign up as supplier: test@abctireshop.com
Verify company created with domain "abctireshop.com"
Check console for "New company created" or "Joined existing company"

Test 2: Seed Data (Company-Owned)

After signup, verify 6 demo requests appear
Check database: fleet_company_id should be populated
Check database: supplier_company_id should be populated
Check database: submitted_by_user_id should be populated

Test 3: Manual Request (Supplier)

Click "+ Manual" button
Fill form and submit
Check database: new request has supplier_company_id set
Verify request appears on dashboard

Test 4: Fleet Request (Fleet)

Sign up as fleet: test@xyzlogistics.com
Click "Send Request" to a supplier
Fill form and submit
Check database: new request has fleet_company_id set
Check database: new request has supplier_company_id set

Test 5: Team Visibility (CRITICAL)

Sign up user 1: joe@abctireshop.com
Create 2 requests
Sign up user 2: sarah@abctireshop.com (SAME DOMAIN)
Verify Sarah sees Joe's 2 requests ✅
This proves company-owned works!

Test 6: Daily Access (Org-Scoped)

Go to fleetos.com
Click "Login" → enter joe@abctireshop.com
Click magic link
Verify sees ALL company requests (not just Joe's)

Test 7: Emergency Access (One Sentence Test)

Simulate tired operator:
- Go to fleetos.com
- Enter work email
- Get magic link
- Click → see today's work
Verify sees ALL company work (not just personal)

Database Verification Queries

-- Check all requests have company IDs
SELECT
  request_id,
  fleet_company_id,
  supplier_company_id,
  submitted_by_user_id,
  CASE
    WHEN fleet_company_id IS NULL THEN '❌ Missing'
    ELSE '✅ Set'
  END as fleet_company_status,
  CASE
    WHEN supplier_company_id IS NULL THEN '❌ Missing'
    ELSE '✅ Set'
  END as supplier_company_status
FROM service_requests
ORDER BY created_at DESC
LIMIT 20;

-- Should show ✅ Set for all company columns

-- Verify team visibility
-- User 1: joe@abctireshop.com
-- User 2: sarah@abctireshop.com
-- Both should see SAME requests

SELECT
  c.company_name,
  c.domain,
  COUNT(sr.request_id) as total_requests
FROM companies c
LEFT JOIN service_requests sr ON sr.supplier_company_id = c.company_id
WHERE c.domain = 'abctireshop.com'
GROUP BY c.company_id, c.company_name, c.domain;

-- Should show count > 0 if seed data worked

🔄 Phase 4: Rollback Strategy

If Migration Fails

SQL Rollback:

-- Remove new columns (safe if nothing using them)
ALTER TABLE service_requests DROP COLUMN IF EXISTS fleet_company_id;
ALTER TABLE service_requests DROP COLUMN IF EXISTS supplier_company_id;
ALTER TABLE service_requests DROP COLUMN IF EXISTS submitted_by_user_id;
ALTER TABLE service_requests DROP COLUMN IF EXISTS assigned_to_user_id;

-- Drop indexes
DROP INDEX IF EXISTS idx_service_requests_fleet_company;
DROP INDEX IF EXISTS idx_service_requests_supplier_company;
DROP INDEX IF EXISTS idx_service_requests_submitted_by;
DROP INDEX IF EXISTS idx_service_requests_assigned_to;

Code Rollback:

# Revert to previous commit
git revert HEAD

# Or reset if not pushed
git reset --hard HEAD~1

📅 Deployment Plan

Step 1: Preparation

Backup database (Supabase automatic)
Review all changes one more time
Ensure dev environment clean

Step 2: Database Migration

Run SQL migration in Supabase SQL Editor
Verify columns created
Run verification queries

Step 3: Code Deployment

Update all code files per plan
Run npm run build - verify passes
Run manual tests (signup, create request, etc.)
Commit changes

Step 4: Verification

Test team visibility (2 users, same domain)
Test daily access magic links
Test emergency access flow
Verify One Sentence Test passes

Step 5: Documentation

Update FLEETOS_BIBLE_GAP_ANALYSIS.md
Mark migration complete
Update any affected docs

✅ Success Criteria

Migration is successful when:

✅ Database has 4 new columns
✅ All new requests use company IDs
✅ Team members see each other's work
✅ Boss sees all company work
✅ Daily access magic links work
✅ Emergency access works
✅ One Sentence Test passes
✅ Build passes with no errors
✅ No data loss or corruption
✅ BIBLE compliance = 100%

📊 Estimated Timeline

Phase

Task

Time

Database Migration

30 min

Code Changes

3 hours

Testing

2 hours

Documentation

30 min

Total

6 hours

🚀 Ready to Execute

Status: Plan approved, ready to implement

Next Steps:

Create database migration SQL file
Update code files per plan
Run tests
Deploy

Questions? Review FLEETOS_FUNDAMENTAL_DECISION.md for rationale.

Created: 2026-01-09 Status: APPROVED - Ready to Execute Next: Begin Phase 1 (Database Migration)

PreviousFleetOS BIBLE - Gap Analysis & Mapping NextFleetOS Emergent Contacts Migration Plan

Last updated 8 days ago

Was this helpful?

hashtag🎯 Objective

hashtag📋 Phase 1: Database Migration

hashtagStep 1.1: Add Company Columns to service_requests

hashtag📝 Phase 2: Code Changes

hashtagStep 2.1: Update QueryService.createServiceRequest()

hashtagStep 2.2: Update Seed Functions (Fleet Side)

hashtagStep 2.3: Update Seed Functions (Supplier Side)

hashtagStep 2.4: Update Manual Request Form

hashtagStep 2.5: Update Fleet Request Form

hashtagStep 2.6: Update App.tsx Route Handlers

hashtag🧪 Phase 3: Testing Checklist

hashtagPre-Migration Tests

hashtagMigration Tests

hashtagCode Tests

hashtagDatabase Verification Queries

hashtag🔄 Phase 4: Rollback Strategy

hashtagIf Migration Fails

hashtag📅 Deployment Plan

hashtagStep 1: Preparation

hashtagStep 2: Database Migration

hashtagStep 3: Code Deployment

hashtagStep 4: Verification

hashtagStep 5: Documentation

hashtag✅ Success Criteria

hashtag📊 Estimated Timeline

hashtag🚀 Ready to Execute

🎯 Objective

📋 Phase 1: Database Migration

Step 1.1: Add Company Columns to service_requests

📝 Phase 2: Code Changes

Step 2.1: Update QueryService.createServiceRequest()

Step 2.2: Update Seed Functions (Fleet Side)

Step 2.3: Update Seed Functions (Supplier Side)

Step 2.4: Update Manual Request Form

Step 2.5: Update Fleet Request Form

Step 2.6: Update App.tsx Route Handlers

🧪 Phase 3: Testing Checklist

Pre-Migration Tests

Migration Tests

Code Tests

Database Verification Queries

🔄 Phase 4: Rollback Strategy

If Migration Fails

📅 Deployment Plan

Step 1: Preparation

Step 2: Database Migration

Step 3: Code Deployment

Step 4: Verification

Step 5: Documentation

✅ Success Criteria

📊 Estimated Timeline

🚀 Ready to Execute