crm-lead-pipeline-patterns

CRM Lead Pipeline — Patterns Canônicos

Quando usar

LLM carrega esta skill ao implementar CRM lead pipeline em B2B multi-tenant. Trigger phrases:

• "CRM lead pipeline", "sales pipeline stages"
• "lead state machine Postgres", "transition validation"
• "ownership transfer lead", "lead assignment"
• "lead dedup phone email"
• "integração WhatsApp CRM lead"

Regras absolutas

REGRA #1 (6 stages canônicos): Pipeline tem 6 stages: lead → qualified → proposal → negotiation → won | lost. Custom stages permitidos via prefix custom_* mas estes 6 são obrigatórios.

REGRA #2 (trigger PG > CHECK constraint): Validar transições via trigger BEFORE UPDATE com RAISE EXCEPTION, não apenas CHECK constraint. CHECK valida valor, mas não valida transição (lead → won direto = bug, deve passar por qualified+proposal+negotiation).

REGRA #3 (ownership transfer com audit): Mudança em leads.owner_id SEMPRE dispara: (a) notificação ao novo owner, (b) entry em audit_logs com previous_owner_id, new_owner_id, reason. Trigger AFTER UPDATE.

REGRA #4 (dedup unique constraints): unique(org_id, contact_phone) + unique(org_id, contact_email) em leads. Insert duplicado falha — app code precisa fazer lookup ANTES.

REGRA #5 (lookup ANTES de criar via WhatsApp): Webhook handler WhatsApp inbound: SELECT id FROM leads WHERE org_id=$1 AND contact_phone=$2. Se existe, append message à conversa do lead. Se não existe, criar lead novo com source='whatsapp_inbound'.

Patterns canônicos

Tabela leads

create table public.leads (
  id uuid primary key default gen_random_uuid(),
  org_id uuid not null references public.organizations(id) on delete cascade,
  dept_id uuid references public.departments(id) on delete set null,

  -- Contato
  contact_name text not null,
  contact_email text,
  contact_phone text,
  contact_company text,

  -- Pipeline
  stage text not null default 'lead'
    check (stage in ('lead', 'qualified', 'proposal', 'negotiation', 'won', 'lost')
           or stage like 'custom\_%'),
  source text,  -- 'whatsapp_inbound', 'website_form', 'manual', etc.

  -- Ownership
  owner_id uuid references auth.users(id) on delete set null,

  -- Dados financeiros
  expected_value numeric(12, 2),
  expected_close_date date,
  closed_at timestamptz,
  closed_reason text,

  -- Metadata
  metadata jsonb not null default '{}'::jsonb,
  created_at timestamptz not null default now(),
  updated_at timestamptz not null default now(),

  -- REGRA #4: dedup
  unique (org_id, contact_phone),
  unique (org_id, contact_email)
);

create index leads_org_stage_idx on public.leads (org_id, stage);
create index leads_org_owner_idx on public.leads (org_id, owner_id) where owner_id is not null;
create index leads_org_dept_idx on public.leads (org_id, dept_id) where dept_id is not null;

-- RLS: aplicar pattern multi-tenant-rls-hierarchy
alter table public.leads enable row level security;

create policy "leads_select_member" on public.leads
  for select to authenticated
  using (private.is_member_of(org_id));

create policy "leads_insert_with_permission" on public.leads
  for insert to authenticated
  with check (private.has_permission('create', 'leads', org_id));

create policy "leads_update_with_permission_or_owner" on public.leads
  for update to authenticated
  using (
    private.has_permission('update', 'leads', org_id)
    or owner_id = (select auth.uid())
  )
  with check (
    private.has_permission('update', 'leads', org_id)
    or owner_id = (select auth.uid())
  );

create policy "leads_delete_admin" on public.leads
  for delete to authenticated
  using (private.has_role(org_id, 'admin') or private.has_role(org_id, 'owner'));

create policy "leads_super_admin_bypass" on public.leads
  as permissive for all to authenticated
  using (private.is_super_admin())
  with check (private.is_super_admin());