0047. Analytics from message facts: idempotent first-time facts, reports by grouping in the project store, and conversions in a run's conversion window
- Status: Proposed. That members see recipient-level engagement within the retained window, report days in the project’s timezone, and a conversion window of 7 days by default and 30 at most are the maintainer’s decisions of 2026-11-19; a fact retention of 90 days in the first release and releasing re-entry during the conversion window are the maintainer’s decisions of 2026-11-20. Those are Accepted and not reopened.
- Date: 2026-11-19
- Amends: ADR 0023 (deletion also removes engagement records and experiment assignments), ADR 0017 (a run in its conversion window does not hold the re-entry index)
Context
Section titled “Context”The maintainer decided on 2026-11-19 that analytics must work properly: “how many of this email went to whom today”, and whether a drip of audience-filtered sends worked, must be answerable from Claude Code and later the dashboard. No ADR covers analytics.
Facts:
- Built at
6ea2d7a:metrics.getreturns project-wide totals (billable emails, delivered, bounced, complained), andmessages.listlists one customer’s messages. Provider events advance a message’s delivery status in a fixed order and never regress (ADR 0034). Duplicate provider webhooks must not inflate metrics (check 4 of the first validation milestone). - SES sends its Send event after acceptance, sometimes late; a Send event after 72 hours is not billed (ADR 0012). Our own
claimSendand the provider response are always recorded first. - Analytics Engine is sampled and operational only; D1 holds exact cross-project roll-ups; the project store holds everything per project (ADR 0010). A DO holds at most 10 GB, with an alert at 70%.
- Goals are rechecked before each send; evaluating them when an event arrives is Proposed (Goal).
- Engagement records come from ADR 0046; experiment measurement is ADR 0048.
Decision
Section titled “Decision”Message facts
Section titled “Message facts”The message row (one per (run, step), already unique) carries:
- Dimensions:
workflow_id,workflow_version,step_id,variant_id(ADR 0048),template_idonce templates are their own resources,post_conclusion(created after its experiment was concluded, ADR 0048), and the saved audience IDs of the run’s trigger (ADR 0045). A broadcast is a workflow, so its ID is theworkflow_id(ADR 0049). - Cohort time: the message stores
claimed_atonce, at its firstclaimSend(a later claim does not change it), andcohort_at = COALESCE(claimed_at, created_at), so a message never claimed (skipped or waiting before any claim) falls in the period it was created. Reports put a message in the period of itscohort_at, so every rate has its denominator in the same set. The provider’s Send event does not move it. - First-time facts: nullable timestamps
accepted_at(our provider response),sent_at(the Send event, the billing rule of ADR 0012),delivered_at,delayed_at,soft_bounced_at,bounced_at,complained_at,rejected_at,first_open_at,first_human_open_at,first_click_at,first_human_click_at,unsubscribed_at(through this message’s link, ADR 0036), andconverted_at; plusopen_countandclick_count, capped atENGAGEMENT_MAX_COUNT_PER_MESSAGE; plus the final state and reason (skipped:suppressed,failed:outcome_unknown, and so on) andtracked_opensandtracked_clicks(ADR 0046). - Idempotence. A fact is written only when its column goes from null to a value, in the transaction of the event that causes it, and an engagement record is applied once per event ID. Nothing is ever taken back: a duplicate provider event, a redelivered queue message, or a repeated open changes no count, because counts are derived from the facts.
- Per run: entries, skips by reason (re-entry, audience), and exits by reason, from the run and entry rows that exist.
Reports by grouping, no counters yet
Section titled “Reports by grouping, no counters yet”- Every report is a
GROUP BYover message rows, filtered bycohort_atrange and dimensions, counting non-null facts and states, with an index on(workflow_id, cohort_at)and one on(cohort_at). Rates are computed from those counts: delivery, bounce, complaint, human open and human click (over tracked messages only), unsubscribe, conversion. - Hourly or daily counters and their rollups are deferred until the load test shows grouping is too slow at the largest tier.
- Days are built in the report’s
timezone(the project’s by default; maintainer’s decision) fromcohort_at, which is an exact instant, so any zone, including half-hour offsets, is exact. - Activity view (“how many clicks today”, whatever the send date) groups engagement records by their own time, within their retention.
- No workspace-wide report: reports are per project. A workspace view is a later decision.
Conversions
Section titled “Conversions”- A conversion is the run’s goal being met. The goal is evaluated when an event or customer update for that customer arrives (the Proposed rule of Goal becomes part of this decision), as well as before each send.
- Conversion window. A workflow with a goal has a
conversionWindow(ISO 8601 duration, defaultP7D, at mostP30D; maintainer’s decision). After its last send step (a message sent, orskipped:holdoutfrom ADR 0048), the run does not end: it waits in the statewaiting:conversion_windowuntil the window ends (completed) or the goal is met (exited:goal_met). This is an ordinary wait of the engine, with no separate watch index. - While a run waits in its conversion window, its
workflow_entriesrow is no longeractive, so re-entry policies (ADR 0017) treat the run as ended (the maintainer’s decision of 2026-11-20); stop and rollback end it like any waiting run. - Message attribution is last touch: the conversion sets
converted_aton the run’s last message withaccepted_atbefore the conversion. A goal met before any message was accepted is counted on the run asconvertedBeforeSend. One event credits at most one run per workflow: when one event meets the goal of several runs of the same workflow (a run in its conversion window and a newer run of the same customer, or runs underalways), only the run that started latest is credited with the conversion and its message fact; the others exitexited:goal_metwithout a conversion, so a workflow’s conversions are not counted twice. Experiments measure differently, by intent to treat from assignment (ADR 0048); the two answer different questions and reports label which one they show.
Retention and storage
Section titled “Retention and storage”-
Message fact rows are kept for
MESSAGE_FACT_RETENTIONaftercohort_at(90 days in the first release, the maintainer’s decision of 2026-11-20: the longest conversion window plus a 60-day engagement tail; configuration). After it, the row is archived (ADR 0010) and leaves the store; engagement for it is dropped and counted (engagement_dropped_expired). Reports cover about the last 90 days. The 13 months of Data placement for messages is pending the load test, and so is any longer report history. -
Engagement records are kept for the hot window (
ENGAGEMENT_RECORD_RETENTION), then archived with raw clicks. -
Storage budget at the largest tier (1M emails and about 8M events per month, ADR 0014). Estimates of 2026-11-19, not measured; the load test replaces them:
Data Rows Bytes per row with indexes (estimate) Size Message rows with facts, 90 days 3M 550 1.7 GB Engagement records, 30-day hot window, about 1.5 per email 1.5M 180 0.3 GB Experiment assignments (ADR 0048): one per customer and experiment, kept until the experiment is concluded or ended plus EXPERIMENT_ASSIGNMENT_RETENTION(proposed 30 days), then archived; worst case every send in experiments that run 90 days4M 150 0.6 GB customer_event_stats(ADR 0045): one row per (customer, summarised name) actually performed, at most 200 names. Typical: 1M customers × 5 names; heavy: × 20 names5M to 20M 70 0.4 to 1.4 GB Send confirmations (ADR 0050), swept after expiry under 10k 300 under 0.01 GB Existing: customers (1M) and a 14-day raw event window about 3 GB Total about 6 GB typical, about 7 GB heavy: at the 7 GB (70%) alert The heavy case reaches the alert, so the load test must measure it. The levers, in order: the event hot window,
MESSAGE_FACT_RETENTION,EXPERIMENT_ASSIGNMENT_RETENTION, and the event-name cap. Every value is configuration, not code.
Actions
Section titled “Actions”api actions with full parity (ADR 0005), read-only, scope read, role member, target: "project", principal: "any" (ADR 0041):
| Action | Answers |
|---|---|
metrics.get (extended; today’s totals stay the default) |
Counts and rates over from/to with interval (day, none), timezone, groupBy (workflow, step, variant, audience), and the same dimensions as filters; skipped and failed counts by reason; caveats (opens_unreliable; audience_groups_not_additive, because a message can come from several saved audiences) |
metrics.workflow |
One workflow’s funnel by version: entries, skips by reason, exits by reason, conversions, and per-step and per-variant counts |
messages.list (extended) |
Recipient-level rows: customerId becomes optional, and the filters above plus state, reason, engagement (opened, human_opened, clicked, human_clicked, converted, unsubscribed, bounced, complained), and a cursor are added. Each row has the customer’s external ID, the dimensions, the state and reason, and the facts |
messages.get (extended) |
Adds the facts and the message’s engagement records (time, kind, class, reason, link index) |
Members see recipient-level engagement within the retained window (maintainer’s decision). The user’s AI answers “how did yesterday’s send do?” with metrics.get for that workflow and day, and “who got it?” with messages.list.
What the dashboard can rely on
Section titled “What the dashboard can rely on”Only the actions above (ADR 0028): a project overview with a daily chart and the skipped and failed reasons; per workflow and per broadcast, the funnel and the per-step and per-variant tables; experiment status (ADR 0048); drill-down to recipients and a message’s timeline; opens always shown as estimates beside human opens.
Privacy and pricing
Section titled “Privacy and pricing”- Customer deletion (ADR 0023) deletes the customer’s messages with their facts, their engagement records, and their experiment assignments. Reports are computed from what remains, so a report over a past period can change after a deletion; the API docs say so.
- Unsubscribing does not stop recording engagement on mail already sent.
- Opens and clicks are not events: they do not count toward the event cap (ADR 0013) and are not billed. Analytics adds no plan feature or price (ADR 0014). Retention values are configuration.
Consequences
Section titled “Consequences”- Counts are exact and idempotent by construction, from one store, with no second copy to reconcile.
- Grouping over rows costs CPU per report; it is acceptable at the sizes above and is the first thing the load test measures.
- Runs of workflows with goals live longer by the conversion window, and the step log shows
waiting:conversion_window. - Open: counters and rollups for history beyond the fact retention; per-link click reports; recipient exports beyond the window; a workspace-wide view.