Drip/sales sequence engine: freeze a per-enrollment plan and hand each step to the existing scheduled-email queue in the same transaction

When building a multi-step outreach sequence (timed emails + task reminders) on top of an app that already has a scheduled-email sender, naive designs read the live sequence definition at each step, render tokens at send time, and keep timers in memory. Result: edits to the sequence silently change what already-enrolled people receive, token values drift, steps can run twice or be lost after a server restart, and business-day/time-window math gets duplicated between UI preview and the worker.

1) At enrollment, copy the plan into an enrollment_steps table (one row per step: kind, delay in business days, rendered subject/body, chosen A/B variant, task fields, state, planned_at). Editing the sequence later never touches existing plans; a "re-enroll" replays the old plan. 2) Put all calendar math in SQL functions (add_business_days(date, n, mode), slot(step) -> planned_at using the enrollment time zone and the send window) and test them in pgTAP; the UI preview calls the same function. 3) A worker loop picks due steps with FOR NO KEY UPDATE SKIP LOCKED, and in ONE transaction: re-checks the enrollment is still active, inserts a normal email row with status 'scheduled' (or a task row), marks the step done, and plans the next step. The existing scheduled-email sender (which already claims rows 'scheduled' -> 'sending') delivers it once. 4) Pausing tasks: a DB trigger on task status completion marks the waiting step done and plans the next one from the completion time. 5) On restart, a step overdue by more than ~1h is re-slotted to the next allowed window instead of firing at night/weekend. 6) One-active-enrollment-per-contact is a partial unique index (state in active/paused).

Freezing the plan matches how commercial sequence tools behave (changes apply only to new enrollments, tokens frozen at enrollment) and makes every step idempotent: the state change and the side effect commit together, so a crash either did both or neither. Reusing the existing scheduled-email claim keeps exactly one delivery path.