# PRP-013 — Lifecycle email automation & admin marketing hub
- **Status:** Done (Phase A + Phase B implemented & verified; real Brevo delivery + browser UI clickthrough to confirm on staging)
- **Plan:** Lite
- **Depends on:** PRP-009 (Brevo service, `templates.js` layout, event bus), PRP-003 (root admin console), PRP-007 (dashboard aggregates)
- **Estimated effort:** ~3–4 days (Phase A engine ~2 days, Phase B hub ~1.5 days)
- **Pro KB ref:** §32.2 (Brevo), §46 (integrations), advanced reports (teaser)
> **Audience:** junior dev. This PRP turns Lite from "sends a welcome email once" into a **growth
> machine**: a scheduled engine that sends **CTA-optimized lifecycle emails** (e.g. "create your first
> order", "add your first seller") at admin-configured delays, plus an **admin marketing hub** that
> shows who to nudge, which companies perform best, and where the prospects are.
>
> Two hard rules carried over from PRP-009:
> 1. **Fail-soft** — nothing here may ever throw into a request or crash the process. A dead Brevo key,
> a bad template, or a DB hiccup must only log and move on.
> 2. **The email *layout* stays in code** (`templates.js` `wrap()`), but the **content** (subject,
> heading, body copy, CTA label + URL) is **admin-editable per campaign**. The admin never touches
> HTML structure — only the words and the button.
---
## 1. Goal / Why
Users are starting to arrive but many sign up and go cold. We want to (a) **re-engage them
automatically** with well-timed, single-action CTA emails driven by their actual behaviour ("you
registered but haven't created an order"), and (b) give the root operator a **marketing cockpit** to
read platform health (signups, orders, activation funnel, top companies, dormant tenants) so we can
manually reach out to the best prospects. Both feed one outcome: more active Lite companies → more
qualified Pro upgrades.
## 2. Scope
**In (Phase A — automation engine):**
- `email_campaigns` table: a fixed set of **seeded, admin-editable** lifecycle campaigns, each bound to
a **hard-coded trigger** (audience rule) but with **admin-controlled timing + content**.
- `email_sends` ledger guaranteeing **one send per user per campaign** (idempotency, no spam).
- An in-process **scheduler tick** (no new dependency) + a cron-callable `POST /api/admin/automations/run`
fallback, that evaluates due campaigns and sends via the existing Brevo service.
- Admin API + UI to **edit content, set delay, enable/disable, send a test, view send log** per campaign.
- **Unsubscribe / marketing opt-out** (`users.marketing_opt_out` + public one-click endpoint) — required
for lawful bulk email and Brevo deliverability.
**In (Phase B — marketing insights hub):**
- `GET /api/admin/insights` — signups & orders over time, activation funnel, top companies, zero-order
and dormant "prospect" lists.
- Admin page reorganised into tabs: **Companies** (existing), **Insights**, **Automations**.
- CSV export of a prospect list for manual outreach.
**Out (Pro / later):**
- Open/click tracking + A/B testing, drip *sequences* (multi-step flows), per-segment campaign builder,
customer-facing per-order-status SMS/email automation (that's the Pro `sms` feature), send-time
optimisation, and free-form custom campaigns created from the UI. Phase A ships a **fixed catalogue**
of triggers; new triggers are added in code, not by the admin.
## 3. Decisions (ground truth for this PRP)
These are chosen so implementation is unambiguous. Open questions that could change them are in §12.
- **Trigger model:** every campaign has a stable `key` (e.g. `no_first_order`). The **audience query**
lives in code (`automations/triggers.js`); the admin can only edit *timing* (`delay_hours`), *content*,
and *enabled*. This keeps arbitrary SQL out of the admin's hands and keeps sends predictable.
- **Recipient:** the company's single manager user (`users` where `company_id = company.id`,
`role = manager`). Root users are never emailed by campaigns.
- **Idempotency unit:** `(campaign_id, user_id)` for one-shot campaigns. A recurring campaign
(`weekly_summary`) uses `(campaign_id, user_id, period_key)` where `period_key` is e.g. `2026-W28`.
- **Content language:** Phase A ships **single-language (English) editable content** with token
interpolation. Per-locale content (ar/fr) is stored as an optional JSON override column now (so the
schema doesn't need a later migration) but the UI to edit locales is deferred — see §12 Q1.
- **Scheduler:** an in-process `setInterval` tick every `AUTOMATION_TICK_MINUTES` (default 15). Passenger
keeps one long-lived process, so this is enough; the `POST /api/admin/automations/run` endpoint lets a
cPanel cron act as a belt-and-suspenders backup. A DB advisory lock (`GET_LOCK`) prevents overlapping
runs if two processes ever exist.
- **Send throttle:** at most `AUTOMATION_BATCH_MAX` (default 200) emails per campaign per tick, sent
sequentially with a tiny delay, so we never hammer Brevo or block the loop.
## 4. Data model
Three migrations (one file). Follow existing conventions: `t.increments('id')`, `company_id` FKs with
`onDelete`, `t.timestamps(true, true)`, indexes on lookup columns.
### 4.1 `email_campaigns` — the admin-editable catalogue
```
id increments
key string(64) unique -- stable trigger key, e.g. 'no_first_order'
name string(191) -- admin-facing label
description string(500) nullable -- what/when it fires (shown in UI)
trigger_type string(32) -- 'lifecycle' | 'recurring'
delay_hours integer default 24 -- admin-tunable timing (ignored by recurring)
enabled boolean default false -- OFF until the admin turns it on
subject string(255) -- editable, supports {{tokens}}
heading string(255) -- editable email H1
body_html text -- editable body (safe subset, supports {{tokens}})
cta_label string(120) nullable
cta_url string(500) nullable -- {{tokens}} allowed (default {{app_url}})
content_by_locale json nullable -- optional { ar: {subject,heading,body_html,...}, fr:{...} }
last_run_at timestamp nullable
timestamps
```
### 4.2 `email_sends` — the idempotency ledger + audit
```
id increments
campaign_id fk -> email_campaigns onDelete CASCADE
user_id fk -> users onDelete SET NULL
company_id fk -> companies onDelete SET NULL
to_email string(191)
period_key string(16) nullable -- null for lifecycle; 'YYYY-Www' for recurring
status string(16) default 'sent'-- 'sent' | 'failed' | 'skipped'
error string(500) nullable
created_at timestamp default now
UNIQUE (campaign_id, user_id, period_key) -- the anti-duplicate guarantee
INDEX (campaign_id, created_at)
```
> MySQL treats `NULL`s as distinct in a UNIQUE index, so a lifecycle campaign (period_key NULL) could in
> theory insert twice. Guard against it in code by checking existence first **and** by storing a literal
> `'-'` for lifecycle rows instead of NULL (so the UNIQUE constraint actually bites). Use `'-'`.
### 4.3 `users.marketing_opt_out`
```
alter users add marketing_opt_out boolean default false
alter users add unsubscribe_token string(64) nullable -- random, for one-click unsubscribe
```
Backfill `unsubscribe_token` for existing users in the same migration (random hex per row).
## 5. Trigger catalogue (seeded campaigns)
Seed these rows (all `enabled = false` initially). The audience rule for each `key` is implemented in
`triggers.js`; timing/content shown are **defaults** the admin can override.
| key | trigger_type | default delay | Audience rule (code) | CTA |
|-----|--------------|---------------|----------------------|-----|
| `no_first_order` | lifecycle | 24h | active company, registered ≥ delay ago, `orders_count = 0` | "Create your first order" → `{{app_url}}/orders` |
| `no_first_merchant` | lifecycle | 72h | active company, `orders_count ≥ 1`, `merchants_count = 0` | "Add your first seller" → `{{app_url}}/merchants` |
| `dormant_no_orders_7d` | lifecycle | 168h (7d) | active company, had orders, none created in last 7d | "Pick up where you left off" → `{{app_url}}` |
| `approaching_cap` | lifecycle | 0h | Lite company that hit ≥ 80% of daily cap at least once | "Need more than 30/day? Go Pro" → `{{pro_upgrade_url}}` |
| `explore_tracking` | lifecycle | 120h (5d) | active company, `orders_count ≥ 3`, hasn't been nudged about tracking | "Share live tracking with your customers" → `{{app_url}}/track` |
| `weekly_summary` | recurring | — (weekly) | active company with ≥1 order in the ISO week | "Your week on Shiprex" digest → `{{app_url}}` |
> Start by implementing `no_first_order` and `no_first_merchant` (the two the user explicitly asked for)
> end-to-end, then the rest are copy-paste evaluators. `weekly_summary` is the only recurring one — build
> it last; it exercises the `period_key` path.
## 6. Backend implementation (Phase A)
### Step 1 — env additions (`server/src/config/env.js` + `.env`)
Add to the `env` object:
```js
automations: {
enabled: process.env.AUTOMATION_ENABLED !== 'false', // default ON
tickMinutes: Number(process.env.AUTOMATION_TICK_MINUTES || 15),
batchMax: Number(process.env.AUTOMATION_BATCH_MAX || 200),
},
```
`.env` (and `.env.example`): `AUTOMATION_ENABLED=true`, `AUTOMATION_TICK_MINUTES=15`,
`AUTOMATION_BATCH_MAX=200`.
### Step 2 — migration
`npm --workspace server run migrate:make marketing_automation`, then fill per §4 (all three tables/alters
in one file's `up`, reverse order in `down`). Backfill `unsubscribe_token` with
`crypto.randomBytes(24).toString('hex')` per existing user.
### Step 3 — seed the campaign catalogue
`server/src/db/seeds/10_email_campaigns.js` — **idempotent upsert by `key`** (insert if missing, never
overwrite admin edits): for each row in §5, `select ... where key`; if absent, insert defaults. Put the
default English subject/heading/body/CTA here. Keep body copy tight and single-CTA (see §9 copy).
### Step 4 — token interpolation helper
`server/src/services/email/tokens.js`:
```js
// Replace {{token}} with context values. Unknown tokens -> '' (never leak braces to users).
function render(str, ctx) {
return String(str || '').replace(/\{\{\s*(\w+)\s*\}\}/g, (_, k) => (ctx[k] != null ? String(ctx[k]) : ''));
}
module.exports = { render };
```
Standard context keys: `user_name, company_name, country, currency, app_url, pro_upgrade_url,
orders_count, unsubscribe_url`.
### Step 5 — a campaign email builder (reuse the existing layout)
Extend `server/src/services/email/templates.js` with one generic builder that wraps admin content in the
existing branded `wrap()` + `button()` (do **not** duplicate the layout):
```js
const { render } = require('./tokens');
function campaignEmail(campaign, ctx) {
const subject = render(campaign.subject, ctx);
const heading = render(campaign.heading, ctx);
const body = render(campaign.body_html, ctx); // trusted admin content
const ctaUrl = render(campaign.cta_url || '{{app_url}}', ctx);
const cta = campaign.cta_label ? `${button(ctaUrl, render(campaign.cta_label, ctx))}
` : '';
const unsub = `Don't want these tips? Unsubscribe.
`;
return { subject, html: wrap(heading, `${body}${cta}${unsub}`) };
}
```
Export `campaignEmail`. (Admin body is trusted operator input; still store/edit it as a constrained set
of tags — see §8 validation — to avoid breaking the layout.)
### Step 6 — trigger evaluators
`server/src/modules/automations/triggers.js` — a map `key -> async ({ db, campaign }) => recipients[]`.
Each returns `[{ user, company }]` **already excluding** opted-out users, non-active companies, and
anyone with an existing `email_sends` row for this campaign (LEFT JOIN / NOT EXISTS). Example:
```js
async function noFirstOrder({ db, campaign }) {
const cutoff = new Date(Date.now() - campaign.delay_hours * 3600e3);
const rows = await db('companies as c')
.join('users as u', 'u.company_id', 'c.id')
.leftJoin('orders as o', 'o.company_id', 'c.id')
.where('c.status', 'active')
.andWhere('u.role', 'manager')
.andWhere('u.marketing_opt_out', false)
.andWhere('c.created_at', '<=', cutoff)
.groupBy('c.id', 'u.id')
.havingRaw('COUNT(o.id) = 0')
.whereNotExists(function () {
this.select('*').from('email_sends as s')
.whereRaw('s.campaign_id = ?', [campaign.id])
.andWhereRaw('s.user_id = u.id');
})
.select('c.* as _c', 'u.id as _uid'); // shape into {user, company} in JS, or select explicit cols
return rows.map(/* -> { user, company } */);
}
```
> Keep each evaluator small and independently testable. Prefer explicit column selects
> (`c.id as company_id, c.name as company_name, u.id as user_id, u.email, u.name as user_name`) over
> `select *` to avoid column-name collisions across the join.
### Step 7 — the runner
`server/src/modules/automations/runner.js`:
- `runCampaign(campaign)`: call the evaluator for `campaign.key`; for recurring, compute `period_key`
(`YYYY-Www`) and filter already-sent for that period. Cap to `env.automations.batchMax`. For each
recipient: build `ctx` (including `unsubscribe_url = {app_url}/api/unsubscribe?token=`),
render `campaignEmail`, `sendTransactional`, then **insert `email_sends`** with the result status.
Wrap each recipient in try/catch → record `status:'failed', error` and continue. Update
`campaign.last_run_at`.
- `runDue()`: load `enabled` campaigns; run each; **fail-soft** per campaign. Wrap the whole thing in a
MySQL `GET_LOCK('shiprex_automations', 0)` / `RELEASE_LOCK` so overlapping ticks/instances can't
double-send. Return a summary `{ campaign_key: { evaluated, sent, failed, skipped } }`.
### Step 8 — the scheduler
`server/src/lib/scheduler.js`: `start()` sets `setInterval(runDue, tickMinutes*60_000)` **only if**
`env.automations.enabled` and not in test. Call `.unref()` so the timer never keeps the process alive on
shutdown. In `server/src/app.js`, after `require('./listeners/emailListeners').register();`, add
`require('./lib/scheduler').start();`. Log `[automations] scheduler started (every Nm)`.
> Do **not** run the first tick at boot-time synchronously; let the interval fire. Add a small startup
> jitter is unnecessary for a single instance.
### Step 9 — admin automations routes
`server/src/modules/automations/automations.routes.js` (mounted root-only under `/api/admin/automations`
— either add to `admin.routes.js` via `router.use('/automations', ...)` or mount in `routes.js` behind
`requireAuth, requireRoot`). Endpoints in §7. Validate edits with zod; only whitelist editable fields
(`name, description, delay_hours, enabled, subject, heading, body_html, cta_label, cta_url,
content_by_locale`) — never let `key` or `trigger_type` change.
### Step 10 — public unsubscribe
`server/src/modules/unsubscribe/unsubscribe.routes.js`, mounted public at `/api/unsubscribe`:
`GET /?token=...` → set `marketing_opt_out = true` where `unsubscribe_token = token`; always return a
friendly HTML page ("You're unsubscribed") regardless (don't reveal whether the token matched). Rate-limit.
## 7. API surface (Phase A + B)
All admin endpoints: **root only** (`requireAuth, requireRoot`). Standard `{ success, data }` envelope.
| Method / path | Purpose |
|---|---|
| `GET /api/admin/automations` | list campaigns + `{ sent_total, sent_30d, last_run_at }` per campaign |
| `GET /api/admin/automations/:id` | one campaign (full editable content) |
| `PATCH /api/admin/automations/:id` | edit whitelisted fields (content/delay/enabled) |
| `POST /api/admin/automations/:id/test` | body `{ email }` → render + send a test to that address (no ledger row) |
| `POST /api/admin/automations/:id/preview` | body `{}` → return rendered `{ subject, html }` for the editor preview (no send) |
| `GET /api/admin/automations/:id/sends?limit=50` | recent send log rows |
| `POST /api/admin/automations/run` | manually trigger `runDue()` now (also the cPanel-cron target); returns the run summary |
| `GET /api/admin/insights?range=30d` | Phase B analytics payload (see §10) |
| `GET /api/admin/insights/prospects.csv?segment=zero_orders` | CSV export of a prospect segment |
| `GET /api/unsubscribe?token=...` | public one-click opt-out (HTML response) |
**Postman:** add a folder **"Automations"** with the admin endpoints (Bearer root token) and a public
**Unsubscribe** request; commit the updated collection.
## 8. Editor validation & safety (content is operator-trusted but must not break layout)
- `subject` ≤ 255, `heading` ≤ 255, `body_html` ≤ 8000 chars.
- Sanitize `body_html` to a **small allowlist** (`p, br, strong, em, ul, ol, li, a[href]`); strip
`