33. CRM / Business-Development pipeline

Date: 2026-06-14

Status

Accepted

Context

Nightingale sells managed staffing to skilled-nursing facilities. Until now the only notion of a sales funnel was the facilities.status = 'prospect' flag plus the NY-SNF prospect import — useful for record-keeping, but not a workspace for business development. The team wanted a real pipeline: stages, an assigned owner, lead source, follow-ups, opportunity value, and an activity log — viewed as a Kanban board — plus a clean way to turn a won lead into a client.

We considered extending facilities (prospects = the funnel). The owner chose a separate leads entity instead: it can hold leads that are not yet (or never become) facilities, keeps the facilities table focused on actual clients, and avoids overloading status.

Decision

• Separate crm_leads + crm_activities tables (migration 0032). A lead carries a Kanban stage (new → contacted → qualified → proposal) and a terminal status (open | won | lost), so won/lost leads leave the board but stay reportable. Other fields: owner_id (→ profiles), lead_source, primary contact (name/email/phone), city/state, estimated_value_cents (USD, integer cents per the money rule), next_follow_up_date, expected_close_date, notes, converted_facility_id, lost_reason. crm_activities mirrors facility_notes (call/email/meeting/note). Both have company-scoped admin RLS and the standard set_updated_at + audit_row triggers.
• Won → convert to facility. markLeadWon creates an active facilities row from the lead (mapping in src/lib/crm/pipeline.ts, unit-tested) and copies the primary contact into facility_contacts, then marks the lead won and links converted_facility_id. The facilities FK has no cascade, so if the final lead update fails we delete the just-created facility (its contact is removed by facility_contacts' ON DELETE CASCADE) — no orphaned client.
• Kanban board at /admin/crm (PipelineBoard). Cards move by native HTML5 drag-and-drop (desktop) or a per-card "Move to…" menu (touch / keyboard). Moves are optimistic and re-sync on router.refresh(); failures revert and toast. Stage advance, mark-won (with conversion), and mark-lost (with reason) live on the lead detail page where they can be confirmed.
• Security per ADR-0004. All writes go through 'use server' actions that verify the caller via getCurrentAdmin, Zod-validate input, and scope every query by company_id (the service client bypasses RLS). owner_id is checked to belong to the admin's company (defence-in-depth on the FK). CRM actions are admin-level, matching createFacility (creating a client is admin work; terminate and archive remain owner-only on the facility side).
• Reuse, not reinvention. New shared table primitives — a portal-based RowActions kebab menu, URL-driven SortHeader (@/lib/table/sort), and FilterBox — back both the rebuilt facilities table and the CRM, and are reusable across other admin lists.

Consequences

• The facilities table is now client-focused (active/inactive) with quick row actions; the new pipeline owns top-of-funnel prospecting. The legacy facilities.status = 'prospect' rows from the NY-SNF import still exist; converting them into crm_leads and repointing the importer is a recommended follow-up (not done here) so the pipeline becomes the single funnel. Until then, both notions of "prospect" coexist.
• crm_activities duplicates the shape of facility_notes rather than sharing a table — acceptable for a separate entity; a unified activity timeline could be revisited later.
• The Kanban uses native drag-and-drop (no new dependency); the per-card menu is the accessible and mobile path.