Spec — Sequences page (NEW)

Automated multi-step outreach ("drip cadences tied to pipeline stages") — the Follow Up Boss replacement. Prototype: panel-sequences.

UX

  • Left list: sequences, each with name, 4 steps · 7 running · 31 sent, and a progress bar (--pct). Active item = accent-tinted.
  • Right detail:
    • Trigger line: Triggers when: pipeline.stage = "new".
    • Stats row: Active runs, Open rate, Reply rate, Moved to (9 / 31).
    • Timeline of steps (vertical line + circles, .fired = sent). Each step: delay (T+0h, T+24h, T+72h, T+7d), channel dot (Email / SMS / call / task), status (sent / queued / scheduled), template subject, body preview with {{first_name}} {{street}} {{agent}} merge vars, and counters (Opens / Clicks / Replies).
  • + New sequence and + New step editors.

Engagement model

A sequence is a template (steps). Enrolling a lead creates an enrollment that walks the steps on a delay schedule, materializing one message per step. Messages carry status + engagement (open/click/reply). Sequence-level stats are rollups over its enrollments' messages.

D1 schema (new migration)

  • sequences(id, user_id, name, description, trigger_stage[new|contacted|offer-sent|under-contract|assigned], status[active|paused|archived] DEFAULT active, created_at, updated_at) — idx (user_id, status, updated_at).
  • sequence_steps(id, sequence_id→sequences ON DELETE CASCADE, step_order, channel[email|sms|call|task], delay_minutes, template_name, template_subject, template_body /* {{vars}} */, condition?, created_at) — idx (sequence_id, step_order).
  • sequence_enrollments(id, sequence_id, lead_id→leads, contact_id?, enrolled_at, enrolled_by['trigger'|'manual'], status[active|paused|completed|unsubscribed] DEFAULT active, current_step_index, completed_at, unsubscribe_reason?) — idx (sequence_id, status, enrolled_at), (lead_id, status).
  • sequence_messages(id, enrollment_id→sequence_enrollments ON DELETE CASCADE, step_id→sequence_steps, channel, recipient, message_body /* rendered */, status[scheduled|queued|sent|failed|opened|clicked|replied] DEFAULT scheduled, scheduled_for, sent_at, failed_reason?, engagement_data(JSON: {opened_at,clicked_at,clicked_links,replied_at,reply_body}), created_at) — idx (enrollment_id, status, scheduled_for), (step_id, sent_at).
  • Optional later: sequence_contacts (multi-contact leads: owner + attorney), sequence_templates (reusable), sequence_analytics (denormalized rollup for dashboard speed).

API (/api/v1)

CRUD: GET/POST /sequences, GET/PUT/DELETE /sequences/:id (DELETE = archive), POST /sequences/:id/steps, PUT/DELETE /sequences/:id/steps/:stepId. Enrollment: POST /sequences/:id/enroll {lead_ids[]}, GET /sequences/:id/enrollments, PUT …/enrollments/:eid (pause/resume/unsubscribe). Messages: GET /sequences/:id/messages, POST …/messages/:mid/resend. Dashboard: GET /sequences/stats.

Internal (cron-driven, not UI):

  • POST /sequences/trigger-check — find leads whose pipeline stage matches an active sequence's trigger_stage and auto-enroll them. Driven by the Pipeline stage-change event and/or a cron sweep.
  • POST /sequences/message-dispatch — pick scheduled messages with scheduled_for <= now, send via provider, set sent/sent_at. Cron every 60s.

Sending

Reuse @velli/email-relay (Resend) for email. SMS/call are stubs initially (mark sent and record intent). Anti-spam: respect unsubscribed, dedupe per enrollment+step.

Build notes

  • Ship the read/visual slice first (list + detail timeline from seeded sequences/steps/enrollments/messages) — fully verifiable offline.
  • Then the engine slice: enroll → schedule → cron dispatch → engagement status, wired to the pipeline stage-change event.

Implementation plan — send engine (ROADMAP slice 7)

Added 2026-07-06. Status: NOT BUILT. This section is the implementation-ready plan; a cold-start agent should need nothing beyond this file + the repo.

Ground truth today (verified 2026-07-06)

  • Schema exists: migration app/migrations/0007_sequences.sql already creates sequences, sequence_steps, sequence_enrollments, sequence_messages (incl. scheduled_for, recipient, engagement_data) plus seeds. No new migration is needed for the core engine; add one only for the columns listed below.
  • Read routes only: app/worker/routes/sequences.ts implements GET /sequences and GET /sequences/:id (rollup stats). No CRUD, enroll, or dispatch routes exist.
  • Page is hidden (ADR 2026-06-04 in docs/content/DECISIONS.md): /sequences redirects to / in app/src/App.tsx; TopNav/⌘K entries removed. Un-hiding is the last step of this slice.
  • Cron already runs every 60s: app/wrangler.jsonc crons: ["* * * * *"] → app/worker/scheduled.ts scheduled() (currently settles auctions + restocks the demo floor). The dispatcher hooks in there — no new trigger infra.
  • Email provider: the original spec said "reuse @velli/email-relay" — that package is not a dependency of this repo (checked package.json + app/package.json). Decision for this slice: call the Resend REST API directly with fetch (POST https://api.resend.com/emails), matching the house pattern (Stripe is also raw fetch, no SDK). RESEND_API_KEY and RESEND_DEFAULT_FROM exist in the repo-root .env (local); they must be set as worker secrets (bunx wrangler secret put RESEND_API_KEY --config app/wrangler.jsonc, same for a RESEND_DEFAULT_FROM var) before live send. Sender domain must be verified in Resend before production dispatch.

Build order (each step testable in isolation)

  1. Enrollment write path — POST /sequences/:id/enroll {lead_ids[]}: creates one sequence_enrollments row per lead (skip if an active enrollment for that (sequence, lead) already exists — uniqueness rule), and materializes the step-1 message with status='scheduled', scheduled_for = now + step.delay_minutes. PUT .../enrollments/:eid for pause/resume/unsubscribe.
  2. Renderer — pure function: template body + lead/contact fields → rendered text. Merge vars {{first_name}} {{street}} {{agent}}; unknown vars render empty, never throw. Unit-test directly (no D1).
  3. Dispatcher — dispatchDueSequenceMessages(env, nowMs) in a new app/worker/sequences-engine.ts, called from scheduled() after settlement:
    • Select sequence_messages WHERE status='scheduled' AND scheduled_for <= now (use parseDbTime/datetime() normalization — D1 timestamps lack T/Z), LIMIT batch (see rate rules), JOIN enrollment+sequence to skip paused|unsubscribed|archived.
    • Claim before send (idempotency, same pattern as auction settlement): UPDATE sequence_messages SET status='queued' WHERE id=? AND status='scheduled'; only a claim that changes 1 row proceeds. A crashed send leaves queued rows; a sweep re-queues queued older than 10 min.
    • Email channel → Resend fetch; on 2xx set status='sent', sent_at=now, store the Resend message id in engagement_data; on failure set status='failed', failed_reason (no auto-retry in v1 beyond the queued-sweep; a POST .../messages/:mid/resend route covers manual retry).
    • SMS/call/task channels → stub: mark sent and record intent (per spec).
    • After a send, materialize the enrollment's next step message (scheduled_for = sent_at + next.delay_minutes); if no next step, set enrollment status='completed', completed_at.
  4. Trigger check — auto-enroll on pipeline stage change: in the PUT /portfolios/:id/stage handler (the event emitter slice 3 built), enqueue enrollment for active sequences whose trigger_stage matches, same dedupe rule as step 1. A cron sweep backstops missed events.
  5. Engagement — v1 ships without open/click tracking wired: Resend webhooks (email.opened, email.clicked, delivery events) need a public webhook route + signing-secret verification (same shape as the Stripe webhook). Ship send/complete first; add POST /webhooks/resend as a fast follow, updating sequence_messages.status + engagement_data.
  6. Un-hide the page — restore the TopNav link, ⌘K entry, and <Route path="/sequences" element={<Sequences />} /> (reversal steps are in the 2026-06-04 ADR). Add enroll/pause UI affordances to the existing read UI.

Idempotency & rate rules (hard requirements)

  • One live enrollment per (sequence_id, lead_id): reject/skip duplicates.
  • One message per (enrollment_id, step_id): guard with INSERT OR IGNORE on a new unique index — add migration 002X_sequence_engine.sql with CREATE UNIQUE INDEX ... ON sequence_messages(enrollment_id, step_id) and a sends_attempted counter column if needed.
  • Claim-before-send (above) makes concurrent crons safe.
  • Rate cap: max 50 emails per cron tick (Workers subrequest headroom + Resend rate limits), FIFO by scheduled_for. Overflow simply waits for the next tick — the query is naturally resumable.
  • Suppression: never send when enrollment is unsubscribed/paused, when the sequence is paused|archived, or when the recipient email is missing — mark failed with reason no_recipient rather than silently skipping.
  • Kill switch: gate the dispatcher on a SEQUENCES_SEND_ENABLED env var (default off in production until Cam flips it) so deploy ≠ send.

Test plan

  • Unit (no D1): renderer merge vars incl. missing fields; due-selection SQL boundary cases via parseDbTime; rate-cap batching math.
  • Integration (worker tests, fake D1 like scheduled.test patterns): enroll → step-1 message materialized with correct scheduled_for; duplicate enroll is a no-op; dispatch claims + sends (Resend fetch mocked) + advances to next step; final step completes enrollment; paused/unsubscribed enrollments never dispatch; failed send records failed_reason; replayed dispatch of a claimed row is a no-op; stage-change trigger enrolls exactly once.
  • Programmatic: bun run verify green; migration applies locally and --remote after merge; wrangler secret list shows RESEND_API_KEY (names only).
  • Live proof (gated): with SEQUENCES_SEND_ENABLED on and a disposable recipient, one real enrollment sends one real email (Resend dashboard receipt
    • D1 sent row), a resend is idempotent, and unsubscribe stops step 2. Never send to real seller leads during proof — use a fixture lead whose contact email is a team address.

Out of scope for this slice

Real SMS/call providers; sequence_templates/sequence_analytics tables; per-user sending quotas; timezone-aware send windows (v1 sends on schedule, UTC). Note them in the PR if they bite.