Milkmen Delivery - Shared Agent CRM Architecture Plan

CREATE TABLE territories (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  name text NOT NULL,                    -- "SoHo", "Lower Manhattan"
  region text NOT NULL,                   -- "NYC", "LA"
  boundaries jsonb,                       -- GeoJSON polygon
  center_lat numeric,
  center_lng numeric,
  active boolean DEFAULT true,
  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now()
);

-- Many-to-many: agents can share territories
CREATE TABLE territory_agents (
  territory_id uuid REFERENCES territories(id),
  agent_id uuid REFERENCES agents(id),
  role text NOT NULL CHECK (role IN ('seeder', 'closer', 'both')),
  visit_order integer NOT NULL,           -- 1 = visits first, 2 = visits second
  active boolean DEFAULT true,
  PRIMARY KEY (territory_id, agent_id)
);

CREATE TABLE visit_sequences (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  location_id uuid REFERENCES locations(id) ON DELETE CASCADE,
  territory_id uuid REFERENCES territories(id),

  -- Sequence definition
  current_step integer NOT NULL DEFAULT 1,  -- Which step we're on (1, 2, 3...)
  total_steps integer NOT NULL DEFAULT 2,   -- Total planned steps
  status text CHECK (status IN ('pending', 'in_progress', 'completed', 'paused')),

  -- Timing
  started_at timestamptz,
  completed_at timestamptz,

  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),

  UNIQUE(location_id)  -- One active sequence per location
);

CREATE TABLE visit_steps (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  sequence_id uuid REFERENCES visit_sequences(id) ON DELETE CASCADE,
  step_number integer NOT NULL,             -- 1, 2, 3...

  -- Assignment
  agent_id uuid REFERENCES agents(id),
  agent_role text,                          -- 'seeder', 'closer'

  -- Scheduling
  scheduled_date date,
  scheduled_time_window text,               -- 'morning', 'afternoon', 'any'
  days_after_previous integer DEFAULT 7,    -- Auto-schedule N days after step N-1

  -- Execution
  status text CHECK (status IN ('pending', 'scheduled', 'completed', 'skipped', 'rescheduled')),
  completed_at timestamptz,
  completed_by_agent_id uuid REFERENCES agents(id),

  -- Outcome (links to visit_outcomes for details)
  outcome_id uuid REFERENCES visit_outcomes(id),
  outcome_summary text,                     -- Quick reference

  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),

  UNIQUE(sequence_id, step_number)
);

CREATE TABLE agent_schedules (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  agent_id uuid REFERENCES agents(id) ON DELETE CASCADE,
  date date NOT NULL,

  -- Daily capacity
  max_visits integer DEFAULT 12,
  scheduled_visits integer DEFAULT 0,

  -- Time blocks
  available_from time DEFAULT '09:00',
  available_until time DEFAULT '17:00',

  -- Status
  status text CHECK (status IN ('open', 'full', 'blocked', 'off')),
  notes text,

  created_at timestamptz DEFAULT now(),
  updated_at timestamptz DEFAULT now(),

  UNIQUE(agent_id, date)
);

-- Pre-generated schedule slots
CREATE TABLE schedule_slots (
  id uuid PRIMARY KEY DEFAULT gen_random_uuid(),
  schedule_id uuid REFERENCES agent_schedules(id) ON DELETE CASCADE,
  visit_step_id uuid REFERENCES visit_steps(id),

  -- Slot details
  slot_order integer NOT NULL,
  estimated_time time,
  estimated_duration_minutes integer DEFAULT 30,

  -- Location quick reference
  location_id uuid REFERENCES locations(id),
  location_title text,
  location_address text,

  -- Status
  status text CHECK (status IN ('planned', 'confirmed', 'completed', 'cancelled', 'no_show')),
  completed_at timestamptz,

  created_at timestamptz DEFAULT now(),

  UNIQUE(schedule_id, slot_order)
);

ALTER TABLE locations ADD COLUMN IF NOT EXISTS
  current_sequence_id uuid REFERENCES visit_sequences(id),
  next_scheduled_visit_id uuid REFERENCES visit_steps(id),
  sequence_status text;  -- Quick lookup: 'seeding', 'awaiting_closer', 'in_closing', 'completed'

-- Auto-schedule next visit after completion
CREATE OR REPLACE FUNCTION auto_schedule_next_visit()
RETURNS TRIGGER AS $$
DECLARE
  next_step visit_steps%ROWTYPE;
  next_agent_id uuid;
  schedule_date date;
BEGIN
  -- Only run when step is completed
  IF NEW.status = 'completed' AND OLD.status != 'completed' THEN
    -- Find next step in sequence
    SELECT * INTO next_step
    FROM visit_steps
    WHERE sequence_id = NEW.sequence_id
      AND step_number = NEW.step_number + 1;

    IF FOUND THEN
      -- Calculate schedule date
      schedule_date := COALESCE(NEW.completed_at::date, CURRENT_DATE) + next_step.days_after_previous;

      -- Update next step
      UPDATE visit_steps
      SET scheduled_date = schedule_date,
          status = 'scheduled'
      WHERE id = next_step.id;

      -- Create schedule slot for next agent
      -- (Additional logic for slot creation)
    END IF;
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_auto_schedule_next
  AFTER UPDATE ON visit_steps
  FOR EACH ROW EXECUTE FUNCTION auto_schedule_next_visit();

src/features/
├── territories/
│   ├── components/
│   │   ├── TerritoryList.tsx
│   │   ├── TerritoryMap.tsx
│   │   └── TerritoryAgentAssignment.tsx
│   ├── hooks/
│   │   ├── useTerritories.ts
│   │   └── useTerritoryLocations.ts
│   └── index.ts
│
├── scheduling/
│   ├── components/
│   │   ├── AgentCalendar.tsx
│   │   ├── WeekView.tsx
│   │   ├── DaySchedule.tsx
│   │   └── ScheduleSlotCard.tsx
│   ├── hooks/
│   │   ├── useAgentSchedule.ts
│   │   ├── useScheduleBuilder.ts
│   │   └── useAutoScheduler.ts
│   └── services/
│       └── schedule-generator.ts
│
├── visits/
│   ├── components/
│   │   ├── VisitSequenceCard.tsx
│   │   ├── VisitStepProgress.tsx
│   │   ├── VisitOutcomeForm.tsx
│   │   └── SequentialVisitTimeline.tsx
│   ├── hooks/
│   │   ├── useVisitSequence.ts
│   │   ├── useVisitStep.ts
│   │   └── useCompleteVisit.ts
│   └── index.ts
│
├── dashboard/
│   ├── components/
│   │   ├── UnifiedDashboard.tsx
│   │   ├── AgentSwitcher.tsx
│   │   ├── TodaySchedule.tsx
│   │   ├── UpcomingVisits.tsx
│   │   └── TeamMetrics.tsx
│   ├── hooks/
│   │   └── useDashboardData.ts
│   └── index.ts
│
└── shared/
    ├── components/
    │   ├── LocationCard.tsx
    │   ├── AgentBadge.tsx
    │   └── StatusBadge.tsx
    └── hooks/
        └── useLocations.ts

// src/features/dashboard/components/UnifiedDashboard.tsx
interface UnifiedDashboardProps {
  defaultAgent?: string;  // Pre-select agent
}

export function UnifiedDashboard({ defaultAgent }: UnifiedDashboardProps) {
  const [selectedAgent, setSelectedAgent] = useState<string | 'all'>(defaultAgent || 'all');
  const { agents } = useAgents();
  const { schedule, isLoading } = useAgentSchedule(selectedAgent);

  return (
    <MainLayout>
      {/* Agent Switcher - Top Bar */}
      <AgentSwitcher
        agents={agents}
        selected={selectedAgent}
        onSelect={setSelectedAgent}
      />

      {/* Today's Schedule */}
      <TodaySchedule
        agent={selectedAgent}
        schedule={schedule?.today}
      />

      {/* Week Overview */}
      <WeekView
        agent={selectedAgent}
        schedule={schedule?.week}
      />

      {/* Pending Actions */}
      <PendingActions agent={selectedAgent} />

      {/* Team Metrics (when 'all' selected) */}
      {selectedAgent === 'all' && <TeamMetrics />}
    </MainLayout>
  );
}

// src/features/scheduling/components/AgentCalendar.tsx
interface AgentCalendarProps {
  agentId: string;
  weekStart: Date;
}

export function AgentCalendar({ agentId, weekStart }: AgentCalendarProps) {
  const { schedule, regenerate, addSlot, removeSlot } = useAgentSchedule(agentId, weekStart);

  return (
    <div className="grid grid-cols-7 gap-2">
      {schedule.days.map(day => (
        <DayColumn
          key={day.date}
          date={day.date}
          slots={day.slots}
          capacity={day.maxVisits}
          onSlotClick={(slot) => openSlotDetails(slot)}
          onAddClick={() => addSlot(day.date)}
        />
      ))}
    </div>
  );
}

// src/features/visits/components/VisitSequenceCard.tsx
interface VisitSequenceCardProps {
  locationId: string;
}

export function VisitSequenceCard({ locationId }: VisitSequenceCardProps) {
  const { sequence, steps, currentStep } = useVisitSequence(locationId);

  return (
    <Card>
      <CardHeader>
        <CardTitle>{sequence.location.title}</CardTitle>
        <Badge>{sequence.status}</Badge>
      </CardHeader>
      <CardContent>
        {/* Visual timeline showing all steps */}
        <div className="flex items-center gap-2">
          {steps.map((step, i) => (
            <React.Fragment key={step.id}>
              <StepIndicator
                step={step}
                isCurrent={i === currentStep - 1}
              />
              {i < steps.length - 1 && (
                <Arrow days={steps[i + 1].days_after_previous} />
              )}
            </React.Fragment>
          ))}
        </div>

        {/* Current step details */}
        <CurrentStepDetails step={steps[currentStep - 1]} />
      </CardContent>
    </Card>
  );
}

1. MORNING - Check Today's Schedule
   ├── Open dashboard → See pre-planned visits
   ├── Review each location's context (previous visits, notes)
   └── Confirm/adjust schedule if needed

2. DURING DAY - Execute Visits
   ├── Navigate to location (Google Maps integration)
   ├── Complete visit → Log outcome
   ├── System auto-schedules partner's follow-up
   └── See updated metrics

3. END OF DAY - Review
   ├── See completion stats
   ├── Review tomorrow's schedule
   └── Flag any issues for attention

LOCATION: "Blue Bottle - SoHo"

Step 1: Carson (Seeder) - Day 1
├── Scheduled: Monday 10:00 AM
├── Visit: Introduce product, leave samples
├── Outcome: "Left samples, interested, speak to owner Friday"
└── Auto-trigger: Schedule Mohamed for Day 1 + 5 days = Saturday

Step 2: Mohamed (Closer) - Day 6 (Auto-Scheduled)
├── Scheduled: Saturday 11:00 AM (auto-generated)
├── Context: Sees Carson's notes, outcome
├── Visit: Follow-up with owner
└── Outcome: "Closed! First order: 10 boxes"

Step 3: (Optional) Carson - Day 13
├── If needed: Check-in visit
└── Or: Mark sequence complete

WEEKLY SCHEDULE GENERATION

Input:
├── Territory: "SoHo"
├── Agents: Carson (seeder), Mohamed (closer)
├── Week: Feb 10-14, 2025
└── Settings: 12 visits/day max, 7-day follow-up gap

Process:
1. Get all locations needing seeding (no recent visit)
2. Get all locations awaiting closing (seeded, due for follow-up)
3. Prioritize by: urgency, clustering (nearby locations)
4. Distribute across week:
   - Carson: Seed new locations Mon-Wed
   - Mohamed: Close follow-ups Thu-Fri
   - Both: Flexible time blocks

Output:
├── Carson's Schedule: [Mon: 10 visits, Tue: 12, Wed: 11] (seeding)
├── Mohamed's Schedule: [Thu: 8, Fri: 9] (closing follow-ups)
└── Auto-scheduled follow-ups for next week

TanStack Query (keep)
├── Server state (locations, schedules, agents)
├── Caching with invalidation
└── Optimistic updates for visit completion

Zustand (for local state)
├── UI state (selected agent, filters)
├── Draft schedules before saving
└── Undo/redo for schedule changes

Supabase Realtime (existing)
├── Subscribe to schedule_slots changes
├── Live updates when partner completes visit
└── Notifications for auto-scheduled visits

Single implementation: src/services/route-optimization/
├── Cluster nearby locations
├── Minimize travel between slots
└── Respect time windows

-- Generate weekly schedule for agent
CREATE FUNCTION generate_agent_week(
  p_agent_id uuid,
  p_week_start date,
  p_territory_ids uuid[]
) RETURNS jsonb;

-- Complete a visit step with auto-scheduling
CREATE FUNCTION complete_visit_step(
  p_step_id uuid,
  p_outcome_data jsonb
) RETURNS jsonb;

-- Get agent's schedule for date range
CREATE FUNCTION get_agent_schedule(
  p_agent_id uuid,
  p_start_date date,
  p_end_date date
) RETURNS jsonb;

-- Get unified dashboard data
CREATE FUNCTION get_dashboard_data(
  p_agent_id uuid DEFAULT NULL,  -- NULL = all agents
  p_date date DEFAULT CURRENT_DATE
) RETURNS jsonb;

1. Keep old system running during transition
2. Create new tables alongside existing
3. Build new features on new tables
4. Migrate data incrementally
5. Switch over when ready
6. Archive old code

-- Create sequences for locations with visit history
INSERT INTO visit_sequences (location_id, territory_id, current_step, total_steps, status)
SELECT
  l.id,
  t.id,
  CASE
    WHEN l.third_visit IS NOT NULL THEN 3
    WHEN l.second_visit IS NOT NULL THEN 2
    WHEN l.first_visit IS NOT NULL THEN 1
    ELSE 1
  END,
  2,  -- Default to 2-step sequences
  CASE
    WHEN l.is_partner OR l.stage = 'closed_won' THEN 'completed'
    ELSE 'in_progress'
  END
FROM locations l
JOIN territories t ON ST_Contains(t.boundaries, ST_Point(l.longitude, l.latitude));