Proposal: per-step analytics for workflows (sent / delivered / open rate / click rate)

[cyrilchandelier]

Problem

Campaigns expose delivery and engagement stats, but workflows are a black box. Once a workflow is live there is no way to answer:

• How many emails did step 3 actually send?
• Is the open rate on the follow-up email worse than the welcome email?
• Where in the sequence do contacts drop off?

For anyone running onboarding or lifecycle sequences this is the difference between "the automation runs" and "the automation works". Today the only recourse is querying the database directly, and for Cloud customers, that's not even an option.

Good news: the data already exists

No information is lost today, it just isn't aggregated or exposed.

• Email.workflowStepExecutionId is populated at send time (WorkflowExecutionService.ts:576), so every workflow email is traceable to the exact step that sent it.
• Email already carries sentAt, deliveredAt, openedAt, clickedAt, bouncedAt, complainedAt, plus opens / clicks counters.
• WorkflowStepExecution carries stepId and status, which gives entry/completion counts for every step type, not just email steps.
• The SES webhook handler in EmailService.handleWebhookEvent (EmailService.ts:552-587) already increments denormalized campaign counters on delivered / opened / clicked / bounced. There is simply no equivalent branch for workflows.

What is missing

1. No aggregate. Computing stats on the fly means joining emails → workflow_step_executions → stepId across millions of rows on every page view. I'm assuming the performance hit is out of question.
2. No API. Workflows.ts has no stats endpoint.
3. No UI. WorkflowBuilder.tsx / WorkflowVisualizer.tsx render step nodes with no metrics, and WorkflowEdge.tsx renders edges with no metrics.

Proposed implementation

1. Schema: denormalized counters, mirroring Campaign

Add counters directly to WorkflowStep rather than introducing a separate stats table, so the shape matches the existing Campaign precedent (totalRecipients / sentCount / deliveredCount / etc.) and reads need no join.

model WorkflowStep {
  // ... existing fields

  // Stats (denormalized counters, incremented on write)
  enteredCount   Int @default(0)  // all step types
  completedCount Int @default(0)  // all step types

  sentCount         Int @default(0)  // SEND_EMAIL only
  deliveredCount    Int @default(0)
  openedCount       Int @default(0)  // unique, matches campaign semantics
  clickedCount      Int @default(0)  // unique
  bouncedCount      Int @default(0)
  complainedCount   Int @default(0)
}

model WorkflowTransition {
  // ... existing fields
  traversedCount Int @default(0)
}

model Workflow {
  // ... existing fields
  statsSince DateTime?  // when counters started being collected; null = since forever (backfilled)
}

On the trade-off of counters-on-the-row: WorkflowStep rows are wider than a counter table and are read on every execution tick, so constant increments do create more MVCC churn than a narrow dedicated table would. In practice the counters are unindexed, so Postgres can apply HOT updates, and fillfactor can be tuned if page pressure ever shows up. Consistency with the campaign pattern and a smaller diff seem worth more than a second table at this stage; splitting stats out later is a mechanical migration if write volume demands it.

2. Write path

Email events. Add a workflow branch alongside the existing campaign branch in EmailService.handleWebhookEvent. stepId is reachable from the already-loaded email via workflowStepExecution (join on primary key, cheap):

if (email.workflowStepExecutionId) {
  // same switch as the campaign branch:
  //   delivered → deliveredCount
  //   opened    → openedCount, only if !email.openedAt  (unique, matches campaign semantics)
  //   clicked   → clickedCount, only if !email.clickedAt
  //   bounced   → bouncedCount
  //   complained → complainedCount
}

Keeping the !email.openedAt / !email.clickedAt guards means workflow open/click rates are unique-based and directly comparable with campaign rates shown elsewhere in the dashboard.

sentCount increments where the email is created in WorkflowExecutionService.ts:576.

Step entry/completion. Increment enteredCount / completedCount where WorkflowStepExecution rows are created and transitioned to COMPLETED in WorkflowExecutionService.

Transitions. Increment traversedCount at the branch-selection sites in WorkflowExecutionService:

• processNextSteps (~:1113-1138), conditional branch selection
• the WAIT_FOR_EVENT timeout / fallback path (~:351-375)
• the first-transition path (~:648-657)

All three should funnel through one private helper (recordTransition(transitionId)) so the behaviour cannot drift between call sites.

3. Read path

GET /workflows/:id/stats returns, in one query with no joins:

{
  "statsSince": "2026-08-04T00:00:00Z",  // null = complete history
  "steps": {
    "<stepId>": {
      "enteredCount": 1200, "completedCount": 1180,
      "sentCount": 1180, "deliveredCount": 1150,
      "openedCount": 520, "clickedCount": 90,
      "bouncedCount": 30, "complainedCount": 2,
      "openRate": 45.2, "clickRate": 7.8, "bounceRate": 2.5
    }
  },
  "transitions": {
    "<transitionId>": { "traversedCount": 420 }
  }
}

Rates computed server-side over deliveredCount (or sentCount) to match how AnalyticsService derives campaign rates.

4. UI

• Step nodes (CustomNode in WorkflowBuilder.tsx:277, and WorkflowVisualizer.tsx): compact footer on SEND_EMAIL nodes, Sent 1,180 · Open 45% · Click 8%. Non-email steps show Entered 1,200.
• Edges (WorkflowEdge.tsx): traversal count and share of the source step's completedCount, e.g. 420 (84%) on the yes branch, 80 (16%) on the no branch. The gap between the sum of outgoing edges and the source's completedCount surfaces drop-off directly.
• When statsSince is set, the workflow page shows "Stats since Aug 4, 2026" so pre-existing workflows don't look broken with zeroes.

5. Backfill, deliberately not in the migration

The Prisma migration should only do ALTER TABLE ... ADD COLUMN ... INTEGER NOT NULL DEFAULT 0, which is metadata-only on Postgres 11+: instant regardless of table size, and equally safe for Plunk Cloud and for a self-hoster on modest hardware.

Aggregating historical emails inside migrate deploy would be blocking, unbounded, and could wedge a release on a large database. Instead:

• New/existing workflows get statsSince = now() at deploy. The UI is honest about the window; nothing needs to be computed.
• A separate, optional script (apps/api/src/scripts/backfill-workflow-step-stats.ts) lets the operator reconstruct history off-peak if they want it, and clears statsSince for workflows it fully backfills.

The script should be --dry-run capable, filterable by --workflow-id, batched per step with a sleep between batches, and idempotent (writing absolute values rather than increments, so a rerun cannot double-count).

The per-step aggregate stays index-friendly using the existing workflow_step_executions(stepId) and emails(workflowStepExecutionId) indexes, one query per step rather than one scan over the whole email table:

SELECT
  count(*) FILTER (WHERE e."sentAt"       IS NOT NULL) AS sent,
  count(*) FILTER (WHERE e."deliveredAt"  IS NOT NULL) AS delivered,
  count(*) FILTER (WHERE e."openedAt"     IS NOT NULL) AS opened,
  count(*) FILTER (WHERE e."clickedAt"    IS NOT NULL) AS clicked,
  count(*) FILTER (WHERE e."bouncedAt"    IS NOT NULL) AS bounced,
  count(*) FILTER (WHERE e."complainedAt" IS NOT NULL) AS complained
FROM emails e
JOIN workflow_step_executions se ON se.id = e."workflowStepExecutionId"
WHERE se."stepId" = $1;

There is precedent for hand-written data migrations in this repo (packages/db/prisma/migrations/20260615120000_normalize_contact_emails/migration.sql), but that one had to run in lockstep with a constraint change. This one does not, which is exactly why it can live outside the deploy path.

Transitions cannot be meaningfully backfilled. Nothing records which edge a past execution traversed; it could only be approximated by counting executions of each destination step, which breaks down when several steps feed the same destination. Transition counters should start at zero and rely on statsSince.

Scope

In scope for a first PR:

• Counters on WorkflowStep and WorkflowTransition, statsSince on Workflow
• Write-path increments (email events, step entry/completion, transition traversal)
• GET /workflows/:id/stats
• Step node and edge metrics in the workflow builder/visualizer
• Optional backfill script

Explicitly out of scope (possible follow-ups):

• Time-windowed stats / trend charts. This needs a per-step-per-day rollup table. Rough sizing: ~60 bytes per row, 20 steps × 365 days ≈ 7.3k rows per workflow per year, so ~7M rows/year at 1,000 active workflows. Manageable, but it needs a retention policy and a rollup job, and it isn't necessary to answer the core question. Lifetime counters first; buckets only if people ask for trends.
• Per-contact drill-down ("show me the 80 people who didn't open").
• Exposing workflow step stats in the project-level AnalyticsService aggregates.

Questions for maintainers

1. Counters on WorkflowStep vs. a dedicated WorkflowStepStats table: is matching the Campaign precedent the preference, or is MVCC churn on a hot-read table a concern worth pre-empting?
2. Should rates be computed over deliveredCount or sentCount? Whichever matches the campaign dashboard is probably right; happy to follow.
3. Is apps/api/src/scripts/ an acceptable new location for operator scripts, or is there an existing convention for one-off maintenance tasks?

@driaug I'd be happy to open a PR if this direction sounds right, just let me know.

[driaug]

I believe the main difference between a campaign and a workflow is that a campaign is a one-off. There the fields on the campaign work for stat keeping because you never want to compare two date ranges on the same campaign.

With a workflow, there can be changes to the template or overall flow that will have impact on the stats. Thus making it slightly more complex as with the proposal there doesn't appear to be historical context.

[cyrilchandelier]

That's a fair point, and I think it invalidates the counter-on-the-row approach rather than just complicating it. A campaign's stats describe a single immutable send, so one number per metric is the whole truth. A workflow step is a long-lived thing whose behaviour changes underneath the counter, so a lifetime total silently blends "before the template change" with "after it", and that's precisely the comparison people want to make. Same for structural edits: rerouting a branch changes which contacts even reach a step.

So: revised proposal, with time and revision as first-class dimensions.

1. Time-bucketed rollups instead of counters on the row

Drop the counters from WorkflowStep / WorkflowTransition entirely. Single source of truth becomes a daily rollup table:

model WorkflowStepStat {
  id String @id @default(uuid())

  step   WorkflowStep @relation(fields: [stepId], references: [id], onDelete: Cascade)
  stepId String

  revision Int      // workflow revision this bucket belongs to
  date     DateTime @db.Date

  enteredCount   Int @default(0)
  completedCount Int @default(0)

  sentCount       Int @default(0)
  deliveredCount  Int @default(0)
  openedCount     Int @default(0)
  clickedCount    Int @default(0)
  bouncedCount    Int @default(0)
  complainedCount Int @default(0)

  @@unique([stepId, revision, date])
  @@index([stepId, date])
  @@map("workflow_step_stats")
}

Same shape for WorkflowTransitionStat with traversedCount, keyed [transitionId, revision, date].

Lifetime totals are then a SUM over the rows for a step, not a separate stored number, so there's no second source of truth to drift.

On the size worry that made me hesitate here originally: rows are only created for days with activity, so the table is sparse, not dense. A workflow that sends nothing on Sunday has no Sunday rows. At roughly 60 bytes per row, an active workflow with 20 steps sending daily costs about 7k rows per year. It's real but it's small, and it buys the date-range comparison that makes the feature actually useful.

2. Revision stamping, so edits are visible in the data

Add revision Int @default(1) to Workflow, bumped by WorkflowService whenever something that can move the numbers changes:

• a step's config or templateId changes
• a step is added or deleted
• a transition is added, deleted, or its condition changes
• a Template referenced by a SEND_EMAIL step is edited (bump every workflow whose steps reference that template, from TemplateService.update)

Stamp the revision onto WorkflowExecution at creation. The webhook path already loads the email, so email → workflowStepExecution → execution.workflowRevision is two primary-key joins and gives both stepId and revision without any extra lookup.

Alongside it, a small audit table so the UI can label and annotate revisions:

model WorkflowRevision {
  id String @id @default(uuid())

  workflow   Workflow @relation(fields: [workflowId], references: [id], onDelete: Cascade)
  workflowId String

  revision  Int
  changeType String   // STEP_CONFIG | STEP_ADDED | STEP_REMOVED | TRANSITION_CHANGED | TEMPLATE_EDITED
  summary   String?   // "Template 'Welcome' edited", "Delay changed 24h -> 48h"
  createdAt DateTime @default(now())

  @@unique([workflowId, revision])
  @@map("workflow_revisions")
}

That gives the UI what your comment is really asking for: a chart of open rate over time with a marker on the day the template changed, and the ability to compare "v4 vs v3" rather than one blended lifetime average.

A single workflow-level revision counter means an unrelated edit also segments the untouched steps. Per-step revisions would be tighter, but structural edits affect steps they don't touch (rerouting a branch changes a downstream step's inbound traffic), so a per-step counter would lie in the other direction. One workflow-level counter is the honest version, and since raw rows are kept, the UI can always sum across revisions when the user wants the blended view.

3. Attribution: cohort by send date, not event date

Worth deciding explicitly because it determines whether the comparison is valid at all.

If an email goes out Monday and is opened Wednesday, the open can be counted on Wednesday (event date) or on Monday (send date). Campaign counters effectively do event date, which is fine for a one-off blast. For a workflow it smears the effect of a template change across the days after it, so an A/B read on a change is muddy.

Proposal: bucket by the email's sentAt date. Opens and clicks are recorded against the day the email was sent, so a row's openedCount / deliveredCount is a true cohort rate for that day's send under that revision. email.sentAt is already loaded in the webhook handler, so the bucket date needs no extra query.

The tradeoff is that recent buckets keep maturing as late opens arrive, so today's open rate always looks pessimistic. Standard in email analytics, and the UI should just avoid presenting the last 24 to 48 hours as final.

Step entry/completion and transition traversal have no such delay and are bucketed on the day they happen.

4. What this changes about the rest of the proposal

• API: GET /workflows/:id/stats?from=&to=&revision= instead of a bare totals endpoint. Default to the current revision, with an option to include all. Returns per-step and per-transition aggregates for the range, plus the revision markers in it.
• UI: node footers show the selected range rather than all-time, with a range picker on the workflow page. Clicking a step opens a time series with revision markers overlaid.
• Write path: an upsert per event instead of an increment. Same order of cost, and the hot row (stepId, current revision, today) is narrow rather than a wide config-carrying row, which incidentally resolves the MVCC churn concern I raised about putting counters on WorkflowStep.
• Backfill: still an optional out-of-band script rather than part of migrate deploy, and it gets more useful now, since it can reconstruct daily buckets from emails.sentAt. Historical rows would all land under revision 1, since there's no record of past edits. Workflow.statsSince still covers the un-backfilled case.
• Retention: daily rows can be compacted to monthly beyond, say, 12 months if the table ever becomes a concern. Not needed on day one, but the schema doesn't preclude it.

Does this line up with what you had in mind? The main open question from my side is whether workflow-level revisions are the right granularity, or whether you'd rather see full workflow versioning (immutable snapshots of the graph per version), which is more faithful but a considerably bigger change.

---

PS: I'm using Claude to help me write up the proposal, but I read, agree and vouch for every word in it.

[cyrilchandelier]

Quick follow up @driaug, with your go ahead, I can get a PR ready, I just need to make sure I'm aligned with Plunk vision, thank you.