Prompt Library

Add a Postgres migration that creates the schema for a follow-up email engine. Use these tables:

- contacts: id (uuid pk), first_name, last_name, email, phone, service_type, address, notes, source (check in 'manual','csv','sms_intake','api', default 'manual'), created_at.
- sequences: id (uuid pk), slug, name, is_default boolean, enabled boolean default true. Enforce only one is_default = true at a time.
- sequence_steps: id (uuid pk), sequence_id fk, position int, channel (check in 'email','sms', default 'email'), delay_hours int (cumulative hours from enrollment start), subject text, body text, enabled boolean default true. Unique on (sequence_id, position).
- contact_enrollments: id (uuid pk), contact_id fk, sequence_id fk, current_position int default 0, status (check in 'active','paused','completed','canceled','opted_out', default 'active'), next_send_at timestamptz, context_jsonb jsonb (frozen copy of the contact's merge-tag values at enrollment time), started_at, completed_at, canceled_at. Add a partial unique index so a contact can only have one active enrollment per sequence.
- email_jobs: id (uuid pk), job_type text, to_email text, subject text, body text, status (check in 'pending','sending','sent','failed', default 'pending'), attempt_count int default 0, related_enrollment_id fk nullable, related_step_id fk nullable, created_at, sent_at.
- send_log: id (uuid pk), email_job_id fk, to_email, provider_message_id text, sent_at, created_at.

Add sensible indexes: enrollments by (status, next_send_at) for the worker scan, email_jobs by (status, created_at). Then seed one default sequence "post_appointment" with 4 steps at delay_hours 0, 24, 72, 96 - a thank-you, a check-in, a review ask, and a referral ask. Use {{first_name}}, {{business_name}}, and {{review_link}} merge tags in the bodies.

Build a lightweight operator dashboard for the follow-up engine, no heavy framework. Three screens:

1. Contacts: a table listing contacts (name, email, phone, service type, created date) with a "New contact" form (first/last name, email, phone, service type) that POSTs to an API route inserting into the contacts table.
2. Sequence editor: load the default sequence and render its steps in order. Each step is an editable card with fields for subject, body (textarea), and delay (hours from enrollment). Save writes back to sequence_steps. Show the merge tags ({{first_name}}, {{business_name}}, {{review_link}}) as a hint above the body field.
3. Enroll: on a contact's row, an "Enroll" button that calls an API route which inserts a contact_enrollments row for the default sequence - status 'active', current_position 0, next_send_at = now(), and context_jsonb = a snapshot of that contact's merge-tag values.

Keep it server-rendered with small inline scripts for the form submits (match the existing style in this project). Validate inputs with zod on the server. Return clear JSON errors the UI can show inline.

Build two background workers for the follow-up engine, each runnable as its own script on a timer (I'll wire them to systemd timers - one every 60s, one every 30s).

Worker A - sequence advancer (60s):
- In a transaction, SELECT from contact_enrollments WHERE status='active' AND next_send_at <= now() ORDER BY next_send_at LIMIT 50 FOR UPDATE SKIP LOCKED (so multiple runs never grab the same row).
- For each: find the sequence_step at current_position. Render its subject + body by substituting merge tags ({{first_name}} etc.) from the enrollment's context_jsonb. Insert an email_jobs row (status 'pending') with the rendered subject/body and to_email.
- Advance the enrollment: bump current_position. If a next step exists, set next_send_at = started_at + (next step's delay_hours). If not, set status='completed', completed_at=now().

Worker B - email sender (30s):
- SELECT pending email_jobs LIMIT 50 FOR UPDATE SKIP LOCKED. For each, mark 'sending', send the email, then mark 'sent' (write a send_log row with the provider message id) or 'failed' (bump attempt_count; give up after 3 attempts).
- Sending: a thin sendEmail(to, subject, html, text) helper using AWS SES v2 (@aws-sdk/client-sesv2, SendEmailCommand). Read region + from-address from env (AWS_REGION, SES_FROM_EMAIL, SES_FROM_NAME). Don't build a domain/reputation layer - just send.

Write a renderMergeTags(template, context) helper shared by both, and unit-test it against missing keys (leave the literal tag if a key is absent).

Add a Twilio inbound-SMS webhook that lets an operator enroll a contact by texting our number. Route: POST /api/twilio/inbound, body is Twilio's form-urlencoded (Body, From).

Steps, first match wins:
1. Validate the Twilio signature using the twilio library's validateRequest against the auth token, the X-Twilio-Signature header, the public webhook URL (from env TWILIO_WEBHOOK_PUBLIC_ORIGIN + the request path), and the parsed body. Reject with 403 if invalid.
2. If Body (trimmed, uppercased) is a STOP keyword (STOP/UNSUBSCRIBE/CANCEL/END/QUIT) → add the sender to a suppression list and reply "You're unsubscribed. Reply START to opt back in."
3. If it's HELP or INTAKE → reply with the expected format: "Text a contact as: Name | Email | Phone | Service".
4. Otherwise parse the body as a contact: split on "|" (fall back to commas). Extract a name, an email (strict regex), a phone (10-15 digits), and an optional service type. Normalize the phone to E.164 (10 digits → +1XXXXXXXXXX; 11 starting with 1 → +1…; already-+ → strip non-digits). If required fields are missing/invalid, reply listing exactly what to fix and stop.
5. On a clean parse: insert a contacts row (source 'sms_intake'), create an active contact_enrollments row on the default sequence with next_send_at=now() and a context_jsonb snapshot, and reply "Enrolled {name} - first message goes out shortly."

Respond with TwiML (<Response><Message>…</Message></Response>) so the reply text comes back over SMS. Read TWILIO_ACCOUNT_SID / TWILIO_AUTH_TOKEN / TWILIO_NUMBER from env.

Add a sendSms(to, body) helper using the twilio Node library. If TWILIO_MESSAGING_SERVICE_SID is set, send with { messagingServiceSid, to, body } so we ride an A2P 10DLC campaign + sender pool. If it isn't set (local/dev), fall back to { from: TWILIO_NUMBER, to, body }. Cache the Twilio client in module scope so we don't re-create it per call. Return the message SID on success; log and rethrow on failure so callers can decide whether to retry. Don't add a queue - direct send is fine for this volume.

# Database
DATABASE_URL=postgres://user:pass@127.0.0.1:5432/your_db

# AWS SES (sending) - see the setup pack for getting out of sandbox + verifying a domain
AWS_REGION=us-east-1
AWS_ACCESS_KEY_ID=replace-me
AWS_SECRET_ACCESS_KEY=replace-me
SES_FROM_EMAIL=hello@mail.yourdomain.com
SES_FROM_NAME=Your Business

# Twilio (text-to-enroll)
TWILIO_ACCOUNT_SID=ACxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
TWILIO_AUTH_TOKEN=replace-me
TWILIO_NUMBER=+15555550100
TWILIO_MESSAGING_SERVICE_SID=MGxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx   # optional, enables A2P 10DLC
TWILIO_WEBHOOK_PUBLIC_ORIGIN=https://yourdomain.com