Sponsor CRM (Phase 1)

[Starefossen]

Sponsor CRM System - Implementation Plan

Overview

Build a sponsor lifecycle tracking system from prospect through contract signing, including invoicing status tracking and year-over-year sponsor replication. The system adds conference-scoped CRM capabilities while maintaining full backward compatibility with existing sponsor display and management.

---

Implementation Steps

1. Create Sanity Schemas

Location: schemaTypes

sponsorForConference.ts

• References:

  • sponsor → sponsor document (required)
  • conference → conference document (required)
  • tier → sponsorTier document (optional, filtered by conference)
  • assigned_to → speaker document (filtered to is_organizer: true)

• Lifecycle Fields:

  • status: enum ['prospect', 'contacted', 'negotiating', 'closed-won', 'closed-lost']
  • contact_initiated_at: datetime
  • contract_signed_at: datetime

• Financial Fields:

  • contract_value: number (defaults from tier.price[0].amount)
  • contract_currency: enum ['NOK', 'USD', 'EUR'] (defaults from tier.price[0].currency)

• Invoice Fields:

  • invoice_status: enum ['not-sent', 'sent', 'paid', 'overdue', 'cancelled']
  • invoice_sent_at: datetime (auto-populated on status change to 'sent')
  • invoice_paid_at: datetime (auto-populated on status change to 'paid')

• Metadata:

  • notes: text (freeform relationship notes)
  • tags: array of strings from global list:
    • 'warm-lead'
    • 'returning-sponsor'
    • 'cold-outreach'
    • 'referral'
    • 'high-priority'
    • 'needs-follow-up'
    • 'multi-year-potential'

sponsorActivity.ts

• References:

  • sponsor_for_conference → sponsorForConference document (required)
  • created_by → speaker document with is_organizer: true

• Activity Fields:

  • activity_type: enum ['stage_change', 'invoice_status_change', 'note', 'email', 'call', 'meeting', 'contract_signed']
  • description: text
  • metadata: object (structured data for old/new values)
  • created_at: datetime

Registration: Add both schemas to schema.ts

---

2. Build Migration

Location: migrations/014-create-sponsor-for-conference/

Migration Logic

1. Query all conferences with sponsors
2. For each conference.sponsors[] entry, create sponsorForConference document:
   • Default values:
     • status: 'closed-won'
     • invoice_status: 'paid'
     • contract_signed_at: conference._createdAt
     • contract_value: tier.price[0].amount
     • contract_currency: tier.price[0].currency (fallback: 'NOK')
   • Preserve: sponsor ref, conference ref, tier ref
3. Log document counts before/after for validation
4. DO NOT modify original conference.sponsors[] array (backward compatibility)

Files

• index.ts - Migration implementation
• README.md - Migration instructions following existing pattern:
  • Backup command
  • Validation command
  • Impact statement (affected document count)
  • Run command

---

3. Extend tRPC Routers

Location: sponsor.ts

New Sub-Router: crm

Queries:

• list: Filter/sort by conferenceId, status, assigned_to, invoice_status, tags
• getById: Fetch single sponsorForConference with related data

Mutations:

• create: Create new sponsor prospect/relationship
• update: Update fields (contract value, notes, tags, etc.)
• moveStage: Change status with validation, auto-log activity
• updateInvoiceStatus: Change invoice status, auto-set timestamps, auto-log activity
• delete: Remove sponsor relationship
• copyFromPreviousYear:
  • Params: sourceConferenceId, targetConferenceId
  • Logic: Copy all 'closed-won' sponsors as 'prospect' status
  • Preserve assigned_to if organizer exists in target conference
  • Reset financial/invoice fields

Nested Sub-Router: crm.activities

• list: Fetch activity log for sponsor
• create: Add manual activity (note, email, call, meeting)

Supporting Files

• Schemas: src/server/schemas/sponsorForConference.ts - Zod validation
• Services: sanity.ts - GROQ queries and Sanity operations
• Types: types.ts - TypeScript interfaces

---

4. Build CRM UI Components

Location: src/components/admin/sponsor-crm/

Component List

PipelineKanban.tsx

• 5 columns for each status (prospect → contacted → negotiating → closed-won, closed-lost)
• Drag-and-drop functionality using @dnd-kit
• Display total pipeline value per column
• Optimistic updates with error rollback
• Column headers show count and total value

SponsorCRMCard.tsx

• Compact sponsor display for kanban cards
• Shows: logo, name, formatted contract value
• Badges: invoice status (color-coded), tags (chips)
• Assigned-to organizer avatar/name
• Quick actions dropdown menu

SponsorDetailDrawer.tsx

• Slide-over panel (right side)
• Tabbed interface:
  • Overview: Financial summary, contract dates, tags editor
  • Activity Timeline: Chronological activity log
  • Notes: Freeform text editor with save
  • Actions: Quick buttons for stage changes, invoice updates, edit contract
• Close button and keyboard shortcuts (Escape)

ActivityTimeline.tsx

• Grouped by date
• Icon per activity type (color-coded)
• Expandable metadata for stage/status changes
• User attribution with avatar
• Timestamps formatted with formatDatesSafe()

SponsorCRMFilters.tsx

• Multi-select dropdowns:
  • Status filter (prospect, contacted, etc.)
  • Assigned-to filter (organizers list)
  • Invoice status filter
• Tag chips (multi-select with color coding)
• "Clear all filters" button
• Filter count indicator

CopySponsorsModal.tsx

• Conference selector dropdown (previous conferences)
• Preview: show count of sponsors to be copied
• Warning messages (if any organizers won't transfer)
• Confirmation button with loading state
• Success/error toast notifications

---

5. Create CRM Admin Page

Location: page.tsx

Page Structure (Server Component)

Data Fetching:

• getConferenceForCurrentDomain() - Current conference
• tRPC crm.list({ conferenceId }) - All sponsor relationships

Layout:

1. Header:

   • Page title: "Sponsor Pipeline"
   • Conference name subtitle
   • "Copy from Previous Year" button (opens modal)
   • Filter controls

2. Stats Cards Row:

   • Total pipeline value (by stage)
   • Invoice status breakdown (count per status)
   • Unassigned sponsors count
   • Overdue invoices alert

3. Pipeline Kanban:

   • Full-width kanban board
   • Scrollable columns
   • Responsive design (stack on mobile)

Navigation Updates

• Add link in page.tsx header (tab navigation)
• Add "Pipeline CRM" link in admin sidebar navigation
• Breadcrumb: Admin > Sponsors > Pipeline

---

6. Implement Auto-Activity Logging

Location: src/lib/sponsor-crm/activity.ts

Helper Function: createSponsorActivity()

Parameters:

• sponsorForConferenceId: string
• activityType: ActivityType enum
• description: string
• metadata: object (optional)
• createdBy: string (from NextAuth session)

Auto-Logging Triggers (in tRPC mutations):

1. Stage Changes (moveStage):

   • Activity type: 'stage_change'
   • Metadata: { old_status, new_status }
   • Description: "Status changed from {old} to {new}"

2. Invoice Status Changes (updateInvoiceStatus):

   • Activity type: 'invoice_status_change'
   • Metadata: { old_status, new_status, timestamp }
   • Auto-populate invoice_sent_at on → 'sent'
   • Auto-populate invoice_paid_at on → 'paid'
   • Description: "Invoice status changed from {old} to {new}"

3. Contract Signing:

   • Detect when contract_signed_at changes from null → date
   • Activity type: 'contract_signed'
   • Metadata: { contract_value, contract_currency, signed_date }
   • Description: "Contract signed for {value} {currency}"

Timestamp Utilities:

• Use getCurrentDateTime() from @/lib/time for ISO timestamps
• Store user from ctx.session.user in tRPC context
• Handle errors gracefully (log but don't block main operation)

---

Implementation Details

Financial Logic

• Pipeline value calculation: Use contract_value if set, otherwise fallback to tier.price[0].amount
• Currency filtering: Group and sum by contract_currency for accurate totals
• Display formatting: Use formatCurrency() from @/lib/format

Organizer Copying Logic

• When copying sponsors from previous year:
  1. Check if assigned_to organizer exists in target conference organizers[]
  2. If yes: preserve assignment
  3. If no: set assigned_to to null (requires manual assignment)
  4. Log warning in copy operation result

Tag Rendering

• Color coding with clsx:
  • warm-lead: green (bg-green-100, text-green-800)
  • cold-outreach: blue (bg-blue-100, text-blue-800)
  • high-priority: red (bg-red-100, text-red-800)
  • returning-sponsor: purple (bg-purple-100, text-purple-800)
  • referral: yellow (bg-yellow-100, text-yellow-800)
  • needs-follow-up: orange (bg-orange-100, text-orange-800)
  • multi-year-potential: indigo (bg-indigo-100, text-indigo-800)

Invoice Status Badges

• Visual indicators with Heroicons:
  • not-sent: gray, DocumentIcon
  • sent: yellow, PaperAirplaneIcon
  • paid: green, CheckCircleIcon
  • overdue: red, ExclamationTriangleIcon
  • cancelled: gray striped, XCircleIcon

Drag-and-Drop Implementation

• Library: @dnd-kit (may need to install)
• Optimistic UI updates using React Query/tRPC
• Error handling: revert on mutation failure
• Disable drag for 'closed-won' and 'closed-lost' (final states)

Access Control

• All CRM endpoints use adminProcedure
• Requires is_organizer: true in session
• Filter assigned_to options to current conference organizers

---

Backward Compatibility

Data Model

• Preserve: Original conference.sponsors[] array unchanged
• Public display: Continue using conference.sponsors[] for website sponsor sections
• Admin CRM: Use new sponsorForConference documents exclusively
• Both structures coexist: No breaking changes to existing queries

Migration Safety

• Backup required: Manual npx sanity dataset export before migration
• Validation: Check document counts before/after
• Rollback: Migration creates new documents, doesn't modify existing
• Testing: Run on development dataset first

---

Testing Checklist

• Migration runs successfully on test dataset
• All existing sponsor displays still work (homepage, program page)
• CRM page loads with correct data
• Drag-and-drop updates sponsor status
• Activity log shows all automatic entries
• Invoice status transitions update timestamps
• Copy from previous year preserves/removes organizers correctly
• Filters work correctly (status, assigned-to, tags, invoice)
• Stats cards calculate totals accurately
• Access control blocks non-organizers
• tRPC endpoints handle errors gracefully
• Mobile responsive design works

---

Future Enhancements (Not in Scope)

• Contract generation and e-signing integration
• Automated invoicing through accounting system
• Email templates for sponsor communications
• Sponsor portal (self-service login)
• Multi-year contract tracking
• Renewal prediction/health scoring
• Document attachment storage
• Task/reminder automation
• Integration with CRM platforms (HubSpot, Salesforce)