Skip to main content

Facility Pipeline — design review synthesis

Four independent reviews critiqued the draft against this codebase: schema/security, UX/usability, BD procedure, and codebase integration. This is the distilled result — what was wrong, what changed in the current revision, and what is left as an owner decision.

Applied in this revision (migration 0053, lib, ADR)​

Schema / data (from the data-architecture review)​

  • Idempotent follow-up tasks. follow_up_tasks now has unique (enrollment_id, step_no) so the daily cron can never double-create a task on re-run. The runner uses ON CONFLICT DO NOTHING.
  • Idempotent seed. The "NY SNF 21-Day" step inserts use ON CONFLICT (sequence_id, step_no) DO NOTHING (the draft would throw a unique violation on re-apply).
  • Reserved keyword removed. follow_up_sequences.trigger → enrollment_trigger (trigger is reserved and would require quoting everywhere and trip type-gen).
  • Range CHECKs. pipeline_score ∈ [0,100], pipeline_tier ∈ [1,3], medicaid_pct ∈ [0,100] (numeric(5,2)), urgency_score ∈ [0,4] — DB-enforced so a scoring bug can't corrupt the board.
  • No double active enrollment. Partial unique indexes prevent enrolling the same facility/lead in the same sequence twice (auto-enroll race).
  • facility_aliases table (mirrors billing_payers, 0049) to resolve management-company/DBA names and shrink the manual-match queue.
  • updated_at + trigger added to follow_up_steps (was missing); job_raw gets updated_at.
  • Semantic dedup (one role re-posted weekly = one signal) is handled in the ingest action, not a DB index (a date_trunc-on-date index expression isn't IMMUTABLE and a hard constraint would wrongly reject two genuinely distinct same-week postings).

Scoring model (from the BD-procedure review) — the biggest substantive change​

The draft weights summed to 140% and triple-counted the CMS star hierarchy (overall star is a composite of QM + staffing, both also scored). The revised model in src/lib/pipeline/score.ts:

  • Drops the CMS overall star as an independent factor; scores the QM and Staffing domain stars directly.
  • Role-weights the posting (MDS Coordinator 1.0 → QM clerk 0.5) — an MDS posting is worth more than a QM-clerk posting.
  • Weights sum to 1.0: posting 0.36, QM 0.20, F641 0.18, leadership turnover 0.12, staffing 0.08, MDS-volume 0.06; NY Medicaid census applies a ≤ +8% multiplier.
  • Tier cutoffs are calibratable (deriveTier(score, cutoffs)), to be set from the cohort distribution after the first full score run — not hard-coded — so a team isn't handed 200 "Tier-1" accounts.

UX (from the usability review)​

  • Extend /admin/crm, don't add a competing "Facility Pipeline" nav item. ADR-0033 already ships the Kanban + won-conversion there; the feature adds tabs (Signal Inbox, Today's Follow-ups, Sequences) and reuses the existing board + facility detail. See 04-ux-spec.md.
  • Reuse real components (Table, Badge, Dialog, EmptyState, Toaster/notify, confirmDanger, PhoneInput, AdminShell/AdminNav) — the standalone mockups used a bespoke dark shell that does not exist in production. The annotated mockups now map every element to its real component.
  • All interactive states + a full modal inventory (13 modals) are specified in 04-ux-spec.md (the draft was missing loading skeletons, error states, empty states, and most modals — New lead was a window.prompt).
  • Provenance/trust: score chips show "as of" date; CMS rows show data vintage; signal cards show age + status; the unmatched badge explains why matching failed.
  • Tier color fix: Tier-1 used the danger/red tone (semantically backwards). Tier-1 → brand indigo, Tier-2 → amber, Tier-3 → neutral; status is never color-only.

Integration (from the codebase-integration review)​

  • Migration renumbered 0050 → 0053 — the repo already has 0050–0052 (verified). The file in the tree is 0053_facility_pipeline.sql.
  • Confirmed gotchas folded into 03-implementation-plan.md: type-regen ordering, exactOptionalPropertyTypes insert-object pattern (?? null, spread-omit), noUncheckedIndexedAccess, Biome rules, guardrails (NEXT_PUBLIC_*SECRET ban), tests live in tests/, new env vars must be .optional().

Procedure additions folded into the design spec​

  • Active-client guard (P0): ingestion routes a signal on an active facility to an upsell track, never a new-logo prospect lead.
  • Prospect-Discovery view (P1): surfaces high-score facilities with no open posting (CMS-only signals) — closes the non-signal path.
  • Won→client handoff: flip facilities.status to active, require the intake form by the Qualified stage, and stop the prospect sequence — atomically.
  • Lost re-entry: a new signal on a lost lead prompts "re-open" rather than creating a duplicate.
  • Contact-enrichment gate: block Tier-1 sequence enrollment until at least one Administrator/DON contact with a direct phone exists (547/559 contacts are a generic admin today). Seed Administrator names from the CMS Provider Information backfill.
  • Capacity throttle: per-rep Tier-1 cap (≈8/rep), ≤2–3 new Tier-1 enrollments/rep/week, and a condensed 3-touch monthly Tier-2 nurture (not the full 9-step sequence).
  • Manual-match SLA: auto-dismiss signals unresolved after 3 days; seed facility_aliases from the first weeks of manual matches; mandate the CCN backfill.
  • KPIs (funnel conversion, activity/cadence, pipeline quality, account health) and three dashboards are specified in 01-design-spec.md §7.
  • Outreach tone guardrail: cold emails reference NY market/industry context, not the facility's specific deficiencies/citations (a "we saw your F641" opener reads as surveillance). Baked into the email-draft prompt instruction, not left to ad-hoc generation.

Open decisions for the owner​

  1. Source budget — start free (CareerOneStop + chain career pages) only, or add a licensed vendor (Techmap ~$29/mo) for Indeed/Glassdoor coverage now?
  2. CCN backfill — load the free CMS Provider Information dataset to stamp cms_provider_number? Strongly recommended (feeds the score and makes matching near-exact). The review escalated this from "optional" to "do it first".
  3. Tier cutoffs — confirm calibrating from the cohort distribution (recommended) vs. fixed 70/40.
  4. Auto-enroll — may a Tier-1/high-urgency signal auto-enroll into the 21-day cadence, or always require a click? (Default: manual; auto-enroll available per sequence enrollment_trigger.)
  5. Email send — keep follow-up emails as human-approved drafts (recommended, matches the welcome-email seam), or allow true auto-send for specific steps later?
  6. Upsell track — model active-client expansion as crm_leads with lead_source='upsell' (no schema change) or add a dedicated lane? (Default: reuse crm_leads, filter by facility status.)