Lead Pipeline Data Model

Overview

Petunia now persists the complete lead lifecycle (pipeline, stages, activities, sources, assignment rules, and conversions) in Prisma. The schema mirrors the stateful concepts described in the contact & lead management spec and follows the same multi-tenant guardrails we use for billing/Stripe objects (immutable IDs, explicit ownership, metadata blobs for custom fields).

Tables

LeadPipeline

• Purpose: Owns a customizable pipeline per client/company.
• Key fields: id, businessId, clientId, companyId, name, isDefault, isActive, metadata.
• Relations: LeadPipelineStage[], LeadAssignmentRule[], Lead[], LeadConversion[].
• Notes: Only one isDefault pipeline is expected per client/company. Metadata keeps feature flags (seedVersion, isDemoData).

LeadPipelineStage

• Purpose: Ordered Kanban/Lifecycle stages tied to a pipeline.
• Key fields: pipelineId FK, position, probability, slaMinutes, metrics.
• Relations: Belongs to LeadPipeline, referenced by Lead, LeadConversion, and assignment rules.

LeadActivity

• Purpose: Append-only timeline that mirrors the “activity feed”.
• Key fields: leadId, activityType, description, channel, metadata, context, createdByUserId.
• Indexes: leadId, (businessId, activityType), clientId, companyId, contactId, createdByUserId for analytics.

LeadAssignmentRule

• Purpose: Stores auto-routing configs (conditions + assignment payload) similar to Stripe webhook rulesets.
• Key fields: priority, isActive, matchType, conditions JSON, assignment JSON, optional pipelineId/appliesToStageId.

LeadSource & LeadSourceMetric

• Purpose: Canonicalizes lead attribution and stores aggregated UTM style stats.
• Key fields: type, medium, campaign, channel, isPrimary, metrics JSON.
• Metrics Table: periodStart, timeframe, leadsCount, conversions, conversionRate, cost, revenue.

LeadConversion

• Purpose: Audit log for stage transitions + monetary impact.
• Key fields: leadId, pipelineId, stageFromId, stageToId, amount, probabilityBefore/After, metadata.
• Usage: Feed forecasting + win/loss analytics; one row per conversion event.

Lead (updated)

• Added fields: pipelineId, pipelineStageId, leadSourceId, assignmentRuleId, campaignMetadata JSON.
• Relations: New FKs to pipeline, stage, source, and assignment rule plus activity/conversion children.

Migrations & Backfill

• prisma/migrations/20251119_add_lead_pipeline_tables adds all tables, FKs, and indexes.
• Migration auto-creates default pipelines/stages and maps historical Lead.source values into LeadSource rows so legacy data keeps attribution.
• Existing leads are assigned to their client/company pipeline (fallbacks to a global pipeline) and placed in the first stage.

Seeding

• prisma/seed.ts now seeds:
  • Default pipeline + seven stages per client (demo vs production colors/metadata kept separate).
  • Baseline lead sources (Website, Referral, Paid Search) and a default round-robin assignment rule.
• Demo portals keep demo-only metadata so dashboards can filter them out from real tenants.

Repository Helpers

• lib/services/leads/leadRepository.ts exposes Prisma-backed helpers for:
  • Logging & paginating LeadActivity entries.
  • Listing pipelines/stages with ordering and active filters.
  • Managing LeadSource objects (upsert, list) and assignment rules.
  • Recording LeadConversion events.

Operational Notes

• Always update Lead.pipelineStageId when moving a lead to keep reporting accurate.
• Assignment rules should remain pure-data (no code) so they can be evaluated by whichever runtime processes new leads.
• Prefer storing experimental flags in the metadata JSON columns instead of adding more nullable scalar fields.