FleetOS Emergent Contacts Migration Plan

Date: 2026-01-09 Status: APPROVED - Ready to Execute Architecture: Company-Owned Work + Emergent Contacts Effort: 8 hours Risk: LOW

---

🎯 Objective

Implement BIBLE-compliant architecture:

• ✅ Companies own all work (permanent)

• ✅ Contacts are emergent actors (temporary, created on first access)

• ✅ Contacts can assign and be assigned (but don't own)

• ✅ No signup required

• ✅ Emergency access works

---

📋 Phase 1: Database Migration

Step 1.1: Create Migration File

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

-- ============================================
-- FLEETOS EMERGENT CONTACTS MIGRATION
-- Implements BIBLE-compliant architecture:
-- - Companies own the work
-- - Contacts are emergent actors (not owners)
-- Safe to run: Uses IF NOT EXISTS
-- ============================================

-- ============================================
-- PART 1: CONTACTS TABLE (Emergent)
-- ============================================

CREATE TABLE IF NOT EXISTS contacts (
  contact_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  company_id UUID NOT NULL REFERENCES companies(company_id) ON DELETE CASCADE,
  contact_value TEXT UNIQUE NOT NULL,  -- Canonical email or phone
  contact_type TEXT CHECK (contact_type IN ('email', 'phone')),
  display_name TEXT,
  first_seen_at TIMESTAMP DEFAULT NOW(),
  last_seen_at TIMESTAMP DEFAULT NOW()
);

-- Indexes for performance
CREATE INDEX IF NOT EXISTS idx_contacts_company ON contacts(company_id);
CREATE INDEX IF NOT EXISTS idx_contacts_value ON contacts(contact_value);
CREATE INDEX IF NOT EXISTS idx_contacts_last_seen ON contacts(last_seen_at DESC);

COMMENT ON TABLE contacts IS 'Emergent contacts - created on first access, no signup required';
COMMENT ON COLUMN contacts.contact_value IS 'Canonical email (lowercase) or phone (E.164 format)';
COMMENT ON COLUMN contacts.first_seen_at IS 'When this contact first accessed FleetOS';
COMMENT ON COLUMN contacts.last_seen_at IS 'Most recent access (for "last active" routing)';

-- ============================================
-- PART 2: ADD COMPANY COLUMNS TO SERVICE_REQUESTS
-- ============================================

-- Add fleet_company_id (companies own the work)
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 already exists';
  END IF;
END $$;

-- Add supplier_company_id (companies own the work)
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 already exists';
  END IF;
END $$;

-- Add submitted_by_contact_id (emergent actor, not owner)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='service_requests' AND column_name='submitted_by_contact_id'
  ) THEN
    ALTER TABLE service_requests
    ADD COLUMN submitted_by_contact_id UUID REFERENCES contacts(contact_id);

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

-- Add assigned_to_contact_id (emergent actor, not owner)
DO $$
BEGIN
  IF NOT EXISTS (
    SELECT 1 FROM information_schema.columns
    WHERE table_name='service_requests' AND column_name='assigned_to_contact_id'
  ) THEN
    ALTER TABLE service_requests
    ADD COLUMN assigned_to_contact_id UUID REFERENCES contacts(contact_id);

    RAISE NOTICE '✅ Added assigned_to_contact_id column';
  ELSE
    RAISE NOTICE 'ℹ️  assigned_to_contact_id 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_contact_id);

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

-- ============================================
-- PART 3: EVENT LOG (Audit Trail)
-- ============================================

CREATE TABLE IF NOT EXISTS event_log (
  event_id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  event_type TEXT NOT NULL,
  company_id UUID REFERENCES companies(company_id) ON DELETE CASCADE,
  contact_id UUID REFERENCES contacts(contact_id) ON DELETE SET NULL,
  target_request_id UUID REFERENCES service_requests(request_id) ON DELETE CASCADE,
  metadata JSONB,
  created_at TIMESTAMP DEFAULT NOW()
);

CREATE INDEX IF NOT EXISTS idx_event_log_company ON event_log(company_id);
CREATE INDEX IF NOT EXISTS idx_event_log_contact ON event_log(contact_id);
CREATE INDEX IF NOT EXISTS idx_event_log_created ON event_log(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_event_log_type ON event_log(event_type);

COMMENT ON TABLE event_log IS 'Audit trail - tracks every access and action';
COMMENT ON COLUMN event_log.contact_id IS 'Which emergent contact acted';

-- ============================================
-- PART 4: HELPER FUNCTIONS
-- ============================================

-- Function: Get or create emergent contact
CREATE OR REPLACE FUNCTION get_or_create_contact(
  p_contact_value TEXT,
  p_company_id UUID,
  p_contact_type TEXT,
  p_display_name TEXT DEFAULT NULL
) RETURNS UUID AS $$
DECLARE
  v_contact_id UUID;
BEGIN
  -- Try to find existing contact
  SELECT contact_id INTO v_contact_id
  FROM contacts
  WHERE contact_value = LOWER(TRIM(p_contact_value));

  -- If not found, create emergent contact
  IF v_contact_id IS NULL THEN
    INSERT INTO contacts (
      company_id,
      contact_value,
      contact_type,
      display_name,
      first_seen_at,
      last_seen_at
    ) VALUES (
      p_company_id,
      LOWER(TRIM(p_contact_value)),
      p_contact_type,
      p_display_name,
      NOW(),
      NOW()
    )
    RETURNING contact_id INTO v_contact_id;

    RAISE NOTICE '✨ Emergent contact created: %', p_contact_value;
  ELSE
    -- Update last_seen for existing contact
    UPDATE contacts
    SET last_seen_at = NOW()
    WHERE contact_id = v_contact_id;
  END IF;

  RETURN v_contact_id;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_or_create_contact IS 'Get existing or create emergent contact on first access';

-- Function: Get last active contact for company
CREATE OR REPLACE FUNCTION get_last_active_contact(p_company_id UUID)
RETURNS TABLE(contact_id UUID, contact_value TEXT, last_seen_at TIMESTAMP) AS $$
BEGIN
  RETURN QUERY
  SELECT c.contact_id, c.contact_value, c.last_seen_at
  FROM contacts c
  WHERE c.company_id = p_company_id
  ORDER BY c.last_seen_at DESC
  LIMIT 1;
END;
$$ LANGUAGE plpgsql;

COMMENT ON FUNCTION get_last_active_contact IS 'Find last active contact for org-level fallback notifications';

-- ============================================
-- PART 5: UPDATE STATUS ENUM
-- ============================================

-- Drop existing constraint if exists
DO $$
BEGIN
  IF EXISTS (
    SELECT 1 FROM information_schema.constraint_column_usage
    WHERE constraint_name = 'valid_status'
  ) THEN
    ALTER TABLE service_requests DROP CONSTRAINT valid_status;
    RAISE NOTICE '✅ Dropped old status constraint';
  END IF;
END $$;

-- Add new BIBLE-compliant status constraint
ALTER TABLE service_requests
ADD CONSTRAINT valid_status
CHECK (status IN ('new', 'acknowledged', 'in_progress', 'completed', 'declined'));

RAISE NOTICE '✅ Added BIBLE-compliant status constraint (new → acknowledged → in_progress → completed/declined)';

-- ============================================
-- MIGRATION COMPLETE
-- ============================================

DO $$
BEGIN
  RAISE NOTICE '========================================';
  RAISE NOTICE '✅ FLEETOS EMERGENT CONTACTS MIGRATION COMPLETE';
  RAISE NOTICE '========================================';
  RAISE NOTICE '';
  RAISE NOTICE 'Created:';
  RAISE NOTICE '  ✅ contacts table (emergent actors)';
  RAISE NOTICE '  ✅ event_log table (audit trail)';
  RAISE NOTICE '';
  RAISE NOTICE 'Added to service_requests:';
  RAISE NOTICE '  ✅ fleet_company_id (companies own work)';
  RAISE NOTICE '  ✅ supplier_company_id (companies own work)';
  RAISE NOTICE '  ✅ submitted_by_contact_id (emergent actor)';
  RAISE NOTICE '  ✅ assigned_to_contact_id (emergent actor)';
  RAISE NOTICE '';
  RAISE NOTICE 'Helper functions:';
  RAISE NOTICE '  ✅ get_or_create_contact() - emergent contact creation';
  RAISE NOTICE '  ✅ get_last_active_contact() - for fallback routing';
  RAISE NOTICE '';
  RAISE NOTICE 'Status lifecycle: new → acknowledged → in_progress → completed/declined';
  RAISE NOTICE '';
  RAISE NOTICE '⚠️  NEXT: Update application code to use new architecture';
  RAISE NOTICE '========================================';
END $$;

How to run:

1. Copy entire SQL above

2. Open Supabase SQL Editor: https://supabase.com/dashboard/project/YOUR_PROJECT/sql

3. Paste SQL

4. Click "Run"

5. Verify success messages in output

---

Step 1.2: Verify Migration

Run these queries to confirm:

-- Check contacts table exists
SELECT table_name, column_name, data_type
FROM information_schema.columns
WHERE table_name = 'contacts'
ORDER BY ordinal_position;

-- Should return 7 columns:
-- contact_id, company_id, contact_value, contact_type, display_name, first_seen_at, last_seen_at

-- Check service_requests columns
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_contact_id',
    'assigned_to_contact_id'
  );

-- Should return 4 rows

-- Check event_log exists
SELECT table_name
FROM information_schema.tables
WHERE table_name = 'event_log';

-- Should return 1 row

-- Test helper function
SELECT get_or_create_contact(
  '[e-mail]',
  (SELECT company_id FROM companies LIMIT 1),
  'email',
  'Test User'
);

-- Should return a UUID

---

📝 Phase 2: Code Changes

Step 2.1: Create ContactService

File: services/shared/ContactService.ts (NEW)

/**
 * ContactService - Emergent Contact Management
 *
 * Contacts are NOT users:
 * - No signup required
 * - Created on first access (emergent)
 * - Can assign and be assigned
 * - Do NOT own work (companies own)
 */

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

export interface Contact {
  contact_id: string;
  company_id: string;
  contact_value: string;  // email or phone
  contact_type: 'email' | 'phone';
  display_name?: string;
  first_seen_at: string;
  last_seen_at: string;
}

export class ContactService {
  /**
   * Get or create emergent contact
   * Called when someone uses a magic link for the first time
   */
  static async getOrCreate(
    contactValue: string,
    companyId: string,
    contactType: 'email' | 'phone',
    displayName?: string
  ): Promise<Contact> {
    // Normalize contact value
    const normalized = contactType === 'email'
      ? contactValue.toLowerCase().trim()
      : contactValue.replace(/\D/g, ''); // Remove non-digits from phone

    // Try to find existing contact
    const { data: existing, error: findError } = await supabase
      .from('contacts')
      .select('*')
      .eq('contact_value', normalized)
      .single();

    if (existing) {
      // Update last_seen timestamp
      await supabase
        .from('contacts')
        .update({ last_seen_at: new Date().toISOString() })
        .eq('contact_id', existing.contact_id);

      console.log('✅ Existing contact:', normalized);
      return existing;
    }

    // Create emergent contact (first access)
    const { data: newContact, error: createError } = await supabase
      .from('contacts')
      .insert({
        company_id: companyId,
        contact_value: normalized,
        contact_type: contactType,
        display_name: displayName,
      })
      .select()
      .single();

    if (createError) throw createError;

    console.log('✨ Emergent contact created:', normalized);
    return newContact;
  }

  /**
   * Get last active contact for company
   * Used for org-level fallback notifications
   */
  static async getLastActive(companyId: string): Promise<Contact | null> {
    const { data, error } = await supabase
      .from('contacts')
      .select('*')
      .eq('company_id', companyId)
      .order('last_seen_at', { ascending: false })
      .limit(1)
      .single();

    if (error || !data) return null;
    return data;
  }

  /**
   * Get all contacts for a company
   * For display/assignment UI
   */
  static async getForCompany(companyId: string): Promise<Contact[]> {
    const { data, error } = await supabase
      .from('contacts')
      .select('*')
      .eq('company_id', companyId)
      .order('last_seen_at', { ascending: false });

    if (error) throw error;
    return data || [];
  }

  /**
   * Update display name
   * Optional - can be set from magic link payload or manually
   */
  static async updateDisplayName(
    contactId: string,
    displayName: string
  ): Promise<void> {
    await supabase
      .from('contacts')
      .update({ display_name: displayName })
      .eq('contact_id', contactId);
  }
}

export default ContactService;

---

Step 2.2: Create EventLogService

File: services/shared/EventLogService.ts (NEW)

/**
 * EventLogService - Audit Trail
 * Records every access and action for BIBLE compliance
 */

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

export interface EventLog {
  event_id: string;
  event_type: string;
  company_id: string;
  contact_id?: string;
  target_request_id?: string;
  metadata?: Record<string, any>;
  created_at: string;
}

export class EventLogService {
  /**
   * Log an event
   */
  static async log(event: {
    event_type: string;
    company_id: string;
    contact_id?: string;
    target_request_id?: string;
    metadata?: Record<string, any>;
  }): Promise<void> {
    const { error } = await supabase
      .from('event_log')
      .insert({
        event_type: event.event_type,
        company_id: event.company_id,
        contact_id: event.contact_id,
        target_request_id: event.target_request_id,
        metadata: event.metadata,
      });

    if (error) {
      console.error('Failed to log event:', error);
      // Don't throw - logging failures shouldn't break main flow
    }
  }

  /**
   * Get recent events for a company (audit trail)
   */
  static async getForCompany(
    companyId: string,
    limit: number = 100
  ): Promise<EventLog[]> {
    const { data, error } = await supabase
      .from('event_log')
      .select('*')
      .eq('company_id', companyId)
      .order('created_at', { ascending: false })
      .limit(limit);

    if (error) throw error;
    return data || [];
  }

  /**
   * Get events for a specific request (audit trail)
   */
  static async getForRequest(requestId: string): Promise<EventLog[]> {
    const { data, error } = await supabase
      .from('event_log')
      .select('*')
      .eq('target_request_id', requestId)
      .order('created_at', { ascending: false });

    if (error) throw error;
    return data || [];
  }
}

export default EventLogService;

---

Step 2.3: Update MagicLinkService

File: services/shared/MagicLinkService.ts

Add to MagicLinkPayload interface:

export interface MagicLinkPayload {
  email?: string;
  phone?: string;
  product?: 'fleetos' | 'robo-hub' | 'robo-dapp';

  // EMERGENT CONTACTS (BIBLE-compliant)
  contact_id?: string;        // ✅ NEW - emergent contact ID
  company_id?: string;         // ✅ Org scope
  company_name?: string;       // For display

  // Capabilities
  targetPath?: string;
  rfqId?: string;              // RFQ-scoped link

  // Expiry
  exp?: number;

  [key: string]: any;
}

No other changes needed to MagicLinkService - it already handles encoding/decoding!

---

Step 2.4: Update AuthService.sendDailyAccessLink()

File: services/AuthService.ts

Find the sendDailyAccessLink() method and update:

async sendDailyAccessLink(email: string): Promise<{ success: boolean; message?: string; error?: string }> {
  try {
    // Block free email domains (optional - or handle via contact mapping)
    if (isFreeEmailDomain(email)) {
      return {
        success: false,
        error: 'Please use your company email address (not Gmail, Yahoo, etc.)',
      };
    }

    const domain = extractDomain(email);

    // Look up company by domain (or create if first user)
    let { data: company, error: companyError } = await supabase
      .from('companies')
      .select('company_id, company_name, domain')
      .eq('domain', domain)
      .single();

    if (companyError || !company) {
      // First user from this domain - create company
      const suggestedName = domain.split('.')[0]
        .replace(/[-_]/g, ' ')
        .split(' ')
        .map(w => w.charAt(0).toUpperCase() + w.slice(1))
        .join(' ');

      const { data: newCompany, error: createError } = await supabase
        .from('companies')
        .insert({
          domain,
          company_name: suggestedName,
          company_type: 'supplier', // Default, can change later
        })
        .select()
        .single();

      if (createError) {
        console.error('Failed to create company:', createError);
        return {
          success: false,
          error: 'Failed to set up your company. Please contact support.',
        };
      }

      company = newCompany;
      console.log('✨ New company created:', domain);
    }

    // Get or create emergent contact
    const contact = await ContactService.getOrCreate(
      email,
      company.company_id,
      'email'
    );

    // Generate org-scoped magic link
    const token = MagicLinkService.encodeToken({
      contact_id: contact.contact_id,     // ✅ Emergent contact
      company_id: company.company_id,      // ✅ Org scope
      company_name: company.company_name,  // For display
      email: email,                        // For display
      product: 'fleetos',
      accessType: 'org_daily_access',
    });

    const magicLink = `${this.config.baseUrl}/auth/magic?token=${token}`;

    // Log the access request
    await EventLogService.log({
      event_type: 'magic_link_requested',
      company_id: company.company_id,
      contact_id: contact.contact_id,
      metadata: { email, access_type: 'daily_access' },
    });

    // Send magic link via email
    // (Implement email sending here or use existing NotificationService)
    console.log('🔗 Magic Link:', magicLink);
    console.log('📧 Send to:', email);

    return {
      success: true,
      message: 'Check your email for the access link!',
    };
  } catch (error: any) {
    console.error('Error in sendDailyAccessLink:', error);
    return {
      success: false,
      error: 'Failed to send access link. Please try again.',
    };
  }
}

---

Step 2.5: Update QueryService.createServiceRequest()

File: services/shared/QueryService.ts

Replace the createServiceRequest method:

/**
 * Create a new service request (COMPANY-OWNED)
 * Work is owned by companies, contacts are emergent actors
 */
static async createServiceRequest(requestData: {
  fleet_company_id: string;
  supplier_company_id: string;
  submitted_by_contact_id?: string;  // Optional emergent contact
  assigned_to_contact_id?: string;   // Optional emergent contact
  description: string;
  vehicle_identifier?: string;
  location?: string;
  urgency: 'URGENT' | 'TODAY' | 'SCHEDULED';
  status?: string;
  source_product?: string;
  created_via?: string;
  fleet_name?: string;
  fleet_contact?: string;
  contact_type?: string;
  contact_name?: string;
  is_manual_entry?: boolean;
  scheduled_time?: string;
}) {
  const { data, error } = await supabase
    .from('service_requests')
    .insert({
      // COMPANIES OWN THE WORK ✅
      fleet_company_id: requestData.fleet_company_id,
      supplier_company_id: requestData.supplier_company_id,

      // CONTACTS ARE ACTORS (emergent, optional) ✅
      submitted_by_contact_id: requestData.submitted_by_contact_id,
      assigned_to_contact_id: requestData.assigned_to_contact_id,

      // Work data
      description: requestData.description,
      vehicle_identifier: requestData.vehicle_identifier,
      location: requestData.location,
      urgency: requestData.urgency,
      status: requestData.status || 'new',
      source_product: requestData.source_product || 'fleetos',
      created_via: requestData.created_via || 'manual',

      // Legacy fields for display (optional)
      fleet_name: requestData.fleet_name,
      fleet_contact: requestData.fleet_contact,
      contact_name: requestData.contact_name,
      contact_type: requestData.contact_type,
      is_manual_entry: requestData.is_manual_entry || false,
      scheduled_time: requestData.scheduled_time,
    })
    .select()
    .single();

  if (error) throw error;

  // Log the creation
  if (requestData.submitted_by_contact_id) {
    await EventLogService.log({
      event_type: 'rfq_created',
      company_id: requestData.supplier_company_id,
      contact_id: requestData.submitted_by_contact_id,
      target_request_id: data.request_id,
      metadata: {
        urgency: requestData.urgency,
        created_via: requestData.created_via,
      },
    });
  }

  return data;
}

---

Step 2.6: Update Seed Functions

File: App.tsx

Update seedShepherdRelationships:

async function seedShepherdRelationships(
  shepherdId: string,
  fleetCompanyId: string,
  contactId: string  // ✅ NEW - emergent contact
): 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 from domain
    const { data: supplierCompany } = await supabase
      .from('companies')
      .select('company_id')
      .eq('domain', extractDomain(tireSupplier.email || '[e-mail]'))
      .single();

    if (!supplierCompany) {
      console.warn('No supplier company found for seed data');
      return { success: true };
    }

    const demoRequests = [
      {
        // COMPANIES OWN ✅
        fleet_company_id: fleetCompanyId,
        supplier_company_id: supplierCompany.company_id,

        // CONTACT ACTS ✅
        submitted_by_contact_id: contactId,

        // Work data
        description: 'Flat tire on front passenger side. Need immediate replacement.',
        vehicle_identifier: 'Truck #127',
        location: '555 Market St, San Francisco, CA',
        urgency: 'URGENT' as const,
        status: 'new' as const,
        source_product: 'fleetos' as const,
        created_via: 'demo_seed',
        fleet_name: 'ABC Logistics',
        fleet_contact: '[e-mail]',
      },
      // ... add 5 more with variations
    ];

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

    console.log('✅ Seeded 6 FleetOS requests (COMPANY-OWNED)');
    return { success: true };
  } catch (error: any) {
    console.error('❌ Seed failed:', error);
    return { success: false, error: error.message };
  }
}

Update call site (around line 1050):

// After creating company and contact in signup
const seedResult = await seedShepherdRelationships(
  userId,
  companyId,     // ✅ Company owns
  contactId      // ✅ Contact acts
);

---

Step 2.7: Update Signup Handlers

File: App.tsx

Update FLEETOS_SUPPLIER_ONBOARDING handler:

{currentView === 'FLEETOS_SUPPLIER_ONBOARDING' && (
  <MinimalSignup
    userType="supplier"
    error={signupError}
    onClearError={() => setSignupError(null)}
    onComplete={async (data) => {
      setSignupError(null);

      try {
        const email = data.email;
        const domain = extractDomain(email);

        // Get or create company
        const { companyId, isNew: isNewCompany } = await getOrCreateCompany(
          supabase,
          email,
          data.companyName,
          'supplier'
        );

        // Create emergent contact (no user record!)
        const contact = await ContactService.getOrCreate(
          email,
          companyId,
          'email',
          data.contactName || data.companyName
        );

        // Generate magic link
        const magicLink = NotificationService.generateMagicLink({
          contact_id: contact.contact_id,    // ✅ Emergent contact
          company_id: companyId,              // ✅ Org scope
          company_name: data.companyName,     // Display
          email: email,                       // Display
          userType: 'SUPPLIER',
          targetPath: '/fleetos/supplier/dashboard',
          expiresInMinutes: 60 * 24,
        });

        console.log('✨ Emergent contact created:', email);
        console.log('🏢 Company:', data.companyName);
        console.log('🔗 Magic Link:', magicLink.url);

        // Save to session (minimal state)
        setStoredUser({
          id: contact.contact_id,
          company_id: companyId,
          email: email,
          name: data.contactName || data.companyName,
          type: 'supplier',
        });

        // Seed demo data (company-owned)
        await seedSupplierRelationships(
          contact.contact_id,
          companyId
        );

        // Navigate to dashboard
        handleNavigation('FLEETOS_SUPPLIER_DASHBOARD');
      } catch (error: any) {
        console.error('Signup failed:', error);
        setSignupError('Failed to create account. Please try again.');
      }
    }}
  />
)}

Similar update for FLEETOS_FLEET_ONBOARDING (same pattern)

---

Step 2.8: Update Dashboard Queries

File: components/fleetos/components/SupplierTodoList.tsx

Update the query to use company_id:

useEffect(() => {
  async function fetchRequests() {
    try {
      const { data, error } = await supabase
        .from('service_requests')
        .select('*')
        .eq('supplier_company_id', companyId)  // ✅ Company-scoped
        .order('created_at', { ascending: false });

      if (error) throw error;

      // Optional: Filter by assigned contact
      let filtered = data || [];
      if (showOnlyMyTasks && userId) {
        filtered = filtered.filter(r =>
          r.assigned_to_contact_id === userId  // userId is actually contact_id
        );
      }

      setRequests(filtered);
    } catch (error) {
      console.error('Failed to fetch requests:', error);
    }
  }

  fetchRequests();
}, [companyId, userId, showOnlyMyTasks]);

---

🧪 Phase 3: Testing Checklist

Pre-Migration Tests

• Backup database

• npm run build passes

• No TypeScript errors

Migration Tests

• Run SQL migration in Supabase

• Verify contacts table created

• Verify event_log table created

• Verify 4 new columns in service_requests

• Test get_or_create_contact() function

• Test get_last_active_contact() function

Code Tests

Test 1: Emergent Contact Creation

1. Go to fleetos.com
2. Click "Login"
3. Enter: [e-mail]
4. Check console: "✨ Emergent contact created"
5. Check database:
   SELECT * FROM contacts WHERE contact_value = '[e-mail]';
   Should show: first_seen_at = now, company_id set

Test 2: Repeat Access (No Duplicate)

1. Request magic link again: [e-mail]
2. Check console: "✅ Existing contact"
3. Check database:
   SELECT * FROM contacts WHERE contact_value = '[e-mail]';
   Should show: last_seen_at updated, only 1 record

Test 3: Company-Owned Work

1. Sign up as [e-mail]
2. Verify 6 demo requests created
3. Check database:
   SELECT fleet_company_id, supplier_company_id, submitted_by_contact_id
   FROM service_requests
   LIMIT 5;

   Should show:
   - fleet_company_id: NOT NULL
   - supplier_company_id: NOT NULL
   - submitted_by_contact_id: joe's contact_id

Test 4: Team Visibility (CRITICAL)

1. Sign up [e-mail] → creates 6 requests
2. Sign up [e-mail] (SAME DOMAIN)
3. Sarah should see ALL 6 requests (company-owned!)
4. Check query:
   WHERE supplier_company_id = (company_id for abctireshop.com)

   Should return 6 requests for both Joe and Sarah

Test 5: Assignment

1. As Joe, assign Request #1 to Sarah
2. Check database:
   SELECT assigned_to_contact_id FROM service_requests WHERE request_id = 'req-1';
   Should show: sarah's contact_id

3. Check event_log:
   SELECT * FROM event_log WHERE event_type = 'assignment_changed';
   Should show: Joe assigned to Sarah

Test 6: Emergency Access

1. Close all tabs
2. Go to fleetos.com
3. Enter: [e-mail] (new person, same domain)
4. Get magic link, click
5. Should see ALL company work (Joe's + Sarah's requests)
6. Verify: Emergency operator sees everything

Test 7: Event Log Audit

1. Perform various actions (create request, assign, update status)
2. Query event_log:
   SELECT event_type, contact_id, created_at
   FROM event_log
   WHERE company_id = (abctireshop company_id)
   ORDER BY created_at DESC;

   Should show: Complete audit trail

Verification Queries

-- Check all requests are company-owned
SELECT
  COUNT(*) as total_requests,
  COUNT(fleet_company_id) as with_fleet_company,
  COUNT(supplier_company_id) as with_supplier_company,
  COUNT(submitted_by_contact_id) as with_submitted_by
FROM service_requests;

-- Should show: All have company IDs

-- Check emergent contacts
SELECT
  c.contact_value,
  c.display_name,
  c.first_seen_at,
  c.last_seen_at,
  co.company_name
FROM contacts c
JOIN companies co ON c.company_id = co.company_id
ORDER BY c.first_seen_at DESC;

-- Should show: Contacts emerged from access

-- Check event log
SELECT
  event_type,
  COUNT(*) as count
FROM event_log
GROUP BY event_type
ORDER BY count DESC;

-- Should show: Various event types logged

---

🔄 Phase 4: Rollback Strategy

If Migration Fails

SQL Rollback:

-- Drop new tables
DROP TABLE IF EXISTS event_log CASCADE;
DROP TABLE IF EXISTS contacts CASCADE;

-- Remove new columns
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_contact_id;
ALTER TABLE service_requests DROP COLUMN IF EXISTS assigned_to_contact_id;

-- Drop helper functions
DROP FUNCTION IF EXISTS get_or_create_contact;
DROP FUNCTION IF EXISTS get_last_active_contact;

Code Rollback:

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

---

✅ Success Criteria

Migration succeeds when:

1. ✅ contacts table exists with helper functions

2. ✅ event_log table exists

3. ✅ service_requests has 4 new columns

4. ✅ Emergent contact creation works (no signup required)

5. ✅ Team visibility works (same domain = see all work)

6. ✅ Assignment works (contacts can assign)

7. ✅ Emergency access works (any domain member can access)

8. ✅ Event log captures all actions

9. ✅ One Sentence Test passes

10. ✅ Build passes with no errors

---

📊 Timeline

Phase	Task	Time
1	Database Migration	1 hour
2	Code Changes	4 hours
3	Testing	2 hours
4	Documentation	1 hour
Total		8 hours

---

🚀 Execution Order

1. ✅ Phase 1: Run database migration

2. ✅ Phase 2: Update all code files

3. ✅ Phase 3: Run all tests

4. ✅ Phase 4: Document results

5. ✅ Commit: Save to git

---

📝 Files Changed Summary

New Files (3):

1. database/fleetos/emergent_contacts_migration.sql

2. services/shared/ContactService.ts

3. services/shared/EventLogService.ts

Modified Files (5):

1. services/shared/MagicLinkService.ts - Add contact_id to payload

2. services/AuthService.ts - Use emergent contacts

3. services/shared/QueryService.ts - Company-owned requests

4. App.tsx - Update signup handlers and seed functions

5. components/fleetos/components/SupplierTodoList.tsx - Company-scoped queries

Total: 8 files

---

✅ Ready to Execute

Architecture: BIBLE-compliant emergent contacts Risk: LOW (additive changes) Effort: 8 hours Status: APPROVED

Next: Begin Phase 1 (Database Migration)

---

Created: 2026-01-09 Author: Claude Sonnet 4.5 + Founder Approval Status: Ready to Execute